Oracle 数据类型及存储方式.docx

资源描述

Oracle 数据类型及存储方式.docx

《Oracle 数据类型及存储方式.docx》由会员分享，可在线阅读，更多相关《Oracle 数据类型及存储方式.docx（18页珍藏版）》请在冰豆网上搜索。

Oracle 数据类型及存储方式.docx

Oracle数据类型及存储方式

概述

通过实例，全面而深入的分析oralce的基本数据类型及它们的存储方式。

以ORACLE10G为基础，介绍oralce10g引入的新的数据类型。

让你对oracle数据类型有一个全新的认识。

揭示一些不为人知的秘密和被忽略的盲点。

从实用和优化的角度出发，讨论每种数据类型的特点。

从这里开始oracle之旅！

第一部份字符类型

§1.1 char

定长字符串，会用空格来填充来达到其最大长度,最长2000个字节。

1．新建一个测试表test_char.,只有一个char类型的列。

长度为10

SQL>createtabletest_char（colAchar（10））;

Tablecreated

2．向这个表中插入一些数据。

SQL>insertintotest_charvalues（'a'）;

1rowinserted

SQL>insertintotest_charvalues（'aa'）;

1rowinserted

SQL>insertintotest_charvalues（'aaa'）;

1rowinserted

SQL>insertintotest_charvalues（'aaaa'）;

1rowinserted

SQL>insertintotest_charvalues（'aaaaaaaaaa'）;

1rowinserted

注意：

最多只能插入10个字节。

否是就报错。

SQL>insertintotest_charvalues（'aaaaaaaaaaa'）;

insertintotest_charvalues（'aaaaaaaaaaa'）

ORA-12899:

valuetoolargeforcolumn"PUB_TEST"."TEST_CHAR"."COLA"（actual:

11,maximum:

10）

3．使用dump函数可以查看每一行的内部存数结构。

SQL>selectcolA,dump（colA）fromtest_char;

COLA DUMP（COLA）

------------------------------------------------------------------------------------------

a Typ=96Len=10:

97,32,32,32,32,32,32,32,32,32

aa Typ=96Len=10:

97,97,32,32,32,32,32,32,32,32

aaa Typ=96Len=10:

97,97,97,32,32,32,32,32,32,32

aaaa Typ=96Len=10:

97,97,97,97,32,32,32,32,32,32

aaaaaaaaaaTyp=96Len=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的内部存储是以数据库字符集进行存储的。

97正好是字符a的ASCII码。

可以使用chr函数把ASCII码转成字符。

SQL>selectchr（97）fromdual;

CHR（97）

-------

要想知道一个字符的ASCII码，可以使用函数ascii

SQL>selectascii（'a'）fromdual;

ASCII（'A'）

----------

32正好是空格的ascii码值。

Char类型是定长类型。

它总会以空格来填充以达到一个固定宽度。

使用char类型会浪费存储空间。

Oracle的数据类型的长度单位是字节。

SQL>selectdump（'汉'）fromdual;

DUMP（'汉'）

---------------------

Typ=96Len=2:

186,186

可见一个汉字在oracle中是占用了两个字节的。

英文字母或符号只占用一个字节。

Char（10）最多可存放5个汉字。

§1.2 varchar2

是一种变长的字符类型。

最多可占用4000字节的存储空间。

1.创建一个表,只有一列，类型为varchar2,长度为10

SQL>createtabletest_varchar（colvarchar2（10））;

Tablecreated

2.插入一些数据

SQL>insertintotest_varcharvalues（'a'）;

1rowinserted

SQL>insertintotest_varcharvalues（'aa'）;

1rowinserted

SQL>insertintotest_varcharvalues（'aaa'）;

1rowinserted

SQL>insertintotest_varcharvalues（'aaaaaaaaaa'）;

1rowinserted

SQL>insertintotest_varcharvalues（'aaaaaaaaaaa'）;

2.用dump函数查看每一行的内部存储结构。

SQL>selectcol,dump（col）fromtest_varchar;

COL DUMP（COL）

------------------------------------------------------------------------------------------

a Typ=1Len=1:

aa Typ=1Len=2:

97,97

aaa Typ=1Len=3:

97,97,97

aaaaaaaaaaTyp=1Len=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（10byte）长度为10个字节。

Varchar2（10char）长度为10个字符所占的长度。

Char（10byte）长度为10个字节。

Char（10char）长度为10个字符所占的长度。

一个字符占用多少个字节，是由当前系统采用的字符集来决定的。

如一个汉字占用两个字节。

查看当前系统采用的字符集

SQL>select*fromnls_database_parameterswhereparameter='NLS_CHARACTERSET';

PARAMETER VALUE

--------------------------------------------------------------------------------------------------------------

NLS_CHARACTERSET ZHS16GBK

如果在定义类型时，不指定单位。

默认是按byte，即以字节为单位的。

采用char为单位的好处是，使用多字节的字符集。

比如，在ZHS16GBK字符集中，一个汉字占用两个字节。

把数据表的某一列长度定义为可存放10个汉字，通过下面的定义就可以了。

Createtabletest_varchar（col_char varchar2（10char））;

这样相对简单一些。

在数据库表设计时需要注意。

继续实验，新建一个表，包含两列。

一列采用byte为单位，一列采用char为单位

SQL>createtabletest_varchar2（col_charvarchar2（10char）,col_bytevarchar2（10byte））;

Tablecreated

Col_char列，定义为可存放10个字符。

Col_byte列，定义为可存放10个字节的字符。

当前的系统采用字符集为ZHS16GBK.所以一个字符占两个字节。

试着在表中插入一些数据

SQL>insertintotest_varchar2values（'a','a'）;

1rowinserted

SQL>insertintotest_varchar2values（'袁','a'）;

1rowinserted

SQL>insertintotest_varchar2values（'袁袁袁袁袁袁袁袁袁袁','aaaaaaaaaa'）;

1rowinserted

SQL>insertintotest_varchar2values（'袁袁袁袁袁袁袁袁袁袁','袁袁袁袁袁袁袁袁袁袁'）;

insertintotest_varchar2values（'袁袁袁袁袁袁袁袁袁袁','袁袁袁袁袁袁袁袁袁袁'）

ORA-12899:

valuetoolargeforcolumn"PUB_TEST"."TEST_VARCHAR2"."COL_BYTE"（actual:

20,maximum:

10）

第一次，在两列中都插入字符a

第二次，在col_char列插入字符’袁’,在col_byte插入字符a

第三次，在col_char列中插入10个中文字符’袁’,在col_byte插入10个字符a

第四次，在两列中都插入中文字符’袁’时，报错了。

第二列长度不够。

再看看每一行的存储结构

SQL>selectcol_char,dump（col_char）fromtest_varchar2;

COL_CHAR DUMP（COL_CHAR）

----------------------------------------------------------------------------------------------------

a Typ=1Len=1:

袁 Typ=1Len=2:

212,172

袁袁袁袁袁袁袁袁袁袁Typ=1Len=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>desctest_varchar2;

Name Type NullableDefaultComments

-------------------------------------------

COL_CHARVARCHAR2（20）Y

COL_BYTEVARCHAR2（10）Y

当采用多字节的字符集时，定义字段长度还是采用char为单位指定为佳。

因为可以避免字段长度的问题。

当不知道当前数据库采用的字符集，一个字符占用多少字节时，可以使用lengthb函数。

SQL>selectlengthb（'袁'）fromdual;

LENGTHB（'袁'）

-------------

§1.4 char还是varchar

1.新建一个表,一列为char类型，一列为varchar2类型

SQL>createtabletest_char_varchar（char_colchar（20）,varchar_colvarchar2（20））;

Tablecreated

2.向该表中的两列都插入相关的数据

SQL>insertintotest_char_varcharvalues（'HelloWorld','HelloWorld'）;

1rowinserted

SQL>select*fromtest_char_varchar;

CHAR_COL VARCHAR_COL

----------------------------------------

HelloWorld HelloWorld

3.以char_col列为条件查询

SQL>select*fromtest_char_varcharwherechar_col='HelloWorld';

CHAR_COL VARCHAR_COL

----------------------------------------

HelloWorld HelloWorld

4.以varchar_col列为条件查询

SQL>select*fromtest_char_varcharwherevarchar_col='HelloWorld';

CHAR_COL VARCHAR_COL

----------------------------------------

HelloWorld HelloWorld

5.似乎char和varchar类型没有什么两样。

再看看下面的语句。

SQL>select*fromtest_char_varcharwherevarchar_col=char_col;

CHAR_COL VARCHAR_COL

----------------------------------------

这已经看出他们并不一样，这涉及到字符串比较的问题。

因为已经发生了隐式转换,在与char列char_col进行比较时，char_col列的内容已经转换成了char（20）.在HelloWorld后面以空格进行填充了。

而varchar_col列并没有发生这种转换。

如果要让char_col列与varchar_col列相等。

有两种方法。

第一种是：

使用trim把char_col列的空格去掉。

第二种是：

使遥rpad把varchar_col列用空格进行填充长度为20的字符。

SQL>select*fromtest_char_varcharwheretrim（char_col）=varchar_col;

CHAR_COL VARCHAR_COL

----------------------------------------

HelloWorld HelloWorld

SQL>select*fromtest_char_varcharwherechar_col=rpad（varchar_col,20）;

CHAR_COL VARCHAR_COL

----------------------------------------

HelloWorld HelloWorld

如果使用trim函数，如果char_col列上有索引，那么索引将不可用了。

此外还会在绑定变量时出现问题。

§1.5 NCHAR和NVARCHAR2

如果系统需要集中管理和存储多种字符集，就需要使用这两种字符类型。

在使用NCAHR和NVARCHAR2时，文本内容采用国家字符集来存储和管理。

而不是默认字符集。

这两种类型的长度指的是字符数，而不是字节数。

NLS国家语言支持（NationalLanguageSupport）

在oracle9i及以后的版本，数据库的国家字符集可以是：

utf-8和AL16UTF-16两种。

Oracle9i是utf-8,Oralce10g是AL16UTF-16.

1.新建一个表,有两列，类型分别为：

nchar和nvarchar2.长度都为10

SQL>createtabletest_nvarchar（col_ncharnchar（10）,col_nvarchar2nvarchar2（10））;

Tablecreated

2.插入一些数据

SQL>insertintotest_nvarcharvalues（'袁','袁光东'）;

1rowinserted

SQL>insertintotest_nvarcharvalues（N'袁',N'袁光东'）;

1rowinserted

（在9i之前的版本，插入时加上N时，在处理时跟普通方式有不同的方式。

但是在10g的时候已经有了改变，加不加N都是一样,这里只是为了测试）

SQL>insertintotest_nvarcharvalues（'a','b'）;

1rowinserted

插入一行英文字母

3.查看每行的col_nchar列的存储方式。

SQL>selectcol_nchar,dump（col_nchar）fromtest_nvarchar;

COL_NCHAR DUMP（COL_NCHAR）

----------------------------------------------------------------------------------------------------

袁 Typ=96Len=20:

136,129,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32

a Typ=96Len=20:

0,97,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32

袁 Typ=96Len=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>selectcol_nvarchar2,dump（col_nvarchar2）fromtest_nvarchar;

COL_NVARCHAR2 DUMP（COL_NVARCHAR2）

----------------------------------------------------------------------------------------------------

袁光东 Typ=1Len=6:

136,129,81,73,78,28

b Typ=1Len=2:

0,98

袁光东 Typ=1Len=6:

136,129,81,73,78,28

Typ=1与varchar2一样。

每一行的len值都不样同。

不会使用空格进行填充。

每一个字符都占有两个字节两进行存储。

b存储为：

0,98

袁存储为：

136,129

5.nchar和nvarchar2的数据定义。

SQL>desctest_nvarchar;

Name Type NullableDefaultComments

-------------------------------------------------

COL_NCHAR NCHAR（20） Y

COL_NVARCHAR2NVARCHAR2（20）Y

虽然在定义nchar和nvarchar2时，指定的长度是指字符数。

但是表结构的定义中，仍然是存储着它的字节数。

在定义时nchar（10）表示可以最大存储10个字符。

在查看数据表结构时，显示该列最大占用的字节数。

需要注意的是：

在char和nchar中对汉字的实际存储值是不一样的。

因为采用了不同的字符集，就有了不同的字符编码。

SQL>insertintotest_varcharvalues（'袁'）;

1rowinserted

SQL>selectcol,dump（col）fromtest_varcharwherecol='袁';

COL DUMP（COL）

------------------------------------------------------------------------------------------

袁 Typ=1Len=2:

212,172

这时采用的字符集系统默认字符集ZHS16GBK。

这里很容易的把它转换成ascii码。

高位*256（2的8次方）+低位.

212*256+172=54444

SQL>selectchr（54444）fromdual;

CHR（54444）

----------

袁

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

SQL>insertinto

展开阅读全文