오라클 패키지(Package), ONE TIME ONLY PROCEDURE, 전위적 선언(Forward Declaration),

오라클 패키지(Package), ONE TIME ONLY PROCEDURE, 전위적 선언(Forward Declaration),

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=584

 

오라클 패키지(Package), ONE TIME ONLY PROCEDURE, 전위적 선언(Forward Declaration),

오라클 패키지(Package), ONE TIME ONLY PROCEDURE, 전위적 선언(Forward Declaration),8. 오라클패키지(Package) 8.1 개요 및 기본문법 n 패키지란? 연관성 있는, 논리적으로 연관된 PL/SQL 타입(Types), 변수(Variable), 서브프로그램(프러시저, 함수)의 그룹이며 오라클 스키마 오브젝트이다.…

ojc.asia

8. 오라클 패키지(Package)

8.1 개요 및 기본문법

n 패키지란? 연관성 있는, 논리적으로 연관된 PL/SQL 타입(Types), 변수(Variable), 서브프로그램(프러시저, 함수)의 그룹이며 오라클 스키마 오브젝트이다. 패키지 명세(Specification)와 패키지 바디(Body)로 나뉘어지며 바디는 생략 가능하다.

n 패키지 명세는 Create Package 명령으로, 패키지 바디는 Create Package Body 명령으로 작성한다.

n 패키지 명세에서 정의한 서브프로그램 및 변수, 타입만 외부에서 접근, 호출 가능하다.

n 패키지 명세 : 타입, 전역변수, 커서, 예외, PL/SQL 프로그램에 대한 선언

n 패키지 바디 : 패키지 명세에서 정의한 모든 커서, 서브프로그램을 구현

[장점]

n 모듈화 : 연관된 타입, 서브프로그램들을 PL/SQL 모듈안에 캡슐화

n 쉬운 응용프로그램 디자인 : 패키지 명세를 통한 커서, 예외, 함수나 프로시저의 구조를 정의하고 이를 먼저 컴파일하는 것이 가능하다.

n 정보의 숨김 : 패키지를 통해 타입, 서브프로그램 등을 PUBLIC, PRIVATE으로 정의가능 하다.

n 기능 추가의 용이 : 패키지 명세에서 정의한 커서 or 예외, PUBLIC 변수들은 세션동안 유효하므로 이것들을 활용한 새로운 기능의 추가가 용이하다.

n 성능 향상 : 패키지안의 프로시저등이 최초 호출될 때 전체 패키지가 메모리에 로드 되므로 이후 호출 부터는 DISK I/O는 없다.

[형식]

[패키지 명세] CREATE [OR REPLACE] PACKAGE PACKAGE_NAME IS [variable declaration] [cursor declaration] [exception declaration] [procedure declaration] [function declaration] END; [패키지 바디] CREATE [OR REPLACE] PACKAGE BODY [PACKAGE_NAME] IS [variable declaration] [cursor implementation] [exception implementation] [procedure bodies] [function bodies] END;

-- 아래 간단히 패키지 Helloworld를 만들어 보자. SQL> create or replace package pkgtest is procedure p_test(p_name varchar2); end; / SQL> create or replace package body pkgtest is procedure p_test(p_name varchar2) is begin dbms_output.put_line('HelloWorld ' || p_name); end; end pkgtest; / SQL> set serveroutput on SQL> exec pkgtest.p_test('오라클자바커뮤니티') HelloWorld 오라클자바커뮤니티 PL/SQL 처리가 정상적으로 완료되었습니다.

8.2 전위적 선언(Forward Declaration)

n 동일한 PL/SQL블록에 여러 개의 Sub Program들이 올 수 있는데 어떤 프러시저/함수에서 다른 프로시저/함수를 호출할 때 호출당하는 프러시저/함수는 먼저 선언 되어야 한다. 먼저 선언하지 않으려면 호출하는 프러시저/함수의 앞쪽에 만들면 된다.

SQL> DECLARE -- proc2 선언 및 구현 PROCEDURE proc2(num NUMBER) IS BEGIN proc1(); END; -- proc1 구현 PROCEDURE proc1 IS BEGIN proc2(8514790) ; END; BEGIN NULL; END; / proc1(); * 5행에 오류: ORA-06550: 줄 5, 열9:PLS-00313: 범위 내에 'PROC1'(이)가 선언되지 않았습니다 ORA-06550: 줄 5, 열9:PL/SQL: Statement ignored SQL> DECLARE -- proc1 선언 PROCEDURE proc1; -- proc2 선언 및 구현 PROCEDURE proc2(num NUMBER) IS BEGIN proc1(); END; -- proc1 구현 PROCEDURE proc1 IS BEGIN proc2(8514790) ; END; BEGIN NULL; END; / PL/SQL 처리가 정상적으로 완료되었습니다. SQL> DECLARE -- proc1 구현 PROCEDURE proc1 IS BEGIN NULL; END; -- proc2 선언 및 구현 PROCEDURE proc2(num NUMBER) IS BEGIN proc1(); END; BEGIN NULL; END; / PL/SQL 처리가 정상적으로 완료되었습니다.

8.3 ONE TIME ONLY PROCEDURE

n 객체지향 프로그래밍의 초기화블록 처럼 패키지에서도 초기화 블록이 존재하는데 패키지내 함수나 프로시저가 최초 호출될 때 딱한번 호출되는 BEGIN~END 절을 만들 수 있는데 이를 One Time Only 프로시저라고 하고 주로 패키지내에서의 초기화 작업을 수행한다.

n 패키지 바디의 맨 뒤쪽에 기술한다.

-- 이번에는 좀더 복잡한 패키지를 만들어 보자. SQL> create table emp_log ( log_date date, log_user varchar2(50), program varchar2(50) ); 테이블이 생성되었습니다. SQL> CREATE OR REPLACE PACKAGE emp_pkg AS /* 외부에서 접근가능한 TYPE, CURSOR, EXCEPTION */ TYPE EmpRecType IS RECORD (ename emp.ename%TYPE, sal emp.sal%TYPE); CURSOR desc_sal RETURN EmpRecType; -- 커서를 패치할 때 EmpRecType으로 받아야 함 /* emp 테이블의 insert 건수를 기록, 전역변수 */ insert_cnt NUMBER; /* 외부에서 호출 가능한 서브 프로그램들 */ FUNCTION insert_emp ( p_ename emp.ename%TYPE, p_sal emp.sal%TYPE, p_deptno dept.deptno%TYPE) RETURN emp.empno%TYPE; PROCEDURE fire_emp (p_empno emp.empno%TYPE); FUNCTION nth_highest_sal (p_rank NUMBER) RETURN EmpRecType; END emp_pkg; / 패키지가 생성되었습니다. SQL> CREATE OR REPLACE PACKAGE BODY emp_pkg IS /* desc_sal 커서에 대한 구현부 */ CURSOR desc_sal RETURN EmpRecType IS SELECT ename, sal FROM emp ORDER BY sal DESC; /* insert_emp 함수의 구현부 */ FUNCTION insert_emp ( p_ename emp.ename%TYPE, p_sal emp.sal%TYPE, p_deptno dept.deptno%TYPE) RETURN emp.empno%TYPE IS new_empno NUMBER(5); BEGIN SELECT max(empno) + 1 into new_empno FROM EMP; INSERT INTO emp (empno, ename, sal, deptno) VALUES ( new_empno, p_ename, p_sal, p_deptno ); COMMIT; insert_cnt := insert_cnt + 1; RETURN new_empno; END insert_emp; /* fire_emp 프로시저 구현부 */ PROCEDURE fire_emp (p_empno emp.empno%TYPE) IS BEGIN DELETE FROM emp WHERE empno = p_empno; COMMIT; END fire_emp; /* nth_highest_salary함수 구현부 급여 순위 몇번째에 해당 하는 사원 리턴 */ FUNCTION nth_highest_sal (p_rank NUMBER) RETURN EmpRecType IS --커서가 리턴하는 EmpRecType을 받기위한 변수 emp_rec EmpRecType; BEGIN OPEN desc_sal; --패키지에서 정의한 전역커서 사용 FOR i IN 1..p_rank LOOP -- 커서의 리턴형이 EmpRecType이므로,, -- 만약 커서의 RETURN형이 선언 안되었으면 두개의 칼럼(ename, sal)을 -- 각각 변수로 받아야 함 FETCH desc_sal INTO emp_rec; END LOOP; CLOSE desc_sal; RETURN emp_rec; END nth_highest_sal; -- 패키지가 최초 호출될 때 한번만 호출 -- one time only procedure BEGIN INSERT INTO emp_log VALUES (SYSDATE, USER, 'EMP_PKG'); COMMIT; insert_cnt := 0; * END emp_pkg; / 패키지 본문이 생성되었습니다. SQL> variable empno number SQL> exec :empno := emp_pkg.insert_emp('이종철',9990, 30) PL/SQL 처리가 정상적으로 완료되었습니다. SQL> print empno EMPNO ---------- 10000 SQL> exec emp_pkg.fire_emp(10000) PL/SQL 처리가 정상적으로 완료되었습니다. SQL> select empno, ename from emp where empno = 10000; 선택된 레코드가 없습니다. SQL> set serveroutput on SQL> DECLARE emp_rec emp_pkg.EmpRecType; BEGIN emp_rec := emp_pkg.nth_highest_sal(1); dbms_output.put_line('급여 최고높은 사원 :' || emp_rec.ename); END; / 급여 최고높은 사원 :오라클자바커뮤니티 SQL> BEGIN DBMS_OUTPUT.PUT_LINE('insert 건수 :' || emp_pkg.insert_cnt); END; / insert 건수 :1 SQL> select * from emp_log; LOG_DATE LOG_USER PROGRAM -------- -------------------------------------------------- -------- 16/01/01 SCOTT EMP_PKG

[LAB]

- 패키지 명세가 아래와 같다. 패키지 바디를 구현하고 각각 호출하여 테스트 하세요. create or replace package emp_pkg2 as -- 사번을 받아 사원명을 출력 procedure print_ename( p_empno number ); -- 사번을 받아 관리자 이름을 출력(mgr 칼럼이 관리자ID 임) procedure print_mname( p_empno number ); -- 부서코드를 입력받아 해당 부서원들의 사번, 이름, 급여추출 function get_emps(p_deptno number) return sys_refcursor; end emp_pkg2; / http://ojc.asia/bbs/board.php?bo_table=Cyber&wr_id=7567

​