1、實驗 存儲過程一、實驗?zāi)康模?）掌握T-SQL流控制語句。（2）掌握創(chuàng)建存儲過程的方法。（3）掌握存儲過程的執(zhí)行方法。（4）掌握存儲過程的管理和維護。二、實驗內(nèi)容1、創(chuàng)建簡單存儲過程（1）創(chuàng)建一個名為stu_pr的存儲過程，該存儲過程能查詢出051班學(xué)生的所有資料，包括學(xué)生的基本信息、學(xué)生的選課信息（含未選課同學(xué)的信息）。要求在創(chuàng)建存儲過程前請判斷該存儲過程是否已創(chuàng)建，若已創(chuàng)建則先刪除，并給出“已刪除！”信息，否則就給出“不存在，可創(chuàng)建！”的信息。if exists (select * from sysobjects where name='stu_pr' and type=&

2、#39;P')begindrop procedure stu_prprint '已刪除！'endelseprint '不存在，可創(chuàng)建！'create procedure stu_prasselect distinct * from Student sleft join SC on s.Sno=SC.Snoleft join Course c on c.Cno=sc.Cnowhere Classno='051'執(zhí)行：exec stu_pr2、創(chuàng)建帶參數(shù)的存儲過程（1）創(chuàng)建一個名為stu_proc1的存儲過程，查詢某系、某姓名的學(xué)生的學(xué)號、姓

3、名、年齡，選修課程名、成績。系名和姓名在調(diào)用該存儲過程時輸入，其默認(rèn)值分別為“%”與“林%”。執(zhí)行該存儲過程，用多種參數(shù)加以測試。if exists (select * from sysobjects where name='stu_proc1' and type='P')begindrop procedure stu_proc1print '已刪除！'endelseprint '不存在，可創(chuàng)建！'create procedure stu_proc1sdept varchar(10)='%',sname varch

4、ar(10)='林%'asselect Sname,s.Sno,YEAR(getdate()-YEAR(Birth) Age,Cname,Grade from Student s,Course c,SCwhere s.Sno=sc.Sno and c.Cno=sc.Cnoand s.Sname like sname and s.Sdept like sdept執(zhí)行：、exec stu_proc1、exec stu_proc1 sdept ='%',sname ='林%'（2）創(chuàng)建一個名為Student_sc的存儲過程，可查詢出某段學(xué)號的同學(xué)的學(xué)號

5、、姓名、總成績。（學(xué)號起始號與終止號在調(diào)用時輸入，可設(shè)默認(rèn)值）。執(zhí)行該存儲過程。if exists (select name from sysobjects where name='Student_sc' and type='P')drop procedure Student_scgocreate procedure Student_scsno_begin varchar(10)='20110001',sno_end varchar(10)='20110103'asselect s.Sno,Sname,SUM(grade) tot

6、al_grade from Student s,SCwhere s.Sno=sc.Sno and s.Sno between sno_begin and sno_endgroup by s.Sno,Sname執(zhí)行：exec Student_sc3、創(chuàng)建帶輸出參數(shù)的存儲過程（1）創(chuàng)建一個名為Course_sum的存儲過程，可查詢某門課程考試的總成績。總成績可以輸出，以便進一步調(diào)用。if exists (select name from sysobjects where name='Course_sum' and type='P')drop procedure Co

7、urse_sumcreate procedure Course_sumcname varchar(10)='%'asselect SUM(grade) total_grade,COUNT(sno) sno from Course c,SCwhere c.Cno=sc.Cno and Cname like cname 執(zhí)行：exec Course_sum '高數(shù)'（2）創(chuàng)建一執(zhí)行該存儲過程的批處理，要求當(dāng)總成績小于100時，顯示信息為：“XX課程的總成績?yōu)椋篨X,其總分未達100分”。超過100時，顯示信息為：“XX課程的總成績?yōu)椋篨X”。create proce

8、dure sum_grade2cname varchar(10),sum smallint outputasselect sum=sum(grade)from Course c,SCwhere c.Cno=sc.Cno and Cname like cnamedeclare sumgrade smallintexec sum_grade2 '高數(shù)',sumgrade outputif sumgrade<100begin print '高數(shù)的總成績?yōu)椋?#39;+CAST(sumgrade AS varchar(20)+'，其總分未達到分。'ende

9、lse print '高數(shù)的總成績?yōu)椋?#39;+CAST(sumgrade AS varchar(20)+'。'4、創(chuàng)建帶重編譯及加密選項的存儲過程創(chuàng)建一個名為update_sc、并帶重編譯及加密選項的存儲過程，可更新指定學(xué)號、指定課程號的學(xué)生的課程成績。（學(xué)號、課程號由調(diào)用時輸入）if exists (select name from sysobjects where name='update_sc' and type='P')drop procedure update_sccreate procedure update_sccno

10、varchar(10),sno varchar(10),grade intwith recompile,encryption -重編譯，加密asupdate SCset grade=gradewhere SC.Cno=cno and SC.Sno=snoexec update_sc '001','20110001','80'5、使用T-SQL語句管理和維護存儲過程（1）使用sp_helptext查看存儲過程Student_sc的定義腳本sp_helptext Student_sc（2）使用select語句查看Student_sc存儲過程的定義腳本

11、（提示：通過查詢表sysobjects和表syscomments） select o.id, c.textfrom sysobjects o inner join syscomments c on o.id = c.idwhere o.type = 'p' and = 'Student_sc'（3）將存儲過程stu_pr改為查詢學(xué)號為2011001的學(xué)生的詳細(xì)資料。alter procedure stu_prsno varchar(10)asselect distinct * from Studentwhere Sno='20110001&

12、#39;exec stu_pr sno='2011001'（4）刪除存儲過程stu_pr。drop procedure stu_pr6、使用SQL Server Management Studio管理存儲過程（1）在SQL Server Management Studio中重新創(chuàng)建剛刪除的存儲過程stu_pr選擇數(shù)據(jù)庫Student_info可編程性存儲過程，右擊“存儲過程”新建存儲過程出現(xiàn)如下畫面：修改存儲過程的名稱為stu_pr修改SQL語句，使之成為完整的存儲過程CREATE PROCEDURE stu_pr - Add the parameters for the st

13、ored procedure hereASBEGIN- SET NOCOUNT ON added to prevent extra result sets from- interfering with SELECT statements.SET NOCOUNT ON; - Insert statements for procedure hereSELECT distinct * from Student sleft join SC on s.Sno=SC.Snoleft join Course c on c.Cno=sc.Cnowhere Classno='051'END（2）查看存儲過程stu_pr，并將該過程修改為查詢051班女生的所有資料。可編程性存儲過程stu_pr，右擊stu_pr修改出現(xiàn)如下畫面：修改SQL語句，使之能查詢051班所有女生的資料的存儲過程ALTER PROCEDURE dbo.stu_pr - Add the parameters for the stored procedure hereASBEGIN- SET NOCOUNT ON added to prevent extra result sets from- interfering with SELECT statements.SET NOCOUNT ON;