==================================
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 螻蠍螳豌企 蠍一 襷豺蟆給..