欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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, ... 

 

相关标签: 数据库

上一篇: 二分查找思想及代码

下一篇: