Oracle数据类型阿里巴巴课件

1、主题数值型(NUMBER)字符型(STRING)日期型(DATETIME)大字段(LOB)第1页，共31页。数值型NUMBER(p,s)p:整数位数+小数位数 取值范围： 1-38s:小数位数 取值范围：-47-127Number(6,2)表示总位数为6位，整数最大可以是4位，存储的小数为2位，小数超过2位会自动四舍五入Number(6,-2) ?Number(1,-2) ? 949,950DATA_LENGTH=22 ?第2页，共31页。Number存储结构实际用1-21个字节存储，具体的长度与数值有关系0用1个字节(0 x80)存储，1用2个字节(0 xC1,0 x02)存储，-1用了3个

2、字节(0 x3E,0 x64,0 x66)存储。SQL select dump(123456789,16) from dual; DUMP(123456789,16)-Typ=2 Len=6: c5,2,18,2e,44,5a第3页，共31页。unDumpNumber if Bytes(0) = 128 then -128表示0 result := 0; elsif Bytes(0) 128 then -大于128表示正数 for i in 1 . vlength - 1 loop result := result + (Bytes(i) - 1) * power(100, (Bytes(0)

3、 - 193) - i+1); end loop; else -小于128表示负数 for i in 1 . vlength - 2 loop -负数的最后一个字节总是102，所以可以忽略 result := result + (Bytes(i) - 101) * power(100, (62 - Bytes(0) - i+1); end loop; end if; return(Result);第4页，共31页。新数值类型(10g)BINARY_FLOAT 单精度浮点型数据，内部采用4字节存储BINARY_DOUBLE双精度浮点型数据，内部采用8字节存储牺牲精度，提高性能第5页，共31页。性

4、能测试结果Number VS Binary_Floatinsert into t(c_number,c_float) select rownum/100,rownum/100 from dual connect by rownum1000000运算(999999 rows)(0.01-9999.99)NUMBERBINARY_FLOATNUMBER CAST FLOAT比值NUMBER/FLOAT比值NUMBER/CAST FLOATSUM(A)188ms170ms1563ms1.100.12SUM(A*4.52)250ms218ms1672ms1.140.15LN(A)24141ms469m

5、s1906ms51.4712.67POWER(A,4.5)47952ms594ms2094ms80.7322.90SIN(A)15141ms312ms1781ms48.538.5第6页，共31页。同义词NUMERIC(p,s)：完全映射至NUMBER(p,s)DECIMAL(p,s)或DEC(p,s)：完全映射至NUMBER(p,s)INTEGER 或INT,SMALLINT：完全映射至NUMBER类型，但是小数位精度为0。FLOAT(p)：映射至NUMBER(p) 类型，但小数位精度不限制。DOUBLE PRECISION：相当于FLOAT(126)，映射至NUMBER 类型。REAL：相当

6、于FLOAT(63)映射至NUMBER 类型。第7页，共31页。字符型CHARVARCHAR2NCHARNVARCHAR2第8页，共31页。常见字符集ASCIIANSI(GB2312,BIG5,JIS,GBK)GB2312-7445字符GBK-21886字符GB18030-27484字符CJK,CJKVUNICODE(UCS-2,UTF8,UTF16)ISO-8891-XXLatin-XX第9页，共31页。ORACLE字符集数据库字符集Oracle数据库最早支持的编码方案是US7ASCII Oracle的字符集命名遵循以下命名规则: 即: 比如: ZHS16GBK表示采用GBK编码格式,16位

7、 ZHS16DBCS表示CJK编码格式,16位 WE8ISO8859P1表示ISO-8859-1编码格式,8位国家字符集用以存储NCHAR, NVARCHAR2, NCLOB等类型数据只能在unicode编码中的AF16UTF16和UTF8中选择，默认值是AF16UTF16第10页，共31页。常用字符类型CHAR(n BYTE | CHAR) 1-2000VARCHAR2(n BYTE | CHAR) 1-4000NCHAR(n) 1-2000，默认为1NVARCHAR2(n) 1-4000VARCHAR是VARCHAR2的同意词，主要是兼容作用第11页，共31页。状态类型CHAR(1)VAR

8、CHAR2(1)NUMBER(1)两者存储的空间和性能在ORACLE完全一样，所以没什么区别，Thomas Kate说CHAR/NCHAR 实际上只是伪装的VARCHAR2/NVARCHAR2，所以个人认为VARCHAR2(1)可以完全代替CHAR(1)使用第12页，共31页。Varchar2 VS NumberValuesNUMBER（AVG_DATA_LENGTH）VARCHAR2（AVG_DATA_LENGTH）1-10211-100221-100033(万)1-10000341-100000451-1000000461-1000000057(亿)1-10000000058第13页，共3

9、1页。随机字符串dbms_random.string函数以下是dbms_random.string 函数生成随机的字符型数据类型- u, U - 返回全是大写的字符串 - l, L - 返回全是小写的字符串 - a, A - 返回大小写结合的字符串 - x, X - 返回全是大写和数字的字符串 - p, P - 返回键盘上出现字符的随机组合 第14页，共31页。字符串函数SUBSTR(abcdeft,-3,2)TRIM(001234560,01)如何将一串数字转换为对应的汉字？34234298 to 三四二三四二九八TranslateSelect Translate(3430387664,12

10、34567890,一二三四五六七八九零) from dual;第15页，共31页。日期型DATE公元前4712年1月1日至公元9999年12月31日TIMESTAMP保存小数秒，小数位数可以指定为0-9，默认为6位第16页，共31页。Date内部存储2010-2-12 10:20:30Typ=12 Len=7: 120,110,2,12,11,21,31第1字节：世纪+100第2字节：年+100第3字节：月第4字节：天第5字节：小时+1第6字节：分+1第7字节：秒+1第17页，共31页。Timestamp内部存储2010-2-12 13:24:52.123456Typ=180 Len=11:

11、120,110,2,12,14,25,53,7,91,202,0第1字节：世纪+100第2字节：年+100第3字节：月第4字节：天第5字节：小时+1第6字节：分+1第7字节：秒+1第8-11字节：纳秒，采用4个字节存储，内部运算类型为整形第18页，共31页。TO_CHAR日期转换YYYY-MM-DD HH24:MI:SS如何得到今天是本年的第几天？TO_CHAR(date,DDD)TO_CHAR(date,MM) EXTRACT(date,MONTH)第19页，共31页。求下个月的今天时间？Sysdate+30?Sysdate+31?Sysdate+NUMTOYMINTERVAL(1, mon

12、th)ADD_MONTHS(Sysdate,1)第20页，共31页。ADD_MONTHSADD_MONTHS(date 2010-2-12, 1)ADD_MONTHS(date 2010-2-27, 1)ADD_MONTHS(date 2010-2-28, 1)ADD_MONTHS(date 2010-1-31, 1)第21页，共31页。LOBCLOB(字符)BLOB(二进制)NCLOB(国家字符集)BFILE(外部文件存储)9i MaxSize 4G Bytes10g MaxSize 4G BLOCKS第22页，共31页。LOB 存储定义语法 TABLESPACE tablespace| E

13、NABLE | DISABLE STORAGE IN ROW| storage_clause| CHUNK integer| PCTVERSION integer| RETENTION| FREEPOOLS integer| CACHE | NOCACHE | CACHE READS logging_clause 第23页，共31页。LOB每个LOB字段有两个段 LOBINDEX,LOGSEGMENT,如果未指定名字，则系统自动命令，如：SYS_LOB0000030371C00001$名字里会有表的OBJECT#，其中30371就是表的OBJECTlob存储可以指定单独的表空间， lobind

14、ex 和lobsegment 在同一个表空间中。第24页，共31页。Lobindex & Lobsegment第25页，共31页。LOB -STORAGE IN ROWENABLE(默认)小于4000字节与表数据存在一起，会在data_buffer里保存大于4000字节保存在LOB指定的段中DISABLE所有数据都保存在LOB指定的段中第26页，共31页。LOG -CHUNK Size设置LOB段每个单元分配的字节数，要求是数据块大小的整理倍，如果不是数据块大小的整理倍，ORACLE会自动向上取整，缺省为1个数据块大小，最大为32KBCHUNK是每条记录的每个LOB字段存储的最小单位，假设CHUNK=8192，如果LOB字段只有1个字节，也会点用1个CHUNK存储，如果LOB字段有8193个字节，则会点用2个CHUNK第27页，共31页。LOB- UNDOlobindex 会像其他段一样生成undo，但是lobsegment 不会，ORACLE对LOB字段进行更新时不会重用原有的存储空间，而是先重新分配新的空间，然后调整lobindex的指针到新空间的地址。PCTVERSION percent，默认是10%使用预留百分比的数据块保存旧版本RETENTION保存时间与初始化参数undo_retention相同第28页，共31页。LOB-CACHENOCACHE(默认值)全部直接磁