-- 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;
/
--数字型
-------------------创建调用返回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;
/