實驗8 觸發(fā)器及作業(yè)

1、實驗8 觸發(fā)器與作業(yè)姓名：學(xué)號：專業(yè)：軟件工程（金融）班級：同組人：無實驗日期：2013/7/22【實驗?zāi)康呐c要求】n 理解什么是觸發(fā)器n 掌握觸發(fā)器的設(shè)計與使用n 掌握在Oracle中完成定時作務(wù)的方法【實驗內(nèi)容與步驟】相關(guān)說明：本實驗中前面實驗創(chuàng)建的觸發(fā)器可能對后面的實驗產(chǎn)生干擾，若出現(xiàn)這種情況，請把前面觸發(fā)器刪除，再完成后面實驗。建議每完成一個實驗題后，即將該題相關(guān)的觸發(fā)器刪除，以免對后面實驗產(chǎn)生干擾。8.0 觸發(fā)器創(chuàng)建與刪除語法1觸發(fā)器的創(chuàng)建觸發(fā)器是一種在發(fā)生數(shù)據(jù)庫事件時自動運行的PL/SQL語法如下：CREATE OR REPLACE TIGGER觸發(fā)器名 觸發(fā)時間 觸發(fā)事件ON表名

2、FOR EACH ROWBEGINpl/sql語句END2觸發(fā)器的刪除DROP TRIGGER trigger_name8.1 觸發(fā)器基礎(chǔ)以下程序展示的是觸發(fā)器的基本使用方法，請閱讀并理解以下程序代碼，給出運行結(jié)果。1.創(chuàng)建測試表-drop table employees;create table employees( id number(5), name varchar2(30), salary number(8,2), job_id varchar2(20);2.創(chuàng)建觸發(fā)器CREATE OR REPLACE TRIGGER secure_emp_1 -這里不能有IS BEFORE INSE

3、RT ON employees - 這里沒有分號BEGIN IF (TO_CHAR (SYSDATE,'DY') IN ('星期六','星期天') OR (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00' ) THEN RAISE_APPLICATION_ERROR(-20500,'你只能在工作時間對表進(jìn)行操作'); END IF; END;/3.測試觸發(fā)器（1）-測試語句insert into employees

4、values (1,'a',2222,'aaaaa');給出測試結(jié)果截圖：8.2 使用觸發(fā)器監(jiān)控數(shù)據(jù)更新操作閱讀以下程序，理解程序功能，給出運行測試結(jié)果。1.創(chuàng)建觸發(fā)器CREATE OR REPLACE TRIGGER secure_emp_2 BEFORE INSERT OR UPDATE OR DELETE ON employeesBEGIN -如果當(dāng)前時間是周六或周日 或者時間不在8:00-18:00之間 IF (TO_CHAR (SYSDATE,'DY') IN ('星期五','星期天') OR (TO_C

5、HAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00' ) THEN IF DELETING THEN RAISE_APPLICATION_ERROR (-20502,'你只能在工作時間刪除員工表的數(shù)據(jù)'); ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (-20500,'你只能在工作時間插入員工表的數(shù)據(jù).'); ELSIF UPDATING ('SALARY') THEN RAISE_APPLICAT

6、ION_ERROR (-20503,'你只能在工作時間更新員工表的數(shù)據(jù)'); ELSE RAISE_APPLICATION_ERROR (-20504,'你只能在工作事件操作員工表的數(shù)據(jù).'); END IF; END IF;END;/2.運行測試分別運行以下測試語句，給出運行結(jié)果。并理解為什么會有這樣的結(jié)果。insert into employees values (1,'a',2222,'AD_PRES');insert into employees values (2,'b',2222,'AD_VP&

7、#39;);給出測試結(jié)果截圖：delete from employees;給出測試結(jié)果截圖：update employees set salary=3000;給出測試結(jié)果截圖：8.3 在insert或update中使用:new閱讀并理解以下程序，理解其功能，給出運行測試結(jié)果。1.創(chuàng)建觸發(fā)器-在insert或update中使用:new-CREATE OR REPLACE TRIGGER restrict_salary BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW BEGIN - 插入和修改可以使用:new來訪問新的數(shù)據(jù),修

8、改也可以使用:old來訪問舊的數(shù)據(jù) IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP') AND :NEW.salary > 15000 THEN RAISE_APPLICATION_ERROR (-20202,'員工不能賺到這么多薪水'); END IF;END;2.運行測試-測試語句insert into employees values (1,'a',20000,'AD_PRES');insert into employees values (2,'b'

9、,20000,'AD_VPs');update employees set salary=30000 ,job_id='xxxx' where name='a'給出運行結(jié)果：思考：觀察運行結(jié)果，比較insert和update語句執(zhí)行的差別。8.4 在delete中使用:old閱讀并理解以下程序，理解其功能，給出運行測試結(jié)果。1.創(chuàng)建觸發(fā)器CREATE OR REPLACE TRIGGER restrict_salary_del BEFORE DELETE ON employees FOR EACH ROW BEGIN - 在delete語句中只能

10、使用:old來訪問舊的數(shù)據(jù) IF (:OLD.job_id IN ('AD_PRES', 'AD_VP') AND (:OLD.salary > 15000) THEN RAISE_APPLICATION_ERROR (-20202,'該員工不可以刪除'); END IF;END;2.運行測試-測試語句insert into employees values (2,'張三',20000,'AD_PRES');delete from employees where name='張三'給出運行結(jié)果

11、：8.5 INSTEAD OF類型的觸發(fā)器閱讀并理解以下程序，理解其功能，給出運行測試結(jié)果。1.準(zhǔn)備工作：創(chuàng)建觸發(fā)器及相應(yīng)測試用表-INSTEAD OF類型的觸發(fā)器drop table departments;create table departments( dept_id number(5) primary key, dept_name varchar2(20);drop table employees;create table employees( employee_id number(5), employee_name varchar2(20), department_id numbe

12、r(5), constraint empl_dept_fk foreign key (department_id) references departments(dept_id);-創(chuàng)建視圖create or replace view v_empasselect e.employee_id,e.employee_name,e.department_id,d.dept_namefrom employees e,departments dwhere e.department_id = d.dept_id-創(chuàng)建觸發(fā)器create or replace trigger new_view_emp_dep

13、t instead of insert on v_empl for each rowbegin if inserting then insert into departments values(:new.department_id,:new.dept_name); insert into employees values(:new.employee_id,:new.employee_name,:new.department_id); end if;end;2.運行測試（1）查詢視圖：select * from v_emp;給出運行結(jié)果：（2）插入測試數(shù)據(jù)-向視圖中插入數(shù)據(jù)insert into

14、 v_emp values (171,'張六',1000,'銷售部');（3）結(jié)果測試select * from employees;給出運行結(jié)果：select * from departments;給出運行結(jié)果：8.6 系統(tǒng)級觸發(fā)器閱讀并理解以下程序，理解其功能，給出運行測試結(jié)果。1.創(chuàng)建觸發(fā)器-系統(tǒng)觸發(fā)器-創(chuàng)建登陸或者退出數(shù)據(jù)庫日志表drop table logs;create table logs(userid varchar2(20),times date,op varchar2(10) 操作類型);-登陸數(shù)據(jù)庫觸發(fā)器create or replace

15、trigger log_onafter logon on schemabegin insert into logs values(user,sysdate,'logon');end;/-退出數(shù)據(jù)庫觸發(fā)器create or replace trigger log_offbefore logoff on schemabegin insert into logs values(user,sysdate,'logoff');end;/2.運行測試(1)-以不同的用戶身份登錄和退出數(shù)據(jù)庫數(shù)次，以獲得相關(guān)數(shù)據(jù)如：conn sys/testdb as sysdba; Conn

16、 soctt/tiger (2)- A設(shè)置日期的顯示格式alter session set nls_date_format='YYYY-MM-DD HH_MI_SS'(3)- 查看記錄select * from logs;給出運行結(jié)果：8.7 作業(yè)與定時任務(wù)8.7.1 作業(yè)概述作業(yè)是Oracle中一個程序包，可用于Oracle中實現(xiàn)定時任務(wù)。日常開發(fā)最常用到的用于完成定時任務(wù)的是Job中包中的submit函數(shù)，其接口如下：1. dbms_job.submit( job out binary_integer,   2. whatinarchar2, 

17、  3. next_date indate，   4. intervalinvarchar2,   5. no_parseinboolean)   其中： job：輸出變量，是此任務(wù)在任務(wù)隊列中的編號； what：執(zhí)行的任務(wù)的名稱及其輸入?yún)?shù)； next_date：任務(wù)執(zhí)行的時間； interval：任務(wù)執(zhí)行的時間間隔。 其中Interval這個值是決定Job何時，被重新執(zhí)行的關(guān)鍵；當(dāng)interval設(shè)置為null時，該job執(zhí)行結(jié)束后，就被從隊列中刪除。假如我們需要該job周期性地執(zhí)行，則要用sysdatem表示。以下實驗

18、中創(chuàng)建一個名稱為tri_test_id的觸發(fā)器，用于實現(xiàn)當(dāng)在作業(yè)中定時調(diào)用存儲過程，向Test表插入數(shù)據(jù)時，自動的觸發(fā)序列號的產(chǎn)生，并添加數(shù)據(jù)到Test表中。更新員工工資之后，將更新紀(jì)錄保存到表salary_change_record中。8.7.2 實驗準(zhǔn)備-(1)創(chuàng)建測試表:CREATE TABLE Test(id number,cur_user varchar2(20),cur_time varchar2(30); -(2)創(chuàng)建序列 test_sequence: CREATE SEQUENCE test_sequence INCREMENT BY 1-每次加幾個 START WITH 1

19、-從1開始計數(shù) NOMAXVALUE -不設(shè)置最大值 NOCYCLE -一直累加，不循環(huán) CACHE10; -建觸發(fā)器tri_test_id： CREATE OR REPLACE TRIGGER tri_test_id before INSERT ON test -test 是表名 FOR each row DECLARE nextid number; BEGIN IF :new.id IS NULL or :new.id=0 THEN -id是列名 SELECT test_sequence.nextval - test_sequence正是剛才創(chuàng)建的序列 INTO nextid FROM s

20、ys.dual; :new.id:=nextid; END IF; END tri_test_id; / -創(chuàng)建一個自定義過程 CREATE OR REPLACE PROCEDURE proc_test AS BEGIN INSERT INTO test(cur_user,cur_time) VALUES(user,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'); -系統(tǒng)時間轉(zhuǎn)為格式串 END; / 8.7.3 作業(yè)的創(chuàng)建與使用-創(chuàng)建JOBDECLARE jobNo number; BEGIN dbms_job.submit( jobNo, -

21、job參數(shù) 'proc_test;', -what參數(shù)是將被執(zhí)行的PL/SQL代碼塊 sysdate, -next_date,參數(shù)指識何時將運行這個工作,本例為馬上開始 'sysdate+1/(24*60*10)'-interval參數(shù),即1/10分鐘運行test過程一次 ); -no_parse參數(shù)，無 END; -next_date,可如：to_date('20110517093500','yyyy-mm-dd hh24:mi:ss'),8.7.4 測試-測試： 1分鐘后查看Test表中數(shù)據(jù)SELECT *FROM test請

22、給出運行結(jié)果：8.7.5 停止與移除作業(yè)若要移除、停止或啟用作業(yè)，需根據(jù)作業(yè)號，調(diào)用作業(yè)包中存儲過程dbms_job.remove(jobNo)來完成。其中，作業(yè)號可通過數(shù)據(jù)字典all_jobs獲取。1.獲取作業(yè)號可通過查詢數(shù)據(jù)字典all_jobs來獲取作業(yè)號，示例如下：其中，job字段值即為作業(yè)號。2.移除作業(yè)-刪除JOBbegindbms_job.remove(jobNo);-jobNo為作業(yè)號，使用時應(yīng)用查詢得到的結(jié)果end;/3. 停止一個JOBexec dbms_job.broken(jobNo,true) -jobNo為作業(yè)號，使用時應(yīng)用查詢得到的結(jié)果4. 停止一個JOBexec

23、dbms_job.broken(jobNo,false) -jobNo為作業(yè)號，使用時應(yīng)用查詢得到的結(jié)果第 20 頁 共 20 頁8.8 實驗練習(xí)1.實驗練習(xí)：利用觸發(fā)器追蹤薪水變動情況請創(chuàng)建一個名稱為 change_record的觸發(fā)器，實現(xiàn)當(dāng)對emp表更新員工工資之后，將更新紀(jì)錄保存到表salary_change_record中。具體要求如下：建立日志對薪水的變動情況形成一個追蹤，也就是說，如果對某個職員的薪水進(jìn)行變更就應(yīng)該將其相應(yīng)的變更記錄全部記下來，將數(shù)據(jù)記錄到表salary_change_record（empid,old_salary,new_salary,change_date）中

24、，其中old_salary:用來紀(jì)錄員工原來的工資，new_salary：用來紀(jì)錄更新后的工資，change_date：記錄更新的系統(tǒng)時間。請給出相應(yīng)的代碼：在emp表中添加或修改幾行數(shù)據(jù)，而后查看表salary_change_record中數(shù)據(jù)。請給出測試結(jié)果：2編寫一個數(shù)據(jù)庫觸發(fā)器，當(dāng)任何時候某個部門從"dept"表中刪除時，該觸發(fā)器將從"emp"表中刪除該部門的所有雇員記錄。給出相應(yīng)的代碼：給出測試結(jié)果：3. 映射emp表中每個部門的總?cè)藬?shù)和總工資（1）-創(chuàng)建映射表CREATE TABLE dept_sal ASSELECT deptno,COUN

25、T(empno) AS total_emp,SUM(sal) AS total_salFROM empGROUP BY deptno;DESC dept_sal;（2）-創(chuàng)建觸發(fā)器，映射emp表中每個部門的總?cè)藬?shù)和總工資創(chuàng)建一觸發(fā)器，當(dāng)emp表中數(shù)據(jù)改變時，自動修改dept_sal表中數(shù)據(jù)，使其反映出Emp表中數(shù)據(jù)的最新狀態(tài)。請給出程序源碼：create or replace trigger up_empafter update or delete or insert on emp for each rowdeclare t_depno number; t_sal number; t_emp

26、number; t_no number;begin if updating then t_depno:=:old.deptno; select count(empno),sum(sal) into t_sal,t_emp from emp where deptno=t_depno; update dept_sal set total_emp=t_emp,total_sal=t_sal where deptno=t_depno ; elsif inserting then t_depno:=:new.deptno; select count(*) into t_no from emp where

27、 deptno=t_depno; if(t_no)<2 then select count(empno),sum(sal) into t_sal,t_emp from emp where deptno=t_depno; insert into dept_sal values(t_depno,t_emp,t_sal); else select count(empno),sum(sal) into t_sal,t_emp from emp where deptno=t_depno; update dept_sal set total_emp=t_emp,total_sal=t_sal whe

28、re deptno=t_depno ; end if; elsif deleting then t_depno:=:old.deptno; select count(*) into t_no from emp where deptno=t_depno ; if(t_no)>0 then select count(empno),sum(sal) into t_sal,t_emp from emp where deptno=t_depno; update dept_sal set total_emp=t_emp,total_sal=t_sal where deptno=t_depno ; else delete from dept_sal where deptno=t_depno; end if; end if;end;/（3）測試按下面步驟完成測試，結(jié)出測試結(jié)果，并比對測試結(jié)果，看是否滿足要求。A查詢dept_sal中數(shù)據(jù)：SELECT * FROM dept_sal;給出結(jié)果：B-對emp表進(jìn)行DML操作INSERT INTO emp(empno,deptno,sal) VALUES