커서와 트리거

-- 5. 커서
-- 실습: 부서 테이블의 모든 내용을 조회하기
-- 커서를 사용하여 부서 테이블의 모든 내용을 출력한다.

SELECT * FROM DEPT;

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE01
IS
    VDEPT DEPT%ROWTYPE; -- 레코드 타입으로 변수 선언
    CURSOR C1 -- 커서 선언
    IS
    SELECT * FROM DEPT;
BEGIN
    DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
    OPEN C1; -- 커서 열기
    LOOP
        FETCH C1 INTO VDEPT.DEPTNO, VDEPT.DNAME, VDEPT.LOC;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO || ' ' || VDEPT.DNAME || ' ' || VDEPT.LOC);
    END LOOP;
    CLOSE C1; -- 커서 닫기
END;
/

EXECUTE CURSOR_SAMPLE01; -- 저장 절차를 실행한다.

-- CURSOR와 FOR LOOP

SELECT * FROM DEPT;

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE02
IS
    VDEPT DEPT%ROWTYPE; -- 레코드 타입으로 변수 선언
    CURSOR C1 IS                       -- 커서 선언
        SELECT * FROM DEPT;
BEGIN
    DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
    DBMS_OUTPUT.PUT_LINE('---------------------------------');
    --  OPEN C1; -- 커서 열기
    --  LOOP
    --     FETCH C1 INTO VDEPT.DEPTNO, VDEPT.DNAME, VDEPT.LOC;
    --     EXIT WHEN C1%NOTFOUND;
    --     DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO || ' ' || VDEPT.DNAME || ' ' || VDEPT.LOC);
    --END LOOP;
    --CLOS C1;
    FOR VDEPT IN C1 LOOP
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO||' ' || VDEPT.DNAME|| ' ' || VDEPT.LOC);
    END LOOP;
END;
/

EXECUTE CURSOR_SAMPLE02; -- 저장 절차를 실행한다.

-- 6. 트리거

DROP TABLE EMP01;

-- 1) 사원 테이블 생성
CREATE TABLE EMP01(  
    EMPNO NUMBER(4) PRIMARY KEY,
    ENAME VARCHAR2(20),
    JOB VARCHAR2(20)
);

-- 2) 트리거 작성
CREATE OR REPLACE TRIGGER TRG_01 
AFTER INSERT ON EMP01 -- 이벤트
BEGIN
    -- 이벤트 핸들러
    DBMS_OUTPUT.PUT_LINE('신입사원이 입사했습니다.');
END;
/

-- 3) 사원 테이블에 데이터를 추가
SELECT * FROM EMP01; 
SET SERVEROUTPUT ON
INSERT INTO EMP01 VALUES(1, '전원지', '화가');

-- 실습: 급여 정보를 자동으로 추가하는 트리거 작성하기
-- 사원 테이블에 새로운 데이터가 들어오면(즉, 신입 사원이 들어오면)
-- 급여 테이블에 새로운 데이터(즉, 신입 사원의 급여정보)를 자동으로 생성하도록 하기 위해서
-- 사원 테이블에 트리거를 작성한다.(신입사원의 급여는 일괄적으로 100으로 한다.)

-- 1) 급여를 저장할 테이블을 생성
DROP TABLE SAL01;

CREATE TABLE SAL01(
    SALNO NUMBER(4) PRIMARY KEY,
    SAL NUMBER(7, 2),
    EMPNO NUMBER(4) REFERENCES EMP01(EMPNO)
);

-- 2) 급여번호를 자동 생성하는 시퀀스를 정의하고 이 시퀀스로부터 일련번호 얻어 급여번호에 부여
CREATE SEQUENCE SAL01_SALNO_SEQ;

-- 3) 트리거 생성
CREATE OR REPLACE TRIGGER TRG_02
AFTER INSERT ON EMP01 FOR EACH ROW
BEGIN
    INSERT INTO SAL01
    VALUES(SAL01_SALNO_SEQ.NEXTVAL, 100, :NEW.EMPNO);
END;
/

-- 4) 사원 테이블에 로우를 추가
INSERT INTO EMP01 VALUES(2, '전수빈', '프로그래머');
SELECT * FROM EMP01;
SELECT * FROM SAL01;

INSERT INTO EMP01 VALUES(3, '김종현', '교수');
SELECT * FROM EMP01;
SELECT * FROM SAL01;

-- 실습: 급여 정보를 자동으로 삭제하는 트리거 작성하기
-- 사원이 삭제되면 그 사원의 급여 정보도 자동 삭제되는 트리거를 작성한다.

-- 1) 사원 테이블의 로우를 삭제
DELETE FROM EMP01 WHERE EMPNO=2; -- SQL Error 발생

-- 2) 트리거를 작성
CREATE OR REPLACE TRIGGER TRG_03
AFTER DELETE ON EMP01 FOR EACH ROW
BEGIN
    DELETE FROM SAL01 WHERE EMPNO=:old.EMPNO;
END;
/

-- 3) 사원 테이블의 로우를 삭제
DELETE FROM EMP01 WHERE EMPNO=2;
SELECT * FROM EMP01;
SELECT * FROM SAL01;

-- 실습: 급여 정보를 자동 추가하는 트리커 제거하기
-- 1. 급여 정보를 자동 추가하는 트리거인 TRG_02를 제거해 본다.
-- 2. TRG_02 트리거를 삭제하고 난 후에 사원 테이블에 행을 추가했더니
--      급여가 자동으로 입력되지 않는 것을 확인할 수 있다.

DROP TRIGGER TRG_02;
INSERT INTO EMP01 VALUES(4, '최은정', '선생님');
SELECT * FROM EMP01;
SELECT * FROM SAL01;

-- 예제를 통한 트리거의 적용
-- 실습: 입고 트리거 작성하기
-- 입고 테이블에 상품이 입력되면 입고 수량을 상품 테이블의 재고 수량에 추가하는 트리거 작성

-- 1) 테이블을 생성
CREATE TABLE 상품(
    상품코드 CHAR(6) PRIMARY KEY,
    상품명 VARCHAR2(12) NOT NULL,
    제조사 VARCHAR(12),
    소비자가격 NUMBER(8),
    재고수량 NUMBER DEFAULT 0
);

CREATE TABLE 입고(
    입고번호 NUMBER(6) PRIMARY KEY,
    상품코드 CHAR(6) REFERENCES 상품(상품코드),
    입고일자 DATE DEFAULT SYSDATE,
    입고수량 NUMBER(6),
    입고단가 NUMBER(8),
    입고금액 NUMBER(8)
);

-- 2) 샘플 데이터를 입력
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격)
VALUES('A00001', '세탁기', 'LG', 500);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격)
VALUES('A00002', '컴퓨터', 'LG', 700);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격)
VALUES('A00003', '냉장고', '삼성', 600);
SELECT * FROM 상품;

-- 3) 입고 트리거
CREATE OR REPLACE TRIGGER TRG_04
AFTER INSERT ON 입고
FOR EACH ROW
BEGIN
    UPDATE 상품
    SET 재고수량 = 재고수량 + :NEW.입고수량
    WHERE 상품코드 = :NEW.상품코드;
END;
/

-- 4) 입고 테이블에 상품을 입력
INSERT INTO 입고(입고번호, 상품코드, 입고수량, 입고단가, 입고금액)
VALUES(1, 'A00001', 5, 320, 1600);
SELECT * FROM 입고;
SELECT * FROM 상품;
INSERT INTO 입고(입고번호, 상품코드, 입고수량, 입고단가, 입고금액)
VALUES(2, 'A00002', 10, 680, 6800);
SELECT * FROM 입고;
SELECT * FROM 상품;
INSERT INTO 입고(입고번호, 상품코드, 입고수량, 입고단가, 입고금액)
VALUES(3, 'A00003', 3, 220, 660);
INSERT INTO 입고(입고번호, 상품코드, 입고수량, 입고단가, 입고금액)
VALUES(4, 'A00003', 5, 220, 1100);
SELECT * FROM 입고;
SELECT * FROM 상품;

 

-- 갱신 트리거 작성하기
-- 이미 입고된 상품에 대해서 입고 수량이 변경되면 상품 테이블의 재고수량 역시 변경되어야 한다.
-- 이를 위한 갱신 트리거를 작성한다.

-- 1) 갱신 트리거
CREATE OR REPLACE TRIGGER TRG03
AFTER UPDATE ON 입고
FOR EACH ROW
BEGIN
    UPDATE 상품
    SET 재고수량 = 재고수량 + (- :old.입고수량  + :new.입고수량)
    WHERE 상품코드 = :new.상품코드;
END;
/

-- 2) 입고 수량 변경
UPDATE 입고 SET 입고수량=10, 입고금액=2200
WHERE 입고번호=4;

SELECT * FROM 입고 ORDER BY 입고번호;
SELECT * FROM 상품;

 

-- 실습: 삭제 트리거 작성하기
-- 입고 테이블에서 입고되었던 상황이 삭제되면 상품 테이블에 재고수량에서 삭제된 입고수량 만큼을 빼는 삭제 트리거를 작성한다.

-- 1) 삭제 트리거
CREATE OR REPLACE TRIGGER TRG04
AFTER DELETE ON 입고
FOR EACH ROW
BEGIN
    UPDATE 상품
    SET 재고수량 = 재고수량 - :old.입고수량
    WHERE 상품코드 = :old.상품코드;
END;
/

-- 2) 삭제
DELETE 입고 WHERE 입고번호=3;
SELECT * FROM 입고 ORDER BY 입고번호;
SELECT * FROM 상품;