庫存管理系統 SQL

/*建立 Store 資料庫 */

 

create database Store

on primary

(

name=P_manage,

filename='C:/store/manage.mdf',

size=4MB,

filegrowth=10%

)

 

log on 

(

name=l_manage,

filename='C:/store/manage.ldf',

size=1MB,

filegrowth=1MB

)

 

go

 

use Store

 

go

 

/*員工表資訊表 */

 

create table Employee

(

Employee_id varchar(10) primary key ,--員工編號

Employee_name varchar(10) not null,--員工姓名

Employee_psd varchar(6) not null,--員工登陸密碼

Employee_Permission varchar(10) not null ,--員工許可權

Employee_address varchar(20), --員工地址

Employee_phone varchar(20) --員工電話號碼

)

 

go

 

/*材料資訊表 */

 

create table Materail

(

Materail_id  varchar(6)  primary key, --材料編號

Materail_name varchar(10) not null,--材料名稱

Materail_model varchar(20)  not null,--材料型號

Materail_unit varchar(6)  not null,--材料單位名稱

Materail_recorder varchar(50) --備註

)

 

go

 

/*材料庫存資訊表 */

 

create table GoodInfor

(

Materail_id  varchar(6)  primary key, --材料編號

Materail_name varchar(10) not null, --材料名稱

Materail_total int check(Materail_total>=0), --庫存數量

Materail_max int check(Materail_max>0),--最大庫存量

Materail_min int check(Materail_min>0)--最小庫存量

)

 

go

 

/*入庫資訊表 */

 

create table MaterailIn

(

Materail_Inid varchar(10) primary key , --入庫單編號

Materail_date datetime not null, --入庫時間

Materail_suply varchar(20) not null, --提供單位

Materail_quantity int check(Materail_quantity>=0) not null, --入庫數量

Materail_id  varchar(6) foreign key(Materail_id) references Materail(Materail_id), --材料編號

Materail_price float check(Materail_price>=0) not null , --進貨單價

Materail_checkID varchar(10) foreign key(Materail_checkID) references Employee(Employee_id) , --檢驗員編號

Materail_recorder varchar(50) not null --備註

)

 

go

 

/*出庫資訊表 */

 

create table MaterailOut

(

Materail_Inid varchar(10) primary key , --入庫單編號

Materail_date datetime not null, --出庫時間

Materail_depart varchar(20) not null, --使用部門

Materail_use varchar(20) not null, --材料用途

Materail_id  varchar(6) foreign key(Materail_id) references Materail(Materail_id), --材料編號

Materail_send int check(Materail_send>=0) not null, --出庫數量

Materail_recieveId varchar(10) foreign key(Materail_recieveId) references Employee(Employee_id) , --領取人員編號

Materail_recorder varchar(50) not null --備註

)

 

go

 

/*Employee表插入資料 */

 

insert into Employee values('1001','zhangsan','123','檢視 ',' 玄武區 ','15651010922')

insert into Employee values('1002','lisi','222','刪除 ',' 棲霞區 ','15101722342')

insert into Employee values('1003','wangwu','333','所有操作 ',' 清甫區 ','13276509151')

insert into Employee values('1004','libai','321','查詢 ',' 楚洲區 ','18756108936')

insert into Employee values('1005','dufu','234','修改 ',' 江寧區 ','13277509051')

 

go

 

/*Materail表插入資料 */

 

insert into Materail values('001','螺絲 ',' 大 ',' 個 ',' 合格品 ')

insert into Materail values('002','塑料 ',' 中 ',' 塊 ',' 合格品 ')

insert into Materail values('003','鋼管 ',' 短 ',' 根 ',' 合格品 ')

insert into Materail values('004','車輪 ',' 大 ',' 個 ',' 合格品 ')

insert into Materail values('005','車燈 ',' 小 ',' 個 ',' 合格品 ')

insert into Materail values('006','車燈 ',' 大 ',' 個 ',' 次品 ')

insert into Materail values('007','螺絲 ',' 中 ',' 個 ',' 次品 ')

insert into Materail values('008','車輪 ',' 小 ',' 個 ',' 次品 ')

insert into Materail values('009','車輪 ',' 中 ',' 個 ',' 次品 ')

insert into Materail values('010','車輪 ',' 小 ',' 個 ',' 次品 ')

insert into Materail values('011','車輪 ',' 大 ',' 個 ',' 次品 ')

 

go

 

/*GoodInfor表插入資料 */

 

insert into GoodInfor values('001','螺絲 ','100','400','10')

insert into GoodInfor values('002','塑料 ','50','200','10')

insert into GoodInfor values('003','鋼管 ','200','500','50')

insert into GoodInfor values('004','車輪 ','80','200','10')

insert into GoodInfor values('005','車燈 ','20','100','5')

insert into GoodInfor values('006','車燈 ','1','3','1')

insert into GoodInfor values('007','螺絲 ','10','50','5')

insert into GoodInfor values('008','車燈 ','20','100','10')

 

go

 

/*MaterailIn表插入資料 */

 

insert into MaterailIn values('0001','2010-4-1','螺絲公司 ','200','001','1.5','1001',' 情況符合 ')

insert into MaterailIn values('0002','2010-6-2','鋼管公司 ','100','003','15','1002',' 情況符合 ')

insert into MaterailIn values('0003','2010-10-1','塑料公司 ','300','002','10','1004',' 情況符合 ')

insert into MaterailIn values('0004','2010-11-7','汽車公司 ','1','006','50','1003',' 情況符合 ')

insert into MaterailIn values('0005','2011-4-1','朝陽公司 ','2','008','1.5','1001',' 情況符合 ')

insert into MaterailIn values('0006','2012-4-1','螺絲公司 ','20','007','1.5','1005',' 情況符合 ')

 

 

go

 

/*MaterailOut表插入資料 */

 

insert into MaterailOut values('0002','2008-6-2','組裝部 ',' 組裝 ','002','10','1002',' 情況符合 ')

insert into MaterailOut values('0001','2010-6-2','組裝部 ',' 組裝 ','003','30','1001',' 情況符合 ')

insert into MaterailOut values('0003','2011-4-1','組裝部 ',' 組裝 ','004','10','1004',' 情況符合 ')

insert into MaterailOut values('0006','2009-6-9','組裝部 ',' 組裝 ','002','10','1005',' 情況符合 ')

insert into MaterailOut values('0004','2011-10-2','組裝部 ',' 組裝 ','001','10','1003',' 情況符合 ')

insert into MaterailOut values('0005','2010-9-2','組裝部 ',' 組裝 ','002','10','1001',' 情況符合 ')

insert into MaterailOut values('0008','2010-4-2','組裝部 ',' 組裝 ','007','10','1002',' 情況符合 ')

insert into MaterailOut values('0007','2012-4-11','組裝部 ',' 組裝 ','008','10','1005',' 情況符合 ')

 

go

 

/*****使用者登入驗證 *****/

 

if exists(select name from sysobjects

         where name='pro_UserLogin' and type='P')

drop procedure pro_UserLogin

go

 

create procedure pro_UserLogin

@Employee_name varchar(10) ,

@Employee_psd varchar(6) ,

@Employee_Permission varchar(10) 

as

if exists(select Employee_name ,Employee_psd ,Employee_Permission from Employee 

          where Employee_name=@Employee_name and Employee_psd=@Employee_psd and Employee_Permission=@Employee_Permission)

begin 

set @Employee_name=(select Employee_name from Employee where Employee_name=@Employee_name)

set @Employee_Permission=(select Employee_Permission from Employee where Employee_Permission=@Employee_Permission) 

print '登陸成功 !'

print '使用者 '+@Employee_name+' 具有 '+@Employee_Permission+' 許可權 '

end

if exists(select Employee_name,Employee_psd ,Employee_Permission from Employee 

          where Employee_name!=@Employee_name and Employee_psd=@Employee_psd and Employee_Permission=@Employee_Permission)

begin 

print '登陸失敗 !'

print '使用者名稱錯誤！ '

end

if exists(select Employee_name ,Employee_psd,Employee_Permission from Employee 

          where Employee_name=@Employee_name and Employee_psd!=@Employee_psd and Employee_Permission=@Employee_Permission)

begin 

print '登陸失敗 !'

print '密碼錯誤！ '

end

if exists(select Employee_name ,Employee_psd,Employee_Permission from Employee 

          where Employee_name!=@Employee_name and Employee_psd!=@Employee_psd and Employee_Permission=@Employee_Permission)

begin 

print '登陸失敗 !'

print '使用者名稱與密碼均錯誤！ '

end

if exists(select Employee_name ,Employee_psd,Employee_Permission from Employee 

          where Employee_name=@Employee_name and Employee_psd=@Employee_psd and Employee_Permission!=@Employee_Permission)

begin 

print '登陸失敗 !'

print '許可權選擇有誤！ '

end

 

go

 

exec pro_UserLogin @Employee_name='zhangsan' ,@Employee_psd ='123', @Employee_Permission ='檢視 '

 

go

 

/*根據材料名稱查詢材料資訊 */

 

if exists(select name from sysobjects

         where name='pro_Materail_name' and type='P')

drop procedure pro_Materail_name

 

go

 

create procedure pro_Materail_name

@Materail_name varchar(10)

as

select MaterailIn.Materail_id,Materail_total,Materail_max,Materail_min ,Materail_price,Employee.Employee_name 

from GoodInfor ,MaterailIn,Employee

where Materail_name=@Materail_name and GoodInfor.Materail_id=MaterailIn.Materail_id 

                                   and Employee.Employee_id=MaterailIn.Materail_checkID

 

go

 

exec pro_Materail_name @Materail_name='螺絲 '

 

go

 

/*根據提供單位查詢材料資訊 */

 

if exists(select name from sysobjects

         where name='pro_Materail_suply' and type='P')

drop procedure pro_Materail_suply

 

go

 

create procedure pro_Materail_suply

@Materail_suply varchar(10)

as

select GoodInfor.Materail_id,Materail_total,Materail_max,Materail_min ,Materail_price,Employee.Employee_name

from GoodInfor ,MaterailIn,Employee 

where MaterailIn.Materail_suply=@Materail_suply and GoodInfor.Materail_id=MaterailIn.Materail_id

                                                and Employee.Employee_id=MaterailIn.Materail_checkID

go

 

exec pro_Materail_suply @Materail_suply='塑料公司 '

 

go

 

/*根據材料日期查詢材料資訊 */

 

if exists(select name from sysobjects

         where name='pro_Materail_time' and type='P')

drop procedure pro_Materail_time

 

go

 

create procedure pro_Materail_time

@Materail_date varchar(10)

as

select GoodInfor.Materail_id,Materail_total,Materail_max,Materail_min ,Materail_price,Employee.Employee_name

from GoodInfor ,MaterailIn,Employee 

where MaterailIn.Materail_date=@Materail_date and GoodInfor.Materail_id=MaterailIn.Materail_id

                                              and Employee.Employee_id=MaterailIn.Materail_checkID

go

 

exec pro_Materail_time @Materail_date='2010-10-1'

 

go

 

/*通過修改入庫出庫材料數量來動態改變庫存數量 */

if exists(select name from sysobjects

         where name='trigger_Materail_update')

drop trigger trigger_Materail_update

 

go

 

create trigger trigger_Materail_update

on GoodInfor

for update

as

update GoodInfor

set Materail_total=Materail_total+(select (Materail_quantity-Materail_send) from MaterailIn,MaterailOut,GoodInfor

                                   where MaterailIn.Materail_id=GoodInfor.Materail_id and GoodInfor.Materail_id=MaterailOut.Materail_id)

 

go

 

update MaterailIn

set Materail_quantity='100'

where Materail_Inid='0001'

go

select Materail_quantity,Materail_send,Materail_total,(Materail_quantity-Materail_send+Materail_total)

 from MaterailIn,MaterailOut,GoodInfor

                                   where MaterailIn.Materail_id=GoodInfor.Materail_id 

and GoodInfor.Materail_id=MaterailOut.Materail_id

 

go

 

/*通過插入入庫出庫表材料數量來動態改變庫存數量 */

if exists(select name from sysobjects

         where name='trigger_Materail_insert')

drop trigger trigger_Materail_insert

 

go

 

create trigger trigger_Materail_insert

on GoodInfor

for insert

as

update GoodInfor

set Materail_total=Materail_total+(select (Materail_quantity-Materail_send) from MaterailIn,MaterailOut,GoodInfor

                                   where MaterailIn.Materail_id=GoodInfor.Materail_id and GoodInfor.Materail_id=MaterailOut.Materail_id)

 

go

 

insert into MaterailIn values('0013','2010-6-1','螺絲公司 ','50','001','1.5','1001',' 情況符合 ')

insert into MaterailIn values('0014','2011-4-1','朝陽公司 ','20','008','1.5','1001',' 情況符合 ')

 

go

 

/****庫存量過小預警報告 ****/

 

if exists(select name from sysobjects

         where name='trigger_Materail_Quantity')

drop trigger trigger_Materail_Quantity

 

go

 

create trigger trigger_Materail_Quantity

on MaterailOut

for insert

as

declare @Materail_total int

declare @Materail_min int 

set @Materail_total=(select(Materail_total-Materail_send) from GoodInfor,MaterailOut

                     where GoodInfor.Materail_id=MaterailOut.Materail_id and Materail_Inid='0002')

set @Materail_min=(select Materail_min from GoodInfor,MaterailOut

                   where GoodInfor.Materail_id=MaterailOut.Materail_id and Materail_Inid='0002')

if(@Materail_total<=@Materail_min)

print '當前庫存量已小於最小庫存量，請訂貨！ '

rollback transaction

print '當前庫存量已小於最小庫存量，請訂貨！ '

go

insert into MaterailOut values('0015','2008-6-2','組裝部 ',' 組裝 ','002','500','1002',' 情況符合 ')

 

go

 

/**庫存量過大預警報告 **/

 

if exists(select name from sysobjects

         where name='trigger_Materail_surplus')

drop trigger trigger_Materail_surplus

 

go

create trigger trigger_Materail_surplus

on MaterailIn

for insert

as

declare @Materail_total int

declare @Materail_max int

 

set @Materail_total=(select(Materail_total+Materail_quantity) from GoodInfor,MaterailIn

                     where MaterailIn.Materail_id=GoodInfor.Materail_id and Materail_Inid='0001')

set @Materail_max=(select Materail_max from GoodInfor,MaterailIn

                   where GoodInfor.Materail_id=MaterailIn.Materail_id and Materail_Inid='0001')

if(@Materail_total>=@Materail_max)

 

print '當前庫存量已大於最大庫存量，不允許入庫操作！ '

rollback transaction

print '當前庫存量已大於最大庫存量，不允許入庫操作！ '

go

insert into MaterailIn values('0034','2010-4-1','螺絲公司 ','400','001','1.5','1001',' 情況符合 ')

 

go

 

/*月底結算詳細報表 */

 

if exists(select name from sysobjects

         where name='pro_Materail_check' and type='P')

drop procedure pro_Materail_check

go

 

create procedure pro_Materail_check

@Materail_date varchar(10)

as

select GoodInfor.Materail_id 材料編號  ,GoodInfor.Materail_name  材料名稱 ,(Materail_quantity-Materail_send+Materail_total)

庫存數量,MaterailIn.Materail_suply  提供單位 ,MaterailIn.Materail_price  進貨單價 ,

MaterailIn.Materail_quantity 入庫數量 ,MaterailOut.Materail_depart  使用部門 ,MaterailOut.Materail_send  出庫數量 

from GoodInfor,MaterailIn,MaterailOut

where GoodInfor.Materail_id=MaterailIn.Materail_id and GoodInfor.Materail_id=MaterailOut.Materail_id

and month(MaterailIn.Materail_date)=month(@Materail_date) and year(MaterailIn.Materail_date)=year(@Materail_date)

      

go

 

exec pro_Materail_check @Materail_date='2010-4-11'

 

/*系統安全管理 */

 

/*go

 

對使用者進行許可權分配

 

grant delete on Employee , Materail to aaaa with grant option  */

 

go

 

/*對系統資料庫進行備份 */

 

use Store

go

exec sp_addumpdevice 'disk','Store_bakdevice','C:/store'

 

go

 

/*對員工表資訊表 Employee 建立索引 */

 

go

 

create unique index Index_Employee on Employee(Employee_id)

 

go

 

/*刪除員工表資訊表 Employee 的索引 */

 

drop index Employee.Index_Employee

 

go

 

/*檢視員工表資訊表 Employee 的索引 */

 

exec sp_helpindex Employee

 

go

 

/*對材料資訊表 Materail 建立索引 */

 

create unique index Index_Materail on Materail(Materail_id)

 

go

 

exec sp_helpindex Materail

 

go

 

/*對材料庫存資訊表 GoodInfor 建立索引 */

 

create unique index Index_GoodInfor on GoodInfor(Materail_id)

 

go

 

exec sp_helpindex GoodInfor

 

go

 

/*對材料的銷售情況建立檢視 */

 

go

create view view_Materail_check

as

select GoodInfor.Materail_id 材料編號  ,GoodInfor.Materail_name  材料名稱 ,(Materail_quantity-Materail_send+Materail_total)

庫存數量,MaterailIn.Materail_suply  提供單位 ,MaterailIn.Materail_price  進貨單價 ,

MaterailIn.Materail_quantity 入庫數量 ,MaterailOut.Materail_depart  使用部門 ,MaterailOut.Materail_send  出庫數量 

from GoodInfor,MaterailIn,MaterailOut

where GoodInfor.Materail_id=MaterailIn.Materail_id and GoodInfor.Materail_id=MaterailOut.Materail_id

 

go

 

select * from  view_Materail_check    

 

/*對材料的入庫負責人員建立檢視查詢 */

 

go

 

create view view_MaterailIn

as

select MaterailIn.Materail_id 材料編號  ,GoodInfor.Materail_name  材料名稱 ,Employee.Employee_name  負責姓名 ,

MaterailIn.Materail_suply 提供單位 ,MaterailIn.Materail_price  進貨單價 ,MaterailIn.Materail_quantity  入庫數量

from MaterailIn,Employee,GoodInfor

where Employee.Employee_id=MaterailIn.Materail_checkID and GoodInfor.Materail_id= MaterailIn.Materail_id

 

go

 

select * from  view_MaterailIn 

   

go

 

/*****對材料的出庫負責人員建立檢視查詢 *****/

 

go

 

create view view_MaterailOut

as

select MaterailOut.Materail_id 材料編號  ,GoodInfor.Materail_name  材料名稱 ,Employee.Employee_name  負責姓名 ,

MaterailOut.Materail_depart 使用部門 ,MaterailOut.Materail_send  出庫數量 

from MaterailOut,Employee,GoodInfor

where Employee.Employee_id=MaterailOut.Materail_recieveId and GoodInfor.Materail_id= MaterailOut.Materail_id

 

go

 

select * from  view_MaterailOut    

 

/****************/