sqlserver存儲過程詳細代碼超贊!

1、use jxgl-首先判斷有沒有已經建立up_getallstudents 存儲過程，有則先刪除if exists(select name from sysobjects where name = 'up_getallstudents'and type='p')drop procedure up_getallstudents-編寫存儲過程up_getallstudents,用于獲取學生表students的所有記錄create procedure up_getallstudentsasselect * from students-使用 execute執(zhí)行存儲過程

2、up_getallstudentsexec up_getallstudents-也可寫成execute up_getallstudents=*u_t3rih94bL_b*thplASBstd_efr«i3lu_|jeieph.diepl1DB,肖1Wfl11。方 QZdWOCLg QO'DODHULLNULLMULL11赳噬號DO W 00 WONULLHULL350071211 CitnCC&12S cornNULL跑i2W7isiiDigg魅祁1J9B5-11-1DDaO0DD DOD.1.話街城®&CLQqq carriNULL52M7ianD

3、io&者切麗女1SB77-11 00 00130 000宏歧詢職甲學comNULLdCO32D071211IB1CM黑11SB8-12-C2 Dam DO DOO奇叫吉9旦DC_Hqq oamNULLdD042007121101®甘畤190&-O7-13(K-(KkaO.OmNULLMULL:2DC/I211DI0B耳g男l(wèi)!8B3d&S 14 oaao DO.DODGRZgisjins iximiNULLMULLuyr1幗儀如 MW 0O.DO0如L旨供旦GH&ajl63MfliNULLMULL-編寫一個存儲過程up_insertstudent,完成

4、學生表students數(shù)據(jù)的插入-1 、不帶默認值的參數(shù)createprocedureup_insertstudentsid varchar (15), sname varchar (30), ssex char (10),sbirth datetime, sbirthplacevarchar ( 300 )asbegininsert into students(stu_id , stu_name , stu_sex , stu_birth , stu_birthplace )values(sid , sname , ssex , sbirth , sbirthplace )endexec u

5、p_insertstudent東省滕州市木石鎮(zhèn)''200712110111','肖玉峰','男','1975-02-05','山-等同于exec up_insertstudentsname ='肖玉峰',sid = '200712110111', ssex ='男',sbirth'1975-02-05', sbirthplace ='山東省滕州市木石鎮(zhèn)'drop procedureup_insertstudentstudents (st

6、u_id , stu_name , stu_sex , stu_birth , )values ('200712110110','馬繆','男','1986-010-17 00:00:00.000','廣東廣州')insert intostu_birthplace-編寫一個存儲過程 up_delstudentbyname, if object_id ( 'up_delstudentbyname' drop procedure up_delstudentbyname gocreate procedur

7、e up_delstudentbyname sname varchar ( 30) as begindelete from studentswhere stu_name = snameend根據(jù)輸入的學生姓名，刪除該學生記錄'p' ) is not null-調用存儲過程的代碼如下：exec up_delstudentbyname select * from students馬繆'-編寫一個存儲過程 up_getstuinformationbyname,學號、姓名、課程名和成績if object_id ( 'up_getstuinformationbyname&

8、#39;根據(jù)輸入的學生姓名，顯示該學生的'p' ) is not nulldrop procedure up_getstuinformationbynamegocreate procedure up_getstuinformationbynamesname varchar ( 30 )asbeginselect "S" . stu_id , stu_name , cour_name , scorefrom students "S”, courses "C”, course_score "CS”where "S"

9、; . stu_id="CS" . stu_idand "CS" . cour_id = "C" . cour_id and stu_name=sname end'仇立權'-ok-調用存儲過程的代碼如下:exec up_getstuinformationbyname結果消息stu_id5tu_nacour_rvameSCOI 1p00/1110102 I仇右權文512200712110102Cl立權馬克思王又星布原理G83200712110102中國近現(xiàn)代史要70450071211D102機立枚計算機專業(yè)英語71520

10、0712110102仇立權D&phi應用程序設汁42G2007121101C2仇立權操作系統(tǒng)877200712110102藏原理60-2、帶默認值的參數(shù)-編寫一個存儲過程up_insertstuwithdefault,給參數(shù)定義默認值，完成學生表students 數(shù)據(jù)的插入drop procedure up_insertstuwithdefaultgocreate procedure up_insertstuwithdefaultsid varchar ( 15), sname varchar ( 30), ssex char ( 10)='男',sbirth date

11、time , sbirthplace varchar ( 300 ) = '' , semail varchar ( 50)= ''asbegininsert into students(stu_id , stu_name , stu_sex , stu_birth , stu_birthplace , stu_email )values(sid , sname , ssex , sbirth , sbirthplace , semail )end-調用存儲過程的代碼如下：exec up_insertstuwithdefault sid= '11'

12、; , sname ='趙小樂', sbirth ='1976-07-05'select * from students-3、帶輸出參數(shù)-在創(chuàng)建存儲過程時，可以用關鍵字OUTPUT來創(chuàng)建一個輸出參數(shù)，另外，調用時也必須給出OUTPUT關鍵字-根據(jù)給定的學生姓名，獲取該生的平均成績use jxglgocreateprocedureup_getAvgScorebynameanamevarchar(30), avgscore int outputasbeginselectavgscore= avg (score ) from students "S"

13、;, course_score "CS"where "S" . stu_id="CS” . stu_id and "S" . stu_name = anameend-調用過程代碼declare avgscore int exec up_getAvgScorebyname print avgscore-不能寫成 print 'avgscore'囪消息亶£啜-avgscore可以改成任意變量名仇立權',avgscore output -ok或print('avgscore' ),

14、這是輸出字符串-等同于course_score cwhere s . stu_id=c . stu_id and s . stu_name='仇立權select avg ( score ) from students s-4、帶返回值的存儲過程-()print語句可以將用戶定義的消息返回給客戶端-編寫一個存儲過程up_insertstudent2,在插入學生數(shù)據(jù)前，先判斷一下學號是否存在，-如果存在，輸出要插入的學生的學號已經存在”；否則，插入學生數(shù)據(jù)，并返回”恭喜，數(shù)據(jù)插入成功"create procedure up_insertstudent2sid varchar (

15、15), sname varchar ( 30), ssex char ( 10)='男',sbirth datetimeasbeginif exists( select * from students where stu_id = sid )print ('要插入的學生的學號已經存在 ')-也可寫成raiserror('要插入的學生的學號已經存在',16, 1)elsebegininsert into students(stu_id , stu_name , stu_sex , stu_birth )values(sid , sname , s

16、sex , sbirth ) print ('恭喜，數(shù)據(jù)插入成功)-raiserror(' 恭喜，數(shù)據(jù)插入成功',16, 10)endenddrop procedure up_insertstudent2select * from students-調用過程代碼如下：exec up_insertstudent2 sid= '13' , sname ='張小飛',sbirth ='1983-02-01' -ok胃案"學生的學號已經存肖exec up_insertstudent2 sid'10' ,

17、sname ='張小龍',sbirth'1988-01-08'-ok山消息用戶氈入或更改了一條學生記錄，-：*_叫27._亦一*由:般發(fā)器被觸發(fā)II行受影響1 恭喜數(shù)據(jù)插入成功-()return 語句，return 語句可以從過程、批處理或語句塊中退出，不執(zhí)行其后繼語句create procedure up_delstudentbyname2 sname varchar ( 30)asbegindelete from studentswhere stu_name = snamereturn rowcount -行計數(shù)enddrop procedure up_de

18、lstudentbyname2-調用存儲過程的代碼如下：declare ret_val intexec ret_val = up_delstudentbyname2'張小龍' -okselect ret_val as "刪除的行”-也可寫成 print ret_val-5、帶變量的存儲過程/*在存儲過程可以定義變量，包括全局變量(廖量名)和局部變量(E量名)。用于保存存儲過程中的臨時結果編寫存儲過程up_getavgscorebyname2 ,根據(jù)輸入的學生姓名，計算該學生的平均成績。根據(jù)該生平局成績與全體學生平均成績的關系，返回相應信息*/use jxglgocre

19、ate procedure up_getavgscorebyname2aname varchar (30), resStr varchar ( 30) output asdeclarebegincurAvg decimal ( 18 , 2) where "S" . stu_id = "CS" . stu_id and "S" . stu_name = aname if curAvg > totalAvgdeclaretotalAvgdecimal (18,2)selecttotalAvgavg (scorefrom cours

20、escoreselectcurAvg=avg (scorefrom students "S”coursescore "CS"set resStr ='高于平均分' elseset resStr ='低于平均分'print('總平均分為'+convert(varchar( 18),totalAvg )print('該生平均分'+convert(varchar( 18),curAvg)print resStr drop procedure up_getavgscorebyname2-調用存儲過程的代碼de

21、clare resstring varchar ( 30)exec up_getavgscorebyname2'甘明，resstring output-這句和要求不一樣，不知道哪里出問題-6、使用output游標參數(shù)-output游標參數(shù)用來將存儲過程的局部游標傳遞回執(zhí)行調用的批處理、存儲過程或觸發(fā)器/*編寫一個帶有output 型游標參數(shù)的存儲過程up_getstudent_cursor,再編寫一個過程up_printstudentbycursor,對游標中的數(shù)據(jù)進行顯示*/-存儲過程up_getstudent_cursor的代碼如下create procedure up_getstudent_cursorstudent_cursor cursor varying outputasbeginset student_cursor= cursorfo