oracle sql plsql snippets

constants

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 ;

more

https://www.oracletutorial.com/plsql-tutorial/plsql-procedure/

oracle sql plsql snippets.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;

PROCEDURE.sql

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 ;