PL/SQL - DAY 11

BUMSOO·2024년 7월 17일

종속관계

<모듈식 개발>

- function

매개변수를 입력하여 boolean값을 반환하는 함수

CREATE OR REPLACE FUNCTION validate_comm(
    p_comm IN number)
RETURN boolean
IS
    v_max_comm number;
BEGIN
    SELECT max(commission_pct)
    INTO v_max_comm
    FROM hr.employees;
    
    IF p_comm > v_max_comm THEN
        RETURN FALSE;
    ELSE
        RETURN TRUE;
    END IF;
END;
/

- procedure

입력받은 매개변수를 함수에 전달하여 작동하는 프로시저

CREATE OR REPLACE PROCEDURE reset_comm(
    p_comm IN number)
IS
    v_comm number := 0.1; -- 로컬변수
BEGIN
    IF validate_comm(p_comm) THEN
        dbms_output.put_line('old : ' ||to_char(v_comm,'0.9'));
        v_comm := p_comm;
        dbms_output.put_line('new : ' ||to_char(v_comm,'0.9'));
    ELSE
        raise_application_error(-20000,'기존 최고값을 넘을 수 없습니다',FALSE);
    END IF;
    
END reset_comm;
/

- 프로시저 속 v_comm 로컬변수는 execute가 종료되면 다시 원래 값으로 되돌아간다. 이를 해결하려면 글로벌 변수를 사용해야 하는데, 서브프로그램에서는 bind 변수 사용이 불가능하다.

- 객체의 구조가(데이터 타입 등)가 변경되면 변경객체를 참조하는 객체의 상태는 invalid가 된다.

참조하는 객체는 참조당하는 객체의 timestamp(컴파일 시간)를 가지고 있는데 참조 당하는 객체의 timestamp(컴파일 시간)가 변경되면 그 차이로 참조하는 객체가 invalid 상태가 된다.
종속 관계는 컴파일 시간을 가지고 비교한다.

- 객체 종속 관계 확인하기

objects의 timestamp는 마지막 객체 컴파일 시간이다.

SELECT * FROM user_dependencies WHERE referenced_name IN ('EMPLOYEES','VALIDATE_COMM', 'RESET_COMM');
SELECT * FROM user_objects WHERE object_name IN ('EMPLOYEES','VALIDATE_COMM', 'RESET_COMM');

- hr.employees의 데이터 타입 변경

ALTER TABLE hr.employees MODIFY commission_pct number(10,3);

- 다시 객체 상태 확인

SELECT * FROM user_objects WHERE object_name IN ('EMPLOYEES','VALIDATE_COMM', 'RESET_COMM');

- invalid 상태가 된걸 확인할 수 있다.
- 그럼 DBA들이 수동으로 리컴파일 작업을 해야한다.

ALTER FUNCTION validate_comm COMPILE;
ALTER PROCEDURE reset_comm COMPILE;

ALTER 객체타입 객체이름 COMPILE;

- 다시 object 상태를 확인해보면 valid 상태를 확인할 수 있고 timestamp 또한 마지막 컴파일 시간으로 변경 된걸 확인할 수 있다.

SELECT * FROM user_objects WHERE object_name IN ('EMPLOYEES','VALIDATE_COMM', 'RESET_COMM');

리컴파일시 오류가 발생하면 디버깅 작업을 수행해야 한다.
리컴파일을 최소 하기 위해서는 데이터타입 선언시 %type을 이용하는걸 권장한다.

user_dependencies를 사용하지 않고 쉽게 객체 종속 관계를 확인하는 방법

1) SQLPLUS에 스크립트 구동하기

@C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\utldtree.sql

스크립트 주소 앞에 @를 붙여야 실행 가능
utldtree.sql 파일이 객체종속관계 확인 스크립트

2) deptree_fill 프로시저 실행

execute deptree_fill('table','hr','employees'); -- 오브젝트 타입, 오브젝트 소유자, 오브젝트 이름

매개변수는 객체 타입, 객체 소유자, 객체 이름 순으로 작성

3) deptree 뷰 확인

SELECT * FROM deptree; -- deptree는 뷰이다

계층형 레벨을 통해 종속 관계를 쉽게 확인할 수 있다.
1 level은 바로 앞에 0 level을 종속, 2 level은 바로 앞에 1 level을 종속

PACKAGE(패키지)

관련성있는 서브프로그램(함수, 프로시저), 변수, 타입을 모아 놓은 프로그램

Specification(Public) - 선언 영역

선언하는 영역이기 때문에 BEGIN절이 없다.

Body(Private) - 선언 한것을 로직구현 영역

BEGIN절은 옵션적인 부분

실행방법
패키지명.선언한것 (spec에서 선언된것만 사용가능)

글로벌 변수는 해당 세션에서만 지속 가능, 다른 세션에서는 초기화 되어있음

specification은 패키지의 인터페이스 입니다. 패키지 외부에서 참조할 수 있는 데이터 유형, 변수, 상수, 예외사항, 커서, 서브프로그램을 선언한다.

body는 소스코드를 정의한다.

spec에서 선언한 글로벌 변수의 값은 프로그램안에서 새로운 값으로 수정하게 되면 현재 session에서는 지속적으로 변경한 값을 사용한다.

Specification(Public)

CREATE OR REPLACE PACKAGE comm_pkg
IS
    g_comm number := 0.1; 
    PROCEDURE reset_comm(p_comm IN number); -- 프로시저 해드 부분까지 선언, 함수는 RETURN 절까지
END comm_pkg;
/

Body(Private)

CREATE OR REPLACE PACKAGE BODY comm_pkg -- Spec에서 선언된 패키지 이름을 사용, BODY 사용
IS
	/* 함수는 패키지 바디에서만 작동되기 때문에 spec에 선언하지 않음 */
    FUNCTION validate_comm(
        p_comm IN number)
    RETURN boolean
    IS
        v_max_comm number;
    BEGIN
        SELECT max(commission_pct)
        INTO v_max_comm
        FROM hr.employees;
    
        IF p_comm > v_max_comm THEN
             RETURN FALSE;
        ELSE
            RETURN TRUE;
        END IF;
    END validate_comm;    
    
    PROCEDURE reset_comm(
         p_comm IN number)
    IS
    BEGIN
        IF validate_comm(p_comm) THEN
            dbms_output.put_line('old : ' ||to_char(g_comm,'0.9'));
            g_comm := p_comm;
             dbms_output.put_line('new : ' ||to_char(g_comm,'0.9'));
        ELSE
             raise_application_error(-20000,'기존 최고값을 넘을 수 없습니다',FALSE);
        END IF;
    
    END reset_comm;
END comm_pkg;
/

<실행>

execute comm_pkg.reset_comm(0.2); -- 패키지명.서브프로그램 (spec에서 선언된 public한 서브프로그램만 실행할 수 있다)
execute comm_pkg.reset_comm(0.3);
execute comm_pkg.reset_comm(0.4);
execute comm_pkg.reset_comm(0.5);

- 패키지 spec의 서브프로그램 및 파라미터 확인

글로벌 변수는 조회되지 않음 (코드 확인해야함)
실행 가능한 서브프로그램 이름과 해당 서브프로그램의 파라미터 확인 가능

desc comm_pkg;

- spec에서 선언한 글로벌 변수의 값을 변경할 수 있다.

해당 세션에서만 변경되고 다른 세션에서는 패키지 변수 선언 값 그대로 이다.

EXECUTE comm_pkg.g_comm := 0.35;
execute comm_pkg.reset_comm(0.3);

PRAGMA SERIALLY_REUSABLE 지시어

spec,body에 기술하며 사용 목적은 글로벌 변수를 프로그램안에서 수정한 경우 프로그램 호출이 끝나면 선언시에 설정한 초기값으로 되돌아 가는 지시어 이다.

CREATE OR REPLACE PACKAGE comm_pkg
IS
    PRAGMA SERIALLY_REUSABLE;  /*지시어*/
    
    g_comm number := 0.1; 
    PROCEDURE reset_comm(p_comm IN number); /*프로시저 해드 부분까지 선언, 함수는 RETURN 절까지*/
END comm_pkg;
/

CREATE OR REPLACE PACKAGE BODY comm_pkg  /* Spec에서 선언된 패키지 이름을 사용, BODY 사용*/
IS
    PRAGMA SERIALLY_REUSABLE; /* 지시어 */
    FUNCTION validate_comm(  /* 함수는 패키지 바디에서만 작동되기 때문에 spec에 선언하지 않음 */
        p_comm IN number)
    RETURN boolean
...

Package Overloading

패키지 내에서 동일한 이름의 프로시저, 함수를 만들수 있다.

형식 파라미터의 개수, 데이터 타입이 틀린경우, 모드가 틀리게 구성하면 overloading 할 수 있다.
to_char(날짜, 문자)
to_char(숫자, 문자)

<오버로딩 예시>

CREATE OR REPLACE PACKAGE pack_over -- SPEC은 필수
IS
    TYPE date_tab_type IS TABLE OF date INDEX BY pls_integer;
    
    TYPE num_tab_type IS TABLE OF number INDEX BY pls_integer;
    
    PROCEDURE init(tab OUT date_tab_type, n IN number); -- 오버로딩 , 데이터타입 다름
    PROCEDURE init(tab OUT num_tab_type, n IN number); -- 오버로딩, 데이터타입 다름
END pack_over;
/

CREATE OR REPLACE PACKAGE BODY pack_over -- PACKAGE BODY는 옵션이다.
IS
    PROCEDURE init(tab OUT date_tab_type, n IN number)
    IS 
    BEGIN
        FOR i in 1..n LOOP
            tab(i) := sysdate+i;
        END LOOP;
    END init;
    
    PROCEDURE init(tab OUT num_tab_type, n IN number)
    IS
    BEGIN
        FOR i in 1..n LOOP
            tab(i) := i;
        END LOOP;
    END init;

END pack_over;
/

<실행>

DECLARE
    date_tab pack_over.date_tab_type; --패키지에 선언된 타입을 가져와야함
    num_tab pack_over.num_tab_type;  --패키지에 선언된 타입을 가져와야함	
BEGIN
    pack_over.init(date_tab, 5);
    pack_over.init(num_tab, 5);

    FOR i IN 1..5 LOOP
        dbms_output.put_line(date_tab(i));
        dbms_output.put_line(num_tab(i));
    END LOOP;
END;
/

<오류코드>

DECLARE
    TYPE date_tab_type IS TABLE OF date INDEX BY pls_integer;
    TYPE num_tab_type IS TABLE OF number INDEX BY pls_integer;
    
    v_tab_date date_tab_type;
    v_tab_num num_tab_type;
BEGIN
    pack_over.init(v_tab_date, 5);
    pack_over.init(v_tab_num, 5);

    FOR i IN 1..5 LOOP
        dbms_output.put_line(v_tab_date(i));
        dbms_output.put_line(v_tab_num(i));
    END LOOP;
END;
/

해당오류는 호환성의 문제로 유저가 정의한 타입을 가지고 OUT 모드로 변수를 전달 받아야 한다면 기존에 정의해 놓은 타입을 그대로 가져와야 한다.

패키지 커서 지속상태

익명블록구조 or standardalone 프로그램에서는 open되어 있는 커서를 따로 close 해주지 않아도 프로그램 종료시 자동으로 close 된다.
패키지에서 선언한 커서를 open 하게 되면 현재 session이 열려 있는 동안에는 계속 open 되어있다.
현재 session을 닫을때 or 커서를 close하게 되면 cursor는 종료된다.

<예시>

CREATE OR REPLACE PACKAGE pack_cur
IS 
    PROCEDURE open;
    PROCEDURE next(p_num IN number);
    PROCEDURE close;
END pack_cur;
/

CREATE OR REPLACE PACKAGE BODY pack_cur
IS 
    CURSOR c1 IS  -- private 하게 cursor 선언
    SELECT employee_id, last_name
    FROM hr.employees
    ORDER BY employee_id DESC;
    
    v_no number; -- private 변수는 같은 패키지 내에서는 어디서든 사용 가능하다.
    v_name varchar2(30);
    
    PROCEDURE open
    IS
    BEGIN
        IF NOT c1%ISOPEN THEN
            OPEN c1;
            dbms_output.put_line('c1 cursor open');
        END IF;
    END open;
    
    PROCEDURE next(p_num IN number)
    IS
    BEGIN
        LOOP
            EXIT WHEN c1%rowcount >= p_num;
            FETCH c1 INTO v_no, v_name;
            dbms_output.put_line('사번 : ' || v_no || ' 이름 : ' || v_name);
        END LOOP;
    END next;
    
    PROCEDURE close
    IS
    BEGIN
        IF c1%ISOPEN THEN
            CLOSE c1;
            dbms_output.put_line('c1 cursor close');
        END IF;
    END close;
END pack_cur;
/

<실행>

EXECUTE pack_cur.open; --패키지에서 cursor open
EXECUTE pack_cur.next(3); -- rowcount는 계속 지속되고 있음
EXECUTE pack_cur.next(6); -- rowcount 4부터 6까지, cursor는 계속 열려있다. 
EXECUTE pack_cur.close;

BUMSOO

이전 포스트

PL/SQL - DAY 10

다음 포스트

PL/SQL - DAY 11

종속관계

PACKAGE(패키지)

Specification(Public)

Body(Private)

PRAGMA SERIALLY_REUSABLE 지시어

Package Overloading

패키지 커서 지속상태

PL/SQL - DAY 10

PL/SQL - DAY 12

0개의 댓글