公司一同事写的存储过程 带游标

CREATE OR REPLACE PROCEDURE sp_ypjg_addcgd(ai_operid   number,
                                           as_frcode   varchar2,
                                           ai_forgid   number) AS
  /*
  功能：下级单位申请的采购计划生成上级的采购汇总单。
  名称：sp_ypjg_cgtj
  参数: ai_operid   操作员
        as_frcode   县区编码
        ai_forgid   管理机构ID即当前操作员所在机构的ID
  创建：XCK
  创建时间：2012-05-24
  */
-- Create table
/*create global temporary table TMP_SQHZD
(
  PRECID NUMBER,
  SQSL   NUMBER,
  SPSL   NUMBER,
  UNIT   VARCHAR2(10)
)
on commit delete rows;*/

v_requoffiid       number;
v_requestid        number;
v_requcode         varchar2(10);
v_k                number;
v_precid           number;
v_mediid           number;
v_sqsl             number;
v_spsl             number;
v_unit             varchar2(10);
v_storesplit       number;
v_currdate         date;

v_cnt              number;

  CURSOR cur_sqxx Is
  select t.precid,t.sqsl,t.spsl,t.unit
   from tmp_sqhzd t;

BEGIN
    v_currdate := sysdate;
   
    --汇总药品于临时表
   insert into tmp_sqhzd(PRECID,Sqsl,spsl,Unit)
    select t3.precid,sum(t2.requnum) as sqsl,sum(t2.spsl),t2.unit
    from request_total t1,request t2,dictmedi t3
    where t1.requestid = t2.requestid
      and t2.mediid = t3.mediid
      and t1.forgid = t2.forgid
      and t1.frcode = t2.frcode
      and t2.forgid = t3.forgid
      and t2.frcode = t3.frcode
      and t1.stage = 1
      and t1.retu = 0
      and t1.forgid in(select gx_forgid from ypjg_gxjg where forgid = ai_forgid)
    group by t3.precid,t2.unit;
   
    select count(*) into v_cnt
    from tmp_sqhzd;
    if v_cnt = 0 then
       raise_application_error(-20001,'没有待汇总的采购信息.');
       return;
    end if;

    --取当前操作员科室
     select officeid into v_requoffiid
     from dictoffice
     where officeid in(select officeid from dictoper where operid = ai_operid and forgid = ai_forgid)
       and forgid = ai_forgid;

     --写主记录表
     select s_request_total.nextval into v_requestid from dual;
     select count(*) into v_cnt from request_total where forgid = ai_forgid;
     if v_cnt = 0 then
        v_requcode := '00000001';
     else
        select trim(to_char(to_number(max(requcode))+1,'00000000'))
          into v_requcode
        from request_total where forgid = ai_forgid and substr(requcode,1,1)<>'F';
     end if;
     insert into  request_total(REQUESTID,
                                REQUCODE,
                                REQUTRANID,
                                REQUDATE,
                                REQUOFFICE,
                                BASEID,
                                REMARK,
                                INPUTOR,
                                STAGE,
                                RETU,
                                RETUOPERID,
                                RETUDATE,
                                FORGID,
                                FRCODE)
                         values(v_requestid,
                                v_requcode,
                                18,--采购申请
                                v_currdate,
                                v_requoffiid,
                                v_requoffiid,
                                '生成采购汇总',
                                ai_operid,
                                1,
                                0,
                                null,
                                null,
                                ai_forgid,
                                as_frcode);
     --循环写明细记录
     v_k := 1;
     OPEN cur_sqxx;
     LOOP
        FETCH cur_sqxx into v_precid,v_sqsl,v_spsl,v_unit;
        EXIT WHEN cur_sqxx%notfound;

        select mediid,store_split into v_mediid,v_storesplit from dictmedi_public
        where forgid = ai_forgid and frcode = as_frcode and precid = v_precid;
         --写明细表
         insert into request( REQUESTID,--1
                              ORDERID,--2
                              MEDIID,--3
                              REQUNUM,--4
                              SPSL,--5
                              UNIT,--6
                              ACTPRI,--7
                              RETPRI,--8
                              PCID,--9
                              PROID,--10
                              CARRID,--11
                              YXQ,--12
                              PH,--13
                              YPDJID,--14
                              ZBID,--15
                              ZBDJ,--16
                              MIN_NUM,--17
                              FORGID,--18
                              FRCODE,--19
                              ISDEL,--20
                              ISDY,--21
                              REMARK)
                      values( v_requestid,--1
                              v_k,--2
                              v_mediid,--3
                              v_sqsl,--4
                              v_spsl,--5
                              v_unit,--6
                              0,--7
                              0,--8
                              null,--9
                              null,--10
                              null,--11
                              null,--12
                              null,null,null,null,
                              v_spsl*v_storesplit,--17
                              ai_forgid,--18
                              as_frcode,--19
                              null,null,null);

        v_k := v_k + 1;

     END LOOP;
    
     --更新受理标识
     update request_total
            set retu = 1,
                retuoperid=ai_operid,
                retudate = v_currdate,
                hz_requestid = v_requestid
     where forgid in(select gx_forgid from ypjg_gxjg where forgid = ai_forgid);

End;

 

<parameterMap id="busGenProcedureFormParam" class="java.util.HashMap">
   <parameter property="OPERID" jdbcType="NUMBER" mode="IN"/>
   <parameter property="FRCODE" jdbcType="varchar" mode="IN"/>
   <parameter property="FORGID" jdbcType="NUMBER" mode="IN"/>
 </parameterMap>
 <procedure id="busGenProcedureForm" parameterMap="busGenProcedureFormParam">
   {call sp_ypjg_addcgd(?,?,?)}
 </procedure>