《数据库SQL ServerSQLite教程》第8章课后习题答案

第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