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
DATE型的主要功能是存储日期时间数据，其有效范围从公元前4712年1月1日到公元9999年12月31日， 同时如果要捕获当前的日期时间，可以通过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 ; / |
