Oracle数据类型@阿里巴巴.pptx

Oracle数据类型 叶正盛Alibaba inc2010 05 主题 数值型 NUMBER 字符型 STRING 日期型 DATETIME 大字段 LOB 数值型 NUMBER p s p 整数位数 小数位数取值范围 1 38s 小数位数取值范围 47 127Number 6 2 表示总位数为6位 整数最大可以是4位 存储的小数为2位 小数超过2位会自动四舍五入Number 6 2 Number 1 2 949 950DATA LENGTH 22 Number存储结构 实际用1 21个字节存储 具体的长度与数值有关系0用1个字节 0 x80 存储 1用2个字节 0 xC1 0 x02 存储 1用了3个字节 0 x3E 0 x64 0 x66 存储 SQL selectdump 123456789 16 fromdual DUMP 123456789 16 Typ 2Len 6 c5 2 18 2e 44 5a unDumpNumber ifBytes 0 128then 128表示0result 0 elsifBytes 0 128then 大于128表示正数foriin1 vlength 1loopresult result Bytes i 1 power 100 Bytes 0 193 i 1 endloop else 小于128表示负数foriin1 vlength 2loop 负数的最后一个字节总是102 所以可以忽略result result Bytes i 101 power 100 62 Bytes 0 i 1 endloop endif return Result 新数值类型 10g BINARY FLOAT单精度浮点型数据 内部采用4字节存储BINARY DOUBLE双精度浮点型数据 内部采用8字节存储牺牲精度 提高性能 性能测试结果NumberVSBinary Float insertintot c number c float selectrownum 100 rownum 100fromdualconnectbyrownum 1000000 同义词 NUMERIC p s 完全映射至NUMBER p s DECIMAL p s 或DEC p s 完全映射至NUMBER p s INTEGER或INT SMALLINT 完全映射至NUMBER类型 但是小数位精度为0 FLOAT p 映射至NUMBER p 类型 但小数位精度不限制 DOUBLEPRECISION 相当于FLOAT 126 映射至NUMBER类型 REAL 相当于FLOAT 63 映射至NUMBER类型 字符型 CHARVARCHAR2NCHARNVARCHAR2 常见字符集 ASCIIANSI GB2312 BIG5 JIS GBK GB2312 7445字符GBK 21886字符GB18030 27484字符CJK CJKVUNICODE UCS 2 UTF8 UTF16 ISO 8891 XXLatin XX ORACLE字符集 数据库字符集Oracle数据库最早支持的编码方案是US7ASCIIOracle的字符集命名遵循以下命名规则 即 比如 ZHS16GBK表示采用GBK编码格式 16位ZHS16DBCS表示CJK编码格式 16位WE8ISO8859P1表示ISO 8859 1编码格式 8位国家字符集用以存储NCHAR NVARCHAR2 NCLOB等类型数据只能在unicode编码中的AF16UTF16和UTF8中选择 默认值是AF16UTF16 常用字符类型 CHAR n BYTE CHAR 1 2000VARCHAR2 n BYTE CHAR 1 4000NCHAR n 1 2000 默认为1NVARCHAR2 n 1 4000VARCHAR是VARCHAR2的同意词 主要是兼容作用 状态类型 CHAR 1 VARCHAR2 1 NUMBER 1 两者存储的空间和性能在ORACLE完全一样 所以没什么区别 ThomasKate说CHAR NCHAR实际上只是伪装的VARCHAR2 NVARCHAR2 所以个人认为VARCHAR2 1 可以完全代替CHAR 1 使用 Varchar2VSNumber 随机字符串 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 三四二三四二九八 TranslateSelectTranslate 3430387664 1234567890 一二三四五六七八九零 fromdual 日期型 DATE公元前4712年1月1日至公元9999年12月31日TIMESTAMP保存小数秒 小数位数可以指定为0 9 默认为6位 Date内部存储 2010 2 1210 20 30 Typ 12Len 7 120 110 2 12 11 21 31第1字节 世纪 100第2字节 年 100第3字节 月第4字节 天第5字节 小时 1第6字节 分 1第7字节 秒 1 Timestamp内部存储 2010 2 1213 24 52 123456 Typ 180Len 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 DDHH24 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 外部文件存储 9iMaxSize4GBytes10gMaxSize4GBLOCKS LOB存储定义语法 TABLESPACEtablespace ENABLE DISABLE STORAGEINROW storage clause CHUNKinteger PCTVERSIONinteger RETENTION FREEPOOLSinteger CACHE NOCACHE CACHEREADS logging clause LOB 每个LOB字段有两个段LOBINDEX LOGSEGMENT 如果未指定名字 则系统自动命令 如 SYS LOB0000030371C00001 名字里会有表的OBJECT 其中30371就是表的OBJECTlob存储可以指定单独的表空间 lobindex和lobsegment在同一个表空间中 Lobindex Lobsegment LOB STORAGEINROW ENABLE 默认 小于4000字节与表数据存在一起 会在data buffer里保存大于4000字节保存在LOB指定的段中DISABLE所有数据都保存在LOB指定的段中 LOG CHUNKSize 设置LOB段每个单元分配的字节数 要求是数据块大小的整理倍 如果不是数据块大小的整理倍 ORACLE会自动向上取整 缺省为1个数据块大小 最大为32KBCHUNK是每条记录的每个LOB字段存储的最小单位 假设CHUNK 8192 如果LOB字段只有1个字节 也会点用1个CHUNK存储 如果LOB字段有8193个字节 则会点用2个CHUNK LOB UNDO lobindex会像其他段一样生成undo 但是lobsegment不会 ORACLE对LOB字段进行更新时不会重用原有的存储空间 而是先重新分配新的空间 然后调整lobindex的指针到新空间的地址 PCTVERSIONpercent 默认是10 使用预留百分比的数据块保存旧版本RETENTION保存时间与初始化参数undo retention相同 LOB CACHE NOCACHE 默认值 全部直接磁