存儲過程及觸發(fā)器的練習

1、一、實驗目的（1）理解存儲過程的概念，掌握存儲過程的使用方法，加深對客戶/服務機制的理解。（2）學習觸發(fā)器的使用，體會觸發(fā)器執(zhí)行的時機，加深對觸發(fā)器功能和作用的理解知識回顧1.創(chuàng)建存儲過程建立存儲過程的語句如下：CREATE PROCEDURE過程名（參數(shù)表）AS SQL語句創(chuàng)建存儲過程示例創(chuàng)建一個不帶參數(shù)的存儲過程CREATE PROCEDURE brow_student AS SELECT * FROM student WHERE classno=200501執(zhí)行存儲過程的方法(1) 獨立執(zhí)行存儲過程:EXEC brow_student (2) 在INSERT語句中執(zhí)行存儲過程: INSE

2、RT INTO student_b EXEC brow_student 創(chuàng)建帶參數(shù)的存儲過程例:顯示指定學號的學生信息。CREATE PROCEDURE stu_proc1 sno1 char(8)AS SELECT * FROM student WHERE sno=sno1 例：為參數(shù)設置缺省值。CREATE PROCEDURE sc_proc1 (sno1 char(8), cno1 char(20) =c01 )AS SELECT * from sc WHERE sno=sno1 and cno=cno1存儲過程的返回值可以用return或select返回存儲過程的值例：create p

3、rocedure sc_proc2 (sno1 char(8) asbegin declare sno2 char(8) select sno2=count(*) from sc where sno=sno1 if sno20 return 10 Else return 99endgodeclare s intexec s=sc_proc2 20050101 if s=10 print 找到記錄elseprint 未找到記錄Go create procedure sc_proc3 (sno1 char(8),cno1 char(4) as Declare var1 char(8) if (se

4、lect grade from sc where sno= sno1 and cno=cno1 )60 set var1 =及格 Else set var1 =不及格 Select 是否及格=var1 更改和刪除存儲過程更改存儲過程：ALTER PROCEDURESQL Server中更改存儲過程，就是用新定義的存儲過程替換原來的定義。語句格式： ALTER PROCEDURE 同定義從當前數(shù)據(jù)庫刪除用戶定義的存儲過程。語句格式： DROP PROCEDURE 2.觸發(fā)器（Trigger） 對應于數(shù)據(jù)的操縱類型，SQL Server 中的觸發(fā)器可分為:INSERT觸發(fā)器UPDATE觸發(fā)器DEL

5、ETE觸發(fā)器 當表中數(shù)據(jù)被修改（增、刪、改）時，SQL Server自動執(zhí)行相應的觸發(fā)器。建立觸發(fā)器的語句格式CREATE TRIGGER 觸發(fā)器名 ON 表名 |視圖名 WITH ENCRYPTION FOR INSERT,UPDATA,DELETE AS SQL操作語句表名（視圖名）：觸發(fā)該觸發(fā)器的表名或視圖名。WITH ENCRYPTION： 對觸發(fā)器的定義文本加密。INSERT,UPDATA,DELETE ：說明觸發(fā)觸發(fā)器的事件。一個定義語句允許定義多個觸發(fā)事件。 SQL操作語句：指定觸發(fā)器動作。如果要指定多個觸發(fā)器操作，需用BEGINEND將它們組成語句塊。觸發(fā)器中可使用的特殊表使用

6、觸發(fā)器時，SQL Server提供了兩張?zhí)厥獾呐R時表： inserted表deleted表 表存在于高速緩存中，與創(chuàng)建觸發(fā)器的表有相同的結構。用戶可以使用該表檢查某些修改操作的前后狀態(tài)。并基于此狀態(tài)采取行動。例如，可以使用該表的內容作為查詢操作的判斷條件，但要在FROM中寫出使用的表名（inserted 或deleted）。用戶不能直接修改該表中的數(shù)據(jù)。臨時表inserted表：存放INSERTE和UPDATE語句的新數(shù)據(jù)。deleted表：存放DELETE和UPDATE語句的舊數(shù)據(jù)。UPDATE觸發(fā)器可使用deleted表和inserted表INSERT類觸發(fā)器INSERT 觸發(fā)器：每在學生

7、備份表中增加一條記錄時，同時要在學生表中刪除一條相應的學生記錄。CREATE TRIGGER student_BACKON student_bFOR INSERTAS DELETE from student FROM student_b WHERE student.sno = student_b.snoUPDATE類觸發(fā)器例：對成績表SC中的grade屬性執(zhí)行update語句時觸發(fā)：修改成績時，如果新成績比原成績低，則恢復原成績而不做修改。CREATE TRIGGER up_grade ON sc FOR UPDATE AS UPDATE sc SET grade=deleted.grade

8、from deleted WHERE sc.sno = deleted.sno And o=o AND sc.gradeY時：返回值12，提示“X應小于Y！”當查找后沒有滿足條件的記錄時：返回值-10，提示“對不起！沒有滿足條件的記錄！”（2）建立存儲過程P2，查詢選修了某門課程的學生信息（其中課程名，返回值自行設計）（3）建立存儲過程P3，將某一專業(yè)的所有學生的成績放在一個臨時表中（4）建立存儲過程P4，計算某一個專業(yè)的某一門課和的平均成績,顯示出專業(yè)名稱、課程名稱和平均成績 2）在學生表、成績表和課程表中輸入原始數(shù)據(jù)后，運行上述所建立的存儲過程，觀察其執(zhí)行結果。2、觸發(fā)器的創(chuàng)建與執(zhí)行注意：在建立以下的觸發(fā)器時，請刪除各個之間的參照完整性和用戶定義完整性1）觸發(fā)器的建立（1）在課程表和成績表上建立一組觸發(fā)器，實現(xiàn)課程表中的課程號與成績表中的課程號之間的參照關系。（2）在學生表上建立一個插入和修改觸發(fā)器，實現(xiàn)當修改學生性別時，檢查是否取值在“男”或“女”之間，如果是，由插入或修改成功，否由失敗（3）在成績表上建立一個修改類的觸發(fā)器，實現(xiàn)當修改成績時，檢查修改后的成