oracle存储过程声明及调用

一、声明

-- 此过程用于删除多余扎帐数据
-- IN_MZZZID: 门诊扎帐ID
-- OUT_MESSAGE: 输出执行结果信息

create or replace procedure  
    p_out_yl_mz_mzzz_del(IN_MZZZID IN VARCHAR2, OUT_MESSAGE OUT VARCHAR2) 
is
  v_msg varchar2(32767);
begin
  insert into BHIS.YL_MZ_ACCT_SORT_DETAIL_DELETE select * from BHIS.YL_MZ_ACCT_SORT_DETAIL t where mzzzid = IN_MZZZID;
  delete from BHIS.YL_MZ_ACCT_SORT_DETAIL t where mzzzid = IN_MZZZID;

  insert into BHIS.yl_mz_mzzzmx_delete select * from bhis.yl_mz_mzzzmx where mzzzid = IN_MZZZID;
  delete from bhis.yl_mz_mzzzmx where mzzzid = IN_MZZZID;

  insert into BHIS.yl_mz_mzzz_delete select * from bhis.yl_mz_mzzz where mzzzid = IN_MZZZID;
  delete from bhis.yl_mz_mzzz where mzzzid = IN_MZZZID;
  
  insert into BHIS.REP_MZSFRBB_GH_delete select * from bhis.REP_MZSFRBB_GH where mzzzid = IN_MZZZID;
  delete from bhis.REP_MZSFRBB_GH where mzzzid = IN_MZZZID;
  
  commit;
  
  OUT_MESSAGE := '操作成功';
EXCEPTION
  WHEN OTHERS THEN
    v_msg       := SQLERRM;
    OUT_MESSAGE := OUT_MESSAGE || '详细错误信息:' || v_msg;
end;

二、调用

declare
  msg varchar(32767);
begin
  P_OUT_YL_MZ_MZZZ_DEL('1389631', msg);
end;