《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
10、partmentID=(SELECT DepartmentID FROM DELETED)END3. 删除Departments表中记录的同时删除该记录“DepartmentID”字段值在Employees表中对应的记录CREATE TRIGGER Departmentddelete ON DepartmentsFOR DELETEAS BEGINDELETE FROM EmployeesWHERE DepartmentID=(SELECT DepartmentID FROM DELETED)ENDDELETE FROM DepartmentsWHERE DepartmentID=34. 创建
11、INSERTED OF触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在Employees中是否存在，如果存在则执行操作，如果不存在则提示“员工号不存在”CREATE TRIGGER EM_EXISTS ON SalaryINSTEAD OF INSERTASBEGINDECLARE EmployeeID CHAR(6)SELECT EmployeeID=(SELECT EmployeeID FROM INSERTED)IF(EmployeeID IN(SELECT EmployeeID FROM Employees)INSERT INTO SalarySELECT
12、* FROM INSERTEDELSEPRINT员工号不存在ENDINSERT INTO SalaryVALUES(000005,1000,800)5. 创建DDL触发器，当删除YGGL数据库的一个表时，提示“不能删除表”，并回滚删除表的操作CREATE TRIGGER TABLE_DELETEON DATABASEAFTER DROP_TABLEASPRINT不能删除该表ROLLBACK TRANSACTIONGODROP TABLE Employees对于YGGL数据库，表Employees的EmployeeID列与表Salary的EmployeeID列应该满足参照的完整性规则，请用触发器
13、实现两个表的参照完整性CREATE TRIGGER Salaryins ON SalaryFOR INSERT,UPDATEASBEGINIF(SELECT EmployeeID FROM INSERTED) NOT IN(SELECT EmployeeID FROM Employees)ROLLBACKENDCREATE TRIGGER Employeesupdate ON dbo.EmployeesFOR UPDATEASBEGINUPDATE SalarySET EmployeeID=(SELECT EmployeeID FROM INSERTED)WHERE EmployeeID=(S
14、ELECT EmployeeID FROM DELETED)ENDCREATE TRIGGER Employeesdelete ON EmployeesFOR DELETEASBEGINDELETE FROM SalaryWHERE EmployeeID=(SELECT EmployeeID FROM DELETED)ENDINSERT INTO SalaryVALUES (000005,2000,1000)UPDATE EmployeesSET EmployeeID=000006WHERE EmployeeID=020010DELETE FROM EmployeesWHERE Employe
15、eID=000006当修改表Employees时，若将Employees表中员工的工作时间增加1年，则将收入增加500，若增加2年则增加1000，依次增加。若工作时间减少则无变化CREATE TRIGGER EM_WORKYEAR ON EmployeesAFTER UPDATEASBEGINDECLARE A INT,B INTSET A=(SELECT WorkYear FROM INSERTED)SET B=(SELECT WorkYear FROM DELETED)IF(AB)UPDATE SalarySET InCome=InCome+(A-B)*500WHERE EmployeeI
16、D IN(SELECT EmployeeID FROM INSERTED)ENDUPDATE EMPLOYEESSET WORKYEAR=12WHERE EMPLOYEEID=000001创建UPDATE触发器,当Salary表中Income值增加500时，outcome值增加50CREATE TRIGGER SA_INCOME ON SalaryFOR UPDATEASBEGINIF(SELECT InCome FROM INSERTED)-(SELECT InCome FROM DELETED)=500)UPDATE SalarySET OutCome=OutCome+50WHERE Em
17、ployeeID=(SELECT EmployeeID FROM INSERTED)ENDSELECT INCOME,OUTCOMEFROM SALARYWHERE EMPLOYEEID=000001UPDATE SalarySET InCome=InCome+500WHERE EmployeeID=000001SELECT INCOME,OUTCOMEFROM SALARYWHERE EMPLOYEEID=000001创建INSTEAD OF触发器，实现向不可更新视图插入数据CREATE VIEW A_VIEWASSELECT Employees.EmployeeID,Name,WorkYe
18、ar,InCome,OutComeFROM Employees,SalaryWHERE Employees.EmployeeID=Salary.EmployeeIDGOCREATE TRIGGER GXSTON A_VIEWINSTEAD OF INSERTASBEGINDECLARE EI CHAR(6),NAME CHAR(10),WY TINYINT,IC FLOAT,OC FLOATSELECT EI=EmployeeID,NAME=Name,WY=WorkYear,IC=InCome,OC=OutCome FROM insertedINSERT INTO Employees(Empl
19、oyeeID,Name,WorkYear) VALUES(EI,NAME,WY)INSERT INTO Salary VALUES(EI,IC,OC)ENDINSERT INTO A_VIEWVALUES(000011,小芳,3,2000,1500)SELECT * FROM A_VIEWWHERE EmployeeID=000011SELECT * FROM EmployeesWHERE EmployeeID=000011SELECT * FROM SalaryWHERE EmployeeID=000011创建DDL触发器,当删除数据库时，提示“无法删除”并回滚删除操作CREATE TRIGGER T_DELETEON ALL SERVERAFTER DROP_DATABASEASPRINT不能删除该表ROLLBACK TRANSACTIONGODROP DATABASE YGGL