實(shí)驗(yàn)10 存儲(chǔ)過(guò)程

1、實(shí)驗(yàn) 存儲(chǔ)過(guò)程一、實(shí)驗(yàn)?zāi)康模?）掌握T-SQL流控制語(yǔ)句。（2）掌握創(chuàng)建存儲(chǔ)過(guò)程的方法。（3）掌握存儲(chǔ)過(guò)程的執(zhí)行方法。（4）掌握存儲(chǔ)過(guò)程的管理和維護(hù)。二、實(shí)驗(yàn)內(nèi)容1、創(chuàng)建簡(jiǎn)單存儲(chǔ)過(guò)程（1）創(chuàng)建一個(gè)名為stu_pr的存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程能查詢出051班學(xué)生的所有資料，包括學(xué)生的基本信息、學(xué)生的選課信息（含未選課同學(xué)的信息）。要求在創(chuàng)建存儲(chǔ)過(guò)程前請(qǐng)判斷該存儲(chǔ)過(guò)程是否已創(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ù)的存儲(chǔ)過(guò)程（1）創(chuàng)建一個(gè)名為stu_proc1的存儲(chǔ)過(guò)程，查詢某系、某姓名的學(xué)生的學(xué)號(hào)、姓

3、名、年齡，選修課程名、成績(jī)。系名和姓名在調(diào)用該存儲(chǔ)過(guò)程時(shí)輸入，其默認(rèn)值分別為“%”與“林%”。執(zhí)行該存儲(chǔ)過(guò)程，用多種參數(shù)加以測(cè)試。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)建一個(gè)名為Student_sc的存儲(chǔ)過(guò)程，可查詢出某段學(xué)號(hào)的同學(xué)的學(xué)號(hào)

5、、姓名、總成績(jī)。（學(xué)號(hào)起始號(hào)與終止號(hào)在調(diào)用時(shí)輸入，可設(shè)默認(rèn)值）。執(zhí)行該存儲(chǔ)過(guò)程。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ù)的存儲(chǔ)過(guò)程（1）創(chuàng)建一個(gè)名為Course_sum的存儲(chǔ)過(guò)程，可查詢某門課程考試的總成績(jī)。總成績(jī)可以輸出，以便進(jìn)一步調(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í)行該存儲(chǔ)過(guò)程的批處理，要求當(dāng)總成績(jī)小于100時(shí)，顯示信息為：“XX課程的總成績(jī)?yōu)椋篨X,其總分未達(dá)100分”。超過(guò)100時(shí)，顯示信息為：“XX課程的總成績(jī)?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ù)的總成績(jī)?yōu)椋?#39;+CAST(sumgrade AS varchar(20)+'，其總分未達(dá)到分。'ende

9、lse print '高數(shù)的總成績(jī)?yōu)椋?#39;+CAST(sumgrade AS varchar(20)+'。'4、創(chuàng)建帶重編譯及加密選項(xiàng)的存儲(chǔ)過(guò)程創(chuàng)建一個(gè)名為update_sc、并帶重編譯及加密選項(xiàng)的存儲(chǔ)過(guò)程，可更新指定學(xué)號(hào)、指定課程號(hào)的學(xué)生的課程成績(jī)。（學(xué)號(hào)、課程號(hào)由調(diào)用時(shí)輸入）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語(yǔ)句管理和維護(hù)存儲(chǔ)過(guò)程（1）使用sp_helptext查看存儲(chǔ)過(guò)程Student_sc的定義腳本sp_helptext Student_sc（2）使用select語(yǔ)句查看Student_sc存儲(chǔ)過(guò)程的定義腳本

11、（提示：通過(guò)查詢表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）將存儲(chǔ)過(guò)程stu_pr改為查詢學(xué)號(hào)為2011001的學(xué)生的詳細(xì)資料。alter procedure stu_prsno varchar(10)asselect distinct * from Studentwhere Sno='20110001&

12、#39;exec stu_pr sno='2011001'（4）刪除存儲(chǔ)過(guò)程stu_pr。drop procedure stu_pr6、使用SQL Server Management Studio管理存儲(chǔ)過(guò)程（1）在SQL Server Management Studio中重新創(chuàng)建剛刪除的存儲(chǔ)過(guò)程stu_pr選擇數(shù)據(jù)庫(kù)Student_info可編程性存儲(chǔ)過(guò)程，右擊“存儲(chǔ)過(guò)程”新建存儲(chǔ)過(guò)程出現(xiàn)如下畫面：修改存儲(chǔ)過(guò)程的名稱為stu_pr修改SQL語(yǔ)句，使之成為完整的存儲(chǔ)過(guò)程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）查看存儲(chǔ)過(guò)程stu_pr，并將該過(guò)程修改為查詢051班女生的所有資料?？删幊绦源鎯?chǔ)過(guò)程stu_pr，右擊stu_pr修改出現(xiàn)如下畫面：修改SQL語(yǔ)句，使之能查詢051班所有女生的資料的存儲(chǔ)過(guò)程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;