PL/SQL编程基础（三）：数据类型划分

数据类型划分

• 在Oracle之中所提供的数据类型，一共分为四类：

标量类型（SCALAR，或称基本数据类型）	用于保存单个值，例如：字符串、数字、日期、布尔； 标量类型只是作为单一类型的数据存在，有的只能够在PL/SQL中使用。
复合类型（COMPOSITE）	复合类型可以在内部存放多种数值，类似于多个变量的集合， 例如：记录类型、嵌套表、索引表、可变数组等都称为复合类型；
引用类型（REFERENCE）	用于指向另一不同的对象，例如：REF CURSOR、REF；
LOB类型	大数据类型，最多可以存储4G的信息，主要用来处理二进制数据；

• 常见的标量类型

分类	数据类型	描述
数 值 型	NUMBER(数据总长度 [, 小数位长度])	NUMBER是一种表示数字的数据类型。可以声明它保存数据类型的整数位和小数位的精度，在数据库之中是以十进制格式存储，在计算时，系统会将其变为二进制数据进行运算，占32个字节
	BINARY_INTEGER	不存储在数据库之中，只能够在PL/SQL中使用的带符号整数，其范围是“-231~231”，如果运算发生溢出时，则自动变为NUMBER型数据
	PLS_INTEGER	有符号的整数，其范围是“-231~231”，可以直接进行数学运算，进行的运算发生溢出的时候，会触发异常，与NUMBER相比PLS_INTEGER占用空间小，而且性能更好
	BINARY_FLOAT	单精度32位浮点数类型，占5个字节
	BINARY_DOUBLE	双精度64位浮点数类型，占9个字节
字 符 型	CHAR(长度)	定长字符串，如果所设置的内容不足定义长度，则自动补充空格，可以保存32767个字节的数据
	VARCHAR2(长度)	变长字符串，VARCHAR2数据类型列按照字节或字符来存储可变长度的字符串，可以保存1~32767个字节的数据
	VARCHAR(长度)	其功能与VARCHAR2类似，由于其是ANSI定义的标准类型，Oracle有可能在以后的版本对其进行修改，建议使用VARCHAR2
	NCHAR(长度)	定长字符串，存储UNICODE编码数据
	NVARCHAR2(长度)	变长字符串，存放UNICODE编码数据
	LONG	变长字符串数据，存储超过4000个字符时使用，最多可以存储2G大小的数据，这是一个可能会被取消的类型，替代它的类型为LOB
	RAW	保存固定长度的二进制数据，最多可以存放2000个字节的数据
	LONG RAW	存储二进制数据（图片、音乐等），最多可以存储2G大小的数据，有可能会被LOB替代
	ROWID	数据表中每行记录的唯一物理地址标记，只支持物理行ID，不支持逻辑行ID
	UROWID	支持物理行ID和逻辑行ID，
日 期 型	DATE	DATE是一个7字节的列，可以保存日期和时间，不包含毫秒
	TIMESTAMP	DATE子类型，包含日期和时间，时间部分包含毫秒，有TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE两种子类型
	INTEVAL	DATE的子类型，用于管理时间间隔，有INTERVAL DAY TO SECOND和INTERVAL YEAR TO MONTH两种子类型
大 对 象	CLOB	CLOB数据类型代表Character Large Object（字符型大对象）。它最多可以存储4G的字符串数据
	NCLOB	存放UNICODE编码的大文本数据，最多可以存储4G的字符串数据
	BLOB	BLOB数据类型列可以包含最大4GB的任何诶性的非结构化的二进制数据
	BFILE	BFILE数据类型列包含存储在外部文件系统上文件的索引，最大不超过4GB
布尔	BOOLEAN	布尔类型，可以设置的内容：TRUE、FALSE、NULL

• 数值型

• 数值型数据可以保存整数、浮点数，
• 可以使用NUMBER、PLS_INTEGER、BINARY_INTEGER、BINARY_FLOAT、BINARY_DOUBLE进行定义。

• NUMBER类型（十进制存储）

NUMBER数据类型即可以定义整型（NUMBER(n)）也可以定义浮点型数据（NUMBER(n,m)）。

• BINARY_INTEGER与PLS_INTEGER（二进制存储）
• BINARY_INTEGER和PLS_INTEGER具有相同的范围长度（-231~231，-2147483648 ~ 2147483647），
• 与NUMBER相比较而言，其所占用的范围更小。
• 在数学计算时，由于NUMBER类型保存的数据为十进制类型，所以需要首先将将十进制转为二进制数据之后才可以进行计算，
• 而对于BINARY_INTEGER与PLS_INTEGER类型而言，采用的是二进制的补码形式存储，所以性能上要比NUMBER类型更高。
• BINARY_INTEGER与PLS_INTEGER是有区别的，
• 当使用BINARY_INTEGER操作的数据大于其数据范围定义时，会自动将其变为NUMBER型数据保存，
• 而使用PLS_INTEGER操作的数据大于其数据范围定义时，会抛出异常信息。
• BINARY_FLOAT与BINARY_DOUBLE（二进制存储）

在Oracle 10g之后引入了两个新的数据类型：BINARY_FLOAT、BINARY_DOUBLE，

使用这两个类型要比使用NUMBER节约空间，同时表示的范围也越大，

最为重要的是这两个数据类型并不像NUMBER采用了十进制方式存储，而直接采用二进制方式存储，这样在进行数学计算时，其性能更高。

• 表示范围：

BINARY_FLOAT与BINARY_DOUBLE > NUMBER >BINARY_INTEGER与PLS_INTEGER

• BINARY_FLOAT和BINARY_DOUBLE常量
• 在BINARY_FLOAT和BINARY_DOUBLE之中还定义了如表15-4所定义的几个常量，但是这些常量只能够在PL/SQL之中使用。

常量名称	使用环境	描述
BINARY_FLOAT_NAN	SQL、PL/SQL	表示非BINARY_FLOAT类型数据，Nan在科学记数法中表示Not a Number（非数字）
BINARY_FLOAT_INFINITY	SQL、PL/SQL	表示BINARY_FLOAT的数据为无穷大（Inf）
BINARY_DOUBLE_NAN	SQL、PL/SQL	表示非BINARY_DOUBLE类型数据（Nan）
BINARY_DOUBLE_INFINITY	SQL、PL/SQL	表示BINARY_DOUBLE的数据为无穷大（Inf）
BINARY_FLOAT_MIN_NORMAL	PL/SQL	表示数字：1.17549435E-038，最小常数
BINARY_FLOAT_MAX_NORMAL	PL/SQL	表示数字：3.40282347E+038，最大绝对数
BINARY_FLOAT_MIN_SUBNORMAL	PL/SQL	表示数字：1.40129846E-045，向下溢出的最小值
BINARY_FLOAT_MAX_SUBNORMAL	PL/SQL	表示数字：1.17549421E-038，最小绝对数
BINARY_DOUBLE_MIN_NORMAL	PL/SQL	表示数字：2.2250738585072014E-308，最小常数
BINARY_DOUBLE_MAX_NORMAL	PL/SQL	表示数字：1.7976931348623157E+308，最大绝对数
BINARY_DOUBLE_MIN_SUBNORMAL	PL/SQL	表示数字：4.9406564584124654E-324，，向下溢出的最小值
BINARY_DOUBLE_MAX_SUBNORMAL	PL/SQL	表示数字：2.2250738585072009E-308，最小绝对数

定义NUMBER变量

DECLARE v_x    NUMBER(3) ;   -- 最多只能为3位数字 v_y    NUMBER(5,2) ;   -- 3位整数，2位小数 BEGIN v_x := -500 ; v_y := 999.88 ; DBMS_OUTPUT.put_line('v_x = ' || v_x) ; DBMS_OUTPUT.put_line('v_y = ' || v_y) ; DBMS_OUTPUT.put_line('加法运算：' || (v_x + v_y)) ;    -- 整数 + 浮点数 = 浮点数 END ; / v_x = -500 v_y = 999.88 加法运算：499.88

验证BINARY_DOUBLE操作

DECLARE v_float        BINARY_FLOAT := 8909.51F ; v_double        BINARY_DOUBLE := 8909.51D ; BEGIN v_float := v_float + 1000.16 ; v_double := v_double + 1000.16 ; DBMS_OUTPUT.put_line('BINARY_FLOAT变量内容：' || v_float) ; DBMS_OUTPUT.put_line('BINARY_DOUBLE变量内容：' || v_double) ; END ; /

观察表示范围的常量内容

DECLARE BEGIN DBMS_OUTPUT.put_line('1、BINARY_FLOAT_MIN_NORMAL = ' || BINARY_FLOAT_MIN_NORMAL) ; DBMS_OUTPUT.put_line('1、BINARY_FLOAT_MAX_NORMAL = ' || BINARY_FLOAT_MAX_NORMAL) ; DBMS_OUTPUT.put_line('1、BINARY_FLOAT_MIN_SUBNORMAL = ' || BINARY_FLOAT_MIN_SUBNORMAL) ; DBMS_OUTPUT.put_line('1、BINARY_FLOAT_MAX_SUBNORMAL = ' || BINARY_FLOAT_MAX_SUBNORMAL) ; DBMS_OUTPUT.put_line('2、BINARY_DOUBLE_MIN_NORMAL = ' || BINARY_DOUBLE_MIN_NORMAL) ; DBMS_OUTPUT.put_line('2、BINARY_DOUBLE_MAX_NORMAL = ' || BINARY_DOUBLE_MAX_NORMAL) ; DBMS_OUTPUT.put_line('2、BINARY_DOUBLE_MIN_SUBNORMAL = ' || BINARY_DOUBLE_MIN_SUBNORMAL) ; DBMS_OUTPUT.put_line('2、BINARY_DOUBLE_MAX_SUBNORMAL = ' || BINARY_DOUBLE_MAX_SUBNORMAL) ; END ; /

超过范围的计算

DECLARE BEGIN DBMS_OUTPUT.put_line('超过范围计算的结果：' || BINARY_DOUBLE_MAX_NORMAL * BINARY_DOUBLE_MAX_NORMAL) ; DBMS_OUTPUT.put_line('超过范围计算的结果：' || BINARY_DOUBLE_MAX_NORMAL / 0) ; END ; /

字符型

字符串指的是使用“'”声明的内容，在开发之中，比较常用的字符串类型为VARCHAR2，但是除了此类型之外，在Oracle之中也提供了许多的其他字符串类型

CHAR与VARCHAR2	CHAR数据类型使用定长方式保存字符串，如果用户为其设置的内容不足其定义长度，会自动补充空格。 VARCHAR2是变长字符串，如果为其设置的内容不足其长度，也不会为其补充内容。
NCHAR与NVARCHAR2	NCHAR与NVARCHAR2的操作特点与CHAR和VARCHAR2一样， 唯一不同的是，NCHAR和NVARCHAR2保存的数据为UNICODE编码，即：中文或英文都会变为16进制编码保存。
LONG与LONG RAW	LONG和LONG RAW数据类型只用于后向兼容， 一般在使用LONG的地方都会使用CLOB或NCLOB， 而使用LONG RAW的地方都替换为BLOB或BILE。 LONG数据类型主要存储字符流，而LONG RAW主要存储二进制数据流。
ROWID与UROWID	ROWID表示的是一条数据的物理行地址，由18个字符组合而成，这一点与Oracle数据库表中的ROWID伪列功能相同。 UROWID（UNIVERSAL ROWID，通用性ROWID）除了表示数据的物理行地址之外还增加了一个逻辑行地址，在PL/SQL编程之中应该将所有的ROWID交给UROWID管理。

观察CHAR和VARCHAR2的区别

DECLARE v_info_char        CHAR(10) ; v_info_varchar        VARCHAR2(10) ; BEGIN v_info_char := 'MLDN' ;    -- 长度不足10个 v_info_varchar := 'java' ;    -- 长度不足10个 DBMS_OUTPUT.put_line('v_info_char内容长度：' || LENGTH(v_info_char)) ; DBMS_OUTPUT.put_line('v_info_varchar内容长度：' || LENGTH(v_info_varchar)) ; END ; /

验证NCHAR和NVARCHAR2

DECLARE v_info_nchar        NCHAR(10) ; v_info_nvarchar        NVARCHAR2(10) ; BEGIN v_info_nchar := '魔乐科技' ;            -- 长度不足10个 v_info_nvarchar := 'java高端培训' ;    -- 长度不足10个 DBMS_OUTPUT.put_line('v_info_nchar内容长度：' || LENGTH(v_info_nchar)) ; DBMS_OUTPUT.put_line('v_info_nvarchar内容长度：' || LENGTH(v_info_nvarchar)) ; END ; /

使用LONG和LONG RAW操作

DECLARE v_info_long        LONG ; v_info_longraw        LONG RAW ; BEGIN v_info_long := '寻' ;        -- 直接设置字符串 v_info_longraw := UTL_RAW.cast_to_raw('寻香径') ;    -- 将字符串变为RAW DBMS_OUTPUT.put_line('v_info_long内容：' || v_info_long) ; DBMS_OUTPUT.put_line('v_info_longraw内容：' || UTL_RAW.cast_to_varchar2(v_info_longraw)) ; END ; /

使用ROWID及UROWID

DECLARE v_emp_rowid    ROWID ; v_emp_urowid    UROWID ; BEGIN SELECT ROWID INTO v_emp_rowid FROM emp WHERE empno=7369 ;    -- 取得ROWID SELECT ROWID INTO v_emp_urowid FROM emp WHERE empno=7369 ;    -- 取得ROWID DBMS_OUTPUT.put_line('7369雇员的ROWID = ' || v_emp_rowid) ; DBMS_OUTPUT.put_line('7369雇员的UROWID = ' || v_emp_urowid) ; END ; /

日期型

在Oracle中，日期类型的数据主要包含DATE、TIMESTAMP、INTEVAL这几个类型，通过这几个类型允许用户操作日期、时间、时间间隔

DATE

日， 同时如果要捕获当前的日期时间，可以通过SYSDATE或SYSTIMESTAMP两个伪列完成，DATE数据类型的主要字段索引组成如表所示。

字段名称	有效范围	有效内部值
YEAR	-4712 ~ 9999（不包含公元0年）	任何非零整数
MONTHS	01 ~ 12	0 ~ 11
DAY	01 ~ 31（参考日历）	任何非零整数
HOUR	00 ~ 23	0 ~ 23
MINUTE	00 ~ 59	0 ~ 59
SECOND	00 ~ 59	00 ~ 59.9（其中0.1是秒的精度部分）

定义DATE型变量

DECLARE v_date1        DATE := SYSDATE ; v_date2        DATE := SYSTIMESTAMP ; v_date3        DATE := '19-9月-1981' ; BEGIN DBMS_OUTPUT.put_line('日期数据：' || TO_CHAR(v_date1,'yyyy-mm-dd hh24:mi:ss')) ; DBMS_OUTPUT.put_line('日期数据：' || TO_CHAR(v_date2,'yyyy-mm-dd hh24:mi:ss')) ; DBMS_OUTPUT.put_line('日期数据：' || TO_CHAR(v_date3,'yyyy-mm-dd hh24:mi:ss')) ; END ; /

定义TIMESTAMP型变量

DECLARE v_timestamp1        TIMESTAMP := SYSDATE ; v_timestamp2        TIMESTAMP := SYSTIMESTAMP ; v_timestamp3        TIMESTAMP := '19-9月-1981' ; BEGIN DBMS_OUTPUT.put_line('日期数据：' || v_timestamp1) ; DBMS_OUTPUT.put_line('日期数据：' || v_timestamp2) ; DBMS_OUTPUT.put_line('日期数据：' || v_timestamp3) ; END ; /

TIMESTAMP

• TIMESTAMP与DATE类型相同，但是相比较DATE类型而言，TIMESTAMP可以提供更为精确的时间，但是此时就必须使用SYSTIMESTAMP伪列来为其赋值，如果使用的只是SYSDATE，那么TIMESTAMP与DATE没有任何区别。

• TIMESTAMP两个子类型

• 在TIMESTAMP之中还定义了两个扩充的子类型：TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE

• TIMESTAMP WITH TIME ZONE：包含与格林威治时间的时区偏移量；

  DECLARE v_timestamp        TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP ; BEGIN DBMS_OUTPUT.put_line(v_timestamp) ; END ; /

  TIMESTAMP WITH LOCAL TIME ZONE：不管是何种时区的数据，都使用当前数据库的时区。

  DECLARE v_timestamp        TIMESTAMP WITH LOCAL TIME ZONE := SYSTIMESTAMP ; BEGIN DBMS_OUTPUT.put_line(v_timestamp) ; END ; /

INTERVAL

• 之前的两种日期时间类型都只是单纯的记录某个日期时间点的数据，如果现在要想保存两个时间戳之间的时间间隔，则可以使用INTERVAL数据类型，INTERVAL类型一共分为两种子类型：

INTERVAL YEAR[(年的精度)] TO MONTHS：	保存和操作年和月之间的时间间隔，用户可以指定设置年的数据精度，如果不设置精度，则默认值为2； 赋值字符串格式：'年-月'；
INTERVAL DAY[(天的精度)] TO SECOND[(秒的精度)]：	保存和操作天、时、分、秒之间的时间间隔，如果未设置天的精度数字，则默认为2，如果没有设置秒的精度则默认为6。 赋值字符串格式：'天 时:分:秒.毫秒'；

• 时间间隔计算

• 当取得时间间隔之后，可以直接利用直接利用以下的公式进行计算

操作数据1类型	运算符	操作数据2类型	结果类型
时间戳	+	时间间隔	时间戳
时间戳	-	时间间隔	时间戳
时间间隔	+	时间间隔	时间间隔
时间间隔	-	时间间隔	时间间隔
时间间隔	*	数值型	时间间隔
时间间隔	/	数值型	时间间隔
日期	+	时间间隔	日期

定义INTERVAL YEAR TO MONTHS类型变量

DECLARE v_interval    INTERVAL YEAR(3) TO MONTH := INTERVAL '27-09' YEAR TO MONTH ; BEGIN DBMS_OUTPUT.put_line('时间间隔：' || v_interval) ; DBMS_OUTPUT.put_line('当前时间戳 + 时间间隔：' || (SYSTIMESTAMP + v_interval)) ; DBMS_OUTPUT.put_line('当前日期 + 时间间隔：' || (SYSDATE + v_interval)) ; END ; /

定义INTERVAL DAY TO SECOND类型变量

DECLARE v_interval    INTERVAL DAY(6) TO SECOND (3) := INTERVAL '8 18:19:27.367123909' DAY TO SECOND; BEGIN DBMS_OUTPUT.put_line('时间间隔：' || v_interval) ; DBMS_OUTPUT.put_line('当前时间戳 + 时间间隔：' || (SYSTIMESTAMP + v_interval)) ; DBMS_OUTPUT.put_line('当前日期 + 时间间隔：' || (SYSDATE + v_interval)) ; END ; /

布尔型

• 在PL/SQL编程之中为了方便进行逻辑的处理，专门为用户提供了BOOLEAN型数据，此数据可以保存TRUE、FALSE和NULL。

定义布尔型变量

DECLARE v_flag        BOOLEAN ; BEGIN v_flag := true ; IF v_flag THEN DBMS_OUTPUT.put_line('条件满足。') ; END IF ; END ; /

子类型

• 虽然Oracle为用户提供了许多的标量类型数据，但是很多时候用户会希望在某一标量类型的基础上定义更多的约束，从而创建一个新的类型，此时这种新的类型就被称为子类型，子类型的创建语法如下所示。
• SUBTYPE 子类型名称 IS 父数据类型[(约束)] [NOT NULL] ;

定义NUMBER子类型

DECLARE SUBTYPE score_subtype IS NUMBER(5,2) NOT NULL ; v_score        score_subtype := 99.35 ; BEGIN DBMS_OUTPUT.put_line('成绩为：' || v_score) ; END ; /

定义VARCHAR2子类型

DECLARE SUBTYPE string_subtype IS VARCHAR2(200) ; v_company    string_subtype ; BEGIN v_company := '寻香径（http://www.cnblogs.com/thescentedpath/'）' ; DBMS_OUTPUT.put_line(v_company) ; END ; /