#字符类型
我们来看集中常用的字符类型。
##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;
/
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;