PL/SQL - DAY 12

BUMSOO·2024년 7월 18일
PL/SQL오라클 DBA

복습

- PACKAGE SPEC 선언

CREATE OR REPLACE PACKAGE taxes_pkg 
IS
    FUNCTION tax (p_value IN NUMBER) RETURN number;
END taxes_pkg;
/

- PACKAGE BODY 선언

CREATE OR REPLACE PACKAGE BODY taxes_pkg 
IS
    FUNCTION tax (p_value IN NUMBER)
    RETURN number
    IS 
        v_rate number := 0.034;
    BEGIN
        RETURN (p_value * v_rate) ;
    END tax;
END taxes_pkg;
/
  • body는 spec을 참조하는 종속 관계
  • package spec을 수정하게 되면 body는 invalid 상태로 바뀐다. 리컴파일을 해줘야 한다.

RECOMPILE 명령어

  • ALTER PACKAGE taxes_pkg COMPILE BODY;
    body만 recompile

  • ALTER PACKAGE taxes_pkg COMPILE;
    spec, body 둘다 recompile

  • ALTER PACKAGE taxes_pkg COMPILE SPECIFICATION;
    spec만 recompile 하게 되면 body는 자동으로 invalid가 된다.

DROP 명령어

  • specification and body package 삭제
    DROP PACKAGE taxes_pkg;
  • body package만 삭제
    DROP PACKAGE BODY taxes_pkg;

SOURCE CODE 확인

  • package spec source 확인
SELECT * 
FROM user_source 
WHERE name = 'TAXES_PKG'
AND TYPE = 'PACKAGE';
  • package body source 확인
SELECT * 
FROM user_source 
WHERE name = 'TAXES_PKG'
AND TYPE = 'PACKAGE BODY';

상수 표준화

1 mile = 1.6903 kilo
1 kilo = 0.6214 mile
1 yard = 0.9144 meter
1meter = 1.0936 yard

- 상수를 패키지에 선언

CREATE OR REPLACE PACKAGE global_consts 
IS 
    c_mile_2_kilo CONSTANT NUMBER := 1.6093;
    c_kilo_2_mile CONSTANT NUMBER := 0.6214;
    c_yard_2_meter CONSTANT NUMBER := 0.9144;
    c_meter_2_yard CONSTANT NUMBER := 1.0936;
    
END global_consts;
/

- 실행

BEGIN
    dbms_output.put_line('20 kilo = ' || 20 * global_consts.c_kilo_2_mile || ' mi');
END;
/

- 미터를 야드로 환산하는 함수 만들기

CREATE OR REPLACE FUNCTION mtr_to_yrd(
    p_m IN NUMBER)
RETURN NUMBER
IS
BEGIN
    RETURN (p_m * global_consts.c_meter_2_yard);
END mtr_to_yrd;
/

- 실행

execute dbms_output.put_line(mtr_to_yrd(100));

EXCEPTION 이름 표준화

- 에러번호에 따른 예외처리 패키지 

CREATE OR REPLACE PACKAGE err_pkg 
IS
    notnull_err EXCEPTION;
    PRAGMA EXCEPTION_INIT(notnull_err, -01400);
END;
/

- 예외처리 패키지 활용

BEGIN
    INSERT INTO hr.departments(department_id, department_name) 
    VALUES (280,NULL);
EXCEPTION
    WHEN err_pkg.notnull_err THEN --패키지 사용
        dbms_output.put_line('INSERT OPERATION FAILED');
        dbms_output.put_line(SQLERRM);
        dbms_output.put_line(SQLCODE);
END;
/

[문제 22]

사원들의 급여를 10% 인상하는 프로그램을 생성해주세요.

declare
v_num emp_pkg.numlist := emp_pkg.numlist(100,103,107,110,112,115,160,170,180,190,200);
begin
emp_pkg.update_sal(v_num);
rollback;
end;
/

사원번호 : 100 사원이름 : King 수정 급여 : 29040
사원번호 : 103 사원이름 : Hunold 수정 급여 : 9900
사원번호 : 107 사원이름 : Lorentz 수정 급여 : 4620
사원번호 : 110 사원이름 : Chen 수정 급여 : 9020
사원번호 : 112 사원이름 : Urman 수정 급여 : 8580
사원번호 : 115 사원이름 : Khoo 수정 급여 : 3410
사원번호 : 160 사원이름 : Doran 수정 급여 : 8250
사원번호 : 170 사원이름 : Fox 수정 급여 : 10560
사원번호 : 180 사원이름 : Taylor 수정 급여 : 3520
사원번호 : 190 사원이름 : Gates 수정 급여 : 3190
사원번호 : 200 사원이름 : Whalen 수정 급여 : 5808

<풀이>

CREATE OR REPLACE PACKAGE emp_pkg -- spec 문
IS
    TYPE numlist IS TABLE OF number;
    PROCEDURE update_sal (v_num IN numlist);
END emp_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg -- body 문
IS
--    TYPE numlist IS TABLE OF number; -- 타입 선언문은 body에 작성하면 오류 발생
    
    PROCEDURE update_sal (v_num IN numlist)
    IS
        TYPE v_rec_type IS RECORD(
            v_id hr.employees.employee_id%type,
            v_name hr.employees.last_name%type,
            v_sal hr.employees.salary%type);
        
        TYPE v_tab_type IS TABLE OF v_rec_type;
        v_tab v_tab_type;
        
        
    BEGIN
        FORALL i IN v_num.first..v_num.last 
            update hr.employees
            SET salary = salary * 1.1
            WHERE employee_id = v_num(i)
            RETURNING employee_id, last_name, salary BULK COLLECT INTO v_tab;
        
        FOR j IN v_tab.first..v_tab.last LOOP
            dbms_output.put_line('사원번호 : '||v_tab(j).v_id || '사원이름 : '||v_tab(j).v_name||'수정 급여 : '||v_tab(j).v_sal);
        END LOOP;
        
    END update_sal;
END emp_pkg;
/

TRIGGER(트리거)

  • 트리거는 데이터베이스에 저장된 이벤트에 대한 응답으로 실행되는 PL/SQL 블록 입니다.
  • 오라클 데이터베이스는 지정된 조건이 발생할 때 트리거를 자동으로 실행합니다.
  • 트리거는 테이블,뷰,스키마(소유자), 또는 데이터베이스에 정의될 수 있다.
  • CREATE TRIGGER 시스템권한이 필요하다.

트리거의 유형

  • 특정 테이블, 뷰에 있는 DML문
  • CREATE, ALTER, DROP, TRUNCATE
  • 데이터베이스 시작, 종료
  • 특정 오류 메시지, 또는 임의의 오류 메시지
  • 유저 로그인, 로그오프
  • 값의 대한 감사

문장 트리거

  • 트리거 생성시 기본값
  • 트리거 이벤트에 한번만 실행된다.
  • 영향을 받은 행이 없더라도 무조건 한번 실행된다.

행 트리거

  • 트리거 생성시 FOR EACH ROW 절
  • 트리거 이벤트에 영향을 받은 각 행에 대해 실행된다.
  • 영향을 받은 행이 없으면 실행되지 않는다.

- 문장 트리거 생성

/* BEFORE 문장 트리거 */
CREATE OR REPLACE TRIGGER dept_before
BEFORE -- 타이밍
    INSERT on hr.dept  --DML문
BEGIN
     dbms_output.put_line('INSERT하기 전에 문장 트리거 수행');
END;
/

/* AFTER 문장 트리거 */
CREATE OR REPLACE TRIGGER dept_after
AFTER
    INSERT on hr.dept
BEGIN
     dbms_output.put_line('INSERT한 후에 문장 트리거 수행');
END;
/

- 행 트리거 생성

/* BEFORE 행 트리거 */
CREATE OR REPLACE TRIGGER dept_row_before
BEFORE -- 타이밍
    INSERT on hr.dept--DML문
FOR EACH ROW
BEGIN
     dbms_output.put_line('INSERT하기 전에 행 트리거 수행');
END dept_row_before;
/

/* AFTER 행 트리거 */
CREATE OR REPLACE TRIGGER dept_row_after
AFTER
    INSERT on hr.dept
FOR EACH ROW
BEGIN
     dbms_output.put_line('INSERT한 후에 행 트리거 수행');
END dept_row_after;
/

- 생성 트리거 확인
SELECT * FROM user_triggers;

조건부 술어

  • INSERTING, DELETING, UPDATING을 사용하여 조건에 따라 로직구현을 할 수 있다.
    • 문장, 행 트리거에서 사용 할 수 있다.

<조건부 술어 활용 예시>

CREATE OR REPLACE TRIGGER secure_dept
BEFORE
    INSERT OR UPDATE OR DELETE ON hr.dept -- 해당 테이블에 대한 DML문 전에 제어
BEGIN
    IF to_char(sysdate,'hh24:mi') NOT BETWEEN '08:00' AND '16:00' THEN --해당 시간에 대한 DML작업을 불허
    	IF INSERTING THEN
        	RAISE_APPLICATION_ERROR(-20500, '근무외 시간에는 데이터 입력할 수 없습니다.'); -- hr.dept 에서 암시적으로 작동한다.
        ELSIF UPDATING THEN
        	RAISE_APPLICATION_ERROR(-20500, '근무외 시간에는 데이터 수정할 수 없습니다.');
        ELSE
        	RAISE_APPLICATION_ERROR(-20500, '근무외 시간에는 데이터 삭제할 수 없습니다.');
       END IF;
    END IF;
END secure_dept;
/

INSERT INTO hr.dept(department_id, department_name)
VALUES (280,'Data Architect');

수식자

  • old : 트리거가 처리한 레코드의 원래값을 저장한다.
  • new : 새값을 처리한다
  • 트리거의 BEGIN 절에서 사용할 때는 콜론(:)을 붙여야 한다.
  • WHEN 절에서 사용할 때는 콜론(:)을 붙이면 안된다.
  • 행트리거에서만 사용한다.

<트리거 응용버젼>

CREATE OR REPLACE TRIGGER test_trigger
BEFORE
    DELETE OR INSERT OR 
    UPDATE OF salary,employee_id, last_name ON hr.copy_emp -- salary 컬럼에 대한 update 할때만 trigger
FOR EACH ROW -- 행 트리거
WHEN (new.department_id = 20 OR old.department_id = 10) -- insert시 새로 들어온 new , delete시 삭제하기 전 old,update시에는 old, new 둘다 저장용도
-- WHEN절은 조건절, 트리거 조건은 행전체적으로 확인
DECLARE 
    v_diff number;
BEGIN
        IF DELETING THEN -- DELETE문에 대한 조건부 술어
        	dbms_output.put_line('old salary : ' || :old.salary); -- BEGIN절에서 수식자를 쓸때는 : 사용
    	ELSIF INSERTING THEN
        	dbms_output.put_line('new salary : ' || :new.salary);
    	ELSIF UPDATING('salary') THEN
        	v_diff := :new.salary - :old.salary;
        	dbms_output.put_line('사번 : ' || :new.employee_id || '이전 급여 : ' || :old.salary || '수정 급여 : '|| :new.salary || '급여차이 : ' || v_diff);
        ELSIF UPDATING('employee_id') THEN
       		dbms_output.put_line('test1');
        ELSIF UPDATING('last_name') THEN
       		dbms_output.put_line('test2');     
   		 END IF;
END;
/

<update 실행>

UPDATE hr.copy_emp
SET salary = salary * 1.1
WHERE department_id = 20;

UPDATE hr.copy_emp
SET salary = salary * 1.1
WHERE department_id = 10;

UPDATE hr.copy_emp
SET salary = salary * 1.1
WHERE employee_id = 201; -- department_id가 10번이기 때문에 트리거 수행

<delete 실행>

DELETE from hr.copy_emp where department_id = 10;

<insert 실행>

INSERT INTO hr.copy_emp(employee_id, last_name, salary, department_id) VALUES (300,'SCOTT',1000,20); -- department_id가 20번이기 때문에 트리거 수행

profile
BUMSOO
이전 포스트

PL/SQL - DAY 11

다음 포스트

PL/SQL - DAY 13

0개의 댓글