-- 实例：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;