MS SQL Server之光标、存储过程和触发器

光标

通常数据库操作被认为是以数据集为基础的操作，但是光标被用于以记录为单位来进行操作，来获取数据库中的数据的子集。光标一般用于过程化程序里的嵌入的SQL语句。

对光标的定义如下：

DECLARE CURSOR_NAME CURSOR

FOR SELECT_STATEMENT[FOR [READ ONLY | UPDATE {[COLUMN_LIST]}]

光标创建之后可以进行如下操作：

OPEN：打开定义的光标。

OPEN CURSOR_NAME

FETCH：从光标获取记录，赋予程序变量。

FETCH  NEXT  FORM CURSOR_NAME [ INTO FETCH_LIST ]

例如：

BEGIN
DECLARE @custname VARCHAR(30);
DECLARE namecursor CURSOR FOR SELECT CUST_NAME FORM TBL_CUSTOM

OPEN namecursor;
FETCH NEXT FROM namecursor INTO @custname
WHILE (@@FETCH_STATUS<>-1)
BEGIN FETCH NEXT FORM namecursor INTO @custname
print 'custname:'+@custname
END

CLOSE namecursor
DEALLOCATE namecursor//释放资源
END

CLOSE：对光标操作完成之后，关闭光标。

存储过程

存储过程是一组关联的SQL语句，通常被成为函数和子程序。存储过程可以嵌套在另一个存储过程里面。

在SQL Server定义存储过程如下：
GO
/****** Object:  StoredProcedure [dbo].[select_terminal]    Script Date: 08/06/2015 13:51:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[select_terminal]
--(
--@id int
--)
-- Add the parameters for the stored procedure here
--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
declare @tsn varchar(50)
--declare @isOnline int 

declare QueryTsn cursor for select top 10 tsn from tTerminal
OPEN QueryTsn;

fetch next from QueryTsn into @tsn

while (@@FETCH_STATUS=0)
BEGIN 
Fetch next from QueryTsn into @tsn
print 'tsn:'+convert(varchar,@tsn)
END
close QueryTsn
deallocate QueryTsn

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;

    -- Insert statements for procedure here
--SELECT * from tTerminal where ID=@id
END

执行存储过程如下:

USE [DB_NAME]
GO

DECLARE@return_value int

EXEC@return_value = [dbo].[select_terminal]

SELECT'Return Value' = @return_value

GO

触发器

触发器是数据中编译了的SQL过程，基于数据库里发生的其他行为来执行操作。可以在insert、delete或update之后执行，可以检查数据完整性，可以回退事务。

SQL Server 创建触发器的语法如下：

CREATE TRIGGER TRIGGER_NAME
ON TABLE_NAME
FOR {INSRT | UPDATE | DELETE [, ..]}
AS 
SQL_STATEMENTS

[ RETURN ]

例如：

USE [DB_NAME]
GO
/****** Object:  Trigger [dbo].[createtable]    Script Date: 08/06/2015 14:28:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[createtable]
ON [dbo].[t508]
FOR Insert 
AS 
BEGIN
declare @sqlstr varchar(1000)
set @sqlstr = 'CREATE TABLE [dbo].[zlogt508](
[ID] [int] IDENTITY(1,1) NOT NULL,
[mID] [int] NOT NULL,
[gpsTime] [datetime] NOT NULL,
primary key ([ID]))
'
exec(@sqlstr)
   PRINT 'You must ask your DBA to drop or alter tables!' 

END

***创建的触发器在要触发的表的触发器文件内

删除触发器

drop trigger trigger_name