PL/SQL 總結，plsql總結

PL/SQL 總結，plsql總結
一、.基本文法
PL/SQL的結構塊如下：
declare
      --聲明變數、類型、遊標 
begin
        --程式執行的部分，類似main方法
exception
        --針對異常，提供如何處理的機制
        --when ....then....
end;
注意，沒有用的部分，就 不需要寫，比如程式的確是沒有異常要處理，那麼exception就不寫
建議的命名方法：
標識符  命名規則 例子
程式變數  V_name V_name
程式常量  C_Name C_company_name
遊標變數          Name_cursor Emp_cursor
異常標識  E_name E_too_many
表類型  Name_table_typeEmp_record_type
表  Name_table Emp
記錄類型  Name_recordEmp_record
SQL*Plus替代變數  P_name P_sal
綁定變數  G_name G_year_sal
HelloWorld示範：

begin  dbms_output.put_line('HelloWorld');end;

輸出100員工的工資：

declare  v_sal number(20) :=0;    --聲明變數，要注意的是，資料類型和要查詢的表中的資料類型是對應得  --附一個預設值，:=   v_email varchar2(20);--另一種寫法，v_email employees.email % type  v_hire_date date;    --v_hire_date employees.hire_date % type   //v_hire_date與employees表中的hire_date類型一樣--%type ，如果自訂類型的精度不夠，那麼%type就是動態擷取表中的類型及精度begin   select salary,email , hire_date into v_sal,v_email,v_hire_date   from employees where employee_id = 100;  --實現select操作，注意是select into  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);--列印end;

修改資料

declare     v_emp_id employees.employee_id%type;begin     v_emp_id :=100;     update employees     set salary = salary + 100     where employee_id = v_emp_id;     dbms_output.put_line('OK');end; 

使用%TYPE
定義一個變數，其資料類型與已經定義的某個 資料變數的類型相同，或者與資料庫表的某個列的資料類型
相同，這時可以使用%TYPE。
使用%TYPE 特性的優點在於：
  所引用的資料庫列的資料類型可以不必知道；
  所引用的資料庫列的資料類型可以即時改變。
使用%ROWTYPE
PL/SQL 提供%ROWTYPE  操作符,  返回一個記錄類型,  其資料類型和資料庫表的資料結構相一致。
使用%ROWTYPE 特性的優點在於：
  所引用的資料庫中列的個數和資料類型可以不必知道；
  所引用的資料庫中列的個數和資料類型可以即時改變。
二、記錄類型
type ... is record(,,);

DECLARE  --聲明一個記錄類型，類似於java中的類 TYPE emp_record IS RECORD(      v_sal employees.salary % type,      v_email employees.email % type,--以逗號連結      v_hire_date employees.hire_date % type);--分號   --定義一個記錄變數   v_emp_record emp_record;BEGIN      SELECT salary , email, hire_date INTO v_emp_record      FROM employees WHERE employee_id = 101;      dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','||v_emp_record.v_hire_date);END;

擷取一個表中所有列的記錄類型

declare     v_emp_record employees%rowtype;     v_emp_id number(10);begin     v_emp_id  := 100;     select * into v_emp_record     from employees     where employee_id = v_emp_id;     dbms_output.put_line(v_emp_record.salary||' , '||v_emp_record.last_name);--用表中的列名字即可end; 

三、流程式控制制：
條件判斷：（兩種）
方式一：if ..then elseif then...else...end if;
方式二：case...when...then ..end;
迴圈結構：（三種）
方式一：loop...exit when ...end loop;
方式二：while..loop...end;
方式三：for i in ..loop ..end loop;
關鍵字：goto （類似java中的break後接一個標籤，跳出那個迴圈）、 exit
條件判斷：
--查詢出 150號 員工的工資, 若其工資大於或等於 10000 則列印 'salary >= 10000'; 
--若在 5000 到 10000 之間, 則列印 '5000<= salary < 10000'; 否則列印 'salary < 5000'
方式一

declare     v_sal employees.salary%type;     v_result varchar2(20);begin     select salary into  v_sal from employees where employee_id = 150;     if v_sal >= 10000 then v_result := 'salary >= 10000';     elsif v_sal >=5000 then v_result := 'salary >= 5000';     else v_result := 'salary < 5000';     end if;     dbms_output.put_line(v_sal||' , '||v_result);end;

方式二

declare     v_sal employees.salary%type;     v_result varchar2(20);begin     select salary into  v_sal from employees where employee_id = 150;     v_result :=     case trunc(v_sal/5000) when 2  then 'salary >= 10000'                            when 1  then '5000 <= salary < 10000'                            when 0  then 'salary < 5000'     end;     dbms_output.put_line(v_sal||' , '||v_result);end;

case...when..then..相當於java的switch，使用起來有局限性，還是if..then..elsif...then比較好
注意，if..then每一次後面都要加“分號”，而case..when..then不能加，分號的問題在PL/DQL中比較坑
迴圈結構：
--使用迴圈語句列印 1 - 100.（三種方式）
--（1）初始化 （2）迴圈體 （3）迴圈條件 （4）迭代條件

declare       --(1)       v_i number(3) := 1;/*方式一begin       loop       --(2)           dbms_output.put_line(v_i);           --(4)          v_i := v_i + 1;       --(3)       exit when v_i >100;       end  loop;--注意每一行，都要有“分號”end;*/方式二：（推薦使用 ）begin       while v_i <= 100 loop             dbms_output.put_line(v_i);             v_i := v_i + 1;        end loop;end;/*方式三begin        for i in 1..100 loop --for i in reverse 1..100 loop，這樣就是反著，從100-1，還要注意in後面是兩個“.”            dbms_output.put_line(i);        end loop;end;*/

輸出1-100的所有質數

declare  v_i number(3) := 2;  v_j number(3) := 2;  v_flag number(3) := 1;begin  /*while v_i<=100 loop          while v_j <= sqrt(v_i) loop              if mod(v_i,v_j)=0 then v_flag := 0;              goto OK;              end if;          v_j := v_j + 1;              end loop;  <<OK>>  if v_flag = 1 then dbms_output.put_line(v_i);  end if;  v_j := 2;  v_i := v_i + 1;  v_flag := 1;  end loop;  */  for i in 2..100 loop      for j in 2..sqrt(i) loop          if mod(i,j)=0 then v_flag := 0;          goto OK;          end if;      end loop;      <<OK>>                  if v_flag = 1 then dbms_output.put_line(i);      end if;      v_flag := 1;  end loop;      end;

對於while 迴圈的嵌套。特別要注意初始化條件

四、遊標的使用（類似於java的iterator）
主要方便用於處理多行資料。

declare  --定義一個記錄類型  type v_record is record(       v_sal employees.salary%type,       v_last_name employees.last_name%type  );  --聲明一個記錄類型  v_emp_record v_record;  --定義一個遊標  cursor emp_sal_cursor is select salary,last_name from employees where department_id = 80;begin  --開啟遊標  open emp_sal_cursor;  --提取遊標  fetch emp_sal_cursor into v_emp_record;  while emp_sal_cursor%found loop        dbms_output.put_line(v_emp_record.v_last_name||' : '||v_emp_record.v_sal);        fetch emp_sal_cursor into v_emp_record;  end loop;  close emp_sal_cursor;end;

遊標的使用，用for比較簡單

declare  cursor emp_sal_cursor is select salary,last_name from employees where department_id = 80;begin  for c in emp_sal_cursor loop       dbms_output.put_line(c.last_name||c.salary);      end loop;end;

帶參數的遊標

declare    --定義遊標    cursor emp_sal_cursor(dept_id number, sal number) is            select salary + 1000 sal, employee_id id            from employees            where department_id = dept_id and salary > sal;        --定義基數變數    temp number(4, 2);begin    --處理遊標的迴圈操作    for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop          --判斷員工的工資, 執行 update 操作          --dbms_output.put_line(c.id || ': ' || c.sal);                    if c.sal <= 5000 then             temp := 0.05;          elsif c.sal <= 10000 then             temp := 0.03;             elsif c.sal <= 15000 then             temp := 0.02;          else             temp := 0.01;          end if;                    dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);          --update employees set salary = salary * (1 + temp) where employee_id = c.id;    end loop;end;

隱式遊標
隱式遊標屬性
SQL%FOUND 布爾型屬性,當最近一次讀記錄時成功返回，則值為 TRUE；
SQL%NOTFOUND 布爾型屬性,與%FOUND 相反；
SQL %ROWCOUNT 數字型屬性, 返回已從遊標中讀取得記錄數；
SQL %ISOPEN 布爾型屬性, 取值總是 FALSE。SQL 命令執行完畢立即關閉隱式遊標。
--更新指定員工資訊，如果該員工沒有找到，則列印”查無此人”資訊。

declare   v_sal employees.salary%type;  v_empid employees.employee_id%type := 101;begin  update employees  set last_name = 'ABC'  where employee_id = v_empid;  if sql%notfound then --     dbms_output.put_line('查無此人');  end if;end;

五、異常處理（三種類型）
異常處理概念
  異常情況處理(EXCEPTION) 是用來處理正常執行過程中未預料的事件, 程式塊的異常處理預定義的錯誤
和 自訂錯誤, 由於 PL/SQL  程式塊一旦產 生異常而沒有指出如何處理時,程式就會自動終止整個程式運行
共有三種類型：
1.預定義 ( Predefined ) 錯誤
ORACLE 預定義的異常情況大約有 24 個。對這種異常情況的處理，無需在程式中定義，由 由 ORACLE  自動
將其引發。
2.非預定義 ( Predefined )錯誤
即其他標準的 ORACLE 錯誤。對這種異常情況的處理，需要使用者在程式中定義，然後由 ORACLE 自動將
其引發。
3.使用者定義(User_define) 錯誤
程式執行過程中，出現編程人員認為的非正常情況。對這種異常情況的處理， 需要 使用者在程式中定義，
然後顯式地在程式中將其引發。
異常處理的基本結構：

EXCEPTIONWHEN first_exception THEN <code to handle first exception >WHEN second_exception THEN <code to handle second exception >WHEN OTHERS THEN <code to handle others exception >END;

異常處理可以按任意次序排列,但 OTHERS 必須放在最後.
預定義異常

declare  v_sal employees.salary%type;begin  select salary into v_sal from employees  where employee_id > 100;  dbms_output.put_line(v_sal);exception  when too_many_rows then dbms_output.put_line('要輸出的行數太多了!');  when others then dbms_output.put_line('其他錯誤!');end;

非預定義異常
步驟如下：
1.在 PL/SQL  塊的定義部分定義異常情況：
<異常情況> EXCEPTION;
2.  將其定義好的異常情況，與標準的 ORACLE  錯誤聯絡起來，使用 PRAGMA EXCEPTION_INIT  語句：
PRAGMA EXCEPTION_INIT(< 異常情況>, < 錯誤碼>);
3. 在 PL/SQL  塊的異常情況處理部分對異常情況做出相應的處理。

SQL>delete employeeswhere employee_id = 100ORA-02292: 違反完整約束條件 (SCOTT.DEPT_MGR_FK) - 已找到子記錄因為自己表裡有manager_id也指向自己表裡的employee_id，而刪除emp_id，因為manager_id指向，所以或無法刪除declare  e_deleteid_exception exception;  pragma exception_init(e_deleteid_exception,-2292);--將2292這個錯和自己定義的異常名稱關聯起來begin  delete employees  where employee_id = 100;exception  when e_deleteid_exception then dbms_output.put_line('違反完整性條件約束異常!');end;

使用者自訂的異常處理

當與一個異常錯誤相關的錯誤出現時，就會隱含觸發該異常錯誤。 使用者定義的異常錯誤是通過顯式使
用 RAISE  語句來觸發。當引發一個異常錯誤時，控制就轉向到 EXCEPTION  塊異常錯誤部分，執行錯誤處
理代碼。
步驟如下：
1． 在 在 PL/SQL  塊的定義部分定義異常情況：
< 異常情況> EXCEPTION;
2． RAISE < 異常情況>；
3．在 PL/SQL  塊的異常情況處理部分對異常情況做出相應的處理
在非預言定義異常的基礎上

declare  e_deleteid_exception exception;  pragma exception_init(e_deleteid_exception,-2292);  e_too_high_sal_exception exception; --自訂異常  v_sal employees.salary % type;begin   delete from employees where employee_id = 100;  select salary into v_sal from employees where employee_id = 100;  if v_sal > 10000 then   raise e_too_high_sal_exception;   --拋出異常  end if;exception  when e_too_high_sal_exception then dbms_output.put_line('工資太高了');  when e_deleteid_exception then dbms_output.put_line('違反完整性條件約束異常');  when others then dbms_output.put_line('其他異常');end;

1-5為基礎中的基礎
六、儲存函數和預存程序

過程和函數的唯一區別是函數總向調用者返回資料，而過程則不返回資料。
即儲存函數（有返回值）、預存程序（無返回值）
-----儲存函數：

建立函數1.  建立內嵌函數文法如下：CREATE [OR REPLACE] FUNCTION function_name[ (argment [ { IN | IN OUT }] Type,argment [ { IN | OUT | IN OUT } ] Type ][ AUTHID DEFINER | CURRENT_USER ]RETURN return_type{ IS | AS }<類型.變數的說明>BEGINFUNCTION_bodyEXCEPTION其它語句END;

說明:
1) OR REPLACE 為可選. 有了它, 可以或者建立一個新函數或者替換相同名字的函數, 而不會出現衝突
2) 函數名後面是一個可選的參數列表, 其中包含 IN, OUT 或 IN OUT 標記. 參數之間用逗號隔開. IN  參數
標記表示傳遞給函數的值在該函數執行中不改變; OUT  標記表示一個值在函數中進行計算並通過該參
數傳遞 給調用語句; IN OUT  標記表示傳遞給函數的值可以變化並傳遞給調用語句.  若省略標記,  則參數
隱含為 IN 。
3) 因為函數需要返回一個值, 所以 RETURN 包含返回結果的資料類型。
--儲存函數結構

create or replase function func_name (depart_id number, salary number)return number --返回某部門所有的工資is            --函數在使用過程中，需要聲明的變數、記錄類型record、遊標cursorbegin       --函數的執行體exception       --處理函數執行過程中的異常end;

Hello，World示範

create or replace function HelloDemoreturn varchar2isbegin       return 'Hello,World';end;

顯示Function created，說明函數已經建立
調用函數
方式一：

begin  dbms_output.put_line(HelloDemo);end;

方式二：

SQL>select HelloDemo from dual; /*create or replace function HelloDemo(v_world varchar2)return varchar2isbegin       return 'Hello'||v_world;end;*//*select HelloDemo('Worrld') from dual*/

擷取系統時間函數

create or replace function get_sysdatereturn dateis   v_date date;begin       v_date := sysdate;       return v_date;end;select get_date from  dual;

定義兩個數相加的函數

create or replace function add_func(v_num1 number,v_num2 number)return numberis   v_sum number(10);--這裡需要指定長度begin       v_sum := v_num1 + v_num2;       return v_sum;end;select add_func(1,2) from dual;

擷取指定部門的工資總和

create or replace function get_all_sal(dept_id number)return numberis   v_sumsal number(20,2) := 0;      cursor salary_cursor is select salary from employees where department_id = dept_id;begin       for c in salary_cursor loop       v_sumsal := v_sumsal + c.salary;       end loop;              return v_sumsal;end;

關於OUT形的參數
因為函數只能有一個返回值，PL/SQL程式可以通過OUT參數實現多個返回值

擷取指定部門的工資總和，人員總和

create or replace function get_all_sal(dept_id number,emp_num out number)--注意out這裡return numberis   v_sumsal number(20,2) := 0;      cursor salary_cursor is select salary from employees where department_id = dept_id;begin          emp_num := 0;       for c in salary_cursor loop       v_sumsal := v_sumsal + c.salary;       emp_num := emp_num + 1;       end loop;              return v_sumsal;end;輸出declare  v_num number(3):=0;begin  dbms_output.put_line(get_all_sal(80,v_num));     dbms_output.put_line(v_num); --v_num就是部門人數end;    

-----預存程序：
--定義預存程序，實現指定部門的工資（通過out參數），要求部門id和工資總數為參數

create or replace procedure get_sal(dept_id number,v_sal out number)--不需要傳回值is   cursor salary_cursor is select salary from employees where department_id = dept_id;begin          v_sal := 0;       for c in salary_cursor loop       v_sal := v_sal + c.salary;       end loop;       dbms_output.put_line(v_sal);end;

declare  v_sal number(10):=0;begin  get_sal(80,v_sal); --直接調用即可end;

因為預存程序不需要返回值，所以可以把增刪改的操作定義預存程序
七、觸發器
觸發器是許多關聯式資料庫系統都提供的一項技術。在 ORACLE 系統裡， 觸發器類似過程和函數，都有
聲明，執行和異常處理過程的 PL/SQL
一個helloworld層級的觸發器

create or replace trigger hello_triggerafter update on employees--for each rowbegin     dbms_output.put_line('hello...');    --dbms_output.put_line('old.salary:'|| :OLD.salary||',new.salary'||:NEW.salary);end;

然後執行：update employees set salary = salary + 1000;

6-7重點