oracle學習筆記（十七） PL/SQL高階應用

one發表於2019-05-28
Oracle筆記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包

存放圖片

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

相關文章