PL/SQL学习九

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