Oracle数据类型@阿里巴巴

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2010-5-25,#,Oracle,数据类型,叶正盛,Alibaba-inc,2010-05,http:/ 取值范围：,1-38,s,:,小数位数,取值范围：,-47-127,Number(6,2),表示总位数为,6,位，整数最大可以是,4,位，存储的小数为,2,位，小数超过,2,位会自动四舍五入,Number(6,-2)?,Number(1,-2)?949,950,DATA_LENGTH=22?,Number,存储结构,实际用,1-21,个字节存储，具体的长度与数值有关系,0,用,1,个字节,(0 x80),存储，,1,用,2,个字节,(0 xC1,0 x02),存储，,-1,用了,3,个字节,(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,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)-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);,新数值类型,(10g),BINARY_FLOAT,单精度浮点型数据，内部采用,4,字节存储,BINARY_DOUBLE,双精度浮点型数据，内部采用,8,字节存储,牺牲精度，提高性能,性能测试结果,Number VS Binary_Float,insert,into t(c_number,c_float)select rownum/100,rownum/100 from dual connect by rownum1000000,运算,(999999 rows),(0.01-9999.99),NUMBER,BINARY_FLOAT,NUMBER CAST FLOAT,比值,NUMBER/,FLOAT,比值,NUMBER/,CAST FLOAT,SUM(A),188ms,170ms,1563ms,1.10,0.12,SUM(A*4.52),250ms,218ms,1672ms,1.14,0.15,LN(A),24141ms,469ms,1906ms,51.47,12.67,POWER(A,4.5),47952ms,594ms,2094ms,80.73,22.90,SIN(A),15141ms,312ms,1781ms,48.53,8.5,同义词,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,：相当于,FLOAT(63),映射至,NUMBER,类型。,字符型,CHAR,VARCHAR2,NCHAR,NVARCHAR2,常见字符集,ASCII,ANSI(GB2312,BIG5,JIS,GBK),GB2312-7445,字符,GBK-21886,字符,GB18030-27484,字符,CJK,CJKV,UNICODE(UCS-2,UTF8,UTF16),ISO-8891-XX,Latin-XX,ORACLE,字符集,数据库字符集,Oracle,数据库最早支持的编码方案是,US7ASCII,Oracle,的字符集命名遵循以下命名规则,:,即,:,比如,:,ZHS16GBK,表示采用,GBK,编码格式,16,位,ZHS16DBCS,表示,CJK,编码格式,16,位,WE8ISO8859P1,表示,ISO-8859-1,编码格式,8,位,国家字符集,用以存储,NCHAR,NVARCHAR2,NCLOB,等类型数据,只能在,unicode,编码中的,AF16UTF16,和,UTF8,中选择，默认值是,AF16UTF16,常用字符类型,CHAR(n BYTE|,CHAR,)1-2000,VARCHAR2(n BYTE|,CHAR,)1-4000,NCHAR(n)1-2000,，默认为,1,NVARCHAR2(n)1-4000,VARCHAR,是,VARCHAR2,的同意词，主要是兼容作用,状态类型,CHAR(1),VARCHAR2(1),NUMBER(1),两者存储的空间和性能在,ORACLE,完全一样，所以没什么区别，,Thomas Kate,说,CHAR/NCHAR,实际上只是伪装的,VARCHAR2/NVARCHAR2,，所以个人认为,VARCHAR2(1),可以完全代替,CHAR(1),使用,Varchar2 VS Number,Values,NUMBER,（,AVG_DATA_LENGTH,）,VARCHAR2,（,AVG_DATA_LENGTH,）,1-10,2,1,1-100,2,2,1-1000,3,3,(,万,)1-10000,3,4,1-100000,4,5,1-1000000,4,6,1-10000000,5,7,(,亿,)1-100000000,5,8,随机字符串,dbms_random.string,函数,以下是,dbms_random.string,函数生成随机的字符型数据类型,-u,U-,返回全是大写的字符串,-l,L-,返回全是小写的字符串,-a,A-,返回大小写结合的字符串,-x,X-,返回全是大写和数字的字符串,-p,P-,返回键盘上出现字符的随机组合,字符串函数,SUBSTR(abcdeft,-3,2),TRIM(001234560,01),如何将一串数字转换为对应的汉字？,34234298 to ,三四二三四二九八,Translate,Select Translate(3430387664,1234567890,一二三四五六七八九零,)from dual;,日期型,DATE,公元前,4712,年,1,月,1,日至公元,9999,年,12,月,31,日,TIMESTAMP,保存小数秒，小数位数可以指定为,0-9,，默认为,6,位,Date,内部存储,2010-2-12 10:20:30,Typ=12 Len=7:120,110,2,12,11,21,31,第,1,字节：世纪,+100,第,2,字节：年,+100,第,3,字节：月,第,4,字节：天,第,5,字节：小时,+1,第,6,字节：分,+1,第,7,字节：秒,+1,Timestamp,内部存储,2010-2-12 13:24:52.123456,Typ=180 Len=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,个字节存储，内部运算类型为整形,TO_CHAR,日期转换,YYYY-MM-DD HH24:MI:SS,如何得到今天是本年的第几天？,TO_CHAR(date,DDD),TO_CHAR(date,MM),EXTRACT(date,MONTH),求下个月的今天时间？,Sysdate+30?,Sysdate+31?,Sysdate+NUMTOYMINTERVAL(1,month),ADD_MONTHS(Sysdate,1),ADD_MONTHS,ADD_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),LOB,CLOB(,字符,),BLOB(,二进制,),NCLOB(,国家字符集,),BFILE(,外部文件存储,),9i MaxSize 4G Bytes,10g MaxSize 4G BLOCKS,LOB,存储定义语法,TABLESPACE tablespace,|ENABLE|DISABLE STORAGE IN ROW,|storage_clause,|CHUNK integer,|PCTVERSION integer,|RETENTION,|FREEPOOLS integer,|CACHE,|NOCACHE|CACHE READS logging_clause,LOB,每个,LOB,字段有两个段,LOBINDEX,LOGSEGMENT,如果未指定名字，则系统自动命令，如：,SYS_LOB0000030371C00001$,名字里会有表的,OBJECT#,，其中,30371,就是表的,OBJECT,lob,存储可以指定单独的表空间，,lobindex,和,lobsegment,在同一个表空间中。,Lobindex&Lobsegment,LOB-STORAGE IN ROW,ENABLE(,默认,),小于,4000,字节与表数据存在一起，会在,data_buffer,里保存,大于,4000,字节保存在,LOB,指定的段中,DISABLE,所有数据都保存在,LOB,指定的段中,LOG-CHUNK Size,设置,LOB,段每个单元分配的字节数，要求是数据块大小的整理倍，如果不是数据块大小的整理倍，,ORACLE,会自动向上取整，缺省为,1,个数据块大小，最大为,32KB,CHUNK,是每条记录的每个,LOB,字段存储的最小单位，假设,CHUNK=8192,，如果,LOB,字段只有,1,个字节，也会点用,1,个,CHUNK,存储，如果,LOB,字段有,8193,个字节，则会点用,2,个,CHUNK,LOB-UNDO,lobindex,会像其他段一样生成,undo,，但是,lobsegment,不会，,ORACLE,对,LOB,字段进行更新时不会重用原有的存储空间，而是先重新分配新的空间，然后调整,lobindex,的指针到新空间的地址。,PCTVERSION percent,，默认是,10%,使用预留百分比的数据块保存旧版本,RETENTION,保存时间与初始化参数,undo_retention,相同,LOB-CACHE,NOCACHE(,默认值,),全部直接磁盘读写,CACHE,读写都通过缓存,CACHE READ,读的数据会缓存到内存,BFILE,create or replace directory MY_DIR as e:/download;,create table t(c_bfile bfile);,insert into t values(bfilename(MY_DIR,Version.xml);,BFILE,不是,读一致性,BFILE,在数据库中占用的空间是文件名长度,谢谢！,