Describe LOB의사용 here {{{ ===================== PL/SQL에서 LOB의 사용 ===================== 쩝...절라 빡센 내용이 될꺼 같습니다...양도 만만치 않구여... 이 단원에서는 오라클의 고급기능을 알아보겠습니다.. 그중에서 LOB에 관해서 자세히 알아보고...로케이터, DBMS_LOB패키지의 사용법을 알아보겠습니다.. ========================================= 대용량 데이타를 처리하기 위한 데이타 타입 ========================================= BLOB : 구조화 되지 않은 이진 데이타를 저장, 최대 4기가 --> 외부 CLOB : 구조화 되지 않은 문자 데이타를 저당, 최대 4기가 --> 내부 NCLOB : 저장된 문자 데이타에 대해 다양한 국가 언어 지원기능 사용, 최대 4기가 --> 내부 BFILE : 구조화 되지 않은 데이타를 데이타베이스 외부에 저장, 최대 4기가 --> 내부 ======================== 대용량 오브젝트제한 사항 ======================== - 클러스터링을 할수 없다 - group by, order by, select distinct, join문이나 집합함수에 사용될 수 없다 - 분산불가능 - varray 에 사용될 수 없다 ========================== LOB와 LONG 데이타형의 비교 ========================== LOB LONG --------------------------------- ----------------------------- 하나의 행에 여러 LOB 저장가능 한행에 하나씩만 저장 사용자정의 데이타형을 가질수 있음 사용자정의 데이타형을 가질수 없음 컬럼에 LOB 로케이터만 저장 전체값이 컬럼에 저장 (LOB 크기가 3964바이트이면 컬럼에 저장) LOB 컬럼에 접근할때는 로케이터가 반환 전체값이 반환 4GB까지 저장 2GB까지 저장 랜덤 오프셋 엑세스가능 처음부터 원하는 위치까지 액세스 지역과 분산된 환경에 모두 복제가능 복제 불가능 ================================== LONG형 데이타타입을 LOB로 변환하기 ================================== TO_LOB함수를 사용하면 됩니다...(버전에 따라 틀릴수 있는데...이 함수의 지원여부는 메뉴얼을 참고..) <문법> TO_LOB (LONG 값) 꽤 쓸모가 있으리라 생각합니다... ================== 외부 파일 사용하기 ================== 외부 파일을 사용한다는 말은 오라클이 데이타베이스내에 저장된 데이타에 접근하는 것이 아니라 운영체제의 파일을 접근한다는 것입니다.. 이러한 기능을 하는 것이 BFILE 대용량 객체입니다... 외부파일을 엑세스 하려면 먼저 디렉토리 객체를 생성해야합니다... <----------------------- 반드시 디렉토리 객체 생성한다..!!!!!!!! 다음은 문법입니다.. CREATE [OR REPLACE] DIRECTORY 디렉토리이름 AS 경로 - 디렉토리 객체의 이름은 최대 30자까지 - 파일이름은 2000자까지 먼저 실습을 위해 윈도우에서 F:\Books라는 디렉토리를 생성하고 그림파일 9개를 집어넣었습니다.. 그런후 디렉토리 오브젝트를 생성해보겠습니다.. SQL> connect yasi/yasi 연결되었습니다. SQL> create or replace directory books_dir as 'F:\Books'; 디렉토리가 생성되었습니다. ================= BFILE 의 제한사항 ================= - 트랜잭션을 지원하지 않는다. - 파일은 읽기 전용으로 열린다. - too many files open 오류를 조심해야 한다.. 해결은 파라미터 파일의 SESSION_MAX_OPEN_FILES = 20의 값을 조정한다.. 운영체제의 요구사항도 고려하고, 파일을 열었을때는 반드시 닫아주어야 한다. 그렇지 않으면 사용하지 않았을때도 계속 열린파일로 기록되어서, 실제로 제한된 파일수 이하로 액세스하고 잇는 데도 에러를 발생하게 된다. 오류가 발생할때는 모든 파일을 닫는수 밖에 없다.. ===================================== BFILE과 함께 DBMS_LOB 패키지 사용하기 ===================================== 일단 DBMS_LOB 패키지를 사용하기 위해서는 다음과 같은 2가지 스크립트를 실행합니다..817 버전에서는 없지여.. SQL> @ORACLE_HOME/rdbms/admin/dbmslob.sql SQL> @ORACLE_HOME/rdbms/admin/prvtlob.plb 오라클은 외부 LOB에만 사용되는 함수를 따로 제공합니다.. 다음은 그 함수요약입니다.. 함수or프로시저 용가능데이타형 설명 ------------ ------------------ -------------------------------------------------------------------------------- BFILENAME BFILE PL/SQL 블록이나 테이블에 파일의 위치에 대한 포인터(로케이터)를 생성 COMPARE ALL LOB 두 LOB를 전체 또는 일부를 비교 FILECLOSE BFILE BFILE 로케이터와 연결된 파일을 닫는다. FILECOLOSEALL BFILE BFILE을 모두 닫는다. FILEEXISTS BFILE 로케이터가 파일이 있어야 한다고 가리키는 위치에 파일이 존재하는지를 검사한다. FILEGETNAME BFILE BFILE의 디렉토리 오브젝트와 경로를 반환한다. FILEISOPEN BFILE 파일이 이미 열려 있는지를 검사한다. FILEOPEN BFILE 실제로 파일을 연다 GETLENGTH ALL LOB LOB의 실제 길이를 반환한다. INSTR ALL LOB LOB에서 지정된 문자 스트링과 일치하는 패턴을 찾는다. READ ALL LOB 지정된 양만큼 LOB를 버퍼로 읽어 들인다. BSUBSTR ALL LOB 파라미터에 의해서 지정된 전체 또는 일부 LOB를 반환한다. 각각의 함수는 모두 유용하게 사용됩니다.... 하나의 BFILE에 대한 작업을 하려면 위의 함수를 50%넘게 사용해야 할 것입니다... 중요한 BFILE에 대한 작업은 다음과 같습니다.. - 일단 테이블에 로케이터가 저장되기 위해서는 BFILENAME함수를 이용해야 합니다. - 항상 파일에 어떤 연산을 수행할때, 그 파일이 존재하는지 FILEEXISTS함수를 사용해서 확인해야 합니다.. - BFILE을 사용하기 전에 FILEISOPEN함수를 사용해서 그 파일이 이미 열려 있는지 확인해야 합니다. - 외부 파일에 엑세스하려면 먼저 FILEOPEN프로시저를 사용해서 그 파일을 열어야 합니다.. - READ프로시저를 사용해서 파일의 일부 또는 전체를 지역 메모리로 읽어들일 수 있습니다.. - 파일을 다 읽었으면 반드시 FILECLOSE프로시저를 이용해서 파일을 닫습니다.. - 모든 BFILE에 대한 처리를 끝낸뒤 FILECLOSEALL 프로시저를 통해서 세션을 끝냅니다.. 직접 예를 통해서 해봅시다.. 다음의 예제는 BFILE을 열고, BFILE을 닫고, FILEEXISTS, FILEISOPEN을 사용하여 오류를 검사하고, GETFILENAME을 사용해서 디렉토리 객체와 파일경로를 검색하는 예제입니다.. DECLARE /* This Anonymous PL/SQL block will demonstrate how to open a BFILE, close the BFILE, and do some error checking with FILEEXISTS, FILEISOPEN, and retrieve the Directory Object and Path with GETFILENAME */ v_BOOKFILE BFILE; -- BFILE to access v_DIRNAME VARCHAR2(30); -- Holds Directory Object for FILEGETNAME v_LOCATION VARCHAR2(2000); -- Holds filename for FILEGETNAME v_FILEISOPEN INTEGER; -- Holds status to check if the file is open v_FILEEXISTS INTEGER; -- Holds status if the file actually exists BEGIN v_BOOKFILE := BFILENAME('BOOKS_DIR','BOOKS1.JPG'); -- Create Locator v_FILEISOPEN := DBMS_LOB.FILEISOPEN(v_BOOKFILE);-- Check if file open v_FILEEXISTS := DBMS_LOB.FILEEXISTS(v_BOOKFILE); IF v_FILEEXISTS = 1 THEN DBMS_OUTPUT.PUT_LINE('The file exists'); ELSE DBMS_OUTPUT.PUT_LINE('The file cannot be found'); END IF; IF v_FILEISOPEN = 1 THEN --Determine actions if file is opened DBMS_OUTPUT.PUT_LINE('The file is open'); ELSE DBMS_OUTPUT.PUT_LINE('Opening the file'); DBMS_LOB.FILEOPEN(v_BOOKFILE); END IF; DBMS_LOB.FILEGETNAME(v_BOOKFILE,v_DIRNAME,v_LOCATION); DBMS_OUTPUT.PUT_LINE('The Directory Object is: ' || v_DIRNAME || ' The File Name is: ' || v_LOCATION); DBMS_LOB.FILECLOSE(v_BOOKFILE); -- Close the BFILE END; / 실제로 이미지를 보려면.....클라이언트 프로그램을 써야합니다...비베나..파워빌덩등을요... 다음 예는 파일을 비교하고 파일의 길이를 구하는 예제입니다. DECLARE /* The purpose of this anonymous PL/SQl block is to compare the contents of three files completely. The size of the files is determined by the GETLENGTH function */ v_FILE1 BFILE; v_FILE2 BFILE; v_FILE3 BFILE; v_GETLENGTH1 INTEGER; -- Hold length of the file v_GETLENGTH2 INTEGER; -- Hold length of the file v_GETLENGTH3 INTEGER; -- Hold length of the file v_COMPARELENGTH INTEGER; -- Holds smallest of two values v_COMPARERESULT INTEGER; -- Hold result of comparing files BEGIN -- Create three locators for each of the files to compare v_FILE1 := BFILENAME('BOOKS_DIR','BOOKS1.JPG'); v_FILE2 := BFILENAME('BOOKS_DIR','BOOKS2.JPG'); v_FILE3 := BFILENAME('BOOKS_DIR','BOOKS5.JPG'); -- Open the files for access DBMS_LOB.FILEOPEN(v_FILE1); DBMS_LOB.FILEOPEN(v_FILE2); DBMS_LOB.FILEOPEN(v_FILE3); v_GETLENGTH1 := DBMS_LOB.GETLENGTH(v_FILE1); v_GETLENGTH2 := DBMS_LOB.GETLENGTH(v_FILE2); v_GETLENGTH3 := DBMS_LOB.GETLENGTH(v_FILE3); -- Compare 1st and 2nd File IF v_GETLENGTH1 < v_GETLENGTH2 THEN v_COMPARELENGTH := v_GETLENGTH1; ELSE v_COMPARELENGTH := v_GETLENGTH2; END IF; v_COMPARERESULT := DBMS_LOB.COMPARE(v_FILE1,v_FILE2, v_COMPARELENGTH,1,1); IF v_COMPARERESULT = 0 THEN DBMS_OUTPUT.PUT_LINE('Both Files Are Identical'); ELSE DBMS_OUTPUT.PUT_LINE('Both Files Are Different'); END IF; -- Compare 1st and 3rd file IF v_GETLENGTH1 < v_GETLENGTH3 THEN v_COMPARELENGTH := v_GETLENGTH1; ELSE v_COMPARELENGTH := v_GETLENGTH3; END IF; v_COMPARERESULT := DBMS_LOB.COMPARE(v_FILE1,v_FILE3, v_COMPARELENGTH,1,1); IF v_COMPARERESULT = 0 THEN DBMS_OUTPUT.PUT_LINE('Both Files Are Identical'); ELSE DBMS_OUTPUT.PUT_LINE('Both Files Are Different'); END IF; DBMS_LOB.FILECLOSEALL; END; / ======== 로케이터 ======== 로케이터는 실제 데이타가 저장된 위치를 가리키는 것으로 대용량 객체 컬럼에 저장됩니다.. 오라클 로케이터를 사용할 때는 트랜잭션 수준에서 어떤 일이 발생하는지를 이해하는 것이 중요합니다.. LOB를 한 행에서 다음 행으로 복사할 때는 새로운 로케이터가 생성된 다음, 원본 행으로부터 모든 데이타가 복사되고 저장됩니다.. 이것은 만일 핸 행을 삭제하려고 하는데 그 전체 내용을 새로운 행으로 복사하지 않았다면, LOB의 모든 데이타를 잃어버리게 되기 때문에 중요합니다...내부 LOB를 삭제할 때는 LOB의 로케이터와 내용이 둘다 삭제됩니다.. 만일 외부 BFILE을 삭제하고 있다면 로케이터만 삭제될 뿐 파일은 그대로 남습니다.. 내부 LOB를 테이블에 추가할 때는 LOB 컬럼에 데이타를 할당하거나 EMPTY_BLOB나 EMPTY_CLOB함수를 사용하여 로케이터를 생성해야 합니다..BFILE을 테이블에 추가할때는 BFILENAME을 사용하여 컬럼에 로케이터만 할당하면 됩니다.. 마지막 문제는 내부 LOB에만 적용되는 것입니다. 다른 사용자가 LOB에 접근하지 못하도록 처리할때는 LOB를 잠그는 것도 좋은 생각입니다.. ======================================= 내부 LOB와 함께 DBMS_LOB패키지 사용하기 ======================================= 함수or프로시저 용가능데이타형 설명 ------------ ------------------ -------------------------------------------------------------------------------- COMPARE ALL LOB 두 LOB를 전체 또는 일부를 비교 GETLENGTH ALL LOB LOB의 실제 길이를 반환한다. INSTR ALL LOB LOB에서 지정된 문자 스트링과 일치하는 패턴을 찾는다. READ ALL LOB 지정된 양만큼 LOB를 버퍼로 읽어 들인다. BSUBSTR ALL LOB 파라미터에 의해서 지정된 전체 또는 일부 LOB를 반환한다. APPEND 내부 LOB 한 LOB를 다른 LOB에 추가한다 COPY 내부 LOB 한 행의 LOB를 다른 행으로 복사한다. EMPTY_BLOB BLOB BLOB 컬럼에 로케이터를 생성 EMPTY_CLOB CLOB CLOB 컬럼에 로케이터를 생성 EREASE 내부 LOB 내부 LOB 전체 또는 일부를 삭제 TRIM 내부 LOB 지정된 길이만큼 LOB를 자른다. WRITE 내부 LOB LOB에 데이타를 기록한다. ==== 예제 ==== SQL> create table lobs( 2 lob_index integer, 3 clob_locator clob); 테이블이 생성되었습니다. SQL> INSERT INTO LOBS VALUES(1,'Teach Yourself Oracle8i in 21 Days'); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO LOBS VALUES(2,'Oracle Data Warehousing Unleashed'); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO LOBS VALUES(3,'Teach Yourself Database Development With Oracle in 21 Days'); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO LOBS VALUES(4,'Oracle Unleashed 2E'); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO LOBS VALUES(5,EMPTY_CLOB()); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO LOBS VALUES(6,EMPTY_CLOB()); 1 개의 행이 만들어졌습니다. SQL> select * from lobs; LOB_INDEX CLOB_LOCATOR ---------- ------------------------------------------------------------- 1 Teach Yourself Oracle8i in 21 Days 2 Oracle Data Warehousing Unleashed 3 Teach Yourself Database Development With Oracle in 21 Days 4 Oracle Unleashed 2E 5 6 6 개의 행이 선택되었습니다. ======================================================== COPY 프로시저를 이용한 데이타가 없는 두 행을 복사하는 예 ======================================================== DECLARE Source_Lob CLOB; Dest_Lob CLOB; Copy_Amount INTEGER; BEGIN SELECT CLOB_LOCATOR into Dest_LOB FROM LOBS WHERE LOB_INDEX = 5 FOR UPDATE; -- FOR UPDATE locks the ROW SELECT CLOB_LOCATOR into Source_LOB FROM LOBS WHERE LOB_INDEX = 1; Copy_Amount := DBMS_LOB.GETLENGTH(Source_Lob); DBMS_LOB.COPY(Dest_LOB, Source_LOB,Copy_Amount); COMMIT; -- Start second copy process SELECT CLOB_LOCATOR into Dest_LOB FROM LOBS WHERE LOB_INDEX = 6 FOR UPDATE; SELECT CLOB_LOCATOR into Source_LOB FROM LOBS WHERE LOB_INDEX = 2; Copy_Amount := DBMS_LOB.GETLENGTH(Source_Lob); DBMS_LOB.COPY(Dest_LOB, Source_LOB,Copy_Amount); COMMIT; END; / SQL> SELECT * FROM LOBS; LOB_INDEX CLOB_LOCATOR ---------- ----------------------------------------------------------------------- 1 Teach Yourself Oracle8i in 21 Days 2 Oracle Data Warehousing Unleashed 3 Teach Yourself Database Development With Oracle in 21 Days 4 Oracle Unleashed 2E 5 Teach Yourself Oracle8i in 21 Days 6 Oracle Data Warehousing Unleashed 6 개의 행이 선택되었습니다. 이 뒷부분은 책에 나온 예제입니다... 예제를 보고 따라해보시면.....각 함수와 프로시저를 어떻게 적용할 것인가 알수 있을 것입니다.. Listing 14.7 Appending and Writing to LOBs DECLARE /* This appends the contents of Row 1 to the contents of Row 5. In addition, it writes text at the end of the values in Row 6. */ Source_Lob CLOB; Dest_Lob CLOB; Write_Amount INTEGER := 10; Writing_Position INTEGER ; Buffer VARCHAR2(10) := 'Added Text'; BEGIN -- Append from Row 1 to Row 5 SELECT CLOB_LOCATOR into Dest_LOB FROM LOBS WHERE LOB_INDEX = 5 FOR UPDATE; -- Locks Row for Update SELECT CLOB_LOCATOR into Source_LOB FROM LOBS WHERE LOB_INDEX = 1; DBMS_LOB.APPEND(Dest_LOB, Source_LOB); COMMIT; -- Write to a LOB SELECT CLOB_LOCATOR into Source_LOB FROM LOBS WHERE LOB_INDEX = 6 FOR UPDATE; -- Locks Row for Update Writing_Position := DBMS_LOB.GETLENGTH(Source_Lob) + 1; DBMS_LOB.WRITE(Source_LOB,Write_Amount,Writing_Position,Buffer); COMMIT; END; Listing 14.8 Extracting and Matching Data Inside CLOBs DECLARE /* This PL/SQL block finds patterns in a CLOB. It also extracts part of the data from a CLOB with SUBSTR */ Source_Lob CLOB; v_Pattern VARCHAR2(6) := 'Oracle'; v_Starting_Location INTEGER := 1; v_Nth_Occurrence INTEGER := 1; v_Position INTEGER ; v_Extract_Amount INTEGER; v_Buffer VARCHAR2(100) ; BEGIN -- Search for 1st Occurrence of Oracle in Row 5 SELECT CLOB_LOCATOR into Source_LOB FROM LOBS WHERE LOB_INDEX = 5; v_Position := DBMS_LOB.INSTR(Source_LOB,v_Pattern, v_Starting_Location,v_Nth_Occurrence); DBMS_OUTPUT.PUT_LINE('The first occurrence starts at position: ' || v_Position); -- Search for 2nd Occurrence of Oracle in Row 5 v_Nth_Occurrence := 2; SELECT CLOB_LOCATOR into Source_LOB FROM LOBS WHERE LOB_INDEX = 5; v_Position := DBMS_LOB.INSTR(Source_LOB,v_Pattern, v_Starting_Location,v_Nth_Occurrence); DBMS_OUTPUT.PUT_LINE('The second occurrence starts at position: ' || v_Position); -- Extract part of the data from a CLOB SELECT CLOB_LOCATOR into Source_LOB FROM LOBS WHERE LOB_INDEX = 6; v_Buffer := DBMS_LOB.SUBSTR(Source_LOB,11,v_Starting_Location); DBMS_OUTPUT.PUT_LINE('The substring extracted is: ' || v_Buffer); END; Listing 14.9 Reducing Data in CLOBs DECLARE /* This erases the data in Row 6, and trims the data in row 5 to one occurrence of the book title. */ Source_Lob CLOB; Erase_Amount INTEGER; Trim_Amount INTEGER; BEGIN -- Erase the data completely in Row 6 SELECT CLOB_LOCATOR into Source_LOB FROM LOBS WHERE LOB_INDEX = 6 FOR UPDATE; -- Locks Row for Update Erase_Amount :=DBMS_LOB.GETLENGTH(Source_LOB); DBMS_LOB.ERASE(Source_LOB,Erase_Amount,1); --Reduce Data in Row 5 to one instance of Book Title SELECT CLOB_LOCATOR into Source_LOB FROM LOBS WHERE LOB_INDEX = 5 FOR UPDATE; TRIM_AMOUNT := DBMS_LOB.GETLENGTH(Source_LOB) / 2; DBMS_LOB.TRIM(Source_LOB, TRIM_AMOUNT); COMMIT; END; }}}