oracle 存储过程调用有游标的存储过程
create or replace procedure pcx_h5_htqyyscx(
O_CODE OUT NUMBER,
O_NOTE OUT VARCHAR2,
O_HASRECORDSET OUT NUMBER,
O_RESULT OUT SYS_REFCURSOR,
I_PAGING IN NUMBER,
I_PAGENO IN NUMBER,
I_PAGELENGTH IN NUMBER,
I_TOTALROWS IN OUT NUMBER,
I_SORT IN VARCHAR2,
·
·
·
I_JSNR IN VARCHAR2 --接收内容
)
/*
-- 创建临时表 用于存储游标数据(创建语句放在sql窗口执行)
create global temporary table temp_h5_tykj
(
code varchar2(50),
name varchar2(100)
)ON commit preserve rows;
*/
V_SQL VARCHAR2(32767);
V_COL_LST VARCHAR2(2000);
V_SORT VARCHAR2(2000);
--定义游标类型
rs sys_refcursor;
--TYPE 表示定义变量,变量类型是RECORD(变量1,变量2···)
type qyys_rec_type is record (code varchar2(100), contnumber varchar2(100), contname varchar2(100), datajson varchar2(4000), savepath varchar2(200));
-- 定义一个存放记录行类型元素的集合类型即表
type qyys_tab_type is table of qyys_rec_type;
qyys_tab qyys_tab_type;
begin
O_CODE := 1;
O_NOTE := '成功';
O_HASRECORDSET := 1;
--- 清空下临时表的数据
execute immediate 'truncate table temp_h5_tykj';
-- 调用子过程(返回游标的)
PCX_FAMILY_APP_QYYS(rs,I_TOTALROWS,I_PAGENO,I_PAGELENGTH,I_SORT,i_htid,v_khbh,1,null,null);
--遍历游标取数
fetch rs bulk collect into qyys_tab;
for i in 1..qyys_tab.count loop
qyys_tab.extend;
insert into temp_h5_tykj(code,name)
values (tykj_tab(i).code,tykj_tab(i).name); -- 将游标数据存放到临时表中
/*v_code := qyys_tab(i).code;
v_contnumber := qyys_tab(i).contnumber;
v_contname := qyys_tab(i).contname;
v_datajson := qyys_tab(i).datajson;
v_savepath := qyys_tab(i).savepath;*/
end loop;
/*V_SQL := 'select ''' || v_code || ''' as code,''' || v_contnumber || ''' as contnumber,''' || v_contname || ''' as contname,'''
|| v_datajson || ''' as datajson,''' || v_savepath || ''' as savepath from dual';
V_COL_LST := 'code as "code",contnumber as "contnumber",contname as "contname",datajson as "datajson",savepath as "savepath"';*/
·V_SQL := 'select code,name from temp_h5_tykj';
·V_COL_LST := 'code as "code",name as "name"';
·
---- 子过程实例
CREATE OR REPLACE PROCEDURE PCX_FAMILY_APP_QYYS(CUR_RESULT OUT TYPES.CURSORTYPE, --结果游标
O_COUNT OUT NUMBER, --结果集总记录数
I_PAGENUMBER IN NUMBER, --页码
I_PAGECOUNT IN NUMBER, --每页行数
I_SORT IN VARCHAR2 DEFAULT NULL, --排序字段
I_ID IN NUMBER, --合同ID 或 利益分配明细ID
I_CUSTNO IN VARCHAR2,
I_SIGN_TYPE IN NUMBER,
I_ID2 IN NUMBER,
I_ID3 IN NUMBER
) IS
·
·
·
V_SQL := '
SELECT '''||V_HTMB||''' o_tempcode,
'''||·····||''' o_contnumber,
'''||······||''' o_contname,
'''||·····||''' o_datajson,
'''||·····||''' o_savepath
FROM DUAL';
END IF;
/* o_tempcode 模板编码
o_contnumber 合同编号
o_contname 合同名称
o_datajson 填充数据
o_savepath 保存路径 */
V_COLIST := 'o_tempcode,o_contnumber,o_contname,o_datajson,o_savepath';
也可以参考以下地址:
https://blog.csdn.net/zy103118/article/details/86300243