Oracle PL/SQL开发入门（第四弹：数据类型）

字符类型

我们来看集中常用的字符类型。

CHAR

如：

DECLARE   v_name CHAR(2 BYTE);   v_name2 CHAR(2 CHAR);   v_name3 CHAR;   v_name4 CHAR(20);BEGIN  v_name:='ab';       --正确，2个字节的字符串  v_name:='中国';   --错误，大于2个字节   v_name2:='中国';    --正确，2个字符  v_name3:=1;         --正确，单个字节  v_name4:='This is string';  --为CHAR赋字符串值  DBMS_OUTPUT.put_line(LENGTH(v_name4));--输出字符串长度END;

VARCHAR2

如：

DECLARE  v_name VARCHAR2(25);  v_name1 VARCHAR2(25 BYTE);  v_name2 VARCHAR2(25 CHAR);     --v_name3 VARCHAR2;          --错误，必须要为VARCHAR2指定长度值BEGIN  v_name:='中华人民共和国';    --为变量赋值，并输出变量的长度  DBMS_OUTPUT.put_line('v_name变量的长度为：'||LENGTH(v_name)||'字节');  v_name1:='中华人民共和国';  DBMS_OUTPUT.put_line('v_name1变量的长度为：'||LENGTH(v_name1)||'字节');    v_name2:='中华人民共和国';  DBMS_OUTPUT.put_line('v_name1变量的长度为：'||LENGTH(v_name2)||'字节');    END; 

LONG和LONG RAW

在Oracle 11g中，LONG和LONG RAW数据类型仅是为了保持向后兼容性，我们应该尽量避免使用这两个数据类型。
对于LONG类型，可以使用VARCHAR2(32760)、BLOB、CLOB或NCLOB来代替。
对于LONG RAW类型可以使用BLOB来代替。
LONG类型和VARCHAR2非常相似，但是LONG类型的最大长度是32760字节，比VARCHAR2的最大长度少了7字节。
LONG RAW类型用来存储二进制数据和字节字符串，LONG RAW数据和LONG数据相似，最大字节数也为32760。
Oracle数据库同样提供了LONG和LONG RAW列类型，与PL/SQL不同的是，这两个类型的最大存储数据量是2GB。
在SQL语句中，PL/SQL将LONG类型的值当做VARCHAR2进行处理而不是LONG类型，如果长度超过VARCHAR2允许的最大长度即4000字节时，Oracle会自动转换成LONG类型。

ROWID和UROWID

每个Oracle数据表都有一个名为ROWID的伪列，这个伪列用来存放每一行数据的存储地址的二进制值。
每个ROWID的值是由18个字符组合进行表示的，ROWID又有物理ROWID和逻辑ROWID之分。物理ROWID用来标识普通数据表中的一行信息，而逻辑ROWID能够标识索引组织表中的一行信息。
物理的ROWID可以显著的加速数据检索的性能，因为只要行存在，物理ROWID值就不会改变。

NCHAR和NVARCHAR2

这两个类型是CHAR和VARCHAR2的Unicode版本，通常在开发多语言程序时非常有用。

数字类型

NUMBER

如：

DECLARE   v_num1 NUMBER:=3.1415926;           --结果：3.1415926   v_num2 NUMBER(3):=3.1415926;        --四舍五入等于3 --v_num2_1 NUMBER(3):=3145.1415926;   --错误，精度太高：ORA-06502:PL/SQL:数字或值错误:数值精度太高      v_num3 NUMBER(4,3):=3.1415926;      --结果：3.142 --v_num3_1 NUMBER(4,3):=314.123;      --错误，精度太高：ORA-06502:PL/SQL:数字或值错误:数值精度太高      v_num4 NUMBER(8,3):=31415.9267;     --四舍五入2位小数，结果为：31415.927   v_num5 NUMBER(4,-3):=3145611.789;   --由于为负3,要小数点左侧进行舍入3位结果为31000   v_num5_1 NUMBER(4,-3):=314.567895;  --舍入后的结果为0   v_num6 NUMBER(4,-1):=31451;         --舍入后结果31450   --v_num6_1 NUMBER(4,-1):=3145123;     --错误，精度太高：ORA-06502:PL/SQL:数字或值错误:数值精度太高   BEGIN  DBMS_OUTPUT.put_line('v_num1:='||v_num1);  DBMS_OUTPUT.put_line('v_num2:='||v_num2);  DBMS_OUTPUT.put_line('v_num3:='||v_num3);  DBMS_OUTPUT.put_line('v_num4:='||v_num4);        DBMS_OUTPUT.put_line('v_num5:='||v_num5);      DBMS_OUTPUT.put_line('v_num5_1:='||v_num5_1);    DBMS_OUTPUT.put_line('v_num6:='||v_num6);          END; 

PLS_INTEGER和BINARY_INTEGER

PLS_INTEGER和BINARY_INTEGER具有相同的取值范围，都是从-2147483647到+2147483647，PLS_INTEGER相对于NUMBER来说需要更少的空间来存储数据，在计算方面也比NUMBER更有效率。
NUMBER数据类型是以十进制格式进行存储的，为了进行算术运算，NUMBER必须要转换为二进制类型，因此效率比较慢。BINARY_INTEGER以2的补码二进制形式进行存储，可以直接进行计算二无需转换。
PLS_INTEGER与BINARY_INTEGER类似，也是使用2的补码格式进行计算，他们的区别是，如果在为PLS_INTEGER类型的变量赋的值溢出时，会触发异常；而为BINARY_INTEGER类型的变量赋的值溢出时，会将结果指派为NUMBER类型的拥有最大精度的类型，不会触发异常。

日期和时间类型

DATE

DATE类型用来存储时间和日期信息，包含世纪、年、月、日、时、分、秒，但是不包含秒的小数部分。DATE类型从世纪到秒每一部分是一个字节，占用7个字节。
DATE类型的有效日期范围是从公元前4712年1月1号到公元9999年12月31号，默认的日期格式是由Oracle的初始换参数NLS_DATA_FORMAT来设置的。

TIMESTAMP

TIMESTAMP与DATE类似，存储了年、月、日、时、分、秒，而且还存储秒字段的小数部分。

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE是TIMESTAMP的扩展，包含了时区偏移信息，时区偏移部分就是指当前时间和格林威治时间的差异部分。
可以通过查询V$TIMEZONE_NAMES系统视图来获取更多的时区的字符串表示形式，或者查询SESSIONTIMEZONE来获取当前会话的时区：SELECT SESSIONTIMEZONE FROM DUAL;

TIMESTAMP WITH LOCAL TIME ZONE

该类型存储的是数据库的时区，不管在PL/SQL代码中用的时区是什么，它总是使用数据库的时区。

INTERVAL类型

INTERVAL类型用于存储两个时间戳之间的时间间隔，分为两种：
- INTERVAL YEAR TO MONTH：用来存储和操纵年和月之间的时间间隔。
- INTERVAL DAY TO SECOND：用来存储和操纵天数、时、分和秒之间的时间间隔。
用法如下：

DECLARE   v_start      TIMESTAMP;                         --定义起始与结束时间戳类型   v_end        TIMESTAMP;   v_interval   INTERVAL YEAR TO MONTH;   v_year       NUMBER;   v_month      NUMBER;BEGIN   v_start := TO_TIMESTAMP ('2010-05-12', 'yyyy-MM-dd');  --赋指定的时间戳值   v_end := CURRENT_TIMESTAMP;                            --赋当前的时间戳值   v_interval := (v_end - v_start) YEAR TO MONTH;         --YEAR TO MONTH是INTERVAL表达式语法。   v_year := EXTRACT (YEAR FROM v_interval);              --提取年份和月份   v_month := EXTRACT (MONTH FROM v_interval);   --输出当前的INTERVAL类型的值   DBMS_OUTPUT.put_line ('当前的INTERVAL值为：' || v_interval);   --输出年份与月份值   DBMS_OUTPUT.put_line (   'INTERVAL年份为：'                         || v_year                         || CHR (13)                         || CHR (10)                         || 'INTERVAL月份为：'                         || v_month                        );   v_interval := INTERVAL '01-03' YEAR TO MONTH;            --直接为INTERVAL赋值   --输出INTERVAL的值   DBMS_OUTPUT.put_line ('当前的INTERVAL值为：' || v_interval);   v_interval := INTERVAL '01' YEAR;                        --直接为INTERVAL赋年份值   DBMS_OUTPUT.put_line ('当前的INTERVAL值为：' || v_interval);   --提取年份和月份   v_year := EXTRACT (YEAR FROM v_interval);   v_month := EXTRACT (MONTH FROM v_interval);   --输出值   DBMS_OUTPUT.put_line (   'INTERVAL年份为：'                         || v_year                         || CHR (13)                         || CHR (10)                         || 'INTERVAL月份为：'                         || v_month                        );   v_interval := INTERVAL '03' MONTH;                      --直接为INTERVAL赋月份   --输出月份值   DBMS_OUTPUT.put_line ('当前的INTERVAL值为：' || v_interval);END;/

布尔类型

Oracle数据库并不包含布尔类型，多数情况下使用CHAR(1)来代替布尔值，PL/SQL为了结构化程序的需要包含了布尔值，不能忘数据库中插入或者从数据库中检索出布尔类型的值。
BOOLEAN类型可以存储True、False和NULL值。

LOB对象类型

LOB类型又称为大型对象类型，包含了BFILE、BLOB、CLOB和NCLOB等类型，LOB类型最大可存储4GB的非结构数据，通常用来存储文本、图像、声音和视频等大型数据。
LOB类型和LONG、LONG RAW类型相比，不同之处在于：
- LOB类型可以作为对象类型的属性，但LONG类型不可以。
- LOB类型的最大值是4GB，但LONG只有2GB。
- LOB支持随机访问数据，但LONG只支持顺序访问。

引用类型

REF CURSOR

REF CURSOR类型的变量通常被称为游标变量，可以通过定义一个SYS_REFCURSOR类型的变量，从过程或函数中获取一个记录集。SYS_REFCURSOR是一个弱类型的REF CURSOR类型的引用类型，用法如下：

CREATE OR REPLACE FUNCTION selectallemployments   RETURN sys_refcursor           --定义一个返回sys_refcursor的函数AS   st_cursor   sys_refcursor;BEGIN   OPEN st_cursor FOR             --使用该函数查询所有的员工记录      SELECT *        FROM emp;   --返回指向游标的指针   RETURN st_cursor;END;//* Formatted on 2011/08/14 07:01 (Formatter Plus v4.8.8) */DECLARE   x       sys_refcursor;    --定义引用游标变量   v_emp   emp%ROWTYPE;      --定义获取游标结果的记录类型BEGIN   x := selectallemployments;--调用函数获取游标指针   --循环遍历游标指针   LOOP      FETCH x                --提取游标数据       INTO v_emp;      --当没有找到游标记录时则退出      EXIT WHEN x%NOTFOUND;      --输出记录信息      DBMS_OUTPUT.put_line (   '员工编号：'                            || v_emp.empno                            || '   员工名称：'                            || v_emp.ename                           );   END LOOP;END;

REF

REF用在对象类型中，REF类型就是一个指向对象类型实例的指针。

复合类型

复合类型是具有内部组件的类型，与标量类型的单一表现特征不一样，复合类型中可以包含多个标量类型作为其属性，复合类型包含了 记录、嵌套表、索引表和变长数组。

用户自定义子类型

自定义子类型，就是在标准类型的基础上进一步约束而创建的新类型。
可以使用标量类型作为基类型，如：

DECLARE   SUBTYPE empcounttype IS INTEGER ;          --定义子类型   empcount   empcounttype;                   --声明子类型变量BEGIN   SELECT COUNT (*)                           --查询emp表为子类型变量赋值     INTO empcount     FROM emp;   --输出员工人数   DBMS_OUTPUT.put_line ('员工人数为：' || empcount);END;

也可以使用记录类型%TYPE或%ROWTYPE来作为基类型，当%TYPE提供数据库字段中的数据类型时，子类型继承字段的大小约束，但是不能继承其他的如NOT NULL约束。用法如：

DECLARE   TYPE empnamelist IS TABLE OF VARCHAR2 (20); --定义表类型   --定义表类型的子类型   SUBTYPE namelist IS empnamelist;   --定义员工记录   TYPE emprec IS RECORD (      empno   NUMBER (4),      ename   VARCHAR2 (20)   );   --定义员工记录子类型   SUBTYPE emprecord IS emprec;   --定义数据库表emp中的empno列类型   SUBTYPE empno IS emp.empno%TYPE;   --定义数据库表emp中的行记录子类型   SUBTYPE emprow IS emp%ROWTYPE;BEGIN   NULL;END;

子类型可以检查数值是否越界，例如想要让某个数字类型在0~9范围之间，可以基于NUMBER类型定义一个子类型，这样在赋值时，如果数据溢出，编译器会弹出错误提示，代码如：

DECLARE   SUBTYPE numtype IS NUMBER (1, 0);  --定义子类型   --定义子类型变量   x_value   numtype;   y_value   numtype;BEGIN   x_value := 3;                    --正常   y_value := 10;                   --弹出异常提示END;

未约束的子类型可以和它的基类型交互使用，如：

DECLARE    SUBTYPE numtype IS NUMBER;       --定义类型和变量   x_value NUMBER;   y_value numtype;BEGIN   x_value:=10;                     --赋初值   y_value:=x_value;                --类型交换END; 

如果基类型相同，那么子类型可以交互使用，如：

DECLARE    SUBTYPE numtype IS VARCHAR2(200);       --定义类型和变量   x_value VARCHAR2(20);   y_value numtype;BEGIN   x_value:='This is a word';              --赋初值   y_value:=x_value;                       --类型交换END;