Describe LOB데이터타입과오라클고급객체 here {{{ ================================== LOB 데이타 타입과 오라클 고급 객체 ================================== LOB (Large Object)에 대해서... 오라클에는 여러가지 데이타 타입이 존재합니다.. 옛날에는 그리 큰 데이타를 지원하지 않아도 되었습니다...왜냐하면 그만큼 다루어야 할 정보가 많치 않았고..또한 기술이 되지를 않았지여.. 그러나 지금은 너무나도 정보가 많습니다... 그중에 우리가 지금 할 LOB는 대용량의 데이타를 처리할 수 있는 데이타 타입입니다... LOB타입을 다루려면 PL/SQL을 알아야 합니다... 모르시는 분덜은 PL/SQL 에서 대용량 객체 다루는 부분을 살펴보시기 바랍니다.. 대용량 데이타를 처리하기 위한 데이타 타입은 다음과 같습니다.. BLOB : 구조화 되지 않은 이진 데이타를 저장, 최대 4기가 CLOB : 구조화 되지 않은 문자 데이타를 저당, 최대 4기가 NCLOB : 저장된 문자 데이타에 대해 다양한 국가 언어 지원기능 사용, 최대 4기가 BFILE : 구조화 되지 않은 데이타를 데이타베이스 외부에 저장, 최대 4기가 LOB타입은 외부와 내부로 나눌 수 있습니다.. BFILE만 빼고 다 내부 LOB로 볼 수 있습니다.. ======== 내부 LOB ======== 내부 LOB는 큰 객체를 말하지여..즉, 어떤 파일이나 긴 문자열이 하나의컬럼을 차지하고 있다는 것입니다.. 테이블의 하나의 행에 저장되는 데이타중 LOB타입으로 선언한 컬럼에 어떤 이미지, 문서, 문자열등의 큰 화일을 담는다고 생각하면 됩니다.. 생성문법은 다음과 같습니다.. CREATE TABLE table_name ( column1 [Data Type] column2 [Data Type] . . . LOB (column1, column2....) STORE AS TABLESPACE ENABLE DISABLE STORAGE IN ROW STORAGE 절 CHUNK [크기] PCTVERSION [비율] CACHE [NOCACHE] LOGGING [NOLOGGING] INDEX [인덱스명] TABLESPACE [테이블스페이스명] STORAGE (initial n next n minextents n maxextents n); 자세한 테이블 생성의 문법은 메뉴얼을 참고하시구여... 옵션을 살펴보겠습니다.. ENABLE STORAGE IN ROW : 데이타의 크기가 4000바이트 미만이면 같은 저장구조에 저장 DISABLE STORAGE IN ROW : 데이타의 크기가 4000바이트 미만이라도 다른 저장구조에 저장 CHUNK : LOB타입은 CHUNK단위로 읽기/쓰기작업을 하는데, CHUNK라는 것은 연속적인 블록을 의미, 기본값 1블록(최대 32K) PCTVERSION : 명시한 수치(백분율)대로 LOB공간내의 롤백을 위한 공간 확보, 검색이 주로인 테이블에는 0으로 설정하는 것이 유리 CACHE : 메모리에 캐쉬할 것인가를 결정 LOGGING : 트랜잭션 발생시 변경전의 데이타와 변경후의 데이타를 모두 별도의 공간에 저장하겠다는 의미 SQL> connect yasi/yasi 연결되었습니다. SQL> run 1 create table internal_lob 2 ( 3 room_no number, 4 layout blob, 5 text clob) 6 lob (layout, text) 7 store as (storage (initial 128k next 128k pctincrease 0) 8 chunk 20480 -- 20480 바이트씩 읽기/쓰기 수행 9 pctversion 20 -- LOB공간을 롤백을 위해 20%의 공간을 확보 10 nocache 11 nologging 12 index (storage (initial 128k next 128k)) 13* ) 테이블이 생성되었습니다. ======== 외부 LOB ======== 외부 LOB는 운영체제상의 파일의 로케이터를 저장하여 운영체제의 파일을 사용하는 방식입니다.. 다음은 외부 LOB사용시 주의 사항입니다.. - 데이타베이스 외부에 파일형태로 저장되는 데이타 타입 - 읽기전용, BFILE 컬럼은 BFILENAME함수를 사용하여 읽기 작업 수행 - 최대 4GB까지 데이타를 저장 - 테이블의 컬럼에는 외부에 있는 파일의 디렉토리와 파일명만 가지고 있다..즉, 로케이터만 저장 - BFILE 을 사용하기 전에 먼저 디렉토리 객체를 생성해 주어야 한다..(보안기능 제공) SQL> !pwd /export/home/oracle SQL> !touch bfile_test SQL> !cat > bfile_test bfile LOB test... ^D SQL> create table external_doc( 2 id number, 3 doc bfile); 테이블이 생성되었습니다. SQL> insert into external_doc 2 values (1001, bfilename('/export/home/oracle','bfile_test')); 1 개의 행이 만들어졌습니다. SQL> select * from external_doc; SP2-0678: SQL*Plus에 의해 열 또는 속성 유형을 나타낼 수 없습니다 <----------- DBMS_LOB 패키지를 사용해야 한다.. 지금까지 디렉토리에 대한 보안 문제는 신경쓰지 않았습니다.. 즉, BFILE 타입을 사용할때 사용되는 디렉토리에 대한 보안 문제는 데이타베이스 내부에서 조절할 방법이 없습니다.. 그런 이유로 데이타베이스를 사용하는 모든 사용자가 사용할 수 있다면 보안문제가 발생할 수 있습니다.. 그래서 디렉토리 객체를 생성해서 BFILE 타입을 사용할 때 정의되는 디렉토리에 대한 보안을 제공할 수 있습니다. SQL> create directory bfile_path as '/export/home/oracle'; 디렉토리가 작성되었습니다. SQL> grant read on directory bfile_path to scott; 권한이 부여되었습니다. SQL> connect scott/tiger 연결되었습니다. SQL> create table bfile_doc( 2 id number, 3 doc bfile); 테이블이 생성되었습니다. SQL> insert into bfile_doc 2 values (1002, bfilename('bfile_path','bfile_test')); 1 개의 행이 만들어졌습니다. 다음은 디렉토리 객체를 사용할 때 주의사항입니다... - 디렉토리 객체를 정의할때 사용하는 운영체제상의 경로는 홈디렉토리를 사용하지 말아야 한다. 왜냐하면 홈디렉토리에는 중요한 파일들이 들어 있기 때문이다.. - 디렉토리 객체를 사용하는 사용자는 CREATE ANY DIRECTORY 와 DROP ANY DIRECTORY 시스템 권한이 있어야 한다. - 시스템 디렉토리에 대한 사용권한을 시스템 관리자와 충분한 상의를 거쳐야 한다. - 디렉토리 객체를 생성할 때는 시스템 디렉토리에 대한 존재여부를 검사하지 않으므로 세심한 주의를 필요로 한다. SQL> desc dba_lobs; 이름 널? 유형 ------------------------------------------- -------- ---------------- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) SEGMENT_NAME NOT NULL VARCHAR2(30) INDEX_NAME NOT NULL VARCHAR2(30) CHUNK NUMBER PCTVERSION NOT NULL NUMBER CACHE VARCHAR2(10) LOGGING VARCHAR2(3) IN_ROW VARCHAR2(3) ======== 임시 LOB ======== 임시 LOB는 내부 LOB를 잠시 저장하는 변수의 역할을 합니다...사용방법은 다음과 같습니다.. . . . DBMS_LOB.CREATETEMPORARY(lob_temp, true, DBMS_LOB.TRANSACTION); . . . lob_temp는 임시 LOB의 로케이터이고 true 는 임시 LOB를 위해서 버퍼 영역을 할당하여 내부 LOB를 저장하겠다는 의미입니다.. LOB에 대한 읽기는 UTL_FILE 패키지와 DBMS_LOB 패키지를 적절히 이용해야 합니다.. 이부분은 PL/SQL부분으로 넘기고...넘어가겠습니다.. ======== REF 타입 ======== REF 타입은 테이블의 어떤 컬럼이 객체 테이블을 참조하는 관계를 말합니다.. 즉, 객체를 생성하여 그 객체를 테이블의 한 컬럼으로 참조하여 사용한다는 뜻입니다.. 직접 해봅시다.. SQL> create or replace type person_type as object 2 (lsat_name varchar2(10), 3 first_name varchar2(10), 4 phone varchar(12), 5 birthday varchar2(12)); 6 / 유형이 작성되었습니다. SQL> create or replace type emp_type as object 2 (empno number, 3 emp person_type); 4 / 유형이 작성되었습니다. SQL> create table emp of emp_type 2 oidindex emp_oid; 테이블이 생성되었습니다. SQL> insert into emp 2 values (emp_type(1000, person_type('SMITH','JOO','517-6678','7-JAN-65'))); 1 개의 행이 만들어졌습니다. SQL> create table dept 2 (empno number(4), 3 ename varchar2(15), 4 mgr REF emp_type SCOPE IS emp); 테이블이 생성되었습니다. SQL> insert into dept 2 select empno, 'SCOTT', REF(e) 3 from emp e 4 where empno = 1000; 1 개의 행이 만들어졌습니다. SQL> select ename, empno 2 from dept; ENAME EMPNO --------------- ---------- SCOTT 1000 SQL> insert into emp 2 values (emp_type(1000, person_type('SMITH','JOO','517-6678','7-JAN-65'))); 1 개의 행이 만들어졌습니다. SQL> insert into dept 2 select empno, 'SCOTT', REF(e) 3 from emp e 4 where empno = 1000; 2 개의 행이 만들어졌습니다. SQL> select ename, empno from dept; ENAME EMPNO --------------- ---------- SCOTT 1000 SCOTT 1000 SQL> select mgr, DEREF(mgr) from dept; MGR -------------------------------------------------------------------------------- DEREF(MGR)(EMPNO, EMP(LSAT_NAME, FIRST_NAME, PHONE, BIRTHDAY)) -------------------------------------------------------------------------------- 000022020895A053C87402DD51E030000395A0019995A053C87401DD51E030000395A00199 EMP_TYPE(1000, PERSON_TYPE('SMITH', 'JOO', '517-6678', '7-JAN-65')) 000022020895A06C21CE8F91D9E030000395A001C895A053C87401DD51E030000395A00199 EMP_TYPE(1000, PERSON_TYPE('SMITH', 'JOO', '517-6678', '7-JAN-65')) SQL> select emp from emp; EMP(LSAT_NAME, FIRST_NAME, PHONE, BIRTHDAY) -------------------------------------------------------------------------------- PERSON_TYPE('SMITH', 'JOO', '517-6678', '7-JAN-65') PERSON_TYPE('SMITH', 'JOO', '517-6678', '7-JAN-65') REF 사용시 주의해야 할 것이 있습니다.. Dangling 현상이라고 하는 것이 있는데...REF 타입의 특성상 REF 타입으로 정의된 컬럼은 실제 데이타가 저장되는 것이 아니고 참조되는 객체가 존재하는 포인터 정보를 가지고 있기 때문에 실제 데이타가 삭제된다면 해당 컬럼은 정산적으로 검색되지 못하겠지여.. 이러한 현상을 댕그링현상이라고 합니다.. 이 현상을 해결하기 위해서는 ANALYZE 명령을 통해서 널값으로 바꾸어 주어야 합니다.. SQL> select empno, ename, mgr 2 from dept 3 where mgr is dangling; 선택된 레코드가 없습니다. <---------------- 만약 댕글링이 발견되었다면 아래와 같이 해준다.. SQL> analyze table dept validate ref update 2 set dangling to null; 테이블이 분석되었습니다. =========== VARRAY 타입 =========== 이 타입은 배열형태로 저장되는 데이타 유형입니다.. 먼저 create type 문장으로 varray 형태의 유형을 작성후 테이블에 적용시켜줍니다.. 다음은 문법입니다.. CREATE TYPE [객체명] AS VARRAY(n) OF [객체명] CREATE TABLE [테이블명] (컬럼 VARRARY [객체명] . . . . ) 이 유형을 사용시 입력되는 데이타는 입력순으로 정령됩니다.. 또한 인덱스를 사용할 수 없으며, 배열의 데이타를 검색할 경우는 TABLE()을 사용합니다.. 배열내에는 일반적인 데이타 타입과 LOB 타입 을 사용할 수 있습니다.. REF와는 다르게 직접 배열컬럼에 데이타가 입력됩니다.. 또한 별도의 저장공간에 저장할 수 있는데.. 이는 아래 예를 참조하면 되겠습니다.. 이 배열타입은 입력, 삭제, 갱신이 자주 일어나는 경우에 별루 추천하지 않습니다.. 이유는 VARRAY 타입으로 정의된 컬럼에는 입력, 삭제, 갱신될때 배열 전체 데이타가 삭제되고 새롭게 입력되기 때문입니다... 즉, 거의 조회연산을 하는 테이블이면 적용해도 괜찮겠죠... SQL> create or replace type line_type as object 2 (line_id number(6), 3 amount number(9,2), 4 quantity number(5)); 5 / 유형이 작성되었습니다. SQL> create or replace type line_type_array 2 as varray(100) of line_type; 3 / 유형이 작성되었습니다. SQL> create table ord 2 (ord_id number(9), 3 cust number(6), 4 line_item line_type_array) 5 varray line_item store as lob ts_line; <----------------- LOB 타입은 ts_line이라는 별도의 테이블스페이스에 저장한다는 뜻.. 테이블이 생성되었습니다. SQL> insert into ord 2 values (1001, 200, line_type_array(line_type(3001, 123.4, 60000), 3 line_type(3002, 567.8, 55000), 4 line_type(3003, 123.4, 12345))); 1 개의 행이 만들어졌습니다. SQL> insert into ord 2 values (1002, 3000, line_type_array(line_type(4001, 456.7, 34567), 3 line_type(4002, 890.2, 34000), 4 line_type(4003, 987.6, 50000))); 1 개의 행이 만들어졌습니다. SQL> select * from ord; ORD_ID CUST LINE_ITEM(LINE_ID, AMOUNT, QUANTITY) ---------- ---------- ----------------------------------------------------------------------------------------------------------- 1001 200 LINE_TYPE_ARRAY(LINE_TYPE(3001, 123.4, 60000), LINE_TYPE(3002, 567.8, 55000), LINE_TYPE(3003, 123.4, 12345)) 1002 3000 LINE_TYPE_ARRAY(LINE_TYPE(4001, 456.7, 34567), LINE_TYPE(4002, 890.2, 34000), LINE_TYPE(4003, 987.6, 50000)) SQL> select o.ord_id, o.cust, i.line_id, i.amount, i.quantity 2 from ord o, table(o.line_item)i -------------------------> table함수를 사용해서 배열을 읽고 있다.. 3 where i.line_id in (3001, 4001); ORD_ID CUST LINE_ID AMOUNT QUANTITY ---------- ---------- ---------- ---------- ---------- 1001 200 3001 123.4 60000 1002 3000 4001 456.7 34567 SQL> =========== 중첩 테이블 =========== 중첩테이블도 VARRAY 타입과 비슷한 개념입니다.. 문법도 비슷하구여... 다음은 중첩테이블과 VARRAY타입의 비교입니다.. VARRAY NESTED TABLE ------------------------------------------- -------------------------------------------------- 3GL의 배열과 같다. 별도의 저장공간에 저장하는 객체 다른 컬럼과 같은 저장공간 또는 다른 저장공간 다른 컬럼과 별도의 저장공간 인덱스를 사용할 수 없다. 인덱스를 사용할 수 있다. 제약조건을 사용할 수 없다. 제약조건을 사용할 수 있다. 배열의 수에 제한이 있다. 제한이 없다. SQL> select o.ord_id, o.cust, i.line_id, i.amount, i.quantity 2 from ord o, table(o.line_item)i 3 where i.line_id in (3001, 4001); ORD_ID CUST LINE_ID AMOUNT QUANTITY ---------- ---------- ---------- ---------- ---------- 1001 200 3001 123.4 60000 1002 3000 4001 456.7 34567 SQL> create type line_table_type 2 as table of line_type; 3 / 유형이 작성되었습니다. SQL> create table nord 2 (nord_id number(9), 3 cust number(10), 4 line_item line_table_type) 5 nested table line_item store as users; 테이블이 생성되었습니다. SQL> insert into nord 2 values (1001, 2000, line_table_type (line_type(2001, 12.2, 45000), 3 line_type(2002, 34.1, 3200), 4 line_type(2003, 2.1, 123))); 1 개의 행이 만들어졌습니다. SQL> insert into nord 2 values (1002, 3000, line_table_type (line_type(3001, 1.2, 650), 3 line_type(3002, 123.4, 45678), 4 line_type(3003, 56.7, 90000))); 1 개의 행이 만들어졌습니다. SQL> select * from nord; NORD_ID CUST LINE_ITEM(LINE_ID, AMOUNT, QUANTITY) ---------- ---------- ----------------------------------------------------------------------------------------------------------------- 1001 2000 LINE_TABLE_TYPE(LINE_TYPE(2001, 12.2, 45000), LINE_TYPE(2002, 34.1, 3200), LINE_TYPE(2003, 2.1, 123)) 1002 3000 LINE_TABLE_TYPE(LINE_TYPE(3001, 1.2, 650), LINE_TYPE(3002, 123.4, 45678), LINE_TYPE(3003, 56.7, 90000)) SQL> select o.nord_id, o.cust, i.line_id, i.amount, i.quantity 2 from nord o, table(o.line_item) i 3 where i.line_id in (2002, 3003); NORD_ID CUST LINE_ID AMOUNT QUANTITY ---------- ---------- ---------- ---------- ---------- 1001 2000 2002 34.1 3200 1002 3000 3003 56.7 90000 SQL> select o.nord_id, o.cust, i.line_id, i.amount, i.quantity 2 from nord o, table(o.line_item) i; NORD_ID CUST LINE_ID AMOUNT QUANTITY ---------- ---------- ---------- ---------- ---------- 1001 2000 2001 12.2 45000 1001 2000 2002 34.1 3200 1001 2000 2003 2.1 123 1002 3000 3001 1.2 650 1002 3000 3002 123.4 45678 1002 3000 3003 56.7 90000 6 개의 행이 선택되었습니다. ====== 객체뷰 ====== 객체뷰란 관계형 테이블을 객체타입과 결합하여 사용하는 뷰를 의미합니다.. 바로 예로 들어가겠습니다.. SQL> create table prod 2 (ord_id number(9) primary key, 3 cust_id number(6)); 테이블이 생성되었습니다. SQL> insert into prod values(100, 200); 1 개의 행이 만들어졌습니다. SQL> create table line_item 2 (ord_id number(9) references prod, 3 prod_id number(6), 4 price number(9,2), 5 quantity number(5)); 테이블이 생성되었습니다. SQL> insert into line_item values(100, 300, 12.45, 600); 1 개의 행이 만들어졌습니다. SQL> insert into line_item values (100, 400, 3.4, 200); 1 개의 행이 만들어졌습니다. SQL> create or replace type line_item_type as object 2 (prod_id number(6), 3 price number(9,2), 4 quantity number(5)); 5 / 유형이 생성되었습니다. SQL> create or replace type line_item_array_type 2 as array(100) of line_item_type; -------------> 객체를 이용해서 배열을 생성했다. 3 / 유형이 생성되었습니다. SQL> create or replace view ord_item as -----------------> 객체뷰 생성 2 select o.ord_id, o.cust_id, 3 cast(multiset(select prod_id, price, quantity from line_item i ----------------> cast함수는 데이타형 변환, multiset함수는 다중형이 리턴되는 것을 의미 4 where i.ord_id = o.ord_id) as line_item_array_type) 5 as line_item 6 from prod o; 뷰가 생성되었습니다. SQL> select * from ord_item; ORD_ID CUST_ID LINE_ITEM(PROD_ID, PRICE, QUANTITY) ---------- ---------- ------------------------------------------------------------------------------------- 100 200 LINE_ITEM_ARRAY_TYPE(LINE_ITEM_TYPE(300, 12.45, 600), LINE_ITEM_TYPE(400, 3.4, 200)) 객체뷰는 일반적인 insert, update, delete문으로 입력, 갱신, 조작을 할 수 없습니다.. 객체뷰를 조작할때는 트리거를 사용해야 합니다.. SQL> create or replace trigger ord_item_insert -------------> 생성한 객체뷰에 대한 삽입트리거 2 instead of insert on ord_item 3 for each row 4 begin 5 insert into prod 6 values (:new.ord_id, :new.cust_id); ---------------> 입력되는 값을 바인드 변수로 받는다. 7 for i in 1..:new.line_item.count loop 8 insert into line_item 9 values( :new.ord_id, :new.line_item(i).prod_id, :new.line_item(i).price, :new.line_item(i).qu antity); 10 end loop; 11 end; 12 / 트리거가 생성되었습니다. SQL> insert into ord_item 2 values(300, 350, line_item_array_type(line_item_type(200, 432, 430), line_item_type(210, 21, 450))); 1 개의 행이 만들어졌습니다. SQL> select o.ord_id, o.cust_id, i.prod_id, i.price, i.quantity 2 from ord_item o, table(o.line_item) i 3 where i.prod_id in (300); ORD_ID CUST_ID PROD_ID PRICE QUANTITY ---------- ---------- ---------- ---------- ---------- 100 200 300 12.45 600 오라클 8 버전 이상에는 여러가지 추가된 객체들이 많습니다..이 객체뷰도 추가된 객체지여... 물리적 뷰, 디맨션, 외부 프로시저의 사용도 있슴다....이 세가지는 넘어가겠슴다... 실습 여건도 안돼구...저도 자신 없구여...이 문서를 만드는 지금은 한대라서리...쩝... 컴 한대는 집에...지금은 학교에...쩝...암튼...이 나머지 부분은 다른 문서를 참고하시기 바랍니다.. 또, 객체타입이란 것이 있는데..이 객체타입에 관해서는 PL/SQL 부분으로 넘기고.. 오라클의 LOB와 고급객체는 여기서 마치겠습니다.. }}}