记录一个简单的存储过程

清疚 2022-01-13 12:43 295阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

CREATE OR REPLACE PACKAGE WEB0012C IS TYPE V_CUR IS REF CURSOR;
    -------------------------------------------------------------------------------
    
    -- DECLARATION     ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    -------------------------------------------------------------------------------
    
    PROCEDURE P_SREFER(
    
       P_COMPANY                IN   NISCO.EP_WWJGGX.COMPANY%TYPE                 DEFAULT NULL,
       P_PROD                   IN   NISCO.EP_WWJGGX.PROD%TYPE                    DEFAULT NULL,
    
    
        P_CUR                OUT  V_CUR);
    
    PROCEDURE P_SMODIFY(
    
        iTYPE                    IN   VARCHAR2,
        P_COMPANY                IN   COMPANY%TYPE                           DEFAULT NULL,
        P_PROD                   IN   EP_WWJGGX.PROD%TYPE                    DEFAULT NULL,
        P_CAPA                   IN   EP_WWJGGX.CAPA%TYPE                    DEFAULT NULL,
        P_EPERSON                IN   EP_WWJGGX.EPERSON%TYPE                 DEFAULT NULL,
    
        P_E_CODE             OUT  NUMBER,
        P_E_MSG              OUT  VARCHAR2);
    
    PROCEDURE P_SONEROW(
    
        P_COMPANY                IN   EP_WWJGGX.COMPANY%TYPE                 DEFAULT NULL,
        P_PROD                   IN   EP_WWJGGX.PROD%TYPE                    DEFAULT NULL,
    
        P_CUR                OUT  V_CUR);
    
    END WEB0012C;

CREATE OR REPLACE PACKAGE BODY WEB0012C IS
    
    PROCEDURE P_SREFER(
    
        P_COMPANY                IN   EP_WWJGGX.COMPANY%TYPE                 DEFAULT NULL,
        P_PROD                   IN   EP_WWJGGX.PROD%TYPE                    DEFAULT NULL,
    
        P_CUR                OUT  V_CUR) IS
    
    BEGIN
    
        OPEN P_CUR FOR
             SELECT  COMPANY
                    ,PROD
                    ,CAPA
                    ,EPERSON
                    ,EDATA
                    ,ETIME
               FROM  EP_WWJGGX
              WHERE  COMPANY LIKE P_COMPANY ||'%'
                AND  PROD LIKE P_PROD ||'%';
    
    END P_SREFER;
    
    --------------------------------------------------------------------------------------------
    
    PROCEDURE P_SMODIFY(
    
        iTYPE                    IN   VARCHAR2,
        P_COMPANY                IN   WWJGGX.COMPANY%TYPE                    DEFAULT NULL,
        P_PROD                   IN   EP_WWJGGX.PROD%TYPE                    DEFAULT NULL,
        P_CAPA                   IN   EP_WWJGGX.CAPA%TYPE                    DEFAULT NULL,
        P_EPERSON                IN   EP_WWJGGX.EPERSON%TYPE                 DEFAULT NULL,
      
        P_E_CODE             OUT  NUMBER,
        P_E_MSG              OUT  VARCHAR2) IS
    
        V_CNT                NUMBER;
        MISTAKE_VALUE        EXCEPTION;
    
    BEGIN
    
        P_E_CODE :=0;
    
        IF iTYPE = 'I' THEN
           GOTO P_INSERT;
        ELSIF iTYPE = 'U' THEN
           GOTO P_UPDATE;
        ELSIF iTYPE = 'D' THEN
           GOTO P_DELETE;
        END IF;
    
    <<P_INSERT>>
    
      INSERT INTO EP_WWJGGX(
                     COMPANY
                    ,PROD
                    ,CAPA
                    ,EPERSON
                    ,EDATA
                    ,ETIME
    
      )
      VALUES(
               P_COMPANY  
             , P_PROD 
             , P_CAPA
             , P_EPERSON 
             , TO_CHAR(SYSDATE,'YYYYMMDD')
             , TO_CHAR(SYSDATE,'HH24MISS')
    
      );
    
      RETURN;
    
    
    <<P_UPDATE>>
    
      UPDATE EP_WWJGGX SET
    
               CAPA             =  P_CAPA 
              ,EPERSON          =  P_EPERSON 
              ,EDATA            = TO_CHAR(SYSDATE,'YYYYMMDD')
              ,ETIME            = TO_CHAR(SYSDATE,'HH24MISS')
    
      WHERE COMPANY = P_COMPANY
        AND PROD    = P_PROD;
    
      IF SQL%ROWCOUNT = 0 THEN
         RAISE NO_DATA_FOUND;
      END IF;
    
      RETURN;
    
    
    <<P_DELETE>>
    
      DELETE FROM EP_WWJGGX
       WHERE COMPANY = P_COMPANY
        AND PROD     = P_PROD;
    
      IF SQL%ROWCOUNT = 0 THEN
         RAISE NO_DATA_FOUND;
      END IF;
    
      RETURN;
    
      EXCEPTION
        WHEN MISTAKE_VALUE THEN
          P_E_CODE := 1;
          RETURN;
        WHEN NO_DATA_FOUND THEN
          P_E_CODE := 1;
          P_E_MSG  := '没找到数据';
          RETURN;
        WHEN DUP_VAL_ON_INDEX THEN
          P_E_CODE := 1;
          P_E_MSG  := '索引重复';
        RETURN;
    
      END P_SMODIFY;
    
    -----------------------------------------------------------------------------------------------
    
    PROCEDURE P_SONEROW(
    
        P_COMPANY                IN   EP_WWJGGX.COMPANY%TYPE                 DEFAULT NULL,
        P_PROD                   IN   EP_WWJGGX.PROD%TYPE                    DEFAULT NULL,
    
        P_CUR                OUT  V_CUR ) IS
    
    BEGIN
    
        OPEN P_CUR FOR
            SELECT   COMPANY
                    ,PROD
                    ,CAPA
                    ,EPERSON
                    ,EDATA
                    ,ETIME
               FROM  EP_WWJGGX
               WHERE COMPANY = P_COMPANY
        AND PROD    = P_PROD;
    
    END P_SONEROW;
    
    ----------------------------------------------------------------------------------------------
    
    END WEB0012C;

转载于:https://my.oschina.net/u/3740271/blog/3031857

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220113/52ad69d71c9c465880851988063f9fb2.png