实验五存储过程和触发器的定义和使用资料

1、实验五存储过程和触发器的定义和使用徐龙琴设计制作实验五存储过程和触发器的定义和使用、实验目的1、掌握局部变量、全局变量、流程控制语句的使用方法2、 了解存储过程的类型和作用，并掌握使用对象资源管理器和Transact-SQL语句创建 存储过程的方法及使用方法。3、 理解触发器的特点和作用，并掌握使用Transact-SQL语言创建触发器的方法-实验内容1. 在学生成绩库中中有如下各表：学生表（Studen）学号姓名性别出生日期专业所在系联系电话020101杨颖01980-7-20计算机应用计算机88297147020102方露露01981-1-15信息管理计算机88297147020103俞奇

2、军11980-2-20信息管理计算机88297151020104胡国强11980-11-7信息管理计算机88297151020105薛冰11980-7-29水利工程水利系88297152020201秦盈飞01981-3-10电子商务经济系88297161020202董含静01980-9-25电子商务经济系88297062020203陈伟11980-8-7电子商务经济系88297171020204陈新江11980-7-20房建水利系88297171create database学生成绩数据库create tableStude nt(学号Char(6)notnull,姓名Char(8)notnul

3、l,性别Bitnotnull,岀生日期 smalldatetime 专业Char(10 ),所在系Cha&10),联系电话 Char (11) null )课程表(Course)课程号课程名教师开课学期学时学分101:计算机原理陈红2453102计算方法王颐3453103操作系统徐格2604104:数据库原理及应用应对刚3755105网络基础吴江江4453106高等数学孙中文1906107:英语陈刚1906108VB程序设计赵红韦3705create table Course(课程号 Char (3) not null, 课程名 Char ( 20) not null, 教师 Char ( 1

4、0), 开课学期Tinyint , 学时 Tinyint ,学分 Tinyint notnull,)学生选课成绩表（SC）学号】田千口 i=r. 课程号成绩02010110185020101102870201011038802010210158020102102630201041077602020210355020202107800202031035702020410371create table SC(学号 Char (6) notnull,课程号 Char (3) not null, 成绩 Smallint,)对三个表格分别导入，截图如下:实验五存储过程和触发器的定义和使用徐龙琴设计制作略

5、 licrasof 1： SQL SBEvez 弧注話阳1 SIulIioCF 咖E） f2B V）顶司疋 3itiS：|沁uu *皴 A0 . CSb-L S=tavtj 0 0.1 口总和-3和帶音直- L A r*EntTirrfafi: -U 島肌3：址duZsDi1 寸 i 不13 am库k苹m 壷T 口 db-: Sndoii 不匸秤那 卫口职诃 _j Scrvi ct trukiL 鬲二3百魅-丄量主性口口口口?*li.c4Jliac. Setti :ei: 如屉九r I：卽吵R“J KM*扭唱a学主RiMTL .Q岂jrtF号址dnld专afrittV.ltKH唏FateLu&

6、LWI LI-? CHW itXl朋期嗥L血邙TrueLHlh?-!? . UU D*.*LX由応MMH虫031MI*ax旳由L9ei4klO 0:00 直1 UU JO7#亡廿建UdilXi：OZO03JKfFMBL畑0 0D：OT蓟*B821TLma*驿11TnJsLO-7-20 DM 沁労建0fl7l?L構昶1iKAiJlLEXli*KElAUxUti 直客蕊1 F菲吕軒亍牛：楚羽家库廿砂计dististt -3.-JL.l- 2：孑展頭L性竝血晰目時丟ffi默,liGEUsoft 5QL Server Man.3c5tucLi!i(TFirxJTMI M 超 HEYi JiaQ. !3

7、fX -人4计二 Lj feftK血杜W恨-垃卿m阪A btnrTirf orfesi _ ACaniHur神MkOl.r二竝岸矣氏图-丄蕊*丄辛和k Jh -1 匚 EW K I 二1 dhc Stfc 2 4b 飢前tni-秤雨宝-i冋王伺*- La甬诙世丄I SarvL e* B*$k*ri 3宝主性箕制芒豐tafiiatiiciDL S*r*Le*s沁注g忙珪用弋咒KP)fIJu青-aikrCf*i! & 二北：Syj希I卫罕笔 叶铤 亍山调程导啾i名制币汗IS!芋朋脈计Hfl戶理BFiEZ53JOE:4JTi*三gj3453便梏Z+jiM应刘勺355gRasa吕UH43H卞HL点财咛

8、內L!Ub1W囲耐计Q亞干S5胆百WAiAUXAU丄l&M &rT曰血304IJAUllil 目 IUiJjlOX* 11 猶吧 llfflIII IttlO.i iBiU 工鼻 fli U.HCJ s* OP 唾询倉_j V丄二二一 h._ ._- _-:一.26 &sIQL Sirvw S n. LKS - SkUn(2SLMvet匕 SKM丄物悵!r烛卑j Ji Jvntiirfirtej iilwfli HirrijrfoCfflI -rrmaftsrx j rfi&*.?ES - So 57 垂 - dbsf i*TJ 口卄心学曼 Eryll 珂 H話jf 4 = select 妊窑

9、 frairi Studteot SC r-Sece 堆占】码丹 J SDudeiK-学号却：吿号Rll*?E. U 目緬飯 Eeoh SC.ltUdkn due .gitam一址嵩 辱|炉1 SEibjfinX.辛号 址学号ElMF-nE.itfl臨 j srIffl 1 JUl H CjHa 3 dfei sc 崛 3 J. Slwkii.!ira 更 is 可斯性S*rviic lb-&bMr N $lt 一I安仝憧沖Ll琏性 j0 L_J 的 I唯 J lilii-ric4|.ift Sgrvjr* 二石L Strw n!H -已羽用忙5 ：I遇一tiJm I,fj山列ICk ILia

10、(3) 循环语句的使用。 用WHILE语句编程计算1- 100之间所有能被3整除的数的个数及总和。DECLARE S SMALLINT , I SMALLINT , NUMS SMALLINTSET S=0SET I =1SET NUMS=0WHILE (l=100)負BEGINIF (I%3 = 0)BEGINSET S = SfISET NUMS=NUMS1ENDSET I =I + 1ENDPRINT SPRINT NUMS 利用GOTO语句求出从1加到5的总和。DECLARE S SMALLINT , 1 SMALLINTSET I =1SET S=0BEG:IF (lbMr&KL1P

11、? 381 i8 5-aSET寻ETBEGIF iE i 占BEGUfwuxncT. i t siALLnrr:iiEVIItifSET |G2T0PRINT d IJ1-817 0l SiS z i i BBC HI* 一丄曲5ftj甲誓卅泊r_j reJ Ir-li t Fi rt1* 14*. Sirri pr*i:r： KL et rlH宜罕甲亡悪 FISjuJir愉尸帧tiJ尸fWiHIS册I谯二八i血lyr-wfeLb（4）WAITFOR 语句的使用。 等待5秒后执行查询Student学生信息waitfor delay 00:00:05select *from Stude nt 等

12、到晚上10:20执行存储过程 update_all_stats。 BEGINWAITFOR TIME 10:20EXECUTE update all statsEND11 cm of I SHL Stiwr Iwuicewnt StudioJtff jJ 处理 JJEffliJJ宜比唆 圧目 XAiJ.1虚 ttllit）料問也j.eraiflej _j沽山耳丄皿二H s丄皱 7i；* Z第厂吟曰匚二逍空r - it SC . T - dt，. Wti MX*產檢！l 士 1心 KQL F.rvw 9 山冏-Ziui.r.ifEi.jkhLi一|沁崖p-Bi-f口匸 如】曰竽 DD: 口: ：

13、 53fl*Cr： *frai-翫川1电配IIkH-JiL孰IF氏忡刃J AJrE.! ur /irriJ dineiji.iif*hr*drtJ-曰N耒-出dlH Cw iv T-ibiSC国 _j dEia Ekoin4* -J fli 娟” ”如= J S#rifiUfjlLaj Sari-iciLS琨 弘Fb比坤也盘曲：lrns.t J Mer*隍Tikt?盘岂论1w01那过世迫8羽即1TIOGJIffl0i9o?ioQaininmnam關 29E?即02万flfflQi如刚iwsmaii-HFSlWH阖的盯卜fi5ETiW.irttOQD计im诵缈1加QEW禹旬T133HWKOIOT

14、计fiM602M051TXPSAWCOLI：- Jir丐:朋CGH3J1 51QlEHB-IDOttOiaO=-33F1G1CQ112Q2QigaFOsoanidD?ffi.囂=fi29?-应M17*J31T1型讪Stttl1lT1Fyil ：iT赛醫:百srmf昭特|朋雀I? Tth4hL1 V.O UW 訊u帥筒/h 铿It胖B體岸M lj斤it（5）CASE语句的使用。查询Student的SNO, SEX，如果SEX为“ 1”则输出“男”，如果为“ 0”输出“女”。SELECT 学号,性别=CASE性别WHEN 1 THEN 男WHEN 0 THEN 女ENDFROM Stude nt从

15、SC表中查询所有同学选课成绩情况，凡成绩为空者输出未考”小于60分输出不及格” 60分至70分输出 及格”、70分至90分输出 良好”大于或等于90分时输出 优 秀”SELECT学号，课程号成绩=CASENULLTHEN 未考THEN不及格IAND成绩70THEN及格AND成绩90THEN良好THEN1 优秀WHEN成绩ISWHEN 成绩 =60WHEN 成绩 =70WHEN 成绩 =90ENDFROM SClicTwofl： SOL Strwvr iKuicewfit Studios还紳兰壬卑倒綁” t-IA qL T.fht 9 n L19S -诒.415I- 異酌i, 丁 .fetfcF

16、M C.-t I 43wr+iir irrt* J judj-iur irit jLkUl3 IS3*i dl* Cwit 虫=4IU工S 3 dt StnitTii 土 丄 Ul:7D m 1*TO JJ；I 駆0 THEFJi - 90 7BH i 龙隸聲驹壬一105,ttE丄囲閉1.QfliinI w tt5029101噬U用找阵L歌3CCUlnID?J曲！U4Q2 01 QfTRitIrtl fiFlli iW 囂Fvi * 中1s野02910?ia?臥J苹 m=*r F 插书代理：旳SHIMupPLS7ccoefE103不盘捋惬gceoHBIOS101EUirtT -J fl *1J

17、 1A i： l.Wi ?iutenL5,iAuiim ilf ill.r 出丨学片轴沪匡童W Fi rL H fr疔绅刊】（k I3、存储过程的的使用。在上面学生成绩库中完成如下操作: （1 ）创建如下 不带参数的简单存储过程：查询成绩在60至80分之间的学生的学号和课程号。create procedure SCListasselect 学号，课程号from SCwhere 成绩 60 and 成绩 80（2 ）分别创建如下的 带输入参数的存储过程：根据用户输入的学号，删除该学生选课成绩记录；create procedurestude ntqk1（萨号 char （ 10）asdelete

18、成绩from SCwhere 学号=号向SC表中插入数据。createproceduresccharu(学号char(10),课程号 char ( 10), 成绩 int )asin sertintoSCvalues ( 学号，课程号,成绩)(3) 创建如下的 带输出参数的存储过程，并写出一个执行该过程的例子： 根据用户输入的学号，课程号，输出其成绩。create procedure grade(学号 char (10), 课程号 char (10) output , 成绩 int output ) asselect 成绩=成绩from SCwhere 学号=学号 and 课程号=课程号dec

19、lare成绩 intexecute grade 020101, 101, 成绩 output詡田 垃ei血娶vi商aioi虞目tn ifim ec(w)吃匚 樹hiJ習曲测 口色吕垃注：H鼻申SB V爭；?7ii空駝霹,a x桶亦I； 出 PC - 201L27CVBf (SOL ScftW*d jaf?j-_j好至步B U|宇生亍吐述隹ir _i_j去KI J奚浊iU 3 士JDduEe3 二 rJD_SC3 二 ffcDStldftltr(t- J WJE(i J 刖i-新咗i Pfr-lrl丄砂处芹勺二 dbo.SCUd，i dbo.StmPfcti71Fl丄御丢慎為W J fiM川J

20、WPQ j6It) ir 3ruurIH i 些grm*F - dbtj.SC 豆-Ug篁 PC MIMIITOWBF 7 遴UJiwryl呷-抵莊 X二注LQ30 5-L P匸UEW得号疗程号E反播2 z - e ptExied;jt - gi=h.；i0 解横 avtio) cfiKpuc.,1 mt QLicpmiMi理1砖：子箭芯-责二;异号-坤号山理程号好程号掏亍.=1 s r- 0从谢 ict=gii.il- :牡汀即蹶 一”砰t =!-.3Fjrn邀査闰已FWlijwPC-JflLlDLJTWFJ s 41 3-L)Er2U 3M畀阿对归 rEWr 區)茅生期曲丽 H：DQ LD

21、 I -ffChirwselect 成绩=成绩(4) 创建存储过程 proc_t1，要求实现如下功能：输入专业名称，产生该专业学生的选课 情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用此存储过 程，显示“信息管理”专业学生的选课情况列表。createprocedureproc_t1(专业 char (10)asselectStude nt.学号,Student.专业，Student.姓名，Course .课程号，Course.课程名，Course .学分，成绩fromStude nt ,Course , SCwhereStude nt.学号=SC.学号and Cour

22、se .课程号=SC.课程号 and Student.专业=专业execproc_t1信息管理(5) 对学生成绩库中已创建的存储过程proc_t1进行修改，要求实现如下功能：输入专业实验五存储过程和触发器的定义和使用徐龙琴设计制作名称,产生该专业所有男生的选课情况列表,其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用修改后的存储过程，显示“信息管理”专业男生的选课情况列表。alterprocedureproc_t1(专业 char (10 )-里倒雛I-痔決F .学生抵建岸ih j zsta-5出 J dboGjmw0 二 dbDtuJentID睡”,訓注-口可鈕I 逊喘媚-二

23、ugHList7I m dbc.XQrcPrw I jSK-衣曲1出Er. _魅辰il 1 PXT111fPC-S01LT? (f (I 井Lj5 ?-2E IMJLTOVUizj(52)J $肓 45pj 24Br -決-氏5rcirselectStude nt.学号,Stude nt.专业，Student.姓名，Course .冲了口 口.课程号 ,Course.课程名，Course .学分，成绩fromStude nt ,Course,SCwhereStude nt.学号=SC.学号and Course .课程号=SC.课程号专业and 性别=1execproc_t1信息管理asand

24、Student.专业=:.M DftSQL Sen. er viargerfr： Slc 二-i 3 lii4F薦处羽应彌可ir 闻tL（c糜,h）2 ?rilXNl 自図陥B矗3目秸習;：1U空趣戲!圖圖*飞昨宜勺收目占虽口三些墾气XS - dbcSaderc 證db。/t Xlit*: rroMaite专号业10氏二0. 孝g.mMl九专业:也 M-S IlCE SL-jUfent .CCLUfle.K鼻二二二兰 ZLUOeLL. 孝号-H学号d.2血4課程号-监腿号A Jcddeot.专业Y专业 and怡别-丄口翌規71. ti曲維羅4購忑毋欝器懈ic? 觀（6）删除学生成绩库中的 存储

25、过程proc_t2 。drop procedure proc_t1徐龙琴设计制作assum (casewhe nsum (casewhe nsum (casewhe nsum (casewhe nend计算机原理J10刖成绩 成绩 成绩 成绩group by课程名between 60 and 69 then 1 else 0 end ) as60-69between 70 and 89 then 1 else 0 end ) as70-89between 90 and 100 then 1 else 0 end ) as 90-100between 0 and 59 then 1 else 0

26、 end ) as不及格(1) DML触发器的使用实验五存储过程和触发器的定义和使用create procedure scqk (课程名 char(10)(7)编写存储过程，要求实现如下功能：输入课程名称，产生该课程各分数段及其相应人 数的成绩分布情况统计。beginselect课程名7 Fl陌号2 PfSBtW)TKr.i|If4、触发器的使用。在上面学生成绩库中完成如下操作exec scqkLT I打pr-7nmi；Tiny 冷 n ti) ? _-?c i ml： Tiwri- Li n n- (5?jfrom SC,Coursewhere SC.课程号=Course.课程号and课程名

27、=课程名门目旳 TM：TitZtC 昌堰-一皑聲容. 6MB 7 M)蔣頤莎I 0 I CT7 i r 怦 7 tJ 亍 4 ；=* ： -.- - dhc ；： Pt .-迥叫応甲1 些尸实验五存储过程和触发器的定义和使用徐龙琴设计制作 创建一个INSERT触发器tri_sc_insert，当向sc表中添加数据时，如果添加的数据与 Stude nt表中的数据不匹配（没有对应的学号），则将此数据删除。create trigger tri_sc_inserton SCfor in sertasbegindeclare bh char （6）select bh =inserted.学号from i

28、n sertedif not exists （select 学号 from Student where Student .学号=bh） delete SC where 学号=bhend 创建一个UPDATE触发器tri_sc_upd，用来防止用户修改SC表的成绩。create trigger tri_sc_upd on SCfor updateasif update （成绩）beginprint 修改失败。raiserror （不能修改SC表的成绩,16,10）rollback tran sacti onend 创建一个级联修改触发器trigger_1，即当修改学生课程表Course中的某门课的课程号时，对应学生的选课表SC中的课程号也作修改create trigger trigger_1on Coursefor updateasif update （课程号）beg in upd