Oracle中根据条件参数插入自增的(带参数自动获取DPNO)

来源：互联网发布：linux上svn命令编辑：程序博客网时间：2024/05/16 19:38

参数条件是MJCD, KKCD

1. TABLE 存储记录的自增数

CREATE TABLE SASC_TT_SRG_SQNO (    SRGMJCD  VARCHAR2(20) NOT NULL,    SRGKKCD  CHAR(6)  NOT NULL,    SRGSQNO  NUMBER(10,0),    PRIMARY KEY (SRGMJCD, SRGKKCD) )/

2. FUNCTION

CREATE OR REPLACE FUNCTION GET_SASC_TT_SRG_SEQNO(P_MJCD IN VARCHAR, P_KKCD IN CHAR) RETURN INTEGER AS  -- 変数 --  V_SQNO NUMBER(6,0) := 0; -- シーケンス№  CURSOR C_SRG(P_MJCD IN VARCHAR, P_KKCD IN CHAR) IS    SELECT * FROM SASC_TT_SRG_SQNO WHERE SRGMJCD = P_MJCD AND SRGKKCD = P_KKCD;  R_SRG C_SRG%ROWTYPE;  BEGIN-- 初期化V_SQNO := 0;R_SRG  := NULL;  OPEN C_SRG(P_MJCD, P_KKCD);FETCH C_SRG INTO R_SRG;IF C_SRG%FOUND THENV_SQNO := R_SRG.SRGSQNO + 1;    UPDATE SASC_TT_SRG_SQNO SET SRGSQNO = V_SQNO     WHERE SRGMJCD = P_MJCD AND SRGKKCD = P_KKCD;      ELSE    V_SQNO := 1;        INSERT INTO SASC_TT_SRG_SQNO (        SRGMJCD,        SRGKKCD,        SRGSQNO    ) VALUES (        P_MJCD,        P_KKCD,        V_SQNO    );      END IF;  COMMIT;    RETURN V_SQNO;EXCEPTION  WHEN OTHERS THEN  IF C_SRG%ISOPEN THEN    CLOSE C_SRG;  END IF;    RETURN 0;END;/