oracle学习笔记(第十六章:oracle大对象)

-- 大对象的使用
-- oracle 支持以下4种LOB数据类型
--LOB数据类型-------------------------------说明------------------------------------------------------
-- BLOB                                     二进制LOB.存储在数据库中的二进制数据
-- CLOB                                     字符LOB.存储在数据库中的字符型数据
-- BFILE                                    二进制文件.存储在数据库外部的只读二进制数据,其长度受操作系统限制
-- NCLOB                                    支持多字符集的CLOB
-- 可以在数据库中创建多个LOB 数据类型
-- 不能在一个表中创建多个 LONG 或 LONG RAW

CREATE TABLE LOB_TEST(TEST_ID CHAR(5) PRIMARY KEY,
                      TEST_CLOB CLOB,
                      TEST_BLOB BLOB,
                      TEST_BFILE BFILE)
                      TABLESPACE LYJDB
                      LOB(TEST_CLOB,TEST_BLOB) STORE AS
                      (TABLESPACE LYJDB
                      STORAGE(INITIAL 500K NEXT 500K PCTINCREASE 0 )
                      CHUNK 64 PCTVERSION 40 NOCACHE LOGGING);
-- DROP TABLE LOB_TEST
----------------------------
INSERT INTO LOB_TEST(TEST_ID,TEST_CLOB,TEST_BLOB,TEST_BFILE)
VALUES('00001','CLOB TEST',EMPTY_BLOB(),NULL);

INSERT INTO LOB_TEST(TEST_ID,TEST_CLOB,TEST_BLOB,TEST_BFILE)
VALUES('00007','CLOB TEST2',EMPTY_BLOB(),BFILENAME('lob_test_dir','test.doc'));

INSERT INTO LOB_TEST(TEST_ID,TEST_CLOB,TEST_BLOB,TEST_BFILE)
VALUES('00003','CLOB TEST3',EMPTY_BLOB(),NULL);

INSERT INTO LOB_TEST(TEST_ID,TEST_CLOB,TEST_BLOB,TEST_BFILE)
SELECT '00004','CLOB_TEST4',TEST_BLOB,TEST_BFILE FROM LOB_TEST WHERE TEST_ID = '00003';
-- 使用 子查询插入数据 lob 列N设为空定位器
-- 设置LOB列的空定位器
BLOB    使用EMPTY_BLOB()
CLOB    使用empty_clob()
NCLOB   使用empty_clob()
BFILE   使用bfilename
--在更新bfile列的null值时,不用设置bfile列值为空定位器 ;若更新blog,clob,nclob列的null值时
--需要将null设置为空定位器之后在更新
------------------------
可以使用bfilename过程指向一个目录或文件名的组合.
在向bfilename函数中键入一个目录值前,具有DBA角色或 CREATE  ANY DIRECTORY 系统权限的用户必须创建该目录
创建一个目录的命令:
CREATE DIRECTORY lob_test_dir AS 'F:\LOB_DIR'
--DROP DIRECTORY lob_test_dir
----------------------------
-- 可以对CLOB的值使用oracle提供的串函数
SELECT substr(test_clob,1,4) FROM lob_test
-- 不可以在CLOB列上使用nvl()和decode函数
-- 使用dbms_log 程序包处理lob值
-- 子程序 ------------------------- 说明 ------------------------------
append 过程                         将源LOB内容附加到目的LOB
CLOSE 过程                          关闭前面打开的内部或外部 LOB
compare函数                         比较两个lob或lob组成的不分
copy 过程                           将全部或部分lob复制到目标lob
erase 过程                          删除全部或部分LOB值
fileclose过程                       关闭文件
filecloseall过程                    关闭以前打开的所有文件
filegetname过程                     获取目录别名和文件名
fileisopen函数                      检查文件是否已经使用输入bfile定位器打开
fileopen过程                        打开文件
getchunksize函数                    返回在lob块中用来存储lob值的空间量
getlength函数                       取lob值长度
ISOPEN 函数                         确定lob是否已经输入定位器打开
OPEN 过程                            以指定的方式打开lob( 内部或外部或临时 )
READ 过程                           以给定的偏移量开始初读取lob数据
WRITE 过程                          从指定的偏移量值处将数据写入lob
writeappend 过程                    将某个缓冲区写入一个lob的末尾
------------------------------------------------------------------------------------
-- read 函数的使用 ,需要使用 pl/sql 块,其结构如下
DECLARE
       VARIABLE TO hold LOCATOR VALUE;
       VARIABLE TO hold THE amount(THE NUMBER OF characters/bytes TO READ);
       VARIABLE TO hold THE offset;
       VARIABLE TO hold THE output;
BEGIN
       SET VALUE FOR amount_var;
       SET VALUE FOR offset_var;
       SELECT LOCATOR VALUE INTO LOCATOR var FROM TABLE;
       dbms_lob.READ(LOCATOR var,amount var,offset var,output var);
       dbms_output.put_line('output:'||);
END
--------------------
DECLARE
        locator_var CLOB;
        amount_var INTEGER;
        offset_var INTEGER;
        output_var VARCHAR2(10);
BEGIN
        amount_Var := 10;--读取长度为10
        offset_var := 1;--从第一个字符开始
        SELECT test_clob INTO locator_var FROM lob_test WHERE test_id = '00003';
        dbms_lob.read(locator_var,amount_var,offset_var,output_var);
        dbms_output.put_line('START of test_clob :'||output_var);  
END;
--------------------------
create   table   man(map   blob);  

create   or   replace   directory   "MyFilePath"   as   'F:\' 

create or replace directory DIR_TEST as 'F:\BOOKS\Oracle\';

declare
  a_blob  blob;
  a_bfile bfile := bfilename('JAVA_DIR', 'toexcel.java'); -- dir_test  directory 名称区分大小写, label.txt 文件名称不区分大小写
begin
  if dbms_lob.fileexists(a_bfile) <> 0 then
insert into blobtest
(id, ablob)
values
(22, empty_blob())
returning ablob into a_blob;
dbms_lob.fileopen(a_bfile);
dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
dbms_lob.fileclose(a_bfile);
commit;
  else
dbms_output.put_line('file not exits');
  end if;
end;
-------------------------------------------------------
CREATE OR REPLACE DIRECTORY TEST_DIR AS 'F:\LOB_DIR\';
--
DECLARE
   V_BLOB BLOB;
   V_CLOB CLOB;
   V_BFILE BFILE;
   V_BLOB1 BLOB := BFILENAME('TEST_DIR','BLOB.TXT');
   V_CLOB1 CLOB := BFILENAME('TEST_DIR','CLOB.TXT');
   V_BFILE1 BFILE := BFILENAME('TEST_DIR','TEST.DOC');
BEGIN
   DBMS_LOB.FILEOPEN(V_BLOB1);
   DBMS_LOB.loadfromfile(V_BLOB,V_BLOB1,DBMS_LOB.GETLENGTH(V_BLOB1));
   DBMS_LOB.fileclose(V_BLOB1);

   DBMS_LOB.FILEOPEN(V_CLOB1);
   DBMS_LOB.loadfromfile(V_CLOB,V_CLOB1,DBMS_LOB.GETLENGTH(V_CLOB1));
   DBMS_LOB.fileclose(V_CLOB1);

   DBMS_LOB.FILEOPEN(V_BFILE1);
   DBMS_LOB.loadfromfile(V_BFILE,V_BFILE1,DBMS_LOB.GETLENGTH(V_BFILE1));
   DBMS_LOB.fileclose(V_BFILE1);
INSERT INTO LOB_TEST VALUES( TEST_ID='00008',
                                V_CLOB,
                                V_BLOB,
                                V_BFILE);
END ;
---
SELECT * FROM LOB_TEST


-- 文件的输入输出
-- UTL_FILE,类似客户端的包 text_io
-- 打开和关闭文件 fopen ,fclose
-- function fopen(location in varchar2,filename in varchar2,open_mode in varchar2[,max_linesize] in binary_integer) return file_type;
-- 参数说明: location      文件位于目录的路径,如果该目录与可访问的目录表中的目录不匹配,则将引发异常:utl_file.invalid_path
--           filename      要打开的文件名,如果open_mode为"w",则将现有的文件覆盖
--           open_mode     打开方式:r 读文本,w 写文本, a 追加文本,该参数对大小写敏感.如果指定为 a 方式,但文件不存在,则按 w 方式创建新文件
--           max_linesize  文件的最大行数
--           return value  后续使用的文件句柄
-- function fclose(file_handle in out file_type);file_handle 文件句柄
-- is_open() 返回文件处于打开的状态
-- function is_open(file_handle in file_type) return boolean
-- fclose_all 关闭所有打开的文件
-- 文件输出:有五个过程可以用来把数据输出到文件中:put,pur_line,new_line,putf,fflush
-- 其中put,put_line,new_line输出的最大容量为1023(可以使用foepn说明新的容量值)
-- put 将字符串输出到指定的文件中,该文件在执行put操作前应处于打开状态
-- procedure put(file_handle in file_type,buffer in varchar2);
-- buffer 等待写入文件中的字符串,如果没有使用 w , 或 a 方式打开,则引发异常utl_file.invalid_filehandle
-- put 将不在该文件追加新行字符如果需要,则使用put_line或new_line在行中加入行结束符
-- new_line 将一个或多个行结束符写入指定的文件中?
-- procedure new_line( file_handle in file_type , lines in natural := 1 );
-- lines 结束符的个数
-- put_line 将字符串输出到指定的文件中,并在字符串后加上行结束符,被写入的文件必须是以写的方式打开
-- procedure put_line(file_handle in file_type,buffer in varchar2);
-- putf() 与put类似,但是容许对输出的字符串格式化
-- procedure putf(file_handle in file_type,format in varchar2,
--                [ arg1 in varchar2 default null,arg2 in varchar2 default null,arg3 in varchar2 default null,arg4 in varchar2 default null,arg5 in varchar2 default null ])
-- 格式字符串format除了带有正常的文本外,还有两个特殊的字符 %s,\n .在格式字符串中出现 %s 的地方可以用参数arg1 -- arg5 代替,
-- 出现 \n 的地方可以用一个新的行结束符代替
-- arg1 -- arg5 这五个可选参数将被对应 %s 的格式字符代替
--例子
DECLARE
v_outputfile utl_file.file_type;
v_name VARCHAR2(10):='scott';
BEGIN
     v_outputfile := utl_file.fopen(...);
     utl_file.putf(v_outputfile,'Hi there !\nMy name is %s, and I am a %s major.\n',v_name,'computer')
     fclose(v_outputline);
END;
--则输出结果为:
-- Hi there !
-- My name is scott, and I am a computer major.
---fflush ; put,put_line,putf,new_line,输出的字符通常是存储在缓冲区中,当缓冲区满了,缓冲区的字符将输入到文件中
-- fflush 直接将字符输入到缓冲区中.
-- procedure fflush(file_hanle in file_type)
-- 文件的输入 get_line:是用来重文件中执行读入操作,对过程每次从指定的文件中读入一行指定的文本,
-- 并将该文本写入缓冲区,新行字符不包括返回的字符串中,输入行最大长度为1022可以使用fopen的参数max_linesizes指定最大行
-- procedure get_line(file_handle in file_type,buffer out varchar2)
--
DECLARE
v_outputfile utl_file.file_type;
BEGIN
     v_outputfile := utl_file.fopen('DIR_TEST','Label.txt','a');--向 label.txt 文件写入数据(w覆盖,r读,a追加)，
     utl_file.put_line(v_outputfile,'my name is 李永结!');  -- 写入数据的内容
     utl_file.fclose(v_outputfile);
     EXCEPTION
               WHEN utl_file.invalid_path THEN
                    raise_application_error(-20100,'open:inivalid path');
               WHEN utl_file.invalid_mode THEN
                    raise_application_error(-20100,'open:inivalid mode');
               WHEN utl_file.invalid_operation THEN
                    raise_application_error(-20100,'open:inivalid peration');
               WHEN utl_file.internal_error THEN
                    raise_application_error(-20100,'open:inivalid error');
END;