實驗五存儲過程和觸發(fā)器的定義和使用資料

1、實驗五存儲過程和觸發(fā)器的定義和使用徐龍琴設(shè)計制作實驗五存儲過程和觸發(fā)器的定義和使用、實驗?zāi)康?、掌握局部變量、全局變量、流程控制語句的使用方法2、 了解存儲過程的類型和作用，并掌握使用對象資源管理器和Transact-SQL語句創(chuàng)建 存儲過程的方法及使用方法。3、 理解觸發(fā)器的特點和作用，并掌握使用Transact-SQL語言創(chuàng)建觸發(fā)器的方法-實驗內(nèi)容1. 在學(xué)生成績庫中中有如下各表：學(xué)生表（Studen）學(xué)號姓名性別出生日期專業(yè)所在系聯(lián)系電話020101楊穎01980-7-20計算機應(yīng)用計算機88297147020102方露露01981-1-15信息管理計算機88297147020103俞奇

2、軍11980-2-20信息管理計算機88297151020104胡國強11980-11-7信息管理計算機88297151020105薛冰11980-7-29水利工程水利系88297152020201秦盈飛01981-3-10電子商務(wù)經(jīng)濟系88297161020202董含靜01980-9-25電子商務(wù)經(jīng)濟系88297062020203陳偉11980-8-7電子商務(wù)經(jīng)濟系88297171020204陳新江11980-7-20房建水利系88297171create database學(xué)生成績數(shù)據(jù)庫create tableStude nt(學(xué)號Char(6)notnull,姓名Char(8)notnul

3、l,性別Bitnotnull,岀生日期 smalldatetime 專業(yè)Char(10 ),所在系Cha&10),聯(lián)系電話 Char (11) null )課程表(Course)課程號課程名教師開課學(xué)期學(xué)時學(xué)分101:計算機原理陳紅2453102計算方法王頤3453103操作系統(tǒng)徐格2604104:數(shù)據(jù)庫原理及應(yīng)用應(yīng)對剛3755105網(wǎng)絡(luò)基礎(chǔ)吳江江4453106高等數(shù)學(xué)孫中文1906107:英語陳剛1906108VB程序設(shè)計趙紅韋3705create table Course(課程號 Char (3) not null, 課程名 Char ( 20) not null, 教師 Char ( 1

4、0), 開課學(xué)期Tinyint , 學(xué)時 Tinyint ,學(xué)分 Tinyint notnull,)學(xué)生選課成績表（SC）學(xué)號】田千口 i=r. 課程號成績02010110185020101102870201011038802010210158020102102630201041077602020210355020202107800202031035702020410371create table SC(學(xué)號 Char (6) notnull,課程號 Char (3) not null, 成績 Smallint,)對三個表格分別導(dǎo)入，截圖如下:實驗五存儲過程和觸發(fā)器的定義和使用徐龍琴設(shè)計制作略

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 不匸秤那 衛(wèi)口職訶 _j Scrvi ct trukiL 鬲二3百魅-丄量主性口口口口?*li.c4Jliac. Setti :ei: 如屜九r I：卽吵R“J KM*扭唱a學(xué)主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構(gòu)昶1iKAiJlLEXli*KElAUxUti 直客蕊1 F菲呂軒亍牛：楚羽家?guī)熵ド坝媎ististt -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衛(wèi)罕筆 葉鋌 亍山調(diào)程導(dǎo)啾i名制幣汗IS!芋朋脈計Hfl戶理BFiEZ53JOE:4JTi*三gj3453便梏Z+jiM應(yīng)劉勺355gRasa呂UH43H卞HL點財嚀

8、內(nèi)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 口卄心學(xué)曼 Eryll 珂 H話jf 4 = select 妊窯

9、 frairi Studteot SC r-Sece 堆占】碼丹 J SDudeiK-學(xué)號卻：吿號Rll*?E. U 目緬飯 Eeoh SC.ltUdkn due .gitam一址嵩 辱|爐1 SEibjfinX.辛號 址學(xué)號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) 循環(huán)語句的使用。 用WHILE語句編程計算1- 100之間所有能被3整除的數(shù)的個數(shù)及總和。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秒后執(zhí)行查詢Student學(xué)生信息waitfor delay 00:00:05select *from Stude nt 等

12、到晚上10:20執(zhí)行存儲過程 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*產(chǎn)檢！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關(guān) 29E?即02萬flfflQi如剛iwsmaii-HFSlWH闔的盯卜fi5ETiW.irttOQD計im誦緲1加QEW禹旬T133HWKOIOT

14、計fiM602M051TXPSAWCOLI：- Jir丐:朋CGH3J1 51QlEHB-IDOttOiaO=-33F1G1CQ112Q2QigaFOsoanidD?ffi.囂=fi29?-應(yīng)M17*J31T1型訕Stttl1lT1Fyil ：iT賽醫(yī):百srmf昭特|朋雀I? Tth4hL1 V.O UW 訊u帥筒/h 鏗It胖B體岸M lj斤it（5）CASE語句的使用。查詢Student的SNO, SEX，如果SEX為“ 1”則輸出“男”，如果為“ 0”輸出“女”。SELECT 學(xué)號,性別=CASE性別WHEN 1 THEN 男WHEN 0 THEN 女ENDFROM Stude nt從

15、SC表中查詢所有同學(xué)選課成績情況，凡成績?yōu)榭照咻敵鑫纯肌毙∮?0分輸出不及格” 60分至70分輸出 及格”、70分至90分輸出 良好”大于或等于90分時輸出 優(yōu) 秀”SELECT學(xué)號，課程號成績=CASENULLTHEN 未考THEN不及格IAND成績70THEN及格AND成績90THEN良好THEN1 優(yōu)秀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 出丨學(xué)片軸滬匡童W Fi rL H fr疔紳刊】（k I3、存儲過程的的使用。在上面學(xué)生成績庫中完成如下操作: （1 ）創(chuàng)建如下 不帶參數(shù)的簡單存儲過程：查詢成績在60至80分之間的學(xué)生的學(xué)號和課程號。create procedure SCListasselect 學(xué)號，課程號from SCwhere 成績 60 and 成績 80（2 ）分別創(chuàng)建如下的 帶輸入?yún)?shù)的存儲過程：根據(jù)用戶輸入的學(xué)號，刪除該學(xué)生選課成績記錄；create procedurestude ntqk1（薩號 char （ 10）asdelete

18、成績from SCwhere 學(xué)號=號向SC表中插入數(shù)據(jù)。createproceduresccharu(學(xué)號char(10),課程號 char ( 10), 成績 int )asin sertintoSCvalues ( 學(xué)號，課程號,成績)(3) 創(chuàng)建如下的 帶輸出參數(shù)的存儲過程，并寫出一個執(zhí)行該過程的例子： 根據(jù)用戶輸入的學(xué)號，課程號，輸出其成績。create procedure grade(學(xué)號 char (10), 課程號 char (10) output , 成績 int output ) asselect 成績=成績from SCwhere 學(xué)號=學(xué)號 and 課程號=課程號dec

19、lare成績 intexecute grade 020101, 101, 成績 output詡田 垃ei血娶vi商aioi虞目tn ifim ec(w)吃匚 樹hiJ習(xí)曲測 口色呂垃注：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 區(qū))茅生期曲麗 H：DQ LD

21、 I -ffChirwselect 成績=成績(4) 創(chuàng)建存儲過程 proc_t1，要求實現(xiàn)如下功能：輸入專業(yè)名稱，產(chǎn)生該專業(yè)學(xué)生的選課 情況列表，其中包括專業(yè)、學(xué)號、姓名、課程號、課程名、成績、學(xué)分等。并調(diào)用此存儲過 程，顯示“信息管理”專業(yè)學(xué)生的選課情況列表。createprocedureproc_t1(專業(yè) char (10)asselectStude nt.學(xué)號,Student.專業(yè)，Student.姓名，Course .課程號，Course.課程名，Course .學(xué)分，成績fromStude nt ,Course , SCwhereStude nt.學(xué)號=SC.學(xué)號and Cour

22、se .課程號=SC.課程號 and Student.專業(yè)=專業(yè)execproc_t1信息管理(5) 對學(xué)生成績庫中已創(chuàng)建的存儲過程proc_t1進行修改，要求實現(xiàn)如下功能：輸入專業(yè)實驗五存儲過程和觸發(fā)器的定義和使用徐龍琴設(shè)計制作名稱,產(chǎn)生該專業(yè)所有男生的選課情況列表,其中包括專業(yè)、學(xué)號、姓名、課程號、課程名、成績、學(xué)分等。并調(diào)用修改后的存儲過程，顯示“信息管理”專業(yè)男生的選課情況列表。alterprocedureproc_t1(專業(yè) char (10 )-里倒雛I-痔決F .學(xué)生抵建岸ih j zsta-5出 J dboGjmw0 二 dbDtuJentID睡”,訓(xùn)注-口可鈕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.學(xué)號,Stude nt.專業(yè)，Student.姓名，Course .沖了口 口.課程號 ,Course.課程名，Course .學(xué)分，成績fromStude nt ,Course,SCwhereStude nt.學(xué)號=SC.學(xué)號and Course .課程號=SC.課程號專業(yè)and 性別=1execproc_t1信息管理asand

24、Student.專業(yè)=:.M DftSQL Sen. er viargerfr： Slc 二-i 3 lii4F薦處羽應(yīng)彌可ir 聞tL（c糜,h）2 ?rilXNl 自図陥B矗3目秸習(xí);：1U空趣戲!圖圖*飛昨宜勺收目占雖口三些墾氣XS - dbcSaderc 證db。/t Xlit*: rroMaite專號業(yè)10氏二0. 孝g.mMl九專業(yè):也 M-S IlCE SL-jUfent .CCLUfle.K鼻二二二蘭 ZLUOeLL. 孝號-H學(xué)號d.2血4課程號-監(jiān)腿號A Jcddeot.專業(yè)Y專業(yè) and怡別-丄口翌規(guī)71. ti曲維羅4購忑毋欝器懈ic? 觀（6）刪除學(xué)生成績庫中的 存儲

25、過程proc_t2 。drop procedure proc_t1徐龍琴設(shè)計制作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觸發(fā)器的使用實驗五存儲過程和觸發(fā)器的定義和使用create procedure scqk (課程名 char(10)(7)編寫存儲過程，要求實現(xiàn)如下功能：輸入課程名稱，產(chǎn)生該課程各分數(shù)段及其相應(yīng)人 數(shù)的成績分布情況統(tǒng)計。beginselect課程名7 Fl陌號2 PfSBtW)TKr.i|If4、觸發(fā)器的使用。在上面學(xué)生成績庫中完成如下操作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 些尸實驗五存儲過程和觸發(fā)器的定義和使用徐龍琴設(shè)計制作 創(chuàng)建一個INSERT觸發(fā)器tri_sc_insert，當向sc表中添加數(shù)據(jù)時，如果添加的數(shù)據(jù)與 Stude nt表中的數(shù)據(jù)不匹配（沒有對應(yīng)的學(xué)號），則將此數(shù)據(jù)刪除。create trigger tri_sc_inserton SCfor in sertasbegindeclare bh char （6）select bh =inserted.學(xué)號from i

28、n sertedif not exists （select 學(xué)號 from Student where Student .學(xué)號=bh） delete SC where 學(xué)號=bhend 創(chuàng)建一個UPDATE觸發(fā)器tri_sc_upd，用來防止用戶修改SC表的成績。create trigger tri_sc_upd on SCfor updateasif update （成績）beginprint 修改失敗。raiserror （不能修改SC表的成績,16,10）rollback tran sacti onend 創(chuàng)建一個級聯(lián)修改觸發(fā)器trigger_1，即當修改學(xué)生課程表Course中的某門課的課程號時，對應(yīng)學(xué)生的選課表SC中的課程號也作修改create trigger trigger_1on Coursefor updateasif update （課程號）beg in upd