DBMS_LOB 관련 함수 (SUBSTR,INSTR,GETLENGTH)

알아두면 편한 DBMS_LOB 관련 함수

* DBMS_LOB.SUBSTR(CLOB_컬럼, 가져올 길이, OFFSET) <= SELECT
* DBMS_LOB.INSTR(CLOB_컬럼, '검색할 단어', OFFSET, 몇 번째 위치한 단어) <= WHERE
* DBMS_LOC.GETLENGTH(CLOB_컬럼) <= 해당컬럼의 전체길이를 반환

[Ex. 01]
SELECT DBMS_LOB.GETLENGTH(CONTENTS) AS CON_LENGTH <= 해당 CLOB_컬럼의 전체 길이를 반환
FROM BOARD
WHERE DOMAIN_CODE = 10012
AND DBMS_LOB.INSTR(CONTENTS, 'Cookie', 1, 1) > 0 <=CLOB_컬럼에서 'Cookie'라는 단어가 들어가 있는 조건

/* 여기서 CONTENTS컬럼은 CLOB타입이다. */

[결과]
CON_LENGTH
--------------
896 --------- ⓐ
715 --------- ⓑ

- 추가 설명 -
위의 결과를 보면 2개의 row가 검색됐다. 즉 'Cookie'라는 단어가 들어가 있는 CONTENTS는 총 2개의 행이다.
만약 ⓐ결과의 컬럼에 'Cookie'라는 단어는 1개
ⓑ결과의 컬럼에 'Cookie'라는 단어는 3개 있다고 가정할 때
DBMS_LOB.INSTR(CONTENTS, 'Cookie', 1, 1) > 0
는 위와 같은 결과가 나오지만
DBMS_LOB.INSTR(CONTENTS, 'Cookie', 1, 2) > 0 이라고 하면 ⓑ 행의 715라는 결과만 보여주게 된다.
그 이유는 ⓐ에는 'Cookie'라는 단어가 총 1개밖에 없기 때문에 DBMS_LOB.INSTR()의 조건에 맞지 않는다.
'몇 번째 위치한 단어'의 조건 중 ⓐ에는 2번째에 위치한 'Cookie'가 없기 때문이다.(1개만 존재하지 때문에)

[Ex. 02]
SELECT DBMS_LOB.SUBSTR(CONTENTS, DBMS_LOB.GETLENGTH(CONTENTS), 1) AS LOB_CONTENTS <=CLOB_컬럼의 내용을 보여줌
FROM BOARD
WHERE DOMAIN_CODE = 10012
AND DBMS_LOB.INSTR(CONTENTS, 'Cookie', 1, 1) > 0

[결과]
LOB_CONTENTS
--------------------------------
aafagrek dasgew fgwfafd sdfe....
any learners, who are diffic....

- 추가 설명 -
CONTENTS컬럼의 처음길이(1)부터 전체길이(GETLENGTH(CONTENTS))까지를 보여주게 된다.(사용자 지정가능)
여기서 OFFSET을 5로 설정한다면
결과는
grek dasgew ....
learners, wh....
이런식의 결과가 나오게 된다.

위의 방식되로 하면..CLOB타입의 결과를 그냥 가져올 수 있기 때문에 게시판 같은 경우 저렇게 코딩해서 가져오면 되지 않느냐? 라고 할수도 있겠지만 조건절이 없을 경우(LIST처럼) CLOB가 너무커서 많은 데이터를 가져오면 DB BUFFER가 ERROR나게 된다.

 

출처 : http://snoopy81.tistory.com/134