集合方法、赋值、比较 批量绑定

集合方法

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;

 

 

 

COUNT该集合返回当前集合变量中元素总个数。

 

DECLARE

  TYPE ename_table_type  IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;

  ename_table  ename_table_type;

BEGIN

  ename_table(1):='SCOTT';

  ename_table(2):='SMITH';

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

END;

 

 

 

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;

 

 

 

FIRST、LAST分别用于返回集合变量的第一个、最后一个元素的下标。PRIOR、NEXT分别用于返回当前集合元素的前一个、后一个元素的下标。

 

 

EXTEND该方法用于扩展集合变量的尺寸，并未它们增加元素。该方法只适用于嵌套表和VARRAY。EXTEND用于为集合变量添加一个null元素，EXTEND(n)用于为集合变量添加n个null元素，而EXTEND(n,i)用于为集合添加n个元素(元素值与第i个元素相同)。

 

 

TRIM用于从集合尾部删除元素。其中TRIM用于从集合尾部删除一个元素，而TRIM(n)则用于从集合尾部删除n个元素。

 

 

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

 

 

 

集合赋值

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

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;

 

 

 

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;

 

 

 

MULTISET UNION DISTINCT操作符用于取得两个嵌套表的并集，并取消重复结果。MULTISET INTERSECT操作符用于取得两个嵌套表的交集。MULTISET EXCEPT用于取得两个嵌套表的差集。

 

 

比较集合

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

 

DECLARE

  TYPE nt_table_type IS TABLE OF NUMBER;

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

BEGIN

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

END;

 

 

 

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

 

DECLARE

  TYPE  nt_table_type IS TABLE OF NUMBER;

  nt1 nt_table_type:=nt_table_type(1,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;

 

 

 

操作符MEMBER OF用于检测特定数据是否为嵌套表的元素。IS A SET用于检测嵌套表是否包含重复的元素值。

 

 

批量绑定是指执行单次SQL操作能传递所有集合元素的数据。通过批量绑定，可以极大地加快数据处理速度，提高应用程序的性能。

FORALL语句，其语法为：

FORALL index IN lower_bound..upper_bound

    sql_statement;

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

 

FORALL index IN  INDICES OF collection

   [BETWEEN lower_bound.AND.  upper_bound]

   sql_statement;

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

 

FORALL index in  VALUES  OF  index_collection

   sql_statement;

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

 

INDICES OF用于跳过NULL集合元素。

DECLARE

  TYPE id_table_type IS TABLE OF NUMBER(6);

  id_table id_table_type;

BEGIN

  id_table:=id_table_type(1,null,3,null,5);

  FORALL i IN INDICES OF id_table

    DELETE FROM demo WHERE num_id=id_table(i);

END;

 

 

VALUES OF用于从其他集合变量中取得集合下标的值。

DECLARE

  TYPE id_table_type IS TABLE OF demo.num_id%TYPE;

  TYPE name_table_type IS TABLE OF demo.num_name%TYPE;

  id_table id_table_type;

  name_table name_table_type;

  TYPE index_point_type IS TABLE OF PLS_INTEGER;

  index_point index_point_type;

BEGIN

  SELECT * BULK COLLECT INTO id_table,name_table FROM DEMO;

  index_point:=index_point_type(6,8,10);

  FORALL i IN VALUES OF index_point

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

END;

 

 

SQL%BULK_ROWCOUNT用于取得在执行批量绑定操作时第i个元素所作用的行数。

 

 

在SELECT INO 语句中使用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;

 

 

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

DECLARE

  TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;

  emp_table emp_table_type;

BEGIN

  DELETE FROM emp WHERE  deptno=&no;

     RETURNING ename BULK COLLECT INTO emp_table;

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

  FOR i IN 1..emp_table.COUNT LOOP

    dbms_output.put_line(' '||emp_table(i));

  END LOOP;

  dbms_output.new_line();

END;