1、银行转账
代码语言：javascript复制use master
go
if exists (select * from sysdatabases where name ='BankDB' )
drop database BankDB
go
create database BankDB
go
use BankDB
go
create table bank
(
name nvarchar(50) primary key,
money int not null
)
go
insert into bank values ('张三',1000)
insert into bank values ('李四',0)
insert into bank values ('王五',15000)
insert into bank values ('赵六',1000)
insert into bank values ('孙七',8000)
select * from bank
go
if exists (select * from sysobjects where name ='proc_bank' )
drop proc proc_bank
go
create proc proc_bank
@from nvarchar(50),
@to nvarchar(50),
@money int
as
if(@money<0)
begin
raiserror('不可以小于0',16,1)
end
declare @err int
set @err = 0
begin transaction
update bank set money = money-@money where name = @from
set @err = @@ERROR
update bank set money = money @money where name = @to
set @err = @@ERROR
if(@err >0)
rollback transaction
else
commit transaction
go
exec proc_bank '张三','李四',1000
go2、学员信息
代码语言：javascript复制use master
go
if exists(select * from sysdatabases where name = 'MySchool')
drop database MySchool
go
create database MySchool
go
use MySchool
go
create table Stu
(
id int primary key identity(1,1),
gradeId nvarchar(50) not null,
name nvarchar(50) not null unique,
sex bit not null,
age int not null check (age>=0 and age <=100),
hobby nvarchar(50) not null
)
insert into Stu values(1,'张三',0,10,'篮球')
insert into Stu values(2,'李四',1,20,'篮球')
insert into Stu values(3,'王五',0,55,'篮球')
insert into Stu values(2,'赵六',1,44,'篮球')
insert into Stu values(1,'孙七',0,33,'篮球')
go
select * from Stu
go
--要求全部使用存储过程
--1.查询全部学员信息（不带任何参数）
if exists(select * from sysobjects where name = 'Proc_Stu')
drop proc Proc_Stu
go
create proc Proc_Stu
as
select * from Stu
go
exec Proc_Stu
--2.根据学号查询学员信息（带输入参数）
if exists(select * from sysobjects where name = 'Proc_Stu')
drop proc Proc_Stu
go
create proc Proc_Stu
@id int
as
select * from Stu where id = @id
go
exec Proc_Stu 1
--3.根据学号查询学员年龄（使用带输出参数 print @age）
if exists(select * from sysobjects where name = 'Proc_Stu')
drop proc Proc_Stu
go
create proc Proc_Stu
@id int,
@age int output
as
select @age = age from Stu where id = @id
go
declare @age int
exec Proc_Stu 1,@age output
print @age
