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

Oracle 数据类型及存储方式
(oracle小组学习笔记)
袁光东

概述 1
第一部份 字符类型 1
§1.1 char 1
§1.2 varchar2 3
§1.3 byte 和char 4
§1.4 char还是varchar 7
§1.5 NCHAR和NVARCHAR2 8
§1.6 RAW 10
第二部分 数值类型 12
§ 2.1 number 12
§2.2 小数位在哪里？ 14
§2.3 number的精度和小数位 15
§2.4 binary_float 和binary_double 19
§2.5 Oracle在语法上还支持的数值数据类型 21
第三部分 日期时间类型 21
§3.1 DATE 21
§3.2 向Date类型增加或减时间 23
§3.3 TIMESTAMP 26
§3.4 TIMESTAMP WITH TIME ZONE 28
§3.5 TIMESTAMP WITH LOCAL TIME ZONE 29
§3.6 INTERVAL 30
第四部分 LOB类型 32
§ 4.1 LOB类型 32
§ 4.2 BFILE 37
第五部分 LONG类型 37
第六部分 ROWID 38

概述
通过实例，全面而深入的分析oralce的基本数据类型及它们的存储方式。以ORACLE 10G为基础，介绍oralce 10g引入的新的数据类型。让你对oracle数据类型有一个全新的认识。揭示一些不为人知的秘密和被忽略的盲点。从实用和优化的角度出发，讨论每种数据类型的特点。从这里开始oracle之旅！
第一部份 字符类型
§1.1 char
定长字符串，会用空格来填充来达到其最大长度,最长2000个字节。

1． 新建一个测试表test_char.,只有一个char类型的列。长度为10
SQL> create table test_char(colA char(10));
Table created
2． 向这个表中插入一些数据。
SQL> insert into test_char values('a');

1 row inserted

SQL> insert into test_char values('aa');

1 row inserted

SQL> insert into test_char values('aaa');

1 row inserted

SQL> insert into test_char values('aaaa');

1 row inserted

SQL> insert into test_char values('aaaaaaaaaa');
1 row inserted

注意：最多只能插入10个字节。否是就报错。
SQL> insert into test_char values('aaaaaaaaaaa');

insert into test_char values('aaaaaaaaaaa')

ORA-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,32
aa Typ=96 Len=10: 97,97,32,32,32,32,32,32,32,32
aaa Typ=96 Len=10: 97,97,97,32,32,32,32,32,3232
aaaa Typ=96 Len=10: 97,97,97,97,32,32,32,32,32,32
aaaaaaaaaa 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的内部存储是以ascii码进行存储的。
97正好是字符a的ASCII码。
可以使用chr函数把ASCII码转成字符。
SQL> select chr(97) from dual;

CHR(97)
-------
a

要想知道一个字符的ASCII码，可以使用函数ascii
SQL> select ascii('a') from dual;

ASCII('A')
----------
97

32正好是空格的ascii码值。
Char类型是定长类型。它总会以空格来填充以达到一个固定宽度。
使用char类型会浪费存储空间。
Oracle的数据类型的长度单位是字节
SQL> select dump('汉') from dual;

DUMP('汉')
---------------------
Typ=96 Len=2: 186,186

可见一个汉字在oracle中是占用了两个字节的。
英文字母或符号只占用一个字节。
Char(10)最多可存放5个汉字。

§1.2 varchar2
是一种变长的字符类型。最多可占用4000字节的存储空间。
1. 创建一个表,只有一列，类型为varchar2,长度为10
SQL> create table test_varchar( col varchar2(10))

Table created
2. 插入一些数据
SQL> insert into test_varchar values('a');

1 row inserted

SQL> insert into test_varchar values('aa');

1 row inserted

SQL> insert into test_varchar values('aaa');

1 row inserted

SQL> insert into test_varchar values('aaaaaaaaaa');

1 row inserted

SQL> insert into test_varchar values('aaaaaaaaaaa');

2. 用dump函数查看每一行的内部存储结构。
SQL> select col, dump(col) from test_varchar;

COL DUMP(COL)
---------- --------------------------------------------------------------------------------
a Typ=1 Len=1: 97
aa Typ=1 Len=2: 97,97
aaa Typ=1 Len=3: 97,97,97
aaaaaaaaaa Typ=1 Len=10: 97,97,97,97,97,97,97,97,97,97

Typ=1,说明varchar2类型在oracle中的类型编号为1
Len代表了每一行数据所占用的字节数。
后面是具体的存储值。
由此可见，varchar2是存多少就占用多少空间。比较节省空间的。不会像char那样用空格填充。

§1.3 byte 和char
在10g中，字符类型的宽度定义时，可以指定单位。
Byte就是字节。
Char就是字符。
Varchar2(10 byte) 长度为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，即以字节为单位的。
采用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));

Table created

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

1 row inserted

SQL> insert into test_varchar2 values('袁','a');

1 row inserted

SQL> insert into test_varchar2 values('袁袁袁袁袁袁袁袁袁袁','aaaaaaaaaa');

1 row inserted

SQL> 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个中文字符’袁’,在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个汉字时，它的长度为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为单位指定为佳。因为可以避免字段长度的问题。

当不知道当前数据库采用的字符集，一个字符占用多少字节时，可以使用lengthb函数。
SQL> select lengthb('袁') from dual;
LENGTHB('袁')
-------------
2

§1.4 char还是varchar
1. 新建一个表,一列为char类型，一列为varchar2类型
SQL> create table test_char_varchar(char_col char(20),varchar_col varchar2(20));

Table created
2. 向该表中的两列都插入相关的数据
SQL> insert into test_char_varchar values('Hello World','Hello World');

1 row inserted

SQL> select * from test_char_varchar;

CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World

3. 以char_col列为条件查询
SQL> select * from test_char_varchar where char_col ='Hello World';

CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World

4. 以varchar_col列为条件查询
SQL> select * from test_char_varchar where varchar_col ='Hello World';

CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World

5.似乎char 和varchar类型没有什么两样。再看看下面的语句。
SQL> select * from test_char_varchar where varchar_col =char_col;

CHAR_COL VARCHAR_COL
-------------------- --------------------
这已经看出他们并不一样，这涉及到字符串比较的问题。
因为已经发生了隐式转换,在与char列char_col进行比较时，char_col列的内容已经转换成了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 World

SQL> 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时，文本内容采用国家字符集来存储和管理。而不是默认字符集。
这两种类型的长度指的是字符数，而不是字节数。
NLS国家语言支持(National Language Support)
在oracle 9i及以后的版本，数据库的国家字符集可以是：utf-8和AL16UTF-16两种。
Oracle 9i是utf -8, Oralce 10g是AL16UTF-16.

1.新建一个表,有两列，类型分别为：nchar和nvarchar2.长度都为10
SQL> create table test_nvarchar(col_nchar nchar(10),col_nvarchar2 nvarchar2(10));
Table created

2.插入一些数据
SQL> insert into test_nvarchar values('袁','袁光东');
1 row inserted
SQL> 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) from 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,32
a 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,32

Typ=96 与char的类型编码一样。
Len=20 每一行的长度都是20字节。这一点跟char一样。都是定长的，会以空格填充。
需要注意的是：统统以两位来表示一个字符。
136,129 表示’袁’
0,97 表示’a’
0,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,28
b Typ=1 Len=2: 0,98
袁光东 Typ=1 Len=6: 136,129,81,73,78,28

Typ=1 与varchar2一样。
每一行的len值都不样同。不会使用空格进行填充。
每一个字符都占有两个字节两进行存储。
b 存储为： 0, 98
袁 存储为： 136,129

5.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个字符。
在查看数据表结构时，显示该列最大占用的字节数。

需要注意的是：在char和nchar中对汉字的实际存储值是不一样的。因为采用了不同的字符集，就有了不同的字符编码。

SQL> insert into test_varchar values('袁');

1 row inserted
SQL> select col, dump(col) from test_varchar where col='袁';

COL DUMP(COL)
---------- --------------------------------------------
袁 Typ=1 Len=2: 212,172
这时采用的字符集系统默认字符集ZHS16GBK。
这里很容易的把它转换成ascii码。
高位 * 256(2的8次方) + 低位.
212 * 256 + 172 = 54444

SQL> select chr(54444) from dual;

CHR(54444)
----------
袁

而在Nchar 和Nvarchar中，采用的是UTF-8或UTF-16的字符集。

SQL> insert into test_nvarchar values('袁','袁');

1 row inserted

SQL> select col_nvarchar2, dump(col_nvarchar2) from test_nvarchar where col_nvarchar2='袁';

COL_NVARCHAR2 DUMP(COL_NVARCHAR2)
-------------------- --------------------------------------------------------------------------------
袁 Typ=1 Len=2: 136,129

‘袁’存储的值为：136,129
Oracle 10以上对nchar和nvarchar都采用utf-16字符集了。它的好处就是对字符采用固定长度的字节存储(2字节),支持多国字符，在操作效率上会更高。但是它却无法兼容于ascii码。
§1.6 RAW
RAW与CHAR和VARCHAR2相比。RAW属于二进制数据，更可以把它称为二进制串。在对CHAR和VARCHAR2类型进行存储时，会进行字符集转换。而对二进制数据进行存储则不会进行字符集转换。
SQL> create table test_raw (col_chr varchar2(10), col_raw raw(10));

Table created
SQL> insert into test_raw values('aa','aa');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test_raw;

COL_CHR COL_RAW
---------- --------------------
aa AA

SQL> select col_chr,dump(col_chr) from test_raw;

COL_CHR DUMP(COL_CHR)
---------- --------------------------------------------------------------------------------
aa Typ=1 Len=2: 97,97
SQL> select col_raw,dump(col_raw) from test_raw;

COL_RAW DUMP(COL_RAW)
-------------------- ------------------------------------------
AA Typ=23 Len=1: 170

通过上面的分析，虽然我们通过select查询得到的结果，raw列显示为插入的字符。但是我们通过dump函数得知到raw并不是以字符的方式存储。它是把插入的字符认为是16进制的值。
比如本例，我们向raw列插入aa,但是它占用的空间为1个字节。值为170.
170转为16进制正好是aa
向raw列插入数据时会发生一个隐式转换HEXTORAW
从raw列读取数据时会发生一个隐式转换RAWTOHEX

如果向raw列插入值不是有效的十六进制值时，会报错的。
SQL> insert into test_raw values('h','h');

insert into test_raw values('h','h')

ORA-01465: invalid hex number

第二部分 数值类型
§ 2.1 number
Number类型是oralce的数值类型，存储的数值的精度可以达到38位。Number是一种变长类型,长度为0-22字节。取值范围为:10e-130 – 10e 126(不包括)
Number(p,s)
P和s都是可选的。
P指精度(precision)，即总位数。默认情况下精度为38。精度的取值范围为1~38.
S指小数位(scale).小数点右边的位数。小数点位数的合法值为-48~127。小数位的默认值由精度来决定。如果没有指定精度，小数位默认为最大的取值区间.如果指定了精度，没有指定小数位。小数位默认为0(即没有小数位).
精度和小数位不会影响数据如何存储，只会影响允许哪些数值及数值如何舍入。

1.新建一个表
SQL> create table test_number(col_number number(6,2));

Table created
2.插入一些不同的数据
SQL> insert into test_number values(-1);

1 row inserted

SQL> insert into test_number values(0);

1 row inserted

SQL> insert into test_number values(1);

1 row inserted

SQL> insert into test_number values(2);

1 row inserted

SQL> insert into test_number values(11.00);

1 row inserted

SQL> insert into test_number values(11.11);

1 row inserted

SQL> insert into test_number values(1234.12);

1 row inserted

SQL> insert into test_number values(-0.1);

1 row inserted

SQL> insert into test_number values(-11.11);

1 row inserted

SQL> insert into test_number values(-1234.12);

1 row inserted
SQL> commit;

Commit complete

3.查看结果
SQL> select * from test_number;

COL_NUMBER
----------
-1.00
0.00
1.00
2.00
11.00
11.11
1234.12
-0.10
-11.11
-1234.12

10 rows selected

5. 查看存储结构
SQL> select col_number, dump(col_number) from test_number;

COL_NUMBER DUMP(COL_NUMBER)
---------- --------------------------------------------------------------------------------
-1.00 Typ=2 Len=3: 62,100,102
0.00 Typ=2 Len=1: 128
1.00 Typ=2 Len=2: 193,2
2.00 Typ=2 Len=2: 193,3
11.00 Typ=2 Len=2: 193,12
11.11 Typ=2 Len=3: 193,12,12
1234.12 Typ=2 Len=4: 194,13,35,13
-0.10 Typ=2 Len=3: 63,91,102
-11.11 Typ=2 Len=4: 62,90,90,102
-1234.12 Typ=2 Len=5: 61,89,67,89,102

10 rows selected

由此可见：
Number类型的内部编码为：2
根据每一行的len值可以看出，number是一个变长类型。不同的数值占用不同的空间。
如果指定了精度，显示结果与精度相关。
就像我插入语句写为
insert into test_number values(0);
但是显示结果为：0.00
如果数值是负数，在最后一位上填充一个补码102.即表示该数值为负数。
0是一个特殊的值，它在oracle中存储为128.
第一位为标志位。以128为比较。如果数值大于128,则它大于0。如果小于128小于0。
-1的内部存储为：
-1.00 Typ=2 Len=3: 62,100,102
最后一位是102，是一个负数。
第一位小于128，所以小于10.
除了第一位标志位外，其它的都是数值为了。
如果该值是一个正数。每一位的存储值减1为每一位的实际值。
1.0的存储结构为：
1.00 typ=2 Len=2: 193,2
实值上1.00的存储结果与1相同。
第一位193为标志位，大于128，大于0.
第二位为数值为，因为是正数，实际值为存储值减1。2-1 = 1。
如是该值是一个负数，每一位的实际值为101 减去存储的值。
-1.00的存储结构为：
-1.00 Typ=2 Len=3: 62,100,102
最后一位102为补位。
第一位62为标志位，小于128。实际值小于0.
第二位为数值为，因为是负数。实际值为:101 – 100 =1.

§2.2 小数位在哪里？
从上面的存储结果看，对小数存储时，它并没有一个小数的标志位。但是它实际上是由第一位标志位，和数值位(第二位)来决定的。
当存储的数是一个正数,该数值的前几位为：第一位 * power(100 , (标志位 - 193));
当存储的数是一个负数，该数值的前几位为：第一位 * power(100,(62 – 标志位));
11.11的存储结果为：
11.11 Typ=2 Len=3: 193,12,12
第一位数值位为：12 实际数值为11
标志位为：193
12 * power(100, (193- 193);
100的零次方为1.
12 乘1 等于12.
所以这个数的前