PL/SQL_프로시저/트리거/함수

PL(Procedural Language) / SQL(=절차형 sql)

: SQL에도 절차 지향적인 프로그램이 가능

: SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장모듈 생성 가능 

 

 

* 구조

DECLARE 
  변수 선언

BEGIN
  시작 : 실행
 
EXCEPTION
   예외처리
   
END;

DECLARE : 프로시저의 명칭, 변수와 인수 데이터 타입 선언부

BEGIN/END : 프로스저의 시작과 종료

EXCEPTION : BEGIN~END절에서 실행되는 SQL문이 실행될 때 예외 발생 시 예외 처리 방법 정의

 

 

 

 

 

 

 

 

---

 

 

-1) 프로시저

① 절차형 SQL을 활용하여 특정 기능을 수행할 수 있는 트랜잭션 언어

② 한 번에 처리할 수 있는 쿼리문의 집합

③ 기존 프로시저를 덮어쓰기 하기 위해 CREATE OR REPLACE PROCEDURE 사용

④ 프로시저 호출을 통해 실행

 

* 구성 

CREATE OR REPLACE PROCEDURE PROCEDURE_NAME[PARAMETERS]
IS(AS)

BEGIN

EXCEPTION

END;

-- 호출
EXECUTE PROCEDURE_NAME[PARAMETERS];

* 파라미터의 종류

① IN : 호출하는 곳에서 함수, 프로시저로 값을 전달

② OUT : 함수, 프로시저에서 호출한 곳으로 값을 전달 

③ IN OUT : 호출하는 곳에서 함수, 프로시저로 값을 전달하고 동시에 함수, 프로시저에서 호출한 곳으로 값을 전달

 

---

 

-2) 트리거

① 특정 테이블에 삽입, 수정, 삭제(DML) 등의 데이터 변경 이벤트가 발생하면 DBMS에서 자동적으로 실행되도록 구현된 프로그램 

② DML 작업 수행 직전에 실행되는 BEFORE /  직후에 실행되는 AFTER TRIGGER

 

* 구성

CREATE OR REPLACE TRIGGER TRIGGER_NAME

AFTER(OR BEFORE)               -- 수정 후 OR 전
INSERT OR UPDATE OR DELETE     -- 트리거가 동작할 DML 선택
ON 테이블명 
FOR EACH ROW                    -- 행 단위


BEGIN


END;

 

 

 

 

---

-3) 함수

- 프로시저와 비슷한 구성 

① 반환 값이 있다.(RETURN : 값은 하나만)

② 쿼리문에서 함수를 호출해서 사용

CREATE OR REPLACE FUNCTION [FUNCTION_NAME](PARAMETER IN/OUT TYPE)

IS

BEGIN

END;

--호출 
SELECT FUNCTION_NAME(PARAMETERS) FROM DUAL;

 

 

 

-- 예제

CREATE OR REPLACE FUNCTION FUNC(SCORE NUMBER)   -- 매개변수 SCORE
RETURN CHAR   -- 반환 타입 

AS
   GRADE CHAR(1);

BEGIN
   IF SCORE >= 90 THEN
          GRADE := 'A';
   ELSIF SCORE >= 80 THEN
          GRADE := 'B';
   ELSIF SCORE >= 70 THEN
          GRADE := 'C';
   ELSIF SCORE >= 60 THEN
          GRADE := 'D';
   ELSE 
          GRADE := 'E';
   END IF;
   RETURN GRADE;    -- 반환처리 
   
END FUNC;


-- FUNC 함수확인 
SELECT FUNC(40) FROM DUAL;