Oracle - Procedure 예제1

[ Oracle - Procedure 예제1 ]

 

Stored Procedure Sample CREATE OR REPLACE PROCEDURE DP_BUYER_DTCONV IS V_YY TMP_AC.MKR_3%TYPE; V_MM TMP_AC.MKR_3%TYPE; V_DD TMP_AC.MKR_3%TYPE; V_3 TMP_AC.MKR_3%TYPE; V_4 TMP_AC.MKR_4%TYPE; V_9 TMP_AC.MKR_9%TYPE; V_10 TMP_AC.MKR_10%TYPE; V_11 TMP_AC.MKR_11%TYPE; V_CD TMP_AC.MKR_1%TYPE; CURSOR CUR1 IS SELECT MKR_1, MKR_3, MKR_4, MKR_9, MKR_10, MKR_11 FROM tmp_ac; BEGIN OPEN CUR1; LOOP FETCH CUR1 INTO V_CD, V_3, V_4, V_9, V_10, V_11; EXIT WHEN CUR1%NOTFOUND; IF SUBSTR(V_3 ,5,2) < '00' THEN V_3 := SUBSTR(V_3,1,4)||'01'; ELSIF SUBSTR(V_3 ,5,2) > '31' THEN IF SUBSTR(V_3 ,3,2) IN ('01','03','05','07','08','10','12') THEN V_3 := SUBSTR(V_3,1,4)||'31'; ELSE V_3 := SUBSTR(V_3,1,4)||'30'; END IF; END IF; IF SUBSTR(V_4 ,5,2) < '00' THEN V_4 := SUBSTR(V_4,1,4)||'01'; ELSIF SUBSTR(V_4 ,5,2) > '31' THEN IF SUBSTR(V_4 ,3,2) IN ('01','03','05','07','08','10','12') THEN V_4 := SUBSTR(V_4,1,4)||'31'; ELSE V_4 := SUBSTR(V_4,1,4)||'30'; END IF; END IF; IF SUBSTR(V_9 ,5,2) < '00' THEN V_9 := SUBSTR(V_9,1,4)||'01'; ELSIF SUBSTR(V_9 ,5,2) > '31' THEN IF SUBSTR(V_9 ,3,2) IN ('01','03','05','07','08','10','12') THEN V_9 := SUBSTR(V_9,1,4)||'31'; ELSE V_9 := SUBSTR(V_9,1,4)||'30'; END IF; END IF; IF SUBSTR(V_10 ,5,2) < '00' THEN V_10 := SUBSTR(V_10,1,4)||'01'; ELSIF SUBSTR(V_10,5,2) > '31' THEN IF SUBSTR(V_10,3,2) IN ('01','03','05','07','08','10','12') THEN V_10 := SUBSTR(V_10,1,4)||'31'; ELSE V_10 := SUBSTR(V_10,1,4)||'30'; END IF; END IF; IF SUBSTR(V_11 ,5,2) < '00' THEN V_11 := SUBSTR(V_11,1,4)||'01'; ELSIF SUBSTR(V_11,5,2) > '31' THEN IF SUBSTR(V_11,3,2) IN ('01','03','05','07','08','10','12') THEN V_11 := SUBSTR(V_11,1,4)||'31'; ELSE V_11 := SUBSTR(V_11,1,4)||'30'; END IF; END IF; BEGIN UPDATE TMP_AC SET MKR_3 = V_3, MKR_4 = V_4, MKR_9 = V_9, MKR_10 = V_10, MKR_11 = V_11 WHERE MKR_1 = V_CD; END; COMMIT; END LOOP; CLOSE CUR1; END; / 발주처 : http://myhome.netsgo.com/mino70/main_03_oracle03.htm