存储过程的创建

1、无参数存储过程的创建：（配合着游标的使用）

use LineManager
go
create proc GetNewestData
as
begin

declare GetResult insensitive scroll cursor for-----------游标的使用 定义getresult游标，【insensitive】使用insensitive定义的

                                                                               --游标，会将提取出来的数据存放在一个系统数据库tempdb创建的一个临时

                                                                               -- 表里。任何通过该游标进行的操作都在临行表里进行，因此所有对基本表

                                                                               --的改动都不会在游标中体现出来。

                                                                               --【scroll】使用scroll定义的游标成为滚动游标，可以提取任意位置的数据

                                                                               --行。若无此关键字，只能按顺序提取。
select SetDateTime,HalfHourcost,UnitTime,PrepareTime,LeastCurrency,UserName from BasicData order by SetDateTime
open GetResult              -----------------------打开游标
fetch last from GetResult            ---------------取得游标中最后一行的数据
close GetResult                     ------------------关闭游标
deallocate GetResult                -----------------释放游标
end
go
2、有输入参数存储过程的创建

use LineManager
go
create proc ProcCharge
@CardNo varchar(10),                    -----------------参数   
@ChargeDateTime datetime,
@Charge int,
@UserName varchar(10),
@LastBalance decimal,
@CurrentBalance decimal,
@MachineNo varchar(10)               -------------参数
as
begin
begin transaction                          ------------事务开始
insert into Charge (CardNo ,Charge ,ChargeDateTime ,CurrentBalance ,LastBalance ,OrderState ,PrintState ,UserName )
values (@CardNo ,@Charge,@ChargeDateTime ,@CurrentBalance ,@LastBalance ,'未结账','未打印',@UserName )
update Balance set Balance =@CurrentBalance where CardNo =@CardNo
if @@ERROR =0
   Commit transaction                  -------------事务提交
else
   rollback transaction                  ---------------事务回滚
end                           

go

3、带输出参数存储过程的创建

use LineManager
go
alter proc procBalanceUserName
@CardNo varchar(11),
@MachineNo varchar(50),
@Balance float output,                    -------------output关键字
@UserName varchar(10) output
as
begin
select @Balance=Balance from Balance where CardNo =@CardNo
select @UserName =UserName from OnWorkTeacher where MachineNo =@MachineNo
end
go