存储过程、触发器、游标简单Demo，SQLserver

1.存储过程

--存储过程 优点：预编译、批处理（SQL预先存放在服务器上面的数据结构，减少网络流通）procedure
create procedure proc_register
(
@name varchar(50),
@pwd varchar(50),
@remark varchar(100)
)
as 
begin
insert into EFCoreDemo.dbo.Users(username,password,remark)
values(@name,@pwd,@remark)
return @@IDENTITY
end;

declare @test int
exec @test = proc_register '侧测试','123','备注'
select @test

select * from EFCoreDemo.dbo.Users

drop procedure proc_register

2.游标

--游标 只进游标、静态游标、动态游标

declare cursor_userinfo cursor  --只进游标
for 
select Id, UserName, Password, Remark from EFCoreDemo.dbo.Users

open cursor_userinfo --局部游标、全局游标（global） 前提，几个存储过程，都在一次会话 全局游标都可以被调用

declare @id int,@username varchar(50),@password varchar(50),@remark varchar(100)
fetch next from cursor_userinfo into @id,@username,@password,@remark
print cast(@id as varchar)+'-'+@username+'-'+@password+'-'+@remark

close cursor_userinfo

deallocate cursor_userinfo --释放资源


--循环执行
declare @id int,@username varchar(50),@password varchar(50),@remark varchar(100)
fetch next from cursor_userinfo into @id,@username,@password,@remark
while @@FETCH_STATUS =0 --判断状态是否执行成功
begin
	print cast(@id as varchar)+'-'+@username+'-'+@password+'-'+@remark
	fetch next from cursor_userinfo into @id,@username,@password,@remark
end

3.触发器

--触发器的使用
--临时表 在一次批处理中产生的临时内存结构
--inserted:新数据信息记录 deleted 旧的数据信息记录
--insert delete update 对数据产生影响
--添加、删除 添加+删除
create trigger tri_user
on EFCoreDemo.dbo.Users
for insert --受约束限制delete,update   instead of insert 不受限
as 
begin
    declare @id int  --定义一个用户编号
	select @id=id from inserted

	--插入日志表
	insert into Logs(logContent,id)
	values('添加动作',@id)
end


insert into EFCoreDemo.dbo.Users(Id, UserName, Password, Remark) values(103,'领导','123','fer');