--查询总菜单表
select * from recipe;
--查询每周菜单表
select * from menu;
--查询当日点菜记录表
select * from order_from;
--查询当日点菜总份额表
select * from blanket_order;
--查询所有点菜记录表（即每天点菜记录汇总）
select * from all_order_from;
--厨房主管查询每天点菜份额
select * from all_orders;
--餐厅经理对menu进行定制菜单表
insert into menu_(C_name) values('?'); --加菜是对menu_临时表进行添加，然后使用触发器，对menu表进行添加数据，让用户只输入一个菜名就可以系统自动获取其他（类型，图片，计量单位，价格等）
--用于清空菜单
delete from menu where 1=1;
--用户进行点菜操作
insert into order_temp(C_name,C_number)values(?,?);--点菜操作是对order_temp进行添加，使用触发器，对order_from 进行完善数据，让用户输入菜名和份额，系统可以自动获取其他数据（操作员账号，操作员电话，操作时间，食物计量单位，价格，合计总价）
--在各表中没有使用外键，而是在触发器中执行查询对比的功能，保持数据一致性，完整性，避免了在删除相关数据时，外键不能删除等情况；
--创建储存过程add_all，每天对blanket_order表内数据进行定时存储（每天凌晨1点执行），
--创建存储过程add_order_from，每天对order_from表进行定时存储（每天凌晨1点05分执行），存储玩数据后对order_from，order_temp, blanket_order表内数据进行清空，用于第二天的使用，
--创建存储过程all_menus，对每天形成的menu表（菜单表）进行存储，创建定时器，每天凌晨0点执行.
--查询当前用户账单记录
select * from SCOTT_TEXT.all_order_from where o_name= (SELECT user FROM dual) and O_time like '2021-11-08%';
--查询用户每个月的总花费
select O_name 用户名, sum(C_total) 总费用 from all_order_from where o_name= (SELECT user FROM dual) and O_time like '2021-11-11%'GROUP by O_name;
--查询后厨表的某天点菜数量总表
select C_name 菜名,numbers 数量, times 时间,total 总金额 from all_orders where times like '2021-11-08';
--以下是建表等语句--执行该语句 用管理员身份登录
----创建新测试用户；
Create user scott_text identified by 123456;
grant connect, resource to scott_text;
--创建role权限
grant create role to scott_text with admin option;
--创建用户权限
grant create user to scott_text with admin option;
grant alter user to scott_text with admin option;
grant become user to scott_text with admin option;
grant drop user to scott_text with admin option;
--select * from scott_text.users;
grant create session to scott_text with admin option;
grant connect to scott_text with admin option;
commit;
/* 对menu_添加数据，其实是用触发器添加到menu表中，那样就不用添加太多信息，只需写入菜名就可以了，起到了外键功能，但是删除主键却不影响menu表*/
Create Or Replace Trigger menu_checkname
after Insert On menu_ For Each Row
declare
--存储recipe的数据
name varchar2(20);
class varchar2(20);
pic varchar2(20);
unit varchar2(20);
price varchar2(20);
menu_name varchar2(20);
x int(1);
Begin
menu_name:=:new.R_name;
select count(*) into x from recipe a where a.r_name=menu_name;
if(x=1) then
select R_class into class from recipe where r_name=menu_name;
select R_pic into pic from recipe where r_name=menu_name;
select r_unit into unit from recipe where r_name=menu_name;
select R_price into price from recipe where r_name=menu_name;
DBMS_OUTPUT.PUT_LINE(x||'---->找到了'||class );
insert into menu values(menu_name,class,pic,unit,price);
End If;
if(x!=1) then
DBMS_OUTPUT.PUT_LINE('您输入的菜名'||menu_name||'不在总菜单中');
RAISE_APPLICATION_ERROR(-20011, '您输入的菜名不在总菜单中');
end if;
End;
--创建真正的点菜表
create table order_from as select * from order_temp;
SET SERVEROUTPUT ON ;--点菜触发器
/*insert into order_temp(C_name,C_number) values('肉',2);
select * from users;*/
Create Or Replace Trigger new_order
After Insert or delete On order_temp For Each Row
declare--存储recipe的数据
name varchar2(12);
O_name varchar2(12);
o_phone number(12);
o_time varchar2(30);
C_name varchar2(20);
C_unit varchar2(20) ;
C_number number(4);
price number(4);
C_total number(6);
phone_1 number(12);
name_2 varchar2(20);
name_3 varchar2(20);
x int(1);
Begin
If Inserting Then --检查菜单上的菜名是否存在
C_name:=:new.C_name;
select count(*) into x from recipe a where a.r_name=C_name;
if( x<1) then
RAISE_APPLICATION_ERROR(-20022, '菜单上没有该菜名');
end if; --将查询结果作为赋值语句--查询当前用户名 --select user from dual;
SELECT user into name_2 FROM dual;
o_name:=upper(name_2);
name_3:=Lower(o_name);
DBMS_OUTPUT.PUT_LINE(o_name||'点菜成功');
select phone into o_phone from users where reg_name =o_name or reg_name=name_3 ;
--查询电话号码
-- DBMS_OUTPUT.PUT_LINE(o_phone||'点菜成功');
--下面有bug，上面的O_name无法获取
--赋值当前时间
O_time:=:new.O_time;
--查找菜名
C_name:=:new.C_name;
--菜的份额
C_unit:=:new.C_unit;
--菜的数量
C_number:=:new.C_number;
--菜的价格
select R_price into price from menu where R_name=C_name;
--总价钱
C_total:=C_number*price;
DBMS_OUTPUT.PUT_LINE(phone_1||o_name);
Insert Into order_from Values (O_name,O_phone,O_time,C_name,C_unit,C_number,price,C_total);
--删除数据
Elsif Deleting Then
name:=:old.C_name;
delete order_from where C_name=name;
End If;
End;
--创建厨房总管表，用于打印食材的数量
create table blanket_order(
C_name varchar2(20),
numbers number(4),
times varchar2(20) default to_char(sysdate,'yyyy-mm-dd'),
totals NUMBER(10)
)
--drop table blanket_order;
--创建触发器对blanket_order表更新，统计order from表的食材数目；
set serveroutput on;
Create Or Replace Trigger add_C
After Insert Or Delete On order_from For Each Row
DECLARE
name_ varchar2(20);
name_a varchar2(20);
num number(4);
num_ number(4);
num_1 number(4);
price_ number(4);
x number(4);
total number(10);
n_ varchar2(10);
old_totals number(10);
x_3 number(10);
Begin
If Inserting Then
name_A:=:new.C_name;
select count(*) into num from blanket_order where C_name=name_a;
--改
price_:=:new.C_price;
x:=:new.C_number;
n_:=:new.C_number;
total:=x*price_;
--DBMS_OUTPUT.PUT_LINE('的价格是：：'||total);
--创建数据
if(num=0) then
insert into blanket_order(C_name,numbers,totals) values(name_a,n_,total);
--添加数据
end if;
if(num>0) then
--查出总表中的旧金额
select totals into old_totals from blanket_order where C_name=name_A;
--DBMS_OUTPUT.PUT_LINE('旧金额是：：'||old_totals);
num_:=:new.C_number;
select numbers into num_1 from blanket_order where C_name=name_a;
update blanket_order set numbers=num_+num_1 where C_name=name_a;
update blanket_order set totals=old_totals+total where C_name=name_a;
end if;
--输出数据的时候
end if;
if deleting then
name_:=:old.C_name;
num:=:old.C_number;
old_totals:=:old.C_total;
select numbers into num_1 from blanket_order where C_name=name_;
select totals into total from blanket_order where C_name=name_;
DBMS_OUTPUT.PUT_LINE('被删除的数据是：：'||name_||' 数量是：'||num_1||' 总金额是：'||total);
DBMS_OUTPUT.PUT_LINE('需要删除：'||num||'删除的总金额为：'||old_totals);
UPDATE blanket_order set numbers=num_1-num where C_name=name_;
UPDATE blanket_order set totals=total-old_totals where C_name=name_;
--当食材数量为0时，删除该行
select numbers into x_3 from blanket_order where C_name=name_;
if(x_3=0) then
delete from blanket_order where C_name=name_;
end if;
end if;
end;
/*
select * from users;
--
--触发器--操作时间限制-order_temp表
CREATE OR REPLACE TRIGGER time_s
BEFORE INSERT OR DELETE OR UPDATE
ON order_temp
BEGIN
if((select count(*) from users where role_='企业员工' and reg_name in (select lower(user) from dual))>0) then
IF (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '09:00' AND '16:00') THEN
RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改点菜表');
END IF;
end if;
END;
drop TRIGGER time_s;
commit;
--触发器--操作时间限制-order_from 打印表
CREATE OR REPLACE TRIGGER time_limit
BEFORE INSERT OR DELETE OR UPDATE
ON order_from
BEGIN
if((select count(*) from users where role_='配餐员' and reg_name in (select lower(user) from dual))>0) then
IF (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '09:00' AND '19:00') THEN
RAISE_APPLICATION_ERROR(-20112, '未到11：30分，不能打印所有订单');
END IF;
end if;
END;
--drop TRIGGER time_limit;*/
/*
BEGIN
--210为job的id,此id不是随便填写的,而是执行select * from user_jobs;查询到定时任务名称对应的id
dbms_job.remove(29);
commit;
end;
--查询计时器的job
*/
--delete all_orders where 1=1;
--删除计时器
--select * from blanket_order;
--select * from order_from;
--select * from order_temp;
--delete order_temp where 1=1;
--delete blanket_order where 1=1;
--insert into blanket_order(C_name,numbers,totals) values('肉',2,20);
----创建角色
-- --创建角色--
--创建多个角色：
create role manager_max;
create role manager_med;
create role manager_min;
create role emp_man;
--drop role manager_max;
-- drop role manager_med;
-- drop role manager_min;
-- drop role emp_mans;
--餐厅经理对所有表都有权限；usrs,order_from,order_temp,recipe,menu,blanket_order等表有所有权限
grant all on users to manager_max;
grant all on order_from to manager_max;
grant all on order_temp to manager_max;
grant all on recipe to manager_max;
grant all on menu to manager_max;
grant all on blanket_order to manager_max;
grant all on menu_ to manager_max;
grant all on all_orders to manager_max;
grant all on all_order_from to manager_max;
--厨房经理order_from,order_temp,menu,blanket_order等表有所有权限
grant all on order_from to manager_med;
grant all on order_temp to manager_med;
grant select on menu to manager_med;
grant select on recipe to manager_med;
grant all on blanket_order to manager_med;
grant select on all_orders to manager_med;
grant select on all_order_from to manager_med;
--revoke all on menu from manager_med; --收回farxix角色的权限
grant all on order_from to manager_min;
grant all on order_temp to manager_min;
grant select on menu to manager_min;
grant select on blanket_order to manager_min;
grant select on all_orders to manager_min;
grant select on all_order_from to manager_min;
grant select on users to emp_man;
grant select on recipe to emp_man;
grant select on menu to emp_man;
grant all on order_temp to emp_man;
grant all on order_from to emp_man;
grant all on blanket_order to emp_man;
grant select on all_order_from to emp_man;
----SET SERVEROUTPUT ON ;
--创建users用户，并赋值角色；
declare
regname users.reg_name%type;
pwd_ users.pwd%type;
x number(20);
role_1 varchar2(20);
regname_ users.reg_name%type;
null_ int(6);
num int(20) :=1 ;
BEGIN
select max(rownum) into x from users;
while num <= X
loop --查询账号密码，用于创建用户
select reg_name into regname from(select rownum no , reg_name from users) where no=num;
select pwd into pwd_ from(select rownum no , pwd from users) where no=num;
select emp into role_1 from (select rownum no , role_ emp from users)where no=num;
regname_:=upper(regname);
select count(*) into null_ from all_users where username=regname_;
if(null_<1)then
DBMS_OUTPUT.PUT_LINE('我可以创建用户了 id======：'||null_);
execute immediate 'Create user'||' '|| regname||' '||' Identified by '||pwd_ ;
execute immediate 'grant create SESSION to'||' '|| regname;
end if;
if(role_1='企业员工')then
execute immediate 'grant emp_man to'||' '|| regname;
DBMS_OUTPUT.PUT_LINE(role_1||'赋值了');
elsif(role_1='厨房主管')then
execute immediate 'grant manager_med to'||' '|| regname;
DBMS_OUTPUT.PUT_LINE(role_1||'赋值了');
end if;
if(role_1='财务管理员') then
execute immediate 'grant manager_min to'||' '|| regname;
DBMS_OUTPUT.PUT_LINE(role_1||'赋值了');
elsif(role_1='餐厅经理')then
--execute immediate 'grant manager_max to'||' '|| regname;
DBMS_OUTPUT.PUT_LINE(role_1||'赋值了');
end if;
num:=num+1;
end loop;
END;
--添加数据语句
--数据的添加
--添加数据到user表
insert into users(name,reg_name,demp,role_) values('小麦','SCOTT_text','经理部','企业员工');
insert into users(name,reg_name,demp,role_) values('小麦','scott_text_1','经理部','企业员工');
--recipe总菜单表
insert into recipe(R_name,R_class,r_unit,r_price) values('麻婆豆腐','主食','/杯','50');
insert into recipe(R_name,R_class,r_unit,r_price) values('爽辣鱼片','主食','/盘','80');
insert into recipe(R_name,R_class,r_unit,r_price) values('煲鳕鱼','主食','/煲','60');
insert into recipe(R_name,R_class,r_unit,r_price) values('红豆奶茶','甜品','/杯','10');
insert into recipe(R_name,R_class,r_unit,r_price) values('马拉糕','糕点','/件','5');
insert into recipe(R_name,R_class,r_unit,r_price) values('鸡蛋羹','糕点','/件','1');
insert into recipe(R_name,R_class,r_unit,r_price) values('冰水','甜品','/杯','1');
insert into recipe(R_name,R_class,r_unit,r_price) values('肉','主食','/块','10');
insert into recipe(R_name,R_class,r_unit,r_price) values('火焰冰淇淋','甜品','/杯','10');
insert into recipe(R_name,R_class,r_unit,r_price) values('小龙虾','主食','/盘','200');
--添加菜单
insert into menu_(R_name) values('麻婆豆腐');
insert into menu_(R_name) values('马拉糕');
insert into menu_(R_name) values('肉');
insert into menu_(R_name) values('冰水');
insert into menu_(R_name) values('小龙虾');
insert into menu_(R_name) values('爽辣鱼片');
--点菜
insert into order_temp(C_name,C_number) values('肉',2);
insert into order_temp(C_name,C_number) values('马拉糕',2);
insert into order_temp(C_name,C_number) values('小龙虾',2);
发表于 2021-11-15 20:59
