130829大对象

创建大对象表

SQL> create table clob_content (
  2  id integer primary key,
  3  clob_column clob not null );
 
Table created

SQL> create table blob_content (
  2  id integer primary key,
  3  blob_column blob not null );
 
Table created

SQL> create table bfile_content (
  2  id integer primary key,
  3  bfile_column bfile not null );
 
Table created

分别测试bfile、clob、blob；

在向lob中写入内容之前，必须首先初始化lob列；

SQL> insert into calvin.clob_content ( id, clob_column ) 
  2  values (1, empty_clob() );
 
1 row inserted
 
SQL> insert into calvin.blob_content( id, blob_column)
  2  values (1,empty_blob());
 
1 row inserted

使用update将两个表中的lob更新；

SQL> update calvin.clob_content set clob_column = 'Creeps in this petty pace' where id = 1;
 
1 row updated
 
SQL> update blob_content set blob_column = '1001110101011' where id =1;
 
1 row updated
 
SQL> commit;
 
Commit complete
 
SQL> select * from clob_content;
 
                                     ID CLOB_COLUMN
--------------------------------------- --------------------------------------------------------------------------------
                                      1 Creeps in this petty pace
 
SQL> select * from blob_content;
 
                                     ID BLOB_COLUMN
--------------------------------------- -----------
                                      1 <BLOB>

sqlplus中无法显示blob中的具体内容；

测试bfile；

bfile lob类型可以存储指向文件的指针，这些文件可以通过计算机系统访问；

1.创建目录对象，需要create any directory 权限；

SQL> conn /@test as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as [calvin]@TEST AS SYSDBA
 
SQL> grant create any directory to calvin;
 
Grant succeeded
 
SQL> grant create directory to calvin;
 

SQL> conn calvin/123456@test
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as calvin@TEST
 
SQL> create or replace directory sample_dir as 'd:\sample_dir';
 
Directory created
 

2.插入bfile行；

 
SQL> insert into bfile_content ( id, bfile_column )
  2  values ( 1, bfilename('SAMPLE_DIR', 'out1.txt'));
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from calvin.bfile_content;
 
                                     ID BFILE_COLUMN
--------------------------------------- ------------
                                      1 <BFILE>
 

sqlplus中无法留恋bfile具体内容；可以使用plsql查看；

使用plsql阅读clob和blob内容；

SQL> create or replace procedure read_clob( id_par in integer )
  2  is clob_var clob;
  3  char_buffer_var varchar2(50);
  4  offset_var integer :=1;
  5  amount_var integer :=50;
  6  begin
  7    select clob_column into clob_var from calvin.clob_content where id = id_par;
  8  
  9    dbms_lob.read(clob_var,amount_var, offset_var, char_buffer_var);
 10    dbms_output.put_line('char_buffer_var' || char_buffer_var);
 11    dbms_output.put_line('amount_var' || amount_var);
 12  end read_clob;
 13  /
 
Procedure created

测试读出结果；

SQL> set serveroutput on;
SQL> call calvin.read_clob(1);
char_buffer_varCreeps in this petty pace
amount_var25

Call completed.

SQL> create or replace procedure read_blob(
  2  id_par in integer )
  3  is blob_var blob;
  4  binary_buffer_var raw(25);
  5  offset_var integer :=1;
  6  amount_var integer :=25;
  7  begin
  8    select blob_column into blob_var from calvin.blob_content where id = id_par;
  9    dbms_lob.read(blob_var, amount_var, offset_var, binary_buffer_var);
 10    dbms_output.put_line('bindary_buffer_var = ' || binary_buffer_var);
 11    dbms_output.put_line('amount_var = ' || amount_var);
 12  end read_blob;
 13  /
 
Procedure created
 

读出blob测试结果；

SQL> call calvin.read_blob(1);
bindary_buffer_var = 01001110101011
amount_var = 7

Call completed.