PL/SQL块定义部分之四LOB类型

在Oracle的早期版本中，使用LONG 和 LONG RAW 类型存放大数据。从8i版本开始，Oracle引入了LOB数据类型，并且Oracle建议开发人员尽量去使用LOB类型而不去使用 LONG 和 LONG RAW 。LOB类型和LONG、LONG RAW类型相比有几个不同的地方。如：
1、LOB(除了NCOLB)可以作为对象类型的一个属性，但LONG类型不可以。
2、LOB的最大值是4G，而LONG只有2G。
3、LOB支持随机访问数据，但LONG只支持顺序访问。
    LOB包括内部LOB和外部LOB，其中内部LOB(BLOB CLOB NLOB)的数据存放到数据库中，而外部LOB(BFILE)的数据存放在操作系统的文件中。具体说明如下：
BLOB:存放二进制格式的数据，如：图像、音频、视频等。
CLOB:存放数据库字符集格式的字符数据，如：大的文本数据。
NCLOB:存放Unicode字符集的字符数据。
BFILE:存放指向操作系统文件的指针，并且该文件即可以是文本文件，也可以是二进制文件。当我们访问该类型所表示的列时，只能读取数据，不能修改。
   
一、理解什么是LOB定位器和LOB数据
    LOB数据是我们存入到数据库中的值，而LOB 定位器 是操作这些值的指针。当我们想读取或者修改LOB数据时，我们必须先要获取LOB 定位器 ，然后通过LOB定位器才能读取或者修改LOB数据，下面的示例能够帮助我们更好的理解LOB数据和LOB定位器：
DECLARE
picLOB BLOB;
BEGIN
                 --假定 picColumn列是BLOB类型
  SELECT picColumn INTO picLOB FROM myTable WHERE ID=10;
END;
    当SELECT INTO语句执行完之后， picLOB变量将被赋值，但是 picLOB变量的值不是LOB数据，而是LOB数据的定位器。要想获取LOB数据，我们必须对定位器进行操作，Oracle中DBMS_LOB包里的很多函数和过程都可以对定位器进行操作，如下：
APPEND()： 将源LOB中的内容加到目的LOB中
COPY() ：从源LOB中复制数据到目的LOB
ERASE()： 删除LOB中全部或部分内容
TRIM()： 将LOB值减少到指定的长度
WRITE()： 向LOB 中写入数据
COMPARE()： 比较两个同种数据类型的LOB的部分或全部值是否相同
GETLENGTH()： 获取LOB的长度
READ()： 从LOB中读出数据

二、使用CLOB数据类型
示例1：创建带有CLOB数据类型的表，并插入数据
CREATE TABLE CLOB_TABLE
(
  id NUMBER(3) PRIMARY KEY,
  remark CLOB    --remark：备注
);
INSERT INTO CLOB_TABLE(id) VALUES(1);  --remark列没有定位器，并且没有数据
INSERT INTO CLOB_TABLE VALUES(2,'ID为2的备注信息');  --remark列有定位器，并且有数据
INSERT INTO CLOB_TABLE VALUES(3,empty_clob());--remark含有定位器，但是没有数据

检索定位器的值：
DECLARE
myLob CLOB;
BEGIN
  SELECT remark INTO myLob FROM CLOB_TABLE WHERE id=___ ;
  IF myLob IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('myLob IS NULL ');
  ELSE
    DBMS_OUTPUT.PUT_LINE(myLob );
  END IF;
END;
当下划线填入1时，结果如下：
myLob IS NULL
当下划线填入2时，结果如下：--打印出定位器所指向的数据
ID为2的备注信息
当下划线填入3时，没有打印出信息。

修改id为1的remark列，使其具有定位器：
UPDATE CLOB_TABLE SET remark=empty_clob() WHERE id=1;
这时再调用以上的代码块，这次没有打印出信息。

示例2：向CLOB列追加数据
DECLARE
myLob CLOB;
buffer VARCHAR2(20) := '这是第一次追加的内容';
amount INT;
offset INT;
BEGIN
  --要想能够修改LOB数据，必须指定FOR UPDATE子句
  SELECT remark INTO myLob FROM CLOB_TABLE WHERE id=2 FOR UPDATE ;
  amount:=LENGTH( buffer );  --获取追加文本的长度
  offset:=DBMS_LOB.GETLENGTH (myLob)+1;  --获取定位器引用的LOB数据的 长度
  DBMS_LOB.WRITE (myLob,amount,offset, buffer );-- 向CLOB列追加数据，每次最大追加32767字节。要注意必须先存在定位器才能追加数据，否则会出现invalid LOB locator specified: ORA-22275错误。
  --DBMS_LOB.WRITEAPPEND(myLob,amount, buffer );--使用这个函数不用考虑offset参数
END;

示例3：读取CLOB数据
DECLARE
myLob CLOB;
text VARCHAR2(40) := '这是第三次追加的内容';
amount INT;
offset INT;
BEGIN
  SELECT remark INTO myLob FROM CLOB_TABLE WHERE id=2;
  amount:=15;
  offset:=3;
  --从LOB数据中的第三个字符开始读取共读取15个字符放入到text变量
  DBMS_LOB.READ(myLob,amount,offset,text);
  DBMS_OUTPUT.PUT_LINE(text);
END;

三、使用BFile数据类型
    BFile类型不存放文件实际数据，而是存放文件的指针，文件的数据存储在操作系统的文件系统上，并且我们只能通过BFile类型读取文件的数据，不能修改。
示例1：创建ScottDir目录，并授予scott用户读权限
CREATE DIRECTORY ScottDir AS 'E:/ScottDir'; --注意此语句并不会在操作系统上创建文件夹
GRANT READ ON DIRECTORY ScottDir TO scott;

示例2：创建含有BFile数据类型的列，并插入数据
CREATE TABLE BFILE_TABLE
(
   id NUMBER PRIMARY KEY,
   remark  BFILE
);
插入数据：
INSERT INTO BFILE_TABLE(id) VALUES(1);
UPDATE BFILE_TABLE SET remark=BFILENAME('SCOTTDIR ','1.txt') ;
INSERT INTO BFILE_TABLE(id,remark) VALUES(2,BFILENAME(' SCOTTDIR ','2.doc') );
注意：BFILE数据类型指向的文件，即可以是文本文件 也可以是二进制文件 。而且BFILE数据类型不能通过SELECT语句直接查看其内容。

示例3：将BFile文件的内容读取到CLOB列(前提是BFile文件是文本文件)
DECLARE
src   BFILE;
des   CLOB;
amount    INT;
src_offset INT :=1;
des_offset INT :=1;
csid INT :=850;
lc   INT :=0;
warning INT;
BEGIN
  SELECT remark INTO src FROM BFile_TABLE WHERE id=1;
  SELECT remark INTO des FROM CLOB_TABLE WHERE id=1 FOR UPDATE ;
  DBMS_LOB.FILEOPEN(src,DBMS_LOB.FILE_READONLY);
  amount:=DBMS_LOB.GETLENGTH(src);
  DBMS_OUTPUT.PUT_LINE(amount);
 DBMS_LOB.LOADCLOBFROMFILE (des,src,amount,des_offset,src_offset,csid,lc,warning);
  DBMS_LOB.CLOSE(src);
END;

四、使用BLOB数据类型
示例1：创建带有BLOB列的表并插入数据
 CREATE TABLE BLOB_TABLE
 (
   id NUMBER(3),
   remark BLOB
 );
INSERT INTO BLOB_TABLE VALUES(1,EMPTY_BLOB());
将文件内容插入到BLOB列：
DECLARE
src   BFILE;
des   BLOB;
amount    INT;
src_offset INT :=1;
des_offset INT :=1;
csid INT :=0;
lc   INT :=0;
warning INT;
BEGIN
  src:=BFILENAME('SCOTTDIR','2.doc');
  SELECT remark INTO des FROM BLOB_TABLE WHERE id=1 FOR UPDATE;
  DBMS_LOB.FILEOPEN(src,DBMS_LOB.FILE_READONLY);
  amount:=DBMS_LOB.GETLENGTH(src);
  DBMS_OUTPUT.PUT_LINE(amount);
  --DBMS_LOB.LOADBLOBFROMFILE (des,src,amount,des_offset,src_offset);
  DBMS_LOB.CLOSE(src);
END;