SQL实验七：存储过程和触发器的使用.doc

学校代码： 10128学 号： 201220905048 数据库原理及应用实验报告(题 目：存储过程和触发器的使用 学生姓名：孙跃学 院：理学院系 别：数学系专 业：信息与计算科学班 级：信计12-2任课教师：侯睿 二 一 五 年 五 月91、 实验目的1.掌握存储过程的使用方法；2.掌握触发器的使用方法。2、 实验内容1.存储过程；2.触发器。3、 实验程序及结果1、存储过程(1)创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。USE YGGLGOCREATE PROCEDURE TEST NUMBER1 int OUTPUT AS BEGIN DECLARE NUMBER2 int SET NUMBER2=(SELECT COUNT(*)FROM Employees) SET NUMBER1=NUMBER2ENDGOUSE YGGLGODECLARE num intEXEC TEST num OUTPUTSELECT num(2)创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1。USE YGGLGOCREATE PROCEDURE COMPA ID1 CHAR(6),ID2 CHAR(6),BJ INT OUTPUT AS BEGIN DECLARE SR1 FLOAT,SR2 FLOAT SELECT SR1=InCome-OutCome FROM Salary WHERE EmployeeID=ID1 SELECT SR2=InCome-OutCome FROM Salary WHERE EmployeeID=ID2 IF ID1ID2 SET BJ=0 ELSE SET BJ=1 ENDUSE YGGLGODECLARE BJ int EXEC COMPA 000001,108991,BJ OUTPUT SELECT BJ (3)创建添加职员记录的存储过程EmployeeADD。USE YGGL GO CREATE PROCEDURE EmployeeAdd ( employeeid char(6),name char(10),education char(4),birthday datetime, woekyear tinyint, sex bit,address char(40),phonenumber char(12), departmentID char(3) ) AS BEGIN INSERT INTO Employees VALUES(employeeid,name,education,birthday,woekyear, sex,address,phonenumber,departmentID) END RETURN GOUSE YGGLGOEXEC EmployeeAdd 990230,刘朝,本科,840909,2,1,武汉小洪山号,85465213,3(4)创建一个带有OUTPUT游标参数的存储过程，在Employees表中声明并打开一个游标。USE YGGL GO CREATE PROCEDURE em_cursor em_cursor cursor VARYING OUTPUT AS BEGIN SET em_cursor=CURSOR FORWARD_ONLY STATIC FOR SELECT*FROM Employees OPEN em_cursor END GO声明一个局部游标变量，执行上述存储过程，并将游标赋值给局部游标变量，然后通过该游标变量读取记录：USE YGGLGODECLARE MYCURSOR CURSOR EXEC em_cursor em_cursor=MyCursor OUTPUT FETCH NEXT FROM MyCursor WHILE (FETCH_STATUS=0) BEGIN FETCH NEXT FROM MyCursor END CLOSE MyCursor DEALLOCATE MyCursor GO(5)创建一个存储过程，使用游标确定一个员工的实际收入是否排在前三名。结果为1表示是，结果为0表示否。USE YGGLGOCREATE PROCEDURE TOP_THREE EM_ID char(6),OK bit OUTPUT AS BEGIN DECLARE X_EM_ID char(6) DECLARE ACT_IN int,SEQ int DECLARE SALARY_DIS cursor FOR SELECT EmployeeID,InCome-OutCome FROM Salary ORDER BY InCome-OutCome DESC SET SEQ=0 SET OK=0 OPEN SALARY_DIS FETCH SALARY_DIS INTO X_EM_ID,ACT_IN WHILE SEQ3 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 END CLOSE SALARY_DIS DEALLOCATE SALARY_DIS END执行该存储过程，并查看结果：USE YGGLGODECLARE OK bitEXEC TOP_THREE 108991,OK OUTPUT SELECT OK 2、触发器(1) 向Employees表插入或修改一个记录时,通过触发器检查记录的DepartmentID值在Department表中是否存在，若不存在，则取消插入或修改操作。USE YGGL GO CREATE TRIGGER EmployeesIns ON dbo.Employees FOR INSERT,UPDATE AS BEGIN IF(SELECT DepartmentID from inserted) NOT IN (SELECT DepartmentID FROM Department) ROLLBACK END向Employees表插入或修改一行记录，查看结果 (2) 修改Department表“DpartmentID”字段值时,该字段在Employees表中对应的值也做相应修改。USE YGGL GO CREATE TRIGGER DepartmentUpdate ON dbo.Department FOR UPDATE AS BEGIN UPDATE Employees SET DepartmentID=(SELECT DepartmentID FROM insered) WHERE DepartmentID=(SELECT DepartmentID FROM deleted) END GO(3) 删除Department表中记录的同时删除该记录“DepartmentID”字段值在Employees表中对应的记录。USE YGGLGOCREATE TRIGGER DepartmentsDelate ON dbo.Department FOR DELETE AS BEGIN DELETE FROM Employees WHERE DepartmentID=(SELECT DepartmentID FROM deleted) END GO(4) 创建INSTEAD OF 触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在Employees中是否存在，如果在则执行插入操作，如果不存在，则提示“员工号不存在”。USE YGGLGOCREATE TRIGGER EM_EXISTS ON Salary INSTEAD OF INSERT AS BEGIN DECLARE EmployeeID char(6) SELECT EmployeeID=EmployeeID FROM inserted IF(EmployeeID IN(SELECT EmployeeID FROM Employees) INSERT INTO Salary SELECT*FROM inserted ELSE PRINT员工号不存在 END向Salary表中插入一行记录查看效果：USE YGGLGOINSERT INTO Salary VALUES(111111,2500.3,123.2)(5) 创建DDL触发器,当删除YGGL数据库的一个表时,提示“不能删除表”,并回滚删除表的操作。USE YGGL GO CREATE TRIGGER table_delete ON DATABAS