oracle写存储过程ssm中调用存储过程

最新推荐文章于 2024-08-09 15:02:39 发布

toanofasdof

最新推荐文章于 2024-08-09 15:02:39 发布

阅读量643

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/weixin_38323645/article/details/97127255

版权

Oracle 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

在package中定义存储过程

create or replace package PEIS_ReportPictureManage is

  type ref_cur is ref cursor;

  procedure YW_QueryPic(paraTJH000 in varchar2,
                        paraBMBH00 in varchar2,
                        cur_Pic out ref_cur,
                        cur_SFXMJG out ref_cur);

  procedure YW_QueryReportPic(paraTJH000 in varchar2,
                              paraBMBH00 in varchar2,
                              paraSFDY00 in varchar2,
                              paraSFDL00 in varchar2,
                              cur_Pic out ref_cur,
                              cur_SFXMJG out ref_cur);

  function YW_JudgeBBWCJ(paraTJH000 in number,
                         paraBMBH00 in number)
  return number;

  procedure QueryPictureDept(paraTJH000 in number,
    paraBMBH00 in varchar2,
    cur_PictureDept out ref_cur);

  procedure queryJCXMTX(paraTJH000 in number,
                        cur_BMBH out ref_cur,
                        cur_CSTP out ref_cur);

  procedure SavePicToDB(paraTJH000 in number,
                        paraBMBH00 in number,
                        paraKSLX00 in varchar2,
                        paraSFCS00 in char,
                        paraTPBT00 in varchar2,
                        paraTPFX00 in varchar2,
                        paraTP0000 in blob);

  procedure queryPicForFrx(paraTJH000 in number,
                           paraBMBH00 in number,
                           paraOPFLAG in varchar2,
                           cur_Image out ref_cur,
                           cur_CSXM00 out ref_cur,
                           cur_CSJCXM out ref_cur);

  --查询检查项目图像
  procedure queryExamItemPicture(paraTJH000 in number,
    paraJCKSID in number,
    cur_ExamItemPicture out ref_cur,
    cur_ExamArea out ref_cur);

  --检查项目图像操作
  function examItemPictureOperate(paraOPFLAG in number,
    paraTJH000 in number,
    paraBMBH00 in number,
    paraKSLX00 in varchar2,
    paraJCBW00 in varchar2,
    paraBDY000 in varchar2,
    paraTPBT00 in varchar2,
    paraCZY000 in number)return varchar2;

  procedure queryPicFromOtherSysTable(paraTJH000 in number,
                                      paraDSFBM0 in varchar2, --此处代表 第三方表名
                                      paraTJLM00 in varchar2,  --此处代表 第三方表中的搜索条件列
                                      paraTPLM00 in varchar2,
                                      cur_Pic    out ref_cur);

  --复制科室图片
  procedure CopyDeptPicture(paraTJH000 in number,
    paraTOTJH0 in number,
    paraYKSID0 in number,
    paraTOKSID in number,
    paraTPBT00 in varchar2,
    paraTSXX00 out varchar2,
    cur_ExamItemPicture out ref_cur,
    cur_PictureServer out ref_cur);

  procedure QueryPersonDeptInfo(paraTJH000 in number,
    paraCZY000 in number,
    cur_PersonDeptInfo out ref_cur);

  --删除接口图片
  function DeleteInterfacePicture(paraTJH000 in number,
    paraBMBH00 in number,
    paraTPLJ00 in varchar2)return varchar2;

end PEIS_ReportPictureManage;

在package body中写实际的存储过程

create or replace package body PEIS_ReportPictureManage is

  --此过程为人民医院报告打印接口过程，慎改
  procedure YW_QueryPic(paraTJH000 in varchar2,
                        paraBMBH00 in varchar2,
                        cur_Pic out ref_cur,
                        cur_SFXMJG out ref_cur)is
  begin
    open cur_Pic for
      select 0 NX,
             A.TJH000,
             B.YXLX00 KSLX00,
             A.BMBH00,
             min(SFYWID) SFYWID,
             min(JCID00) JCID00,
             B.JKMS00,
             B.DSFPZ0,
             B.FTPPZ0,
             B.FTPYHM,
             B.FTPMM0,
             B.MS0000,
             B.MBFS00,
             C.TJDM00,
             B.YXMBM0,
             B.JKZJBM,
             B.FTPDKH,
             B.TPFX00,
             B.FTPURL,
             B.FKBGMB,
             nvl(B.SFDGMB,'0') SFDGMB,
             (select BMMC00 from BM_BMBM00 where BMBH00 = A.BMBH00) BMMC00
        from SY_PE_YWSJ_TJJCXM a, SY_PE_YWZD_YXPZB0 b, SY_PE_YWSJ_TJZ000 c
       where A.BMBH00 = B.BMBH00
         and A.TJH000 = C.TJH000
         and A.TJH000 = paraTJH000
         and B.SFYX00 = '1'
         and A.BMBH00 = nvl(paraBMBH00, A.BMBH00)
         and YW_JudgeBBWCJ(A.TJH000,B.BMBH00) = 1
       group by A.TJH000,
                A.BMBH00,
                B.YXLX00,
                B.JKMS00,
                B.DSFPZ0,
                B.FTPPZ0,
                C.TJDM00,
                B.MS0000,
                B.MBFS00,
                B.YXMBM0,
                B.FTPYHM,
                B.FTPMM0,
                B.JKZJBM,
                B.FTPDKH,
                B.FTPURL,
                B.TPFX00,
                B.FKBGMB,
                B.SFDGMB;

    open cur_SFXMJG for
      select A.*, (
        select BMMC00 from BM_BMBM00 where BMBH00 = A.BMBH00) BMMC00 from(
          select distinct TJH000, TPLJ00, BMBH00, 0 TPLY00, '1' BDY000
            from VW_PE_SFXMJG
           where TJH000 = paraTJH000
             and BMBH00 = nvl(paraBMBH00, BMBH00)
          union all
            select TJH000,  A.KSLX00||'/'||A.CTRQ00||'/'||to_char(A.TJH000)||'/'||A.TPBT00 TPXDLJ,
                   BMBH00, 1 TPLY00, BDY000
              from SY_PE_YWSJ_JCXMTX A
             where TJH000 = paraTJH000
               and BMBH00 = nvl(paraBMBH00, BMBH00)
             )A;
  end;

  procedure YW_QueryReportPic(paraTJH000 in varchar2,
                              paraBMBH00 in varchar2,
                              paraSFDY00 in varchar2,
                              paraSFDL00 in varchar2,
                              cur_Pic out ref_cur,
                              cur_SFXMJG out ref_cur)is
  begin
    open cur_Pic for
      select * from (
        select 0 NX,
               A.TJH000,
               B.YXLX00 KSLX00,
               A.BMBH00,
               min(SFYWID) SFYWID,
               min(JCID00) JCID00,
               B.JKMS00,
               B.DSFPZ0,
               B.FTPPZ0,
               B.FTPYHM,
               B.FTPMM0,
               B.MS0000,
               B.MBFS00,
               C.TJDM00,
               B.YXMBM0,
               B.JKZJBM,
               B.FTPDKH,
               B.TPFX00,
               B.FTPURL,
               B.FKBGMB,
               B.DYJ000,
               nvl(B.SFDGMB,'0') SFDGMB,
               (select PXXH00 from BM_BMBM00 where BMBH00 = A.BMBH00) PXXH00,
               (select BMMC00 from BM_BMBM00 where BMBH00 = A.BMBH00) BMMC00
          from SY_PE_YWSJ_TJJCXM a, SY_PE_YWZD_YXPZB0 b, SY_PE_YWSJ_TJZ000 c
         where A.BMBH00 = B.BMBH00
           and A.TJH000 = C.TJH000
           and A.TJH000 = paraTJH000
           and B.SFYX00 = '1'
           and A.BMBH00 = nvl(paraBMBH00, A.BMBH00)
           and ((paraSFDL00 = 0)
                 or (paraSFDL00 = 1 and B.SFDLDY = 0)
                 or (paraSFDL00 = 2 and B.SFDLDY = 1))
           and YW_JudgeBBWCJ(A.TJH000,B.BMBH00) = 1
         group by A.TJH000,
                  A.BMBH00,
                  B.YXLX00,
                  B.JKMS00,
                  B.DSFPZ0,
                  B.FTPPZ0,
                  C.TJDM00,
                  B.MS0000,
                  B.MBFS00,
                  B.YXMBM0,
                  B.FTPYHM,
                  B.FTPMM0,
                  B.JKZJBM,
                  B.FTPDKH,
                  B.FTPURL,
                  B.TPFX00,
                  B.FKBGMB,
                  B.SFDGMB,
                  B.DYJ000
      ) order by PXXH00;

    open cur_SFXMJG for
      select A.*, (
        select BMMC00 from BM_BMBM00 where BMBH00 = A.BMBH00) BMMC00 from(
          select distinct TJH000, TPLJ00, BMBH00, 0 TPLY00, '1' BDY000
            from VW_PE_SFXMJG
           where TJH000 = paraTJH000
             and BMBH00 = nvl(paraBMBH00, BMBH00)
          union all
            select TJH000,  A.KSLX00||'/'||A.CTRQ00||'/'||to_char(A.TJH000)||'/'||A.TPBT00 TPXDLJ,
                   BMBH00, 1 TPLY00, BDY000
              from SY_PE_YWSJ_JCXMTX A
             where TJH000 = paraTJH000
               and BMBH00 = nvl(paraBMBH00, BMBH00)
               and (nvl(paraSFDY00, '0') = '0' or (paraSFDY00 = '1' and BDY000 = '1'))
             )A;
  end;

  function YW_JudgeBBWCJ(paraTJH000 in number,
                         paraBMBH00 in number)
  return number
  as
    vCount number;
    vResult number;
  begin
    vResult:= 1;
    if paraBMBH00 = 11 then
      select count(1) into vCount from SY_PE_YWSJ_TJZFJ0 where TJH000 = paraTJH000 and WCCJDD = '2';
      if vCount > 0 then
        vResult:= 0;
      end if;
    end if;
    return vResult;
  end;

  --查询影像科室
  procedure QueryPictureDept(paraTJH000 in number,
    paraBMBH00 in varchar2,
    cur_PictureDept out ref_cur)is
  begin
    open cur_PictureDept for
      with VW_PIC as
      (select distinct BMBH00 from(
          select BMBH00
          from VW_PE_SFXMJG where TJH000 = paraTJH000 and BMBH00 = nvl(paraBMBH00, BMBH00)
          union all
          select BMBH00
          from SY_PE_YWSJ_JCXMTX A where TJH000 = paraTJH000 and BMBH00 = nvl(paraBMBH00, BMBH00) and BDY000 = '1'))
          select A.*, (select BMMC00 from BM_BMBM00 where BMBH00 = A.BMBH00) BMMC00,
          (select YXLX00 from BM_BMBM00 where BMBH00 = A.BMBH00) YXLX00 from VW_PIC A;
  end;

  procedure queryJCXMTX(paraTJH000 in number,
                        cur_BMBH out ref_cur,
                        cur_CSTP out ref_cur) is
  begin
    open cur_BMBH for
      select distinct BMBH00 from VW_PE_JCXMTX a
        where TJH000 = paraTJH000
        and BDY000 = '1'
        and exists (select 1 from  SY_PE_YWZD_YXPZB0 where a.bmbh00=bmbh00 and MBFS00='0');

      open cur_CSTP for
      select * from VW_PE_JCXMTX a
        where TJH000 = paraTJH000
        and BDY000 = '1'
        and exists (select 1 from  SY_PE_YWZD_YXPZB0 where a.bmbh00=bmbh00 and MBFS00='0');
  end;

  procedure queryItems(paraTJH000  in number,
                       paraBMBH00  in number,
                       cur_SFXMJG out ref_cur) is
  begin
     open cur_SFXMJG for
        select * from VW_PE_SFXMJG where TJH000=paraTJH000 and BMBH00=paraBMBH00;
  end;

  procedure SavePicToDB(paraTJH000 in number,
                        paraBMBH00 in number,
                        paraKSLX00 in varchar2,
                        paraSFCS00 in char,
                        paraTPBT00 in varchar2,
                        paraTPFX00 in varchar2,
                        paraTP0000 in blob)is
  begin
    delete from SY_PE_TMP_PDFReportPic where TJH000 = paraTJH000 and BMBH00 = paraBMBH00
      and KSLX00 = paraKSLX00 and TPBT00 = paraTPBT00;
    insert into SY_PE_TMP_PDFReportPic(Tjh000, Bmbh00, Kslx00, SFCS00, Tpbt00, Tp0000, Tpfx00)
    values(paraTJH000, paraBMBH00, paraKSLX00, paraSFCS00, paraTPBT00,paraTP0000,paraTPFX00) ;--return Tp0000 into b_lob;
    commit;
  end;

  procedure queryPicForFrx(paraTJH000 in number,
                           paraBMBH00 in number,
                           paraOPFLAG in varchar2,
                           cur_Image out ref_cur,
                           cur_CSXM00 out ref_cur,
                           cur_CSJCXM out ref_cur)is
  begin
      --图片
    open cur_Image for
      select * from SY_PE_TMP_PDFREPORTPIC where tjh000 = paraTJH000 and (paraBMBH00 is null or BMBH00 = paraBMBH00);
    if paraOPFLAG = '0' then
      --超声项目
      open cur_CSXM00 for
        select a.TJH000, A.XM0000, to_char(to_date(A.DJRQ00,'yyyy-mm-dd'),'yyyy-mm-dd') DJRQSJ,
            A.XBMC00, A.NL0000, (select ZWXM00 from BM_YGBM00 where YGBH00 = B.KDYSID) KDYSXM,
            A.BMBH00, A.BMMC00, A.KSXJ00, A.SHRQGG, A.YSXM00 KSYSXM, A.YSXM00, A.KSRQGG,
            SF_PE_GetSQXMMC(a.TJH000, a.bmbh00) SQXMMC,
        (select QM0000 from BM_YGBM00 where YGBH00 = A.KSYS00) QM0000
          from VW_PE_TJFKJC a, SY_PE_YWSJ_TJZ000 B
         where A.TJH000 = B.TJH000
           and A.TJH000 = paraTJH000
           and a.BMBH00 = paraBMBH00;

        --超声检查项目
      open cur_CSJCXM for
        select a.* from VW_PE_JCXMBG a where TJH000 = paraTJH000
             and a.BMBH00 = paraBMBH00 and TZCMS0 is not null order by SQXMXH,JCXMXH;
    end if;
  end;

  --查询检查项目图像
  procedure queryExamItemPicture(paraTJH000 in number,
    paraJCKSID in number,
    cur_ExamItemPicture out ref_cur,
    cur_ExamArea out ref_cur)
  is
  VSFSH00 number;
  VYYID00 SY_PE_YWSJ_TJZ000.YYID00%type;
  VQFYTXM XT_XTCS00.VALUE0%type;
  begin
    select YYID00 into VYYID00 from SY_PE_YWSJ_TJZ000 where TJH000 = paraTJH000;
    select sf_pe_getxtcs('PE_FKXTMS', '0', VYYID00) into VQFYTXM from dual;
    if VQFYTXM = '1' then
      --是否有图项目
      select decode(count(1), 0, 1, 0) into VSFSH00
         from SY_PE_YWSJ_TJYW00 A, SY_PE_YWZD_TJSQXM B where A.SQXMID = B.SQXMID
         and A.TJH000 = paraTJH000 and A.JCKSID = paraJCKSID and (nvl(BBLXID,0) <> 0);
    else
      VSFSH00 := 0;
    end if;
    if VSFSH00 = 0 then
      select decode(count(1), 0, 0, 1) into VSFSH00
             from SY_PE_YWSJ_TJFKJC where TJH000 = paraTJH000 and BMBH00 = paraJCKSID and KSSH00 = '1';
    end if;
    open cur_ExamArea for
      select distinct BBLXID, BBLXMC, YXLX00 from SY_PE_YWSJ_TJYW00 A, VW_PE_SQXM00 B
          where A.TJH000 = paraTJH000 and JCKSID = paraJCKSID and A.SQXMID = B.SQXMID and SFJCXM = '1'
          and  BBLXMC is not null;

     open cur_ExamItemPicture for
       select TJH000, BMBH00, JCBW00, BDY000, KSLX00, CTRQ00, TPBT00, VSFSH00 SFSH00, SFYWID, KSLX00 YXLX00
          from VW_PE_JCXMTX where TJH000 = paraTJH000 and BMBH00 = paraJCKSID;
  end;

  --检查项目图像操作
  function examItemPictureOperate(paraOPFLAG in number,
    paraTJH000 in number,
    paraBMBH00 in number,
    paraKSLX00 in varchar2,
    paraJCBW00 in varchar2,
    paraBDY000 in varchar2,
    paraTPBT00 in varchar2,
    paraCZY000 in number)return varchar2
  is
  VKSLX00 BM_BMBM00.KSLX00%type;
  VTPXDLJ SY_PE_YWSJ_JCXMTX.TPXDLJ%type;
  VSFYWID SY_PE_YWSJ_JCXMTX.SFYWID%type;
  VJCID00 SY_PE_YWSJ_JCXMTX.JCID00%type;
  VTPBT00 SY_PE_YWSJ_JCXMTX.TPBT00%type;
  VJCBW00 SY_PE_YWSJ_JCXMTX.JCBW00%type;
  begin
    if paraBMBH00 is not null and paraKSLX00 is null then
      select YXLX00 into VKSLX00 from BM_BMBM00 where BMBH00 = paraBMBH00;
    else
      VKSLX00 := paraKSLX00;
    end if;
    VTPBT00 := upper(paraTPBT00);
    case paraOPFLAG
      when 0 then
        select nvl(max(SFYWID),0), nvl(max(JCID00),0) into VSFYWID, VJCID00
               from SY_PE_YWSJ_TJJCXM where TJH000=paraTJH000 and BMBH00=paraBMBH00;
        VTPXDLJ := '\'|| VKSLX00 ||'\'||to_char(sysdate, 'YYYYMMDD')||'\'||paraTJH000||'\'||VTPBT00;
        insert into SY_PE_YWSJ_JCXMTX(XMTXID, TJH000, SFYWID, JCID00, TPBT00, BDY000, TPXDLJ, KSLX00, CTRQ00, BMBH00)
          select SQ_PE_YWSJ_JCXMTX_XMTXID.NextVal, paraTJH000, VSFYWID, VJCID00, VTPBT00, '0', VTPXDLJ,
           VKSLX00, to_char(sysdate, 'YYYYMMDD'), paraBMBH00 from dual
           where not exists(select 1 from VW_PE_JCXMTX where TJH000 = paraTJH000
                 and upper(TPBT00) =  VTPBT00 and KSLX00 = VKSLX00);
      when 1 then
        if paraJCBW00 is null then
          select max(BBLXID) into VJCBW00 from SY_PE_YWSJ_TJYW00 A, VW_PE_SQXM00 B --BBLXMC
              where A.TJH000 = paraTJH000 and JCKSID = paraBMBH00 and A.SQXMID = B.SQXMID
              and SFJCXM = '1' and  BBLXMC is not null;
        else
          VJCBW00 := paraJCBW00;
        end if;
        update SY_PE_YWSJ_JCXMTX set JCBW00 = VJCBW00, BDY000 = paraBDY000
          where TJH000 = paraTJH000 and KSLX00 = VKSLX00 and upper(TPBT00) = VTPBT00;
        insert into SY_PE_YWSJ_TXDYLS(LSID00, TJH000, BMBH00, YXLX00, TPBT00, SFDY00, CZY000)
           select SQ_PE_YWSJ_TXDYLS_LSID00.Nextval, paraTJH000, paraBMBH00, VKSLX00, VTPBT00,
           paraBDY000, paraCZY000 from dual;
      when 2 then
        delete from SY_PE_YWSJ_JCXMTX where TJH000 = paraTJH000 and KSLX00 = VKSLX00 and upper(TPBT00) = VTPBT00;
      end case;

      commit;
      return 'succeed';
  end;

  procedure queryPicFromOtherSysTable(paraTJH000 in number,
                                      paraDSFBM0 in varchar2, --此处代表 第三方表名
                                      paraTJLM00 in varchar2,  --此处代表 第三方表中的搜索条件列
                                      paraTPLM00 in varchar2,
                                      cur_Pic    out ref_cur)is
    aSqlStr varchar2(300);
  begin
    aSqlStr:= 'select  ''1.jpg'' TPBT00, '||paraTPLM00|| ' TP0000 '||
              ' from '||paraDSFBM0||' where '||paraTJLM00||' = '||paraTJH000;
/*    aSqlStr:= 'insert into SY_PE_TMP_PDFReportPic(Tjh000, Bmbh00, Kslx00, SFCS00, Tpbt00, Tp0000)'||
              ' select '||paraTJH000||','||paraBMBH00||','''||paraKSLX00||''',0,''1.jpg'','||paraTPLM00||
              ' from '||paraDSFBM0||' where '||paraTJLM00||' = '||paraTJH000;*/
    open cur_Pic for aSqlStr;
    commit;
  end;

  --复制科室图片
  procedure CopyDeptPicture(paraTJH000 in number,
    paraTOTJH0 in number,
    paraYKSID0 in number,
    paraTOKSID in number,
    paraTPBT00 in varchar2,
    paraTSXX00 out varchar2,
    cur_ExamItemPicture out ref_cur,
    cur_PictureServer out ref_cur)
  is
  VSFYWID SY_PE_YWSJ_TJJCXM.SFYWID%type;
  VJCID00 SY_PE_YWSJ_TJJCXM.JCID00%type;
  VYXLX00 BM_BMBM00.YXLX00%type;
  VTPBT00 varchar2(4000);
  begin
    begin
    select SFYWID, JCID00 into VSFYWID, VJCID00
           from SY_PE_YWSJ_TJJCXM where TJH000 = paraTOTJH0 and BMBH00 = paraTOKSID and rownum = 1;
     exception
       when NO_DATA_FOUND then
         paraTSXX00 := '目标科室还未开始检查，复制失败！';
         return;
       end;
     select YXLX00 into VYXLX00 from BM_BMBM00 where BMBH00 = paraTOKSID;
     if paraTPBT00 is not null then
       VTPBT00 := ','||upper(paraTPBT00)||',';
     end if;
     insert into SY_PE_YWSJ_JCXMTX(XMTXID, TJH000, SFYWID, JCID00, TPBT00, BDY000, TPXDLJ, KSLX00, CTRQ00, BMBH00)
              select SQ_PE_YWSJ_JCXMTX_XMTXID.NextVal, paraTOTJH0, VSFYWID, VJCID00, TPBT00, '0',
              '\'|| VYXLX00 ||'\'||to_char(sysdate, 'YYYYMMDD')||'\'||paraTOTJH0||'\'||TPBT00,
               VYXLX00, to_char(sysdate, 'YYYYMMDD'), paraTOKSID from SY_PE_YWSJ_JCXMTX A
               where  BMBH00 = paraYKSID0 and TJH000 = paraTJH000
               and instr(VTPBT00, ','||TPBT00||',') > 0
               and not exists(select 1 from VW_PE_JCXMTX where TJH000 = paraTOTJH0
                     and TPBT00 = A.TPBT00 and KSLX00 = VYXLX00);
     if sql%notfound then
       paraTSXX00 := '原科室无图片， 复制失败！';
       return;
     end if;
     commit;

    select YXLX00 into VYXLX00 from BM_BMBM00 where BMBH00 = paraYKSID0;
     open cur_ExamItemPicture for
       with VW_TX as
       (select TJH000, BMBH00, JCBW00, BDY000, KSLX00, CTRQ00, TPBT00, SFYWID, KSLX00 YXLX00
          from VW_PE_JCXMTX where TJH000 = paraTOTJH0 and BMBH00 = paraTOKSID)
       select TJH000, BMBH00, JCBW00, BDY000, KSLX00, CTRQ00, TPBT00, SFYWID, KSLX00 YXLX00 from VW_TX
       union all
       select paraTJH000 TJH000, paraYKSID0 BMBH00, JCBW00, BDY000, VYXLX00 KSLX00, CTRQ00, TPBT00, SFYWID, VYXLX00 YXLX00 from VW_TX;

     open cur_PictureServer for
        select BMBH00, FTPURL, FTPYHM, FTPMM0, (select YXLX00 from BM_BMBM00 where BMBH00 = A.BMBH00) YXLX00
        from SY_PE_YWZD_YXPZB0 A where BMBH00 in(paraTOKSID,paraYKSID0);
  end;

  --查询体检者影像科室信息
  procedure QueryPersonDeptInfo(paraTJH000 in number,
    paraCZY000 in number,
    cur_PersonDeptInfo out ref_cur)is
  begin
    open cur_PersonDeptInfo for
        select BMBH00, FTPURL, FTPYHM, FTPMM0, (select YXLX00 from BM_BMBM00 where BMBH00 = A.BMBH00) YXLX00
          from SY_PE_YWZD_YXPZB0 A where exists(select 1 from SY_PE_YWSJ_TJFKJC
               where BMBH00 = A.BMBH00 and TJH000 = paraTJH000)
          and BMBH00 in (select BMBH00 from XT_YHBMQX where YGBH00 = paraCZY000);
  end;

  --删除接口图片
  function DeleteInterfacePicture(paraTJH000 in number,
    paraBMBH00 in number,
    paraTPLJ00 in varchar2) return varchar2 is
  begin
    update SY_PE_YWSJ_SFXMJG set TPLJ00 = replace(TPLJ00, paraTPLJ00||';', '')
           where TJH000 = paraTJH000 and BMBH00 = paraBMBH00 and instr(TPLJ00, paraTPLJ00||';')>0;
    if sql%notfound then
      update SY_PE_YWSJ_SFXMJG set TPLJ00 = replace(TPLJ00, paraTPLJ00, '')
             where TJH000 = paraTJH000 and BMBH00 = paraBMBH00 and instr(TPLJ00, paraTPLJ00)>0;
    end if;
    commit;
    return 'succeed';
  end;

end PEIS_ReportPictureManage;

调用：
dao

void examItemPictureOperate(Map<String, Object> params);

mybastis的xml
PEIS_ReportPictureManage.examItemPictureOperate这是个function

<parameterMap id="examItemPictureOperate_paramMap" type="java.util.HashMap">
        <parameter property="result" mode="OUT" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <parameter property="paraOPFLAG" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <parameter property="paraTJH000" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <parameter property="paraBMBH00" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <parameter property="paraKSLX00" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <parameter property="paraJCBW00" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <parameter property="paraBDY000" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <parameter property="paraTPBT00" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <parameter property="paraCZY000" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>

    </parameterMap>
    <select id="examItemPictureOperate" parameterMap="examItemPictureOperate_paramMap" resultType="java.util.Map" statementType="CALLABLE">
		 <![CDATA[
             {?=call PEIS_ReportPictureManage.examItemPictureOperate(?,?,?,?,?,?,?,?)}
        ]]>
	</select>

类似地这是个procedure

 <parameterMap id="getReportForFrx_paramMap" type="java.util.HashMap">
        <parameter property="paraTJH000" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <parameter property="paraYYID00" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <parameter property="curSQXM" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curTJZ000" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curTJZZYS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curZYBS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curJWBS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curJWSJ" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curSYS_SFYY" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curHYS_SFYH" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curJZBS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curWJZBS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curJWHBS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curJZQTBS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curTJZYJS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curFFSZYS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curFSZYS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curTJZSHS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curTJZHYS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curTJZSYS" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curJKCF" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curZZBG" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curFKJC00" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curZJBGD" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curYXH" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curYXMSZD" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curJYK000" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curJYKJCXM" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curJYFKJC" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curZJ0000" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curXTZZ" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curZXZJTJ" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curJZZJTJ" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curXTZJ" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curJBFL" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curZYSQXM" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curZYJC" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curZYZSJG" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curDCJG" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curJCXMBD" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curTPBG00" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
        <parameter property="curTWBGT0" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="commonMap"/>
    </parameterMap>
    <select id="getReportForFrx" parameterMap="getReportForFrx_paramMap" resultType="java.util.Map" statementType="CALLABLE">
		<![CDATA[
              call PEIS_PUBLIC_PRINT.queryReportForFrx(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        ]]>
	</select>

比较特殊的存储过程 table of类型的：
在package中定义的

type arrTTSFID is table of SY_PE_YWSJ_TTFZXM.TTSFID%type index by binary_integer;

procedure OperGroupingItem(paraFlag in number, --标志 0：新增  1 修改 2：删除 3清除 4  加入套餐
                                                         paraTTSFID in out arrTTSFID ,--分组项目ID
                                                         paraRWFZID in number ,--任务分组ID
                                                         paraTCID00 in number, --套餐ID
                                                         paraKL0000 in number, --扣率
                                                         paraSQXMID in number ,--申请项目ID
                                                         paraHospitalID in varchar2, --医院ID
                                                         paraRETURN out varchar2, --返回标志 success 成功  failure 失败
                                                         paraRTNMSG out varchar2 --返回信息
                                                        ) is
 vZXCGBZ number;
 vTSXX00 varchar2(400);
 vXXXX00 varchar2(400);
 idx     number;
 ECUSTOM  exception;
 begin
    paraRETURN:='failure';
    if   (paraFlag =0) or (paraFlag =1) or (paraFlag =2)  then
      for idx in 1..paraTTSFID.count loop
        SP_PE_YWSJ_TTFZXM_EDIT(POPFLAG=>paraFlag,PTTSFID=>paraTTSFID(idx),PRWFZID=>paraRWFZID,PTCID00=>paraTCID00,
                                                       PKL0000=>paraKL0000,PSQXMID=>paraSQXMID,PYYID00=>paraHospitalID,PCOMMIT=>'N',
                                                       PZXCGBZ=>vZXCGBZ,PTSXX00=>vTSXX00,PXXXX00=>vXXXX00);
          if vZXCGBZ=0 then
             paraRTNMSG:=vTSXX00;
             raise ECUSTOM;
          end if;
       end loop;
    elsif paraFlag=3 then
        delete from SY_PE_YWSJ_TTFZXM where RWFZID =paraRWFZID;
    elsif paraFlag=4 then
       SP_PE_JRTCCZ(PFLAG00=>1,PTCID00=>paraTCID00,PKL0000=>paraKL0000,PID0000=>paraRWFZID,PSFGXXE=>'0',PDJBDBZ=>'1',
                                  PCZY000=>9999,PCZYKS0=>20,PDDH000=>'',PCOMMIT=>'N',PZXCGBZ=>vZXCGBZ,
                                  PTSXX00=>vTSXX00,PXXXX00=>vXXXX00);
       if vZXCGBZ=0 then
          paraRTNMSG:=vTSXX00;
          raise ECUSTOM;
       end if;
    end if;
    if  sf_pe_getxtcs('PE_TSXESFSSGX','Y')<>'N' then
              update SY_PE_YWSJ_TTRWFZ A set TSXE00=(select sum(ZHJG00) from SY_PE_YWSJ_TTFZXM where RWFZID=A.Rwfzid)
              where A.RWFZID=paraRWFZID and FBBH00 in ('2','4');
     end if;
    commit;
    paraRETURN:='success';
    paraRTNMSG:='';
 exception
   when ECUSTOM then
     rollback;
   when others then
     rollback;
     paraRTNMSG:=substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240);
 end;

调用

package com.thinkgem.jeesite.modules.group.dao;

import com.thinkgem.jeesite.common.utils.SpringContextHolder;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTypes;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;

/**
 * 调用存储过程 参数类型 包含table of ...类型的
 */
@Repository("OperGroupingItemDao")
public class OperGroupingItemDao extends JdbcDaoSupport {
    @Autowired
    public OperGroupingItemDao() {
        javax.sql.DataSource dataSource = SpringContextHolder.getBean("dataSourceXmsq");
        setDataSource(dataSource);
    }

    public void operGroupingItem(Map<String, Object> params) throws SQLException{
        Connection conn=super.getConnection();
        OracleCallableStatement cs = null;
        try {
            oracle.jdbc.OracleConnection con = null;
            if (conn.isWrapperFor(OracleConnection.class)) {
                con = conn.unwrap(OracleConnection.class);
            }

            int[] arrTTSFID=(int[]) params.get("paraTTSFID");
            cs = (OracleCallableStatement) con.prepareCall("begin PEIS_AssignmentReserve.OperGroupingItem(?,?,?,?,?,?,?,?,?); end;");
            System.out.println((String) params.get("paraOPFLAG"));
            cs.setString(1, (String) params.get("paraOPFLAG"));
            if (null != arrTTSFID && arrTTSFID.length > 0) {
                cs.setPlsqlIndexTable(2, arrTTSFID, arrTTSFID.length, arrTTSFID.length, OracleTypes.NUMBER, 0);

            }
            cs.setString(3, (String) params.get("paraRWFZID"));
            cs.setString(4, params.get("paraTCID00")==null?"":(String) params.get("paraTCID00"));
            cs.setString(5, params.get("paraKL0000")==null?"0":(String) params.get("paraKL0000"));
            cs.setString(6, params.get("paraSQXMID")==null?"":(String) params.get("paraSQXMID"));
            cs.setString(7, (String) params.get("paraYYID00"));

            cs.registerOutParameter(8, OracleTypes.VARCHAR);
            cs.registerOutParameter(9, OracleTypes.VARCHAR);
            cs.registerIndexTableOutParameter (2,arrTTSFID.length,OracleTypes.NUMBER,0);
            cs.execute();
            params.put("returnTTSFID",cs.getPlsqlIndexTable(2));
        }finally{
            if(null != conn){
                conn.close();
            }
            if(null != cs){
            	cs.close();
            }
        }
    }
}