关于简单的存储过程

Question

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_NO
                        AND BILL_SEQ=C1.BILL_SEQ;                END;
            END LOOP;
        END;
    ELSIF (IN_TYPE = 'rollback' ) THEN
        BEGIN
            SELECT COUNT(*)
            INTO V_CNT
            FROM ACNT_TAX120
            WHERE ACNT_UNIT_CD=IN_ACNT_UNIT_CD
                AND ACNT_FG_CD=IN_ACNT_FG_CD
                AND CAMP_FG_CD IN (v_value1, v_value2, v_value3)
                AND BUY_SELL_FG_CD=IN_BUY_SELL_FG_CD
                AND BILL_FG_CD=IN_BILL_FG_CD
                AND RPRT_YN='Y'
                AND RPRT_SEQ=IN_RPRT_SEQ;
            UPDATE ACNT_TAX120
            SET RPRT_YN='N'
                , RPRT_SEQ=NULL
                , UPDT_ID=IN_UPDT_ID
                , UPDT_DT=SYSDATE
                , UPDT_IP=IN_UPDT_IP
            WHERE ACNT_UNIT_CD=IN_ACNT_UNIT_CD
                AND ACNT_FG_CD=IN_ACNT_FG_CD
                AND CAMP_FG_CD IN (v_value1, v_value2, v_value3)
                AND BUY_SELL_FG_CD=IN_BUY_SELL_FG_CD
                AND BILL_FG_CD=IN_BILL_FG_CD
                AND RPRT_YN='Y'
                AND RPRT_SEQ=IN_RPRT_SEQ;        END;    END IF;    /********* 에러처리 ROLLBACK ************************/    /*EXCEPTION
      WHEN OTHERS THEN
        OUT_RTN     := -1;
        OUT_MSG     := TO_CHAR(SQLCODE)|| ' : ' || SQLERRM;
      RETURN;*/    /******** 성공처리 COMMIT *********************/        OUT_CD      := '00';
        OUT_MSG     := V_CNT;
      RETURN;END SP_ACNT_TAX120B;谁能详细说说这段代码.因为它在执行后得到了        OUT_CD  ,
        OUT_MSG 的值.不太清楚这个sql语句...

Answer 1

OUT_CD OUT_MSG是就得出值!显示出来!

Answer 2

貌似是韩语。
 --  RETURN VALUE
          OUT_CD                      OUT         VARCHAR2,
          OUT_MSG                     OUT         VARCHAR2
这就是要从过程中得到的返回值，好比你投入了一份爱情就会收获一个女人一样的道理。

Answer 3

韩国人写的程序有那么难吗?
整个存储过程主要看OUT_MSG返回值(OUT_MSG:= V_CNT);
而OUT_MSG的返回值是由传入变量IN_TYPE = 'run'还是IN_TYPE = 'rollback' 决定的,
如果是run,那么变量V_CNT是循环计数;如果是rollback则是直接统计记录数OUT_CD返回一个常量,也没什么好说了但有一点值得我们学习的是别写的代码非常规范\整齐