SQL에 없는 변수 선언, 선택 처리, 반복 처리를 제공하는 절차적인 언어
세부분의 영역으로 구분하여 PL/SQL 작성
영역에서 하나의 명령을 구분하기 위해 ; 사용
마지막 영역은 END 키워드로 마무리 후 ; 사용
PL/SQL 실행을 위해 마지막에 / 기호를 사용
메세지를 출력할 수 있도록 세션의 환경변수 설정값 변경
SET SERVEROUT ON;
--간단한 메세지를 출력하는 PL/SQL 작성
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO, ORACLE!!!');
END;
/
오라클 자료형을 사용하여 선언된 변수
스칼라 변수를 선언하여 값을 저장하고 화면에 변수값을 출력하는 PL/SQL 작성
DECLARE
VEMPNO NUMBER(4) := 7788;
VENAME VARCHAR2(20) := 'SCOTT';
BEGIN
DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름');
DBMS_OUTPUT.PUT_LINE('-----------------');
DBMS_OUTPUT.PUT_LINE(VEMPNO||' / '||VENAME);
DBMS_OUTPUT.PUT_LINE('-----------------');
VEMPNO := 7893;
VENAME := 'KING';
DBMS_OUTPUT.PUT_LINE(VEMPNO||' / '||VENAME);
DBMS_OUTPUT.PUT_LINE('-----------------');
END;
/
다른 변수의 자료형 또는 테이블의 컬럼 자료형을 참조하여 선언된 변수 (선언부)
형식) 변수명 {변수명%TPYE | 테이블명.컬럼명%TYPE}
테이블에 저장된 행을 검색하여 컬럼값을 변수에 저장하는 명령 (실행부)
형식) SELECT 검색대상,검색대상,... INTO 변수명,변수명,... FROM 테이블명 [WHERE 조건식]
--EMP 테이블의 EMPNO 컬럼과 ENAME 컬럼의 자료형을 참조하여 레퍼런스 변수를 선언하고
--EMP 테이블에서 사원이름이 SCOTT인 사원의 사원번호와 사원이름을 검색하여
--레퍼런스 변수에 저장해서 출력하는 PL/SQL 작성
DECLARE
VEMPNO EMP.EMPNO%TYPE;
VENAME EMP.ENAME%TYPE;
BEGIN
/* 주석문 처리 - 프로그램에 설명을 제공하는 문장 */
/* 단일행을 검색하여 컬럼값을 레퍼런스 변수에 저장 - 다중행 검색시 에러 발생 */
SELECT EMPNO,ENAME INTO VEMPNO,VENAME FROM EMP WHERE ENAME='SCOTT';
DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름');
DBMS_OUTPUT.PUT_LINE('-----------------');
DBMS_OUTPUT.PUT_LINE(VEMPNO||' / '||VENAME);
END;
/
테이블에 저장된 행을 여러개 검색하여 얻은 다수의 컬럼값을 저장하기 위해 선언하는 변수
형식) 테이블변수명 테이블타입명
테이블 변수를 생성하기 위해 테이블 변수의 자료형(테이블 타입)을 먼저 선언
형식) TYPE 테이블타입명 IS TABLE OF {자료형 | 변수명%TYPE | 테이블명.컬럼명%TYPE}
[NOT NULL][INDEX BY BINARY_INTEGER]
테이블 변수의 사용 방법 : 테이블 변수는 테이블 변수의 요소를 첨자로 구분하여 사용
형식) 테이블변수명(첨자)
--EMP 테이블의 EMPNO 컬럼과 ENAME 컬럼을 참조하여 테이블 변수를 선언하고 EMP 테이블에 저장된
--모든 사원의 사원번호,사원이름을 검색하여 테이블 변수에 저장해서 출력하는 PL/SQL 작성
DECLARE
/* 테이블 타입 선언 */
TYPE EMPNO_TABLE_TYPE IS TABLE OF EMP.EMPNO%TYPE INDEX BY BINARY_INTEGER;
TYPE ENAME_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER;
/* 테이블 변수 선언 */
VEMPNO_TABLE EMPNO_TABLE_TYPE;
VENAME_TABLE ENAME_TABLE_TYPE;
/* 테이블 변수의 요소를 반복처리 하기 위해 첨자 역활을 실행하기 위한 변수 선언 - 초기값 저장 */
I BINARY_INTEGER := 0;
BEGIN
/* EMP 테이블에 저장된 모든 사원의 사원번호, 사원이름을 검색하여
테이블 변수의 요소에 저장하기 위한 반복문 */
FOR K IN (SELECT EMPNO,ENAME FROM EMP) LOOP
I := I + 1;
VEMPNO_TABLE(I) := K.EMPNO;
VENAME_TABLE(I) := K.ENAME;
END LOOP;
DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름');
DBMS_OUTPUT.PUT_LINE('-----------------');
/* 테이블 변수에 저장된 요소값을 출력하기 위한 반복문 */
FOR J IN 1..I LOOP
DBMS_OUTPUT.PUT_LINE(VEMPNO_TABLE(J)||' / '||VENAME_TABLE(J));
END LOOP;
END;
/
테이블에 저장된 하나의 행의 모든 컬럼값을 저장하기 위해 선언하는 변수
형식) 레코드변수명 레코드타입명
레코드 변수를 생성하기 위해 레코드 변수의 자료형(레코드 타입)을 먼저 선언
형식) TYPE 레코드타입명 IS RECORD RECORD(필드명 {자료형 | 변수명%TYPE | 테이블명.컬럼명%TYPE} [NOT NULL][{:=|DEFAULT} 표현식], 필드명 {자료형 | 변수명%TYPE | 테이블명.컬럼명%TYPE} [NOT NULL][{:=|DEFAULT} 표현식],...)
레코드 변수를 사용하여 필드에 접근하는 방법
형식) 레코드변수명.필드명
--EMP 테이블의 EMPNO,ENAME,JOB,SAL,DEPTNO 컬럼을 참조하여 레코드 변수를 선언하고
--EMP 테이블에서 사원번호가 7844인 사원의 사원번호,사원이름,급여,업무,부서번호를 검색하여
--레코드 변수에 저장해서 출력하는 PL/SQL 작성
DECLARE
/* 레코드 타입 선언 */
TYPE EMP_RECORD_TYPE IS RECORD(VEMPNO EMP.EMPNO%TYPE, VENAME EMP.ENAME%TYPE,
VJOB EMP.JOB%TYPE, VSAL EMP.SAL%TYPE,VDEPTNO EMP.DEPTNO%TYPE);
/* 레코드 변수 선언 */
EMP_RECORD EMP_RECORD_TYPE;
BEGIN
/* 검색행의 모든 컬럼값을 레코드 변수의 필드에 저장 - 검색행이 다중행인 경우 에러 발생 */
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO INTO EMP_RECORD.VEMPNO, EMP_RECORD.VENAME,
EMP_RECORD.VJOB, EMP_RECORD.VSAL, EMP_RECORD.VDEPTNO FROM EMP WHERE EMPNO=7844;
DBMS_OUTPUT.PUT_LINE('사원번호 = '||EMP_RECORD.VEMPNO);
DBMS_OUTPUT.PUT_LINE('사원이름 = '||EMP_RECORD.VENAME);
DBMS_OUTPUT.PUT_LINE('업무 = '||EMP_RECORD.VJOB);
DBMS_OUTPUT.PUT_LINE('급여 = '||EMP_RECORD.VSAL);
DBMS_OUTPUT.PUT_LINE('부서번호 = '||EMP_RECORD.VDEPTNO);
END;
/
--EMP 테이블의 EMPNO,ENAME,JOB,SAL,DEPTNO 컬럼을 참조하여 레코드 변수를 선언하고
--EMP 테이블에서 사원번호가 7844인 사원의 사원번호,사원이름,급여,업무,부서번호를 검색하여
--레코드 변수에 저장해서 출력하는 PL/SQL 작성
DECLARE
EMP_RECORD EMP%ROWTYPE;
BEGIN
/* EMP 테이블의 검색행(단일행)의 모든 컬럼값을 레코드 변수의 필드에 저장 */
SELECT * INTO EMP_RECORD FROM EMP WHERE EMPNO=7844;
DBMS_OUTPUT.PUT_LINE('사원번호 = '||EMP_RECORD.EMPNO);
DBMS_OUTPUT.PUT_LINE('사원이름 = '||EMP_RECORD.ENAME);
DBMS_OUTPUT.PUT_LINE('업무 = '||EMP_RECORD.JOB);
DBMS_OUTPUT.PUT_LINE('급여 = '||EMP_RECORD.SAL);
DBMS_OUTPUT.PUT_LINE('부서번호 = '||EMP_RECORD.DEPTNO);
END;
/
--EMP 테이블에서 사원번호 7788인 사원정보를 검색하여 사원번호, 사원이름, 부서번호에 대한
--부서명을 출력하는 PL/SQL 작성
--부서번호에 대한 부서명 : 10(ACCOUNTING), 20(RESEARCH), 30(SAELS), 40(OPERATION)
DECLARE
VEMP EMP%ROWTYPE; /* 레코드 변수 선언 */
VDNAME VARCHAR2(20) := NULL; /* 스칼라 변수 선언 */
BEGIN
SELECT * INTO VEMP FROM EMP WHERE EMPNO=7788;
IF(VEMP.DEPTNO=10) THEN
VDNAME := 'ACCOUNTING';
END IF;
IF(VEMP.DEPTNO=20) THEN
VDNAME := 'RESEARCH';
END IF;
IF(VEMP.DEPTNO=30) THEN
VDNAME := 'SAELS';
END IF;
IF(VEMP.DEPTNO=40) THEN
VDNAME := 'OPERATION';
END IF;
DBMS_OUTPUT.PUT_LINE('사원번호 = '||VEMP.EMPNO);
DBMS_OUTPUT.PUT_LINE('사원이름 = '||VEMP.ENAME);
DBMS_OUTPUT.PUT_LINE('부서이름 = '||VDNAME);
END;
/
--EMP 테이블에서 사원번호가 7788인 사원정보를 검색하여 사원번호, 사원이름, 사원연봉을 계산하여
--출력하는 PL/SQL 작성, 사원연봉 : (급여+성과급)*12
DECLARE
VEMP EMP%ROWTYPE; -- 레코드 변수선언
ANNUAL NUMBER(7,2) := 0; -- 스칼라 변수선언
BEGIN
SELECT * INTO VEMP FROM EMP WHERE EMPNO=7788;
IF VEMP.COMM IS NULL THEN
ANNUAL := VEMP.SAL * 12;
ELSE
ANNUAL := (VEMP.SAL + VEMP.COMM) * 12;
END IF;
DBMS_OUTPUT.PUT_LINE('사원번호 = '||VEMP.EMPNO);
DBMS_OUTPUT.PUT_LINE('사원이름 = '||VEMP.ENAME);
DBMS_OUTPUT.PUT_LINE('사원연봉 = '||ANNUAL);
END;
/
--EMP 테이블에서 사원번호 7788인 사원정보를 검색하여 사원번호, 사원이름, 부서번호에 대한
--부서명을 출력하는 PL/SQL 작성
--부서번호에 대한 부서명 : 10(ACCOUNTING), 20(RESEARCH), 30(SAELS), 40(OPERATION)
DECLARE
VEMP EMP%ROWTYPE; --레코드 변수선언
VDNAME VARCHAR2(20) := NULL; -- 스칼라 변수선언
BEGIN
SELECT * INTO VEMP FROM EMP WHERE EMPNO=7788;
IF(VEMP.DEPTNO=10) THEN
VDNAME := 'ACCOUNTING';
ELSIF(VEMP.DEPTNO=20) THEN
VDNAME := 'RESEARCH';
ELSIF(VEMP.DEPTNO=30) THEN
VDNAME := 'SAELS';
ELSIF(VEMP.DEPTNO=40) THEN
VDNAME := 'OPERATION';
END IF;
DBMS_OUTPUT.PUT_LINE('사원번호 = '||VEMP.EMPNO);
DBMS_OUTPUT.PUT_LINE('사원이름 = '||VEMP.ENAME);
DBMS_OUTPUT.PUT_LINE('부서이름 = '||VDNAME);
END;
/
--EMP 테이블에서 사원번호가 7788인 사원정보를 검색하여 사원번호, 사원이름, 업무, 급여,
--업무별 급여 실지급액을 계산하여 출력하는 PL/SQL 작성
--업무별 급여 실지급액 - ANALYST:급여*1.1, CLERK:급여*1.2, MANAGER:급여*1.3,
-- PRESIDENT:급여*1.4, SALESMAN:급여*1.5
DECLARE
VEMP EMP%ROWTYPE;
VPAY NUMBER(7,2);
BEGIN
SELECT * INTO VEMP FROM EMP WHERE EMPNO=7788;
CASE VEMP.JOB
WHEN 'ANALYST' THEN
VPAY := VEMP.SAL * 1.1;
WHEN 'CLERK' THEN
VPAY := VEMP.SAL * 1.2;
WHEN 'MANAGER' THEN
VPAY := VEMP.SAL * 1.3;
WHEN 'PRESIDENT' THEN
VPAY := VEMP.SAL * 1.4;
WHEN 'SALESMAN' THEN
VPAY := VEMP.SAL * 1.5;
END CASE;
DBMS_OUTPUT.PUT_LINE('사원번호 = '||VEMP.EMPNO);
DBMS_OUTPUT.PUT_LINE('사원이름 = '||VEMP.ENAME);
DBMS_OUTPUT.PUT_LINE('업무 = '||VEMP.JOB);
DBMS_OUTPUT.PUT_LINE('급여 = '||VEMP.SAL);
DBMS_OUTPUT.PUT_LINE('실지급액 = '||VPAY);
END;
/
--EMP 테이블에서 사원번호가 7788인 사원정보를 검색하여 사원번호, 사원이름, 급여, 급여등급을
--계산하여 출력하는 PL/SQL 작성
--급여등급 : E(0~1000), D(1001~2000), C(2001~3000), B(3001~4000), A(4001~5000)
DECLARE
VEMP EMP%ROWTYPE;
VGRADE VARCHAR2(1);
BEGIN
SELECT * INTO VEMP FROM EMP WHERE EMPNO=7788;
CASE
WHEN VEMP.SAL BETWEEN 0 AND 1000 THEN
VGRADE := 'E';
WHEN VEMP.SAL BETWEEN 1001 AND 2000 THEN
VGRADE := 'D';
WHEN VEMP.SAL BETWEEN 2001 AND 3000 THEN
VGRADE := 'C';
WHEN VEMP.SAL BETWEEN 3001 AND 4000 THEN
VGRADE := 'B';
WHEN VEMP.SAL BETWEEN 4001 AND 5000 THEN
VGRADE := 'A';
END CASE;
DBMS_OUTPUT.PUT_LINE('사원번호 = '||VEMP.EMPNO);
DBMS_OUTPUT.PUT_LINE('사원이름 = '||VEMP.ENAME);
DBMS_OUTPUT.PUT_LINE('급여 = '||VEMP.SAL);
DBMS_OUTPUT.PUT_LINE('급여등급 = '||VGRADE);
END;
/
--1~5 범위의 숫자값을 출력하는 PL/SQL 작성
DECLARE
I NUMBER(1) := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(I);
I := I + 1;
IF I > 5 THEN
EXIT;
END IF;
END LOOP;
END;
/
--1~10 범위의 정수들의 합계를 계산하여 출력하는 PL/SQL 작성
DECLARE
TOT NUMBER(2) := 0;
BEGIN
/* FOR LOOP 구문에서 생성되는 변수(INDEX_COUNTER)는 FOR LOOP 구문에서만 사용 가능 */
FOR I IN 1..10 LOOP
TOT := TOT + I;
END LOOP;
DBMS_OUTPUT.PUT_LINE('1~10 범위의 정수들의 합계 = '||TOT);
END;
/
--EMP 테이블에 저장된 모든 사원정보를 검색하여 사원번호,사원이름을 출력하는 PL/SQL 작성
BEGIN
FOR VEMP IN (SELECT * FROM EMP) LOOP
DBMS_OUTPUT.PUT_LINE('사원번호 = '||VEMP.EMPNO||', 사원이름 = '||VEMP.ENAME);
END LOOP;
END;
/
DECLARE
CURSOR C IS SELECT * FROM DEPT;
VDEPT DEPT%ROWTYPE;
BEGIN
/* 커서 열기 - 첫번째 검색행을 제공받기 위해 커서의 위치 이동 */
OPEN C;
LOOP
/* 현재 커서의 위치에 존재하는 검색행을 제공받아 레코드 변수에 저장
- 커서는 다음행으로 자동 이동 */
FETCH C INTO VDEPT;
EXIT WHEN C%NOTFOUND;/* 커서의 위치에 더이상 검색행이 없는 경우 반복문 종료 */
DBMS_OUTPUT.PUT_LINE('부서번호 = '||VDEPT.DEPTNO||',
부서이름 = '||VDEPT.DNAME||', 부서위치 = '||VDEPT.LOC);
END LOOP;
/* 커서 닫기 */
CLOSE C;
END;
/
DECLARE
CURSOR C IS SELECT * FROM DEPT;
BEGIN
/* FOR LOOP 구문을 사용하면 커서에 대한 OPEN, FETCH, CLOSE 명령을
사용하지 않아도 자동으로 반복 처리 가능 */
FOR VDEPT IN C LOOP
DBMS_OUTPUT.PUT_LINE('부서번호 = '||VDEPT.DEPTNO||',
부서이름 = '||VDEPT.DNAME||', 부서위치 = '||VDEPT.LOC);
END LOOP;
END;
/
BEGIN
/* 선언부에서 커서를 생성하지 않고 FOR LOOP 구문에서 SELECT 명령을 직접 사용하여
검색행에 대한 반복 처리 가능 */
FOR VDEPT IN (SELECT * FROM DEPT) LOOP
DBMS_OUTPUT.PUT_LINE('부서번호 = '||VDEPT.DEPTNO||',
부서이름 = '||VDEPT.DNAME||', 부서위치 = '||VDEPT.LOC);
END LOOP;
END;
/
--1~10 범위의 정수들의 합계를 계산하여 출력하는 PL/SQL 작성
DECLARE
I NUMBER(2) := 1;
TOT NUMBER(2) := 0;
BEGIN
WHILE I <= 10 LOOP
TOT := TOT + I;
I := I + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('1~10 범위의 정수들의 합계 = '||TOT);
END;
/
--EMP2 테이블에 저장된 모든 사원정보를 삭제하는 저장 프로시저 생성
CREATE OR REPLACE PROCEDURE DELETE_ALL_EMP2 IS
BEGIN
DELETE FROM EMP2;
COMMIT;
END;
/
SELECT NAME,TEXT FROM USER_SOURCE WHERE NAME='DELETE_ALL_EMP2';
--DELETE_ALL_EMP2 저장 프로시저 호출
EXECUTE DELETE_ALL_EMP2;
SELECT * FROM EMP2;--모든 사원정보 삭제됨 확인
SHOW ERROR; --오류 없음 확인
--DELETE_ALL_EMP2 저장 프로시저 삭제
DROP PROCEDURE DELETE_ALL_EMP2;
SELECT NAME,TEXT FROM USER_SOURCE WHERE NAME='DELETE_ALL_EMP2';--삭제 확인
IN : 외부의 값을 저장 프로시저로 전달받아 PL/SQL 명령에서 사용할 목적의 매개변수(MODE)를 선언할 때 사용 - 저장 프로시저 호출시 값 전달
OUT : PL/SQL 명령의 실행 결과값을 저장 프로시저의 외부로 제공할 목적할 매개변수를 선언할 때 사용 - 저장 프로시저 호출시 바인딩 변수 전달
INOUT : IN 모드와 OUT 모드를 모두 제공하는 매개변수를 선언할 때 사용 - 저장 프로시저 호출시 바인딩 변수(값) 전달
--사원번호를 매개변수로 전달받아 EMP 테이블에서 해당 사원번호의 사원정보를 검색하여
--사원이름, 업무, 급여를 매개변수로 전달하여 외부로 제공하는 저장 프로시저 생성
CREATE OR REPLACE PROCEDURE SELECT_EMPNO(VEMPNO IN EMP.EMPNO%TYPE,
VENAME OUT EMP.ENAME%TYPE, VJOB OUT EMP.JOB%TYPE, VSAL OUT EMP.SAL%TYPE) IS
BEGIN
SELECT ENAME,JOB,SAL INTO VENAME,VJOB,VSAL FROM EMP WHERE EMPNO=VEMPNO;
END;
/
VARIABLE VAR_ENAME VARCHAR2(15);
VARIABLE VAR_JOB VARCHAR2(20);
VARIABLE VAR_SAL NUMBER;
EXECUTE SELECT_EMPNO(7788,:VAR_ENAME,:VAR_JOB,:VAR_SAL);
PRINT VAR_ENAME; --SCOTT
PRINT VAR_JOB; --ANALYST
PRINT VAR_SAL; --3000
--사원번호를 매개변수로 전달받아 EMP 테이블에서 해당 사원번호의 사원정보를 검색하여
--급여에 2배에 해당하는 결과값을 반환하는 저장 함수 생성
CREATE OR REPLACE FUNCTION CAL_SAL(VEMPNO IN EMP.EMPNO%TYPE) RETURN NUMBER IS
VSAL NUMBER(7,2);
BEGIN
SELECT SAL INTO VSAL FROM EMP WHERE EMPNO=VEMPNO;
RETURN(VSAL*2.0);
END;
/
--USER_SOURCE 딕셔너리
SELECT NAME,TEXT FROM USER_SOURCE WHERE NAME='CAL_SAL';
VARIABLE VAR_SAL NUMBER;
EXECUTE :VAR_SAL := CAL_SAL(7788);
PRINT VAR_SAL;
SELECT EMPNO,ENAME,SAL,CAL_SAL(EMPNO) "특별수당" FROM EMP;
--CAL_SAL 저장 함수 삭제
DROP FUNCTION CAL_SAL;
SELECT NAME,TEXT FROM USER_SOURCE WHERE NAME='CAL_SAL'; --삭제 확인
형식) CREATE [OR REPLACE] TRIGGER 트리거명 {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON 테이블명 [FOR EACH ROW][WITH 조건식] BEGIN 명령;명령;... END;
FOR EACH ROW : 생략된 경우 1. 문장 레벨 트리거를 생성하고 선언한 경우 2. 행 레벨 트리거로 생성
1.문장 레벨 트리거 : 이벤트 DML 명령이 실행되면 트리거에 작성된 PL/SQL 프로시저의 명령을 한번만 실행
2.행 레벨 트리거 : 이벤트 DML 명령이 실행되면 트리거에 작성된 PL/SQL 프로시저의 명령을 행의 갯수만큼 실행
트리거에 등록된 PL/SQL 프로시저 명령에는 TCL 명령(COMMIT 또는 ROLLBACK) 사용 불가능
--SAWON 테이블에서 사원정보가 삽입될 경우 메세지를 출력하는 트리거 생성
CREATE OR REPLACE TRIGGER SAWON_INSERT AFTER INSERT ON SAWON
BEGIN
DBMS_OUTPUT.PUT_LINE('새로운 사원이 입사 하였습니다.');
END;
/
--SAWON 테이블에 행 삽입
--INSERT 명령 실행 후 SAWON_INSERT 트리거에 등록된 PL/SQL 프로시저의 명령 실행
INSERT INTO SAWON VALUES(3000,'전우치',10);--새로운 사원이 입사하였습니다.
SELECT * FROM SAWON;
COMMIT;
SELECT TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_NAME FROM USER_TRIGGERS;
--EMP 테이블에 저장된 모든 사원의 사원번호, 사원이름, 급여, 부서번호를 검색하여
--EMP_TRI 테이블를 생성하여 검색행을 삽입
CREATE TABLE EMP_TRI AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP;
--EMP_HIS 테이블 생성 - 사원번호(숫자형), 사원이름(문자형), 사원상태(문자형)
CREATE TABLE EMP_HIS(NO NUMBER(4),NAME VARCHAR2(20),STATUS VARCHAR2(50));
--EMP_TRI 테이블에서 행을 삽입하거나 변경 또는 삭제한 경우 명령 실행 후 행 삽입,변경,삭제에 대한
--이유를 EMP_HIS 테이블에 행으로 삽입하는 트리거 생성
CREATE OR REPLACE TRIGGER INSERT_EMP_HIS AFTER INSERT OR UPDATE OR DELETE
ON EMP_TRI FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO EMP_HIS VALUES(:NEW.EMPNO,:NEW.ENAME,'입사');
ELSIF UPDATING THEN
IF :NEW.DEPTNO <> :OLD.DEPTNO THEN
INSERT INTO EMP_HIS VALUES(:OLD.EMPNO,:OLD.ENAME,'부서이동');
ELSE
INSERT INTO EMP_HIS VALUES(:OLD.EMPNO,:OLD.ENAME,'개인사유');
END IF;
ELSIF DELETING THEN
INSERT INTO EMP_HIS VALUES(:OLD.EMPNO,:OLD.ENAME,'퇴사');
END IF;
END;
/
--EMP_TRI 테이블에 행 삽입 - 트리거에 의해 EMP_HIS 테이블에 행 삽입
INSERT INTO EMP_TRI VALUES(5000,'PARK',2000,10);
SELECT * FROM EMP_TRI WHERE EMPNO=5000; --입사 확인
SELECT * FROM EMP_HIS;
--EMP_TRI 테이블에 행의 컬럼값 변경 - 트리거에 의해 EMP_HIS 테이블에 행 삽입
UPDATE EMP_TRI SET DEPTNO=20 WHERE EMPNO=5000;
SELECT * FROM EMP_TRI WHERE EMPNO=5000;
SELECT * FROM EMP_HIS; --부서이동 확인
UPDATE EMP_TRI SET SAL=2500 WHERE EMPNO=5000;
SELECT * FROM EMP_TRI WHERE EMPNO=5000; --개인사유 확인
SELECT * FROM EMP_HIS;
--EMP_TRI 테이블에 행 삭제 - 트리거에 의해 EMP_HIS 테이블에 행 삽입
DELETE FROM EMP_TRI WHERE EMPNO=5000;
SELECT * FROM EMP_TRI WHERE EMPNO=5000;
SELECT * FROM EMP_HIS; --퇴사 확인
DELETE FROM EMP_TRI WHERE SAL<2000;
SELECT * FROM EMP_TRI;
SELECT * FROM EMP_HIS; --8명 퇴사 확인
COMMIT;