Oracle存储过程-1

Oracle数据类型及存储方式概述通过实例，全面而深入的分析oralce的基本数据类型及它们的存储方式。以ORACLE10G为基础，介绍oralce10g引入的新的数据类型。让你对oracle数据类型有一个全新的认识。揭示一些不为人知的秘密和被忽略的盲点。从实用和优化的角度出发，讨论每种数据类型的特点。从这里开始oracle之旅！第一部份字符类型§1.1char定长字符串，会用空格来填充来达到其最大长度,最长2000个字节。1．新建一个测试表test_char.,只有一个char类型的列。长度为10SQLcreatetabletest_char(colAchar(10));Tablecreated2．向这个表中插入一些数据。SQLinsertintotest_charvalues('a');1rowinsertedSQLinsertintotest_charvalues('aa');1rowinsertedSQLinsertintotest_charvalues('aaa');1rowinsertedSQLinsertintotest_charvalues('aaaa');1rowinsertedSQLinsertintotest_charvalues('aaaaaaaaaa');1rowinserted注意：最多只能插入10个字节。否是就报错。SQLinsertintotest_charvalues('aaaaaaaaaaa');insertintotest_charvalues('aaaaaaaaaaa')ORA-12899:valuetoolargeforcolumnPUB_TEST.TEST_CHAR.COLA(actual:11,maximum:10)3．使用dump函数可以查看每一行的内部存数结构。SQLselectcolA,dump(colA)fromtest_char;COLADUMP(COLA)------------------------------------------------------------------------------------------aTyp=96Len=10:97,32,32,32,32,32,32,32,32,32aaTyp=96Len=10:97,97,32,32,32,32,32,32,32,32aaaTyp=96Len=10:97,97,97,32,32,32,32,32,32,32aaaaTyp=96Len=10:97,97,97,97,32,32,32,32,32,32aaaaaaaaaaTyp=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码转成字符。SQLselectchr(97)fromdual;CHR(97)-------a要想知道一个字符的ASCII码，可以使用函数asciiSQLselectascii('a')fromdual;ASCII('A')----------9732正好是空格的ascii码值。Char类型是定长类型。它总会以空格来填充以达到一个固定宽度。使用char类型会浪费存储空间。Oracle的数据类型的长度单位是字节。SQLselectdump('汉')fromdual;DUMP('汉')---------------------Typ=96Len=2:186,186可见一个汉字在oracle中是占用了两个字节的。英文字母或符号只占用一个字节。Char(10)最多可存放5个汉字。§1.2varchar2是一种变长的字符类型。最多可占用4000字节的存储空间。1.创建一个表,只有一列，类型为varchar2,长度为10SQLcreatetabletest_varchar(colvarchar2(10));Tablecreated2.插入一些数据SQLinsertintotest_varcharvalues('a');1rowinsertedSQLinsertintotest_varcharvalues('aa');1rowinsertedSQLinsertintotest_varcharvalues('aaa');1rowinsertedSQLinsertintotest_varcharvalues('aaaaaaaaaa');1rowinsertedSQLinsertintotest_varcharvalues('aaaaaaaaaaa');2.用dump函数查看每一行的内部存储结构。SQLselectcol,dump(col)fromtest_varchar;COLDUMP(COL)------------------------------------------------------------------------------------------aTyp=1Len=1:97aaTyp=1Len=2:97,97aaaTyp=1Len=3:97,97,97aaaaaaaaaaTyp=1Len=10:97,97,97,97,97,97,97,97,97,97Typ=1,说明varchar2类型在oracle中的类型编号为1Len代表了每一行数据所占用的字节数。后面是具体的存储值。由此可见，varchar2是存多少就占用多少空间。比较节省空间的。不会像char那样用空格填充。§1.3byte和char在10g中，字符类型的宽度定义时，可以指定单位。Byte就是字节。Char就是字符。Varchar2(10byte)长度为10个字节。Varchar2(10char)长度为10个字符所占的长度。Char(10byte)长度为10个字节。Char(10char)长度为10个字符所占的长度。一个字符占用多少个字节，是由当前系统采用的字符集来决定的。如一个汉字占用两个字节。查看当前系统采用的字符集SQLselect*fromnls_database_parameterswhereparameter='NLS_CHARACTERSET';PARAMETERVALUE--------------------------------------------------------------------------------------------------------------NLS_CHARACTERSETZHS16GBK如果在定义类型时，不指定单位。默认是按byte，即以字节为单位的。采用char为单位的好处是，使用多字节的字符集。比如，在ZHS16GBK字符集中，一个汉字占用两个字节。把数据表的某一列长度定义为可存放10个汉字，通过下面的定义就可以了。Createtabletest_varchar(col_charvarchar2(10char));这样相对简单一些。在数据库表设计时需要注意。继续实验，新建一个表，包含两列。一列采用byte为单位，一列采用char为单位SQLcreatetabletest_varchar2(col_charvarchar2(10char),col_bytevarchar2(10byte));TablecreatedCol_char列，定义为可存放10个字符。Col_byte列，定义为可存放10个字节的字符。当前的系统采用字符集为ZHS16GBK.所以一个字符占两个字节。试着在表中插入一些数据SQLinsertintotest_varchar2values('a','a');1rowinsertedSQLinsertintotest_varchar2values('袁','a');1rowinsertedSQLinsertintotest_varchar2values('袁袁袁袁袁袁袁袁袁袁','aaaaaaaaaa');1rowinsertedSQLinsertintotest_varchar2values('袁袁袁袁袁袁袁袁袁袁','袁袁袁袁袁袁袁袁袁袁');insertintotest_varchar2values('袁袁袁袁袁袁袁袁袁袁','袁袁袁袁袁袁袁袁袁袁')ORA-12899:valuetoolargeforcolumnPUB_TEST.TEST_VARCHAR2.COL_BYTE(actual:20,maximum:10)第一次，在两列中都插入字符a第二次，在col_char列插入字符’袁’,在col_byte插入字符a第三次，在col_char列中插入10个中文字符’袁’,在col_byte插入10个字符a第四次，在两列中都插入中文字符’袁’时，报错了。第二列长度不够。再看看每一行的存储结构SQLselectcol_char,dump(col_char)fromtest_varchar2;COL_CHARDUMP(COL_CHAR)----------------------------------------------------------------------------------------------------aTyp=1Len=1:97袁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).不信，我们可以试试看。SQLdesctest_varchar2;NameTypeNullableDefaultComments-------------------------------------------COL_CHARVARCHAR2(20)YCOL_BYTEVARCHAR2(10)Y当采用多字节的字符集时，定义字段长度还是采用char为单位指定为佳。因为可以避免字段长度的问题。当不知道当前数据库采用的字符集，一个字符占用多少字节时，可以使用lengthb函数。SQLselectlengthb('袁')fromdual;LENGTHB('袁')-------------2§1.4char还是varchar1.新建一个表,一列为char类型，一列为varchar2类型SQLcreatetabletest_char_varchar(char_colchar(20),varchar_colvarchar2(20));Tablecreated2.向该表中的两列都插入相关的数据SQLinsertintotest_char_varcharvalues('HelloWorld','HelloWorld');1rowinsertedSQLselect*fromtest_char_varchar;CHAR_COLVARCHAR_COL----------------------------------------HelloWorldHelloWorld3.以char_col列为条件查询SQLselect*fromtest_char_varcharwherechar_col='HelloWorld';