PROCEDURE(프로시저) / FUNCTION(함수) / CURSOR(커서) / TRIGGER (트리거) < 2021-09-29 >

목차 

(순서 마지막에 예제 있음)

+ PROCEDURE (프로시저)

+ FUNCTION (함수)

+ CURSOR (커서)

+ TRIGGER (트리거)

+ 예제

---

PROCEDURE (프로시저)

- PL / SQL문을 저장하는 객체

- 필요할 때마다 복잡한 구문을 다시 입력할 필요 없이 간단하게 호출해서 실행 결과를 얻을 수 있다.

- 특정 로직을 처리하고 결과값을 반환하지 않음 (반환 할 수도 있음)

=> FUNCTION(함수)와의 차이점 

-- ** PROCEDURE의 생성

[표현식]
CREATE OR REPLACE PROCEDURE 프로시저명
	(매개변수명1 [ IN | OUT | IN OUT ] 데이터타입 [ := DEFAULT값 ],
     매개변수명1 [ IN | OUT | IN OUT ] 데이터타입 [ := DEFAULT값 ],
     ...
    )
IS
	선언부
BEGIN
	실행부
[EXCEPTION
	예외처리부]
END [프로시저명];
/


-- ** PROCEDURE의 실행
EXECUTE 프로시저명;
EXEC 프로시저명;


-- ** IN / OUT 매개변수, 바인드 변수(VARIABLE or VAR)
IN  : 프로시저 내부에서 사용될 변수
OUT : 프로시저 외부(호출부)에서 사용될 변수
==> 바인드 변수를 활용해 표시해야함

VARIABLE(VAR) : SQL문을 실행할 때 SQL에 사용 값을 전달할 수 있는 통로 역할을 하는 변수
			    프로시저 실행 시 조회 결과가 저장될 바인드 변수를 생성
                [작성법] VARIABLE 변수명 자료형;
       
       
-- ** 바인드 변수가 있는 PROCEDURE의 실행
EXEC 프로시저명 (매개변수 , :결과저장변수1(VAR) , :결과저장변수2(VAR) , ...);

 

---

FUNCTION (함수)

- 프로시저와 사용 용도가 거의 비슷하나, OUT 매개변수를 사용하지 않아도 실행 값을 되돌려 받을 수 있음. (RETURN)

* PROCEDURE / FUNCTION의 차이

- 실행 후 반환값이 있을 수도, 없을 수도 있음 ==> PROCEDURE

- 실행 후 반환값이 있음 ==> FUNCTION

-- ** FUNCTION 생성

CREATE OR REPLACE FUNCTION 함수명 (매개변수1 매개변수타입, ...)
RETURN 데이터타입
IS
	선언부
BEGIN
	실행부
    RETURN 반환값; -- PROCEDURE와 다르게 RETURN 구문 추가
[EXCEPTION    
    예외처리부]
END [함수명];
/


-- ** FUNCTION 사용
1) 프로시저처럼 사용 (VAR , EXEC 사용)
EXEC :바인드변수 := 함수명(매개변수);

2) SQL문에서 단일함수, 그룹함수처럼 사용
SELECT 함수명
FROM 테이블;

---

CURSOR (커서)

- SELECT문 처리 결과( 처리 결과가 여러 행(ROW) )를 담고있는 메모리 공간에 대한 포인터

- 커서 사용 시 여러 ROW로 나타난 처리 결과에 순차적으로 접근 가능

 ( SELECT 결과가 단일행 => INTO절을 이용해 변수에 저장 가능

   SELECT 결과가 다중행 => INTO절 이용한 처리 불가 => CURSOR를 이용한 행단위 처리 )

-- * 커서의 사용 방법 4단계 *
1) CURSOR -- 커서 선언
2) OPEN   -- 커서 오픈
3) FETCH  -- 커서에서 데이터 추출
4) CLOSE  -- 커서 닫기

 

* 커서의 종류

 ** 묵시적 커서 (IMPLICIT CURSOR)
	- 오라클에서 자동으로 생성되어 사용하는 커서
    - PL/SQL 블록에서 실행하는 SQL문 실행 시 마다 자동으로 만들어져 사용된다.
    - 사용자는 생성 유무를 알 수 없지만 , 커서 속성을 활용해 정보를 얻어 올 수 있다.
    
 ** 커서 속성
	(묵시적 커서 속성 정보 참조 시 커서명 = SQL)
    - 커서명%ROWCOUNT : SQL 처리 결과로 얻어온 ROW 수 (0에서 시작 FETCH마다 1증가)
    - 커서명%FOUND    : 커서 영역의 ROW 수가 한 개 이상일 경우 TRUE 아니면 FALSE
    - 커서명%NOTFOUND : 커서 영역의 ROW 수가 없으면 TRUE 있으면 FALSE
    - 커서명%ISOPEN   : 커서가 OPEN 상태인 경우 TRUE (묵시적 커서는 항상 FALSE)
    
    
 ** 명시적 커서 (EXPLICIT CURSOR)
	- 사용자가 직접 선언해서 사용할 수 있는 이름 있는 커서
    [작성법]
    	CURSOR 커서명 IS [ SELECT문 ]
    	OPEN 커서명;
        FETCH 커서명 INTO 변수;
        CLOSE 커서명;

---

TRIGGER (트리거)

- 테이블이나 뷰가 INSERT, UPDATE, DELETE 등의 DML문에 의해 변경될 경우 (테이블 이벤트 발생 시)

  자동으로(묵시적) 실행될 내용을 정의하여 저장하는 객체 (PROCEDURE)

* TRIGGER의 종류

- SQL 실행 시기에 따른 분류

1) BEFORE TRIGGER : SQL문 실행 전 트리거 실행

2) AFTER TRIGGER   : SQL문 실행 후 트리거 실행

- SQL 문에 의해 영향을 받는 각 ROW에 따른 분류

1) ROW TRIGGER

- SQL문 각 ROW에 대해 한번씩 실행함

- 트리거 생성 시 FOR EACH ROW 옵션 작성

- : OLD   : 참조 전 열의 값 (INSERT: 입력 전 자료, UPDATE : 수정 전 자료, DELETE : 삭제할 자료)

- : NEW   : 참조 후 열의 값 (INSERT : 입력 할 자료, UPDATE : 수정 할 자료)

2) STATEMENT TRIGGER

- SQL문에 대해 한번만 실행 (DEFAULT TRIGGER)

 

* TRIGGER 생성

[표현식]

CREATE [OR REPLACE] TRIGGER 트리거명
BEFORE | AFTER 
INSERT | UPDATE | DELETE
ON 테이블명
[FOR EACH ROW] -- ROW TRIGGER 옵션
[WHEN 조건]

DECLARE
	선언
BEGIN
	실행
[EXCEPTION]
END;
/

---

예제 ) 

- 기존에 사용하던 EMPLOYEE 테이블과 그 컬럼값들을 재사용할 예정

1. IN/OUT 매개변수가 있는 PROCEDURE (프로시저) 예제

사번을 이용해 사원의 이름, 급여, 보너스 조회하는 프로시저 생성

SET SERVEROUT ON;

CREATE TABLE TEST_EMP AS
SELECT * FROM EMPLOYEE;
-- EMPLOYEE 테이블을 복사한 TEST_EMP 테이블 생성

CREATE OR REPLACE PROCEDURE SELECT_EMP_ID (	
    P_EMP_ID   IN TEST_EMP.EMP_ID%TYPE,
    
    P_EMP_NAME OUT TEST_EMP.EMP_NAME%TYPE,
    P_SALARY   OUT TEST_EMP.SALARY%TYPE,
    P_BONUS    OUT TEST_EMP.BONUS%TYPE  
)
IS
BEGIN
	SELECT EMP_NAME , SALARY , NVL(BONUS , 0)
    INTO P_EMP_NAME , P_SALARY , P_BONUS -- OUT 매개변수
    FROM EMPLOYEE
    WHERE EMP_ID = P_EMP_ID; -- IN 매개변수
END;
/

-- 바인드 변수 생성
VAR RESULT_NAME VARCHAR2(30);
VAR RESULT_SALARY NUMBER;
VAR RESULT_BONUS NUMBER;

EXEC SELECT_EMP_ID( '200', :RESULT_NAME, :RESULT_SALARY, :RESULT_BONUS);
-- 사번이 200인 사원의 이름, 급여, 보너스
-- 출력 명령을 작성하지 않았으므로 결과가 아직 나타나지 않음

PRINT RESULT_NAME;
PRINT RESULT_SALARY;
PRINT RESULT_BONUS;

-- SET AUTOPRINT 설정으로 바로 출력되게 변경 가능
SET AUTOPRINT ON;
EXEC SELECT_EMP_ID( '&사번', :RESULT_NAME, :RESULT_SALARY, :RESULT_BONUS);

2. FUNCTION (함수) 예제

사번을 입력 받아 해당 사원의 연봉을 계산하고 리턴하는 함수 생성

CREATE OR REPLACE FUNCTION ANNUAL_SALARY (
	F_EMP_ID EMPLOYEE.EMP_ID%TYPE
)
RETURN NUMBER
IS	-- 조회 결과 및 계산 결과를 저장하는 변수 선언부
	RESULT_SALARY EMPLOYEE.SALARY%TYPE;
    RESULT_BONUS  EMPLOYEE.BONUS%TYPE;
    
    ANNUAL_SAL  NUMBER;
BEGIN -- 실행부
	SELECT SALARY , NVL(BONUS , 0)
    INTO RESULT_SALARY, RESULT_BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = F_EMP_ID; -- 매개변수
    
    -- 연봉 계산
    ANNUAL_SAL = RESULT_SALARY * (1 + RESULT_BONUS) * 12;
    
    RETURN ANNUAL_SAL;
END;
/

-- 1번 방법
VAR RESULT_CALCULATION NUMBER;
EXEC :RESULT_CALCULATION := ANNUAL_SALARY('&사번');

-- 2번 방법
SELECT EMP_ID , EMP_NAME , ANNUAL_SALARY(EMP_ID) AS "연봉"
FROM EMPLOYEE;

3. EXPLICIT CURSOR (명시적 커서) 예제

급여가 300만원 이상인 사원의 사번, 이름, 급여를 출력

DECLARE -- 선언
	-- 명시적 커서 생성
    CURSOR C1 IS
    	SELECT EMP_ID , EMP_NAME , SALARY
        FROM EMPLOYEE
        WHERE SALARY >= 3000000;
    
    -- 변수 선언    
    ID   EMPLOYEE.EMP_ID%TYPE;
    NAME EMPLOYEE.EMP_NAME%TYPE;
    SAL  EMPLOYEE.SALARY%TYPE;
        
BEGIN -- 실행부
	-- 커서 사용을 위한 OPEN
	OPEN C1;
    LOOP
    	FETCH C1 INTO ID, NAME, SAL;
        EXIT WHEN C1%NOTFOUND;
    	-- 커서로부터 추출한 내용이 없으면 TRUE
        DBMS_OUTPUT.PUT_LINE( ID || '/' || NAME || '/' || SAL);
    END LOOP;
    -- 사용 완료한 커서 CLOSE
    CLOSE C1;
END;
/

4. TRIGGER (트리거) 예제

트리거 테스트용 TABLE , SEQUENCE 생성

-- 상품 정보 TABLE
CREATE TABLE PRODUCT (
	PCODE NUMBER PRIMARY KEY,		-- 상품 코드
    PNAME VARCHAR2(200) NOT NULL,	-- 상품명
    BRAND VARCHAR2(60) NOT NULL, 	-- 제조 회사
    PRICE NUMBER NOT NULL,			-- 가격
    STOCK NUMBER DEFAULT 0			-- 재고
);

-- 상품 입/출고 TABLE
CREATE TABLE PROD_IO (
	IO_NO NUMBER PRIMARY KEY,		-- 입/출고 번호 (식별자 용도)
    IO_DATE DATE DEFAULT SYSDATE,	-- 입/출고일
    IO_AMOUNT NUMBER NOT NULL,		-- 입/출고량
    STATUS CHAR(6) CHECK (STATUS IN ('입고','출고')	-- 상태 구분
    PCODE NUMBER REFERENCES PRODUCT	-- 상품 코드 (PRODUCT 테이블과 관계형성)
);

-- SEQUENCE 생성
CREATE SEQUENCE SEQ_PCODE;	-- 상품 번호 생성용
CREATE SEQUENCE SEQ_IO_NO	-- 입/출고 번호 생성용

-- SAMPLE DATA INSERT
INSERT INTO PRODUCT
VALUES ( SEQ_PCODE.NEXTVAL , 'RAM' , 'SAMSUNG' , '80000' , DEFAULT);

INSERT INTO PRODUCT
VALUES ( SEQ_PCODE.NEXTVAL , 'SSD' , 'SAMSUNG' , '200000' , DEFAULT);

INSERT INTO PRODUCT
VALUES ( SEQ_PCODE.NEXTVAL , 'CPU' , 'INTEL' , '150000' , DEFAULT);
    
COMMIT;

* PROD_IO 테이블에 데이터를 입력하면 PRODUCT 재고 값이 자동으로 UPDATE 되는 트리거 생성하기
CREATE OR REPLACE TRIGGER STOCK_UPDATE
AFTER INSERT
ON PROD_IO -- PROD_IO 테이블에 DATA INSERT 한 후에 트리거 실행
FOR EACH ROW -- ROW TRIGGER 옵션
BEGIN
	IF(:NEW.STATUS = '입고') -- :NEW => 새로 INSERT된 행을 가리키는 바인드변수
    THEN
    	UPDATE PRODUCT 
        SET STOCK = STOCK + :NEW.IO_AMOUNT -- 기존 재고에 입력된 값 추가
        WHERE PCODE = :NEW.PCODE;
    ELSE -- '출고'
    	UPDATE PRODUCT
        SET STOCK = STOCK - :NEW.IO_AMOUNT -- 기존 재고에서 입력값 차감
        WHERE PCODE = :NEW.PCODE;
    END IF;
END;
/

-- PCODE가 1인 상품 1000개 입고
INSERT INTO PROD_IO
VALUES(SEQ_IO_NO.NEXTVAL , DEFAULT , 1000 , '입고' , 1 );

-- PCODE가 2인 상품 500개 입고
INSERT INTO PROD_IO
VALUES(SEQ_IO_NO.NEXTVAL , DEFAULT , 500 , '입고' , 2 );

-- PCODE가 1인 상품 200개 출고
INSERT INTO PROD_IO
VALUES(SEQ_IO_NO.NEXTVAL , DEFAULT , 200 , '출고' , 1 );