Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
restaurant oracle
SET SERVEROUTPUT ON
/*
* dishes table
*/
DROP TABLE dishes;
DROP SEQUENCE dishes_seq;
CREATE TABLE dishes (
id NUMBER(10) NOT NULL,
name VARCHAR2(100) NOT NULL,
weight NUMBER(10) DEFAULT 0,
price NUMBER(10) DEFAULT 0);
ALTER TABLE dishes ADD (
CONSTRAINT dishes_pk PRIMARY KEY (id));
CREATE SEQUENCE dishes_seq MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 ORDER NOCACHE NOCYCLE;
CREATE OR REPLACE TRIGGER dishes_trg BEFORE
INSERT ON dishes FOR EACH ROW BEGIN :new.id := dishes_seq.nextval; END;
/*
* ingredients table
*/
DROP TABLE ingredients;
DROP SEQUENCE ingredients_seq;
CREATE TABLE ingredients (
id NUMBER(10) NOT NULL,
name VARCHAR2(100) NOT NULL,
category VARCHAR2(100));
ALTER TABLE ingredients ADD (
CONSTRAINT ingredients_pk PRIMARY KEY (id));
CREATE SEQUENCE ingredients_seq MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 ORDER NOCACHE NOCYCLE;
CREATE OR REPLACE TRIGGER ingredients_trg BEFORE
INSERT ON ingredients FOR EACH ROW BEGIN :new.id := ingredients_seq.nextval; END;
/*
* orders table
*/
DROP TABLE orders;
DROP SEQUENCE orders_seq;
CREATE TABLE orders (
id NUMBER(10) NOT NULL,
table_room NUMBER(10) NOT NULL,
ordered_at TIMESTAMP,
state VARCHAR2(20));
ALTER TABLE orders ADD (
CONSTRAINT orders_pk PRIMARY KEY (id));
CREATE SEQUENCE orders_seq MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 ORDER NOCACHE NOCYCLE;
CREATE OR REPLACE TRIGGER orders_trg BEFORE
INSERT ON orders FOR EACH ROW BEGIN :new.id := orders_seq.nextval; END;
/*
* dish_ingredients relation table
*/
DROP TABLE dish_ingredients;
CREATE TABLE dish_ingredients (
dish_id INT NOT NULL,
ingredient_id INT NOT NULL,
weight NUMBER(10),
PRIMARY KEY(dish_id, ingredient_id));
ALTER TABLE dish_ingredients ADD CONSTRAINT
dish_ingredients_fk_dish FOREIGN KEY(dish_id) references dishes(id);
ALTER TABLE dish_ingredients ADD CONSTRAINT
dish_ingredients_fk_ingredient FOREIGN KEY(ingredient_id) references ingredients(id);
/*
* order_dishes relation table
*/
DROP TABLE order_dishes;
CREATE TABLE order_dishes (
order_id INT NOT NULL,
dish_id INT NOT NULL,
count NUMBER(10),
PRIMARY KEY(order_id, dish_id));
ALTER TABLE order_dishes ADD CONSTRAINT
order_dishes_fk_order FOREIGN KEY(order_id) references orders(id);
ALTER TABLE order_dishes ADD CONSTRAINT
order_dishes_fk_dish FOREIGN KEY(dish_id) references dishes(id);
/*
* add_dish procedure
*/
CREATE OR REPLACE PROCEDURE add_dish (
name IN VARCHAR2, weight IN NUMBER, price IN NUMBER
) AS
BEGIN
INSERT INTO dishes VALUES (null, name, weight, price);
END add_dish;
/*
* add_ingredient procedure
*/
CREATE OR REPLACE PROCEDURE add_ingredient (
name IN VARCHAR2, category IN VARCHAR2
) AS
BEGIN
INSERT INTO ingredients VALUES (null, name, category);
END add_ingredient;
/*
* add_ingredient_to_dish procedure
*/
CREATE OR REPLACE PROCEDURE add_ingredient_to_dish (
ingredient_id IN NUMBER, dish_id IN NUMBER, weight IN NUMBER
) AS
ingredients_count NUMBER;
dishes_count NUMBER;
BEGIN
SELECT COUNT(*) INTO ingredients_count
FROM ingredients WHERE id = ingredient_id;
SELECT COUNT(*) INTO dishes_count
FROM dishes WHERE id = dish_id;
IF ingredients_count = 0 THEN
DBMS_OUTPUT.Put_Line('ERROR: ingredient by id not found');
return;
END IF;
IF dishes_count = 0 THEN
DBMS_OUTPUT.Put_Line('ERROR: dish by id not found');!
return;
END IF;
INSERT INTO dish_ingredients VALUES (dish_id, ingredient_id, weight);
END add_ingredient_to_dish;
/*
* create_order procedure
*/
CREATE OR REPLACE PROCEDURE create_order (
table_room IN NUMBER, dish_id IN NUMBER
) AS
order_id NUMBER;
dishes_count NUMBER;
BEGIN
SELECT COUNT(*) INTO dishes_count
FROM dishes WHERE id = dish_id;
IF dishes_count = 0 THEN
DBMS_OUTPUT.Put_Line('ERROR: dish by id not found');
return;
END IF;
INSERT INTO orders VALUES (null, table_room, SYSDATE, 'created')
RETURNING id into order_id;
INSERT INTO order_dishes VALUES (order_id, dish_id, 1);
END create_order;
/*
* add_dish_to_order procedure
*/
CREATE OR REPLACE PROCEDURE add_dish_to_order (
dish_id_arg IN NUMBER, order_id_arg IN NUMBER, dishes_count IN NUMBER
) AS
orders_rows_count NUMBER;
dishes_rows_count NUMBER;
order_dish_count NUMBER;
BEGIN
SELECT COUNT(*) INTO orders_rows_count
FROM orders WHERE id = order_id_arg;
SELECT COUNT(*) INTO dishes_rows_count
FROM dishes WHERE id = dish_id_arg;
IF dishes_rows_count = 0 THEN
DBMS_OUTPUT.Put_Line('ERROR: dish by id not found');
return;
END IF;
IF orders_rows_count = 0 THEN
DBMS_OUTPUT.Put_Line('ERROR: order by id not found');
return;
END IF;
SELECT COALESCE(SUM(count), 0) INTO order_dish_count
FROM order_dishes
WHERE order_id = order_id_arg AND dish_id = dish_id_arg;
IF order_dish_count = 0 THEN
INSERT INTO order_dishes VALUES (order_id_arg, dish_id_arg, dishes_count);
ELSE
UPDATE order_dishes SET count = order_dish_count + dishes_count
WHERE order_id = order_id_arg AND dish_id = dish_id_arg;
END IF;
END add_dish_to_order;
/*
* show_menu procedure
*/
CREATE OR REPLACE PROCEDURE show_menu AS
CURSOR dish_cursor IS
SELECT name, weight, price
FROM dishes;
cur_row dish_cursor%ROWTYPE;
BEGIN
OPEN dish_cursor;
FETCH dish_cursor into cur_row;
LOOP
EXIT WHEN dish_cursor%notfound;
DBMS_OUTPUT.PUT_LINE(cur_row.name || ', ' || cur_row.weight || 'гр., ' || cur_row.price || 'р.');
FETCH dish_cursor into cur_row;
END LOOP;
END show_menu;
/*
* show_recipe procedure
*/
CREATE OR REPLACE PROCEDURE show_recipe (
dish_id_arg IN NUMBER
) AS
dishes_rows_count NUMBER;
CURSOR ingredient_cursor IS
SELECT name, dish_ingredients.weight
FROM ingredients
INNER JOIN dish_ingredients ON
ingredients.id = dish_ingredients.ingredient_id AND
dish_ingredients.dish_id = dish_id_arg;
cur_row ingredient_cursor%ROWTYPE;
BEGIN
SELECT COUNT(*) INTO dishes_rows_count
FROM dishes WHERE id = dish_id_arg;
IF dishes_rows_count = 0 THEN
DBMS_OUTPUT.Put_Line('ERROR: dish by id not found');
return;
END IF;
OPEN ingredient_cursor;
FETCH ingredient_cursor into cur_row;
LOOP
EXIT WHEN ingredient_cursor%notfound;
DBMS_OUTPUT.PUT_LINE(cur_row.name || ', ' || cur_row.weight || 'гр.');
FETCH ingredient_cursor into cur_row;
END LOOP;
END show_recipe;
/*
* show_order_details procedure
*/
CREATE OR REPLACE PROCEDURE show_order_details (
order_id_arg IN NUMBER
) AS
orders_rows_count NUMBER;
table_room orders.table_room%TYPE;
ordered_at orders.ordered_at%TYPE;
CURSOR dishes_cursor IS
SELECT name, order_dishes.count
FROM dishes
INNER JOIN order_dishes ON
dishes.id = order_dishes.dish_id AND
order_dishes.order_id = order_id_arg;
cur_row dishes_cursor%ROWTYPE;
BEGIN
SELECT COUNT(*) INTO orders_rows_count
FROM orders WHERE id = order_id_arg;
IF orders_rows_count = 0 THEN
DBMS_OUTPUT.Put_Line('ERROR: order by id not found');
return;
END IF;
SELECT table_room, ordered_at INTO table_room, ordered_at
FROM orders WHERE id = order_id_arg;
DBMS_OUTPUT.PUT_LINE('table: ' || table_room || ', ' || 'ordered at: ' || ordered_at);
OPEN dishes_cursor;
FETCH dishes_cursor into cur_row;
LOOP
EXIT WHEN dishes_cursor%notfound;
DBMS_OUTPUT.PUT_LINE(cur_row.name || ', ' || cur_row.count || 'шт.');
FETCH dishes_cursor into cur_row;
END LOOP;
END show_order_details;
/*
* change_order_state procedure
*/
CREATE OR REPLACE PROCEDURE change_order_state (
order_id_arg IN NUMBER
) AS
orders_rows_count NUMBER;
state orders.state%TYPE;
new_state orders.state%TYPE;
BEGIN
SELECT COUNT(*) INTO orders_rows_count
FROM orders WHERE id = order_id_arg;
IF orders_rows_count = 0 THEN
DBMS_OUTPUT.Put_Line('ERROR: order by id not found');
return;
END IF;
SELECT state INTO state
FROM orders WHERE id = order_id_arg;
new_state := CASE state
WHEN 'created' THEN 'processed'
WHEN 'processed' THEN 'completed'
WHEN 'completed' THEN 'paid'
WHEN 'paid' THEN 'paid'
ELSE 'created'
END;
UPDATE orders SET state = new_state
WHERE id = order_id_arg;
END change_order_state;
/*
* calculate_profit procedure
*/
CREATE OR REPLACE PROCEDURE calculate_profit
AS
profit NUMBER;
BEGIN
SELECT
SUM(count * price) INTO profit
FROM order_dishes
INNER JOIN dishes ON dishes.id = order_dishes.dish_id
INNER JOIN orders ON orders.id = order_dishes.order_id
WHERE orders.state = 'paid';
DBMS_OUTPUT.PUT_LINE('profit: ' || profit);
END calculate_profit;
/*
* remove_unclaimed_dishes procedure
*/
CREATE OR REPLACE PROCEDURE remove_unclaimed_dishes
AS
CURSOR dishes_cursor IS
SELECT
id, name, weight, price
FROM dishes
LEFT OUTER JOIN order_dishes ON dishes.id = order_dishes.dish_id
WHERE dish_id IS NULL;
cur_row dishes_cursor%ROWTYPE;
BEGIN
OPEN dishes_cursor;
FETCH dishes_cursor into cur_row;
LOOP
EXIT WHEN dishes_cursor%notfound;
DBMS_OUTPUT.PUT_LINE(cur_row.name || ', ' || cur_row.weight || 'гр., ' || cur_row.price || 'р.');
DELETE FROM dishes WHERE id = cur_row.id;
FETCH dishes_cursor into cur_row;
END LOOP;
END remove_unclaimed_dishes;
/*
* show_dishes_by_rank procedure
*/
CREATE OR REPLACE PROCEDURE show_dishes_by_rank (
min_orders_numer IN NUMBER DEFAULT 1
) AS
CURSOR dishes_cursor IS
SELECT
name, SUM(count) orders_number
FROM dishes
INNER JOIN order_dishes ON order_dishes.dish_id = dishes.id
GROUP BY id, name
HAVING SUM(count) >= min_orders_numer
ORDER BY orders_number DESC;
cur_row dishes_cursor%ROWTYPE;
BEGIN
OPEN dishes_cursor;
FETCH dishes_cursor into cur_row;
LOOP
EXIT WHEN dishes_cursor%notfound;
DBMS_OUTPUT.PUT_LINE(cur_row.name || ', ordered: ' || cur_row.orders_number);
FETCH dishes_cursor into cur_row;
END LOOP;
END show_dishes_by_rank;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment