제품 : PL/SQL
작성날짜 : 2001-02-08
CLOB COLUMN 또는 CLOB VARIABLE를 REPLACE, SEARCH하는 방법
=========================================================
1. Table 및 Data 생성
SAL> CREATE TABLE test (key NUMBER,c CLOB);
SAL> INSERT INTO test VALUES (1,'aaa#~#bbb#~#ccc');
SAL> INSERT INTO test VALUES (2,'aaa#~#bbb#~#ccc');
2. 사용된 DBMS_LOB package routines :
- ISOPEN()
- CREATETEMPORARY()
- INSTR()
- GETLENGTH()
- COPY()
- TRIM()
3. 아래처럼 Clob를 handling하기 위한 PL/SQL Code 생성
CREATE OR REPLACE PROCEDURE Srch_N_Repl_Clob
(dest_lob IN OUT CLOB, search_str VARCHAR2,replace_str VARCHAR2)
AS
temp_clob CLOB;
end_offset INTEGER := 1;
start_offset INTEGER := 1;
occurence NUMBER := 1;
replace_str_len NUMBER := LENGTH(replace_str);
temp_clob_len NUMBER := 0;
dest_lob_len NUMBER := 0;
BEGIN
IF DBMS_LOB.ISOPEN(dest_lob) = 0 THEN
NULL;
END IF;
DBMS_LOB.CREATETEMPORARY(temp_clob,TRUE,DBMS_LOB.SESSION);
LOOP
end_offset := DBMS_LOB.INSTR(dest_lob,search_str,1,occurence);
IF end_offset = 0 THEN
temp_clob_len := DBMS_LOB.GETLENGTH(temp_clob);
dest_lob_len := DBMS_LOB.GETLENGTH(dest_lob) - start_offset + 1;
IF dest_lob_len > 0 THEN
DBMS_LOB.COPY(temp_clob,dest_lob,dest_lob_len,temp_clob_len+1,start_offset);
END IF;
EXIT;
END IF;
temp_clob_len := DBMS_LOB.GETLENGTH(temp_clob);
IF (end_offset - start_offset) > 0 THEN
DBMS_LOB.COPY(temp_clob,dest_lob,(end_offset - start_offset),temp_clob_len+1,start_offset);
END IF;
start_offset := end_offset + LENGTH(search_str);
occurence := occurence + 1;
IF replace_str IS NOT NULL THEN
DBMS_LOB.WRITEAPPEND(temp_clob,replace_str_len,replace_str);
END IF;
END LOOP;
IF LENGTH(search_str) > LENGTH(replace_str) THEN
DBMS_LOB.TRIM(dest_lob,DBMS_LOB.GETLENGTH(temp_clob));
END IF;
DBMS_LOB.COPY(dest_lob,temp_clob,DBMS_LOB.GETLENGTH(temp_clob),1,1);
END ;
/
4. Clob의 column 및 variable이 변경된 상태를 실행해보면
SQL>DECLARE
2 dest_lob CLOB;
3 BEGIN
4 SELECT c INTO dest_lob FROM test WHERE key = 1 FOR UPDATE;
5 Srch_N_Repl_Clob(dest_lob,'#~#',' Replaced ');
6 END;
7 /
SQL>SELECT * FROM test;
KEY C
--------------------------------------------------
1 aaa Replaced bbb Replaced ccc
2 aaa#~#bbb#~#ccc
