DataBase 之 存储过程、函数、触发器、事务、游标

(1)存储过程

[1]创建存储过程（无参）

create procedure ProTestA
as
  select * from TestA

--修改存储过程（有参，且为输出参数)
alter procedure ProTestA
@LastName varchar(10) = null output
as
  if @LastName is null
  begin
     print ‘null‘
     print ‘123‘
  end
  else
  begin
  print @LastName
  print ‘456‘
  end
 exec ProTestA
 exec ProTestA ‘Olive‘ 

(2)自定义函数（关键点：函数和存储过程 区别主要在于函数必须声明返回值类型）

[1]用户自定义函数（返回时间）

create function DayOnly(@date datetime)
returns varchar(12)
as
begin
    return convert(varchar(12),@date,101)
end 
select dbo.DayOnly(getdate())

[2]用户自定义函数(返回表)

drop function TestATable

create function TestATable(@Name nvarchar(10))

returns table

as

return select * from TestA where id=@Name

select * from TestA

select * from TestATable(‘12‘)

--用户自定义函数输入参数、返回表

create function ResultTable(@Name nvarchar(10))

returns @Result table (Rid varchar(10),Rpwd varchar(10))

as

begin

insert into @Result select * from TestA where id=@Name

return

end

select * from ResultTable(‘12‘)

(3)触发器

[1]创建触发器

create trigger UpdateStudent
on Student for insert
as
begin
  update Student set Sname=‘张三‘ where SID=( select Inserted.SID from Inserted)
end
insert into Student select ‘MM‘,‘1988-11-11‘,‘男‘;
select * from Student order by SID Desc;

[2]禁用触发器

alter table Sales.SalesOrderDetail

DisableTrigger all --这里的all可以是具体的触发器名

[3]删除触发器

Drop Trigger orderDetailNotDiscontinued

[4]修改触发器

Alter Trigger tr_title

on mytable

for insert,update

as

if update(title)

begin

print(‘title1‘)

end

[5]触发器的相关函数Update()

create trigger UpdateTest

on Student

for insert,update

as

if update(SName)----如果update（）中的字段有更新的话就为true否则为false

select Inserted.Sname from Inserted

insert into Student select ‘OLIVEVEVE‘,‘1989-12-22‘,‘女‘

[6]触发器(插入新的数据，并且在插入后更新刚插入的输入的姓名)

create trigger InsertTwo

on Student

after insert

as

update Student set Sname=‘OOOO‘ where SID=( select inserted.SID from Inserted)

insert into Student select ‘MoYao‘,‘1989-11-15‘,‘男‘

select * from Student

其中inserted表是一个临时表

存储的是将要插入的信息

这个触发器的目的是检查将要插入的信息是否符合规定

(在product表里没有特殊的记录)

这个例子是check约束所不能解决的了的

use AdventureWorks

go

Create Trigger orderDetailNotDiscontinued

On Sales.SalesOrderDetail

After insert,update

as

if exists

 (

select ‘true‘ from Inserted i

join Production.Product p

on i.ProductID= p.ProductID

where p.DiscontinuedDateis not null

 )

begin

print(‘error appear.‘)

rollback tran

end

(4)事务

sql事务是使用SQL server自身的事务：在存储过程中直接使用Begin Tran，Rollback Tran，Commit Tran实现事务：

优点：执行效率最佳

限制：事务上下文仅在数据库中调用，难以实现复杂的业务逻辑。

begin try

  begin tran

  insert into Student(SID) values(-1)

  commit tran

  print ‘commited‘

end try

begin catch

  rollback

  print ‘rolled back‘

end catch 

TransactionScope事务

TransactionScope事务类，它可以使代码块成为事务性代码。并自动提升为分布式事务

优点：实现简单，同时能够自动提升为分布式事务

那么如何解决数据库并发问题呢？

解决此问题的办法就是为数据库加锁，以防止多个组件读取数据，通过锁住事务所用的数据，能保证开锁之前，只有本事务才能访问数据库。这样就避免了交叉存取的问题

(5)游标

--游标：数据集合里的上下移动的指针

Drop cursor StudentCursor

--声明变量

Declare @SID int

Declare @Sname nvarchar(30)

Declare @Sage nvarchar(30)

Declare @Ssex nvarchar(5)

--声明游标

Declare StudentCursor cursor  for

select * from Student

--打开游标

Open StudentCursor

--将游标移向下一行，将获取的数据放入之前声明的变量中

fetch next from StudentCursor into @SID,@Sname,@Sage,@Ssex

--判断是否获取数据成功，@@fetch_status为系统全局变量，0-表示成功，1-表示取不到数据，2-超出了最后一条数据

while @@fetch_status=0

begin

print(Cast(@SID as varchar)+‘:‘+@Sage+‘:‘+@Ssex)

--提取下一条记录

fetch next from StudentCursor into  @SID,@Sname,@Sage,@Ssex

end

close StudentCursor

deallocate StudentCursor

--存储过程中使用游标（游标的作用域）

create procedure TestStudentCursor

as

--声明变量填充游标检索到的数据

declare @SID int

declare @Sname nvarchar(30)

declare @Sage nvarchar(30)

declare @Ssex nvarchar(5)

--声明存储过程内部的全局游标

declare StudentCursor  cursor global for

select * from Student

--打开游标

open StudentCursor

--读取下一条数据，并将数据填充到之前声明的变量中

fetch next from StudentCursor into @SID,@Sname,@Sage,@Ssex

--判断是否取到数据，且取到的数据是否符合要求

while(@SID<5) and (@@fetch_status=0)

begin

--打印数据

print(cast(@SID as varchar)+‘::‘+@Sname+‘::‘+@Sage)

--读取下一条数据

fetch next from StudentCursor into @SID,@Sname,@Sage,@Ssex

end

--关闭游标

close StudentCursor

--释放游标

deallocate StudentCursor

--执行存储过程

exec TestStudentCursor