PLSQL中如何执行带有返回值的存储过程

Question

create or replace procedure zlhis.P_MEC_LIS_INSPECTION_SAMPLE(patid in varchar2 ,zycs in varchar2,results out sys_refcursor)
as

msql varchar2(6000);

begin
msql := '';

msql := msql || '
select DECODE(A.审核人, NULL, ''已报告'', ''已审核'') INSPECTION_STATE,
C.主页ID INPATIENT_ID,
B.开嘱医生 REQUISITION_PERSON,
A.ID INSPECTION_ID,
A.ID REQUISITION_ID,
C.病人ID PATIENT_NO,
B.医嘱内容 TEST_ORDER_NAME,
A.检验时间 CHECK_TIME,
A.检验人 INSPECTION_PERSON
from 检验标本记录 A,
(select distinct 标本id, 医嘱id from 检验项目分布) D,
病人医嘱记录 B,
病案主页 C
where
A.审核人 IS NOT NULL
AND A.ID = D.标本ID
and d.医嘱ID = B.ID
AND B.病人ID = C.病人ID
AND B.主页ID = C.主页ID
and c.主页id > 0
and c.出院日期 is null
and a.病人id ='''||patid||'''
and A.主页id ='''||zycs||'''
';
open results for msql;
exception
WHEN NO_DATA_FOUND THEN
open results for select * from (
SELECT
'' as INSPECTION_STATE,
'' as INPATIENT_ID ,
'' as REQUISITION_PERSON,
'' as INSPECTION_ID ,
'' as REQUISITION_ID ,
'' as PATIENT_NO ,
'' as TEST_ORDER_NAME,
sysdate as CHECK_TIME ,
''as INSPECTION_PERSON
from dual
) where rownum = 0;

end ;

在命令窗口中如何用call去执行

Answer 1

先定义包规范：
create or replace package zhidao_pkg as
type t_RetSet is REF CURSOR;
procedure test_proc (Condition1 varchar2,Condition2 varchar2,Condition3 varchar2,Condition4 varchar2,r_Cursor out t_RetSet,status out int);
end zhidao_pkg;
包体创建
create or replace package body zhidao_pkg is
procedure test_proc (Condition1 varchar2,Condition2 varchar2,Condition3 varchar2,Condition4 varchar2,r_Cursor out t_RetSet,status out int) as 
begin 
..........
end zhidao_pkg;
调用：
SQL> var cur refcursor
SQL> var stas int
SQL> exec test(10,:cur,:stas)
 
PL/SQL 过程已成功完成。
 
SQL> print :cur
SQL> print :stas

希望对你有帮助，有问题再追问吧，望采纳。