Oracle 创建存储过程及调用测试示例

/*
select * from NationalDiseases
*/

/*

CREATE OR REPLACE PROCEDURE proc_NationalDiseases
(
   inp_no_str IN varchar2,
   v_st_result OUT sys_refcursor
)
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('传入参数：'||inp_no_str);
  open v_st_result for 
      select guid,inp_no,interfacetype,operationtype,savedate from nationaldiseases a where  a.inp_no = inp_no_str;
 DBMS_OUTPUT.PUT_LINE('传入111参数：'||inp_no_str);
END;

insert into NationalDiseases(inp_no,InterfaceType,OperationType,savedate)values('2240803000016','2.2.6 住院病案首页','add',sysdate)

insert into NationalDiseases(inp_no,InterfaceType,OperationType,savedate)values('2240803000016','2.2.7 出院记录','add',sysdate)

insert into NationalDiseases(inp_no,InterfaceType,OperationType,savedate)values('2240803000016','2.2.14 死亡信息','add',sysdate)

*/

/*
DECLARE
  v_result sys_refcursor;
BEGIN
  proc_NationalDiseases('Apple,Orange,Banana', v_result);
 
END;
*/

DECLARE
  p_code NationalDiseases.inp_no%type;
  out_var sys_refcursor;
  v_row  NationalDiseases%ROWTYPE;
BEGIN

 p_code:='2240803000016'; --录入参数赋值

 DBMS_OUTPUT.PUT_LINE('传入参数：'||p_code);

 DBMS_OUTPUT.PUT_LINE('输出结果：');

 proc_NationalDiseases(p_code,out_var);  --调用

  LOOP

   FETCH out_var INTO v_row;  --调用的存储过程结果给变量

   EXIT WHEN out_var%NOTFOUND;

   DBMS_OUTPUT.PUT_LINE(v_row.guid||' , '||v_row.inp_no||' , '||v_row.interfacetype||' , '||v_row.operationtype||' , '||v_row.savedate);

  END LOOP; 

END ;