Oracle PL/SQL入門之案例實踐

一． 案例介紹
　　
　　某資料庫有兩張表，是關於某公司員工資料、薪水和部門資訊的，它們分別是emp表和dept表，兩張表的結構如下：
　　
　　要求如下：
　　
　　1、按照上表結構建立相應的表，並每張表寫入5組合法資料。
　　
　　2、操縱相關表，使得“技術部”的員工的薪水上漲20%。
　　
　　3、建立日誌，追蹤薪水變動情況。
　　
　　4、建立測試包。
　　
　　 二． 案例的分析與實現
　　
　　從前面案例的介紹不難看出，要求1考察點為基本SQL語句；要求2主要考察複合查詢；要求3是考察觸發器的應用；要求4的考察面相對多一些，不僅考察了包的建立，而且也考察了在PL/SQL中的測試方法。瞭解了這些考察的知識點，就可以一一去解決。
　　
　　要求1：
　　
　　首先根據前面表的結構可以建立兩張表：
　　
　　——建立員工表
　　
　　create table emp (emp_id number(5), emp_name varchar2(20), emp_salary number(4));
　　
　　——部門表
　　
　　create table dept (dept_id number(3), dept_name varchar2(20), emp_id number(5));
　　
　　建立了表之後就可以往表裡面寫資料了，這裡把添加表記錄的代碼寫入到相應的預存程序。
　　
　　/*給emp表添加記錄的預存程序*/
　　create or replace procedure ins_table_emp(p_emp_id number,p_emp_name varchar2,p_emp_salary number) as
　　v_emp_id number:=p_emp_id;
　　v_emp_name varchar2(20):=p_emp_name;
　　v_emp_salary number:=p_emp_salary;
　　begin
　　insert into emp values (v_emp_id,v_emp_name,v_emp_salary);
　　end ins_table_emp;
　　
　　/*給dept表添加記錄的預存程序*/
　　create or replace procedure ins_table_dept(p_dept_id number,p_dept_name varchar2,p_emp_id number) as
　　v_dept_id number:=p_dept_id;
　　v_dept_name varchar2(20):=p_dept_name;
　　v_emp_id number:=p_emp_id;
　　begin
　　insert into dept values (v_dept_id,v_dept_name,v_emp_id);
　　end ins_table_emp;
　　
　　/*調用相應的預存程序實現記錄添加*/
　　begin
　　ins_table_emp(10000,'',4000);
　　ins_table_emp(10001,'CCM,2300);
　　ins_table_emp(10002,WMR,3500);
　　ins_table_emp(10003,'CXD',3500);
　　ins_table_emp(10004,'LXD',3500);
　　
　　ins_table_dept(111,'JAVA',10000);
　　ins_table_dept(111,'JAVA',10001);
　　ins_table_dept(111,.'NET',10002);
　　ins_table_dept(112,'.NET',10003);
　　ins_table_dept(113,'JAVA',10004);
　　end;
　　
　　要求2：
　　
　　給指定部門的員工加薪，這實際上是一個複合查詢，首先需要把所有該部門的員工塞選出來，然後對這些員工的薪水進行相應的改動。依照這一思路，代碼如下：
　　
　　（需要注意的是：將要加薪的部門作為參數，這樣的預存程序更有靈活性。）
　　
　　create or replace procedure add_salary(p_dept_name varchar2) as
　　v_dept_name varchar2(20):=p_dept_name;
　　begin
　　update emp set emp.EMP_SALARY=emp.EMP_SALARY*1.2 where emp.EMP_ID in (select emp.EMP_ID from emp,dept where emp.EMP_ID=dept.EMP_ID and dept.DEPT_ID='??ê?2?');
　　end add_salary;
　　
　　要求3：
　　
　　建立日誌對薪水的變動情況形成一個追蹤，也就是說，如果對某個職員的薪水進行變更就應該將其相應的變更記錄全部記下來。如果對emp表的salary欄位建立一個觸發器，來監視對salary的更改，把每次更改進行記錄，這樣就達到了要求3的目的了。
　　
　　create or replace trigger print_salary_change
　　before delete or insert or update on emp　　--觸發事件
　　for each row　　　　　　　　　　　　　　　　　　-- 每修改一行都需要調用此過程
　　
　　declare 　　　　　　　　　　--只有觸發器的聲明需要declare，過程和函數都不需要
　　salary_balance number;
　　begin
　　--:new 與:old分別代表該行在修改前和修改後的記錄
　　salary_balance=:new.salary=:old.salary;
　　dbms_output.PUT_LINE('old salary is: '|| :old.salary);
　　dbms_output.PUT_LINE('old salary is: '|| :new.salary);
　　dbms_output.PUT_LINE('old salary is: '|| to_char(salary_balance));
　　end print_salary_change;
　　
　　要求4：
　　
　　與其他語言（c/c++等）相比，PL/SQL的測試有其不同之處，歸納下來有三種方法：
　　
　　1、使用DBMS_OUTPUT包的PUT_LINE方法來顯示中間變數，以此來觀察程式是否存在邏輯錯誤。
　　
　　2、插入測試表的方法。即建立一個臨時的中間表，然後把所有涉及到的中間變數的結果都作為記錄插入到中間表中，這樣可以查詢表中的結果來觀察程式的執行情況。
　　
　　3、使用異常處理手段，對可疑的程式段使用begin … end ，然後可以在exception裡進行異常捕獲處理。
　　
　　這裡準備使用第二種方法來建立一個測試包，PL/SQL裡包的概念類似於物件導向裡的類的概念，包將一組操作和屬性封裝在一起，不僅增強了程式的模組化，而且由於封裝了更多的操作和屬性而提高了執行效能。建立一個PL/SQL需要兩個步驟：首先要建立包頭，類似於建立一個類的標頭檔，裡面主要對包中的過程，函數和變數的聲明；第二部分主要是包體部分，實現前面聲明的過程和函數，另外還需要對包進行初始化等工作。
　　
　　根據這一思路，建立測試包如下：
　　
　　/*包頭部分*/
　　create or replace package debug as
　　procedure debug(v_description varchar2,v_valueOfvariable varchar2)
　　procedure reset;
　　v_numberOfLine number;
　　end debug;
　　/*包體部分*/
　　create or replace package body debug as
　　procedure debug(v_description varchar2,v_valueOfvariable varchar2) is
　　begin
　　insert into debugtable
　　values(v_numberOfLine,v_description,v_valueOfvariable);
　　v_numberOfLine:=v_numberOfLine+1;
　　end debug;
　　procedure reset is
　　begin
　　v_numberOfLine:=1;
　　delete from debugtable;
　　end reset;
　　/*初始化部分*/
　　begin
　　reset;
　　end debug;
　　
　　 三．小結
　　
　　綜合前面對4個問題的解答，基本把PL/SQL的主要部分融會進來了，雖然很多地方只是涉及到比較粗淺的層次，但是有了這一基礎，深入下去也是不難的。