ORACLE Database / WAS (iAS) / SQL 자료실 - 포기하지 않으면 실패하지 않는다!
Vote Reply Modify Delete Forward Prev Next List

  Author   : 조성환 [ ladmin ] Vote: 1913, Hit: 5995, Lines: 374, Category: Etc.
Oracle LOB의 종류 및 응용 SQL

출처 : http://blog.naver.com/kkhigh5?Redirect=Log&logNo=30009122730



큰 데이터를 저장할 때 LOB(Large Object)라고 하는 비구조화 데이터(바이너리 데이터나 대량의 텍스트 데이터)를 사용합니다.





■ Oracle에 격납할 수 있는 LOB의 종류


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




Oracle 데이타베이스에 격납할 수 있는 LOB에는, 이하의 4 종류가 존재합니다.

LOB종류 설명
BFILE 데이터를 데이타베이스내에 격납하지 않고, OS상의 파일 시스템에 기록합니다
BLOB 음성·화상등의 바이너리 데이터를 데이타베이스 내부에 격납합니다
CLOB 대량의 문자열 데이터를 데이타베이스 내부에 격납합니다(싱글 바이트·캐릭터 또는 멀티 바이트·캐릭터를 포함한다)
NCLOB 대량의 문자열 데이터를 데이타베이스 내부에 격납합니다(Unicode 캐릭터를 포함한다)
표 1 Oracle 데이타베이스에 격납할 수 있는 LOB의 종류




이번회에서는 BLOB에 대해 SQL문으로의 액세스 방법등을 소개합니다. BLOB는 대량의 바이너리 데이터를 격납할 수 있는 데이터형입니다. Pro*C나 Java등의 어플리케이션·프로그램으로부터 바이너리 데이터를 읽어들여 격납하는 방법도 있습니다만, 여기에서는 PL/SQL로 실행하는 방법을 간단한 예로 해설합니다.





■BLOB 데이터를 격납하는 샘플



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





본 연재에서 사용되는 예제의 샘플소스는 위에서 다운 로드 받으실수 있습니다..


내용물은
create_ddl.sql
 샘플용의 오브젝트 작성 스크립트
atmarkit_7_1.sql
 BLOB 데이터의 격납 샘플  
atmarkit_7_2.sql
  BLOB 데이터의 출력 샘플
이러한 샘플 코드는 Oracle Database 10.1.0.4에서 동작 확인을 하였습니다.

1 set serveroutput on
2 declare
3         v_blob_locater  blob;
4         v_handle        bfile := bfilename('LOB_DATA_PATH', 'maguro1.jpg');
5
6         v_amount        pls_integer;
7         v_doffset       pls_integer := 1;
8         v_soffset       pls_integer := 1;
9
10 begin
11         dbms_lob.fileopen(
12                 v_handle,
13                 dbms_lob.file_readonly
14         );
15
16         v_amount := dbms_lob.getlength(v_handle);
17
18         insert into test_blob_data (
19                 id,
20                 data
21         ) values (
22                 seq_test_blob_data.nextval,
23                 empty_blob()
24         ) returning data into v_blob_locater;
25
26         dbms_lob.loadblobfromfile(
27                 v_blob_locater,
28                 v_handle,
29                 v_amount,
30                 v_doffset,
31                 v_soffset
32         );
33         dbms_output.put_line ( '書き込まれたサイズ : ' || v_doffset || 'Bytes' );
34         commit;
35         dbms_lob.fileclose(v_handle);
36 end;
37 /
리스트 1 BLOB 데이터를 격납하는 PL/SQL 샘플
(표시의 사정으로 개행하고 있는 곳이 있습니다.코드는 list7_1부터 참조할 수 있습니다)





■ BLOB 데이터를 격납하는 샘플의 해설


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



3行目

BLOB 데이터형을 취급할 때 locator라고 불리는 포인터와 같은 것을 사용합니다.데이터의 격납등을 실시할 때  이 locator를 기본으로 사용합니다.



4行目

BFILE이라고 하는 LOB 데이터형입니다. BLOB와의 차이는, 데이터를 Oracle내에 격납하는 것이 아니라, OS상의 파일로서 포인터만 Oracle에 격납하는 이미지가 됩니다. 그리고 BFILENAME 함수를 사용하고, v_handle에 BFILE locator를 세트 하고 있습니다.BFILENAME 함수의 인수는

BFILENAME('Directory','Filename');




Directory라고 기재하고 있습니다만, OS상의 풀 패스가 아니고, Create Directory문으로 작성한 Directory 오브젝트(주 1)가 됩니다.혹은, 초기화 파라미터 파일의 utl_file_dir로 지정한 패스를 지정해 주세요.

   (주 1) Directory 오브젝트

  다운로드용의 샘플 코드, create_ddl.sql에 작성용의 SQL가 기재되어 있습니다.




11行目

DBMS_LOB 패키지의 FileOpen 서브 프로그램을 사용해, 대상의 파일을 오픈합니다.인수에는, BFILENAME 함수로 세트 한 BFILE locator와 읽기 모드를 지정합니다. 읽기 모드는 DBMS_LOB로 이하의 정수로서 선언되고 있습니다.BFILE의 경우는 기입을 할 수 없기 때문에 dbms_lob.file_readonly를 사용합니다.

file_readonly   binary_integer = 0
lob_readonly    binary_integer = 0
lob_readwrite   binary_integer = 1




16行目

26행째의 DBMS_LOB.LoadBLOBFromFile 서브 프로그램에서 사용할 데이터 사이즈를 GETLENGTH 서브 프로그램으로 취득합니다. 만약, amount로 지정한 사이즈가 실제의 데이터보다 큰 경우는 에러가 발생합니다.



발생하는 에러
   ORA-22993: 지정된 입력량은 실제의 소스량을 넘고 있습니다.

  반대로, amount로 지정한 사이즈가, 실제의 데이터보다 작은 경우는 amount로 지정된

   사이즈분만큼이 써집니다.



18行目

데이터를 테이블에 삽입합니다만 returning구를 사용해 data열의 BLOB locator를 취득하고 있습니다. 또, data열에 사용하고 있는 empty_blob() 함수는 열의 속성을 비우는 함수입니다.이 함수를 사용함으로 LOB 영역의 초기화를 해 데이터가 존재하지 않는 상태가 됩니다.




26行目

DBMS_LOB.LoadBLOBFromFile 서브 프로그램에서는 실제로 데이터를 격납합니다.

인수를 순서에 보고 갑니다.



dest_lob IN OUT NOCOPY BLOB(v_blob_locater)


격납처의 BLOB locator를 지정합니다.


src_bfile IN BFILE(v_handle)
대상 파일의 BFILE locator를 지정합니다.



amount IN INTEGER(v_amount)
DBMS_LOB.GETLENGTH로 취득한 기입 사이즈를 지정합니다. DBMS_LOB.GETLENGTH를 사용해 데이터 사이즈를 미리 취득해 두었습니다만, DBMS_LOB.LOBMAXSIZE 정수(4294967295=4Gbytes)를 지정할 수도 있습니다. 이 정수를 사용하면, 사전에 amount를 요구할 필요는 없습니다.




dest_offset IN OUT INTEGER(v_doffset)
기재를 개시하는 위치를 지정합니다.예를 들면, 한 번 RAW 데이터형(최대 32767 bytes)에 데이터를 넣고 나서 WRITE 하는 경우, 32767 bytes를 쓰고, 다음은 32768 bytes째부터……라고 할 때 사용합니다.또, DBMS_LOB.LoadBLOBFromFile 실행 후의 이 값은, 써진 사이즈가 격납됩니다.33행째로는, 써진 사이즈를 DBMS_OUTPUT 패키지를 사용해 출력하고 있습니다.


src_offset IN OUT INTEGER (v_soffset)
대상 파일의 읽기 개시 위치를 지정합니다.이쪽도 실행 후에 읽어내 종료후의 위치가 격납되고 있고, 다음의 읽기 개시 위치로서 이용할 수 있습니다.




34~35行目

다음은 데이터를 위탁하고, BFILE locator를 Close 할 뿐입니다.



■ BLOB 데이터를 출력하는 샘플




다음은 Oracle에 격납되고 있는 데이터 파일을 PL/SQL로 OS상의 파일에 써내는 샘플을 만들어 보겠습니다.

1 set serveroutput on
2 declare
3         v_blob_locater          blob;
4         v_offset                integer := 1;
5         v_buffer                long raw;
6
7         v_file_buffer_size      integer := 32000;
8         v_amount                integer := 2000;
9
10         v_totalsize             integer;
11         v_filetype              utl_file.file_type;
12         v_filename              varchar2(1000) := 'maguro_out.jpg';
13         v_openmode              char(1) := 'w';
14
15 begin
16         select data into v_blob_locater from test_blob_data where id = 1;
17         v_totalsize := dbms_lob.getlength(v_blob_locater);
18         v_filetype := utl_file.fopen('LOB_DATA_PATH', v_filename, v_openmode, v_file_buffer_size);
19
20         while v_offset < v_totalsize loop
21                 if v_offset + v_amount > v_totalsize then
22                         v_amount := v_totalsize - v_offset;
23                 end if;
24                 dbms_lob.read(
25                         v_blob_locater,
26                         v_amount,
27                         v_offset,
28                         v_buffer
29                 );
30
31                 utl_file.put_raw(
32                         v_filetype,
33                         v_buffer,
34                         true
35                 );
36
37                 v_offset := v_offset + v_amount;
38         end loop;
39
40         utl_file.fclose(v_filetype);
41
42 end;
43 /
리스트 2 BLOB 데이터를 출력하는 PL/SQL 샘플
(표시의 사정으로 개행하고 있는 곳이 있습니다.코드는 list7_2로부터 참조할 수 있습니다)


 

■ 데이터를 출력하는 샘플의 해설



5行目

버퍼 데이터를 LONG RAW형으로 선언하고 있습니다.바이너리 데이터이므로, RAW 데이터가 사용되고 있습니다



7行目

UTL_FILE.PUT_RAW 서브 프로그램의 버퍼 사이즈의 상한은 32767 bytes입니다.



11行目

utl_file.file_type형의 변수를 정의합니다. 이것이 출력하는 파일의 파일 ID가 됩니다.



16行目

SELECT문으로, ID=1의 레코드의 BLOB 데이터를 추출하고 있습니다.검색된 BLOB locator를 v_blob_locater에 대입해 이후  DBMS_LOB.READ로 사용합니다.



17行目

UTL_FILE.PUT_RAW의 제한(32767 bytes까지의 버퍼)을 클리어 하기 위해서, 루프 하면서 LOB의 읽기와 파일에의 서두를 실시합니다.그 때문에, Max치로서 DBMS_LOB.GETLENGTH로, 대상 LOB 데이터의 MAX 사이즈를 취득합니다.



18行目

UTL_FILE.FOPEN로 파일을 오픈합니다.UTL_FILE.FOPEN의 인수는 아래와 같습니다.



location IN VARCHAR2
VARCHAR로 되어 있으나 Create Directory로 작성한 Directory 오브젝트를 지정하는 것도 가능합니다. 여기로 지정하는 PATH는 이번처럼 Create Directory로 작성한 Directory 오브젝트 또는 초기화 파라미터 파일의 utl_file_dir로 지정된 PATH만을 사용할 수 있습니다.




filename IN VARCHAR2
출력하는 파일명을 지정합니다.


open_mode IN VARCHAR2
오픈하는 모드를 지정합니다.
   r … 읽기
   w … 기입
   a … 추가 기입


max_linesize IN BINARY_INTEGER
BLOB 데이터의 경우는 바이너리 데이터이기 위해 최대 버퍼의 값이됩니다.최대는 32767 bytes로, 디폴트에서는 1000 bytes가 됩니다.




20行目

v_amount로 지정한 bytes만큼씩 LOB의 독해와 파일에의 서두를 v_totalsize까지 반복해 실시합니다. 2123행째로는, v_totalsize를 넘었을 경우 경우(데이터의 EOF)에, 전체로부터 기입을 실시한 사이즈를 빼고  나머지의 amount를 산출합니다.



24行目

SELECT문으로 취득한 BLOB locator로부터 실데이터를 읽어들입니다.DBMS_LOB.READ의 인수는 아래와 같습니다.



lob_loc IN BLOB
읽기 대상의 BLOB locator를 지정합니다.


amount IN OUT NOCOPY BINARY_INTEGER
읽기 사이즈를 지정합니다.offset로 지정한 개시 위치로부터 amount 분의 데이터를 읽어들입니다.



offset IN INTEGER
읽기 개시 위치를 지정합니다.




buffer OUT RAW
읽어낸 데이터를 격납하는 버퍼를 지정합니다.

샘플의 예에서는, BLOB locator로 나타나는 BLOB 데이터를 v_offset 째부터 v_amount 만큼을 읽어들이고, v_buffer 변수에 격납이라고 하는 의미입니다.



31行目

24행째에 취득한 BLOB 데이터를 UTL_FILE.PUT_RAW 서브 프로그램으로 파일에 써내고 있습니다. 3번째의 인수(autoflash)를 true로 설정하면 기입 버퍼를 자동적으로 플래시 해 물리 디스크에 기록해 줍니다.





이상으로 BLOB 데이터의 격납과 출력을 실시할 수 있습니다.실제의 현장에서는 PL/SQL만으로 실시하는 케이스는 별로 없을지도 모릅니다만 개발기등에서 BLOB의 동작을 확인하고 싶을 때 등에 사용할 수 있으므로.. 참고하삼.



Prev: DATE 형식으로 들어간 필드 날짜 수정하기 (초강추)
Next: ias 에서 웹사이트 디렉토리 구조 열람 방지 설정 해제 (Apache Webserver)
2007/08/23(17:01) from 203.234.120.78
CrazyWWWBoard 2000

Vote Reply Modify Delete Forward Prev Next List
(c) Nobreak Technologies, Inc.