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;
/