set serveroutput on

1、pl/sql记录
type emp_record_type is record(
     name emp.ename%type,
     salary emp.sal%type);
emp_record emp_record_type;
or
type emp_record emp%rowtype;

2、pl/sql表
type emp_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;

3、嵌套表(嵌套表的元素下标以1开始，值可以是稀疏的，要使用构造方法初始化嵌套表变量)
declare
 type ename_table_type is table of scott.emp.ename%type;
 ename_table ename_table_type;
begin
 ename_table:=ename_table_type('aa','');
 select ename into ename_table(1) from scott.emp where empno=&no;
 dbms_output.put_line(ename_table(1));
end;
/

4、变长数组(varray)
declare
 type name_array_type is varray(20) of scott.emp.ename%type;
 type sal_array_type  is varray(20) of scott.emp.sal%type;
 name_array name_array_type:=name_array_type('d');
 sal_array sal_array_type:=sal_array_type(1);
begin
 select ename,sal into name_array(1),sal_array(1) from scott.emp where empno=&eno;
 dbms_output.put_line(name_array(1)||','||sal_array(1));
end;
/

declare
 type name_array_type is varray(20) of scott.emp.ename%type;
 type sal_array_type  is varray(20) of scott.emp.sal%type;
 name_array name_array_type;
 sal_array sal_array_type;
begin
 select ename,sal bulk collect into name_array,sal_array from scott.emp where deptno=&dno;
 for i in 1 .. name_array.count loop
  dbms_output.put_line(name_array(i)||','||sal_array(i));
 end loop;
end;
/

5、pl/sql记录表
declare
 type emp_table_type is table of scott.emp%rowtype index by binary_integer;
 emp_table emp_table_type;
begin
 select * into emp_table(1) from scott.emp where empno=&no;
 dbms_output.put_line(emp_table(1).ename||'  '||emp_table(1).sal);
end;
/

declare
 type emp_table_type is table of scott.emp%rowtype index by binary_integer;
 emp_table emp_table_type;
begin
 select * bulk collect into emp_table from scott.emp;
 for i in 1 .. emp_table.count loop
  dbms_output.put_line(emp_table(i).ename||'  '||emp_table(i).sal);
 end loop;
end;
/

批量绑定

1、在SELECT INTO 语句中使用BULK COLLECT子句
declare
  type emp_table_type is table of scott.emp%rowtype index by binary_integer;
  emp_table emp_table_type;
begin
     select * bulk collect into emp_table from scott.emp where deptno=&no;
     for i in 1..emp_table.count loop
         dbms_output.put_line(emp_table(i).ename||'  '||emp_table(i).job
         ||'  '||emp_table(i).sal);
     end loop;
end;
/

2、在KML返回子句中使用BULK COLLECT子句
declare
  type ename_table_type is table of scott.emp.ename%type;
  type sal_table_type is table of scott.emp.sal%type;
  ename_table ename_table_type;
  sal_table sal_table_type;
begin
     update scott.emp set sal=sal*1.2 where deptno=&no returning ename,sal
     bulk collect into ename_table,sal_table;
     for i in 1..ename_table.count loop
         dbms_output.put_line(ename_table(i)||'  '||sal_table(i));
     end loop;
end;
/

3、使用FORALL语句(批量插入、更新、删除)
create table demo (id number(6),name varchar2(10));
/
declare
  type id_table_type is table of demo.id%type index by binary_integer;
  type name_table_type is table of demo.name%type index by binary_integer;
  id_table id_table_type;
  name_table name_table_type;
begin
     for i in 1..20000 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;
/

4、使用SQL%BULK_ROWCOUNT属性
declare
  type deptno_table_type is table of number(3);
  deptno_table deptno_table_type:=deptno_table_type(10,20);
begin
     forall i in 1..deptno_table.count
            update scott.emp set sal=sal*1.2 where deptno=deptno_table(i);
     dbms_output.put_line('部门10被更新了'||sql%bulk_rowcount(1)||'行');
     dbms_output.put_line('部门20被更新了'||sql%bulk_rowcount(2)||'行');
end;
/

declare
  type deptno_table_type is table of number(3);
  deptno_table deptno_table_type := deptno_table_type(10, 20);
begin
  forall i in 1 .. deptno_table.count
    update scott.emp set sal = sal * 1.2 where deptno = deptno_table(i);
  for i in 1 .. deptno_table.count loop
    dbms_output.put_line('部门' || deptno_table(i) || '被更新了' || sql%bulk_rowcount(i) || '行');
  end loop;
end;
/