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#中也可以直接使用
事务。