SQL code-- 实例:Oracle存储过程返回数据集,并将数据集插入表
CREATE table userinfo(id number(18,0), name varchar2(30), sex varchar2(10), age number(3,0), address varchar2(100));
insert into userinfo(id,name,sex,age,address) values(1,'luoyoumou','男',33,'湖南省衡阳市');
insert into userinfo(id,name,sex,age,address) values(2,'miaoxiaoming','男',32,'江西省吉安市');
insert into userinfo(id,name,sex,age,address) values(3,'hanqiguang','男',28,'江西省赣州市');
commit;
create table userinfo2 as select * from userinfo where 1=2;
-- i_address 是存储过程的输入参数,o_cur是存储过程的输出游标参数,用以获取返回的结果集!
CREATE OR REPLACE PROCEDURE userinfo_proc(i_address VARCHAR2, o_cur OUT SYS_REFCURSOR)
IS
sqlstr VARCHAR2(200); -- 定义变量,用以存放SQL语句
BEGIN
sqlstr := 'SELECT Id, Name, Sex, Age, Address FROM userinfo WHERE Address = :i_address'; -- 给SQL变量赋值,其中 :i_address 是绑定变量,以提高执行效率!
OPEN o_cur FOR sqlstr USING i_address; -- 给游标变量赋值
END;
/
create or replace procedure userinfo_proc_get
is
type userinfo_rec_type is record (id number(18,0), name varchar2(30), sex varchar2(10), age number(3,0), address varchar2(100));
type userinfo_tab_type is table of userinfo_rec_type;
userinfo_tab userinfo_tab_type;
type uif_tab_type is table of userinfo%rowtype;
uif_tab uif_tab_type:=uif_tab_type();
v_cnt number(10,0);
rs sys_refcursor;
begin
userinfo_proc('江西省吉安市',rs);
fetch rs bulk collect into userinfo_tab;
-- 处理要插入 表的数据
for i in 1..userinfo_tab.count loop
uif_tab.extend;
uif_tab(i).id := userinfo_tab(i).id;
uif_tab(i).name := userinfo_tab(i).name;
uif_tab(i).sex := userinfo_tab(i).sex;
uif_tab(i).age := userinfo_tab(i).age;
uif_tab(i).address := userinfo_tab(i).address;
end loop;
--将数据批量插入 表
forall i in 1..uif_tab.count
insert into userinfo2 values uif_tab(i);
close rs;
commit;
end;
/
exec userinfo_proc_get;
-- 验证数据是否插入成功:
select * from userinfo2;