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


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'

use SaleManagerDB


go






--给商品入库表创建Insert触发器（也就是用户对商品入库信息增加的时候）


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


drop trigger ProductStorage_Insert


go


create trigger ProductStorage_Insert


on ProductStorage after insert


as


    declare @ProductId varchar(50),@AddedCount int






  --从插入的Inserted临时表中获取数据


  select @ProductId=ProductId,@AddedCount=AddedCount from inserted






  --更新库存表中对应商品的库存数据


  update ProductInventory set TotalCount=TotalCount+@AddedCount


                where ProductId=@ProductId






go


--给库存表创建update触发器


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


drop trigger ProductInventory_update


go


create trigger ProductInventory_update


on ProductInventory after update


as






   declare @ProductId varchar(50),@totalCount int






   --从插入的临时表中获取商品ID和商品库存数据


   select @ProductId=ProductId,@totalCount=TotalCount from inserted






   --根据当前的库存数量更新商品的库存状态


   update ProductInventory set StatusId=case


                 when @totalCount>MaxCount then 2


         when @totalCount<MinCount and @totalCount>0 then -1


         when @totalCount=0 then -2


         else 1


     end where ProductId=@ProductId 


go






--给商品入库表创建【update】触发器（修改了对应商品的入库数量）


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


drop trigger ProductStorage_Update


go


create trigger ProductStorage_Update


on ProductStorage after update


as


       declare @ProductId varchar(50),@AddedCount int,@deletedCount int






     --从删除的临时表中获取旧的数据(也就是修改之前的数据）


     select @ProductId=ProductId,@deletedCount=AddedCount from deleted






     --从插入的临时表中获取新的数据


     select @AddedCount=AddedCount from inserted






     --更新库存表中库存数据


     update ProductInventory set TotalCount=TotalCount-@deletedCount+@AddedCount


                where ProductId=@ProductId


go






--给商品入库表创建【delete】触发器（删除了入库信息）


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


drop trigger ProductStorage_Delete


go


create trigger ProductStorage_Delete


on ProductStorage after  delete


as


       declare @ProductId varchar(50),@AddedCount int






     --从删除的临时表中获取已经删除的数据


     select @AddedCount=AddedCount,@ProductId=ProductId from deleted






      --更新库存表中对应商品的库存数据


     update ProductInventory set TotalCount=TotalCount-@AddedCount


                where ProductId=@ProductId


go






---------------------------------------【测试触发器】---------------------------------------


select * from ProductStorage


select * from ProductInventory where ProductId='6005004003001'


----商品进货入库


--insert into ProductStorage (ProductId,AddedCount) values('6005004003001',50)






--update ProductInventory set TotalCount=300,StatusId=1 where ProductId='6005004003001'






--修改入库数量


--update ProductStorage set AddedCount=100 where StorageId=100010






--删除已经添加的数据


delete from ProductStorage where StorageId=100012






select * from ProductStorage


select * from ProductInventory where ProductId='6005004003001'