T-SQL:存储过程的编写

use SaleManagerDB
go
--普通查询
select Products.ProductId,ProductName,Unit,UnitPrice,TotalCount,MaxCount,MinCount,StatusDesc
from Products
inner join ProductInventory on ProductInventory.ProductId=Products.ProductId
inner join InventoryStatus on InventoryStatus.StatusId=ProductInventory.StatusId

--将前面的查询保存到视图
if exists(select * from sysobjects where  name='view_QueryInventoryInfo')
drop view view_QueryInventoryInfo
go
create view view_QueryInventoryInfo
as
      select Products.ProductId,ProductName,Unit,UnitPrice,TotalCount,MaxCount,MinCount,StatusDesc
from Products
inner join ProductInventory on ProductInventory.ProductId=Products.ProductId
inner join InventoryStatus on InventoryStatus.StatusId=ProductInventory.StatusId

go

--视图创建好以后，我们可以把它看成表一样查询
select * from view_QueryInventoryInfo where MaxCount>400
--视图不要嵌套

--实际开发中，我们可以根据查询的需要，创建很多视图，对于已经存在的视图，也可以修改。

--实际开发中我们查询的可能比较复杂，如下：（回顾C#基础语法中，有switch case）

if exists(select * from sysobjects where  name='view_QueryInventoryInfo2')
drop view view_QueryInventoryInfo2
go
create view view_QueryInventoryInfo2
as
      select Products.ProductId,ProductName,Unit,UnitPrice,TotalCount,MaxCount,MinCount,
	  StatusDesc=case
	                       when TotalCount>MaxCount then '库存过高'
						   when TotalCount<MinCount then '库存紧张'
						   when TotalCount=0 then '缺货'
						   else '库存正常'
	       end
from Products
inner join ProductInventory on ProductInventory.ProductId=Products.ProductId
inner join InventoryStatus on InventoryStatus.StatusId=ProductInventory.StatusId

go

--前面我们的StudentManageDB数据库中成绩表，我们想根据学员的总成绩做一个评比
--比如总成绩在120-130之间，表示D   131-160 表示C   161-180 B  181-200  A等级（给大家一个建议性作业）

select * from view_QueryInventoryInfo2

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

--存储过程编写
if exists(select * from sysobjects where name='usp_AddProduct')
drop procedure usp_AddProduct
go
create procedure usp_AddProduct
--存储过程输入参数的定义
@ProductId varchar(50), 
@ProductName varchar(50), 
@UnitPrice numeric(18,2), 
@Unit varchar(50),
@CategoryId int,
@MinCount int, 
@MaxCount int
as
  --在这里编写具体的存储过程内容

  --在商品信息表中，添加数据
  insert into Products(ProductId, ProductName, UnitPrice, Unit,  CategoryId)
  values(@ProductId, @ProductName, @UnitPrice, @Unit,  @CategoryId)

  --同时在商品库存表中，添加数据
  insert into ProductInventory(ProductId, MinCount, MaxCount, TotalCount,StatusId)
  values(@ProductId, @MinCount, @MaxCount,0, -2)
go

--存储过程调用
exec usp_AddProduct '6005004003099','测试商品1',50,'箱',10,100,200

--查询刚才添加的数据
select Products.ProductId, ProductName, UnitPrice, Unit, Discount, CategoryId,
 TotalCount, MinCount, MaxCount, StatusId
 from Products
 inner join ProductInventory on ProductInventory.ProductId=Products.ProductId
 where Products.ProductId='6005004003099'

 --创建一个存储过程，带参数默认值
 if exists(select * from sysobjects where name='usp_AddProduct2')
drop procedure usp_AddProduct2
go
create procedure usp_AddProduct2
--存储过程输入参数的定义
@ProductId varchar(50), 
@ProductName varchar(50), 
@UnitPrice numeric(18,2), 
@Unit varchar(50),
@CategoryId int,
@MinCount int, 
@MaxCount int,
@TotalCount int=0,  --可以给参数添加默认值，有默认值的参数必须放到后面，方便调用
@StatusId int=-2      --参数默认值
as
  --在这里编写具体的存储过程内容
  --在这个地方使用declare声明你要使用的变量...


  --在商品信息表中，添加数据
  insert into Products(ProductId, ProductName, UnitPrice, Unit,  CategoryId)
  values(@ProductId, @ProductName, @UnitPrice, @Unit,  @CategoryId)

  --同时在商品库存表中，添加数据
  insert into ProductInventory(ProductId, MinCount, MaxCount, TotalCount,StatusId)
  values(@ProductId, @MinCount, @MaxCount,@TotalCount,@StatusId)

  --如果你声明了自己的参数，可以在这个地方根据你的业务使用...(这个就相当于C#方法中的局部变量)


go

--调用带参数默认值的存储过程
exec usp_AddProduct2 '7005004003099','测试商品2',60,'箱',10,100,200  --后面两个参数全部使用默认值
exec usp_AddProduct2 '8005004003099','测试商品3',65,'箱',10,100,200,150,1  --都不使用默认值
exec usp_AddProduct2 '9005004003099','测试商品4',65,'箱',10,100,200,150 --第2个使用默认值
exec usp_AddProduct2 '9005004003199','测试商品5',65,'箱',10,100,200, @StatusId=1 --第1个使用默认值

--查询
select Products.ProductId,UnitPrice,Unit,categoryId,TotalCount,MinCount,MaxCount,StatusId
from Products
inner join ProductInventory on ProductInventory.ProductId=Products.ProductId

--输出参数的存储过程
 if exists(select * from sysobjects where name='usp_QueryProduct')
drop procedure usp_QueryProduct
go
create procedure usp_QueryProduct
 @ProductCount int output   --定义输出参数
as
     -- 你可以在这个地方执行任何的查询....
	 select ProductId, productName,UnitPrice from Products
	 --....

	 select @ProductCount=count(*)  from Products
go

--调用带参数的存储过程（我们在这个地方练习的时候还是比较麻烦的，但是你要学会，一般测试阶段都要用）
--在SQLServer中，声明变量使用declare

declare @ProductCount int  --首先定义输出参数
exec usp_QueryProduct @ProductCount output

--使用参数
select 商品总数=@ProductCount

--测试：我们给Products中的UnitPrice添加一个检查约束

alter table Products
add constraint ck_UnitPrice check(UnitPrice>=2)

--下面的操作，会造成第一个insert失败，但是第二个照常执行
exec usp_AddProduct '6005004003299','测试商品1',1,'箱',10,100,200

----------------------------------【事务的编写】----------------------------------------------------------------------------------------


--首先测试@@error全局变量的使用
if exists(select * from sysobjects where name='usp_AddProduct3')
drop procedure usp_AddProduct3
go
create procedure usp_AddProduct3
@ProductId varchar(50), 
@ProductName varchar(50), 
@UnitPrice numeric(18,2), 
@Unit varchar(50),
@CategoryId int,
@MinCount int, 
@MaxCount int
as
  
  declare @errorSum int --@errorSum这个变量用来记录错误号码的总数

  --在商品信息表中，添加数据
  insert into Products(ProductId, ProductName, UnitPrice, Unit,  CategoryId)
  values(@ProductId, @ProductName, @UnitPrice, @Unit,  @CategoryId)
  select @@error

  --同时在商品库存表中，添加数据
  insert into ProductInventory(ProductId, MinCount, MaxCount, TotalCount,StatusId)
  values(@ProductId, @MinCount, @MaxCount,0, -2)
  select 错误号码=@@error
go

--观察错误号码
exec usp_AddProduct3 '6005004003599','测试商品1',1,'箱',10,100,200

--正式编写事务
-----------------------------------------------------------------------------------------------------------------------------
if exists(select * from sysobjects where name='usp_AddProduct4')
drop procedure usp_AddProduct4
go
create procedure usp_AddProduct4
@ProductId varchar(50), 
@ProductName varchar(50), 
@UnitPrice numeric(18,2), 
@Unit varchar(50),
@CategoryId int,
@MinCount int, 
@MaxCount int
as
  
  declare @errorSum int --@errorSum这个变量用来记录错误号码的总数
  set @errorSum=0    --给当前的变量赋值初始数据

  --开启事务
  begin transaction
     begin
	      insert into Products(ProductId, ProductName, UnitPrice, Unit,  CategoryId)
          values(@ProductId, @ProductName, @UnitPrice, @Unit,  @CategoryId)
		  --紧跟在SQL语句后面，来捕获错误号码  @@error只要前面的SQL语句有错误，这个变量就不为0
		  set @errorSum=@errorSum+@@error

		   insert into ProductInventory(ProductId, MinCount, MaxCount, TotalCount,StatusId)
           values(@ProductId, @MinCount, @MaxCount,0, -2)
		   set @errorSum=@errorSum+@@error   --注意如果你有查询语句，查询语句后面不用记录

		   --测试(实际开发中，这个print不用谢)
		   print @errorSum

		   --判断是否发生错误
		   if @errorSum>0
		       rollback transaction   --回滚事务
		   else
		       commit transaction    --提交事务
	 end
go
--通过事务，测试下面的错误数据，会保证回滚
exec usp_AddProduct4 '6005004003799','测试商品1',1,'箱',10,100,200
--这个是正确的
exec usp_AddProduct4 '6005004003799','测试商品1',10,'箱',10,100,200

select * from Products where ProductId='6005004003799'
select * from ProductInventory  where ProductId='6005004003799'

 

八、事务

1. 为什么要使用事务？

通过刚才示例的观察，我们发现，当一个存储过程或多个SQL语句（指代insert、update、delete类型）依次执行时候，

如果其中一条或几条发生错误，但是其他的还会继续执行，会造成数据的不一致，非常危险。常见的，比如银行，我们从一个账号

转钱到另一个账号，如果当我们转出的时候，系统发生了故障，但是没有转入，这样的话，银行的数据就出问题。

想解决这个问题，我们使用事务！

2. 事务的特点

原子性：就是不可分割性，是一个整体。

一致性：也就是我们操作的数据，前后都保持高度的一致。

3. 主要作用：就是保证数据在不同的操作中，或者这些操作全部成功，或者失败的时候全部取消。

4. 事务的创建和使用（示例代码）

在SQLServer中全局变量使用@@标识

常见的：@@Identity      @@error 用来存储SQL操作最后一条语句的状态。

通过练习，大家充分感觉到使用事务的重要性！

建议：如果你在开发中，使用存储过程，只要是两个或两个以上的insert、update或delete类型的SQL被执行，你就都可以

使用事务。

后续扩展：我们在C#应用程序中，如果我们发送的SQL语句，有多个insert或update、delete，同样在C#中也可以直接使用
事务。