[Oracle] FOR LOOP

[Oracle] FOR LOOP

참조 : https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/a_samps.htm#540

1. 단순 FOR LOOP

DECLARE x NUMBER := 100; BEGIN FOR i IN 1..10 LOOP IF MOD(i,2) = 0 THEN -- i is even INSERT INTO temp VALUES (i, x, 'i is even'); ELSE INSERT INTO temp VALUES (i, x, 'i is odd'); END IF; x := x + 100; END LOOP; COMMIT; END;

2. CURSOR

DECLARE CURSOR c1 is SELECT ename, empno, sal FROM emp ORDER BY sal DESC; -- start with highest paid employee my_ename VARCHAR2(10); my_empno NUMBER(4); my_sal NUMBER(7,2); BEGIN OPEN c1; FOR i IN 1..5 LOOP FETCH c1 INTO my_ename, my_empno, my_sal; EXIT WHEN c1%NOTFOUND; /* in case the number requested */ /* is more than the total */ /* number of employees */ INSERT INTO temp VALUES (my_sal, my_empno, my_ename); COMMIT; END LOOP; CLOSE c1; END;

3. SCOPING - LOOP 안에 중복 변수 사용

DECLARE x NUMBER := 0; counter NUMBER := 0; BEGIN FOR i IN 1..4 LOOP x := x + 1000; counter := counter + 1; INSERT INTO temp VALUES (x, counter, 'in OUTER loop'); /* start an inner block */ DECLARE x NUMBER := 0; -- this is a local version of x BEGIN FOR i IN 1..4 LOOP x := x + 1; -- this increments the local x counter := counter + 1; INSERT INTO temp VALUES (x, counter, 'inner loop'); END LOOP; END; END LOOP; COMMIT; END;

4. Batch Transaction Processing

DECLARE CURSOR c1 IS SELECT account_id, oper_type, new_value FROM action ORDER BY time_tag FOR UPDATE OF status; BEGIN FOR acct IN c1 LOOP -- process each row one at a time acct.oper_type := upper(acct.oper_type); /*----------------------------------------*/ /* Process an UPDATE. If the account to */ /* be updated doesn't exist, create a new */ /* account. */ /*----------------------------------------*/ IF acct.oper_type = 'U' THEN UPDATE accounts SET bal = acct.new_value WHERE account_id = acct.account_id; IF SQL%NOTFOUND THEN -- account didn't exist. Create it. INSERT INTO accounts VALUES (acct.account_id, acct.new_value); UPDATE action SET status = 'Update: ID not found. Value inserted.' WHERE CURRENT OF c1; ELSE UPDATE action SET status = 'Update: Success.' WHERE CURRENT OF c1; END IF; /*--------------------------------------------*/ /* Process an INSERT. If the account already */ /* exists, do an update of the account */ /* instead. */ /*--------------------------------------------*/ ELSIF acct.oper_type = 'I' THEN BEGIN INSERT INTO accounts VALUES (acct.account_id, acct.new_value); UPDATE action set status = 'Insert: Success.' WHERE CURRENT OF c1; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- account already exists UPDATE accounts SET bal = acct.new_value WHERE account_id = acct.account_id; UPDATE action SET status = 'Insert: Acct exists. Updated instead.' WHERE CURRENT OF c1; END; /*--------------------------------------------*/ /* Process a DELETE. If the account doesn't */ /* exist, set the status field to say that */ /* the account wasn't found. */ /*--------------------------------------------*/ ELSIF acct.oper_type = 'D' THEN DELETE FROM accounts WHERE account_id = acct.account_id; IF SQL%NOTFOUND THEN -- account didn't exist. UPDATE action SET status = 'Delete: ID not found.' WHERE CURRENT OF c1; ELSE UPDATE action SET status = 'Delete: Success.' WHERE CURRENT OF c1; END IF; /*--------------------------------------------*/ /* The requested operation is invalid. */ /*--------------------------------------------*/ ELSE -- oper_type is invalid UPDATE action SET status = 'Invalid operation. No action taken.' WHERE CURRENT OF c1; END IF; END LOOP; COMMIT; END;