存储过程Body写法

PACKAGE

CREATE OR REPLACE PACKAGE PV_ITEM_PKG IS
    -------------------------------------------------
    -- REM.1.0 过程名：ITEM_APPLY
    -- REM.1.1 描述：指标申请
    ------------------------------------------------- 
    PROCEDURE ITEM_APPLY_PROC(
       I_ORG_CODE       IN  VARCHAR2,
       I_PERSON_CODE    IN  VARCHAR2,
       I_DEPT_CODE      IN  VARCHAR2,
       I_UP_DEPT_CODE   IN  VARCHAR2,
       I_ITEM_STATE     IN  VARCHAR2,
       I_APPLY_REASON   IN  VARCHAR2,  --表单信息
       I_ITEM_NAME      IN  VARCHAR2,  --指标信息
       I_BASE_TYPE      IN  VARCHAR2,  
       I_ITEM_UNIT      IN  VARCHAR2, 
       I_ITEM_EXPLAIN   IN  VARCHAR2,
       I_ITEM_COM_FLAG  IN  VARCHAR2,
       I_ITEM_TYPE      IN  VARCHAR2,
       I_IS_ALL         IN  VARCHAR2,
      
       O_APPLY_ID       OUT VARCHAR2,
       O_RETURN         OUT VARCHAR2,
       O_MSG            OUT VARCHAR2
    );
    -------------------------------------------------
    -- REM.1.0 过程名：ITEM_AUDIT
    -- REM.1.1 描述：指标审核
    ------------------------------------------------- 
    PROCEDURE ITEM_AUDIT_PROC(
       I_APPLY_ID       IN  VARCHAR2,
       I_ORG_CODE       IN  VARCHAR2,
       I_PERSON_CODE    IN  VARCHAR2,
       I_DEPT_CODE      IN  VARCHAR2,  --表单信息
       I_AUDIT_STATE    IN  VARCHAR2,  --审核信息
       I_REMARK         IN  VARCHAR2,
      
       O_RETURN         OUT VARCHAR2,
       O_MSG            OUT VARCHAR2
    );
  
END PV_ITEM_PKG;

PACKAGE BODY

CREATE OR REPLACE PACKAGE BODY PV_ITEM_PKG IS

TYPE DATA_CUR IS REF CURSOR;

-------------------------------------------------
-- REM.1.0 过程名：ITEM_APPLY
-- REM.1.1 描述：指标申请
-------------------------------------------------
PROCEDURE ITEM_APPLY_PROC(
     I_ORG_CODE       IN  VARCHAR2,
     I_PERSON_CODE    IN  VARCHAR2,
     I_DEPT_CODE      IN  VARCHAR2,
     I_UP_DEPT_CODE   IN  VARCHAR2,
     I_ITEM_STATE     IN  VARCHAR2,
     I_APPLY_REASON   IN  VARCHAR2,  --表单信息
     I_ITEM_NAME      IN  VARCHAR2,  --指标信息
     I_BASE_TYPE      IN  VARCHAR2,  
     I_ITEM_UNIT      IN  VARCHAR2,     
     I_ITEM_EXPLAIN   IN  VARCHAR2,
     I_ITEM_COM_FLAG  IN  VARCHAR2,
     I_ITEM_TYPE      IN  VARCHAR2,
     I_IS_ALL         IN  VARCHAR2,
     O_APPLY_ID       OUT VARCHAR2,
     O_RETURN         OUT VARCHAR2,
     O_MSG            OUT VARCHAR2
)
AS
     G_APPLY_ID      VARCHAR2(20);
     G_APPLY_NAME    VARCHAR2(50);
     G_ORG_CODE      VARCHAR2(8);
     G_PERSON_CODE   VARCHAR2(8);
     G_DEPT_CODE     VARCHAR2(20);
     G_UP_DEPT_CODE  VARCHAR2(20);
     G_ITEM_STATE    VARCHAR2(2);
     G_APPLY_REASON  VARCHAR2(200);
     ----------------
     G_DETAIL_ID     VARCHAR2(20);
     G_ITEM_NAME     VARCHAR2(80);
     G_BASE_TYPE     VARCHAR2(2);
     G_ITEM_UNIT     VARCHAR2(20);
     G_ITEM_EXPLAIN  VARCHAR2(200);
     G_ITEM_COM_FLAG VARCHAR2(2);
     G_ITEM_TYPE     VARCHAR2(2);
     G_IS_ALL        VARCHAR2(1);
 
BEGIN
     G_ORG_CODE := I_ORG_CODE;
     G_PERSON_CODE := I_PERSON_CODE;
     G_DEPT_CODE := I_DEPT_CODE;
     G_UP_DEPT_CODE := I_UP_DEPT_CODE;
     G_ITEM_STATE := I_ITEM_STATE;
     G_APPLY_REASON := I_APPLY_REASON;
     G_ITEM_NAME := I_ITEM_NAME;
     G_BASE_TYPE := I_BASE_TYPE;
     G_ITEM_UNIT := I_ITEM_UNIT;
     G_ITEM_EXPLAIN := I_ITEM_EXPLAIN;
     G_ITEM_COM_FLAG := I_ITEM_COM_FLAG;
     G_ITEM_TYPE := I_ITEM_TYPE;
     G_IS_ALL := I_IS_ALL;
     
     G_APPLY_NAME := I_ITEM_NAME||G_ORG_CODE||'申请表';
     
     --1.取得 APPLY_ID
     SELECT SEQ_APPLY_ID.NEXTVAL INTO G_APPLY_ID FROM DUAL;
     --2.取得 APPLY_DETAIL_ID
     SELECT SEQ_APPLY_DETAIL_ID.NEXTVAL INTO G_DETAIL_ID FROM DUAL;
     
     --1.插入 PV_ITEM_APPLY
     INSERT INTO PV_ITEM_APPLY(
            APPLY_ID,APPLY_NAME,PERSON_CODE,DEPT_CODE,ORG_CODE,UP_DEPT_CODE,
            ITEM_STATE,APPLY_DATE,APPLY_REASON,AUDIT_STATE
     )
     VALUES(
            G_APPLY_ID,G_APPLY_NAME,G_PERSON_CODE,G_DEPT_CODE,G_ORG_CODE,
            G_UP_DEPT_CODE,G_ITEM_STATE,SYSDATE,G_APPLY_REASON,'0'
     );
     --2.插入 PV_ITEM_APPLY_DETAIL
     INSERT INTO PV_ITEM_APPLY_DETAIL(
            APPLY_ID,DETAIL_ID,ITEM_NAME,BASE_TYPE,ITEM_UNIT,
            ITEM_EXPLAIN,ITEM_COM_FLAG,ITEM_TYPE,IS_ALL
     )
     VALUES(
            G_APPLY_ID,G_DETAIL_ID,G_ITEM_NAME,G_BASE_TYPE,G_ITEM_UNIT,
            G_ITEM_EXPLAIN,G_ITEM_COM_FLAG,G_ITEM_TYPE,G_IS_ALL
     );
     COMMIT;
     O_RETURN := '1';
     O_APPLY_ID := G_APPLY_ID;
     O_MSG    := '指标申请保存成功!';
     EXCEPTION WHEN OTHERS THEN
     O_RETURN := '-1';
     O_MSG    := '程序异常,指标申请保存失败,请联系信息管理员!';
     ROLLBACK;
     
END ITEM_APPLY_PROC;

-------------------------------------------------
-- REM.1.0 过程名：ITEM_AUDIT
-- REM.1.1 描述：指标申请
-------------------------------------------------
PROCEDURE ITEM_AUDIT_PROC(
     I_APPLY_ID       IN  VARCHAR2,
     I_ORG_CODE       IN  VARCHAR2,
     I_PERSON_CODE    IN  VARCHAR2,
     I_DEPT_CODE      IN  VARCHAR2,  --表单信息
     I_AUDIT_STATE    IN  VARCHAR2,  --审核信息
     I_REMARK         IN  VARCHAR2,
     O_RETURN         OUT VARCHAR2,
     O_MSG            OUT VARCHAR2
)
AS
     G_APPLY_ID      VARCHAR2(20);
     G_ORG_CODE      VARCHAR2(8);
     G_PERSON_CODE   VARCHAR2(8);
     G_DEPT_CODE     VARCHAR2(20);
     G_AUDIT_STATE   VARCHAR2(2);
     G_REMARK        VARCHAR2(200);
     G_ITEM_CODE     VARCHAR2(20);
     G_AP_ORG_CODE   VARCHAR2(20);
     G_AP_DEPT_CODE  VARCHAR2(20); 
     G_AU_ORG_CODE   VARCHAR2(20);
     G_AU_DEPT_CODE  VARCHAR2(20);   
     G_REMARK_ID     VARCHAR2(20);
     DETAIL_CUR      DATA_CUR;
     G_ITEM_NAME     VARCHAR2(50);
     G_BASE_TYPE     VARCHAR2(2);
     G_ITEM_UNIT     VARCHAR2(20);
     G_ITEM_EXPLAIN  VARCHAR2(200);
     G_ITEM_COM_FLAG VARCHAR2(2);
     G_ITEM_TYPE     VARCHAR2(2);
     G_IS_ALL        VARCHAR2(1);
BEGIN
     G_APPLY_ID := I_APPLY_ID;
     G_ORG_CODE := I_ORG_CODE;
     G_PERSON_CODE := I_PERSON_CODE;
     G_DEPT_CODE := I_DEPT_CODE;
     G_AUDIT_STATE := I_AUDIT_STATE;
     G_REMARK := I_REMARK;
     
     --1.审批信息
     UPDATE PV_ITEM_APPLY 
     SET AUDIT_PERSON_CODE=G_PERSON_CODE,AUDIT_ORG_CODE=G_ORG_CODE,AUDIT_DEPT_CODE=G_DEPT_CODE,
         AUDIT_STATE=G_AUDIT_STATE,AUDIT_DATE=SYSDATE,REMARK=G_REMARK,EXECUTION_ID=''
     WHERE APPLY_ID=G_APPLY_ID;
     
     --2.取得申请人及审核人信息
     SELECT ORG_CODE,DEPT_CODE,AUDIT_ORG_CODE,AUDIT_DEPT_CODE INTO G_AP_ORG_CODE,G_AP_DEPT_CODE,G_AU_ORG_CODE,G_AU_DEPT_CODE 
     FROM PV_ITEM_APPLY WHERE APPLY_ID=G_APPLY_ID;
     
     --3.审批通过,建立新指标
     IF G_AUDIT_STATE=1 THEN      
         OPEN DETAIL_CUR FOR
              SELECT ITEM_NAME,BASE_TYPE,ITEM_UNIT,ITEM_EXPLAIN,ITEM_COM_FLAG,ITEM_TYPE,IS_ALL
              FROM PV_ITEM_APPLY_DETAIL WHERE APPLY_ID=G_APPLY_ID;
         LOOP
              FETCH DETAIL_CUR INTO G_ITEM_NAME,G_BASE_TYPE,G_ITEM_UNIT,G_ITEM_EXPLAIN,G_ITEM_COM_FLAG,G_ITEM_TYPE,G_IS_ALL;
         EXIT WHEN DETAIL_CUR%NOTFOUND;
             --1.审批通过，生成新指标，加入指标中心 (PV_ITEM)
             SELECT SEQ_ITEM_CODE.NEXTVAL INTO G_ITEM_CODE FROM DUAL;
             
             INSERT INTO PV_ITEM(
                 ITEM_CODE,ITEM_NAME,ITEM_EXPLAIN,ITEM_TYPE,ITEM_UNIT,COMPUTE_FLAG,
                 USE_FLAG,BASE_TYPE,IS_ALL,CREATE_CODE,ORG_CODE,DEPT_CODE,CREATE_DATE
             )
             VALUES(
                 G_ITEM_CODE,G_ITEM_NAME,G_ITEM_EXPLAIN,G_ITEM_TYPE,G_ITEM_UNIT,G_ITEM_COM_FLAG,
                 '1',G_BASE_TYPE,G_IS_ALL,G_PERSON_CODE,G_ORG_CODE,G_DEPT_CODE,SYSDATE
             );
             
             --2.申请人和定义的上级单位部门均为指标拥有者
             INSERT INTO PV_ITEM_OWNER(
                 OWNER_ID,ITEM_CODE,DEPT_CODE,ORG_CODE,OPERATE_DATE
             )
             VALUES(
                 SEQ_ITEM_OWNER_ID.NEXTVAL,G_ITEM_CODE,G_AP_DEPT_CODE,G_AP_ORG_CODE,SYSDATE
             );
             INSERT INTO PV_ITEM_OWNER(
                 OWNER_ID,ITEM_CODE,DEPT_CODE,ORG_CODE,OPERATE_DATE
             )
             VALUES(
                 SEQ_ITEM_OWNER_ID.NEXTVAL,G_ITEM_CODE,G_AU_DEPT_CODE,G_AU_ORG_CODE,SYSDATE
             );
         END LOOP;
         
         --3.审批通过,备案 PV_ITEM_REMARK
         SELECT SEQ_REMARK_ID.NEXTVAL INTO G_REMARK_ID FROM DUAL;
         INSERT INTO PV_ITEM_REMARK(REMARK_ID,APPLY_ID,REMARK_DATE) 
         VALUES(G_REMARK_ID,G_APPLY_ID,SYSDATE);
         
     END IF;
     --4.申请指标信息
     UPDATE PV_ITEM_APPLY_DETAIL SET ITEM_CODE=G_ITEM_CODE
     WHERE APPLY_ID=G_APPLY_ID;
     
     COMMIT;
     O_RETURN := '1';
     O_MSG    := '指标审核保存成功!';
     EXCEPTION WHEN OTHERS THEN
     ROLLBACK;
     O_RETURN := '-1';
     O_MSG    := '程序异常,指标审核保存失败,请联系信息管理员!';
     
END ITEM_AUDIT_PROC;

END PV_ITEM_PKG;