第四章 2、存储过程

4.2.1 存储过程的基本概念

1. 概念：
     在使用Transact-SQL语言编程的过程中，可以将某些需要多次调用以实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名调用，称为存储过程。

4.2.2 创建存储过程

• 格式：创建存储过程的SQL语句格式为：

  CREATE PROCEDURE 存储过程名[;版本号]
  	[{@参数 数据类型}[= 默认值][OUTPUT],
  	······]
  AS--后面跟要执行的语句
  	SQL语句
  

• 语句含义：
  • ［;版本号］：把多个同名的存储过程合成一个组。
  • @参数：存储过程中的参数，可以声明一个或多个参数，但必须在执行存储过程时提供每个参数的值（除非定义了该参数的默认值）。
  • 数据类型：参数的数据类型
  • =默认值：设置参数的默认值，如果定义了默认值，则不必指定该参数的值即可执行存储过程，默认值必须是常量或NULL，特可以报货通配符。
  • output：表明参数是返回参数
• 一些存储过程：
  • 基本存储过程：
    1. 创建一个最简单的存储过程，用于返回stock表中的所有记录。

       CREATE PROCEDURE exp1
       AS
       	SELECT * FROM Stock
       GO
       

    2. 执行语句：

       EXECUTE exp1
       或者：
       EXEC exp1
       

  • 带输入参数的存储过程：
    1. 创建一个存储过程，通过输入的仓库名称显示出该仓库的所有库存物资信息。

       CREATE  PROCEDURE exp2
       	@ckmc varchar(50)
       AS
       	SELECT * 
       	FROM Stock
       	WHERE warehouse=@ckmc
       GO
       

    2. 创建一个带输入参数的存储过程，想stock表中添加一个新的数据行。

       if exists (select name from sysobjects where name='exp3' and type='P')
       	drop procedure exp3
       go
       --为了确保create命名能成功执行，可以在create procedure 之前执行上述语句。
       create procedure exp3
       	@mno char(8),@mname varchar(50),@mspeci varchar(20)
       as
       	insert
       	into stock(mat_num,mat_name,speci)
       	values(@mno,@mname,@mspeci)
       go
       

    3. 执行该存储过程：

       execute exp3 'm030','护套绝缘电线','BVV-35'
       或者：
       execute exp3 @mno='m030',@mname='护套绝缘电线',@mspeci='BVV-35'
       或者：
       execute exp3 @mname='护套绝缘电线',@mspeci='BVV-35',@mno='m030'
       

  • 带默认输入参数的存储过程：
    1. 创建一个带默认参数的存储过程，通过传递的参数显示物资的名称、规格、项目名称、是否按期完工等信息，如果没有提供参数，则使用预设的默认值。

       if exists (select name from sysobjects where name='exp3' and type='P')
       	drop procedure exp4
       go/*防止系统表中有exp4*/
       create procedure exp4
       	@mname varchar(50)='%绝缘%',@pno char(8)='20110001' --等号后面为参数的默认值
       as
       	select mat_name,speci,prj_name,prj_status
       	from stock,salvaging,out_stock
       	where stock.mat_num=out_stock.mat_num
       	 and  salvaging.prj_num=out_stock.prj_num
       	 and  mat_name like @mname
       	 and  salvaging.prj_num=@pno
       go
       

    2. 执行过程：

       execute exp4
       或者：
       execute exp4'绝缘电线'
       或者：
       execute exp4 @pno='20110001'
       或者：
       execute exp4 '护套绝缘电线','20110001'
       

  • 带输出参数的存储过程：
    1. 创建一个存储过程，求某一个抢修工程项目号统计其领取物资的总数量。

       create procedure sum_mat
       	@pn char(8),@sum int output      
       	  /*output表示此参数为返回参值，
       	当我们调用这个存储过程时，可以通过返回参数，返回给用户相关的值*/
       as
       	select @sum=sum(amount)
       	from out_stock
       	where prj_num=@pn
       go
       

    2. 执行过程：

       declare @total int
       execute sum_mat'20110001',@total output
       print '该项目领取物资总量为：'+cast(@total as varchar(20))
       

    3. 创建一个存储过程，根据输入的工程部门及起始时间段，统计汇总该部门在对应时间段内所参与抢修的工程项目总数以及领取物资的总成本，并要求输出。

       create procedure sum_count
       	@department varchar(50),@start_date datetime,@end_date datetime,
       	@count_prj int output,@sum_cost decimal(18,2) output
       as
       	select @count_prj=count(salvaging.prj_num),@sum_cost=sum(out_stock.amount*stock.unit)
       	from salvaging,out_stock,stock
       	where out_stock.prj_num=salvaging.prj_num
       	 and  out_stock.mat_num=stock.mat_num
       	 and  department=@department
       	 and  get_date between @start_date and  @end_date
       go
       

    4. 执行语句：

       declare @prjcounts int,@sumcosts decimal(18,2)
       exec sum_count '工程2部','2011-1-1','2011-1-31',@prjcounts output,@sumcosts output
       print '该部门参与抢修工程项目'+cast(@prjcounts as varchar(20))+‘个，总成本为'+cast(@sumcosts as varchar(20))
       

  • 嵌套调用存储过程：
    1. 嵌套调用存储过程，查看使用抢修物资总数最多的工程项目信息。

       create procedure exp6
       	@prj_no char(8) output
       as
       	select top1 @prj_no = prj_num -- 对局部变量prj_no赋值
       	from out_stock
       	group by prj_num
       	order by sum(amount)desc
       go
       
       create procedure exp7
       as
       	declare @prj_id char(8)
       	exec exp6 @prj_id output
       	select * 
       	from salvaging
       	where prj_num=@prj_id
       go
       

    2. 执行语句

       exec exp7
       

4.2.3 修改和删除存储过程

• 修改存储过程语句：

  Alter procedure 存储过程名[;版本号]
  	[{@参数 数据类型}[= 默认值][OUTPUT],
  	······]
  AS
  	SQL语句
  

• 删除过程语句;

  drop procedure 存储过程名
  

  注：一条删除语句可以删除多个存储过程，且该语句不分版本号。该语句可以同时删除同名的所有存储过程。