任务五 数据库存储过程的规划与设计 SQL语言是应用程序和SQL Server数据库之间的主要编程接口。使用SQL语言编写代码时，可用两种方法存储和执行代码。一种是在客户端存储代码，并创建向数据库管理系统发送SQL命令（或SQL语句）并处理返回结果的应用程序；另外一种是将发送的SQL语句存储在数据库管理系统中，存储在数据库管理系统中的SQL语句就是存储过程，然后再创建执行存储过程并处理返回结果的应用程序。存储过程是SQL语句和控制流语句的预编译集合，它以一个名称存储并作为一个单元处理，应用程序可以通过调用来执行存储过程。利用存储过程可以使用户对数据库的管理和操作更加容易、效率更高。存储过程在SQL编程中应用非常广泛。 一、任务目标 1掌握创建和使用存储过程来完成对数据库的操作； 2 掌握在存储过程里面使用游标来实现循环的目的； 3 掌握事务的操作以及事务在存储过程中的应用； 二、教学任务 1 介绍存储过程和事务的概念； 2 存储过程的创建和执行语法； 3创建不带参数的存储过程； 4创建带参数的存储过程； 5创建带有多个输入参数并有默认值的存储过程 6 分析设计分销系统中需要的存储过程 5.1 SQL Server存储过程概述 5.1.1 存储过程的概念 当利用MS SQL Server 创建一个应用程序时，Transaction-SQL 是一种主要的编程语言。 172 若运用Transaction-SQL 来进行编程，有两种方法。其一是，在本地存储Transaction- SQL 程序，并创建应用程序向SQL Server 发送命令来对结果进行处理。其二是，可以把部分用Transaction-SQL 编写的程序作为存储过程存储在SQL Server 中，并创建应用程序来调用存储过程，对数据结果进行处理存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定；返回状态值给调用者，指明调用是成功或是失败；包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。在SQL编程中，第二种方法应用更加广泛，即在SQL Server 中使用存储过程而不是在客户计算机上调用Transaction-SQL 编写的一段程序，原因在于存储过程允许标准组件式编程，存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL 语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。 数据库管理系统中的存储过程与其它程序设计语言中的过程类似，存储过程可以： 接受输入参数并以输出参数的形式将多个值返回至调用程序。 包含执行数据库操作（包括调用其它存储过程）的编程语句。 向调用程序返回状态值，以表明成功或失败（以及失败原因）。 在SQL Server 的系列版本中存储过程分为两类：系统提供的存储过程和用户自定义存储过程。系统过程主要存储在master 数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。通过系统存储过程，MS SQL Server 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。尽管这些系统存储过程被放在master 数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在本章中所涉及到的存储过程主要是指用户自定义存储过程。 5.1.2 创建和执行存储过程 创建存储过程的SQL语句为CREATE PROCEDURE，其语法格式为： CREATE PROC 存储过程名 [｛@参数名 数据类型｝[=default][OUTPUT]] 173 AS SQL语句[…] 其中 [= default]为参数的默认值。如果定义了默认值，则不指定该参数的值也可以执行该存储过程。默认值必须是常量或NULL。 [OUTPUT]：带有OUTPUT的参数是输出参数，不带OUTPUT的参数是输入参数。 执行存储过程的语句是EXECUTE，其语法格式为： [EXEC [UTE ]] 存储过程名 [实参 [,OUTPUT] [,…n] ] 任务5-1：创建不带参数的存储过程Proc_kcxx1，查询仓库编码为“001”的期初库存信息。 在SQLQuery窗口中执行如下命令： USE 分销系统 GO CREATE PROCEDURE Proc_kcxx1 As select 仓库编码,仓位编码,商品编码,商品名称,单位,期初数量,期初金额 from 期初库存 where 仓库编码='001' 任务5-2：执行存储过程Proc_kcxx1。 在SQLQuery窗口中执行如下命令： USE 分销系统 GO EXEC Proc_kcxx1 GO 任务5-3：创建带参数的存储过程。对上面的存储过程进行改造，改成带一个参数，仓库编码，可以指定需要查询的仓库编码的期初库存信息。 在SQLQuery窗口中执行如下命令： 174 CREATE PROCEDURE Proc_kcxx2 (@ckbm varchar(20)) As Select 仓库编码,仓位编码,商品编码,商品名称,单位,期初数量,期初金额 from 期初库存 where 仓库编码=@ckbm GO 任务5-4：执行此存储过程,查询仓库编码为“001”的期初库存信息。 在SQLQuery窗口中执行如下命令： EXEC Proc_kcxx2 '001' 任务5-5：创建带有多个输入参数并有默认值的存储过程。对上面的存储过程进行改造，改成带两个参数，仓库编码和商品名称，可以指定需要查询的仓库编码和商品名称的期初库存信息，其中仓库编码默认值为“0001”。 在SQLQuery窗口中执行如下命令： CREATE PROCEDURE Proc_kcxx3 (@spmc varchar(50), @ckbm varchar(20)= '001') As Select 仓库编码,仓位编码,商品编码,商品名称,单位,期初数量,期初金额 from 期初库存 where 仓库编码=@ckbm and 商品名称=@spmc 任务5-6：执行此存储过程,查询仓库编码为“001”,商品名称为“阿一波无沙紫菜25g”的期初库存信息。 在SQLQuery窗口中执行如下命令： EXEC Proc_kcxx3 '阿一波无沙紫菜25g','001' 如果在定义存储过程时为参数指定了默认值，则在执行存储过程时可以不为有默认值的参数提供值。如执行此存储过程,只指明商品名称为“运动水壶”的期初库存信息： EXEC Proc_kcxx3 '阿一波无沙紫菜25g' 或者 EXEC Proc_kcxx3 @spmc='阿一波无沙紫菜25g' 相当于执行： 175 EXEC Proc_kcxx3 '阿一波无沙紫菜25g','001' 在存储过程中，不但可以写查询语句，还可以写任何其它的SQL语句。如创建更新数据语句。 任务5-7：创建存储过程，把期初库存中指定的记录更新为：期初金额＝期初数量×期初单价。 在SQLQuery窗口中执行如下命令： CREATE PROCEDURE Proc_kcxx_update @spmc varchar(50),@ckbm varchar(20)='001' As Update 期初库存 set 期初金额=期初数量*期初单价 where 仓库编码=@ckbm and 商品名称=@spmc 任务5-8：执行此存储过程，将所有商品名称为“阿一波无沙紫菜25g”且仓库编码为“001”的期初库存信息中期初金额重新计算。 在SQLQuery窗口中执行如下命令： EXEC Proc_kcxx_update '阿一波无沙紫菜25g','001' 任务5-9：在存储过程中，可以对多个表进行操作，并且可以使用游标。下面的例子将指定供应商供应的商品的出库信息里面的单价统一更新为某个指定价格。为了说明游标的使用，在这个例子中特意用了游标，当然，不用游标也可以实现这个功能。注意的是，在存储过程里面可以使用注释，其中—开头的那一行都是注释，以后在存储过程中如果有必要，都会写些简单的注释帮助读者理解。 注：由于后面的存储过程涉及到分销系统的多个表，请读者自行按照预备知识四的分销系统建模分析的数据把分销系统的各个表都建好，并按照预备知识三需求分析的数据，把数据插入各个表中。 在SQLQuery窗口中执行如下命令（“--”符号后面是注释）： CREATE PROCEDURE [dbo].[Proc_rkdj_update] @gysbm varchar(20),@dj numeric(12,2) --参数@gysbm是指定的供应商编码，@dj是指定的单价，为2位小数的数字类型。 As declare @spbm varchar(20) 176 --定义一个变量名@spbm用来从游标里面读出数据 DECLARE crusor_spbm CURSOR FOR SELECT distinct 商品编码 FROM 入库单 a left outer join 入库单明细表 b on a.入库单号 = b.入库单号 where a.供应商编码=@gysbm --这里定义一个名字为crusor_spbm的游标，并且从入库单里面找到该指定供应商供应的所有商品编码，存进游标里面。Distinct关键字是用来去掉重复的商品编码值。 open crusor_spbm --打开游标crusor_spbm fetch next from crusor_spbm into @spbm -- 从游标crusor_spbm里面读出一个商品编码，并赋值给变量@spbm while @@fetch_status=0 --建立一个while循环，当@@fetch_status＝0时，即游标还没有游到最后一行数据时，继续循环体的执行。 BEGIN -- 循环体开始 update 入库单明细表 set 单价=@dj where 商品编码=@spbm --将商品编码＝@spbm的单价设置为指定单价。 Fetch next from crusor_spbm into @spbm --将游标里面下一个商品编码值赋值给@spbm，并且游标往下游一步。 END --循环结束 CLOSE crusor_spbm --关闭游标crusor_spbm DEALLOCATE crusor_spbm --释放游标crusor_spbm 任务5-10：执行此存储过程，将供应商编码为“8002”所订购的所有商品的出库信息单价强行更新为3.3元。 EXEC Proc_rkdj_update '8002' , 3.3 177 在存储过程中，如果里面的某些语句要不全部执行，要不全部不执行，一保证其一致性，则可以通过建立事务来实现其功能。 任务5-11：创建存储过程，更新指定入库单号的指定商品编码的单价为指定价格，并更新金额，同时也要更新该入库单主表的总金额。 在SQLQuery窗口中执行如下命令（“--”符号后面是注释）： create PROCEDURE [dbo].[Proc_rkje_update] @rkdh varchar(20),@spbm varchar(20),@dj numeric(12,2) --参数@rkdh是指定的供应商编码，@spbm 为指定的商品编码，@dj是指定的单价，为2位小数的数字类型。 As declare @i int set @i=0 begin transaction --定义事务开始 update 入库单明细表 set 单价=@dj where 入库单号=@rkdh and 商品编码=@spbm set @i=@i+@@ERROR update 入库单明细表 set 金额=数量*单价 where 入库单号=@rkdh and 商品编码=@spbm set @i=@i+@@ERROR update 入库单 set 总金额=(select sum(金额) as zje from 入库单明细表 where 入库单号=@rkdh) where 入库单号=@rkdh set @i=@i+@@ERROR If @i <> 0 --当@@Error <> 0，即上面三个update语句有出错时，回滚事务到初始阶段。 BEGIN END 178 ROLLBACK TRANSACTION ELSE --否则，即上面三个update语句都成功执行时，提交事务完成所有操作。 BEGIN COMMIT TRANSACTION END 任务5-12：执行此存储过程，将入库单号为“RK001”，商品编码为“A-001”的单价改为1.45元并相应更改金额和总金额。 在SQLQuery窗口中执行如下命令： EXEC Proc_rkje_update 'RK001','A-001',1.45 由于定义了事务，就不会有只是更新了其中一些数据，而其它没有更改到的情况出现。 5.2 分销系统存储过程的创建 5.2.1 项目中需要设计的存储过程 在分销系统里面，库存计算是其中最基本的功能，也是最复杂的一个计算过程。一般的做法是建立一个库存交易表，然后建立一个存储过程来计算库存。在本书里面，为了简单化，对库存计算采取不用库存交易表的方法，直接将跟库存有关系的各种表单（期初库存、入库单、出库单、盘点表、调拨单、报废单）的数据取出来放到一个临时表中，然后在统一计算出某个商品的库存。这种方法实现起来比较简单，但是当业务单据数据量多的时候，存在一个计算速度慢的问题`。但是本任务只是通过这个简单的例子来使大家能够掌握在SQL Server里面进行存储过程设计的要领。 同样的，在分销系统里面，还存在应付款计算和应收款计算的问题，这些计算过程，都需要设计一个存储过程来实现。 5.2.2 库存计算存储过程设计实例 179 典型的库存计算流程如图5-1，下面的例子是用来进行库存计算的存储过程。 开始创建一个临时表根据输入的条件产生where字符串将期初库存插入临时表将入库单插入临时表将出库单插入临时表将盘点表插入临时表将调拨单插入临时表将报废单插入临时表将临时表的数据汇总求出库存数量 图5-1 库存计算简单流程图 任务5-13：创建一个简单的库存计算存储过程。 create proc [dbo].[pro_kcjs]( @ckbh varchar(100) ='%%', @cw varchar(100)= '%%', @spbm varchar(100)='%%', @spmc varchar(100)= '%%' ) as create table #kc 180 ( ckbh varchar(100), cw varchar(100), spbm varchar(100), spmc varchar(100), sl numeric(12,2) ) declare @wherestr as varchar(5000) declare @sqlstr as varchar(8000) --产生where条件 set @wherestr = ' where 仓库编码 like ''' + @ckbh + ''' and 仓位编码 like ''' + @cw + ''' and 商品编码 like ''' + @spbm + ''' and 商品名称 like ''' + @spmc + '''' --将期初库存插入临时表 --产生字符串 set @sqlstr = 'insert into #kc(ckbh,cw,spbm,spmc,sl) select 仓库编码,仓位编码,商品编码,商品名称,期初数量 from 期初库存 ' + @wherestr --执行字符串 print @sqlstr exec (@sqlstr) --将期初库存插入临时表结束 --将入库单插入临时表 --产生字符串 set @sqlstr = 'insert into #kc(ckbh,cw,spbm,spmc,sl) select 仓库编码,仓位编码,商品编码,商品名称,数量 from 入库单明细表 ' + @wherestr --执行字符串 exec (@sqlstr) --将入库单插入临时表结束 181 --将出库单插入临时表 --产生字符串 set @sqlstr = 'insert into #kc(ckbh,cw,spbm,spmc,sl) select 仓库编码,仓位编码,商品编码,商品名称,-数量 as 数量 from 出库单明细表 ' + @wherestr --执行字符串 exec (@sqlstr) --将出库单插入临时表结束 --将调拨单插入临时表 --产生字符串 set @sqlstr = 'insert into #kc(ckbh,cw,spbm,spmc,sl) select 仓库编码,仓位编码,商品编码,商品名称,数量 from (select 调出仓库编码 as 仓库编码,调出仓位编码 as 仓位编码,商品编码,商品名称,-调拨数量 as 数量 from 调拨单 dbd left outer join 调拨单明细表 dbdmxb on dbd.调拨单号=dbdmxb.调拨单号 union all select 调入仓库编码 as 仓库编码,调入仓位编码 as 仓位编码,商品编码,商品名称,调拨数量 as 数量 from 调拨单 dbd left outer join 调拨单明细表 dbdmxb on dbd.调拨单号=dbdmxb. 调拨单号) a ' + @wherestr --执行字符串 exec (@sqlstr) --将调拨单插入临时表结束 --将报废单插入临时表 --产生字符串 set @sqlstr = 'insert into #kc(ckbh,cw,spbm,spmc,sl) select 仓库编码,仓位编码,商品编码,商品名称,-报废数量 as 数量 from 报废单明细表 ' + @wherestr --执行字符串 exec (@sqlstr) --将报废单插入临时表结束 182 --将库存汇总计算 select ckbh as 仓库编码,cw as 仓位,spbm as 商品编码,spmc as 商品名称,sum(sl) as 数量 from #kc group by ckbh,cw,spbm,spmc 任务5-14：执行任务5-13的存储过程计算库存。 Exec pro_kcjs 5.2.3 项目中其它需要设计的存储过程 除了上面所举例子外，在分销系统里面，应收款计算，应付款计算，甚至库存成本计算，也需要设计存储过程来实现。下面提供典型应付帐款、应收帐款的流程图以及存储过程供读者参考，请读者参照这些例子完成其它存储过程的设计与编写。 图5-2求应付款简单流程图 任务5-15：创建一个简单的应付款计算存储过程。 --求应付款的简单存储过程 create proc pro_yfk( @gysbm varchar(100)='%%', 183 @gysmc varchar(100)= '%%' ) as create table #yfk ( gysbm varchar(100), gysmc varchar(100), je numeric(12,2), ) declare @wherestr as varchar(5000) declare @sqlstr as varchar(8000) --产生where条件 set @wherestr = ' where 供应商编码 like ''' + @gysbm + ''' and 供应商名称 like ''' + @gysmc + '''' --将入库单明细插入临时表 --产生字符串 set @sqlstr ='insert into #yfk(gysbm,gysmc,je) select 供应商编码,供应商名称,金额 from 入库单 a left outer join 入库单明细表 b on a.入库单号=b.入库单号 ' + @wherestr --执行字符串 print 1 exec (@sqlstr) --将入库单插入临时表结束 print 2 --将付款单插入临时表 --产生字符串 set @sqlstr = 'insert into #yfk(gysbm,gysmc,je) select 供应商编码,供应商名称,-付款金额 from 付款单' + @wherestr --执行字符串 184 exec (@sqlstr) --将付款单插入临时表结束 --将应付款汇总计算 select gysbm as 供应商编码,gysmc as 供应商名称,sum(je) as 金额 from #yfk group by gysbm,gysmc 任务5-16：执行任务5-16的存储过程计算应付款。 Exec pro_yfk 图5-3求应收款简单流程图 任务5-17：创建一个简单的应收款计算存储过程。 --求应收款的简单存储过程 Create proc pro_ysk( @khbm varchar(100)='%%', @khmc varchar(100)= '%%' ) as 185 create table #ysk ( khbm varchar(100), khmc varchar(100), je numeric(12,2), ) declare @wherestr as varchar(5000) declare @sqlstr as varchar(8000) --产生where条件 set @wherestr = ' where 客户编码 like ''' + @khbm + ''' and 客户名称 like ''' + @khmc + '''' --将出库单明细插入临时表 --产生字符串 set @sqlstr = 'insert into #ysk(khbm,khmc,je) select 客户编码,客户名称,金额 from 出库单 a left outer join 出库单明细表 b on a.出库单号=b.出库单号 ' + @wherestr --执行字符串 exec (@sqlstr) --将出库单插入临时表结束 --将收款单插入临时表 --产生字符串 set @sqlstr = 'insert into #ysk(khbm,khmc,je) select 客户编码,客户名称,-收款金额 from 收款单' + @wherestr --执行字符串 exec (@sqlstr) --将收款单插入临时表结束 --将应收款汇总计算 select khbm as 客户编码,khmc as 客户名称,sum(je) as 金额 from #ysk group by khbm,khmc 186 任务5-18：执行任务5-17的存储过程计算应收款。 Exec pro_ysk 187
