《數(shù)據(jù)庫SQL ServerSQLite教程》第8章課后習(xí)題答案

第8章存儲(chǔ)過程、觸發(fā)器和游標(biāo)一、填空題1.系統(tǒng)存儲(chǔ)過程、擴(kuò)展存儲(chǔ)過程、用戶自定義存儲(chǔ)過程2.procedure_name、@parameter、data_type、default、output、as3.trigger、添加、更新、刪除二、判斷題1.√2.×3.√三、選擇題1.BCD2.ABCD四、實(shí)踐題1.execxp_cmdshell'dirD:\sq\';2.createproceduresamp(@departnamevarchar(50),@numintoutput,@avgsalaryfloatoutput)asdeclare@aintdeclare@bfloatifnotexists(select*fromdepartmentwheredpname=@departname)return-100select@a=(selectcount(*)fromemployees,departmentwhereemployees.dpid=department.dpidanddepartment.dpname=@departname)if@a=0return-101select@b=(selectavg(employees.salary)fromemployees,departmentwhereemployees.dpid=department.dpidanddepartment.dpname=@departname)set@num=@aset@avgsalary=@b3.使用存儲(chǔ)過程和游標(biāo)(1)createviewv_scavgscoreasselects.s#,sname,cast(avg(score)asnumeric(5,1))asavgscorefromstudents,scwheres.s#=sc.s#groupbys.s#,sname(2)createprocedureproc_studscoreasdeclarestudscorecursorforselects#,sname,avgscorefromv_scavgscoreorderbyavgscoredescopenstudscoredeclare@s#varchar(12),@snamevarchar(20),@iint,@avgscorenumeric(5,1)set@i=1fetchnextfromstudscoreinto@s#,@sname,@avgscore--將游標(biāo)向下移1行，獲取的值放入變量中printspace(3)+'學(xué)號(hào)'+space(5)+'姓名'+space(5)+'平均分'+space(5)+'名次'while(@@fetch_status=0)beginprint@s#+space(5)+@sname+space(5)+cast(@avgscoreasvarchar)+space(5)+cast(@iasvarchar)fetchnextfromstudscoreinto@s#,@sname,@avgscores