27.커서,트리거
/*
<CURSOR>
특정 SQL문장의 처리결과집합을 담고 있는 영역을 가르키는 포인터
커서를 이용하여 SQL문장의 처리결과집합에 접근 할 수 있다
<묵시적 커서>
오라클 내부에서 자동으로 생성되는 커서
PL/SQL 블록에서 SQL문장이 실행 될때마다 자동으로 생성
커서의 속성에 접근하여 여러가지 정보를 알 수 있다
- 묵시적커서의 속성
커서명%ISOPEN : FALSE
커서명%FOUND : 결과 집합이 1개 이상인 경우
커서명%NOTFOUND : 결과 집합이 0개인 경우
커서명%ROWCOUNT : 최근 수행된 쿼리의 결과행 수
<명시적 커서>
사용자가 직접 정의하여 사용하는 커서
SQL문장의 결과집합에 접근 하여 커서 사용 시 여러 행으로 나타난 처리 결과에 순차적으로 접근이 가능하다.
커서선언(DECLARE)
↓
커서열기(OPEN)
↓
커서에서 데이터 가져오기(FATCH) --
↓ ㅣ→ 반복
데이터처리 --
↓
커서닫기(CLOSE)
[커서 속성]
커서명%ISOPEN : 커서가 OPEN 상태인 경우 TRUE, 아니면 FALSE
커서명%FOUND : 커서 영역에 남아있는 ROW 수가 한 개 이상일 경우 TRUE, 아니면 FALSE
커서명%NOTFOUND : 커서 영역에 남아있는 ROW 수가 없다면 TURE, 아니면 FALSE
커서명%ROWCOUNT : SQL 처리 결과로 얻어온 행(ROW)의 수
[사용 방법]
1) CURSOR 커서명 IS .. : 커서 선언
2) OPEN 커서명; : 커서 오픈
3) FETCH 커서명 INTO 변수, ... : 커서에서 데이터 추출(한 행씩 데이터를 가져온다.)
4) CLOSE 커서명 : 커서 닫기
[표현법]
CURSOR 커서명 IS [SELECT 문]
OPEN 커서명;
FETCH 커서명 INTO 변수;
...
CLOSE 커서명;
*/
/*
사원테이블에 등록된 사원의 사번과 이름을 출력하는 익명의 프로시져
*/
DECLARE
-- 1.커서 선언
CURSOR C1
IS (SELECT emp_id, emp_name FROM emp);
-- 변수 선언
eid emp.emp_id%TYPE;
ename emp.emp_name%TYPE;
BEGIN
-- 2.오픈 커서
-- OPEN 커서이름
OPEN C1;
LOOP
-- 3.패치 : 다음행을 읽어 변수에 담아준다
FETCH C1 INTO EID, ENAME;
-- 4.반복문탈출
-- 커서 영역의 자료가 모두 FETCH되어 다음영역이 존재하지 않으면 탈출
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('사번 : '||eid);
DBMS_OUTPUT.PUT_LINE('이름 : '||ename);
END LOOP;
-- 5.클로즈 커서
CLOSE C1;
END;
/
-- 급여가 3000000 이상인 사원의 사번, 이름, 급여 출력(PL/SQL)
DECLARE
CURSOR C1 IS (SELECT emp_id, emp_name, salary FROM emp WHERE salary >= 3000000);
v_eid emp.emp_id%TYPE;
v_ename emp.emp_name%TYPE;
v_sal emp.salary%TYPE;
v_cnt NUMBER;
BEGIN
OPEN C1;
LOOP
--결과집합으로부터 한행씩 읽어서 변수에 저장
FETCH C1 INTO v_eid, v_ename, v_sal;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('사번 : '||v_eid);
DBMS_OUTPUT.PUT_LINE('이름 : '||v_ename);
DBMS_OUTPUT.PUT_LINE('급여 : '||v_sal);
END LOOP;
v_cnt := C1%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('총 건수 : '||v_cnt);
--DBMS_OUTPUT.PUT_LINE('총 건수 : '||C1%ROWCOUNT);
CLOSE C1;
END;
/
-- 부서테이블의 전체 데이터를 조회 후 출력하느 프로시저
CREATE OR REPLACE PROCEDURE PROC_CURSOR_DEPT
IS
v_dept dept%ROWTYPE;
CURSOR C1 IS(SELECT * FROM dept);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO v_dept.dept_id, v_dept.dept_title, v_dept.location_id;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_dept.dept_id ||' '|| v_dept.dept_title ||' '|| v_dept.location_id);
END LOOP;
CLOSE C1;
END;
/
EXEC PROC_CURSOR_DEPT;
/*
FOR IN LOOP를 이용한 커서 사용
FOR ~ IN: CURSOR를 선언할 필요가 없으며, CURSOR의 OPEN, CLOSE, FETCH가 자동으로 관리됨.
FOR 변수명 IN ( 쿼리 )
쿼리 결과 집합으로부터 한건씩 읽어 변수에 담아 줍니다
*/
CREATE OR REPLACE PROCEDURE PROC_CURSOR_DEPT_TITLE
IS
v_dept dept%ROWTYPE;
BEGIN
FOR v_dept_title IN (SELECT * FROM DEPT)
LOOP
DBMS_OUTPUT.PUT_LINE(v_dept.dept_id||' '||v_dept_title.dept_title);
END LOOP;
END;
/
EXEC PROC_CURSOR_DEPT_TITLE;
/*
사원의 사번, 이름, 부서명을 출력(레코드 타입을 선언)
프로시저명 : proc_cursor_record
*/
CREATE OR REPLACE PROCEDURE PROC_CURSOR_RECORD
IS
-- 레코드 타입을 선언
TYPE emp_record_type IS RECORD(
emp_id emp.emp_id%TYPE,
emp_name emp.emp_name%TYPE,
dept_title dept.dept_title%TYPE
);
-- 변수의 타입으로 지정
v_info emp_record_type;
BEGIN
FOR v_info IN (
SELECT emp_id, emp_name, dept_title
FROM emp e, dept d
WHERE e.dept_code = d.dept_id(+)
)
LOOP
DBMS_OUTPUT.PUT_LINE(v_info.emp_id||' '||v_info.emp_name||' '||v_info.dept_title);
END LOOP;
END;
/
EXEC PROC_CURSOR_RECORD;
/*
<TRIGGER>
테이블이 INSERT, UPDATE, DELETE 등 DML 구문에 의해서 변경될 경우
자동으로 실행될 내용을 정의해놓는 객체이다.
* 트리거의 종류
1) SQL 문의 실행 시기에 따른 분류
- BEFORE TRIGGER : 해당 SQL 문장 실행 전에 트리거를 실행한다.
- AFTER TRIGGER : 해당 SQL 문장 실행 후에 트리거를 실행한다.
2) SQL 문에 의해 영향을 받는 행에 따른 분류
- 문장 트리거 : 해당 SQL 문에 한 번만 트리거를 실행한다.
- 행 트리거 : 해당 SQL 문에 영향을 받는 행마다 트리거를 실행한다.
[표현법]
CREATE OR REPLACE TRIGGER 트리거명
BEFORE|AFTER INSERT|UPDATE|DELETE ON 테이명
[FOR EACH ROW]
DECLARE
선언부
BEGIN
실행부
EXCEPTION
예외처리부
END;
/
*/
CREATE OR REPLACE TRIGGER TRC_EMP_IST
AFTER INSERT ON EMP
BEGIN
DBMS_OUTPUT.PUT_LINE('신입사원이 입사했습니다.');
END;
/
DESC EMP;
SELECT MAX(EMP_ID)+1 FROM EMP;
INSERT INTO emp(emp_id, emp_name, emp_no, job_code) VALUES((SELECT MAX(EMP_ID)+1 FROM EMP),
'박보검', '111111-1111111', 'J1');
-- 부서코드 변경
UPDATE EMP SET dept_code = NULL WHERE dept_code = 'D2';
SELECT * FROM EMP WHERE dept_code = 'D2';
CREATE OR REPLACE TRIGGER TRG_DEPT_DEL
AFTER UPDATE ON EMP FOR EACH ROW
BEGIN
-- :OLD EMP테이블이 변경되기 전 데이터
-- :NEW EMP테이블이 변경된 후 데이터
DBMS_OUTPUT.PUT_LINE('사원정보가 업데이트 되었습니다.');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.DEPT_CODE ||' 변경 후 : '|| :NEW.DEPT_CODE);
END;
/
/*
백업테이블 생성
사원정보가 수정될 경우 변경전 정보를 HISTORY 테이블에 저장
*/
-- 테이블을 복사하여 HISTORY테이블을 생성
CREATE TABLE EMP_HISTORY
AS SELECT emp.*, SYSDATE REGDATE FROM emp WHERE 1=2;
SELECT * FROM emp_history;
-- 트리거명 : TRG_EMP_UDT
-- EMP테이블에 업데이트 작업이 일어난 이후 실행
-- 수정사항이발생한 행의 이전정보를 EMP_HISTORY테이블에 입력
CREATE OR REPLACE TRIGGER TRG_EMP_UDT
AFTER UPDATE ON EMP FOR EACH ROW
BEGIN
INSERT INTO EMP_HISTORY(emp_id, emp_name, emp_no, job_code, dept_code)
VALUES(:OLD.emp_id, :OLD.emp_name, :OLD.emp_no, :OLD.job_code, :OLD.dept_code);
END;
/