oracle学習ノート(十七)PL/SQL高級応用

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;
/