ORACLE PACKAGE INTRODUCTION 프로그램단위 1 의하나인패키지는특정처리를위해관련된 PL/SQL 블록들이논리적으로하나의그룹을이루는특수한형태이다. 이때 PL/SQL 블록들은프로시저또는함수가될수있으며, 또한패키지는오라클에서제공하는 PL/SQL 데이터유형, 복합유형등을포함하기도한다. 패키지는두부분으로구성된다. 하나는패키지기술부 (Package Specification) 이며, 다른하나는패키지몸체부 (Package Body) 이다. 이두개의구성이하나의패키지를이루게된다. 패키지기술부의역할은패키지를통해접근 (public 2 ) 될수있는프로시저, 함수, 변수등에대한프로토타입 (Prototype) 을선언하는것이다. 즉패키지기술부는인터페이스가되는것이다. 이부분을통해패키지에대한전체적인정보를얻게된다. 이렇게선언된프로시저와함수는패키지몸체에서실제처리되는절차적인부분이정의된다. 물론패키지기술부에선언되지않은별개의변수나프로시저들이지역적인 (private 3 ) 의미로패키지몸체부에정의될수있다. 패키지몸체부에있는프로시저나함수들은패키지기술부에선언된변수나예외등에접근할수있다. 패키지를개발할때는우선패키지기술부를먼저생성하고, 해당패키지몸체부를생성한다. 생성된패키지의구성요소 ( 멤버 ) 에접근할때에는패키지명을접두어로기술해야한다. 만약, 생성된패키지를변경하는경우, 패키지기술부가변경되었다면무조건패키지몸체부는다시재생성해야하며패키지를참조 ( 호출 ) 하는서브프로그램들도재번역 (recompile) 해야한다. 이에반하여패키지몸체부는변경되면패키지기술부와관련서브프로그램에영향을주지않고몸체부만재생성될수있다. 이는패키지를참조또는접근하는오브젝트에게영향을주지않는다 ( 이부분 오브젝트종속성 에대한자세한사항은마지막장에서설명하기로한다.). 1 PL/SQL 블록은데이터베이스에오브젝트로서저장될수있는데, 이를서브프로그램또는프로그램단위라부르며, 그유형은프로시저 (Procedure), 함수 (Function), 패키지 (Package), 트리거 (Trigger) 가있다 2 패키지의기술부에선언된것들은모두접근유형이 PUBLIC 으로외부의프로그램또는다른사용자로부터접근 ( 호출 ) 이가능하다는것이다. 3 패키지의몸체부에정의된것들, 즉기술부에선언된것이외의것들의접근유형은 PRIVATE 로외부에서접근될수없다. 그러나몸체부내부의다른요소로부터는접근이가능하다. Written by AngelaLEE 6-1 www.muhanedu.net
자, 그렇다면왜패키지를사용하는가? 주된이유 ( 장점 ) 는여러가지가있는데, 첫째는처음으로패키지내의한구성요소 ( 변수, 프로시저, 함수 ) 가호출될당시패키지전체가메모리에적재 (load) 되므로이후의패키지접근은스토리지 ( 디스크 ) 에대한입출력없이도빠르게수행될수있으므로성능측면에서보면좋은성능향상을얻게되는장점이며, 둘째는전역변수 (Global Variable) 를선언할수있다는것이다. 이는패키지기술부에변수를선언함으로써가능한데, 세션내에서세션레벨의전역변수로서참조될수있다. 셋째는패키지관련서브프로그램들이하나의그룹을이루게되므로, 유지관리가매우효율적이라는것이다. 넷째는서브프로그램 ( 프로시저, 함수 ) 의오버로딩 (Overloading 1 ) 기능을제공한다는것이다. 이는단독 (Stand-alone) 서브프로그램에서는불가능하다. 이오버로딩 (overloading) 은형식매개변수의특정데이터유형또는개수에제약을가지지않으므로, 사용자에게도응용프로그램개발에도한층더진보된유연성 (flexibility) 을제공한다. 제약사항이있다면형식매개변수의데이터유형이서로비슷한계열 (same family) 에속할경우오버로딩을할수없다는것과함수의오버로딩에서는리턴유형이다른계열에속한다해도불가능하다. 즉리턴유형은동일해야한다는것이다. 오버로딩된서브프로그램이실행될때는오라클이자동적으로가장적절한서브프로그램으로처리하도록해준다. 다섯째는데이터의은닉 (Encapsulation, Hiding Information) 으로, 패키지에대한접근 (public, private) 을제한하고, 패키지몸체부에대한암호화를통해보안을가능하게한다. * 참고 : 오라클은기본적으로제공하는프로시저, 함수, 패키지들이많이있다. 이는오라클의사용자로하여금편리하고효율적인데이터베이스처리를도와주기위함이다. 여기에더불어오라클은사용자정의서브프로그램을개발하여사용할수있도록 PL/SQL 도제공함으로써다양한비즈니스규칙을구현할수있는기능도제공한다. 1 이기능은여러프로그래밍언어에서제공되는것으로, 오라클의패키지에서도가능하다. 오버로딩은동일한이름의프로시저또는함수를패키지내에여러개정의할수있는것으로형식매개변수의개수, 순서, 데이터유형이다르게정의된다. 이는동일한처리를하되다양한데이터를처리를위해서아주유용한개념이다. Written by AngelaLEE 6-2 www.muhanedu.net
PACKAGE Structure 패키지는두부분으로나누어지는데, 하나는패키지기술부 (Specification) 다른하나는패키지몸체부 (Body) 로구성된다. 패키지기술부에선언된것들은모두접근유형이 public(global) 의의미를가진다. >> 패키지의구성과접근 ( 참조 ) Package Specification 1 2 Procedure ANGELA ; -- 선언 1 Public Variable 2 Public Procedure 3 3 Private Variable Package Body 4 Procedure WIN ; -- 정의 5 Procedure ANGELA ; -- 정의 4 Private Procedure 5 Public Procedure 6 6 Local Variable QUIZ: 위의그림에서 1 public variable 의사용범위는? 5 번에서접근가능한것을모두선택한다면? Written by AngelaLEE 6-3 www.muhanedu.net
패키지기술부 (Package Specification) 생성 > SYNTAX: CREATE [OR REPLACE] PACKAGE package_name IS AS Public type and item declarations Subprogram specifications END package_name ; OR REPLACE 생성하고자하는패키지가기존에동일명으로존재할경우, 기존의내용을현재의내용으로수정하는옵션.( 만약기존에존재하지않는다면큰의미는없다.) Package_name 생성하고자하는패키지명으로스키마내에서는유일한이름이어야한다. 패키지기술부와패키지몸체부의패키지명이동일해야만한다. Public type and item declarations 변수, 상수, 명시적커서, 사용자정의예외, PRAGMA 등을선언한다. 이들은 PUBLIC 의의미를지닌다. Subprogram specifications PL/SQL 서브프로그램을선언한다. 선언할때에는형식매개변수를포함한헤더만을기술한다. > EX: CREATE OR REPLACE PACKAGE comm_pack IS g_comm NUMBER := 0.1 ; -- 0.1 로초기화 PROCEDURE reset_comm ( p_com IN NUMBER) ; END comm_pack ; Written by AngelaLEE 6-4 www.muhanedu.net
패키지몸체부 (Package Body) 생성 > SYNTAX: CREATE [OR REPLACE] PACKAGE BODY package_name IS AS Private type and item declarations Subprogram bodies END package_name ; OR REPLACE 생성하고자하는패키지가기존에동일명으로존재할경우, 기존의내용을현재의내용으로수정하는옵션.( 만약기존에존재하지않는다면큰의미는없다.) Package_name 생성하고자하는패키지명으로스키마내에서는유일한이름이어야한다. 패키지기술부와패키지몸체부의패키지명이동일해야만한다. Private type and item declarations 변수, 상수, 명시적커서, 사용자정의예외, PRAGMA 등을선언한다. 이들은지역적인의미를지닌다. Subprogram bodies 패키지기술부에서선언한 PUBLIC 서브프로그램이든, 지역적으로선언된 PRIVATE 서브프로그램이든실제처리내용 ( 몸체 ) 을기술한다. 단주의해야할사항은서브프로그램의순서이다. 기본적으로참조되는변수든서브프로그램이든참조하는서브프로그램보다는먼저정의되어야한다. 보통 PUBLIC 의서브프로그램은마지막부분에정의된다. Written by AngelaLEE 6-5 www.muhanedu.net
> EX: CREATE OR REPLACE PACKAGE BODY comm_pack IS FUNCTION validate_comm (p_comm IN NUMBER) RETURN BOOLEAN IS v_max_comm NUMBER ; BEGIN SELECT MAX(comm.) INTO v_max_comm FROM EMP ; IF p_comm > v_max_comm THEN RETURN(FALSE) ; ELSE RETURN(TRUE) ; END IF ; END validate_comm ; PRCEDURE reset_comm(p_comm IN NUMBER) IS BEGIN IF validate_comm(p_comm) THEN ELSE END IF ; g_comm := p_comm ; RAISE_APPLICATION_ERROR (-20100, Invalid Commission ) ; END reset_comm ; END comm_pack ; Written by AngelaLEE 6-6 www.muhanedu.net
패키지호출 ( 실행 ) 다양한환경에서패키지는호출되어실행된다. 단, 생성된패키지오브젝트에대한실행권한을가진사용자만이패키지를호출하여실행할수있다. 기본적으로패키지몸체부에서정의된 validate_comm 함수 (private) 는몸체부내에서는참조가가능하다. 물론외부에서는접근이불가능하다. 호출할때는함수이므로수식의일부로호출하여실행한다. 위에서생성한 comm_pack 패키지를 isql*plus 에서호출하려면, EXECUTE comm_pack.reset_comm(0.15) 만약위의패키지가 SCOTT 스키마오브젝트라가정하고, 이패키지에대한실행권한을 DEMO 사용자에게부여했다면, DEMO 세션에서패키지를실행하려면, EXECUTE scott.comm_pack.reset_comm(0.15) 원격에있는데이터베이스에서패키지를호출하려면, EXECUTE comm_pack.reset_comm@anyware(0.15) 패키지삭제 패키지를삭제할때에는패키지기술부와패키지몸체부를모두삭제한다. DROP PACKAGE package_name ; 패키지몸체부만을삭제할수도있다. DROP PACKAGE BODY package_name ; Written by AngelaLEE 6-7 www.muhanedu.net
ORACLE- Supplied PACKAGE 오라클서버는다수의유용한패키지들을제공하는데, 이패키지들은데이터베이스에대한기능을확장할뿐만아니라더불어사용자로하여금데이터베이스에대한접근 / 처리를간단하게그리고편리하게할수있도록도와준다. 일반적으로제공되는표준패키지들은 catproc.sql 을실행함으로써생성된다. >> ORACLE Supplied Packages Built-in name Description CALENDAR Provides calendar maintenance functions. DBMS_ALERT DBMS_APPLICATION_INFO DBMS_AQ DBMS_AQADM DBMS_DDL DBMS_DEBUG DBMS_DEFER DBMS_DEFER_QUERY DBMS_DEFER_SYS Supports asynchronous notification of database events. Messages or alerts are sent on a COMMIT command. Message transmittal is one way, but one sender can alert several receivers. Is used to register an application name with the database for auditing or performance tracking purpose. Provides message queuing as part of the Oracle server, is used to add a message(of a predefined object type) onto a queue or dequeue a message. Is used to perform administrative functions on a queue or queue table for messages of a predefined object type Is used to embed the equivalent of the SQL commands ALTER, COMPILE, and ANALYZE within your PL/SQL programs A PL/SQL API to the PL/SQL debugger layer, Probe, in the Oracle server Is used to build and administer deferred remote procedure calls(use of this feature requires the Replication Option) Written by AngelaLEE 6-8 www.muhanedu.net
Built-in name DBMS_DESCRIBE DBMS_DISTRIBUTED_TRUST_ADMIN DBMS_HS DBMS_HS_EXTPROC DBMS_HS_PASSTHROUGH DBMS_IOT DBMS_JOB DBMS_LOB DBMS_LOCK DBMS_LOGMNR DBMS_LOGMNR_D Description Is used to describe the arguments of a stored procedure Is used to maintain the Trusted Servers list, which is used in conjunction with the list at the central authority to determine whether a privileged database link from a particular server can be accepted. Is used to administer heterogeneous services by registering or dropping distributed external procedures, remote libraries, and non-oracle systems(you use dbms_hs to create or drop some initialization variables for non-oracle systems) Enables heterogeneous services to establish security for distributed external procedures Enables heterogeneous services to send pass-through SQL statements to non-oracle systems Is used to schedule administrative procedures that you want performed at periodic interval; is also the interface for the job queue Is used to schedule administrative procedures that you what performed at periodic intervals. Provides general purpose routines for operations on Oracle large objects(lobs) data types; BLOB, CLOB and BFILES(read-only) Is used to request, convert, and release locks through Oracle Lock Management services. Provides functions to initialize and run the log reader Queries the dictionary tales of the current database, and creates a text base file containing their contents Written by AngelaLEE 6-9 www.muhanedu.net
Built-in name Description DBMS_OFFLINE_OG Provides public APIs for offline instantiation of master groups DBMS_OFFLINE_SNAPSHOT Provides public APIs for offline instantiation of snapshots DBMS_OLAP DBMS_ORACLE_TRACE_AGENT DBMS_ORACLE_TRACE_USER DBMS_OUTPUT DBMS_PCLXUTIL DBMS_PIPE DBMS_PROFILER DBMS_RANDOM DBMS_RECTIFIER_DIFF DBMS_REFRESH Provides procedures for summaries, dimensions, and query rewrites Provides client callable interfaces to the Oracle TRACE instrumentation within the Oracle7 server Provides public access to the Oracle7 release server Oracle TRACE instrumentation for the calling user Accumulates information in a buffer so that it can be retrieved out later Provides intrapartition parallelism for creating partition-wise local indexes Provides a DBMS pipe service that enables message to be sent between sessions Provides a Probe Profiler API to profile existing PL/SQL applications and identify performance bottlenecks Provides a built-in random number generator Provides APIs used to detect and resolve data inconsistencies between two replicated sites Is used to create groups of snapshots that can be refreshed together to a transactionally consistent point in time; requires the Distributed option DBMS_REPAIR Provides data corruption repair procedures DBMS_REPCAT Provides routines to administer and update the replication catalog and environment; requires the Replication option Written by AngelaLEE 6-10 www.muhanedu.net
Built-in name Description DBMS_REPCAT_ADMIN Is used to create users with the privileges needed by the symmetric replication facility; requires the Replication option DBMS_REPCAT_INSTANTIATE Instantiate deployment templates; requires the Replication option DBMS_REPCAT_RGT Controls the maintenance and definition of refresh group templates; requires the Replication option DBMS_REPUTIL DBMS_RESOURCE_MANAGER Provides routines to generate shadow tables, triggers, and packages for table replication Maintains plans, consumer groups, and plan directives; it also provides semantics so that you may group together changes to the plan schema DBMS_RESOURCE_MANAGER_PRIVS Maintains privileges associated with resource consumer groups DBMS_RLS Provides row-level security administrative interface DBMS_ROWID DBMS_SESSION DBMS_SHARED_POOL DBMS_SNAPSHOT DBMS_SPACE Is used to get information about ROWIDs, including the data block number, the object number, and other componets Enables programmatic use of the SQL ALTER SESSION statement as well as other session-level commands Is used to keep objects in shared memory, so that they are not aged out with the normal LRU mechanism Is used to refresh one or more snapshots that are not part of the same refresh group and purge logs; use of this feature requires the Distributed option Provides segment space information not available through standard views Written by AngelaLEE 6-11 www.muhanedu.net
Built-in name Description DBMS_SPACE_ADMIN DBMS_SQL DBMS_STANDARD DBMS_STATS DBMS_TRACE Provides tablespace and segment space administration not available through standard SQL Is used to write stored procedure and anonymous PL/SQL blocks using dynamic SQL; also used to parse and DML or DDL statement Provides language facilities that help your application interact with the Oracle server Provides a mechanism for users to view and modify optimizer statistics gathered for database objects Provides routines to start and stop PL/SQL tracing DBMS_TRANSACTION Provides procedures for a programmatic interface to transaction management DBMS_TTS DBMS_UTILITY DBMS_EXTPROC DBMS_PKG PLITBLM Checks whether if the transportable set is self-contained Provides functionally for managing procedures, reporting errors, and other information Is used to debug external procedures on platforms with debuggers that can attach to a running process Provides the interface for procedures and functions associated with management of stored outlines Handles index-table operations SDO_ADMIN Provides functions implementing spatial index creation and maintenance for spatial objects SDO_GEOM Provides functions implementing geometric operations on spatial objects Written by AngelaLEE 6-12 www.muhanedu.net
Built-in name Description SDO_MIGRATE Provides functions for migrating spatial data from release 7.3.3 and 7.3.4 to 8.1.x SDO_TUNE Provides functions for selecting parameters that determine the behavior of the spatial indexing schema used in the Spatial Cartridge STANDARD Declares types, exception, and subprograms that are available automatically to every PL/SQL program TIMESERIES Provides functions that perform operations, such as extraction, retrieval, arithmetic, and aggregation, on time series data TIMESCALE Provides scale-up and scale-down functions TSTOOLS Provides administrative tools procedures UTL_COLL Enables PL/SQL programs to use collection locators to query and update UTL_FILE UTL_HTTP Enables your PL/SQL programs to read and write operating system(os) text files and provides a restricted version of standard OS stream file I/O Enables HTTP callouts from PL/SQL and SQL to access data on the Internet or to call Oracle Web Server Catridges UTL_PG Provides functions for converting COBOL numeric data into Oracle numbers and Oracle numbers into COBOL numeric data UTL_RAW Provides SQL functions for RAW data types that concatenate, obtain substring, and so on, to and from RAW data types Written by AngelaLEE 6-13 www.muhanedu.net
Built-in name UTL_REF VIR_PKG Description Enables a PL/SQL program to access an object by providing a reference to the object Provides analytical and conversion functions for visual information retreival * 는뒷부분의실습을통해이해를돕는패키지을표시한것임. Written by AngelaLEE 6-14 www.muhanedu.net
실습 ORACLE PACKAGE 1. [ PACKAGE I ] 전역변수만을가지는 GLOBAL_CONST 패키지. CREATE OR REPLACE PACKAGE global_const IS mile_to_kilo CONSTANT NUMBER := 1.6093 ; kilo_to_mile CONSTANT NUMBER := 0.6214; yard_to_meter CONSTANT NUMBER := 0.9144 ; meter_to_yard CONSTANT NUMBER := 1.0936 ; END global_const ; / Written by AngelaLEE 6-15 www.muhanedu.net
Hint : 생성된패키지를 SQL*Plus(iSQL*Plus) 에서실행하기위한예제. SQL> EXECUTE DBMS_OUTPUT.PUT_LINE( 20 miles = 20 * global_const.mile_to_kilo km ) Stand-alone 프로시저에서생성된패키지를호출하는예제. (p0601_run.sql 로저장하여실습 ) CREATE OR REPLACE PROCEDURE meter_to_yard (p_meter IN NUMBER, p_yard OUT NUMBER) IS BEGIN p_yard := p_meter * global_const.meter_to_yard ; END meter_to_yard ; / VARIABLE yard NUMBER EXECUTE meter_to_yard(1, :yard) ; PRINT yard Point : 위의 global_const 패키지는전역상수를사용하기위한것으로, 패키지몸체부를필요로하지않는다. 다시말해서패키지는기술부만으로도생성이가능하다. Written by AngelaLEE 6-16 www.muhanedu.net
2. [ PACKAGE II : OVERLOADING ] ADD_DEPT 프로시저에대한오버로딩을가지는 OVERLOAD_PACK 패키지. CREATE OR REPLACE PACKAGE overload_pack IS PROCEDURE add_dept (p_deptno IN dept.deptno%type, p_name IN dept.dname%type DEFAULT unknown, p_loc IN dept.loc%type DEFAULT 0) ; PROCEDURE add_dept (p_name IN dept.dname%type DEFAULT unknown, p_loc IN dept.loc%type DEFAULT 0) ; END overload_pack; / CREATE OR REPLACE PACKAGE BODY overload_pack IS PROCEDURE add_dept (p_deptno IN dept.deptno%type, p_name IN dept.dname%type DEFAULT unknown, p_loc IN dept.loc%type DEFAULT 0) IS BEGIN INSERT INTO DEPT VALUES(p_deptno, p_name, p_loc) ; END add_dept ; PROCEDURE add_dept (p_name IN dept.dname%type DEFAULT unknown, p_loc IN dept.loc%type DEFAULT 0) IS BEGIN INSERT INTO DEPT VALUES(dno_seq.nextval, p_name, p_loc) ; END add_dept ; END overload_pack ; / Written by AngelaLEE 6-17 www.muhanedu.net
힌트 : 생성된프로시저를 SQL*Plus(iSQL*Plus) 에서실행 / 확인하는예제 EXECUTE overload_pack.add_dept(45, AnyWare, Seoul ) EXECUTE overload_pack.add_dept( Marketing, DaeJeon ) SELECT * FROM DEPT ; Written by AngelaLEE 6-18 www.muhanedu.net
3. [ PACKAGE III : PACKAGE 초기화 ] 패키지가실행될때, 패키지레벨에서단한번의초기화를하는 INITIAL_PACK 패키지. CREATE OR REPLACE PACKAGE initial_pack IS g_max_bonus NUMBER ; FUNCTION bonus (p_bonus IN NUMBER) RETRUN NUMBER ; END initial_pack; / CREATE OR REPLACE PACKAGE BODY initial_pack IS v_rate NUMBER := 1.5 ; FUNCTION bonus (p_bonus IN NUMBER) RETRUN NUMBER IS BEGIN RETURN (p_bonus * v_rate) ; END bonus ; BEGIN SELECT max(sal) * v_rate INTO g_max_bonus FROM EMP ; END initial_pack ; / Written by AngelaLEE 6-19 www.muhanedu.net
힌트 : 생성된패키지를 SQL*Plus(iSQL*Plus) 에서실행 / 확인하는예제. EXECUTE DBMS_OUTPUT.PUT_LINE(initial_pack.g_max_bonus) SQL 문장에서패키지를실행 / 확인하는예제. > SELECT ENAME, SAL, initial_pack.bonus(sal), initial_pack.g_max_bonus FROM EMP ; 포인트 : 복잡한처리를통한전역변수초기화의경우패키지기술부에서는불가능하지만, 패키지레벨에서의초기화 ( 패키지몸체부에서의 BEGIN 부분 ) 를사용하면된다. Written by AngelaLEE 6-20 www.muhanedu.net
4. [ PACKAGE IV : 전역변수의 Scope Rule ] COMM_PACK 패키지의전역변수 G_COMM 의 Scope Rule 을이해하는예제 -- ( 가정 ) G_COMM 의초기값은 0.1, -- V_MAX_COMM 초기값은 0.4 09:00 09:30 EXEC comm_pack.reset_comm(0.25) V_MAX_COMM = 0.4 > 0.25 G_COMM = 0.25 INSERT INTO EMP(EMPNO, COMM) VALUES (9000, 0.8) ; V_MAX_COMM = 0.8 09:35 EXEC comm_pack.reset_comm(0.5) V_MAX_COMM = 0.8 > 0.5 G_COMM = 0.5 10:00 EXEC comm_pack.reset_comm(0.6) 10:30 10:35 V_MAX_COMM = 0.4 > 0.6 ORA-20100:Invalid commission ROLLBACK ; EXIT 13:00 로그인시 V_MAX_COMM = 0.4 G_COMM = 0.1 13:10 EXEC comm_pack.reset_comm(0.25) Written by AngelaLEE 6-21 www.muhanedu.net
포인트 : 기본적으로각세션은세션레벨에서의전역변수를참조할수있으며, 새로운세션이시작되면서패키지에접근이이루어지면전역변수는초기화가된다. Written by AngelaLEE 6-22 www.muhanedu.net
5. [ PACKAGE V : 전역커서의 Scope Rule] 전역커서의 Scope Rule 을이해하기위한 cursor_pack 패키지. CREATE OR REPLACE PACKAGE cursor_pack IS CURSOR cur1 IS SELECT empno FROM emp ORDER BY empno DESC ; PROCEDURE rows_01_03 ; PROCEDURE rows_04_06 ; END cursor_pack; / CREATE OR REPLACE PACKAGE BODY cursor_pack IS v_empno ; PROCEDURE rows_01_03 IS BEGIN OPEN cur1 ; LOOP FETCH cur1 INTO v_empno ; DBMS_OUTPUT.PUT_LINE( ID : v_empno) ; EXIT WHEN cur1%rowcount >= 3 ; END LOOP; END rows_01_03 ; PROCEDURE rows_04_06 IS BEGIN LOOP FETCH cur1 INTO v_empno ; DBMS_OUTPUT.PUT_LINE( ID : v_empno) ; EXIT WHEN cur1%rowcount >= 6 ; END LOOP; CLOSE cur1 ; END rows_04_06 ; END cursor_pack ; / Written by AngelaLEE 6-23 www.muhanedu.net
힌트 : 생성된패키지를 SQL*Plus(iSQL*Plus) 에서실행 / 확인하는예제. 포인트 : SET SERVEROUTPUT ON EXECUTE cursor_pack.rows01_03 ; EXECUTE cursor_pack.rows04_06 ; 전역커서도전역변수와마찬가지로세션레벨에서는언제라도어디에서라도참조가가능하다. Written by AngelaLEE 6-24 www.muhanedu.net
6. [ PACKAGE VI : 전역데이터유형의참조 ] 복합변수형을전역적으로선언했을때참조되는경우를이해하기위한예제. CREATE OR REPLACE PACKAGE type_pack IS TYPE emp_tab_type IS TABLE OF emp%rowtype INDEX BY BINARY_INTEGER ; PROCEDURE read_emp_tab ( p_emp_tab OUT emp_tab_type ) ; END type_pack; / CREATE OR REPLACE PACKAGE BODY type_pack IS PROCEDURE read_emp_tab ( p_emp_tab OUT emp_tab_type ) IS n BINARY_INTEGER := 0 ; BEGIN FOR emp_rec IN (SELECT * FROM EMP) LOOP p_emp_tab(n) := emp_rec ; n := n + 1 ; END LOOP ; END read_emp_tab ; END type_pack ; / 힌트 : 생성된패키지를 SQL*Plus(iSQL*Plus) 에서실행 / 확인하는예제. (p0606_run.sql 로저장하여실습 ) DECLARE v_emp_tab type_pack.emp_tab_type ; BEGIN type_pack.read_emp_tab(v_emp_tab) ; DBMS_OUTPUT.PUT_LINE( An sample: v_emp_tab(4).ename) ; END ; / Written by AngelaLEE 6-25 www.muhanedu.net
7. [ PACKAGE VII : ORACLE 제공패키지 DBMS_SQL (1)] 오라클이제공하는패키지중 DBMS_SQL 을이해하기위한예제. CREATE OR REPLACE PROCEDURE del_all_rows (p_tab_nm IN VARCHAR2, p_rows_del OUT NUMBER) IS cursor_nm INTEGER ; BEGIN cursor_nm := DBMS_SQL.OPEN_CURSOR ; DBMS_SQL.PARSE(cursor_nm, DELETE FROM p_tab_nm, DBMS_SQL.NATIVE) ; p_rows_del := DBMS_SQL.EXECUTE(cursor_nm) ; DBMS_SQL.CLOSE_CURSOR(cursor_nm) ; END ; / Written by AngelaLEE 6-26 www.muhanedu.net
힌트 : 생성된패키지를 SQL*Plus(iSQL*Plus) 에서실행 / 확인하는예제. (p0607_run.sql 로저장하여실습 ) VARIABLE deleted NUMBER EXECUTE del_all_rows( EMP_COPY, :deleted) PRINT deleted * 참고적으로 EMP_COPY 는 create table emp_copy as select * from emp; 명령으로생성한다. 포인트 : Dynamic SQL 의실행을지원하기위해제공되는 DBMS_SQL 은기본적인 SQL 문실행단계, 즉 Parse Bind Execute Fetch(SELECT only) 의처리과정을그대로구현한다. 오라클 8i 이후부터는 Native Dynamic SQL 도실행할수있다. (EXECUTE IMMEDIATE 명령을사용 ) DBMS_SQL 패키지내의멤버 ( 프로시저또는함수 ) 중에서,.. OPEN_CURSOR PARSE BIND_VARIABLE EXECUTE FETCH_ROWS CLOSE_CURSOR * dbmssql.sql 스크립트파일을참조 Written by AngelaLEE 6-27 www.muhanedu.net
8. [ PACKAGE VIII : ORACLE 제공패키지 DBMS_SQL (2)] 오라클이제공하는패키지중 DBMS_SQL 을이해하기위한예제. CREATE OR REPLACE PROCEDURE del_rows (p_tab_nm IN VARCHAR2, p_rows_del OUT NUMBER) IS cursor_nm INTEGER ; BEGIN EXECUTE IMMEDIATE delete from p_tab_nm ; p_rows_del := SQL%ROWCOUNT ; END ; / 힌트 : 생성된패키지를 SQL*Plus(iSQL*Plus) 에서실행 / 확인하는예제. (p0608_run.sql 로저장하여실습 ) VARIABLE deleted NUMBER EXECUTE del_rows( EMP_COPY, :deleted) PRINT deleted * 참고적으로 EMP_COPY 는 create table emp_copy as select * from emp; 명령으로생성한다. Written by AngelaLEE 6-28 www.muhanedu.net
9. [ PACKAGE VIIII : ORACLE 제공패키지 DBMS_SQL (3)] 오라클이제공하는패키지중 DBMS_SQL 을이해하기위한예제. CREATE OR REPLACE PROCEDURE drop_tab (p_tab_nm IN VARCHAR2) IS cursor_nm NUMBER ; err_msg VARCHAR2(255) ; BEGIN cursor_nm := DBMS_SQL.OPEN_CURSOR ; DBMS_SQL.PARSE(cursor_nm, DROP TABLE p_tab_nm, DBMS_SQL.NATIVE) ; DBMS_SQL.CLOSE_CURSOR(cursor_nm) ; EXCEPTION WHEN OTHERS THEN err_msg := SQLERRM ; DBMS_SQL.CLOSE_CURSOR(cursor_nm) ; RAISE_APPLICATION_ERROR(-20200, err_msg) ; END ; / 힌트 : 생성된패키지를 SQL*Plus(iSQL*Plus) 에서실행 / 확인하는예제. (p0609_run.sql 로저장하여실습 ) EXECUTE drop_tab( EMP_COPY ) SELECT * FROM EMP_COPY ; * 참고적으로 EMP_COPY 는 create table emp_copy as select * from emp; 명령으로생성한다. QUIZ : DROP_TAB 프로시저와동일하게 Dynamic SQL 을처리할수있도록수정해보시오. ( 참고 : EXCECUTE IMMEDIATE) Written by AngelaLEE 6-29 www.muhanedu.net
10. [ PACKAGE X : ORACLE 제공패키지 DBMS_DDL ] 오라클이제공하는패키지중 DBMS_DDL 을이해하기위한예제. CREATE OR REPLACE PROCEDURE analyze_object (p_obj_type IN VARCHAR2, p_obj_name IN VARCHAR2) IS BEGIN DBMS_DDL.ANALYZE_OBJECT( p_obj_type, USER, UPPER(p_obj_name), COMPUTE ) ; END ; / 힌트 : -- OBJECT_TYPE -- OWNER -- NAME -- METHOD 생성된패키지를 SQL*Plus(iSQL*Plus) 에서실행 / 확인하는예제. (p0610_run.sql 로저장하여실습 ) EXECUTE analyze_object( TABLE, EMP ) SELECT LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME = EMP ; 참고 : DBMS_DDL 패키지는 PL/SQL 프로그램내에서 SQL 의 DDL 문처리를제공하는패키지로, 트리거내에서는사용될수없다. 이패키지는 SYS 보다실행하고자하는사용자의권한으로실행한다. DBMS_DDL.ALTER_COMPILE( PROCEDURE, SCOTT, Q_EMP ) ; Written by AngelaLEE 6-30 www.muhanedu.net
11. [ PACKAGE XI : ORACLE 제공패키지 DBMS_JOB ] 오라클이제공하는패키지중 DBMS_JOB 을이해하기위한예제. VARIABLE jobno NUMBER BEGIN DBMS_JOB.SUBMIT( job => :jobno, what => OVERLOAD_PACK.ADD_DEPT( EDUCATION,2710) ;, next_date => TRUNC(SYSDATE + 1), interval => TRUNC(SYSDATE + 1) ); COMMIT; END; / PRINT jobno Written by AngelaLEE 6-31 www.muhanedu.net
힌트 : 생성된패키지를 SQL*Plus(iSQL*Plus) 에서실행 / 확인하는예제. (p0611_run.sql 로저장하여실습 ) 참고 : DBMS_JOB 패키지는 PL/SQL 프로그램들에대한 Job Scheduling 을위한패키지로, 데이터베이스의작업이적은시간대에일괄처리를위해스케쥴되어사용되거나프로그램에대한유지보수에도응용된다. DBMS_SQL 패키지내의멤버 ( 프로시저또는함수 ) 중에서,.. SUBMIT REMOVE CHANGE WHAT NEXT_DATE INTERVAL BROKEN RUN DBA_JOBS 딕셔너리뷰를사용해서스케쥴된작업들 (Submitted jobs) 의상태정보를확인할수있다. DBA_JOBS_RUNNING 딕셔너리뷰는지금실행되고있는작업들의정보를확인할수있다. Written by AngelaLEE 6-32 www.muhanedu.net
12. [ PACKAGE XII : ORACLE 제공패키지 UTL_FILE ] 오라클이제공하는패키지중 UTL_FILE 을이해하기위한예제. CREATE OR REPLACE PROCEDURE sal_status (p_filedir IN VARCHAR2, p_filename IN VARCHAR2) IS v_filehandle UTL_FILE.FILE_TYPE ; CURSOR emp_info IS SELECT ename, sal, deptno FROM emp ORDER BY deptno ; v_newdeptno emp.deptno%type ; v_olddeptno emp.deptno%type := 0 ; BEGIN v_filehandle := UTL_FILE.FOPEN(p_filedir, p_filename, w ) ; UTL_FILE.PUTF(v_filehandle, SAL REPORT : GENERATED ON %s\n, SYSDATE); UTL_FILE.NEW_LINE(v_filehandle) ; FOR v_emp_rec IN emp_info LOOP v_newdeptno := v_emp_rec.deptno ; IF v_newdeptno <> v_olddeptno THEN UTL_FILE.PUTF(v_filehandle, DEPARTMENT : %s\n, v_emp_rec.deptno) ; END IF; UTL_FILE.PUTF(v_filehandle, EMPLOYEE : %s\n, v_emp_rec.ename, v_emp_rec.sal) ; v_olddeptno := v_newdeptno ; END LOOP ; UTL_FILE.PUT_LINE(v_filehandle, *** END OF REPORT *** ) ; UTL_FILE.FCOLSE(v_filehandle) ; EXCEPTION END ; / WHEN UTL_FILE.INVALID_FILEHANDLE THEN RAISE_APPLICATION_ERROR(-20300, Invalid File. ); WHEN UTL_FILE.WRITE_ERROR THEN RAISE_APPLICATION_ERROR(-20400, Unable to write to file ) ; Written by AngelaLEE 6-33 www.muhanedu.net
힌트 : 생성된패키지를 SQL*Plus(iSQL*Plus) 에서실행 / 확인하는예제. EXECUTE sal_status( C:\UTL_FILE, SAL_RPT.TXT ) POINT : 접근할디렉토리는초기화파라미터 (UTL_FILE_DIR) 에정의되어야한다. ( 예 : UTL_FILE_DIR = C:\UTL_FILE) Written by AngelaLEE 6-34 www.muhanedu.net
13. [ PACKAGE XIII : ORACLE 제공패키지 DBMS_LOB (1) ] 오라클이제공하는패키지중 DBMS_LOB 을이해하기위한예제. > 우선실습관련테이블을생성하고, 데이터를간단하게입력한다. (p0613_ready.sql 로저장하여실습 ) CREATE TABLE personnel (id NUMBER(6) constraint personnel_id_pk PRIMARY KEY, last_name VARCHAR2(35), review CLOB, picture BLOB) ; CREATE TABLE review_table (employee_id NUMBER, ann_review VARCHAR2(2000) ); INSERT INTO personnel VALUES(2034, Allen, EMPTY_CLOB(), NULL); INSERT INTO personnel VALUES(2035, Bond, EMPTY_CLOB(), NULL); INSERT INTO review_table VALUES(2034, Very good performance this year. Recommanded to increase salary by $500 ); INSERT INTO review_table VALUES(2035, Excellent performance this year. Recommanded to increase salary by $1000 ); COMMIT ; > UPDATE 문장으로 CLOB 에데이터를변경하는예제 UPDATE personnel SET review = (SELECT ann_review FROM WHERE last_name = Allen ; review_table WHERE employee_id = 2034) ( 다음페이지에계속 ) Written by AngelaLEE 6-35 www.muhanedu.net
> DBMS_LOB 패키지를사용하여 CLOB 에데이터를변경하는예제 DECLARE lobloc CLOB ; text VARCHAR2(2000) ; amount NUMBER ; offset INTEGER ; BEGIN SELECT ann_review INTO text FROM review_table WHERE employee_id = 2035 ; SELECT review INTO lobloc FROM personnel WHERE last_name = Bond FOR UPDATE ; offset := 1; amount := length(text) ; DBMS_LOB.WRITE( lobloc, amount, offset, text ) ; END ; / Written by AngelaLEE 6-36 www.muhanedu.net
14. [ PACKAGE XIV : ORACLE 제공패키지 DBMS_LOB (2) ] 오라클이제공하는패키지중 DBMS_LOB 을이해하기위한예제. CREATE OR REPLACE PROCEDURE load_image (p_file_loc IN VARCHAR2) IS v_file BFILE ; BEGIN v_filename VARCHAR2(40) ; v_record_number NUMBER ; v_file_exists AN ; BOOLE CURSOR pic_cur IS SELECT country_id FROM country WHERE region_id = 1 FOR UPDATE ; DBMS_OUTPUT.PUT_LINE( LOADING LOCATORS TO IMAGES ) ; FOR country_record IN pic_cur LOOP LOOP v_filename := country_record.country_id.tif ; v_file := bfilename(p_file_loc, v_filename) ; v_file_exists := (DBMS_LOB.FILEEXISTS(v_file) = 1 ) ; IF v_file_exists THEN DBMS_LOB.FILEOPEN(v_file) ; UPDATE countries SET picture = bfilename(p_file_loc, v_filename) WHERE CURRENT OF country_pic_cursor ; DBMS_OUTPUT.PUT_LINE( LOADED LOCATOR TO FILE : v_filename SIZE : DBMS_LOB.GETLENGTH(v_file) ) ; DBMS_LOB.FILECLOSE(v_file) ; v_record_number := country_pic_cursor%rowcount ; ELSE DBMS_OUTPUT.PUT_LINE( Can not open the file v_filename) ; END IF ; END LOOP ; DBMS_OUTPUT.PUT_LINE( TOTAL FILES UPDATED : v_record_number) ; ( 다음페이지에계속 ) Written by AngelaLEE 6-37 www.muhanedu.net
EXCEPTION WHEN OTHERS THEN DBMS_LOB.FILECLOSE(v_file) ; DBMS_OUTPUT.PUT_LINE( Program Error Occurred : to_char(sqlcode) SQLERRM ) ; END ; / Written by AngelaLEE 6-38 www.muhanedu.net