SQL Server 实用教程（第三版）实验七答案.doc

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，否者输出1CREATE 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 ID1>ID2 SET BJ=0ELSE SET BJ=1ENDDECLARE BJ INTEXEC COMPA 000001,108991,BJ OUTPUTSELECT BJ3. 创建添加职员记录的储存过程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,phonenumber,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 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 SALARY_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 SEQ<3 AND OK=0 BEGIN SET SEQ=SEQ+1 IF X_EM_ID=EM_ID SET OK=1 FETCH SALARY_DIS INTO X_EM_ID,ACT_IN ENDCLOSE SALARY_DISDEALLOCATE SALARY_DISENDGODECLARE OK BITEXEC TOP_THREE 108991,OK OUTPUTSELECT OK创建存储过程，要求当一个员工的年份大于6年时将其转到经理办公室工作CREATE PROC YG EM CHAR(6)ASBEGINIF(SELECT WorkYear FROM EmployeesWHERE EmployeeID=EM)>6UPDATE EmployeesSET DepartmentID=3WHERE EmployeeID=EMENDGOEXEC YG 000001创建存储过程，根据每个员工的学历将收入提高500元CREATE PROC SA_IN EDU CHAR(4)ASBEGINUPDATE 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=Employees.EmployeeID AND Education=本科创建存储过程，使用游标计算本科及以上学历的员工在总员工人数中所占比例使用命令的方式修改存储过程的定义（2） 触发器1. 向Employees表插入或修改一个记录时，通过触发器检查记录的DepartmentID值在Departments表中是否存在，若不存在，则取消插入或修改操作CREATE TRIGGER Departmentsupdate ON dbo.DepartmentsFOR INSERT,UPDATEASBEGINIF(SELECT DepartmentID FROM INSERTED)NOT IN(SELECT DepartmentID 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 DepartmentID=(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. 创建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 * 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列应该满足参照的完整性规则，请用触发器实现两个表的参照完整性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=(SELECT 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 EmployeeID=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(A>B)UPDATE SalarySET InCome=InCome+(A-B)*500WHERE EmployeeID 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 EmployeeID=(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,WorkYear,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(EmployeeID,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