[ORACLE] USSOFT ORACLE 교육 4일차

📌 PACKAGE

🤔 패키지란?

• 변수, 상수, 서브프로그램 등의 항목을 묶어 놓은 객체
• 여러가지 항목들을 모아 하나의 이름으로 묶어놓은 객체
• 컴파일 과정을 거쳐 데이터베이스에 저장
• 다른 프로그램에서 패키지의 항목을 참조하고 실행할 수도 있음

🤨 사용하는 이유

프로그래밍을 하다보면 Procedure 와 같은 Sub Program의 수가 기하급수적으로 많아지게 되는데 이렇게 항목들이 많아지게 되면 관리하기가 힘들고 자신이 필요한 항목을 찾는것 또한 어려워지는 상황이 온다. 이러한 상황을 방지하기 위해 여러가지 항목들을 손쉽게 관리하자는 요지에서 나온 것이 PACKAGE라는 개념이다.

😶 패키지 사용법

패키지는 선언부, 본문, 실행부로 이루어져있다.

💡 선언부

CREATE OR REPLACE PACKAGE EX_PKG IS -- 패키지 선언

FUNCTION FUNC_1(P_PRODUCT_ID IN NUMBER) -- 패키지로 묶을 함수
RETURN VARCHAR2;

PROCEDURE PROC_1; -- 패키지로 묶을 프로시저1

PROCEDURE PROC_2(P_PRODUCT_ID IN NUMBER); -- 패키지로 묶을 프로시저2

END EX_PKG; -- 패키지 종료

💡 본문

CREATE OR REPLACE PACKAGE BODY EX_PKG IS -- 패키지 본문

--- 패키지 선언부 함수1 ---
FUNCTION FUNC_1(P_PRODUCT_ID IN NUMBER)
RETURN VARCHAR2
IS
V_PRODUCT_NAME VARCHAR2(100);
BEGIN
SELECT
PRODUCT_NAME
FROM PRODUCTS
WHERE
PRODUCT_ID = P_PRODUCT_ID;

RETURN NVL(PRODUCT_NAME, '존재하지 않는 제품');
END FUNC_1;

--- 패키지 선언부 프로시저1 ---
PROCEDURE PROC_1
IS
CURSOR EX_CUR IS
SELECT
PRODUCT_ID,
PRODUCT_NAME
FROM PRODUCTS;

BEGIN

FOR I IN EX_CUR LOOP

DBMS_OUTPUT.PUT_LINE('제품ID:' || I.PRODUCT_ID);
DBMS_OUTPUT.PUT_LINE('제품명:' || I.PRODUCT_NAME);

END LOOP;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM || '에러 발생');

END PROC_1;

--- 패키지 선언부 프로시저2 ---
PROCEDURE PROC_2(P_PRODUCT_ID IN NUMBER)
IS
CURSOR EX_CUR IS
SELECT EX_CUR IS
SELECT
PRODUCT_ID,
PRODUCT_NAME
FROM PRODUCTS
WHERE PRODUCT_ID = P_PRODUCT_ID;

BEGIN

FOR I IN EX_CUR LOOP

DBMS_OUTPUT.PUT_LINE('제품ID:' || I.PRODUCT_ID);
DBMS_OUTPUT.PUT_LINE('제품명:' || I.PRODUCT_NAME);

END LOOP;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM || '에러발생');

END PROC_2;

END EX_PKG; -- 패키지 종료

💡 실행부

-- DMBS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SET SERVEROUTPUT ON;

--- 패키지 실행
EXEC EX_PKG.FUNC_1(10);

EXEC EX_PKG.PROC_1;

EXEC EX_PKG.PROC_2(10);

📌 TRIGGER

🤔 트리거란?

• DB System에서 데이터의 입력, 갱신, 삭제 등의 이벤트가 발생할 때마다 자동적으로 수행되는 사용자 정의 Procedure
• TABLE과는 별도로 DATABASE에 저장된다.
• VIEW에 대해서가 아니라 TABLE에 관해서만 정의될 수 있다.
• SQL의 제약조건 방법을 통해 명시할 수 없는 무결성 제약조건을 구현하고, 관련 테이블의 데이터를 일치시킬 때 주로 사용된다.
• 제약조건과 함께 데이터 무결성을 지키는 하나의 방법으로서 특정 이벤트에 대해서 연속적으로 자동 동작하는 특수한 형태의 저장 Procedure라고 볼 수 있다.
• TRIGGER의 종류는 문장트리거, 행 트리거로 나눌 수 있다.

💡 문장 트리거

트리거가 설정된 테이블에 트리거 이벤트가 발생하면 많은 행에 대해 변경 작업이 발생하더라도 오직 한번만 트리거를 발생시키는 방법이다. 컬럼값이 변화가 생길때마다 스스로 알아서 실행된다.

💡 행 트리거

조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러번 수행하는 방법으로 FOR EACE ROW WHEN 조건 절로 정의된다. 컬럼의 데이터 행이 변화가 오면 실행된다. 변경 후의 행은 OLD와 NEW를 통해 가져올 수 있다.

😶 트리거 사용법

CREATE [OR REPLACE] TRIGGER 트리거명
BEFORE|AFTER
[동작(INSERT, UPDATE, DELETE)] ON 테이블명
[REFERENCING NEW | OLD TABLES AS 테이블명]
[FOR EACH ROW]
[WHEN 조건식]
트리거 BODY문

• OR REPLACE
  생성할 트리거와 같은 이름을 가지고 있어도 무시하고 새로운것으로 갱신하는 것이므로 사용할 때 주의해야 한다.
• AFTER
  테이블이 변경된 후에 트리거가 실행되는 옵션
• BEFORE
  테이블이 변경되기 전에 트리거가 실행되는 옵션
• 동작옵션
  각각 INSERT, UPDATE, DELETE가 실행될 때 트리거를 실행시키는 것
• NEW
  새로 추가되거나 변경된 후의 값에 트리거가 적용된다. (INSERT : 입력할 값, UPDATE : 수정할 값)
• OLD
  변경 전의 값에 트리거가 적용된다. (UPDATE : 수정 전 값, DELETE : 삭제할 값)
• WHEN
  트리거가 실행되면서 지켜야할 조건을 지정한다. (조건에 맞는 데이터만 트리거 실행)
• 트리거 BODY문
  트리거의 본문 코드를 입력하는 부분
  BEGIN으로 시작해서 END로 끝나는데, 적어도 하나 이상의 SQL문이 있어야 한다. 그렇지 않으면 오류 발생
  변수에 값을 치환할 때는 예약어 SET을 사용한다.

📝 트리거 예제

CREATE OR REPLACE oracle_trigger
 BEFORE
 INSERT ON oracleStudy
 REFERENCING NEW TABLE AS new_tirgger
 FOR EACH ROW
   WHEN new_trigger.점수 = ''
     BEGIN
       SET NEW_table.점수 = '0';
       END;

🤨 트리거의 제한

• 트리거는 트랜잭션 제어문(COMMIT, ROLLBACK, SAVEPOINT) 을 사용할 수 없다.
• 트리거는 트리거링 문장의 실행부분으로서 실행되는 트리거링 문장과 같은 트랜잭션에 있다.
• 트리거가 걸려있는 대상(트리거링)문장이 COMMIT, ROLLBACK될때 트리거의 작업역시 COMMIT, ROLLBACK 된다.

😮 트리거 관리

• 활성화 / 비활성화
  ALTER TRIGGER 트리거_이름 [DISABLE | ENABLE]

• 테이블에 속한 트리거 활성화 / 비활성화
  ALTER TABLE 테이블_이름[DISABLE | ENABLE] ALL TRIGGER

• 트리거 수정 후 재컴파일
  ALTER TRIGGER 트리거_이름 COMPILE;

• 트리거 삭제
  DROP TRIGGER 트리거_이름

• 트리거 조회
  SELECT * FROM USER_TRIGGERS

📌 JOB

🤔 JOB 이란?

• DB 내에 생성한 PROCEDURE 함수들에 대해 데이터베이스 내의 스케줄러에게 지정한 시간에 자동으로 작업이 진행될 수 있도록 하는 기능
• 주기적으로 수행되어지는 JOB을 JOB QUEUE를 사용하여 스케줄링 할 수 있다.

😶 JOB 사용방법

• JOB_QUEUE_PROCESS = 1 : SNP 프로세스의 개수를 지정
• JOB_QUEUE_INTERVAL = 60 : SNP프로세스가 SLEEP 상태에서 깨어나는 간격을 초로 지정(패키지에 있는 프로시저)
• SUBMIT : 새로운 작업을 JOB QUEUE 목록에 등록
• REMOVE : JOB QUEUE에 등록된 JOB 을 제거
• CHANGE : JOB QUEUE에 등록된 JOB 을 변경
• NEXT_DATE : JOB QUEUE에 등록된 JOB의 작동시간을 변경
• INTERVAL : JOB QUEUE에 등록된 JOB의 수행 주기를 변경
• WHAT : 수행할 PROCEDURE OR PACKAGE 변경
• RUN : 등록되어 있는 특정 JOB을 수동으로 수행