| _覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁 |
|
FrontPage › LOB
|
|
|
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;
鏤
|
豌 豺′ 覦. 覿 覿 磯. 蟠碁ゼ 襴 譟願化 覦. 蠏碁 れ 煙 讌 襷 襤磯ゼ 覦. |