现在的位置: 首页 > 综合 > 正文

ORACLE LOB 大对象处理

2013年10月15日 ⁄ 综合 ⁄ 共 7228字 ⁄ 字号 评论关闭

LOB大对象处理: 主要是用来存储大量数据的数据库字段,最大可以存储4G字节的非结构化数据。 主要介绍字符类型和二进制文件类型LOB数据的存储,单独介绍二进制类型LOB数据的存储。

Oracle中的LOB数据类型分类
1,按存储数据的类型分:
字符类型:
   CLOB:存储大量 单字节 字符数据。
   NLOB:存储定宽 多字节 字符数据。
  二进制类型:
   BLOB:存储较大无结构的二进制数据。
二进制文件类型:
   BFILE:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。

2,按存储方式分:
存储在内部表空间:
    CLOB,NLOB和BLOB
  指向外部操作系统文件:
    BFILE

二,大对象数据的录入
2.1,声明LOB类型列
 
  Create TableSpace Dave  DataFile 'D:/Dave/dave.dbf' Size 20m;

  Create Table tLob (
     no Number(4),
     name VarChar2(10),
     resume CLob,
     photo BLob,
     record BFile
    )
  Lob (resume,photo)Store As (
   Tablespace dave  --指定存储的表空间
   Chunk 6k  --指定数据块大小
   Disable Storage In Row
  );

2,插入大对象列
先插入普通列数据
 
 
遇到大对象列时,插入空白构造函数。
   字符型:empty_clob(),empty_nclob()
   二进制型:empty_blob()
   二进制文件类型:BFileName函数指向外部文件。
     BFileName函数:
      BFileName(‘逻辑目录名’,‘文件名’);
      逻辑目录名只能大写,因为数据词典是以大写方式存储。Oracle是区分大小写的。
      在创建时,无需将BFileName函数逻辑目录指向物理路径,使用时才做检查二者是否关联。

  
将逻辑目录和物理目录关联。(如果是二进制文件类型)
   授予 CREATE ANY DIRECTORY 权限
    Grant  CREATE ANY DIRECTORY  TO 用户名 WITH ADMIN OPTION;
   关联逻辑目录和物理目录
    本地
    Create Directory  逻辑目录名  As  ‘文件的物理目录’;
    网络:
    Create Directory  逻辑目录名  As  ‘//主机名(IP)/共享目录’;
   例子:
    Create Directory  MYDIR As ' D:/Dave';
 
  插入例子:
/* Formatted on 2009-12-24 17:27:00 (QP5 v5.115.810.9015) */

INSERTINTOtlob

VALUES(1,

'Dave',

'CLOB大对象列',

EMPTY_BLOB(),

BFILENAME('MYDIR','Dave.jpg'));

INSERTINTOtlob

VALUES(2,

'Dave',

'CLOB大对象列',

EMPTY_BLOB(),

BFILENAME('MYDIR','Dave.jpg'));

INSERTINTOtlob

VALUES(3,

'Dave',

'CLOB大对象列',

EMPTY_BLOB(),

BFILENAME('MYDIR','Dave.jpg'));

三,大对象数据的读取和操作:DBMS_LOB
  DBMS_LOB包:包含处理大对象的过程和函数

  1,读取大对象数据的过程和函数
DBMS_LOB.Read():从LOB数据中读取指定长度数据到缓冲区的过程。
     DBMS_LOB.Read(LOB数据,指定长度,起始位置,

存储返回LOB类型值变量);
    例子:

/* Formatted on 2009-12-24 17:25:01 (QP5 v5.115.810.9015) */

DECLARE

varC    CLOB;

vRStr   VARCHAR2(1000);

LNNUMBER(4);

Strt    NUMBER(4);

BEGIN

SELECTresume

INTOvarC

FROMtlob

WHEREno=1;

LN:=
DBMS_LOB.GetLength
(varC);

Strt :=1;

DBMS_LOB.Read
(
varC,

LN,

Strt,

vRStr);

  DBMS_OUTPUT.put_line
(
'Return:  ' || vRStr);

END;
 
DBMS_LOB.SubStr():LOB数据中提取子字符串的函数。
     DBMS_LOB.SubStr(LOB数据,指定提取长度,提取起始位置):

     例子:
/* Formatted on 2009-12-24 17:24:35 (QP5 v5.115.810.9015) */

DECLARE

varC    CLOB;

vRStr   VARCHAR2(1000);

LNNUMBER(4);

Strt    NUMBER(4);

BEGIN

SELECTresume

INTOvarC

FROMtlob

WHEREno=1;

LN:=4;

Strt :=1;

vRStr :=
DBMS_LOB.SUBSTR
(varC,LN,
Strt
);

DBMS_OUTPUT.put_line
(
'结果为
' || vRStr);

END;
  
DBMS_LOB.InStr():从LOB数据中查找子字符串位置的函数。
    DBMS_LOB.InStr(LOB数据, 子字符串);

    例子:
   /* Formatted on 2009-12-24 17:25:39 (QP5 v5.115.810.9015) */

DECLARE

varC      CLOB;

vSubStr   VARCHAR2(1000);

vRStr     VARCHAR2(1000);

LNNUMBER(4);

BEGIN

SELECTresume

INTOvarC

FROMtlob

WHEREno=1;

vSubStr :='大对象';

LN:=
DBMS_LOB.INSTR
(varC, vSubStr);

DBMS_OUTPUT.put_line
(
'位置为
' || LN);

vRStr :=
DBMS_LOB.SUBSTR
(varC,LENGTH(vSubStr),LN);

DBMS_OUTPUT.put_line('位置为'

|| LN

                       ||
'
长度为'

|| LENGTH(vSubStr)

|| '的子字符串为:'

|| vRStr);

END;

  
DBMS_LOB.GetLength():返回指定LOB数据的长度的函数。
    DBMS_LOB.GetLength(LOB数据);
  
DBMS_LOB.Compare():比较二个大对象是否相等。返回数值0为相等,-1为不相等。
    DBMS_LOB.Compare(LOB数据,LOB数据);

    例子:
/* Formatted on 2009-12-24 17:26:19 (QP5 v5.115.810.9015) */

DECLARE

varC1   CLOB;

varC2   CLOB;

varC3   CLOB;

LNNUMBER(4);

BEGIN

SELECTresume

INTOvarC1

FROMtlob

WHEREno=1;

SELECTresume

INTOvarC2

FROMtlob

WHEREno=2;

SELECTresume

INTOvarC3

FROMtlob

WHEREno=3;

LN:=
DBMS_LOB.Compare
(varC1, varC1);

DBMS_OUTPUT.put_line
(
'比较的结果为
' || LN);

LN:=
DBMS_LOB.Compare
(varC2, varC3);

DBMS_OUTPUT.put_line
(
'比较的结果为
' || LN);

END;

  2,操作大对象数据的过程
   操作会改变数据库中原有数据,需要加上Updata锁锁上指定数据列,修改完后提交事务。
  
DBMS_LOB.Write():将指定数量的数据写入LOB的过程。
    DBMS_LOB.Write(被写入LOB, 写入长度(指写入LOB数据),写入起始位置(指被写入LOB),写入LOB数据);
  

例子:
   /* Formatted on 2009-12-24 17:43:38 (QP5 v5.115.810.9015) */

DECLARE

varC    CLOB;

vWStr   VARCHAR2(1000);

vStrt   NUMBER(4);

LNNUMBER(4);

BEGIN

vWStr :='CLOB';

LN:=LENGTH(vWStr);

vStrt :=5;

SELECTresume

INTOvarC

FROMtlob

WHEREno=1

FORUPDATE;

DBMS_LOB.Write
(
varC,

LN,

vStrt,

vWStr);

DBMS_OUTPUT.put_line
(
'改写结果为
' || varC);

COMMIT;

END;

  
DBMS_LOB.Append():将指定的LOB数据追加到指定的LOB数据后的过程。
    DBMS_LOB.Append(LOB数据,LOB数据);
    例子:
/* Formatted on 2009-12-24 17:44:17 (QP5 v5.115.810.9015) */

DECLARE

varC    CLOB;

vAStr   VARCHAR2(1000);

BEGIN

vAStr :=',这是大对象列';

SELECTresume

INTOvarC

FROMtlob

WHEREno=1

FORUPDATE;

DBMS_LOB.Append
(
varC, vAStr);

COMMIT;

DBMS_OUTPUT.put_line
(
'追加结果为
' || varC);

END;

  
DBMS_LOB.Erase():删除LOB数据中指定位置的部分数据的过程;
    DBMS_LOB.Erase(LOB数据,指定删除长度, 开始删除位置);
  

例子:
/* Formatted on 2009-12-24 17:44:45 (QP5 v5.115.810.9015) */

DECLARE

varC   CLOB;

LNNUMBER(4);

strt   NUMBER(4);

BEGIN

LN:=1;

strt :=5;

SELECTresume

INTOvarC

FROMtlob

WHEREno=1

FORUPDATE;

DBMS_LOB.ERASE
(
varC,LN, strt);

COMMIT;

DBMS_OUTPUT.put_line
(
'擦除结果为
' || varC);

END;
  
DBMS_LOB.Trim():截断LOB数据中从第一位置开始指定长度的部分数据的过程;
    DBMS_LOB.Trim(LOB数据,截断长度);
   

例子:
/* Formatted on 2009-12-24 17:45:09 (QP5 v5.115.810.9015) */

DECLARE

varC   CLOB;

LNNUMBER(4);

BEGIN

LN:=4;

SELECTresume

INTOvarC

FROMtlob

WHEREno=1

FORUPDATE;

DBMS_LOB.TRIM
(
varC,LN);

COMMIT;

DBMS_OUTPUT.put_line
(
'截断结果为
' || varC);

END;
  
DBMS_LOB.Copy():从指定位置开始将源LOB复制到目标LOB
    DBMS_LOB.Copy(源LOB,目标LOB,复制源LOB长度,复制到目标LOB开始位置,复制源LOB开始位置)

    例子:
  /* Formatted on 2009-12-24 17:45:30 (QP5 v5.115.810.9015) */

DECLARE

vDEST_LOB     CLOB;

vSRC_LOB      CLOB;

AMOUNT        NUMBER;

DEST_OFFSET   NUMBER;

SRC_OFFSET    NUMBER;

BEGIN

SELECTresume

INTOvDEST_LOB

FROMtlob

WHEREno=1

FORUPDATE;

SELECTresume

INTOvSRC_LOB

FROMtlob

WHEREno=2;

AMOUNT :=
DBMS_LOB.GetLength
(vSRC_LOB);

DEST_OFFSET :=
DBMS_LOB.GetLength
(vDEST_LOB)+1;

SRC_OFFSET :=1;

DBMS_LOB.COPY
(
vDEST_LOB,

vSRC_LOB,

           AMOUNT,

DEST_OFFSET,

SRC_OFFSET);

DBMS_OUTPUT.put_line
(
'拷贝结果为
' || vDEST_LOB);

END;

三, 图片的存储或二进制文件的存储


1
,先插入普通数据,遇到大对象列使用empty_blob()构造空的指针。
例子:

/* Formatted on 2009-12-24 18:02:34 (QP5 v5.115.810.9015) */

CREATETABLESPACE Dave 
DATAFILE'D:/Dave/dave.dbf'SIZE20M;

CreateTable MyLob

(

nonumber(8)primarykey,

fname varchar2(30),

myfile blob

)

Lob(myfile)StoreAs

(

Tablespace Dave

Chunk15K

DisableStorageInRow

);

INSERTINTO MyLob

VALUES(1,'IMG_0210.JPG',EMPTY_BLOB());

2,创建逻辑目录MYDIR
  Create Directory  MYDIR As ' D:/Dave';

3,声明一个BLOB类型变量,使用select into
语句让其指向到empty_blob()构造空的指针所指向的存储空间
/* Formatted on 2009-12-24 18:07:25 (QP5 v5.115.810.9015) */

SELECTmyfile

INTOBLOB类型变量

FROMmyLob

WHEREno=1

FORUPDATE;

4,声明一个BFile类型变量,关联逻辑目录和物理目录文件,使用 BFileName() 将其指向到待存储的文件。
    BFile类型变量 := BFileName('MYDIR','IMG_0210.JPG');

5,使用DBMS_LOB.open()方法将BFile类型变量所指向的文件打开
    DBMS_LOB.Open(BFile类型变量);

6,使用DBMS_LOB.loadfromfile()方法将BFile类型变量所指向的文件读入到BLOB类型变量所指向的存储空间
    DBMS_LOB.LoadfromFile(BLOB类型变量,BFile类型变量,DBMS_LOB.getlength(BFile类型变量));

7,使用DBMS_LOB.close()方法将bfile的变量所指向的文件关闭
    DBMS_LOB.Close(BFile类型变量);

8,提交事务
    Commit;

例子;
  Declare
     varB blob;
     varF Bfile;
  Begin
     select myfile into varB from myLob where no = 1 for update;
     varF := bfileName('MYDIR','Dave.JPG');
     DBMS_LOB.open(varF);
    DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
     DBMS_LOB.close(varF);
     commit;
  End;
 
  --查看文件大小
  Declare
     varB blob;
  Begin
     select myfile into varB from myLob where no = 1;
     DBMS_OUTPUT.PUT_LINE('
长度为: '||DBMS_LOB.getlength(varB));
  End;

例子:创建过程存储图片或二进制文件
  Create Or Replace Procedure setBLOB(vFileName varchar2)
  As
     varF bfile;
     varB blob;
     vno number(8);
  Begin
     varF := BFilename('MYDIR',vFileName);
     DBMS_LOB.Open(varF);
     select max(no) into vno from myLob;
     if vno is null then
       vno := 1;
     else
       vno := vno + 1;
     end if;
     insert into myLob values(vno,vFileName,empty_blob());
     select myFile into varB from myLob where no = vno for update;
     DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
     DBMS_LOB.close(varF);
     commit;
  End;
 
  --执行过程
  EXEC setBLOB('Dave.JPG');
 
  --查看文件大小
  Declare
     varB blob;
  Begin
     select myfile into varB from myLob where no = 2;
     DBMS_output.put_line('
长度为: '||DBMS_LOB.getlength(varB));
  End;

【上篇】
【下篇】

抱歉!评论已关闭.