Tibero RDBMS

Transcription

1 Tibero RDBMS tbpsm 참조안내서 Tibero RDBMS 4 SP1 Copyright 2013 TIBERO Co., Ltd. All Rights Reserved.

2 Copyright Notice Copyright 2013 TIBERO Co., Ltd. All Rights Reserved. 대한민국경기도성남시분당구서현동 우 ) Restricted Rights Legend All TIBERO Software Tibero RDBMS ) and documents are protected by copyright laws and the Protection Act of Computer Programs, and international convention. TIBERO software and documents are made available under the terms of the TIBERO License Agreement and may only be used or copied in accordance with the terms of this agreement. No part of this document may be transmitted, copied, deployed, or reproduced in any form or by any means, electronic, mechanical, or optical, without the prior written consent of TIBERO Co., Ltd. 이소프트웨어 Tibero RDBMS ) 사용서의내용과프로그램은저작권법, 컴퓨터프로그램보호법및국제조약에의해서보호받고있습니다. 사용서의내용과여기에된프로그램은 TIBERO Co., Ltd. 와의사용권계약하에서만사용이가능하며, 사용권계약을준수하는경우에만사용또는복제할수있습니다. 이사용서의전부또는일부분을 TIBERO의사전서면동의없이전자, 기계, 녹음등의수단을사용하여전송, 복제, 배포, 2차적저작물작성등의행위를하여서는안됩니다. Trademarks Tibero RDBMS is a registered trademark of TIBERO Co., Ltd. Other products, titles or services may be registered trademarks of their respective companies. Tibero RDBMS 는 TIBERO Co., Ltd. 의등록상표입니다. 기타모든제품들과회사이름은각각해당소유주의상표로서참조용으로만사용됩니다. Open Source Software Notice This product includes open source software developed and/or licensed by "OpenSSL," "RSA Data Security, Inc.," "Apache Foundation," "Jean-loup Gailly and Mark Adler," and "Paul Hsieh's hash". Information about the afore mentioned and the related open source software can be found in the "${INSTALL_PATH}/license/oss_licenses" directory. 본제품은 OpenSSL, RSA Data Security, Inc., Apache Foundation 및 Jean-loup Gailly와 Mark Adler 및 Paul Hsieh's hash 에의해개발또는라이선스된오픈소스소프트웨어를포함합니다. 관련상세정보는제품의디렉터리 ${INSTALL_PATH}/license/oss_licenses 에기재된사항을참고해주십시오. 안내서정보안내서제목 : Tibero RDBMS tbpsm 참조안내서발행일 : 소프트웨어버전 : Tibero RDBMS 4 SP1 안내서버전 : 2.1.4

3 내용목차 안내서에대하여... xiii 제1장 패키지소개 개요 구성 사용자정의패키지 시스템패키지... 4 제2장 DBMS_LOB 개요 프로시저 APPEND COPY CREATETEMPORARY ERASE FREETEMPORARY READ TRIM WRITE WRITEAPPEND 함수 COMPARE GETLENGTH INSTR ISTEMPORARY SUBSTR 제3장 DBMS_OBFUSCATION_TOOLKIT 개요 프로시저와함수 DES3DECRYPT DES3ENCRYPT DES3GETKEY DESDECRYPT DESENCRYPT DESGETKEY 제4장 DBMS_OUTPUT 개요 프로시저 DISABLE ENABLE GET_LINE, GET_LINES Tibero RDBMS iii

4 NEW_LINE PUT, PUT_LINE 제5장 DBMS_STATS 개요 프로시저 CREATE_STAT_TABLE DELETE_COLUMN_STATS DELETE_DATABASE_STATS DELETE_DICTIONARY_STATS DELETE_INDEX_STATS DELETE_SCHEMA_STATS DELETE_SYSTEM_STATS DELETE_TABLE_STATS DROP_STAT_TABLE EXPORT_COLUMN_STATS EXPORT_DATABASE_STATS EXPORT_INDEX_STATS EXPORT_SCHEMA_STATS EXPORT_SYSTEM_STATS EXPORT_TABLE_STATS GATHER_DATABASE_STATS GATHER_DICTIONARY_STATS GATHER_INDEX_STATS GATHER_SCHEMA_STATS GATHER_SYSTEM_STATS GATHER_TABLE_STATS IMPORT_COLUMN_STATS IMPORT_DATABASE_STATS IMPORT_INDEX_STATS IMPORT_SCHEMA_STATS IMPORT_SYSTEM_STATS IMPORT_TABLE_STATS SET_PARAM SET_SYSTEM_STATS 함수 GET_PARAM LOCAL_TRANSACTION_ID TO_BOOLEAN 제6장 DBMS_TRANSACTION 개요 프로시저 COMMIT iv Tibero RDBMS tbpsm 참조안내서

5 ROLLBACK, ROLLBACK_SAVEPOINT SAVEPOINT 제7장 UTL_RAW 개요 함수 BIT_AND BIT_COMPLEMENT BIT_OR BIT_XOR CAST_FROM_BINARY_DOUBLE CAST_FROM_BINARY_FLOAT CAST_FROM_BINARY_INTEGER CAST_FROM_NUMBER CAST_TO_BINARY_DOUBLE CAST_TO_BINARY_FLOAT CAST_TO_BINARY_INTEGER CAST_TO_NUMBER CAST_TO_RAW CAST_TO_VARCHAR COMPARE CONCAT COPIES LENGTH OVERLAY REVERSE SUBSTR TRANSLATE TRANSLITERATE XRANGE 제8장 DBMS_JOB 개요 프로시저 BROKEN CHANGE INTERVAL NEXT_DATE REMOVE RUN SUBMIT WHAT 제9장 DBMS_SQL 개요 Tibero RDBMS v

6 9.2. 타입 DESC_REC DESC_REC DESC_TAB DESC_TAB 프로시저 BIND_VARIABLE CLOSE_CURSOR COLUMN_VALUE DEFINE_COLUMN DESCRIBE_COLUMNS DESCRIBE_COLUMNS PARSE 함수 EXECUTE EXECUTE_AND_FETCH FETCH_ROWS OPEN_CURSOR IS_OPEN LAST_ERROR_POSITION LAST_ROW_COUNT 제10장 DBMS_ROWID 개요 프로시저 ROWID_INFO 함수 ROWID_CREATE ROWID_SEGMENT ROWID_BLOCK_NUMBER ROWID_ROW_NUMBER ROWID_ABSOLUTE_FNO ROWID_TO_RELATIVE_FNO 제11장 DBMS_JAVA 개요 프로시저 SET_OUTPUT 함수 LONGNAME SHORTNAME 제12장 UTL_FILE 개요 프로시저 vi Tibero RDBMS tbpsm 참조안내서

7 FCLOSE FCLOSE_ALL FCOPY FFLUSH FGETATTR FREMOVE FRENAME FSEEK GET_LINE GET_RAW NEW_LINE PUT PUTF PUT_RAW PUT_LINE 함수 FGETPOS FOPEN IS_OPEN 제13장 DBMS_REDEFINITION 개요 프로시저 ABORT_REDEF_TABLE FINISH_REDEF_TABLE START_REDEF_TABLE SYNC_INTERIM_TABLE 제14장 UTL_ENCODE 개요 함수 BASE64_DECODE BASE64_ENCODE TEXT_ENCODE 제15장 UTL_URL 개요 함수 ESCAPE UNESCAPE 제16장 UTL_HTTP 개요 함수 REQUEST Tibero RDBMS vii

8 제17장 DBMS_APM 개요 프로시저 CREATE_SNAPSHOT REPORT_TEXT 제18장 DBMS_MVIEW 개요 프로시저 EXPLAIN_REWRITE REFRESH 제19장 DBMS_SPACE 개요 프로시저 SPACE_USAGE UNUSED_SPACE 제20장 DBMS_SPACE_ADMIN 개요 프로시저 SEGMENT_DUMP 제21장 DBMS_CRYPTO 개요 암호화 / 복호화알고리즘및체인, 패딩 해시알고리즘 프로시저와함수 DECRYPT ENCRYPT HASH 제22장 DBMS_TYPES 개요 상수목록 제23장 DBMS_RANDOM 개요 프로시저와함수 NORMAL RANDOM SEED STRING VALUE 제24장 DBMS_SYSTEM 개요 viii Tibero RDBMS tbpsm 참조안내서

9 24.2. 프로시저와함수 SET_SQL_TRACE_IN_SESSION 제25장 DBMS_UTILITY 개요 프로시저와함수 COMMA_TO_TABLE GET_TIME 제26장 DBMS_ERRLOG 개요 프로시저 CREATE_ERROR_LOG 제27장 DBMS_REPAIR 개요 함수 ONLINE_INDEX_CLEAN 제28장 DBMS_APPLICATION_INFO 개요 프로시저 READ_CLIENT_INFO READ_MODULE SET_ACTION SET_CLIENT_INFO SET_MODULE SET_SESSION_LONGOPS 제29장 DBMS_LOCK 개요 프로시저 SLEEP 제30장 UTL_I18N 개요 프로시저와함수 RAW_TO_CHAR STRING_TO_RAW 제31장 DBMS_SESSION 개요 프로시저 CLEAR_IDENTIFIER UNIQUE_SESSION_ID SET_IDENTIFIER 색인 Tibero RDBMS ix

10

11 예목차 [ 예 1.1] EMP_MGMT 패키지의선언부... 2 [ 예 1.2] EMP_MGMT 패키지의구현부... 3 Tibero RDBMS xi

12

13 안내서에대하여 안내서의대상 본안내서는 Tibero RDBMS 이하 Tibero RDBMS) 에서제공하는저장프로시저모듈즉 tbpsmtibero 의 Persistent Stored Module) 패키지를참조하려는데이터베이스관리자 Database Administrator, 이하 DBA), 애플리케이션프로그램개발자를대상으로기술한다. 안내서의전제조건 본안내서를원활히이해하기위해서는다음과같은사항을미리알고있어야한다. 데이터베이스의이해 RDBMS 의이해 SQL 의이해 tbpsm 의이해 안내서의제한조건 본안내서는 Tibero RDBMS 를실무에적용하거나운용하는데필요한모든사항을포함하고있지않다. 따라서설치, 환경설정등운용및관리에대해서는각제품안내서를참고하기바란다. 참고 Tibero RDBMS 의설치및환경설정에관한내용은 "Tibero RDBMS 설치안내서 " 를참고한다. 안내서에대하여 xiii

14 안내서구성 Tibero RDBMS tbpsm 참조안내서의각장의주요내용은다음과같다. 제1장 : 패키지소개패키지의기본개념과구성을간략히소개한다. 제 2 장 : DBMS_LOB DBMS_LOB 패키지의개요와사용방법을기술한다. 제 3 장 : DBMS_OBFUSCATION_TOOLKIT DBMS_OBFUSCATION_TOOLKIT 패키지의개요와사용방법을기술한다. 제 4 장 : DBMS_OUTPUT DBMS_OUTPUT 패키지의개요와사용방법을기술한다. 제 5 장 : DBMS_STATS DBMS_STATS 패키지의개요와사용방법을기술한다. 제 6 장 : DBMS_TRANSACTION DBMS_TRANSACTION 패키지의개요와사용방법을기술한다. 제 7 장 : UTL_RAW UTL_RAW 패키지의개요와사용방법을기술한다. 제 8 장 : DBMS_JOB DBMS_JOB 패키지의개요와사용방법을기술한다. 제 9 장 : DBMS_SQL DBMS_SQL 패키지의개요와사용방법을기술한다. 제 10 장 : DBMS_ROWID DBMS_ROWID 패키지의개요와사용방법을기술한다. 제 11 장 : DBMS_JAVA DBMS_JAVA 패키지의개요와사용방법을기술한다. xiv Tibero RDBMS tbpsm 참조안내서

15 제 12 장 : UTL_FILE UTL_FILE 패키지의개요와사용방법을기술한다. 제 13 장 : DBMS_REDEFINITION DBMS_REDEFINITION 패키지의개요와사용방법을기술한다. 제 14 장 : UTL_ENCODE UTL_ENCODE 패키지의개요와사용방법을기술한다. 제 15 장 : UTL_URL UTL_URL 패키지의개요와사용방법을기술한다. 제 16 장 : UTL_HTTP UTL_HTTP 패키지의개요와사용방법을기술한다. 제 17 장 : DBMS_APM DBMS_APM 패키지의개요와사용방법을기술한다. 제 18 장 : DBMS_MVIEW DBMS_MVIEW 패키지의개요와사용방법을기술한다. 제 19 장 : DBMS_SPACE DBMS_SPACE 패키지의개요와사용방법을기술한다. 제 20 장 : DBMS_SPACE_ADMIN DBMS_SPACE_ADMIN 패키지의개요와사용방법을기술한다. 제 21 장 : DBMS_CRYPTO DBMS_CRYPTO 패키지의개요와사용방법을기술한다. 제 22 장 : DBMS_TYPES DBMS_TYPES 패키지의개요와사용방법을기술한다. 제 23 장 : DBMS_RANDOM DBMS_RANDOM 패키지의개요와사용방법을기술한다. 안내서에대하여 xv

16 제 24 장 : DBMS_SYSTEM DBMS_SYSTEM 패키지의개요와사용방법을기술한다. 제 25 장 : DBMS_UTILITY DBMS_UTILITY 패키지의개요와사용방법을기술한다. 제 26 장 : DBMS_ERRLOG DBMS_ERRLOG 패키지의개요와사용방법을기술한다. 제 27 장 : DBMS_REPAIR DBMS_REPAIR 패키지의개요와사용방법을기술한다. 제 28 장 : DBMS_APPLICATION_INFO DBMS_APPLICATION_INFO 패키지의개요와사용방법을기술한다. 제 29 장 : DBMS_LOCK DBMS_LOCK 패키지의개요와사용방법을기술한다. 제 30 장 : DBMS_SESSION DBMS_SESSION 패키지의개요와사용방법을기술한다. 제 31 장 : UTL_I18N UTL_I18N 패키지의개요와사용방법을기술한다. xvi Tibero RDBMS tbpsm 참조안내서

17 안내서규약 표기 <AaBbCc123> <Ctrl>+C [Button] 진하게 " " 따옴표 ) ' 입력항목 ' 하이퍼링크 > 참고 의미프로그램소스코드의파일명, 디렉터리 Ctrl과 C를동시에누름 GUI의버튼또는메뉴이름강조다른관련안내서또는안내서내의다른장및절언급화면 UI에서입력항목에대한메일계정, 웹사이트메뉴의진행순서하위디렉터리또는파일있음하위디렉터리또는파일없음참고또는주의사항 [ 그림 1.1] [ 표 1.1] AaBbCc123 그림이름 표이름 명령어, 명령어수행후화면에출력된결과물, 예제코드 { } [ ] 필수인수값 옵션인수값 선택인수값 안내서에대하여 xvii

18 시스템사용환경 요구사항 Platform HP-UX 11i PA-RISC, ia64) Solaris SPARC 9/Solaris 9) AIX PPC 5L/AIX 5.3) GNU X86, 64, IA64) Linux kernel 2.6 이상 Hardware 최소 1.5GB 하드디스크공간 512MB 이상메모리공간 Compiler PSM C99 지원필요 ) tbesql/c C99 지원필요 ) xviii Tibero RDBMS tbpsm 참조안내서

19 관련안내서 안내서 Tibero RDBMS 설치안내서 Tibero RDBMS tbcli 안내서 Tibero RDBMS 애플리케이션개발자안내서 Tibero RDBMS External Procedure 안내서 Tibero RDBMS JDBC 개발자안내서 Tibero RDBMS tbesql/c 안내서 Tibero RDBMS tbesql/cobol 안내서 Tibero RDBMS tbpsm 안내서 Tibero RDBMS 관리자안내서 Tibero RDBMS tbadmin 안내서 Tibero RDBMS 유틸리티안내서 Tibero RDBMS 설치시필요한시스템요구사항과설치및제거방법을기술한안내서이다. Call Level Interface인 tbcli의개념과구성요소, 프로그램구조를소개하고 tbcli 프로그램을작성하는데필요한데이터타입, 함수, 에러메시지를기술한안내서이다. 각종애플리케이션라이브러리를이용하여애플리케이션프로그램을개발하는방법을기술한안내서이다. External Procedure를소개하고이를생성하고사용하는방법을기술한안내서이다. Tibero RDBMS에서제공하는 JDBC 기능을이용하여애플리케이션프로그램을개발하는방법을기술한안내서이다. C 프로그래밍언어를사용해데이터베이스작업을수행하는각종애플리케이션프로그램을작성하는방법을기술한안내서이다. COBOL 프로그래밍언어를사용해데이터베이스작업을수행하는각종애플리케이션프로그램을작성하는방법을기술한안내서이다. 저장프로시저모듈인 tbpsm의개념과문법, 구성요소를소개하고, tbpsm 프로그램을작성하는데필요한제어구조, 복합타입, 서브프로그램, 패키지와 SQL 문장을실행하고에러를처리하는방법을기술한안내서이다. Tibero RDBMS의동작과주요기능의원활한수행을보장하기위해 DBA 가알아야할관리방법을논리적또는물리적측면에서하고, 관리를지원하는각종도구를기술한안내서이다. SQL/PSM 처리와 DBA를위한시스템관리기능을제공하는 GUI 기반의툴인 tbadmin을소개하고, 설치및사용방법을기술한안내서이다. 데이터베이스와관련된작업을수행하기위해필요한유틸리티의설치및환경설정, 사용방법을기술한안내서이다. Tibero RDBMS를사용하는도중에발생할수있는각종에러의원인과해결방법을기술한안내서이다. 안내서에대하여 xix

20 안내서 에러참조안내서 Tibero RDBMS 참조안내서 Tibero RDBMS SQL 참조안내서 Tibero RDBMS의동작과사용에필요한초기화와데이터사전, 정적뷰, 동적뷰를기술한참조안내서이다. 데이터베이스작업을수행하거나애플리케이션프로그램을작성할때필요한 SQL 문장을기술한참조안내서이다. xx Tibero RDBMS tbpsm 참조안내서

21 연락처 Korea TIBERO Co., Ltd Tmax Building 3th floor, Seohyeon-dong, Bundang-gu, Seongnam-si, Gyeonggi-do, South Korea Tel: Fax: Web Korean): 기술지원 : USA TmaxSoft, Inc. 560 Sylvan Avenue Englewood Cliffs, NJ U.S.A Tel: Fax: info@tmaxsoft.com Web English): Japan TmaxSoft Japan Co., Ltd. 5F Sanko Bldg, Mita, Minato-Ku, Tokyo, Japan Tel: Fax: info@tmaxsoft.co.jp Web Japanese): 안내서에대하여 xxi

22 China TmaxSoft China Co., Ltd. Beijing Silver Tower, RM 1508, 2# North Rd Dong San Huan, Chaoyang District, Beijing, China, China Tel: ~8 Fax: Web Chinese): xxii Tibero RDBMS tbpsm 참조안내서

23 제 1 장패키지소개 본장에서는패키지의기본개념과구성을소개한다 개요 패키지 package) 는하나이상의 tbpsm 프로시저 procedure) 와함수 function) 등을포함하는스키마객체이다. 하나의패키지에포함되는스키마객체는프로시저와함수를비롯하여변수, 커서 cursor), 예외상황 exception) 등이있다. 패키지는프로시저, 함수와마찬가지로임의의 SQL 문장에서사용할수있다. 패키지를사용함으로써얻을수있는장점은다음과같다. 연관된프로시저와함수의개발과관리가쉽다. 예를들어, 여러프로시저와함수에서공통으로액세스하는변수를패키지에포함시킬수있다. 애플리케이션프로그램의개발이쉽다. 패키지에서지원하는여러기능을이용하면좀더강력한애플리케이션프로그램을작성할수있다. 효율적인실행이가능하다. 권한의부여와회수, 메모리로딩 memory loading) 등이패키지단위로수행되기때문에효율적인실행 이가능하다 구성 패키지는사용자정의패키지와 Tibero RDBMS 에서제공하는시스템패키지로구성된다 사용자정의패키지 사용자정의패키지는사용자가원하는목적에맞춰정의하는패키지이다. 사용자정의패키지는프로시 저, 함수와유사하게다음과같이정의할수있다. 제 1 장패키지소개 1

24 패키지선언부 외부인터페이스이며공개프로시저 public procedure) 와함수, 변수, 상수등을포함한다. 패키지선언부의세부내용은다음과같다. 문법 CREATE [OR REPLACE] PACKAGE package_name [invoker_rights] AS -- 공개프로시저의선언 -- 공개함수의선언 -- 공개변수, 상수, 예외상황의선언 END [package_name]; 특징 CREATE PACKAGE 문장을이용하여선언부를생성한다. 패키지이름 package_name) 다음에패키지를호출자권한 invoker's rights) 또는정의자권한 definer's rights) 으로실행할것인지를설정할수있다. 설정이생략되면정의자권한으로실행된다. END 다음에패키지이름은생략해도상관없다. 사용예제 [ 예 1.1] EMP_MGMT 패키지의선언부 CREATE PACKAGE EMP_MGMT AS... EMP_MGMT 패키지의선언부... FUNCTION HIREENAME VARCHAR, ADDR VARCHAR, SALARY NUMBER, DEPTNO INT) RETURN INT; PROCEDURE FIREEMPNO INT PROCEDURE RAISE_SALARYEMPNO INT, AMOUNT NUMBER EMP_NOT_FOUND EXCEPTION; END EMP_MGMT; 위의예에서는 EMP_MGMT 패키지를정의자권한으로실행하며, 공개프로시저 2 개 ) 와함수 1 개 ) 그 리고공개예외상황 1 개 ) 를포함하고있다. 패키지구현부 실제프로그램이구현되는부분이다. 공개프로시저와함수등의프로그램구현부와함께비공개프로시 저 private procedure) 와함수, 변수등을포함한다. 2 Tibero RDBMS tbpsm 참조안내서

25 패키지구현부의세부내용은다음과같다. 문법 CREATE [OR REPLACE] PACKAGE BODY package_name AS -- 공개프로시저의구현부 -- 공개함수의구현부 -- 비공개프로시저및함수의선언부 -- 비공개프로시저및함수의구현부 -- 비공개변수, 상수, 예외상황의정의 [BEGIN -- 초기화명령어의집합부 END [package_name]; 특징 CREATE PACKAGE BODY 문장을이용하여구현부를생성한다. END 다음에패키지이름은생략해도상관없다. 사용예제 [ 예 1.2] EMP_MGMT 패키지의구현부 CREATE PACKAGE BODY EMP_MGMT AS... EMP_MGMT 패키지의구현부... NUM_EMP INT; EMPNO NUMBER := 0; FUNCTION HIREENAME VARCHAR, ADDR VARCHAR, SALARY NUMBER, DEPTNO INT) RETURN INT AS BEGIN EMPNO := EMPNO + 1;... 새로운 EMPNO 생성... INSERT INTO EMP VALUES EMPNO, ENAME, ADDR, SALARY, DEPTNO NUM_EMP := NUM_EMP + 1; RETURNEMPNO END; PROCEDURE FIREEMPNO INT) AS BEGIN DELETE FROM EMP WHERE EMP.EMPNO = FIRE.EMPNO; NUM_EMP := NUM_EMP? 1; END; PROCEDURE RAISE_SALARYEMPNO INT, AMOUNT NUMBER) AS BEGIN 제 1 장패키지소개 3

26 END; UPDATE EMP SET SALARY = SALARY + AMOUNT WHERE EMP.EMPNO = RAISE_SALARY.EMPNO; END EMP_MGMT; 위의예에서는 [ 예 1.1] 에포함된모든프로시저와함수에대한정의를포함하고있으며, 비공개변수인 NUM_EMP 를선언하고있다. 패키지프로시저와함수를정의하는방법은패키지변수, 상수, 예외상황에대한내용외에는일반프로시저및함수를정의하는방법과동일하다. 프로시저또는함수내부의컬럼의이름과변수의이름이같은경우, 컬럼이포함된테이블의이름이나프로시저또는함수의이름을지정하여혼동을피할수있다. 예를들어, 프로시저 FIRE 함수의내부에서테이블 EMP에포함된컬럼 EMPNO와프로시저 FIRE로전달된변수 EMPNO가같은이름을가지므로, 각각테이블이름과프로시저이름을지정해주었다. 패키지내에포함된프로시저나함수를그패키지의소유자이외의다른사용자가호출하도록할수있다. 프로시저나함수를호출하려면해당패키지의 EXECUTE 권한을부여받아야한다. 해당패키지의소유자는항상해당패키지를호출할수있다. 뷰 view) 가패키지프로시저나함수를이용하여정의된경우에는뷰를접근하기위해 SELECT 권한을부여받아야한다. 이때, 해당패키지의 EXECUTE 권한은필요하지않다. 패키지프로시저나함수를참조하려면패키지의이름과프로시저또는함수의이름을함께써주어야한다. 다음은 [ 예 1.1] 에서정의한 EMP_MGMT 패키지내의 HIRE 함수를호출하는예이다. 이때반환값은 EMPNO 변수에저장된다. DECLARE empno NUMBER; BEGIN empno := EMP_MGMT.HIRE'John', 'New York', 45000, 5 DBMS_OUTPUT.PUT_LINE'Hired employee No. is ' empno END; 시스템패키지 시스템패키지는 Tibero RDBMS에서기본적으로제공하는패키지라이브러리로, SYS 사용자의소유로정의된다. 또한, Tibero RDBMS를설치한후에일반사용자도사용할수있도록시스템패키지와동일한이름의공용동의어 Public Synonym) 가정의된다. 그리고일반사용자가시스템패키지의프로시저또는함수를호출하면호출자권한으로실행된다. 시스템패키지는일반사용자에게여러가지의확장된기능을제공한다. 시스템패키지를 SQL 문장에서그대로사용한다거나, 다른프로시저와함수그리고패키지를정의할때도사용할수있다. 4 Tibero RDBMS tbpsm 참조안내서

27 시스템패키지의프로시저와함수를호출하는방법은사용자정의패키지와같다. 다음은 32KB 이상인대용량객체의이름과생성자를출력하는예이다. SELECT name, creator FROM image WHERE DBMS_LOB.GETLENGTHdata) >= 32768; 위예에서보듯이, data 는 image 테이블에서대용량객체형으로정의된컬럼이다. 다음은 Tibero RDBMS 에서제공하는시스템패키지이다. 시스템패키지 DBMS_LOB DBMS_OBFUSCA TION_TOOLKIT DBMS_OUTPUT DBMS_STATS DBMS_TRANSACTION UTL_RAW DBMS_JOB DBMS_SQL DBMS_ROWID DBMS_JAVA UTL_FILE DBMS_REDEFINITION UTL_ENCODE UTL_URL UTL_HTTP DBMS_MVIEW DBMS_SPACE BLOB, CLOB 타입의대용량데이터를처리하기위한패키지이다. DES, DES3 알고리즘을이용한데이터암호화및복호화패키지이다. 메시지버퍼에메시지를저장하고읽기위한패키지이다. 데이터베이스객체에대한통계정보를관리하기위한패키지이다. 트랜잭션 transaction) 문장을실행하고트랜잭션을관리하기위한패키지이다. RAW 타입의데이터를처리하기위한패키지이다. JOB을관리하기위한패키지이다. 데이터베이스에접근하는 Dynamic SQL을사용하기위한패키지이다. ROWID에담긴정보를보거나생성하기위한패키지이다. 데이터베이스에서사용하는 Java 객체에접근하기위한패키지이다. 운영체제에서관리하는파일에접근하기위한패키지이다. 온라인상태 on-line state) 에서특정테이블을재정의하기위한패키지이다. 호스트간의데이터를전송할수있도록표준인코딩기술로인코딩하기위한패키지이다. URLUniform Resource Locator) 주소를 ESCAPE 형태로변환하기위한패키지이다. 웹표준 RFC2616) 에따라웹페이지 Web page) 의요청을처리하기위한패키지이다. 실체화뷰 Materialized View) 와관련된정보를제공하고이정보를최근의것으로변경할수있는 REFRESH 기능을사용하기위한패키지이다. 세그먼트 segment) 의크기와공간사용에대한정보를제공하는패키지이다. 제 1 장패키지소개 5

28 시스템패키지 DBMS_SPACE_ADMIN DBMS_CRYPTO DBMS_TYPES DBMS_RANDOM DBMS_SYSTEM DBMS_UTILITY DBMS_ERRLOG DBMS_REPAIR DBMS_APPLICATION_INFO DBMS_LOCK DBMS_SESSION UTL_I18N 세그먼트를관리하는기능을제공하는패키지이다. 데이터를암호화및복호화하는기능을제공하는패키지이다. Tibero에서사용되는데이터타입들을숫자로정의한패키지이다. 임의의숫자및문자열을생성하는패키지이다. SYS 권한을가진관리자가시스템적인제어를위해사용하는패키지이다. 여러가지유용한기능들을제공하는패키지이다. DML의 error logging 기능을지원하는패키지이다. 테이블과인덱스내의깨진블록을검사하고복구하는기능을제공하는패키지이다. 어플리케이션정보에관련된뷰의값을변경하기위한패키지이다. 세션을대기시키는패키지이다. 세션식별자를관리하는패키지이다. 언어, 국가별설정들간의호환기능을제공하는패키지이다. 참고 위의패키지에대한자세한내용은해당패키지를하는각장을참고한다. 6 Tibero RDBMS tbpsm 참조안내서

29 제 2 장 DBMS_LOB 본장에서는 DBMS_LOB 패키지의기본개념과패키지내의프로시저와함수를사용하는방법을한 다 개요 DBMS_LOB은 BLOB 또는 CLOB 타입의컬럼데이터에여러가지연산을제공하는패키지이다. 또한, DBMS_LOB 패키지내의프로시저와함수를이용하여대용량객체형 Large OBject, 이하 LOB) 의전체또는일부에읽기, 쓰기등의작업을수행할수있다. 다음은 DBMS_LOB 패키지를사용할때유의해야할사항이다. 호출자권한 이패키지는호출자권한으로실행된다. 만약다른프로시저나함수에서호출되는경우, 해당프로시저나함수의실행자권한으로실행된다. 프로시저와함수의의길이 length) 와오프셋 offset) 단위 대상 destination) 데이터가 BLOB 타입이면바이트 byte), CLOB 타입이면문자 character) 단위이다. 함수의반환값도이와같다. 프로시저와함수의값을전달할때값의유효범위 LOB 데이터내의오프셋, 길이, 크기등을나타내는값은항상 1 이상이어야하며 LOBMAXSIZE 상 수의값보다크면안된다. LOBMAXSIZE 상수는 ^64 1) 의값을가진다. LOB 데이터내의오프셋, 길이, 크기등이 1 보다작거나 LOBMAXSIZE 보다큰값이면 IN VALID_ARGVAL 예외상황이발생한다. CLOB 타입의컬럼데이터 이타입은항상유니코드 UTF-16 문자집합의문자열이저장되며, 2byte 로하나의문자를나타낸다. 길이또는크기값은 LOBMAXSIZE 상수를 2 로나눈값보다작아야한다. 만약이값보다크면마찬 가지로 INVALID_ARGVAL 예외상황이발생한다. 제 2 장 DBMS_LOB 7

30 APPEND, COPY, TRIM, WRITE LOB 데이터를갱신하려는프로시저와함수의로크기와오프셋값의합이최댓값 LOBMAX SIZE 상수값 ) 을초과하면안된다. 만약초과하면예외상황이발생한다. 그리고, LOB 데이터를갱신하려면먼저그데이터를포함하는로우에잠금 LOCK) 을설정해야한다. 왜냐하면프로시저나함수는자동으로잠금을설정해주지않기때문이다. READ, COMPARE, INSTR, SUBSTR 읽기전용의프로시저와함수는 LOB 데이터의마지막까지만읽기를수행한다. COMPARE, INSTR, SUBSTR 문자열패턴의작업을수행하는함수의로 LIKE 함수에서사용되는퍼센트 %) 와언더바 _) 와같은와일드카드 wild card) 문자를사용할수없다. LOB 데이터를수행할때에는먼저대상 LOB 데이터를 OPEN 프로시저를이용하여열고, 작업이끝나면 CLOSE 프로시저를이용하여닫는다. 이때 OPEN 프로시저로열린 LOB 데이터를닫으면데이터베이스에갱신된내용이반영된다. 반면에, OPEN 프로시저를실행하여열지않은 LOB 데이터에대해갱신연산을수행하면바로데이터베이스에반영된다. 대개의경우 LOB 데이터를갱신하면많은디스크작업이수반되므로, 여러번에걸쳐데이터베이스에반영하는것보다한번에모든갱신을데이터베이스에반영하는것이효율적이다. OPEN 프로시저를실행하여 LOB 데이터를연경우에는 COMMIT 문장을실행하기전에반드시 CLOSE 프로시저를실행하여닫아야한다. 만약열려있는 LOB 데이터가있는데 COMMIT을실행하면, 에러가발생한다. 열려있는 LOB 데이터가있을때 ROLLBACK을실행하면모든갱신은취소되고열려있는 LOB 데이터에대한정보도없어진다. 다음은 DBMS_LOB 패키지내에정의된상수이다. LOBMAXSIZE LOB 데이터의최대크기이다. LOBMAXSIZE CONSTANT BINARY_INTEGER := 데이터타입은 BINARY_INTEGER 이고 LOB 데이터의최대크기는 이다. LOB_READONLY LOB 데이터에대한읽기전용의사용여부를설정하는모드이다. LOB_READONLY CONSTANT BINARY_INTEGER := 0 데이터타입은 BINARY_INTEGER 이고값이 0 이면읽기전용으로설정된다. 8 Tibero RDBMS tbpsm 참조안내서

31 LOB_READWRITE LOB 데이터의읽기및쓰기를설정하는모드이다. LOB_READWRITE CONSTANT BINARY_INTEGER := 1 데이터타입은 BINARY_INTEGER 이고값이 1 이면읽기및쓰기로설정된다 프로시저 본절에서는 DBMS_LOB 패키지에서제공하는프로시저를알파벳순으로한다 APPEND 원본 LOB 데이터의전체를대상 LOB 데이터의끝에붙여넣는프로시저이다. APPEND 프로시저의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.APPEND dest_lob IN OUT NOCOPY BLOB, src_lob IN BLOB CLOB 타입인경우 DBMS_LOB.APPEND dest_lob IN OUT NOCOPY CLOB, src_lob IN CLOB CLOB 데이터를전달하는경우 dest_lob, src_lob 의 LOB 데이터의문자집합 character set) 이같아야한다. dest_lob src_lob 대상 LOB locator 이다. 원본 LOB locator 이다. 제 2 장 DBMS_LOB 9

32 예외상황 예외상황 VALUE_ERROR 대상또는원본 LOB 데이터가 NULL 인경우이다. DECLARE dest_lob CLOB := 'All''s fair in '; src_lob CLOB := 'love and war'; BEGIN DBMS_LOB.APPENDdest_lob, src_lob DBMS_OUTPUT.PUT_LINE'Result = ' dest_lob END; / Result = All's fair in love and war PSM completed SQL> COPY 원본 LOB 데이터의전체또는일부를대상 LOB 데이터에복사하는프로시저이다. 이때복사할원본 LOB 데이터의오프셋과대상 LOB 데이터의오프셋을지정할수있다. 만약대상 LOB 데이터의오프셋이대상 LOB 데이터의길이보다짧으면오프셋위치에존재하는이전데이터는갱신된다. 이와는반대로대상 LOB 데이터오프셋이대상 LOB 데이터의길이보다길면중간에 0BLOB 데이터 ) 또는공백 CLOB 데이터 ) 으로채워진다. COPY 프로시저의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.COPY dest_lob IN OUT NOCOPY BLOB, src_lob IN BLOB, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1 CLOB 타입인경우 10 Tibero RDBMS tbpsm 참조안내서

33 DBMS_LOB.COPY dest_lob IN OUT NOCOPY CLOB, src_lob IN CLOB, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1 CLOB 데이터를전달하는경우 dest_lob, src_lob 의 LOB 데이터의문자집합이같아야한 다. dest_lob src_lob amount dest_offset 대상 LOB locator이다. 원본 LOB locator이다. 복사할 byteblob 데이터 ) 또는문자 CLOB 데이터 ) 의개수이다. 대상 LOB 데이터내의오프셋이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 )) src_offset 원본 LOB 데이터내의오프셋이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 )) 예외상황 예외상황 VALUE_ERROR INVALID_POS INVALID_LEN 입력중하나라도 NULL인경우이다. src_offset, dest_offset의값이 1보다작거나 LOBMAXSIZE보다큰경우이다. amount의값이 1보다작거나 LOBMAXSIZE보다큰경우이다. DECLARE dest_lob CLOB := 'It you would be loved, '; src_lob CLOB := 'be worthy to be loved'; BEGIN DBMS_LOB.COPYdest_lob, src_lob, lengthsrc_lob), lengthdest_lob) + 1, 1 DBMS_OUTPUT.PUT_LINE'Result = ' dest_lob END; 제 2 장 DBMS_LOB 11

34 / Result = It you would be loved, be worthy to be loved PSM completed SQL> CREATETEMPORARY 임시 CLOB 또는임시 BLOB 을생성하는프로시저이다. CREATETEMPORARY 프로시저의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.CREATETEMPORARY lob IN OUT NOCOPY BLOB, cache IN BOOLEAN, dur IN PLS_INTEGER := 10 CLOB 타입인경우 DBMS_LOB.CREATETEMPORARY lob IN OUT NOCOPY CLOB, cache IN BOOLEAN, dur IN PLS_INTEGER := 10 lob cache dur 대상 LOB locator이다. LOB 데이터를읽을때버퍼캐시 buffer cache) 에저장할지여부이다. 현재이는 CREATETEMPORARY 프로시저의수명을조정하는기능을지원하지않는다. 단, 기본은세션이완료되면 CREATETEMPORARY 프로시저가자동으로삭제된다. 예외상황 12 Tibero RDBMS tbpsm 참조안내서

35 예외상황 VALUE_ERROR 캐시가 NULL 인경우이다. DECLARE lob_1 CLOB; lob_2 CLOB := 'tibero'; BEGIN DBMS_LOB.CREATETEMPORARYlob_1, false DBMS_LOB.APPENDlob_1, lob_2 DBMS_OUTPUT.PUT_LINElob_1 END; / tibero PSM completed SQL> ERASE LOB 데이터의일부또는전체를삭제하는프로시저이다. 삭제된영역은 0BLOB 데이터 ) 또는공백 CLOB 데이터 ) 으로채워진다. 대상 LOB 데이터의길이가오프셋과삭제할영역크기의합보다짧다면실제로삭제된영역의크기는삭제할영역으로입력한크기보다작을수있다. 실제로삭제된영역의크기는 amount 로출력된다. ERASE 프로시저의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.ERASE lob IN OUT NOCOPY BLOB, amount IN OUT NOCOPY INTEGER, offset IN INTEGER := 1 CLOB 타입인경우 DBMS_LOB.ERASE lob IN OUT NOCOPY CLOB, 제 2 장 DBMS_LOB 13

36 amount IN OUT NOCOPY INTEGER, offset IN INTEGER := 1 lob amount offset 대상 LOB locator이다. 삭제할 byte 또는문자개수이다. 삭제할오프셋이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 ) ) 예외상황 예외상황 VALUE_ERROR INVALID_POS INVALID_LEN 입력가하나라도 NULL인경우이다. offset의값이 1보다작거나 LOBMAXSIZE보다큰경우이다. amount의값이 1보다작거나 LOBMAXSIZE보다큰경우이다. DECLARE lob CLOB := 'Tmaxsoft Tibero'; amount NUMBER := 7; BEGIN DBMS_OUTPUT.PUT_LINE'Length of original LOB = ' lengthlob) DBMS_LOB.ERASElob, amount, 9 DBMS_OUTPUT.PUT_LINE'Value of erased LOB = ' lob DBMS_OUTPUT.PUT_LINE'Length of erased LOB = ' lengthlob) END; / Length of original LOB = 15 Value of erased LOB = Tmaxsoft Length of erased LOB = 15 PSM completed SQL> 14 Tibero RDBMS tbpsm 참조안내서

37 FREETEMPORARY 이미생성된임시 BLOB 또는임시 CLOB 을삭제하는프로시저이다. FREETEMPORARY 프로시저의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.FREETEMPORARY lob IN OUT NOCOPY BLOB CLOB 타입인경우 DBMS_LOB.FREETEMPORARY lob IN OUT NOCOPY CLOB lob 대상 LOB locator 이다. 예외상황 예외상황 VALUE_ERROR 입력가 NULL 인경우이다. DECLARE lob CLOB; BEGIN DBMS_LOB.CREATETEMPORARYlob, false DBMS_LOB.FREETEMPORARYlob END; / PSM completed SQL> 제 2 장 DBMS_LOB 15

38 READ 대상 LOB 데이터의일부또는전체를읽어출력의버퍼에저장하는프로시저이다. 만약읽기를시작하는오프셋과읽을크기의합이대상 LOB 데이터의크기보다커서 LOB 데이터의끝을지나가게되면, 실제읽은데이터의크기는로주어진읽을크기보다작을수있다. 이때실제로읽어온데이터의크기는입출력 amount에저장되어반환된다. 만약읽을오프셋의위치가대상 LOB 데이터의크기보다크다면 NO_DATA_FOUND 예외상황이발생한다. CLOB 데이터로부터읽은데이터를클라이언트에전송하는경우, 클라이언트의문자집합으로자동변환된다. 이경우실제로읽어온데이터와달라질수있다. READ 프로시저의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.READ lob IN BLOB, amount IN OUT NOCOPY BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW CLOB 타입인경우 DBMS_LOB.READ lob IN CLOB, amount IN OUT NOCOPY BINARY_INTEGER, offset IN INTEGER, buffer OUT VARCHAR2 lob amount 읽을대상 LOB locator 이다. 읽을크기를입력하고실제로읽어온크기를출력한다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 ) 개수 ) offset 읽을대상 LOB 데이터내의오프셋이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 )) 16 Tibero RDBMS tbpsm 참조안내서

39 buffer 읽을데이터를저장하는출력버퍼이다. 예외상황 예외상황 VALUE_ERROR INVALID_POS 입력중하나라도 NULL 인경우이다. offset 의값이 1 보다작거나 LOBMAXSIZE 보다큰경우이다. INVALID_LEN amount 의값이 1 보다작거나 MAXBUFSIZE 보다큰경우이다. amount 의값이버퍼의크기보다큰경우이다. NO_DATA_FOUND offset 이대상 LOB 데이터의크기보다큰경우이다. DECLARE lob CLOB := 'TIBERO fighting!!!'; buffer VARCHAR2256 amount BINARY_INTEGER := 8; BEGIN DBMS_LOB.READlob, amount, 8, buffer DBMS_OUTPUT.PUT_LINE'Value to be read = ' buffer END; / Value to be read = fighting PSM completed SQL> TRIM 대상 LOB 데이터의길이를지정된길이로설정하는함수이다. BLOB 데이터의경우 byte 단위로, CLOB 데이터의경우문자단위로길이를지정한다. 지정된길이를넘는데이터는소멸된다. 만약이프로시저를길이가 0인 LOB 데이터로실행하면아무런값이반환되지않는다. TRIM 프로시저의세부내용은다음과같다. BLOB 타입인경우 제 2 장 DBMS_LOB 17

40 DBMS_LOB.TRIM lob IN OUT NOCOPY BLOB, newlen IN INTEGER CLOB 타입인경우 DBMS_LOB.TRIM lob IN OUT NOCOPY CLOB, newlen IN INTEGER lob newlen 대상 LOB locator 이다. 새롭게지정된 LOB 데이터의길이이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 )) 예외상황 예외상황 VALUE_ERROR INVALID_LEN 입력중하나라도 NULL 인경우이다. newlen 값이 0 보다작거나 LOBMAXSIZE 보다큰경우이다. DECLARE lob CLOB := 'A pity beyond all telling is in the heart of love'; BEGIN DBMS_LOB.TRIMlob, 25 DBMS_OUTPUT.PUT_LINE'Value = ' lob DBMS_OUTPUT.PUT_LINE'Length = ' lengthlob) END; / Value = A pity beyond all telling Length = 25 PSM completed SQL> 18 Tibero RDBMS tbpsm 참조안내서

41 WRITE 대상 LOB 데이터의지정된오프셋위치에주어진데이터를지정된크기만큼저장하는프로시저이다. 새로운데이터가저장되는위치에존재하는이전데이터는소멸된다. BLOB 데이터에대한오프셋및크기는 byte 단위이며, CLOB 데이터에대해서는문자단위이다. 주어진데이터의실제크기보다지정된크기가큰경우에는에러가발생하며, 지정된크기가작은경우에는지정된크기만큼만저장된다. 지정된오프셋이대상 LOB 데이터의크기보다큰경우에는중간에 0BLOB 데이터 ) 또는공백 CLOB 데이터 ) 으로채워진다. 이프로시저를 CLOB 데이터에대하여실행할때 CLOB 데이터의문자집합과저장할데이터의문자집합은같아야한다. 이프로시저를클라이언트쪽에서호출하는경우, 저장할데이터가클라이언트쪽의문자집합으로부터 CLOB 데이터문자집합으로변환된후에저장된다. WRITE 프로시저의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.WRITE lob IN OUT NOCOPY BLOB, amount IN BINARY_INTEGER, offset IN INTEGER, buffer IN RAW CLOB 타입인경우 DBMS_LOB.WRITE lob IN OUT NOCOPY CLOB, amount IN BINARY_INTEGER, offset IN INTEGER, buffer IN VARCHAR lob amount 대상 LOB locator 이다. 저장할데이터의크기이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 )) 제 2 장 DBMS_LOB 19

42 offset 데이터를저장할오프셋이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 )) buffer 저장할데이터이다. 예외상황 예외상황 VALUE_ERROR INVALID_POS INVALID_LEN 중하나라도 NULL인경우이다. offset의값이 1보다작거나 LOBMAXSIZE보다큰경우이다. amount의값이 1보다작거나 MAXBUFSIZE보다큰경우이다. DECLARE lob CLOB; buffer VARCHAR2100 BEGIN DBMS_LOB.CREATETEMPORARYlob, false buffer := 'Love is friendship set on fire'; DBMS_LOB.WRITElob, lengthbuffer), 1, buffer DBMS_OUTPUT.PUT_LINElob END; / Love is friendship set on fire PSM completed SQL> WRITEAPPEND 대상 LOB 데이터의끝에주어진데이터를지정된크기만큼저장하는프로시저이다. 이프로시저는 WRITE 프로시저의오프셋을대상 LOB 데이터의길이로설정한것과같다. BLOB 데이터의크기는 byte 단위이며, CLOB 데이터는문자단위이다. 주어진데이터의실제크기보다지정된크기가큰경우에는에러가발생하며, 지정된크기가작은경우에는지정된크기만큼만저장된다. 이프로시저를 CLOB 데이터에대하여실행할때에 CLOB 데이터의문자집합과저장할데이터의문자집합은같아야한다. 이프로시저를클라이언트쪽에서호출하는경우, 저장할데이터가클라이언트쪽의문자집합에서 CLOB 데이터문자집합으로변환된후에저장된다. 20 Tibero RDBMS tbpsm 참조안내서

43 WRITEAPPEND 프로시저의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.WRITEAPPEND lob IN OUT NOCOPY BLOB, amount IN BINARY_INTEGER, buffer IN RAW CLOB 타입인경우 DBMS_LOB.WRITEAPPEND lob IN OUT NOCOPY CLOB, amount IN BINARY_INTEGER, buffer IN VARCHAR2 lob amount 대상 LOB locator 이다. 저장할데이터의크기이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 ) 개수 ) buffer 저장할데이터이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 )) 예외상황 예외상황 VALUE_ERROR INVALID_LEN 중하나라도 NULL 인경우이다. amount 값이 1 보다작거나 MAXBUFSIZE 보다큰경우이다. DECLARE lob CLOB := 'Parting is such '; 제 2 장 DBMS_LOB 21

44 buffer VARCHAR2100) := 'sweet sorrow'; BEGIN DBMS_LOB.WRITEAPPENDlob, lengthbuffer), buffer DBMS_OUTPUT.PUT_LINE'Result = ' lob END; / Result = Parting is such sweet sorrow PSM completed SQL> 2.3. 함수 본절에서는 DBMS_LOB 패키지에서제공하는함수를알파벳순으로한다 COMPARE 두개의 LOB 데이터의전체또는일부를비교하는함수이다. 같은타입의 LOB 데이터간에만비교가가 능하다. COMPARE 함수의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.COMPARE lob_1 IN BLOB, lob_2 IN BLOB, amount IN INTEGER := , offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1 ) RETURN INTEGER; CLOB 타입인경우 DBMS_LOB.COMPARE lob_1 IN CLOB, lob_2 IN CLOB, amount IN INTEGER := , offset_1 IN INTEGER := 1, 22 Tibero RDBMS tbpsm 참조안내서

45 offset_2 IN INTEGER := 1 ) RETURN INTEGER; CLOB 데이터를전달하는경우 lob_1, lob_2 의 LOB 데이터의문자집합이같아야한다. lob_1 lob_2 amount offset_1 비교대상인첫번째 LOB locator이다. 비교대상인두번째 LOB locator이다. 비교할 byte의크기 BLOB 데이터 ) 또는문자 CLOB 데이터 ) 개수이다. 비교를시작할첫번째 LOB 데이터오프셋이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 )) offset_2 비교를시작할두번째 LOB 데이터오프셋이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 )) 반환값 반환값 0 N < 0 or N > 0 lob_1, lob_2 의 LOB 데이터가동일한경우에반환한다. lob_1, lob_2 의 LOB 데이터가동일하지않은경우에반환한다. NULL amount가 1보다작은경우에반환한다. amount가 LOBMAXSIZE보다큰경우에반환한다. offset_1 또는 offset_2가 1보다작은경우에반환한다. offset_1 또는 offset_2가 LOBMAXSIZE보다큰경우에반환한다. DECLARE lob_1 CLOB := 'abcdefgh'; lob_2 CLOB := 'abcdefgg'; BEGIN IF DBMS_LOB.COMPARElob_1, lob_2) = 0 then DBMS_OUTPUT.PUT_LINE'LOB_1 equals LOB_2' ELSE DBMS_OUTPUT.PUT_LINE'LOB_1 does not equals LOB_2' 제 2 장 DBMS_LOB 23

46 END IF; END; / LOB_1 does not equals LOB_2 PSM completed SQL> GETLENGTH 대상 LOB 데이터의길이를반환하는함수이다. 이때길이는입력 LOB 데이터의타입에따라 byte 또는 문자단위의값으로반환된다. GETLENGTH 함수의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.GETLENGTH lob IN BLOB ) RETURN INTEGER; CLOB 타입인경우 DBMS_LOB.GETLENGTH lob IN CLOB ) RETURN INTEGER; lob 대상 LOB locator 이다. 반환값 0 NULL 빈 LOB 데이터인경우에반환한다. 입력 LOB 데이터의가 NULL 인경우에반환한다. 24 Tibero RDBMS tbpsm 참조안내서

47 COPY, ERASE, WRITE 등의프로시저에의해채워진 0BLOB 데이터 ) 또는공백 CLOB 데이터 ) 문자 도대상 LOB 데이터의길이에포함된다. DECLARE lob CLOB := 'architecture'; BEGIN DBMS_OUTPUT.PUT_LINEDBMS_LOB.GETLENGTHlob) END; / 12 PSM completed SQL> INSTR 대상 LOB 데이터내에서주어진패턴이 n 번째로나타나는오프셋을반환하는함수이다. 패턴을탐색하는위치는대상 LOB 데이터의처음일수도있고입력로지정된위치일수도있다. 이때탐색할대상패턴은 LIKE 연산자에서사용되는퍼센트 %) 또는언더바 _) 와같은와일드카드문자등을포함할수없다. INSTR 함수의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.INSTR lob IN BLOB, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1 ) RETURN INTEGER; CLOB 타입인경우 DBMS_LOB.INSTR lob IN CLOB, pattern IN VARCHAR, offset IN INTEGER := 1, 제 2 장 DBMS_LOB 25

48 ) RETURN INTEGER; nth IN INTEGER := 1 lob pattern offset 패턴을탐색할대상 LOB locator이다. 탐색할패턴이다. LOB 데이터내의탐색을시작할오프셋이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 )) nth 탐색할패턴의개수이다. 1 이상의값을가지며, 1 이면첫번째패턴의오프셋을반환한다. 반환값 반환값 INTEGER 패턴과일치하는부분을찾은경우해당패턴이시작되는오프셋을반환한다. 만약찾지못한경우에는 0 을반환한다. NULL IN 중하나라도 NULL 인경우에반환한다. offset 이 1 보다작거나 LOBMAXSIZE 보다큰경우에반환한다. nth 가 1 보다작거나 LOBMAXSIZE 보다큰경우에반환한다. DECLARE lob CLOB := 'Corporate floor'; result NUMBER; BEGIN result := DBMS_LOB.INSTRlob, 'or', 3, 2 DBMS_OUTPUT.PUT_LINE'Result offset = ' result END; / Result offset = 14 PSM completed SQL> 26 Tibero RDBMS tbpsm 참조안내서

49 ISTEMPORARY 주어진 LOB 이임시 LOB 인지아닌지의여부를확인하는함수이다. ISTEMPORARY 함수의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.ISTEMPORARY lob IN BLOB ) RETURN INTEGER; CLOB 타입인경우 DBMS_LOB.ISTEMPORARY lob IN CLOB ) RETURN INTEGER; lob 대상 LOB locator 이다. 반환값 반환값 1 0 임시 LOB 인경우에반환한다. 임시 LOB 이아닌경우에반환한다. DECLARE lob CLOB; BEGIN DBMS_LOB.CREATETEMPORARYlob, false IF DBMS_LOB.ISTEMPORARYlob) = 1THEN DBMS_OUTPUT.PUT_LINE'TRUE' ELSE 제 2 장 DBMS_LOB 27

50 DBMS_OUTPUT.PUT_LINE'FALSE' END IF; END; / TRUE PSM completed SQL> SUBSTR 대상 LOB 데이터의지정된오프셋의위치로부터지정된크기만큼의데이터를반환하는함수이다. 반환될최대크기는 32767byte이며, CLOB 데이터의경우반환될최대문자개수는 32767을 2로나눈값이다. CLOB 데이터에대하여이함수를호출하면같은문자집합의문자열이반환된다. 이함수는하나의라도 NULL이넘겨지거나, amount 또는 offset의값이 1보다작거나 LOBMAXSIZE보다큰경우에 NULL을반환한다. 이함수를 CLOB 데이터에대해클라이언트쪽에서호출한경우, 만약클라이언트쪽에설정된문자집합가 CLOB 데이터문자집합과다르다면데이터전송중에자동적으로문자집합의변환이일어난다. SUBSTR 함수의세부내용은다음과같다. BLOB 타입인경우 DBMS_LOB.SUBSTR lob IN BLOB, amount IN INTEGER := 32767, offset IN INTEGER := 1 ) RETURN RAW; CLOB 타입인경우 DBMS_LOB.SUBSTR lob IN CLOB, amount IN INTEGER := 32767, offset IN INTEGER := 1 ) RETURN VARCHAR2; 28 Tibero RDBMS tbpsm 참조안내서

51 lob amount 대상 LOB 데이터이다. 읽을데이터의크기이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 ) 개수 ) offset 데이터를읽을 LOB 데이터내의오프셋이다. 단위 : byteblob 데이터 ) 또는문자 CLOB 데이터 )) 반환값 반환값 RAW/VARCHAR2 데이터 SUBSTR 함수가성공적으로종료된경우에반환한다. NULL 입력가하나라도 NULL 인경우에반환한다. amount 가 1 보다작거나 32767byte 보다큰경우에반환한다. offset 이 1 보다작거나 LOBMAXSIZE 보다큰경우에반환한다. DECLARE lob CLOB := 'Your friend is too young'; buffer VARCHAR2100 BEGIN buffer := DBMS_LOB.SUBSTRlob, 6, 6 DBMS_OUTPUT.PUT_LINE'My favorite word is ' UPPERbuffer) END; / My favorite word is FRIEND PSM completed SQL> 제 2 장 DBMS_LOB 29

52

53 제 3 장 DBMS_OBFUSCATION_TOOLKIT 본장에서는 DBMS_OBFUSCATION_TOOLKIT 패키지의기본개념과패키지내의프로시저와함수를 사용하는방법을한다 개요 DBMS_OBFUSCATION_TOOLKIT은데이터를암호화 encryption) 하고복호화 decryption) 하는패키지이다. 이패키지에서는데이터의암호화및복호화를위해 DESData Encryption Standard) 또는 3DESTriple DES) 알고리즘을이용한다. DES와 3DES 알고리즘은대칭키 symmetric key) 를사용하는알고리즘이다. 알고리즘 DES 56 bit 의키를사용하는알고리즘이다. 그동안널리사용되어왔으나최근에는보 안성이보장되지않아점차사용빈도가줄어들고있다. 대칭키를사용하는알고리즘이다. 3DES 하나의데이터에 DES 알고리즘을두번내지세번반복하여적용하는알고리즘 이다. 각각 * 2) bit 와 * 3) bit 키를사용한다. DES 알고리즘과마찬 가지로대칭키를사용하는알고리즘이다. 다른대칭키를사용하는알고리즘에비해암호화및복호화를하는시간이많이 필요하다는단점이있다. 이러한대칭키를사용하는암호화알고리즘에서는키를안전하게관리하는것이무엇보다중요하다. 암호화알고리즘에서키를관리하는방법은다음과같다. 데이터베이스에키를저장하는방법특정테이블의컬럼에키를저장하는방법이다. 이때키를저장하는테이블은암호화된데이터컬럼과같은테이블일수도있고, 다른테이블일수도있다. 같은테이블에키를저장하는경우에는해당테이블전체에대한액세스특권을부여하는대신뷰또는 tbpsm 프로그램등을통하여해당테이블에액세스해야한다. 운영체제파일에저장하는방법 제 3 장 DBMS_OBFUSCATION_TOOLKIT 31

54 키를파일에저장하고, 데이터베이스내에서 tbpsm 프로그램등을통하여운영체제파일로부터키를읽어데이터를암호화하거나복호화하는방법이다. 이때키를저장하는운영체제파일은패스워드파일과같이다른사람이직접액세스할수없도록보안을유지해야한다. 사용자가키를직접입력하는방법사용자또는애플리케이션프로그램내에서필요할때마다키를데이터베이스로전송하여암호화및복호화를수행하는방법이다. 이방법은사용자가키를항상암기하고있거나애플리케이션프로그램의소스내에키가포함되어있어야한다. 또한, 키는네트워크를통하여데이터베이스서버로전달되므로, 네트워크보안에도유의해야한다 프로시저와함수 본절에서는 DBMS_OBFUSCATION_TOOLKIT 패키지에서제공하는프로시저와함수를알파벳순으로 한다 DES3DECRYPT 3DES 알고리즘을이용하여암호화된데이터를복호화하는프로시저와함수이다. 주어진암호화된데이터에대하여두번또는세번의 DES 복호화를수행할수있다. 복호화를위해주어진키에대해복호화를두번수행한다면 16byte128bit), 세번을수행한다면 24byte192bit) 이어야하며, 그렇지않으면예외상황이발생한다. 디폴트는두번의 DES 복호화를수행한다. 암호화된데이터와같은횟수만큼 DES 알고리즘을수행해야한다. DES3DECRYPT 프로시저와함수의세부내용은다음과같다. 프로시저 DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT input IN RAW, key IN RAW, decrypted_data OUT RAW, which IN PLS_INTEGER DEFAULT 0, iv IN RAW DEFAULT NULL 32 Tibero RDBMS tbpsm 참조안내서

55 DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT input_string IN VARCHAR2, key_string IN VARCHAR2, decrypted_string OUT VARCHAR2, which IN PLS_INTEGER DEFAULT 0, iv_string IN VARCHAR2 DEFAULT NULL 함수 DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT input IN RAW, key IN RAW, which IN PLS_INTEGER DEFAULT 0, iv IN RAW DEFAULT NULL ) RETURN RAW; DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT input_string IN VARCHAR2, key_string IN VARCHAR2, which IN PLS_INTEGER DEFAULT 0, iv_string IN VARCHAR2 DEFAULT NULL ) RETURN VARCHAR2; input, input_string key decrypted_data 복호화할데이터이다. 복호화하기위해주어진키값이다. 복호화된결과데이터이다. which 0 이면 DES 복호화를 2 번수행한다. 1 이면 DES 복호화를 3 번수행한다. iv 초기화벡터이다. 예외상황 제 3 장 DBMS_OBFUSCATION_TOOLKIT 33

56 예외상황 INVALID_ARGUMENT INVALID_INPUT KEY_TOO_SHORT INVALID_DES_MODE 중하나라도 NULL인경우이다. input_data의길이가 8의배수가아닌경우이다. key 값의길이가 8보다작은경우이다. which 값이 0 또는 1이아닌경우이다. DECLARE data RAW256 key RAW16 encrypted_data RAW256 decrypted_data RAW256 BEGIN data := ' AE030D ABCDEF'; key := '0A123B8E002CD3FF01DE2389A4567BCF'; DBMS_OBFUSCATION_TOOLKIT.DES3Encryptinput => data, key => key, encrypted_data => encrypted_data data := encrypted_data; DBMS_OBFUSCATION_TOOLKIT.DES3Decryptinput => data, key => key, decrypted_data => decrypted_data END; DES3ENCRYPT 3DES 알고리즘을이용하여데이터를암호화하는프로시저와함수이다. 주어진데이터에대하여두번또는세번의 DES 암호화를수행할수있다. 암호화를위해주어진키에대해암호화를두번을수행한다면 16byte128bit), 세번을수행한다면 24byte192bit) 이어야하며, 그렇지않으면예외상황이발생한다. 디폴트는두번의 DES 암호화를수행한다. DES3ENCRYPT 프로시저와함수의세부내용은다음과같다. 프로시저 DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT input IN RAW, key IN RAW, encrypted_data OUT RAW, 34 Tibero RDBMS tbpsm 참조안내서

57 which IN PLS_INTEGER DEFAULT 0, iv IN RAW DEFAULT NULL DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT input_string IN VARCHAR2, key_string IN VARCHAR2, encrypted_string OUT VARCHAR2, which IN PLS_INTEGER DEFAULT 0, iv_string IN VARCHAR2 DEFAULT NULL 함수 DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT input IN RAW, key IN RAW, which IN PLS_INTEGER DEFAULT 0, iv IN RAW DEFAULT NULL ) RETURN RAW; DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT input_string IN VARCHAR2, key_string IN VARCHAR2, which IN PLS_INTEGER DEFAULT 0, iv_string IN VARCHAR2 DEFAULT NULL ) RETURN VARCHAR2; input, input_string key encrypted_data 암호화할데이터이다. 암호화하기위해주어진키값이다. 암호화된결과데이터이다. which 0 이면 DES 암호화를 2 번수행한다. 1 이면 DES 암호화를 3 번수행한다. iv 초기화벡터이다. 제 3 장 DBMS_OBFUSCATION_TOOLKIT 35

58 예외상황 예외상황 INVALID_ARGUMENT INVALID_INPUT KEY_TOO_SHORT INVALID_DES_MODE 중하나라도 NULL인경우이다. input_data의길이가 8의배수가아닌경우이다. key 값의길이가 8보다작은경우이다. which 값이 0 또는 1이아닌경우이다. DECLARE data RAW256 key RAW16 encrypted_data RAW256 decrypted_data RAW256 BEGIN data := ' AE030D ABCDEF'; key := '0A123B8E002CD3FF01DE2389A4567BCF'; DBMS_OBFUSCATION_TOOLKIT.DES3Encryptinput => data, key => key, encrypted_data => encrypted_data data := encrypted_data; DBMS_OBFUSCATION_TOOLKIT.DES3Decryptinput => data, key => key, decrypted_data => decrypted_data END; DES3GETKEY 임의의값을입력값으로받아 DES3 알고리즘을위한키를생성하는프로시저와함수이다. DES3GETKEY 프로시저와함수의세부내용은다음과같다. 프로시저 DBMS_OBFUSCATION_TOOLKIT.DES3GETKEY which IN PLS_INTEGER DEFAULT 0, seed IN RAW, key OUT RAW DBMS_OBFUSCATION_TOOLKIT.DES3GETKEY 36 Tibero RDBMS tbpsm 참조안내서

59 which IN PLS_INTEGER DEFAULT 0, seed_string IN VARCHAR2, key OUT VARCHAR2 함수 DBMS_OBFUSCATION_TOOLKIT.DES3GETKEY which IN PLS_INTEGER DEFAULT 0, seed IN RAW ) RETURN RAW; DBMS_OBFUSCATION_TOOLKIT.DES3GETKEY which IN PLS_INTEGER DEFAULT 0, seed_string IN VARCHAR2 ) RETURN VARCHAR2; which 0 이면 DES 암호화를 2 번수행한다. 1 이면 DES 암호화를 3 번수행한다. seed key 80 자이상의임의의값이다. 암호화하기위해주어진키값이다. 예외상황 예외상황 NO_SEED SEED_TOO_SHORT INVALID_INPUT INVALID_DES_MODE seed의값이 NULL인경우이다. seed의길이가 80보다작은경우이다. which가 NULL인경우이다. which의값이 0 또는 1이아닌경우이다. DECLARE data VARCHAR24096 제 3 장 DBMS_OBFUSCATION_TOOLKIT 37

60 key VARCHAR24096 key_seed VARCHAR24096 encrypted_data VARCHAR24096 decrypted_data VARCHAR24096 BEGIN data := ' AE030D'; key_seed := ' ' ' ' ' ' ' '; key_seed := rpadkey_seed,80 key := dbms_obfuscation_toolkit.des3getkeyseed_string => key_seed DBMS_OBFUSCATION_TOOLKIT.DES3Encryptinput_string => data, key_string => key, encrypted_string=> encrypted_data data := encrypted_data; DBMS_OBFUSCATION_TOOLKIT.DES3Decryptinput_string => data, key_string => key, decrypted_string => decrypted_data END; / DESDECRYPT DES 알고리즘을이용하여암호화된데이터를복호화하는프로시저와함수이다. 복호화를위해주어진 키는반드시 8byte64bit) 이어야하며, 그렇지않으면예외상황이발생한다. DESDECRYPT 프로시저와함수의세부내용은다음과같다. 프로시저 DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT input IN RAW, key IN RAW, decrypted_data OUT RAW DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT input_string IN VARCHAR2, key_string IN VARCHAR2, decrypted_string OUT VARCHAR2 함수 38 Tibero RDBMS tbpsm 참조안내서

61 DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT input IN RAW, key IN RAW ) RETURN RAW; DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT input_string IN VARCHAR2, key_string IN VARCHAR2 RETURN VARCHAR2; input, input_string key decrypted_data 복호화할데이터이다. 복호화하기위해주어진키값이다. 복호화된결과데이터이다. 예외상황 예외상황 INVALID_ARGUMENT INVALID_INPUT KEY_TOO_SHORT 중하나라도 NULL인경우이다. input_data의길이가 8의배수가아닌경우이다. key 값의길이가 8보다작은경우이다. DECLARE data RAW256 key RAW16 encrypted_data RAW256 decrypted_data RAW256 BEGIN data := ' AE030D'; key := '0A123B8E002CD3FF'; DBMS_OBFUSCATION_TOOLKIT.DESEncryptinput => data, key => key, encrypted_data => encrypted_data data := encrypted_data; DBMS_OBFUSCATION_TOOLKIT.DESDecryptinput => data, key => key, 제 3 장 DBMS_OBFUSCATION_TOOLKIT 39

62 END; decrypted_data => decrypted_data DESENCRYPT DES 알고리즘을이용하여데이터를암호화하는프로시저와함수이다. 암호화를위해주어진키는반드 시 8byte64bit) 이어야하며, 그렇지않으면예외상황이발생한다. DESENCRYPT 프로시저와함수의세부내용은다음과같다. 프로시저 DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT input IN RAW, key IN RAW, encrypted_data OUT RAW DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT input_string IN VARCHAR2, key_string IN VARCHAR2, encrypted_string OUT VARCHAR2 함수 DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT input IN RAW, key IN RAW ) RETURN RAW; DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT input_string IN VARCHAR2, key_string IN VARCHAR2 ) RETURN VARCHAR2; 40 Tibero RDBMS tbpsm 참조안내서

63 input, input_string key encrypted_data 암호화할데이터이다. 암호화하기위해주어진키값이다. 암호화된결과데이터이다. 예외상황 예외상황 INVALID_ARGUMENT INVALID_INPUT KEY_TOO_SHORT 중하나라도 NULL인경우이다. input_data의길이가 8의배수가아닌경우이다. key 값의길이가 8보다작은경우이다. DECLARE data RAW256 key RAW16 encrypted_data RAW256 decrypted_data RAW256 BEGIN data := ' AE030D'; key := '0A123B8E002CD3FF'; DBMS_OBFUSCATION_TOOLKIT.DESEncryptinput => data, key => key, encrypted_data => encrypted_data data := encrypted_data; DBMS_OBFUSCATION_TOOLKIT.DESDecryptinput => data, key => key, decrypted_data => decrypted_data END; DESGETKEY 임의의값을입력값으로받아 DES 알고리즘을위한키를생성하는프로시저와함수이다. DESGETKEY 프로시저와함수의세부내용은다음과같다. 프로시저 DBMS_OBFUSCATION_TOOLKIT.DESGETKEY seed IN RAW, 제 3 장 DBMS_OBFUSCATION_TOOLKIT 41

64 key OUT RAW DBMS_OBFUSCATION_TOOLKIT.DESGETKEY seed_string IN VARCHAR2, key OUT VARCHAR2 함수 DBMS_OBFUSCATION_TOOLKIT.DESGETKEY seed IN RAW ) RETURN RAW; DBMS_OBFUSCATION_TOOLKIT.DESGETKEY seed_string IN VARCHAR2 ) RETURN VARCHAR2; seed key 80 자이상의임의의값이다. 암호화하기위해주어진키값이다. 예외상황 예외상황 NO_SEED SEED_TOO_SHORT seed 의값이 NULL 인경우이다. seed 의길이가 80 보다작은경우이다. DECLARE data VARCHAR24096 key VARCHAR24096 key_seed VARCHAR24096 encrypted_data VARCHAR24096 decrypted_data VARCHAR24096 BEGIN 42 Tibero RDBMS tbpsm 참조안내서

65 data := ' AE030D'; key_seed := ' ' ' ' ' ' ' '; key_seed := rpadkey_seed,80 key := dbms_obfuscation_toolkit.desgetkeyseed_string => key_seed DBMS_OBFUSCATION_TOOLKIT.DESEncryptinput_string => data, key_string => key, encrypted_string=> encrypted_data data := encrypted_data; DBMS_OBFUSCATION_TOOLKIT.DESDecryptinput_string => data, key_string => key, decrypted_string => decrypted_data END; / 제 3 장 DBMS_OBFUSCATION_TOOLKIT 43

66

67 제 4 장 DBMS_OUTPUT 본장에서는 DBMS_OUTPUT 패키지의기본개념과패키지내의프로시저를사용하는방법을한다 개요 DBMS_OUTPUT은메시지를버퍼에저장하고버퍼로부터메시지를읽어오기위한인터페이스를제공하는패키지이다. 하나의프로시저, 함수, 트리거등에의해저장된메시지는다른프로시저, 함수, 트리거등에서읽어올수있다. DBMS_OUTPUT 패키지내의 ENABLE 프로시저를실행하여메시지를주고받기위한버퍼를지정된크기로할당한다. 만약메시지를지정된버퍼크기이상으로저장하려고하면예외상황이발생한다. 할당된버퍼는 DISABLE 프로시저를실행하여제거할수있으며, 다시 ENABLE 프로시저를실행하기전에는 GET_LINE 및 GET_LINES 또는 PUT 및 PUT_LINE, NEW_LINE 프로시저를호출해도무시된다. 버퍼의메시지는여러라인으로구성되어있으며, 라인마다라인끝 End of Line, 이하 EOL) 문자로끝난다. PUT_LINE 프로시저를통해버퍼에저장된메시지는 GET_LINE이나 GET_LINES 프로시저를실행하여읽어올수있다. 다음은 DBMS_OUTPUT 패키지내에정의된타입이다. CHARARR 메시지버퍼에서읽어온내용을저장하기위한공간이다. TYPE CHARARR IS TABLE OF VARCHAR232767) 4.2. 프로시저 본절에서는 DBMS_OUTPUT 패키지에서제공하는프로시저를알파벳순으로한다 DISABLE 할당된메시지버퍼를제거하고, DBMS_OUTPUT 패키지내의다른프로시저를사용할수없게하는프 로시저이다. 이프로서지를실행하면메시지버퍼에남아있는모든메시지가함께제거된다. 제 4 장 DBMS_OUTPUT 45

68 DISABLE 프로시저의세부내용은다음과같다. DBMS_OUTPUT.DISABLE; BEGIN DBMS_OUTPUT.PUT_LINE'Before DISABLE' DBMS_OUTPUT.DISABLE; DBMS_OUTPUT.PUT_LINE'After DISABLE' END; / PSM completed SQL> ENABLE 지정된크기의메시지버퍼를할당하고 DBMS_OUTPUT 패키지내의다른프로시저를사용할수있게하는프로시저이다. 이프로시저를여러번호출하면가장크게지정한크기로메시지버퍼를할당한다. 이때메시지버퍼의크기는최소 2KB, 최대 2MB이다. ENABLE 프로시저의세부내용은다음과같다. DBMS_OUTPUT.ENABLE buffer_size IN INTEGER DEFAULT buffer_size 할당할메시지버퍼의크기이다. 단위 : byte) BEGIN DBMS_OUTPUT.DISABLE; DBMS_OUTPUT.PUT_LINE'Before ENABLE' DBMS_OUTPUT.ENABLE32768 DBMS_OUTPUT.PUT_LINE'After ENABLE' 46 Tibero RDBMS tbpsm 참조안내서

69 END; / After ENABLE PSM completed SQL> GET_LINE, GET_LINES 메시지버퍼로부터라인단위로메시지를읽어오는프로시저이다. 이프로시저는라인단위로메시지를읽으며, 하나의라인을형성하지않은메시지는읽지않는다. GET_LINE 또는 GET_LINES 프로시저를호출한후에 PUT 또는 PUT_LINE 프로시저를호출하면, 현재까지메시지버퍼에남아있던메시지는모두제거된다. 한라인의메시지는최대 255byte의크기를가지므로, 출력의크기를충분하게설정해야한다. GET_LINE, GET_LINES 프로시저의세부내용은다음과같다. GET_LINE GET_LINE 프로시저는한번호출될때마다하나의라인만을읽어온다. DBMS_OUTPUT.GET_LINE line OUT VARCHAR, status OUT INTEGER GET_LINES GET_LINE 프로시저는지정된수만큼, 한번에여러라인의메시지를읽어온다. 이때메시지버퍼로부터실제로읽어온메시지라인의수를반환한다. 만약메시지버퍼내에충분한수의메시지라인이없어서지정된수만큼의메시지라인을가져오지못하면, 가져온메시지라인의수만큼만저장된다. DBMS_OUTPUT.GET_LINES lines OUT CHARARR, num_lines IN OUT INTEGER 제 4 장 DBMS_OUTPUT 47

70 line, lines 메시지버퍼로부터읽어온한라인또는여러라인의메시지이다. status 메시지를성공적으로읽어온경우에는 0 을반환한다. 메시지를성공적으로읽어오지못한경우에는 1 을반환한다. num_lines 읽어올메시지라인의수를입력하고, 실제로읽어온메시지라인의수를출 력한다. GET_LINE DECLARE message VARCHAR1024 status INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE'A poet is the painter of the soul' DBMS_OUTPUT.PUT_LINE'Faith without deeds is useless' DBMS_OUTPUT.PUT_LINE'Forgiveness is better than revenge' DBMS_OUTPUT.GET_LINEmessage, status DBMS_OUTPUT.PUT_LINEmessage END; / A poet is the painter of the soul PSM completed SQL> GET_LINES DECLARE message_arr DBMS_OUTPUT.CHARARR; num_lines INTEGER := 4; BEGIN DBMS_OUTPUT.PUT'A poet is ' DBMS_OUTPUT.PUT'the painter of the soul' DBMS_OUTPUT.NEW_LINE; DBMS_OUTPUT.PUT_LINE'Faith without deeds is useless' DBMS_OUTPUT.PUT_LINE'Forgiveness is better than revenge' DBMS_OUTPUT.GET_LINESmessage_arr, num_lines DBMS_OUTPUT.PUT_LINEmessage_arr2) END; / Faith without deeds is useless 48 Tibero RDBMS tbpsm 참조안내서

71 PSM completed SQL> NEW_LINE 메시지버퍼에 EOL 문자를저장하는프로시저이다. PUT 프로시저로메시지를저장한경우, EOL 문자가없으므로라인단위로메시지를읽는 GET_LINE, GET_LINES 프로시저로문자열을읽어올수없다. 단, NEW_LINE 프로시저를사용하면 GET_LINE, GET_LINES 프로시저로문자열을읽어올수있다. NEW_LINE 프로시저의세부내용은다음과같다. DBMS_OUTPUT.NEW_LINE; BEGIN DBMS_OUTPUT.PUT_LINE'The will of a man is his happiness' DBMS_OUTPUT.NEW_LINE; DBMS_OUTPUT.PUT_LINE'Love your neighbor as yourself' END; / The will of a man is his happiness Love your neighbor as yourself PSM completed SQL> PUT, PUT_LINE 메시지버퍼에메시지를저장하는프로시저이다. PUT, PUT_LINE 프로시저의세부내용은다음과같다. PUT 제 4 장 DBMS_OUTPUT 49

72 PUT 프로시저는하나의라인을여러번에걸쳐저장할수있다. 버퍼에저장되는메시지의마지막라 인끝에 EOL 문자가첨부되지않는다. DBMS_OUTPUT.PUTdata IN NUMBER DBMS_OUTPUT.PUTdata IN VARCHAR PUT_LINE PUT_LINE 프로시저는 PUT 프로시저와는반대로버퍼에저장되는메시지의마지막라인끝에 EOL 문자를첨부한다. 만약 EOL 문자만을저장하려면 NEW_LINE 프로시저를호출한다. DBMS_OUTPUT.PUT_LINEdata IN NUMBER DBMS_OUTPUT.PUT_LINEdata IN VARCHAR 입력값으로주어진의데이터타입은 NUMBER, VARCHAR, DATE 세가지이며, 메시지로저장되기전에항상 VARCHAR 타입으로변환된다. NUMBER와 DATE 타입의데이터는 TO_CHAR 함수를적용하여반환된결과로저장된다. data 메시지버퍼에저장할메시지데이터이다. 예외상황 예외상황 BUF_OVERFLOW 메시지버퍼의최댓값을초과한경우이다. 50 Tibero RDBMS tbpsm 참조안내서

73 제 5 장 DBMS_STATS 본장에서는 DBMS_STATS 패키지의기본개념과패키지내의프로시저와함수를사용하는방법을 한다 개요 DBMS_STATS는데이터베이스객체의통계정보를수집하고관리하는패키지이다. 이패키지를사용하면데이터베이스에있는컬럼, 테이블, 데이터사전 DD: Data Dictionary), 인덱스, 스키마, 시스템등에대한통계정보를수집하고관리 예 : 삭제또는초기화등 ) 할수있다 프로시저 본절에서는 DBMS_STATS 패키지에서제공하는프로시저를알파벳순으로한다 CREATE_STAT_TABLE 통계정보를저장할통계테이블을생성하는프로시저이다. CREATE_STAT_TABLE 프로시저의세부내용은다음과같다. DBMS_STATS.CREATE_STAT_TABLE ownname VARCHAR2, stattab VARCHAR2, tblspace VARCHAR2 DEFAULT NULL ownname stattab tblspace 통계테이블이속한스키마의이름이다. 통계정보를저장할통계테이블의이름이다. 통계테이블이속할테이블스페이스의이름이다. 기본값 : NULL, NULL 일경우 SYSTEM 테이블스페이스에저장된다.) 제 5 장 DBMS_STATS 51

74 예외상황 예외상황 7068 잘못된스키마를입력한경우이다 DELETE_COLUMN_STATS 컬럼의통계정보를삭제하는프로시저이다. DELETE_COLUMN_STATS 프로시저의세부내용은다음과같다. DBMS_STATS.DELETE_COLUMN_STATS ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, no_invalidate BOOLEAN DEFAULT to_booleanget_param'stat_no_invalidate')) ownname tabname colname partname 스키마의이름이다. 컬럼이속한테이블의이름이다. 컬럼의이름이다. 통계정보를삭제할파티션의이름이다. 테이블이파티션되어있고, partname 의값이 NULL 이면테이블수준의컬럼 통계정보를삭제한다. cascade_parts no_invalidate 테이블이파티션되어있고 partname 이 NULL 일때, 이를 TRUE 로 설정하면모든파티션수준의컬럼통계정보도함께삭제한다. TRUE 로설정하면관련된 Physical Plan 을삭제하지않는다. 예외상황 예외상황 OBJECT_NOT_EXISTS 객체가존재하지않는경우이다. 52 Tibero RDBMS tbpsm 참조안내서

75 DELETE_DATABASE_STATS 데이터베이스에있는모든테이블의통계정보를삭제하는프로시저이다. DELETE_DATABASE_STATS 프로시저의세부내용은다음과같다. DBMS_STATS.DELETE_DATABASE_STATS no_invalidate BOOLEAN DEFAULT to_booleanget_param'stat_no_invalidate')) no_invalidate TRUE 로설정하면관련된 Physical Plan 을삭제하지않는다 DELETE_DICTIONARY_STATS 모든데이터사전의스키마 SYS, SYSCAT) 의통계정보를삭제하는프로시저이다. DELETE_DICTIONARY_STATS 프로시저의세부내용은다음과같다. DBMS_STATS.DELETE_DICTIONARY_STATS no_invalidate BOOLEAN DEFAULT to_booleanget_param'stat_no_invalidate')) no_invalidate TRUE 로설정하면관련된 Physical Plan 을삭제하지않는다 DELETE_INDEX_STATS 인덱스의통계정보를삭제하는프로시저이다. DELETE_INDEX_STATS 프로시저의세부내용은다음과같다. 제 5 장 DBMS_STATS 53

76 DBMS_STATS.DELETE_INDEX_STATS ownname VARCHAR2, idxname VARCHAR2, partname VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, no_invalidate BOOLEAN DEFAULT to_booleanget_param'stat_no_invalidate')) ownname idxname partname 스키마의이름이다. 인덱스의이름이다. 통계정보를삭제할파티션의이름이다. 인덱스가파티션으로나누어져있고, partname 의값이 NULL 이면인덱스수 준의통계정보를삭제한다. cascade_parts no_invalidate 인덱스가파티션으로나누어져있고, partname 의값이 NULL 일때, 이파라 미터를 TRUE 로설정하면모든파티션수준의통계정보도함께삭제한다. TRUE 로설정하면관련된 Physical Plan 을삭제하지않는다. 예외상황 예외상황 OBJECT_NOT_EXISTS 객체가존재하지않는경우이다 DELETE_SCHEMA_STATS 스키마전체의통계정보를삭제하는프로시저이다. DELETE_SCHEMA_STATS 프로시저의세부내용은다음과같다. DBMS_STATS.DELETE_SCHEMA_STATS ownname VARCHAR2, no_invalidate BOOLEAN DEFAULT to_booleanget_param'stat_no_invalidate')) 54 Tibero RDBMS tbpsm 참조안내서

77 ownname no_invalidate 스키마의이름이다. TRUE 로설정하면관련된 Physical Plan 을삭제하지않는다. 예외상황 예외상황 OBJECT_NOT_EXISTS 객체가존재하지않는경우이다 DELETE_SYSTEM_STATS workload 시스템의통계정보를삭제하고, noworkload 시스템의통계정보를초기화하는프로시저이다. workload 시스템의통계정보는 INTERVAL, START, STOP 옵션으로, noworkload 시스템의통계정보는 NOWORKLOAD 옵션으로수집한다. DELETE_SYSTEM_STATS 프로시저의세부내용은다음과같다. DBMS_STATS.DELETE_SYSTEM_STATS 예외상황 예외상황 DBA 권한이없는경우이다 DELETE_TABLE_STATS 테이블의통계정보를삭제하는프로시저이다. DELETE_TABLE_STATS 프로시저의세부내용은다음과같다. DBMS_STATS.DELETE_TABLE_STATS ownname VARCHAR2, tabname VARCHAR2, 제 5 장 DBMS_STATS 55