存储过程、游标和触发器常见使用

一、存储过程

1.存储过程的分类

1.1 不含参数的存储过程

重点变量先声明在使用，declare声明后记得添加数据类型。
变量声明在as前是参数（分为输入参数和输出参数output），在as之后为局部变量。

create PROCEDURE dbo.testp1
	
AS
BEGIN
	declare @a int;
	set @a=10;
	select @a;
END

执行：exec testp1

结果：10

1.2 带返回值的存储过程

create PROCEDURE [dbo].[testp2]
AS
BEGIN
	declare @a int;
	set @a=3;
	return @a;
END

执行：

declare @testp2 int;
exec @testp2=dbo.testp3;
select @testp2

结果：3

1.3 执行含查询语句的存储过程

create PROCEDURE [dbo].[testp3]
AS
BEGIN
	
	select * from dbo.test01;
END 

执行：exec testp3

结果：查询 dbo.test01表所有数据

1.4 含参数的存储过程

create PROCEDURE [dbo].[testp4]
	@datetime1 date,
	@out_number int,
	@in_number int
AS
BEGIN
	insert into dbo.test01 values(@datetime1,@out_number,@in_number);
END

执行：exec testp4 ‘2021-5-1’,100,101;

结果：向表dbo.test01 中插入一条数据

1.5 带（含参）返回值的存储过程

create PROCEDURE [dbo].[testp5]
	@datetime1 date,
	@out_number int,
	@in_number int
AS
BEGIN
	if @datetime1 is not null
	 begin
	   insert into dbo.test01 values(@datetime1,@out_number,@in_number);
	   return 1;
	 end;
	else 
	   return -1;
END

执行：

declare @ret int;
exec @ret = dbo.testp5 ‘2022-12-17’,200,201;
select @ret;

结果：1

1.6 带输出参数的返回值

create PROCEDURE [dbo].[testp6]
	@out_number int,
	@in_number int,
	@datetime1 date output
AS
BEGIN
  if(@out_number is not null)
   begin
	declare @datet date;
	set @datet=GETDATE();
	insert into dbo.test01 values(@datet,@out_number,@in_number);
	set @datetime1=@datet;
  
   end;
  else 
	set @datetime1=null;
END

执行：

declare @datetime date;
exec dbo.testp6 300,301,@datetime output;
select @datetime;

结果：当前日期

二、游标使用

1.使用步骤

• 声明建立游标

  DECLARE Once_Cursor CURSOR FOR  ----1.声明建立游标
  	SELECT top 10 [DEVICE_ID],[VEHICLE_ID],[LONGITUDE],[LATITUDE] FROM [dbo].[IDS_GPS_20220815]
  

• 打开游标

  OPEN Once_Cursor;--2.打开游标
  

• 推进游标

  FETCH NEXT FROM Once_Cursor INTO @DEVICE_ID,@VEHICLE_ID,@LONGITUDE,@LATITUDE;--3.推进游标：循环遍历数据集中的每一行数据，并赋值。
  

• 使用游标并推进游标

  WHILE @@FETCH_STATUS = 0--4:判断是否正确取到值：循环执行以下逻辑直至最后一条数据时跳出
  		BEGIN						
  			INSERT INTO [dbo].[Vehicle_GPS](DEVICE_ID,LONGITUDE,LATITUDE) VALUES (@DEVICE_ID,@LONGITUDE,@LATITUDE)
  
  			FETCH NEXT FROM Once_Cursor INTO @DEVICE_ID,@VEHICLE_ID,@LONGITUDE,@LATITUDE;--5：同条件4作用：循环遍历数据集中的每一行数据，并赋值。
  		END;
  

• 关闭游标

  CLOSE Once_Cursor;--6；关闭游标
  

• 销毁游标

  DEALLOCATE Once_Cursor;--7：销毁游标
  

2.荔枝

USE [RW_TEST]
GO
/****** Object:  StoredProcedure [dbo].[CursorUse]    Script Date: 2022/12/16 13:25:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<王明亮>
-- Create date: <2022年12月16日13:13:41,>
-- Description:	<游标的使用>
-- =============================================
ALTER PROCEDURE [dbo].[CursorUse]   @topNum  int
AS
 DECLARE 
    @DEVICE_ID	VARCHAR(50),	 
    @VEHICLE_ID	int,
    @LONGITUDE	decimal(11, 8),	
	@LATITUDE	decimal(11, 8)

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 -------------------------------------------声明Once_Cursor游标----------------------------------------
--SET IDENTITY_INSERT  [dbo].[Vehicle_GPS]  OFF
DECLARE Once_Cursor CURSOR FOR  ----1.声明建立游标
	SELECT top 10 [DEVICE_ID],[VEHICLE_ID],[LONGITUDE],[LATITUDE] FROM [dbo].[IDS_GPS_20220815]
OPEN Once_Cursor;--2.打开游标
	FETCH NEXT FROM Once_Cursor INTO @DEVICE_ID,@VEHICLE_ID,@LONGITUDE,@LATITUDE;--3.推进游标：循环遍历数据集中的每一行数据，并赋值。
		WHILE @@FETCH_STATUS = 0--4:判断是否正确取到值：循环执行以下逻辑直至最后一条数据时跳出
		BEGIN						
			INSERT INTO [dbo].[Vehicle_GPS](DEVICE_ID,LONGITUDE,LATITUDE) VALUES (@DEVICE_ID,@LONGITUDE,@LATITUDE)

			FETCH NEXT FROM Once_Cursor INTO @DEVICE_ID,@VEHICLE_ID,@LONGITUDE,@LATITUDE;--5：同条件4作用：循环遍历数据集中的每一行数据，并赋值。
		END;
CLOSE Once_Cursor;--6；关闭游标
DEALLOCATE Once_Cursor;--7：销毁游标
-------------------------------------------结束Once_Cursor游标----------------------------------------
END

三、触发器

​ 触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

​ 触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。

​ 触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于强制服从复杂的业务规则或要求。例如：您可以根据客户当前的帐户状态，控制是否允许插入新订单。

​ 触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。

触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。

1.查询所有触发器

select * from sysobjects where xtype='TR'

2.语法

create trigger [shema_name . ] trg_name
on { table | view }
[ with encryption ]
{ for | after | instead of }
{ insert , update , delete }
as
sql_statement

3.开启与关闭触发器

disable trigger [触发器名] on database --禁用触发器

enable trigger [触发器名] on database --开启触发器

4.触发器实例

4.1 Insert触发器实例

create trigger test
on al
for insert
as
declare @id int,@uid int,@lid int,@result char
select @id=id,@uid=uid,@lid=lid,@result=result from inserted
if(@lid=4)
begin
 update al set uid=99 where id=@id
 print 'lid=4时自动修改用户id为99'
end

4.2 Update触发器实例

create trigger test_update
on al
 for update
as
 declare @oldid int,@olduid int,@oldlid int,@newid int,@newuid int,@newlid int
 select @oldid=id,@olduid=uid,@oldlid=lid from deleted;
 select @newid=id,@newuid=uid,@newlid=lid from inserted
 if(@newlid>@oldlid)
 begin
 print 'newlid>oldid'
 rollback tran;
 end
 else
 print '修改成功'

4.3 Delete触发器实例

create trigger test_delete
on al
for delete
as
declare @did int,@duid int,@dlid int
select @did=id,@duid=uid,@dlid=lid from deleted
if(exists(select * from list where @dlid=id))
begin
print '无法删除'
rollback tran;
end
else
print '删除成功'