精通Oracle10编程SQL(8)使用复合数据类型

/*
 *使用复合数据类型
 */

--PL/SQL记录
--定义PL/SQL记录
--自定义PL/SQL记录
DECLARE
  TYPE emp_record_type IS RECORD(
     name emp.ename%TYPE,
     salary emp.sal%TYPE,
     dno emp.deptno%TYPE
  );
  emp_record emp_record_type;
  ...
  
--使用PL/SQL记录
--在SELECT INTO语句中使用PL/SQL记录
--在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;

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

--在INSERT语句中使用PL/SQL记录
--在VALUES子句中使用记录变量
DECLARE
  dept_record dept%ROWTYPE;
BEGIN
  dept_record.deptno:=51;
  dept_record.dname:='ADMINISTRATOR';
  dept_record.loc:='BEIJING';
  insert into dept values dept_record;
END;

SELECT * FROM dept;

--在VALUES子句中使用记录成员
declare
  dept_record dept%ROWTYPE;
begin
  dept_record.deptno:=60;
  dept_record.dname:='SALES';
  INSERT INTO dept(deptno,dname) values
  (dept_record.deptno,dept_record.dname);
end;

--在UPDATE语句中使用PL/SQL记录
--在SET子句中使用记录变量
DECLARE
  dept_record dept%ROWTYPE;
begin
  dept_record.deptno:=60;
  dept_record.dname:='SALES';
  dept_record.loc:='SHANGHAI';
  update dept set row=dept_record where deptno=60;
end;

select * from dept;

--在SET子句中使用记录成员
DECLARE
  dept_record dept%ROWTYPE;
BEGIN
  dept_record.loc:='GUANGZHOU';
  UPDATE dept set loc=dept_record.loc where deptno=50;
end;

--在DELETE语句中使用PL/SQL记录
DECLARE
  dept_record dept%ROWTYPE;
begin
  dept_record.deptno:=50;
  delete from dept where deptno=dept_record.deptno;
end;

select * from dept;

--PL/SQL集合
--索引表
--在索引表中使用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;

SELECT * FROM EMP;  
  
--在索引表中使用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块中使用嵌套表
--其中，ename_table_type为嵌套表类型，而ename_table_type()是其构造方法
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(2));
END;

--在表列中使用嵌套表
create type phone_type is table of varchar2(20);

create table employeeTest(
  id number(4),name varchar2(10),sal number(6,2),
  phone phone_type
)nested table phone store as phone_table;

--在PL/SQL块中为嵌套表列插入数据
BEGIN
  INSERT INTO employeeTest values(1,'SCOTT',800,
  phone_type('0739-12121212','13800001111'));
END;

select * from employeeTest;

--在PL/SQL块中检索嵌套表列的数据
DECLARE
   phone_table phone_type;
BEGIN
   SELECT phone into phone_table from employeeTest where id=1;
   for i IN 1..phone_table.count loop
     dbms_output.put_line('电话号码：'||phone_table(i));
   end loop;
end;

--在PL/SQL块中更新嵌套表列的数据
DECLARE
  phone_table phone_type:=phone_type('0471-3456788','13825252525','0471-2233066','13056278568');
BEGIN
  UPDATE employeeTest set phone=phone_table where id=1;
END;

--变长数组（VARRAY）
--在PL/SQL块中使用VARRY
--当在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;

--在表列中使用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记录表
--处理多行多列数据
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;

--多级集合
--在PL/SQL块中使用多级VARRAY
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;

--在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,78),
      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;

--在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(1)(3):=57;
   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;

--集合方法
--集合方法是ORACLE所提供的用于操纵集合变量的内置函数或过程，其中EXISTS,COUNT,LIMIT,FIRST,NEXT,PRIOR和NEXT是函数，而EXTEND,TRIM和DELETE则是过程。
--集合方法的调用语法如下：collection_name.method_name[(parameters)]
--集合方法只能在PL/SQL语句中使用，而不能在SQL语句中调用。另外集合方法EXTEND和TRIM只适用于嵌套表和VARRAY，而不适用于索引表。

--EXISTS
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
--返回当前集合变量中的元素总个数,如果集合元素存在数值，则统计结果会包含该元素，如果集合元素为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(0):='SMITH';
  ename_table(5):='MARY';
  ename_table(10):='BLAKE';
  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
--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(0):='SMITH';
  ename_table(5):='MARY';
  ename_table(10):='BLAKE';
  dbms_output.put_line('第一个元素:'||ename_table.first);
  dbms_output.put_line('最后一个元素：'||ename_table.last);
END;

--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(0):='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;

--EXTEND
--用于扩展集合变量的尺寸，并为它们增加元素，该方法只适用于嵌套表和VARRAY。
--EXTEND:为集合变量添加一个null元素
--EXTEND(n):为集合变量添加n个null元素
--EXTEND(n,i)：为集合变量添加n个元素（元素值与第i个元素相同）
DECLARE
  TYPE ename_table_type is array(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;

--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');
   ename_table.trim(2);
   dbms_output.put_line('元素总个数：'||ename_table.count);
END;

--DELETE
--用于删除集合元素，该方法只适用于嵌套表和索引表，而不适用于VARRAY
--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;


--集合赋值
--通过执行INSERT，UPDATE，FETCH，SELECT，赋值语句，用户可以将一个集合的数据赋值给另一个集合。
--SET操作符用于取消嵌套表中的重复值
--MULTISET UNION用于取得两个嵌套表的并集（带有DISTINCT操作符可以取消重复结果）
--MULTISET INTERSECT用于取得两个嵌套表的交集
--MULTISET EXCEPT用于取得两个嵌套表的差集

--将一个集合的数据赋值给另一个集合
--当使用赋值语句（：=）或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;

--给集合赋NULL值
DECLARE
  TYPE name_varry_type is varray(4) of varchar2(10);
  name_array name_varry_type;
  name_empty name_varry_type;
BEGIN
  name_array:=name_varry_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;

--使用集合操作符给嵌套表赋值
--使用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操作符
--取得两个嵌套表的并集，并取消重复结果
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;

--使用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;

--使用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;


--比较集合
--CARDINALITY用于返回嵌套表变量的元素个数，操作符SUBMULTISET OF用于确定一个嵌套表是否为另一个嵌套表的子集
--MEMBER OF用于检测特定数据是否为嵌套表元素
--IS A SET用于检测嵌套表是否包含重复的元素值
--IS EMPTY用于检测嵌套表是否为NULL

--检测集合是否为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;

--当检测嵌套表是否为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;


--比较嵌套表是否相同
--允许使用比较操作符=和！=检测两个嵌套表变量是否相同
--注意，使用这两个比较符只能比较嵌套表，而不能比较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;

--在嵌套表上使用集合操作符
--从ORACLE 10g开始，可以在嵌套表上使用ANSI集合操作符CARDINALITY，MEMBER OF，IS A SET。
--注意，这些操作符只适用于嵌套表，而不适用于VARRAY和索引表

--使用函数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;

--SUBMULTISET OF：确定一个嵌套表是否为另一个嵌套表的子集
DECLARE
   TYPE nt_table_type is table of number;
   nt1 nt_table_type:=nt_table_type(1,2,4);
   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：检测特定数据是否为嵌套表的元素
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的元素');
   else
      dbms_output.put_line(v1||'不是nt1的元素');
   end if;
END;

--使用操作符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无重复值');
   else
      dbms_output.put_line('嵌套表nt1有重复值');
   end if;
END;

--批量绑定
create table demo(
  id number(6) primary key,name varchar2(10)
);

--不使用批量绑定
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;

select * from demo order by id;
delete demo;

--使用批量绑定
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;

--批量绑定是使用BULK COLLECT子句和FORALL语句来完成的，其中BULK COLLECT子句用于取得批量数据，该子句只能用于SELECT语句、FETCH语句和DML返回子句中
--而FORALL语句只适用于执行批量的DML操作

--FORALL语句
--在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;
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;

select * from demo;

--在UPDATE语句上使用批量绑定
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;

--在DELETE语句上使用批量绑定
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;

--在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..10 loop
     id_table(i):=11-i;
  end loop;
  forall i in 8..10
     insert into demo(id) values(id_table(i));
END;

SELECT * FROM DEMO;

--在FORALL语句上使用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 id=id_table(i);
END;


--在FORALL语句上使用VALUES OF子句
create table new_demo as select * from demo where 1=0;
select * from new_demo;
select * from demo;

delete new_demo;

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(4,6,7);      --将第4，6，7这三行数据复制到new_demo表中
  forall i in values of index_pointer
    insert into new_demo values(id_table(i),name_table(i));
END;

--使用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,2);
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;

select * from emp;

--BULK COLLECT子句
--在SELECT INTO语句中使用BULK COLLECT子句
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 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;