存储过程+游标+事务

来源：互联网发布：阿里云蜘蛛池编辑：程序博客网时间：2024/06/06 05:14

一丶存储过程

什么是存储过程：存储过程可以说是一个记录集吧，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了

存储过程的好处：

1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。
2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。
3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

存储过程的一些基本语法：

1. 只返回单一记录集的存储过程

-------------创建名为GetUserAccount的存储过程----------------
create Procedure GetUserAccount
as
select * from UserAccount
go


-------------执行上面的存储过程----------------
exec GetUserAccount

2.没有输入输出的存储过程

-------------创建名为GetUserAccount的存储过程----------------

create Procedure inUserAccount
as
insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9)
go

-------------执行上面的存储过程----------------

exec inUserAccount

3.有返回值的存储过程

-------------创建名为GetUserAccount的存储过程----------------

create Procedure inUserAccountRe
as
insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(10,10,'2013-01-02',10)
return @@rowcount
go

-------------执行上面的存储过程----------------

exec inUserAccountRe

4.有输入参数和输出参数的存储过程

-------------创建名为GetUserAccount的存储过程----------------create Procedure GetUserAccountRe@UserName nchar(20),@UserID int outputasif(@UserName>5)select @UserID=COUNT(*) from UserAccount where UserID>25elseset @UserID=1000go-------------执行上面的存储过程----------------exec GetUserAccountRe '7',null

5. 同时具有返回值、输入参数、输出参数的存储过程

-------------创建名为GetUserAccount的存储过程----------------create Procedure GetUserAccountRe1@UserName nchar(20),@UserID int outputasif(@UserName>5)select @UserID=COUNT(*) from UserAccount where UserID>25elseset @UserID=1000return @@rowcountgo-------------执行上面的存储过程----------------exec GetUserAccountRe1 '7',null

6.同时返回参数和记录集的存储过程

-------------创建名为GetUserAccount的存储过程----------------create Procedure GetUserAccountRe2@UserName nchar(20),@UserID int outputasif(@UserName>5)select @UserID=COUNT(*) from UserAccount where UserID>25elseset @UserID=1000select * from UserAccountreturn @@rowcountgo-------------执行上面的存储过程----------------exec GetUserAccountRe2 '7',null

7.返回多个记录集的存储过程

-------------创建名为GetUserAccount的存储过程----------------create Procedure GetUserAccountRe3asselect * from UserAccountselect * from UserAccount where UserID>5go-------------执行上面的存储过程----------------exec GetUserAccountRe3

二丶游标

在关系数据库中，我们对于查询的思考是面向集合的。

而游标则是面向行的。

游标的写法：

declare test_course(定义的变量名) cursor for select *from 表名

打开游标：

open test_course

循环读取游标：

next:
declare test_course cursor  for  select *from [dbo].[Info_User]open test_course 
fetch next from test_course  while @@fetch_status=0beginfetch next from test_course end

(FIRST),最后一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相对于目前跳几行(RELATIVE(n)):

对于使用这些参数来读取数据必须指定scroll选项:

declare test_two cursor scroll for  select *from [dbo].[Info_User]
open test_two
fetch LAST  from test_two

使用 FETCH 将值存入变量

 fetch next from mycursor into @ID,@ExpertID,@UserID

三丶事务

事务：保持逻辑数据一致性与可恢复性，必不可少的利器。

书面解释：事务具有原子性，一致性，隔离性，持久性。

原子性：事务必须是一个自动工作的单元，要么全部执行，要么全部不执行。
一致性：事务结束的时候，所有的内部数据都是正确的。
隔离性：并发多个事务时，各个事务不干涉内部数据，处理的都是另外一个事务处理之前或之后的数据。
持久性：事务提交之后，数据是永久性的，不可再回滚

 create procedure mon --创建存储过程，定义几个变量

@toID int,    --接收转账的账户

@fromID int ,  --转出自己的账户

@momeys money --转账的金额

as

begin tran --开始执行事务

 

update bb set moneys=moneys-@momeys where ID=@fromID -执行的第一个操作，转账出钱，减去转出的金额

update bb set moneys=moneys+@momeys where ID=@toID --执行第二个操作，接受转账的金额，增加

 

if @@error<>0 --判断如果两条语句有任何一条出现错误

begin rollback tran –开始执行事务的回滚，恢复的转账开始之前状态

return 0

end

go

 

else   --如何两条都执行成功

begin commit tran 执行这个事务的操作

return 1

end

go

存储过程+游标+事务

ALTER procedure [dbo].[ExpertOrderBack]
AS
--声明几个变量 
declare @UserID int-- 提问的用户ID
declare @ExpertUserID int --回答问题的专家ID
declare @ExpertID int    
declare @Baodan decimal  --报单账户
declare @Price decimal --咨询一次的价格
declare @errors int
declare @ID int



declare mycursor cursor for select ID,ExpertID,UserID from ExpertOrder where OrderState=0 and OdertTime<dateadd(day,-7,getdate())
 --打开游标  
    open mycursor      
    --从游标里取出数据赋值到我们刚才声明的3个变量中  
    fetch next from mycursor into @ID,@ExpertID,@UserID      
    --判断游标的状态  
    -- 0 fetch语句成功      
    ---1 fetch语句失败或此行不在结果集中      
    ---2 被提取的行不存在  
    while (@@fetch_status=0)  
    begin    
--通过ExpertID 获取专家对应的用户ID
Select @ExpertUserID=(select UserId from Expert where ID=@ExpertID)
Select @Price=(select Price from Expert where ID=@ExpertID)
Select @Baodan=(select BaodanGold from Info_User where UserID=@ExpertUserID)


  begin  tran --开始执行事务


  --判断用户的金额是否足够
if @Baodan<@Price
begin
set @errors=@@ERROR+1
end


--开始进行业务上变动
update ExpertOrder set OrderState=3,IsSatisfaction=1,IsAudit=1 where ID=@ID
insert Info_Transaction(UserID,TranUserAccountType,TranType,BeforeTranMoney,TranMeney,EndTranMoney,Notes,IsCancel,TranCurrency,AddTime,IsDelete) values(@ExpertUserID,0,9,@Baodan,@Price,@Baodan+@Price,'会员未确认，系统自动扣款',0,'人民币',GETDATE(),0)
update Info_User set BaodanGold=@Baodan+@Price where UserID=@ExpertUserID

if @errors<>0 --判断  如果两条语句有任何一条出现错误。(如果前面的SQL 语句执行没有错误，则返回0)
begin
print '出现错误'
rollback tran --开始执行事务的回滚，恢复转账开始之前的状态
return 0
end

else  --如果两个语句都执行成功
begin
commit tran --执行这个事务的操作
end






       print '游标成功取出一条数据'  
       print @ExpertID  
       print @UserID   
   print @ID


       fetch next from mycursor into @ID,@ExpertID,@UserID  
    end  
    --关闭游标  
    close mycursor  
    --撤销游标  
    DEALLOCATE mycursor   

0 0