25. PL/SQL中使用复合数据类型
使用记录类型来进行单行多列的数据处理;使用索引表、嵌套表、可变数组进行单列多行的数据处理;
使用PL/SQL记录表处理多行多列的数据.
25.1 记录类型
PL/SQL记录类似C中的STRUCT类型.记录类型一般定义如下:
DECLARE
TYPE EMP_RECORD_TYPE IS RECORD(
NAME EMP.ENAME%TYPE,
SALARY EMP.SAL%TYPE,
DNO EMP.DEPTNO%TYPE);
EMP_RECORD EMP_RECORD_TYPE;
还可以使用ROWTYPE定义记录类型,此时记录变量与表或视图的列的个数、名称、类型完全一样,如:
emp_record emp%ROWTYPE;
PL/SQL中使用记录.
-- ex:pl/sql_23
DECLARE
TYPE EMP_RECORD_TYPE IS RECORD(
NAME EMP.ENAME%TYPE,
SALARY EMP.SAL%TYPE,
DNO EMP.DEPTNO%TYPE);
EMP_RECORD EMP_RECORD_TYPE;
DEPT_RECORD DEPT%ROWTYPE;
BEGIN
SELECT ENAME, SAL, DEPTNO INTO EMP_RECORD FROM EMP WHERE EMPNO = 7900;
DBMS_OUTPUT.PUT_LINE('name:' || EMP_RECORD.NAME);
DEPT_RECORD.DEPTNO := 99;
DEPT_RECORD.DNAME := 'ADMIN';
DEPT_RECORD.LOC := 'GUANGZHOU';
INSERT INTO DEPT VALUES DEPT_RECORD;
DEPT_RECORD.DNAME := 'TEST';
DEPT_RECORD.LOC := '上海';
UPDATE dept SET ROW=dept_record WHERE deptno=99;
DELETE FROM DEPT WHERE DEPTNO = DEPT_RECORD.DEPTNO;
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('deptno 重复,无法插入!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('error!');
END;
25.2 索引表
索引表类似C语言中的数组,不同的是索引表数组元素个数没有限制,并且下标可以为负值.
索引表的下标有3种类型(BINARY_INTEGER、PLS_INTEGER、VARCHAR2).注意9i前不能使用VARCHAR2做下标类型。
--ex:pl/sql_24
SQL> set serveroutput on
SQL> DECLARE
2 TYPE ENAME_TABLE_TYPE IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
3
4 TYPE LOC_TABLE_TYPE IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(10);
5
6 ENAME_TABLE ENAME_TABLE_TYPE;
7 LOC_TABLE LOC_TABLE_TYPE;
8 BEGIN
9 --使用BINARY_INTEGER做下标
10 SELECT ENAME INTO ENAME_TABLE(-11) FROM SCOTT.EMP WHERE EMPNO = 7900;
11 DBMS_OUTPUT.PUT_LINE('员工名称:' || ENAME_TABLE(-11));
12
13 --使用VARCHAR2做下标
14 SELECT LOC INTO LOC_TABLE('部门40') FROM SCOTT.DEPT WHERE DEPTNO = 40;
15 DBMS_OUTPUT.PUT_LINE('部门地址:' || LOC_TABLE('部门40'));
16
17 EXCEPTION
18 WHEN OTHERS THEN
19 DBMS_OUTPUT.PUT_LINE('error!');
20 END;
21 /
员工名称:JAMES
部门地址:BOSTON
PL/SQL 过程已成功完成。
25.3 嵌套表
嵌套表也是类似C语言数组的数据类型.它的元素下标从1开始,但元素个数没有限制。
嵌套表可以做表的字段类型使用.
SQL> --ex:pl/sql_25
SQL> DECLARE
2 --定义嵌套表类型
3 TYPE ENAME_TAB_TYPE IS TABLE OF VARCHAR2(20);
4 ENAME_TAB ENAME_TAB_TYPE;
5 BEGIN
6 --使用构造方法初始化嵌套表变量
7 ENAME_TAB := ENAME_TAB_TYPE('姓名', '姓名', '姓名');
8 SELECT ENAME INTO ENAME_TAB(1) FROM SCOTT.EMP WHERE EMPNO = 7369;
9 SELECT ENAME INTO ENAME_TAB(2) FROM SCOTT.EMP WHERE EMPNO = 7900;
10 SELECT ENAME INTO ENAME_TAB(3) FROM SCOTT.EMP WHERE EMPNO = 7844;
11
12 FOR i IN 1..ENAME_TAB.count LOOP
13 DBMS_OUTPUT.PUT_LINE('姓名'||i||':' || ENAME_TAB(i));
14 END LOOP;
15
16 EXCEPTION
17 WHEN OTHERS THEN
18 DBMS_OUTPUT.PUT_LINE('error!');
19 END;
20 /
姓名1:SMITH
姓名2:JAMES
姓名3:TURNER
PL/SQL 过程已成功完成。
表列中使用嵌套表见7.3、嵌套表(Nested Table)。
25.4 变长数组.
变长数组也是类似C语言数组的数据类型.它也可以做为表的字段类型使用.
它的下标也是从1开始,并且元素的最大个数也是有限制的.
SQL> --ex:pl/sql_26
SQL> DECLARE
2 --定义变长数组类型
3 TYPE ENAME_TAB_TYPE IS VARRAY(5) OF VARCHAR2(20);
4 ENAME_TAB ENAME_TAB_TYPE;
5 BEGIN
6 --使用构造方法初始化变长数组变量
7 ENAME_TAB := ENAME_TAB_TYPE('姓名', '姓名', '姓名','姓名','姓名');
8 SELECT ENAME INTO ENAME_TAB(1) FROM SCOTT.EMP WHERE EMPNO = 7369;
9 SELECT ENAME INTO ENAME_TAB(2) FROM SCOTT.EMP WHERE EMPNO = 7900;
10 SELECT ENAME INTO ENAME_TAB(5) FROM SCOTT.EMP WHERE EMPNO = 7844;
11
12 FOR i IN 1..5 LOOP
13 DBMS_OUTPUT.PUT_LINE('姓名'||i||':' || ENAME_TAB(i));
14 END LOOP;
15
16 EXCEPTION
17 WHEN OTHERS THEN
18 DBMS_OUTPUT.PUT_LINE('error!');
19 END;
20 /
姓名1:SMITH
姓名2:JAMES
姓名3:姓名
姓名4:姓名
姓名5:TURNER
PL/SQL 过程已成功完成。
表列中使用变长数组.
首先创建变长数组类型,然后创建包含变长数组类型字段的表.
SQL> CREATE OR REPLACE TYPE phone_type IS varray(20) OF VARCHAR2(20);
2 /
类型已创建。
SQL> CREATE TABLE test_emp(
2 ID NUMBER(6),
3 NAME VARCHAR2(10),
4 sal NUMBER(6,2),
5 phone phone_type);--变长数组类型字段
表已创建。
25.5 PL/SQL记录表
PL/SQL记录表用来处理多行多列数据.
SQL> --ex:pl/sql_27
SQL> DECLARE
2 TYPE EMP_TAB_TYPE IS TABLE OF EMP%ROWTYPE
3 INDEX BY BINARY_INTEGER;
4 EMP_TAB EMP_TAB_TYPE;
5
6 BEGIN
7 SELECT * INTO EMP_TAB(-1) FROM EMP WHERE ROWNUM = 1;
8 DBMS_OUTPUT.PUT_LINE(EMP_TAB(-1).ENAME || ' ' || EMP_TAB(-1).SAL);
9
10 EXCEPTION
11 WHEN OTHERS THEN
12 DBMS_OUTPUT.PUT_LINE('错误!');
13 END;
14 /
SMITH 2402.4
PL/SQL 过程已成功完成。
25.6 多级集合操作
多级集合是指集合里嵌套集合类型.实现类似C语言中的多维数组功能.
--使用多级变长数组VARRAY
SQL> --ex:pl/sql_28
SQL> DECLARE
2 --一维
3 TYPE ONE_VAR_TYPE IS VARRAY(10) OF INT;
4 --二维
5 TYPE TWO_VAR_TYPE IS VARRAY(10) OF ONE_VAR_TYPE;
6 --初始化二维数组
7 TWO_VAR TWO_VAR_TYPE := TWO_VAR_TYPE(ONE_VAR_TYPE(2, 3, 4),
8 ONE_VAR_TYPE(11, 22, 33, 44),
9 ONE_VAR_TYPE(52, 54));
10 BEGIN
11 DBMS_OUTPUT.PUT_LINE('输出二维数组:');
12 FOR I IN 1 .. TWO_VAR.COUNT LOOP
13 FOR J IN 1 .. TWO_VAR(I).COUNT LOOP
14 DBMS_OUTPUT.PUT_LINE('two_var(' || I || ',' || J || ')=' ||
15 TWO_VAR(I) (J));
16 END LOOP;
17 END LOOP;
18
19 EXCEPTION
20 WHEN OTHERS THEN
21 DBMS_OUTPUT.PUT_LINE('错误!');
22 END;
23 /
输出二维数组:
two_var(1,1)=2
two_var(1,2)=3
two_var(1,3)=4
two_var(2,1)=11
two_var(2,2)=22
two_var(2,3)=33
two_var(2,4)=44
two_var(3,1)=52
two_var(3,2)=54
PL/SQL 过程已成功完成。
-- 多级嵌套表
SQL> --ex:pl/sql_29
SQL> DECLARE
2 --一维嵌套表
3 TYPE ONE_VAR_TYPE IS TABLE OF INT;
4 --二维嵌套表
5 TYPE TWO_VAR_TYPE IS TABLE OF ONE_VAR_TYPE;
6 --初始化二维嵌套表
7 TWO_VAR TWO_VAR_TYPE := TWO_VAR_TYPE(ONE_VAR_TYPE(2, 4),
8 ONE_VAR_TYPE(11),
9 ONE_VAR_TYPE(52, 54));
10 BEGIN
11 DBMS_OUTPUT.PUT_LINE('输出二维数组:');
12 FOR I IN 1 .. TWO_VAR.COUNT LOOP
13 FOR J IN 1 .. TWO_VAR(I).COUNT LOOP
14 DBMS_OUTPUT.PUT_LINE('two_var(' || I || ',' || J || ')=' ||
15 TWO_VAR(I) (J));
16 END LOOP;
17 END LOOP;
18
19 EXCEPTION
20 WHEN OTHERS THEN
21 DBMS_OUTPUT.PUT_LINE('错误!');
22 END;
23 /
输出二维数组:
two_var(1,1)=2
two_var(1,2)=4
two_var(2,1)=11
two_var(3,1)=52
two_var(3,2)=54
-- 多级索引表
SQL> --ex:pl/sql_30
SQL> DECLARE
2 --一维索引表
3 TYPE ONE_VAR_TYPE IS TABLE OF INT
4 INDEX BY BINARY_INTEGER;
5 --二维索引表
6 TYPE TWO_VAR_TYPE IS TABLE OF ONE_VAR_TYPE
7 INDEX BY BINARY_INTEGER;
8 TWO_VAR TWO_VAR_TYPE;
9 BEGIN
10 --初始化二维索引表
11 TWO_VAR(1)(1):=15;
12 TWO_VAR(1)(2):=25;
13 TWO_VAR(2)(1):=35;
14 TWO_VAR(2)(2):=45;
15 --注意存放要连续,否则输出会出错
16 TWO_VAR(8)(9):=55;
17
18 DBMS_OUTPUT.PUT_LINE('输出二维数组:');
19 FOR I IN 1 .. TWO_VAR.COUNT LOOP
20 FOR J IN 1 .. TWO_VAR(I).COUNT LOOP
21 DBMS_OUTPUT.PUT_LINE('two_var(' || I || ',' || J || ')=' ||
22 TWO_VAR(I) (J));
23 END LOOP;
24 END LOOP;
25
26 EXCEPTION
27 WHEN OTHERS THEN
28 DBMS_OUTPUT.PUT_LINE('错误!');
29 END;
30 /
输出二维数组:
two_var(1,1)=15
two_var(1,2)=25
two_var(2,1)=35
two_var(2,2)=45
错误!
PL/SQL 过程已成功完成。
25.7 集合操作
操作集合有函数和过程.其中EXIST、COUNT、LIMIT、FIRST、NEXT、PRIOR、NEXT是函数.
EXTEND、TRIM、DELETE是过程.
注意以上函数和过程只能在PL/SQL中使用,不能在SQL中使用.
另EXTEND和TRIM只适用于嵌套表和VARRAY,不适用于索引表.
--EXIST
SQL> --ex:pl/sql_31
SQL> DECLARE
2 --嵌套表
3 TYPE ENAME_TAB_TYPE IS TABLE OF EMP.ENAME%TYPE;
4 ENAME_TAB ENAME_TAB_TYPE;
5 BEGIN
6 --exists使用
7 IF ENAME_TAB.EXISTS(1) THEN
8 ENAME_TAB(1) := 'CRY';
9 ELSE
10 DBMS_OUTPUT.PUT_LINE('嵌套表变量需要初始化');
11 END IF;
12
13 EXCEPTION
14 WHEN OTHERS THEN
15 DBMS_OUTPUT.PUT_LINE('错误!');
16 END;
17 /
嵌套表变量需要初始化
PL/SQL 过程已成功完成。
--COUNT
SQL> --ex:pl/sql_32
SQL> DECLARE
2 --索引表
3 TYPE ENAME_TAB_TYPE IS TABLE OF EMP.ENAME%TYPE
4 INDEX BY BINARY_INTEGER;
5 ENAME_TAB ENAME_TAB_TYPE;
6 BEGIN
7 --COUNT使用
8 IF ENAME_TAB.EXISTS(1) THEN
9 ENAME_TAB(1) := 'CRY';
10 ELSE
11 ENAME_TAB(-1) := 'SCOTT';
12 ENAME_TAB(1) := 'MARK';
13 ENAME_TAB(-2) := 'mary';
14 ENAME_TAB(9) := 'test';
15 END IF;
16 DBMS_OUTPUT.PUT_LINE('集合元素总数:' || ENAME_TAB.COUNT);
17
18 EXCEPTION
19 WHEN OTHERS THEN
20 DBMS_OUTPUT.PUT_LINE('错误!');
21 END;
22 /
集合元素总数:4
PL/SQL 过程已成功完成
--LIMIT
SQL> --ex:pl/sql_33
SQL> --因为嵌套表和索引表元素个数没有限制,所以LIMIT方法会返回NULL,这里测试变长数组的LIMIT方法
SQL> DECLARE
2
3 TYPE ENAME_TAB_TYPE IS VARRAY(18) OF EMP.ENAME%TYPE;
4 ENAME_TAB ENAME_TAB_TYPE:=ENAME_TAB_TYPE('CRY');
5 BEGIN
6 --LIMIT使用
7 DBMS_OUTPUT.PUT_LINE('变长数组ename_tab最大元素个数为:'||ENAME_TAB.limit);
8
9 EXCEPTION
10 WHEN OTHERS THEN
11 DBMS_OUTPUT.PUT_LINE('错误!');
12 END;
13 /
变长数组ename_tab最大元素个数为:18
PL/SQL 过程已成功完成。
--FIRST和LAST
SQL> --ex:pl/sql_34
SQL> --FIRST和LAST
SQL> DECLARE
2 --索引表
3 TYPE ENAME_TAB_TYPE IS TABLE OF EMP.ENAME%TYPE
4 INDEX BY BINARY_INTEGER;
5 ENAME_TAB ENAME_TAB_TYPE;
6 BEGIN
7 ENAME_TAB(1.5):='TEST02';
8 ENAME_TAB(-2):='TEST01';
9 ENAME_TAB(2):='TEST03';
10 ENAME_TAB(-9):='TEST04';
11
12 DBMS_OUTPUT.PUT_LINE('第一个元素为:'||ENAME_TAB.first);
13 DBMS_OUTPUT.PUT_LINE('最后一个元素为:'||ENAME_TAB.last);
14
15 EXCEPTION
16 WHEN OTHERS THEN
17 DBMS_OUTPUT.PUT_LINE('错误!');
18 END;
19 /
第一个元素为:-9
最后一个元素为:2
SQL> --PRIOR和NEXT
SQL> DECLARE
2 --索引表
3 TYPE ENAME_TAB_TYPE IS TABLE OF EMP.ENAME%TYPE
4 INDEX BY BINARY_INTEGER;
5 ENAME_TAB ENAME_TAB_TYPE;
6 BEGIN
7 ENAME_TAB(1):='TEST02';
8 ENAME_TAB(-2):='TEST01';
9 ENAME_TAB(2):='TEST03';
10 ENAME_TAB(-9):='TEST04';
11 --FIRST和LAST
12 DBMS_OUTPUT.PUT_LINE('第一个元素为:'||ENAME_TAB.first);
13 DBMS_OUTPUT.PUT_LINE('最后一个元素为:'||ENAME_TAB.last);
14 --PRIOR和NEXT
15 DBMS_OUTPUT.PUT_LINE('第1个元素前一个为:'||ENAME_TAB.PRIOR(1));
16 DBMS_OUTPUT.PUT_LINE('第1个元素后一个为:'||ENAME_TAB.NEXT(1));
17 EXCEPTION
18 WHEN OTHERS THEN
19 DBMS_OUTPUT.PUT_LINE('错误!');
20 END;
21 /
第一个元素为:-9
最后一个元素为:2
第1个元素前一个为:-2
第1个元素后一个为:2