--system身份登录
create user admin1 identified by m123;
grant connect,resource,create view,create procedure,create trigger to admin1;
--admin1身份登录
drop table desk;
drop table memu;
drop table ord;
drop table cook;
drop table payoff;
drop sequence deskNumber;
drop sequence memuNumber;
-
-创建桌号表
SQL> create table desk(
2 deskGuid varchar2(32),
3 deskNumber number,
4 personNumber number,
5 deskState varchar2(12)
6 )
--创建deskNumber的自动增长序列
SQL> create sequence deskNumber
2 increment by 1
3 start with 1001
4 nomaxvalue
5 nocycle
6 nocache;
Sequence created
--创建菜单表
SQL> create table memu(
2 memuGuid varchar2(32),
3 memuNumber number,
4 dishName varchar2(30),
5 unitPrice number(10,2),
6 dishType varchar2(12)
7 );
Table created
--创建deskNumber的自动增长序列
SQL> create sequence memuNumber
2 increment by 1
3 start with 1
4 nomaxvalue
5 nocycle
6 nocache;
Sequence created
--创建点菜表
SQL> create table ord(
2 ordGuid varchar2(32),
3 memuGuid varchar2(32),
4 deskGuid varchar2(32) ,
5 ordTime date,
6 ordNumber number,
7 ordState varchar2(10),
8 isState varchar2(10)
9 );
Table created
--创建做菜表
SQL> create table cook(
2 cookGuid varchar2(32),
3 deskGuid varchar2(32),
4 memuGuid varchar2(32),
5 cookState varchar2(10)
6 );
Table created
--创建结算表
SQL> create table payoff(
2 payoffGuid varchar2(32),
3 deskGuid varchar2(32),
4 payoffTime date,
5 total number(10,2),
6 ispay varchar2(10)
7 );
Table created
--添加约束
SQL> alter table desk
2 add constraint PK_deskGuid primary key(deskGuid)
3 modify personNumber default 12
4 modify deskState default ';未使用';
Table altered
SQL> alter table memu
2 add constraint PK_memuGuid primary key(memuGuid);
Table altered
SQL> alter table ord
2 add constraint PK_ordGuid primary key(ordGuid)
3 add constraint FK_deskGuid foreign key(deskGuid) references desk(deskGuid)
4 modify ordTime default sysdate
5 modify isState default ';是';
Table altered
SQL> alter table cook
2 add constraint PK_cookGuid primary key(cookGuid)
3 add constraint FK1_deskGuid foreign key(deskGuid) references desk(deskGuid)
4 add constraint FK_memuGuid foreign key(memuGuid) references memu(memuGuid)
5 modify cookState default ';否';
Table altered
SQL> alter table payoff
2 add constraint PK_payoffGuid primary key(payoffGuid)
3 add constraint FK2_deskGuid foreign key(deskGuid) references desk(deskGuid)
4 modify payoffTime default sysdate
5 modify ispay default ';是';
Table altered
--插入数据
SQL> insert into desk values(sys_guid(),deskNumber.Nextval,default,default);
1 r
ow inserted
SQL> insert into desk values(sys_guid(),deskNumber.Nextval,8,';正在使用');
1 row inserted
SQL> insert into desk values(sys_guid(),deskNumber.Nextval,10,';正在使用');
1 row inserted
SQL> insert into desk values(sys_guid(),deskNumber.Nextval,default,';正在使用');
1 row inserted
SQL> insert into desk values(sys_guid(),deskNumber.Nextval,4,default);
1 row inserted
SQL> insert into memu values(sys_guid(),memuNumber.Nextval,';小鸡炖蘑菇',40.00,';荤菜类');
1 row inserted
SQL> insert into memu values(sys_guid(),memuNumber.Nextval,';烤乳猪',100.00,';荤菜类');
1 row inserted
SQL> insert into memu values(sys_guid(),memuNumber.Nextval,';猪肉炖粉条',50.00,';荤菜类');
1 row inserted
SQL> insert into memu values(sys_guid(),memuNumber.Nextval,';炒三丝',40.00,';蔬菜类');
1 row inserted
insert into ord values(sys_guid(),'91FDC1B016BD4829828B0432F716E2AF','825D230C32B84C66BFB44C8B033D4031',default,1,default,default);
insert into ord values(sys_guid(),'91B6008C17D14D099D4D5395D78348A8','825D230C32B84C66BFB44C8B033D4031',default,1,default,default);
insert into ord values(sys_guid(),'D64401CC19B548219F4892D20E994F91','825D230C32B84C66BFB44C8B033D4031',default,1,default,default);
select * from ord;
update ord set ordState=';是';
insert into cook values(sys_guid(),'825D230C32B84C66BFB44C8B033D4031','91FDC1B016BD4829828B0432F716E2AF',default);
insert into cook values(sys_guid(),'825D230C32B84C66BFB44C8B033D4031','91B6008C17D14D099D4D5395D78348A8',default);
insert into cook values(sys_guid(),'825D230C32B84C66BFB44C8B033D4031','D64401CC19B548219F4892D20E994F91',default);
select * from cook;
insert into payoff values(sys_guid(),'825D230C32B84C66BFB44C8B033D4031',default,180,';是');
select * from payoff;
SQL> create index index_dishName on memu (dishName);
Index created
SQL> create or replace view v_table as select deskNumber 桌位编号,deskState 桌位状态 from desk;
View created
SQL> select * from v_table;
桌位编号 桌位状态
---------- ------------
1002 未使用
1003 正在使用
1004 正在使用
1005 正在使用
1006 未使用
SQL> create or replace view v_info as uGuid 菜单Guid,m.dishName 菜名,m.unitPrice 单价,o.ordNumber 点菜数量,(m.dNumber) 金额
2 from memu m,ord o uguid;
View created
SQL> select * from v_info;
菜单GUID 菜名 单价 点菜数量 金额
-------------------------------- ------------------------------ ------------ ---------- ----------
SQL> create or replace view v_analyse as select to_char(pay
offTime,'mm') 月份,sum(total) 销售金额 from payoff
2 where to_char(payoffTime,'yy')=to_char(sysdate,'yy') group by to_char(payoffTime,'mm');
View created
--存储过程&;游标
1.完成一个存储过程sp_Cooking,模拟厨
房做菜操作,参数是点菜Guid和桌位Guid
完成如下功能:
A. 将做菜信息插入到做菜表中去;
注意:此时对应的做菜表应该对应当前点菜的桌位所点的所有菜
SQL> create or replace procedure sp_Cooking(ordGuid1 varchar2,deskGuid1 varchar2)
2 is
3 memuGuid1 varchar2(32);
4 begin
5 select memuGuid into memuGuid1 from ord where deskGuid=deskGuid1 and ordGuid=ordGuid1;
6 insert into cook values(sys_guid(),deskGuid1,memuGuid1,default);
7 dbms_output.put_line(';添加了一道菜'||memuGuid1);
8 exception
9 when others then
10 dbms_output.put_line(sqlerrm);
11 end;
12 /
Procedure created
SQL> declare
2 ord_Guid varchar2(32):='&;点菜Guid';
3 desk_Guid varchar2(32):='&;桌位Guid';
4 begin
5 sp_Cooking(ord_Guid,desk_Guid);
6 end;
7 /
PL/SQL procedure successfully completed
2.完成一个存储过程sp_account,模拟结算操作,参数是桌位Guid
完成如下功能:
A. 将已经上的菜的金额存放到结算表中去;
B. 设置桌号已经空了;
C. 标志点菜表,做菜表中相应记录已经结算完成;
注意:此处结算的菜包括:在桌上吃的菜,不是所有点的菜。
SQL> create or replace procedure sp_account(deskGuid1 varchar2)
2 as
3 total1 number(10,2):=0;
4 begin
5 select sum(m.dnumber) into total1 from ord o,memu m uguid and deskGuid=deskGuid1;
6 insert into payoff values(sys_guid(),deskGuid1,default,total1,';是');
7 dbms_output.put_line(';已将已经上的菜的金额存放到结算表中去了');
8 update desk set deskState=';未使用' where deskGuid=deskGuid1;
9 dbms_output.put_line(';桌号已经空了');
10 update ord set ordState=';是' where deskGuid=deskGuid1;
11 update cook set cookState=';是' where deskGuid=deskGuid1;
12 dbms_output.put_line(';标志点菜表,做菜表中相应记录已经结算完成');
oracle游标的使用13 exception
14 when others then
15 dbms_output.put_line(sqlerrm);
16 end;
17 /
Procedure created
SQL> declare
2 desk_Guid varchar2(32):='&;桌位Guid';
3 begin
4 sp_account(desk_Guid);
5 end;
6 /
PL/SQL procedure successfully completed
--3.完成一个存储过程sp_bill, 使用游标模拟生成账单操作,传入“桌位”;(菜名编号,菜名,单价)
SQL> create or replace procedure sp_bill(deskGuid1 varchar2)
2 as
3 cursor c is uNumber,m.dishName,m.unitPrice from memu m,ord o
uguid and o.deskGuid=deskGuid1;
4 c1rec c%rowtype;
5 total number(10,2);
6 begin
7 dbms_output.put_line(';菜名编号'||' '||' 菜名 '||' '||' 单价 ');
8 for clrec in c
9 loop
10 dbms_output.put_uNumber||' '||clrec.dishName||' '||clrec.unitPrice);
11 end loop;
12 select sum(m.dnumber) into total from ord o,memu m uguid and deskGuid=deskGuid1;
13 dbms_output.put_line(' '||' '||' 总价 : '||' '||total);
14 exception
15 when others then
16 dbms_output.put_line(sqlerrm);
17 close c;
18 end;
19 /
Procedure created
SQL> declare
2 desk_Guid varchar2(32):='&;桌位Guid';
3 begin
4 sp_bill(desk_Guid);
5 end;
6 /
PL/SQL procedure successfully completed
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论