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