SQL Server数据仓库中的Cube配置视频

SQLServer高级查询与T-SQL编程实现仓储库存管理系统

SQLServer高级查询与T-SQL编程

• SQLServer高级查询与T-SQL编程实现仓储库存管理系统

• 1、语言和环境
• 2、功能需求
• 3、使用DDL创建数据库StorageDB
• 4. 使用DDL分别创建库存物品表(ProductsInfo)及进出库记录表(Recode)结构及约束
• 5. 使用DML向数据表中插入如下数据
• 6. 创建视图用于查询进出库详情，查询字段为货品ID、货品名称、进出库数量、进出库类型、进出库日期
• 5. 编写存储过程用于进出库记录，使用事务。

1、语言和环境

1.实现语言：T—SQL
2.环境要求：SQLServer2012及以上版本。

2、功能需求

某公司需要开发一款仓储库存管理系统，现有数据库概念模型及数据字典

概念模型：

物理模型：

数据字典：

属性名	说明	备注
ProductID	货品ID	主键，自增
ProductName	货品名称	约束：非空
Quantity	库存数量	约束：大于0
RecodeID	记录编号	主键,自增
Count	进出库数量
RecodeType	进出库类型	0:入库 1：出库
RecodeDate	进出库日期

3、使用DDL创建数据库StorageDB

--1. 使用DDL创建数据库StorageDB
create database StorageDB
--打开库
use StorageDB

4. 使用DDL分别创建库存物品表(ProductsInfo)及进出库记录表(Recode)结构及约束

--2. 使用DDL分别创建库存物品表(ProductsInfo)及进出库记录表(Recode)结构及约束
create table ProductsInfo(
	ProductID int identity(1,1) primary key not null,
	ProductName varchar(50) not null,
	Quantity int not null
)
create table Recode(
	RecodeID int identity(1,1) primary key not null,
	ProductID int references ProductsInfo(ProductID),--注意外键关系
	Count int not null,
	RecodeType smallint not null,
	RecodeDate date not null
)

5. 使用DML向数据表中插入如下数据

--3. 使用DML向数据表中插入如下数据
insert into ProductsInfo values('Dell 电脑',100)
insert into ProductsInfo values('雷朋键盘',150)
insert into ProductsInfo values('iPhone 手机',200)
select * from ProductsInfo

insert into Recode values(1,300,0,'2019-08-22')
insert into Recode values(1,150,1,'2019-08-25')
insert into Recode values(1,50,1,'2019-09-22')
insert into Recode values(2,100,0,'2019-08-22')
insert into Recode values(2,50,0,'2019-08-25')
insert into Recode values(3,300,0,'2019-08-22')
insert into Recode values(3,100,1,'2019-08-25')
select * from Recode

也可以使用批量插入，两个效果是一样的。

insert into ProductsInfo
select 'Dell 电脑' ,100 union
select '雷朋键盘' ,150 union
select 'iPhone 手机',200
insert into Recode 
select 1,300,0,'2019-8-22' union
select 1,150,1,'2019-8-25' union
select 1,50,1,'2019-8-22' union
select 2,100,0,'2019-8-22' union
select 2,50,0,'2019-8-25' union
select 3,300,0,'2019-8-22'union
select 3,100,1,'2019-8-25' 
go

6. 创建视图用于查询进出库详情，查询字段为货品ID、货品名称、进出库数量、进出库类型、进出库日期

视图中的进出库类型类型需要显示【入库/出库】

-- 4. 创建视图用于查询进出库详情，查询字段为货品ID、货品名称、进出库数量、进出库类型、进出库日期
--判断视图是否存在，存在则删除
if exists(select * from sysobjects where name='v_Details')
	drop view v_Details
go
create view v_Details as
select p.ProductID '货品ID',ProductName '货品名称',Count '进出库数量'
,(case r.RecodeType when 0 then '入库' when 1 then '出库' else 'nill' end )'进出库类型',RecodeDate '进出库日期' 
from ProductsInfo p join Recode r on p.ProductID=r.ProductID
go

这里使用了case when判断是入库还是出库

5. 编写存储过程用于进出库记录，使用事务。

进出库记录存储过程业务流程为:修改库存数->生成进出库记录，库存数小于0无法完成进出库过程

--5. 编写存储过程用于进出库记录并编写5条语句及进行测试，要求使用事务。
if object_id('proc_chuku') is not null
	drop proc proc_chuku 
go
create proc proc_chuku(@ProductName varchar(20),@churu varchar(20),@Quantity int)
as
   if exists(select * from ProductsInfo where ProductName=@ProductName)
		begin
			declare @ProductID int;--货品id
			declare @Quantity2 int;--库存
			declare @sumError int=0;--定义统计错误信息
			select @ProductID=ProductID,@Quantity2=Quantity from ProductsInfo where ProductName=@ProductName
				begin tran
					if @Quantity2-@Quantity<0
						begin
							print @churu+'失败,库存不足'
						end
						else
						begin
							if @churu='出库'
								begin
									update ProductsInfo set Quantity=Quantity-@Quantity where ProductID=@ProductID;
									set @sumError+=@@ERROR;--统计错误信息
									insert into Recode values(@ProductID,@Quantity,1,GETDATE())
									set @sumError+=@@ERROR;--统计错误信息
								end
								else
								begin
									update ProductsInfo set Quantity=Quantity+@Quantity where ProductID=@ProductID;
									set @sumError+=@@ERROR;--统计错误信息
									insert into Recode values(@ProductID,@Quantity,0,GETDATE())
									set @sumError+=@@ERROR;--统计错误信息
								end
							end
					if @sumError=0
				begin
					commit tran
					print @churu+'成功'
				end
				else
				begin
					print @churu+'失败'
					rollback tran--回滚事务
				end
		end
	else
	print'该货品不存在'
go

--带参数调用存储过程
declare @ProductName varchar(20)='iPhone 手机'
declare @churu varchar(20)='出库'
declare @Quantity int=99
exec proc_chuku @ProductName,@churu,@Quantity;

到此就结束啦，快去练习一下吧！欢迎大佬和小Monkey沟通。

感谢大佬指正 小Monkey