■ SQL%FOUND 암시적 커서 속성을 사용하는 방법을 보여준다.
▶ 예제 코드 (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 44 45 46 47 48 49 50 51 52 53 54 |
-- 직원 테이블을 생성한다. 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; -- 직원명 변경하기 프로시저를 생성한다. CREATE PROCEDURE ChangeEmployeeName(P_OLD_NAME IN EMP.ENAME%TYPE, P_NEW_NAME IN EMP.ENAME%TYPE, P_CHANGED OUT BOOLEAN) IS BEGIN UPDATE EMP SET ENAME = P_NEW_NAME WHERE ENAME = P_OLD_NAME; P_CHANGED := SQL%FOUND; END; SET SERVEROUTPUT ON; DECLARE V_CHANGED BOOLEAN; BEGIN ChangeEmployeeName('ALLEN', 'ALLEN_NEW', V_CHANGED); IF V_CHANGED = TRUE THEN DBMS_OUTPUT.PUT_LINE('UPDATED'); ELSE DBMS_OUTPUT.PUT_LINE('NO UPDATED'); END IF; END; |