倉(cāng)庫(kù)管理系統(tǒng)

1、<附錄>設(shè)計(jì)報(bào)告參考格式：XXXXX課程設(shè)計(jì)課 程：數(shù)據(jù)庫(kù)題 目：倉(cāng)庫(kù)管理系統(tǒng)專 業(yè)： 軟件工程班 級(jí)：座 號(hào)：姓 名： ZJ年 月 日倉(cāng)庫(kù)管理系統(tǒng)引言：數(shù)據(jù)庫(kù)技術(shù)是計(jì)算機(jī)科學(xué)中的一個(gè)非常重要的部分，數(shù)據(jù)庫(kù)技術(shù)以數(shù)據(jù)庫(kù)的應(yīng)用也正以日新月異的速度發(fā)展。倉(cāng)庫(kù)管理在企業(yè)的整個(gè)供應(yīng)鏈中起著至關(guān)重要的作用，如果不能保證正確的進(jìn)貨和庫(kù)存控制及發(fā)貨，將會(huì)導(dǎo)致管理費(fèi)用的增加，服務(wù)質(zhì)量難以得到保證，從而影響企業(yè)的競(jìng)爭(zhēng)力。傳統(tǒng)簡(jiǎn)單、靜態(tài)的倉(cāng)庫(kù)管理已無法保證企業(yè)各種資源的高效利用。如今的倉(cāng)庫(kù)作業(yè)和庫(kù)存控制作業(yè)已十分復(fù)雜化多樣化，僅靠人工記憶和手工錄入，不但費(fèi)時(shí)費(fèi)力，而且容易出錯(cuò)，給企業(yè)帶來巨大損失。使用

2、倉(cāng)庫(kù)管理系統(tǒng) , 對(duì)倉(cāng)庫(kù)各環(huán)節(jié)實(shí)施全過程控制管理，并可對(duì)貨物進(jìn)行貨位、批次、保質(zhì)期、配送等實(shí)現(xiàn)系統(tǒng)列號(hào)管理，對(duì)整個(gè)收貨、發(fā)貨、補(bǔ)貨、集貨、送貨等各個(gè)環(huán)節(jié)的規(guī)范化作業(yè) , 還可以根據(jù)客戶的需求制作多種合理的統(tǒng)計(jì)報(bào)表。倉(cāng)庫(kù)管理系統(tǒng)能幫助企業(yè)合理有效地利用倉(cāng)庫(kù)空間，以快速、準(zhǔn)確、低成本的方式為客戶提供最好的服務(wù)。 關(guān)鍵字：SQL，Delphi，倉(cāng)庫(kù)管理，系統(tǒng)1.倉(cāng)庫(kù)管理系統(tǒng)系統(tǒng)功能的基本要求：1）產(chǎn)品入庫(kù)管理，可以填寫入庫(kù)單，確認(rèn)產(chǎn)品入庫(kù)；2）產(chǎn)品出庫(kù)管理，可以填寫出庫(kù)單，確認(rèn)出庫(kù)；3）借出管理，憑借條借出，然后能夠還庫(kù)；4）初始庫(kù)存設(shè)置，設(shè)置庫(kù)存的初始值，庫(kù)存的上下警戒限；5）可以進(jìn)行盤庫(kù)，反映每

3、月、年的庫(kù)存情況；6）可以查詢產(chǎn)品入庫(kù)情況、出庫(kù)情況、當(dāng)前庫(kù)存情況，可以按出庫(kù)單、入庫(kù)單，產(chǎn)品、時(shí)間進(jìn)行查詢；2.需求分析基本資料管理：供貨單位管理：設(shè)置并管理供貨商檔案，檔案信息包括客戶編號(hào)、客戶名稱、地址、聯(lián)系人、聯(lián)系電話和備注信息。1 收貨單位管理：設(shè)置并管理收貨單位檔案，檔案信息包括客戶編號(hào)、客戶名稱、地址、聯(lián)系人、聯(lián)系電話和備注信息。2 貨品檔案管理：檔案信息包括貨品名稱、編號(hào)、貨品描述、計(jì)量單位、備注說明。3 庫(kù)管人員設(shè)置：設(shè)置并管理倉(cāng)庫(kù)管理人員。倉(cāng)庫(kù)貨物進(jìn)出管理：1 貨品入倉(cāng)管理：可以自動(dòng)生成入庫(kù)單號(hào)，貨品及客戶選擇方便快捷，不限制每筆入庫(kù)單貨物筆數(shù)。2 貨品出倉(cāng)管理：可以自動(dòng)生

4、成出庫(kù)單號(hào)，貨品及客戶選擇方便快捷，不限制每筆出貨單貨物筆數(shù)。3 貨品盤點(diǎn)管理：可以自動(dòng)生成盤點(diǎn)單號(hào)，貨品選擇方便快捷。庫(kù)存查詢及統(tǒng)計(jì)：1 庫(kù)存查詢：可以隨時(shí)查詢各種貨品庫(kù)存，貨品自動(dòng)分類匯總統(tǒng)計(jì)。2 單據(jù)查詢：可以隨時(shí)查詢?nèi)霂?kù)、出庫(kù)、盤點(diǎn)等各種貨物單據(jù)。3 綜合查詢：可以隨時(shí)查詢不同時(shí)間段、憑證號(hào)、客戶、貨品等查詢條件查詢?nèi)?、出、盤等數(shù)據(jù)，并能對(duì)查詢數(shù)據(jù)進(jìn)行統(tǒng)計(jì)匯總。 庫(kù)存分析報(bào)警：1 庫(kù)存上下限設(shè)置：可以設(shè)定倉(cāng)庫(kù)庫(kù)存中各項(xiàng)貨品的庫(kù)存上下限。庫(kù)3.概念結(jié)構(gòu)設(shè)計(jì)1）庫(kù)存實(shí)體E-R圖：2）入庫(kù)實(shí)體E-R圖：3) 出庫(kù)實(shí)體E-R圖：4）部門需求實(shí)體E-R圖：5）還庫(kù)實(shí)體E-R圖：6）計(jì)劃采購(gòu)實(shí)體E

5、-R圖：7）實(shí)體和實(shí)體之間的關(guān)系圖如圖所示：8) 倉(cāng)庫(kù)管理E-R圖如圖所示4.邏輯結(jié)構(gòu)設(shè)計(jì) 關(guān)系模型設(shè)計(jì)1) 倉(cāng)庫(kù)表STORE_INFONO.字段名代碼類型約束1倉(cāng)庫(kù)號(hào)stoIDChar(3)主鍵2已用庫(kù)存Used_volumeSmallint非空，默認(rèn)0，unsigned，<=All_volume3庫(kù)存總量All_volumeSmallint非空，默認(rèn)10000，unsigned4負(fù)責(zé)人principalVarchar(20)非空5電話號(hào)碼stoTELVarchar(15)2) 零件表PRO_INFONO.字段名代碼類型約束1零件號(hào)PRO_CODEChar(7)主鍵2名稱PRO_NAM

6、EVarchar(20)非空3規(guī)格PRO_SIZEChar(10)4單價(jià)priceDecimal(6,2)非空，默認(rèn)0，unsigned5計(jì)量單位mensurationChar(4)非空，默認(rèn)“元”6描述descriptionText3) 供應(yīng)商SUPPLIER_INFONO.字段名代碼類型約束1編號(hào)SUPPLIER_CODEvarchar(5)主鍵2供應(yīng)商SUPPLIER_NAMEVarchar(25)非空3電話號(hào)碼LINK_PHONEVarchar(15)非空4地址addressText4) 使用者USER_TABLENO.字段名代碼類型約束1用戶名DUSER_NAMEVARCHAR(30

7、)主鍵2密碼USER_PWDVarchar(20)非空3用戶級(jí)別USER_LEVELVarchar(15)5) 入庫(kù)LIST_INFONO.字段名代碼類型約束0入庫(kù)編號(hào)LIST_CODEvarchar(5)主鍵1時(shí)間LIST_DATEdatetime2物品數(shù)量PRO_NUMint3物品單位UNITvarchar(4)4物品號(hào)PRO_CODESmallint 非空5庫(kù)位代碼PLACE_CODEVarchar(20)非空6操作員號(hào)OPERATOR_IDVarchar(20)非空6) 操作員OPERATOR_INFONO.字段名代碼類型約束0操作員號(hào)OPERATOR_IDVARCHAR(5)主鍵1姓

8、名DNAMEChar(3)非空2性別SEXChar(3)非空3等級(jí)LEVEL1Char(7)4電話PHONESmallint 非空5地點(diǎn)ADDRESSVarchar(20)非空7) 庫(kù)位表PLACE_INFONO.字段名代碼類型約束0庫(kù)位號(hào)PLACE_CODEsmallint主鍵1區(qū)域名AREA_NAMEChar(3)2物品編號(hào)PRO_CODEChar(7)主鍵，3貨架號(hào)SHELF_NUMSmallint 非空4層號(hào)FLOOR_NUMVarchar(20)非空5列號(hào)LINE_NUMVarchar(20)非空6高度HEIGHTVarchar(20)非空7長(zhǎng)度LENGTHTimestamp非空8寬

9、度WIDTH5.系統(tǒng)流圖定貨報(bào)告入庫(kù)信息處理出庫(kù)信息處理購(gòu)貨商需求表采購(gòu)貨物入庫(kù)倉(cāng)庫(kù)管理系統(tǒng)存儲(chǔ)文件入庫(kù)信息出庫(kù)信息表庫(kù)存報(bào)表供貨商出入庫(kù)庫(kù)存報(bào)表6.設(shè)計(jì)存儲(chǔ)過程庫(kù)存盤點(diǎn)存儲(chǔ)過程設(shè)計(jì)思路：1、 判斷是否期初盤點(diǎn)，期初盤點(diǎn)則根據(jù)已有的入倉(cāng)數(shù)據(jù)和出倉(cāng)數(shù)據(jù)，計(jì)算現(xiàn)庫(kù)存數(shù)量，如果沒有入倉(cāng)數(shù)據(jù)和出倉(cāng)數(shù)據(jù)，則數(shù)量計(jì)為0，把結(jié)果數(shù)據(jù)插入盤點(diǎn)信息表，返回成功標(biāo)志，程序結(jié)束；2、 非期初盤點(diǎn)，則根據(jù)當(dāng)前時(shí)間點(diǎn)找到最近一期的盤點(diǎn)數(shù)據(jù)，再根據(jù)從該期盤點(diǎn)時(shí)間到當(dāng)前時(shí)間點(diǎn)所有的入倉(cāng)數(shù)據(jù)和出倉(cāng)數(shù)據(jù)，計(jì)算現(xiàn)庫(kù)存數(shù)量，把結(jié)果數(shù)據(jù)插入盤點(diǎn)信息表，返回成功標(biāo)志，程序結(jié)束；3、 考慮到貨品信息表會(huì)不定時(shí)更新，盤點(diǎn)表也要以最新的貨品

10、信息表進(jìn)行盤點(diǎn)。實(shí)時(shí)庫(kù)存情況存儲(chǔ)過程實(shí)時(shí)庫(kù)存情況存儲(chǔ)過程與庫(kù)存盤點(diǎn)存儲(chǔ)過程設(shè)計(jì)思路類似，但計(jì)算結(jié)果不需寫到數(shù)據(jù)表，直接以數(shù)據(jù)集形式返回。設(shè)計(jì)思路：1、 判斷是否已存在盤點(diǎn)數(shù)據(jù)，如否則根據(jù)已有的入倉(cāng)數(shù)據(jù)和出倉(cāng)數(shù)據(jù)，計(jì)算現(xiàn)庫(kù)存數(shù)量，如果沒有入倉(cāng)數(shù)據(jù)和出倉(cāng)數(shù)據(jù)，則數(shù)量計(jì)為0，返回結(jié)果數(shù)據(jù)集，程序結(jié)束；2、 如已存在盤點(diǎn)數(shù)據(jù)，則根據(jù)當(dāng)前時(shí)間點(diǎn)找到最近一期的盤點(diǎn)數(shù)據(jù)，再根據(jù)從該期盤點(diǎn)時(shí)間到當(dāng)前時(shí)間點(diǎn)所有的入倉(cāng)數(shù)據(jù)和出倉(cāng)數(shù)據(jù)，計(jì)算現(xiàn)庫(kù)存數(shù)量，返回結(jié)果數(shù)據(jù)集，程序結(jié)束；3、 考慮到貨品信息表會(huì)不定時(shí)更新，盤點(diǎn)表也要以最新的貨品信息表進(jìn)行盤點(diǎn)。查詢庫(kù)存異常情況存儲(chǔ)過程查詢庫(kù)存異常情況存儲(chǔ)過程是以設(shè)置庫(kù)存上下限

11、數(shù)據(jù)，對(duì)實(shí)時(shí)庫(kù)存情況進(jìn)行計(jì)算，返回低于庫(kù)存下限或高于上限的貨品數(shù)據(jù)設(shè)計(jì)思路：1、 根據(jù)庫(kù)存上下限的數(shù)據(jù)，限定計(jì)算實(shí)時(shí)庫(kù)存的貨品范圍；2、 判斷是否已存在盤點(diǎn)數(shù)據(jù)，如否則根據(jù)已有的入倉(cāng)數(shù)據(jù)和出倉(cāng)數(shù)據(jù)，計(jì)算現(xiàn)庫(kù)存數(shù)量，返回低于庫(kù)存下限或高于上限的貨品數(shù)據(jù)集，程序結(jié)束；3、 如已存在盤點(diǎn)數(shù)據(jù)，則根據(jù)當(dāng)前時(shí)間點(diǎn)找到最近一期的盤點(diǎn)數(shù)據(jù)，再根據(jù)從該期盤點(diǎn)時(shí)間到當(dāng)前時(shí)間點(diǎn)所有的入倉(cāng)數(shù)據(jù)和出倉(cāng)數(shù)據(jù)，計(jì)算現(xiàn)庫(kù)存數(shù)量，返回低于庫(kù)存下限或高于上限的貨品數(shù)據(jù)集，程序結(jié)束。7.Delphi前臺(tái)演示1、登錄2主界面3供應(yīng)商信息管理4操作員信息管理5物品信息管理、6庫(kù)位信息管理7入庫(kù)信息管理8.出庫(kù)信息管理9物資借出管理1

12、0物資歸還管理11.庫(kù)存信息初始化12庫(kù)位信息查詢13庫(kù)盤點(diǎn)操作管理14.某時(shí)間段庫(kù)存信息查詢15.按時(shí)間盤點(diǎn)庫(kù)存信息操作16入庫(kù)信息查詢17.出庫(kù)信息查詢18當(dāng)前庫(kù)存信息查詢19.用戶賬號(hào)管理20.賬號(hào)密碼修改8.數(shù)據(jù)庫(kù)的實(shí)施和維護(hù)-創(chuàng)建數(shù)據(jù)庫(kù)名為Material_DB的數(shù)據(jù)庫(kù)-CREATE DATABASE Material_DBON PRIMARY(NAME = 'Material_DB1',FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQLDATAMaterial_DB1.mdf',SIZE = 10

13、MB,MAXSIZE = 500MB,FILEGROWTH = 20MB),(NAME = 'Material_DB2',FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQLDATAMaterial_DB2.mdf',SIZE = 1MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)LOG ON(NAME = 'Material_DB_Log',FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQLDATAMate

14、rial_DB_Log.ldf',SIZE = 10MB,MAXSIZE = 100MB,FILEGROWTH = 10%);GO-創(chuàng)建各實(shí)體與聯(lián)系的關(guān)系表-供應(yīng)商管理SQL語(yǔ)句create procedure FindAllSupplier asselect supplier_code 供應(yīng)商代碼,supplier_name 供應(yīng)商名稱,link_man 聯(lián)系人,link_phone 聯(lián)系電話,supplier_address 供應(yīng)商地址,remark 備注from supplier_infocreate proc InsertSupplier SUPPLIER_CODEED var

15、char(5),SUPPLIER_NAMEED varchar(25),LINK_MANED varchar(8),LINK_PHONEED varchar(15),SUPPLIER_ADDRESSED varchar(50),REMARKED varchar(255)asinsert into supplier_info(supplier_code,supplier_name,link_man,link_phone,supplier_address,remark) values(supplier_codeed,supplier_nameed,link_maned,link_phoneed,s

16、upplier_addressed,remarked)create proc ModifySupplier SUPPLIER_CODEED varchar(5),SUPPLIER_NAMEED varchar(25),LINK_MANED varchar(8),LINK_PHONEED varchar(15),SUPPLIER_ADDRESSED varchar(50),REMARKED varchar(255)asupdate supplier_info set supplier_code=supplier_codeed,supplier_name=supplier_nameed,link_

17、man=link_maned,link_phone=link_phoneed,supplier_address=supplier_addressed,remark=remarkedwhere supplier_code=supplier_codeedcreate proc DeleteSupplier Supplier_codeed varchar(5)asdelete from supplier_info where supplier_code=supplier_codeed-物品管理SQL語(yǔ)句create procedure FindAllProasselect pro_code 物品編碼

18、,pro_name 物品名稱,unit 計(jì)量單位,pro_type 物品類型,pro_size 物品規(guī)格,pro_price 單位價(jià)格,supplier_code 供應(yīng)商代碼,order_num 訂貨批量,order_time 訂貨提前期from pro_infocreate proc InsertPro pro_codeed varchar(5),pro_nameed varchar(15),pro_typeed varchar(5),supplier_codeed varchar(5),united varchar(4),pro_sizeed varchar(15),order_numed

19、 int,pro_priceed int,order_timeed intasinsert into pro_info(pro_code,pro_name,pro_type,supplier_code,unit,pro_size,order_num,pro_price,order_time) values(pro_codeed,pro_nameed,pro_typeed,supplier_codeed,united,pro_sizeed,order_numed,pro_priceed,order_timeed)create proc ModifyPro pro_codeed varchar(5

20、),pro_nameed varchar(15),pro_typeed varchar(5),supplier_codeed varchar(5),united varchar(4),pro_sizeed varchar(15),order_numed int,pro_priceed int,order_timeed intasupdate pro_info set pro_code=pro_codeed,pro_name=pro_nameed,pro_type=pro_typeed,supplier_code=supplier_codeed,unit=united,pro_size=pro_

21、sizeed,order_num=order_numed,pro_price=pro_priceed,order_time=order_timeedwhere pro_code=pro_codeedcreate proc DeletePro pro_codeed varchar(5)asdelete from pro_info where pro_code=pro_codeed-庫(kù)位管理SQL語(yǔ)句create proc InsertPlace PLACE_CODEED VARCHAR(5),AREA_NAMEED VARCHAR(10),PRO_CODEED VARCHAR(5),SHELF_

22、NUMED INT,FLOOR_NUMED INT,LINE_NUMED INT,HEIGHTED INT,LENGTHED INT,WIDTHED INTasinsert into place_info(place_code,area_name,pro_code,shelf_num,floor_num,line_num,height,length,width) values(PLACE_CODEED,AREA_NAMEED,PRO_CODEED,SHELF_NUMED,FLOOR_NUMED,LINE_NUMED,HEIGHTED,LENGTHED,WIDTHED)create proc M

23、odifyPlace PLACE_CODEED VARCHAR(5),AREA_NAMEED VARCHAR(10),PRO_CODEED VARCHAR(5),SHELF_NUMED INT,FLOOR_NUMED INT,LINE_NUMED INT,HEIGHTED INT,LENGTHED INT,WIDTHED INTasupdate place_info set place_code=place_codeed,area_name=area_nameed,pro_code=pro_codeed,shelf_num=shelf_numed,floor_num=floor_numed,l

24、ine_num=line_numed,height=heighted,length=lengthed,width=widthedwhere place_code=place_codeedcreate proc DeletePlace place_codeed varchar(5)asdelete from place_info where place_code=place_codeedcreate procedure FindAllPlaceasselect place_code 庫(kù)位號(hào),area_name 區(qū)域名,pro_code 物品編號(hào),shelf_num 貨架號(hào)碼,floor_num

25、層號(hào),line_num 列號(hào),height 貨格高度,length 貨格長(zhǎng)度,width 貨格寬度f(wàn)rom place_info-操作員信息管理SQL語(yǔ)句create proc InsertOperator OPERATOR_IDED VARCHAR(5),NAMEED VARCHAR(8),SEXED VARCHAR(2),LEVEL1ED VARCHAR(10),PHONEED VARCHAR(15),ADDRESSED VARCHAR(30)asinsert into operator_info(OPERATOR_ID,DNAME,SEX,LEVEL1,PHONE,ADDRESS) va

26、lues(OPERATOR_IDED,NAMEED,SEXED,LEVEL1ED,PHONEED,ADDRESSED)create proc ModifyOperator OPERATOR_IDED VARCHAR(5),NAMEED VARCHAR(8),SEXED VARCHAR(2),LEVEL1ED VARCHAR(10),PHONEED VARCHAR(15),ADDRESSED VARCHAR(30)asupdate operator_info set OPERATOR_ID=OPERATOR_IDED,DNAME=NAMEED,SEX=SEXED,LEVEL1=LEVEL1ED,

27、PHONE=PHONEED,ADDRESS=ADDRESSEDwhere operator_id=OPERATOR_IDEDcreate proc DeleteOperator operator_ided varchar(5)asdelete from operator_info where operator_id=operator_idedcreate procedure FindAllOperatorasselect OPERATOR_ID 操作員ID,DNAME 姓名,LEVEL1 級(jí)別,SEX 性別,PHONE 聯(lián)系電話,ADDRESS 住址from operator_info-入庫(kù)信

28、息管理SQL語(yǔ)句create procedure FindAllList asselect LIST_CODE 入庫(kù)單編號(hào),OPERATOR_ID 操作員代號(hào),LIST_DATE 入庫(kù)時(shí)間,PRO_CODE 入庫(kù)物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from list_infocreate procedure FindAllList_1 asselect LIST_CODE 入庫(kù)單編號(hào),OPERATOR_ID 操作員代號(hào),LIST_DATE 入庫(kù)時(shí)間,PRO_CODE 入庫(kù)物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_COD

29、E 庫(kù)位代號(hào)from list_infowhere list_code like '1%'create proc InsertList LIST_CODEED varchar(5),LIST_DATEED datetime,PRO_NUMED int,UNITED varchar(4),PRO_CODEED varchar(5),PLACE_CODEED VARCHAR(5),OPERATOR_IDED VARCHAR(5)asinsert into list_info(LIST_CODE,LIST_DATE,PRO_NUM,UNIT,PRO_CODE,PLACE_CODE,O

30、PERATOR_ID) values(LIST_CODEED,LIST_DATEED,PRO_NUMED,UNITED,PRO_CODEED,PLACE_CODEED,OPERATOR_IDED)create proc ModifyList LIST_CODEED varchar(5),LIST_DATEED datetime,PRO_NUMED int,UNITED varchar(4),PRO_CODEED varchar(5),PLACE_CODEED VARCHAR(5),OPERATOR_IDED VARCHAR(5)asupdate list_info set LIST_CODE=

31、list_codeed,LIST_DATE=list_dateed,PRO_NUM=pro_numed,UNIT=united,PRO_CODE=pro_codeed,PLACE_CODE=place_codeed,OPERATOR_ID=operator_idedwhere list_code=list_codeedcreate proc DeleteList List_codeed varchar(5)asdelete from list_info where list_code=list_codeed-出庫(kù)信息管理SQL語(yǔ)句create procedure FindAllList_0 a

32、sselect LIST_CODE 出庫(kù)單編號(hào),OPERATOR_ID 操作員代號(hào),LIST_DATE 出庫(kù)時(shí)間,PRO_CODE 出庫(kù)物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from list_infowhere list_code like '0%'create proc FindStore_Pro_Place PRO_CODEED VARCHAR(5),PLACE_CODEED VARCHAR(5)asselect * from store_info where pro_code=pro_codeed and place_co

33、de=place_codeed-借出信息管理SQL語(yǔ)句create procedure FindAllList_o asselect LIST_CODE 借出單編號(hào),OPERATOR_ID 操作員代號(hào),LIST_DATE 借出時(shí)間,PRO_CODE 借出物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from list_infowhere list_code like 'o%'-歸還信息管理SQL語(yǔ)句create procedure FindAllList_i asselect LIST_CODE 歸還單編號(hào),OPERATOR_ID 操作員

34、代號(hào),LIST_DATE 歸還時(shí)間,PRO_CODE 歸還物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from list_infowhere list_code like 'i%'-庫(kù)存信息管理SQL語(yǔ)句create procedure FindAllStore asselect PRO_CODE 入庫(kù)物品編碼,STORE_PRO_NUM 庫(kù)存數(shù)量,STORE_UP_NUM 庫(kù)存上限,STORE_DOWN_NUM 庫(kù)存下限,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from store_infocreate proc Inser

35、tStore PRO_CODEED VARCHAR(5),STORE_PRO_NUMED INT,STORE_UP_NUMED INT,STORE_DOWN_NUMED INT,UNITED VARCHAR(4),PLACE_CODEED VARCHAR(5)asinsert into store_info(PRO_CODE,STORE_PRO_NUM,STORE_UP_NUM,STORE_DOWN_NUM,UNIT,PLACE_CODE) values(PRO_CODEED,STORE_PRO_NUMED,STORE_UP_NUMED,STORE_DOWN_NUMED,UNITED,PLAC

36、E_CODEED)create proc ModifyStore PRO_CODEED VARCHAR(5),STORE_PRO_NUMED INT,STORE_UP_NUMED INT,STORE_DOWN_NUMED INT,UNITED VARCHAR(4),PLACE_CODEED VARCHAR(5)asupdate store_info set PRO_CODE=PRO_CODEED,STORE_PRO_NUM=STORE_PRO_NUMED,STORE_UP_NUM=STORE_UP_NUMED,STORE_DOWN_NUM=STORE_DOWN_NUMED,UNIT=UNITE

37、D,PLACE_CODE=PLACE_CODEEDwhere pro_code=pro_codeed and place_code=place_codeedcreate proc InsertStore_List PRO_CODEED VARCHAR(5),STORE_PRO_NUMED INT,UNITED VARCHAR(4),PLACE_CODEED VARCHAR(5)asinsert into store_info(PRO_CODE,STORE_PRO_NUM,UNIT,PLACE_CODE) values(PRO_CODEED,STORE_PRO_NUMED,UNITED,PLAC

38、E_CODEED)create proc ModifyStore_List PRO_CODEED VARCHAR(5),STORE_PRO_NUMED INT,UNITED VARCHAR(4),PLACE_CODEED VARCHAR(5)asupdate store_info set PRO_CODE=PRO_CODEED,STORE_PRO_NUM=STORE_PRO_NUMED,UNIT=UNITED,PLACE_CODE=PLACE_CODEEDwhere pro_code=pro_codeed and place_code=place_codeedcreate proc Delet

39、eStore PRO_CODEED VARCHAR(5),PLACE_CODEED VARCHAR(5)asdelete from store_info where pro_code=pro_codeed and place_code=place_codeed-庫(kù)盤點(diǎn)作業(yè)管理SQL語(yǔ)句create proc ModifyStore_ProNum PRO_CODEED VARCHAR(5),PLACE_CODEED VARCHAR(5),STORE_PRO_NUMED INTasupdate store_info set STORE_PRO_NUM=STORE_PRO_NUMEDwhere pr

40、o_code=pro_codeed and place_code=place_codeedcreate proc FindStore_Pro PRO_CODEED VARCHAR(5)asselect * from store_info where pro_code=pro_codeedcreate proc FindAllStore_Operateas select PRO_CODE 入庫(kù)物品編碼,STORE_PRO_NUM 庫(kù)存數(shù)量,STORE_UP_NUM 庫(kù)存上限,STORE_DOWN_NUM 庫(kù)存下限,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào) from store_info

41、-入庫(kù)信息查詢create proc FindAllList_1_time LIST_DATEED1 datetime,LIST_DATEED2 datetimeasselect LIST_CODE 入庫(kù)單編號(hào),OPERATOR_ID 操作員代號(hào),LIST_DATE 入庫(kù)時(shí)間,PRO_CODE 入庫(kù)物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from list_infowhere list_code like '1%' and list_date between list_dateed1 and list_dateed2create p

42、roc FindAllList_1_Pro_code PRO_CODEED varchar(5)asselect LIST_CODE 入庫(kù)單編號(hào),OPERATOR_ID 操作員代號(hào),LIST_DATE 入庫(kù)時(shí)間,PRO_CODE 入庫(kù)物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from list_infowhere list_code like '1%' and pro_code=pro_codeedcreate proc FindAllList_1_Ope_id OPERATOR_IDED VARCHAR(5)asselect LIS

43、T_CODE 入庫(kù)單編號(hào),OPERATOR_ID 操作員代號(hào),LIST_DATE 入庫(kù)時(shí)間,PRO_CODE 入庫(kù)物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from list_infowhere list_code like '1%' and operator_id=operator_idedcreate proc FindList_1_ListCode LIST_CODEED VARCHAR(5)asselect LIST_CODE 入庫(kù)單編號(hào),OPERATOR_ID 操作員代號(hào),LIST_DATE 入庫(kù)時(shí)間,PRO_CODE 入庫(kù)

44、物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from list_infowhere list_code=list_codeed-出庫(kù)信息查詢create proc FindAllList_0_time LIST_DATEED1 datetime,LIST_DATEED2 datetimeasselect LIST_CODE 入庫(kù)單編號(hào),OPERATOR_ID 操作員代號(hào),LIST_DATE 入庫(kù)時(shí)間,PRO_CODE 入庫(kù)物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from list_infowhere lis

45、t_code like '0%' and list_date between list_dateed1 and list_dateed2create proc FindAllList_0_Pro_code PRO_CODEED varchar(5)asselect LIST_CODE 入庫(kù)單編號(hào),OPERATOR_ID 操作員代號(hào),LIST_DATE 入庫(kù)時(shí)間,PRO_CODE 入庫(kù)物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from list_infowhere list_code like '0%' and pro_

46、code=pro_codeedcreate proc FindAllList_0_Ope_id OPERATOR_IDED VARCHAR(5)asselect LIST_CODE 入庫(kù)單編號(hào),OPERATOR_ID 操作員代號(hào),LIST_DATE 入庫(kù)時(shí)間,PRO_CODE 入庫(kù)物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from list_infowhere list_code like '0%' and operator_id=operator_idedcreate proc FindList_0_ListCode LIST_CO

47、DEED VARCHAR(5)asselect LIST_CODE 出庫(kù)單編號(hào),OPERATOR_ID 操作員代號(hào),LIST_DATE 出庫(kù)時(shí)間,PRO_CODE 出庫(kù)物品編碼,PRO_NUM 物品數(shù)量,UNIT 物品單位,PLACE_CODE 庫(kù)位代號(hào)from list_infowhere list_code=list_codeed-用戶信息表管理SQL語(yǔ)句create proc InsertUser DUSER_NAMEED VARCHAR(30),USER_PWDED VARCHAR(30),USER_LEVELED VARCHAR(10)asinsert into user_table

48、(DUSER_NAME,USER_PWD,USER_LEVEL) values(DUSER_NAMEED,USER_PWDED,USER_LEVELED)create proc UpdateUser DUSER_NAMEED VARCHAR(30),USER_PWDED VARCHAR(30),USER_LEVELED VARCHAR(10)asupdate user_table set DUSER_NAME=DUSER_NAMEED,USER_PWD=USER_PWDED,USER_LEVEL=USER_LEVELEDwhere DUSER_NAME=DUSER_NAMEEDcreate p

49、roc DeleteUser DUSER_NAMEED VARCHAR(30)asdelete from user_table where DUSER_NAME=DUSER_NAMEEDcreate proc FindAllUserasselect DUSER_NAME 用戶名,USER_PWD 用戶密碼,USER_LEVEL 用戶級(jí)別from user_tablecreate proc UpdateUser_Psw DUSER_NAMEED VARCHAR(30),USER_PWDED VARCHAR(30)asupdate user_table set DUSER_NAME=DUSER_N

50、AMEED,USER_PWD=USER_PWDEDwhere DUSER_NAME=DUSER_NAMEED-輸入各表數(shù)據(jù)-輸入供應(yīng)商信息insert into supplier_info(supplier_code,supplier_name,link_man,link_phone,supplier_address,remark) values('sup00','冠捷電子','劉熔林莆田','小念')insert into supplier_info(supplier_code,supplier_name,link_man,li

51、nk_phone,supplier_address,remark) values('sup01','三明石化','育才三明','奔跑的春風(fēng)')insert into supplier_info(supplier_code,supplier_name,link_man,link_phone,supplier_address,remark) values('sup02','才子','永樂莆田','雨下的林')insert into supplier_info(suppli

52、er_code,supplier_name,link_man,link_phone,supplier_address,remark) values('sup04','思科','良海泉州','V.I.C')-輸入物品信息insert into pro_info(pro_code,pro_name,supplier_code,unit) values('pro00','軍事百科全書','sup00','本')insert into pro_info(pro_code,pr

53、o_name,supplier_code,unit) values('pro01','地理百科全書','sup01','本')insert into pro_info(pro_code,pro_name,supplier_code,unit) values('pro02','中國(guó)百科全書','sup02','本')insert into pro_info(pro_code,pro_name,supplier_code,unit) values('pro03&#

54、39;,'國(guó)家地理','sup03','本')insert into pro_info(pro_code,pro_name,supplier_code,unit) values('pro04','圣經(jīng)','sup04','本')-輸入庫(kù)位信息insert into place_info(place_code,area_name,pro_code) values('pla00','104','pro00')insert into plac

55、e_info(place_code,area_name,pro_code) values('pla01','104','pro00')insert into place_info(place_code,area_name,pro_code) values('pla02','104','pro01')insert into place_info(place_code,area_name,pro_code) values('pla03','104','pro02&

56、#39;)insert into place_info(place_code,area_name,pro_code) values('pla04','104','pro03')-輸入員工信息insert into operator_info(OPERATOR_ID,DNAME,SEX,LEVEL1,PHONE,ADDRESS) values('ope00','劉熔林','男','倉(cāng)管員insert into operator_info(OPERATOR_ID,DNAME,SEX,LEVEL1

57、,PHONE,ADDRESS) values('ope01','朱江','男','經(jīng)理insert into operator_info(OPERATOR_ID,DNAME,SEX,LEVEL1,PHONE,ADDRESS) values('ope02','育才','男','倉(cāng)管主管insert into operator_info(OPERATOR_ID,DNAME,SEX,LEVEL1,PHONE,ADDRESS) values('ope03','永樂','男','倉(cāng)管員insert into operator_info(OPERATOR_ID,DNAME,SEX,LEVEL1,PHONE,ADDRESS) v