PL/SQL의 사용법을 익혔다면

이제 사용자가 원하는 어떤 목적을 해결해 줄수 있는 프로그램의 형태로 만들어야 한다

저장 서브프로그램

지금까지 실습한 PL/SQL 블록은 한번 작성하여 바로 실행하는 방식이었다(익명 블록)

실행할때마다 읽어 들어오는 방식이아닌 하나의 작업을 객체로 만들어서 저장후

원할때마다 자유롭게 사용할수 있게끔 만들어주는 서브 프로그램들이 있다

SQL Server의 성능향상 (첫 실행시 컴파일, 재컴파일 안함)

1. 프로시저

특정 처리작업 수행을 위한 서브프로그램(SQL문에서 사용불가능)

STORED PROCEDURE

프로시저는 반환값이 없어서 SELECT 안에 넣을수 없다

- 문법

CREATE [OR REPLACE] PROCEDURE 이름 
IS AS 
BEGIN
실행부
EXCEPTION
END;

- 프로시저 만들기

-- DECLARE 부분이 IS라고 보면 된다

CREATE OR REPLACE PROCEDURE pro_noparam
IS
   V_EMPNO NUMBER(4) := 7788;
   V_ENAME VARCHAR2(10);
BEGIN
   V_ENAME := 'SCOTT';
   DBMS_OUTPUT.PUT_LINE('V_EMPNO : ' || V_EMPNO);
   DBMS_OUTPUT.PUT_LINE('V_ENAME : ' || V_ENAME);
END;

--지금 프로시저는 V_EMPNO 와 V_ENAME를 출력하는 프로시저다

- 만들어진 프로시저를 실행하기

EXECUTE pro_noparam;

EXEC pro_noparam;             -- 인수없는 경우 호출
EXEC pro_noparam(값, 값,....); -- 인수있는 경우 호출
-- EXEC 만 쓰는게 편하다

BEGIN
pro_noparam;	-- BEGIN문 안에 넣을수 있다
END;

- 프로시저 확인하기

 ※ 주의 : 데이터 사전은 대문자로 값을 저장하기 때문에 대문자로 검색하자

SELECT *
FROM USER_SOURCE
WHERE NAME = 'PRO_NOPARAM';

--프로시저를 만들면 제약조건을 확인하는것 마냥 USER_SOURCE 테이블에 저장된다

SELECT TEXT
FROM USER_SOURCE
WHERE NAME = 'PRO_NOPARAM';

- DROP PROCEDURE

DROP PROCEDURE pro_noparam;

- ALTER PROCEDURE?

CREATE OR REPLACE PROCEDURE pro_noparam
IS
   V_EMPNO NUMBER(4) := 7788;
   V_ENAME VARCHAR2(10);
BEGIN
   V_ENAME := 'SCOTT';
   DBMS_OUTPUT.PUT_LINE('V_EMPNO : ' || V_EMPNO);
   DBMS_OUTPUT.PUT_LINE('V_ENAME : ' || V_ENAME);
END;

-- 생성할때랑 똑같다?
--CREATE OR REPLACE 자체가 없으면 만들고 있으면 수정하라 라는 뜻이기 때문에 수정할때도 똑같이
--사용하면 기존에 존재하는 프로시저는 수정, 없는 프로시저는 생성이 자동으로 된다

- 프로시저생성시 DEFAULT값의 변수명이 있다면?

--프로시저를 생성할때의 생성자 오버로딩처럼 ()안에 참조값이 들어가있다
CREATE OR REPLACE PROCEDURE pro_param_in
(
   param1 IN NUMBER, -- 지정하지 않으면 기본값이고 프로시저 호출할때 값을 입력해야한다
   param2 NUMBER,
   param3 NUMBER := 3,	-- 초기값이 들어가있다
   param4 NUMBER DEFAULT 4
)
IS

BEGIN
   DBMS_OUTPUT.PUT_LINE('param1 : ' || param1);
   DBMS_OUTPUT.PUT_LINE('param2 : ' || param2);
   DBMS_OUTPUT.PUT_LINE('param3 : ' || param3);
   DBMS_OUTPUT.PUT_LINE('param4 : ' || param4);
END;
/

EXEC pro_param_in; -- 호출이 안됨

EXEC pro_param_in(10,9,8,7); -- DEFAULT없이 모든 변수에 값이 들어가게 되면서 출력이 가능해진다

EXEC pro_param_in(10,9); -- DEFAULT 값에만 값이 들어가게 되면서 출력가능

EXEC pro_param_in(param1 => 10, param2 => 20); -- => 배정연산자 이다 
-- 특정 변수를 직접 지정해서 값을 넣어줄수도있고 아니면 생성할 당시의 순서대로 값을 넣을수도 있다

- OUT 모드 파라미터

프로시저 실행후 호출한 프로그램으로 값을 반환

사원번호를 입력받아 사원이름과 사원급여를 반환하는 pro_param_out 프로시저 작성

CREATE OR REPLACE PROCEDURE pro_param_out
(
   in_empno IN EMP.EMPNO%TYPE,      -- 3개의 파라미터값을 만들었는데 
   out_ename OUT EMP.ENAME%TYPE,    -- IN, OUT이 뭘까?
   out_sal OUT EMP.SAL%TYPE
)
IS

BEGIN
   SELECT ENAME, SAL INTO out_ename, out_sal
     FROM EMP
    WHERE EMPNO = in_empno;
END pro_param_out;
/


-- 실행 해보자

DECLARE
   v_ename EMP.ENAME%TYPE;  -- 반환되는 DATA를 받기위해 변수를 생성했다
   v_sal EMP.SAL%TYPE;
BEGIN
   pro_param_out(7788, v_ename, v_sal);     -- 7788은 프로시저를 불러올때 넣어줘야하는 값
   DBMS_OUTPUT.PUT_LINE('ENAME : ' || v_ename); --v_ename, v_sal 은 값을 받아오기때문에(OUT)
   DBMS_OUTPUT.PUT_LINE('SAL : ' || v_sal); -- BEGIN절에서 사용하려고 만든 변수명
END;
/

• 아하 OUT을 이용하면 값을 받을수도 있구나
  그리고 그값을 받아서 출력하거나 새로 사용할수 있구나
  OUT을 쓰려면 참조값에 리턴받을 변수명을 써야하고
  IN을 쓰려면 참조값에 넣어야하는 데이터값을 쓰면 된다

-- IN OUT 이 둘다 있는경우?

CREATE OR REPLACE PROCEDURE pro_param_inout
(
   inout_no IN OUT NUMBER
)
IS

BEGIN
   inout_no := inout_no * 2;
END pro_param_inout;	-- -> 이분이 다른 문법과는 다르다 처음으로 END뒤에 무언가 나왔다
/


--실행하기

DECLARE
num1 NUMBER;

BEGIN
num1 := 5;
pro_param_inout(num1);
DBMS_OUTPUT.PUT_LINE(num1);		-- 값이 10이나오면 정상작동 한것이다
END;

• 값을 넣었더니 값을 리턴받았다 동시에 두가지를 사용할 수도 있다

2. 함수

특정 연산을 거친 작업의 결과를 반환하는 서브프로그램(SQL문에서 사용가능)
PROCEDURE보다 가용성이 높음

1. 내장함수(BUILT_IN FUNCTION)
2. 사용자 정의 함수(USER DEFINED FUNCTION)

- PROCEDURE 과 함수의 차이점

1. 실행
   변수를 사용한 EXECUTE 명령어
   SQL문에서 직접 실행가능
   서브프로그램 내에서도 실행가능

2. 파라미터 지정
   IN모드만 사용가능(혹은 생략가능)

3. 출력값
   RETURN절 또는 RETURN문을 통해 반환 (절,문 같은 의미다)
   반드시 하나의 값을 반환해야 한다

- 문법

CREATE [OR REPLACE] FUNCTION 이름
(파라미터 이름1 [IN] 자료형1.
파라미터 이름2 [IN] 자료형2...)
RETURN 자료형
IS | AS
선언부
BEGIN
실행부
RETURN(반환값);
EXCEPTION
예외처리부
END[함수이름];

• 예제

SET SERVEROUTPUT ON;

CREATE OR REPLACE FUNCTION func_aftertax(sal IN NUMBER)
RETURN NUMBER
IS
   tax NUMBER := 0.05;
BEGIN
   RETURN (ROUND(sal - (sal * tax)));
END func_aftertax;
/

DECLARE
aftertax NUMBER;
BEGIN
aftertax := func_aftertax(3000);
DBMS_OUTPUT.PUT_LINE('aftertax 값 : ' || aftertax);
END;
/

SELECT func_aftertax(3000) FROM DUAL;	--SQL문 안에서도 사용가능하다

SELECT EMPNO, ENAME, SAL, func_aftertax(3000) AS "AFTER TAX"
FROM EMP;

- DROP FUNCTION

DROP FUNCTION func_aftertax;

• 예제

CREATE OR REPLACE FUNCTION func_date_kor(date1 IN DATE)
RETURN VARCHAR2
IS
BEGIN
    RETURN TO_CHAR(date1, 'YYYY"년"MM"월"DD"일"');
END func_date_kor;
/

SELECT ENAME, func_date_kor(HIREDATE) AS HIREDATE
FROM EMP
WHERE EMPNO = 7369;

3. 패키지

저장 서브프로그램을 그룹화하는 개념

4. 트리거(TRIGGER)

TRIGGER : 방아쇠를 당기다

이벤트가 발생했을때 자동으로 연달아 수행할 기능을 구현
(연속성을 가지는 어떤 동작의 시작점)

- 특징

사용자가 직접 호출하는 것이 아니라 데이터베이스에서
자동으로 호출한다는 것이 가장 큰 특징이다

테이블과 뷰를 대상으로 정의 할 수 있고 전체 트랜잭션 작업에 대한 트리거와
각행을 대상으로 발생하는 트리거가 있다

- 장점

1. 연관된 여러작업을 수행하기때문에 데이터 관련 작업을 더 간편하게 수행가능
2. 제약조건만으로는 구현하기 힘든 복잡한 데이터규칙을 정할 수 있기때문에
   수준높은 데이터 정의가 가능
3. 데이터 변경과 관련된 일련의 정보를 기록함으로써 여러 사용자가 공유하는
   데이터의 보안성과 안정성 문제발생시의 예외상황 처리능력을 높일 수 있다

- Trigger의 종류

1. DML Trigger
2. DDL Trigger
3. INSTEAD OF Trigger -> VIEW
4. SIMPLE Trigger -> timing point
   트리거를 작동시킬 문장이 실행되기 전 시점
   트리거를 작동시킬 문장이 실행된 후 시점
   트리거를 작동시킬 문장이 행에 영향을 미치기 전 시점
   트리거를 작동시킬 문장이 행에 영향을 준 후 시점
5. 복합트리거 -> 단순트리거의 여러 시점에 동작

- 문법

CREATE OR REPLACE TRIGGER 트리거이름		-- 트리거 이름 명시
BEFORE | AFTER	-- 트리거가 작동할 타이밍을 작성(명령이 작동되기 전 or 후)
INSERT | UPDATE | DELETE ON 테이블이름	-- 지정한 테이블의 트리거가 작동할 DML명령어 작성
[REFFERENCING Old AS old | New as new] 	-- DML로 변경되는 행의 전 값과 변경 후의 값을 참조
FOR EACH ROW WHEN 조건식	-- 트리거를 실행하는 DML문장에 대해 한번만 실행할지
-- 아니면 영향받는 행단위로 실행할지 조건식을 지정하여 정한다
[FOLLOWS 트리거이름2, 트리거이름3...] -- 오라클11g 여러관련 트리거의 실행순서를 정함
[ENABLE | DISABLE] -- 오라클11g 트리거의 활성화 비활성화 상태 지정

DECLARE
선언부
BEGIN
실행부
END;

처음 문법을 보면 전혀 이해가 안된다 직접 만들어보면서 천천히 이해해보자

CREATE TABLE EMP_TRG	-- 연습할 테이블을 하나만들자
AS SELECT * FROM EMP;	-- scott 계정

• 예제1

trg_emp_nodml_weekend 트리거
주말에 EMP_TRG 테이블에 DML 명령어를 사용하면 오류를 일으키고
작업한 명령어를 취소하는 트리거

CREATE OR REPLACE TRIGGER trg_emp_nodml_weekend
BEFORE	-- SQL문이 수행되기전에 작동하는 트리거
INSERT OR UPDATE OR DELETE ON EMP_TRG
BEGIN
   IF TO_CHAR(sysdate, 'DY') IN ('토', '일') THEN
      IF INSERTING THEN
         raise_application_error(-20000, '주말 사원정보 추가 불가');
      ELSIF UPDATING THEN
         raise_application_error(-20001, '주말 사원정보 수정 불가');
      ELSIF DELETING THEN
         raise_application_error(-20002, '주말 사원정보 삭제 불가');
      ELSE
         raise_application_error(-20003, '주말 사원정보 변경 불가');
      END IF;
   END IF;
END;
/

주말에는 정보수정이 안되는 트리거를 만들었다

DELETE FROM EMP_TRG
WHERE EMPNO = 7369;	-- 토요일과 일요일은 수정이 불가능해진다

• 예제2

정보를 수정했을때 LOG 기록을 남겨두는 트리거를 만들어보자

--LOG 기록을 담을 테이블 만들기
CREATE TABLE EMP_TRG_LOG(
   TABLENAME VARCHAR2(10), -- DML이 수행된 테이블 이름
   DML_TYPE VARCHAR2(10),  -- DML 명령어의 종류
   EMPNO NUMBER(4),        -- DML 대상이 된 사원 번호
   USER_NAME VARCHAR2(30), -- DML을 수행한 USER 이름
   CHANGE_DATE DATE        -- DML이 수행된 날짜
);

트리거 만들기

CREATE OR REPLACE TRIGGER trg_emp_log   
AFTER	-- SQL문이 수행된 후에 작동하는 트리거
INSERT OR UPDATE OR DELETE ON EMP_TRG
FOR EACH ROW

BEGIN

    IF INSERTING THEN
        INSERT INTO emp_trg_log
        VALUES ('EMP_TRG', 'INSERT', :new.empno,
                     SYS_CONTEXT('USERENV', 'SESSION_USER'), sysdate); 
                     --SYS_CONTEXT 현재 접속 중인 사용자의 정보를 받아올 때 사용

    ELSIF UPDATING THEN
        INSERT INTO emp_trg_log
        VALUES ('EMP_TRG', 'UPDATE', :old.empno,
                     SYS_CONTEXT('USERENV', 'SESSION_USER'), sysdate);
                     
    ELSIF DELETING THEN
        INSERT INTO emp_trg_log
        VALUES ('EMP_TRG', 'DELETE', :old.empno,
                     SYS_CONTEXT('USERENV', 'SESSION_USER'), sysdate);
    END IF;
END;
/
    

트리거가 잘 작동하는지 확인하기

DELETE FROM EMP_TRG
WHERE EMPNO = 7499;		-- 데이터를 하나 삭제

SELECT * FROM emp_trg_log;	-- 로그테이블에 잘 기록이 됬는지 확인

정보수정후 바뀐게 안보인다면 COMMIT을 해보자

:new / :old ?
트리거를 만들때 다른 테이블에서 가져오는 컬럼의 값일경우
써야하는 문법이다
new는 새로변경된 컬럼의 값이고 old는 변경전 컬럼의 값이다
:new.컬럼명 의 형태로 사용한다