oracle数据库中的大对象1——永久性的

简介: 整理自丁俊老师plsql文档: 1、基本介绍 oracle和pl/sql都支持lob(large object)类型,用来存储大数量数据,如图像文件,声音文件等。

整理自丁俊老师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;




相关文章
|
2天前
|
存储 Oracle 关系型数据库
Oracle数据库快速入门
Oracle数据库快速入门
7 0
|
4天前
|
存储 Oracle 关系型数据库
|
6天前
|
SQL Oracle 关系型数据库
探索 Linux 命令 `db_archive`:Oracle 数据库归档日志的工具
探索 Linux 中的 `db_archive`,实际与 Oracle 数据库归档日志管理相关。在 Oracle 中,归档日志用于恢复,当在线重做日志满时自动归档。管理员可使用 SQL*Plus 查看归档模式,通过 `RMAN` 进行备份和恢复操作。管理归档日志需谨慎,避免数据丢失。了解归档管理对 Oracle 管理员至关重要,确保故障时能快速恢复数据库。
|
8天前
|
SQL Oracle 关系型数据库
改变Oracle数据库连接端口
改变Oracle数据库连接端口
16 4
|
Oracle 关系型数据库 数据库
oracle数据库控制文件的备份和恢复之三RMAN自动备份和恢复
使用RMAN自动备份的控制文件向数据库中恢复控制文件
326 0
|
Oracle 关系型数据库 数据库
|
SQL Oracle 关系型数据库
oracle数据库控制文件的备份和恢复之一手动备份和恢复
实验步骤:手动备份和恢复oracle控制文件
534 0
|
Oracle 关系型数据库 数据库

推荐镜像

更多