关于简单的存储过程
SQL codecreate 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;