2024.07.17

privatekim·2024년 7월 17일

아이티윌

목록 보기
5/6
CREATE OR REPLACE FUNCTION valid_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;
/
SELECT * FROM USER_SOURCE WHERE NAME = 'VALID_COMM'; -- PARSE된 코드값 저장
SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME = 'VALID_COMM'; --OBJECT 정보
CREATE OR REPLACE PROCEDURE rest_comm (p_comm number)
IS
    v_comm number := 0.1;
BEGIN
    IF valid_comm(p_comm) THEN
        dbms_output.put_line('old : ' || v_comm);
        v_comm := p_comm;
        dbms_output.put_line('new : ' || v_comm);
    ELSE
        RAISE_APPLICATION_ERROR(-20001,'기존 최고값을 넘을 수 없다.');
    END IF;
END;
/
  1. 0.2를 입력했을 때 언제까지 v_comm에 0.2가 유지되느냐? : 프로시저가 종료될 때 까지.
    • 세션이 유지되는 동안 v_comm이 유지되게 할 수 없느냐? : global 변수 사용해야 함 - 패키지
  1. 종속 관계 (hr.employees, valid_comm)의 객체가 수정되면 참초하는 객체의 상태는 INVALID상태가 된다.
    • recompile을 자동으로 해주지만, 성능(속도)이 감소한다.
    • DBA는 INVALID한 객체들을 먼저 recompile해야 한다.
    • 종속관계에 있는 객체들을 모아서 관리하자 - 패키지
    • 유지보수 종속관계 관리 EASY
select * from user_dependencies where referenced_name in ('EMPLOYEES','VALID_COMM');
  • object 객체 참조 관계 조회
SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME in ('EMPLOYEES','REST_COMM','VALID_COMM'); --OBJECT 정보

name 객체가 referenced_name 객체를 참조한다.

또는 프로그램을 통해 가능하다.

C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\utldtree.sql 해당 경로의 utldtree 스크립트를 sqlplus에서 실행하면 된다.

실행 방법

테스트를 hr계정에서 하기 때문에 hr계정에 conn한 후 아래 코드를 실행한다.

conn hr/hr
@C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\utldtree.sql
--해당 경로의 sql스크립트 실행

하면 프로시저에 DEPTREE_FILL이라는 프로시저가 생성된다.

해당 프로시저를 실행시키면 deptree_fill view가 생성되고 해당 view를 통해 트리구조로 종속관계를 확인할 수 있다.

desc deptree_fill;
exec deptree_fill('table', 'hr', 'employees');
select * from deptree;

이때 C:\oraclexe\app\oracle\product\11.2.0\serve 까지의 경로를 오라클 홈 이라고 한다.

  • 영향도 평가

해당 객체가 변경(modify)해야 할 경우 참조 객체들을 확인하고 해당 참조객체에 영향을 미치는지 파악하고 회의를 진행 후 변경해야 한다.

ALTER TABLE hr.employees MODIFY(commission_pct number(10,3));
-- 다음과 같이 객체의 상태 변경

변경시 해당 객체를 참조하고 있는 객체들이 INVALID가 되면서 LAST_DDL_TIME, TIMESTAMP의 값이 변경 시간으로 최신화 된다.

  • 종속관계가 어긋났는지 어떻게 체크하느냐?

종속관계에 있는 객체에 대한 마지막 compile TIMESTAMP와 자신의 TIMESTAMP를 비교하여 체크한다.

-> hr.employees.commission_pct 갱신 ~> valid_comm의 invalid (timestamp 갱신)
-> valid_comm의 TIMESTAMP갱신 ~> rest_comm의 invalid (timestamp 갱신)

프로시저 컴파일 하는 방법

ALTER FUNCTION valid_comm COMPILE;
ALTER PROCEDURE REST_COMM COMPILE;

컴파일 하더라도 오류나는 경우가 있다. 그때는 소스코드의 문제로 디버깅을 진행해야 한다.

즉, 변수의 타입을 사용할 때 %type을 이용해 참조하는 것이 좋다.

PACKAGE

  • 글로벌 변수 / 종속 관계 관리(overload) / cursor의 지속상태 관리

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

즉 모아서 관리하는 프로그램.

패키지 생성 규칙

  1. Specification (Public) - 선언부 (DECLARE, IS), OPEN 영역, 참조 영역, 패키지의 인터페이스.

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

CREATE OR REPLACE PACKAGE comm_pkg
IS
    --global 변수
    g_comm number := 0.1;
    -- PROCEDURE 선언부분만 작성
    PROCEDURE rest_comm(comm number);
    -- FUNCTION은 RETURN 까지만 선언.
    FUNCTION valid_comm(comm number) RETURN BOOLEAN;
    -- 즉 IS절 바로 위까지 선언    
END; /*선언하는 부분 spec 즉 begin 필요없다.*/
  1. Body (Private) - 로직구현 (BEGIN), 캡슐화, 없어도 된다.

소스코드를 정의하는 영역이다.

CREATE OR REPLACE PACKAGE BODY comm_pkg  /* BODY 선언 */
IS
    /* CREATE OR REPLACE 제거 */
    FUNCTION valid_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 valid_comm;
    
    /* CREATE OR REPLACE 제거 */
    PROCEDURE rest_comm (p_comm number)
    IS
        /*v_comm number := 0.1; 글로벌 변수로 대체*/
    BEGIN
        IF valid_comm(p_comm) THEN
            dbms_output.put_line('old : ' || g_comm);
            g_comm := p_comm;
            dbms_output.put_line('new : ' || g_comm);
        ELSE
            RAISE_APPLICATION_ERROR(-20001,'기존 최고값을 넘을 수 없다.');
        END IF;
    END rest_comm;
END; /* BEGIN절은 OPTIONAL한 절이다. */
/
  1. 실행하기
exec comm_pkg.rest_comm(0.2);

반드시 패키지명을 통해서 호출해야 한다.

글로벌 변수는 세션이 죽을 때 까지 살아있다. 다른 세션에서 내 세션의 값을 공유하지 않는다.

desc package

글로벌 변수는 보여지지 않고, 사용 가능한 서브 프로그램의 정보를 보여준다.

desc comm_pkg;

글로벌 변수의 사용

해당 패키지의 글로벌 변수를 임의로 조회, 조작할 수 있다. 단, 반드시 패키지명을 함꼐 붙일 것.

exec comm_pkg.g_comm := 0.35
exec comm_pkg.rest_comm(0.3)
exec dbms_output.put_line(comm_pkg.g_comm)

수정시 현재 세션에 대해서는 지속적으로 그 값을 유지하지만, 다른 세션이나 세션이 종료될 경우 초기 값을 사용한다.

글로벌 변수의 자동 초기화

PRAGMA SERIALLY_REUSABLE을 글로벌 변수로서 프로시저가 끝나면 초기 값으로 되돌아 오고 싶을 때 사용

SEPC과 BODY에 모두 기술해야 함.

패키지 오버로딩 (overloading)

동일한 이름의 서브 프로그램을 생성할 때 사용 기법. - 다형성 (객체지향 프로그램)

동일한 이름의 프로시저, 함수를 만들 수 있다.
형식 파라미터의 개수, 타입 또는 모드가 달라여 된다.

  • 패키지
CREATE OR REPLACE PACKAGE pack_over
IS
    TYPE t_date_tab IS TABLE OF date INDEX BY pls_integer;
    TYPE t_num_tab IS TABLE OF number INDEX BY pls_integer;
    
    PROCEDURE init(tab OUT t_date_tab,n IN number);
    PROCEDURE init(tab OUT t_num_tab,n IN number);
    
END pack_over;
/

CREATE OR REPLACE PACKAGE BODY pack_over
IS
    PROCEDURE init(tab OUT t_date_tab,n IN number)
    IS
    BEGIN
        FOR i in 1..n LOOP
            tab(i) := sysdate + i;
        END LOOP;
    END init;
    
    PROCEDURE init(tab OUT t_num_tab,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.t_date_tab;
    num_tab pack_over.t_num_tab;
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;
/

임의로 데이터타입을 선언해 사용할 수 없다.
아마 참조에 의한 방식으로 사용하게 하려고 하지 않을까?! (pointer 관점)

배열은 대용량 데이터이기 때문에 데이터를 직접 전달 하는것은 성능상으로 문제가 발생할 수 있다. 즉 패키지에서 생성한 데이터타입을 참조(주소적으로) 하도록 유도하기 위해 오류를 발생시킨다.

호환성 문제이다. user_define_type(collection_type)은 오라클에서 만든 타입이 아니기 때문에 해당 타입에 대해서는 있는 그대로 써야한다.

declare
    TYPE t_date_tab IS TABLE OF date INDEX BY pls_integer; -- 사용할 수 없음
    date_tab t_date_tab;
    num_tab pack_over.t_num_tab;
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;
/

패키지 커서의 지속상태

### 패키지에서 선언한 커서를 open하게 되면 현재 session이 열려있는 동안 계속 open되어 있다. 현재 session을 닫거나 cursor를 close할 때 까지 open 상태를 유지한다.

  • spec
CREATE OR REPLACE PACKAGE pack_cur
IS
    PROCEDURE   open;
    PROCEDURE   next(p_num in number);
    PROCEDURE   close;
    
END pack_cur;
/
  • body
CREATE OR REPLACE PACKAGE BODY pack_cur
IS
    CURSOR c1 IS 
        SELECT employee_id, last_name
        FROM    hr.employees
        ORDER BY 1 desc;
        
    v_no number; -- protect
    v_name varchar2(30); -- protect
    
    PROCEDURE open
    IS
    BEGIN
        if not c1%ISOPEN then
            open c1;
            dbms_output.put_line('c1 cursor is opend');
        end if;
    END open;
    -- 해당 프로시저에서 프로그램이 종료되어도 계속 열여 있다.
    -- 세션이 종료되지 않거나 close 하기 전까지 계속 열려있다.
    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;
    
    PROCEDURE close
    IS
    BEGIN
        IF c1%ISOPEN THEN
            close c1;
            dbms_output.put_line('c1 is closed');
        END IF;
    END close;
END pack_cur;
/

결과 :

느낀 점

주석처리 주의, 단일행 주석보다는 여러행 주석을 사용권장한다.

PRAGMA SERIALLY_REUSABLE
mod의 오버로딩
userdefine타입 package 테스트

profile
privatekim

0개의 댓글