1、共享知識分享快樂實驗六存儲過程、實驗目的(1) 掌握T-SQL流控制語句。(2) 掌握創建存儲過程的方法。(3) 掌握存儲過程的執行方法。(4 )掌握存儲過程的管理和維護。、實驗內容1、創建簡單存儲過程(1)創建一個名為stu_pr的存儲過程，該存儲過程能查詢出051班學生的所有資料，包括 學生的基本信息、學生的選課信息(含未選課同學的信息)。要求在創建存儲過程前請判斷該存儲過程是否已創建，若已創建則先刪除，并給出已刪除！ ”信息，否則就給出不存在，可創建！ ”的信息。if exists (select * from sysobjects where name = stu_prand type

2、 =P)begindrop procedure stu_prprint 已刪除！ endelseprint 不存在，可創建！SQLQuerylql - ZQh+,(2qy-PCzqy (52)* if eKLJts iselect * froixiwhere naxne=lstu_pr1 and cype=* P1白 begindrop procedure stu_prPrint -B刪除！-endelseL rint 不存在，可創建！J|_b消息|不存在，可創建！create procedure stu_prasselectdist inct* fromStude ntsleftjoi nS

3、C on s .Sno =SC. Snoleftjoi nCourse con c. Cno =sc . CnowhereClass no= 051SQLQueryl.fql ” ZQ.(iqy PCzqy (52)* SQLQuery4.sq 日 create procedure scu_prasH select distinct - from Studenc s left oin SC on s.Sno=SC-Sno 亠亡壬匸 j oj.n Course c an s Cnc = SQ - Cno -where Cla53no=0Si1* 1 b消患命令已成功完成執行：exec stu_p

4、rSQLQuerylfiql - ZQ. .(zqy PCzqy (52)*exec scu_pr囚結果|由消息|Sno| Sname| Sex | BirthQassnoEntrance dateHameaddrSdept11 2D05DDD1i張虹S 13844H-01 00:00:0005120054B-01 00:00:00南京CS22OT5OTQ2女 15S3-11-12OTOOOO05120009-01 00 00%北京cs32OO5OOOJ胃 13S211 00:00 000512(xmi OOQC;OO上海MAd20050004干右胃 19821100:00:000512305

5、砂)1 tDD:MJ天津MA2、創建帶參數的存儲過程(1)創建一個名為stu_proc1的存儲過程，查詢某系、某姓名的學生的學號、 姓名、年齡, 選修課程名、成績。系名和姓名在調用該存儲過程時輸入，其默認值分別為與林。執行該存儲過程，用多種參數加以測試。if exists (select * from sysobjects where name = stu_proc1 and type =P) begindrop procedure stu_proc1print 已刪除！endelseprint 不存在，可創建！f SQLQucryLsql - ZQ+(zqy-PCVqy (弓壽尸 if ex

6、ists i select - from 3L: i： e:wheie nair.e - stu procl ad type -T PT- -白 begindiop procedure snu praelprini:記刪囪，-endelseL print 1不存在，可創建！須I肉消息| 不存在；可創建！create procedure stu_proc1sdept varchar (10 )= % , sname varchar (10)=林 %asselectSname , s . Sno , YEAR( getdate ()- YEAR( Birth ) Age , Cname , Gra

7、defrom Students , Course c, SCwhere s. Sno =sc . Sno and c. Cno =sc . Cnoand s. Sname like sname and s. Sdept like sdeptSQLQuryl.ql - ZQtiqy-PCXqy (52)*El crea匸亡 procedure s匸口_proclsdept varchar 10 ： = * % * r snair.e varchar ； 10 i = %： *A3P select Sname, 3 * Sno t YEAR getdate ( j - YEAR Birth i A

8、ge, Cnamef Grade from Student; a t Course u#5 亡where aSno=sc * Sno and c.Cno=sc 亡noLand 3 . Sname lite snaiEe and 5, Sdept like adeptJL肉消息|命令已成功完成。執行：、exec stu_proc1SQLQueryl.ql 亠 ZQ.(zqy-PCzqy (52)*exec bClI procl二結果| A肖息ISnarrw | SnoAge | CnameGrade1林紅 20110002%= :P*21C語言程序設計60、 exec stu_proc1 sde

9、pt =% , sname =林%SQLQueryLsql ” ZQ.(zqy P?qy (52)亡st亡c3Zu prccl Ssdepr(2)創建一個名為Student_sc的存儲過程，可查詢出 某段學號 的同學的學號、姓名、總成績。(學號起始號與終止號在調用時輸入，可設默認值)。執行該存儲過程。if exists(selectname from sysobjectswheren ame =Stude nt_scandtype =P)dropprocedureStude nt_scgocreateprocedureStude nt_scsno_beginvarchar ( 10)= 201

10、10001, sno_endvarchar ( 10)= 20110103asselect s. Sno , Sname , SUM( grade ) total_gradefromStudents , SCwhere s. Sno =sc . Sno and s. Sno betweensno_beginand sno_endgroup by s . Sno , SnameZQY- PC, Stud entj rrfo - d bo-Stu dent SQLQuery _sq I - ZQ_(zqy- PCz|y (52)*fE ifselect name from 373 2? i = w

11、here nairie= Student_3cr and type= * PT)1 drop procedure Studenr_scgo3 create procedjre Student sc3snci_begin varchar (10 =,2DLLGOQ1, f 3nc_end varchar 10j =1 2 01LQL0 3F select 3, Snc, Snamer SUM (giadep tcxal_gradefranc Suud皀nt: sTSCwhere s . Sno=sc . Sno and 3. Sna fcet./.Tesn 3sna_begin and 3snc

12、_end- gi?oiip by s . Sno, Snair.e4消息命令已成功完成*執行：exec Stude nt_scZQV-PC.Student_inf - dbo.Student 黃 exec Scudent_5cJ二結果| ：消息|SnoSnametotal grade1! 20110001張虹2K220110002林紅603201101Q3趙青803、創建帶輸出參數的存儲過程(1)創建一個名為Course_sum的存儲過程，可查詢某門課程考試的總成績。 總成績可以輸出，以便進一步調用。if exists (select name from sysobjects where na

13、me =Course_sumandtype =卩) drop procedure Course_sumSQLQueryl.sql - ZQ._(2qy-PCzqy (52)*fif eclats select name frorr二u二where najne=T匚ard type=TP)drop piocedare Ccurfle_3um甲I命令已成功完成。create procedure Course_sumcname varchar (10)= %asselect SUM( grade ) total_grade , COUNT( sno ) snofrom Course c, SCwhe

14、re c. Cno =sc . Cno and Cname likecname/SClLQueryl-sql - ZQ(2qy-PC2qy (52)*EJ create procedure Course_sum3cnair.e varchar 10 =1 % 1 as select 3LM (grade1 total grade,CCUNT(ano sno from Course cfSCwhere c. Cnc = 3c. Cno ard Cnair.e like 3cnair.e_j消息命令已成功完成。執行：exec Course_sum 高數SQLQueryl.sql - ZQ.(zqy

15、-PCzqy ( exec Ccur5e_snn 高數4n結果.j消息total_grade sno1 1E9 I 2(2)創建一執行該存儲過程的批處理，要求當總成績小于100時，顯示信息為：“ XX課程的總成績為：XX,其總分未達100分”。超過100時，顯示信息為：“XX課程的總成績為：XX。 create procedure sum_grade2cname varchar (10), sum smallintoutputasselectsur=sum ( grade )from Course c, SCwhere c. Cno =sc . Cno and Cname likecnameS

16、QLQueryL$|l - ZQfzqy-PQzqy (52)*曰 create procedure sum_grade2Scname varchar10 rsmallin匸 ou匸pn匸as select gradefrom Course ctSC-where c. Cno=5c . Cnc ind 匸nair.e Lite scnair.e|門山西消息命令已成功完成廠declare sumgrade smalli ntexec sum_grade2 高數 , sumgrade outputif sumgrade 100beginprint高數的總成績為：+ CAST ( sumgrade

17、AS varchar (20)+ ，其總分未達到分。endelseprint高數的總成績為：+ CAST ( sumgrade AS varchar ( 20)+ 。/SQLQueryLiqHPgqy (52)r|日 declare 3singrade smallintgo 曲S現癱 高數飛餉唄護a亡 output3 if siLgracielt)OHbeginprint，高數的總成績為：* CAST Ss-.u.grade A3 varchar 20 亠、， 其總分未達到分 -endelseprint 高數的總成績為=“CAST s-3s-Jtgrade AS varchar 20+ Q *

18、帀蠹消息命令已成功完成#4、創建帶重編譯及加密選項的存儲過程創建一個名為update_sc、并帶重編譯及加密選項的存儲過程，可更新指定學號、指定課程號的學生的課程成績。(學號、課程號由調用時輸入)if exists (selectname from sysobjects where name =update_sc andtype =卩)drop procedure update_scSQLQueryl.Eql - ZQ(zqy-PC2qy (52)*|E) i exists select name roir :fc ：: where name=1 update_sc and type=F：I

19、drop procedure update sc*ruLj微息命令已成功完戰。create procedure update_sccno varchar (10), sno varchar ( 10), grade intwith recompile , encryption -重編譯，加密 asupdate SCset grade =gradewhere SC. Cno=cno and SC. Sno =snoSQLQuery2,sql - ZQ“(zqyPCqy (54)* SQLQueryl.iql - ZQ” create procedure update sccna varchar(

20、10if ?sno varchar10i,Sgrade intwith recorcpile, encryption - 重編譯，加密asn update 5Cset grade=gradewhere 勻匚.匚no=cno and SC.Snc5snoexec update_sc 001 ，20110001, 80ZQYPCStuidentjnfo - dbo,SC SQLQueryLsql - ZQ -(z exec update sc 1Q01*,*201100011,1801I IBVl弘消息u行受影響)5、使用T-SQL語句管理和維護存儲過程（1）使用sp_helptext查看存儲過程

21、Student_sc的定義腳本（2）使用select語句查看Student_sc存儲過程的定義腳本（提示：通過查詢表sysobjects禾口表 syscomments）selecto. id , c.textfromsysobjectsoinnerjoin syscomme ntscono. id = c.idwhereo. type =pando. name = Student_scZQV-PC.Student_info - dbo.SC SQLQueryl.sql - ZQ.(zqy PC?qy CS2) select o id# c cexcfromo _riner jcin 273zc

22、irr.-EnsB can a . id = c. idwhere 0 type =and c nair.e - 1 Stunt_sc 4H結杲昂也idtext1S37573D22 | atate pfocedure Student_sc sno_begin vardiar(1 DJJDIIDOOr.snend ware.(3)將存儲過程stu_pr改為查詢學號為2011001的學生的詳細資料。alter procedure stu_prsno varchar ( 10)asselectdistinct* fromStudentwhere Sn o =20110001SQLQuryS.sql

23、- ZQfqy匚遼qy (54)* SQLQu alter proced口蘭己 5tL； pr&snc varchar- (10asn select 匸inct - fron: ScudencI where Sno=*20110001 *|出消息| 命令已矗功完成。exec stu_pr s no =2011001SQLQueryS.sql - ZQ(zqy- PCzqy (54)* exec stu pr sno=1201100011SQLQueryl.sqi - ZQ.(zqy-PCzjqy (52)* L17*1國結果ti消息SrwSrwme Sot BirthQassno Entran

24、ce_dHteHome _addr Sdept Postcode1f20liSw_l冷閩M 13924)M1 M：D0：M 0512011-DM100:00:00 南京OS 2DD413wii iwnii m-ni j(4)刪除存儲過程stu_pr。drop procedure stu_pr消息SQLQueiySql - ZQ.(zqy-PCzqy (54)drop procedure s匸li pr命令已成功完成。6、使用 SQL Server Management Studio 管理存儲過程(1 )在SQL Server Management Studio中重新創建剛刪除的存儲過程stu_

25、pr選擇數據庫Student_info 宀可編程性宀存儲過程，右擊“存儲過程”宀新建存儲過程卑微如螻蟻、堅強似大象日_j可壽層| _d .W E _l S 田_i玄聲 出匸禮膽+ j翹 “ li規則 al _|獣認3 _J 計 fillJHW新墮存倚乩程(N)r, 擁議L) 啟卻 PcwrrShdl(H) 報表型刷新F)+ A SerJe Broker出現如下畫面:-values below.一一 This block of conuterLtB will nac te included in -the definition of the proced口工皀”SET ANSI_NULLS ON

26、GOSET QUO TE D_ I DENT I FIE R CMGOAuthor:Create, dare iDeflcripnian: CREATE FROCEDUREProcedare Narr.e , sysnaite.修改存儲過程的名稱為 stu_prCREATE PROCEDURE一一 Add the parameters for 匸h亡 scored procd 0Framif sysnairef 0pl Datatype For Earam DaJcaDyp_Fc?i:_pNram修改SQ語句，使之成為完整的存儲過程CREATE PROCEDURE stu_pr-Add the

27、 parameters for the stored procedure hereASBEGIN-SET NOCOUNT ON added to preve nt extra result sets from-i nterferi ng with SELECT stateme nts.SET NOCOUNT ON;-I nsert stateme nts for procedure hereSELECT distinct* fromStudentsleft join SC on s . Sno =SC. Sno left join Course c on c. Cno =sc . Cnowhe

28、re Class no =051END CREATE PROCEDURE stuprr- Add the paraiLeters for the scored procedure here AS BEGIN - SET NOCOUNT ON added to prevent extra result seta from-intexfezzng with SELECT statements.SET NOCOUNT ON；一一 Insert suaements for procedure hereGOJ消息命令已成功完成。(2)查看存儲過程stu_pr，并將該過程修改為查詢051班女生的所有資料。

29、可編程性t存儲過程tstu_pr，右擊stu_pr宀修改dJ出w A E如曲1 M 刃 db&Coune.iumLl 匚db&Ttu_pi 測 辺 dbQ.$4ude- 圄 Hborunn 日兄呻d4l土注u皿as 土丄百 土竝3a土 _j fcua 土丄計酣電hJ 口 Servke Broke*妥全性IJ 5ftjdcn|Jnfo_?rtlier IJ 5ftidenl3IKi3jnfo_isrt X5GL.W5dbo5tu_pqSET一 131.1；?SET HCK顯旳過驗Ml.現行存枝過圖;Fl.用曲iHii竝力聞9曲獨建華(V):&8(M|iMFLR皐也i尺i出現如下畫面:-一 Des

30、cription: 3 ALTER PROCEDURE Idbol crlN 一5 EA Bm-mAda rHe _parajretersforthe stored procedure hereSET nOCOUNT ON added to prevent extra result sets froir.一一 ：Let; erf ering wj匸？i SELECT statements , SET NOCOUNT OW；一一 Insert staenients for procedure hereSELECT distinct from Student 3 left join SC on s *Sno=SC5no left oil： Course c on c,Cnosc,匚口口- where Classno=r 0511END修改SQ語句，使之能查詢051班所有女生的資料的存儲過程ALTER PROCEDURE dbo . stu_pr-Add the param