集合的批量绑定(二)bulkcollect

1、在select into 中使用BULK COLLECT，批量绑定到集合变量中

DECLARE
  TYPE list_of_emp_type IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
  list_emp list_of_emp_type;
BEGIN
  SELECT * BULK COLLECT INTO list_emp FROM emp WHERE deptno = &deptno;
  FOR i IN 1 .. list_emp.count LOOP
    dbms_output.put_line('工号: ' ||list_emp(i).empno||',姓名: '||list_emp(i).ename ||
     ',的薪水: ' || list_emp(i).sal || '$');
  END LOOP;
END;
/

2、在DML返回子句中使用BULK COLLECT

declare
type list_name_type is table of emp.ename%type index by pls_integer;
type list_sal_type  is table of emp.sal%type index by pls_integer;
list_name           list_name_type;
list_sal            list_sal_type;
BEGIN
  update emp set sal=sal*1.1 where deptno=&deptno returning ename,sal bulk collect into
list_name,list_sal;
  for i in 1..list_name.count loop
    DBMS_OUTPUT.PUT_LINE('姓名 :'||list_name(i)||'new sal: '||list_sal(i));
    end loop;
END;
/

 3、在update中使用BULK COLLECT

declare
type list_name_type is table of emp.ename%type index by pls_integer;
type list_sal_type  is table of emp.sal%type index by pls_integer;
list_name           list_name_type;
list_sal            list_sal_type;
list_name1          list_name_type;
list_sal1           list_sal_type;
BEGIN
  update emp set sal=sal*1.2 where deptno=&deptno returning ename,sal bulk collect into
list_name,list_sal;
  for i in 1..list_name.count loop
    DBMS_OUTPUT.PUT_LINE('name :'||list_name(i)||'new sal: '||list_sal(i));
    end loop;
END;
/