某數(shù)據(jù)庫有兩張表，是關(guān)于某公司員工資料、薪水和部門信息的，它們分別是emp表和dept表，兩張表的結(jié)構(gòu)如下：

　　要求如下：

　　1、按照上表結(jié)構(gòu)建立相應(yīng)的表，并每張表寫入5組合法數(shù)據(jù)。

　　2、操縱相關(guān)表，使得“技術(shù)部”的員工的薪水上漲20%。

　　3、建立日志，追蹤薪水變動情況。

　　4、建立測試包。

　　二． 案例的分析與實現(xiàn)

　　從前面案例的介紹不難看出，要求1考察點為基本SQL語句；要求2主要考察復(fù)合查詢；要求3是考察觸發(fā)器的應(yīng)用；要求4的考察面相對多一些，不僅考察了包的創(chuàng)建，而且也考察了在PL/SQL中的測試方法。了解了這些考察的知識點，就可以一一去解決。

　　要求1：

　　首先根據(jù)前面表的結(jié)構(gòu)可以創(chuàng)建兩張表：

　　——創(chuàng)建員工表

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

　　建立了表之后就可以往表里面寫數(shù)據(jù)了，這里把添加表記錄的代碼寫入到相應(yīng)的存儲過程。

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

/*調(diào)用相應(yīng)的存儲過程實現(xiàn)記錄添加*/
begin
　ins_table_emp(10000,'',4000);
　ins_table_emp(10001,'??èy',2300);
　ins_table_emp(10002,'3?t',3500);
　ins_table_emp(10003,'à???',3500);
　ins_table_emp(10004,'á?ò?',3500);

　ins_table_dept(111,'DD?t2?',10000);
　ins_table_dept(111,'DD?t2?',10001);
　ins_table_dept(111,'DD?t2?',10002);
　ins_table_dept(112,'??ê?2?',10003);
　ins_table_dept(113,'êD3?2?',10004);
end;

　　要求2：

　　給指定部門的員工加薪，這實際上是一個復(fù)合查詢，首先需要把所有該部門的員工塞選出來，然后對這些員工的薪水進(jìn)行相應(yīng)的改動。依照這一思路，代碼如下：

　　（需要注意的是：將要加薪的部門作為參數(shù)，這樣的存儲過程更有靈活性。）

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：

　　建立日志對薪水的變動情況形成一個追蹤，也就是說，如果對某個職員的薪水進(jìn)行變更就應(yīng)該將其相應(yīng)的變更記錄全部記下來。如果對emp表的salary字段創(chuàng)建一個觸發(fā)器，來監(jiān)視對salary的更改，把每次更改進(jìn)行記錄，這樣就達(dá)到了要求3的目的了。

create or replace trigger print_salary_change 　
before delete or insert or update on emp　　--觸發(fā)事件
for each row　　　　　　　　　　　　　　　　　　-- 每修改一行都需要調(diào)用此過程

declare 　　　　　　　　　　--只有觸發(fā)器的聲明需要declare，過程和函數(shù)都不需要
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、插入測試表的方法。即創(chuàng)建一個臨時的中間表，然后把所有涉及到的中間變量的結(jié)果都作為記錄插入到中間表中，這樣可以查詢表中的結(jié)果來觀察程序的執(zhí)行情況。

　　3、使用異常處理手段，對可疑的程序段使用begin … end ，然后可以在exception里進(jìn)行異常捕獲處理。

　　這里準(zhǔn)備使用第二種方法來建立一個測試包，PL/SQL里包的概念類似于面向?qū)ο罄锏念惖母拍睿鼘⒁唤M操作和屬性封裝在一起，不僅增強(qiáng)了程序的模塊化，而且由于封裝了更多的操作和屬性而提高了執(zhí)行效能。建立一個PL/SQL需要兩個步驟：首先要建立包頭，類似于建立一個類的頭文件，里面主要對包中的過程，函數(shù)和變量的聲明；第二部分主要是包體部分，實現(xiàn)前面聲明的過程和函數(shù)，另外還需要對包進(jìn)行初始化等工作。

　　根據(jù)這一思路，建立測試包如下：

/*包頭部分*/
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;