PLSQL重點問題理解和實戰

一 ORACLE中PL/SQL使用的集合變數型別有RECORD（類）、VARRAY(sql陣列)、TABLE(巢狀表)
TABLE(巢狀表) 可以加index定義也可以不加，加表示index by是建立主鍵索引相當於陣列，不加就是個巢狀表集合
1 TABLE(巢狀表)定義表變數型別
  type type_table_emp_empno is table of emp.empno%type index by binary_integer;--TYPE表示表中一行中欄位型別
  v_empnos type_table_emp_empno;
  如果用%type定義
  定義集合變數v_empnos 是一個有emp.empno欄位型別的陣列，自己理解是存放實際還是一個表，裡面只有一個欄位，且欄位上有索引
  對此集合變數(is table of index by)型別的操作 包括count，delete，但不能用trim
  對VARRAY 可以用count，delete和trim
  使用形式
  select to_char(truck_in_out_id),
        employee_id,
        employee_nm,
        truck_in_purpose
   bulk collect into
        carid,
        empid,
        empnm,
        dest
 
   forall i in 1 .. carid.COUNT
 
   update cpnew.CP_VISIT_APPLY  a
      set a.mgr_principal_id = empid(i),
          a.mgr_principal_nm = empnm(i),
          a.visit_dest       = dest(i)
    where a.visit_apply_id = carid(i)
      and a.mgr_principal_id is null;
  
  type delArray1 is table of TEST.COURSE%ROWTYPE index by binary_integer; --ROWTYPE表示表中一行的記錄型別
  cur_result delArray1;
  如果用%rowtype定義
  定義集合變數cur_result是一個COURSE表型別的集合，自己理解是按一個表存放，裡面包括COURSE的所有欄位型別，且用整形數做這個表的索引
  使用形式
  select * bulk collect into cur_result
 
   forall i in 1 .. cur_result.COUNT
 
   update cpnew.CP_VISIT_APPLY  a
      set a.mgr_principal_id = cur_result(i).empid,
          a.mgr_principal_nm = cur_result(i).empnm,
          a.visit_dest       = cur_result(i).dest
    where a.visit_apply_id = cur_result(i).carid
      and a.mgr_principal_id is null;
 
 
實際工作中的例子
plsql 大資料量刪除，修改的方法FORALL加bulk collection into
create or replace procedure zl_del_UPDATEAPPLY_DEST187 as
  --type ridArray is table of rowid index by binary_integer;
  type delArray1 is table of varchar2(32) index by binary_integer;
  type delArray2 is table of CP_2012.CP_VISIT_TRUCK_INOUT.employee_id%type index by binary_integer;
  type delArray3 is table of CP_2012.CP_VISIT_TRUCK_INOUT.employee_nm%type index by binary_integer;
  type delArray4 is table of CP_2012.CP_VISIT_TRUCK_INOUT.truck_in_purpose%type index by binary_integer;
  //你會發現用%type就得每個欄位都得定義他的型別
  carid delArray1;
  empid delArray2;
  empnm delArray3;
  dest delArray4;
begin
 
   select to_char(truck_in_out_id),
        employee_id,
        employee_nm,
        truck_in_purpose
   bulk collect into
        carid,
        empid,
        empnm,
        dest
   from CP_2012.CP_VISIT_TRUCK_INOUT;
   --where rownum < 600001;
 
  forall i in 1 .. carid.COUNT
 
   update cpnew.CP_VISIT_APPLY  a
      set a.mgr_principal_id = empid(i),
          a.mgr_principal_nm = empnm(i),
          a.visit_dest       = dest(i)
    where a.visit_apply_id = carid(i)
      and a.mgr_principal_id is null;

  DBMS_OUTPUT.PUT_LINE(to_char(carid.COUNT) ||
                       ' records deleted from temp_mid_hubei_bak   !!!');                
  
end;
這種方法最大缺點是forall裡不能訪問遠端表，也不能用dblink，且只能放dml語句不能用dbms.putline
經過測試過發現for可以替代forall儘管時間相對慢一點，但也能接受，所以可以在for中用dblink，相應語句如下：

for i in 1 .. carid.COUNT

  loop
     update cpnew.CP_VISIT_APPLY@LINK_213TO187_CPNEW  a
      set a.mgr_principal_id = empid(i),
          a.mgr_principal_nm = empnm(i),
          a.visit_dest       = dest(i)
     where a.visit_apply_id = carid(i)
      and a.mgr_principal_id is null;

2 Record變數型別:（相當於java的類）
定義
type type_record_dept is record
        (
            deptno dept.deptno%type,
            dname dept.dname%type,
            loc dept.loc%type
        );
    v_temp type_record_dept;

3 VARRAY
定義和使用
CREATE OR REPLACE TYPE numbers_t IS VARRAY (5) OF NUMBER
DECLARE
   l_list numbers_t:= numbers_t (1, 2, 3, 4, 5);
BEGIN
   l_list.DELETE;
   DBMS_OUTPUT.put_line (CASE l_list.COUNT WHEN 0 THEN 'Empty' END);
END;
陣列使用例子 參考 Oracle陣列的使用 http://blog.itpub.net/12932950/viewspace-351791/

還可以對比本人之前的blog檢視遊標和bulk collect into的用法 http://blog.itpub.net/750077/viewspace-2075986/

二 PL/SQL異常

異常型別 1預定義的異常處理，2非預定義 ( Predefined )錯誤，3使用者定義(User_define) 錯誤
一般預定義和使用者定義異常使用較多
1預定義異常 如oracle已定義的異常
ORA-1403 No_data_found SELECT INTO沒有找到資料

使用時如果select沒查出資料時就直接處理沒找到資料的異常
EXCEPTION
 WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('資料庫中沒有編碼為'||v_empno||'的員工');
END;
2使用者自定義異常
使用者先定義異常
no_result  EXCEPTION;
如沒有更新的資料時，丟擲這個異常
UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno;
   IF SQL%NOTFOUND THEN
      RAISE no_result;
   END IF;
然後處理這個異常
EXCEPTION
   WHEN no_result THEN
      DBMS_OUTPUT.PUT_LINE('你的資料更新語句失敗了!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
SQLCODE，SQLERRM是ORACLE函式，會列印錯誤程式碼和錯誤名稱

參考 ORACLE PL/SQL程式設計之五：異常錯誤處理 https://www.cnblogs.com/huyong/archive/2011/05/06/2038743.html

後續持續更新
......

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/750077/viewspace-2147912/，如需轉載，請註明出處，否則將追究法律責任。