15. PL/SQL - 함수/프로시저/트리거

프로시저

- 특정 작업을 수행하는 이름이 있는 PL/SQL 블록

- 매개변수를 받을 수 있고 반복적으로 사용할 수 있다.

- 연속실행, 구현이 복잡한 트랜잭션을 수행하는 PL/SQL 블록을 데이터베이스에 저장하기 위해 생성

 

 

저장 프로시저(Stored Procedure) 

오라클이 만들어 놓은 PL/SQL 블록을 저장해 놓고 필요한 경우 호출하여 사용하기 위해 제공하는 것.

→ 복잡한 DML문들이 필요할 때마다 다시 입력할 필요 없이 간단히 호출만 하면 되며 성능향상과 호환성 문제도 해결해준다.

-- 형식
CREATE[OR REPLACE] PROCEDURE 프로시저이름    ---> 생성: 프로시저를 여러번 반복해 호출하여 사용할 수 있음 

 ( 매개변수1 [MODE] 데이터 타입            ---> 프로시저는 어떤 값을 전달 받아 그 값에 의해 서로 다른 결과물을 
   매개변수2 [MODE] 데이터 타입 ... )       --  구하게 되는데, 그 때 프로시저에 값을 전달받을  매개변수 선언
IS                                          --  MODE: IN(입력), OUT(출력), INOUT(두가지 모두) --생략 시 IN
지역변수 선언                                          
BEGIN 
 실행문1;
 실행문2;
  -- ...
END;


-- 프로시저 실행
EXECUTE 프로시저이름

 

예제: 사원테이블에 저장된 사원을 삭제하는 프로시저 작성

CREATE OR REPLACE PROCEDURE  del_all_proc
(vename emp_table2.ename%type)
IS
BEGIN 
DELETE FROM emp_table2 WHERE ename = vename;
COMMIT;
END;

EXECUTE del_all_proc('test');

 

예제2: 사원테이블에 저장된 사원을 출력하는 프로시저 작성

CREATE OR REPLACE PROCEDURE sel_empno
(
vempno IN emp_table.empno%type,
vename OUT emp_table.ename%type,
vsal         OUT emp_table.sal%type,
vjob        OUT emp_table.job%type
)
IS
BEGIN
SELECT ename, sal, job INTO vename, vsal, vjob
FROM emp_table
WHERE empno = vempno;
END;

VARIABLE var_ename VARCHAR2(10);
VARIABLE var_sal NUMBER;
VARIABLE var_job VARCHAR2(9);
EXECUTE sel_empno(7844, :var_ename, :var_sal, :var_job);

PRINT var_ename var_sal var_job;

 

 

---

함수

 

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

1. 저장 프로시저는 매개변수와 달리 IN, OUT, IN OUT 등을 사용할 수 없다. ↔ 함수는 모두 입력 매개변수로 사용

2. 저장 프로시저는 별도의 변환 구문이 없으며 필요하다면 여러개의 OUT 매개변수를 사용한다.

     ↔ 함수는 return문으로 반활할 값의 데이터형식 지정하며 본문 안에서 return문으로 하나의 값을 반환해야 한다.

3. 저장 프로시저는 EXECUTE로 호출 ↔ 함수는 EXECUTE뿐만 아니라 SELECT문장 안에서도 호출된다.

    → 저장 함수는 저장 프로시저와 거의 유사한 용도로 사용하나, 함수는 실행결과를 되돌려 받을 수 있다.

-- 형식
CREATE[OR REPLACE] FUNCTION 함수이름
( 매개변수1 [MODE] 데이터 타입..)
RETURN 데이터타입; 
IS
BEGIN 
-- 실행문장;
RETURN 매개변수이름;
END;

예제: 급여의 200%를 특별 보너스로 지급하기 위한 저장함수 작성

CREATE OR REPLACE FUNCTION cal_bonus_func
(vempno IN emp_table.empno%type)
 RETURN NUMBER
IS
 VSAL NUMBER(7,2);
BEGIN
 SELECT sal INTO vsal FROM emp_table WHERE empno = vempno;
 RETURN (vsal * 200);
END;

VARIABLE var_res NUMBER;
EXECUTE :var_res := cal_bonus_func(7788);

PRINT var_res

 

---

트리거

 

1. 트리거란?

: 특정 테이블이 변경되면 이를 이벤트(계기)로 다른 테이블이 자동으로(연속적으로) 변경되도록 하기 위해 사용

→ 특정 동작을 계기로 그로 인해서만 실행되는 프로시저의 일종임.

* 트랜잭션과 비슷(A이 B에게 송금 = A통장 출금 B통장 입금)

​

2. 형식

CREATE TRIGGER 트리거이름
[triming: BEFORE/AFTER]  [event: INSERT/UPDATE/DELETE]
ON 테이블이름
[FOR EACH ROW]
[WHEN conditions]
BEGIN
실행문
END

① 트리거의 타이밍

- BEFORE: 테이블에 INSERT, UPDATE, DELETE문이 실행 될 때 해당 문장이 실행되기 전 트리거가 가지고 있는

BEGIN ~ END 사이 문잘 실행

- AFTER: INSERT, UPDATE, DELETE문이 실행되고 난 후 트리거가 가지고 있는 BEGIN ~ END 사이 문장 실행

 

② 트리거의 이벤트

- 사용자가 어떤 DML(INSERT/UPDATE/DELETE)문을 실행했을 때 트리거를 발생시킬 것인지 결정

​

③ 트리거 몸체

- 해당 타이밍에 해당 이벤트가 발생하면 실행될 기본 로직이 포함되는 부분으로 BEGIN ~ END에 기술

​

3. 트리거 유형

: FOR EACH ROW에 의해 문장 레벨 트리거와 행 래벨 트리거로 나눈다. (생략- 문장레벨 / 작성- 행레벨)

- 문장 레벨 트리거(기본값: 생략 시) → DML문을 실행할 때 단 한번만 트리거를 발생시킴(행이 하나일 때)

- 행 레벨 트리거 → DML문에 의해 여러 행이 변경된다면 각 행이 변경될 때마다 트리거를 발생시킴(행이 여러개일 때)

* 트리거의 조건은 행레벨 트리거에서만 설정 가능하며, 구체적인 데이터 검색조건을 부여할 때 사용한다.

​

4. 트리거의 사용

- 어떤 테이블의 데이터를 특정 사용자가 변경하려 할 때

- 해당 데이터나 사용자 기록을 확인 혹은 상황에 따라 변경하지 못하도록 막는 것이 가능

- 데이터 와 연관된 여러 작업을 수행하기 위해 일일히 PL/SQL문 또는 서브프로그램(프로시저)을 실행해야 하는 번거로움 줄임

- 제약조건만으로 구현이 어렵거나 불가능한 복잡한 데이터 규칙을 정할 수 있음

- 데이터 변경과 관련된 일련의 정보 기록 가능

 

예제: emp01 테이블에 새로운 사원 정보가 입력되면 나올 문장 설정하기

CREATE OR REPLACE TRIGGER newemp_trg
AFTER INSERT
ON emp01
BEGIN 
DBMS_OUTPUT.PUT_LINE('새로운 사원 정보가 추가되었습니다.');
END;

INSERT INTO emp01 VALUES(1, 'AMY', 'SALESMAN');

 

예제2: emp_table에 새로운 사원 정보가 입력되면 급여테이블에 새로운 데이터(새 사원의 급여정보)를 생성하도록 하는

트리거 작성. 단, 신입사원 급여는 일괄적으로 1000으로 설정

 

-- 1. 급여를 저장할 테이블 생성 
CREATE TABLE sal01(
salno NUMBER(4) PRIMARY KEY,
sal NUMBER(7,2),
empno NUMBER(4) REFERENCES emp01(empno));

-- * 오류:  ORA-02270: no matching unique or primary key for this column-list
--          → 참조하려는 emp_table2의 empno에 PRIMARY KEY지정이 안되어 있었음. 


-- 2. 급여번호 자동 생성하는 시퀀스 정의 → 이 시퀀스로부터 일렬번호를 얻어 급여번호에 부여 
CREATE SEQUENCE sal2_salno_seq;


-- 3. 시퀀스로부터 부여받은 일렬번호를 급여정보에 자동추가하는 트리거 작성 
CREATE OR REPLACE TRIGGER newsal_trg
AFTER INSERT
ON emp01
FOR EACH ROW
BEGIN
INSERT INTO sal01 VALUES(sal01_salno_seq.NEXTVAL, 1000, :NEW.empno);
END;
-- * :old : UPDATE/DELETE되기 전의 값 접근 가능
--   :new:  INSERT되는 값, UPDATE된 후의 값 접근 가능  


INSERT INTO emp01 VALUES(2, 'HARRY', 'DESIGNER');

 

예제3: 급여정보를 자동으로 삭제하는 트리거 작성

① 2번 사원의 정보를 emp01테이블에서 삭제해보기

▶ 오류

empno를 sal01테이블에서도 참조하고 있기 때문에 삭제가 불가.

사원이 삭제되려면 급여 정보도 같이 sal02 테이블에서 삭제되어야 한다.

사원의 정보가 삭제될 때 사원의 급여정보도 같이 삭제하는 내용으로 트리거를 작성해야 한다.

 

② emp01테이블에서 사원의 정보가 삭제될 때 sal01테이블에서 급여정보도 같이 삭제되는 트리거 작성

CREATE OR REPLACE TRIGGER empdle_trg
AFTER DELETE ON emp01
FOR EACH ROW
BEGIN 
DELETE FROM sal01 WHERE empno = :OLD.empno;
END;

DELETE FROM emp01 WHERE empno = 2;

 

* 실질적인 트리거의 예제 :

① 테이블 생성: product, input

② 상품이 입고되어 input테이블에 상품이 입력되면 product테이블의 stock에 추가하는 트리거 작성

CREATE OR REPLACE TRIGGER new_input_trg
AFTER INSERT ON input
FOR EACH ROW
BEGIN
UPDATE product
SET stock = stock + :NEW.in_ea
WHERE p_code = :NEW.p_code;
END;

③ input 테이블에 행 추가하여 확인하기

INSERT INTO input(in_no, p_code, in_ea, in_uprice, in_price) VALUES(1, 'A00001', 10, 1000, 10000);
INSERT INTO input(in_no, p_code, in_ea, in_uprice, in_price) VALUES(2, 'A00002', 10, 800, 8000);
INSERT INTO input(in_no, p_code, in_ea, in_uprice, in_price) VALUES(3, 'A00003', 5, 1500, 7500);

 

---

TIP. 테이블에 주석 달기(comment)

1. 주석 만들기

① 테이블에 주석 만들기

-- 형식:  COMMENT ON TABLE 테이블이름 IS '보충설명';
COMMENT ON TABLE product IS '트리거예제- 상품테이블';

② 컬럼 각각에 주석 만들기

-- 형식: COMMENT ON COLUMN 테이블이름.컬럼이름 IS '보충설명';
COMMENT ON COLUMN product.stock IS '트리거예제- 재고';

 

2. 주석 조회

① 테이블 주석 조회

-- 형식
SELECT * 
FROM all_tab_comments
WHERE tablel_name = '테이블이름';

-- 예시
SELECT * 
FROM all_tab_comments
WHERE lower(table_name) = 'product';

 

② 컬럼 주석 조회

-- 형식
SELECT * 
FROM all_col_comments
WHERE table_name = '테이블이름';


-- 예시
SELECT * 
FROM all_col_comments
WHERE lower(table_name) = 'product';

 

3. 주석 수정

① 테이블 주석 수정

-- 형식 COMMENT ON TABLE 테이블이름 IS '수정할 내용';
COMMENT ON TABLE product IS '(트리거예제)상품테이블';

② 컬럼 주석 수정

-- 형식: COMMENT ON COLUMN 테이블이름.컬럼이름 IS​ '수정할 내용';
COMMENT ON COLUMN product.stock IS '(트리거예제)재고';

 

4. 주석 삭제

① 테이블 주석 삭제

-- 형식: COMMENT ON TABLE 테이블이름 IS ''; 
--       * ''은 작은따옴표(') 두 개가 붙어있는 것, 큰따옴표(") 아님.. 
COMMENT ON TABLE product IS '';

② 컬럼 주석 삭제

-- 형식: COMMENT ON COLUMN 테이블이름.컬럼이름 IS '';
COMMENT ON COLUMN product.stock IS '';