■ DML 명령문에서 RETURNING 키워드를 사용하는 방법을 보여준다.
▶ 예제 코드 (SQL)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
-- 직원 테이블을 생성한다. CREATE TABLE EMP ( EMPNO NUMBER(4, 0) ,ENAME VARCHAR2(10) ,SAL NUMBER(7, 2) ,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO) ); -- 직원 테이블에 데이터를 추가한다. INSERT INTO EMP VALUES(7369, 'SMITH' , 800); INSERT INTO EMP VALUES(7499, 'ALLEN' , 1600); INSERT INTO EMP VALUES(7521, 'WARD' , 1250); INSERT INTO EMP VALUES(7566, 'JONES' , 2975); INSERT INTO EMP VALUES(7654, 'MARTIN', 1250); INSERT INTO EMP VALUES(7698, 'BLAKE' , 2850); INSERT INTO EMP VALUES(7782, 'CLARK' , 2450); INSERT INTO EMP VALUES(7788, 'SCOTT' , 3000); INSERT INTO EMP VALUES(7839, 'KING' , 5000); INSERT INTO EMP VALUES(7844, 'TURNER', 1500); INSERT INTO EMP VALUES(7876, 'ADAMS' , 1100); INSERT INTO EMP VALUES(7900, 'JAMES' , 950 ); INSERT INTO EMP VALUES(7902, 'FORD' , 3000); INSERT INTO EMP VALUES(7934, 'MILLER', 1300); COMMIT; DECLARE V_ENAME EMP.ENAME%TYPE; V_SAL EMP.SAL%TYPE; BEGIN FOR EMPLOYEE_RECORD IN (SELECT * FROM EMP) LOOP UPDATE EMP SET SAL = SAL + 100 WHERE EMPNO = EMPLOYEE_RECORD.EMPNO RETURNING ENAME, SAL INTO V_ENAME, V_SAL; DBMS_OUTPUT.PUT_LINE(V_ENAME || ', ' || V_SAL); END LOOP; END; |