프로시저

저장 프로시저(stored procedure)

• 특정 처리 작업을 수행하는 데 사용하는 저장 섭 프로그램

• 용도에 따라 파라미터를 사용할 수 있고 사용하지 않을 수도 있음

• 보통의 프로시저는 저장 프로시저를 의미함

• 이미 존재하면 create가 안 되기 때문에 replace로 교체해 주어야 함

• 프로시저 생성

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

• 프로시저 실행

EXECUTE PRO_NOPARAM;

프로시저를 먼저 만들고, 이후에 만든 이름을 통해서 실행시켜야 함

• PL/SQL 안에서 실행

BEGIN
    PRO_NOPARAM;
END;

프로시저와 함수의 차이

함수 생성

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;

함수 실행

VARIABLE VSAL NUMBER;
EXECUTE :VSAL := FUNC_AFTERTAX(1000);
PRINT VSAL;

트리거

• 매우 중요!!!!
• DB안의 이벤트(특정 상황이나 동작)가 발생할 경우 자동으로 실행되는 기능을 정의하는 PL/SQL 서브프로그램

장점

1. 데이터 관련 작업을 좀 더 간편하게 수행할 수 있음(서브프로그램을 일일이 실행해야 하는 번거로움 줄임)
2. 제약 조건(constraints)만으로 구현이 어렵거나 불가능한, 좀 더 복잡한 데이터 규칙을 정할 수 있어 더 수준 높은 데이터 정의가 가능함
3. 여러 사용자가 공유하는 데이터 보안성과 안정성, 문제 발생 시의 대처 능력을 높일 수 있음 -> 데이터 변경과 관련된 일련의 정보 기록이 가능하기 때문

단점

1. 특정 작업이나 이벤트가 발생하면 다른 데이터 작업을 추가로 실행하기 때문에 무분별하게 사용하면 DB의 성능을 떨어뜨리는 원인이 됨

동작 지정

• 트리거는 테이블, 뷰, 스키마, DB 수준에서 이벤트에 동작을 지정할 수 있음
• 데이터 조작어(DBL) : INSERT, UPDATE, DELETE
• 데이터 정의어(DDL) : CREATE, ALTER, DROP
• 데이터베이스 동작 : SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN

제작 및 사용(BEFORE)

CREATE TABLE EMP_TRG
    AS SELECT * FROM EMP;

--함수를 SQL 안에서 실행
CREATE OR REPLACE TRIGGER EMP_TRIGGER
BEFORE INSERT ON EMP_TRG
BEGIN 
    --이벤트 발생 시 실행되는 코드
    DBMS_OUTPUT.PUT_LINE('신입사원 입사');
    
END;

아래의 코드를 실행해 보ㅕ면 맨 처음에 '신입사원 입사'라는 문구가 출력되는데, 이것이 TRIGGER이다.

• 보통 실무에서의 TRIGGER는 언제 어떻게 사용하는지가 정해져 있다.
  
  SQL PLUS에서도 다음과 같이 TRIGGER를 추가해 줄 수 있다.

제작 및 사용(AFTER)

--테이블 생성
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이 수행된 날짜
);

--트리거 생성
BEGIN
    IF INSERTING THEN
        INSERT INTO EMP_TRG_LOG
        VALUES('EMP_TRG', 'INSERT', :NEW.EMPNO,
               'SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE);
               
    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;
/