使用复合数据类型

使用复合数据类型

 

一． PL/SQL记录

可以简化单行多列数据的处理。

 

1.1定义PL/SQL记录

当使用PL/SQL记录时，应用开发人员既可以自定义记录类型和记录变量，也可以使用%ROWTYPE属性直接定义记录变量。

(1)    自定义PL/SQL记录

当使用自定义的PL/SQL记录时，需要分别定义PL/SQL记录类型和记录变量。

语法：

TYPE type_name IS RECORD(

  field_declaration[,

  field_declaration]…

);

identifier type_name;

l         type_name：用于指定自定义记录类型的名称(IS RECORD表示记录类型)

l         field_declaration：用于记录成员的定义

l         identifier：用于指定记录变量名

当为记录类型指定多个成员时，记录成员之间用逗号隔开。

示例：

DECLARE

  TYPE emp_record_type IS RECORD(

name emp.ename%TYPE,

salary emp.sal%TYPE,

dno emp.deptno%TYPE

);

emp record emp_record_type;

...

记录类型emp_record_type包含三个记录成员name，salary和dno；emp_record是基于记录类型emp_record_type所定义的记录变量。

 

(2)    使用%ROWTYPE属性定义记录变量

%ROWTYPE属性可以基于表或视图定义记录变量。当使用该属性定义记录变量时，记录成员的名称和类型与表或视图列的名称和类型完全相同。为了简化表或视图所有列数据的处理，应该使用该属性定义记录变量。而如果只是处理某几列数据，那么应该使用自定义记录类型和记录变量，这样可以节省内存空间。

语法：

identifier table_name%ROWTYPE;

或

identifier view_name%ROWTYPE;

当使用%ROWTYPE属性定义记录变量时，记录成员个数、名称、类型与表或视图列的个数、名称、类型完全相同。

示例：

dept_record dept%ROWTYPE;

emp_record emp%ROWTYPE;

记录变量dept_record的成员名为表dept的列名(deptno，dname，loc)；而记录变量emp_record的成员名为表emp的列名(empno，ename，…)。

 

1.2使用PL/SQL记录

 

(1)    在SELECT INTO语句中使用PL/SQL记录

SELECT INTO语句用于检索单行数据。如果选择列表包含的多个列或表达式，并且使用标量变量接受数据，那么需要定义多个标量变量；如果使用PL/SQL记录变量接收数据，就只需要一个记录变量就可以接收数据，从而简化了数据处理。当在SELECT INTO语句中使用PL/SQL记录时，既可以直接使用记录变量，也可以使用记录成员。

 

在SELECT INTO语句中使用记录变量

当在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;

 

输出：

原值 9: FROM emp WHERE empno = &no;

新值 9: FROM emp WHERE empno = 7788;

SCOTT

PL/SQL 过程已成功完成。

 

注意，当引用记录成员时，必须在成员名之前加记录变量名作为前缀。

 

在SELECT INTO语句中使用记录成员

当在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;

 

输出：

原值 9: FROM emp WHERE empno = &no;

新值 9: FROM emp WHERE empno = 7788;

SCOTT

PL/SQL 过程已成功完成。

 

(2)    在INSERT语句中使用PL/SQL记录

当在VALUES子句中使用记录变量插入数据时，列的顺序、个数、类型必须要与记录成员的顺序、个数、类型完全匹配。

示例：

DECLARE

  dept_record dept%ROWTYPE;

BEGIN

  dept_record.deptno := 60;

  dept_record.dname := 'ADMINISTRATOR';

  dept_record.loc := ' BEIJING ';

  INSERT INTO dept VALUES dept_record;

END;

 

输出：

PL/SQL 过程已成功完成。

 

在VALUES子句中使用记录成员

当在VALUES子句中使用记录成员插入数据时，列的顺序可以任意指定，但记录成员需要与之匹配。

示例：

DECLARE

  dept_record dept%ROWTYPE;

BEGIN

  dept_record.deptno := 70;

  dept_record.dname := 'SALES';

  INSERT INTO dept(deptno,dname) VALUES

    (dept_record.deptno,dept_record.dname);

END;

 

输出：

PL/SQL 过程已成功完成。

 

(3)    在UPDATE语句中使用PL/SQL记录

在SET子句中使用记录变量

当在SET子句中使用记录变量更新数据时，列的顺序、个数、类型必须要与记录成员的顺序、个数、类型完全匹配。

示例：

DECLARE

  dept_record dept%ROWTYPE;

BEGIN

  dept_record.deptno := 70;

  dept_record.dname := 'SALES';

  dept_record.loc := ' SHANGHAI ';

  UPDATE dept SET ROW = dept_record WHERE deptno = 70;

END;

 

输出：

PL/SQL 过程已成功完成。

 

在SET子句中使用记录成员

当在SET子句中使用记录成员更新数据时，列的顺序可以任意指定，但记录成员需要与之匹配。

示例：

DECLARE

  dept_record dept%ROWTYPE;

BEGIN

  dept_record.loc := ' GUANGZHOU ';

  UPDATE dept SET loc = dept_record.loc WHERE deptno = 70;

END;

 

输出：

PL/SQL 过程已成功完成。

 

(4)    在DELETE语句中使用PL/SQL记录

当使用PL/SQL记录删除数据时，只能在DELETE语句的WHERE子句中使用记录成员。

示例：

DECLARE

  dept_record dept%ROWTYPE;

BEGIN

  dept_record.deptno := 70;

  DELETE FROM dept WHERE deptno = dept_record.deptno;

END;

 

输出：

PL/SQL 过程已成功完成。

 

二． PL/SQL集合

PL/SQL集合可以处理单列多行数据。

2.1索引表

索引表也称为PL/SQL表，它是Oracle早期版本用于处理PL/SQL数组的数据类型。

注意，高级语言数组的元素个数是有限制的，而且下标不能为负值；而索引表的元素个数没有限制，并且下标可以为负值。

索引表只能作为PL/SQL复合数据类型使用，而不能作为表列的数据类型使用。

语法：

TYPE type_name IS TABLE OF element_type

[NOT NULL] INDEX BY key_type;

identifier type_name;

l         tType_name：用于指定用户自定义数据类型的名称(IS TABLE..INDEX表示索引表)

l         element_type：用于指定索引表元素的数据类型

l         nOT NULL：表示不允许引用NULL元素

l         key_type：用于指定索引表元素下标的数据类型(BINARY_INTEGER、PLS_INTEGER或VARCHAR2)

l         identifier：用于定义索引表变量

(1)    在索引表中使用BINARY_INTEGER和PLS_INTEGER

示例：

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;

 

输出：

原值 7: WHERE empno = &no;

新值 7: WHERE empno = 7788;

雇员名：SCOTT

PL/SQL 过程已成功完成。

 

(2)    在索引表中使用VARCHAR2

示例：

DECLARE

  TYPE area_table_type IS TABLE OF NUMBER

    INDEX BY VARCHAR2(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;

 

输出：

第一个元素：北京

最后一个元素：上海

PL/SQL 过程已成功完成。

 

2.2嵌套表

嵌套表也是一种用于处理PL/SQL数组的数据类型。

注意，高级语言数组的元素下标从0或1开始，并且元素个数是有限制的；而嵌套表的元素下标从1开始，并且元素个数没有限制。另外，高级语言的数组元素值是顺序的，而嵌套表元素的数组元素值可以是稀疏的。

索引表不能作为表列的数据类型使用，但嵌套表类型可以作为表列的数据类型使用。

语法：

TYPE type_name IS TABLE OF element_type;

identifier type_name;

l         type_name：用于指定嵌套表的类型名

l         element_type：用于指定嵌套表元素的数据类型

l         identifier：用于定义嵌套表变量

注意，当使用嵌套表元素时，必须首先使用其构造方法初始化嵌套表。

示例：

DECLARE

  TYPE ename_table_type IS TABLE OF emp.ename%TYPE;

  Ename_table ename_table_type := ename_table_type(‘A’,’A’);

 

(1)    在PL/SQL块中使用嵌套表

当在PL/SQL块中使用嵌套表变量时，必须首先使用构造方法初始化嵌套表变量，然后才能在PL/SQL块内引用嵌套表元素。

示例：

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(1));

  dbms_output.put_line('雇员名：'||ename_table(2));

END;

 

输出：

原值 7: WHERE empno = &no;

新值 7: WHERE empno = 7788;

雇员名：MARY

雇员名：SCOTT

PL/SQL 过程已成功完成。

 

其中,ename_table_type为嵌套表类型；而ename_table_type()是其构造函数。

 

(2)    在表列中使用嵌套表

嵌套表类型不仅可以在PL/SQL块中直接引用，也可以作为表列的数据类型使用。但如果在表列中使用嵌套表类型，必须首先使用CREATE TYPE命令建立嵌套表类型。另外注意，当使用嵌套表类型作为表列的数据类型时，必须要为嵌套表列指定专门的存储表。

示例：

CREATE TYPE phone_type IS TABLE OF VARCHAR2(20);

 

输出：

类型已创建。

 

CREATE TABLE employee(

  id NUMBER(4),

  name VARCHAR2(10),

  sal NUMBER(6,2),

  phone phone_type

)NESTED TABLE phone STORE AS phone_table;

 

输出：

表已创建。

 

在使用CREATE TYPE命令建立了嵌套表类型phone_type之后，就可在建立表employee时使用该嵌套表类型。

 

在PL/SQL块中为嵌套表列插入数据

当定义嵌套表类型时，Oracle自动为该类型生成相应的构造方法。当为嵌套表列插入数据时，需要使用嵌套表的构造方法。

示例：

BEGIN

  INSERT INTO employee VALUES(1,'SCOTT',800,

    phone_type('0471-3456788','13804711111'));

END;

 

输出：

PL/SQL 过程已成功完成。

 

在PL/SQL块中检索嵌套表列的数据

当在PL/SQL块中检索嵌套表列的数据时，需要定义嵌套表类型的变量接收其数据。

示例：

DECLARE

  phone_table phone_type;

BEGIN

  SELECT phone INTO phone_table

  FROM employee WHERE id = 1;

  FOR i IN 1..phone_table.COUNT LOOP

    dbms_output.put_line('电话号码：'||phone_table(i));

  END LOOP ;

END;

 

输出：

电话号码：0471-3456788

电话号码：13804711111

PL/SQL 过程已成功完成。

 

在PL/SQL块中更新嵌套表列的数据

当在PL/SQL块中更新嵌套表列的数据时，首先需要定义嵌套表变量，并使用构造方法初始化该变量，然后才可在执行部分使用UPDATE语句更新其数据。

示例：

DECLARE

  phone_table phone_type := phone_type('0471-3456788',

    '13804711111','0471-2233066','13056278568');

BEGIN

  UPDATE employee SET phone = phone_table

  WHERE id = 1;

END;

 

输出：

PL/SQL 过程已成功完成。

 

2.3变长数组(VARRAY)

VARRAY也是一种用于处理PL/SQL数组的数据类型，它也可以作为表列的数据类型使用。该数据类型与高级语言数组非常类似，其元素下标以1开始，并且元素的最大个数是有限制的。

语法：

TYPE type_name IS VARRAY(size_limit) OF element_type [NOT NULL];

identifier type_name;

l         type_name：用于指定VARRAY类型名

l         size_limit：用于指定VARRAY元素的最大个数

l         element_type：用于指定元素的数据类型

l         identifier：用于定义VARRAY变量

注意，当使用VARRAY元素时，必须要使用其构造方法初始化VARRAY元素。

示例：

DECLARE

TYPE ename_table_type IS VARRAY(20) OF emp.ename%TYPE;

ename_table ename_table_type := ename_table_type(‘A’,’A’);

 

(1)    在PL/SQL块中使用VARRAY

当在PL/SQL块中使用VARRAY变量时，必须首先使用其构造方法来初始化VARRAY变量，然后才能在PL/SQL块内引用VARRAY元素。

示例：

DECLARE

  TYPE ename table_type IS VARRAY(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;

 

(2)    在表列中使用VARRAY

VARRAY类型不仅可以在PL/SQL块中直接引用，也可以作为表列的数据类型使用。但如果要在表列中引用该数据类型，则必须使用CREATE TYPE命令建立VARRAY类型。

示例：

CREATE TYPE phone_type IS VARRAY(20) OF VARCHAR2(20);

/

CREATE TABLE employee(

  id NUMBER(4),

  name VARCHAR2(10),

  sal NUMBER(6,2),

  phone phone_type);

 

在PL/SQL块中操纵VARRAY列的方法与操纵嵌套表列的方法完全相同，但注意，嵌套表列的元素个数没有限制，而VARRAY列的元素个数是有限制的。

 

2.4PL/SAL记录表

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;

 

输出：

原值 7: WHERE empno = &no;

新值 7: WHERE empno = 7788;

雇员姓名：SCOTT

雇员工资：2100

PL/SQL 过程已成功完成。

 

2.5多级集合

多级集合是指嵌套了集合类型的集合类型。通过使用多级集合，可以在PL/SQL中实现类似于多维数组的功能。

(1)    在PL/SQL块中使用多级VARRAY

当在PL/SQL块中实现类似于多维数组的功能时，如果多维数组的元素个数是有限制的，那么可以在VARRAY类型中嵌套另一个VARRAY类型。

示例：

set serveroutput on

DECLARE

  --定义意味VARRAY

  TYPE al_varray_type IS VARRAY(10) OF INT;

  --定义二维VARRAY集合

  TYPE nal_varray_type IS VARRAY(10) OF al_varray_type;

  --初始化二维集合变量

  nvl nal_varray_type := nal_varray_type(

    al_varray_type(58,100,102),

    al_varray_type(55,6,73),

    al_varray_type(2,4));

BEGIN

  dbms_output.put_line('显示二维数组所有元素');

  FOR i IN 1..nvl.COUNT LOOP

    FOR j IN 1..nvl(i).COUNT LOOP

      dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j));

    END LOOP ;

  END LOOP ;

END;

 

输出：

显示二维数组所有元素

nvl(1,1)=58

nvl(1,2)=100

nvl(1,3)=102

nvl(2,1)=55

nvl(2,2)=6

nvl(2,3)=73

nvl(3,1)=2

nvl(3,2)=4

PL/SQL 过程已成功完成。

 

(2)    在PL/SQL块中使用多级嵌套表

在PL/SQL块中实现类似于多维数组的功能时，如果多维数组的元素个数没有限制，那么可以在嵌套类型中嵌套另一个嵌套表类型。

示例：

DECLARE

  --定义意味嵌套表

  TYPE al_table_type IS TABLE OF INT;

  --定义二维嵌套表集合

  TYPE nal_table_type IS TABLE OF al_table_type;

  --初始化二维集合变量

  nvl nal_table_type := nal_table_type(

    al_table_type(2,4),

    al_table_type(5,73));

BEGIN

  dbms_output.put_line('显示二维数组所有元素');

  FOR i IN 1..nvl.COUNT LOOP

    FOR j IN 1..nvl(i).COUNT LOOP

      dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j));

    END LOOP ;

  END LOOP ;

END;

 

输出：

显示二维数组所有元素

nvl(1,1)=2

nvl(1,2)=4

nvl(2,1)=5

nvl(2,2)=73

PL/SQL 过程已成功完成。

 

(3)    在PL/SQL块中使用多级索引表

在PL/SQL块中实现类似于多维数组的功能时，如果多维数组的元素个数没有限制，那么不仅可以使用多级嵌套表实现，也可以使用多级索引表实现。

示例：

DECLARE

  --定义意味table

  TYPE al_table_type IS TABLE OF INT

    INDEX BY BINARY_INTEGER;

  --定义二维table集合

  TYPE nal_table_type IS TABLE OF al_table_type

    INDEX BY BINARY_INTEGER;

  nvl nal_table_type;

BEGIN

  nvl(1)(1) := 10;

  nvl(1)(2) := 5;

  nvl(2)(1) := 100;

  nvl(2)(2) := 50;

  dbms_output.put_line('显示二维数组所有元素');

  FOR i IN 1..nvl.COUNT LOOP

    FOR j IN 1..nvl(i).COUNT LOOP

      dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j));

    END LOOP ;

  END LOOP ;

END;

 

输出：

显示二维数组所有元素
nvl(1,1)=10
nvl(1,2)=5
nvl(2,1)=100
nvl(2,2)=50
PL/SQL 过程已成功完成。

 

2.6集合方法

集合方法是Oracle所提供的用于操纵集合变量的内置函数或过程，其中EXISTS，COUNT，LIMIT，FIRST，NEXT，PRIOR和NEXT是函数，而EXTEND，TRIM和DELETE则是过程。

集合方法的调用语法：

collection_name.method_name[(parameters)]

注意，集合方法只能在PL/SQL语句中使用，而不能在SQL语句中调用。另外集合方法EXTEND和TRIM只适用于嵌套表和VARRAY，而不适用于索引表。

(1)    EXISTS

该方法用于确定集合元素是否存在，如果集合元素存在，则返回TRUE；如果集合元素不存在，则返回FALSE。

示例：

DECLARE

  TYPE ename_table_type IS TABLE OF emp.ename%TYPE;

  ename_table ename_table_type;

BEGIN

  IF ename_table.EXISTS(1) THEN

    ename_table(1) := 'SCOTT';

  ELSE

    dbms_output.put_line('必须初始化集合元素');

  END IF;

END;

 

输出：

必须初始化集合元素

PL/SQL 过程已成功完成。

 

注意，在引用嵌套表或VARRAY元素之前，必须首先初始化相应元素，而索引表元素的SELECT语句中可以直接引用。

 

(2)    COUNT

该集合方法用于返回当前变量中的元素总个数。如果集合元素存在数值，则统计结果会包含该元素；如果集合元素为NULL，则统计结果不会包含该元素。

示例：

DECLARE

  TYPE ename_table_type IS TABLE OF emp.ename%TYPE

    INDEX BY BINARY_INTEGER;

  ename_table ename_table_type;

BEGIN

  ename_table(-5) := 'SCOTT';

  ename_table(1) := 'SMITH';

  ename_table(5) := 'MARY';

  ename_table(10) := 'BLAKE';

  dbms_output.put_line('集合元素总个数：'||ename_table.COUNT);

END;

 

输出：

集合元素总个数：4
PL/SQL 过程已成功完成。

 

(3)    LIMIT

该方法用于返回集合元素的最大个数。因为嵌套表和索引表的元素个数没有限制，所以调用该方法会返回NULL；而对于VARRAY来说，该方法会返回VARRAY所允许的最大元素个数。

示例：

DECLARE

  TYPE ename_table_type IS VARRAY(20) OF emp.ename%TYPE;

  ename_table ename_table_type := ename_table_type('mary');

BEGIN

  dbms_output.put_line('集合元素的最大个数：'||ename_table.LIMIT);

END;

 

输出：

集合元素的最大个数：20
PL/SQL 过程已成功完成。

 

(4)    FIRST和LAST

FIRST方法用于返回集合变量第一个元素的下标，而LAST方法用于返回集合变量最后一个元素的下标。

示例：

DECLARE

  TYPE ename_table_type IS TABLE OF emp.ename%TYPE

    INDEX BY BINARY_INTEGER;

  ename_table ename_table_type;

BEGIN

  ename_table(-5) := 'SCOTT';

  ename_table(1) := 'SMITH';

  ename_table(5) := 'MARY';

  ename_table(10) := 'BLAKE';

  dbms_output.put_line('第一个元素：'||ename_table.FIRST);

  dbms_output.put_line('最后一个元素：'||ename_table.LAST);

END;

 

输出：

第一个元素：-5

最后一个元素：10

PL/SQL 过程已成功完成。

 

(5)    PRIOR和NEXT

PRIOR方法用于返回当前集合元素的前一个元素的下标，而NEXT方法则用于返回当前集合元素的后一个元素的下标。

示例：

DECLARE

  TYPE ename_table_type IS TABLE OF emp.ename%TYPE

    INDEX BY BINARY_INTEGER;

  ename_table ename_table_type;

BEGIN

  ename_table(-5) := 'SCOTT';

  ename_table(1) := 'SMITH';

  ename_table(5) := 'MARY';

  ename_table(10) := 'BLAKE';

  dbms_output.put_line('元素5的前一个元素：'||ename_table.PRIOR(5));

  dbms_output.put_line('元素5的后一个元素：'||ename_table.NEXT(5));

END;

 

输出：

元素5的前一个元素：1

元素5的后一个元素：10

PL/SQL 过程已成功完成。

 

(6)    EXTEND

该方法用于扩展集合变量的尺寸，并为它们增加元素。

注意，该方法只适用于嵌套表和VARRAY。该方法有EXTEND，EXTEND(n)，EXTEND(n,i)等三种调用格式，其中EXTEND用于为集合变量添加一个null元素，EXTEND(n)用于为集合添加n个null元素，而EXTEND(n,i)则用于为集合变量添加n个元素(元素值与第i个元素相同)。

示例：

DECLARE

  TYPE ename_table_type IS VARRAY(20) OF VARCHAR2(10);

  ename_table ename_table_type;

BEGIN

  ename_table := ename_table_type('MARY');

  ename_table.EXTEND(5,1);

  dbms_output.put_line('元素总个数：'||ename_table.COUNT);

END;

 

输出：

元素总个数：6

PL/SQL 过程已成功完成。

 

(7)    TRIM

该方法用于从集合尾部删除元素，它有TRIM和TRIM(n)两种调用格式。其中TRIM用于从集合尾部删除一个元素；而TRIM(n)则用于从集合尾部删除n个元素。

注意，该方法只适用于嵌套表和VARRAY。

示例：

DECLARE

  TYPE ename_table_type IS TABLE OF VARCHAR2(10);

  ename_table ename_table_type;

BEGIN

  ename_table := ename_table_type('A','A','A','A','A','A');

  ename_table.TRIM(2);

  dbms_output.put_line('元素总个数：'||ename_table.COUNT);

END;

 

输出：

元素总个数：4

PL/SQL 过程已成功完成。

 

(8)    DELETE

该方法用于删除集合元素，但该方法只适用于嵌套表和索引表，而不适用于VARRAY。该方法有DELETE，DELETE(n)，DELETE(m,n)等三种调用格式。其中DELETE用于删除集合变量的所有元素；DELETE(n)用于删除集合变量的第n个元素；而DELETE(m,n)则用于删除集合变量从m到n之间的所有元素。

示例：

DECLARE

  TYPE ename_table_type IS TABLE OF emp.ename%TYPE

    INDEX BY BINARY_INTEGER;

  ename_table ename_table_type;

BEGIN

  ename_table(-5) := 'SCOTT';

  ename_table(1) := 'SMITH';

  ename_table(5) := 'MARY';

  ename_table(10) := 'BLAKE';

  ename_table.DELETE(5);

  dbms_output.put_line('元素总个数：'||ename_table.COUNT);

END;

 

输出：

元素总个数：3

PL/SQL 过程已成功完成。

 

 2.7集合赋值

当使用嵌套表和VARRAY时，通过执行INSERT，UPDATE，FETCH，SELECT，赋值语句，用户可以将一个集合的数据赋值给另一个集合。从Oracle 10g 开始，当给嵌套表赋值时，还可以使用SET，MULTISET UNION，MULTISET INTERSECT，MULTISET EXCEPT等集合操作符。其中，SET操作符用于取消嵌套表中的重复值，MULTISET UNION用于取得两个嵌套表的并集(带有DISTINCT操作符可以取消重复结果)；MULTISET INTERSECT用于取得两个嵌套表的交集；MULTISET EXCEPT用于取得两个嵌套表的差集。

(1)    将一个集合的数据赋值给另一个集合

当使用赋值语句(:=)或SQL语句将源集合中的数据赋值给目标集合时，会自动清除目标集合原有的数据，并将源集合中的数据赋值给该目标集合。

示例：

DECLARE

  TYPE name_varray_type IS VARRAY(4) OF VARCHAR2(10);

  name_array1 name_varray_type;

  name_array2 name_varray_type;

BEGIN

  name_array1 := name_varray_type('SCOTT','SMITH');

  name_array2 := name_varray_type('a','a','a','a');

  dbms_output.put('name_array2的原数据：');

  FOR i IN 1..name_array2.COUNT LOOP

    dbms_output.put(' '||name_array2(i));

  END LOOP ;

  dbms_output.new_line;

  name_array2 := name_array1;

  dbms_output.put('name_array2的新数据：');

  FOR i IN 1..name_array2.COUNT LOOP

    dbms_output.put(' '||name_array2(i));

  END LOOP ;

  dbms_output.new_line;

END;

 

输出：

name_array2的原数据： a a a a

name_array2的新数据： SCOTT SMITH

PL/SQL 过程已成功完成。

 

注意，当进行集合赋值时，源集合和目标集合的数据类型必须完全一致。如果集合元素数据类型一致，但集合类型不一致，那也不能进行赋值。

 

(2)    给集合赋NULL值

编写PL/SQL程序时，某些情况可能需要清空集合变量的所有数据。在清空集合变量的所有数据时，既可以使用集合方法DELETE和TRIM，也可以将一个NULL集合变量赋值给目标集合变量。

示例：

DECLARE

  TYPE name_varray_type IS VARRAY(4) OF VARCHAR2(10);

  name_array name_varray_type;

  name_empty name_varray_type;

BEGIN

  name_array := name_varray_type('SCOTT','SMITH');

  dbms_output.put_line('name_array的原有元素个数：'||name_array.COUNT);

  name_array := name_empty;

  IF name_array IS NULL THEN

    dbms_output.put_line('name_array的现有元素个数：0');

  END IF;

END;

 

输出：

name_array的原有元素个数：2

name_array的现有元素个数：0

PL/SQL 过程已成功完成。

 

(3)    使用集合操作符给嵌套表赋值

从Oracle 10g 开始，在编写PL/SQL程序时允许将多个嵌套表的结果组合到某个嵌套表中，这项任务是使用ANSI集合操作符(SET，MULTISET UNION，MULTISET INTERSECT，MULTISET EXCEPT)来完成的。

 

使用SET操作符

SET操作符用于取消特定嵌套表中的重复值。

示例：

DECLARE

  TYPE nt_table_type IS TABLE OF NUMBER;

  nt_table nt_table_type := nt_table_type(2,4,3,1,2);

  result nt_table_type;

BEGIN

  result := SET(nt_table);

  dbms_output.put('result:');

  FOR i IN 1..result.COUNT LOOP

    dbms_output.put(' '||result(i));

  END LOOP ;

  dbms_output.new_line;

END;

 

输出：

result: 2 4 3 1

PL/SQL 过程已成功完成。

 

使用MULTISET UNION操作符

MULTISET UNION用于取得两个嵌套表的并集。当使用该操作符合并嵌套表结果时，结果集中会包含重复值。

示例：

DECLARE

  TYPE nt_table_type IS TABLE OF NUMBER;

  nt1 nt_table_type := nt_table_type(1,2,3);

  nt2 nt_table_type := nt_table_type(3,4,5);

  result nt_table_type;

BEGIN

  result := nt1 MULTISET UNION nt2;

  dbms_output.put('result:');

  FOR i IN 1..result.COUNT LOOP

    dbms_output.put(' '||result(i));

  END LOOP ;

  dbms_output.new_line;

END;

 

输出：

result: 1 2 3 3 4 5

PL/SQL 过程已成功完成。

 

使用MULTISET UNION DISTINCT操作符

MULTISET UNION DISTINCT操作符用于取得两个嵌套表的并集，并取消重复结果。当使用MULTISET UNION操作符合并嵌套表时，结果集中会包含重复值。为了取消结果集中的重复值，应该使用MULTISET UNION DISTINCT操作符。

示例：

DECLARE

  TYPE nt_table_type IS TABLE OF NUMBER;

  nt1 nt_table_type := nt_table_type(1,2,3);

  nt2 nt_table_type := nt_table_type(3,4,5);

  result nt_table_type;

BEGIN

  result := nt1 MULTISET UNION DISTINCT nt2;

  dbms_output.put('result:');

  FOR i IN 1..result.COUNT LOOP

    dbms_output.put(' '||result(i));

  END LOOP ;

  dbms_output.new_line;

END;

 

输出：

result: 1 2 3 4 5

PL/SQL 过程已成功完成。

 

使用MULTISET INTERSECT操作符

MULTISET INTERSECT操作符用于取得两个嵌套表的交集。

示例：

DECLARE

  TYPE nt_table_type IS TABLE OF NUMBER;

  nt1 nt_table_type := nt_table_type(1,2,3);

  nt2 nt_table_type := nt_table_type(3,4,5);

  result nt_table_type;

BEGIN

  result := nt1 MULTISET INTERSECT nt2;

  dbms_output.put('result:');

  FOR i IN 1..result.COUNT LOOP

    dbms_output.put(' '||result(i));

  END LOOP ;

  dbms_output.new_line;

END;

 

输出：

result: 3

PL/SQL 过程已成功完成。

 

使用MULTISET EXCEPT操作符

MULTISET EXCEPT用于取得两个嵌套表的差集。

示例：

DECLARE

  TYPE nt_table_type IS TABLE OF NUMBER;

  nt1 nt_table_type := nt_table_type(1,2,3);

  nt2 nt_table_type := nt_table_type(3,4,5);

  result nt_table_type;

BEGIN

  result := nt1 MULTISET EXCEPT nt2;

  dbms_output.put('result:');

  FOR i IN 1..result.COUNT LOOP

    dbms_output.put(' '||result(i));

  END LOOP ;

  dbms_output.new_line;

END;

 

输出：

result: 1 2

PL/SQL 过程已成功完成。

 

2.8比较集合

在Oracle 10g 之前，当使用嵌套表类型和VARRAY类型的集合变量时，开发人员可以检测集合变量是否为NULL。从Oracle 10g 开始，开发人员还可以比较两个集合变量是否相同，另外还可以在嵌套表上使用CARDINALITY，SUBMULTISET OF，MEMBER OF，IS A SET，IS EMPTY等集合操作符。其中，行数CARDINALITY用于返回嵌套表变量的元素个数，操作符SUBMULTISET OF用于确定一个嵌套表是否为另一个嵌套表的子集，操作符MEMBER OF用于检测特定数据是否为嵌套表元素，操作符IS A SET用于检测嵌套表是否包含重复的元素值，操作符IS EMPTY用于检测嵌套表是否为NULL。

(1)    检测集合是否为NULL

当编写复杂的PL/SQL应用程序时，经常需要检测集合变量是否为NULL。

示例：

DECLARE

  TYPE name_array_type IS VARRAY(3) OF VARCHAR2(10);

  name_array name_array_type;

BEGIN

  IF name_array IS NULL THEN

    dbms_output.put_line('name_array未初始化');

  END IF;

END;

 

输出：

name_array未初始化

PL/SQL 过程已成功完成。

 

从Oracle 10g 开始，当检测嵌套表是否为NULL时，不仅可以使用IS NULL操作符，也可以使用IS EMPTY操作符。

注意，IS EMPTY操作符只适用于嵌套表，而不适用于VARRAY。

示例：

DECLARE

  TYPE name_table_type IS TABLE OF VARCHAR2(10);

  name_table name_table_type;

BEGIN

  IF name_table IS EMPTY THEN

    dbms_output.put_line('name_table为初始化');

  END IF;

END;

 

输出：

name_array未初始化

PL/SQL 过程已成功完成。

 

(2)    比较嵌套表是否相同

在Oracle 10g 之前，不能直接比较两个嵌套表是否相同。但从Oracle 10g 开始，允许使用比较操作符=和!=检测两个嵌套表变量是否相同。

注意，使用这两个比较符只能比较嵌套表，而不能比较VARRAY和索引表。

示例：

DECLARE

  TYPE name_table_type IS TABLE OF VARCHAR2(10);

  name_table1 name_table_type;

  name_table2 name_table_type;

BEGIN

  name_table1 := name_table_type('SCOTT');

  name_table2 := name_table_type('SMITH');

  IF name_table1 = name_table2 THEN

    dbms_output.put_line('两个嵌套表完全相同');

  ELSE

    dbms_output.put_line('两个嵌套表数值不同');

  END IF;

END;

 

输出：

两个嵌套表数值不同

PL/SQL 过程已成功完成。

 

(3)    在嵌套表上使用集合操作符

从Oracle 10g 开始，开发人员可以在嵌套表上使用ANSI集合操作符CARDINALITY，MEMBER OF，IS A SET。

注意，这些操作符只适用于嵌套表，而不适用于VARRAY和索引表。

 

使用函数CARDINALITY

函数CARDINALITY用于返回嵌套表变量的元素个数。

示例：

DECLARE

  TYPE nt_table_type IS TABLE OF NUMBER;

  nt1 nt_table_type := nt_table_type(1,2,3,1);

BEGIN

  dbms_output.put_line('元素个数：'||CARDINALITY(nt1));

END;

 

输出：

元素个数：4

PL/SQL 过程已成功完成。

 

使用操作符SUBMULTISET OF

操作符SUBMULTISET OF用于确定一个嵌套表是否为另一个嵌套表的子集。

示例：

DECLARE

  TYPE nt_table_type IS TABLE OF NUMBER;

  nt1 nt_table_type := nt_table_type(1,2,3);

  nt2 nt_table_type := nt_table_type(1,2,3,4);

BEGIN

  IF nt1 SUBMULTISET OF nt2 THEN

    dbms_output.put_line('nt1是nt2的子集');

  END IF;

END;

 

输出：

nt1是nt2的子集

PL/SQL 过程已成功完成。

 

使用操作符MEMBER OF

操作符MEMBER OF用于检测特定数据是否为嵌套表的元素。

示例：

DECLARE

  TYPE nt_table_type IS TABLE OF NUMBER;

  nt1 nt_table_type := nt_table_type(1,2,3,5);

  v1 NUMBER := &v1;

BEGIN

  IF v1 MEMBER OF nt1 THEN

    dbms_output.put_line('v1是nt1的元素');

  END IF;

END;

 

输出：

原值 4: v1 NUMBER := &v1;

新值 4: v1 NUMBER := 2;

v1是nt1的元素

PL/SQL 过程已成功完成。

 

使用操作符IS A SET

操作符IS A SET用于检测嵌套表是否包含重复的元素值。

示例：

DECLARE

  TYPE nt_table_type IS TABLE OF NUMBER;

  nt1 nt_table_type := nt_table_type(1,2,3,5);

BEGIN

  IF nt1 IS A SET THEN

    dbms_output.put_line('嵌套表nt1无重复值');

  END IF;

END;

 

输出：

嵌套表nt1无重复值

PL/SQL 过程已成功完成。

 

三． 批量绑定

批量绑定是Oracle 9i新增加的特征，是指执行单次SQL操作能传递所有集合元素的数据。当在SELECT，INSERT，UPDATE，DELETE语句上处理批量数据时，通过批量绑定，可以极大地加快数据处理速度，提高应用程序的性能。

建立示例表：

CREATE TABLE demo(

  id NUMBER(6) PRIMARY KEY,

  name VARCHAR2(10)

);

 

输出：

表已创建。

 

(1)    不使用批量绑定

在Oracle 9i之前，当使用VALUES子句将数据插入到数据库表时，每次只能插入一条数据。如果要插入5000行数据，那么就需要调用5000次INSERT语句。因此，为了将多个集合元素的数据插入到数据库表，就必须要使用循环方式来完成。

示例：

DECLARE

  TYPE id_table_type IS TABLE OF NUMBER(6)

    INDEX BY BINARY_INTEGER;

  TYPE name_table_type IS TABLE OF VARCHAR2(10)

    INDEX BY BINARY_INTEGER;

  id_table id_table_type;

  name_table name_table_type;

  start_time NUMBER(10);

  end_time NUMBER(10);

BEGIN

  FOR i IN 1..5000 LOOP

    id_table(i) := i;

    name_table(i) := 'Name'||TO_CHAR(i);

  END LOOP ;

  start_time := dbms_utility.get_time;

  FOR i IN 1..id_table.COUNT LOOP

    INSERT INTO demo VALUES(id_table(i),name_table(i));

  END LOOP ;

  end_time := dbms_utility.get_time;

  dbms_output.put_line('总计事件(秒)：'||TO_CHAR((end_time-start_time)/100));

END;

 

输出：

总计事件(秒)：1.33
PL/SQL 过程已成功完成。

 

(2)    使用批量绑定

在Oracle 9i之中，当使用VALUES子句为数据库表插入数据时，通过使用批量绑定特征，只需要执行一条INSERT语句就可以插入5000行数据。

示例：

DECLARE

  TYPE id_table_type IS TABLE OF NUMBER(6)

    INDEX BY BINARY_INTEGER;

  TYPE name_table_type IS TABLE OF VARCHAR2(10)

    INDEX BY BINARY_INTEGER;

  id_table id_table_type;

  name_table name_table_type;

  start_time NUMBER(10);

  end_time NUMBER(10);

BEGIN

  FOR i IN 1..5000 LOOP

    id_table(i) := i;

    name_table(i) := 'Name'||to_char(i);

  END LOOP ;

  start_time := dbms_utility.get_time;

  FORALL i IN 1..id_table.COUNT

    INSERT INTO demo VALUES(id_table(i),name_table(i));

  end_time := dbms_utility.get_time;

  dbms_output.put_line('总计事件(秒)：'||TO_CHAR((end_time-start_time)/100));

END;

 

输出：

总计事件(秒)：.03

PL/SQL 过程已成功完成。

 

(3)    结论

显然使用批量绑定的速度要远远优于不使用批量绑定。批量绑定是使用BULK COLLECT子句和FORALL语句来完成的，其中BULK COLLECT子句用于取得批量数据，该子句只能用于SELECT语句、FETCH语句和DML返回子句中；而FORALL语句只适用于执行批量的DML操作。

 

3.1FORALL语句

当要在PL/SQL应用程序中执行批量INSERT、UPDATE和DELETE操作时，可以使用FORALL语句。在Oracle 9i之中，当使用FORALL语句时，必须具有连续的元素；而从Oracle 10g 开始，通过使用新增加的INDICES OF子句和VALUES OF子句，可以使用不连续的集合元素。

注意，FOR语句是循环语句，但FORALL语句却不是循环语句。

FORALL语句有三种执行语法：

语法一：

FORALL index IN lower_bound..upper_bound

  sql_statement;

l         index：是隐含定义的整数变量(将作为集合元素下标被引用)

l         lower_bound和upper_bound：分别是集合元素的上界和下界

 

语法二：

FORALL index IN INDICES OF collection

  [BETWEEN lower_bound AND upper_bound]

  sql_statement;

l         INDICES OF子句用于指定只取得对应于collection集合元素下标的index值

 

语法三：

FORALL index IN VALUES OF index_collection

  sql_statement;

l         VALUES OF：子句用于指定index值从集合变量index_collection中取得。

注意，在Oracle 9i中只能使用第一种语法。

 

在INSERT语句上使用批量绑定

当使用批量绑定为数据库表插入数据时，首先需要给集合元素赋值，然后使用FORALL语句执行批量绑定插入操作。

示例：

DECLARE

  TYPE id_table_type IS TABLE OF NUMBER(6)

    INDEX BY BINARY_INTEGER;

  TYPE name_table_type IS TABLE OF VARCHAR2(10)

    INDEX BY BINARY_INTEGER;

  id_table id_table_type;

  name_table name_table_type;

BEGIN

  FOR i IN 1..10 LOOP

    id_table(i) := i;

    name_table(i) := 'Name'||TO_CHAR(i);

  END LOOP ;

  FORALL i IN 1..id_table.COUNT

    INSERT INTO demo VALUES(id_table(i),name_table(i));

END;

 

输出：

PL/SQL 过程已成功完成。

 

在UPDATE语句上使用批量绑定

当使用批量绑定更新数据库数据时，首先需要给集合元素赋值，然后使用FORALL语句执行批量绑定更新操作。

示例：

DECLARE

  TYPE id_table_type IS TABLE OF NUMBER(6)

    INDEX BY BINARY_INTEGER;

  TYPE name_table_type IS TABLE OF VARCHAR2(10)

    INDEX BY BINARY_INTEGER;

  id_table id_table_type;

  name_table name_table_type;

BEGIN

  FOR i IN 1..5 LOOP

    id_table(i) := i;

    name_table(i) := 'N'||TO_CHAR(i);

  END LOOP ;

  FORALL i IN 1..id_table.COUNT

    UPDATE demo SET name = name_table(i)

    WHERE id = id_table(i);

END;

 

输出：

PL/SQL 过程已成功完成。

 

在DELETE语句上使用批量绑定

当使用批量绑定删除数据库表的数据时，首先需要为集合元素赋值，然后才使用FORALL语句执行批量绑定删除相应数据。

示例：

DECLARE

  TYPE id_table_type IS TABLE OF NUMBER(6)

    INDEX BY BINARY_INTEGER;

  id_table id_table_type;

BEGIN

  FOR i IN 1..3 LOOP

    id_table(i) := i;

  END LOOP ;

  FORALL i IN 1..id_table.COUNT

    DELETE FROM demo WHERE id = id_table(i);

END;

 

输出：

PL/SQL 过程已成功完成。

 

在FORALL语句中使用部分集合元素

使用FORALL语句执行批量绑定时，既可以使用集合的所有元素，也可以使用集合的部分元素。

示例：

DECLARE

  TYPE id_table_type IS TABLE OF NUMBER(6)

    INDEX BY BINARY_INTEGER;

  id_table id_table_type;

BEGIN

  FOR i IN 1..20 LOOP

    id_table(i) := 21 - i;

  END LOOP ;

  FORALL i IN 18..20

    INSERT INTO demo(id) VALUES(id_table(i));

END;

 

输出：

PL/SQL 过程已成功完成。

 

在FORALL语句上使用INDICES OF子句

INDICES OF子句是Oracle 10g 新增加的特征，该子句用于跳过NULL集合元素。

示例：

DECLARE

  TYPE id_table_type IS TABLE OF NUMBER(6);

  id_table id_table_type;

BEGIN

  id_table := id_table_type(4,null,6,null,8);

  FORALL i IN INDICES OF id_table

    DELETE FROM demo WHERE id = id_table(i);

END;

 

输出：

PL/SQL 过程已成功完成。

 

在FORALL语句上使用VALUES OF子句

VALUES OF子句是Oracle 10g 新增加的特征，该子句用于从其他集合变量中取得集合下标(index)的值。

先创建以下表：

CREATE TABLE new_demo AS SELECT * FROM demo WHERE 1 = 0;

 

示例：

DECLARE

  TYPE id_table_type IS TABLE OF demo.id%TYPE;

  TYPE name_table_type IS TABLE OF demo.name%TYPE;

  id_table id_table_type;

  name_table name_table_type;

  TYPE index_pointer_type IS TABLE OF PLS_INTEGER;

  index_pointer index_pointer_type;

BEGIN

  SELECT * BULK COLLECT INTO id_table,name_table

  FROM demo;

  index_pointer := index_pointer_type(2,5,6);

  FORALL i IN VALUES OF index_pointer

    INSERT INTO new_demo VALUES(id_table(i),name_table(i));

END;

 

输出：

PL/SQL 过程已成功完成。

 

使用SQL%BULK_ROWCOUNT属性

属性SQL%BULK_ROWCOUNT是专门为FORALL语句提供的，用于取得在执行批量绑定操作时第i个元素所作用的行数。

示例：

DECLARE

  TYPE dno_table_type IS TABLE OF NUMBER(3);

  dno_table dno_table_type := dno_table_type(10,20);

BEGIN

  FORALL i IN 1..dno_table.COUNT

    UPDATE emp SET sal = sal*1.1 WHERE deptno = dno_table(i);

  dbms_output.put_line('第2个元素更新的行数：'||SQL%BULK_ROWCOUNT(2));

END;

 

输出：

第2个元素更新的行数：4

PL/SQL 过程已成功完成。

 

3.2BULK COLLECT子句

BULK COLLECT子句用于取得批量数据，它只适用于SELECT INTO语句，FETCH INTO语句和DML返回子句。通过使用该子句，可以将批量数据存放到PL/SQL集合变量中。

语法：

…BULK COLLECT INTO collection_name[,collection_name]…

l         Collection_name：用于指定集合变量名。

 

在SELECT INTO语句中使用BULK COLLECT子句

在Oracle 9i之前，当编写SELECT INTO语句时，该语句必须返回一行数据，并且只能返回一行数据，否则会触发PL/SQL例外。从Oracle 9i开始，通过在SELECT INTO语句中使用BULK COLLECT子句，可以一次将SELECT语句的多行结果检索到集合变量中。

示例：

DECLARE

  TYPE emp_table_type IS TABLE OF emp%ROWTYPE

    INDEX BY BINARY_INTEGER;

  emp_table emp_table_type;

BEGIN

  SELECT * BULK COLLECT INTO emp_table

    FROM emp WHERE deptno = &no;

  FOR i IN 1..emp_table.COUNT LOOP

    dbms_output.put_line('雇员姓名：'||emp_table(i).ename);

  END LOOP ;

END;

 

输出：

原值 7: FROM emp WHERE deptno = &no;

新值 7: FROM emp WHERE deptno = 10;

雇员姓名： CLARK

雇员姓名：KING

雇员姓名：MILLER

PL/SQL 过程已成功完成。

 

在DML的返回子句中使用BULK COLLECT子句

执行DML操作时会改变数据库数据。为了取得DML操作所改变的数据，可以使用RETURNING子句。为了取得DML所作用的多行数据，需要使用BULK COLLECT子句。

示例：

DECLARE

  TYPE ename_table_type IS TABLE OF emp.ename%TYPE;

  ename_table ename_table_type;

BEGIN

  DELETE FROM emp WHERE deptno = &no

    RETURNING ename BULK COLLECT INTO ename_table;

  dbms_output.put('雇员名：');

  FOR i IN 1..ename_table.COUNT LOOP

    dbms_output.put(ename_table(i)||' ');

  END LOOP ;

  dbms_output.new_line;

END;

 

输出：

原值 5: DELETE FROM emp WHERE deptno = &no

新值 5: DELETE FROM emp WHERE deptno = 40

雇员名：JOHN MARY

PL/SQL 过程已成功完成。