数据类型划分
- 在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 ; / |