PL/SQL高级应用
动态SQL
在PL/SQL中,不能直接执行DDL(create,alter,drop),得使用动态SQL,当然,除了DDL,动态SQL也可以执行DML(select,insert,update,delete)
语法:
复制EXECUTE IMMEDIATE '动态SQL语句'
[INTO define_variable_list]
[USING bind_argument_list];
例子:
复制--创建一个表
declare
v_sql string(200);
begin
v_sql := '
create table my_diagrams(
diagram_descr VARCHAR2(40),
diagram_no integer,
)
';
execute immediate v_sql;
end;
/
--动态赋值,插入数据
--后面的数值可以使用变量
execute immediate 'INSERT INTO employee VALUES(:eptno, :ename)' USING 1010,'stars';
--相当于insert into employee(eptno,ename) values(1010,stars)
--把查询到的员工编号以及员工姓名赋值给两个变量,使用动态赋值
execute immediate 'select empno,ename from employee where sal=:sal' INTO v_empno,v_ename USING 1010;
目录对象
创建目录对象表示文件系统的某个文件夹,需要sysdba授权:
复制grant create any directory to scgs;
--语法
create directory SCGS_SQL_DIR
as 'directory_path'
drop directory DIR_NAME;--删除目录对象
--创建目录对象
create directory MY_IMG_DIR
as 'Q:\img';
LOB类型(大数据类型)
BLOB
按二进制来存储的,存放图片,文件,音乐
CLOB
直接存储文字的,文章或者是较长的文字
BFILE
一个二进制数据,相当于一个指针,指向某个文件,不属于数据库管理
- BFILE字段指向的文件不是数据库的一部份,只能在数据库外维护
- 对BFILE字段操作要使用BFILENAME函数
- 读取BFILE字段需要使用DBMS_LOB包
存放图片
- 用bfilename()函数定位文件
复制--此方法返回一个bfile对象,第二步需要使用
bfilename($dir_name$,$file_name$)
- 以只读方式打开文件:dbms_lob.open()
复制--mode模式选择dbms_lob包中提供的常量
dbms_lob.open($bfile$,$mode$)
- 插入数据,预先用empty_blob()填充字段值,并将该字段关联到blob变量
- 调用dbms_lob.loadfromfile(dest_lob, src_lob, amount)函数将bfile对象的数据加载到关联的blob变量
- 关闭文件:dbms_lob.close()
复制declare
src_bfile bfile;
dest_file blob;
v_amount number;
begin
-- 1. 用bfilename()函数定位文件,此函数返回bfile对象(文件定位器)
--oracle10g文件名可以使用中文,oracle11g好像使用中文会出错?
src_bfile := bfilename('GLLG_IMG_DIR','priscilla chan.jpg');
--2.以只读方式打开文件:dbms_lob.open()
dbms_lob.open(src_bfile,dbms_lob.file_readonly);
--3. 插入数据,预先用empty_blob()填充字段值,并将该字段关联到blob变量
--diagram是该表中的一个列名
insert into my_diagrams values('永远是你的陈慧娴',1,empty_blob()) returning diagram into dest_file;
--4. 调用dbms_lob.loadfromfile(dest_lob, src_lob, amount)函数将bfile对象的数据加载到关联的blob变量
v_amount := dbms_lob.getlength(src_bfile); --得到图片文件的大小
dbms_lob.loadfromfile(dest_file,src_bfile,v_amount);
--5. 关闭文件:dbms_lob.close()
dbms_lob.close(src_bfile);
commit;
end;
/
存放文本文件
步骤与上面一样,加载数据的函数参数有些不同,是把bfile文件的数据加载到了clob
复制declare
src_file bfile;
dest_file clob;
v_amount number;
v_dest_offset number := 1;
v_src_offset number := 1;
v_lang_context number := dbms_lob.default_lang_ctx;
v_warning number;
begin
-- 1. 用bfilename()函数定位文件
src_file := bfilename('GLLG_SQL_DIR','oracle.sql');
--2. 以只读方式打开文件:dbms_lob.open()
dbms_lob.open(src_file,dbms_lob.file_readonly);
--3. 插入数据,预先用empty_clob()填充字段值,并将该字段关联到blob变量
insert into my_book values(2,'oracle所有章节',empty_clob()) returning book_file into dest_file;
v_amount := dbms_lob.getlength(src_file);
--4. 调用 dbms_lob.loadclobfromfile(...有8个参数...)函数将bfile对象的数据加载到关联的blob变量
dbms_lob.loadclobfromfile(dest_lob => dest_file,
src_bfile => src_file,
amount => v_amount,
dest_offset => v_dest_offset,
src_offset => v_src_offset,
bfile_csid => dbms_lob.default_csid,
lang_context => v_lang_context,
warning => v_warning);
-- 检查错误
IF v_warning = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
DBMS_OUTPUT.PUT_LINE('字符转换错误!');
END IF;
--5. 关闭文件:dbms_lob.close()
dbms_lob.close(src_file);
commit;
end;
/