整理自丁俊老师plsql文档：
1、基本介绍
oracle和pl/sql都支持lob（large object）类型，用来存储大数量数据，如图像文件，声音文件等。oracle 10g r1 支持最大8 到128万一字节的数据存储，依赖于你的数据库的 block size。
在pl/sql中，可以声明的lob的类型变量如下：
BFILE :二进制文件，存储在数据库外的操作系统文件，只读的。把此文件当二进制处理。
BLOB：二进制大对象，存储在数据库里的大对象，一般是图像声音等文件。
CLOB: 字符型大对象。一般存储大数量文本信息。存储单字节，固定宽度的数据。
NCLOB ： 字节字符大对象。存储单字节大块，多字节固定宽度，多字节变宽度数据。
oracle将大对象（LOB）分为两种：
1、存储在数据库里，参与数据库的事务。有三种是这个大对象：BLOB, CLOB , NCLOB。
2、存储在数据库外的一种：BFILE ，不参与数据库的事务，也就是不能roolback 或者commit等，它依赖于文件系统的数据完整性。
LONG 和 LONG RAW 这两种数据类型也是存储字符的，但是有很多问题，已经不建议使用了。不再讨论。
2、lob的使用
下面只讨论lob的基本原理和在plsql中的基本使用，为plsql开发使用lob提供一个基础性指导。
本部分使用的表是：
CREATE TABLE waterfalls (
falls_name VARCHAR2(80),--name
falls_photo BLOB,--照片
falls_directions CLOB,--文字
falls_description NCLOB,--文字
falls_web_page BFILE);--指向外部的html页面
这个表我们并不需要clob 和nclob 两个，只取一就可以，这里全部定义只是为了演示使用。
理解LOB的Locator
表中的Lob类型的列中存储的只是存储指向数据库中实际存储lob数据的一个指针。
在plsql中申明了一个lob类型的变量，然后从数据库中查询一个lob类型的值分配给变量，也只是将指针复制给了它，那么这个变量也会指向数据库中实际存放lob数据的地方。如：
--understanding lob locators
DECLARE
photo BLOB;
BEGIN
SELECT falls_photo
INTO photo
FROM waterfalls
WHERE falls_name='Dryer Hose';
如下图所示：
从上面的图可以看出，要处理lob数据，必须先获得lob locators。我们可以通过一个select 语句获取，当赋值给lob变量的时候，它也同样的lob locators。我们再plsql中处理可以使用dbms_lob包，里面内置了很多过程和函数来读取和修改我们的lob数据。下面给出处理lob数据的一边干方法：
1、通过select 语句获得一个lob locator。
2、通过调用dbms_lob.open打开lob。
3、调用dbms_lob.getchunksize 获得最佳读写lob值。
4、调用dbms_lob.getlength获取lob数据的字节值。
5、调用dbms_lob.read 获取lob数据。
6、调用dbms_lob.close 关闭lob
empty lob and null lob
empty的意思是我们已经获取了一个lob locator，但是没有指向任何lob数据。null是定义了一变量，但是没有获得lob locator。对于lob类型的处理和其他类型不一样。看下面的例子：
/* null lob example*/
declare
directions clob; --定义了，但是没有分配值，为null
begin
if directions is null then
dbms_output.put_line('directions is null');
else
dbms_output.put_line('directions is not null');
end if;
end;
/
DECLARE
directions CLOB; --定义一个，并且分配值
BEGIN
DELETE
FROM waterfalls
WHERE falls_name='Munising Falls'; --删除一行
INSERT INTO waterfalls
(falls_name,falls_directions)
VALUES ('Munising Falls',EMPTY_CLOB( )); --插入一行通过使用 EMPTY_CLOB( ) to 建立一个lob locator
SELECT falls_directions
INTO directions
FROM waterfalls
WHERE falls_name='Munising Falls'; --获得lob locator，上面插入的数据，因为我们插入的是一个empty_clob(),那么lob locator不指向任何数据，虽然给变量分配了只
IF directions IS NULL THEN
DBMS_OUTPUT.PUT_LINE('directions is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('directions is not NULL'); --打印此句
END IF;
DBMS_OUTPUT.PUT_LINE('Length = '|| DBMS_LOB.GETLENGTH(directions)); --结果为o
END;
注意：
1. 上面例子中的empty_clob()是oracle的内置函数，创建了一个lob locator。但是我们没有让它指向任何数据，所以是empty。而且通过select语句给变量directions分配了lob locator，所以不是null,但是length为0，故为empty。
2. 在基本类型中，我们判断一个变量是不是有数据，只要is null就可以了。但是在lob类型中我们从以上的例子看出来是不正确的。Lob首先必须判断is null看是否分配lob locator，如果分配了还需要进一步检查length是否为0，看是否是empty，所以完整的是下面这样：
IF some_clob IS NULL THEN
--如果is null 为true表示未分配，肯定没有数据
ELSEIF DBMS_LOB.GETLENGTH(some_clob) = 0 THEN
--分配了length 为0，也没有数据
ELSE
--有数据
END IF;
建立LOB
在上面我们使用empty_clob()建立了一个空的clob，lob locator只是一个指针，真正的数据是存储在磁盘中或数据库文件中。我们先建立一个空的clob，然后我们可以update来让变量真正指向由数据的lob。empty_clob() 可以用来处理clob和nclob。
向LOB里写入数据
当获得有效的lob locator之后，就可以使用dbms_lob 包的下列procedure向lob中写入数据。
DBMS_LOB.WRITE:允许自动写入数据到lob中。
DBMS_LOB.writeappend：向lob的末尾写入数据。
-- write lob
DECLARE
directions CLOB;
amount BINARY_INTEGER;
offset INTEGER;
first_direction VARCHAR2(100);
more_directions VARCHAR2(500);
BEGIN
--Delete any existing rows for 'Munising Falls' so that this example can be executed multiple times
DELETE
FROM waterfalls
WHERE falls_name='Munising Falls';
INSERT INTO waterfalls
(falls_name,falls_directions)
VALUES ('Munising Falls',EMPTY_CLOB( )); --Insert a new row using EMPTY_CLOB( ) to create a LOB locator
SELECT falls_directions
INTO directions
FROM waterfalls
WHERE falls_name='Munising Falls'; --Retrieve the LOB locator created by the previous INSERT statement
DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE); --Open the LOB; not strictly necessary, but best to open/close LOBs.
first_direction := 'Follow I-75 across the Mackinac Bridge.'; --Use DBMS_LOB.WRITE to begin
amount := LENGTH(first_direction);
offset := 1; --number of characters to write
DBMS_LOB.WRITE(directions, amount, offset, first_direction); --begin writing to the first character of the CLOB
more_directions := ' Take US-2 west from St. Ignace to Blaney Park.' -Add some more directions using DBMS_LOB.WRITEAPPEND
|| ' Turn north on M-77 and drive to Seney.'
|| ' From Seney, take M-28 west to Munising.';
DBMS_LOB.WRITEAPPEND(directions,
LENGTH(more_directions), more_directions);
more_directions := ' In front of the paper mill, turn right on H-58.' --Add yet more directions
|| ' Follow H-58 to Washington Street. Veer left onto'
|| ' Washington Street. You''ll find the Munising'
|| ' Falls visitor center across from the hospital at'
|| ' the point where Washington Street becomes'
|| ' Sand Point Road.';
DBMS_LOB.WRITEAPPEND(directions,
LENGTH(more_directions), more_directions);
DBMS_LOB.CLOSE(directions); --Close the LOB, and we are done.
END;
/
在这个例子里，我们使用了write 和 writeappend这两个过程来插入数据到lob中。因为开始的时候，我们插入了一个空的lob locator。要注意一点，我们最后使用了 dbms_lob.close方法关闭lob。这是一个好方法，特别是在处理oracle text的时候，任何oracle text domain和 function-based indexes 被update是在write和writeappend的时候调用的，而不是在close的时候被update的。
我们向lob中写入数据的时候，没有必要更新表中的列。因为它保存的只是一个locator，我们的变量也获得同样的locator，当我们写入数据去lob的时候，locator并没有改变。改变的只是locator指向的物理数据。
在sqlplus中系那是上面的例子：
SQL> set long 2000
SQL> column falls_directions word_wrapped format a70
SQL> select falls_directions from waterfalls where falls_name='Munising Falls';
FALLS_DIRECTIONS
----------------------------------------------------------------------
Follow I-75 across the Mackinac Bridge. Take US-2 west from St. Ignace
to Blaney Park. Turn north on M-77 and drive to Seney. From Seney, take M-28 We
sing. In front of the paper mill, turn right on H-58. Follow H-58 to W
ashington Street. Veer left onto Washington Street. You'll find the Munising Fal
center across from the hospital at the point where Washington Street
becomes Sand Point Road.
其中set long 2000 是显示2000个字符。word_wrappend是自动换行。
从lob中读取数据
步骤：1、通过select 查询获得lob locator初始化lob变量。2、调用dbms_lob.read 过程读取lob数据。
下面是dbms_lob.read过程的定义，注意参数：
PROCEDURE read( lob_loc IN BLOB, --初始化后的lob变量lob locator
amount IN OUT NOCOPY INTEGER, --读取的数量(clob为字符数,blob,bfile是字节数)
offset IN INTEGER, --开始读取位置
buffer OUT RAW); --读到的数据,raw要显示用转换函数，见bfile
PROCEDURE read( lob_loc IN CLOB CHARACTER SET ANY_CS,
amount IN OUT NOCOPY INTEGER,
offset IN INTEGER,
buffer OUT VARCHAR2 CHARACTER SET lob_loc%CHARSET);
PROCEDURE read( file_loc IN BFILE,
amount IN OUT NOCOPY INTEGER,
offset IN INTEGER,
buffer OUT RAW);
下面是一个读取clob的例子：
--从lob中读取数据
DECLARE
directions CLOB;
directions_1 VARCHAR2(300);
directions_2 VARCHAR2(300);
chars_read_1 BINARY_INTEGER;
chars_read_2 BINARY_INTEGER;
offset INTEGER;
BEGIN
--首先获得一个lob locator
SELECT falls_directions
INTO directions
FROM waterfalls
WHERE falls_name='Munising Falls';
--记录开始读取位置
offset := 1; --尝试读取229个字符，chars_read_1将被实际读取的字符数更新
chars_read_1 := 229;
DBMS_LOB.READ(directions, chars_read_1, offset, directions_1); --当读取229个字符之后，更新offset，再读取225个字符
IF chars_read_1 = 229 THEN
offset := offset + chars_read_1; --offset变为offset+chars_read_1，也就是从300开始
chars_read_2 := 255;
DBMS_LOB.READ(directions, chars_read_2, offset, directions_2);
ELSE
chars_read_2 := 0; --否则后面不在读取
directions_2 := '';
END IF;
DBMS_OUTPUT.PUT_LINE('Characters read = ' || --显示读取的字符数
TO_CHAR(chars_read_1+chars_read_2)); --显示结果
DBMS_OUTPUT.PUT_LINE(directions_1);
dbms_output.put_line(length(directions_1));
DBMS_OUTPUT.PUT_LINE(directions_2);
dbms_output.put_line(length(directions_2));
END;
/
Dbms_lob.read 的第二个参数是传递要读取的数量。对于clob是字符数，blob 和 bfile都是字节数。它是随着读取的数目自动更新的，offset不会更新。所以分布读取需要手动更新offset，下个offset是上一个offset+读取数量。我们可以通过dbms_lob.get_length(lob_locator)获得这个lob的长度，结果clob是字符数，blob和bfile是字节数，然后分布读取。
3、使用BFILE
BFILE 和 clob ，nclob，blob 是不同的。bfile是外部的lob类型，其他三个是oracle内部的lob类型，他们至少有三点主要不同的地方：
1、bfile的值是存在操作系统文件中，而不是数据库中。
2、bfile不参与数据库事务操作。也就是改变bfile不能commit或rollback。但是改变bfile的locator可以commit或rollback。
3、bfile在plsql和oracle中是只读的，不允许写。你必须生成一个外部的操作系统文件让bfile locator能够完全指向它。
在plsql 中使用bifle，仍然需要lob locator，只不过是一个目录和文件的别名，你可以使用bfilename函数获得一个bfile locator。使用create or replace directory
[alias] as ‘file locator directory’，你必须具有CREATE ANY DIRECTORY 权限才能使用。如：
CREATE DIRECTORY bfile_data AS 'c:\PLSQL Book\Ch12_Misc_Datatypes';
GRANT READ ON DIRECTORY bfile_data TO gennick; --读的权限给这个用户。
通过all_directory查找目录信息。
建立bfile locator
Bfile locator 是很容易被创建的，只要简单地调用BFILENAME函数传入相应的参数就可以了，不像其他的lob 对象还要从数据库中select。BFILENAME 函数有两个参数，第一个参数是我们创建的目录的别名，一定要大写，第二个参数是我们要传入的目录下的文件。
BFILENAME(directory_alias,file)
-bfile的使用
/*创建一个bfile locator*/
--创建一个目录
create or replace directory bfile_data as 'C:\';
--创建bfile locator
declare
web_page bfile;
begin
DELETE FROM waterfalls WHERE falls_name='Tannery Falls';
--创建bfile locator，使用bfilename函数,directory是大小写敏感的，一定要大写
web_page:=bfilename('BFILE_DATA','Tannery Falls.htm');
--save to db
INSERT INTO waterfalls (falls_name, falls_web_page)
VALUES ('Tannery Falls',web_page);
end;
/
访问bfiles
首先需要获得bfile locator，有两种方式，一种通过select into 获得，第二种通过函数bfilename 获得。然后使用dbms_lob 的open，read，close 方法。注意和clob 不一
样，它和blob 一样，读取的结果存在buff中都是raw类型的，要输出必须通过utl_raw包中的cast_to_varchar2(res)方法。Utl_raw包里有很多方法，实现类型转换。
select * from waterfalls where falls_name='Tannery Falls';
SP2-0678: 列或属性类型无法通过 SQL*Plus 显示
SQL> select * from all_directories; --查找目录
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------ ------------------------------ -----------------------------------------------------------
SYS DM_PMML_DIR D:\oracle\product\10.1.0\Db_1\dm\admin
SYS BFILE_DATA c:\
DECLARE --read bfile
web_page BFILE;
html RAW(60);
amount BINARY_INTEGER := 60;
offset INTEGER := 1;
BEGIN
--获得一个bfile locator
SELECT falls_web_page
INTO web_page
FROM waterfalls
WHERE falls_name='Tannery Falls';
--不使用查询，使用bfilename函数也可以获得一个bfile locator
-- web_page:=bfilename('BFILE_DATA','Tannery Falls.htm');
--打开locator
DBMS_LOB.OPEN(web_page);
DBMS_LOB.READ(web_page, amount, offset, html); --这里的ammount和clob不一样，是type
DBMS_LOB.CLOSE(web_page);
--rawtohex：2进制转换成16进制字符
DBMS_OUTPUT.PUT_LINE(RAWTOHEX(html));
--结果转型使用utl_raw.cast_to_varchar2
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(html));
END;
注：1.在一个session中打开bfile的上限是由数据库的初始化参数 session_max_open_fisle 决定的。它包括任何种类的文件打开,不仅限于bfile，对于utl_file包也同样限制。
2.对于处理外部文件的一个图像的集合，那么使用bfile是很好的方法。使用bfile指向外部文件夹中的每个图像，然后在plsql中处理它们。
使用bfile加载lob列
除了允许访问数据库环境外的二进制文件，oracle的dbms_lob提供了三个过程可以从外部文件加载数据到数据库的lob 列。Oracle 9i realse1 只有dbms_lob.loadfromfile,
realse2 增加了2 个:dbms_lob.loadclobfromfile,dbms_lob.loadblobfromfile。(查询相关说明请到plsql developer的浏览器中找到package 栏)。注意loadclobfromfile 需要做
字符的翻译工作，见lang_context 参数。参数和使用说明：
PROCEDURE loadfromfile (dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
src_lob IN BFILE,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1);
PROCEDURE loadblobfromfile(dest_lob IN OUT NOCOPY BLOB,
src_bfile IN BFILE,
amount IN INTEGER,
dest_offset IN OUT INTEGER,
src_offset IN OUT INTEGER);
PROCEDURE loadclobfromfile(dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
src_bfile IN BFILE,
amount IN INTEGER,
dest_offset IN OUT INTEGER,
src_offset IN OUT INTEGER,
bfile_csid IN NUMBER,
lang_context IN OUT INTEGER,
warning OUT INTEGER);
loadclobfromfile 中有bfile_csid为每个从外部文件加载的字符设置一个id。
Lang_context 就是loadclobfromfile必须要做翻译(translate)工作，从而使外部字符到数据库字符的转换。Nls_charset_id是设置字符集的，确保中文等能正确存到数据库中。Warning和exception 不一样，只是一种警告信息，就算有，过程还会继续执行。
/**
从外部文件加载数据到lob列,使用vdbms_lob.loadfromfile过程，oracle 9i realse2增强，dbms_lob.loadclobfromfile和dbms_lob.loadblobfromfile **/
DECLARE
--1.创建一个lob locator
Tannery_Falls_Directions BFILE
:= BFILENAME('BFILE_DATA','TanneryFalls.directions');
directions CLOB;
--目标偏移量从第1个字符开始
destination_offset INTEGER := 1;
--源偏移量也从第1个字符开始
source_offset INTEGER := 1;
--设置一个字符的翻译，使外部文件数据能够转为数据库中的字符集一致的数据
language_context INTEGER := DBMS_LOB.default_lang_ctx;
--警告
warning_message INTEGER;
BEGIN
--删除
DELETE FROM waterfalls WHERE falls_name='Tannery Falls';
--插入，使用empty_clob()建立一个locator
INSERT INTO waterfalls
(falls_name,falls_directions)
VALUES ('Tannery Falls',EMPTY_CLOB( ));
--获得刚插入的lob
SELECT falls_directions
INTO directions
FROM waterfalls
WHERE falls_name='Tannery Falls';
--打开目标的lob和源的bfile
DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.OPEN(Tannery_Falls_Directions);
--加载bfile的内容到clob中，使用dbms_lob包中的lobmaxsize常量，全部加载
DBMS_LOB.LOADCLOBFROMFILE
(directions, Tannery_Falls_Directions,
DBMS_LOB.LOBMAXSIZE,
destination_offset, source_offset,
NLS_CHARSET_ID('ZHS16GBK'),
language_context, warning_message);
--使用if判断warning信息
IF warning_message = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
dbms_output.put_line(
'Warning! Some characters couldn''t be converted.');
END IF;
--关闭源和目标的lob
DBMS_LOB.CLOSE(directions);
DBMS_LOB.CLOSE(Tannery_Falls_Directions);
END;
