第8章存儲過程、觸發器和游標一、填空題1.系統存儲過程、擴展存儲過程、用戶自定義存儲過程2.procedure_name、@parameter、data_type、default、output、as3.trigger、添加、更新、刪除二、判斷題1.√2.×3.√三、選擇題1.BCD2.ABCD四、實踐題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.使用存儲過程和游標(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--將游標向下移1行，獲取的值放入變量中printspace(3)+'學號'+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