服裝租賃管理系統涂星楊園剛文杰-資料下載頁

　　

【正文】 ot null, /*服裝編號*/check(clothNo like 39。[C][L][09][09][09][09][09] [09][09][09][09][09] [09]39。), quantity numeric(7,2) not null, /*數量*/ price numeric(7,2) not null, /*價格*/color char() not null /*顏色*/size char(2) not null /*大小*/ constraint orderDetailPK primary key clustered(orderNo,clothNo), constraint orderDetailFK1 foreign key(orderNo) references ordermaster(orderNo), constraint orderDetailFK2 foreign key(clothNo ) references cloth(clothNo ) )Gocreate table manager 管理員表( managerNo char(10) not null, /*管理員編號*/ check(managerNo like 39。[M][09][09][09][09][09][09][09][09][09]39。), code char(12) not null, /*密碼*/ managerName varchar(20) not null, /*管理員姓名*/ sex char(2) not null, /*性別*/ birthday datetime not null, /*出生年月*/ salary numeric(7,2) not null, /*薪水*/address varchar(100) not null, /*地址*/telephone char(11) not null, /*電話號碼*/ char(40) not null, /*郵箱*/ constraint managerPK primary key (managerNo))GoCREATE TABLE Evaluate 評價表( evaNo char(7) not null, /*評價編號*/ check(evaNo like 39。[E][09][09][09][09][09][09]39。), cusNo char(9) not null, /*會員編號*/ evaDate Datetime not null, /*評價日期*/ evaContent Varchar(280) not null, /*評價內容*/ managerNo char(10) not null, /*管理員編號*/replyDate Datetime not null, /*回復日期*/replyContent Varchar(280) not null, /*回復內容*/ constraint EvaluatePK primary key (evaNo))goCREATE TABLE Send 配送表( sendNo Char(8) not null, /*配送單號*/ check(sendNo like 39。[S][09][09][09][09][09][09][09]39。),OrderNo Char(15) not null, /*訂單編號*/sendDate Datetime not null, /*配送日期*/reciver Varchar(20) not null, /*收貨人*/sendAddress Varchar(40) not null, /*配送地址*/zipCode Char(6) not null, /*郵編*/sendTel Char(11) not null, /*電話*/sendState Char(6) not null, /*配送狀態*/constraint SendPK primary key (sendNo),constraint sendFK1 foreign key(orderNo) references orderMaster(orderNo))Go 數據庫完整性設計創建觸發器，如果服裝表中服裝改變時，訂單明細表也隨之更改。/*（楊園）*/create trigger trigger on orderDetail for update asif update(quantity) or update(clothNo)begin declare @orderNo char(12) declare cur_orderdetail scroll cursor for select orderNo,clothNo from deleted open cur_orderdetail begin tran fetch next from cur_orderdetail into @orderNo while(@@fetch_status=0) begin update order set shouldRecieve=*+* from inserted i,deleted d where = and = and =@orderNo fetch next from cur_orderdetail into @orderNo end mit tran close cur_orderdetail deallocate cur_orderdetailend 存儲過程設計創建存儲過程，主要是將數據庫前臺工作封裝到后臺處理，縮短系統的響應，提高系統服務能力。如無特殊說明，所有涉及到前臺服務請求的過程均封裝成后臺服務器的存儲過程，具體設計細節，參見數據庫設計腳本。（1）建立存儲過程，實現服裝租賃的租金總價，根據訂單明細表中的數據計算得出。/*（楊園）*/create procedure p_rentsumas begin declare @orderno char(15) declare cur_rentsum scroll cursor for select orderNo from orderdetail open cur_rentsum fetch next from cur_rentsum into @orderno while(@@fetch_status=0) begin update order set rentsum=* from orderdetail a where =@orderno fetch next from cur_rentsum into @orderno end close cur_rentsum deallocate cur_rentsum end（2）建立一個存儲過程，實現會員編號的自動生成。/*（涂星）*/create procedure cusNo(@sYear char(4),@scusNo char(8) output)as begin declare @cusNo char(8),@num int declare @sNum char(3) select @cusNo=max(cusNo) from customer where cusNo like 39。C39。+@sYear+39。%39。 set @num=isnull(convert(int,right(@cusNo,3)),0)+1 if @num10 set @sNum=39。0039。+convert(char(1),@num) else if @num100 set @sNum=39。039。+convert(char(2),@num) else set @sNum=convert(char(3),@num)set @scusNo=39。E39。+@sYear+@sNumendgodeclare @scusNo char(8)execute cusNo39。201239。,@scusNo outputselect @scusNo 員工編號（3）創建存儲過程，當會員查詢某件服裝時，統計與該服裝所屬的服裝類別中租賃次數排名前三的服裝，并向顧客推薦。/*（涂星）*/createprocedurechunchu(@classNochar(1))asbegindeclare@clothNochar(12),@sumintdeclare@textchar(50)declarecur_clothcursorforselecttop3clothNo,count(*)fromOrderDetaila,clothbwhere=and=@classNogroupbyorderbycount(*)opencur_clothNofetchcur_clothNointo@clothNo,@sumwhile(@@fetch_status=0)beginselect@text=@clothNoprint@clothNofetchcur_clothNointo@clothNo,@sumendclosecur_clothNodeallocatecur_clothNoend（4）創建存儲過程，按照服裝名稱查詢服裝信息。/*剛文杰*/create procedure cloth_name @name varchar(20)，asselect * from clothwhere cloth_fname+cloth_lname like 39。%@name%39。exec cloth_name 39。劉玲39。（5）設置帶參數的存儲過程，統計某服裝類別中服裝總數。實驗時統計M類服裝中服裝總數。/*剛文杰*/create procedure clothsum @classNo char(1)asbeginselect count(clothNo) 該服裝類別中服裝總數from cloth where classNo=@classNoendgoexecute clothsum @classNo=39。M39。