出入庫管理系統(tǒng)設(shè)計(jì)文檔

成功是失敗之母，一切都是努力的結(jié)果出入庫管理系統(tǒng)設(shè)計(jì)文檔軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)082軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)1、設(shè)計(jì)和實(shí)現(xiàn)庫存管理系統(tǒng)2、撰寫設(shè)計(jì)文檔按照管理信息系統(tǒng)的原理和方法，采用成熟、先進(jìn)的信息技術(shù)和手段，支持倉庫進(jìn)出庫管理工作的全過程，保證倉庫貨品的有序正常的流通。1、概念結(jié)構(gòu)設(shè)計(jì)，繪出庫存管理的E-R圖。2、邏輯結(jié)構(gòu)設(shè)計(jì)，根據(jù)關(guān)系數(shù)據(jù)庫設(shè)計(jì)原理，設(shè)計(jì)出庫存管理的關(guān)系模型。3、物理結(jié)構(gòu)設(shè)計(jì)，給出庫存管理數(shù)據(jù)庫在SQLServer2005中的定義。4、數(shù)據(jù)庫實(shí)現(xiàn)，在SQLServer2005中建立庫存管理數(shù)據(jù)庫。1：出入庫信息操作、查詢庫存信息、產(chǎn)品分類管理、添加刪除人員、查看系統(tǒng)事件。：查詢庫內(nèi)信息、修改本用戶密碼。21、進(jìn)、出庫管理。對(duì)進(jìn)、出庫信息進(jìn)行記錄。2、查詢功能。倉庫管理對(duì)查詢要求高，通過主菜單記錄當(dāng)前操作用戶的用戶編號(hào)，保證了對(duì)進(jìn)、出庫信息錄入負(fù)責(zé)人的確認(rèn)。1頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果3、庫存管理，根據(jù)外界對(duì)庫存的要求，企業(yè)訂購(gòu)的特點(diǎn)，預(yù)測(cè)，計(jì)劃和執(zhí)行一種補(bǔ)充庫存的行為，并對(duì)這種行為進(jìn)行控制，重點(diǎn)在于確定如何訂貨，訂購(gòu)多少，何時(shí)定貨。4、部門資料管理與庫存報(bào)表生成軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)5、界面簡(jiǎn)單、易于操作根據(jù)系統(tǒng)的業(yè)務(wù)流程和用戶的需求分析可以得出，本系統(tǒng)應(yīng)該包含產(chǎn)品基本信息管理、入出庫管理、庫存管理、查詢統(tǒng)計(jì)、系統(tǒng)日志管理幾大功能，1）基本信息管理：貨品信息、供應(yīng)商信息、客戶信息、員工信息，分別實(shí)現(xiàn)相應(yīng)信息添加、刪除、查找、修改2）入庫信息管理：入貨信息錄入3）出庫信息管理：入貨信息錄入4）庫存信息管理：查詢庫存信息5）查詢統(tǒng)計(jì)：貨品查詢、供應(yīng)商查詢、客戶查詢、入庫查詢、出庫查詢、員工查詢按日期：查詢某個(gè)時(shí)間段的出入庫信息按人員：供應(yīng)商、客戶、員工信息查詢按物品：查詢某種商品的出入庫信息和余料打印報(bào)表6)系統(tǒng)日志信息管理：記錄相應(yīng)操作信息每個(gè)關(guān)系中都至少要有一個(gè)主鍵，關(guān)系與關(guān)系之上的約束條件，定義觸發(fā)器。當(dāng)外界非正常因素導(dǎo)致系統(tǒng)關(guān)閉時(shí)可自動(dòng)保存間通過主外鍵關(guān)聯(lián)。定義屬性2頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果系統(tǒng)操作日志是不允許改動(dòng)的對(duì)于非倉庫管理員是不允許進(jìn)行增、刪、改相應(yīng)的操作，只可查看相關(guān)信息記錄合格訂單總帳訂單查看訂單入庫打印信息明細(xì)帳不合格訂單（入庫）查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)（出庫）查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查（功能模塊圖）E-R：1軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)員工編號(hào)姓名性別職位3頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果員工信息固定電話手機(jī)通訊地址郵政編碼備注2)：查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查3)：類別編號(hào)類別名稱計(jì)量單位貨物類別4)：查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查5)：軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查7供應(yīng)商編供應(yīng)商名供應(yīng)商地聯(lián)系人聯(lián)系電話號(hào)稱址供應(yīng)商8客戶編號(hào)客戶名稱聯(lián)系人聯(lián)系電話客戶地址客戶4頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果9倉庫編號(hào)倉庫名稱聯(lián)系人id倉庫地址倉庫信息10現(xiàn)有存儲(chǔ)倉庫編號(hào)貨品編號(hào)最高限量最低限量量庫存信息v11)：軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查11查查查查查查查查查查N查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查M查查查查查查查查查N1MN查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查查1查查查查查查查查查查查查查查查查查查查查查查查11查查查查查查查查查查查查查查查查查查查查NN查查查查查查查查查查查查查查查查查查查查查查查查11查查查查查查查查查查查查查查用戶管理、員工信息、業(yè)務(wù)關(guān)系人員信息、銀行賬戶信息、倉庫信息、貨物基本信息、貨物5頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果類別、報(bào)損信息、調(diào)撥信息、貨物入庫信息、貨物出庫信息、庫存總賬信息、系統(tǒng)日志信息1.初始大增長(zhǎng)大文件文件名稱最大文件大小小小數(shù)據(jù)庫不受限制StockManagement_Data.mdf32日志不受限制StockManagement_Log.log112.軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)Staff序?qū)傩悦Q屬性描述數(shù)據(jù)類型空否默認(rèn)值備注號(hào)1Staff_id員工編號(hào)int否主鍵2Staff_name姓名varchar(50)否3Staff_sex性別bit4Staff_Security權(quán)限Int否5Staff_tel固定電話varchar(50)6Staff_mobile手機(jī)varchar(50)7Staff_address通訊地址varchar(50)8Staff_postalcode郵政編碼varchar(50)9Staff_remark備注Varchar(100)Goods序空默認(rèn)屬性名稱屬性描述數(shù)據(jù)類型備注號(hào)否值2Goods_id編號(hào)int否主鍵3Goods_name名稱varchar(50)否4Goods_type規(guī)格型號(hào)varchar(50)否Goods_warranty保修期int否Goods_sort_id貨品類別int否外鍵Goods_area產(chǎn)地varchar(50)否Goods_Entry_price入庫單價(jià)money否Goods_Sale_price銷售單價(jià)money否Goods_Supplier_id供應(yīng)商int否6頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果Goods_remark備注Varchar(100)GoodsSort序默認(rèn)屬性名稱屬性描述數(shù)據(jù)類型空否備注號(hào)值1GoodsSort_id類別編號(hào)int否主鍵2GoodsSort_name名稱varchar(50)否3GoodsSort_unit計(jì)量單位varchar(4)否Instock序空默認(rèn)屬性名稱屬性描述數(shù)據(jù)類型備注號(hào)否值自動(dòng)增長(zhǎng)1Instock_serial入庫序號(hào)int否（1，1）3Instock_Googs_id貨品編號(hào)int否主鍵軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)4Instock_amount數(shù)量int否5Instock_date入庫時(shí)間smalldatatime否經(jīng)手人編Instock_Staff_idint否外鍵號(hào)入貨單編Instock_In_idint否自動(dòng)生成號(hào)Instock_stock_id倉庫idInt否外鍵Instock_ExtraMoney額外費(fèi)用moneyInstock_remark備注Varchar(50)Outstock序空默認(rèn)屬性名稱屬性描述數(shù)據(jù)類型備注號(hào)否值自動(dòng)增長(zhǎng)1Outstock_serial出庫序號(hào)int否（1，1）3Outstock_Goods_id貨品編號(hào)int否主鍵4Outstock_amouny實(shí)發(fā)數(shù)量int否5Outstock_Sale_price實(shí)際售價(jià)money否Outstock_延保年限int否Extend_warranty經(jīng)手人編Outstock_Staff_idint否外鍵號(hào)出貨單編Outstock_Out_idint否自動(dòng)生成號(hào)7頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果Outstock_Stock_id倉庫編號(hào)int否外鍵Outstock_date出庫日期smalldatatime否Outstock_ExtraMoney額外費(fèi)用moneyOutstock_remark備注Varchar(100)Supplier序默認(rèn)屬性名稱屬性描述數(shù)據(jù)類型空否備注號(hào)值1Supplier_id供應(yīng)商編號(hào)int否主鍵2Supplier_top供應(yīng)商名稱int否3Supplier_name聯(lián)系人varchar(50)否4Supplier_tel聯(lián)系電話varchar(50)否Supplier_address供應(yīng)商地址varchar(50)否Customs軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)序默認(rèn)屬性名稱屬性描述數(shù)據(jù)類型空否備注號(hào)值1Customs_id客戶編號(hào)int否主鍵2Customs_top客戶名稱varchar(50)否3Customs_name聯(lián)系人varchar(50)否4Customs_tel聯(lián)系電話varchar(50)否Customs_adddress客戶地址varchar(50)否Stock序?qū)傩悦Q屬性描述數(shù)據(jù)類型空否默認(rèn)值備注號(hào)1Stock_id倉庫idint否主鍵2Stock_name倉庫名稱varchar(50)否3Stock_Staff_id員工編號(hào)int否外鍵4Stock_address倉庫地址varchar(50)否StockRemain序?qū)傩悦杩漳J(rèn)屬性名稱數(shù)據(jù)類型備注號(hào)述否值StockRemain_goods_id貨物idint否主鍵StockRemain_stock_id倉庫idint否外鍵8頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果最低限StockRemain_Min_amountint否量最高限StockRemain_Max_amountint否量現(xiàn)有庫StockRemain_amountint否存量SystemLog序?qū)傩悦杩漳J(rèn)屬性名稱數(shù)據(jù)類型備注號(hào)述否值主鍵操作序1SystemLog_Serialint否自動(dòng)增長(zhǎng)（1，號(hào)1）操作人2SystemLog_Staff_idint否外鍵編號(hào)操作內(nèi)3SystemLog_mattervarchar(100)否容軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)操作時(shí)4SystemLog_datesmalldatatime否間createdatabaseStockManagementonprimary(name=StockManagement_mdf,filename='D:\StockManagement_mdf.mdf',size=3MB,maxsize=unlimited,filegrowth=3%)logon(name=StockManagement_log,filename='D:\StockManagement_log.log',size=1MB,maxsize=unlimited,filegrowth=3%9頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果)gouseStockManagementgocreatetablestaff--員工基本信息(staff_idintnotnullprimarykey,staff_namevarchar(50)notnull,staff_passwordvarchar(50)notnull,staff_securityintnotnull,staff_sexbit,staff_telvarchar(50),staff_mobilevarchar(50),staff_addressvarchar(50),staff_remarkvarchar(100))useStockManagementgocreatetablegoods--貨物基本信息軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)(goods_idintnotnullprimarykey,goods_namevarchar(50)notnull,goods_typevarchar(50)notnull,goods_warrantyintnotnull,goods_sort_idintnotnull,goods_supplier_idintnotnull,goods_remarkvarchar(100))10頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果useStockManagementgocreatetablegoodsSort--貨物種類信息(goodsSort_idintnotnullprimarykey,goodsSort_namevarchar(50)notnull,goodsSort_unitvarchar(50)notnull,)useStockManagementgocreatetableinStock--入庫信息(inStock_serialintidentity(1,1)notnull,inStock_goods_idintnotnullprimarykey,inStock_amountintnotnull,inStock_in_pricemoneynotnull,inStock_datesmalldatetimenotnull,inStock_staff_idintnotnull,inStock_in_idintnotnull,inStock_stock_idintnotnull,inStock_extraMoneymoney,inStock_remarkvarchar(100))useStockManagementgocreatetableoutStock--出庫信息(outStock_serialintidentity(1,1)notnull,outStock_goods_idintnotnullprimarykey,outStock_amountintnotnull,11頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果outStock_sale_pricemoneynotnull,軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)outStock_extend_warrantyintnotnull,outStock_staff_idintnotnull,outStock_out_idintnotnull,outStock_stock_idintnotnull,outStock_datesmalldatetimenotnull,outStock_extraMoneymoney,outStock_remarkvarchar(100))gouseStockManagementgocreatetablesupplier--供應(yīng)商信息(supplier_idintnotnullprimarykey,supplier_topvarchar(50)notnull,supplier_namevarchar(50)notnull,supplier_telvarchar(50)notnull,supplier_addressvarchar(50)notnull)gouseStockManagementgocreatetablecustoms--客戶信息(customs_idintnotnullprimarykey,customs_topvarchar(50)notnull,customs_namevarchar(50)notnull,customs_telvarchar(50)notnull,customs_addressvarchar(50)notnull)gouseStockManagementgo12頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果createtablestock--倉庫信息(stock_idintnotnullprimarykey,stock_namevarchar(50)notnull,stock_staff_idintnotnull,stock_addressvarchar(50)notnull)useStockManagement軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)gocreatetablestockRemain--庫存信息(stockRemain_goods_idintnotnullprimarykey,stockRemain_min_amountintnotnull,stockRemain_max_amountintnotnull,stockRemain_amountintnotnull)gouseStockManagementgocreatetablesystemLog--系統(tǒng)日志信息(systemLog_serialintidentity(1,1)notnullprimarykey,systemLog_staff_idintnotnull,systemLog_mattervarchar(100)notnull,systemLog_datesmalldatetimenotnull)13頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果--查看日志信息useStockManagement軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)gocreateviewselectSysLogasselect*fromsystemLog*/*******************************************************************表名：staff******************************************************************/--------------------------------------用途：是否已經(jīng)存在該員工------------------------------------CREATEPROCEDUREstaff_Exists@staff_idintASDECLARE@TempIDintSELECT@TempID=count(1)FROMstaffWHEREstaff_id=@staff_id14頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果IF@TempID=0RETURN0ELSERETURN1GO--------------------------------------用途：增加一條員工記錄------------------------------------CREATEPROCEDUREstaff_ADD@staff_idint,@staff_namevarchar(50),@staff_passwordvarchar(50),@staff_securityint,@staff_sexbit,@staff_telvarchar(50),@staff_mobilevarchar(50),@staff_addressvarchar(50),@staff_remarkvarchar(100)ASINSERTINTOstaff(staff_id,staff_name,staff_password,staff_security,staff_sex,staff軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)_tel,staff_mobile,staff_address,staff_remark)VALUES(@staff_id,@staff_name,@staff_password,@staff_security,@staff_sex,@staff_tel,@staff_mobile,@staff_address,@staff_remark)GO15頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果--------------------------------------用途：修改一條員工記錄------------------------------------CREATEPROCEDUREstaff_Update@staff_idint,@staff_namevarchar(50),@staff_passwordvarchar(50),@staff_securityint,@staff_sexbit,@staff_telvarchar(50),@staff_mobilevarchar(50),@staff_addressvarchar(50),@staff_remarkvarchar(100)ASUPDATEstaffSETstaff_name=@staff_name,staff_password=@staff_password,staff_security=@staff_security,staff_sex=@staff_sex,staff_tel=@staff_tel,staff_mobile=@staff_mobile,staff_address=@staff_address,staff_remark=@staff_remarkWHEREstaff_id=@staff_idGO--------------------------------------用途：刪除一條員工記錄16頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果------------------------------------CREATEPROCEDUREstaff_Delete@staff_idintASDELETE[staff]WHEREstaff_id=@staff_idGO軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)--------------------------------------用途：根據(jù)員工ID得到員工詳細(xì)信息------------------------------------CREATEPROCEDUREstaff_GetModel@staff_idintASSELECTstaff_id,staff_name,staff_password,staff_security,staff_sex,staff_tel,staff_mobile,staff_address,staff_remarkFROMstaffWHEREstaff_id=@staff_idGO--------------------------------------用途：查詢所有記錄信息------------------------------------CREATEPROCEDUREstaff_GetList17頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果ASSELECTstaff_id,staff_name,staff_password,staff_security,staff_sex,staff_tel,staff_mobile,staff_address,staff_remarkFROMstaffGO/*******************************************************************表名：stock******************************************************************/--------------------------------------用途：是否已經(jīng)存在該倉庫記錄------------------------------------CREATEPROCEDUREstock_Exists@stock_idintASDECLARE@TempIDintSELECT@TempID=count(1)FROMstockWHEREstock_id=@stock_idIF@TempID=0RETURN0ELSERETURN1軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)GO--------------------------------------用途：增加一條倉庫記錄------------------------------------18頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果CREATEPROCEDUREstock_ADD@stock_idint,@stock_namevarchar(50),@stock_staff_idint,@stock_addressvarchar(50)ASINSERTINTOstock(stock_id,stock_name,stock_staff_id,stock_address)VALUES(@stock_id,@stock_name,@stock_staff_id,@stock_address)GO--------------------------------------用途：更新一條倉庫記錄------------------------------------CREATEPROCEDUREstock_Update@stock_idint,@stock_namevarchar(50),@stock_staff_idint,@stock_addressvarchar(50)ASUPDATEstockSETstock_name=@stock_name,stock_staff_id=@stock_staff_id,stock_address=@stock_addressWHEREstock_id=@stock_idGO------------------------------------19頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果--用途：刪除一條倉庫記錄------------------------------------CREATEPROCEDUREstock_Delete@stock_idintASDELETEstock軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)WHEREstock_id=@stock_idGO—進(jìn)出庫管理系統(tǒng)--------------------------------------用途：根據(jù)倉庫Id得到倉庫詳細(xì)信息------------------------------------CREATEPROCEDUREstock_GetModel@stock_idintASSELECTstock_id,stock_name,stock_staff_id,stock_addressFROMstockWHEREstock_id=@stock_idGO--------------------------------------用途：查詢所有倉庫記錄信息------------------------------------CREATEPROCEDUREstock_GetListASSELECT20頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果stock_id,stock_name,stock_staff_id,stock_addressFROMstockGO/*******************************************************************表名：stockRemain******************************************************************/--------------------------------------用途：根據(jù)貨物id得到庫存詳細(xì)信息------------------------------------CREATEPROCEDUREstockRemain_GetModel@stockRemain_goods_idintASSELECTstockRemain_goods_id,stockRemain_min_amount,stockRemain_max_amount,stockRemain_amount軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)FROMstockRemainWHEREstockRemain_goods_id=@stockRemain_goods_idGO--------------------------------------用途：查詢所有庫存記錄信息------------------------------------CREATEPROCEDUREstockRemain_GetListASSELECT21頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果stockRemain_goods_id,stockRemain_min_amount,stockRemain_max_amount,stockRemain_amountFROMstockRemainGO/*******************************************************************表名：supplier******************************************************************/--------------------------------------用途：是否已經(jīng)存在該供應(yīng)商------------------------------------CREATEPROCEDUREsupplier_Exists@supplier_idintASDECLARE@TempIDintSELECT@TempID=count(1)FROMsupplierWHEREsupplier_id=@supplier_idIF@TempID=0RETURN0ELSERETURN1GO--------------------------------------用途：增加一條供應(yīng)商記錄------------------------------------CREATEPROCEDUREsupplier_ADD@supplier_idint,22頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果@supplier_topvarchar(50),軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)@supplier_namevarchar(50),@supplier_telvarchar(50),@supplier_addressvarchar(50)ASINSERTINTOsupplier(supplier_id,supplier_top,supplier_name,supplier_tel,supplier_address)VALUES(@supplier_id,@supplier_top,@supplier_name,@supplier_tel,@supplier_address)GO--------------------------------------用途：更新一條供應(yīng)商記錄------------------------------------CREATEPROCEDUREsupplier_Update@supplier_idint,@supplier_topvarchar(50),@supplier_namevarchar(50),@supplier_telvarchar(50),@supplier_addressvarchar(50)ASUPDATEsupplierSETsupplier_top=@supplier_top,supplier_name=@supplier_name,supplier_tel=@supplier_tel,supplier_address=23頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果@supplier_addressWHEREsupplier_id=@supplier_idGO--------------------------------------用途：刪除一條供應(yīng)商記錄------------------------------------CREATEPROCEDUREsupplier_Delete@supplier_idintASDELETEsupplierWHEREsupplier_id=@supplier_id軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)GO--------------------------------------用途：根據(jù)供應(yīng)商ID得到供應(yīng)商詳細(xì)信息------------------------------------CREATEPROCEDUREsupplier_GetModel@supplier_idintASSELECTsupplier_id,supplier_top,supplier_name,supplier_tel,supplier_addressFROMsupplierWHEREsupplier_id=@supplier_id24頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果GO--------------------------------------用途：查詢所有供應(yīng)商記錄信息------------------------------------CREATEPROCEDUREsupplier_GetListASSELECTsupplier_id,supplier_top,supplier_name,supplier_tel,supplier_addressFROMsupplierGO/*******************************************************************表名：customs******************************************************************/--------------------------------------用途：是否已經(jīng)存在該客戶------------------------------------CREATEPROCEDUREcustoms_Exists@customs_idintASDECLARE@TempIDintSELECT@TempID=count(1)FROMcustomsWHEREcustoms_id=@customs_idIF@TempID=0RETURN0ELSE軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)25頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果RETURN1GO--------------------------------------用途：增加一條客戶記錄------------------------------------CREATEPROCEDUREcustoms_ADD@customs_idint,@customs_topvarchar(50),@customs_namevarchar(50),@customs_telvarchar(50),@customs_addressvarchar(50)ASINSERTINTOcustoms(customs_id,customs_top,customs_name,customs_tel,customs_address)VALUES(@customs_id,@customs_top,@customs_name,@customs_tel,@customs_address)GO--------------------------------------用途：修改一條客戶記錄------------------------------------CREATEPROCEDUREcustoms_Update@customs_idint,@customs_topvarchar(50),@customs_namevarchar(50),@customs_telvarchar(50),@customs_addressvarchar(50)26頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果ASUPDATEcustomsSETcustoms_top=@customs_top,customs_name=@customs_name,customs_tel=@customs_tel,customs_address=@customs_addressWHEREcustoms_id=@customs_idGO--------------------------------------用途：刪除一條客戶記錄軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)------------------------------------CREATEPROCEDUREcustoms_Delete@customs_idintASDELETEcustomsWHEREcustoms_id=@customs_idGO--------------------------------------用途：根據(jù)客戶ID得到客戶詳細(xì)信息------------------------------------CREATEPROCEDUREcustoms_GetModel@customs_idintASSELECTcustoms_id,customs_top,customs_name,customs_tel,customs_address27頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果FROMcustomsWHEREcustoms_id=@customs_idGO--------------------------------------用途：查詢所有客戶記錄信息------------------------------------CREATEPROCEDUREcustoms_GetListASSELECTcustoms_id,customs_top,customs_name,customs_tel,customs_addressFROMcustomsGO/*******************************************************************表名：goods******************************************************************/--------------------------------------用途：是否已經(jīng)存在該貨物------------------------------------CREATEPROCEDUREgoods_Exists@goods_idintAS軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)DECLARE@TempIDintSELECT@TempID=count(1)FROMgoodsWHEREgoods_id=@goods_idIF@TempID=0RETURN028頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果ELSERETURN1GO--------------------------------------用途：增加一條貨物記錄------------------------------------CREATEPROCEDUREgoods_ADD@goods_idint,@goods_namevarchar(50),@goods_typevarchar(50),@goods_warrantyint,@goods_sort_idint,@goods_supplier_idint,@goods_remarkvarchar(100)ASINSERTINTOgoods(goods_id,goods_name,goods_type,goods_warranty,goods_sort_id,goods_supplier_id,goods_remark)VALUES(@goods_id,@goods_name,@goods_type,@goods_warranty,@goods_sort_id,@goods_supplier_id,@goods_remark)GO--------------------------------------用途：修改一條貨物記錄------------------------------------CREATEPROCEDUREgoods_Update@goods_idint,29頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果@goods_namevarchar(50),@goods_typevarchar(50),@goods_warrantyint,@goods_sort_idint,@goods_supplier_idint,@goods_remarkvarchar(100)ASUPDATEgoods]SET軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)goods_name=@goods_name,goods_type=@goods_type,goods_warranty=@goods_warranty,goods_sort_id=@goods_sort_id,goods_supplier_id=@goods_supplier_id,goods_remark=@goods_remarkWHEREgoods_id=@goods_idGO--------------------------------------用途：刪除一條貨物記錄------------------------------------CREATEPROCEDUREgoods_Delete@goods_idintASDELETEgoodsWHEREgoods_id=@goods_idGO--------------------------------------用途：根據(jù)貨物Id得到貨物詳細(xì)信息------------------------------------CREATEPROCEDUREgoods_GetModel30頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果@goods_idintASSELECTgoods_id,goods_name,goods_type,goods_warranty,goods_sort_id,goods_supplier_id,goods_remarkFROMgoodsWHEREgoods_id=@goods_idGO--------------------------------------用途：查詢所有貨物記錄信息------------------------------------CREATEPROCEDUREgoods_GetListASSELECTgoods_id,goods_name,goods_type,goods_warranty,goods_sort_id,goods_supplier_id,goods_remarkFROMgoodsGO軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)/*******************************************************************表名：goodsSort******************************************************************/--------------------------------------用途：是否已經(jīng)存在該貨物種類------------------------------------31頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果CREATEPROCEDUREgoodsSort_Exists@goodsSort_idintASDECLARE@TempIDintSELECT@TempID=count(1)FROMgoodsSortWHEREgoodsSort_id=@goodsSort_idIF@TempID=0RETURN0ELSERETURN1GO--------------------------------------用途：增加一條貨物種類記錄------------------------------------CREATEPROCEDUREgoodsSort_ADD@goodsSort_idint,@goodsSort_namevarchar(50),@goodsSort_unitvarchar(50)ASINSERTINTOgoodsSort(goodsSort_id,goodsSort_name,goodsSort_unit)VALUES(@goodsSort_id,@goodsSort_name,@goodsSort_unit)GO------------------------------------32頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果--用途：修改一條貨物種類記錄------------------------------------CREATEPROCEDUREgoodsSort_Update@goodsSort_idint,軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)@goodsSort_namevarchar(50),@goodsSort_unitvarchar(50)ASUPDATEgoodsSortSETgoodsSort_name=@goodsSort_name,goodsSort_unit=@goodsSort_unitWHEREgoodsSort_id=@goodsSort_idGO--------------------------------------用途：刪除一條貨物種類記錄------------------------------------CREATEPROCEDUREgoodsSort_Delete@goodsSort_idintASDELETEgoodsSortWHEREgoodsSort_id=@goodsSort_idGO--------------------------------------用途：根據(jù)貨物種類Id得到種類詳細(xì)信息------------------------------------CREATEPROCEDUREgoodsSort_GetModel@goodsSort_idintAS33頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果SELECTgoodsSort_id,goodsSort_name,goodsSort_unitFROMgoodsSortWHEREgoodsSort_id=@goodsSort_idGO--------------------------------------用途：查詢種類記錄信息------------------------------------CREATEPROCEDUREgoodsSort_GetListASSELECTgoodsSort_id,goodsSort_name,goodsSort_unitFROMgoodsSortGO軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)/*******************************************************************表名：inStock******************************************************************/--------------------------------------用途：是否已經(jīng)存在進(jìn)庫記錄------------------------------------CREATEPROCEDUREinStock_Exists@inStock_goods_idint,@inStock_serialintAS34頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果DECLARE@TempIDintSELECT@TempID=count(1)FROMinStockWHEREinStock_goods_id=@inStock_goods_idandinStock_serial=@inStock_serialIF@TempID=0RETURN0ELSERETURN1GO--------------------------------------用途：增加一條進(jìn)庫記錄------------------------------------CREATEPROCEDUREinStock_ADD@inStock_serialintoutput,@inStock_goods_idint,@inStock_amountint,@inStock_in_pricemoney,@inStock_datesmalldatetime,@inStock_staff_idint,@inStock_in_idint,@inStock_stock_idint,@inStock_extraMoneymoney,@inStock_remarkvarchar(100)ASINSERTINTOinStock(inStock_goods_id,inStock_amount,inStock_in_price,inStock_date,inS35頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果tock_staff_id,inStock_in_id,inStock_stock_id,inStock_extraMoney,inStock_remark)VALUES(軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)@inStock_goods_id,@inStock_amount,@inStock_in_price,@inStock_date,@inStock_staff_id,@inStock_in_id,@inStock_stock_id,@inStock_extraMoney,@inStock_remark)SET@inStock_serial=@@IDENTITYGO--------------------------------------用途：修改一條進(jìn)庫記錄------------------------------------CREATEPROCEDUREinStock_Update@inStock_serialint,@inStock_goods_idint,@inStock_amountint,@inStock_in_pricemoney,@inStock_datesmalldatetime,@inStock_staff_idint,@inStock_in_idint,@inStock_stock_idint,@inStock_extraMoneymoney,36頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果@inStock_remarkvarchar(100)ASUPDATEinStockSETinStock_amount=@inStock_amount,inStock_in_price=@inStock_in_price,inStock_date=@inStock_date,inStock_staff_id=@inStock_staff_id,inStock_in_id=@inStock_in_id,inStock_stock_id=@inStock_stock_id,inStock_extraMoney=@inStock_extraMoney,inStock_remark=@inStock_remarkWHEREinStock_goods_id=@inStock_goods_idandinStock_serial=@inStock_serialGO--------------------------------------用途：刪除一條進(jìn)庫記錄------------------------------------CREATEPROCEDUREinStock_Delete@inStock_goods_idint,@inStock_serialintASDELETEinStockWHEREinStock_goods_id=@inStock_goods_idand軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)inStock_serial=@inStock_serialGO------------------------------------37頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果--用途：根據(jù)貨物id得到進(jìn)庫詳細(xì)信息------------------------------------CREATEPROCEDUREinStock_GetModel@inStock_goods_idint,@inStock_serialintASSELECTinStock_serial,inStock_goods_id,inStock_amount,inStock_in_price,inStock_date,inStock_staff_id,inStock_in_id,inStock_stock_id,inStock_extraMoney,inStock_remarkFROMinStockWHEREinStock_goods_id=@inStock_goods_idandinStock_serial=@inStock_serialGO--------------------------------------用途：查詢所有進(jìn)庫記錄信息------------------------------------CREATEPROCEDUREinStock_GetListASSELECTinStock_serial,inStock_goods_id,inStock_amount,inStock_in_price,inStock_date,inStock_staff_id,inStock_in_id,inStock_stock_id,inStock_extraMoney,inStock_remark38頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果FROMinStockGO/*******************************************************************表名：outStock******************************************************************/--------------------------------------用途：是否已經(jīng)存在該出庫記錄------------------------------------CREATEPROCEDUREoutStock_Exists@outStock_goods_idint,@outStock_serialint軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)ASDECLARE@TempIDintSELECT@TempID=count(1)FROMoutStockWHEREoutStock_goods_id=@outStock_goods_idandoutStock_serial=@outStock_serialIF@TempID=0RETURN0ELSERETURN1GO--------------------------------------用途：增加一條出庫記錄------------------------------------39頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果CREATEPROCEDUREoutStock_ADD@outStock_serialintoutput,@outStock_goods_idint,@outStock_amountint,@outStock_sale_pricemoney,@outStock_extend_warrantyint,@outStock_staff_idint,@outStock_out_idint,@outStock_stock_idint,@outStock_datesmalldatetime,@outStock_extraMoneymoney,@outStock_remarkvarchar(100)ASINSERTINTOoutStock(outStock_goods_id,outStock_amount,outStock_sale_price,outStock_extend_warranty,outStock_staff_id,outStock_out_id,outStock_stock_id,outStock_date,outStock_extraMoney,outStock_remark)VALUES(@outStock_goods_id,@outStock_amount,@outStock_sale_price,@outStock_extend_warranty,@outStock_staff_id,@outStock_out_id,@outStock_stock_id,@outStock_date,@outStock_extraMoney,@outStock_remark)40頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果SET@outStock_serial=@@IDENTITYGO--------------------------------------用途：修改一條出庫記錄軟件學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)—進(jìn)出庫管理系統(tǒng)------------------------------------CREATEPROCEDUREoutStock_Update@outStock_serialint,@outStock_goods_idint,@outStock_amountint,@outStock_sale_pricemoney,@outStock_extend_warrantyint,@outStock_staff_idint,@outStock_out_idint,@outStock_stock_idint,@outStock_datesmalldatetime,@outStock_extraMoneymoney,@outStock_remarkvarchar(100)ASUPDATEoutStockSEToutStock_amount=@outStock_amount,outStock_sale_price=@outStock_sale_price,outStock_extend_warranty=@outStock_extend_warranty,outStock_staff_id=41頁腳內(nèi)容成功是失敗之母，一切都是努力的結(jié)果@outStock_staff_id,outStock_out_id=@outStock_out_id,outStock_stock_id=@outStock_stock_id,outStock_date=@outStock_date,outStock_extraMoney=@outStock_extraMoney,outStock_remark=@outStock_remarkWHEREoutStock_goods_id=@outSto