2022年数据库课程设计程序代码 .pdf

《2022年数据库课程设计程序代码 .pdf》由会员分享，可在线阅读，更多相关《2022年数据库课程设计程序代码 .pdf（4页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、create database hotelmanager/*创建数据库hotelmanager*/go use hotelmanager go create table cusinfo/*创建数据表cusinfo*/(dingname varchar(10)not null,cname varchar(10),cid char(18),vip char(2)go create table ordform/*创建数据表ordform*/(ono varchar(36)not null,cname varchar(10)not null,csex char(2),cid char(18),rid

2、char(6)not null,rstyle varchar(10),arrivetime datetime,cno char(10)not null,dingname varchar(10),livetime char(10),dingph char(15)not null,ozt char(4)go create table roominfo/*创建数据表roominfo*/(rid char(6)not null,rstyle varchar(10),kfzt char(2)not null)go create table liveorder/*创建数据表liveorder*/(lno

3、varchar(36)not null,rid char(6)not null,rstyle varchar(10),btime datetime not null,cno char(10),cname varchar(10)not null,etime datetime,ono varchar(36),lzt char(4)go create table bill/*创建数据表bill*/(bno varchar(36)not null,rid char(6)not null,cname varchar(10),名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 4 页 -cust

4、 money,btime datetime,etime datetime,remarks varchar(40)go create table roomsp/*创建数据表roomsp*/(rstyle varchar(10)not null,rprice money)go create table hordform/*创建数据表hordform*/(ono varchar(36)not null,cname varchar(10)not null,csex char(2),cid char(18),rid char(6)not null,rstyle varchar(10),arrivetim

5、e datetime,cno char(10)not null,dingname varchar(10),livetime char(10),dingph char(15)not null,ozt char(4)go create table hliveorder/*创建数据表hliveorder*/(lno varchar(36)not null,rid char(6)not null,rstyle varchar(10),btime datetime not null,cno char(10),cname varchar(10)not null,etime datetime,ono var

6、char(36),lzt char(4)go insert into cusinfo values(mao,zcl,330227198805060071,n)insert into cusinfo values(qxb,zyy,330227198603070022,n)go insert into roominfo values(2010,s,n)insert into roominfo values(2201,s,n)insert into roominfo values(2301,s,n)insert into roominfo values(3010,s,y)insert into ro

7、ominfo values(3012,s,n)insert into roominfo values(3015,s,n)insert into roominfo values(4210,d,n)名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 4 页 -insert into roominfo values(4110,d,n)insert into roominfo values(4010,d,n)insert into roominfo values(5220,d,n)insert into roominfo values(5210,d,n)insert into roominf

8、o values(5201,d,n)go insert into roomsp values(s,200)insert into roomsp values(d,300)go insert into ordform values(o0201001,zcl,f,330227198805060071,2301,s,2010-01-02,1,mao,1,883060089,n)go insert into liveorder values(l2010001,2301,s,2010-01-02,1,zcl,2010-01-03,o0201001,n)insert into liveorder valu

9、es(l2010002,3010,s,2010-01-03,1,qxb,2010-01-04,o0201002,y)insert into liveorder(lno,rid,rstyle,btime,cno,cname,ono,lzt)values(l2010003,5220,d,2010-01-09,1,myy,o0201003,y)insert into liveorder(lno,rid,rstyle,btime,cno,cname,ono,lzt)values(l2010004,5201,d,2010-01-02,2,zxx,null,y)go insert into bill(bn

10、o,rid,cname,cust,btime)values(b2010003,5220,myy,0,2010-01-10)insert into bill(bno,rid,cname,cust,btime)values(b2010004,5201,zxx,0,2010-01-02)go alter table cusinfo add primary key(dingname)alter table ordform add primary key(ono)alter table roominfo add primary key(rid)alter table liveorder add prim

11、ary key(lno)alter table bill add primary key(bno)alter table roomsp add primary key(rstyle)go create proc ordformproc lno varchar(36)/*保存入住单*/as begin tran update roominfo set kfzt=y from roominfo,liveorder where liveorder.rid=roominfo.rid and liveorder.lno=lno/*修改客房信息*/update ordform set ozt=y from

12、 ordform,liveorder where liveorder.ono=ordform.ono and liveorder.lno=lno/*修改预订单信息*/insert into hordform select*from ordform where ozt=y/*将已入住的预订单放入历史*/delete from ordform where ozt=y/*清除已入住预订单*/commit go select*from roominfo select*from ordform go exec ordformproc l2010001 名师资料总结-精品资料欢迎下载-名师精心整理-第 3

13、 页，共 4 页 -exec ordformproc l2010004 select*from roominfo select*from ordform go create proc liveorderproc lno varchar(36)/*收银退房*/as begin tran update liveorder set etime=getdate(),lzt=n where lno=lno update roominfo set kfzt=n from roominfo,liveorder where roominfo.rid=liveorder.rid and liveorder.ln

14、o=lno/*修改客房状态*/insert into hliveorder select*from liveorder where lno=lno/*导入历史*/delete from liveorder where lno=lno/*清除入住单*/commit go select*from liveorder select*from hliveorder go exec liveorderproc l2010004 go select*from liveorder select*from hliveorder select*from roominfo where rid=5201 go cr

15、eate proc billproc lno varchar(36)/*计算应收款*/as begin tran declare total money declare datecount int update liveorder set etime=getdate()where lno=lno/*设置离店时间*/select datecount=datediff(dd,btime,getdate()from liveorder where lno=lno/*计算住店天数*/select total=0 select total=datecount*(isnull(rprice,0)from

16、liveorder,bill,roomsp where bill.rid=liveorder.rid and liveorder.rstyle=roomsp.rstyle and lno=lno/*计算住店金额*/update bill set etime=getdate(),cust=total from bill,liveorder where bill.rid=liveorder.rid and lno=lno/*修改账单*/commit go select*from liveorder select*from bill go exec billproc l2010004 go select*from liveorder select*from bill 名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 4 页 -