SQL存储过程的创建和使用
程序员文章站
2024-03-20 09:15:46
...
存储过程创建
create procedure 存储过程名
as
sql语句
go
create procedure bookbooks(@bno int, @sno varchar(12))
as
begin transaction
declare @state int
select @state = Bstate from book where Bno = @bno
if @state in (1 ,3)
begin
rollback
end
else if @state = 0
begin
update book set Bstate = 1 where Bno = @bno
insert into Bbook
(Bdate, Bno, Sno)
values
(GETDATE(), @bno, @sno)
end
else if @state = 2
begin
update book set Bstate = 3 where Bno = @bno
insert into Bbook
(Bdate, Bno, Sno)
values
(GETDATE(), @bno, @sno)
end
commit
go
go
create procedure renwebook(@bno int, @sno varchar(12), @aid varchar(12))
as
begin transaction
declare @state int
select @state = bstate from book where Bno = @bno
if(@state in (0, 1, 3))
begin
rollback
end
else if(@state = 2)
begin
insert into Rbook
(Aid, Bdate, Bno, Sno)
values
(@aid, GETDATE(), @bno, @sno)
update book set Bstate = 3 where Bno = @bno
end
commit
go
存储过程使用
exec dbo.bookbooks 参数1 = 值1, 参数2 = 值2, ...
上一篇: 二分查找思想及代码