试验六存储过程

1、实验六、存储过程一、实验目的(1) 掌握T-SQL流控制语句。(2) 掌握创建存储过程的方法。(3) 掌握存储过程的执行方法。(4) 掌握存储过程的管理与维护。二、实验内容1、创建简单存储过程创建一个名为stu_pr的存储过程，该存储过程能查询出051班学生的所有资料，包括学生的基本信息、学生选课信息(含未选课同学的 信息)。要求在创建存储过程前判断该存储过程是否已创建，若已创 建则先删除，并给出“已删除！ ”信息，否则就给出“不存在，可创 建！”的信息。if匸占 selecc name fxoid syaobj ecra where name= * 3ti2_pz 1 ind type= 1

2、 F r ibegindrcp procedureprint1 B 删除！，endelsePrint1不存在，可创建I，create procedure stu_prasselect studentfrem student _ef匸 u二二皀二 -zm sc on 吕匸udent.skq-bc.anowere classna='051 *;<J消息不存在,可创建！2、创建带参数的存储过程(1)创建一个名为stu_proc1的存储过程，查询某系、某姓名的学生的学号、姓名、年龄、选修课程名、成绩。系名和姓名在调用该存储过程时输入，其默认值分别为“% ”与“林% ”。执行该存储过程,用

3、多种参数加以测试。zf eKZBts select nair.e ItciddiciF Fioceaure 5tu_pTOClczreat已 pzraceduie stu pioc 1sysobjects wherefstu prccl1and 匸ype-1t r:isdept vazschaT 50 ="宅* 广专aznajne var char 3 = '林Iselect student dziG. siLdn亡，吕亞t亡rl二三工(二亡町二，b丄工匸h，©亡cd电丨；f cname , gradefrom sc7srndenty ccuisewrieie sc

4、 .5ro=5tudent. arc d sc cno=couz3« cjio .r.d 5uaine = 5nane = r. 3depc=|s:execute execu七丘executeStV_FXQcl计鼻机系3Xu_pxocl毎吕監曰rriE='赵青' 电吕曰宅口匸=，较件 丁程1 ;3tu_procl Badept='计草机系',鈿nane 林虹'；HI口结果Ld消息sno sname年aiame gradesno sname 年. ename gradesno5iiarn« 年cmamegrde11 2C11D103禺吉

5、 20高数8022C11Q10i赵音 20匚语言程序设计1anoj«>- «iii -! ni!->'!ii im mi mii 20110002aname 年MI 22cname高数gradeNULL22D1WD02林虹 22C语言程序设计32(3110002W 22J珅语言程序NULL（2）创建一个名为student_sc的存储过程，可查询出某学号段的同 学的学号、姓名、总成绩。（学号起始号与终止号在调用时输入，可 设置默认值。）执行该存储过程。丄/L 丄 IIAh. . -y 1 .玉斗LJrBIX-.=亠U«£ JT 丄.b

6、IfX*create procedure student_sc Sstart char -3 - , 0end c；iar i S:select student* sno . sname. gradefrom bc.strident讨here stden匸.3na-3c . sno =nd studenc r sna.>-SEart 且1scudenu snc-,=endozder tystudent.snomzh grade by student.snaexecutestudent_scT20110001' ,&end='20110003 F<爲结果_j消

7、息snosnamegraae1f 20110001张虹阳220110001张虹78320110001张虹83420110001张虹NULL sun1j 2563snosnamegrade1(201100021林虹NULL220110002林虹GO320110002林虹NULLsum1 60UUIKJUI:SO0snaniegrade120110003i lily7B220110003Mly9S320110003lily87420110003Mly75I sum1查询已成功执行.3FH9IXEWYKXSNA2(9 RIH) 3F3、创建带输出参数的存储过程（1）创建一个名为course_aver

8、age勺存储过程，可查询某门课程考试的平均成绩。总成绩可以输出，以便进一步调用if exxat (select name from sysobjecT；s where name= * coL；rse_average1 and type=, P 1 ) drop procedure course_averagegocreate procedure course_average cnc char 3)tint outp口匸同旦int output asselect> grade 3迦=曰二m gradefrom scwhere cno=cncideclare Javge ilii匸decla

9、re total intexecute course average 1 Z CC 1cutpLi J ©avg已 outputPrine，平均成绩为: 1-cfiSEiavge as char S )Print，总成绩为: 亠uamt gtotal aa char 8 - ;|蔭消息警告：聚合或真他金歸消除了空值.平均成绩为油2总成绩为注釣(2)创建一执行该存储过程的批处理，要求当平均成绩小于 60时， 显示信息为：“XX课程的平均成绩为：XX,其平均分未达60分”超 过60分时，显示信息为：“XX课程的平均成绩为：XX ”if exists select name irara s

10、yscbjeets wliere nazne- hcoLirse_avez：as= ' =r.d type- 1 F dicp piocedize c uzsveiageger&ete procedure ccuiBe average gunm亡 chai 2G . Jsura mt outputf 3avg mt outpias匚匸 Savg nvgi t <3s'=z , g二牡dufrom sc,coursewriere crene=£匚口己me ar.d 曰匚.匚11口=£：口口二吕匸，匚nodeclaie ename eiiar i2

11、0ldeclare C?avge 二nrdeclaxeintset Scr-arrA-语言程序设计ez&cure GOUzse_aveTage Scname . Meatal flutpur.kegirif Save-：60print case 3ename as varciai 亠 ，课程的平均成绩为= h-ear avge as vaieharj 4',具 elseprirt =ast 3cnan:e &s vaxchax ''课程的平均成绩为：1 -cast avge as vaxahaxj ;end.J消息总吾言程序设计课程的平均成绩为：決4、创

12、建带重编译及加密选项的存储过程创建一个名为update_sc并带重编译及加密选项的存储过程，可更新指定学号、指定课程号的学生的课程成绩。（学号、课程号在调用时输入）if exi select name from ays oh jec'ts where n&me=T Lipda 匸已 scT ir.d type=T P R drop procedure update_sc30creace procedure 口pdace_sc 3no char 20j ,cno chari20wit?i recoinpile f encryptiona.3select gradeftoil sc

13、 studentwhere sc.sno=3tudent,sno and sc.sno=6snocno=cnoexecute update_sc 12 0110001T f T 0Q21;V踣果匕消息gradeJILIIUILIIUILIUILI1 p8'SUIHIUIHIUIBIU |E5、使用T-SQL语句管理和维护存储过程(1)使用sp_helptext查看存储过程student_sc的定义脚本sp helptext student sc<二1结果j消息Text1 j create pnocedune 5tudent_sc (start charS,end .2 3S3 s

14、elect student sno .snarne, grade4 from sc student5 w+ienestudent.sno=sc.sno and student.snoH<st吕rt.6 order by student .sno7 compute sum grade by student .sno(2)使用select语句查看student_sc存储过程的定义脚本select厂from- syscoirmeEtswhere neme=psudden匸已匕 己二d rype * P<_ jil3结果j消息nameidjct/peidinfo statusb3ae_ec

15、ierra_vBrreplinfoparerrt_objciriateIistudent_sc164195635P1000002)11Wr7;39;4e.G232Etudent_sc164195635P1D00002013-11X)417:39:4fi.6233shjdent_sc164195635P1D0000201M14M17;39;4S.G234stuti 亡 nt_scT64195635P1D00002013-11-0417:33:48.6235etude nt_sc164195635P1000002013-1117:39:40.623£stjudent_sct6415553

16、5P1000cG2013-IUM 17:39:4fi.K3"7 /student_sc164195635P1000002013-11-Od 17:3943 S2 3<L©查询已威功执厅-aFMSBIZWYKltSW ©. 0 RTttJ 3FH9IXZ(3)将存储过程stu_pr改为查询学号为2011001的学生的详细资料alter praaedure sca_prasselect student. -fsc.-from st-uden 匸 ：m 三匸 r -.;-er -sir. sc on student 5no=sc. sno wile re 3d s

17、no 1 2Q1LQQQ1p;ex-ecuce sta pr;角赣岛消息nSnoSnemeSexGirttiJClassnoEntrancedateHomeaddrSdeptPost Gode Sn1i 2D11DW1男199241-12 OTflOrN0612011-054)1 凶;OOm南总计算机羔2DW1320220110M1穽虹男1991111-12 MiMiMtKIan-0M1南京计耸杭系2MM132C32D110M1嵌虹199Z41T2 00:00:00K1201T454H 00:M:00南駅计算机系21XM132042D110M1199211-12 M:MH»201Mk

18、MHH南京计算新詡20041320(4)删除存储过程stu_pr.drop procedure6、使用 SQL Server Management Studio管理存储过程(1)在SQL Server Management Studio中重新创建刚删除的存储过程 stu_pr.uescriprion: <uescripuion, r >CECEATE PROCEDURE3EGINselect student-sc.'from student left 口丄二亡二：cir sc on student,snoscsno where sc . 311q= 1 2jQL * ;ENDGO<L_j消息命令已成功完成*(2)查看存储过程stu_pr，并将该过程改为查询051班女生的所有资料。3FH9II21T1I. . _ LQT»ry3-3F1BIXZIT 醫迹廿宾 一 mjL*3TmmVKX . . Quryg. sal* 拥要USE Student infoSO/* 乂扌象：宮匸or