SQL Server 实用教程(第三版)实验七答案.doc
《SQL Server 实用教程(第三版)实验七答案.doc》由会员分享,可在线阅读,更多相关《SQL Server 实用教程(第三版)实验七答案.doc(11页珍藏版)》请在三一文库上搜索。
1、200901501116 刘玉佩存储过程和触发器的使用(1) 存储过程1. 创建存储过程,使用Employees表中的员工人数来初始化一个局部变量,并调用这个存储过程CREATE PROC TEST NUMBER1 INT OUTPUTAS BEGINDECLARE NUMBER2 INTSET NUMBER2=(SELECT COUNT(*) FROM Employees)SET NUMBER1=NUMBER2ENDGODECLARE NUM INTEXEC TEST NUM OUTPUTSELECT NUM2. 创建存储过程,比较两个员工的实际收入,若前者比后者高就输出0,否者输出1CRE
2、ATE PROC COMPA ID1 CHAR(6),ID2 CHAR(6),BJ INT OUTPUTAS BEGINDECLARE SR1 FLOAT,SR2 FLOATSELECT SR1=INCOME-OUTCOME FROM SALARY WHERE EMPLOYEEID=ID1SELECT SR2=INCOME-OUTCOME FROM SALARY WHERE EMPLOYEEID=ID2IF ID1ID2 SET BJ=0ELSE SET BJ=1ENDDECLARE BJ INTEXEC COMPA 000001,108991,BJ OUTPUTSELECT BJ3. 创建添
3、加职员记录的储存过程EmployeeAddCREATE PROC EmployeeAdd(employeeid char(6),name char(10),education char(4),birthday datetime, workyear tinyint,sex bit,address char(40),phonenumber char(12),departmentid char(3)AS BEGININSERT INTO EmployeesVALUES(employeeid,name,education,birthday,workyear, sex,address,phonenumb
4、er,departmentid)ENDGOEXEC EmployeeAdd990230,刘超,本科,840909,2,1,武汉,85465213,3创建一个带有OUTPUT游标参数的存储过程,在Employees表中生命并打开一个游标CREATE PROC EM_CURSOR EM_CURSOR CURSOR VARYING OUTPUTASBEGINSET EM_CURSOR=CURSOR FORWARD_ONLY STATIC FORSELECT * FROM EmployeesOPEN EM_CURSORENDGODECLARE MYCURSOR CURSOREXEC EM_CURSOR
5、 EM_CURSOR=MYCURSOR OUTPUTFETCH NEXT FROM MYCURSORWHILE(FETCH_STATUS=0)BEGIN FETCH NEXT FROM MYCURSORENDCLOSE MYCURSORDEALLOCATE MYCURSORGO创建存储过程,使用游标确定一个员工的实际收入是否排在前三位。结果为1表示是,结果为0表示否CREATE PROC TOP_THREE EM_ID CHAR(6),OK bit OUTPUTASBEGINDECLARE X_EM_ID CHAR(6)DECLARE ACT_IN INT,SEQ INTDECLARE SAL
6、ARY_DIS CURSOR FOR SELECT EmployeeID,INCOME-OUTCOME FROM SALARY ORDER BY INCOME-OUTCOME DESCSET SEQ=0SET OK=0OPEN SALARY_DISFETCH SALARY_DIS INTO X_EM_ID,ACT_IN WHILE SEQ6UPDATE EmployeesSET DepartmentID=3WHERE EmployeeID=EMENDGOEXEC YG 000001创建存储过程,根据每个员工的学历将收入提高500元CREATE PROC SA_IN EDU CHAR(4)ASB
7、EGINUPDATE SalarySET InCome=InCome+500FROM Salary,EmployeesWHERE Employees.EmployeeID=Salary.EmployeeID AND Education=EDUENDSELECT InComeFROM Salary,EmployeesWHERE Salary.EmployeeID=Employees.EmployeeID AND Education=本科GOEXEC SA_IN 本科GOSELECT InComeFROM Salary,EmployeesWHERE Salary.EmployeeID=Employ
8、ees.EmployeeID AND Education=本科创建存储过程,使用游标计算本科及以上学历的员工在总员工人数中所占比例使用命令的方式修改存储过程的定义(2) 触发器1. 向Employees表插入或修改一个记录时,通过触发器检查记录的DepartmentID值在Departments表中是否存在,若不存在,则取消插入或修改操作CREATE TRIGGER Departmentsupdate ON dbo.DepartmentsFOR INSERT,UPDATEASBEGINIF(SELECT DepartmentID FROM INSERTED)NOT IN(SELECT Depa
9、rtmentID FROM Departments)ROLLBACKENDUPDATE EmployeesSET DepartmentID=8WHERE EmployeeID=0000012. 修改Departments表“DepartmentID”字段的值,该字段在Employees表中的对应值也做相应的修改CREATE TRIGGER Departmentsupdate ON dbo.DepartmentsFOR UPDATEASBEGINUPDATE EmployeesSET DepartmentID=(SELECT DepartmentID FROM INSERTED)WHERE De
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server 实用教程第三版实验七答案 实用教程 第三 实验 答案
链接地址:https://www.31doc.com/p-10729237.html