光标
通常数据库操作被认为是以数据集为基础的操作,但是光标被用于以记录为单位来进行操作,来获取数据库中的数据的子集。光标一般用于过程化程序里的嵌入的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
版权声明:本文为博主原创文章,未经博主允许不得转载。