简单的SQL存储过程运用

1. 存储过程**************必须掌握 首先我们看概念
2. 是结构化语言，优点提高代码重用性，方便模块化设计，提高效率，减少流量，可移植性高，提高安全性
3. 是查询化语句和控制流语句的预编译集合
4. 存储过程也是数据库对象
5. 自定义存储过程，自定义功能
6. 系统存储过程：管理与检索系统信息
7. 存储过程是对T-SQL语句的封装
8. 系统存储过程以SP_开头，在master 数据库下
9. 系统管理员sa可以在任何数据库中调用系统存储过程
10. 以xp_开头是扩展存储过程，可以调用DOS命令

 

if exists(select * from sys.databases where name ='stuDB1')
	drop database stuDB1
go
create database stuDB1
go
use stuDB1
go
create table stuInfo14
(
	stuNo int primary key  identity  ,
	stuName nvarchar(16),
	sttuAge int,
	stratTime datetime default(getdate()),
	sellmoney money
)
go
insert into stuInfo14 values('夏冬',60,'2019-10-18 00:00',100)
insert into stuInfo14 values('夏伟',88,'2019-12-18 00:00',100)
insert into stuInfo14 values('周瑜',96,'2019-1-18 00:00',100)
insert into stuInfo14 values('黄盖',18,'2018-10-18 00:00',100)
insert into stuInfo14 values('乐进',540,'2016-10-18 00:00',100)
insert into stuInfo14 values('李典',18,'2019-10-25 00:00',100)
insert into stuInfo14 values('太史慈',5,'2019-9-18 00:00',100)
insert into stuInfo14 values('潘璋',10,'2019-9-8 00:00',100)
go
--存储过程
--调用语法
--execute 存储过程名 [参数]

--创建无参的存储过程
create procedure proc_getName
	--无参
as
	select stuName, case 
		when sttuAge>99 then '太老了'
		when sttuAge>60 then '老当益壮'
		when sttuAge>40 then '男人40一朵花'
		when sttuAge>20 then '加油骚年'
		else '相信未来一切皆有可能'
		end  '年龄备注'
	from stuInfo14

--调用
execute proc_getName
go
--创建带输入参的存储过程,根据学号查询姓名
--可以给参数赋默认值
create proc proc_getAge
	@stuNo int --输入参
	 
as
	select stuName ,stuNo from stuInfo14 where stuNo=@stuNo
go
--调用
exec proc_getAge 2  --传参
go
--创建带多个输入参且有默认值的存储过程
--查询比输入的学号或年年龄大的所有学生

create procedure proc_stuSum
	@stuNo int =0,
	@stuAge int =0
as
select * from stuInfo14 where sttuAge>@stuAge or stuNo>@stuNo
go
--按指定顺序
execute proc_stuSum 6,90 --因带默认参不输入参数将查询所有人
--按指定参数
execute proc_stuSum @stuNo=5,@stuAge=80
go
--创建带输出输入参的存储过程

create proc proc_getStuAge
	@stuNO int = 0,
	@age int  output  --必须表明这是输出参
as
select @age=sttuAge from stuInfo14 where stuNo=@stuNO
go
declare @age int -- 定义表量接收输出参
--调用
exec proc_getStuAge 8,@age output --接收时候也一定表明输出参
--判断年龄
if (@age<=20)
	print '你是少年'
else
	print '你是青年'
--注意，搞清楚方法的返回值和输出参。返回值只有一个，而输出参数可以有1-N个

--return 的使用
--终止存储过程或返回数据

--创建一个存储过程返回添加学生的自增学号

go

create proc proc_getId
	@stuName nvarchar(8),
	@stuAge int,
	@stratTime datetime
as
insert stuInfo14 values(@stuName,@stuAge,@stratTime)
return @@identity
go
--定义变量接收返回值，有且只有一个返回值
declare @id int
exec @id=proc_getId '魏延',18,'2017-10-10'
select @id '新增学号'
--跟据输入的学生姓名学号,必须要全部正确才给予查询，如果有返回他入学时间,和年龄
go

create proc proc_getstuNaem
	@stuName nvarchar(8),
	@stuNo int,
	@time datetime output,
	@age int output
as
if exists(select 1 from stuInfo14 where stuName=@stuName)
	print '我西永校区有该学生'
else
	begin
		print '西永校区无该学生，你去贵州校区看看吧'
		return --终止程序
	end

if not  exists(select 1 from stuInfo14 where stuNo=@stuNo and stuName=@stuName)
	begin
		print '我西永校区没有该学号'
		return
	end
else
	print '西永校区有该学号,和学生'

--开始查询
select  @age=sttuAge , @time=stratTime from stuInfo14 where stuNo=@stuNo and stuName=@stuName
go
 declare @age1 int,@time1 datetime
 exec proc_getstuNaem '夏冬',100,@time1 output,@age1 output
 select @age1,@time1

 drop proc proc_getstuNaem


go
--处理存储过程的的错误信息
--语法 raiserror(自定义错误信息，错误严重级别，错误状态)
--自定义错误信息是变量，或字符串，严重级别0-18，错误状态1-127

--创建存储过程，用户输入学号，和需要充值饭卡的钱

create proc proc_UpdateMoney
	@stuNo int,
	@sellmoney money
as
	if not exists (select 1 from stuInfo14 where stuNo= @stuNo)
		begin
			declare @msg nvarchar(128)
			set @msg = '您输入的学号: '+convert(nvarchar,@stuNo)+' 不正确不能给您充值'
			raiserror(@msg,1,126)
			return
		end
	if(@sellmoney<20)
		begin
			raiserror('充值金额不能小于20元',1,126)
			return
		end
update stuInfo14 set sellmoney=sellmoney+@sellmoney where stuNo=@stuNo 
	if(@@error>0)
		raiserror('充值失败',2,122)

	else
		print '充值成功！学号为'+convert(nvarchar,@stuNo)+'  的同学充值成功！'
go

exec proc_UpdateMoney 100,10

  select * from stuInfo14