g0206财务管理系统数据库脚本设计

引 编写目 系统功 报表设 逻辑设 过 引顾客和订单的信息报表设 X年X月客房订单数量统计制表日期：X年XX月XXXX总XXXX合审核人：制表人X制表日期：X年XX月XX123456789XX

审核人：制表人--制表日期：X年XX月XX被品餐其总采购单采购单采购单采购单采购单采购单采购单.合逻辑设

审核人 制表人表3-1凭证字NOTNOTNOT表3-2凭证明细表字典NOTNOTNOTNOTNOTNOT表3-3入库单字典NOTNOTNOT订单DTOrder3-4NOTNOTNOTNOTNOTNOTNOT客户订购联系表DTOrderDetail3-5NOTNOT采购单DTPurchaseSheet3-6NOTNOTNOTNOTNOT采购明细DTPurchaseSheetDetail3-7NOTNOTNOTNOTNOT采购凭证表DTGreOfPurchaseSheet，如表示NOTNOT，如表NOTNOT入住缴费凭证DTPaymentOfCheckIn3-10NOTNOTNOTNOT入住DTCheckIn3-11NOTNOTNOTNOTNOTNOTNOTBEEmployeeNOT主键，格式NOTNOTNOT号NOTNOTNOTNOTNOTNOT房间BERoom，如3-13所示NOTNOTNOT会计BEAccountingSubject，如表3-14NOTNOT顾客BECustomer3-15所示号NOTNOTNOTNOTNOT商 modity，如表3-16所示NOTNOTNOTNOT会员BEMember3-17NOTNOTBENoMember，如表3-18NOT客房BDRoomleveNo，如表3-19所示NOTNOTNOT商品类 modityLeve，如表3-20所示NOTNOT用餐BDDinnerType，如表3-21NOTNOT1)会员等级表ODMemberleveForm，如表3-20所示NOTNOT数据库/**创建业务字典数据表 /**创建会员等级表------------------ /**VIP等级与折扣关系0-1,1-0.95,2-0.9,3-0.85 CREATETABLEDTMemberlevel( NOTNULLcheck(memLevelNobetween'0'and'3'), number(5,2) default1NOTNULL, number(1)default0check(IfVoidbetween0and1) notnull, constraintDTMemberLevelPKprimarykey(memLevelNo)/**创建用餐类型表--------------- CREATETABLEDTDinnerType( NOTNULLPRIMARYKEY,-- notnull, number(9,2)NOTNULL /**创建客房类型表--------------- CREATETABLEDTRoomType( NOT NOTroomTypePricenumber(9,2)NOTNULL, constraintDTRoomTypePKprimarykey(roomTypeNo)/**创建商品类别表--------------- CREATETABLE ( NOT PRIMARY 号 NOT /**创建基础数据表Basic /**创建员工表--------------- CREATETABLEBEEmployee( NOT -NOT-号NOT NOT number(9,NOT /**创建顾客表---------------- CREATETABLEBECustomer( NOT NOT NOTCHECK(sexIN('男女NOTcustomerNo NOT /**创建会员表---------------- CREATETABLEBEMember( NOT BEMemberPKprimarykey(customerNo), BEMemberFK1foreignkey(customerNo)referencesBECustomer(customerNo), /**创建非会员表---------------- NOT /**创建房间表---------------- CREATETABLEBERoom(NOT NOT NOTNULL, /**创建会计科目表---------------- CREATETABLEBEAccountingSubject( NOT NOT NOT /**创建商品表---------------- CREATETABLE ( NOT NOTNULL, number(7,2) NOTNULL, NOTNULL /**创建业务数据表Business /**创建凭证主表---------------- CREATETABLEBDVoucherMaster( NOT PRIMARYKEY,-- NOT /**创建凭证明细表---------------- CREATETABLEBDVoucherDetail(NOTNOT NOT NOT NOTNULL, NOT NOT /**创建入库表---------------- CREATETABLEBDGodownEntry(NOTNOT NOT /**创建入库凭证表---------------- CREATETABLEBDCreOfGodownEntry(NOTNOT /**创建订单主表---------------- CREATETABLEBDOrderMaster( NOT NOT NOT NOT NOT NOT/**创建订单明细表---------------- CREATETABLEBDOrderDetail(NOTNOTNOTNOT/**创建采购单主表---------------- CREATETABLEBDPurchaseMaster( NOT NOTNULL, NOTNULL, default0 NOTNULL, 和/**创建采购单明细表---------------- CREATETABLEBDPurchaseDetail(NOTNOTNOTnumber(7,default NOT /**创建采购凭证表---------------- CREATETABLEBDGreOfPurchaseSheet(NOTNOT /**创建入住表---------------- CREATETABLEBDCheckIn(NOTNOTNOTNOTNOT/**创建入住缴费凭证表---------------- CREATETABLEBDPaymentOfCheckIn(

NOTNULL,NOT

NOT 4.2数据/**数 /**数据字典数 /**会员等级表------------------ /**VIP等级与折扣关系0-1,1-0.95,2-0.9,3-0.85折 insertintoDTMemberlevelvalues('0',1,0);insertintoDTMemberlevelvalues('1',0.95,0);insertintoDTMemberlevelvalues('2',0.9,0);insertintoDTMemberlevelvalues('3',0.85,0);/**用餐类型表---------------2 insertintoDTDinnerTypevalues('D201400001','套餐A',60.00);insertintoDTDinnerTypevalues('D201400002','套餐B',80.00);insertintoDTDinnerTypevalues('D201400003','套餐C',100.00);/**客房类型表---------------3 insertintoDTRoomTypevalues('R201410001','单人间',99.00);insertintoDTRoomTypevalues('R201410002','双人间',199.00);/**商品类别表---------------4 insert insertinto insertinto modityTypevalues('2013102103','食品');/**基本表数 /**员工表--------------- insertintoBEEmployeevalues('EC20140104','Tom','男',' ','财务科','采购员',1500.00,'市朝阳区209号',' insertintoBEEmployeevalues('EC20140105','Jay','女',' ','人事部','',3000.00,'南昌市经开区209号',' insertintoBEEmployeevalues('EC20140106','', ',2600,'市浦东大道6号',' insertintoBEEmployeevalues('EC20060103','', ',2500,'江西财经大学5栋1-101室',' insertintoBEEmployeevalues('EC20070101','吴浮萍','女',' ',2500,'南昌高新开发区12号',null);insertintoBEEmployeevalues('EC20080101','','男',' ',3200,'江西财经大学12栋3-304室',null);insertintoBEEmployeevalues('EC20080105','','女',' ',5000,'市阳关大道10号',null);/**顾客表---------------- insertintoBECustomer ','','女insertintoBECustomer ','','女insertintoBECustomer ','','男insertintoBECustomer ','','男 ','','女insertintoBECustomer ','','男/**会员表---------------- insertintoBEMember insertintoBEMember /**非会员表----------------8 insertintoBENoMembervalues('C201001010011');insertintoBENoMembervalues('C201001010012');insertintoBENoMembervalues('C201001010013');insertintoBENoMembervalues('C201001010015');/**房间表----------------9 insertintoBERoomvalues('101','在用','R201410001');insertintoBERoomvalues('102','空房','R201410001');insertintoBERoomvalues('103','在用','R201410002');insertintoBERoomvalues('104','在用','R201410003');insertintoBERoomvalues('201','空房','R201410001');insertintoBERoomvalues('202','在用','R201410001');insertintoBERoomvalues('203','空房','R201410002');insertintoBERoomvalues('204','在用','R201410003');insertintoBERoomvalues('205','空房','R201410002');insertintoBERoomvalues('301','在用','R201410001');insertintoBERoomvalues('302','空房','R201410002');insertintoBERoomvalues('303','空房','R201410002');insertintoBERoomvalues('304','在用','R201410003');insertintoBERoomvalues('305','在用','R201410003');insertintoBERoomvalues('306','空房','R201410003');/**会计科目表----------------10 insertintoBEAccountingSubjectvalues('20001','1','库存现金');insertintoBEAccountingSubjectvalues('20003','1','其他货币');insertintoBEAccountingSubjectvalues('20004','1','应收票据');insertintoBEAccountingSubjectvalues('20005','1','应收账款');insertintoBEAccountingSubjectvalues('20006','1','预付账款');insertintoBEAccountingSubjectvalues('20007','1','库存商品');insertintoBEAccountingSubjectvalues('20008','1','固定资产');insertintoBEAccountingSubjectvalues('20009','1','无形资产');insertintoBEAccountingSubjectvalues('20010','1','应付票据');insertintoBEAccountingSubjectvalues('20011','1','应付账款');insertintoBEAccountingSubjectvalues('20012','1','预收账款');insertintoBEAccountingSubjectvalues('20013','1','应交税费');insertintoBEAccountingSubjectvalues('20014','1','坏账准备');insertintoBEAccountingSubjectvalues('20015','1','累计折旧');insertintoBEAccountingSubjectvalues('20016','1','本年利润');insertintoBEAccountingSubjectvalues('20017','1','利润分配');insertintoBEAccountingSubjectvalues('20018','1','主营业务收入');insertintoBEAccountingSubjectvalues('20019','1','营业外收入');insertintoBEAccountingSubjectvalues('20020','1','主营业务成本');insertintoBEAccountingSubjectvalues('20021','1','营业税金及附加');insertintoBEAccountingSubjectvalues('20022','1','销售费用');insertintoBEAccountingSubjectvalues('20023','1','用');insertintoBEAccountingSubjectvalues('20024','1','财务费用');insertintoBEAccountingSubjectvalues('20025','1','资产减值损失');insertintoBEAccountingSubjectvalues('20026','1','营业外支出');insertintoBEAccountingSubjectvalues('20027','1','所得税费用');insertintoBEAccountingSubjectvalues('20028','1','应付职工薪酬');/**商品表---------------- insertinto modityvalues('2013102100001','床单',30,'2013102102');insertinto modityvalues('2013102100002','毛巾',10,'2013102102');insertinto modityvalues('2013102100003','洗漱套装',40,'2013102102');insertinto modityvalues('2013102100004','电视机',1200,'2013102101');insertinto modityvalues('2013102100005','空调机',1500,'2013102101');insertinto modityvalues('2013102100006','',70,'2013102101');insertinto modityvalues('2013102100007','洗衣机',1300,'2013102101');insertinto modityvalues('2013102100008','套餐A',40,'2013102103');insertinto modityvalues('2013102100009','套餐B',60,'2013102103');insertinto modityvalues('2013102100010','套餐C',80,'2013102103');insertinto modityvalues('2013102100011','套餐D',100,'2013102103');/**业务数据 /**凭证主表----------------12 insertintoBDVoucherMastervalues('001','付款凭证');insertintoBDVoucherMastervalues('002','收款凭证');insertintoBDVoucherMastervalues('003','转账凭证');/**凭证明细表---------------- '用 '库存现insertintoBDVoucherDetailvalues('20141001','002','','insertintoBDVoucherDetailvalues('20141002','003','用','应付账款'/**入库表---------------- /**入库凭证表---------------- insertintoBDCreOfGodownEntryvalues('G201401002','20141002');insertintoBDCreOfGodownEntryvalues('G201401003','20141002');/**订单主表---------------- /**订单明细表---------------- insertintoBDOrderDetailvalues('2013122201','R201410001','单人间',1);/**采购单主表---------------- /**采购单明细表---------------- insertintoBDPurchaseDetailvalues('G201401001','2013102100001',100,30);insertintoBDPurchaseDetailvalues('G201401001','2013102100002',100,10);insertintoBDPurchaseDetailvalues('G201401001','2013102100003',100,40);insertintoBDPurchaseDetailvalues('G201401002','2013102100004',100,1200);insertintoBDPurchaseDetailvalues('G201401002','2013102100005',100,1500);insertintoBDPurchaseDetailvalues('G201401002','2013102100006',100,70);insertintoBDPurchaseDetailvalues('G201401002','2013102100007',100,1300);insertintoBDPurchaseDetailvalues('G201401003','2013102100008',100,40);insertintoBDPurchaseDetailvalues('G201401003','2013102100009',100,60);insertintoBDPurchaseDetailvalues('G201401003','2013102100010',100,80);insertintoBDPurchaseDetailvalues('G201401003','2013102100011',100,100);/**采购凭证表---------------- insertintoBDGreOfPurchaseSheetvalues('G201401001','20141000');insertintoBDGreOfPurchaseSheetvalues('G201401002','20141001');insertintoBDGreOfPurchaseSheetvalues('G201401003','20141002');/**入住表---------------- /**创建入住缴费凭证表---------------- insertintoBDPaymentOfCheckInvalues('CH20131230','20141001',99);insertintoBDPaymentOfCheckInvalues('CH20140113','20141001',199);4.3 mycountsqlString1varchar(100);mycount/*sqlString1:='droptable ifmycount>0endif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BDCHECKIN';ifmycount>0thenendif;/*sqlString1:='droptable ifmycount>0endif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BDPURCHASEDETAIL';ifmycount>0thenendif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BDPURCHASEMASTER';ifmycount>0thenendif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BDORDERDETAIL';ifmycount>0thenendif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BDORDERMASTER';ifmycount>0thenendif;/*sqlString1:='droptable ifmycount>0endif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BDGODOWNENTRY';ifmycount>0thenendif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BDVOUCHERDETAIL';ifmycount>0thenendif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BDVOUCHERMASTER';ifmycount>0thenendif;/ mycountsqlString1varchar(100);mycount/*sqlString1:='drop selectcount(*)intomycountfromuser_tableswhereTable_Name=' ifmycount>0thenendif;/*sqlString1:='droptable ifmycount>0endif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BEROOM';ifmycount>0thenendif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BENOMEMBER';ifmycount>0thenendif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BEMEMBER';ifmycount>0thenendif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BECUSTOMER';ifmycount>0thenendif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='BEEMPLOYEE';ifmycount>0thenendif;/ mycountsqlString1varchar(100);mycount/*sqlString1:='drop selectcount(*)intomycountfromuser_tableswhereTable_Name=' ifmycount>0thenendif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='DTROOMTYPE';ifmycount>0thenendif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='DTDINNERTYPE';ifmycount>0thenendif;/*sqlString1:='droptableselectcount(*)intomycountfromuser_tableswhereTable_Name='DTMEMBERLEVEL';ifmycount>0thenendif;5.过 varchar2,p_dataoutsys_refcursor)openp_data bdordermaster.indate)=bbyearandextract(monthfrombdordermaster.indate)=bbmonth;endcreateorreplacePROCEDUREGETSYSTIME(adateoutvarchar2)ASsdatechar(10);selectto_char(sysdate,'YYYYMMDDintosdatefromdual;/*查询服务器当前时间的年份并赋值给AYEAR*/ENDcreateorreplacePROCEDUREgethuobi(sumHBoutNUMBER)sum1number:=0;sum2number:=0;sum3number:=0;sum4number:=0;sum5number:=0;sum6number:=0;selectsum(money)intosum1frombdvoucherdetailwheredebit='库存现金';selectsum(money)intosum2frombdvoucherdetailwheredebit='银行存款';selectsum(money)intosum3frombdvoucherdetailwheredebit='其他货币';selectsum(money)intosum4frombdvoucherdetailwherecredit='库存现金';selectsum(money)intosum5frombdvoucherdetailwherecredit='银行存款';selectsum(money)intosum6frombdvoucherdetailwherecredit='其他货币';if(sum1isnull)thensum1:=0;endif;if(sum2isnull)thensum2:=0;endif;if(sum3isnull)thensum3:=0;endif;if(sum4isnull)thensum4:=0;endif;if(sum5isnull)thensum5:=0;endif;if(sum6isnull)thensum6:=0;endif;sumHB:=sum1+sum2+sum3-sum4-sum5-sum6;endgethuobi;createorPROCEDUREgetdqjk(sumdqjkoutNUMBER)sum2selectsum(money)intosum1frombdvoucherdetailwheredebit='短期借款';selectsum(money)intosum2frombdvoucherdetailwherecredit='短期借款';if(sum1isnull)thensum1:=0;endif;if(sum2isnull)thensum2:=0;endif;sumdqjk:=sum2-sum1;endgetdqjk;createorPROCEDUREgetyszk(sumyszkoutNUMBER)sum2selectsum(money)intosum1frombdvoucherdetailwheredebit='应收账款';selectsum(money)intosum2frombdvoucherdetailwherecredit='应收账款';if(sum1isnull)thensum1:=0;endif;if(sum2isnull)thensum2:=0;endif;sumyszk:=sum1-sum2;endgetyszk;createorPROCEDUREgetyfzk(sumyfzkoutNUMBER)sum2selectsum(money)intosum1frombdvoucherdetailwheredebit='应付账款';/*借方*/selectsum(money)intosum2frombdvoucherdetailwherecredit='应付账款';/*贷方*/if(sum1isnull)thensum1:=0;endif;if(sum2isnull)thensum2:=0;endif;sumyfzk:=sum2-sum1;endgetyfzk;createorreplacePROCEDUREgetqtysk(sumqtyskoutNUMBER)sum2selectsum(money)intosum1frombdvoucherdetailwheredebit='其他应收款';/*借方*/selectsum(money)intosum2frombdvoucherdetailwherecredit='其他应收款';/*贷方*/if(sum1isnull)thensum1:=0;endif;if(sum2isnull)thensum2:=0;endif;sumqtysk:=sum1-sum2;endgetqtysk;createorPROCEDUREgetyjsf(sumyjsfoutNUMBER)sum2selectsum(money)intosum1frombdvoucherdetailwheredebit='应交税费';/*借方*/selectsum(money)intosum2frombdvoucherdetailwherecredit='应交税费';/*贷方*/if(sum1isnull)thensum1:=0;endif;if(sum2isnull)thensum2:=0;endif;sumyjsf:=sum2-sum1;endgetyjsf;createorPROCEDUREgetkcsp(sumkcspoutNUMBER)sum2selectsum(money)intosum1frombdvoucherdetailwheredebit='库存商品';/*借方*/selectsum(money)intosum2frombdvoucherdetailwherecredit='库存商品';/*贷方*/if(sum1isnull)thensum1:=0;endif;if(sum2isnull)thensum2:=0;endif;endgetkcsp;createorPROCEDUREgetyflx(sumyflxoutNUMBER)sum2selectsum(money)intosum1frombdvoucherdetailwheredebit='应付利息';/*借方*/selectsum(money)intosum2frombdvoucherdetailwherecredit='应付利息';/*贷方*/if(sum1isnull)thensum1:=0;endif;if(sum2isnull)thensum2:=0;endif;sumyflx:=sum1-sum2;endgetyflx;createorPROCEDUREgetgdzc(sumgdzcoutNUMBER)selectsum(money)intosum1frombdvoucherdetailwheredebit='固定资产'; if(sum1isnull)thensum1:=0;endif; createorPROCEDUREgetljzj(sumljzjoutNUMBER) if(sum1isnull)thensum1:=0;endif; createorPROCEDUREgetsszb(sumsszboutNUMBER)selectsum(money)intosum1frombdvoucherdetailwherecredit='实收资本'; if(sum1isnull)thensum1:=0;endif; createorreplacePROCEDUREgetwfplr(sumwfplroutNUMBER)sum2selectsum(money)intosum1frombdvoucherdetailwherede