欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle学习笔记(十七) PL/SQL高级应用

程序员文章站 2022-03-24 17:59:26
PL/SQL高级应用 动态SQL 在PL/SQL中,不能直接执行DDL(create,alter,drop),得使用动态SQL,当然,除了DDL,动态SQL也可以执行DML(select,insert,update,delete) 语法: 例子: 目录对象 创建目录对象表示文件系统的某个文件夹,需要 ......

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包

存放图片

  1. 用bfilename()函数定位文件
--此方法返回一个bfile对象,第二步需要使用
bfilename($dir_name$,$file_name$)
  1. 以只读方式打开文件:dbms_lob.open()
--mode模式选择dbms_lob包中提供的常量
dbms_lob.open($bfile$,$mode$)
  1. 插入数据,预先用empty_blob()填充字段值,并将该字段关联到blob变量
  2. 调用dbms_lob.loadfromfile(dest_lob, src_lob, amount)函数将bfile对象的数据加载到关联的blob变量
  3. 关闭文件: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;
/