oracle数据类型及存储方式.doc

1、Oracle 数据类型及存储方式(oracle 小组学习笔记)袁光东概述 .1第一部份 字符类型 .11.1 char.11.2 varchar2.31.3 byte 和 char.41.4 char 还是 varchar.71.5 NCHAR 和 NVARCHAR2 .81.6 RAW .10第二部分 数值类型 .12 2.1 number .122.2 小数位在哪里？ .142.3 number 的精度和小数位 .152.4 binary_float 和 binary_double.192.5 Oracle 在语法上还支持的数值数据类型 .21第三部分 日期时间类型 .213.1 DATE

2、.213.2 向 Date 类型增加或减时间 .233.3 TIMESTAMP .263.4 TIMESTAMP WITH TIME ZONE.283.5 TIMESTAMP WITH LOCAL TIME ZONE .293.6 INTERVAL.30第四部分 LOB 类型 .32 4.1 LOB 类型 .32 4.2 BFILE.37第五部分 LONG 类型 .37第六部分 ROWID.38概述通过实例，全面而深入的分析 oralce 的基本数据类型及它们的存储方式。以 ORACLE 10G为基础，介绍 oralce 10g 引入的新的数据类型。让你对 oracle 数据类型有一个全新的认

3、识。揭示一些不为人知的秘密和被忽略的盲点。从实用和优化的角度出发，讨论每种数据类型的特点。从这里开始 oracle 之旅！第一部份 字符类型1.1 char定长字符串，会用空格来填充来达到其最大长度,最长 2000 个字节。1 新建一个测试表 test_char.,只有一个 char 类型的列。长度为 10SQL create table test_char(colA char(10);Table created2 向这个表中插入一些数据。SQL insert into test_char values(a);1 row insertedSQL insert into test_char va

4、lues(aa);1 row insertedSQL insert into test_char values(aaa);1 row insertedSQL insert into test_char values(aaaa);1 row insertedSQL insert into test_char values(aaaaaaaaaa);1 row inserted注意：最多只能插入 10 个字节。否是就报错。SQL insert into test_char values(aaaaaaaaaaa);insert into test_char values(aaaaaaaaaaa)ORA

5、-12899: value too large for column “PUB_TEST“.“TEST_CHAR“.“COLA“ (actual: 11, maximum: 10)3 使用 dump 函数可以查看每一行的内部存数结构。SQL select colA, dump(colA) from test_char;COLA DUMP(COLA)- -a Typ=96 Len=10: 97,32,32,32,32,32,32,32,32,32aa Typ=96 Len=10: 97,97,32,32,32,32,32,32,32,32aaa Typ=96 Len=10: 97,97,97,3

6、2,32,32,32,32,32,32aaaa Typ=96 Len=10: 97,97,97,97,32,32,32,32,32,32aaaaaaaaaa Typ=96 Len=10: 97,97,97,97,97,97,97,97,97,97注意：Typ=96 表示数据类型的 ID。Oracle 为每一种数据类型都进行了编号。说明 char 类型的编号是 96.Len =10 表示所在的内部存储的长度(用字节表示) 。虽然第一例只存了一个字符a ，但是它还是占用了 10 个字节的空间。97,32,32,32,32,32,32,32,32,32 表示内部存储方式。可见 oracle 的内部存

7、储是以ascii 码进行存储的。97 正好是字符 a 的 ASCII 码。可以使用 chr 函数把 ASCII 码转成字符。SQL select chr(97) from dual;CHR(97)-a要想知道一个字符的 ASCII 码，可以使用函数 asciiSQL select ascii(a) from dual;ASCII(A)-9732 正好是空格的 ascii 码值。Char 类型是定长类型。它总会以空格来填充以达到一个固定宽度。使用 char 类型会浪费存储空间。Oracle 的数据类型的长度单位是字节。SQL select dump(汉) from dual;DUMP(汉)-Ty

8、p=96 Len=2: 186,186可见一个汉字在 oracle 中是占用了两个字节的。英文字母或符号只占用一个字节。Char(10)最多可存放 5 个汉字。1.2 varchar2 是一种变长的字符类型。最多可占用 4000 字节的存储空间。1. 创建一个表,只有一列，类型为 varchar2,长度为 10SQL create table test_varchar( col varchar2(10);Table created2. 插入一些数据SQL insert into test_varchar values(a);1 row insertedSQL insert into test_

9、varchar values(aa);1 row insertedSQL insert into test_varchar values(aaa);1 row insertedSQL insert into test_varchar values(aaaaaaaaaa);1 row insertedSQL insert into test_varchar values(aaaaaaaaaaa);2. 用 dump 函数查看每一行的内部存储结构。SQL select col, dump(col) from test_varchar;COL DUMP(COL)- -a Typ=1 Len=1: 9

10、7aa Typ=1 Len=2: 97,97aaa Typ=1 Len=3: 97,97,97aaaaaaaaaa Typ=1 Len=10: 97,97,97,97,97,97,97,97,97,97Typ=1,说明 varchar2 类型在 oracle 中的类型编号为 1Len 代表了每一行数据所占用的字节数。后面是具体的存储值。由此可见，varchar2 是存多少就占用多少空间。比较节省空间的。不会像 char 那样用空格填充。1.3 byte 和 char在 10g 中，字符类型的宽度定义时，可以指定单位。Byte 就是字节。Char 就是字符。Varchar2(10 byte) 长

11、度为 10 个字节。Varchar2(10 char) 长度为 10 个字符所占的长度。Char(10 byte)长度为 10 个字节。Char(10 char) 长度为 10 个字符所占的长度。一个字符占用多少个字节，是由当前系统采用的字符集来决定的。如一个汉字占用两个字节。查看当前系统采用的字符集SQL select * from nls_database_parameters where parameter =NLS_CHARACTERSET;PARAMETER VALUE- -NLS_CHARACTERSET ZHS16GBK如果在定义类型时，不指定单位。默认是按 byte，即以字节为

12、单位的。采用 char 为单位的好处是，使用多字节的字符集。比如，在 ZHS16GBK 字符集中，一个汉字占用两个字节。把数据表的某一列长度定义为可存放 10 个汉字，通过下面的定义就可以了。Create table test_varchar(col_char varchar2(10 char);这样相对简单一些。在数据库表设计时需要注意。继续实验，新建一个表，包含两列。一列采用 byte 为单位，一列采用 char 为单位SQL create table test_varchar2 (col_char varchar2(10 char),col_byte varchar2(10 byte);

13、Table createdCol_char 列，定义为可存放 10 个字符。Col_byte 列，定义为可存放 10 个字节的字符。当前的系统采用字符集为 ZHS16GBK.所以一个字符占两个字节。试着在表中插入一些数据SQL insert into test_varchar2 values(a,a);1 row insertedSQL insert into test_varchar2 values(袁,a);1 row insertedSQL insert into test_varchar2 values(袁袁袁袁袁袁袁袁袁袁,aaaaaaaaaa);1 row insertedSQL

14、insert into test_varchar2 values(袁袁袁袁袁袁袁袁袁袁,袁袁袁袁袁袁袁袁袁袁);insert into test_varchar2 values(袁袁袁袁袁袁袁袁袁袁, 袁袁袁袁袁袁袁袁袁袁)ORA-12899: value too large for column “PUB_TEST“.“TEST_VARCHAR2“.“COL_BYTE“ (actual: 20, maximum: 10)第一次， 在两列中都插入字符 a第二次， 在 col_char 列插入字符 袁, 在 col_byte 插入字符 a第三次， 在 col_char 列中插入 10 个中文字符

15、袁, 在 col_byte 插入 10 个字符 a第四次， 在两列中都插入中文字符袁 时，报错了。第二列长度不够。再看看每一行的存储结构SQL select col_char, dump(col_char) from test_varchar2;COL_CHAR DUMP(COL_CHAR)- -a Typ=1 Len=1: 97袁 Typ=1 Len=2: 212,172袁袁袁袁袁袁袁袁袁袁 Typ=1 Len=20: 212,172,212,172,212,172,212,172,212,172,212,172,212,172,212,172,21当我们在 col_char 列插入 10

16、个汉字时，它的长度为 20.尽管我们在定义的时候是采用 varchar2(10,char).由此可见,oracle 是根据当前数据库采用的字符集，每个字符的所占字节数 X 字段长度来决定了该字段所占的字节数。在本例中，varchar2(10,char)相当于 varchar2(20).不信，我们可以试试看。SQL desc test_varchar2;Name Type Nullable Default Comments - - - - - COL_CHAR VARCHAR2(20) Y COL_BYTE VARCHAR2(10) Y 当采用多字节的字符集时，定义字段长度还是采用 char 为

17、单位指定为佳。因为可以避免字段长度的问题。当不知道当前数据库采用的字符集，一个字符占用多少字节时，可以使用 lengthb函数。SQL select lengthb(袁) from dual;LENGTHB(袁)-21.4 char 还是 varchar1. 新建一个表,一列为 char 类型，一列为 varchar2 类型SQL create table test_char_varchar(char_col char(20),varchar_col varchar2(20);Table created2. 向该表中的两列都插入相关的数据SQL insert into test_char_va

18、rchar values(Hello World,Hello World);1 row insertedSQL select * from test_char_varchar;CHAR_COL VARCHAR_COL- -Hello World Hello World3. 以 char_col 列为条件查询SQL select * from test_char_varchar where char_col =Hello World;CHAR_COL VARCHAR_COL- -Hello World Hello World4. 以 varchar_col 列为条件查询SQL select *

19、from test_char_varchar where varchar_col =Hello World;CHAR_COL VARCHAR_COL- -Hello World Hello World5.似乎 char 和 varchar 类型没有什么两样。再看看下面的语句。SQL select * from test_char_varchar where varchar_col =char_col;CHAR_COL VARCHAR_COL- -这已经看出他们并不一样，这涉及到字符串比较的问题。因为已经发生了隐式转换,在与 char 列 char_col 进行比较时，char_col 列的内容

20、已经转换成了 char(20).在 Hello World 后面以空格进行填充了。而 varchar_col 列并没有发生这种转换。如果要让 char_col 列与 varchar_col 列相等。有两种方法。第一种是：使用 trim 把 char_col 列的空格去掉。第二种是：使遥 rpad 把 varchar_col 列用空格进行填充长度为 20 的字符。SQL select * from test_char_varchar where trim(char_col) = varchar_col;CHAR_COL VARCHAR_COL- -Hello World Hello WorldS

21、QL select * from test_char_varchar where char_col = rpad(varchar_col,20);CHAR_COL VARCHAR_COL- -Hello World Hello World如果使用 trim 函数，如果 char_col 列上有索引，那么索引将不可用了。此外还会在绑定变量时出现问题。1.5 NCHAR 和 NVARCHAR2如果系统需要集中管理和存储多种字符集，就需要使用这两种字符类型。在使用NCAHR 和 NVARCHAR2 时，文本内容采用国家字符集来存储和管理。而不是默认字符集。这两种类型的长度指的是字符数，而不是字节数。

22、NLS 国家语言支持(National Language Support)在 oracle 9i 及以后的版本，数据库的国家字符集可以是：utf-8 和 AL16UTF-16 两种。Oracle 9i 是 utf -8, Oralce 10g 是 AL16UTF-16.1.新建一个表,有两列，类型分别为：nchar 和 nvarchar2.长度都为 10SQL create table test_nvarchar(col_nchar nchar(10),col_nvarchar2 nvarchar2(10);Table created2.插入一些数据SQL insert into test_n

23、varchar values(袁,袁光东);1 row insertedSQL insert into test_nvarchar values(N袁,N袁光东);1 row inserted(在 9i 之前的版本，插入时加上 N 时，在处理时跟普通方式有不同的方式。但是在 10g的时候已经有了改变，加不加 N 都是一样,这里只是为了测试)SQL insert into test_nvarchar values(a,b);1 row inserted插入一行英文字母3. 查看每行的 col_nchar 列的存储方式。SQL select col_nchar, dump(col_nchar) f

24、rom test_nvarchar;COL_NCHAR DUMP(COL_NCHAR)- -袁 Typ=96 Len=20: 136,129,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32a Typ=96 Len=20: 0,97,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32袁 Typ=96 Len=20: 136,129,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32Typ=96 与 char 的类型编码一样。Len=20 每一行的长度都是 20 字节。这一点跟 c

25、har 一样。都是定长的，会以空格填充。需要注意的是：统统以两位来表示一个字符。136,129 表示袁0,97 表示a0,32 表示空格。4. nvarchar2 的储存SQL select col_nvarchar2, dump(col_nvarchar2) from test_nvarchar;COL_NVARCHAR2 DUMP(COL_NVARCHAR2)- -袁光东 Typ=1 Len=6: 136,129,81,73,78,28b Typ=1 Len=2: 0,98袁光东 Typ=1 Len=6: 136,129,81,73,78,28Typ=1 与 varchar2 一样。每一行

26、的 len 值都不样同。不会使用空格进行填充。每一个字符都占有两个字节两进行存储。b 存储为： 0, 98袁 存储为： 136,1295.nchar 和 nvarchar2 的数据定义。SQL desc test_nvarchar;Name Type Nullable Default Comments - - - - - COL_NCHAR NCHAR(20) Y COL_NVARCHAR2 NVARCHAR2(20) Y 虽然在定义 nchar 和 nvarchar2 时，指定的长度是指字符数。但是表结构的定义中，仍然是存储着它的字节数。在定义时 nchar(10)表示可以最大存储 10 个

27、字符。在查看数据表结构时，显示该列最大占用的字节数。需要注意的是：在 char 和 nchar 中对汉字的实际存储值是不一样的。因为采用了不同的字符集，就有了不同的字符编码。SQL insert into test_varchar values(袁);1 row insertedSQL select col, dump(col) from test_varchar where col=袁;COL DUMP(COL)- -袁 Typ=1 Len=2: 212,172这时采用的字符集系统默认字符集 ZHS16GBK。这里很容易的把它转换成 ascii 码。高位 * 256(2 的 8 次方) +

28、低位.212 * 256 + 172 = 54444SQL select chr(54444) from dual;CHR(54444)-袁而在 Nchar 和 Nvarchar 中，采用的是 UTF-8 或 UTF-16 的字符集。SQL insert into test_nvarchar values(袁,袁);1 row insertedSQL select col_nvarchar2, dump(col_nvarchar2) from test_nvarchar where col_nvarchar2=袁;COL_NVARCHAR2 DUMP(COL_NVARCHAR2)- -袁 Typ=1 Len=2: 136,129袁存储的值为：136,129Oracle 10 以上对 nchar 和 nvarchar 都采用 utf-16 字符集了。它的好处就是对字符采用固定长度的字节存储(2 字节),支持多国字符，在操作效率上会更高。但是它却无法兼容于ascii 码。