--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小时内删除。