6实验六存储过程

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