오라클 DBMS 정리 15
09 Nov 2018
Reading time ~6 minutes
오라클 DBMS 정리 15 - PL/SQL PACKAGE, TRIGGER
PACKAGE
- 관련 있는 FUNCTION, PROCEDURE를 묶어서 관리하는 객체
- HEADER와 BODY로 구성
- HEADER
- PACKAGE에 포함될 함수나 프로시저의 목록 정의
- BODY
- HEADER에 포함된 함수나 프로시저의 코드를 정의하는 부분
- HEADER
- user_procedures 딕셔너리에서 조회가능
- 패키지로 묶여 있는 FUNCTION, PROCEDURE를 호출
- 같은 이름의 함수나 프로시저를 여러개 정의할 수 있다 (패키지별)
패키지명.함수명
패키지명.프로시저명
- 작성
- 패키지안에 여러개의 함수, 프로시저를 정의해서 복잡도가 상승(주석활용)
-- 1. PACKAGE HEADER 작성 - 패키지에 포함될 목록을 정의
CREATE [OR REPLACE] PACKAGE 패키지명
AS
FUNCTION 함수명(매개변수명, ... ) RETURN 반환형;
...
PROCEDURE 프로시저명(매개변수명, ... );
...
END;
/
-- 2. PACKAGE BODY 작성 - 업무를 구현
CREATE [OR REPLACE] PACKAGE BODY 패키지명
IS
--------- 어떤함수 시작 --------
FUNCTION 함수명(매개변수명, ...) RETURN 반환형
IS
변수선언
BEGIN
코드작성
END [함수명];
--------- 어떤함수 끝 ----------
-- 코드구분을 명확히 하기 위해 주석을 잘 활용할 것
...
--------- 어떤 프로시저 시작 --------
PROCEDURE 프로시저명(매개변수명, ...)
IS
변수
BEGIN
코드
END [프로시저명];
---------- 어떤 프로시저 끝--------
...
END;
/
-- 3. 컴파일
SQL> @경로명/header명.sql
SQL> @경로명/body명.sql
-- 4. 실행
-- 함수사용
SELECT 패키지명.함수명(값)
...
-- 프로시저사용
VAR 바인드변수명 데이터형(크기)
EXEC 패키지명.프로시저명(매개변수명, .. ,:바인드변수명)
PRINT 바인드변수명
- 패키지 삭제
DROP PACKAGE 패키지명
-- 패키지 헤더 : 패키지에 들어갈 함수나 프로시저의 목록을 정의
-- (업무 구현 X)
CREATE OR REPLACE PACKAGE test_pack
AS
FUNCTION get_msg(name VARCHAR2) RETURN VARCHAR2;
PROCEDURE get_age(birth_year IN NUMBER, age OUT NUMBER);
END;
-- PACKAGE BODY 정의 : 업무 구현
-- BODY에서 구현하는 함수나 프로시저의 매개변수명도 헤더와 동일해야함!
CREATE OR REPLACE PACKAGE BODY test_pack
IS
-------------FUNCTION get_msg START ------------------
FUNCTION get_msg(name VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN TO_CHAR(SYSDATE,'day')||'입니다. '||name||'님 안녕하세요';
END get_msg;
----------- FUNCTION get_msg END --------------------
----------- PROCEDURE get_age START -----------------
PROCEDURE get_age(birth_year IN NUMBER, age OUT NUMBER)
IS
BEGIN
age := TO_CHAR(SYSDATE, 'yyyy') - birth_year + 1;
END get_age;
----------- PROCEDURE get_age END ------------------
END test_pack;
/
-- 패키지에 포함된 함수 호출
SELECT test_pack.get_msg('이재찬')
FROM dual;
-- 프로시저 호출
VAR age NUMBER;
EXEC test_pack.get_age(1993, :age);
PRINT age;
난수(Random number)
- 컴퓨터가 임의의 수를 생성하여 반환하는 것
- DBMS_RANDOM 패키지 사용
DBMS_RANDOM.VALUE(시작, 범위) -- 숫자를 얻는 함수
DBMS_RANDOM.STRING('형식', 난수갯수) -- 문자열을 얻는 함수
-- 형식 : u - 영문자 대문자, l - 영문자 소문자,
-- a - 대소문자 혼합, x - 대문자, 숫자 혼합,
-- p - 대소문자 숫자 특수문자 혼합
-- 1에서부터 10 사이의 난수 얻기 (소수발생)
SELECT TRUNC(DBMS_RANDOM.VALUE(1, 10), 0) random_number
FROM dual;
-- 조심할건 10 정수를 얻고 싶으면 11까지 범위를 정하고 TRUNC해야 함!
SELECT DBMS_RANDOM.STRING('u',10) u,
DBMS_RANDOM.STRING('l',10) l,
DBMS_RANDOM.STRING('a',10) a,
DBMS_RANDOM.STRING('x',10) x,
DBMS_RANDOM.STRING('p',10) p
FROM DUAL;
-- DBMS_RANDOM 패키지 사용한 패키지 만들기
-- lucky_pack PACKAGE HEADER
CREATE OR REPLACE PACKAGE lucky_pack
AS
-- 행운의 수(1~45) 중 하나를 얻는 함수
FUNCTION lucky_num RETURN NUMBER;
-- 귀인얻기 프로시저
PROCEDURE lucky_name(name OUT VARCHAR2);
END;
/
-- lucky_pack PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY lucky_pack
IS
-- 행운의 수(1~45) 중 하나를 얻는 함수
---------- lucky_num 시작 -------------------
FUNCTION lucky_num RETURN NUMBER
IS
BEGIN
RETURN TRUNC(DBMS_RANDOM.VALUE(1,46));
END lucky_num;
---------- lucky_num 끝 ---------------------
-- 귀인얻기 프로시저
---------- lucky_name 시작 -----------------
PROCEDURE lucky_name(name OUT VARCHAR2)
IS
TYPE tab_name IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
arr_name tab_name;
temp_num NUMBER;
BEGIN
arr_name(1) := '둘리';
arr_name(2) := '또치';
arr_name(3) := '도우너';
arr_name(4) := '마이콜';
arr_name(5) := '고길동';
-- 난수는 1~5사이에 발생해야 함
temp_num := TRUNC(DBMS_RANDOM.VALUE(1,arr_name.count+1));
name := arr_name(temp_num);
END lucky_name;
---------- lucky_name 끝 -------------------
END;
/
-- lucky_pack 패키지 사용
-- lucky_num 함수 사용
SELECT lucky_pack.lucky_num
FROM dual;
-- lucky_name 프로시저 사용
VAR name VARCHAR2(30);
EXEC lucky_pack.lucky_name(:name);
PRINT name;
TRIGGER
- 연쇄 작업시 사용
- 월급 들어올 때 자동으롤 보험료가 빠지는 예..
- 테이블을 감시하다가 테이블에서 작업이 발생하면 연결하여 다른 작업을 처리할 때 사용
- 작업(Triggering Event) : INSERT, UPDATE, DELETE
- 다른 작업 : 연산, INSERT, UPDATE, DELETE
- 트리거 안에서 COMMIT과 ROLLBACK을 할 수 없다.
- 트랜잭션을 처리할 수 없다
- 직접 처리결과를 확인 후에 COMMIT 또는 ROLLBACK 수행
- user_triggers 딕셔너리에서 조회 가능
-- 감시테이블에서 작업이 발생하면 값을 사용할 수 있다.
:OLD.컬럼명 -- 이전 값
:NEW.컬럼명 -- 새로운 값
-- INSERT : :NEW.value
-- UPDATE : :OLD.value, :NEW.value
-- DELETE : :OLD.value
- 작성법
- TriggeringEvent(감시할 작업) : INSERT, UPDATE, DELETE
- OR로 여러작업 묶일 수 있음
- TriggeringEvent(감시할 작업) : INSERT, UPDATE, DELETE
CREATE OR REPLACE TRIGGER 트리거명
AFTER|BEFORE TriggeringEvent ON 감시할테이블명
FOR EACH ROW
[DECLARE]
-- 변수 선언
BEGIN
-- 이벤트 비교하여 처리할 코드
END;
/
/*
-- INSERT 비교
IF INSERTING THEN
연쇄적으로 동작할 코드
END IF;
-- UPDATE 비교
IF UPDATING THEN
-- DELETE 비교
IF DELETING THEN
*/
-- TRIGGER 예제용 테이블 생성
CREATE TABLE trigger1 (
name VARCHAR2(30),
age NUMBER(3),
addr VARCHAR2(100)
);
CREATE TABLE trigger2 (
name VARCHAR2(30),
age NUMBER(3)
);
-- TRIGGER1 테이블을 감시하는 TRIGGER 생성
CREATE OR REPLACE TRIGGER test_trigger
AFTER INSERT ON trigger1
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('감시중');
END;
-- OR를 사용하면 Triggering Event를 추가할 수 있다.
AFTER INSERT OR DELETE OR UPDATE ON trigger1
...
DBMS_OUTPUT.PUT_LINE('감시중');
DBMS_OUTPUT.PUT_LINE('이전값 : '||:old.name||' '||:old.age);
DBMS_OUTPUT.PUT_LINE('새로운 값 : '||:new.name||' '||:new.age);
END;
-- Triggering Event에서 사용되는 값들
-- INSERT : :NEW.value
-- UPDATE : :OLD.value, :NEW.value
-- DELETE : :OLD.value
-- Triggering Event 비교1
CREATE OR REPLACE TRIGGER event_compare
AFTER INSERT OR UPDATE OR DELETE ON trigger1
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('추가 작업 감시');
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('변경 작업 감시');
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('삭제 작업 감시');
END IF;
END;
/
-- Triggering Event 비교2
CREATE OR REPLACE TRIGGER event_compare
AFTER INSERT OR UPDATE OR DELETE ON trigger1
FOR EACH ROW
BEGIN
IF INSERTING THEN
-- trigger1 테이블에 추가작업이 발생하면
-- trigger2 테이블에 이름, 나이+1 을 추가
INSERT INTO trigger2(name, age)
VALUES(:new.name, :new.age + 1);
-- TRIGGER에서는 transaction 명령을 사용할 수 없음
-- COMMIT; error!
ELSIF UPDATING THEN
-- trigger1 테이블에서 변경작업이 발생하면
-- trigger2 테이블에 이전이름과 나이를 추가
INSERT INTO trigger2(name, age)
VALUES(:old.name, :old.age);
ELSIF DELETING THEN
-- trigger1 테이블에서 삭제작업이 발생하면
-- trigger2 테이블에서 같은 이름의 모든 레코드 삭제
DELETE FROM trigger2
WHERE name=:old.name;
END IF;
END;
-- trigger1 테이블만 트리거로 감시중이기 때문에
-- trigger2에서 Trigerring Event가 발생해도 아무작업을
-- 수행하지 않는다.
-- TRIGGER 안에서 변수를 작성하려면
-- DECLARE를 만들어 BEGIN전에 변수를 선언
...
FOR EACH ROW
DECLARE
-- 변수 선언
BEGIN
...
-- TRIGGER내 변수사용 예제 사전작업
INSERT INTO class4_info(num,age)
VALUES(99,29);
COMMIT;
-- trigger1 테이블 레코드 추가시
-- trigger2 테이블에 trigger1에 추가된 이름과
-- class4_info 테이블의 번호가 99번인 학생의 나이로
-- trigger2 테이블에 레코드 추가
CREATE OR REPLACE TRIGGER tri_variable
AFTER INSERT ON trigger1
FOR EACH ROW
DECLARE
st_age NUMBER;
BEGIN
SELECT age
INTO st_age
FROM class4_info
WHERE num=99;
INSERT INTO trigger2(name,age)
VALUES(:new.name, st_age);
END;
/
-- cp_emp테이블에 레코드가 추가되면
-- cp_emp3 테이블에 사원번호, 사원명, 부서번호,
-- 직무('사원'), 연봉을 추가하는 TRIGGER를 작성
-- 단, 부서번호는 emp테이블의 사원번호가 7788인
-- 사원과 동일한 번호로 추가
-- 연봉이 2000이하가 추가되면 cp_emp3엔
-- 연봉에 500을 더해 추가하는 트리거 작성
CREATE OR REPLACE TRIGGER tri_emp
AFTER INSERT ON cp_emp
FOR EACH ROW
DECLARE
deptno NUMBER;
BEGIN
SELECT deptno
INTO deptno
FROM emp
WHERE emp.empno=7788;
IF :new.sal <= 2000 THEN
INSERT INTO cp_emp3(empno, ename, deptno, job, sal)
VALUES(:new.empno, :new.ename, deptno, '사원', :new.sal+500);
ELSE
INSERT INTO cp_emp3(empno, ename, deptno, job, sal)
VALUES(:new.empno, :new.ename, deptno, '사원', :new.sal);
END IF;
END;