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_23DECLARE 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_24SQL> set serveroutput onSQL> 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部门地址:BOSTONPL/SQL 过程已成功完成。
25.3 嵌套表
嵌套表也是类似C语言数组的数据类型.它的元素下标从1开始,但元素个数没有限制。
嵌套表可以做表的字段类型使用.
SQL> --ex:pl/sql_25SQL> 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:TURNERPL/SQL 过程已成功完成。
表列中使用嵌套表见7.3、嵌套表(Nested Table)。
25.4 变长数组.
变长数组也是类似C语言数组的数据类型.它也可以做为表的字段类型使用.
它的下标也是从1开始,并且元素的最大个数也是有限制的.
SQL> --ex:pl/sql_26SQL> 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:TURNERPL/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.4PL/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)=2two_var(1,2)=3two_var(1,3)=4two_var(2,1)=11two_var(2,2)=22two_var(2,3)=33two_var(2,4)=44two_var(3,1)=52two_var(3,2)=54PL/SQL 过程已成功完成。
-- 多级嵌套表SQL> --ex:pl/sql_29SQL> 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)=2two_var(1,2)=4two_var(2,1)=11two_var(3,1)=52two_var(3,2)=54
-- 多级索引表SQL> --ex:pl/sql_30SQL> 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)=15two_var(1,2)=25two_var(2,1)=35two_var(2,2)=45错误!PL/SQL 过程已成功完成。
25.7 集合操作
操作集合有函数和过程.其中EXIST、COUNT、LIMIT、FIRST、NEXT、PRIOR、NEXT是函数.
EXTEND、TRIM、DELETE是过程.
注意以上函数和过程只能在PL/SQL中使用,不能在SQL中使用.
另EXTEND和TRIM只适用于嵌套表和VARRAY,不适用于索引表.
--EXISTSQL> --ex:pl/sql_31SQL> 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 过程已成功完成。
--COUNTSQL> --ex:pl/sql_32SQL> 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 /集合元素总数:4PL/SQL 过程已成功完成
--LIMITSQL> --ex:pl/sql_33SQL> --因为嵌套表和索引表元素个数没有限制,所以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最大元素个数为:18PL/SQL 过程已成功完成。
--FIRST和LASTSQL> --ex:pl/sql_34SQL> --FIRST和LASTSQL> 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和NEXTSQL> 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