关于简单的存储过程SQL codecreate or replace PROCEDURESP_ACNT_TAX120B(IN_TYPEINVARCHAR2,--????IN_ACN
关于简单的存储过程
- 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_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语句...
[解决办法]
[解决办法]
韩国人写的程序有那么难吗?
整个存储过程主要看OUT_MSG返回值(OUT_MSG:= V_CNT);
而OUT_MSG的返回值是由传入变量IN_TYPE = 'run'还是IN_TYPE = 'rollback' 决定的,
如果是run,那么变量V_CNT是循环计数;如果是rollback则是直接统计记录数
OUT_CD返回一个常量,也没什么好说了
但有一点值得我们学习的是别写的代码非常规范\整齐