Oracle 数据类型及存储方式
袁光东 原创 http://klyuan.iteye.com/blog/220760
概述
通过实例,全面而深入的分析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,32,32
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的内部存储是以数据库字符集进行存储的。
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列上有索引,那么索引将不可用了。
此外还会在绑定变量时出现问题。
分享到:
相关推荐
关于oracle数据类型及存储方式基础知识,也许对你有所帮助
RT oracle数据类型及存储方式.doc
Oracle 数据类型及存储方式 数据存储是数据流在加工过程中产生的临时文件或加工过程中需要查找的信息。数据以某种格式记录在计算机内部或外部存储介质上。
[整理版]oracle数据类型及存储方式
oracle的数据类型及存储方式 文档,好资源和大家一起分享!
Oracle基本数据类型存储格式浅析(一)——字符类型 Oracle基本数据类型存储格式浅析(二)——数字类型 Oracle基本数据类型存储格式浅析(三)——日期类型 Oracle基本数据类型存储格式浅析(四)——ROWID类型 ...
让你对oracle数据类型有一个全新的认识。揭示一些不为人知的秘密和被忽略的盲点。从实用和优化的角度出发,讨论每种数据类型的特点。从这里开始oracle之旅! 第一部份 字符类型 §1.1 char 定长字符串,会用空格...
5、DATE数据类型,使用7个字节固定长度,每个字节分别存储世纪,年,月,日 ,时,分,秒,ORACLE中SYSDATE函数的功能是返回当前的日期和时间 6、TIMESTAMP数据类型,和DATE相似,但是这个类型的秒精确到小数点后6...
oracle 的基本数据类型的存储格式有了一些了解,最近有做了一些测试进行了验证。打算整理总结一下,这一 篇主要说明字符类型的存储格式。主要包括char、varchar2 和long 等几种类型。
C#调用Oracle自定义类型存储过程,Oracle存储入参为type类型,对于不熟悉Oracle朋友,可以参考来调用。实现思路和正常调MSSQL一样,先建立连接,再定义一个IOracleCustomType的类,然后Oracle.DataAccess.dll的...
ORACLE中的数据类型.doc 当你在数据库中创建数据表的时候,你需要定义表中所有字段的类型。ORACLE有许多种数据类型以满足你的需要。数据类型大约分为:character, number, date, LOB, 和RAW等类型。虽然ORACLE8i也...
常用的数据库字段类型如下: 字段类型 中文说明 限制条件 其它说明 CHAR 固定长度字符串 ...数据类型 参数 描述 char(n) n=1 to 2000字节 定长字符串,n字节长,如果不指定长度,缺省为1个字节长(一个汉字为2字节)
Oracle 透明数据加密 (TDE) 能够加密存储在表和表空间中的敏感数据,例如手机号码,身份证号等,对于有权访问数据的数据库用户或应用程序,加密数据将被透明地解密。 TDE 可在存储介质或数据文件被盗时保护存储在...
sql server中的image类型的数据导出到oracle的clob字段中
1、 段是表空间中一种逻辑存储结构,以下(D)不是ORACLE数据库使用的段类型。 (A) 索引段 (B)临时段 (C)回滚段 (D)代码段 2、 ORACLE数据库物理结构包括以下三种文件,以下不属于的是(A) (A) 系统...
第13章 Oracle数据类型.ppt 第14章 Oracle中的函数与表达式.ppt 第15章 Oracle中的控制语句.ppt 第16章 SQL查询.ppt 第17章 SQL更新数据.ppt 第18章 数据库速度优化与数据完整性.ppt 第19章 数据一致性与事务管理....
因为.net中的除string类的数据类型外,其它类型要求手动修改UDT自动生成的类文件,本人测试了2天后,才明白要修改UDT自动生成的类文件,此功能特好用,尤其是象财务凭证中子栏的一次更新,要求一次将整张凭证更新到ORACLE,...
大数据量,返回值虽然是CLOB,依然会报错; CLOB变量需要dbms_lob.createtemporary,临时表空间中,建立临时LOB。 大数据量,返回值虽然是CLOB,依然会报错; CLOB变量需要dbms_lob.createtemporary,临时表空间中,...
类型 含义 存储描述 备注 CHAR 固定长度字符串 最大长度2000bytes VARCHAR2 可变长度的字符串, 最大长度4000bytes 可做索引的最大长度749 NCHAR 根据字符集而定的固定长度字符串 最大长度2000bytes ...