bulk collect into集合

-- Created on 2012-10-29 by EX-YEZHIQIANG001 
--数字型
-------------------创建调用返回CURSOR(start)---------------------------
--创建返回CURSOR的存储过程
create or replace procedure returncursor(p_date out sys_refcursor)
is
begin
  open p_date for
 select * from test_zhi;
end returncursor;


--
declare
  v_date   sys_refcursor;
v_zhi    clob;
v_unit   varchar2(50);
begin

  returncursor(v_date);  --调用返回CURSOR的存储过程


  loop fetch v_date into v_zhi, v_unit;
  exit when v_date%notfound;
 dbms_output.put_line('v_zhi:'||substr(v_zhi,1,100)||',v_unit:'||v_unit);
  end loop;

close v_date;


end;
--------------------创建调用返回CURSOR(end)--------------------------


declare 
  
type array is table of number;
s_empno array;

begin
  
  select t.empno bulk collect into s_empno from emp t;

for i in 1..s_empno.count loop
 dbms_output.put_line(s_empno(i));
  end loop;

end; 
/


--字符串型
declare


type arrays is table of varchar2(20);
  s_node_code arrays;

begin

select t.fname bulk collect into s_node_code from emp t;

for i in 1..s_node_code.count loop
 dbms_output.put_line(s_node_code(i));
end loop;

end;
/


--使用表型 
declare
  
type arrayse is table of emp%rowtype;
  s_node_code arrayse;
 
begin
    
select * bulk collect into s_node_code from emp t;

for i in 1..s_node_code.count loop
 dbms_output.put_line(s_node_code(i).empno);
dbms_output.put_line(s_node_code(i).fname);
end loop;

end;
/


--自定义集合
declare


type temp is record(num number, node_code varchar2(20));  --这里如果与表的结构一样
type arrays is table of temp index by binary_integer;
s_node_code arrays;

begin

select t.empno, t.fname bulk collect into s_node_code from emp t;  --那这里可以写成*号 select * ...

for i in 1..s_node_code.count loop
 dbms_output.put_line(s_node_code(i).num);
dbms_output.put_line(s_node_code(i).node_code);
end loop;

end;
/


--循环bulk collect into(10G以前的写法)
-- Created on 2012-11-13 by EX-YEZHIQIANG001 
declare 
  
  type t_account_code is table of public_account_base.account_code %type;  --只能单个定义
type t_account_name is table of public_account_base.account_name %type;
s_account_code t_account_code;
s_account_name t_account_name;


  cursor curstr is
 select account_code, account_name from public_account_base;

begin
  
  open curstr;

  loop fetch curstr bulk collect into s_account_code, s_account_name limit 10;  --一次性提交10条数据
  --exit when s_account_code.count = 0;  --放在前面
  
forall i in 1..s_account_code.count
   insert into test_zhi(unit, ye)values(s_account_code(i), s_account_name(i));

  commit;
  exit when curstr%notfound;  -- 或者 exit when s_account.count = 0;  --放在前面
  end loop;

  close curstr;


end;
/


--循环bulk collect into(11G以后的写法)
-- Created on 2012-11-13 by EX-YEZHIQIANG001 
declare 
  
  type t_account is table of public_account_base %rowtype;  --可以一次性定义
s_account t_account;


  cursor curstr is
 select account_code, account_name from public_account_base;

begin
  
  open curstr;

  loop fetch curstr bulk collect into s_account limit 10;  --一次性提交10条数据
  exit when s_account.count = 0;
    
forall i in 1..s_account.count
   insert into test_zhi(unit, ye)values(s_account(i).account_code, s_account(i).name);

  commit;
  --exit when curstr%notfound;  -- 或者 exit when s_account.count = 0;  --放在前面
  
  end loop;

  close curstr;


end;
/