日期:2014-05-17  浏览次数:20915 次

关于简单的存储过程
SQL code
create or replace PROCEDURE     SP_ACNT_TAX120B
(
          IN_TYPE                     IN          VARCHAR2,                --????
          IN_ACNT_UNIT_CD             IN          ACNT_TAX120.ACNT_UNIT_CD                       %TYPE,
          IN_ACNT_FG_CD               IN          ACNT_TAX120.ACNT_FG_CD                         %TYPE,
          IN_CAMP_FG_CD               IN          ACNT_TAX120.CAMP_FG_CD                         %TYPE,
          IN_ISSU_DT_FR               IN          ACNT_TAX120.ISSU_DT                            %TYPE,
          IN_ISSU_DT_TO               IN          ACNT_TAX120.ISSU_DT                            %TYPE,
          IN_BUY_SELL_FG_CD           IN          ACNT_TAX120.BUY_SELL_FG_CD                     %TYPE,
          IN_BILL_FG_CD               IN          ACNT_TAX120.BILL_FG_CD                         %TYPE,
          IN_RPRT_SEQ                 IN          ACNT_TAX120.RPRT_SEQ                           %TYPE,
          IN_UPDT_ID                  IN          ACNT_TAX120.UPDT_ID                            %TYPE,
          IN_UPDT_IP                  IN          ACNT_TAX120.UPDT_IP                            %TYPE,


          --  RETURN VALUE
          OUT_CD                      OUT         VARCHAR2,
          OUT_MSG                     OUT         VARCHAR2

)
IS

/****************** ???? ?? ***********************/
--<< ??? ??? ??? ????? >>
  v_value1      CHAR(1) := '1';
  v_value2      CHAR(1) := '2';
  v_value3      CHAR(1) := '3';
  V_CNT         NUMBER  := 0;
/****************** ?????    **********************/

  BEGIN
    /********** ???_1 ?? ?? ***********************/
    IF(IN_TYPE = 'run' ) THEN
        BEGIN
            IF(LENGTH(IN_CAMP_FG_CD) = 1) THEN
            BEGIN
                SELECT IN_CAMP_FG_CD, '9', '9'
                INTO v_value1, v_value2, v_value3
                FROM DUAL;
            END;
            ELSIF(LENGTH(IN_CAMP_FG_CD) = 2) THEN
            BEGIN
                v_value1 := SUBSTR(IN_CAMP_FG_CD, 1, 1);
                v_value2 := SUBSTR(IN_CAMP_FG_CD, 2, 1);
                v_value3 := '9';
            END;
            END IF;


            FOR C1 IN
            (
                SELECT A.BILL_NO, A.BILL_SEQ
                FROM ACNT_TAX120 A, ACNT_DCSN110 B
                WHERE A.CREA_NO=B.CREA_NO
                    AND B.PROG_ST_CD>='30'
                    AND (A.RPRT_YN='N' OR A.RPRT_YN IS NULL)
                    AND A.PAY_YN='Y'
                    AND A.ACNT_UNIT_CD = IN_ACNT_UNIT_CD
                    AND A.ACNT_FG_CD = IN_ACNT_FG_CD
                    AND A.CAMP_FG_CD IN (v_value1, v_value2, v_value3)
                    AND A.ISSU_DT BETWEEN IN_ISSU_DT_FR AND IN_ISSU_DT_TO
                    AND A.BUY_SELL_FG_CD = IN_BUY_SELL_FG_CD
                    AND A.BILL_FG_CD = IN_BILL_FG_CD

                UNION

                SELECT A.BILL_NO, A.BILL_SEQ
                FROM ACNT_TAX120 A
                WHERE A.CREA_NO IS NULL
                    AND (A.RPRT_YN='N' OR A.RPRT_YN IS NULL)
                    AND A.PAY_YN='Y'
                    AND A.ACNT_UNIT_CD = IN_ACNT_UNIT_CD
                    AND A.ACNT_FG_CD = IN_ACNT_FG_CD
                    AND A.CAMP_FG_CD IN (v_value1, v_value2, v_value3)
                    AND A.ISSU_DT BETWEEN IN_ISSU_DT_FR AND IN_ISSU_DT_TO
                    AND A.BUY_SELL_FG_CD = IN_BUY_SELL_FG_CD
                    AND A.BILL_FG_CD = IN_BILL_FG_CD
            )
            LOOP
                BEGIN
                    V_CNT:=V_CNT+1;

                    UPDATE ACNT_TAX120
                    SET RPRT_YN='Y'
                        , RPRT_SEQ=IN_RPRT_SEQ
                        , UPDT_ID=IN_UPDT_ID
                        , UPDT_DT=SYSDATE
                        , UPDT_IP=IN_UPDT_IP
                    WHERE BILL_NO=C1.BILL_N