1、LOB概念

大对象类型用于存储非结构化的大数据，比如大文本、图片、电影、音乐等
Oracle数据库里面的LOB有四种类型：
1、CLOB ：字符大对象，存储在数据库内部；
2、NCLOB：多字节字符大对象，存储在数据库内部；
3、BLOB：二进制大对象，存储在数据库内部；
4、BFILE：二进制文件，存储在数据库外部；

2、LONG 和 LOB的区别

LONG and LONG RAW LOB
Single LONG column per table Multiple LOB columns per table
Up to 2 GB Up to 4 GB
SELECT returns data SELECT returns locator
Data stored in-line Data stored in-line or out-of-line
Sequential access to data Random access to data

Long 和 Long RAW 是Oracle 9i以前的版本使用的大对象类型；在9i以后都建议使用 LOB，Oracle 9i 也提供了一系列函数用以从LONG 升级到LOB OUT-of-LINE 的理解： LOB可以是对象类型的属性，而LONG不行；
LOB数据的存储方式：LOB 分为 Value 和 Locator 两部分，在数据库表上的LOB字段，肯定会存储 LOB的Locator; 至于LOB的value，则要看其内外部类型和大小决定存储位置，默认情况下，内部LOB小于 4000字节会被存储在同一行上，超过部分则会存储在数据库内部的其他地方，这都有Oracle数据库自动管理。

3、内部LOB的一般操作步骤为

1、在表中添加LOB类型的列
2、在程序中声明和初始化LOB的Locator
3、使用 SELECT FOR UPDATE 锁定目标行，准备更新行上的LOB列（LOB的Locator）
4、生成LOB对象，可使用DBMS_LOB 这样的PLSQL包，也可以使用OCI，JDBC等；
5、Commit 提交更改；

4、外部LOB Bfile的操作

Bfile是数据库外部文件，在数据库表上这种类型的字段实际只是存储一个Locator；
Bfile的使用限制：Bfile是数据库外部文件，是只读的，所以不参与事务操作；用户必须先创建文件并放到 特定的目录下，给予Oracle进程以目录和文件的读取权限；
在Oracle 中删除Bfile这样的LOB数据的时候，它并没有实际的去删除对应的操作系统上的文件，实际文件 的删除是DBA，系统管理员的工作，Bfile的大小限制取决于操作系统，不受Oracle限制。

5、Oracle Directory

为了便于控制Bfile存储的安全性，Oracle 数据库引入了Oracle Directory的 概念；
在Oracle 内部创建的 Directory 默认的所有者是sys，并有DBA( 或者是另一个拥有 CREATE ANY DIRECTORY 权限的用户)创建；Directory 对象可以像表那样给其他用户赋权。

6、使用Bfile的一般步骤

1、在操作系统上创建目录，并给Oracle数据库进程赋予阅读权限，把外部文件放入这个目录
2、在Oracle数据库中表添加Bfile类型字段

  1. ALTER TABLE employees
  2.  ADD emp_video BFILE;

3、在Oracle 数据库中创建Directory 对象

  1. CREATE DIRECTORY dir_name
  2.  AS os_path;

4、授权读权限给特定的数据库用户

  1. GRANT READ ON DIRECTORY dir_name TO user|role|PUBLIC;

5、往表中插入数据时使用 BFILENAME 函数，它可以关联外部文件和表上的Bfile列

  1. FUNCTION BFILENAME (directory_alias IN VARCHAR2,
  2.  filename IN VARCHAR2)
  3. RETURN BFILE;

6、在程序中声明和初始化LOB的Locator
7、Select 指定行上 Bfile 列 到Locator
8、使用 DBMS_LOB 或者通过 OCI 读取Bfile (使用Locator作为文件的一个引用）

  1. CREATE OR REPLACE PROCEDURE load_emp_bfile
  2.   (p_file_loc IN VARCHAR2) IS
  3.    v_file BFILE;
  4.    v_filename VARCHAR2(16);
  5.    CURSOR emp_cursor IS
  6.    SELECT first_name FROM employees
  7.    WHERE department_id = 60 FOR UPDATE;
  8. BEGIN
  9.    FOR emp_record IN emp_cursor LOOP
  10.    v_filename := emp_record.first_name || '.bmp';
  11.    v_file := BFILENAME(p_file_loc, v_filename);
  12.    DBMS_LOB.FILEOPEN(v_file);
  13.    UPDATE employees SET emp_video = v_file
  14.    WHERE CURRENT OF emp_cursor;
  15.    DBMS_OUTPUT.PUT_LINE('LOADED FILE: '||v_filename
  16.    || ' SIZE: ' || DBMS_LOB.GETLENGTH(v_file));
  17.    DBMS_LOB.FILECLOSE(v_file);
  18.    END LOOP;
  19. END load_emp_bfile;
  20. /

7、使用DBMS_LOB.FILEEXISTS测试文件是否存在

  1. CREATE OR REPLACE PROCEDURE load_emp_bfile
  2.     (p_file_loc IN VARCHAR2)
  3. IS
  4.    v_file BFILE; v_filename VARCHAR2(16);
  5.    v_file_exists BOOLEAN;
  6.    CURSOR emp_cursor IS ...
  7. BEGIN
  8.    FOR emp_record IN emp_cursor LOOP
  9.    v_filename := emp_record.first_name || '.bmp';
  10.    v_file := BFILENAME (p_file_loc, v_filename);
  11.    v_file_exists := (DBMS_LOB.FILEEXISTS(v_file) = 1);
  12.    IF v_file_exists THEN
  13.    DBMS_LOB.FILEOPEN (v_file); ...

8、DBMS_LOB主要函数介绍

1、更改LOB的值： APPEND, COPY, ERASE, TRIM, WRITE, LOADFROMFILE
2、读取、检查LOB的值： GETLENGTH, INSTR, READ, SUBSTR
3、Bfile专用：FILECLOSE, FILECLOSEALL, FILEEXISTS, FILEGETNAME, FILEISOPEN, FILEOPEN

A.最常用的Read 和Write介绍

  1. PROCEDURE READ (
  2.                 lobsrc IN BFILE|BLOB|CLOB ,
  3.                 amount IN OUT BINARY_INTEGER,
  4.                 offset IN INTEGER,
  5.                 buffer OUT RAW|VARCHAR2 )
  1. PROCEDURE WRITE (
  2.                 lobdst IN OUT BLOB|CLOB,
  3.                 amount IN OUT BINARY_INTEGER,
  4.                 offset IN INTEGER := 1,
  5.                 buffer IN RAW|VARCHAR2 ) -- RAW for BLOB

9、数据库表中LOB列的增删改

下面的例子中resume是CLOB类型，Picture是BLOB类型

A.新增

  1. INSERT INTO employees (employee_id, first_name，last_name, email,
  2. hire_date, job_id, salary, resume, picture)
  3. VALUES (405, 'Marvin', 'Ellis', 'MELLIS', SYSDATE, 'AD_ASST',
  4. 4000, EMPTY_CLOB(),NULL);

B.更新

  1. UPDATE employees
  2. SET resume = 'Date of Birth: 8 February 1951',
  3.  picture = EMPTY_BLOB()
  4. WHERE employee_id = 405;
  1. UPDATE employees
  2. SET resume = 'Date of Birth: 1 June 1956'
  3. WHERE employee_id = 170;

注意： **EMPTY_CLOB()**，**EMPTY_BLOB()** 跟NULL是不同的概念，ISNULL 对这两种情况返回FALSE

  1. DECLARE
  2.    lobloc CLOB; -- serves as the LOB locator
  3.    text VARCHAR2(32767):='Resigned: 5 August 2000';
  4.    amount NUMBER ; -- amount to be written
  5.    offset INTEGER; -- where to start writing
  6. BEGIN
  7.    SELECT resume INTO lobloc
  8.    FROM employees
  9.    WHERE employee_id = 405 FOR UPDATE;
  10.    offset := DBMS_LOB.GETLENGTH(lobloc) + 2;
  11.    amount := length(text);
  12.    DBMS_LOB.WRITE (lobloc, amount, offset, text );
  13.    text := ' Resigned: 30 September 2000';
  14.    SELECT resume INTO lobloc
  15.    FROM employees
  16.    WHERE employee_id = 170 FOR UPDATE;
  17.    amount := length(text);
  18.    DBMS_LOB.WRITEAPPEND(lobloc, amount, text);
  19.    COMMIT;
  20. END;

C.查询

  1. SELECT employee_id, last_name , resume -- CLOB
  2. FROM employees
  3. WHERE employee_id IN (405, 170);

LOB内容截取常用函数

  1. DBMS_LOB.SUBSTR(lob_column, no_of_chars, starting)
  2. DBMS_LOB.INSTR (lob_column, pattern)
  1. SELECT DBMS_LOB.SUBSTR (resume, 5, 18),
  2.  DBMS_LOB.INSTR (resume,' = ')
  3. FROM employees
  4. WHERE employee_id IN (170, 405);

D.删除

  1. DELETE
  2. FROM employees
  3. WHERE employee_id = 405;

删除一行的时候，该行上的内部LOB对象也被删除。 如果只是想删除LOB列，而不想删除整行的数据，那么应该使用UPDATE 语句，是LOB列=NULL 或者 EMPTY_C/BLOB();

  1. UPDATE employees
  2. SET resume = EMPTY_CLOB()
  3. WHERE employee_id = 170;