오라클 DBMS 정리 15

패키지명.함수명
패키지명.프로시저명

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

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;

-- 감시테이블에서 작업이 발생하면 값을 사용할 수 있다.
:OLD.컬럼명 -- 이전 값
:NEW.컬럼명 -- 새로운 값
-- INSERT : :NEW.value
-- UPDATE : :OLD.value, :NEW.value
-- DELETE : :OLD.value

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;