Skip to content

Instantly share code, notes, and snippets.

@jrichardsz
Forked from vano468/restaurant.sql
Last active Apr 22, 2021
Embed
What would you like to do?
oracle sql plsql snippets
CREATE OR REPLACE PROCEDURE oracle_fuck
(
IN_UPD_PGM_ID IN VARCHAR2 ,
PV_UPDR_ID OUT VARCHAR2
)
IS
BEGIN
PV_UPDR_ID := 'asdad';
dbms_output.put_line ('Starting dmp load' || PV_UPDR_ID);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
END ;
https://www.oracletutorial.com/plsql-tutorial/plsql-procedure/
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;
CREATE OR REPLACE PROCEDURE "IS_OWN"."SP_SD_PGM_LOG_HST"
(
IN_UPD_PGM_ID IN VARCHAR2 , -- 로그를 남기는 프로그램ID 30
IN_MSG IN VARCHAR2 -- LOG 메세지 2000자 제한
)
/******************************************************************************
NAME : SP_SD_PGM_LOG_HST
PURPOSE : procedure application log Create
REVISIONS:
Ver Date Author Description
--- -------------- --------------- ------------------------------------
1.0 2018-10-15 TA9-3 1. Created this procedure.
NOTES:
******************************************************************************/
/*
DECLARE
PV_UPD_PGM_ID VARCHAR2(30); -- 로그를 남기는 프로그램ID
PV_MSG VARCHAR2(2000); -- LOG 메세지 2000자 제한
OUT_RSL_CD VARCHAR2(4); -- 실행 결과값 : '0000'외에는 에러
OUT_RSL_MSG VARCHAR2(2000); -- 리턴 및 에러 메세지
STEP VARCHAR2(4); -- 프로시져 스텝
BEGIN
PV_UPD_PGM_ID := 'SP_SD_ARTICLE_ALL_IVCO_MVM'; -- 프로그램ID 선언
STEP := '0001'; -- 프로그램 시작
PV_MSG := 'STEP['||STEP||']' ; -- 에러 메세지에 스탭값을 남김
SP_SD_PGM_LOG_HST(PV_UPD_PGM_ID, PV_MSG); -- 프로시져 STEP별 이력 남기기
DBMS_OUTPUT.PUT_LINE('STEP['||STEP||']');
STEP := '0002';
DBMS_OUTPUT.PUT_LINE('STEP['||STEP||']'); -- SP_SD_ARTICLE_ALL_IVCO_MVM 실행
SP_SD_ARTICLE_ALL_IVCO_MVM('IN_FROM_ARTICLE_CD', 'IN_TO_ARTICLE_CD', OUT_RSL_CD, OUT_RSL_MSG); -- 프로시져 STEP별 이력 남기기
STEP := '0003';
DBMS_OUTPUT.PUT_LINE('STEP['||STEP||']'); -- SP_SD_ARTICLE_ALL_IVCO_MVM 결과출력
DBMS_OUTPUT.PUT_LINE('실행 결과값 : '||OUT_RSL_CD);
DBMS_OUTPUT.PUT_LINE('리턴 메세지 : '||OUT_RSL_MSG);
-- 사용자 에러 강제 발생
RAISE_APPLICATION_ERROR(-20002,'사용자 강제 에러 발생' );
IF OUT_RSL_CD = '0000' THEN
STEP := '0004'; -- 정상종료 후 커핏;
DBMS_OUTPUT.PUT_LINE('STEP['||STEP||']');
PV_MSG := 'STEP['||STEP||']:'|| OUT_RSL_CD || ', ' || OUT_RSL_MSG ; -- 에러 메세지에 스탭값을 남김
SP_SD_PGM_LOG_HST(PV_UPD_PGM_ID, PV_MSG); -- 프로시져 STEP별 이력 남기기
COMMIT;
ELSE
STEP := '0005'; -- 프로시실행 결과값이 에러여서 롤백;
DBMS_OUTPUT.PUT_LINE('STEP['||STEP||']');
PV_MSG := 'STEP['||STEP||']:'|| OUT_RSL_CD || ', ' || OUT_RSL_MSG ; -- 에러 메세지에 스탭값을 남김
SP_SD_PGM_LOG_HST(PV_UPD_PGM_ID, PV_MSG); -- 프로시져 STEP별 이력 남기기
ROLLBACK;
RETURN;
END IF;
EXCEPTION
WHEN OTHERS THEN
STEP := '9999'; -- 에러 출력
OUT_RSL_CD := '9999'; -- 에러코드
OUT_RSL_MSG := 'STEP['||STEP||']:'|| SQLCODE || ', ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(OUT_RSL_MSG);
SP_SD_PGM_LOG_HST(PV_UPD_PGM_ID, OUT_RSL_MSG); -- 프로시져 STEP별 이력 남기기
ROLLBACK;
END;
*/
IS
PRAGMA AUTONOMOUS_TRANSACTION ; -- 자율트랜잭션 선언
REC_PGM_LOG_HST SD_PGM_LOG_HST%ROWTYPE ; -- 테이블 row type
PV_UPDR_ID VARCHAR2(50) := 'BATCH' ; -- 수정자 아이디
PV_UPD_PGM_ID VARCHAR2(2000):= NVL(TRIM(IN_UPD_PGM_ID),'SP_SD_PGM_LOG_HST');
BEGIN
REC_PGM_LOG_HST.WRK_DT := TO_CHAR(SYSDATE,'YYYYMMDD') ; -- 작업 일자
REC_PGM_LOG_HST.WRK_DTTM := SYSDATE ; -- 작업 일시
REC_PGM_LOG_HST.LOG_CNTS := SUBSTRB(IN_MSG,1,2000) ; -- 로그 내용 (2,000자로 짜름)
REC_PGM_LOG_HST.CRTR_ID := PV_UPDR_ID ; -- 생성자 아이디
REC_PGM_LOG_HST.CRT_PGM_ID := SUBSTRB(TRIM(PV_UPD_PGM_ID),1,30) ; -- 생성 프로그램 아이디 (30자로 짜름)
REC_PGM_LOG_HST.CRT_DTTM := SYSDATE ; -- 생성 일시
REC_PGM_LOG_HST.UPDR_ID := PV_UPDR_ID ; -- 수정자 아이디
REC_PGM_LOG_HST.UPD_PGM_ID := SUBSTRB(TRIM(PV_UPD_PGM_ID),1,30) ; -- 수정 프로그램 아이디 (30자로 짜름)
REC_PGM_LOG_HST.UPD_DTTM := SYSDATE ; -- 수정 일시
INSERT INTO SD_PGM_LOG_HST VALUES REC_PGM_LOG_HST ;
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
END ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment