存儲過程和處罰期

1、例一：在jxgl數(shù)據(jù)庫中，創(chuàng)建一個名稱為Select_S的存儲過程，該存儲過南昌航空大學實驗報告二0 一二年五月二十八日課程名稱：數(shù)據(jù)庫原理實驗名稱:存儲過程與觸發(fā)器班級:10202225 姓名： 仇鑫同組人:簽名:指導教師評定:、實驗環(huán)境1. Windows2000或以上版本;2. SQLServer2000 或 2005。二、實驗目的1 .掌握存儲過程的創(chuàng)建、修改、使用、刪除。2 .掌握觸發(fā)器的創(chuàng)建、修改、使用、刪除。三、實驗步驟及參考源代碼1、存儲過程實驗部分(1 )創(chuàng)建存儲過程。程，該存儲過程的功能是從數(shù)據(jù)庫表S中查詢所有女同學的信息，并執(zhí)行該存儲過程。具體實現(xiàn)的代碼如下: 倉y建：c

2、reate procedure Select_S as select * from s where sex = '女'執(zhí)行：execute Select_S例二：定義具有參數(shù)的存儲過程。在jxgl數(shù)據(jù)庫中，創(chuàng)建一個名稱為S表中插入一條數(shù)據(jù)， 新紀錄的值由參數(shù)提供，如果未提供的值給sex時，由參數(shù)的默認值代替。InsRecTos的存儲過程，該存儲過程的功能是向具體實現(xiàn)代碼如下:倉J建：create procedure InsRecToS (sno char(5),sn varchar (8),sex char (2)='男',age int ,dept varch

3、ar (20) as in sert in to S values (sno ,sn ,sex ,age ,dept )執(zhí)行: execute In sRecToSs no ='S8',s n ='羅 兵',age = 18,de pt ='信息'例三：定義能夠返回值的存儲過程。在jxgl數(shù)據(jù)庫中創(chuàng)建一個名稱為Query_S的存儲過程，該存儲過程的功能是從S表中根據(jù)學號查詢某一學生的姓名和年齡，并返回。具體實現(xiàn)代碼如下:倉J建：create procedure Query_S (sno char(5),sn varchar (8) out put

4、 ,age smalli nt out put ) as select sn = sn,age = age from s where sno = sno(2 )執(zhí)行存儲過程。Query_S存儲過程可以通過以下方法執(zhí)行declare SN varchar (8),AGE smallint execute Query_S 'S8' ,SN out put ,AGE out put select Sn,AGE(3 )查看和修改存儲過程。使用alter procedure 命令，修改存儲過程InsRecToS，代碼如下:alter procedure InsRecToS (sno ch

5、ar(5),sn varchar (8),sex char (2)='女',age smallint ,dept varchar (20) as insert into s values (sno ,sn ,sex ,age ,dept )(4)查看、重命名和刪除存儲過程例一：查看數(shù)據(jù)庫表s中存儲過程Select_S的源代碼exec sp_helptext Select_S例二：將存儲過程Select_S改名為Select_Student 。sp_rename Select_S ,Select_Student例三：將存儲過程Select Student 從數(shù)據(jù)庫中刪除。drop

6、 procedure Select_Student(5) 在DingBao數(shù)據(jù)庫中創(chuàng)建存儲過程 C_P_Proc ,實現(xiàn)參數(shù)化查詢顧客訂閱信息，查詢參數(shù)為顧客姓名， 要求能查詢出參數(shù)指定的顧客編號、顧客 名、訂閱報紙名及訂閱份數(shù)等信息。CREATE PROCEDURE C_P_Proc name VARCHAR (8)ASSELECT CUSTOMER .cna,CUSTOMER .cno,PAPER.pna ,CP.numFROM PAPER,CP,CUSTOMERWHERE cna=n ame AND CUSTOMER .c no = CP.cno ANDPAP ER. pno =CP.p

7、no ;口 U DingE白&國Sa表Ed視圏E 3閆義伺曰立可編程性0辛和過H0之就存刪程E 圍 dbo.C_P_Proc(6) 執(zhí)行存儲過程 C_P_Pro ,實現(xiàn)對“李濤”、“錢金浩”等不同顧客的訂 閱信息放入查詢。Execute C_P_Proc name ='李濤'結果如下:Execute C_P_Proc name ='錢金浩' 結果如下:(7) 刪除存儲過程C_P_Pro。DROP PROCEDURE C P Proc2、觸發(fā)器實驗部分(1) 創(chuàng)建觸發(fā)器例一：對 s 表創(chuàng)建 up date 觸發(fā)器 TR_S_Age_u pdate : cr

8、eate trigger TR_S_Age_ up date on s for up date asdeclare iAge int ;select iAge =age from insertedif iAge <8 or iAge >45beg inraiserror （'學生年齡應該大于等于，并小于等于,16,1）rollback transactionend當對s做up date操作時，會自動觸發(fā) TR_S_Age_u pdate 觸發(fā)器，若入學日期與出生日期年份相差小于8或大于45時，則取消該次修改操作。例二：創(chuàng)建一個觸發(fā)器，當向s表中更新一條記錄時， 會自動顯示s

9、表中的記錄:create triggerCha nge_S_Selon s forinsert ,update,delete asselect * from（2）觸發(fā)器的引用（使用）。類似地對課程c表創(chuàng)建插入觸發(fā)器 TR_C_insert : create trigger TR_C_insert on c for insert asdeclare ict int;select ict = ct from insertedif ict < 1 or ict > 10beginraiserror （'課程學分應該大于等于，小于等于,16,1）rollback transacti

10、onend當對 c 表插入一條記錄，女0insert into c(cno,cn,ct)values('c8' /運籌學,'0.5 '),則引發(fā)觸發(fā)器 TR_C_insert，取消該記錄的插入。在查詢子窗口中，對表s執(zhí)行修改命令操作時，引發(fā)了觸發(fā)器修改觸發(fā)器。還能對表創(chuàng)建delete觸發(fā)器，如果此表由delete型觸發(fā)器，則刪除記錄時觸發(fā)器將被觸發(fā)執(zhí)行。被刪的記錄存發(fā)在deleted 表中，如下是在 s表中創(chuàng)建的TR_S_delete觸發(fā)器: create trigger TR_S_delete on s for delete asdeclare icount

11、int ;select icount = count (*)from deleted ,scwhere deleted .sno = sc.snoif icount >= 1beginraiserror ('該學生在表sc中被引用，暫不能被刪除！ ,16,1) rollback transactionend當執(zhí)行刪除命令 delete from s where sno= ' s1 '時，由于sc表中有對學 號為s1'的學生選課記錄，因此刪除未能成功。(3) 查看、修改和刪除觸發(fā)器。<1>修改觸發(fā)器。修改觸發(fā)器 TR_S_Age_update :

12、 alter trigger TR_S_Age_ up date on sfor up date as declare iAge int;select iAge =age from inserted if iAge < 8 or iAge > 45 beg inraiserror （'學生年齡應該大于等于，并小于等于,16,1）rollback transaction end<2>使用系統(tǒng)存儲過程查看觸發(fā)器。例一：查看已建立Change_S_SeI觸發(fā)器所涉及的表:sp_depends 'Cha nge_S_Se l'例二：查看已建立的Cha n

13、ge_S_Sel的命令文本:sp_help text 'Cha nge_S_se l'例題三：查看已建立的Change_S_Sel觸發(fā)器。exec sp_help 'Change_S_Sel'<3>刪除觸發(fā)器。刪除前面創(chuàng)建的觸發(fā)器 TR_S_Age_u pdate : drop trigger TR_S_Age_ up date(4) 在DingBao 數(shù)據(jù)庫中針對 PAPER創(chuàng)建插入觸發(fā)器 TR_PAPER_I、刪除觸發(fā)器TR_PAPER_D、修改觸發(fā)器TR_PAPER_U。具體要求如下。<1>對PAPER的插入觸發(fā)器：插入報紙記錄，單

14、價為負值或為空時，設定為10元。ON paper=pno from insertedCREATE TRIGGER TR PAPER IFOR INSERT ASDECLARE ippr FLOAT ;declare ipno int ;SELECT ippr = ppr ,ipnobeginif ippr <0 or ippr is NULLbegi nraiserror ('報紙的單價為空或小于零!',16,1)up date paper set ppr =10where paper .pno =ipnoendend<2>對PAPER的刪除觸發(fā)器：要刪除的記

15、錄,若正在被訂閱表CP參照時，級聯(lián)刪除訂閱表中相關的訂閱記錄。create trigger TR_Paper_D on paperin stead of delete asdeclare icount int ;select icount = count (*) from deleted ,Cp wheredeleted .pno = Cp .pnoif icount >= 1begindeclare ipno intselect ipno = deleted .pnofrom deleteddelete from cpwhere pno = ipnoend<3>對PAPER

16、的修改觸發(fā)器：當報紙的單價修改為負值或為空時，提示“輸 入單價不正確！ ”的信息，并取消修改操作。create trigger TR_Paper_U on paper for up date asdeclare ippr float ;select ippr = ppr from insertedif ippr <0 or ippr is nullbegi nraiserror （'輸入單價不正確！' ,16,1）rollbacktran sacti onendFi IJ DinjBao壬LJ數(shù)據(jù)庫關靠E1®0B-二奏 二j乗緬表 3 dbft ep|±

17、;(+J dU .pap er口龍涼rii._raper_D f7l rK_FAFEE_I SI ni._rapeT_U(5 )對PAPER表作插入、修改、刪除的多種操作,關注并記錄3種觸發(fā)江西報','-1')豐城報,95')器的觸發(fā)情況。in sert into paper(pno,pna,pp r)values('000006','in sert into paper(pno,pna,pp r)values('000007','up date paper set pp r=13.5 where pno='

18、;000001'up date paper set pp r=-10.5 where pno='000001'以上的代碼是對上述創(chuàng)建的觸發(fā)器的測試，測試結果表明創(chuàng)建的三個觸發(fā)器 都正確，都能根據(jù)對表的操作做出相應的觸發(fā)效果。(6)創(chuàng)建DDL觸發(fā)器，通過它能阻止對DingBao數(shù)據(jù)庫表結構的修改或表的刪除。create trigger safety on database for drop_table ,alter_tableas print '你必須失效DLL觸發(fā)器“ safety ”后，才能刪除或修改數(shù)據(jù)庫表！rollback ;(7)創(chuàng)建與使用 DDL觸發(fā)器:<1>在jxgl數(shù)據(jù)庫中創(chuàng)建 DDL觸發(fā)器，拒絕對庫中表的任何創(chuàng)建、修改或 刪除操作 create trigger safety on database for create_table ,drop_table ,alter_tableas print '你必須失效DLL觸發(fā)器“ safety ”后，才能創(chuàng)建、刪除或修改數(shù)據(jù)庫