ORACLE数据库数据类型
Oracle的数据类型
Oracle的数据类型分为标量（Scalar）类型、复合（Composite）类型、引用（Reference）类型和LOB（Large Object）类型4种类型。
因为标量类型没有内部组件，所以，它又分为四类：数值、字符、布尔和日期/时间。
复合类型也叫组合类型，它包含了能够被单独操作的内部组件，每个组件都可以单独存放值，所以，一个复合变量可以存放多个值。因为复合变量类型不是数据库中已经存在的数据类型，所以，复合变量在声明类型之前，首先要创建复合类型，复合类型创建后可以多次使用，以便定义多个复合变量。复合变量像标量变量一样也有数据类型，复合数据类型有记录（RECORD）、表（TABLE）、嵌套表（Nested TABLE）和数组（VARRAY）四种类型，其中，表、嵌套表和数组也称为集合，而集合类型（表、嵌套表和数组）在使用时必须先使用TYPE进行定义方可使用。记录是由一组相关但又不同的数据类型组成的逻辑单元。表是数据的集合，可将表中的数据作为一个整体进行引用和处理。嵌套表是表中之表。一个嵌套表是某些行的集合，它在主表中表示为其中的一列。对主表中的每一条记录，嵌套表可以包含多个行。在某种意义上，它是在一个表中存储一对多关系的一种方法。可变数组（VARRAY）存储固定数量的元素（在运行中，可以改变元素数量），使用顺序数字作下标，可以定义等价的SQL类型，可以存储在数据库中。可以用SQL进行存储和检索，但比嵌套表缺乏灵活性。
引用类型类似于指针，能够引用一个值。
LOB（Large Object）类型的值就是一个LOB定位器，能够指示出大对象的存储位置。目前Oracle支持的LOB类型具体包括四个子类型（Subtype），分别为CLOB、BLOB、NLOB和BFILE。其中，CLOB、BLOB和NLOB都是将数据保存在数据库内部，所以称为内部LOB，而BFILE类型保存的核心是文件指针，真正的文件是保存在数据库外，所以称为外部LOB。
如果处理单行单列的数据那么可以使用标量变量；如果处理单行多列数据那么可以使用PL/SQL记录；如果处理单列多行数据那么可以使用PL/SQL集合。
BOOLEAN数据类型用于定义布尔型（逻辑型）变量，其值只能为TRUE（真）、FALSE（假）或NULL（空）。需要注意的是，该数据类型是PL/SQL数据类型，不能应用于表列。
下图是在PL/SQL中可以使用的预定义类型。
图 3-1 PL/SQL中可以使用的预定义类型
数据类型的作用在于指明存储数值时需要占据的内存空间大小和进行运算的依据。Oracle的字段数据类型如下表所示：
表中 Oracle的字段数据类型
数据类型
描述
VARCHAR2(size)
可变长字符数据。VARCHAR2(n)数据类型用于定义可变长度的字符串，其中，n用于指定字符串的最大长度，n的值必须是正整数且不超过32767。
CHAR(size)
定长字符数据。CHAR(n)数据类型用于定义固定长度的字符串，其中，n用于指定字符串的最大长度，n的值必须是正整数且不超过32767。
NUMBER(p,s)
可变长数值数据。NUMBER(precision,scale)数据类型用于定义固定长度的整数和浮点数，其中，precision表示精度，用于指定数字的总位数；scale表示标度，用于指定小数点后的数字位数，默认值为0，即没有小数位数。
DATE
日期型数据。DATE数据类型用于定义日期时间类型的数据，其数据长度为固定7个字节，分别描述年、月、日、时、分、秒。
LONG
可变长字符数据，最大可达到2G。LONG数据类型在其它的数据库系统中常被称为备注类型，它主要用于存储大量的可以在稍后返回的文本内容。
TIMESTAMP
TIMESTAMP数据类型也用于定义日期时间数据，但与DATE仅显示日期不同，TIMESTAMP类型数据还可以显示时间和上下午标记，如“11-9月-2007 11:09:32.213 AM”。
CLOB
字符数据，最大可达到4G。
RAW和LONG RAW
裸二进制数据。LONG RAW数据类型在其它数据库系统中常被称为大二进制类型（BLOB），它可以用来存储图形、声音视频数据，尽管关系型数据库管理系统最初不是为它们而设计的，但是多媒体数据可以存储在BLOB或LONG RAW类型的字段内。
BLOB
二进制数据，最大可达到4G。
BFILE
存储外部文件的二进制数据，最大可达到4G。
ROWID
行地址，十六进制串，表示行在所在的表中唯一的行地址，该数据类型主要用于返回ROWID伪列，常用在可以将表中的每一条记录都加以唯一标识的场合。
真题1、PL/SQL中的%ROWTYPE和%TYPE的区别是什么？
答案：%TYPE是定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，其使用示例如下所示：
DECLARE
V_SAL NUMBER(7) ;
V_ENAME EMP.ENAME%TYPE;
V_HIREDATE EMP.HIREDATE%TYPE;
BEGIN
SELECT SAL,ENAME,HIREDATE INTO V_SAL,V_ENAME,V_HIREDATE FROM EMP WHERE EMPNO = 7369;
INSERT INTO EMP(EMPNO,ENAME) VALUES(1111,'LHR');
UPDATE EMP SET SAL = 2000 WHERE EMPNO= 1010;
DELETE EMP WHERE EMPNO = 1012;
COMMIT;
DBMS_OUTPUT.PUT_LINE(V_SAL || ',' || V_ENAME || ',' || V_HIREDATE);
END;
关于%ROWTYPE，需要了解以下内容：
① %ROWTYPE返回的是一个记录类型，其数据类型和数据库表的数据结构一致。
② 声明的变量对应于数据库表或视图中列的集合。
③ 在%ROWTYPE之前加上数据库表名。
④ 记录内字段名和数据类型与参照表或视图中的列相同。
具体而言，%ROWTYPE有如下优点：
① 可以不必知道数据库中列的数量和类型。
② 在运行期间，数据库中列的数量和类型可能发生变化，但是却不用修改代码。
③ 在SELECT语句中使用该属性可以有效地检索表中的行。
%ROWTYPE使用示例如下所示：
DECLARE
V_EMP EMP%ROWTYPE;
BEGIN
SELECT * INTO V_EMP FROM EMP WHERE EMPNO = 7521;
DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO || ',' || V_EMP.ENAME);
END;
1.2 Oracle数据类型
1.2.1 预定义数据类型
Oracle的数据类型分为标量（Scalar）类型、复合（Composite）类型、引用（Reference）类型和LOB（Large Object）类型4种类型 。标量类型没有内部组件又分为四类：数字、字符、布尔和日期/时间；而复合类型包含了能够被单独操作的内部组件；引用类型类似于3G语言中的指针，能够引用一个值；LOB类型的值就是一个lob定位器，能够指示出大对象的存储位置。
下图是在PL/SQL中可以使用的预定义类型。
1.3 标量类型
1.3.1 数字型
数字类型可以存储整数、实数和浮点数，可以表示数值的大小，参与计算。
1.3.1.1 BINARY_INTEGER
我们可以使用BINARY_INTEGER数据类型来存储有符号整数。它的范围是-231至231。跟PLS_INTEGER一样，BINARY_INTEGER所需的存储空间也要小于NUMBER。但是,大多数的BINARY_INTEGER操作要比PLS_INTEGER操作慢。
一、 BINARY_INTEGER子类型
所谓的基类型，就是有子类型继承于它。子类型在基类型的基础上添加一些约束限制，也可能重新定义数值范围。为了使用方便，PL/SQL预定义了下面几个BINARY_INTEGER的子类。
NATURAL
NATURALN
POSITIVE
POSITIVEN
SIGNTYPE
子类型NATURAL和POSITIVE能让我们将一个整数变量的取值范围分别限制在非负数和正整数之内。NATURALN和POSITIVEN不允许为整数类型变量赋空值。SIGNTYPE把整数的取值范围限定在-1，0，1，这在编程中很适合表示三态逻辑(tri-state logic)。
1.3.1.2 NUMBER
我们可以使用NUMBER数据类型来存储定点或浮点数。它的范围是1E-130至10E125。如果表达式的值超过这个范围，我们就会得到数字溢出错误(a numeric overflow or underflow error)。我们可以为要存储的数字指定精度，包括数字的全长和小数长度。语法如下：
NUMBER[(precision,scale)]
其中precision表示数字的总长度，scale代表可以有几位小数。如果要使用浮点数的话，就不能指定长度和精度，像下面这样声明就可以了：
NUMBER
声明整数直接使用下面的语法：
NUMBER(precision) – same as NUMBER(precision,0)
不可以用常量或变量指定NUMBER的长度和精度。NUMBER类型最大的长度是38位。如果不指定NUMBER类型的最大长度，就会默认采用这个长度或是使用系统所支持的最大长度。
scale的范围从-84到127，能够决定舍入规则。例如，一个scale值为2的数字，舍入后的小数部分将是最接原小数部分的百分位数(3.456舍入为3.46)。如果scale是负数，它就会从小数点左边开始进行舍入操作。如scale值为-3的数字舍入后的结果将是最接近原值的千位数(3456舍入为3000)。scale为零的数字舍入后的结果还是本身。如果我们不指定scale的值，默认就为0。
一、 NUMBER子类型
为了能和ANSI/ISO和IBM类型兼容或是想使用一个更加有描述性意义的名字，我们就可以使用下面的NUMBER子类型。
DEC
DECIMAL
DOUBLE PRECISION
FLOAT
INTEGER
INT
NUMERIC
REAL
SMALLINT
使用DEC、DECIMAL和NUMBERIC可以声明最大精度为38位十进制数字的定点数字。而使用DOUBLE PRECISION和FLOAT可以声明最大精度为126位二进制数字的浮点数字，大约相当于38位十进制数字。或是使用REAL声明最大精度为63位二进制数字的浮点数字，大约相当于18位十进制数字。INTEGER、INT和SMALLINT可以声明最大精度为38位十进制数字的整数。
1.3.1.3 PLS_INTEGER
我们可以使用PLS_INTEGER数据类型来存储有符号整数。它的取值范围在-231至231之间。PLS_INTEGER所需的存储空间要比NUMBER少，运算的速度要高于NUMBER和BINARY_INTEGER。虽然PLS_INTEGER和BINARY_INTEGER的取值范围一样，但它们不完全兼容。PLS_INTEGER在运算时如果有溢出，则会有异常抛出，而BIANRY_INTEGER发生溢出时，如果结果是要赋给一个NUMBER类型的变量时，就不会有异常抛出。为了考虑兼容性，我们仍可以在旧的应用程序中使用BINARY_INTEGER；但在新的应用程序中，PLS_INTEGER会带来更好的性能。
1.3.2 字符型
字符类型可以存放字符和数字混合的数据，表现词和文章，操作字符串。
1.3.2.1 CHAR
我们可以使用CHAR类型来存储定长的字符数据。但该数据的内部表现形式是取决于数据库字符集的。CHAR类型有一个用于指定最大长度的可选参数，长度范围在1到32767字节之间。我们可以采用字节或字符的形式来设置该参数。语法如下：
CHAR[(maximum_size [CHAR | BYTE] )]
maximum_size不能是常量或变量，只能是范围在1到32767之间的整数文字。
如果我们不指定最大值，它默认是1。如果我们用字节形式指定最大值，有时就会出现空间不足的问题(多字节字符会占用多于一个字节的空间)。为了避免这样的问题发生，我们可以采用按照字符的方式指定其最大值，这样，即使是那些包含多个字节的参数可以被灵活地存储下来。按照字符指定长度的方式，上限大小仍旧是32767字节，所以，对于双字节和多字节的字符集，我们可以使用字节最大长度的一半或三分之一作为最大字符个数。
虽然PL/SQL字符变量的长度相对来说比较长，但CHAR类型在数据库的字段中最大存储长度为2000个字节，所以，我们不能往数据库CHAR类型字段中插入超过2000个字节的字符。但是，我们可以把任意CHAR(n)插入LONG类型的字段中，因为LONG的最大长度是2**31字节或是2G(gigabyte)。如果我们不使用CHAR或BYTE来对字符类型长度进行限制的话，初始化参数NLS_LENGTH_SEMANTICS会决定默认长度大小的。CHAR的子类型CHARACTER和CHAR有着相同的取值范围。也就是说，CHARACTER只是CHAR的一个别名而已。这个子类型能与ANSI/ISO和IBM类型相兼容。
1.3.2.2 LONG和LONG RAW
我们可以使用LONG类型来存储变长的字符串。除了LONG类型的最大长度是32760字节之外，LONG类型和VARCHAR2很相像。我们还可以使用LONG RAW类型来存储二进制数据或二进制字符串。LONG RAW和LONG类似，但是它不会被PL/SQL解析。LONG RAW的最大长度也是32760字节。从9i开始，LOB类型变量可以与LONG和LONG RAW类型交换使用。Oracle推荐将LONG和LONG RAW都对应的转换成COLB和BLOB类型。
我们可以将LONG类型的值插入字段类型为LONG的数据库中，因为在数据库中LONG的长度是231字节；但是，不可以从LONG字段中检索超过32760字节的字符放到LONG类型变量中去。同样，对于LONG RAW类型来说，这条规则同样适用，在数据库中它的最大长度也是231字节，而变量的长度在32760字节以内。LONG类型字段可以存储文本、字符数组或短文等。我们可以对LONG字段进行UPDATE、INSERT和SELECT操作，但不能在表达式、SQL函数调用、或某个SQL子句(如：WHERE、GROUP BY和CONNECT BY)中使用它。
注意：在SQL语句中，PL/SQL会将LONG类型的值绑定成VARCHAR2类型，而不是LONG。但是，如果被绑定的VARCHAR2值超过4000个字节，Oracle会自动地将绑定类型转成LONG，但LONG并不能应用在SQL函数中，所以，这时我们就会得到一个错误消息。
一、 oracle里long类型的总结
LONG 数据类型中存储的是可变长字符串，最大长度限制是2GB。
2、对于超出一定长度的文本，基本只能用LONG类型来存储，数据字典中很多对象的定义就是用LONG来存储的。
1、LONG 数据类型中存储的是可变长字符串，最大长度限制是2GB。
2、对于超出一定长度的文本，基本只能用LONG类型来存储，数据字典中很多对象的定义就是用LONG来存储的。
3、LONG类型主要用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。
4、很多工具，包括SQL*Plus，处理LONG 数据类型都是很困难的。
5、LONG 数据类型的使用中，要受限于磁盘的大小。
能够操作 LONG 的 SQL 语句：
1、Select语句
2、Update语句中的SET语句
3、Insert语句中的VALUES语句
限制：
1、一个表中只能包含一个 LONG 类型的列。
2、不能索引LONG类型列。
3、不能将含有LONG类型列的表作聚簇。
4、不能在SQLPlus中将LONG类型列的数值插入到另一个表格中,如insert into …select。
5、不能在SQLPlus中通过查询其他表的方式来创建LONG类型列,如create table as select。
6、不能对LONG类型列加约束条件（NULL、NOT NULL、DEFAULT除外），如：关键字列(PRIMARY KEY)不能是 LONG 数据类型。
7、LONG类型列不能用在Select的以下子句中：where、group by、order by，以及带有distinct的select语句中。
8、LONG类型列不能用于分布查询。
9、PL/SQL过程块的变量不能定义为LONG类型。
10、LONG类型列不能被SQL函数所改变，如：substr、instr。
因为long类型的数值不能用insert into … select的方法插入,故我们要分两步走,先插入其它字段,最后再插入long类型字段,这可以通过过程来实现.下面是一个我做实验实现的例子.
SQL>; create table testlong (id number,name varchar2(12),history long);
表已创建。
SQL>; create table testlong1 (id number,name varchar2(12),history long);
表已创建。
SQL>; insert into testlong values(1,‘dwh’,‘work in foshan’);
已创建 1 行。
SQL>; insert into testlong values(2,‘sfg’,‘work in guangzhou’);
已创建 1 行。
SQL>; select * from testlong;
ID NAME HISTORY
1 dwh work in foshan
2 sfg work in guangzhou
SQL>; insert into testlong1 select * from testlong;
insert into testlong1 select * from testlong
*
ERROR 位于第 1 行:
ORA-00997: 非法使用 LONG 数据类型
SQL>; Declare
2 CURSOR bcur
3 IS SELECT id,history from testlong;
4 brec bcur%ROWTYPE;
5 BEGIN
6 insert into testlong1(id,name) select id,name from testlong;–其它类型
先插入
7 OPEN bcur;
8 LOOP
9 FETCH bcur INTO brec;
10 EXIT WHEN bcur%NOTFOUND;
11 update testlong1 set history=brec.history where id=brec.id;
12 END LOOP;
13 CLOSE bcur;
14 END;
15 /
PL/SQL 过程已成功完成。
SQL>; select * from testlong1;
ID NAME HISTORY
1 dwh work in foshan
2 sfg work in guangzhou
create table testlong (id number,name varchar2(12),history long);
create table testlong1 (id number,name varchar2(12),history long);
insert into testlong values(1,‘dwh’,‘work in foshan’);
insert into testlong values(2,‘sfg’,‘work in guangzhou’);
insert into testlong1 select * from testlong; —报错
insert into testlong1 select t.id ,t.name,to_lob(t.history) from testlong t;
二、 LONG转换为字符串
① 只能利用基础表来转换，或者如果是视图可以建立临时表来转换
② 利用to_lob函数来转换成lob，然后再转换为to_char
long列是不允许出现在 create table xx as select * from yyy
里的
In addition, LONG columns cannot appear in these parts of SQL statements:
…
SELECT lists of
CREATE TABLE … AS SELECT statements
…
oracle建议使用clob\blob\nclob来代替。
如:
create table t(a int,b long); insert into t values(1,‘1’);
–可以这样创建，不过默认类型就成了clob
create table t1 as select a,to_lob(b) from t;
–也可以这样,先创建一个表，然后在插入数据时使用to_lob函数转换一下
create table t2 (a int,b long); insert into t2 select a,to_lob(b) from t;
1、 一次“ORA-00997: 非法使用 LONG 数据类型”的错误解决
问题提出：
当前用户下有一系列LIST类型的分区表，希望找出其中包含有DEFAULT分区的表以及对应的分区来。
查询视图 USER_TAB_PARTITIONS，此视图对应每个分区有一条记录，包含的主要字段有
I、 TABLE_NAME(表名)
II、 PARTITION_NAME(分区名)
III、 HIGH_VALUE(最大值（对应LIST分区的分区值）)
IV、 TABLESPACE_NAME(表空间)
因此，要知道包含有DEFAULT分区表的分区，只要通过下面的语句就可以了。
select * from user_tab_partitions WHERE high_value=‘DEFAULT’
事实上，由于user_tab_partitions.high_value是LONG类型的数据，因而无法直接象VARCHAR2类型数据那样直接进行等值比对得到查询结果。所以上面的语句执行时会出现错误：
ORA-00997: 非法使用 LONG 数据类型
解决方法：
- 步骤一
通过网上查找，得到这样一个函数，sys.dbms_metadata_util.long2varchar，这个sys.dbms_metadata_util包是不在Oracle文档中给出解释的。利用DESC，得到其函数的参数：
FUNCTION LONG2VARCHAR RETURNS VARCHAR2
参数名称 类型 输入/输出默认值?
LENGTH NUMBER IN
TAB VARCHAR2 IN
COL VARCHAR2 IN
ROW ROWID IN
这样我们可以通过这个函数将LONG类型的数据转换为VARCHAR2类型的数据，从而得到最终的结果。
执行查询
SELECT *
FROM user_tab_partitions
WHERE UPPER(sys.dbms_metadata_util.long2varchar(1000, ‘USER_TAB_PARTITIONS’, ‘HIGH_VALUE’, ROWID)) = ‘DEFAULT’
返回错误信息：
第 1 行出现错误:
ORA-01446: 无法使用 DISTINCT, GROUP BY 等子句从视图中选择 ROWID 或采样。
由于USER_TAB_PARTITIONS不是基本表，因此不存在ROWID，看来sys.dbms_metadata_util.long2varchar2只对基本表有效。
2. 步骤2
下面我们就通过USER_TAB_PARTITIONS找到其基本表。
连接到sys用户下，执行：
SELECT owner, object_name, object_type
FROM DBA_OBJECTS
WHERE OBJECT_NAME=‘USER_TAB_PARTITIONS’
得到结果如下：
OWNER OBJECT_NAME OBJECT_TYPE
SYS USER_TAB_PARTITIONS VIEW
PUBLIC USER_TAB_PARTITIONS SYNONYM
这里发现USER_TAB_PARTITIONS是一个视图，取得这个视图的脚本。
SELECT DBMS_METADATA.GET_DDL(‘VIEW’,’ USER_TAB_PARTITIONS’)
FROM DUAL;
完整的查询结果这里不再给出，通过查询可以了解到USER_TAB_PARTITIONS的HIGH_VALUE对应tabpartv表其他的关键字段还有
Obj# 分区对象的对象号，对应dba_objects.object_id
Dataobj# 分区对象的数据对象号，对应dba_objects.data_object_id
Bo# 分区表对应的对象号，对应dba_objects.object_id(记录对应为表)
Part# 分区表号，对应user_tab_partitions. partition_position
这样，我们只要执行下面的查询就可以了，
SELECT *
FROM tabpartv$
WHERE sys.dbms_metadata_util.long2varchar(10, ‘SYS.TABPARTV$’, ‘HIBOUNDVAL’, ROWID) = ‘DEFAULT’
根据得到的结果，我们利用user_objects查询对应的obj#，然后再查询USER_TAB_PARTITIONS就可以了。要指定特定的用户，比如SCOTT用户下的分区数据，还要关联obj基础表。
下面用一个SQL得到我们想要的结果，
SELECT a.* FROM dba_tab_partitions a JOIN dba_objects b
ON(a.table_owner=b.owner AND
a.table_name=b.object_name and
a.partition_name=b.subobject_name)
WHERE b.object_id IN(
SELECT a.obj#
FROM tabpartv$ a join obj$ b ON(a.obj#=b.obj#)
JOIN user$ c ON(b.owner#=c.user#)
WHERE UPPER(sys.dbms_metadata_util.long2varchar(10, ‘SYS.TABPARTV’,
‘HIBOUNDVAL’,
c.ROWID) high_value
FROM dba_tab_partitions a,
dba_objects b,
SYS.tabpartv$ c
WHERE a.table_owner = b.owner
AND a.table_name = b.object_name
AND a.partition_name = b.subobject_name
AND b.OBJECT_ID = c.obj#
and a.table_owner not in (‘SYS’,‘SYSTEM’)
ORDER BY a.table_owner,
a.table_name,
a.partition_position;
SQL> alter table plan_table move;
alter table plan_table move
ORA-00997: 非法使用 LONG 数据类型
SQL> desc plan_table
Name Type Nullable Default Comments
STATEMENT_ID VARCHAR2(30) Y
PLAN_ID NUMBER Y
…
PARTITION_STOP VARCHAR2(255) Y
PARTITION_ID INTEGER Y
OTHER LONG Y
DISTRIBUTION VARCHAR2(30) Y
…
QBLOCK_NAME VARCHAR2(30) Y
查看表结构，表里含有类型为 long 的字段，是不能move
那我们来把long改为clob
SQL> alter table plan_table modify(other clob);
Table altered
SQL> alter table plan_table move;
Table altered
成功~
QL> alter table plan_table modify(other long);
alter table plan_table modify(other long)
ORA-22859: 无效的列修改
由此可见想要再把 字段类型改回 long 是不能的
另外 plan_table move 以后会多2个对象出来
SQL> /
SEGMENT_NAME FILE_ID EXTENT_ID BLOCK_ID BYTES
IND_T1_N1_V1 11 0 1081 65536
FY_2004_2 11 3 1033 65536
…
FY_2004_2 14 5 35489 65536
FY_2006 15 0 38313 65536
SYS_LOB0000030377C00026 15 0 1169 65536
FY_2006_IND 15 0 38369 65536
SYS_C003157 15 0 38321 65536
IND_T1_N1 15 0 38377 65536
EMP_TEST 15 0 38361 65536
PLAN_TABLE 15 0 1201 65536
CHAINED_ROWS 15 0 1297 65536
…
FY_2004_2 18 7 1169 65536
已选择31行。
其中SYS_LOB0000030377C00026 是索引
1.3.2.3 RAW
我们可以使用RAW数据类型来存储二进制数据或字节串。例如，一个RAW类型的变量可以存储一个数字化图形。RAW类型数据和VARCHAR2类型数据类似，只是PL/SQL不对其进行解析而已。同样，在我们把RAW数据从一个系统传到另一个系统时，Oracle Net也不会对它做字符集转换。RAW类型包含了一个可以让我们指定最大长度的可选参数，上限为32767字节，语法如下：
RAW(maximum_size)
我们不能使用常量或变量来指定这个参数；而且参数的范围必须是在1到32767范围内。在数据库中RAW类型字段的最大长度是2000个字节，所以，不可以把超过2000字节的内容放到RAW类型字段中。我们可以把任何RAW类型插入到LONG RAW类型的数据库字段中，因为LONG RAW在数据库中是2**31，但是不能把超过32767字节的LONG RAW类型放入RAW变量中。
1.3.2.4 ROWID和UROWID
在Oracle内部，每个数据表都有一个伪列ROWID，用于存放被称为ROWID的二进制值。每个ROWID代表了一行数据的存储地址。物理ROWID能够标识普通数据表中的一行信息，而逻辑ROWID能够标识索引组织表(index-organized table)中的一行信息。其中ROWID类型只能存储物理内容，而UROWID(universal rowid)类型可以存储物理，逻辑或外来(non-Oracle)ROWID。
建议：只有在旧的应用程序中，为了兼容性我们才使用ROWID数据类型。对于新的应用程序，应该使用UROWID数据类型。
当我们把查询出来的ROWID放到ROWID变量时，可以使用内置函数ROWIDTOCHAR，这个函数能把二进制内容转换成18个字节的字符串；还有一个与之对应的CHARTOROWID函数，可以对该过程进行反操作，如果转换过程中发现字符串并不是一个有效的ROWID时，PL/SQL就会抛出预定义异常SYS_INVALID_ROWID。UROWID变量和字符串之间进行转换也可以直接使用赋值操作符。这时，系统会隐式地实现UROWID和字符类型之间的转换。
物理ROWID(Physical Rowid)可以让我们快速的访问某些特定的行。只要行存在，它的物理ROWID就不会改变。高效稳定的物理ROWID在查询行集合、操作整个集合和更新子集是很有用的。例如，我们可以在UPDATE或DELETE语句的WHERE子句中比较UROWID变量和ROWID伪列来找出最近一次从游标中取出的行数据。
物理ROWID有两种形式。10字节扩展ROWID格式(10-byte extended rowid format)支持相对表空间块地址并能辨识分区表和非分区表中的行记录。6字节限定ROWID格式支持向后兼容。
扩展ROWID使用检索出来的每一行记录的物理地址的base-64编码。例如，在SQL*Plus(隐式地将ROWID转换成字符串)中的查询：
SQL> SELECT ROWID, ename
SQL> FROM emp
SQL> WHERE empno = 7788;
ROWID ENAME
AAAAqcAABAAADFNAAH SCOTT
OOOOOOFFFBBBBBBRRR这样的形式有四部分组成:
1 “000000"代表数据对象号(data object number)，如上例中的"AAAAqc”，能够辨识数据库段。同一段中的模式对象，都有着相同的数据对象号。
2 “FFF"代表文件号(file number)，如上例中的"AAB”，能辨识出包含行的数据文件。在数据库中，文件号是唯一的。
3 “BBBBBB"代表块号(block number)，如上例中的"AAADFN”，能辨识出包含行的数据块。块号是与它们所在的数据文件相关，而不是表空间。所以，两个在同一表空间的行数据，如果它们处于不同的数据文件中，也可能有着相同的块号。
4 “RRR"代表了行号(row number)，如上例中的"AAH”，可以辨识块中的行数据。
逻辑ROWID为访问特定行提供了最快的访问速度。Oracle在索引组织表基础上使用它们构建二级索引。逻辑ROWID没有持久的物理地址，当新数据被插入时，它的值就会在数据块上偏移。但是，如果一个行数据的物理位置发生变化，它的逻辑ROWID就无效了。
1.3.2.5 VARCHAR2
我们可以使用VARCHAR2类型来存储变长的字符数据。至于数据在数据库中的内部表现形式要取决于数据库的字符集。VARCHAR2类型需要指明数据长度，这个参数的上限是32767字节。语法如下：
VARCHAR2(maximum_size [CHAR | BYTE])
我们不能使用常量或变量来指定maximum_size值，maximum_size值的有效范围在1到32767之间。
对于长度不同的VARCHAR2类型数据，PL/SQL对它们的处理方式也是不同的。值小的PL/SQL会优先考虑到它的性能问题，而对于值大的PL/SQL会优先考虑到内存的使用效率问题。截止点(cutoff point)为2000个字节。在2000字节以下，PL/SQL会一次性分配我们声明时所指定大小的空间容纳实际的值；2000字节或2000字节以上时，PL/SQL会动态的分配VARCHAR2的存储容量。比如我们声明两个VARCHAR2类型的变量，一个长度是1999字节，一个是2000字节，如果我们把长度为500字节的值分别分配给这两个变量，那么前者占用的空间就是1999字节而后者只需500字节。
如果我们采用字节形式而非字符形式指定最大值时，VARCHAR2(n)变量就有可能太小而不能容纳n个多字节字符。为了避免这个问题，就要使用VARCHAR2(n CHAR)形式进行定义，这样，即使字符中包含多字节字符也不会出问题。所以，对于双字节或多字节字符集，我们可以指定单字节字符集中字符个数的1/2或1/3。
虽然PL/SQL字符变量相对比较长，但VARCHAR2类型的数据库字段最大长度为4000个字节。所以，不能把字节超过4000的VARCHAR2类型值插入VARCHAR2类型的数据库字段中。
我们可以把任何VARCHAR2(n)值插入一个LONG类型的数据库字段，因为LONG字段最大长度为2**31字节。但是，不能把LONG字段中检索出来的长度超过32767字节的值放到VARCHAR2(n)变量中。
如果声明时不使用CHAR或BYTE限定修饰词，初始化参数NLS_LENGTH_SEMANTICS会决定默认的设置。当PL/SQL过程编译时，这个参数的设置就会被记录下来，这样，当过程失效后被重新编译时就会被重新使用。
一、 VARCHAR2的子类型
下面VARCHAR2的子类型的范围与VARCHAR2完全相同，它们只是VARCHAR2的一个别名而已。
STRING
VARCHAR
我们可以使用这些子类型来与ANSI/ISO和IBM类型兼容。注意：目前，VARCHAR和VARCHAR2有着相同意义，但是在以后的PL/SQL版本中，为了符合SQL标准，VARCHAR有可能会作为一个单独的类型出现。所以最好使用VARCHAR2，而不是VARCHAR。
3、本地字符型
被广泛使用的单字节ASCII和EBCDIC字符集很适合表现罗马字符，但有些亚洲语言，如汉语、日语等包含了成千上万个字符，这些语言中的一个字符就需要用两个或三个字节来表示。为了处理这些语言，Oracle提供了全球化支持，允许我们处理单字节和多字节字符数据，并在字符集之间进行数据转换。Oracle还能让我们的应用程序运行在不同的语言环境中。
有了全球化支持，数字和日期格式会根据用户会话中所指定的语言约定(language convention)而自动进行调节。因此，全世界的用户可以使用他们母语来使用Oracle。
Oracle支持两种数据库字符集和一种国家特有字符集，前者用于标识符和源代码，后者用于国家特有语言数据。NCHAR和NVARCHAR2类型用于存储本地字符集。
注意，当在数据库的不同字符集之间转换CHAR或VARCHAR2数据时，要确保数据保持良好的形式(well-formed)。
? 比较UTF8和AL16UTF16编码
国家特有字符集使用Unicode来表现数据，采用UTF8或AL16UTF16编码。
每个使用AL16UTF16编码的字符都占用2个字节。这将简化字符串的长度计算，避免采用混合语言编程时发生截断错误，但是这会比ASCII字符所组成的字符串需要更多空间。
每个使用UTF8编码的字符占用1、2或3个字节。这就能让我们把更多的字符放到变量或数据表的字段中，但这只是在大多数字符用单字节形式表现的条件下才能做到。这种编码在传递数据到字节缓冲器时可能会引起截断错误。
Oracle公司推荐使用默认的AL16UTF16编码，这样可以获取最大的运行时可靠性。如果想知道一个Unicode字符串占用多少字节，就要使用LENGTHB函数，而不是LENGTH。
? NCHAR
我们用NCHAR类型来储存定长国家特有字符数据。数据的内部表现取决于数据库创建时指定的国家特有字符集，字符集可能采用变长编码(UTF8)或定长编码(AL16UTF16)。因为这种类型总是与多字节字符兼容，所以我们可以使用它支持任何Unicode字符数据。
NCHAR数据类型可接受一个可选参数来让我们指定字符的最大长度。语法如下：
NCHAR[(maximum_size)]
因为物理限制是32767个字节，所以在AL16UTF16编码格式下最大长度为32767/2，UTF8编码格式下是32767/3。
我们不能使用常量或变量来指定最大值，只能使用整数文字。
如果我们没有指定最大长度，它默认值就为1。这个值总是代表字符的个数，不像CHAR类型，既可以采用字符形式又可以采用字节形式。
my_string NCHAR(100); – maximum size is 100 characters
NCHAR在数据库字段中的最大宽度是2000字节。所以，我们不能向NCHAR字段中插入值超过2000字节的内容。
如果NCHAR的值比NCHAR字段定义的宽度要小，Oracle就会自动补上空格，填满定义的宽度。
我们可以在语句和表达式中交互使用CHAR和NCHAR值。从CHAR转到NCHAR总是安全的，但在NCHAR值转换到CHAR的过程中，如果CHAR类型不能完全表现NCHAR类型的值，就会引起数据丢失。这样的数据丢失会导致字符看起来像问号(?)。
? NVARCHAR2
我们可以使用NVARCHAR2数据类型来存储变长的Unicode字符数据。数据的内部表现取决于数据库创建时所指定的国家特有字符集，它有可能采用变长编码(UTF8)或是定长编码(AL16UTF16)。因为这个类型总与多字节兼容，我们可以用它来支持Unicode字符数据。
NVARCHAR2数据类型需要接受一个指定最大大小的参数。语法如下：
NVARCHAR2(maximum_size)
因为物理限制是32767个字节，所以在AL16UTF16编码格式下最大长度为32767/2，UTF8编码格式下是32767/3。
我们不能使用常量或变量来指定最大值，只能使用整数文字。
最大值总是代表字符的个数，不像CHAR类型，既可以采用字符形式又可以采用字节形式。
my_string NVARCHAR2(200); – maximum size is 200 characters
NVARCHAR2在数据库字段中的最大宽度是4000字节。所以，我们不能向NVARCHAR2字段中插入长度超过4000字节的值。
我们可以在语句和表达式中交互使用VARCHAR2和NVARCHAR2值。从VARCHAR2向NVARCHAR2转换总是安全的，但在NVARCHAR2值转换到VARCHAR2的过程中，如果VARCHAR2类型不能完全表现NVARCHAR2类型的值，就会引起数据丢失。这样的数据丢失会导致字符看起来像问号(?)。
二、 Oracle数据库中char()，varchar2()，nvarchar2()三种数据类型的区别
-
char()类型:
（1）如果在数据库中定义的长度为10位，而我实际入力的数据长度不足10位，系统会在入力数据的后面用空字符串补足10位。
（2）一个全角认作2位长度。
2. varchar2()类型：
（1） 不足数据库规定长度，不会补足长度。
（2） 一个全角认作2位长度。
3. nvarchar2()类型：
（1） 不足数据库规定长度，不会补足长度。
（2） 一个全角认作1位长度。
结论：由此在做设计时，为节省系统资源会尽量选用varchar2()和nvarchar2()类型。
三、 Oracle CHAR，VARCHAR，VARCHAR2,nvarchar类型的区别与使用
一 varchar,nvarchar,
四个类型都属于变长字符类型， varchar和varchar2的区别在与后者把所有字符都占两字节，前者只对汉字和全角等字符占两字节。 nvarchar和nvarchar2的区别和上面一样， 与上面区别在于是根据Unicode标准所进行的定义的类型，通常用于支持多国语言类似系统的定义。
1.char
char的长度是固定的，比如说，你定义了char(20),即使你你插入abc，不足二十个字节，数据库也会在abc后面自动加上17个空格，以补足二十个字节；
char是区分中英文的，中文在char中占两个字节，而英文占一个，所以char(20)你只能存20个字母或10个汉字。
char适用于长度比较固定的，一般不含中文的情况
2.varchar/varchar2
varchar是长度不固定的，比如说，你定义了varchar(20),当你插入abc，则在数据库中只占3个字节。
varchar同样区分中英文，这点同char。
varchar2基本上等同于varchar，它是oracle自己定义的一个非工业标准varchar，不同在于，varchar2用null代替varchar的空字符串
varchar/varchar2适用于长度不固定的，一般不含中文的情况
3.nvarchar/nvarchar2
nvarchar和nvarchar2是长度不固定的
nvarchar不区分中英文，比如说：你定义了nvarchar(20),你可以存入20个英文字母/汉字或中英文组合，这个20定义的是字符数而不是字节数
nvarchar2基本上等同于nvarchar，不同在于nvarchar2中存的英文字母也占两个字节
nvarchar/nvarchar2适用于存放中文
char [ ( n ) ]
固定长度，非 Unicode 字符数据，长度为 n 个字节。n 的取值范围为 1 至 8,000，存储大小是 n 个字节。
varchar [ ( n | max ) ]
可变长度，非 Unicode 字符数据。n 的取值范围为 1 至 8,000。max 指示最大存储大小是 2^31-1 个字节。存储大小是输入数据的实际长度加 2 个字节，用于反映存储的数据的长度。所输入数据的长度可以为 0 个字符。
* 如果列数据项的大小一致，则使用 char。
* 如果列数据项的大小差异相当大，则使用 varchar。
* 如果列数据项大小相差很大，而且大小可能超过 8,000 字节，请使用 varchar(max)。
如果未在数据定义或变量声明语句中char 或 varchar 数据类型指定 n，则默认长度为 1。如果在使用 CAST 和 CONVERT 函数时char 或 varchar 数据类型未指定 n，则默认长度为 30。
当执行 CREATE TABLE 或 ALTER TABLE 时，如果 SET ANSI_PADDING 为 OFF，则定义为 NULL 的 char 列将作为 varchar 处理。
另外帮助理解的，只供参考：转自http://www.51testing.com/?uid-258885-action-viewspace-itemid-141197
也可参照学习http://ce.sysu.edu.cn/garden/dispbbs.asp?boardid=26&ID=8774&replyID=18180&skin=1
1.NULL值(空值)。
a. char列的NULL值占用存储空间。
b. varcahr列的NULL值不占用存储空间。
c. 插入同样数量的NULL值，varchar列的插入效率明显高出char列。
2.插入数据
无论插入数据涉及的列是否建立索引，char的效率都明显低于varchar。
3. 更新数据
如果更新的列上未建立索引，则char的效率低于varchar，差异不大；建立索引的话，效率较高。
4. 修改结构
a. 无论增加或是删除的列的类型是char还是varchar，操作都能较快的完成，而且效率上没有什么差异。
b. 对于增加列的宽度而言，char与varchar有非常明显的效率差异，修改varcahr列基本上不花费时间，而修改char列需要花费很长的时间。
5.数据检索
无论是否通过索引，varchar类型的数据检索略优于char的扫描。
选择char还是选择varchar的建议
1.适宜于char的情况：
a. 列中的各行数据长度基本一致，长度变化不超过50字节；
b. 数据变更频繁，数据检索的需求较少。
c. 列的长度不会变化，修改char类型列的宽度的代价比较大。
d. 列中不会出现大量的NULL值。
e. 列上不需要建立过多的索引，过多的索引对char列的数据变更影响较大。
2.适宜于varchar的情况;
a. 列中的各行数据的长度差异比较大。
b. 列中数据的更新非常少，但查询非常频繁。
c. 列中经常没有数据，为NULL值或为空值
nchar [ ( n ) ]
n 个字符的固定长度的 Unicode 字符数据。n 值必须在 1 到 4,000 之间（含）。存储大小为两倍 n 字节。
nvarchar [ ( n | max ) ]
可变长度 Unicode 字符数据。n 值在 1 到 4,000 之间（含）。max 指示最大存储大小为 2^31-1 字节。存储大小是所输入字符个数的两倍 + 2 个字节。所输入数据的长度可以为 0 个字符。
注释
如果没有在数据定义或变量声明语句中指定 n，则默认长度为 1。如果没有使用 CAST 函数指定 n，则默认长度为 30。
如果列数据项的大小可能相同，请使用 nchar。
如果列数据项的大小可能差异很大，请使用 nvarchar。
sysname 是系统提供的用户定义数据类型，除了不可为空值外，在功能上与 nvarchar(128) 相同。sysname 用于引用数据库对象名。
为使用 nchar 或 nvarchar 的对象分配的是默认的数据库排序规则，但可使用 COLLATE 子句分配特定的排序规则。
SET ANSI_PADDING ON 永远适用于 nchar 和 nvarchar。SET ANSI_PADDING OFF 不适用于 nchar 或 nvarchar 数据类型。
在Oracle中CHAR,NCHAR,VARCHAR,VARCHAR2,NVARCHAR2这五种类型的区别
1.CHAR(size)和VARCHAR(size)的区别
CHAR为定长的字段，最大长度为2K字节；
VARCHAR为可变长的字段，最大长度为4K字节；
2.CHAR(size)和NCHAR(size)的区别
CHAR如果存放字母数字占1个字节，存放GBK编码的汉字存放2个字节，存放UTF-8编码的汉字占用3个字节；
NCHAR根据所选字符集来定义存放字符的占用字节数，一般都为2个字节存放一个字符(不管字符或者汉字)
3.VARCHAR(size)和VARCHAR2(size)的区别
在现在的版本中，两者是没有区别的；最大长度为4K字节；推荐使用VARCHAR2；
4.VARCHAR2(size)和NVARCHAR2(size)的区别
最大长度为4K字节，区别同CHAR与NCHAR的区别；（如果数据库字符集长度是2，则NVARCHAR2最大为2K）
5.共同特性
当执行insert的时候，插入的值为’’，则转变成null，即insert … values(’’) <=> insert … values(null)
搜索的条件须用where xx is null
6.例子
比如有一个性别字段，里面存放“男，女”的其中一个值，两种常用选择
CHAR(2) 和 NCHAR(1)
四、 NLS_LENGTH_SEMANTICS参数
值设置 数据库字符集 长度
BYTE ZHS16GBK 2
AL32UTF8 3
CHAR ZHS16GBK 1
AL32UTF8
ORACLE初始化参数：NLS_LENGTH_SEMANTICS
初始化参数NLS_LENGTH_SEMANTICS用于指定CHAR列或VARCHAR2列的长度定义方式，默认值为BYTE。当设置该参数为BYTE时，表示定义CHAR列或VARCHAR2列采用字节长度方式；当设置该参数为CHAR时，表示定义CHAR列或VARCHAR2列采用字符个数方式。需要注意，设置该参数对于已存在列没有作用。
该初始化参数是动态参数，可以使用ALTER SESSION或ALTER SYSTEM命令进行修改。示例如下：
SQL>ALTER SESSION SET nls_length_semantics=char;
SQL>CREATE TABLE t1(cola VACHAR2(4));
SQL>INSERT INTO t1 VALUES(‘北京’);
SQL>INSERT INTO t1 VALUES(‘BEIJING’);
上例将报错ORA-12899。
附甲骨文官方说明：
属性 说明
参数类型 String
语法 NLS_LENGTH_SEMANTICS = string
Example: NLS_LENGTH_SEMANTICS = ‘CHAR’
默认值 BYTE
允许动态修改 ALTER SESSION
取值范围 BYTE | CHAR
NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.
NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may be required to use byte semantics in order to maintain compatibility with existing applications.
NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.
从9i开始，oracle提供了NLS_LENGTH_SEMANTICS这个参数，其有两个取值，CHAR和BYTE。当为CHAR时字符类型的长度是按字符个数来计算，而不是按BYTE来计算，这在使用变长字符集(AL32UTF8)的情况下非常有用，因为一个字符所占用的字节数是不定的，就给我们准确估计字段长度(BYTE)带来不便。同时当为CHAR时，对那些采用7/8bit的字符集(US7ASCII/WE8MSWIN1252)来说也不会带来空间上的浪费。
下面就使用使用该参数需要注意的一些地方做出描述
1、该参数分为三个级别，分别是数据库、实例、会话三个级别
可以分别在NLS_DATABASE_PARAMETERS、NLS_INSTANCE_PARAMETERS、NLS_SESSION_PARAMETERS里查询到
数据库级的值在创意数据库时被指定，实例级的值可以通过修改init.ora/Spfile来指定，会话级的可以使用alter session来指定
2、实例/会话级的的参数只对其修改之后的对象(包括字段和pl/sql变量)产生作用，修改之前的维持不变。
可以使用ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=spfile;来修改实例级的参数值
注意，必须需要重启才会生效！(就算指定了scope=both)
该参数取值决定顺序
1、如果没有客户端(环境变量，后面会讲到)或者会话级指定该值，该参数由实例级的值决定(通过NLS_INSTANCE_PARAMETERS查询)
2、从10g开始，NLS_LENGTH_SEMANTICS可以在环境变量或者注册表中设定，一旦设定之后会话级的值默认为环境变量或注册表中的取值
(通过NLS_SESSION_PARAMETERS查询)
3、可以使用ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR改变当前会话的取值。
其它注意事项
Oracle E-business Suite不支持该参数的CHAR语义
在11g以下的数据库中，不要在创建数据库时指定 NLS_LENGTH_SEMANTICS=CHAR，而应该使用 NLS_LENGTH_SEMANTICS=BYTE(或者不指定)来创建数据库，然后在修改ini.ora/spfile里改参数的值。否则会导致数据创建期间部分XDB和SYS的对象使用不支持的CHAR语义，产生错误，不过11.1.0.6之后已经得到解决。详见 Bug 5545716 和 Bug 4886376
数据库级取值为BYTE，实例级取值为CHAR 和 数据库级取值为CHAR，实例级取值为CHAR 是相同的，所以没必要纠结，详情可以参阅 Note 241047.1 The Priority of NLS Parameters Explained.
你需要在BYTE语义下调用使用了STARTUP MIGRATE的脚本(比如patch脚本或者 $ORACLE_HOME/RDBMS/ADMIN 下的脚本(比如catalog.sql ))
If you run patch scripts or scripts from $ORACLE_HOME/RDBMS/ADMIN like catalog.sql use STARTUP MIGRATE; and run then the scripts. ( run them with NLS_LENGTH_SEMANTICS=BYTE )
- Oracle Text does NOT support instance wide NLS_LENGTH_SEMANTICS=CHAR If you are using Oracle Text then the database needs to be created and started with NLS_LENGTH_SEMANTICS=BYTE.
Using CHAR semantics on column definitions itself is supported however.Bug 4465964
This is not documented in the 10.2 docset, it is in the 11g doc: http://download.oracle.com/docs/cd/B28359_01/text.111/b28304/csql.htm#i997737 section “CHARSET COLUMN charset_column_name”
9i以下的客户端不能识别CHAR语义，当你用8i客户端去连接9i UTF8数据库时，数据库的VARCHAR2(10 CHAR)会以BYTE语义显示为VARCHAR2(30)
这样8i的的客户端才能插入不超过30byte的数据，鉴于此，当使用CHAR语义时，最好使用9i以上的客户端。
可以使用以下方法限定客户端版本为9及以上：
在10.1 中可以在init.ora中指定DB_ALLOWED_LOGON_VERSION=9
10.2及以上，在sqlnet.ora中指定SQLNET.ALLOWED_LOGON_VERSION = 9
注意：不要在10.2及以上使用DB_ALLOWED_LOGON_VERSION
设置之后，如果用8i及以下的客户端访问数据库会报ORA-28040: No matching authentication protocol错误
默认值
当NLS_LENGTH_SEMANTICS取值为BYTE时，默认为BYTE；当取值为CHAR时，默认为CHAR。
意思就是说，如果NLS_LENGTH_SEMANTICS=BYTE char(10)实际上就是 char(10 byte)
如果NLS_LENGTH_SEMANTICS=CHAR char(10)实际上就是 char(10 CHAR)
不管NLS_LENGTH_SEMANTICS取值为何，都可以在使用时显示的指定是按CHAR还是BYTE
例如，实例NLS_LENGTH_SEMANTICS=BYTE，在创建表时可以指定char(10 char)就可以使用char语义了
另外，对于单字节字符集编码来说，CHAR=BYTE
NLS_LENGTH_SEMANTICS对于属于SYS的表(对SYSTEM有效)无效，如下：
SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
Session altered.
SQL> Create table sys.test1 (Col1 CHAR(20),Col2 VARCHAR2(100));
Table created.
SQL> Create table sys.test2 (Col1 CHAR(20 CHAR),Col2 VARCHAR2(100 CHAR));
Table created.
SQL> desc test1
Name Null? Type
COL1 CHAR(20 BYTE)
COL2 VARCHAR2(100 BYTE)
SQL> desc test2
Name Null? Type
COL1 CHAR(20)
COL2 VARCHAR2(100)
PL/SQL与NLS_LENGTH_SEMANTICS
存储过程和包里的在创建或者重新编译的时候会读取当前SESSION的NLS_LENGTH_SEMANTICS值，并存入代码中。考虑下面的问题
当前会话使用的是BYTE语义，你创建了一个存储过程，里面有一个变量的类型声明为CHAR(10)，那么此时该变量实际为CHAR(10 BYTE)，
有一天你把BYTE改成了CHAR，然后去调用该存储过程，你会发现报 ORA-06502 PL/SQL Numeric or value error错误，因为虽然环境使用的是CHAR语义，
但是存储过程里还是使用的BYTE语义，此时你需要重要编译该存储过程，它后重新读取当前SESSION的值，写入代码中。
鉴于此，建议在书写存储过程的时候显示声明其语义(CHAR(10 CHAR))，以免不必要的麻烦。
可以通过下在的SQL来查看现有存储过程使用的语义
SELECT C.owner
||’.’
|| C.name
||’ - ’
|| C.type
FROM DBA_PLSQL_OBJECT_SETTINGS C
WHERE C.NLS_LENGTH_SEMANTICS = ‘CHAR’
– to have a list of all using BYTE semantics use
– WHERE C.NLS_LENGTH_SEMANTICS = ‘BYTE’
– to check only for a certain users use
– and C.owner IN (‘SCOTT’)
ORDER BY C.owner,C.name
NLS_LENGTH_SEMANTICS
Property Description
Parameter type String
Syntax NLS_LENGTH_SEMANTICS = string
Example: NLS_LENGTH_SEMANTICS = ‘CHAR’
Default value BYTE
Modifiable ALTER SESSION
Range of values BYTE | CHAR
The session-level value of NLS_LENGTH_SEMANTICS specifies the default length semantics to use for VARCHAR2 and CHAR table columns, user-defined object attributes, and PL/SQL variables in database objects created in the session. This default may be overridden by the explicit length semantics qualifiers BYTE and CHAR in column, attribute, and variable definitions.
会话级别的nls_length_semantics说明默认的char和varchar2，用户定义的对象属性，和pl/sql变量在数据库对象创建的长度语法。默认值会被显示说明的参数值覆盖。
The instance-level value of NLS_LENGTH_SEMANTICS provides a default for the session-level value if NLS_LENGTH_SEMANTICS it is not set explicitly by the database client through the NLS_LENGTH_SEMANTICS client environment variable (does not apply to JDBC Thin clients), or the ALTER SESSION SET NLS_LENGTH_SEMANTICS statement.
实例级别的nls_length_semantics提供默认的会话级别的值，如果数据库客户端没有显示的设定。
NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based.
Sessions logged in as SYS do not use the NLS_LENGTH_SEMANTICS parameter. They use BYTE length semantics for all created objects unless overridden by the explicit BYTE and CHAR qualifiers in object definitions (SQL DDL statements).
sys用户不使用nls_length_semantics参数，他们默认使用BYTE，除非在DDL列属性的时候显示的指定BYTE或CHAR.
Caution:
Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.
oracle强烈建议你不要在实例或者服务参数文件中设置nls_length_semantics参数为CHAR。这是因为许多存在的安装脚本会在使用字符长度语义创建列的时候出现异常，导致运行失败，包括缓存溢出。
Any DBA wanting to use NLS_LENGTH_SEMANTICS.
NLS_LENGTH_SEMANTICS allows you to specify the length of a column datatype in terms of CHARacters rather than in terms of BYTEs. Typically this is when using an AL32UTF8 or other varying width NLS_CHARACTERSET database where one character is not always one byte. While using CHAR semantics has as such no added value in a 7/8 bit characterset it’s fully supported so any application code / table setup using CHAR can also be used in a 7/8bit characterset like US7ASCII/WE8MSWIN1252.
This parameter is a 9i (and up) feature and is not available in older releases.
nls_length_semantics允许你说明列类型的长度为CHAR而不是BYTES。典型的当使用AL32UTF8或者其他多字节字符类型。该参数在9i中出现。
- NLS_LENGTH_SEMANTICS cannot be set as a client side environment/registry parameter in 9i, from 10g onwards NLS_LENGTH_SEMANTICS can be set (please define it in UPPERCASE). If NLS_LENGTH_SEMANTICS is set at client side then any session started from that client will use the value defined in the environment/registry and it can be checked in NLS_SESSION_PARAMETERS.
9i客户端不能设置nls_length_semantics，从10g开始时可以的。如果在客户端设置了nls_length_semantics那么所有使用该客户端开始的会话将会使用定义在环境或者注册中，它将会检查nls_session_parameters中的设置。 - If NLS_LENGTH_SEMANTICS is not set at client side or no alter session is done then the session will use the value found in NLS_INSTANCE_PARAMETERS.
如果nls_length_semantics没有在客户端定义，那么会话将在nls_instance_parameters中查找。(pfile/spfile) - The NLS_LENGTH_SEMANTICS parameter can be set at instance level in the init.ora or Spfile. You will then see the parameter change in NLS_INSTANCE_PARAMETERS.
更改nls_instance_parameters属性在init.ora或者spfile中。你可以使用alter system更改。 SQL>ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=spfile;
Note :
- Oracle advices to use explicit CHAR semantics in the SQL or PL/SQL syntax or to make sure your application does an alter session when connecting if CHAR semantics is required and the semantic is not defined explicit in SQL.
- A lot of people think simply setting NLS_LENGTH_SEMANTICS=CHAR in the spfile/init.ora is the only thing to do to change an existing db to NLS_LENGTH_SEMANTICS=CHAR, this is not true.
- Oracle recommends to NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance parameter file when possible.
NLS_LENGTH_SEMANTICS parameter to CHAR in the init.ora/spfile. - use NLS_LENGTH_SEMANTICS=BYTE in init.ora/spfile and explicit define CHAR semantics in SQL or PL/SQL (= use VARCHAR2(10 CHAR) etc )
- to have your application do an "ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR " when connecting if this application wants to use CHAR semantics .
- use an explicit “ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR” in the beginning of schema creation scripts if the SQL does not use explicit CHAR semantics ( …VARCHAR2(10 CHAR) …etc ) but uses the session setting ( …VARCHAR2(10) …etc ) and the itention is to have them created using CHAR semantics.
The whole idea is simply that many (third party / application install / even maybe some Oracle provided ) scripts still assume NLS_LENGTH_SEMANTICS=BYTE and by using NLS_LENGTH_SEMANTICS=BYTE as init.ora/spfile they will not be affected.This is not matter of “supported” it’s basically a matter of trying to avoid possible issues. There are currently no known issues with setting NLS_LENGTH_SEMANTICS=CHAR in the spfile/init.ora for Oracle RDBMS scripts for Oracle version 11.1.0.6 and higher.By explicit using CHAR semantics in SQL and PLS/SQL syntax or defining NLS_LENGTH_SEMANTICS=CHAR on session level each time when needed one also avoid problems who might arise from accidentally using a NLS_LENGTH_SEMANTICS=BYTE setting when CHAR is actually intended
在Oracle数据库中和Oracle FORM中都 有这样一个参数的设置，有必要澄清一下：
参数NLS_LENGTH_SEMANTICS的含义在Oracle文档中这样描述：
Oracl文档中的说明： Syntax: NLS_LENGTH_SEMANTICS = string Range of values: BYTE | CHAR NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected. NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may be required to use byte semantics in order to maintain compatibility with existing applications. NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.
翻译过来的意思就是该参数有BYTE和CHAR两种取值，使你能够使用这两种语义创建CHAR或VARCHAR2类型的数据库表列，而对现有的列没有影响。
二者的区别就是一个是按字符存放，另一个按字节存放；一般情况数据库都会使用UTF-8编码，一个汉字如果按字节存放，就需要3个字节。
可以看到数据库中参数NLS_LENGTH_SEMANTICS的设置 ，一般默认为‘BYTE’。
这是因为按字节存放，3个汉字就需要9个字节，如果把参数NLS_LENGTH_SEMANTICS设置为‘CHAR’，就可以将数据插入进去了。这是因为按数据库会按字符计数，而不是字节。
Oracle Form中：
ITEM属性设置 中也有Data Length Semantics这样一个属性设置，它有三种选择“NULL”、“CHAR”和“BYTE”，如果设置 为“NULL”则直接从数据库中获取，使用数据库的设置。设置为“CHAR”和“BYTE”则和上述数据库中的情况一样。经过测试发现，如果一个字符类型的ITEM的最大长度为100，则当Data Length Semantics设置为“CHAR”时，最多可以输入100个汉字或100个英文字符；当Data Length Semantics设置为“BYTE”时，最多可以输入33个汉字或100个英文字符。
举例如下：
SQL> show parameter nls_length
NAME TYPE VALUE
nls_length_semantics string BYTE
SQL> create table byte_test (a varchar2(3));
表已创建。
SQL> insert into byte_test values(‘甲骨文’);
insert into byte_test values(‘甲骨文’)
*
ERROR 位于第 1 行:
ORA-01401: inserted value too large for column
SQL> alter session set NLS_LENGTH_SEMANTICS=‘CHAR’;
会话已更改。
SQL> create table char_test (a varchar2(3));
表已创建。
SQL> insert into char_test values(‘甲骨文’);
已创建 1 行。
SQL> show parameter NLS_LENGTH_SEMANTICS
NAME TYPE VALUE
nls_length_semantics string CHAR
SQL> desc byte_test;
名称 是否为空? 类型
A VARCHAR2(3 BYTE)
SQL> desc char_test;
名称 是否为空? 类型
A VARCHAR2(3)
NLS_LENGTH_SEMANTICS
Property Description
Parameter type String
Syntax NLS_LENGTH_SEMANTICS = string
Example: NLS_LENGTH_SEMANTICS = ‘CHAR’
Default value BYTE
Modifiable ALTER SESSION
Range of values BYTE | CHAR
The session-level value of NLS_LENGTH_SEMANTICS specifies the default length semantics to use forVARCHAR2 and CHAR table columns, user-defined object attributes, and PL/SQL variables in database objects created in the session. This default may be overridden by the explicit length semantics qualifiers BYTE and CHAR in column, attribute, and variable definitions.
会话级别的nls_length_semantics说明默认的char和varchar2，用户定义的对象属性，和pl/sql变量在数据库对象创建的长度语法。默认值会被显示说明的参数值覆盖。
The instance-level value of NLS_LENGTH_SEMANTICS provides a default for the session-level value ifNLS_LENGTH_SEMANTICS it is not set explicitly by the database client through theNLS_LENGTH_SEMANTICS client environment variable (does not apply to JDBC Thin clients), or theALTER SESSION SET NLS_LENGTH_SEMANTICS statement.
实例级别的nls_length_semantics提供默认的会话级别的值，如果数据库客户端没有显示的设定。
NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based.
Sessions logged in as SYS do not use the NLS_LENGTH_SEMANTICS parameter. They use BYTE length semantics for all created objects unless overridden by the explicit BYTE and CHAR qualifiers in object definitions (SQL DDL statements).
sys用户不使用nls_length_semantics参数，他们默认使用BYTE，除非在DDL列属性的时候显示的指定BYTE或CHAR.
Caution:
Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.
oracle强烈建议你不要在实例或者服务参数文件中设置nls_length_semantics参数为CHAR。这是因为许多存在的安装脚本会在使用字符长度语义创建列的时候出现异常，导致运行失败，包括缓存溢出。
Any DBA wanting to use NLS_LENGTH_SEMANTICS.
NLS_LENGTH_SEMANTICS allows you to specify the length of a column datatype in terms of CHARacters rather than in terms of BYTEs. Typically this is when using an AL32UTF8 or other varying width NLS_CHARACTERSET database where one character is not always one byte. While using CHAR semantics has as such no added value in a 7/8 bit characterset it’s fully supported so any application code / table setup using CHAR can also be used in a 7/8bit characterset like US7ASCII/WE8MSWIN1252.
This parameter is a 9i (and up) feature and is not available in older releases.
nls_length_semantics允许你说明列类型的长度为CHAR而不是BYTES。典型的当使用AL32UTF8或者其他多字节字符类型。该参数在9i中出现。
- NLS_LENGTH_SEMANTICS cannot be set as a client side environment/registry parameter in 9i, from 10g onwards NLS_LENGTH_SEMANTICS can be set (please define it in UPPERCASE). If NLS_LENGTH_SEMANTICS is set at client side then any session started from that client will use the value defined in the environment/registry and it can be checked in NLS_SESSION_PARAMETERS.
9i客户端不能设置nls_length_semantics，从10g开始时可以的。如果在客户端设置了nls_length_semantics那么所有使用该客户端开始的会话将会使用定义在环境或者注册中，它将会检查nls_session_parameters中的设置。 - If NLS_LENGTH_SEMANTICS is not set at client side or no alter session is done then the session will use the value found in NLS_INSTANCE_PARAMETERS.
如果nls_length_semantics没有在客户端定义，那么会话将在nls_instance_parameters中查找。(pfile/spfile) - The NLS_LENGTH_SEMANTICS parameter can be set at instance level in the init.ora or Spfile. You will then see the parameter change in NLS_INSTANCE_PARAMETERS.
更改nls_instance_parameters属性在init.ora或者spfile中。你可以使用alter system更改。
SQL>ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=spfile;
Note : - Oracle advices to use explicit CHAR semantics in the SQL or PL/SQL syntax or to make sure your application does an alter session when connecting if CHAR semantics is required and the semantic is not defined explicit in SQL.
- A lot of people think simply setting NLS_LENGTH_SEMANTICS=CHAR in the spfile/init.ora is the only thing to do to change an existing db to NLS_LENGTH_SEMANTICS=CHAR, this is not true.
- Oracle recommends to NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance parameter file when possible.
NLS_LENGTH_SEMANTICS parameter to CHAR in the init.ora/spfile. - use NLS_LENGTH_SEMANTICS=BYTE in init.ora/spfile and explicit define CHAR semantics in SQL or PL/SQL (= use VARCHAR2(10 CHAR) etc )
- to have your application do an "ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR " when connecting if this application wants to use CHAR semantics .
- use an explicit “ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR” in the beginning of schema creation scripts if the SQL does not use explicit CHAR semantics ( …VARCHAR2(10 CHAR) …etc ) but uses the session setting ( …VARCHAR2(10) …etc ) and the itention is to have them created using CHAR semantics.
The whole idea is simply that many (third party / application install / even maybe some Oracle provided ) scripts still assume NLS_LENGTH_SEMANTICS=BYTE and by using NLS_LENGTH_SEMANTICS=BYTE as init.ora/spfile they will not be affected.This is not matter of “supported” it’s basically a matter of trying to avoid possible issues. There are currently no known issues with setting NLS_LENGTH_SEMANTICS=CHAR in the spfile/init.ora for Oracle RDBMS scripts for Oracle version 11.1.0.6 and higher.By explicit using CHAR semantics in SQL and PLS/SQL syntax or defining NLS_LENGTH_SEMANTICS=CHAR on session level each time when needed one also avoid problems who might arise from accidentally using a NLS_LENGTH_SEMANTICS=BYTE setting when CHAR is actually intended.
1.3.2.6 布尔类型(BOOLEAN)
布尔类型能存储逻辑值TRUE、FALSE和NULL(NULL代表缺失、未知或不可用的值)。只有逻辑操作符才允许应用在布尔变量上。
数据库SQL类型并不支持布尔类型，只有PL/SQL才支持。所以就不能往数据库中插入或从数据库中检索出布尔类型的值。
1.3.2.7 Datetime和Interval类型
Datetime就是日期时间类型，而Interval指的是时间的间隔。Datetime和Interval类型都由几个域组成，下表是对每个域及其它们对应的有效值描述：
域名称 有效日期时间值 有效间隔值
YEAR -4712 到 9999 (不包括0) 任意非零整数
MONTH 01 到 12 0 到 11
DAY 01 到 31 (根据当地的历法规则，
受MONTH和YEAR值的限制 任意非零整数
HOUR 00 到 23 0 到 23
MINUTE 00 到 59 0 到 59
SECOND 00 到 59.9(n)，
9(n)是秒小数部分的精度 0 to 59.9(n)，
9(n)间隔秒的小数部分的精度
TIMEZONE_HOUR -12 到 14 (随日光节约时间的变化而变化) 不可用
TIMEZONE_MINUTE 00 到 59 不可用
TIMEZONE_REGION 查看视图VTIMEZONE_NAMES 不可用
除了TIMESTAMP WITH LOCAL TIMEZONE以外，剩下的都是SQL92所支持的。
? DATE
DATE数据类型能够存储定长的日期时间。日期部分默认为当月的第一天；时间部分为午夜时间。函数SYSDATE能够返回当前的日期和时间。
提示：如果只进行日期的等值比较，忽略时间部分，可以使用函数TRUNC(date_variable)。
有效的日期范围是从公元前4721年1月1日到公元9999年12月31日。儒略日(Julian date)是自公元前4712年1月1日起经过的天数。我们可以使用日期模式"J"配合函数TO_DATE和TO_CHAR来把日期值转换成等值的儒略日。
在日期表达式中PL/SQL会自动地将格式为默认日期格式的字符值转成DATE类型值。默认的日期格式由Oracle的初始化参数NLS_DATE_FORMAT决定的。例如，默认格式可能是"DD-MON-YY"，它包含两位数字表示一个月中的第几日，月份名称的缩写和两位记年用的数字。
我们可以对日期进行加减运算。例如，下面的语句就能返回员工自被雇用日起，至今所经过的天数：
SELECT SYSDATE - hiredate
INTO days_worked
FROM emp
WHERE empno = 7499;
在算术表达式中，PL/SQL会将整数文字当作"日"来处理，如"SYSDATE + 1"就代表明天的时间。
一、 TIMESTAMP
? 它是精度更高的时间期数据，使用示例：
? Timestamp ：缺省时小数秒精度为6
? TIMESTAMP(n) ：指定小数秒精度为7
? Timestamp WITH TIME Zone
? Timestamp WITH LOCAL Time Zone
我们看看下面的试验：
Create Table ts_test
(dt Date,
ts Timestamp,
ts1 TIMESTAMP(7),
ts2 Timestamp WITH TIME Zone,
ts3 Timestamp WITH LOCAL Time Zone);
Insert Into Ts_Test
Select Sysdate, Sysdate, Sysdate, Sysdate, Sysdate From Dual;
Select * From Ts_Test
DT TS TS1 TS2 TS3
2008-9-7
17:29
07-9月 -08
05.29.50.000000
下午
07-9月 -08
05.29.50.0000000
下午
07-9月 -08
05.29.50.000000
下午 +08:00
07-9月 -08
05.29.50.000000
下午
在上面的例子中，我们创建了一个表TS_TEST，其中字段date的数据类型是date，ts 的
数据类型为Timestamp，ts1 的数据类型为Timestamp(7)，ts2 的数据类型为Timestamp WITH
TIME ZONE。
精度7指示小数秒的精度，如果不指定，小数秒的默认精度是6。
TIMESTAMP WITH TIME ZONE是TIMESTAMP的一个变量，它对TIMESTAMP值进行
一个时区转换，在本地时间和UTC 之间，小时和分钟的时区转换是不同的。
UTC代表协调世界时—以前的格林尼治标准时间。如果两个TIMESTAMP WITH TIME
ZONE在UTC中代表同一时刻，它们的值被认为是相同的，而不管存储在数据中的TIME
ZONE偏移。
因为TIMESTAMP WITH TIME ZONE也可以存储时区信息，它特别适合记录那些必须
组合或协调地理区域的日期信息。
例如，
TIMESTAMP ‘1999-04-15 8:00:00 -8:00’
与
TIMESTAMP ‘1999-04-15 11:00:00 -5:00’
是相同的。
美国西部标准时间 8:00 a.m. 和东部标准时间 11:00 a.m. 是相同的。
该时间也可以被指定为：
TIMESTAMP ‘1999-04-15 8:00:00 US/Pacific’
注：小数秒精度指定SECOND日期时间字段的小数部分数字的数目，其范围是0到9，默
认是6。
TIMESTAMP WITH LOCAL TIME数据类型，TIMESTAMP WITH LOCAL TIME
ZONE 是TIMESTAMP的另一个变量，它对TIMESTAMP值进行一个时区转换，存储在数据
库中的数据被格式化为数据库时区，时区的转换不被作为列数据的一部分存储；Oracle 以
本地会话时区返回数据，TIMESTAMP WITH LOCAL TIME ZONE数据类型被如下指定：
TIMESTAMP[(fractional_seconds_precision)]WITH LOCAL TIME ZONE。
不像TIMESTAMP WITH TIME ZONE，你可以指定TIMESTAMP WITH LOCAL TIME
ZONE类型作为一个主键或唯一键的一部分。在本地时间和UTC之间的时区转换 (小时或分
钟) 是不同的，对于TIMESTAMP WITH LOCAL TIME ZONE是非文字的。
注：小数秒精度指定SECOND日期时间字段的小数部分数字的数目，其范围是0到9，默
认是6。
例如：
CREATE TABLE time_example
(order_date TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO time_example VALUES(‘15-NOV-00 09:34:28 AM’);
SELECT *
FROM time_example;
order_date
15-NOV-00 09.34.28.000000 AM
TIMESTAMP WITH LOCAL TIME ZONE类型适合于两层应用程序，在其中你可以用客
户系统的时区显示日期和时间。
TIMESTAMP是对DATE的扩展，包含了年月日时分秒，语法如下：
TIMESTAMP[(precision)]
precision是可选参数，用于指定秒的小数部分数字个数。参数precision不可以是常量或变量，其有效范围是0到9，默认值是6。默认的时间戳(timestamp)格式是由Oracle初始化参NLS_TIMESTAMP_FORMAT决定的。
在下面的例子中，我们声明了一个TIMESTAMP类型的变量，然后为它赋值：
DECLARE
checkout TIMESTAMP ( 3 );
BEGIN
checkout := ‘1999-06-22 07:48:53.275’;
…
END;
这个例子中，秒的小数部分是0.275。
select systimestamp from dual;
1、 比较
oracle中TIMESTAMP与DATE比较
oracle数据库中timestamp数据类型精度
DATE数据类型
这个数据类型我们实在是太熟悉了，当我们需要表示日期和时间的话都会想到date类型。它可以存储月，年，日，世纪，时，分和秒。它典型地用来表示什么时候事情已经发生或将要发生。DATE数据类型的问题在于它表示两个事件发生时间间隔的度量粒度是秒。这个问题将在文章稍后讨论timestamp的时候被解决。可以使用TO_CHAR函数把DATE数据进行传统地包装，达到表示成多种格式的目的。
我见到的大多数人陷入的麻烦就是计算两个时间的间隔年数、月数、天数、小时数和秒数。你需要明白的是，当你进行两个日期的相减运算的时候，得到的是天数。你需要乘上每天的秒数（1天=86400秒），然后，你可以再次计算得到你想要的间隔数。下面就是我的解决方法，可以精确计算出两个时间的间隔。我明白这个例子可以更简短些，但是我是为了显示所有的数字来强调计算方式。
SELECT TO_CHAR(date1,‘MMDDYYYY:HH24:MI:SS’) date1,
TO_CHAR(date2,‘MMDDYYYY:HH24:MI:SS’) date2,
trunc(86400*(date2-date1))-60*(trunc((86400*(date2-date1))/60)) seconds,
trunc((86400*(date2-date1))/60)-60*(trunc(((86400*(date2-date1))/60)/60)) minutes,
trunc(((86400*(date2-date1))/60)/60)-24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,
trunc((((86400*(date2-date1))/60)/60)/24) days,
trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks
FROM date_table
DATE1 DATE2 SECONDS MINUTES HOURS DAYS WEEKS
----------------- ----------------- ---------- ---------- ---------- ---------- ----------
06202003:16:55:14 07082003:11:22:57 43 27 18 17 2
06262003:11:16:36 07082003:11:22:57 21 6 0 12 1
TIMESTAMP 数据类型
DATE数据类型的主要问题是它粒度不能足够区别出两个事件哪个先发生。ORACLE已经在DATE数据类型上扩展出来了TIMESTAMP数据类型，它包括了所有DATE数据类型的年月日时分秒的信息，而且包括了小数秒的信息。如果你想把DATE类型转换成TIMESTAMP类型，就使用CAST函数。
正如你看到的，在转换后的时间段尾部有了一段“.000000”。这是因为从date转换过来的时候，没有小数秒的信息，缺省为0。而且显示格式是按照参数NLS_TIMESTAMP_FORMAT定的缺省格式显示。当你把一个表中date类型字段的数据移到另一个表的timestamp类型字段中去的时候，可以直接写INSERT SELECT语句，oracle会自动为你做转换的。
TIMESTAMP数据的格式化显示和DATE 数据一样。注意，to_char函数支持date和timestamp，但是trunc却不支持TIMESTAMP数据类型。这已经清楚表明了在当两个时间的差别极度重要的情况下，使用TIMESTAMP数据类型要比DATE数据类型更确切
如果你想显示TIMESTAMP的小数秒信息，参考下面：
在上例中，我只现实了小数点后3位的内容。
计算timestamp间的数据差别要比老的date数据类型更容易。当你直接相减的话，看看会发生什么。结果将更容易理解，第一行的17天，18小时，27分钟和43秒。
SELECT time1, time2,
substr((time2-time1),instr((time2-time1),’ ‘)+7,2) seconds,
substr((time2-time1),instr((time2-time1),’ ‘)+4,2) minutes,
substr((time2-time1),instr((time2-time1),’ ‘)+1,2) hours,
trunc(to_number(substr((time2-time1),1,instr(time2-time1,’ ‘)))) days,
trunc(to_number(substr((time2-time1),1,instr(time2-time1,’ ')))/7) weeks
FROM date_table
TIME1 TIME2 SECONDS MINUTES HOURS DAYS WEEKS
------------------------- -------------------------- ------- ------- ----- ---- -----
06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43 27 18 17 2
06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21 06 00 12 1
这就意味着不再需要关心一天有多少秒在麻烦的计算中。因此，得到天数、月数、天数、时数、分钟数和秒数就成为用substr函数摘取出数字的事情了
系统日期和时间
为了得到系统时间，返回成date数据类型。你可以使用sysdate函数。
为了得到系统时间，返回成timestamp数据类型。你可以使用systimestamp函数。
你可以设置初始化参数FIXED_DATE指定sysdate函数返回一个固定值。这用在测试日期和时间敏感的代码。注意，这个参数对于systimestamp函数无效。
SQL> ALTER SYSTEM SET fixed_date = ‘2003-01-01-10:00:00’;
System altered.
SQL> select sysdate from dual;
SYSDATE
---------
01-JAN-03
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------
09-JUL-03 11.05.02.519000 AM -06:00
当使用date和timestamp类型的时候，选择是很清楚的。你可以随意处置date和timestamp类型。当你试图转换到更强大的timestamp的时候，需要注意，它们既有类似的地方，更有不同的地方，而足以造成破坏。两者在简洁和间隔尺寸方面各有优势，请合理选择。另外，date类型一般很少用，建议大家在产品里面所有的date数据类型全部改为timestamp。
2、 cast函数
cast 是进行类型转换的, 可以针对各种Oracle数据类型. 修改的是用户的数据类型，可以将date类型转换为timestamp类型的数据。
select cast(sysdate as timestamp) from dual;
select cast(‘321312’ as number(8,2)) from dual ;
select UTL_RAW.CAST_TO_RAW(‘123’) from dual;
二、 TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE扩展了TIMESTAMP，能够表现时区位移(time-zone displacement)。时区位移在本地时间和通用协调时间(UTC)中是不同的。语法如下：
TIMESTAMP[(precision)] WITH TIME ZONE
precision的用法同TIMESTAMP语法中的precision。默认格式由Oracle初始化参数NLS_TIMESTAMP_TZ_FORMAT决定。
下例中，我们声明一个TIMESTAMP WITH TIME ZONE类型的变量，然后为其赋值：
DECLARE
LOGOFF TIMESTAMP ( 3 ) WITH TIME ZONE;
BEGIN
LOGOFF := ‘1999-10-31 09:42:37.114 +02:00’;
…
END;
例子中时区位移是+02:00。我们还可以使用符号名称(symbolic name)来指定时区位移，名称可以是完整形式也可以是缩写形式，如"US/Pacific"和"PDT"，或是组合的形式。例如，下面的文字全都表现同一时间。第三种形式最可靠，因为它指定了切换到日光节约时间时的规则。
TIMESTAMP ‘1999-04-15 8:00:00 -8:00’
TIMESTAMP ‘1999-04-15 8:00:00 US/Pacific’
TIMESTAMP ‘1999-10-31 01:30:00 US/Pacific PDT’
我们可以在数据词典V$TIMEZONE_NAMES的TIMEZONE_REGION和TIMEZONE_ABBR字段中找到相应的时区名称和它的缩写。如果两个TIMESTAMP WITH TIME ZONE值在通用协调时间中的值一样，那么系统就会认为它们的值相同而忽略它们的时区位移。下例两个值被认为是相同的，因为在通用协调时间里，太平洋标准时间8:00 AM和(美国)东部时区11:00 AM是相同的：
‘1999-08-29 08:00:00 -8:00’
‘1999-08-29 11:00:00 -5:00’
? TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE是对TIMESTAMP WITH TIME ZONE的扩展，它的语法如下：
TIMESTAMP[(precision)] WITH LOCAL TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE的用途和TIMESTAMP WITH TIME ZONE相似，它们不同之处在于，当我们往数据库中插入TIMESTAMP WITH LOCAL TIME ZONE类型数据的时候，数据会被转成数据库的时区，并且时区位移并不会存放在数据库中。当进行数据检索时，Oracle会按我们本地会话的时区设置返回值。下面就是一个使用TIMESTAMP WITH LOCAL TIME ZONE的例子：
DECLARE
LOGOFF TIMESTAMP ( 3 ) WITH LOCAL TIME ZONE;
BEGIN
NULL;
…
END;
我们不可以用文字值为这种类型的变量赋值。
三、 INTERVAL
1、 INTERVAL YEAR TO MONTH
? 使用语法如下：
INTERVAL YEAR [(year_precision)] TO MONTH
? 主要用于求日期n年、m月后的日期是那一天
? 是普通Data日期型的补充
INTERVAL YEAR TO MONTH用年和月日期时间字段存储一段时间。
用INTERVAL YEAR TO MONTH表示两个日期时间值的差，该差值只有年和月的部分。
例如，你可能用该值设置一个往后120个月的提醒日期，或检查是否从某个特定的日期后6
月已过去。
指定 INTERVAL YEAR TO MONTH 如下语法：
INTERVAL YEAR [(year_precision)] TO MONTH
year_precision 是在YEAR日期时间字段中数字的数目，年精度的默认值是2。
例如：
CREATE TABLE te1
(loan_duration INTERVAL YEAR (3) TO MONTH);
INSERT INTO te1 (loan_duration)
VALUES (INTERVAL ‘120’ MONTH(3));
SELECT TO_CHAR( sysdate+loan_duration, ‘dd-mon-yyyy’)
FROM te1;
TO_CHAR(SYSDATE+LOAN_DURATION,
07-9月 -2018
前面的部分要大于后面的部分，例如：INTERVAL ‘0-1’ MONTH TO YEAR 是无效
的，必须写成：INTERVAL ‘0-1’ YEAR TO MONTH。
我们可以使用INTERVAL YEAR TO MONTH类型用来保存年月的间隔，语法如下：
INTERVAL YEAR[(precision)] TO MONTH
precision指定间隔的年数。参数precision不能是常量或变量，其范围在1到4之间，默认值是2，下面的例子中声明了一个INTERVAL YEAR TO MONTH类型的变量，并把间隔值101年3个月赋给它：
DECLARE
lifetime INTERVAL YEAR(3)TO MONTH;
BEGIN
lifetime := INTERVAL ‘101-3’ YEAR TO MONTH; – interval literal
lifetime := ‘101-3’; – implicit conversion from character type
lifetime := INTERVAL ‘101’ YEAR; – Can specify just the years
lifetime := INTERVAL ‘3’ MONTH; – Can specify just the months
…
END;
2、 INTERVAL DAY TO SECOND
INTERVAL DAY TO SECOND
? 使用语法：
INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]
? 主要用于求日期n天、m小时后的准确时间
? 间隔型数据无法直接查看，必须和一个日期进行运行后才有效。
INTERVAL DAY TO SECOND根据天、小时、分和秒存储一段时间。用INTERVAL DAY
TO SECOND来表示两个日期时间值精确的差。例如，你可能用该值设置一个往后36个小时
的提醒，或记录一个赛跑的开始和结束之间的时间。为了表示很长的时间跨度，包括很多年，
用很高的精度，你可以用一个很大的值表示天的一部分。
指定 INTERVAL DAY TO SECOND 如下：
INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]
day_precision 是在DAY日期时间字段中数字的数目，可接受的值的范围是0到9，默认是2。
fractional_seconds_precision 是在SECOND日期时间字段中数字的数目，可接受的值的范围是0到9，默认是6。
例如：
CREATE TABLE te2
(day_duration INTERVAL DAY (3) TO SECOND);
INSERT INTO te2 (day_duration)
VALUES (INTERVAL ‘180’ DAY(3));
SELECT sysdate + day_duration “Half Year”
FROM te2;
Half Year
2009-3-6 17:38
我们可以使用INTERVAL DAY TO SECOND数据类型存储和操作天、小时、分钟和秒，语法如下：
INTERVAL DAY[(leading_precision)] TO SECOND[(fractional_seconds_precision)]
leading_precision和fractional_seconds_precision分别指定了天数和秒数。这两个值都不可以用常量或变量指定，且只能使用范围在0到9之间的整数文字为其赋值。它们的默认值分别为2和6。下面的例子中，我们声明了一个INTERVAL DAY TO SECOND类型的变量：
DECLARE
lag_time INTERVAL DAY(3)TO SECOND(3);
BEGIN
IF lag_time > INTERVAL ‘6’ DAY THEN …
…
END;
Oracle语法:
INTERVAL ‘{ integer | integer time_expr | time_expr }’
{ { DAY | HOUR | MINUTE } [ ( leading_precision ) ]
| SECOND [ ( leading_precision [, fractional_seconds_precision ] ) ] }
[ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]
leading_precision值的范围是0到9, 默认是2. time_expr的格式为:HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], n表示微秒.
该类型与INTERVAL YEAR TO MONTH有很多相似的地方,建议先看INTERVAL YEAR TO MONTH再看该文.
范围值:
HOUR: 0 to 23
MINUTE: 0 to 59
SECOND: 0 to 59.999999999
eg:
INTERVAL ‘4 5:12:10.222’ DAY TO SECOND(3)
表示: 4天5小时12分10.222秒
INTERVAL ‘4 5:12’ DAY TO MINUTE
表示: 4天5小时12分
INTERVAL ‘400 5’ DAY(3) TO HOUR
表示: 400天5小时, 400为3为精度,所以"DAY(3)", 注意默认值为2.
INTERVAL ‘400’ DAY(3)
表示: 400天
INTERVAL ‘11:12:10.2222222’ HOUR TO SECOND(7)
表示: 11小时12分10.2222222秒
INTERVAL ‘11:20’ HOUR TO MINUTE
表示: 11小时20分
INTERVAL ‘10’ HOUR
表示: 10小时
INTERVAL ‘10:22’ MINUTE TO SECOND
表示: 10分22秒
INTERVAL ‘10’ MINUTE
表示: 10分
INTERVAL ‘4’ DAY
表示: 4天
INTERVAL ‘25’ HOUR
表示: 25小时
INTERVAL ‘40’ MINUTE
表示: 40分
INTERVAL ‘120’ HOUR(3)
表示: 120小时
INTERVAL ‘30.12345’ SECOND(2,4)
表示: 30.1235秒, 因为该地方秒的后面精度设置为4, 要进行四舍五入.
INTERVAL ‘20’ DAY - INTERVAL ‘240’ HOUR = INTERVAL ‘10-0’ DAY TO SECOND
表示: 20天 - 240小时 = 10天0秒
INTERVAL DAY TO SECOND类型存储两个TIMESTAMP之间的时间差异，用日期、小时、分钟、秒钟形式表示。该数据类型的内部代码是183，长度位11字节：
l 4个字节表示天数（增加0X80000000偏移量）
l 小时、分钟、秒钟各用一个字节表示（增加60偏移量）
l 4个字节表示秒钟的小时差异（增加0X80000000偏移量）
以下是一个例子：
SQL> alter table testTimeStamp add f interval day to second ;
表已更改。
SQL> update testTimeStamp set f=(select interval ‘5’ day + interval ‘10’ second from dual);
已更新3行。
SQL> commit;
提交完成。
SQL> select dump(f,16) from testTimeStamp;
DUMP(F,16)
Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0
Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0
Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0
日期：0X80000005-0X80000000=5
小时：60－60＝0
分钟：60－60＝0
秒钟：70－60＝10
秒钟小数部分：0X80000000-0X80000000=0
INTERVAL DAY TO SECOND类型可以用来存储单位为天和秒的时间间隔。下面这条语句创建一个名为promotions的表，用来存储促销信息。promotions表包含了一个INTERVAL DAY TO SECOND类型的列duration，该列用来记录促销有效的时间间隔：
CREATE TABLE promotions (
promotion_id INTEGER CONSTRAINT promotions_pk PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
duration INTERVAL DAY(3) TO SECOND (4)
);
注意此处指定了duration列中天的精度为3，秒的小数部分精度为4。这就是说可以为该列的天存储3位数字，而为该列的秒最多可以在小数点右边存储4位数字。
要向数据库提供一个INTERVAL DAY TO SECOND字面值，可以使用下面的简化语法：
INTERVAL ‘[+|-][ d] [ h[: m[: s]]]’ [DAY[( days_precision)]])
[TO HOUR | MINUTE | SECOND[( seconds_precision)]]
其中
● + 或 - 是一个可选的指示符，用来说明时间间隔是正数还是负数(默认为正数)。
● d 是时间间隔的天数。
● h 是一个可选参数，表示时间间隔的小时数。如果指定了天和小时，必须在INTERVAL子句中包含TO HOUR。
● h 是一个可选参数，表示时间间隔的分钟数。如果指定了天和分，必须在INTERVAL子句中包含TO MINUTES。
● s 是一个可选参数，表示时间间隔的秒数。如果指定了天和秒，必须在INTERVAL子句中包含TO SECOND。
● days_precision是一个可选参数，用来说明天数的精度(默认值为2)。
● seconds_precision是一个可选参数，用来说明秒的精度(默认值为6)。
表5-12给出了几个INTERVAL DAY TO SECOND类型的时间间隔字面量的例子。
表5-12 时间间隔字面量的例子
时间间隔字面量 说明
INTERVAL ‘3’ DAY 时间间隔为3天
INTERVAL ‘2’ HOUR 时间间隔为2小时
INTERVAL ‘25’ MINUTE 时间间隔为25分钟
INTERVAL ‘45’ SECOND 时间间隔为45秒
INTERVAL ‘3 2’ DAY TO HOUR 时间间隔为3天零2小时
INTERVAL ‘3 2:25’ DAY TO MINUTE 时间间隔为3天零2小时25分
INTERVAL ‘3 2:25:45’ DAY TO SECOND 时间间隔为3天零2小时25分45秒
INTERVAL ‘123 2:25:45.12’ DAY(3)
TO SECOND(2) 时间间隔为123天零2小时25分45.12秒； 天的精度是3位数字，秒的小数部分的精度是2位数字
INTERVAL ‘3 2:00:45’ DAY TO SECOND 时间间隔为3天2小时0分45秒
INTERVAL ‘-3 2:25:45’ DAY TO SECOND 时间间隔为负数，值为3天零2小时25分45秒
INTERVAL ‘1234 2:25:45’ DAY(3)
TO SECOND 时间间隔无效，因为天的位数超过了指定的精度3
INTERVAL ‘123 2:25:45.123’ DAY
TO SECOND(2) 时间间隔无效，因为秒的小数部分的位数超过了指定的精度2
SELECT INTERVAL ‘300’ MONTH,
INTERVAL ‘3’ MONTH,
INTERVAL ‘0-1’ YEAR TO MONTH,
INTERVAL ‘54-2’ YEAR TO MONTH,
INTERVAL ‘11:12:10.1234567’ HOUR TO SECOND,
INTERVAL ‘6’ DAY,
INTERVAL ‘120’ HOUR(3),
INTERVAL ‘123 2 :25 :45.123’ DAY(3) TO SECOND(3)
FROM dual;
下面这个INSERT语句向promotions表添加一行记录：
INSERT INTO promotions (promotion_id, name, duration)
VALUES (1, ‘10% off Z Files’, INTERVAL ‘3’ DAY);
INSERT INTO promotions (promotion_id, name, duration)
VALUES (2, ‘20% off Pop 3’, INTERVAL ‘2’ HOUR);
INSERT INTO promotions (promotion_id, name, duration)
VALUES (3, ‘30% off Modern Science’, INTERVAL ‘25’ MINUTE);
INSERT INTO promotions (promotion_id, name, duration)
VALUES (4, ‘20% off Tank War’, INTERVAL ‘45’ SECOND);
INSERT INTO promotions (promotion_id, name, duration)
VALUES (5, ‘10% off Chemistry’, INTERVAL ‘3 2:25’ DAY TO MINUTE);
INSERT INTO promotions (promotion_id, name, duration)
VALUES (6, ‘20% off Creative Yell’, INTERVAL ‘3 2:25:45’ DAY TO SECOND);
INSERT INTO promotions (promotion_id, name, duration)
VALUES (7, ‘15% off My Front Line’,
INTERVAL ‘123 2:25:45.12’ DAY(3) TO SECOND(2));
下面这个查询对promotions表进行检索，注意duration列值的格式化：
SELECT *
FROM promotions;
PROMOTION_ID NAME DURATION
1 10% off Z Files +003 00:00:00.0000
2 20% off Pop 3 +000 02:00:00.0000
3 30% off Modern Science +000 00:25:00.0000
4 20% off Tank War +000 00:00:45.0000
5 10% off Chemistry +003 02:25:00.0000
6 20% off Creative Yell +003 02:25:45.0000
7 15% off My Front Line +123 02:25:45.1200
四、 Datetime和Interval算法
PL/SQL允许我们创建日期时间和间隔表达式。下面列表显示了可以在表达式中使用的操作符：
操作数 1 操作符 操作数 2 返回类型
日期时间 + 时间间隔 日期时间
日期时间 - 时间间隔 日期时间
时间间隔 + 日期时间 日期时间
日期时间 - 日期时间 时间间隔
时间间隔 + 时间间隔 时间间隔
时间间隔 - 时间间隔 时间间隔
时间间隔 * 数字 时间间隔
数字 * 时间间隔 时间间隔
时间间隔 / 数字 时间间隔
我们还可以使用各种函数来操作日期时间类型，如EXTRACT等。
8、使用日期和时间子类型来避免"切断"问题
对于某些日期和时间类型的默认精度都要小于它们对应的最大精度。例如，DAY TO SECOND的默认精度是DAY(2) TO SECOND(6)，而最大精度是DAY(9) TO SECOND(9)。为了避免在使用这些类型进行赋值和传递参数时丢失精度，我们可以声明它们的子类型，这些类型都采用了最大的精度：
TIMESTAMP_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
YMINTERVAL_UNCONSTRAINED
DSINTERVAL_UNCONSTRAINED
1.4 LOB类型
LOB(large object)数据类型BFILE、BLOB、CLOB和NCLOB可以最大存储4G的无结构数据(例如：文本、图形、视频剪辑和音频等)块。并且，它们允许高效地随机地分段访问数据。
LOB类型和LONG、LONG RAW类型相比有几个不同的地方。比如，LOB(除了NCOLB)可以作为对象类型的一个属性，但LONG类型不可以。LOB的最大值是4G，而LONG只有2G。LOB支持随机访问数据，但LONG只支持顺序访问。
LOB类型中可以存储了LOB定位器，它能够指向存放于外部文件中的"大对象"，in-line (inside the row)或out-of-line (outside the row)的形式。BLOB、CLOB、NCLOB或BFILE类型的数据库字段存储了定位器。其中BLOB、CLOB和NCLOB的数据存在数据库中，in-line (inside the row)或out-of-line (outside the row)的形式，而BFILE数据存在数据库之外的操作系统文件中。
PL/SQL是通过定位器来操作LOB的。例如，当我们查询出一个BLOB值，只有定位器被返回。如果在事务中得到定位器，LOB定位器就会包含事务的ID号，这样我们就不能在另外一个事务中更新LOB内容了。同样，我们也不能在一个会话中操作另外一个会话中的定位器。
从9i开始，我们也可以把CLOB类型转成CHAR和VARCHAR2类型或是把BLOB转成RAW，反之亦然，这样，我们就能在大多数SQL和PL/SQL语句和函数中使用LOB类型了。要读、写和分段的操作LOB，我们可以使用Oracle系统包DBMS_LOB。
oracle中支持4种类型的LOB（large object）
CLOB：字符LOB。这种类型用于存储大量的文本信息，如xml或者只是纯文本。这个数据类型需要进行字符集转换，也就是说，在获取时，这个字段中的字符会从数据库的字符集转换为客户的字符集，而在修改时会总客户的字符集转换为数据库的字符集。
NCLOB：这是另一种类型的LOB。存储在这一列中的数据所采用的字符集是数据库的国家字符集，而不是数据库的默认字符集。
BLOB：二进制LOB。这种类型用于存储大量的二进制信息，如处理文档、图像和你能想象的任何其他数据。它不会执行字符集转换。应用向BLOB写入什么位和字节，BLOB就会返回什么位和字节。
BFILE：二进制文件LOB。这与其说是一个数据库存储实体，不如说是一个指针。带BFILE列的数据库存储的只是操作系统的某个文件的一个指针。这个文件在数据库之外维护，根本不是数据库的一部分。BFILE提供了文件内容的只读访问。
因此可以将LOB分为内部LOB（CLOB、NCLOB、BLOB），外部LOB（BFILE内容存储在数据库之外，BFILE列只是一个指向操作系统中的文件的指针）
内部LOB字段在数据库内部并不是在表中记录数据（也可以设置为在表中记录（in row（最多4000字节，超过就会移出）））。LOB字段内部存储分为3个部分，如下图所示：
table T的Txt字段是CLOB类型的，LOB内部分为LOBINDEX、LOBSEGMENT，表T的Txt字段存储的为指向LOBINDEX的地址，LOBINDEX字段存储的为LOBSEGMENT的索引。所以查找的顺序是Txt->LOBINDEX->LOBSEGMENT。
如果是行内（IN ROW）存储，那就和varchar2没有区别了，行内存储要比行外存储块。这里只是浅显的了解了LOB字段的内部存储，深入的参数不记录了
一、 BFILE
BFILE数据类型用于存储二进制对象，它将存储的内容放到操作系统的文件中，而不是数据库内。每个BFILE变量都存储一个文件定位器，它指向服务器上的一个大的二进制文件。定位器包含目录别名，该别名给出了文件全路径。
BFILE类型是只读的，而且它的大小要依赖于系统，不能超过4G。我们的DBA要确保给定的BFILE存在且Oracle有读取它的权限。
BFILE并不参与事务，是不可恢复，不能被复制。能够被打开的BFILE最大数是由Oracle初始化参数SESSION_MAX_OPEN_FILES决定的。
第一步:创建一个目录对象用来存储数据
CREATE OR REPLACE DIRECTORY record_bfile AS ‘/home/oracle/record_bfile/bfile.txt’
第二步:建立一个bfile表
CREATE TABLE articles
(
author_id NUMBER(4),
author_name VARCHAR2 (14) ,
release_date date,
article_content BFILE
);
第三步:插入记录
INSERT INTO articles
VALUES(1111,‘SMITH’,to_date(‘2010-07-11’,‘YYYY-MM-DD’),bfilename(‘record_bfile’,‘bfile.txt’));
You want to access employee details contained in flat files as part of the EMPLOYEE table. You plan
to add a new column to the EMPLOYEE table to achieve this.
Which data type would you use for the new column?
A.CLOB
B.BLOB
C.BFILE
D.LONG RAW
Answer: C
题目解答：表的数据放到file中，要么外部表，要么bfile列存储 本题是列 只能C
BFILE的用法
(1)、create or replace directory
BFILE_TEST
as
‘/oracle/oradata/bfiles’;
(2)、grant read on directory BFILE_TEST to SCOTT;
(3)、host ls -l /oracle/oradata/bfiles/1.TXT
(4)、connect SCOTT/TIGER
create table BFILES (ID number, TEXT bfile );
(5)、insert into BFILES values ( 1,
bfilename ( ‘BFILE_TEST’, ‘1.TXT’ ) );
二、 BLOB、CLOB和NCLOB
BLOB数据类型可以在数据库中存放不超过4G的大型二进制对象；CLOB和NCLOB可以在数据库中分别存储大块CHAR类型和NCHAR类型的字符数据，都支持定宽和变宽字符集。同BFILE一样，这三个类型也都储存定位器，指向各自类型的一个大数据块。数据大小都不能超过4G。BLOB、CLOB和NCLOB都可以在事务中使用，能够被恢复和复制。DBMS_LOB包可以对它们更改过的内容进行提交或回滚操作。BLOB、CLOB和NCLOB的定位器都可以跨事务使用，但不能跨会话使用。
1.4.2 clob和字符串
Oracle中CLOB和BLOB字段虽说在开发中满足了存放超大内容的要求，但是在一些简单使用中确频频带来麻烦。CLOB中存放的是指针，并不能直接取到实际值。而SQLServer中的text字段就很方便，可以直接拿来与需要的字符串比对，象什么等于呀小于呀Like呀不在话下。可是换成Oracle就麻烦死了，要开辟一个缓存，把内容一段段读取出来后转换，难道写个where条件都这么复杂？经过多方寻求资料，终于发现一个方便简单的方法：利用dbms_lob 包中的方法（放心，内置的）instr和substr 。具体的介绍如下：
instr函数与substr函数
instr函数用于从指定的位置开始，从大型对象中查找第N个与模式匹配的字符串。
用于查找内部大对象中的字符串的instr函数语法如下：
1: dbms_lob.instr(
2: lob_loc in clob character set any_cs,
3: pattern in varchar2 character set lob_loc%charset,
4: offset in integer:=1,
5: nth in integer := 1)
6: return integer;
7:
lob_loc为内部大对象的定位器
pattern是要匹配的模式
offset是要搜索匹配文件的开始位置
nth是要进行的第N次匹配
substr函数
substr函数用于从大对象中抽取指定数码的字节。当我们只需要大对象的一部分时，通常使用这个函数。
操作内部大对象的substr函数语法如下：
其中各个参数的含义如下：
lob_loc是substr函数要操作的大型对象定位器
amount是要从大型对象中抽取的字节数
offset是指从大型对象的什么位置开始抽取数据。
如果从大型对象中抽取数据成功，则这个函数返回一个 raw 值。如果有一下情况，则返回null：
1 任何输入参数尾null
2 amount < 1
3 amount > 32767
4 offset < 1
5 offset > LOBMAXSIZE
1: dbms_lob.instr(
2: lob_loc in blob,
3: pattern in raw,
4: offset in integer := 1;
5: nth in integer := 1)
6: return integer;
7: dbms_lob.substr(
8: lob_loc in blob,
9: amount in integer := 32767,
10: offset in integer := 1)
11: return raw;
12: dbms_lob.substr(
13: lob_loc in clob character set any_cs,
14: amount in integer := 32767,
15: offset in integer := 1)
16: return varchar2 character set lob_loc%charset;
照下面示例，很容易看懂：
DECLARE
source_lob CLOB;
pattern VARCHAR2(6) := ‘Oracle’;
start_location INTEGER := 1;
nth_occurrence INTEGER := 1;
position INTEGER;
buffer VARCHAR2(100);
BEGIN
SELECT clob_locator INTO source_lob FROM mylobs WHERE lob_index = 4;
position := dbms_lob.instr(source_lob,
pattern,
start_location,
nth_occurrence);
dbms_output.put_line(‘The first occurrence starts at position:’ ||
position);
nth_occurrence := 2;
SELECT clob_locator INTO source_lob FROM mylobs WHERE lob_index = 4;
position := dbms_lob.instr(source_lob,
pattern,
start_location,
nth_occurrence);
dbms_output.put_line('The first occurrence starts at position:' ||
position);
SELECT clob_locator INTO source_lob FROM mylobs WHERE lob_index = 5;
buffer := dbms_lob.substr(source_lob, 9, start_location);
dbms_output.put_line('The substring extracted is: ' || buffer);
END;
输出结果为：
The first occurrence starts at position:8
The first occurrence starts at position:24
The substring extracted is: Oracle 9i
drop table t;
create table t(x int, y clob);
insert into t values(1, rpad(’’, 5000, '’));
往oracle函CLOB、LONG字段类型的数据表插入值超过4000字节时，在执行INSERT INTO提示：ORA-01704:文字字符串过长错误，这种情况下，如果想用SQL实现的话，可以通过ORACLE存储过程实现。
–处理方法一：用EXECUTE IMMEDIATE 实现
–ASQL：带参数的INSERT INTO 语句
–ALongVar1 : 参数值，大于4000字节
CREATEORREPLACEPROCEDUREEXEC_SQL1(ASQL varchar2, ALongVar1 clob)
AS
BEGIN
EXECUTEIMMEDIATE ASQL USING ALongValue;
ENDEXEC_SQL1;
–处理方法二：用Dbms_sql接口实现
–ASQL：带参数的INSERT INTO 语句
–ALongVar1 : 参数值，大于4000字节
CREATEORREPLACEPROCEDUREEXEC_SQL(ASQL clob, ALongValue clob)
AS
The_c1 Integer;
The_result Integer;–dml_sql_result
BEGIN
The_C1 :=Dbms_sql.open_cursor;
Dbms_sql.parse(the_C1, ASQL, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE
(The_c1, ‘:LongValue’, ALongValue);
The_result:=Dbms_sql.execute(The_c1);
Dbms_sql.close_cursor(The_C1);
ENDEXEC_SQL;
1.4.3 oracle中可以用多种方法来检索或操作lob数据
在oracle中，有4个大对象（lobs）类型可用，分别是blob,clob,bfile,nclob。
下面是对lob数据类型的简单介绍。
blob:二进制lob，为二进制数据，最长可达4GB，存贮在数据库中。
clob:字符lob,字符数据,最长可以达到4GB,存贮在数据库中。
bfile:二进制文件;存贮在数据库之外的只读型二进制数据，最大长度由操作系统限制。
nclob：支持对字节字符集合(nultibyte characterset)的一个clob列。
对于如何检索和操作这些lob数据一直是oracle数据库开发者经常碰到的问题。下面我将在oracle对lob数据处理的一些方法和技巧，介绍给读者，希望能够对读者以后的开发有所帮助。
oracle中可以用多种方法来检索或操作lob数据。通常的处理方法是通过dbms_lob包。
其他的方法包括使用api(application programminginterfaces)应用程序接口和oci（oracle call interface）oracle调用接口程序。
一、在oracle开发环境中我们可以用dbms_lob包来处理！dbms_lob包功能强大，简单应用。既可以用来读取内部的lob对象，也可以用来处理bfile对象。但处理两者之间，还有一点差别。处理内部lob对象（blob,clob）时，可以进行读和写，但处理外部lob对象bfile时，只能进行读操作，写的操作可以用pl/sql处理。另外用sql也可以处理lob，但要注意sql仅可以处理整个lob，不能操作lob的数据片。
在dbms_lob包中内建了read(),append,write()，erase()，copy(),getlength()，substr()等函数，可以很方便地操作lob对象。这里不做深入讨论，读者可以参看相关的书籍。
对于pl/sql，下面介绍一种技巧，用动态的pl/sql语句处理clob对象来传替表名！
example 1.
动态PL/SQL,对CLOB字段操作可传递表名table_name，表的唯一标志字段名field_id，clob字段名field_name记录号v_id，开始处理字符的位置v_pos，传入的字符串变量v_clob
修改CLOB的PL/SQL过程：updateclob
create or replace procedure updateclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,v_id in number,
v_pos in number,
v_clob in varchar2)
is
lobloc clob;
c_clob varchar2(32767);
amt binary_integer;
pos binary_integer;
query_str varchar2(1000);
begin
pos:=v_pos*32766+1;
amt := length(v_clob);
c_clob:=v_clob;
query_str :=’‘select ‘’||field_name||’‘from ‘’||table_name||’’
where ‘’||field_id||’’= :id for update ‘’;
–initialize buffer with data to be inserted or updated
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
–from pos position, write 32766 varchar2 into lobloc
dbms_lob.write(lobloc, amt, pos, c_clob);
commit;
exception
when others then
rollback;
end;
l /用法说明：
在插入或修改以前，先把其它字段插入或修改，CLOB字段设置为空empty_clob()，
然后调用以上的过程插入大于2048到32766个字符。
如果需要插入大于32767个字符，编一个循环即可解决问题。
查询CLOB的PL/SQL函数：getclob
create or replace function getclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number) return varchar2
is
lobloc clob;
buffer varchar2(32767);
amount number := 2000;
offset number := 1;
query_str varchar2(1000);
begin
query_str :=’‘select ‘’||field_name||’’ from ‘’||table_name||’’
where ‘’||field_id||’’= :id ‘’;
–initialize buffer with data to be found
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
offset:=offset+(v_pos-1)*2000;
–read 2000 varchar2 from the buffer
dbms_lob.read(lobloc,amount,offset,buffer);
return buffer;
exception
when no_data_found then
return buffer;
end;
l 用法说明：
用select getclob(table_name,field_id,field_name,v_id,v_pos) as
partstr from dual;
可以从CLOB字段中取2000个字符到partstr中，
编一个循环可以把partstr组合成dbms_lob.getlength(field_name)长度的目标字符串。
二、对于在其他不同的开发环境，例如vc,vb,pb,java等环境下对lob的处理，处理方法不尽相同，在这里将简要举几个例子来说明不在oracle开发环境下对lob的处理。
（一） 在pb中的处理
exampler 2.
string ls_path,ls_filename,ls_jhdh
long ll_num,ll_count,rtn
blob ole_blob
ll_num=dw_lb.getrow()
if ll_num>0 then ls_jhdh=dw_lb.object.ct_njhdh[ll_num]
select count(*) into :ll_count from sj_jh_jhfjb where
ct_jhdlxbh=’‘1’’ and ct_jhdh=:ls_jhdh and ct_jdlxbh=:is_jdlx;
if ll_count>0 then
rtn=messagebox(“提示”,“是否要修改此附件”,question!,yesno!,1)
if rtn=1 then
SELECTBLOB ct_jhfjnr INTO le_blob from sj_jh_jhfjb where
ct_jhdlxbh=’‘1’’ and ct_jhdh=:ls_jhdh and ct_jdlxbh=:is_jdlx;
ole_1.objectdata =ole_blob
If ole_1.activate(offsite!) <> 0 Then
Messagebox(“OLE Activate”,“不能**”)
Return -1
end If
end if
else
messagebox(“提示”,“没有附件”)
end if
end if
（二）在vb中的处理
在vb中处理大对象，一般可以用OO4O(oracle objects for
ole)来处理大对象。这里介绍一种不用0040处理大对象blob的方法。
下面这段程序可以将一个文件（文本文件，doc文件，图象文件等）保存到数据库中，并可以将其从数据库读出
需要两个commandbutton
cmd1 名称 cmdsave caption 保存
cmd2 名称 cmdread caption 读取
一个cmddialog控件
同时需要创建一张表t_demo(字段id 类型 number,;字段text 类型 blob;)
exmple 3.
Option Explicit
Dim rn As ADODB.Connection
Public Function CreateDataSource(DataSource As String, UserID
As String, Password As String) As Boolean
On Error GoTo DbConErr:
Set rn = New ADODB.Connection
With rn
.ConnectionString = “Provider=OraOledb.Oracle.1;” & _
“password=” & Password & “;” & _
“User ID =” & UserID & “;” & _
“Data Source=” & DataSource & “;” & _
“Locale Identifier=2052”
.Open
End With
CreateDataSource = True
Exit Function
DbConErr:
CreateDataSource = False
End Function
Private Sub cmdRead_Click()
Dim rs As New ADODB.Recordset
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = “select * from t_demo”
rs.Open
ComDlgDir.DialogTitle = “保存文件”
ComDlgDir.Filter = “.”
ComDlgDir.ShowSave
Call BlobToFile(rs.Fields(“text”), ComDlgDir.filename)
Set rs = Nothing
Exit Sub
Set rs = Nothing
End Sub
Private Sub cmdsave_Click()
Dim rs As New ADODB.Recordset
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = “select * from t_demo”
rs.Open
rs.AddNew
ComDlgDir.DialogTitle = “选取文件”
ComDlgDir.ShowOpen
rs.Fields(“id”).Value = 1
If ComDlgDir.filename <> “” Then
Call FileToBlob(rs.Fields(“text”), ComDlgDir.filename)
rs.Update
End If
Set rs = Nothing
Exit Sub
Set rs = Nothing
End Sub
Private Sub Form_Load()
If Not CreateDataSource(“sid”, “systemp”, “manager”) Then
MsgBox “Connection failure!”
End If
End Sub
fld As ADODB.Field, filename As String, Optional ChunkSize As
Long = 8192)
Dim fnum As Integer, bytesleft As Long, bytes As Long
Dim tmp() As Byte
If (fld.Attributes And adFldLong) = 0 Then
Err.Raise 1001, , “field doesn’'t support the GetChunk method.”
End If
If Dir$(filename) = “” Then Err.Raise 53, , “File not found”
fnum = FreeFile
Open filename For Binary As fnum
bytesleft = LOF(fnum)
Do While bytesleft
bytes = bytesleft
If bytes > ChunkSize Then bytes = ChunkSize
ReDim tmp(1 To bytes) As Byte
Get fnum, , tmp
fld.AppendChunk tmp
bytesleft = bytesleft - bytes
Loop
Close #fnum
End Sub
Sub BlobToFile(fld As ADODB.Field, filename A
1.5 用户自定义子类型
每个PL/SQL基类型都有对应的值集合和操作符集合。子类同样会指定同其基类型相同的值集合和操作符集合的子集作为它自己的值集合和操作符集合。所以说子类并不是一个新类型，它只是在基类的基础上添加了一个可选的约束。
子类可以增加可读性和兼容性。PL/SQL在STANDARD包里预定义了一些子类型。如下例：
SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38, 0); – allows only whole numbers
子类型CHARACTER和基类型完全一样，所以CHARACTER是一个未作约束的子类型。但是，子类型INTEGER将基类NUMBER的值集合的子集作为自己的值集合，所以INTEGER是一个约束的子类型。
1、定义子类型
我们可以在任何PL/SQL块、子程序或包中定义自己的子类型，语法如下：
SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];
subtype_name就是声明的子类型的名称，base_type可以是任何标量类型或用户定义类型，约束只是用于限定基类型的精度和数值范围，或是最大长度。下面举几个例子：
DECLARE
SUBTYPE birthdate IS DATE NOT NULL; – based on DATE type
SUBTYPE counter IS NATURAL; – based on NATURAL subtype
TYPE namelist IS TABLE OF VARCHAR2(10);
SUBTYPE dutyroster IS namelist; – based on TABLE type
TYPE timerec IS RECORD(
minutes INTEGER,
hours INTEGER
);
SUBTYPE finishtime IS timerec; – based on RECORD type
SUBTYPE id_num IS emp.empno%TYPE; – based on column type
我们可以使用%TYPE或%ROWTYPE来指定基类型。当%TYPE提供数据库字段中的数据类型时，子类型继承字段的大小约束(如果有的话)。但是，子类型并不能继承其他约束，如NOT NULL。
2、使用子类型
一旦我们定义了子类型，我们就可以声明该类型的变量、常量等。下例中，我们声明了Counter类型变量，子类型的名称代表了变量的使用目的：
DECLARE
SUBTYPE counter IS NATURAL;
ROWS counter;
下面的例子演示了如何约束用户自定义子类型：
DECLARE
SUBTYPE accumulator IS NUMBER;
total accumulator(7, 2);
子类型还可以检查数值是否越界来提高可靠性。下例中我们把子类型Numeral的范围限制在-9到9之间。如果程序把这个范围之外的数值赋给Numeral类型变量，那么PL/SQL就会抛出一个异常。
DECLARE
SUBTYPE numeral IS NUMBER(1, 0);
x_axis numeral; – magnitude range is -9 … 9
y_axis numeral;
BEGIN
x_axis := 10; – raises VALUE_ERROR
…
END;
? 类型兼容
一个未作约束的子类型是可以和它的基类型交互使用。例如下面的声明，amount值可以在不用转换的情况下直接赋给total：
DECLARE
SUBTYPE accumulator IS NUMBER;
amount NUMBER(7, 2);
total accumulator;
BEGIN
…
total := amount;
…
END;
如果基类型相同，那么不同的子类型也是可以交互使用。例如，下面的声明中，finished的值就可以赋给debugging：
DECLARE
SUBTYPE sentinel IS BOOLEAN;
SUBTYPE SWITCH IS BOOLEAN;
finished sentinel;
debugging SWITCH;
BEGIN
…
debugging := finished;
…
END;
不同的子类型也是有可能交互使用，只要它们的基类型属于同一个数据类型种类。例如下面的声明中，verb值就能赋给sentence：
DECLARE
SUBTYPE word IS CHAR(15);
SUBTYPE text IS VARCHAR2(1500);
verb word;
sentence text(150);
BEGIN
…
sentence := verb;
…
END;
一、 timestamp with local time zone类型和timestamp with time zone
timestamp with loca time zone类型语法
tmestamp[(fractional_seconds_precisions)] with local zone
timestamp with local time zone 和timesatamp with time zone的最大区别就是，前者在用户提交时间给数据库的时，该类型会转换成数据库的时区来保存数据，即数据库保存的时间是数据库本地时区，当别的用户访问数据库时oracle会自动将该时间转换成当前客户端的时间。
例子：
1、创建表
CREATE TABLE TIMESTAMP_TEST(
TIME DATE,
TIMESTP TIMESTAMP(3),
TIMESTP_TZ TIMESTAMP(3) WITH TIME ZONE,
TIMESTP_LTZ TIMESTAMP(3) WITH LOCAL TIME ZONE)
2、添加数据
INSERT INTO TIMESTAMP_TEST VALUES(SYSDATE,SYSDATE,SYSDATE,SYSDATE);
commit;
3、查询dbtimezone和sessiontimezone的值
select dbtimezone ,sessiontimezone from dual;
DBTIME
SESSIONTIMEZONE
+00:00
+08:00
4、查看数据的值
SQL> SELECT * FROM TIMESTAMP_TEST;
TIME
TIMESTP
TIMESTP_TZ
TIMESTP_LTZ
02-6月 -10
02-6月 -10 11.21.10.000 上午
02-6月 -10 11.21.10.000 上午 +08:00
02-6月 -10 11.21.10.000 上午
5、修改会话的time_zone值
alter session set time_zone=’+10:00’;
6、查看结果
SQL> SELECT * FROM TIMESTAMP_TEST;
TIME
TIMESTP
TIMESTP_TZ
TIMESTP_LTZ
02-6月 -10
02-6月 -10 11.21.10.000 上午
02-6月 -10 11.21.10.000 上午 +08:00
02-6月 -10 01.21.10.000 下午
7、从上面的实验可以看出二者的去区别，当session的时区由8变为10是，时间增加两个小时
再向表中添加一条记录
insert into TIMESTAMP_TEST values(
TO_TIMESTAMP_TZ(‘2010-12-01 23:12:56.788 -12:44’, ‘YYYY-MM-DD HH24:MI:SS.FF TZH:TZM’),
TO_TIMESTAMP_TZ(‘2010-12-01 23:12:56.788-12:44’, ‘YYYY-MM-DD HH24:MI:SS.FF TZH:TZM’),
TO_TIMESTAMP_TZ(‘2010-12-01 23:12:56.788 -12:44’, ‘YYYY-MM-DD HH24:MI:SS.FF TZH:TZM’),
TO_TIMESTAMP_TZ(‘2010-12-0123:12:56.788 -12:44’, ‘YYYY-MM-DD HH24:MI:SS.FF TZH:TZM’));
（tzh：时区中的小时，tzm:时区中的分）
在这里我指定了数据添加时的时区为-12:44，查询结果为
TIME
TIMESTP
TIMESTP_TZ
TIMESTP_LTZ
01-12月-10
01-12月-10 11.12.56.788 下午
01-12月-10 11.12.56.788 下午 -12:44
02-12月-10 09.56.56.788 下午
TIME
TIMESTP
TIMESTP_TZ
TIMESTP_LTZ
02-6月 -10
02-6月 -10 11.21.10.000 上午
02-6月 -10 11.21.10.000 上午 +08:00
02-6月 -10 01.21.10.000 下午
由于当前用户的时区是+10：00，添加数据时的指定时区死-12:44，二者时间相差22小时44分
二、 毫秒
关于毫秒：
Oracle 毫秒的存储必须字段类型为 timestamp(6) –数字表示存储的毫秒位数
–当前毫秒级时间
select to_char(current_timestamp,‘yyyy-mm-dd hh24:mi:ss.ff6’) from dual;
–字符串转为 timestamp类型
select to_timestamp(‘2012-02-03 10:29:46.453234’,‘yyyy-mm-dd hh24:mi:ss.ff6’) from dual;
–timestamp转为字符型
select to_char(systimestamp,‘yyyy-mm-dd hh24:mi:ss.ff6’) from dual;
PS: ff后面的数字表示获得的毫秒位数，默认是6；一般ff3 获得三位毫秒数。
如果你想把DATE类型转换成TIMESTAMP类型，就使用CAST函数。
select cast(sysdate as timestamp) from dual;
但是值得注意的是:在转换后的时间段尾部有了一段“.000000”。这是因为从date转换过来的时候，没有小数秒的信息，缺省为0。而且显示格式是按照参数NLS_TIMESTAMP_FORMAT定的缺省格式显示。当你把一个表中date类型字段的数据移到另一个表的timestamp类型字段中去的时候，可以直接写INSERT SELECT语句，oracle会自动为你做转换的。
注意： to_char函数支持date和timestamp，但是trunc却不支持TIMESTAMP数据类型。这已经清楚表明了在当两个时间的差别极度重要的情况下，使用TIMESTAMP数据类型要比DATE数据类型更确切。
还值得一提的是：毫秒的显示精度是6位，不过有效位是3位，即最大值达到999，满1000ms就进为1s。当然你想保存6位毫秒也是有办法的：
insert 值指定六位：to_timestamp(‘2012-02-03 10:29:46.453234’,‘yyyy-mm-dd hh24:mi:ss.ff6’)
1.6 数据类型转换
有时我们需要把一个值从一个类型转换成另一个类型。例如，如果我们想检查一个ROWID，我们就必须把它转成一个字符串。PL/SQL支持显式和隐式(自动的)数据类型转换。
1、显式转换
如果要进行类型间的转换，我们可以使用内置函数。例如将一个CHAR类型的值转换成一个DATE或NUMBER类型的值，我们就可以使用函数TO_DATE和TO_NUMBER。反过来，如果从DATE或NUMBER转成CHAR的话，可以使用TO_CHAR函数。
2、隐式转换
PL/SQL有时会帮助我们进行隐式地数据类型转换。下面的例子中，CHAR型变量start_time和finish_time都有一个代表从午夜开始后所经过的秒数的值。这两个变量的差值要放到elapsed_time中去。所以，PL/SQL会自动地把CHAR类型转换成NUMBER类型。
DECLARE
start_time CHAR(5);
finish_time CHAR(5);
elapsed_time NUMBER(5);
BEGIN
/* Get system time as seconds past midnight. */
SELECT TO_CHAR(SYSDATE, ‘SSSSS’)
INTO start_time
FROM SYS.DUAL;
– do something
/* Get system time again. */
SELECT TO_CHAR(SYSDATE, ‘SSSSS’)
INTO finish_time
FROM SYS.DUAL;
/* Compute elapsed time in seconds. */
elapsed_time := finish_time - start_time;
INSERT INTO results
VALUES (elapsed_time, …);
END;
另外，在把查询的结果赋给变量之前，如果有必要的话PL/SQL也会把原值类型转成对应的变量类型，典型的例子就是把DATE类型放入VARCHAR2变量中。
同样，在把变量值赋给数据库字段时，PL/SQL在必要的情况下，也会进行数据类型转换。如果PL/SQL无法确定采用哪种转换形式，就会发生变异错误。这种情况下，我们就必须使用类型转换函数。下表是PL/SQL能够做的隐式转换。
BIN_INT BLOB CHAR CLOB DATE LONG NUMBER PLS_INT RAW UROWID VARCHAR2
BIN_INT X X X X X
BLOB X
CHAR X X X X X X X X X X
CLOB X X
DATE X X X
LONG X X X
NUMBER X X X X X
PLS_INT X X X X X
RAW X X X X
UROWID X X
VARCHAR2 X X X X X X X X X
5 注意
6 列表中只列出表现不同的类型，而那些表现相同的类型，如CLOB和NCLOB，CHAR和NCHAR，还有VARCHAR2和NVARCHAR2都是可以互相替换。
7 要在CLOB和NCLOB之间转换，我们必须使用转换函数TO_CLOB和TO_NCLOB。
8 TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE，INTERVAL DAY TO SECOND和INTERVAL YEAR TO MONTH都可以作为DATE类型用同样的转换规则进行转换。但是由于它们的内部表现形式不同，这些类型不能相互转换。
3、显式转换 VS 隐式转换
通常，依赖隐式的数据转换不是一个好习惯，因为这样做会引起性能问题并可能随着软件版本变更而影响到后续的版本。隐式转换是与环境相关的，它的转换结果有时是无法预知的。所以，最好还是使用类型转换函数。这样的话，程序就有更好的可读性和可维护性。
4、DATE值
当把一个查询出来的DATE类型值放到CHAR或VARCHAR2变量中时，PL/SQL必须将内置的二进制值转成字符值，它就会调用TO_CHAR将日期按照默认的日期格式转成字符值。同样，把一个CHAR或VARCHAR2插入到DATE类型的字段中，也是需要进行类型转换的，PL/SQL会调用TO_DATE将字符按照默认的日期格式转成日期类型的值。如果对转换有特殊要求，我们就得显式地指明转换格式。
5、RAW和LONG RAW值
在把查询出来的RAW或LONG RAW类型值赋给CHAR类型或VARCHAR2类型变量的时候，PL/SQL必须把内置的二进制值转成字符值。这种情况下，PL/SQL会把每个RAW或LONG RAW类型的二进制字节转成等值的一对十六进制字符值。比如PL/SQL会把二进制11111111转换成字符"FF"。函数RAWTOHEX也具有这样的功能。把CHAR或VARCHAR2类型值插入RAW或LONG RAW字段时也是需要类型转换的。变量中的每对字符要转成等值二进制字节，如果无法进行正常的转换，PL/SQL就会抛出异常。
除Oracle数据类型之外，Oracle9i/10g数据库中表的列可以用ANSI、DB2和SQL/DS 数
据类型定义。不管用何种方法，Oracle服务器内部将转换这些数据类型为Oracle数据类型。
在某些情况下，Oracle服务器使用一种数据类型的数据，而在另外一种情况下我们希望
使用一种不同数据类型的数据，如果这种情况发生，Oracle服务器自动转换数据为期望的数
据类型。这种数据类型的转换可以被Oracle服务器隐式进行，或由用户显式进行。
1.6.1 隐式数据类型转换
隐式数据类型转换工作依照下面的规则进行。
显式数据类型转换用转换函数进行。转换函数转换从一种数据类型转换值到另一种数据
类型。通常，函数名的构成遵循 数据类型 到 数据类型 的约定，第一个数据类型是输入数
据类型；后一个数据类型是输出数据类型。
尽管隐式数据类型转换是可用的，但建议你做显式数据类型转换以确保SQL语句的可靠
性。
对于直接赋值，ORACLE SQL可以进行以下的隐式转换：
从 到
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2
对于表达式赋值，ORACLE SQL可以进行以下的隐式转换：
从 到
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
1.6.2 显式数据类型转换
SQL 提供三种函数来从一种数据类型转换值到另一种：TO_CHAR、TO_NUMBER、 TO_DATE。
TO_CHAR() 函数又可以分三小类，分别是
转换字符->字符TO_CHAR©：将nchar,nvarchar2,clob,nclob类型转换为char类型；
例如：
Select To_Char(‘AABBCC’) From Dual;
转换时间->字符TO_CHAR(d[,fmt])：将指定的时间(data, timestamp,
timestamp with time zone)按照指定格式转换为varchar2类型；
必须加单引号，并且区分大小写，能够包含任一有效的日期格式元素，有一个fm元素
用来删除填补的空，或者前导零，用一个逗号与日期值分开。
例如：
Select To_Char(Sysdate, ‘yyyy-mm-dd hh24:mi:ss’) From Dual;
转换数值->字符TO_CHAR(n[,fmt])：将指定数值n按照指定格式fmt 转换为
varchar2类型并返回；
例如：
Select To_Char(-100, ‘L99G999D99MI’) From Dual;
TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型，如果fmt参数
不为空，则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为’J’则表示按照
公元制(Julian day)转换，c则必须为大于0并小于5373484的正整数。
例如：
Select To_Date(2454336, ‘J’) From Dual;
Select To_Date(‘2007-8-23 23:25:00’, ‘yyyy-mm-dd hh24:mi:ss’)
From Dual;
TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式
转换为数值类型并返回。
例如：
Select To_Number(’-100.00’, ‘9G999D99’) From Dual;
1.7 ORA-01704:文字字符串过长 oracle CLOB 超过4000字节
往oracle函CLOB、LONG字段类型的数据表插入值超过4000字节时，在执行INSERT INTO提示：ORA-01704:文字字符串过长错误，这种情况下，如果想用SQL实现的话，可以通过ORACLE存储过程实现。
往oracle函CLOB、LONG字段类型的数据表插入值超过4000字节时，在执行INSERT INTO提示：ORA-01704:文字字符串过长错误，这种情况下，如果想用SQL实现的话，可以通过ORACLE存储过程实现。
– 处理方法一：用EXECUTE IMMEDIATE 实现
– ASQL：带参数的INSERT INTO 语句
– ALongVar1 : 参数值，大于4000字节
CREATE OR REPLACE PROCEDURE EXEC_SQL1(ASQL VARCHAR2,
ALongVar1 CLOB) AS
BEGIN
EXECUTE IMMEDIATE ASQL
USING ALongVar1;
END EXEC_SQL1;
– 处理方法二：用Dbms_sql接口实现
– ASQL：带参数的INSERT INTO 语句
– ALongVar1 : 参数值，大于4000字节
CREATE OR REPLACE PROCEDURE EXEC_SQL(ASQL CLOB,
ALongValue CLOB) AS
The_c1 INTEGER;
The_result INTEGER; – dml_sql_result
BEGIN
The_C1 := Dbms_sql.open_cursor;
Dbms_sql.parse(the_C1, ASQL, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(The_c1, ’ :LongValue ', ALongValue);
The_result := Dbms_sql. EXECUTE(The_c1);
Dbms_sql.close_cursor(The_C1);
END EXEC_SQL;
CREATE OR REPLACE FUNCTION sumvarc RETURN CLOB IS
v_out CLOB;
BEGIN
FOR i IN 100…4000 LOOP
v_out := v_out || i;
END LOOP;
RETURN v_out;
END;
?
第2章 pl/sql 块的数据类型
2.1 pl/sql 数据类型
定义并使用变量
declare
v_num number(5) := 123;
v_str varchar2(20) := ‘abc’;
v_date date := sysdate;
c_pi constant number(5,2) not null := 3.14;
begin
–c_pi := c_pi + 1; 常量不能修改其内容
v_num := v_num + 5;
dbms_output.put_line(v_num);
dbms_output.put_line(v_str);
–dbms_output.put_line(‘今天是’ || v_date);
dbms_output.put_line(‘今天是’ || to_char(v_date,‘yyyy"年"mm"月"dd"日"’));
end;
在编写 pl/sql 程序时，可以定义变量和常量；在 pl/sql 程序中包括有：
1.标量类型(scalar)
2.复合类型(composite)
3.参照类型(reference)
4.lob(large object)
变量声明分标量型变量声明和组合变量（复合变量）声明。
2.1.1 标量型变量
标量型变量是指其内部没有成员的变量。
标量(scalar)——常用类型
在编写 pl/sql 块时，如果要使用变量，需在定义部分定义变量。pl/sql 中定义变量和常量的语法如下：
identifier [constant] datatype [not null] [:=| default expr]
identifier : 名称
constant ：指定常量。需要指定它的初始值，且其值是不能改变的
datatype ：数据类型
not null ：指定变量值不能为 null
:= 给变量或是常量指定初始值
default 用于指定初始值
expr ：指定初始值的 pl/sql 表达式，可以是文本值、其它变量、函数等。
2.1.1.1 标量定义的案例
① 定义一个变长字符串
v_ename varchar2(10);
② 定义一个小数,范围 -9999.99~9999.99
v_sal number(6,2);
③ 定义一个小数并给一个初始值为 5.4 :=是 pl/sql 的赋值号
v_sal2 number(6,2):=5.4;
④ 定义一个日期类型的数据
v_hiredate date;
⑤ 定义一个布尔变量，不能为空，初始值为 false
v_valid boolean not null default false;
例：
age number(5) not null:=25;
pi constant number(9):=3.1415926;
name char(10) not null:=‘fan’;
today date not null:=sysdate;
sex boolean:=true;
例：声明一个变量Student_name,其类型基于另一个变量teacher_name。
Teacher_name char(10);
Student_name teacher_name%type;
例: 声明一个变量No,使其与表emp中EMPNO的类型一致。
no emp.empno%type;
标量(scalar)——使用标量 在定义好变量后，就可以使用这些变量。这里需要说明的是 pl/sql 块为变量赋 值不同于其它的编程语言，需要在等号前面加冒号(:=) 下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为 0.03)为例。说 明变量的使用，看看如何编写。
Sql 代码
- declare
- c_tax_rate number(3,2):=0.03;
- –用户名
- v_ename varchar2(5);
- v_sal number(7,2);
- v_tax_sal number(7,2);
- begin
- –执行
- select ename,sal into v_ename,v_sal from emp where empno=&no;
10.–计算所得税 - v_tax_sal := v_sal*c_tax_rate;
12.–输出 - dbms_output.put_line(‘姓名是：’||v_ename||‘工资：
‘||v_sal||’ 交税：’||v_tax_sal);
14.end;
15./
2.1.2 复合变量(composite)
介绍
用于存放多个值的变量。主要包括这几种：
- pl/sql 记录
- pl/sql 表
- 嵌套表
- varray即数组
复合组合变量也叫做组合变量，在复合变量中包括多个内部组件，每个组件都可以单独存放值,因此一个复合变量可以存放多个值。
复合变量类型不是数据库中已经存在的数据类型，因此复合变量在声明类型之前，首先要先创建复合类型，复合类型创建后可以多次使用，以便定义多个复合变量。
复合变量像标量变量一样也有数据类型，复合数据类型（又称为集合）有记录（RECORD）、表（TABLE）、嵌套表（Nested TABLE）和数组（VARRAY）四种类型。记录型是由一组相关但又不同的数据类型组成的逻辑单元。表是数据的集合，可将表中的数据作为一个整体进行引用和处理。关于嵌套表和数组本书不再作过多的介绍了。
记录就是一组相关的数据项，每一个数据项都有自己的名字和数据类型。表由列和关键字组成，其中通过关键字可成组地访问行。一经定义，记录和表都可重复使用。
当使用标量变量处理oracle数据时，每个标量变量只能存放单个值，也就是说只能处理单行单列的数据。如果要使用标量变量处理单行多列数据，那么必须要定义多个变量接收不同列的数据，为了简化单行多列数据的处理，可以使用PL\SQL记录，为了保留并处理多行单列的数据，可以使用索引表，嵌套表和varray，为了处理多行多列的数据，应该使用PL\SQL记录表。
组合型变量内部包含若干个成员，每个成员由标量型变量或组合型变量组成
定义组合型变量的语法如下：
type <类型名> is record
(<域名1> {<标量型数据类型> | <record类型>}[not null],
<域名2> {<标量型数据类型> | <record类型>}[not null],
…….);
<标识符> <类型名>;
例：定义一个变量，存放一个学生的有关信息。
declare
type student is record /定义组合型变量类型/
(id number(4) not null:=0,
name char(10) not null:=’ ',
sex boolean not null:=true);
stu student; /定义组合型变量/
begin
stu.id:=1;
stu.name:=‘sheng’;
stu.sex:=true;
end;
例：声明一个变量，其结构与表emp的数据结构相一致。
declare
emp_value emp%rowtype;
Begin
select * into empvalue from emp
where empno=7369;
dbms_output.put_line(‘姓名：’||‘ ’||emp_value.ename);
End;
注：在运行些PL/SQL块前，应先运行
2.1.2.1 记录(record)类型
PL/SQL 记录
记录是存储在多个字段中的一组相关的数据项，每个字段都有自己的名字和数据类型。例如：描述一名员工的信息需要一些不同的属性，如姓名、工资、雇佣日期等，若把这些属性（字段）放在记录里，就可以组成了一个逻辑单元。当把这些字段作为一个整体声明为记录型时，就可以作为一个单元来处理这些不同的数据了。
记录类型 ：记录类型是把逻辑相关的数据作为一个单元存储起来
当使用PL\SQL记录时，应用开发人员可以自己定义记录类型和记录变量，也可以使用%rowtype属性直接定义记录变量。
含有若干个标量组件，RECORD或PL/SQL表类型称为信息组
其结构类似于第三代语言中的记录
不同于数据库表中的行
把字段的集合当作一个整体的逻辑单元
在数据处理时易于从表中取出行数据
概念：由不同的域组成。记录也可以看成表中的数据行，域则相当于表中的列；
定义记录类型语法如下：
TYPE record_type IS RECORD(
Field1 type1 [NOT NULL] [DEFAULT:= exp1 ],
Field2 type2 [NOT NULL] [DEFAULT:= exp2 ],
. . . . . .
Fieldn typen [NOT NULL] [DEFAULT:= expn ]
) ;
注意：
- 类似于高级语言中的结构体，需要注意的是，当引用 pl/sql 记录成员时，必须 要加记录变量作为前缀(记录变量.记录成员)。
- 记录是存储在多个字段中的一组相关的数据项，每个字段都有自己的名字和数据类型。
- 把字段的集合当作一个整体的逻辑单元
- 主要用于从表中取出查询到的行数据
- 处理单行多列的数据
记录的特点：
① 每个记录内都可以有许多字段。
② 可以为记录赋初值，同时可用NOT NULL来限定记录
③ 无初始值的字段定义为NULL
④ 在定义字段时也可以使用保留字DEFAULT
⑤ 可以在任意一个块、子程序或包的声明部分定义RECORD类型并声明用户自定义的记录
⑥ 可以声明并引用嵌套的记录，一个记录可以是其他记录的组件
注意：
- 记录类型一次不能插入多条记录，否则会报如下错误
例：
DECLARE
TYPE emp_record IS RECORD(
empno NUMBER(4),
ename emp.ename%TYPE,
edate emp.hiredate%TYPE);
v_emp emp_record;
BEGIN
SELECT empno,
ename,
hiredate
INTO v_emp
FROM emp
WHERE empno = 7934;
dbms_output.put_line(v_emp.empno || ‘,’ || v_emp.ename || ‘,’ ||
v_emp.edate);
END;
结果：7934,MILLER,23-1月 -82
定义PL\SQL记录
自定义记录
DECLARE
TYPE v_type IS RECORD(
ename VARCHAR2(20),
sal NUMBER(20),
hdate DATE);
v_mytype v_type;
BEGIN
SELECT ename,
empno,
hiredate
INTO v_mytype
FROM emp
WHERE mgr IS NULL;
dbms_output.put_line(v_mytype.ename || ' ' || v_mytype.sal || ' ' || v_mytype.hdate);
END;
– %rowtype 型记录
DECLARE
v_mytype emp%ROWTYPE;
BEGIN
SELECT * INTO v_mytype FROM emp WHERE mgr IS NULL;
dbms_output.put_line(v_mytype.ename || ’ ’ || v_mytype.sal || ’ ’ || v_mytype.hiredate);
END;
一、 %TYPE
定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE
例：DECLARE
–用%TYPE 类型定义变量
v_ename emp.ename%TYPE;
– 用 %TYPE 类型定义与表相配的字段
declare
v_sal number(7) ;
v_ename emp.ename%type;
v_hiredate emp.hiredate%type;
begin
select sal,ename,hiredate into v_sal,v_ename,v_hiredate from emp where empno = 7369;
insert into emp(empno,ename) values(1111,‘xxxx’);
update emp set sal = 2000 where empno= 1010;
delete emp where empno = 1012;
commit;
dbms_output.put_line(v_sal || ‘,’ || v_ename || ‘,’ || v_hiredate);
end;
TYPE t_Record IS RECORD(
t_no emp.empno%TYPE,
t_name emp.ename%TYPE,
t_sal emp.sal%TYPE );
– 声明接收数据的变量
v_emp t_Record;
二、 %ROWTYPE
- 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。
- 声明的变量对应于数据库表或视图中列的集合
- 在%ROWTYPE 之前加上数据库表名
- 记录内字段的名字和数据类型参照表或视图中的列
%ROWTYPE的优点:
① ? 可以不必知道数据库中列的数量和类型。
② ? 在运行期间，数据库中列的数量和类型可能发生变化。
③ ? 在SELECT语句中使用该属性可以有效地检索表中的行。
例：
DECLARE
v_emp emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp FROM emp WHERE empno = 7521;
dbms_output.put_line(v_emp.empno || ‘,’ || v_emp.ename);
END;
declare
–定义一个 pl/sql 记录类型 emp_record_type,类型包含 3 个数据 name,salary,title。说白了，就是一个类型可以存放 3 个数据，主要是 为了好管理
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type
);
–定义了一个 sp_record 变量，这个变量的类型是 emp_record_type
sp_record emp_record_type;
begin
select ename, sal, job into sp_record from emp where empno =7788;
dbms_output.put_line (‘员工名:’ || sp_record.name);
end;
declare
type emp_record is record(
empno number(4),
ename emp.ename%type,
edate emp.hiredate%type);
v_emp emp_record;
begin
select empno,ename,hiredate into v_emp from emp where empno = 7934;
dbms_output.put_line(v_emp.empno || ‘,’ || v_emp.ename || ‘,’ || v_emp.edate);
end;
/*
这既是Java注释，也是Oracle PLSQL多行注释
*/
declare
v_num number(5) := 123;
v_str varchar2(20) := ‘abc’;
v_date date := sysdate;
c_pi constant number(5,2) not null := 3.14;
begin
–c_pi := c_pi + 1; 常量不能修改其内容
v_num := v_num + 5;
dbms_output.put_line(v_num);
dbms_output.put_line(v_str);
–dbms_output.put_line(‘今天是’ || v_date);
dbms_output.put_line(‘今天是’ || to_char(v_date,‘yyyy"年"mm"月"dd"日"’));
end;
declare
v_num number(4) := 20;
v_num3 number(4) := 20;
v_num4 number(4) := 20;
begin
dbms_output.put_line(‘v_num :’ || v_num);
declare
v_num2 number(4) := 30;
v_num3 number(4) := 50;
begin
v_num4 := 100;
dbms_output.put_line('inner v_num :' || v_num); --内部可以访问外部的变量
dbms_output.put_line('inner v_num2 :' || v_num2);
dbms_output.put_line('inner v_num3 :' || v_num3);
end;
--dbms_output.put_line('outter v_num2 :' || v_num2); 错误
dbms_output.put_line('outter v_num3 :' || v_num3);
dbms_output.put_line('outter v_num3 :' || v_num4);
dbms_output.put_line('the end....');
end;
declare
v_sal number(7) ;
v_ename emp.ename%type;
v_hiredate emp.hiredate%type;
begin
select sal,ename,hiredate into v_sal,v_ename,v_hiredate from emp where empno = 7369;
insert into emp(empno,ename) values(1111,‘xxxx’);
update emp set sal = 2000 where empno= 1010;
delete emp where empno = 1012;
commit;
dbms_output.put_line(v_sal || ‘,’ || v_ename || ‘,’ || v_hiredate);
end;
三、 记录类型的定义
定义方式：显示定义和隐式定义
1、 隐式定义
隐式定义：是在基于表的结构或查询上使用%TYPE 属性，隐式声明是一个更强有力的工具，这是因为这种数据变量是动态创建的。语法如下：
① 表记录的隐式定义（student为表名）
record_type student%ROWTYPE;
② 游标行记录的隐式定义
cursor student_line(no_value student.no%type) is
select cuid,no from student where no =no_value;
stu_row student_line%rowtype;
2、 显示定义
显示定义：在PL/SQL 程序块中创建记录变量之前在声明部分定义。使用type 命令定义记录，然后在创建该记录的变量。创建语法如下：
TYPE record_type IS RECORD (field_definition_list);
注：
i、 field_definition_list是由逗号分隔的列表；
ii、 通过type定义的是一种自定义的类型，需要再通过该类型来创建变量；
iii、 整个定义过称和SQL中定义表相似。
V、 记录中域的引用方式：rtt.testa 如下：
CREATE OR REPLACE FUNCTION VarDefined RETURN INT IS
–定义记录类型record_test
TYPE record_test IS RECORD(
testa NUMBER,
testb NUMBER,
testc NUMBER);
–定义类型为record_test的变量rtt
rtt record_test;
BEGIN
–rtt中域testa的引用及赋值
rtt.testa := 10;
–输出信息
dbms_output.put_line(‘rtt.testa=’ || rtt.testa);
–返回值
RETURN 0;
END VarDefined;
注意事项
显示定义时，必须用type进行定义，然后才能声明该记录的变量；
隐式定义，灵活方便，但是可读性差。
四、 记录的赋值方式
① select into方式；
② fetch方式
1、 SELECT INTO
DECLARE
v_mytype emp%ROWTYPE;
BEGIN
SELECT ename,
sal
INTO v_mytype.ename,
v_mytype.sal
FROM emp
WHERE mgr IS NULL;
dbms_output.put_line(v_mytype.ename || ’ ’ || v_mytype.sal || ’ ’ || v_mytype.hiredate);
END;
注意：
如果 记录类型的字段是数字类型的，且有形如v_r_t.fttb_res_num := v_r_t.fttb_res_num + v_count2; 的，则必须进行初始化，不然变量的值一直为0
DECLARE
v_count2 NUMBER(18) := 0;
v_r_t sqm.SQM_FTTX_RES_COVER%ROWTYPE;
BEGIN
v_r_t.fttb_gpon_res_num := 0;–这里必须进行初始化
v_r_t.fttb_res_num := 0;–这里必须进行初始化
FOR REC1 IN (SELECT * FROM xt_fttx_res_num_02) LOOP
SELECT COUNT(1)
INTO v_count2
FROM XB_PORT P
WHERE P.MEID = REC1.meid
AND P.METACATEGORY IN
(‘com.gxlu.ngrm.equipment.PSTNPort’,
‘com.gxlu.ngrm.equipment.IPPort’,
‘com.gxlu.ngrm.equipment.XdslPort’);
v_r_t.fttb_res_num := v_r_t.fttb_res_num + v_count2;
IF rec1.is_gpon = 1 THEN
v_r_t.fttb_gpon_res_num := v_r_t.fttb_gpon_res_num + v_count2;
END IF;
END LOOP;
END;
2、 fetch 。。。BULK COLLECT INTO
DECLARE
CURSOR c1 IS
SELECT t.OBJECT_ID,
t.OBJECT_NAME
FROM user_objects t
WHERE t.OBJECT_ID <= 272835
AND t.OBJECT_ID >= 272830;
–v_depart user_objects%ROWTYPE;
TYPE v_id_type IS TABLE OF user_objects.OBJECT_ID%TYPE;
v_id v_id_type;
TYPE v_name_type IS TABLE OF user_objects.OBJECT_NAME%TYPE;
v_name v_name_type;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT
INTO v_id,
v_name;
FOR i IN 1 … v_id.count LOOP
dbms_output.put_line(v_id(i) || ‘,’ || v_name(i));
END LOOP;
CLOSE c1;
END;
通过上面的这个例子，大家可以发现如果列很多的话，为每一列定义一个集合似乎有些繁琐，可以把集合和%rowtype结合起来一起使用来简化程序!
在输出结果时，既可以使用集合的count属性和可以使用first和last，在引用%rowtype类型的内容时，还有一个需要注意的地方是v_depart(i).depart_code，而不是v_depart.depart_code(i)，当然没有这样的写法，即使有意义也并不一样。
DECLARE
CURSOR c1 IS
SELECT t.*
FROM user_objects t
WHERE t.OBJECT_ID <= 272835
AND t.OBJECT_ID >= 272830;
TYPE v_depart_type IS TABLE OF user_objects%ROWTYPE;
v_depart v_depart_type;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT
INTO v_depart;
FOR i IN 1 … v_depart.count LOOP
–或者 FOR i IN v_depart.first … v_depart.last LOOP
dbms_output.put_line(v_depart(i)
.OBJECT_ID || ‘,’ || v_depart(i).OBJECT_NAME);
END LOOP;
CLOSE c1;
END;
3、 SELECT 。。。 BULK COLLECT INTO
DECLARE
TYPE v_depart_type IS TABLE OF user_objects%ROWTYPE INDEX BY PLS_INTEGER;
v_depart v_depart_type;
BEGIN
SELECT t.* BULK COLLECT
INTO v_depart
FROM user_objects t
WHERE t.OBJECT_ID <= 272835
AND t.OBJECT_ID >= 272830;
FOR i IN 1 … v_depart.count LOOP
–或者 FOR i IN v_depart.first … v_depart.last LOOP
dbms_output.put_line(v_depart(i)
.OBJECT_ID || ‘,’ || v_depart(i).OBJECT_NAME);
END LOOP;
END;
五、 记录类型的使用
1、 在insert语句中使用记录
–在values 中使用记录变量
DECLARE
v_mytype emp%ROWTYPE;
BEGIN
v_mytype.ename := ‘pengxiao’;
v_mytype.sal := 500;
v_mytype.hiredate := SYSDATE;
INSERT INTO emp VALUES v_mytype;
END;
–在values 中使用记录成员
DECLARE v_mytype emp%ROWTYPE;
BEGIN
v_mytype.ename := ‘pengxiao’;
v_mytype.sal := 500;
v_mytype.hiredate := SYSDATE;
INSERT INTO emp
(ename, sal, hiredate)
VALUES
(v_mytype.ename, v_mytype.sal, v_mytype.hiredate);
END;
2、 Update 中使用记录
–UPDATE 中使用记录变量
DECLARE
v_mytype emp%ROWTYPE;
BEGIN
v_mytype.ename := ‘pengxiao’;
v_mytype.sal := 500;
v_mytype.hiredate := SYSDATE;
UPDATE emp SET ROW = v_mytype WHERE mgr IS NULL;
END;
–UPDATE 中使用记录成员
DECLARE
v_mytype emp%ROWTYPE;
BEGIN
v_mytype.ename := ‘pengxiao’;
v_mytype.sal := 500;
v_mytype.hiredate := SYSDATE;
UPDATE emp
SET ename = v_mytype.ename, sal = v_mytype.sal
WHERE mgr IS NULL;
END;
3、 Delete 中使用记录
–DELETE 只能使用记录成员不能使用记录变量
DECLARE
v_mytype emp%ROWTYPE;
BEGIN
v_mytype.deptno := 30;
DELETE FROM emp WHERE deptno = v_mytype.deptno;
END;
2.1.2.2 PL\SQL集合
PL/SQL中没有数组的概念，他的集合数据类型和数组是相似的。在7.3以前的版本中只有一种集合，称为PL/SQL表，在这之后又有两种集合数据类型:嵌套表和varray。其中varray集合中的元素是有数量限制的，index_by表和嵌套表是没有这个限制的。index-by表是稀疏的，也就是说下标可以不连续，varray类型的集合则是紧密的，他的下标没有间隔。index_by表不能存储在数据库中，但是嵌套表和varray可以被存储在数据库中。
集合在使用时必须先使用type进行定义方可使用。
- 为了处理单行单列数据我们使用 标量变量
- 为了处理单行多列数据我们使用PL\SQL记录
- 为了处理单列多行数据，我们可以使用 PL\SQL集合
- 集合类型包括索引表（PL\SQL表、index-by表，关联数组），嵌套表，和变长数组varray三种类型,使用这三种类型的时候要仔细体会他们中的区别
PL/SQL集合：
- 关联数组：也称索引表，可以用任意数字和字符串作为下标，类似于其他语言中的hash 表。
- 嵌套表：可以存储任意数量的元素，使用连续数字作为下标。可以定义等价的SQL类型，可以存储在数据库表中，用SQL进行检索。
- Varrays（可变数组，可变大小数组）：存储固定数量的元素（在运行中，可以改变元素数量），使用顺序数字作下标，可以定义等价的SQL类型，可以存储在数据库中。可以用SQL进行存储和检索，但比嵌套表缺乏灵活性。
一、 表类型
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而 pl/sql 是可以为负数的，并且表元素的下标没有限制。实例如下：
PL/SQL 表, 或者称为索引表(index-table)或称为关联数组，是可以在PL/SQL 程序中引用、能够模仿数组的非永久表。用户可以定义一个表类型，然后声明这种类型的变量。接下来，用户就可以将记录添加到用户的PL/SQL 表中，并且采用与引用数组元素大体相同的方法引用他们 。
表包括两个基本成分：
① 主键：数据类型为BINARY_INTEGER
② 标量或记录数据类型
Note：此表非彼表
定义一个表：
TYPE type_name IS TABLE OF
{column_type | variable%TYPE | table.column%TYPE } [NOT NULL] | table%ROWTYPE
[ INDEX BY BINARY_INTEGER];
? 注意：
1． Index-by表中的元素不一定要按任何特定的顺序排序
2． 关键字唯一允许的类型是BINARY_INTERGER
3． 使用主键值引用表中的字段
4．Table类型没有长度限制，可以动态增长
5．由于不存储在数据库中，column_type可以是任何合法的PL/SQL数据类型，包括：PLS/INTEGER、SIGNTYPE、和BOOLEAN。
6．使用的时候需要先赋值后读取，至少也要先初始化一下，否则会出现异常：ORA-01403: no data found。
7．这种数组不需要事先指定上限，下标可以不连续，可以是0或负数。
例：v1 TYPE1;
v1(-1) := ‘-1’;
v1(0) := ‘0’;
v1(1) := ‘1’;
DBMS_OUTPUT.put_line(v1(-1)); --访问合法
DBMS_OUTPUT.put_line(v1(2)); --访问非法
Sql 代码
declare
–定义了一个 pl/sql 表类型 sp_table_type，该类型是用于存放emp.ename%type
–index by binary_integer 表示下标是整数
type emp_table_type is table of emp.ename%type
index by binary_integer;
–定义了一个 sp_table 变量，这个变量的类型是 sp_table_type
sp_table emp_table_type;
begin
select ename into sp_table(-1) from emp where empno = 7788;
dbms_output.put_line(‘员工名:’ || sp_table(-1));
end;
说明：
sp_table_type 是 pl/sql 表类型
emp.ename%type 指定了表的元素的类型和长度
sp_table 为 pl/sql 表变量
sp_table(0) 则表示下标为 0 的元素
注意：如果把 select ename into sp_table(-1) from emp where empno = 7788;
变成 select ename into sp_table(-1) from emp;则运行时会出现错误，错误如下：
ORA-01422:实际返回的行数超出请求的行数
解决方法是：使用参照变量（这里不讲）
1、 PL/SQL 表方法
方法 描述
EXISTS(n) 用于判断PL/SQL表中指定的元素是否存在。
COUNT 返回一个PL/SQL表当前包含的元素的数量。
FIRST
LAST 在PL/SQL表中返回第一个和最后一个索引数字。
DELETE
DELETE把所有的元素从PL/SQL表中移开。
DELETE（n）把第n 个元素从PL/SQ表中移开。
DELETE(m,n)从PL/SQ表中移开在范围（m,n）中的所有元素。
DECLARE
TYPE e_table_type IS TABLE OF emp.Ename%TYPE INDEX BY BINARY_INTEGER;
e_tab e_table_type;
v_var NUMBER(2);
BEGIN
e_tab(1) := ‘SMITH’;
UPDATE emp SET sal = 1.1 * sal WHERE Ename = e_tab(1);
v_var := e_tab.count;
dbms_output.put_line(v_var);
e_tab.delete(1);
v_var := e_tab.count;
dbms_output.put_line(v_var);
END;
DECLARE
TYPE dept_table_type IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;
my_dname_table dept_table_type;
v_count NUMBER(3);
BEGIN
v_count := -10;
my_dname_table(v_count).dname := ‘searching’;
dbms_output.put_line(my_dname_table(v_count).dname);
v_count := 0;
my_dname_table(v_count).dname := ‘market’;
dbms_output.put_line(my_dname_table(v_count).dname);
v_count := 10;
my_dname_table(v_count).dname := ‘finance’;
dbms_output.put_line(my_dname_table(v_count).dname);
END;
2、 例一
drop table test ;
create table test(
id number(20),
value varchar2(50)
);
DECLARE
TYPE test_record IS RECORD(
id NUMBER(5),
VALUE VARCHAR(10));
TYPE test IS TABLE OF test_record INDEX BY BINARY_INTEGER;
v_test test;
v_starTime NUMBER(10);
v_endTime NUMBER(10);
BEGIN
--赋值
FOR i IN 1 .. 5000 LOOP
v_test(i).id := i;
v_test(i).value := to_char(floor(dbms_random.value(10, 101)));
END LOOP;
--查看值
v_starTime := dbms_utility.get_time;
FOR i IN v_test.first .. v_test.last LOOP
INSERT INTO test VALUES (v_test(i).id, v_test(i).value);
END LOOP;
v_endTime := dbms_utility.get_time;
Dbms_Output.put_line('所用时间：' || (v_endTime - v_starTime) / 100);
END;
DECLARE
TYPE test_record IS RECORD(
id NUMBER(5),
VALUE VARCHAR(10));
TYPE test1 IS TABLE OF test_record INDEX BY BINARY_INTEGER;
v_test1 test1;
v_starTime NUMBER(10);
v_endTime NUMBER(10);
BEGIN
FOR i IN 1 … 5000 LOOP
v_test1(i).id := i;
v_test1(i).value := to_char(floor(dbms_random.value(10, 101)));
END LOOP;
v_starTime := dbms_utility.get_time;
FORALL i IN v_test1.first … v_test1.last
INSERT INTO test VALUES (v_test1(i).id, v_test1(i).value);
v_endTime := dbms_utility.get_time;
Dbms_Output.put_line(‘所用时间：’ || (v_endTime - v_starTime) / 100);
END;
DECLARE
TYPE test1 IS TABLE OF dept%ROWTYPE;
v_test1 test1;
BEGIN
SELECT * BULK COLLECT INTO v_test1 FROM dept;
FOR i IN v_test1.first … v_test1.last LOOP
Dbms_Output.put_line(‘部门名称：’ || v_test1(i).dname);
END LOOP;
END;
探讨：有关PL/SQL表类型中的理解。
很容易认为PL/SQL表类型象c或Java中的数组类型，因为其中有个关键字叫做“index”(索引)，而数组的下标也叫做“index”。
但是这样无法解释PL/SQL表类型的索引可以不连续，甚至可以为负数的问题。
我认为，PL/SQL表类型更象是java中的map对象，索引就对应map中的键，多个值之间的键可以不连续，可以为负数，甚至可以是字符串。两者有异曲同工之妙。
示例：用字符串做索引。
declare
–定义表结构
type myTabType is table of char(2) index by varchar2(5);
sexTab myTabType;
v_sex_code varchar2(5);
v_sex_name char(2);
begin
–往表中添加内容
sexTab(‘nan’):=‘男’;
sexTab(‘nv’):=‘女’;
–用户输入
v_sex_code:=’&性别编号’;
v_sex_name:=sexTab(v_sex_code);
dbms_output.put_line('您的性别是 = '||v_sex_name);
end;
输入：nan
输出: 男
联想一下下：
·在PL/SQL表中，利用PL/SQL表类型，在某些场合也能够避免减少表连接，从而大大提高连接的效率。
·在java中map对象用途非常广泛，可以缓存很多对象(如配置文件对象，常见的查询结果对象等)。两者的用法真的是异曲同工之妙。
案例：
SQL> declare
2 type table_emp is table of emp%rowtype
3 index by binary_integer;
4 empt table_emp;
5 begin
6 empt(1).ename:=‘wangyi’;
7 dbms_output.put_line(empt(1).ename);
8 end;
9 /
//返回总记录
SQL> declare
2 type table_emp is table of emp%rowtype
3 index by binary_integer;
4 empt table_emp;
5 begin
6 dbms_output.put_line(empt.count);
7 end;
8 /
0 //没有记录
//删除的操作
表名.Delete(记录数);
//检索记录变量
First：获取第一个的索引
Next：下一个的索引 但是必须有参数
Last：最后一个的索引
SQL> declare
2 type table_emp is table of emp%rowtype
3 index by binary_integer;
4 empt table_emp;
5 i number(2):=1;
6 begin
7 while i<10
8 loop
9 empt(i).ename:=‘wangyi’;
10 i:=i+1;
11 end loop;
12
13 dbms_output.put_line(empt.count);
14
15 empt.delete(2);
16
17 dbms_output.put_line(empt.count);
18
19 dbms_output.put_line(empt.first);
20 dbms_output.put_line(empt.next(2));
21 dbms_output.put_line(empt.last);
22 end;
23 /
oracle中type is table 类型删除
最近写存储过程，遇到一个问题，使用批量插入数据老是出错，说违反唯一性约束，最后检查存储过程，发现type table数据没有删除而引起的，存储过程如下：
1 type type_char2 is table of NVARCHAR2(30) index by binary_integer; --定义一个字符串的临时表类型
2 v_card_id type_char2;
3 --下面是游标
4 cursor cur_bt_data is
5 select * from test…;
6 --遍历游标
7 for bt_row in cur_bt_data loop
8 for i in 1 … bt_row.confirm_quanlity loop
9 v_card_id(i) := to_number(bt_row.iccid_start)+i-1;
10 end loop;
11 forall i in 1 … v_card_id.count
12 insert /*+ append */
13 into demo
14 (card_id,…)
15 values
16 (v_card_id(i),…);
17 commit;
18 end loop; – [END]for ‘cur_bt_data’
发现里面的v_card_id(i)问题，如果查询的 bt_row.confirm_quanlity 数量一样，循环初始化，数据应该没有问题，如果数量不一样，如，前一次查询的数量大，后一次的数量小，v_card_id没有初始化后面的，就可能出现重复的结果，所有每次循环都必须清理临时表
查了相关资料，只需要使用v_card_id.delete就删除临时表
修改如下：
19 for bt_row in cur_bt_data loop
20 v_card_id.delete;
21 for i in 1 … bt_row.confirm_quanlity loop
22 …
二、 嵌套表
嵌套表是表中之表。一个嵌套表是某些行的集合，它在主表中表示为其中的一列。对主表中的每一条记录，嵌套表可以包含多个行。在某种意义上，它是在一个表中存储一对多关系的一种方法。
Nested table is a data type in Oracle which is used to support columns containing multi valued attributes. It also hold entire sub table.
1.必须进行初始化，否则会出现异常：ORA-06531: Reference to uninitialized collection
2.初始化方法：
v1 TYPE2 := TYPE2(); --声明时初始化数组为空
v2 TYPE2 := TYPE2(‘1’,‘2’,‘3’,‘4’,‘5’); --声明时初始化数组为5个元素
v1 := TYPE2(); --初期化后数组为空
v2 := TYPE2(‘1’,‘2’,‘3’,‘4’,‘5’); --初期化后数组为5个元素
3.数组元素的访问：
下标从1开始，不能超过数组所有元素的总和，当下标超出允许范围时，出现异常：ORA-06532: Subscript outside of limit
因为不能访问空数组，所以空数组的场合，必须进行数组扩展。 54ne.com
例：v1.EXTEND;
V1(1):= ‘1’; --访问合法
v1(2):= ‘2’; --访问非法，之前必须再次执行v1.EXTEND; 网管网bitsCN_com
例：v2的下标范围是1～5。
v2(5):= ‘Hello’; --访问合法
DBMS_OUTPUT.put_line(v2(6)); --访问非法
Oracle 数据库创建嵌套表
步骤一：打开SQLPLUS, 输入用户名与密码（本例以Scott 用户登录）
登录后显示的界面如下：
图 1-1
步骤二：创建元组类型TDate,在SQLPLUS中输入
create type TDate as object(day integer, month char(3), year integer);
换行输入“/”。创建成功：
图 1-2
步骤三：创建Tcg类型
输入：
create type TCg as object(Course varchar2(5), Grade integer, Cdate TDate);
创建成功，SQLPLUS界面显示如下:
图 1-3
步骤四：创建嵌套表类型 TCgs
在SQLPLUS中输入：create type TCgs as table of TCg
创建成功后：
图 1-4
步骤四：创建最外层表SC
在SQLPLUS中输入：
create table SC(Sname varchar2(10),Cg TCgs)
nested table Cg store as Cg_tab
SQLPLUS 界面如下
图 1-5
步骤五：插入数据
在SQLPLUS 中输入：
insert into SC
values(‘picart’,TCgs(TCg(‘DB’,80,TDate(1, ‘Oct’,2009)),
TCg(‘OS’,80,TDate(1, ‘Nov’,2009))));
insert into SC
values(‘kirk’,TCgs(TCg(‘DB’,85,TDate(1, ‘Jul’,2009)),
TCg(‘MATHS’,80,TDate(1, ‘Oct’,2009))))
如下图所示：
图 1-6
步骤六： 查询数据
1）为了验证数据是否插入表中，可输入“select * from sc” 进行查询，如图所示：
图 1-7
2）查询每个同学选课的门数
在SQLPLUS 中输入：
select sname, (select count(*) from table (x.Cg))
from SC x
结果如图：
图 1-8
3）查询”kirk” 选择课程的详细信息
在SQLPLUS 中输入：
select * from (select cg from sc where sname=’kirk’);
结果下：
图 1-9
4）查询”kirk” 选择的课程名
在SQLPLUS 中输入：
select x.course from table(select cg from sc where sname=’kirk’) x;
结果如下：
图 2-1
步骤七：修改数据
修改”kirk”的选课信息，将“DB”课程修改为“orcl”
在SQLPLUS 中输入：
Update table(select cg from sc where sname=’kirk’) x set x.course=’orcl’ where x.course=’DB’；
然后输入：select x.course from table(select cg from sc where sname=’kirk’) x 查询修改的结果
结果如下：
图 2-2
修改”kirk”的选课时间，将其选择课程‘orcl’的选课时间改为“21，Oct,2011”
在SQLPLUS 中输入：
Update table(select cg from sc where sname=’kirl’) set cdate=tdate(21,’Oct’,2011) where course=’orcl’;
然后输入：select * from table(select cg from sc where sname=’kirk’) where course=’orcl’
结果如图：
图 2-3
修改修改”kirk”的选课时间，将其选择课程‘orcl’的选课时间的年份改为2010年
在SQLPLUS 中输入：
update table(select cg from sc where sname=’kirk’) x set x.cdate.year=2010 where x.course=’orcl’;
然后输入：select * from table(select cg from sc where sname=’kirk’) where course=’orcl’
结果如图：
图 2-4
步骤八：删除SC表中的数据
删除‘kirk‘所选的课程’orcl‘的信息
在SQLPLUS 中输入：
delete from table(select cg from sc where sname=’kirk’) where x.course=’orcl’;
然后输入：select * from table(select cg from sc where sname=’kirk’)查询
结果如图：
2)删除‘kirk‘的选课信息
在SQLPLUS 中输入：
delete from sc where sname=’kirk’
然后输入：select * from sc 查询
ORACLE嵌套表的使用
考查一个包含部门信息的表，在任何时间内每个部门会有很多项目正在实施。在一个严格的关系模型中，将需要建立两个独立的表department和project。
嵌套表允许在department表中存放关于项目的信息。勿需执行联合操作，就可以通过department表直接访问项目表中的记录。这种不经联合而直接选择数据的能力使得用户对数据访问更加容易。甚至在并没有定义方法来访问嵌套表的情况下，也能够很清楚地把部门和项目中的数据联系在一起。在严格的关系模型中，department和project两个表的联系需要通过外部关键字（外键）关系才能实现。
下面以电信账单打印程序中的数据库设计来举例说明嵌套表的使用方法。
1、创建类型ITEM_TYPE：此类型中，对于每个ITEM_TYPE类型都包含有一个记录，记载了其名称、类型、层次和数量信息。
CREATE OR REPLACE TYPE “ITEM_TYPE” As Object
(
Item_Seq Number,
Item_Name Varchar2(100),
Item_Type Varchar2(100),
Item_Level Varchar2(100),
Item_Amt Number
)
2、创建ITEM_LIST_TYPE：此类型将用作一个嵌套表的基础类型。
CREATE OR REPLACE TYPE “ITEM_LIST_TYPE” As Table Of item_type
3、创建表T_MAC_INFO：设备资费信息表
– Create table
create table T_MAC_INFO
(
CHARGE_NO VARCHAR2(100),
MAC_TYPE VARCHAR2(100),
SUBTOTAL NUMBER,
ITEMS ITEM_LIST_TYPE,
MAC_SEQ NUMBER,
PRODUCT_SEQ NUMBER
)
nested table ITEMS store as ITEMS_TAB
4、向嵌套表中插入记录
主要代码以下：
变量声明：
Item_List1 Item_List_Type;
Item1 Item_Type;
For I in 1…10 to loop
Item_List1.Extend;
Item_List1(Item_List1.Count) := Item_Type(Item_List1.Count, Item1.Item_Name, Item1.Item_Type, Item1.Item_Level, Item1.Item_Amt);
End loop;
Insert Into t_Mac_Info
( --Cust_Id,
Product_Seq,
Mac_Seq,
Charge_No,
Mac_Type,
Subtotal,
Items)
Values
( --Mac_Info1.Cust_Id,
v_Seq_Product, – Mac_Info1.Product_Seq,
Mac_Seq,
V_Charge_No,
V_Mac_Type,
V_Subtotal,
Item_List1);
commit;
其中ITEM_LIST1作为一嵌套表，被插入保存在T_MAC_INFO这个表里面。
5、查询嵌套表
Select * From t_Mac_Info d, Table(d.Items) Emp
三、嵌套表的特点：
对于嵌套表，在ORACLE 的OOP编程里面使用得多一点。在数据库中使用关系-对象模型，具体表现为对象复用、标准支持、定义访问路径等，这些都是OOP编程的好处：
1、对象复用：如果编写面向对象的代码，就提高了重用以前编写的代码模块的机会。同样，如果创建面向对象的数据库对象，也就提高了数据库对象能够被重用的机会。
2、标准支持：如果创建标准的对象，那么它们被重用的机会就会提高。如果有多个应用或多个表使用同一数据库对象集合，那么它就是既成事实的数据库对象标准。
3、定义访问路径：对于每一个对象，用户可定义在其上运行的过程和函数，从而可以使数据和访问此数据的方法联合起来。有了用这种方式定义的访问路径，就可以标准化数据访问的方法并提高对象的可复用性。
以上举例说明了嵌套表的简单使用，对于嵌套表的详细用法，可以参考相关书籍。
三、 可变数组
元素下标从0开始，数组变量的声明需要同时初始化
一般格式为：
TYPE type_name IS VARRAY (maximum_size) OF element_type
其中：
- type_name是新可变长数组类型的类型名,maximum_size是一个指定可变数组中元素最大数目的整数。element_type是一个PL/SQL标量、记录或对象类型。
一般格式为：
DECLARE
type numberlist is varray(10) of number(5);
type recordlist is varray(5) of dept%rowtype;
例： DECLARE
type strings is varray(5) of varchar2(10);
– Declare a varray with four element
v_list strings := strings(‘scott’,‘peter’,‘smith’,‘tom’);
v_count number;
BEGIN
v_count := 1;
dbms_output.put_line(v_list(v_count));
v_list(v_count) := ‘urman’;
dbms_output.put_line(v_list(v_count));
v_count := 3;
dbms_output.put_line(v_list(v_count));
v_list(v_count) := ‘jackson’;
dbms_output.put_line(v_list(v_count));
v_list(4):=‘oracle’;
dbms_output.put_line(v_list(4));
END;
TYPE TYPE3 IS ARRAY(5) OF VARCHAR2(10);
由于类型定义时的元素个数限制，所以TYPE3的变量在使用时最大的元素个数不能超过5个。与嵌套表基本相同（略）
四、 表和数组的属性
例：DECLARE
type strings_table is table of varchar2(10) index by
binary_integer;
strings strings_table;
v_count number;
BEGIN
v_count := 1;
strings(v_count ) := ‘element1’;
if strings.exists(v_count ) then
dbms_output.put_line(strings(v_count ));
else
dbms_output.put_line(‘no data!’);
end if;
–to be continued例：DECLARE
type strings_table is table of varchar2(10) index by
binary_integer;
strings strings_table;
v_count number;
BEGIN
v_count := 1;
strings(v_count ) := ‘element1’;
if strings.exists(v_count ) then
dbms_output.put_line(strings(v_count ));
else
dbms_output.put_line(‘no data!’);
end if;
–to be continued
例：
strings(2) := ‘element2’;
strings(3) := ‘element3’;
strings(4) := ‘element4’;
strings(5) := ‘element5’;
strings(6) := ‘element6’;
dbms_output.put_line(strings.count);
dbms_output.put_line(strings.first);
dbms_output.put_line(strings.last);
dbms_output.put_line(strings.next(2));
dbms_output.put_line(strings.prior(4));
strings.delete(1,3);–delete from 1 to 3
–dbms_output.put_line(strings(2)); – cann’t find data
END;
全面探讨PL/SQL的复合数据类型
PL/SQL有两种复合数据结构：记录和集合。记录由不同的域组成，集合由不同的元素组成。在本文中我们将讨论记录和集合的类型、怎样定义和使用记录和集合。
PL/SQL 记录
记录是PL/SQL的一种复合数据结构，scalar数据类型和其他数据类型只是简单的在包一级进行预定义，但复合数据类型在使用前必须被定义，记录之所以被称为复合数据类型是因为他由域这种由数据元素的逻辑组所组成。域可以是scalar数据类型或其他记录类型，它与c语言中的结构相似，记录也可以看成表中的数据行，域则相当于表中的列，在表和虚拟表（视图或查询）中非常容易定义和使用，行或记录中的每一列或域都可以被引用或单独赋值，也可以通过一个单独的语句引用记录所有的域。在存储过程或函数中记录也可能有参数。
创建记录
在PL/SQL中有两种定义方式：显式定义和隐式定义。一旦记录被定义后，声明或创建定义类型的记录变量，然后才是使用该变量。隐式声明是在基于表的结构或查询上使用%TYPE属性，隐式声明是一个更强有力的工具，这是因为这种数据变量是动态创建的。
显式定义记录
显式定义记录是在PL/SQL程序块中创建记录变量之前在声明部分定义。使用type命令定义记录，然后在创建该记录的变量。语法如下：
TYPE record_type IS RECORD (field_definition_list);
field_definition_list是由逗号分隔的列表。
域定义的语法如下：
field_name data_type_and_size [NOT NULL][{:=|DEFAULT} default_value]
域名必须服从与表或列的命名规则相同的命名规则。下面我们看一个例子：
DELCARE
TYPE stock_quote_rec IS RECORD
(symbol stock.symbol%TYPE
,bid NUMBER(10,4)
,ask NUMBER(10,4)
,volume NUMBER NOT NULL:=0
,exchange VARCHAR2(6) DEFAULT ‘NASDAQ’
);
real_time_quote stock_quote_rec;
variable
域定义时的%TYPE属性用于引用数据库中的表或视图的数据类型和大小,而在此之前程序不知道类型和大小。在上面的例子中记录域在编译时将被定义为与列SYMBOL相同的数据类型和大小,当代码中要使用来自数据库中的数据时，在变量或域定义中最好使用%TYPE来定义。
隐式定义记录
隐式定义记录中，我们不用描述记录的每一个域。这是因为我们不需要定义记录的结构，不需要使用TYPE语句，相反在声明记录变量时使用%ROWTYPE命令定义与数据库表，视图，游标有相同结构的记录，与TYPE命令相同的是它是一种定义获得数据库数据记录的好方法。
DECLARE
accounter_info accounts%ROWTYPR;
CURSOR xactions_cur(acct_no IN VARCHAR2) IS
SELECT action,timestamp,holding
FROM portfolios
WHERE account_nbr=‘acct_no’
;
xaction_info xactions_cur%ROWTYPE;
variable
有一些PL/SQL指令在使用隐式定义记录时没有使用%ROWTYPE属性，比如游标FOR循环或触发器中的:old和:new记录。
DELCARE
CURSOR xaction_cur IS
SELECT action,timeamp,holding
FROM portfolios
WHERE account_nbr=‘37’
;
BEGIN
FOR xaction_rec in xactions_cur
LOOP
IF xactions_rec.holding=‘ORCL’
THEN
notify_shareholder;
END IF;
END LOOP;
使用记录
用户可以给记录赋值、将值传递给其他程序。记录作为一种复合数据结构意味作他有两个层次可用。用户可以引用整个记录，使用select into或fetch转移所有域，也可以将整个记录传递给一个程序或将所有域的值赋给另一个记录。在更低的层次，用户可以处理记录内单独的域，用户可以给单独的域赋值或者在单独的域上运行布尔表达式，也可以将一个或更多的域传递给另一个程序。
引用记录
记录由域组成，访问记录中的域使用点（.）符号。我们使用上面的例子看看
DELCARE
TYPE stock_quote_rec IS RECORD
(symbol stock.symbol%TYPE
,bid NUMBER(10,4)
,ask NUMBER(10,4)
,volume NUMBER NOT NULL:=0
,exchange VARCHAR2(6) DEFAULT ‘NASDAQ’
);
TYPE detailed_quote_rec IS RECORD
(quote stock_quote_rec
,timestamp date
,bid_size NUMBER
,ask.size NUMBER
,last_tick VARCHAR2(4)
);
real_time_detail detail_quote_rec;
BEGIN
real_time_detail.bid_size:=1000;
real_time_detail.quote.volume:=156700;
log_quote(real_time_detail.quote);
给记录赋值
给记录或记录中的域赋值的方法有几种，可以使用SELECT INTO或FETCH给整个记录或单独的域赋值， 可以将整个记录的值赋给其他记录，也可以通过给每个域赋值来得到记录，以下我们通过实例讲解每一种赋值方法。
1、使用SELECT INTO
使用SELECT INTO给记录赋值要将记录或域放在INTO子串中，INTO子串中的变量与SELECT中列的位置相对应。
例：
DECLARE
stock_info1 stocks%ROWTYPE;
stock_info2 stocks%ROWTYPE;
BEGIN
SELECT symbol,exchange
INTO stock_info1.symbol,stock_info1.exchange
FROM stocks
WHERE symbol=‘ORCL’;
SELECT * INTO stock_info2 FROM stocks
WHERE symbol=‘ORCL’;
2、使用FETCH
如果SQL语句返回多行数据或者希望使用带参数的游标，那么就要使用游标，这种情况下使用FETCH语句代替INSTEAD INTO是一个更简单、更有效率的方法，但在安全性较高的包中FETCH的语法如下：
FETCH cursor_name INTO variable;
我们改写上面的例子：
DECLARE
CURSOR stock_cur(symbol_in VARCHAR2) IS
SELECT symbol,exchange,begin_date
FROM stock
WHERE symbol=UPPER(symbol_in);
stock_info stock_cur%ROWTYPE
BEGIN
OPEN stock_cur(‘ORCL’);
FETCH stock_cur INTO stock_info;
使用赋值语句将整个记录复制给另一个记录是一项非常有用的技术，不过记录必须精确地被声明为相同的类型，不能是基于两个不同的TYPE语句来获得相同的结构。
例：
DECLARE
TYPE stock_quote_rec IS RECORD
(symbol stocks.symbol%TYPE
,bid NUMBER(10,4)
,ask number(10,4)
,volume NUMBER
);
TYPE stock_quote_too IS RECORD
(symbol stocks.symbol%TYPE
,bid NUMBER(10,4)
,ask number(10,4)
,volume NUMBER
);
--这两个记录看上去是一样的，但实际上是不一样的
stock_one stocks_quote_rec;
stock_two stocks_quote_rec;
--这两个域有相同的数据类型和大小
stock_also stock_rec_too；–与stock_quote_rec是不同的数据类型
BEGIN
stock_one.symbol:=‘orcl’;
stock_one.volume:=1234500;
stock_two：=stock_one;–正确
syock_also：=stock_one;–错误，数据类型错误
stock_also.symbol:=stock_one.symbol;
stock_also.volume:=stock_one.volume;
记录不能用于INSERT语句和将记录直接用于比较，下面两种情况是错误的：
INSERT INTO stocks VALUES (stock_record);
和
IF stock_rec1>stock_rec2 THEN
要特别注意考试中试题中有可能用%ROWTYPE来欺骗你，但这是错误的，记住这一点。还有可能会出现用记录排序的情况，ORACLE不支持记录之间的直接比较。对于记录比较，可以采用下面的两个选择：
. 设计一个函数，该函数返回scalar数据类型，使用这个函数比较记录，如
IF sort_rec(stock_one)>sort_rec(stock_two) THEN
. 可以使用数据库对象，数据库对象可以使用order或map方法定义，允许oracle对复合数据类型进行比较。关于数据库对象的讨论已经超越了本文的范围，要详细了解数据库对象，可以查阅oracle手册。
PL/SQL集合
集合与其他语言中的数组相似，在ORACLE7.3及以前的版本中只有一种集合称为PL/SQL表，这种类型的集合依然保留，就是索引（INDEX_BY）表，与记录相似，集合在定义的时候必须使用TYPE语句，然后才是创建和使用这种类型的变量。
集合的类型
PL/SQL有三种类型的集合
. Index_by表
. 嵌套表
. VARRAY
这三种类型的集合之间由许多差异，包括数据绑定、稀疏性(sparsity)、数据库中的存储能力都不相同。绑定涉及到集合中元素数量的限制，VARRAY集合中的元素的数量是有限，Index_by和嵌套表则是没有限制的。稀疏性描述了集合的下标是否有间隔，Index_by表总是稀疏的，如果元素被删除了嵌套表可以是稀疏的，但VARRAY类型的集合则是紧密的，它的下标之间没有间隔。
Index_by表不能存储在数据库中，但嵌套表和VARRAY可以被存储在数据库中。
虽然这三种类型的集合有很多不同之处，但他们也由很多相似的地方：
. 都是一维的类似数组的结构
. 都有内建的方法
. 访问由点分隔
Index_by表
Index_by表集合的定义语法如下：
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX
BY BINARY_INTERGET;
这里面重要的关键字是INDEX BY BINARY_INTERGET，没有这个关键字，那么集合将是一个嵌套表，element_type可以是任何合法的PL/SQL数据类型，包括：PLS/INTEGER、SIGNTYPE、和BOOLEAN。其他的集合类型对数据库的数据类型都有限制，但Ind
2.1.3 参照变量
参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写 pl/sql 程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。
一、 游标(cursor)
参考：Oracle_lhr_游标.docx
2.1.4 PL/SQL中的变量赋值
在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：
variable := expression ;
variable 是一个PL/SQL变量, expression 是一个PL/SQL 表达式.
BOOLEAN 型变量赋值：
布尔值只有TRUE, FALSE及 NULL 三个值，其中空值在参加算数运算时，结果仍为空值。
例：DECLARE
done BOOLEAN;
/* the following statements are legal: */
BEGIN
done := FALSE;
WHILE NOT done LOOP
----do something
Dbms_output.put_line(‘hello!’);
Done := TRUE;
END LOOP;
END;
2.2 type定义数据类型
2.2.1 CREATE OR REPLACE TYPE
2种定义方式
2.2.1.1 已存在数据类型
create or replace type type_glid_lhr is table of number;
create or replace type type_glid_lhr is table of varchar2(255);
–只能在包声明、块声明等地方定义,不能在 方案级 使用
– 错误： create or replace type type_a is table of xb_location%rowtype；
DECLARE
TYPE type_a IS TABLE OF xb_location%ROWTYPE;
v_a_type type_a;
BEGIN
NULL;
END;
2.2.1.2 不存在原数据类型
– 只能在方案级定义一种新类型
– create or replace TYPE objemp AS OBJECT(maxsal NUMBER, minsal NUMBER);
– 错误：
DECLARE
TYPE objemp AS OBJECT(maxsal NUMBER, minsal NUMBER);
v_a_type objemp;
BEGIN
NULL;
END;
– 正确：
create or replace TYPE objemp as OBJECT(maxsal NUMBER, minsal NUMBER);
– 定义表： 正确,用as或is都可以：
CREATE OR REPLACE TYPE tabemp AS TABLE OF objemp;
CREATE OR REPLACE TYPE tabemp is TABLE OF objemp;
DECLARE
TYPE tabemp is TABLE OF objemp; --这里只能用is
v_a_type tabemp;
BEGIN
NULL;
END;
2.2.2 例一
DECLARE
v_dept_row dept%ROWTYPE;
BEGIN
v_dept_row.deptno := 50;
v_dept_row.dname := ‘repair’;
v_dept_row.loc := ‘BeiJing’;
INSERT INTO dept VALUES v_dept_row;
END;
DECLARE
v_dept_row dept%ROWTYPE;
BEGIN
v_dept_row.deptno := &deptno;
v_dept_row.dname := ‘procduct’;
v_dept_row.loc := ‘Sydeny’;
UPDATE dept SET ROW = v_dept_row WHERE deptno = v_dept_row.deptno;
END;
----------------练习1----------------
create or replace type phone_type is Table of varchar2(50);
create table tb_info(
id number(6) primary key,
vid varchar2(20) not null,
vname varchar2(20) not null,
vphone phone_type
)nested table vphone Store as phone_table;
INSERT INTO tb_info
VALUES
(1, ‘scce001’, ‘dog’, phone_type(‘110’, ‘119’, ‘120’, ‘911’));
DECLARE
v_phone_type phone_type;
BEGIN
SELECT vphone INTO v_phone_type FROM tb_info WHERE id = 1;
FOR i IN 1 … v_phone_type.Count LOOP
Dbms_Output.put_line(v_phone_type(i));
END LOOP;
END;
------------------练习2-------------------------------------------
create or replace type obj_goods as Object
(
price number(5),
quantity varchar2(14),
name varchar2(50),
–定义方法
member Function getMustPay Return number,–实际付款
member Function getChange Return number–找零
);
CREATE OR REPLACE TYPE BODY obj_goods AS
MEMBER FUNCTION getMustPay RETURN NUMBER IS
BEGIN
RETURN Self.price * Self.quantity;
END;
MEMBER FUNCTION getChange RETURN NUMBER IS
BEGIN
RETURN Self.price;
END;
END;
DECLARE
v_obj_goods obj_goods;
BEGIN
v_obj_goods := obj_goods(40, ‘5’, ‘香烟’);
Dbms_Output.put_line(‘商品数量：’ || v_obj_goods.quantity);
Dbms_Output.put_line(‘商品名称：’ || v_obj_goods.name);
Dbms_Output.put_line(‘商品总价：’ || v_obj_goods.getMustPay);
Dbms_Output.put_line(‘商品单价：’ || v_obj_goods.price);
END;
2.2.3 例二
create table parent(
id number(10),
name varchar2(100),
title varchar2(10) );
create table child(
id number(10),
parent_id number(10),
child_name varchar2(100),
child_title varchar2(10),
child_content varchar2(200),
child_time timestamp );
create sequence seq_p_c_id
minvalue 1 maxvalue 9999999999
start with 1 increment by 1
nocache;
drop type t_child_lst_map;
drop type t_child_lst;
drop type t_parent_lst;
CREATE OR REPLACE TYPE t_parent AS OBJECT
(
NAME VARCHAR2(100),
title VARCHAR2(10)
);
CREATE OR REPLACE TYPE t_child AS OBJECT
(
child_name VARCHAR2(100),
child_title VARCHAR2(10),
child_content VARCHAR2(200)
)
;
create or replace type t_parent_lst as table of t_parent;
create or replace type t_child_lst as table of t_child;
create or replace type t_child_lst_map as table of t_child_lst;
CREATE OR REPLACE PROCEDURE proc_ins_parent_child(i_parent_lst IN t_parent_lst, --parent列表
i_child_map_lst IN t_child_lst_map, --child列表集合，一个map元素对应一个child_lst，其下标与 parent列表的下标相同。
o_ret OUT NUMBER) AS
var_parent t_parent;
var_child_lst t_child_lst;
var_child t_child;
var_parent_id NUMBER;
var_child_id NUMBER;
BEGIN
FOR i IN 1 … i_parent_lst.count LOOP
–取得parent各列的值
var_parent := i_parent_lst(i); --取得parent_id;
SELECT seq_p_c_id.nextVal INTO var_parent_id FROM dual; --插入parent表
INSERT INTO PARENT
(id, NAME, title)
VALUES
(var_parent_id, var_parent.name, var_parent.title); --取得该parent对应的child列表
var_child_lst := i_child_map_lst(i);
FOR j IN 1 … var_child_lst.count LOOP
var_child := var_child_lst(j); --取得child_id;
SELECT seq_p_c_id.nextVal INTO var_child_id FROM dual; --插入child表
INSERT INTO child
(id,
parent_id,
child_name,
child_title,
child_content,
child_time)
VALUES
(var_child_id,
var_parent_id,
var_child.child_name,
var_child.child_title,
var_child.child_content,
systimestamp);
END LOOP;
END LOOP;
o_ret := 0;
EXCEPTION
WHEN OTHERS THEN
BEGIN
o_ret := -1;
RAISE;
END;
END proc_ins_parent_child;
2.2.4 例三
drop type t_sms_message_lst;
drop type t_sms_message;
–此类型中，添加了pk_id
CREATE OR REPLACE TYPE t_sms_message AS OBJECT
(
task_id NUMBER(32),
sender_num VARCHAR2(21),
mobile_no VARCHAR2(21),
param_value VARCHAR2(900),
pk_id NUMBER(32)
)
;
create or replace type t_sms_message_lst as table of t_sms_message;
drop procedure proc_sel_sms_message;
CREATE OR REPLACE PROCEDURE proc_sel_sms_message(in_task_id IN NUMBER,
in_count IN NUMBER,
out_sms_message_lst OUT t_sms_message_lst,
out_ret OUT NUMBER) AS
var_cr_sms_message SYS_REFCURSOR;
var_m_row_id VARCHAR2(1000);
var_m_task_id sms_message.task_id%TYPE;
var_m_sender_num sms_message.sender_num%TYPE;
var_m_mobile_no sms_message.mobile_no%TYPE;
var_m_param_value sms_message.param_value%TYPE;
var_m_last_send_time sms_message.last_send_time%TYPE;
var_t_sms_message t_sms_message;
var_total_send_amount INTEGER;
BEGIN
--查询语句增加了pk_id搜索项
OPEN var_cr_sms_message FOR
SELECT ROWID AS row_id,
task_id,
sender_num,
mobile_no,
param_value,
pk_id
FROM sms_message
WHERE task_id = in_task_id
AND (sms_message.last_send_time IS NULL OR
sms_message.last_send_time <>
(SELECT last_send_time
FROM sms_task
WHERE task_id = in_task_id))
AND rownum <= in_count
FOR UPDATE;
out_sms_message_lst := t_sms_message_lst();
SELECT last_send_time
INTO var_m_last_send_time
FROM sms_task
WHERE task_id = in_task_id;
LOOP
--fetch语句增加了pk_id项
FETCH var_cr_sms_message
INTO var_m_row_id,
var_m_task_id,
var_m_sender_num,
var_m_mobile_no,
var_m_param_value,
var_m_pk_id;
EXIT WHEN var_cr_sms_message%NOTFOUND;
--输出对象类型中增加了pk_id项
var_t_sms_message := t_sms_message(var_m_task_id,
var_m_sender_num,
var_m_mobile_no,
var_m_param_value,
var_m_pk_id);
out_sms_message_lst.extend;
out_sms_message_lst(out_sms_message_lst.count) := var_t_sms_message;
UPDATE sms_message
SET last_send_time = var_m_last_send_time
WHERE ROWID = var_m_row_id;
END LOOP;
CLOSE var_cr_sms_message;
out_ret := 0;
EXCEPTION
WHEN OTHERS THEN
BEGIN
out_ret := -1;
RAISE;
END;
END proc_sel_sms_message;
ORACLE基本数据类型总结
2013-08-17 21:04 by 潇湘隐者, 138943 阅读, 5 评论, 收藏, 编辑
ORACLE基本数据类型（亦叫内置数据类型 built-in datatypes)可以按类型分为：字符串类型、数字类型、日期类型、LOB类型、LONG RAW& RAW类型、ROWID & UROWID类型。
在讲叙字符串类型前，先要讲一下编码。字符串类型的数据可依编码方式分成数据库字符集（CHAR/VARCHAR2/CLOB/LONG)和国际字符集(NCHAR/NVARCHAR2/NCLOB)两种。数据库中的字符串数据都通过字符集将字符转换为数字后(二进制)，才存储到数据块中。通过不同的编码集转换，即便是相同的字符，也可能会转换成不同的二进制编码。这也是产生乱码的原因。数据库的编码格式一般是在创建数据库时指定的。当然也可以修改数据库的编码。
查看数据库视图所包含的数据类型:SELECT * FROM DBA_TYPES WHERE OWNER IS NULL.具体细节情况参见Oracle? Database SQL Language Quick Reference 10/11g 或官方文档
一 字符串类型
字符串数据类型还可以依据存储空间分为固定长度类型（CHAR/NCHAR) 和可变长度类型（VARCHAR2/NVARCHAR2)两种.
所谓固定长度：是指虽然输入的字段值小于该字段的限制长度，但是实际存储数据时，会先自动向右补足空格后，才将字段值的内容存储到数据块中。这种方式虽然比较浪费空间，但是存储效率较可变长度类型要好。同时还能减少数据行迁移情况发生。
所谓可变长度：是指当输入的字段值小于该字段的限制长度时，直接将字段值的内容存储到数据块中，而不会补上空白，这样可以节省数据块空间。
1.1：CHAR类型 CHAR(size [BYTE | CHAR])
CHAR类型，定长字符串，会用空格填充来达到其最大长度。非NULL的CHAR（12）总是包含12字节信息。CHAR字段最多可以存储2,000字节的信息。如果创建表时，不指定CHAR长度，则默认为1。另外你可以指定它存储字节或字符，例如 CHAR(12 BYTYE) CHAR(12 CHAR).一般来说默认是存储字节，你可以查看数据库参数
NLS_LENGTH_SEMANTICS的值。
SQL Code
SQL> show parameter nls_length_semantics;
NAME TYPE VALUE
nls_length_semantics string BYTE
eg:
CREATE TABLE TEST
(
NAME_OLD CHAR(10),
NAME_NEW CHAR(10 CHAR)
)
INSERT INTO TEST
( NAME_OLD, NAME_NEW)
SELECT ‘ABCDEFGHIJ’ , ‘你清除字节与字符’ FROM DUAL;
COMMIT;
INSERT INTO TEST
( NAME_OLD, NAME_NEW)
SELECT ‘你清除字节与字符’ , ‘ABCDEFGHIJ’ FROM DUAL;
ORA-12899: 列 “SYS”.“TEST”.“NAME_OLD” 的值太大 (实际值: 24, 最大值: 10)
注意：数据库的NLS_CHARACTERSET 为AL32UTF8，即一个汉字占用三到四个字节。如果NLS_CHARACTERSET为ZHS16GBK，则一个字符占用两个字节。
如果串的长度小于或等于250（0x01~0xFA）， Oracle 会使用1 个字节来表示长度。对于所有长度超过250 的串，都会在一个标志字节0xFE 后跟有两个字节来表示长度。因此，如果有一个包含“Hello World”的VARCHAR2(80)，则在块中可能如图12.-1 所示
clip_image002
1.2： NCHAR类型
这是一个包含UNICODE格式数据的定长字符串。NCHAR字段最多可以存储2,000字节的信息。它的最大长度取决于国家字符集。另外查询时，如果字段是NCHAR类型，则需要如下书写
SELECT translated_description FROM product_descriptions
WHERE translated_name = N’LCD Monitor 11/PM’;
1.3 VARCHAR类型
不要使用VARCHAR数据类型。使用VARCHAR2数据类型。虽然VARCHAR数据类型目前是VARCHAR2的同义词，VARCHAR数据类型将计划被重新定义为一个单独的数据类型用于可变长度的字符串相比，具有不同的比较语义。
1.4： VARCHAR2类型
变长字符串，与CHAR类型不同，它不会使用空格填充至最大长度。VARCHAR2最多可以存储4,000字节的信息。
1.5： NVARCHAR2类型
这是一个包含UNICODE格式数据的变长字符串。 NVARCHAR2最多可以存储4,000字节的信息。
二. 数字类型
2.1 NUMBER类型
NUMBER(P,S)是最常见的数字类型，可以存放数据范围为10130~10126（不包含此值)，需要1~22字节(BYTE)不等的存储空间。
P 是Precison的英文缩写，即精度缩写，表示有效数字的位数，最多不能超过38个有效数字
S是Scale的英文缩写，可以使用的范围为-84~127。Scale为正数时，表示从小数点到最低有效数字的位数，它为负数时，表示从最大有效数字到小数点的位数
下面是官方文档的示例
Actual Data
Specified As
Stored As
123.89
NUMBER
123.89
123.89
NUMBER(3)
124
123.89
NUMBER(6,2)
123.89
123.89
NUMBER(6,1)
123.9
123.89
NUMBER(3)
124
123.89
NUMBER(4,2)
exceeds precision
123.89
NUMBER(6,-2)
100
.01234
NUMBER(4,5)
.01234
.00012
NUMBER(4,5)
.00012
.000127
NUMBER(4,5)
.00013
.0000012
NUMBER(2,7)
.0000012
.00000123
NUMBER(2,7)
.0000012
1.2e-4
NUMBER(2,5)
0.00012
1.2e-5
NUMBER(2,5)
0.00001
2.2 INTEGER类型
INTEGER是NUMBER的子类型，它等同于NUMBER（38,0），用来存储整数。若插入、更新的数值有小数，则会被四舍五入。
例如：
CREATE TABLE TEST
(
ID INTEGER
)
查看表TEST的DDL定义如下所示
CREATE TABLE “SYS”.“TEST”
( “ID” NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “SYSTEM” ;
INSERT INTO TEST
SELECT 12.34 FROM DUAL;
INSERT INTO TEST
SELECT 12.56 FROM DUAL;
SQL> SELECT * FROM TEST;
ID
12
13
2.3 浮点数
浮点数可以有一个十进制数点任何地方从第一个到最后一个数字，或者可以在所有有没有小数点。指数可能（可选） 用于以下数量增加的范围 （例如， 1.777e-20)。刻度值不适用于浮点数字，因为可以显示在小数点后的位数的数量不受限制。
二进制浮点数不同数量的值由 Oracle 数据库内部存储的方式。使用小数精度数存储值。完全相同号码存储范围和数量由支持的精度内的所有文本。正是因为使用小数精度（数字 0 到 9） 表示文本存储文本。使用二进制精度 （数字 0 和 1） 存储二进制浮点数。这种存储方案不能代表所有确切地使用小数精度的值。频繁地，将值从十进制转换为二进制的精度时出现的错误时撤消值回从二进制转换为十进制精度。在字面 0.1 是一个这样的例子。
Oracle 数据库提供了专为浮点数的两种数值数据类型：
BINARY_FLOAT
BINARY_FLOAT 是 32 位、 单精度浮点数字数据类型。可以支持至少6位精度,每个 BINARY_FLOAT 的值需要 5 个字节，包括长度字节。
BINARY_DOUBLE
BINARY_DOUBLE 是为 64 位，双精度浮点数字数据类型。每个 BINARY_DOUBLE 的值需要 9 个字节，包括长度字节。
在数字的列中，浮点数有小数精度。在 BINARY_FLOAT 或 BINARY_DOUBLE 的列中，浮点数有二进制的精度。二进制浮点数支持的特殊值无穷大和 NaN （不是数字）。
您可以指定列在表 2-4 范围内的浮点数。"数字文本"中定义了用于指定浮点数的格式。
Table 2-3 Floating Point Number Limits
Value
Binary-Float
Binary-Double
Maximum positive finite value
3.40282E+38F
1.79769313486231E+308
Minimum positive finite value
1.17549E-38F
2.22507485850720E-308
2.5 FLOAT类型
FLOAT类型也是NUMBER的子类型。
Float(n),数 n 指示位的精度，可以存储的值的数目。N 值的范围可以从 1 到 126。若要从二进制转换为十进制的精度，请将 n 乘以 0.30103。要从十进制转换为二进制的精度，请用 3.32193 乘小数精度。126 位二进制精度的最大值是大约相当于 38 位小数精度。
三. 日期类型
日期类型用于存储日期数据，但是并不是使用一般的格式（2012-08-08）直接存储到数据库的。
3.1 DATE类型
DATE是最常用的数据类型，日期数据类型存储日期和时间信息。虽然可以用字符或数字类型表示日期和时间信息，但是日期数据类型具有特殊关联的属性。为每个日期值，Oracle 存储以下信息： 世纪、 年、 月、 日期、 小时、 分钟和秒。一般占用7个字节的存储空间。
3.2 TIMESTAMP类型
这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同，因为TIMESTAMP可以包含小数秒，带小数秒的TIMESTAMP在小数点右边最多可以保留9位
3.3 TIMESTAMP WITH TIME ZONE类型
这是TIMESTAMP类型的变种，它包含了时区偏移量的值
3.4 TIMESTAMP WITH LOCAL TIME ZONE类型
3.5 INTERVAL YEAR TO MOTH
3.6 INTERVAL DAY TO SECOND
四. LOB类型
内置的LOB数据类型包括BLOB、CLOB、NCLOB、BFILE（外部存储）的大型化和非结构化数据，如文本、图像、视屏、空间数据存储。BLOB、CLOB、NCLOB类型
4.1 CLOB 数据类型
它存储单字节和多字节字符数据。支持固定宽度和可变宽度的字符集。CLOB对象可以存储最多 (4 gigabytes-1) * (database block size) 大小的字符
4.2 NCLOB 数据类型
它存储UNICODE类型的数据，支持固定宽度和可变宽度的字符集，NCLOB对象可以存储最多(4 gigabytes-1) * (database block size)大小的文本数据。
4.3 BLOB 数据类型
它存储非结构化的二进制数据大对象，它可以被认为是没有字符集语义的比特流，一般是图像、声音、视频等文件。BLOB对象最多存储(4 gigabytes-1) * (database block size)的二进制数据。
4.4 BFILE 数据类型
二进制文件，存储在数据库外的系统文件，只读的，数据库会将该文件当二进制文件处理
五. RAW & LONG RAW类型
5.1 LONG类型
它存储变长字符串，最多达2G的字符数据（2GB是指2千兆字节， 而不是2千兆字符），与VARCHAR2 或CHAR 类型一样，存储在LONG 类型中的文本要进行字符集转换。ORACLE建议开发中使用CLOB替代LONG类型。支持LONG 列只是为了保证向后兼容性。CLOB类型比LONG类型的限制要少得多。 LONG类型的限制如下：
1.一个表中只有一列可以为LONG型。(Why?有些不明白)
2.LONG列不能定义为主键或唯一约束，
3.不能建立索引
4.LONG数据不能指定正则表达式。
5.函数或存储过程不能接受LONG数据类型的参数。
6.LONG列不能出现在WHERE子句或完整性约束（除了可能会出现NULL和NOT NULL约束）
官方文档描叙如下：
The use of LONG values is subject to these restrictions:
A table can contain only one LONG column.
You cannot create an object type with a LONG attribute.
LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
LONG columns cannot be indexed.
LONG data cannot be specified in regular expressions.
A stored function cannot return a LONG value.
You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.
Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
In addition, LONG columns cannot appear in these parts of SQL statements:
GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
The UNIQUE operator of a SELECT statement
The column list of a CREATE CLUSTER statement
The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
SQL built-in functions, expressions, or conditions
SELECT lists of queries containing GROUP BY clauses
SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
SELECT lists of CREATE TABLE … AS SELECT statements
ALTER TABLE … MOVE statements
SELECT lists in subqueries in INSERT statements
5.2 LONG RAW 类型，能存储2GB 的原始二进制数据（不用进行字符集转换的数据）
5.3 RAW类型
用于存储二进制或字符类型数据，变长二进制数据类型，这说明采用这种数据类型存储的数据不会发生字符集转换。这种类型最多可以存储2,000字节的信息
六. ROWID & UROWID类型
在数据库中的每一行都有一个地址。然而，一些表行的地址不是物理或永久的，或者不是ORACLE数据库生成的。
例如，索引组织表行地址存储在索引的叶子，可以移动。
例如,外部表的ROWID（如通过网关访问DB2表）不是标准的ORACLE的rowid。
ORACLE使用通用的ROWID（UROWIDs）的存储地址的索引组织表和外表。索引组织表有逻辑urowids的，和国外表的外urowids，。UROWID这两种类型的存储在ROWID伪（堆组织的表的物理行id）。
创建基于逻辑的rowid在表中的主键。逻辑的rowid不会改变，只要主键不改变。索引组织表的ROWID伪UROWID数据类型。你可以访问这个伪列，你会堆组织表的ROWID伪（即使用一个SELECT …ROWID语句）。如果你想存储的rowid索引组织表，那么你就可以定义一列的表型UROWID到列检索值的ROWID伪。
Oracle 11g 数据类型
-
字符类型
数据类型
长度
说明
CHAR(n BYTE/CHAR)
默认1字节，n值最大为2000
末尾填充空格以达到指定长度，超过最大长度报错。默认指定长度为字节数，字符长度可以从1字节到四字节。
NCHAR(n)
默认1字符，最大存储内容2000字节
末尾填充空格以达到指定长度，n为Unicode字符数。默认为1字节。
NVARCHAR2(n)
最大长度必须指定，最大存储内容4000字节
变长类型。n为Unicode字符数
VARCHAR2(n BYTE/CHAR)
最大长度必须指定，至少为1字节或者1字符，n值最大为4000
变长类型。超过最大长度报错。默认存储的是长度为0的字符串。
VARCHAR
同VARCHAR2
不建议使用
-
数字类型
数据类型
长度
说明
NUMBER(p[,s])
1-22字节。
P取值范围1到38
S取值范围-84到127
存储定点数，值的绝对值范围为1.0 x 10 -130至1.0 x 10 126。值大于等于1.0 x 10 126时报错。p为有意义的10进制位数，正值s为小数位数，负值s表示四舍五入到小数点左部多少位。
BINARY_FLOAT
5字节，其中有一长度字节。
32位单精度浮点数类型。
符号位1位，指数位8位，尾数位23位。
BINARY_DOUBLE
9字节，其中有一长度字节。
64位双精度浮点数类型。
-
时间、时间间隔类型
时间字段可取值范围：
时间字段
时间类型有效值
时间间隔类型有效值
YEAR
-4712至9999，包括0
任何整数
MONTH
01至12
0至11
DAY
01至31
任何整数
HOUR
00 至 23
0 至 23
MINUTE
00 至 59
0至 59
SECOND
00 to 59.9(n)，9(n)不适用与DATE类型
0 to 59.9(n)
TIMEZONE_HOUR
-1至14，不适用与DATE和TIMESTAMP类型
不可用
TIMEZONE_MINUTE
00至59，不适用与DATE和TIMESTAMP类型
不可用
TIMEZONE_REGION
不可用
TIMEZONE_ABBR
不可用
时间、时间间隔类型：
数据类型
长度
说明
DATE
7字节
默认值为SYSDATE的年、月，日为01。包含一个时间字段，若插入值没有时间字段，则默认值为：00:00:00 or 12:00:00 for 24-hour and 12-hour clock time。没有分秒和时间区。
TIMESTAMP [(fractional_seconds_precision)]
7至11字节
fractional_seconds_precision为Oracle存储秒值小数部分位数，默认为6，可选值为0到9。没有时间区。
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
13字节
使用UTC，包含字段YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_
HOUR, TIMEZONE_MINUTE
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE
7至11字节
存时使用数据库时区，取时使用回话的时区。
INTERVAL YEAR [(year_precision)] TO MONTH
5字节
包含年、月的时间间隔类型。year_precision是年字段的数字位数，默认为2，可取0至9。
INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]
11字节
day_precision是月份字段的数字位数，默认为2，可取0至9。
TO_DATE()、DATE使用的时间字段值都是午夜值。或者使用TRUNC()函数进行过滤，确保时间字段为午夜值。
时间和时间间隔类型操作规则：
在DATE和TIMESTAMP（会被转化为DATE类型值）类型上加、减NUMBER类型常量，该常量单位为天数。
所有TIMESTAMP类型运算都以UTC时间为准。即对于TIMESTAMP WITH LOCAL TIME ZONE来说，先转化为UTC时间，计算完成后再转化回来。
INTERVAL YEAR TO MONTH常量：
INTERVAL‘year-month’YEAR/MONTH(precision) TO MONTH
year位数超过precision时，返回一个错误。
其中precision为最大的位数，默认为2，可取0到9。
例子：INTERVAL ‘123-2’ YEAR(3) TO MONTH 、
INTERVAL ‘123’ YEAR(3) 、
INTERVAL ‘300’ MONTH(3)。
INTERVAL DAY TO SECOND常量：
INTERVAL ‘n/time_expr/n time_expr’ DAY/HOUR/MINUTE(leading_precision) TO HOUR/MINUTE/SECOND(fractional_second_precision)
INTERVAL ‘n/time_expr’ SECOND(leading_precision, fractional_second_precision)
time_expr格式：HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n] 若n大于分秒精度，则四舍五入n。
只有当第一个字段是DAY时，才可以使用n time_expr。
leading_precision默认为2，可取0至9。
-
大对象类型
数据类型
长度
说明
BLOB
最大为(4GB-1)*数据库块大小
存储非结构化二进制文件。支持事务处理。
CLOB
最大为(4GB-1)*数据库块大小
存储单字节或者多字节字符数据。支持事务处理。
NCLOB
最大为(4GB-1)*数据库块大小
存储Unicode数据。支持事务处理。
BFILE
最大为2 32-1字节
LOB地址指向文件系统上的一个二进制文件，维护目录和文件名。不参与事务处理。只支持只读操作。
LOB列包含一个LOB地址，指向数据库内或者数据库外的LOB类型值。
5. 其他类型
数据类型
长度
说明
LONG
最大为2GB
变长类型，存储字符串。创建表时不要使用该类型。
RAW(n)
最大2000字节，n为字节数，必须指定n
变长类型，字符集发生变化时不会改变值。
LONG RAW
最大为2GB
变长类型，不建议使用，建议转化为BLOB类型，字符集发生变化时不会改变值。
ROWID
10字节
代表记录的地址。显示为18位的字符串。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。
UROWID(n)
ROWID：数据对象编号32位(6个字符)、文件编号10位(3个字符)、块编号22位(6个字符)、行编号16位(3个字符)
使用dqms_rowid包获得ROWID信息：
select dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, dbms_rowid.rowid_row_number(rowid) num
文章参考：
http://blog.itpub.net/26736162/viewspace-2149685
上一篇： 西数战略调整：HGST品牌将逐步退出！闪迪也快了？
下一篇： 利用telnet 发送qq邮件