当使用标量变量处理Oracle数据时,每个标量变量只能存放单个值,也就是说只能处理单行单列的数据。如果要使用标量变量处理单行多列数据,那么必须要定义多个变量接收不同列的数据;为了简化单行多列数据的处理,可以使用PL/SQL记录;为了保留并处理多行单列的数据,可以使用索引表、嵌套表和VARRAY;为了处理多行多列的数据,应该使用PL/SQL记录表。
自定义PL/SQL记录
TYPE type_name IS RECORD(
field_declaration [,
field_declaration]...
);
indetifier type_name;
type_name 用于指定自定义记录类型的名称(IS RECORD表示记录类型);field_declaration用于指定记录成员的定义;indetifier用于指定记录的变量名。当为记录类型指定多个成员时,记录成员之间用逗号隔开。
使用%ROWTYPE属性定义记录变量
%ROWTYPE属性可以基于表或视图定义记录变量。当使用该属性记录变量时,记录成员的名称或类型与表或视图的名称和类型完全相同。
DECLARE
dept_record dept%ROWTYPE;
BEGIN
dept_record.deptno:=50;
dept_record.dname:='ADMINISTRATOR';
dept_record.loc:='BEIJING';
INSERT INTO dept VALUES dept_record;
END;
在SELECT INTO语句中使用记录变量
DECLARE
type emp_record_type IS RECORD(
name emp.ename%TYPE,
salary emp.sal%TYPE,
dno emp.deptno%TYPE);
emp_record emp_record_type;
BEGIN
SELECT ename, sal, deptno INTO emp_record FROM emp WHERE empno = &no;
dbms_output.put_line(emp_record.name);
END;
在SELECT INTO语句中使用记录成员
DECLARE
type emp_record_type IS RECORD(
name emp.ename%TYPE,
salary emp.sal%TYPE,
dno emp.deptno%TYPE);
emp_record emp_record_type;
BEGIN
SELECT ename, sal INTO emp_record.name,emp_record.salary FROM emp WHERE empno = &no;
dbms_output.put_line(emp_record.name);
END;
在INSERT语句中使用PL/SQL记录
DECLARE
dept_record dept%ROWTYPE;
BEGIN
dept_record.deptno := 60;
dept_record.dname := 'SALES';
insert into dept
(deptno, dname)
values
(dept_record.deptno, dept_record.dname);
END;
PL/SQL集合
索引表的元素个数没有限制,并且下标可以为负值。它只能作为PL/SQL复合数据数据类型使用,而不能作为表列的数据类型使用。
TYPE type_name IS TABLE OF element_type
[NOT NULL] INDEX BY key_type;
identifier type_name;
type_name用于指定用户自定义数据类型的名称(IS TABLE..INDEX表示索引表);element_type用于指定索引表元素的数据类型;NOT NULL表示不允许NULL元素,key_type用于指定索引表下标的数据类型(BINARY_INTEGER、PLS_INTEGER或VARCHAR2);identifier用于定义索引表变量。
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
BEGIN
SELECT ename INTO ename_table(-1) FROM emp WHERE empno = &no;
dbms_output.put_line('雇员名:' || ename_table(-1));
END;
DECLARE
TYPE area_table_type IS TABLE OF NUMBER INDEX BY VARCHAR(10);
area_table area_table_type;
BEGIN
area_table('北京') := 1;
area_table('上海') := 2;
area_table('广州') := 3;
dbms_output.put_line('第一个元素:' || area_table.FIRST);
dbms_output.put_line('最后一个元素:' || area_table.LAST);
END;
嵌套表
嵌套表的元素下标从1开始,并且元素个数没有限制。其数组元素值可以是稀疏的。当使用嵌套表时,必须首先使用其构造方法初始化嵌套表。
TYPE type_name IS TABLE OF element_type;
identifier type_name;
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
ename_table ename_table_type;
BEGIN
ename_table := ename_table_type('MARY', 'MARY', 'MARY');
SELECT ename INTO ename_table(2) FROM emp WHERE empno = &no;
dbms_output.put_line('雇员名:' || ename_table(2));
END;
在表列中使用嵌套表,必须首先使用CREATE TYPE命令建立嵌套表类型,必须要为嵌套表列指定专门的存储表。
CREATE TYPE phone_type IS TABLE OF VARCHAR(20);
CREATE TABLE employee(id number(4),name varchar2(10),sal number(6,2),phone phone_type)
NESTED TABLE phone STORE AS phone_table;
变长数组
其元素下标以1开始,并且元素的最大个数是有限制的,在表列中使用同嵌套表。
TYPE type_name IS VARRAY(size_limit) OF element_type [NOT NULL];
identifier type_name;
DECLARE
TYPE ename_table_type IS ARRAY(20) OF emp.ename%TYPE;
ename_table ename_table_type:=ename_table_type('mary');
BEGIN
SELECT ename INTO ename_table(1) FROM emp WHERE empno=&no;
dbms_output.put_line('雇员名:'||ename_table(1));
END;
PL/SQL记录表
DECLARE
TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
BEGIN
SELECT * INTO emp_table(1) FROM emp WHERE empno=&no;
dbms_output.put_line('雇员名:'||emp_table(1).ename);
dbms_output.put_line('雇员工资:'||emp_table(1).sal);
END;