一:存储二进制数据
参考: https://www.2cto.com/database/201202/118146.html
例:
1.使用管理员账户登录
2.创建一个目录比如: F:\b_lob_dir\111.png
3.指定映射目录
create or replace directory lobFiles as ‘F:\b_lob_dir’;
4.将目录的读写权限授予scott用户
grant read on directory lobFiles to SCOTT;
grant write on directory lobFiles to SCOTT;
5.切换为scott用户
drop table cob_tea_demo;
create table cob_tea_demo(
cid varchar(60) primary key,
cname varchar2(50),
note clob,
photo blob
);
6.开始插表
declare
v_photo cob_tea_demo.photo%type;
v_srcFile bfile; --文件定位
v_pos_length integer;
begin
insert into cob_tea_demo(cid,cname,note,photo)
values(sys_guid(),'文件名','笔记.....',empty_blob())
return photo into v_photo;
v_srcFile:= bfilename(upper('lobFiles'),'11.png');
--长度
v_pos_length:=dbms_lob.getlength(v_srcFile);
--打开文件
dbms_lob.fileopen(v_srcFile,dbms_lob.file_readonly);
--保存
dbms_lob.loadfromfile(v_photo,v_srcFile,v_pos_length);
--关闭
dbms_lob.fileclose(v_srcFile);
end;
--查看
select * from cob_tea_demo;
--如果找不到指定的文件,会报错
二 存储文本数据
例:跟上面的clob写法一致
--上面的步骤可以看到,创建映射目录只有管理员才有权限,导致后面的一系列授权
--其实可以把创建目录的权限直接授权给用户
grant create any directory to SCOTT;
--此时的scott就可以创建目录了
create or replace directory lobFiles as 'F:\b_lob_dir';
declare
v_note cob_tea_demo.note%type;
v_srcFile bfile; --文件定位
begin
insert into cob_tea_demo(cid,cname,note,photo)
--如果在plsql中执行,版本:PLSQL Developer11 64位
--经过测试,如果你不想添加photo值,那么使用empty_blob(),查询直接崩溃
--如果是用了null,那么查询后.点击进去直接崩溃
--如果使用 to_blob('11'),也是崩溃
--但是在命令行里面是正常查询的,这个估计跟plsql的查询集封装有关
values(sys_guid(),'文件名',empty_clob(),empty_blob())
return note into v_note;
v_srcFile:= bfilename(upper('lobFiles'),'222.txt');
--打开文件
dbms_lob.fileopen(v_srcFile);
--保存
dbms_lob.loadfromfile(v_note,v_srcFile,dbms_lob.getlength(v_srcFile));
--关闭
dbms_lob.fileclose(v_srcFile);
commit;
end;
select * from cob_tea_demo;
会发现存在乱码问题,默认的txt是ANSI编码的,比如GBK,UNicode等都会乱码,
出现乱码的根本原因是dbms_lob包里的loadfromfile过程不支持变长的字符集,比如GBK,UTF8等。解决方法的可以使用dbms_lob包里的另外一个过程loadclobfromfile过程,因为此过程中有个参数叫BFILE_CSID,这个参数可以用来指定bfile文件使用的字符集编码。使用方法如下:
参考: http://blog.itpub.net/9240380/viewspace-745243/
解决方法如下:
1.准备工作
数据库服务器字符集
select * from nls_database_parameters,其来源于props$,是表示数据库的字符集。
客户端字符集环境
select * from nls_instance_parameters;
–查询oracle server端的字符集
–有很多种方法可以查出oracle server端的字符集,比较直观的查询方法是以下这种:
select userenv(‘language’) from dual;
–过程参数
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);
2.开始解决
create table clobtest(
note clob
);
create or replace directory lobFiles as 'F:\b_lob_dir';
declare
v_note cob_tea_demo.note%type;
v_bfile bfile := bfilename('LOBFILES', '222.txt');
dest_offset number := 1;
source_offset number := 1;
--src_csid number := NLS_CHARSET_ID('UTF8');
src_csid number := NLS_CHARSET_ID('ZHS16GBK');
lang_ctx integer := dbms_lob.default_lang_ctx;
warn integer;
begin
--使用returning 也行
insert into clobtest values (empty_clob()) returning note into v_note;
dbms_lob.fileopen(v_bfile);
dbms_lob.loadclobfromfile(v_note,
v_bfile,
DBMS_LOB.LOBMAXSIZE,
dest_offset,
source_offset,
src_csid,
lang_ctx,
warn);
dbms_lob.fileclose(v_bfile);
commit;
end;
/
--查询,此时就是中文的
select * from clobtest;
三 读文本
--上面的clobtest表已经创建
--这个使用可以参考:https://msdn.microsoft.com/zh-cn/library/system.data.oracleclient.oraclelob.read.aspx
--RAW,类似于CHAR,声明方式RAW(L),L为长度,以字节为单位,作为数据库列最大2000,作为变量最大32767字节
PROCEDURE read(lob_loc IN BLOB,
amount IN OUT NOCOPY INTEGER,
offset IN INTEGER,
buffer OUT RAW);
----------------------------------------------------------
declare
v_clob clob;
v_buffer varchar2(1000);
--要读取的数据长度
v_length number(4):=500;
--不为设置为0,如果设置为2将会忽略第一个字符,可以理解成文件读取的起点偏移量
v_offset number(4) :=1;
--控制偏移量
v_count pls_integer :=1;
--循环次数
v_loop_num number;
begin
select a.note
into v_clob
from clobtest a
where a.cid = 'E6DE99E97D624EFAB9CFA5A6771F38D3';
--获取长度,如果文件小,可以一次性读
-- v_length := dbms_lob.getlength(v_clob);
--如果文件比较大,需要分段读取
select ceil(dbms_lob.getlength(v_clob)/v_length) into v_loop_num from dual;
--如果要分段读取,要控制偏移量
--如果使用v_offset<=v_file_length(文件总长度)来控制,会有问题,一直在变,原因还没找到...
--这里采用确定循环次数的方法
while(v_loop_num>0) loop
dbms_lob.read(v_clob, v_length, v_offset, v_buffer);
dbms_output.put_line('<读取开始>' || v_buffer);
v_offset:=v_length*v_count+1;
v_count :=v_count+1;
v_loop_num:=v_loop_num-1;
end loop;
end;