SQL 다루기(5) : PACKAGE, TRIGGER

1. PACKAGE

• PACKAGE 란 JAVA 에서 여러 클래스들을 모아놓은 그것과 비슷하게 SQL에서 여러 프로시저나 함수 등을 모아놓은 것 이라고 생각하면 된다.
• PACKAGE 에는 선언부 SPEC 와 실행부 BODY 가 있다
  • 선언 파일과 실행부 파일을 따로 만든 후 실행해야한다.
  • PACKAGE 실행은 패키지명.함수명으로 실행한다.
  • SPEC 에서는 어떤 함수와 어떤 프로시저들이 들어가는지 선언한다.

1) 선언부

CREATE OR REPLACE PACKAGE MY_PACK
IS
-- 패키지 실행방법 : 패키지명.함수명

	-- PACKAGE SEPC : 패키지 선언
	-- 1) ANNSAL
	FUNCTION ANNSAL(VEMPNO EMP.EMPNO%TYPE)
	RETURN NUMBER;

	-- 2) RAISE SAL
	PROCEDURE DOWN_SAL(
		VEMPNO EMP.EMPNO%TYPE,
		VRATIO NUMBER);

END;
/

2) 구현부

CREATE OR REPLACE PACKAGE BODY MY_PACK
IS
-- 패키지 실행방법 : 패키지명.함수명

	-- PACKAGE SEPC + BODY : 패키지 선언 + 구현 함께
	-- 1) ANNSAL
	FUNCTION ANNSAL(VEMPNO EMP.EMPNO%TYPE)
		-- 최종 RETURN 값의 타입?
		RETURN NUMBER
	IS
		-- 변수로 쓸 VSAL 의 타입 정의
		VSAL EMP.SAL%TYPE := 0;
	BEGIN
		SELECT SAL*12+NVL(COMM, 0)
			INTO VSAL
		FROM EMP
		-- EMPNO 는 내가 함수의 매개변수로 보내준 VEMPNO 를 사용함
		WHERE EMPNO = VEMPNO;

		-- 계산된 연봉인 VSAL 을 RETURN
		RETURN VSAL;
	END ANNSAL;

	PROCEDURE DOWN_SAL(
		VEMPNO EMP.EMPNO%TYPE,
		VRATIO NUMBER)
	IS

	BEGIN
		UPDATE EMP
		SET SAL = SAL*(1-VRATIO/100)
		WHERE EMPNO = VEMPNO;

	END DOWN_SAL;

END;
/

3) 실행

• 패키지명.함수명

SQL> SELECT MY_PACK.ANNSAL(7839) FROM DUAL;

MY_PACK.ANNSAL(7839)
--------------------
               69120

---

2. TRIGGER

• 정해진 이벤트에 의해서 암시적으로 호출되는 PL/SQL PROGRAM UNIT
  • 즉 정해진 이벤트에 따라서 다른 테이블에 추가, 수정, 삭제 가능
• 진짜 진짜 유용하고 편함 ⇒ 대표적으로 메일서버 구축한 것이나 게임 만들 때 사용했던 것처럼 여러 부분에서 사용 가능
• ORACLE 에서는 새로 추가되는 값은 :NEW.컬럼명 , 이전 값은 :OLD.컬럼명을 사용한다.
• 트리거에서 중요한 부분은 다음 4가지
  • ‘어떤’ 테이블의 변화가 있을 때 실행되는지
  • ‘어떤’ 곳 ROW 에 실행되는지
  • BEGIN 안에는 데이터를 넣을 테이블의 QUERY 문
  • 새로 추가되는 값은 :NEW.컬럼명
  • 이전 값은 :OLD.칼럼명

1) insert, update, delete

CREATE OR REPLACE TRIGGER INS_TRI -- TRIGGER 명
AFTER INSERT ON CDEPT -- CDEPT 테이블에 DATA 가 INSERT 된 경우
FOR EACH ROW -- 매 행마다

BEGIN
	INSERT INTO DEPT
	-- NEW 는 '새로 넣어진 값' 을 대표하는 대표변수
	-- NEW.DEPTNO 라면 새로 INSERT 된 DEPTNO 를 의미
	VALUES(:NEW.DEPTNO, :NEW.DNAME, :NEW.LOC);
END;
/

## update ##
CREATE OR REPLACE TRIGGER UPDATE_TRI -- TRIGGER 명
AFTER UPDATE ON CDEPT 
FOR EACH ROW -- 매 행마다

BEGIN
	UPDATE DEPT
	SET DNAME = :NEW.DNAME, 
	LOC = :NEW.LOC
	WHERE DEPTNO = :OLD.DEPTNO;
END;
/

## delete ##
CREATE OR REPLACE TRIGGER DELETE_TRI
AFTER DELETE ON CDEPT
FOR EACH ROW

BEGIN
	DELETE DEPT
	WHERE DEPTNO = :OLD.DEPTNO;
END;
/

2) Insert, update, delete 를 한번에

CREATE OR REPLACE TRIGGER DML_TRI
AFTER INSERT OR UPDATE OR DELETE ON CDEPT
FOR EACH ROW

BEGIN
	IF INSERTING THEN
		INSERT INTO DEPT
		-- NEW 는 '새로 넣어진 값' 을 대표하는 대표변수
		-- NEW.DEPTNO 라면 새로 INSERT 된 DEPTNO 를 의미
		VALUES(:NEW.DEPTNO, :NEW.DNAME, :NEW.LOC);

	ELSIF UPDATING THEN
		UPDATE DEPT
		SET DNAME = :NEW.DNAME, 
		LOC = :NEW.LOC
		WHERE DEPTNO = :OLD.DEPTNO;

	ELSIF DELETING THEN
		DELETE DEPT
		WHERE DEPTNO = :OLD.DEPTNO;
	END IF;
END;
/

 

---

- 참고

[PL/SQL] 패키지(PACKAGE)

[PL/SQL] 패키지(PACKAGE)