数据库 3

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’