21_ 저장 프로시저, 저장함수, 커서, 트리거

[ 저장 프로시저, 저장함수, 커서, 트리거 목차 ]

1. 저장 프로시저

   1) 저장 프로시저 생성하는 방법

   2) 저장 프로시저의 오류 원인 살피기

   3) 저장 프로시저 조회하기

2. 저장 프로시저의 매개 변수

3. IN, OUT, INOUT 매개변수

   1) IN 매개변수

   2) OUT 매개변수

   3) IN, OUT 매개변수 활용

4. 저장 함수

5. 커서

   1) 커서의 형식과 개념

        - DECLARE CURSOR

        - OPEN CURSOR

        - FETCH CURSOR

        - 커서의 상태

        - CLOSE CURSOR

   2) CURSOR와 FOR LOOP

6. 트리거

   1) 트리거란 무엇인가

   2) 트리거 삭제

   3) 트리거 적용

 

 

 

[ 1 ] 저장 프로시저

- 오라클은 사용자가 만든 PL/SQL 문을 데이터베이스에 저장할 수 있도록

  저장 프로시저(STORED PROCEDURE) 라는 것을 제공한다.

- 저장 프로시저를 생성하려면 CREATE PROCEDURE 다음에

  새롭게 생성하고자하는 프로시저 이름을 기술한다.

- 이렇게 저장 프로시저를 사용하면 복잡한 DML문들이

  필요할 때마다 다시 입력할 필요 없이 간단하게 호출만 해서

  복잡한 DML문의 실행 결과를 얻을 수 있다.

- 또한 저장 프로시저를 사용하면 성능도 향상되고,

  호환성 문제도 해결된다.

 

 

* 호환성 문제 해결이란,

  데이터베이스에는 오라클DB, MYsql 등등 종류가 여러가지 있다.

  데이터베이스 종류에 따라 문법 syntax가 다 다르다.

  그럴 때 외부에서 syntax를 잘 몰라도 값만 잘 넣으면
  잘 출력될 수 있도록 해주는 것을 말한다.

 

 

 

 

1. 저장 프로시저 생성하는 방법

 

* 메서드 생성하는 것처럼 코드 작성한다.

 

-- 형식
CREATE [OR REPLACE] PROCEDURE procedure_name (
	argument1 [mode] data_type,
    argument2 [mode] data_type,
    . . .
)
IS
	local_variable declaration
BEGIN
	statement1;
    statement2;
    . . .
END;
/

-- 실행
EXECUTE procedure_name (argument1, argument2, ...);

 

 

[ 실습 : 저장 프로시저 생성하기 ]

- 사원 테이블에 저장된 모든 사원을 삭제하는 프로시저를 작성해 보자.

-- 21-01-01.저장 프로시저(STORED PROCEDURE) 생성하는 방법
DROP TABLE EMP01;

CREATE TABLE EMP01
AS
SELECT * FROM EMP;

SELECT * FROM EMP01;

-- 프로시저 생성
CREATE OR REPLACE PROCEDURE DEL_ALL
IS
BEGIN
    DELETE FROM EMP01;
END;  -- 실행이 아니라 컴파일 되었다고 나온다! DELETE 하긴 했지만 위에 SELECT EMP01 해보면 데이터 아직 있다.
/

-- 아래의 코드를 실행해야 EMP01 테이블이 DELETE 되어서 데이터가 다 사라지게 된다. 이게 실행된것임,,
EXECUTE DEL_ALL;
-- 이 EXECUTE 실행코드까지 해야 원하는 실행문이 진짜로 실행이 된다.

 

 

[ EMP01 테이블 생성 ]

 

 

[ 프로시저 생성하고 코드 실행하면 생성한 프로시저가 컴파일 된다. ]

 

 

 

[ EXECUTE DEL_ALL; 코드 실행하고 다시 SELECT * FROM EMP01; 코드 실행해본 결과,, 데이터가 다 사라진것을 볼 수 있다. ]

 

 

 

2. 저장 프로시저의 오류 원인 살피기

- 오류가 발생할 경우 "SHOW ERROR" 명령어를 수행하면

  오류가 발생한 원인을 알 수 있게 된다.

 

- 원인을 분석하여 오류를 수정한 후 다시 저장 프로시저를 생성을

  시도하여 '프로시저가 생성되었습니다.'란 메시지가 출력되어

  저장 프로시저가 성공적으로 생성될 때까지 오류 수정 작업을

  반복해야 한다.

 

 

DROP TABLE EMP01;

CREATE TABLE EMP01
AS
SELECT * FROM EMP;

SELECT * FROM EMP01;

DROP PROCEDURE DEL_ALL;

CREATE OR REPLACE PROCEDURE DEL_ALL
IS
--BEGIN
	DELETE FROM EMP01;
END;
/

SHOW ERROR

 

 

 

 

3. 저장 프로시저 조회하기

- 저장 프로시저를 작성한 후 사용자가 저장 프로시저가

  생성되었는지 확인하려면 USER_SOURCE 키워드를

  사용하여  살펴보면 된다.

 

-- 저장 프로시저 조회하기
SELECT NAME, TEXT FROM USER_SOURCE;
-- 우리가 만든 프로시저들 출력된다.

 

 

[ 위에서 우리가 만들었던 프로시저의 이름(NAME)과 내용들(TEXT)이 출력된다. ]

 

 

 

 

 

---

 

 

[ 2 ] 저장 프로시저의 매개 변수

- 저장 프로시저에 값을 전달해주기 위해서 매개변수를 사용한다.

 

-- 매개변수 저장 프로시저
DROP TABLE EMP01;

CREATE TABLE EMP01
AS
SELECT * FROM EMP;

SELECT * FROM EMP01;

-- VENAME 이라는 매개변수가 있고 이 매개변수의 데이터타입은 EMP01의 ENAME칼럼의 데이터타입으로 지정했다.
CREATE OR REPLACE PROCEDURE DEL_ENAME(VENAME EMP01.ENAME%TYPE)
IS
BEGIN
    DELETE FROM EMP01
    WHERE ENAME = VENAME;  -- ENAME이 매개변수로 받은 VENAME 에 해당하는 데이터들을 EMP01 테이블에서 지워라 
END;
/

SELECT * FROM EMP01;

EXECUTE DEL_ENAME('SMITH'); -- 이거 실행하고 다시 SELECT * FROM EMP01 해보면 SMITH 사라짐..

 

 

[ EMP테이블 복사한 EMP01 테이블 출력하기 ]

 

[ DEL_ENAME 프로시저 생성한 후 EMP01 테이블 출력한 결과 ]

 

 

 

 

[ EXECUTE DEL_ENAME 실행하고 EMP01테이블 출력해보니 프로시저 생성하고 출력했을 때는 SMITH가 그대로 있더니 지금은 없는 것을 살펴 볼 수가 있다. ]

 

 

 

 

 

---

 

[ 3 ] IN, OUT, INOUT 매개 변수

- CREATE PROCEDURE로 프로시저를 생성할 때

  MODE를 지정하여 매개변수를 선언할 수 있는데

  MODE에 IN, OUT, INOUT 세가지를 기술할 수 있다.

 

1) IN : 데이터 전달 받을 때 사용

2) OUT : 수행된 결과를 받아갈 때 사용

3) INOUT : IN과 OUT 두가지 목적에 모두 사용될 때

 

 

1. IN 매개변수

- 앞선 예제 중에서 매개변수로 사원의 이름을 전달받아서

  해당 사원을 삭제하는 프로시저인 DEL_ENAME을 작성해보자.

- DEL_ENAME 프로시저에서 사용된 매개변수는 프로시저를

  호출할 때 기술한 값을 프로시저 내부에서 받아서 사용하고 있다.

 

 

- 이렇게 프로시저 호출 시 넘겨준 값을 받아오기 위한 매개변수는

  MODE를 IN으로 지정해서 선언한다.

 

CREATE PROCEDURE DEL_ENAME(VENAME IN EMP01.ENAME%TYPE)

 

 

 

 

2. OUT 매개변수

- 프로시저에 구한 결과 값을 얻어내기 위해서는

  MODE를 OUT으로 지정한다.

 

DROP PROCEDURE SEL_EMPNO;

-- 매개변수가 4개 VEMPNO, VENAME, VSAL, VJOB 인 프로시저
-- IN 은 데이터 입력받는 것, OUT은 수행된 결과값을 받아가는 것
CREATE OR REPLACE PROCEDURE SEL_EMPNO (
    VEMPNO IN EMP.EMPNO%TYPE,
    VENAME OUT EMP.ENAME%TYPE,
    VSAL OUT EMP.SAL%TYPE,
    VJOB OUT EMP.JOB%TYPE
)
IS
BEGIN
    SELECT ENAME, SAL, JOB INTO VENAME, VSAL, VJOB
    FROM EMP
    WHERE EMPNO=VEMPNO;
END;
/

-- 바인드 변수
-- ' : ' 를 덧붙여주는 변수는 미리 선언되어 있어야 한다.
VARIABLE VAR_ENAME VARCHAR2(15);
VARIABLE VAR_SAL NUMBER;
VARIABLE VAR_JOB VARCHAR2(9);

-- OUT 매개변수에서 값을 받아오기 위해서는 프로시저 호출 시 변수 앞에 ' : ' 를 덧붙인다.
EXECUTE SEL_EMPNO(7788, :VAR_ENAME, :VAR_SAL, :VAR_JOB);
-- :VAR_ENAME 는 실행된 값을 여기에 담겠다는 것이다.

PRINT VAR_ENAME; -- 위에서 실행한 결과값을 받은 변수 :VAR_ENAME 를 콘솔창에 출력하는 코드 
PRINT VAR_SAL;
PRINT VAR_JOB;

SELECT ENAME, SAL FROM EMP
WHERE EMPNO=7788;

 

 

[ SEL_EMPNO 프로시저 생성 코드 실행하면 다음과 같이 컴파일 되었다고 출력된다. ]

 

 

 

[ EXECUTE SEL_EMPNO 코드 실행하고 PRINT 구문 실행한 결과 출력 ]

 

 

 

 

 

---

 

[ 과제 : IN, OUT 매개변수 활용 ]

Q1. 사원명으로 검색하여 해당 사원의 직급을 얻어 오는 저장 프로시저를 SEL_EMPNAME 이라는 이름으로 작성해라.

A1.

 

* DROP PROCEDURE SEL_EMPNAME; 코드는 없어도 된다.

   CREATE 구문에 OR REPLACE 를 적어줬기 때문에 SEL_EMPNAME이

   있으면 내가 CREATE 프로시저한 것으로 대체되기 때문이다.

 

 

 

---

[ 4 ] 저장 함수

- 저장 함수 (STORED FUNCTION)

  : 저장 프로시저와 거의 유사한 용도로 사용한다.

* 저장 함수 : 리턴값이 있는 함수

 

- 저장 함수와 저장 프로시저의 차이점

  : 저장 함수는 실행 결과를 되돌려 받을 수 있다.

 

- 프로시저를 만들 때 --> PROCEDURE 로 기술

  함수 만들 때 --> FUNCTION 으로 기술

 

- 함수는 결과를 되돌려 받기 위해

  함수가 되돌려 받게 되는 자료형과

  되돌려 받을 값을 기술해야 한다.

 

- 저장 함수는 호출결과를 얻어오기 위해서

  호출 방식에 있어서도 저장 프로시저와

  차이점이 있다.

 

-- 형식
CREATE OR REPLACE FUNCTION 함수이름 (매개변수1 데이터타입, 매개변수2 데이터타입, . . .)
	RETURN 데이터타입
IS
	지역변수 데이터타입;
BEGIN
	실행문;
    RETURN 반환값;
END;
/

-- 실행
VARIABLE 바인드변수 데이터타입;
EXECUTE :바인드변수 := 함수이름(argument_list);

* 바인드 변수 : 결과값을 받는 변수

 

 

 

 

[ 실습 : 저장함수 작성하기 ]

- 특별 보너스를 지급하기 위한 저장함수를 작성해 보자. 이떼, 보너스는 급여의 200%를 지급한다고 가정하자.

 

SQL> CONN SCOTT/TIGER
SQL> ED PROC04

CREATE OR REPLACE FUNCTION CAL_BONUS(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);
END;
/


SQL> @PROC04

함수가 생성되었습니다.

SQL> VARIABLE VAR_RES NUMBER;
SQL> EXECUTE :VAR_RES :=CAL_BONUS(7788);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> PRINT VAR_RES

   VAR_RES
----------
     6000
    
SQL> SELECT SAL, CAL_BONUS(7788)
 2 FROM EMP
 3 WHERE EMPNO=7788;
 
      SAL  CAL_BONUS(7788)
---------- ---------------
     3000            6000
       
SQL>


-- 아래 쉅 내용 코드 --
CREATE OR REPLACE FUNCTION CAL_BONUS(VEMPNO IN EMP.EMPNO%TYPE)
    RETURN NUMBER -- 매개변수 받아서 함수 실행하고 리턴되는 데이터타입이 NUMBER이다.
IS
    VSAL NUMBER(7, 2);
BEGIN
    SELECT SAL INTO VSAL
    FROM EMP
    WHERE EMPNO = VEMPNO;
    
    RETURN (VSAL * 2);
END;
/

VARIABLE VAR_RES NUMBER; -- NUMBER 타입의 변수 : VAR_RES
EXECUTE :VAR_RES :=CAL_BONUS(7788);
PRINT VAR_RES;

 

 

 

 

 

( SKIP!)

[ 실습 : 판매된 도서에 대한 이익을 계산하는 함수 ]

- 판매된 도서의 이익을 계산하기 위해 각 주문 건별로 실제 판매가격인 SALEPRICE를 입력받아 가격에 맞는 이익

(30,000원 이상이면 10%, 30,000원 미만이면 5%)을 계산하여 반환하는 함수를 만들어보자.

 

D:\TEMP\SQL>SQLPLUS MADANG/MADANG
SQL> ED FNC_INTEREST.SQL
CREATE OR REPLACE FUNCTION FNC_INTEREST (
	PRICE NUMBER
) RETURN NUMBER
IS
	MYINTEREST NUMBER;
BEGIN
-- 가격이 30,000원 이상이면 10%, 30,000원 미만이면 5%
	IF PRICE >= 30000 THEN
    MYINTEREST := PRICE * 0.1;
    END IF;
    RETURN MYINTEREST;
END;
/

 

 

 

 

---

[ 과제 : 저장함수 생성 ]

Q.사원명으로 검색하여 해당 사원의 직급을 얻어 오는 저장 함수를 SEL_EMPNAME02 라는 이름으로 작성해 보자.

 

A.

CREATE OR REPLACE FUNCTION SEL_EMPNAME02(VENAME IN EMP.ENAME%TYPE)
    RETURN VARCHAR2
IS
    VJOB VARCHAR2(9);
BEGIN
    SELECT JOB INTO VJOB
    FROM EMP
    WHERE ENAME = VENAME;
    
    RETURN VJOB;
END;
/

VARIABLE VAR_JOB VARCHAR2(9);
EXECUTE :VAR_JOB :=SEL_EMPNAME02('SCOTT');

PRINT VAR_JOB

 

 

 

 

 

* 오타 조심할 것!!

  데이터타입을 VARCHAR2로 적어야하는 것을 VARCHAR로 적어서 코드 실행했더니

  다음과 같이 콘솔창에 사용법이 출력되었다!

  오타,, 유의하자!!! :-)

 

 

---

 

[ 5 ]  커서

- 대부분의 SELECT문은 수행 후 반환되는 해의 개수가 한개 이상이다.

- 처리 결과가 여러 개의 행으로 구해지는 SELECT문을 처리하려면

  커서를 이용해야 한다.

- 커서(cursor)는 실행 결과 테이블을 한 번에 한 행씩 처리하기 위해

  행을 순서대로 가리키는 데 사용한다.

 

 

키워드	역할
CURSOR <cursor 이름> IS <커서 정의>	커서를 생성
OPEN <cursor 이름>	커서의 사용을 시작
FETCH <cursor 이름> INTO <변수>	한 행의 데이터를 가져옴
CLOSE <cursor 이름>	커서의 사용을 끝냄

[ 표 : 커서와 관련된 키워드 ]

 

 

1. 커서의 형식과 개념

-- 형식
DECLARE
    CURSOR cursor_name IS statement;  -- 커서 선언
BEGIN
    OPEN cursor_name;  -- 커서 열기
    FETCH cursor_name INTO variable_name;  -- 커서로부터 데이터를 읽어와 변수에 저장
    CLOSE cursor_name;  -- 커서 닫긴
END;

 

 

 

( 1 ) DECLARE CURSOR

- 명시적으로 CURSOR를 선언하기 위해 CURSOR문장을 사용한다.

-- 형식
CURSOR cursor_name IS
select_statement;

-- 예시
CURSOR C1 IS
SELECT * FROM DEPT;

 

 

 

( 2 ) OPEN CURSOR

- 질의를 수행하고 검색 조건을 충족하는 모든 행으로 구성된 결과

  셋을 생성하기 위해 CURSOR를 OPEN한다.

  CURSOR는 이제 결과 셋에서 첫번째 행을 가리킨다.

 

-- 형식
OPEN cursor_name;

OPEN c1;

 

 

 

( 3 ) FETCH CURSOR

- FETCH문은 결과 셋에서 로우 단위로 데이터를 읽어 들인다.

  각 인출(FETCH) 후에 CURSOR는 결과 셋에서 다음 행으로 이동한다.

 

-- 형식
FETCH cursor_name INTO {variable1[,variable2,. . . .]};

-- 예시
LOOP
    FETCH C1 INTO VDEPT.DEPTNO, VDEPT.DNAME, VDEPT.LOC;
    EXIT WHEN C1%NOTFOUND;  -- 커서 C1이 NOTFOUND가 true할때까지 LOOP를 돈다.
END LOOP;

 

 

 

( 4 ) 커서의 상태

- FETCH문을 설명하면서 커서의 속성 중에 NOTFOUND를 언급했는데

  오라클에서는 이외에도 다양한 커서의 속성을 통해 커서의 상태를 알려준다.

  이 속성들을 이용해서 커서를 제어해야한다.

 

속성	의미
%NOTFOUND	커서 영역의 자료가 모두 FETCH되었다면 TRUE
%FOUND	커서 영역에 FETCH되지 않은 자료가 있다면 TRUE
%ISOPEN	커서가 OPEN된 상태이면 TRUE
%ROWCOUNT	커서가 얻어 온 레코드의 개수

 

 

 

( 5 ) CLOSE CURSOR

- CLOSE문장은 CURSOR를 사용할 수 없게 하고 결과 셋의 정의를 해제한다.

- SELECT문장이 다 처리된 후에는 CURSOR를 닫는다.

 

-- 형식
CLOSE cursor_name;

 

 

 

 

 

[ 실습 : 부서 테이블의 모든 내용을 조회하기 ]

- 커서를 사용하여 부서 테이블의 모든 내용을 출력한다.

1. ED 다음에 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력한다. (실습파일 : PROC05.SQL)

SET SERVEROUTPUT ON
CREATE OR REPLACE 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.DNAMAE || '  ' || VDEPT.LOC);
    END LOOP;
    
    CLOSE C1; -- 커서 닫기
END;
/

-- 아래 쉅 코드 내용 --
-- CURSOR를 이용해서 부서 테이블의 모든 내용을 조회하기
-- NOTFOUND : 커서 영역의 자료가 모두 FETCH 되면 TRUE

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE01
IS
    VDEPT DEPT%TYPE;  -- 레코드 타입 (자바의 클래스형과 유사)
    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;
        -- 커서로부터 데이터를 읽어와서 레코드 타입인 로컬 변수 VDEPT 에 저장
        EXIT WHEN C1%NOTFOUND;
        -- C1이 더이상 읽을 데이터가 없으면 빠져나가게 한다.
        DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO || '  ' || VDEPT.DNAME || '  ' || VDEPT.LOC);
        -- C1에서 데이터를 읽었으면 NOTFOUND 아니니깐 여기 DBMS로 와서 값이 출력될 것이다.
    END LOOP;
    CLOSE C1;  --커서 닫기
END;
/

EXECUTE CURSOR_SAMPLE01;

-- FETCH : 커서에서 하나씩 가져오는 것
-- 커서 : 테이블을 한번에 한 행씩 순서대로 가리킨다.

 

 

 

 

 

2. 작성을 완료한 후에 파일을 저장한다. SQL> 프롬프트에서 @파일명을 입력하면 SQL 파일 내부에 기술한

  PL/SQL이 실행된 후 오류가 발생하지 않으면 '프로시저가 생성되었습니다.'라는 메시지와 함께

  프로시저가 생성된다. 다음과 같이 프로시저를 호출하면 부서 테이블의 모든 정보가 출력된다.

SQL> @PROC05

프로시저가 생성되었습니다.

SQL> EXECUTE CURSOR_SAMPLE01;
부서번호 / 부서명 / 지역명
--------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>

 

 

 

 

2. CURSOR와 FOR LOOP

- CURSOR FOR LOOP는 명시적 CURSOR에서 행을 처리한다.

  LOOP에서 각 반복마다 CURSOR를 열고 행을 인출(FETCH)하고

  모든 행이 자동으로 CURSOR가 CLOSE되므로 사용하기 편리하다.

 

* LOOP를 사용할 때는 커서를 OPEN으로 열어주고

  FETCH로 데이터를 가져오고

  그 다음에 커서를 CLOSE 해서 닫아줘야 하는데,

  CURSOR FOR LOOP는 그냥 자동적으로 커서가 열리고

  자동적으로 데이터를 가져와지고 자동적으로 커서가 닫힌다.

  즉, OPEN, CLOSE, FETCH 를 생략할 수 있다는 것이다.

 

-- 형식
FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . . . . . .
END LOOP

 

 

 

 

[ 실습 : 부서 테이블의 모든 내용을 조회하기 (CURSOR와 FOR LOOP) ]

- OPEN ~ FETCH ~ CLOSE가 없이 FOR ~ LOOP ~ END LOOP문을 사용해서 보다 간단하게 커서를 처리할 수가 있다.

커서를 사용하여 부서 테이블의 모든 내용을 출력해 보겠다.

 

1. ED 다음에 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력한다. (실습파일: PROC06.SQL)

 

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('------------------------------------------');
    FOR VDEPT IN C1 LOOP
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO || '  ' || VDEPT.DNAME || '  ' || VDEPT.LOC);
    END LOOP;
END;
/

-- 아래 쉅 내용 코드 --
-- CURSOR와 FOR LOOP 사용하는 경우
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('------------------------------');
    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;

 

 

 

 

 

 

 

2. 작성을 완료한 후에 파일을 저장한다. SQL> 프롬프트에서 @파일명을 입력하면 SQL 파일 내부에 기술한

  PL/SQL이 실행된 후 오류가 발생하지 않으면 '프로시저가 생성되었습니다'라는 메서지 와 함께 프로시저가 생성된다.

  다음과 같이 프로시저를 호출하면 부서 테이블의 모든 정보가 출 력된다.

SQL> @PROC06

프로시저가 생성되었습니다.

SQL> EXECUTE CURSOR_SAMPLE02;
부서번호 / 부서명 / 지역명
-----------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>

 

 

 

 

 

---

[ 6 ] 트리거

1. 트리거란 무엇인가?

- 해당 단어의 의미처럼 어떤 이벤트가 발생하면 자동적으로 방아쇠가 당겨져 총알이 발사되듯이

  특정 테이블이 변경되면 이를 이벤트로 다른 테이블이 자동으로 변경되도록 하기 위해서 사용한다.

- 트리거는 특정 동작을 이벤트로 인해서만 실행되는 프로시저의 일종이다.

CREATE TRIGGER trigger_name
timing[BEFORE|AFTER] event[INSERT|UPDATE|DELETE]  -- 어느테이블의 이벤트가 INSERT, UPDATE, DELETE인지 그리고 그 이벤트의 BEFORE인지 AFTER인지
ON table_name
[FOR EACH ROW]
[WHEN conditions]
BEGIN
    statement
END

[ 트리거를 만들기위한 CREATE TRIGGER의 형식 ]

 

 

- 트리거의 타이밍

    - [BEFORE] 타이밍은 어떤 테이블에 INSERT, UPDATE, DELETE문이 실행될 때

      해당 문장이 실행되기 전에 트리거가 가지고 있는 BEGIN ~ END 사이의 문장을 실행한다.

    - [AFTER] 타이밍은 INSERT, UPDATE, DELETE문이 실행되고 난 후에

      트리거가 가지고 있는 BEGIN ~ END 사이의 문장을 실행한다.

 

- 트리거의 이벤트

    - 사용자가 어떤 DML(INSERT, UPDATE, DELETE)문을 실행했을 때

      트리거를 발생시킬 것인지를 결정한다.

 

- 트리거의 몸체

    - 해당 타이밍에 해당 이벤트가 발생하게 되면

      실행될 기본 로직이 포함되는 부분으로 BEGIN ~ END에 기술한다.

 

- 트리거의 유형

    - 트리거 유형은 FOR EACH ROW에 의해 문장 레벨 트리거와 행 레벨 트리거로 나뉜다.

 

      * FOR EACH ROW : 조건절에 해당하는 ROW가 여러개일 때

       각각의 ROW마다 모두 다 실행문을 적용 시켜줘라 라는 의미,

       FOR EACH ROW가 없으면 그냥 총 1번만 실행문을 적용시킨다.

 

 

 

    - FOR EACH ROW가 생략되면 문장 레벨 트리거,

      행 레벨 트리거 정의할 때는 반드시 FOR EACH ROW 기술해줘야 한다.

    1) 문장 레벨 트리거

        : 어떤 사용자가 트리거가 설정되어 있는 테이블에 대해 DML(INSERT, UPDATE, DELETE)문을

          실행할 때 단 한번만 트리거를 발생시킬 때 사용한다.

    2) 행 레벨 트리거

        : DML(INSERT, UPDATE, DELETE)문에 의해서 여러 개의 행이 변경된다면

          각 행이 변경될 때마다 트리거를 발생시키는 방법이다.

          만약 5개의 행이 변경되면 5번 트리거가 발생되는 것이다.

 

- 트리거의 조건

    - 트리거 조건은 행 레벨 트리거에서만 설정할 수 있다.

    - 또한 트리거 이벤트에 정의된 테이블에 이벤트가 발생할 때 보다 구체적인 데이터 검색 조건을

      부여할 때 사용된다.

 

 

 

 

[ 실습 : 단순 메시지를 출력하는 트리거 작성하기 ]

- 사원 테이블에 새로운 데이터가 들어오면 '신입사원이 입사했습니다.' 라는 메시지를 출력하도록 문장 레벨 트리거를 작성해보자.

-- 1. 사원 테이블 생성
DROP TABLE EMP01;

CREATE TABLE EMP01(
    EMPNO NUMBER(4) PRIMARY KEY,
    ENAME VARCHAR2(20),
    JOB VARCHAR2(20)
);

-- 2. 트리거 작성
-- EMP01 테이블에 INSERT 된 후(AFTER)에 실행문을 한번 적용해라.
CREATE OR REPLACE TRIGGER TRG_01
    AFTER INSERT
    ON EMP01
BEGIN
    DBMS_OUTPUT.PUT_LINE('신입사원이 입사했습니다.');
END;
/

-- 3. 사원 테이블에 데이터 추가
SET SERVEROUTPUT ON
INSERT INTO EMP01 VALUES(1, '전원지', '화가');
-- 데이터가 INSERT 되면 트리거로 인해 '신입사원이 입사했습니다', 가 한번 출력된다.

SELECT * FROM EMP01;
-- 데이터가 잘 추가된 것을 확인할 수 있다.

 

 

 

 

 

 

 

 

 

 

 

[ 실습 : 급여 정보를 자동으로 추가하는 트리거 작성하기 ]

- 사원 테이블에 새로운 데이터가 들어오면(즉, 신입 사원이 들어오면) 급여 테이블에 새로운 데이터

(즉 신입 사원의 급여 정보)를 자동으로 생성하도록 하기 위해서 사원 테이블에 트리거를 작성한다.

즉, 신입사원의 급여는 일괄적으로 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;
-- 1부터 시작해서 1씩 증가하는 SEQUENCE이다.

-- 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;
/
-- EMP01에 데이터가 INSERT 된 후(AFTER)에  <-- 우리가 작성한 트리거 내용
-- SAL01테이블에 값(SALNO, 급여 100, )을 INSERT 해라 라는 의미의 트리거이다.
-- :NEW.EMPNO  는 EMP01에 들어온 새로 입력된 ROW(데이터) 중에 EMPNO를 넣어라 라는 의미
-- :OLD 는 새로 입력된 데이터 이전의 ROW(데이터)


-- 4. 사원 테이블에 로우를 추가한다.
INSERT INTO EMP01 VALUES(2, '전수빈', '프로그래머');
SELECT * FROM EMP01;
SELECT * FROM SAL01;
-- 트리거에 의해 EMP01에 데이터를 추가했더니 SAL01테이블에 자동으로 값이 추가된다.

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

 

 

 

 

 

* SAL01 테이블을 SELECT 했을 때,

  EMPNO 칼럼에 나오는 데이터 해당하는 것은

  EMP01 테이블에 추가한 데이터 중 EMPNO 값이 된다.

  EMP01 테이블에 (EMPNO, ENAME, JOB) 형식으로 데이터가 추가된다.

  즉, SAL01의 첫번째 ROW는

  EMP01에 (2, '전수빈', '프로그래머') 데이터를 추가한 뒤

  SAL01에 자동으로 들어간 ROW 데이터값이다.

 

 

 

 

[ 실습 : 급여 정보를 자동으로 삭제하는 트리거 작성하기 ]

- 사원이 삭제되면 그 사원의 급여 정보도 자동 삭제되는 트리거를 작성한다.

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

-- 아래 쉅 내용 코드 --
- 실습 : 급여 정보를 자동으로 삭제하는 트리거 작성하기
-- 사원이 삭제(EMP01 테이블에서 데이터 삭제)되면
-- 그 사원의 급여 정보도 자동으로 삭제되도록
-- 트리거를 작성해보자.

SELECT * FROM EMP01;

-- 1. 사원 테이블의 로우를 삭제한다.
DELETE FROM EMP01 WHERE EMPNO=2;
-- SQL Error(ORA-02292: integrity constraint (SCOTT.SYS_C007054) violated - child record found)가 발생한다.
-- SLA01 테이블에서 EMPNO 칼럼의 값 중 EMPNO=2의 값이 EMP01의 EMPNO=2를 참조하고 있어서
-- EMP01에서 EMPNO=2의 값을 지울 수가 없다.
-- SAL01에서 EMPNO=2의 데이터를 지우고 나서 EMP01테이블에서 EMPNO=2 데이터를 지우면 된다.


-- 2. 트리거를 작성한다.
CREATE OR REPLACE TRIGGER TRG_03
    AFTER DELETE ON EMP01
    FOR EACH ROW
BEGIN
    DELETE FROM SAL01 WHERE EMPNO=:OLD.EMPNO;
    -- SLA01 테이블의 EMPNO가 지우려고 하는 EMPNO 넘버 (:OLD.EMPNO) 를 지워라
END;
/


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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

---

 

[ 과제-21-02.TXT ]

Q. 부서 번호를 전달하여 해당 부서 소속 사원의 정보를 출력하는 SEL_EMP 프로시저를 커서를 사용하여 작성하라.

 

 

* 커서 CURSOR를 만들 때 FOR LOOP 방법으로 만들자!!

  코드가 완전 간결해진다!! :-)

 

 

 

A1. 커서(CURSOR)가 FOR LOOP 방식이 아닐 때,

-- FOR LOOP 방식이 아닐때
SELECT * FROM EMP;
-- EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

CREATE OR REPLACE PROCEDURE SEL_EMP(VDEPTNO EMP.DEPTNO%TYPE)
IS
    VEMP EMP%ROWTYPE;
    CURSOR C1
    IS
    SELECT * FROM EMP
    WHERE DEPTNO = VDEPTNO;
BEGIN
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원명 / 직급 / 급여');
    DBMS_OUTPUT.PUT_LINE('------------------------------------');
    
    OPEN C1;
    
    LOOP
        FETCH C1 INTO VEMP.EMPNO, VEMP.ENAME, VEMP.JOB, VEMP.MGR, VEMP.HIREDATE, VEMP.SAL, VEMP.COMM, VEMP.DEPTNO;
        
        EXIT WHEN C1%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(VEMP.EMPNO || '  ' || VEMP.ENAME || '  ' || VEMP.JOB || '  ' || VEMP.SAL);
    END LOOP;
    
    CLOSE C1;
END;
/


EXECUTE SEL_EMP(20);

 

[ 커서 FOR LOOP 방식으로 안 풀었을 때 ]

 

 

* FOR LOOP 아닌 방식으로 커서를 만들 때,

  가져오려는 데이터를 담을 변수 VEMP의 데이터타입을

  ROWTYP으로 사용하려면

  FETCH 구문에서 행의 모든 데이터들(EMP 테이블에 있는 칼럼들)을

  (EMPNO, ENAME, KOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

  적어줘야 코드 실행이 된다.

 

 

 

 

A2. 커서(CURSOR)가 FOR LOOP 방식일 때,

-- FOR LOOP로 풀어보기
SELECT * FROM EMP;
-- EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

CREATE OR REPLACE PROCEDURE SEL_EMP(VDEPTNO EMP.DEPTNO%TYPE)
IS
    VEMP EMP%ROWTYPE;
    CURSOR C1
    IS
    SELECT * FROM EMP
    WHERE DEPTNO = VDEPTNO;
BEGIN
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원명 / 직급 / 급여');
    DBMS_OUTPUT.PUT_LINE('------------------------------------');
    
    FOR VEMP IN C1 LOOP
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(VEMP.EMPNO || '  ' || VEMP.ENAME || '  ' || VEMP.JOB || '  ' || VEMP.SAL);
    END LOOP;
END;
/


EXECUTE SEL_EMP(20);

 

[ 커서 FOR LOOP 방식으로 만들었을 때 ]

 

 

 

 

 

 

 

 

 

2. 트리거 삭제

- DROP TRIGGER 다음에 삭제할 트리거 명을 기술한다.

DROP TRIGGER TRG_02;

 

 

 

[ 실습 : 급여 정보를 자동 추가하는 트리거 제거하기 ]

1. 급여 정보를 자동 추가하는 트리거인 TRG_02를 제거해 본다.

2. TRG_02 트리거를 삭제하고 난 후에 사원 테이블에 행을 추가했더니 급여가 자동적으로 입력되지 않는 것을 확인할 수 있다.

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

 

 

3. 예제를 통한 트리거의 적용

- 상품 테이블의 예제를 통해서 실질적인 트리거의 적용 예를 살펴보도록 하자.

 

[ 그림 : 상품, 입고 ERD]

 

 

 

[ 실습 : 입고 트리거 작성하기 ]

- 입고 테이블에 상품이 입력되면 입고 수량을 상품 테이블의 재고 수량에 추가하는 트리거를 작성해보자.

-- 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, 'A00004', 5, 220, 110);
SELECT * FROM 입고;
SELECT * FROM 상품;

 

 

 

[ 실습 : 갱신 츠리거 작성하기 ]

- 이미 입고된 상품에 대해서 입고 수량이 변경되면 상품 테이블의 재고수량 역시 변경되어야 한다.

이를 위한 갱신 트리거를 작성한다.

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


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

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 상품;

 

	저장 프로시저	트리거	저장함수
공통점	오라클의 경우, PL/SQL로 작성
정의 방법	CREATE PROCEDURE 문	CREATE TRIGGER 문	CREATE FUNCTION 문
호출 방법	EXEC문으로 직접 호출	INSERT, DELETE, UPDATE문이 실행될 때 자동으로 실행됨	SELECT문으로 호출
기능의 차이	SQL문으로 할 수 없는 복잡한 로직을 수행	기본 값 제공, 데이터 제약 준수, SQL 뷰의 수정, 참조무결성 작업 등을 수행	속성 값을 가공하여 반환, SQL문에 직접 사용

[ 표 : 프로시저, 트리거, 사용자 정의 함수의 공통점과 차이점 ]