数据库中的用户自定义存储过程的使用

--用户自定义的存储过程

--create proc 存储过程名称

--【参数列表】

--as

--begin

--具体的业务逻辑

--end

--go

--1 创建存储过程，完成客户信息表中客户编号，客户名称的查询

--create proc p_customerinfo

--as

--begin

--select CustomerID,CustomerName from Customer

--end

--go

--执行存储过程 /调用存储过程 

--execute/exec 存储过程名称 [对应参数]

--execute p_customerinfogo

--修改存储过程

--alter proc 待修改存储过程名称

--[参数列表]

--as

--新的业务逻辑

--alter proc p_customerinfo

--as

--begin

--select CustomerName,Gender from Customer

--end

--go

--删除存储过程

--drop proc p_customerinfo

--go

--练习 创建一个存储过程，完成商品表中类别编号为1下的商品的名称，

--价格，和类别名称

--调用该存储过程--优化，

--在创建之前，先判断一下存储过程的名字是否已经存在，

--如果存在，则删除

--if exists(select * from sysobjects where name='p_productinfo') 

--drop proc p_productinfo 

--go

--create proc p_productinfo

--as

--begin

--select ProductName,ProductPrice,CategoryName from Product,Category

--where Product.CategoryID=Category.CategoryID 

--and Product.CategoryID=1

--end

--exec p_productinfo

--go

--练习 修改上面的存储过程，对商品价格进行排序 ,调用该存储过程

--alter proc p_productinfo

--as

--begin

--select ProductName,ProductPrice,CategoryName from Product,Category

--where Product.CategoryID=Category.CategoryID 

--and Product.CategoryID=1

--order by ProductPrice

--end

--exec p_productinfo

--go

--练习 删除存储过程

--drop proc p_productinfo

--go

--参数的本质是变量，在存储过程中声明参数（变量）不需要使用declare

--创建存储过程，查询指定性别的客户名称和性别

--create proc p_customerinfo2 

--@sex nvarchar(10)='男'--默认是输入参数

--as

--begin

--select * from Customer where Gender=@sex

--end

--执行存储过程

--exec p_customerinfo2 '女'

--go

--练习 创建存储过程，查询价格在指定区间内的商品的编号，名称和价格

--create proc p_productinfo3

--@num1 int,@num2 int

--as

--begin

--select ProductID,ProductName,ProductPrice from Product 

--where ProductPrice between @num1 and @num2

--end

--exec p_productinfo3  0,1000

--创建一个存储过程，

--完成商品表中指定类别下的商品的名称，价格，和类别名称

--go

--create proc p_productinfo4 

--@id int output --输出参数 向外界返回结果的

--as

--begin

--select @id=ProductID from Product 

--where ProductID=1

--end

--declare @rs int --声明一个变量来接收向外返回的output参数

--exec p_productinfo4 @rs output

--selec@rs

--案例1 创建存储过程，查询商品表中指定商品类别编号的商品的总库存，

--总价格，然后输出总库存和总价格

--go

--create proc p_info1 @cid int, --输入参数

--@totalstore int output, --输出参数

--@totalmoney decimal(18,2) output --输出参数

--as

--begin

--select @totalstore= sum(Store),@totalmoney= sum(ProductPrice)

--from Product where CategoryID=@cid

--end

--执行存储过程

--当执行带有输出参数的存储过程的时候

--（1）要声明和输出参数相同类型的变量来接收

--declare @rs int,@rs2 decimal(18,2)

--(2)在调用存储过程的时候，接收输出参数的变量后面需要加上关键字output

--exec p_info1 1,@rs output,@rs2 output

--select @rs,@rs2

--go

--案例2 创建存储过程，查询指定商品类别编号下的平均库存，

--然后查询指定商品编号的库存和平均库存做比较，

--如果该库存大于平均库存，则输出大于，如果等于平均库存则输出等于

--否则输出小于

--create proc p_msg@cid int ,@pid int,@msg nvarchar(20)output--输出参数

--as

--begin

--声明两个局部变量，分别来存储平均库存和指定商品下的库存

--declare @avgstore int,@store int

--1.求出执行类别编号下的平均库存

--select @avgstore= AVG(Store) from Product 

--where CategoryID=@cid

--2.查询指定商品编号的库存

--select @store= Store from Product 

--where ProductID=@pid

--3.判断指定商品编号的库存和平均库存的关系

--if @store>@avgstore  

--set @msg='大于'

--else if @store=@avgstore 

--set @msg='等于'

--else  

--set @msg='小于'

--end

--调用存储存储过程

--1.声明一个变量接收输出的参数

--declare @rs3 nvarchar(20)

--exec p_msg @cid=1,@pid=1,@msg=@rs3 output

--select @rs3

--go

--创建一个存储过程，然后判断两个数的大小，返回较大值

--create proc p_max 

--@num1 int,@num2 int

--as

--begin

--if @num1>@num2  

--return '较大值'+convert(nvarchar,@num1) --较大值4

--else 

--return '较小值'+convert(nvarchar,@num2)

--end

--调用带有返回值存储过程 先声明变量 然后 存储过程的结果赋值给变量

--declare @rs5 nvarchar(20)

--exec @rs5=p_max 4,2

--select @rs5

--存储过程中，通过return关键字，只能返回整数

--如果希望存储过程中向外界返回其他类型，则可以用输出参数output

--注意，rerun一次只能返回一个整数，如果需要一次返回多个，则也需要使用

--输出参数output