restaurant oracle

restaurant.sql

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;