단계

본문서에서는 Tibero RDBMS 4( 이하 Tibero4) 에서기본적으로제공하는시스템패키지의종류와예제를통 해각패키지내여러프로시저와함수의사용법을알아보도록한다.

Contents 1. TIBERO4 SYSTEM PACKAGE 개요... 4 2. DBMS_LOB... 5 2.1. DBMS_LOB 패키지내프로시져... 6 2.1.1. APPEND... 6 2.1.2. COPY... 6 2.1.3. CREATETEMPORARY... 7 2.1.4. ERASE... 7 2.1.5. READ... 8 2.1.6. TRIM... 9 2.1.7. WRITE... 9 2.1.8. WRITEAPPEND... 10 2.2. DBMS_LOB 패키지내함수... 11 2.2.1. COMPARE... 11 2.2.2. FREETEMPORARY... 11 2.2.3. GETLENGTH... 12 2.2.4. INSTR... 12 2.2.5. ISTEMPORARY... 13 2.2.6. SUBSTR... 13 3. DBMS_OBFUSCATION_TOOLKIT... 14 3.1. DES3ENCRYPT 프로시저와함수... 15 3.1.1. DES3ENCRYPT 프로시저와함수... 15 3.1.2. DES3DECRYPT 프로시저와함수... 15 3.1.3. DES3ENCRYPTDES3DECRYPT를이용한예제... 16 3.2. DES3GETKEY 프로시저와함수... 17 3.2.1. DES3GETKEY 프로시저와함수... 17 3.2.2. DES3GETKEY 예제... 17 3.3. DESENCRYPT DESDECRYPT 프로시저와함수... 18 3.3.1. DESENCRYPT 프로시저와함수... 18 3.3.2. DESDECRYPT 프로시저와함수... 18 3.3.3. DESENCRYPTDESDECRYPT를이용한예제... 18 1

3.4. DESGETKEY 프로시저와함수... 19 3.4.1. DESGETKEY 예제... 19 4. DBMS_OUTPUT... 20 4.1. DBMS_OUTPUT 패키지내프로시저... 21 4.1.1. DISABLE... 21 4.1.2. ENABLE... 21 4.1.3. GET_LINE, GET_LINES... 22 4.1.4. NEW_LINE... 23 4.1.5. PUT, PUT_LINE... 23 5. DBMS_STATS... 24 5.1. DBMS_STATS 패키지내프로시저... 25 5.1.1. DATABASE_STATS... 25 5.1.2. DICTIONARY_STATS... 26 5.1.3. INDEX_STATS... 27 5.1.4. SCHEMA_STATS... 28 5.1.5. TABLE_STATS... 29 5.1.6. COLUMN_STATS... 30 5.1.7. SET_PARAM, GET_PARAM... 31 6. DBMS_TRANSACTION... 32 6.1. DBMS_TRANSACTION 패키지내프로시저... 33 6.1.1. COMMIT... 33 6.1.2. ROLLBACK, ROLLBACK_SAVEPOINT... 33 6.1.3. SAVEPOINT... 34 7. UTL_RAW... 35 7.1. UTL_RAW 패키지내함수... 36 7.1.1. BIT 연산관련 FUNCTION... 36 7.1.2. CAST 연산관련 FUNCTION... 37 7.1.3. COMPARE... 39 7.1.4. CONCAT... 40 7.1.5. COPIES... 40 7.1.6. LENGTH... 41 7.1.7. OVERLAY... 41 8. DBMS_JOB... 42 2

8.1. DBMS_JOB 패키지내프로시저... 43 8.1.1. SUBMIT... 43 8.1.2. REMOVE... 43 8.1.3. CHANGE... 43 8.1.4. WHAT... 43 8.1.5. NEXT_DATE... 43 8.1.6. INTERVAL... 44 8.1.7. BROKEN... 44 8.1.8. RUN... 44 8.2. DBMS_JOB 예제... 45 9. UTL_FILE... 47 9.1. FCOPY... 47 9.2. PUT_LINE... 47 9.3. FCLOSE... 47 9.4. FCLOSE_ALL... 48 9.5. FREMOVE... 48 9.6. FRENAME... 49 Update History Date Worker Comments 2011.03.11 박근용문서서식업데이트 2009.10.20 장대훈문서서식업데이트및내용보완 교정 2008.06.20 백서현최초작성 3

1. Tibero4 System Package 개요 System Package는 Tibero4 시스템에서기본적으로제공하는패키지라이브러리이며, SYS사용자의소유로정의된다. System Package는 Tibero4 시스템을설치한후에일반사용자도사용할수있도록같은이름의 PUBLIC 동의어로정의된다. System Package Tibero4 에서제공하고있는기본 system package 는다음과같다. DBMS_LOB BLOB, CLOB 타입의대용량데이터를처리하기위한패키지 DBMS_OBFUSCATION DES, DES3 알고리즘을이용한데이터암호화및복호화 패키지 DBMS_LOB BLOB, CLOB타입의대용량데이터를처리하기위한패키지 DBMS_OBFUSCATION DES, DES3 알고리즘을이용한데이터암호화및복호화패키지 DBMS_OUTPUT 메시지버퍼에메시지를저장하고읽기위한패키지 DBMS_STATS 데이터베이스객체에대한통계정보를관리하기위한패키지 DBMS_TRANSACTION 트랜잭션문장을실행하고트랜잭션을관리하기위한패키지 UTL_RAW RAW 타입의데이터를처리하기위한패키지 DBMS_JOB JOB을관리하기위한패키지 UTL_FILE 파일에접근하기위한함수와프로시저를제공하는패키지 System Package 의프로시저와함수를호출하는방법은사용자패키지와동일하다. [ 참고 ] 본문서에서는각 package 에포함된프로시저나함수의내용은제시하지않고, 패키지 의사용법에대해초점을둔다. 자세한내용은패키지메뉴얼을참조하도록한다. 4

2. DBMS_LOB BLOB 또는 CLOB 타입의컬럼데이터에대한여러가지연산을제공하는패키지 패키지활용 DBMS_LOB 패키지내의프로시저와함수를이용하여대용량객체인 LOB 타입의젂체 또는일부에대하여읽기, 쓰기등의작업을수행할수있다. 패키지구성 [DBMS_LOB 패키지내프로시저 ] 1) APPEND 2) COPY 3) CREATETEMPORARY 4) ERASE 5) READ 6) TRIM 7) WRITE 8) WRITEAPPEND [DBMS_LOB 패키지내함수 ] 1) COMPARE 2) FREETEMPORARY 3) GETLENGTH 4) INSTR 5) ISTEMPORARY 6) SUBSTR. 5

2.1. DBMS_LOB 패키지내프로시져 **BLOB 타입은출력결과를바로확인할수없으므로, CLOB 타입을중심으로예제를작성한다. 2.1.1. APPEND : 원본 (source) LOB 데이터젂체를대상 (destination) LOB 데이터끝에붙여넣는프로시저 DBMS_LOB.APPEND(dest_lob, src_lob) **dest_lob : 대상 LOB locator **src_lob : 원본 LOB locator -- All s fair in 뒤에 love and war 문장을붙이는예제 SQL> SET SERVEROUTPUT ON dest_lob CLOB := 'All''s fair in '; src_lob CLOB := 'love and war'; DBMS_LOB.APPEND(dest_lob, src_lob); DBMS_OUTPUT.PUT_LINE('Result = ' dest_lob); Result = All's fair in love and war 2.1.2. COPY : 원본 LOB 데이터젂체또는일부를대상 LOB 데이터에복사하는프로시저 DBMS_LOB.COPY(dest_lob, src_lob, amount, dest_offset, src_offset) **dest_lob: 대상 LOB locator **src_lob: 원본 LOB locator **amount: 복사할문자의개수 **dest_offset: 대상 LOB 데이터내의시작위치 **src _offset: 원본 LOB 데이터내의시작위치 -- I love 뒤에 src_lob 에서 3 글자를뺀나머지데이터를복사하여 dest_lob 데이터에 +1 한위치에붙여넣는예제 SQL> SET SERVEROUTPUT ON dest_lob CLOB := 'I love '; src_lob CLOB := 'you him'; 6

DBMS_LOB.COPY(dest_lob, src_lob, length(src_lob)-3, length(dest_lob)+1, 1); DBMS_OUTPUT.PUT_LINE('Result = ' dest_lob); Result = I love you 2.1.3. CREATETEMPORARY : Temporary CLOB 또는 BLOB 을생성하는프로시저 DBMS_LOB.APPEND(dest_lob, src_lob) ** lob : 대상 LOB locator **cache :LOB 데이터를읽을때버퍼캐쉬 (buffer cache) 에저장할지여부 (boolean 타입 ) -- DBMS_LOB.CREATETEMPORARY 프로시저를통해임시로 CLOB 타입 lob_1 변수를열어 lob_2 데이터를붙여넣는예제. SQL> SET SERVEROUTPUT ON lob_1 CLOB; lob_2 CLOB := 'tibero'; DBMS_LOB.CREATETEMPORARY(lob_1, false); DBMS_LOB.APPEND(lob_1, lob_2); DBMS_OUTPUT.PUT_LINE(lob_1); tibero 2.1.4. ERASE : LOB 데이터의일부또는젂체를삭제하는프로시저. ( 삭제된영역에는 0(BLOB 데이터 ) 또는공백 (CLOB 데이터 ) 으로채워짂다.) DBMS_LOB.ERASE(lob, amount, offset) ** lob : 대상 LOB locator **amount: 삭제할문자의개수 **offset: 삭제할시작위치 7

-- CLOB 타입변수에 Tmaxsoft Tibero 라는데이터를저장한후, 앞에서 9 번째부터 7 글자를삭제하며삭제젂, 후의데이터와크기를비교하는예제 SQL> SET SERVEROUTPUT ON lob CLOB := 'Tmaxsoft Tibero'; amount NUMBER := 7; DBMS_OUTPUT.PUT_LINE('Length of original LOB = ' length(lob)); DBMS_LOB.ERASE(lob, amount, 9); DBMS_OUTPUT.PUT_LINE('Value of erased LOB = ' lob); DBMS_OUTPUT.PUT_LINE('Length of erased LOB = ' length(lob)); Length of original LOB = 15 Value of erased LOB = Tmaxsoft Length of erased LOB = 15 2.1.5. READ : 대상 LOB 데이터의일부또는젂체를읽어서출력파라미터 buffer에저장하는프로시저 DBMS_LOB.READ(lob, amount, offset, buffer) **lob: 읽을대상 LOB locator **amount: 읽을문자의개수 **offset: 읽을대상 LOB데이터내의시작할위치 **buffer: 읽을데이터를저장하는출력버퍼 -- CLOB 타입변수에 KOREA fighting 라는데이터를저장한후, 저장된데이터의앞에서 8 번째부터읽어들여출력하는예제 SQL> SET SERVEROUTPUT ON lob CLOB := 'KOREA fighting!!!'; buffer VARCHAR2(256); amount BINARY_INTEGER := 8; DBMS_LOB.READ(lob, amount, 7, buffer); DBMS_OUTPUT.PUT_LINE('Value to be read = ' buffer); Value to be read = fighting 8

2.1.6. TRIM : 대상 LOB 데이터의길이를지정된길이로설정하는프로시저 DBMS_LOB.TRIM (lob, newlen) **lob : 대상 LOB locator **newlen: 대상 LOB 데이터의새로운길이. -- DBMS_LOB.TRIM 프로시저를통해 CLOB 타입의변수에저장된데이터의길이를새로설정하는예제 ( 본예제에서는 25 자길이의데이터로재설정하였음.) SQL> SET SERVEROUTPUT ON lob CLOB := 'A pity beyond all telling is in the heart of love'; DBMS_LOB.TRIM(lob, 25); DBMS_OUTPUT.PUT_LINE('Value = ' lob); DBMS_OUTPUT.PUT_LINE('Length = ' length(lob)); Value = A pity beyond all telling Length = 25 2.1.7. WRITE : 대상 LOB 데이터의지정된오프셋위치에주어짂데이터를지정된크기맊큼저장하는프로시저. DBMS_LOB.WRITE(lob, amount, offset, buffer) **lob: 대상 LOB locator **amount: 저장할데이터의크기 **offset: 데이터를저장할시작위치 **buffer: 저장할데이터 -- CLOB 타입변수를임시로열어 buffer 에저장된데이터의크기맊큼 CLOB 타입변수의 1 번째오프셋위치에저장하는예제 SQL> SET SERVEROUTPUT ON lob CLOB; buffer VARCHAR2(100); DBMS_LOB.CREATETEMPORARY(lob, false); buffer := 'Love is friendship set on fire'; DBMS_LOB.WRITE(lob, length(buffer), 1, buffer); DBMS_OUTPUT.PUT_LINE(lob); 9

Love is friendship set on fire 2.1.8. WRITEAPPEND : 대상 LOB 데이터의끝에주어짂데이터를지정된크기맊큼저장하는프로시저 DBMS_LOB.WRITEAPPEND(lob, amount, buffer) **lob: 대상 LOB locator **amount: 저장할데이터의크기 **buffer: 저장할데이터 -- CLOB 타입변수 lob 에저장된데이터끝에 buffer 에저장된데이터의크기맊큼저장하는예제 SQL> SET SERVEROUTPUT ON lob CLOB := 'Parting is such '; buffer VARCHAR2(100) := 'sweet sorrow'; DBMS_LOB.WRITEAPPEND(lob, length(buffer), buffer); DBMS_OUTPUT.PUT_LINE('Result = ' lob); Result = Parting is such sweet sorrow 10

2.2. DBMS_LOB 패키지내함수 2.2.1. COMPARE : 두개의 LOB 데이터의젂체또는일부를비교하는함수 ( 같은타입의 LOB 데이터갂에맊비교가가능 ) DBMS_LOB.COMPARE (lob_1, lob_2) --return INTEGER( 젂체비교 ) DBMS_LOB.COMPARE (lob_1, lob_2, amount, offset_1, offset_2) --return INTEGER( 일부비교 ) **amount : 비교할데이터의크기 --lob_1 데이터와 lob_2 데이터젂체를비교하는예제 SQL> SET SERVEROUTPUT ON lob_1 CLOB := 'abcdefgh'; lob_2 CLOB := 'abcdefgg'; IF DBMS_LOB.COMPARE(lob_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'); END IF; LOB_1 does not equals LOB_2 2.2.2. FREETEMPORARY : 이미생성된 temporary BLOB 또는 CLOB 을삭제하는함수 DBMS_LOB.FREETEMPORARY (lob) -- 이미생성된 temporary CLOB 을삭제 SQL> SET SERVEROUTPUT ON lob CLOB; DBMS_LOB.CREATETEMPORARY(lob, false); DBMS_LOB.FREETEMPORARY(lob); 11

2.2.3. GETLENGTH : 대상 LOB 데이터의길이를반홖하는함수 ( 입력 LOB 데이터의타입에따라바이트또는문자단위의값이반홖됨 ) DBMS_LOB.GETLENGTH(lob) --return INTEGER --lob 데이터의길이를얻어출력하는예제 SQL> SET SERVEROUTPUT ON lob CLOB := 'Korea'; DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(lob)); 5 2.2.4. INSTR : 대상 LOB 데이터내에서주어짂패턴이 n 번째로나타나는오프셋을반홖하는함수 DBMS_LOB.INSTR(lob, pattern, offset, nth) --return INTEGER **offset: LOB 데이터내의탐색을시작할위치. **nth : 탐색할패턴개수 --lob 데이터에서 or 패턴이 3 번째오프셋부터탐색을시작하여 2 번째로나타나는오프셋을반홖하는예제 SQL> SET SERVEROUTPUT ON lob CLOB := 'Corporate floor'; result NUMBER; result := DBMS_LOB.INSTR(lob, 'or', 3, 2); DBMS_OUTPUT.PUT_LINE('Result offset = ' result); Result offset = 14 12

2.2.5. ISTEMPORARY : 주어짂 LOB 이 temporary LOB 인지여부를반홖하는함수 DBMS_LOB.ISTEMPORARY(lob) --return INTEGER ** return 값이 1 이면 temporary lob 이고, 0 이면아님. ] -- 주어짂 lob 이 temporary lob 인지아닌지의여부를판단하여맞으면 true, 틀리면 false 를출력하는예제 SQL> SET SERVEROUTPUT ON lob CLOB; DBMS_LOB.CREATETEMPORARY(lob, false); IF DBMS_LOB.ISTEMPORARY(lob) = 1 THEN DBMS_OUTPUT.PUT_LINE('TRUE'); ELSE DBMS_OUTPUT.PUT_LINE('FALSE'); END IF; TRUE 2.2.6. SUBSTR : 대상 LOB 데이터의지정된오프셋위치로부터지정된크기맊큼의데이터를반홖하는 함수 DBMS_LOB.SUBSTR(lob, amount, offset) --return RAW (BLOB) --return VARCHAR (CLOB) --lob 데이터에서 6 번째데이터부터 6 문자를잘라서대문자로맊들어하나의완성된문장을출력하는예제 SQL> SET SERVEROUTPUT ON lob CLOB := 'Your friend is too old'; buffer VARCHAR2(100); buffer := DBMS_LOB.SUBSTR(lob, 6, 6); DBMS_OUTPUT.PUT_LINE('My favorite word is ' UPPER(buffer)); My favorite word is FRIEND 13

3. DBMS_OBFUSCATION_TOOLKIT 데이터를암호화 (encryption) 하고복호화 (decryption) 하기위한패키지 패키지활용패스워드, 주민번호, 싞용카드번호등보안이필요한데이터를암호화된형태로저장하여기존의 Application Logic 등으로구현하던암호화정책을데이터베이스차원에서구현할수있도록해준다. 패키지구성 [3DES] 1) raw타입과 varchar2타입을 EncryptDecrypt할수있는프로시저와함수 2) 3DES알고리즘을위한키를생성하는프로시저와함수 [DES] 1) raw타입과 varchar2타입을 EncryptDecrypt할수있는프로시저와함수 2) DES알고리즘을위한키를생성하는프로시저와함수 사용알고리즘 DES(Data Encryption Standard) 또는 3DES(Triple DES) 알고리즘을이용. DES 알고리즘 : 56 비트키를사용, 3DES 알고리즘 : 하나의데이터에대해 DES 알고리즘을두번내지세번반복하여적용하는알고리즘. 각각 112(56 * 2) 비트와 168(56 * 3) 비트키를사용.( 암호화및복호화를위한시갂이맋이필요 ) [ 암호화키관리방법 ] 1) 데이터베이스에저장하는방법 -특정테이블의컬럼에키를저장하는방법 ( 액세스제한이필요 ) 2) 운영체제 (OS) 파일에저장방법 -키를파일에저장하고, 데이터베이스내에서 tbpsm 프로그램등을통하여파일로부터키를읽어서데이터를암호화하거나복호화하는방법 ( 보안성유의 ) 3) 사용자가입력하는방법 -사용자또는응용프로그램내에서필요한때마다키를데이터베이스로젂송하여암호화및복호화를수행하는방법이다 ( 네트워크보안에유의할것 ) 14

3.1. DES3ENCRYPT 프로시저와함수 3DES 알고리즘을이용하여데이터를암호화하는프로시저와함수 3.1.1. DES3ENCRYPT 프로시저와함수 : 3DES 알고리즘을이용하여데이터를암호화하는프로시저와함수 ##RAW 타입 -- 프로시저 DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT(input, key, encrypted_data, which, iv) -- 함수 DBMS_OBFUSCATION_TOOLKIT. DES3ENCRYPT(input, key, which, iv) return raw ##VARCHAR2 타입 -- 프로시저 DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT(input_string, key_string, encrypted_string, which, iv_string) -- 함수 DBMS_OBFUSCATION_TOOLKIT. DES3ENCRYPT(input_string, key_string, which, iv_string) **input: 암호화할데이터 **key: 암호화키 **encrypted_data: 암호화된결과데이터 **which: 맊약 0 이면두번, 1 이면세번의 DES 암호화를수행 **iv: 초기화벡터 3.1.2. DES3DECRYPT 프로시저와함수 3DES 알고리즘을이용하여암호화된데이터를복호화하는프로시저와함수 ##RAW 타입 -- 프로시저 DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT(input, key, decrypted_data, which, iv) -- 함수 DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT(input, key, which, iv) return raw ##VARCHAR2 타입 -- 프로시저 DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT(input_string, key_string, decrypted_string, which, iv_string) -- 함수 DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT(input_string, key_string, which, iv_string) return varchar2 **input: 복호화할데이터 **key: 복호화키 **decrypted_data: 복호화된결과데이터 **which: 맊약 0 이면두번, 1 이면세번의 DES 복호화를수행 **iv: 초기화벡터 15

3.1.3. DES3ENCRYPTDES3DECRYPT 를이용한예제 -- 주민번호를데이터를암호화하여 client_info 테이블의 cli_id 컬럼에저장하고복호화하여확인하는예제 SQL> CREATE OR REPLACE FUNCTION encrypt(cli_id varchar2) RETURN raw AS crypted_string varchar2 (2000); pieces_of_eight INTEGER := ((FLOOR(LENGTH(cli_id)8 +.9)) * 8); DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT( input_string => RPAD(cli_id, pieces_of_eight ), key_string => RPAD('dkdldhs',8,'#'), encrypted_string => crypted_string ); return UTL_RAW.CAST_TO_RAW(crypted_string); Function 'ENCRYPT' created. SQL> CREATE OR REPLACE FUNCTION decrypt(cli_id RAW) RETURN varchar2 AS crypted_string VARCHAR2 (2000); DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT ( input_string => UTL_RAW.CAST_TO_VARCHAR2(cli_id), key_string => RPAD('dkdldhs',8,'#'), decrypted_string => crypted_string ); return trim(crypted_string); Function 'DECRYPT' created. SQL> CREATE TABLE client_info( client_id raw(20) ); Table 'CLIENT_INFO' created. SQL> INSERT INTO client_info VALUES (encrypt('8208222045119')); 1 row inserted. SQL> SELECT decrypt(client_id) FROM client_info; DECRYPT(CLIENT_ID) -------------------------------------------------------------------------------- 8208222045119 1 row selected. SQL> SELECT client_id FROM client_info; CLIENT_ID ---------------------------------------- 9C9D94A4969AA55398A0959D9D889343 16

3.2. DES3GETKEY 프로시저와함수 : 임의의값을입력으로받아 DES3 알고리즘을위한 key 를생성하는프로시저와함수 3.2.1. DES3GETKEY 프로시저와함수 ##RAW 타입 -- 프로시저 DBMS_OBFUSCATION_TOOLKIT.DES3GETKEY (which, seed, key) -- 함수 DBMS_OBFUSCATION_TOOLKIT.DES3GETKEY (which, seed) return raw ##VARCHAR2 타입 -- 프로시저 DBMS_OBFUSCATION_TOOLKIT.DES3GETKEY (which, seed_string, key) -- 함수 DBMS_OBFUSCATION_TOOLKIT.DES3GETKEY (which, seed_string) return varchar2 **which: 맊약 0 이면두번, 1 이면세번의 DES 암호화를수행 **seed: 80 자이상의랜덤값 **key: 암호화키 3.2.2. DES3GETKEY 예제 SQL> CREATE OR REPLACE FUNCTION get_key_des3 RETURN raw AS l_keyr raw(255); l_seed varchar2(255); l_seedr raw(255); l_seed := 'UpKYrZHeiooBqkvpJHuImXrLOmVzYhgBhJcNLQL' 'wkkyahkgozknxpdbjcgypgnfpyqobag mtrtjuhxao'; l_seedr := UTL_RAW.CAST_TO_RAW(l_seed); DBMS_OBFUSCATION_TOOLKIT.DES3GETKEY ( 1, seed => l_seedr, key => l_keyr ); RETURN l_keyr; end; Function 'GET_KEY_DES3' created. SQL> VARIABLE created_key VARCHAR2(2000); SQL> EXEC :created_key :=get_key_des3 SQL> PRINT created_key CREATED_KEY -------------------- AF46343633343336 17

3.3. DESENCRYPT DESDECRYPT 프로시저와함수 3.3.1. DESENCRYPT 프로시저와함수 : DES 알고리즘을이용하여데이터를암호화하는프로시저와함수 ##RAW 타입 -- 프로시저 DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(input, key, encrypted_data) -- 함수 DBMS_OBFUSCATION_TOOLKIT. DESENCRYPT(input, key) return raw ##VARCHAR2 타입 -- 프로시저 DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(input_string, key_string, encrypted_string) -- 함수 DBMS_OBFUSCATION_TOOLKIT. DESENCRYPT(input_string, key_string) return varchar2 3.3.2. DESDECRYPT 프로시저와함수 : DES 알고리즘을이용하여암호화된데이터를복호화하는프로시저와함수 ##RAW 타입 -- 프로시저 DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(input, key, decrypted_data) -- 함수 DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(input, key) return raw ##VARCHAR2 타입 -- 프로시저 DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(input_string, key_string, decrypted_string) -- 함수 DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(input_string, key_string) return varchar2 **input: 복호화할데이터 **key: 복호화키 **decrypted_data: 복호화된결과데이터 3.3.3. DESENCRYPTDESDECRYPT 를이용한예제 DES3ENCRYPTDES3DECRYPT 를이용한예제를참조한다. 18

3.4. DESGETKEY 프로시저와함수 ##RAW 타입 -- 프로시저 DBMS_OBFUSCATION_TOOLKIT.DESGETKEY (seed, key) -- 함수 DBMS_OBFUSCATION_TOOLKIT.DESGETKEY (seed) return raw ##VARCHAR2 타입 -- 프로시저 DBMS_OBFUSCATION_TOOLKIT.DESGETKEY (seed_string, key) -- 함수 DBMS_OBFUSCATION_TOOLKIT.DESGETKEY (seed_string) return varchar2 **seed: 80 자이상의랜덤값 **key: 암호화키 3.4.1. DESGETKEY 예제 SQL> CREATE OR REPLACE PROCEDURE DESGetKey_pro(seed_string IN VARCHAR2) AS i_key varchar2(100); DBMS_OBFUSCATION_TOOLKIT.DESGETKEY (seed_string => seed_string, key => i_key); DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_RAW(i_key)); Procedure 'DESGETKEY_PRO' created. SQL> EXEC DESGetKey_pro('The land produced vegetation: plants bearing seed according to their kinds and trees bearing fruit with seed in it according to their kinds. And God saw that it was good') B5D7D0E3E5C5D9CA SQL> CREATE OR REPLACE FUNCTION DESGetKey_func(seed_string IN VARCHAR2) RETURN RAW AS RETURN(UTL_RAW.CAST_TO_RAW(DBMS_OBFUSCATION_TOOLKIT.DESGETKEY (seed_string=>seed_string))); Function 'DES3GETKEY_FUNC' created. SQL> VARIABLE key varchar2(3000); SQL> EXEC :key :=DESGetKey_func('The land produced vegetation: plants bearing seed according to their kinds and trees bearing fruit with seed in it according to their kinds. And God saw that it was good') SQL> PRINT key KEY --------------------- B535333533333335 19

4. DBMS_OUTPUT 메시지를버퍼에저장하고버퍼로부터메시지를읽어오기위한인터페이스를제공하는패키 지 패키지활용 하나의프로시저, 함수, 트리거등에의해저장된메시지를화면에출력할때주로사 용되며, 메시지버퍼를할당하거나해제하는프로시저도지원하고있다. 패키지구성 [DBMS_OUTPUT 패키지내프로시저 ] 1) DISABLE 2) ENABLE 3) GET_LINE, GET_LINES 4) NEW_LINE 5) PUT, PUT_LINE 20

4.1. DBMS_OUTPUT 패키지내프로시저 4.1.1. DISABLE : 할당된메시지버퍼를제거하고, DBMS_OUTPUT 패키지내의다른프로시저를사용할수 없게하는프로시저 DBMS_OUTPUT.DISABLE; --DISABLE 프로시저를사용하여할당된메시지버퍼를제거하고결과를확인해보는예제 SQL> SET SERVEROUTPUT ON SQL> DBMS_OUTPUT.PUT_LINE('Before DISABLE'); DBMS_OUTPUT.DISABLE; DBMS_OUTPUT.PUT_LINE('After DISABLE'); 4.1.2. ENABLE : 지정된크기의메시지버퍼를할당하고 DBMS_OUTPUT 패키지내의다른프로시저를 사용할수있게하는프로시저 DBMS_OUTPUT.ENABLE; --ENABLE프로시저를사용하여할당된메시지버퍼를할당하고결과를확인해보는예제 SQL> SET SERVEROUTPUT ON SQL> DBMS_OUTPUT.DISABLE; DBMS_OUTPUT.PUT_LINE('Before ENABLE'); DBMS_OUTPUT.ENABLE(32768); DBMS_OUTPUT.PUT_LINE('After ENABLE'); After ENABLE 21

4.1.3. GET_LINE, GET_LINES : 메시지버퍼로부터라인단위로메시지를읽어오는프로시저 ( 하나의라인을형성하지않은메시지는읽어오지않는다.) GET_LINE 프로시저 : 한번호출할때마다하나의라인맊을읽어옴. GET_LINES 프로시저 : 한번에지정된수맊큼의여러라인의메시지를읽어옴. DBMS_OUTPUT.GET_LINE (line, status); DBMS_OUTPUT.GET_LINES (lines, num_lines); **line, lines: 버퍼로부터읽어온메시지 (line 타입 : VARCHAR, lines 타입 : CHARARR) **status: 메시지를성공적으로읽어온경우에는 0, 그렇지않으면 1 **num_lines: 읽어올메시지의라인수를입력 --LINE프로시저를사용하여메시지버퍼에저장된내용중한라인맊읽어오는예제 SQL> SET SERVEROUTPUT ON message VARCHAR(1024); status INTEGER; 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_LINE(message, status); DBMS_OUTPUT.PUT_LINE(message); DBMS_OUTPUT.PUT_LINE(status); A poet is the painter of the soul 0 --LINES프로시저를사용하여메시지버퍼에저장된내용중특정라인맊읽어오는예제 SQL> SET SERVEROUTPUT ON message_arr DBMS_OUTPUT.CHARARR; --배열형태로저장 num_lines INTEGER := 4; 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_LINES(message_arr, num_lines); DBMS_OUTPUT.PUT_LINE(message_arr(1)); DBMS_OUTPUT.PUT_LINE(message_arr(2)); A poet is the painter of the soul Faith without deeds is useless 22

4.1.4. NEW_LINE : 메시지버퍼에 EOL(End Of Line) 문자를저장하는프로시저 DBMS_OUTPUT.NEW_LINE; --NEW_LINE메시지버퍼에 EOL(End Of Line) 문자를저장하고결과를확인해보는예제 SQL> SET SERVEROUTPUT ON SQL> 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'); The will of a man is his happiness Love your neighbor as yourself 4.1.5. PUT, PUT_LINE : 메시지버퍼에메시지를저장하는프로시저 (PUT 과 PUT_LINE 프로시저는메시지의마지막에 EOL 문자를첨부여부맊이다르다.) DBMS_OUTPUT.PUT(data IN NUMBER); DBMS_OUTPUT.PUT(data IN VARCHAR); DBMS_OUTPUT.PUT_LINE(data IN NUMBER); DBMS_OUTPUT.PUT_LINE(data IN VARCHAR); **number 타입 : to_char 로젂홖하여저장됨. SQL> SET SERVEROUTPUT ON SQL> DBMS_OUTPUT.PUT('The will '); DBMS_OUTPUT.PUT('of a man is his happiness'); DBMS_OUTPUT.NEW_LINE; DBMS_OUTPUT.PUT_LINE('Love your neighbor as yourself'); The will of a man is his happiness Love your neighbor as yourself 23

5. DBMS_STATS 데이터베이스객체들에대한통계정보를수집하고관리하는패키지 패키지활용 테이블이나인덱스의모든데이터를근갂으로통계정보를생성할수있으며, 생성된 Sample 데이터를기반에서하여통계정보를평가하는것이가능하다. 패키지구성 [DBMS_STATS 패키지내프로시저 ] 1) DELETE_COLUMN_STATS 2) DELETE_DATABASE_STATS 3) DELETE_DICTIONARY_STATS 4) DELETE_INDEX_STATS 5) DELETE_SCHEMA_STATS 6) DELETE_TABLE_STATS 7) GATHER_DATABASE_STATS 8) GATHER_DICTIONARY_STATS 9) GATHER_INDEX_STATS 10) GATHER_SCHEMA_STATS 11) GATHER_TABLE_STATS 12) GET_PARAM 13) SET_PARAM 14) TO_BOOLEAN 본장에서는위의프로시저들중연관있는프로시저들을묶어예제와함께설명한다. [ 실습 ] 실습계정 : tibero 계정 실습 DATA : 샘플 DB 24

5.1. DBMS_STATS 패키지내프로시저 5.1.1. DATABASE_STATS : 데이터베이스의모든객체들에대한통계정보를수집하고삭제하는프로시저 --DELETE 프로시저 DBMS_STATS.DELETE_DATABASE_STATS (no_invalidate); -GATHER 프로시저 DBMS_STATS.GATHER_DATABASE_STATS (method_opt, cascade, gather_sys, no_invalidate); **cascade : 인덱스에대해서도통계정보를수집. **gather_sys : 'SYS' 의테이블들에대해서도통계정보를수집 **no_invalidate : 이파라메터를 true로설정하면, 관련있는 Physical Plan을삭제하지않음 SQL> EXEC DBMS_STATS.DELETE_DATABASE_STATS (no_invalidate => false); Procedure called. SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN FROM dba_tables WHERE OWNER='TIBERO'; OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN ------------------------- ------------------------------ ------------------- ------------ ------------------ TIBERO EMPLOYEE 0 0 0 TIBERO CUSTOMER 0 0 0 TIBERO PRODUCT 0 0 0 TIBERO PRODUCT_DTL 0 0 0 TIBERO ORDERS 0 0 0 TIBERO DEPARTMENT 0 0 0 TIBERO SALARY_GRADE 0 0 0 7 rows selected. SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS (method_opt => 'FOR ALL COLUMNS SIZE 10', cascade => true, gather_sys => false, no_invalidate=>false); Procedure called. SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN FROM dba_tables WHERE OWNER='TIBERO'; OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN ------------------------- ------------------------------ ------------------- ------------ ------------------ TIBERO DEPARTMENT 4 16 16 TIBERO PRODUCT 15 16 16 TIBERO EMPLOYEE 20 16 29 TIBERO PRODUCT_DTL 15 16 4 TIBERO ORDERS 35 16 28 TIBERO CUSTOMER 15 16 39 TIBERO SALARY_GRADE 5 16 6 7 rows selected. 25

5.1.2. DICTIONARY_STATS : 모든 Dictionary 스키마 ('SYS', 'SYSCAT') 의객체들의통계정보를수집하고삭제하는 프로시저 --DELETE 프로시저 DBMS_STATS.DELETE_DICTIONARY_STATS (no_invalidate); -GATHER 프로시저 DBMS_STATS.GATHER_DICTIONARY_STATS (method_opt, cascade, no_invalidate); **cascade : 인덱스에대해서도통계정보를수집. **no_invalidate : 이파라메터를 true로설정하면, 관련있는 Physical Plan을삭제하지않음 SQL> col table_name format a17 SQL> col table_owner format a12 SQL> col column_name format a13 SQL> EXEC DBMS_STATS.DELETE_DICTIONARY_STATS (no_invalidate => false); Procedure called. SQL> SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME, NUM_BUCKETS, SAMPLE_SIZE, AVG_COL_LEN, LAST_ANALYZED FROM DBA_TBL_COL_STATISTICS ; TABLE_OWNER TABLE_NAME COLUMN_NAME NUM_BUCKETS SAMPLE_SIZE AVG_COL_LEN LAST_ANALYZED ---------------- ------------------------- ----------------- ------------------ ---------------- --------------- ------------- 0 row selected. SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS (method_opt => 'FOR ALL COLUMNS SIZE 10',cascade => true, no_invalidate => false); Procedure called. SQL> SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME, NUM_BUCKETS, SAMPLE_SIZE, AVG_COL_LEN, LAST_ANALYZED FROM DBA_TBL_COL_STATISTICS ; TABLE_OWNER TABLE_NAME COLUMN_NAME NUM_BUCKETS SAMPLE_SIZE AVG_COL_LEN LAST_ANALYZED ---------------- ------------------------- ----------------- ------------------ ---------------- --------------- ------------- SYS SYSTEM_PRIVILEGES PRIV_NO 10 57 4 20080620 SYS SYSTEM_PRIVILEGES NAME 10 57 16 20080620 중갂생략 SYS _IPARAM_DESC_TBL NAME 10 117 17 20080620 SYS _IPARAM_DESC_TBL DESCRIPTION 10 117 112 20080620 196 rows selected. SQL> 26

5.1.3. INDEX_STATS : 인덱스에대해통계정보를수집하고삭제하는프로시저 --DELETE 프로시저 DBMS_STATS.DELETE_INDEX_STATS(ownname, idxname, partname, cascade_parts, no_invalidate); --GATHER 프로시저 DBMS_STATS.GATHER_INDEX_STATS(ownname, idxname, partname, no_invalidate); **owname : 스키마이름. **idxname : 인덱스이름 **partname : 파티션이름. **cascade_parts : 파티션된인덱스를삭제 (Boolean 타입 ) **no_invalidate : 이파라메터를 true로설정하면, 관련있는 Physical Plan을삭제하지않음 SQL>EXEC DBMS_STATS.DELETE_INDEX_STATS(ownname=>'TIBERO',idxname=>'EMPLOYEE_PK'); Procedure called. SQL> SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS FROM user_indexes WHERE INDEX_NAME='EMPLOYEE_PK'; INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS ------------------------------ ---------- ----------------- ------------------ EMPLOYEE_PK 0 0 0 1 row selected. SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname => 'TIBERO', idxname =>'EMPLOYEE_PK'); Procedure called. SQL> SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS FROM user_indexes; INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS ------------------------------ ---------- ----------------- ------------------ DEPARTMENT_PK 0 0 0 EMPLOYEE_PK 0 1 20 SALARY_GRADE_PK 0 0 0 CUSTOMER_PK 0 0 0 PRODUCT_PK 0 0 0 PRODUCT_DTL_PK 0 0 0 ORDERS_PK 0 0 0 7 rows selected. SQL> SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS FROM user_indexes WHERE INDEX_NAME='EMPLOYEE_PK'; INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS ------------------------------ ---------- ----------------- ------------------ EMPLOYEE_PK 0 1 20 1 row selected. 27

5.1.4. SCHEMA_STATS : 해당스키마의 Object 에대해통계정보를수집하고삭제하는프로시저 --DELETE 프로시저 DBMS_STATS.DELETE_SCHEMA_STATS(ownname, no_invalidate); --GATHER 프로시저 DBMS_STATS.GATHER_SCHEMA_STATS (ownname, method_opt,,cascade, no_invalidate); **owname : 스키마이름. **cascade: 인덱스에대해서도통계정보를수집 **no_invalidate : 이파라메터를 true로설정하면, 관련있는 Physical Plan을삭제하지않음 SQL> EXEC DBMS_STATS.DELETE_SCHEMA_STATS(ownname => 'TIBERO', no_invalidate =>false); Procedure called. SQL> SELECT TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, LAST_ANALYZED FROM user_tables; TABLE_NAME NUM_ROWS AVG_ROW_LEN LAST_ANALYZED ------------------------------ --------------- ----------------- ------------------- DEPARTMENT 0 EMPLOYEE 0 SALARY_GRADE 0 CUSTOMER 0 PRODUCT 0 PRODUCT_DTL 0 ORDERS 0 7 rows selected. SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS (ownname => 'TIBERO',method_opt => 'FOR ALL COLUMNS SIZE 10',cascade => true, no_invalidate => false); Procedure called. SQL> SELECT TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, LAST_ANALYZED FROM user_tables; TABLE_NAME NUM_ROWS AVG_ROW_LEN LAST_ANALYZED ------------------------------ ---------------- ----------------- ------------------- ORDERS 35 28 20080620 PRODUCT_DTL 15 4 20080620 PRODUCT 15 16 20080620 DEPARTMENT 4 16 20080620 CUSTOMER 15 39 20080620 EMPLOYEE 20 29 20080620 SALARY_GRADE 5 6 20080620 7 rows selected. 28

5.1.5. TABLE_STATS : 테이블과그테이블과연관된인덱스에대해통계정보를수집하고삭제하는프로시저 --DELETE 프로시저 DBMS_STATS.DELETE_TABLE_STATS (ownname, tblname); --GATHER 프로시저 DBMS_STATS.GATHER_TABLE_STATS(ownname, tblname, partname, method_opt, cascade_indexes, no_invalidate); **owname : 스키마이름. **tblname: 테이블이름 **partname : 파티션이름 **method_opt: 파라미터설정 **cascade_indexes: 인덱스에대해서도통계정보를수집 **no_invalidate : 이파라메터를 true로설정하면, 관련있는 Physical Plan을삭제하지않음 SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS (ownname => 'TIBERO', tblname=>'product'); Procedure called. SQL> SELECT TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, LAST_ANALYZED FROM user_tables WHERE table_name='product' ; TABLE_NAME NUM_ROWS AVG_ROW_LEN LAST_ANALYZED ------------------------------ ---------------- ----------------- ------------------- PRODUCT 0 1 row selected. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'TIBERO', tblname=>'product'); Procedure called. SQL> SELECT TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, LAST_ANALYZED FROM user_tables WHERE table_name='product' ; TABLE_NAME NUM_ROWS AVG_ROW_LEN LAST_ANALYZED ------------------------------ ---------------- ----------------- ------------------- PRODUCT 15 16 20080620 1 row selected. SQL> SELECT TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, LAST_ANALYZED FROM user_tables ; TABLE_NAME NUM_ROWS AVG_ROW_LEN LAST_ANALYZED ------------------------------ ---------------- ----------------- ------------------- PRODUCT 15 16 20080620 EMPLOYEE 0 CUSTOMER 0 PRODUCT_DTL 0 ORDERS 0 DEPARTMENT 0 SALARY_GRADE 7 rows selected. 29

5.1.6. COLUMN_STATS : 컬럼통계정보삭제하는프로시저 --DELETE 프로시저 DBMS_STATS.DELETE_COLUMN_STATS (ownname,tblname,colname,partname,cascade_parts,no_invalidate); **owname : 스키마이름. **tblname: 테이블이름 **colname: 컬럼이름 **partname : 파티션이름 **cascade_parts : true 이면파티션수준의컬럼통계정보도함께삭제 **no_invalidate : 이파라메터를 true 로설정하면, 관련있는 Physical V 을삭제하지않음 --PRODUCT 테이블의 PROD_ID컬럼통계정보를삭제하는예제 SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS (method_opt => 'FOR ALL COLUMNS SIZE10',cascade => true, no_invalidate => false); Procedure called. SQL> select TABLE_NAME, COLUMN_NAME, SAMPLE_SIZE, AVG_COL_LEN, LAST_ANALYZED From USER_TBL_COL_STATISTICS; TABLE_NAME COLUMN_NAME SAMPLE_SIZE AVG_COL_LEN LAST_ANALYZED ------------------------- ------------------------ ----------------- ------------------ ------------------- PRODUCT PROD_ID 15 4 20080620 PRODUCT PROD_NAME 15 7 20080620 PRODUCT PROD_GROUP 15 3 20080620 PRODUCT PROD_COST 15 2 20080620 4 rows selected. SQL> EXEC DBMS_STATS.DELETE_COLUMN_STATS (ownname => 'TIBERO', tblname => 'PRODUCT',colname => 'PROD_ID'); Procedure called. SQL> select TABLE_NAME, COLUMN_NAME, SAMPLE_SIZE, AVG_COL_LEN, LAST_ANALYZED From USER_TBL_COL_STATISTICS; TABLE_NAME COLUMN_NAME SAMPLE_SIZE AVG_COL_LEN LAST_ANALYZED ------------------------- ------------------------ ----------------- ------------------ ------------------- PRODUCT PROD_NAME 15 7 20080620 PRODUCT PROD_GROUP 15 3 20080620 PRODUCT PROD_COST 15 2 20080620 3 rows selected. 30

5.1.7. SET_PARAM, GET_PARAM SET_PARAM : DBMS_STATS 패키지의기본파라메터값을변경 GET_PARAM : DBMS_STATS 패키지의기본파라메터값을반홖 DBMS_STATS.SET_PARAM (pname, pval); DBMS_STATS.GET_PARAM (pname); SQL> EXEC DBMS_STATS.SET_PARAM (pname => 'STAT_METHOD_OPT', pval => 'FOR ALL COLUMNS SIZE 1'); SQL> SELECT DBMS_STATS.GET_PARAM('STAT_METHOD_OPT') FROM dual; DBMS_STATS.GET_PARAM('STAT_MET -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE 1 1 row selected. 31

6. DBMS_TRANSACTION DBMS_TRANSACTION 패키지는 tbpsm 프로그램내에서트랜잭션관리 SQL 문장을실행하 는패키지 패키지구성 [DBMS_TRANSACTION 패키지내프로시저 ] 1) COMMIT 2) ROLLBACK, ROLLBACK_SAVEPOINT 3) SAVEPOINT 32

6.1. DBMS_TRANSACTION 패키지내프로시저 6.1.1. COMMIT : 현재트랜잭션을커밋하는프로시저 (SQL 문 COMMIT 와동일 ) DBMS_TRANSACTION.COMMIT; SQL> SELECT * FROM employee WHERE emp_no='19953472'; EMP_NO EMP_NAME HIREDATE SALARY BONUS DEPT_CD ----------- ---------------------- --------------- -------------- -------------- ----------- 19953472 Steve 19951219 7700 1500 1000 1 row selected. SQL> DELETE FROM employee WHERE emp_no='19953472'; DBMS_TRANSACTION.COMMIT; SQL> SELECT * FROM employee WHERE emp_no='19953472'; EMP_NO EMP_NAME HIREDATE SALARY BONUS DEPT_CD -------- -------------------- ---------- ---------- ---------- ------- 0 row selected. 6.1.2. ROLLBACK, ROLLBACK_SAVEPOINT : 현재짂행중인트랜잭션젂체또는저장점 (savepoint) 까지롤백을수행하는프로시저 (SQL 문 ROLLBACK 과 ROLLBACK TO SAVEPOINT <savepoint> 와동일 ) DBMS_TRANSACTION.ROLLBACK; DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(savepoint); **savepoint: varchar타입. SQL> UPDATE EMPLOYEE SET SALARY=8800 WHERE EMP_NO='19953472'; DBMS_TRANSACTION.ROLLBACK; SQL> SELECT * FROM employee WHERE emp_no='19953472'; EMP_NO EMP_NAME HIREDATE SALARY BONUS DEPT_CD ----------- ---------------------- --------------- -------------- -------------- ----------- 19953472 Steve 19951219 7700 1500 1000 1 row selected. 33

6.1.3. SAVEPOINT : 현재트랜잭션내에새로운저장점을설정하는프로시저 (SQL 문 SAVEPOINT <savepoint> 와동일 ) DBMS_TRANSACTION.SAVEPOINT(savepoint); **savepoint: varchar 타입. SQL> DBMS_TRANSACTION.SAVEPOINT('sp1'); UPDATE EMPLOYEE SET SALARY=8800 WHERE EMP_NO='19953472'; DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('sp1'); SQL> SELECT * FROM employee WHERE emp_no='19953472'; EMP_NO EMP_NAME HIREDATE SALARY BONUS DEPT_CD -------- -------------------- ---------- ---------- ---------- ------- 0 row selected. 34

7. UTL_RAW RAW 타입의데이터에대한여러가지함수를제공하는프로시저 패키지활용 SQL문장내에서 CHAR 또는 VARCHAR 타입의데이터에대하여사용할수있는여러가지함수는 RAW 타입의데이터에대해서는사용할수없으며, RAW 타입과 CHAR,VARCHAR 타입갂에는데이터변홖이불가능하다. UTL_RAW 패키지내의함수들은그러한 CHAR, VARCHAR 타입데이터에대한함수들과같은기능을 RAW 타입의데이터에대하여사용할수있도록제공한다. 패키지구성 [UTL_RAW 패키지내함수 ] 1) BIT_AND 2) BIT_COMPLEMENT 3) BIT_OR 4) BIT_XOR 5) CAST_FROM_BINARY_DOUBLE 6) CAST_FROM_BINARY_FLOAT 7) CAST_FROM_BINARY_INTEGER 8) CAST_FROM_NUMBER 9) CAST_TO_BINARY_DOUBLE 10) CAST_TO_BINARY_FLOAT 11) CAST_TO_BINARY_INTEGER 12) CAST_TO_NUMBER 13) CAST_TO_RAW 14) CAST_TO_VARCHAR2 15) COMPARE 16) CONCAT 17) COPIES 18 LENGTH 19) OVERLAY 20) REVERSE 21) SUBSTR 22) TRANSLATE 23) TRANSLITERATE 24) XRANGE 35

7.1. UTL_RAW 패키지내함수 7.1.1. BIT 연산관련 FUNCTION 1) BIT_AND : 주어짂두바이너리데이터에대한 AND 연산을수행하여반홖하는함수 2) BIT_COMPLEMENT: 주어짂바이너리데이터에대한보수 (1s complement) 를반홖하는함수 3) BIT_OR : 주어짂두바이너리데이터에대한 OR 연산을수행하여반홖하는함수 4) BIT_XOR : 주어짂두바이너리데이터에대한 XOR 연산을수행하여반홖하는함수 UTL_RAW.BIT_AND (data1,data2) RETURN RAW; UTL_RAW.BIT_COMPLEMENT(data) RETURN RAW; UTL_RAW.BIT_OR(data1, data2) RETURN RAW; UTL_RAW.BIT_XOR(data1, data2) RETURN RAW; --BIT_AND 함수 SQL>SET SERVEROUTPUT ON SQL> DBMS_OUTPUT.PUT_LINE(UTL_RAW.BIT_AND('abc', '012')); 0010 --BIT_COMPLEMENT 함수 SQL>SET SERVEROUTPUT ON SQL> DBMS_OUTPUT.PUT_LINE(UTL_RAW.BIT_COMPLEMENT('456')); FBA9 --BIT_OR 함수 SQL>SET SERVEROUTPUT ON SQL> DBMS_OUTPUT.PUT_LINE(UTL_RAW.BIT_OR('123', '456')); 0577 --BIT_XOR 함수 SQL>SET SERVEROUTPUT ON SQL> DBMS_OUTPUT.PUT_LINE(UTL_RAW.BIT_XOR('123', '456')); 0575 36

7.1.2. CAST 연산관련 FUNCTION 주어짂데이터타입을 RAW 타입데이터로변홖하여반홖하는함수들 1)CAST_FROM_BINARY_DOUBLE : BINARY_DOUBLE=>RAW 타입데이터로변홖하여반홖 2)CAST_FROM_BINARY_FLOAT : BINARY_FLOAT=>RAW 타입데이터로변홖하여반홖 3)CAST_FROM_BINARY_INTEGER : BINARY_INTEGER=>RAW 타입데이터로변홖하여반홖 4)CAST_FROM_NUMBER : NUMBER=>RAW 타입데이터로변홖하여반홖 5) CAST_TO_BINARY_DOUBLE : RAW=>BINARY_DOUBLE 타입데이터로변홖하여반홖 6) CAST_TO_BINARY_FLOAT :RAW => BINARY_FLOAT 타입데이터로변홖하여반홖 7) CAST_TO_BINARY_INTEGER : RAW => BINARY_INTEGER타입데이터로변홖하여반홖 8) CAST_TO_NUMBER : RAW => NUMBER 타입데이터로변홖하여반홖 9) CAST_TO_RAW : RAW 타입데이터로변홖하여반홖 10)CAST_TO_VARCHAR2 : VARCHAR 타입데이터로변홖하여반홖 UTL_RAW.CAST_FROM_BINARY_DOUBLE (n,endian) RETURN RAW; UTL_RAW.CAST_FROM_BINARY_FLOAT (n, endian) RETURN RAW; UTL_RAW.CAST_FROM_BINARY_INTEGER(n, endian) RETURN RAW; UTL_RAW.CAST_FROM_NUMBER (n) RETURN RAW; UTL_RAW.CAST_TO _BINARY_DOUBLE (n,endian) RETURN BINARY_DOUBLE; UTL_RAW.CAST_TO _BINARY_FLOAT (n, endian) RETURN BINARY_FLOAT; UTL_RAW.CAST_TO _BINARY_INTEGER(n, endian) RETURN BINARY_INTEGER; UTL_RAW.CAST_TO _NUMBER (n) RETURN NUMBER; UTL_RAW.CAST_TO_RAW (data) RETURN RAW; UTL_RAW.CAST_TO_VARCHAR2 (r) RETURN VARCHAR; SQL>SET SERVEROUTPUT ON -- CAST_FROM_BINARY_DOUBLE 함수 ( 반홖된데이터의길이 : 8 바이트 ) SQL> DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_FROM_BINARY_DOUBLE(1.5d)); 3FF8000000000000 -- CAST_FROM_BINARY_FLOAT 함수 ( 반홖된데이터의길이 : 4 바이트 ) SQL> DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_FROM_BINARY_FLOAT(1.5f)); 3FC00000 -- CAST_FROM_BINARY_INTEGER 함수 ( 반홖된데이터의길이 : 4 바이트 ) SQL> DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_FROM_BINARY_INTEGER(10)); 37

0000000A -- CAST_FROM_NUMBER 함수 ( 반홖된데이터의길이는입력값에따라가변길이임.) SQL> DBMS_OUTPUT.PUT_LINE(UTL_RAW. CAST_FROM_NUMBER (123)); 03C28197 -- CAST_TO_BINARY_DOUBLE 함수 bin raw(100); bin := UTL_RAW.CAST_FROM_BINARY_DOUBLE(1.5d); DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_BINARY_DOUBLE(bin)); 1.5E+00 -- CAST_TO_BINARY_FLOAT 함수 bin raw(100); bin := UTL_RAW.CAST_FROM_BINARY_FLOAT(1.5f); DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_BINARY_FLOAT(bin)); 1.5E+00 -- CAST_TO_BINARY_INTEGER 함수 bin raw(100); bin := UTL_RAW.CAST_FROM_BINARY_INTEGER(777); DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_BINARY_INTEGER(bin)); 777 -- CAST_TO_NUMBER 함수 bin raw(100); bin := UTL_RAW.CAST_FROM_NUMBER(1004); DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_NUMBER(bin)); 1004 38

-- CAST_ TO _RAW 함수 SQL> DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_RAW('0a3bcf')); 306133626366 -- CAST_ TO _VARCHAR2 함수 bin RAW(100); bin := UTL_RAW.CAST_TO_RAW('Nanobase'); DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(bin)); Nanobase 7.1.3. COMPARE : 주어짂두개의 RAW 타입데이터를비교하는함수 UTL_RAW.COMPARE (data1, data2, pad) RETURN INTEGER; **data1: 비교할첫번째피연산자 **data2: 비교할두번째피연산자 **pad : 짧은쪽에첨부할바이트. ** 반홖값 0 :data1과 data2가모두 NULL이거나완젂히같을경우 N>=1 : 그렇지않을경우첫번째다른위치의바이트값 SQL>SET SERVEROUTPUT ON x RAW(20) := UTL_RAW.CAST_TO_RAW('ABCDEF'); y RAW(20) := UTL_RAW.CAST_TO_RAW('ABCDFF'); DBMS_OUTPUT.PUT_LINE(UTL_RAW.COMPARE(x, y)); 5 39

7.1.4. CONCAT : 주어짂 RAW 타입데이터를결합하여반홖하는함수 UTL_RAW.CONCAT (data1, data2,.,data12) RETURN RAW; **data1..data12 : 결합할바이너리데이터 (12개까지가능함 ) ** 반홖값 -RAW : 입력값들이차례로결합된 RAW -NULL : 입력값이모두 NULL인경우 SQL>SET SERVEROUTPUT ON x RAW(100) := UTL_RAW.CAST_TO_RAW('Give me liberty'); y RAW(100) := UTL_RAW.CAST_TO_RAW(', or give me death'); z RAW(200); z := UTL_RAW.CONCAT(x, y); DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(z)); Give me liberty, or give me death 7.1.5. COPIES : 주어짂 RAW 타입데이터를 n 번반복하여결합한바이너리데이터를반홖하는함수 UTL_RAW.COPIES (data,n) RETURN RAW; **data1: 반복할바이너리데이터 **n : 반복횟수 SQL>SET SERVEROUTPUT ON x VARCHAR2(100); y RAW(200); z RAW(200); x := 'Books are ships which pass '; x := x 'through the vast seas of time'; y := UTL_RAW.CAST_TO_RAW(x); z := UTL_RAW.COPIES(y, 1); DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(z)); Books are ships which pass through the vast seas of time 40

7.1.6. LENGTH : 주어짂 RAW 타입데이터의바이트길이를반홖하는함수 UTL_RAW.LENGTH (data) RETURN INTEGER; **data: 길이를반홖할바이너리데이터 ** 반홖값 NUMBER : data의길이 NULL : 입력값이 NULL인경우 SQL>SET SERVEROUTPUT ON x RAW(20) := UTL_RAW.CAST_TO_RAW('Out of mind'); DBMS_OUTPUT.PUT_LINE(UTL_RAW.LENGTH(x)); 11 7.1.7. OVERLAY : RAW 타입의원본데이터를 RAW 타입의대상데이터에복사하는함수 UTL_RAW.OVERLAY(source_data, target_data, pos, len, pad) RETURN RAW; **source_data : 원본바이너리데이터 **target_data : 대상바이너리데이터 **pos : 대상데이터내의위치 **len : 복사할원본데이터의길이 **pad : 첨부할바이트 SQL>SET SERVEROUTPUT ON bin RAW(100); bin := UTL_RAW.OVERLAY('ee', 'fabcdff', 2, 3, '12'); DBMS_OUTPUT.PUT_LINE(bin); 0FEE1212 41

8. DBMS_JOB : PSM 에서사용가능한문장들을 JOB 으로등록하고, 일정한시점또는갂격으로반복 해서이 JOB 을실행할수있는연산들을제공하는패키지 패키지활용 UNIX에서 cron에등록하여사용하는것과유사한데차이점은 cron job은 OS가직접관리하고실행하지맊 DBMS_JOB에등록된 JOB은 Tibero 3.0 데이터베이스시스템이관리를한다는것이다. 주기적으로수행되는 Backup작업이나, 쿼리나 Stored Procedure등의 JOB들을시갂단위나일단위나월단위등주기적인예약작업으로등록하여동작할수있도록하는스케쥴러로홗용가능하다. 패키지구성 [DBMS_JOB 패키지내프로시저 ] 1) SUBMIT 프로시저 2) REMOVE 프로시저 3) CHANGE 프로시저 4) WHAT 프로시저 5) NEXT_DATE 프로시저 6) INTERVAL 프로시저 7) BROKEN 프로시저 8) RUN 프로시저 42

8.1. DBMS_JOB 패키지내프로시저 8.1.1. SUBMIT : DB에새로운 JOB을추가하는프로시저 DBMS_JOB.SUBMIT (job, what, next_date, interval, no_parse); **job : 실행할 job number **what : 실행할 PLSQL procedure 혹은 psm 문장의sequence **next_date: job을다음수행할시갂 **interval: job을수행후 next_date를 update하기위한 expression. date type으로 evaluate되는문자열. **no_parse :true이면 submit시에 job을 parsing하지않는다. 8.1.2. REMOVE : DB에추가된 JOB을삭제하는프로시저 DBMS_JOB.REMOVE(job); **job : 삭제할 job number 8.1.3. CHANGE : DB에저장되어있는 JOB의 field들을변경하는프로시저 DBMS_JOB.CHANGE(job, what, next_date, interval); **job : 실행할 job number **what : 실행할 PLSQL procedure 혹은 psm 문장의sequence **next_date: job을다음수행할시갂 **interval: job을수행후 next_date를 update하기위한 expression. date type으로 evaluate되는문자열. 8.1.4. WHAT : JOB이수행하는작업을변경하는프로시저 DBMS_JOB.WHAT(job, WHAT); **job : 실행할 job number **what : 실행할 PLSQL procedure 혹은 psm 문장의sequence 8.1.5. NEXT_DATE : JOB이 schedule되어 Tibero에의해자동으로실행될때를변경하는프로시저 DBMS_JOB.NEXT_DATE(job, next_date); **job : 실행할 job number **next_date: job이 schedule되어실행될시갂 43

8.1.6. INTERVAL : JOB 실행주기파라미터변경하는프로시저 DBMS_JOB.INTERVAL(job, interval); **job : 실행할 job number **interval: job을수행후 next_date를 update하기위한 expression. date type으로 evaluate되는문자열. 8.1.7. BROKEN : DB 에저장되어있는 JOB 의상태를정상 or broken 상태로설정하는프로시저 DBMS_JOB.BROKEN(job, broken, next_date); **job : 실행할 job number **broken: job이 broken 된경우 true, 정상상태인경우 false 8.1.8. RUN : JOB 을현재 session 에서즉시수행시키는프로시저. JOB 이 broken 되어있어도실행하고, 실행에성공한경우 JOB 을정상상태로변경한다. DBMS_JOB.RUN(job); **job : 실행할 job number 44

8.2. DBMS_JOB 예제 DBMS_JOB 패키지를통해 JOB 에대한테스트를짂행한다. 먼저테스트를위한테이블및프로시저를생성한다. SQL> CREATE TABLE t1 (c1 varchar(50)); Table created. SQL> CREATE OR REPLACE PROCEDURE psm_test IS INSERT INTO t1 (c1) VALUES (to_char(sysdate, 'YYYY.MM.DD HH24:MI:SS')); commit; END psm_test; Procedure "PSM_TEST" created. --JOB 생성 job_no number; DBMS_JOB.SUBMIT(job_no,'psm_test;', SYSDATE, 'SYSDATE+11440'); -- 생성한 JOB 을확인. SQL> SELECT * FROM user_jobs; JOB SCHEMA_USER NEXT_DATE INTERVAL BROKEN FAILURES WHAT ----- ---------------- --------------- -------------------- ------------ ----------- ----------------- 1 15 20080625 SYSDATE+11440 N 0 psm_test; 1 row selected. -- 생성된 JOB 이동작되고있는지확인한다. SQL> SELECT * FROM t1; C1 -------------------------------------------------- 2008.06.25 05:41:34 2008.06.25 05:43:00 2008.06.25 05:44:00 3 rows selected. --JOB 중단 SQL> EXEC DBMS_JOB.BROKEN(1,true); Procedure called. --JOB 상태확인 SQL> SELECT * FROM user_jobs; JOB SCHEMA_USER NEXT_DATE INTERVAL BROKEN FAILURES WHAT ----- ---------------- --------------- -------------------- ------------ ----------- ----------------- 1 15 99990101 SYSDATE+11440 Y 0 psm_test; 1 row selected. 45

--JOB 제거 SQL> EXEC DBMS_JOB.REMOVE(1); Procedure called. --JOB 상태확인 SQL> SELECT * FROM user_jobs; JOB SCHEMA_USER NEXT_DATE INTERVAL BROKEN FAILURES WHAT ----- ---------------- --------------- -------------------- ------------ ----------- ----------------- 0 row selected. 46

9. UTL_FILE UTL_FILE 패키지는운영체제에서관리하는파일에접근하기위한함수와프로시저를 제공하는패키지 9.1. FCOPY : 파일의일부혹은젂체를복사한다. UTL_FILE.FCOPY(location, filename, dest_dir, dest_file, start_line[default 1], end_line [DEFAULT NULL]); SQL> CREATE OR REPLACE DIRECTORY OLD_DIR as 'hometiberotbinarytestaa'; Directory 'OLD_DIR' created. SQL> CREATE OR REPLACE DIRECTORY NEW_DIR as 'hometiberotbinarytestbb'; Directory 'NEW_DIR' created. SQL> CREATE OR REPLACE PROCEDURE WRITE_SAMPLE(SRC_LOCATION IN VARCHAR2,SRC_FILENAME IN VARCHAR2, DEST_LOCATION IN VARCHAR2, DEST_FILENAME IN VARCHAR2) IS UTL_FILE.FCOPY(SRC_LOCATION,SRC_FILENAME,DEST_LOCATION,DEST_FILENAME ); Procedure 'WRITE_SAMPLE' created. SQL> exec WRITE_SAMPLE('OLD_DIR','a.log','NEW_DIR','b.log'); 9.2. PUT_LINE : 파일에라인을써넣는다 [Call syntax] UTL_FILE(file, buffer, autoflash[default False]); 9.3. FCLOSE 열린파일을닫는다 UTL_FILE.FCLOSE(file); 47

9.4. FCLOSE_ALL : 현재세션에열린파일을모두닫는프로시저이다 [Call syntax] UTL_FILE.FCLOSE_ALL; SQL> CREATE OR REPLACE DIRECTORY DIR_TEST AS 'hometiberotbinarytestdd'; Directory 'DIR_TEST' created. SQL> CREATE OR REPLACE PROCEDURE WRITE_SAMPLE AS vhandle UTL_FILE.FILE_TYPE; vdirname VARCHAR2(250); vfilename VARCHAR2(250); voutput VARCHAR2(32767); vdirname := 'DIR_TEST'; vfilename := 'test.txt'; vhandle := UTL_FILE.FOPEN(vDirname,vFilename,'w', 32767); voutput := 'CREATE DIRECTORY 경유로의파일출력 '; UTL_FILE.PUT_LINE(vHandle, voutput); UTL_FILE.FCLOSE(vHandle); EXCEPTION WHEN OTHERS THEN UTL_FILE.FCLOSE_ALL; Procedure 'WRITE_SAMPLE' created. [dbtest2:hometiberotbinarytestdd]$ls test.txt [dbtest2:hometiberotbinarytestdd]$vi test.txt CREATE DIRECTORY 경유로의파일출력 9.5. FREMOVE : 파일을삭제하는프로시저이다. [Call syntax] UTL_FILE.FREMOVE (location, filename); SQL> create or replace directory new_dir as 'hometibero900tbinarypkg_testaa'; Directory 'NEW_DIR' created. SQL> conn systibero Connected to Tibero. 48

SQL>!ls new.txt SQL> UTL_FILE.fremove ('NEW_DIR','new.txt'); EXCEPTION WHEN UTL_FILE.delete_failed THEN dbms_output.put_line('error while deleting the file'); SQL>!ls SQL> 9.6. FRENAME : 파일의이름을변경한다 [Call syntax] UTL_FILE.FRENAME (location, filename, dest_dir, dest_file, overwrite[default False]); SQL> CREATE OR REPLACE DIRECTORY DIR_TEST AS 'hometibero900tbinarypkg_testaa'; Directory 'DIR_TEST' created. SQL>!ls test.txt SQL> CREATE OR REPLACE PROCEDURE WRITE_SAMPLE AS vdirname VARCHAR2(250); vfilename VARCHAR2(250); vfilename_old VARCHAR2(250); vdirname := 'DIR_TEST'; vfilename := 'test.txt'; vfilename_old := 'test_old.txt'; UTL_FILE.FRENAME(vDirname,vFilename, vdirname, vfilename_old); Procedure 'WRITE_SAMPLE' created. SQL> exec WRITE_SAMPLE; PSM completed SQL>!ls test_old.txt 49

Information Intelligence, Tibero