【正文】 create procedure day_ine_search Mno char(4) as declare Ddate datetime, DIneSum smallint, Trent smallint, Efine smallint, Bdfine smallint, Lfine smallint, Vfee smallint, Mname varchar(20) /*從 Ine 表中選出當(dāng)天的系統(tǒng)管理員 */ select Mno=Mno from Ine 2021 級信管專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 36 where Mno=Mno /*從 Ine 表中選出當(dāng)天非會(huì)員的租金 */ select Trent=Trent from Ine where Mno=Mno /*從 Ine 表中選出當(dāng)天過期還書的罰款 */ select Efine=Efine from Ine where Mno=Mno /*從 Ine 表中選出當(dāng)天還書中因書被損壞所收的罰金 */ select Bdfine=Bdfine from Ine where Mno=Mno /*從 Ine 表中選出當(dāng)天因書丟失所收到的賠償金 */ select Lfine=Lfine from Ine where Mno=Mno /*從 Ine 表中選出當(dāng)天因?yàn)樾略鰰?huì)員所收的會(huì)員費(fèi) */ select Vfee=Vfee from Ine where Mno=Mno /*從 SystemManager 表中選出管理員姓名 */ select Mname=Mname from SystemManager where Mno=Mno set DIneSum=0 begin set DIneSum=Trent+Efine+Bdfine+Lfine+Vfee print39。+Vno if(Vno=null) begin insert into TBorrowBook values(Bsno,Tphno,Ldate,Tdeposit,Readline) end else if(Vno0) begin insert into VBorrowBook values(Bsno,Vno,Ldate,Readline) end end end else print39。+Bname+39。 end else if(Bnowcount0) begin print39。+Bname+39。 create procedure book_canborrow Bname varchar(30) as declare Bscount smallint, Bnowcount smallint, Bprice numeric(10,0), Bsno char(15), Vno char(10), Ldate datetime, Readline datetime, Tdeposit smallint, Tphno char(15) /*從 Book 表中選出書的總量 */ select Bscount=Bscount from Book where Bname=Bname /*從 Book 表中選出書的現(xiàn)存量 */ select Bnowcount=Bnowcount from Book where Bname=Bname /*從 Book 表中選出書的價(jià)格 */ select Bprice=Bprice from Book where Bname=Bname /*從 Book 表中選出書的店內(nèi)編號(hào) */ select Bsno=Bsno from Book where Bname=Bname /*非會(huì)員的押金 */ select Bprice=Bprice from Book where Bname=Bname set Tdeposit=Bprice* if(Bname in(select Bname from Book)) begin if(Bnowcount=0) 2021 級信管專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 35 begin print39。 create procedure P11_Vip_Delete Vno char(10) as delete from Vip where Vno=RTRIM(LTRIM(Vno))。 create procedure P3_SystemManager_Insert Mno char(4), Mname varchar(20), Mphno char(15) as insert into SystemManager values(Mno,Mname,Mphno) create procedure P4_Ine_Insert Mno char(4), Ddate datetime, Trent smallint, Efine smallint, Bdfine smallint, Lfine smallint, Vfee smallint, DIneSum smallint as insert into Ine values(Mno,Ddate,Trent,Efine,Bdfine,Lfine,Vfee,DIneSum) 2021 級信管專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 32 create procedure P5_VBorrowBook_Insert Bsno char(15), Vno char(10), Ldate datetime, Rdeadline datetime as insert into VBorrowBook values(Bsno,Vno,Ldate,Rdeadline) create procedure P6_VReturnBook_Insert Vno char(10), Bsno char(15), Rdeadline datetime, Rdate datetime, Wrexpired char(2), Bdfine smallmoney, Lfine smallmoney, Efine smallmoney as insert into VReturnBook values(Vno,Bsno,Rdeadline,Rdate,Wrexpired,Efine,Bdfine,Lfine) create procedure P7_TBorrowBook_Insert Bsno char(15), Tphno char(15), Ldate datetime, ReadLine datetime, Tdeposit smallint as insert into TBorrowBook values(Bsno,Tphno,Ldate,Readline,Tdeposit) create procedure P8_TReturnBook_Insert Bsno char(15), Tphno char(15), Readline datetime, Rdate datetime, Wrexpired char(2), 2021 級信管專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 33 Trent smallint, Efine smallint, Bdfine smallint, Lfine smallint as insert into TReturnBook values(Bsno,Tphno,Readline,Rdate,Wrexpired, Trent,Efine,Bdfine,Lfine) create procedure P9_Book_Delete Bname char(30) as delete from Book where Bname=RTRIM(LTRIM(Bname))。), VMdate date ) 2021 級信管專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 24 create table VBorrowBook( Bsno varchar(15)primary key, Vno varchar(10), Ldate date, Rdeadline date, foreign key(Vno)references Vip(Vno), foreign key(Bsno)references Book(Bsno)) create table TBorrowBook( Bsno varchar(15), Tphno varchar(15), Ldate date, ReadLine date, Tdeposit smallint, primary key (Bsno,Tphno), foreign key(Bsno)references Book(Bsno)) create table VReturnBook( Vno varchar(10), Bsno varchar(15), Rdeadline date, Rdate date, Wrexpired varchar(2), Bdfine number, Lfine number, Efine number, primary key(Bsno,Vno), foreign key(Vno)references Vip(Vno), foreign key(Bsno)references Book(Bsno)) create table TReturnBook( Bsno char(15), Tphno char(15), Readline date, Rdate date, 2021 級信管專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 25 Wrexpired char(2), Trent smallint, Efine smallint, Bdfine smallint, Lfine smallint, primary key(Bsno,Tphno), foreign key(Bsno)references Book(Bsno)) create table SystemManager( Mno char(4)primary key, Mname varchar(20), Mphno char(15)not null) create table Ine( Mno char(4) primary key, Ddate datet, Trent smallint, Efine smallint, Bdfine smallint, Lfine smallint, Vfee smallint, DIneSum smallint, foreign key(Mno)references SystemManager(Mno)) 數(shù)據(jù)表視圖的建立 （ SQL Server2021） 1. 查詢會(huì)員所借書的信息表 create view VBBinfo as select ,Vno,Bwriter,Bname FROM Book,VBorrowBook where = 2021 級信管專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 26 create view TBBinfo as select ,Tphno from Book,TBorrowBook where = 2021 級信管專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 27 create view InmanagerInfo as select Mname,Mphno,DIneSum,Ddate from SystemManager,Ine where = 2021 級信管專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 28 數(shù)據(jù)表視圖的建立（ Oracle） create view VBBinfo as select ,Vno,Bwriter,Bname FROM Book,VBorrowBook where = 2021 級信管專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 29 create view TBBinfo as select ,Tphno from Book,TBorrowBook where = 2021 級信