[Oracle] DBMS_LOB 데이터 조회 방법

 

오라클의 LOB 데이터 조회

1. 첫번쨰 
SELECT DBMS_LOB.INSTR(content, '영문', 1, 1) FROM tb_board_clob
WHERE DBMS_LOB.INSTR(content, '영문', 1, 1) <> 0;

2. 두번째
SELECT DBMS_LOB.SUBSTR(CONTENTS_TXT, DBMS_LOB.GETLENGTH(CONTENTS_TXT), 1) FROM LOCALBBS_ALL WHERE BBS_TYPE = 'T'

이렇게 하면 content 의 내용중에서 '영문' 이라는 문자를 찾아
그곳의 byte 또는 위치값을 Return 합니다.

content 필드에서 '영문' 이라는 문자열을 1 번째 offset으로 부터
시작하여 1 번째 찾아라, 뭐 그런 뜻입니다.

찾지 못하면 0 을 Return 합니다. 그러니 0 과 같지 않으면 찾았다고
보면 되는 거죠.

그럼.. 좋은 정보였으면 합니다.

----------------------------------------------------------------

DBMS_LOB

BLOB 또는 CLOB 타입의 컬럼 데이터에 대한 여러 가지 연산을 제공하는 패키지

패키지 활용

DBMS_LOB 패키지 내의 프로시저와 함수를 이용하여 대용량 객체인 LOB타입의 전체 또는 일부에 대하여 읽기, 쓰기 등의 작업을 수행할 수 있다.

패키지 구성

[DBMS_LOB 패키지 내 프로시저]

• APPEND
• COPY
• CREATETEMPORARY
• ERASE
• READ
• TRIM
• WRITE
• WRITEAPPEND

[DBMS_LOB 패키지 내 함수]

• COMPARE
• FREETEMPORARY
• GETLENGTH
• INSTR
• ISTEMPORARY
• SUBSTR.

DBMS_LOB 패키지 내 프로시져

**BLOB타입은 출력결과를 바로 확인할 수 없으므로, CLOB타입을 중심으로 예제를 작성한다.

APPEND

[Call Syntax]

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
SQL> DECLARE
dest_lob CLOB := 'All''s fair in ';
src_lob CLOB := 'love and war';
BEGIN
DBMS_LOB.APPEND(dest_lob, src_lob);
DBMS_OUTPUT.PUT_LINE('Result = ' || dest_lob);
END;
/
Result = All's fair in love and war
PSM completed.

COPY

: 원본 LOB 데이터 전체 또는 일부를 대상 LOB 데이터에 복사하는 프로시저

[Call Syntax]

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
SQL> DECLARE
dest_lob CLOB := 'I love ';
src_lob CLOB := 'you him';
BEGIN
DBMS_LOB.COPY(dest_lob, src_lob, length(src_lob)-3,
length(dest_lob)+1, 1);
DBMS_OUTPUT.PUT_LINE('Result = ' || dest_lob);
END;
/
Result = I love you
PSM completed.

CREATETEMPORARY

: Temporary CLOB 또는 BLOB을 생성하는 프로시저

[Call Syntax]

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
SQL> DECLARE
lob_1 CLOB;
lob_2 CLOB := 'tibero';
BEGIN
DBMS_LOB.CREATETEMPORARY(lob_1, false);
DBMS_LOB.APPEND(lob_1, lob_2);
DBMS_OUTPUT.PUT_LINE(lob_1);
END;
/
tibero
PSM completed.

ERASE

: LOB 데이터의 일부 또는 전체를 삭제하는 프로시저.

(삭제된 영역에는 0(BLOB 데이터) 또는 공백(CLOB 데이터)으로 채워진다.)

[Call Syntax]

DBMS_LOB.ERASE(lob, amount, offset)

** lob : 대상 LOB locator
**amount: 삭제할 문자의 개수
**offset: 삭제할 시작위치

[테스트예제]

-- CLOB타입 변수에 ‘Tmaxsoft Tibero’라는 데이터를 저장한 후, 앞에서 9번째부터 7글자를 삭제
하며 삭제 전, 후의 데이터와 크기를 비교하는 예제
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lob CLOB := 'Tmaxsoft Tibero';
amount NUMBER := 7;
BEGIN
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));
END;
/
Length of original LOB = 15
Value of erased LOB = Tmaxsoft
Length of erased LOB = 15
PSM completed.

READ

: 대상 LOB 데이터의 일부 또는 전체를 읽어서 출력 파라미터 buffer에 저장하는 프로시저

[Call Syntax]

DBMS_LOB.READ(lob, amount, offset, buffer)

**lob: 읽을 대상 LOB locator
**amount: 읽을 문자의 개수
**offset: 읽을 대상 LOB데이터 내의 시작할 위치
**buffer: 읽을 데이터를 저장하는 출력 버퍼

[테스트예제]

-- CLOB 타입 변수에 ‘KOREA fighting’라는 데이터를 저장한 후, 저장된 데이터의 앞에서 8번째부터
읽어 들여 출력하는 예제
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lob CLOB := 'KOREA fighting!!!';
buffer VARCHAR2(256);
amount BINARY_INTEGER := 8;
BEGIN
DBMS_LOB.READ(lob, amount, 7, buffer);
DBMS_OUTPUT.PUT_LINE('Value to be read = ' || buffer);
END;
/
Value to be read = fighting
PSM completed.

TRIM

: 대상 LOB 데이터의 길이를 지정된 길이로 설정하는 프로시저

[Call Syntax]

-- CLOB 타입 변수에 ‘KOREA fighting’라는 데이터를 저장한 후, 저장된 데이터의 앞에서 8번째부터
DBMS_LOB.TRIM (lob, newlen)

**lob : 대상 LOB locator
**newlen: 대상 LOB 데이터의 새로운 길이.

TRIM

: 대상 LOB 데이터의 길이를 지정된 길이로 설정하는 프로시저

[테스트예제]

-- DBMS_LOB.TRIM 프로시저를 통해 CLOB 타입의 변수에 저장된 데이터의 길이를 새로 설정하
는 예제( 본 예제에서는 25자 길이의 데이터로 재설정하였음.)
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lob CLOB := 'A pity beyond all telling is in the heart of love';
BEGIN
DBMS_LOB.TRIM(lob, 25);
DBMS_OUTPUT.PUT_LINE('Value = ' || lob);
DBMS_OUTPUT.PUT_LINE('Length = ' || length(lob));
END;
/
Value = A pity beyond all telling
Length = 25
PSM completed.

WRITE

: 대상 LOB 데이터의 지정된 오프셋 위치에 주어진 데이터를 지정된 크기만큼 저장하는 프로시저.

[Call Syntax]

DBMS_LOB.WRITE(lob, amount, offset, buffer)

**lob: 대상 LOB locator
**amount: 저장할 데이터의 크기
**offset: 데이터를 저장할 시작위치
**buffer: 저장할 데이터.

[테스트예제]

-- CLOB타입 변수를 임시로 열어 buffer에 저장된 데이터의 크기만큼 CLOB타입 변수의 1번째 오
프셋 위치에 저장하는 예제
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lob CLOB;
buffer VARCHAR2(100);
BEGIN
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);
END;
/
Love is friendship set on fire
PSM completed.

WRITEAPPEND

: 대상 LOB 데이터의 끝에 주어진 데이터를 지정된 크기만큼 저장하는 프로시저

[Call Syntax]

DBMS_LOB.WRITEAPPEND(lob, amount, buffer)

**lob: 대상 LOB locator
**amount: 저장할 데이터의 크기
**buffer: 저장할 데이터

[테스트예제]

-- CLOB타입 변수 lob에 저장된 데이터 끝에 buffer에 저장된 데이터의 크기만큼 저장하는 예제
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lob CLOB := 'Parting is such ';
buffer VARCHAR2(100) := 'sweet sorrow';
BEGIN
DBMS_LOB.WRITEAPPEND(lob, length(buffer), buffer);
DBMS_OUTPUT.PUT_LINE('Result = ' || lob);
END;
/
Result = Parting is such sweet sorrow
PSM completed.

DBMS_LOB 패키지 내 함수

COMPARE

: 두 개의 LOB 데이터의 전체 또는 일부를 비교하는 함수
(같은 타입의 LOB 데이터 간에만 비교가 가능)

[Call Syntax]

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
SQL> DECLARE
lob_1 CLOB := 'abcdefgh';
lob_2 CLOB := 'abcdefgg';
BEGIN
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;
END;
/
LOB_1 does not equals LOB_2
PSM completed.

FREETEMPORARY

: 이미 생성된 temporary BLOB 또는 CLOB을 삭제하는 함수

[Call Syntax]

DBMS_LOB.FREETEMPORARY (lob)

[테스트예제]

--이미 생성된 temporary CLOB을 삭제
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lob CLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(lob, false);
DBMS_LOB.FREETEMPORARY(lob);
END;
/
PSM completed.

GETLENGTH

: 대상 LOB 데이터의 길이를 반환하는 함수
(입력 LOB 데이터의 타입에 따라 바이트 또는 문자 단위의 값이 반환됨)

[Call Syntax]

DBMS_LOB.GETLENGTH(lob) --return INTEGER

[테스트예제]

--lob 데이터의 길이를 얻어 출력하는 예제
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lob CLOB := 'Korea';
BEGIN
DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(lob));
END;
/
5
PSM completed.

INSTR

: 대상 LOB 데이터 내에서 주어진 패턴이 n 번째로 나타나는 오프셋을 반환하는 함수

[Call Syntax]

DBMS_LOB.INSTR(lob, pattern, offset, nth) --return INTEGER

**offset: LOB 데이터 내의 탐색을 시작할 위치.
**nth : 탐색할 패턴 개수

[테스트예제]

--lob 데이터에서 ‘or’패턴이 3번째 오프셋부터 탐색을 시작하여 2번째로 나타나는 오프셋을 반환
하는 예제
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lob CLOB := 'Corporate floor';
result NUMBER;
BEGIN
result := DBMS_LOB.INSTR(lob, 'or', 3, 2);
DBMS_OUTPUT.PUT_LINE('Result offset = ' || result);
END;
/
Result offset = 14
PSM completed.

ISTEMPORARY

: 주어진 LOB이 temporary LOB인지 여부를 반환하는 함수

[Call Syntax]

DBMS_LOB.ISTEMPORARY(lob) --return INTEGER

** return 값이 1이면 temporary lob이고, 0이면 아님.

[테스트예제]

--주어진 lob이 temporary lob인지 아닌지의 여부를 판단하여 맞으면 ’true’ ,틀리면 ‘false’를 출력
하는 예제
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lob CLOB;
BEGIN
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;
END;
/
TRUE
PSM completed.

SUBSTR

: 대상 LOB 데이터의 지정된 오프셋 위치로부터 지정된 크기만큼의 데이터를 반환하는 함수

[Call Syntax]

DBMS_LOB.SUBSTR(lob, amount, offset) --return RAW (BLOB)
--return VARCHAR (CLOB)

[테스트예제]

--lob 데이터에서 6번째 데이터부터 6문자를 잘라서 대문자로 만들어 하나의 완성된 문장을 출력
하는 예제
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lob CLOB := 'Your friend is too old';
buffer VARCHAR2(100);
BEGIN
buffer := DBMS_LOB.SUBSTR(lob, 6, 6);
DBMS_OUTPUT.PUT_LINE('My favorite word is ' || UPPER(buffer));
END;
/
My favorite word is FRIEND
PSM completed.