SQL数据库触发器和游标的使用

SQL数据库触发器和游标的使用

一、概念：

1.触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，触发器与存储过程的区别在于:存储过程是由用户或应用程序显式调用的,而触发器不能被直接调用。
2.游标是从数据表中提取出来的数据，以临时表的形式存放在内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用fetch语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回数据表中。

二、应用场景：

1.触发器是在对数据表进行insert delete update等操作时，强制执行的后台操作。多用于业务逻辑检查，数据检查、生成等。
2.游标是对数据进行逐条进行处理的时候用，是用来循环处理查询得到的每一列数据的方法，类似于while循环，提供集合处理之外的顺序处理能力。

接下来做一个实例：在触发器中，利用游标统计各部门的员工人数。

--部门
CREATE TABLE Department
(
 DepartmentId INT IDENTITY(1,1),
 DepartmentName NVARCHAR (50)
 UNIQUE (DepartmentName), --唯一约束
 PRIMARY KEY (DepartmentId)
)

--员工
CREATE TABLE Staff
(
 StaffId INT IDENTITY(1,1),
 StaffName NVARCHAR (50),
 Age INT,
 DepartmentId INT
)

--创建员工表与部门表的外键约束
ALTER TABLE Staff
ADD CONSTRAINT FK_Staff_StaffId_Department_DepartmentId
FOREIGN KEY (DepartmentId)
REFERENCES Department(DepartmentId)
ON UPDATE CASCADE  --级联更新 
ON DELETE CASCADE  --级联删除 

SELECT *FROM Department

SELECT *FROM Staff

/*
 描述：在触发器中，利用游标统计各部门的员工人数。
 (我对Department表及Staff表都创建了同样逻辑的触发器，只是名称不同。因此只要对任何一张表进行了更改、插入和删除时，都会执行统计操作。)
 作者：Eric He
 日期：2012-08-03
*/
CREATE TRIGGER TRI_GetStaffCount_Department
ON Department
FOR UPDATE ,INSERT, DELETE --当Staff表发生更改、插入和删除时，执行TRI_GetStaffCount_Staff触发器。
AS


--部门员工人数统计表
CREATE TABLE #Temp
(
 StaffCount INT,
 DepartmentName NVARCHAR(50)
)

DECLARE @Id INT
DECLARE @Name NVARCHAR(50)

--1.创建游标
DECLARE CUR_GetDepartment CURSOR 
FOR

SELECT
 DepartmentID,
 DepartmentName
FROM
 Department 
ORDER BY DepartmentID ASC

--2.打开游标
OPEN CUR_GetDepartment
--3.移动或提取列值
FETCH NEXT FROM CUR_GetDepartment
INTO @ID,@Name

/*
   @@fetch_status是SQL的一个全局变量，其值有以下三种，分别表示三种不同含义：
   【返回类型integer】 　　
     0 FETCH 语句成功 　　
    -1 FETCH 语句失败或此行不在结果集中 　　
    -2 被提取的行不存在 　　
    
    @@fetch_status值的改变是通过FETCH NEXT FROM 实现的 　　FETCH NEXT FROM CUR_GetDepartment
*/

--利用循环处理游标中的列值
WHILE @@FETCH_STATUS=0
BEGIN
    --统计当前部门为@Id的员工人数
    DECLARE @counts INT
    SELECT @counts= Count(*) FROMStaff WHEREDepartmentID=@Id 


    --将员工人员及部门名称插入临时表
    INSERT INTO #Temp(StaffCount,DepartmentName)
    VALUES(@counts,@Name)
    PRINT '游标当前行：'+Convert(NVARCHAR(50),@@Cursor_Rows)

    --继向下读取
    FETCH NEXT FROM CUR_GetDepartment
    INTO @ID,@Name
END

--4.关闭游标
CLOSE CUR_GetDepartment
--5.释放游标
DEALLOCATE CUR_GetDepartment

--查询出统计数据
SELECT *FROM#Temp
DROP TABLE #Temp 

 /*分别对表Department和Staff做插入操作测试*/

--插入部门
INSERT INTO Department
(
 DepartmentName
)
VALUES
(
'ENG6'
)

--插入员工数据时会自动统计
INSERT INTO Staff
(
 DepartmentID,
 StaffName,
 Age
)
VALUES
(
 5,'Paul Zhang',28
)

/*******************************************************************************

以上对部门员工人数统计操作，可以使用开窗函数和关联查询实现。
性能比游标好，游标主要是锁表，这个是他真正影响性能的原因。
在这里主要是为了向大家分享触发器、游标的原理及它们的应用场景。

********************************************************************************/

SELECT  DISTINCT(T.DepartmentName),T.StaffCountFROM(
SELECT 
       Count(StaffID) OVER(PARTITON BYS.DepartmentID)ASStaffCount,--利用窗口函数(OVER)按部门统计员工人数
       D.DepartmentName 
FROM Staff AS S
RIGHT JOIN Department AS D
ON S.DepartmentID=D.DepartmentID
)AS T

 

 作者：Eric He

http://my.youkuaiyun.com/eriche_yong