저장 프로그램 이행

SELECT object_type, object_name, status
  FROM user_objects
 WHERE object_type IN ('FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW')
 ORDER BY object_type, object_name;

SELECT name, type, referenced_owner, referenced_name, referenced_type
  FROM user_dependencies
 WHERE referenced_owner NOT IN ('SYS', 'PUBLIC')
 ORDER BY type, name;

@<ORACLE_WORK_DIR>/set_gen.sql
SPOOL <ORACLE_WORK_DIR>/ora_plsql_src.txt
SELECT CASE WHEN line = 1
            THEN CHR(10) || '-- ===== ' || type || ' ' || name || ' =====' || CHR(10)
       END || RTRIM(text, CHR(10))
  FROM user_source
 ORDER BY DECODE(type, 'FUNCTION', 1, 'TRIGGER', 2, 'PROCEDURE', 3), name, line;
SELECT CHR(10) || '-- ===== VIEW ' || view_name || ' =====' FROM user_views;
SELECT text FROM user_views;
SPOOL OFF
EXIT

CREATE ROLE <REPORT_USER> LOGIN PASSWORD '<PASSWORD>';
CREATE SCHEMA <REPORT_USER> AUTHORIZATION <REPORT_USER>;
CREATE TABLE <REPORT_USER>.rpt_daily (rpt_date TIMESTAMP(0), order_cnt NUMERIC, total_amt NUMERIC);
ALTER TABLE <REPORT_USER>.rpt_daily OWNER TO <REPORT_USER>;
GRANT USAGE ON SCHEMA <REPORT_USER> TO <USERNAME>;
GRANT SELECT, INSERT ON <REPORT_USER>.rpt_daily TO <USERNAME>;

CREATE OR REPLACE FUNCTION get_grade (p_sal NUMERIC)
RETURNS VARCHAR
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF p_sal >= 3000 THEN
    RETURN 'A';
  ELSIF p_sal >= 2000 THEN
    RETURN 'B';
  ELSE
    RETURN 'C';
  END IF;
END;
$$;

CREATE OR REPLACE VIEW v_emp_grade AS
SELECT empno, ename, sal, get_grade(sal) AS grade FROM emp_m;

CREATE OR REPLACE FUNCTION trg_orders_bi_fn()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.order_id IS NULL THEN
    NEW.order_id := nextval('ord_seq');
  END IF;
  IF NEW.reg_date IS NULL THEN
    NEW.reg_date := LOCALTIMESTAMP(0);
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_bi
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION trg_orders_bi_fn();

CREATE OR REPLACE FUNCTION trg_orders_ai_fn()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO order_hist VALUES (NEW.order_id, 'INSERT', NEW.amount, LOCALTIMESTAMP(0));
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_orders_ai
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION trg_orders_ai_fn();

CREATE OR REPLACE PROCEDURE add_order (p_cust VARCHAR, p_amt NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
  BEGIN
    INSERT INTO orders (cust_name, amount) VALUES (p_cust, p_amt);
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE '주문 실패 : %', SQLERRM;
      RETURN;
  END;
  COMMIT;
  RAISE NOTICE '주문 성공 : %', p_cust;
END;
$$;

CREATE OR REPLACE PROCEDURE raise_sal (p_deptno NUMERIC, p_pct NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
  v_cnt INTEGER;
BEGIN
  UPDATE emp_m SET sal = sal * (1 + p_pct / 100) WHERE deptno = p_deptno;
  GET DIAGNOSTICS v_cnt = ROW_COUNT;
  RAISE NOTICE '%명 급여 인상', v_cnt;
  COMMIT;
END;
$$;

CREATE OR REPLACE PROCEDURE send_report ()
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO <REPORT_USER>.rpt_daily
  SELECT CURRENT_DATE, COUNT(*), SUM(amount) FROM orders;
  COMMIT;
END;
$$;

SELECT CASE prokind WHEN 'p' THEN 'PROCEDURE' ELSE 'FUNCTION' END AS object_type, proname
  FROM pg_proc WHERE pronamespace = '<USERNAME>'::regnamespace ORDER BY 1, 2;
SELECT tgrelid::regclass AS table_name, tgname, tgenabled FROM pg_trigger WHERE NOT tgisinternal ORDER BY 1, 2;
SELECT viewname FROM pg_views WHERE schemaname = '<USERNAME>';

CALL add_order('토스', 55000);
CALL add_order(repeat('가', 31), 1000);
CALL raise_sal(10, 0);
CALL send_report();
SELECT * FROM <REPORT_USER>.rpt_daily;

psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>' -f '<MYSQL_PROGRAM_DIR>/my_05_func_view.sql'

cat > pg_05_func_view.sql <<'EOF'
CREATE OR REPLACE FUNCTION get_grade (p_sal NUMERIC)
RETURNS VARCHAR
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF p_sal >= 3000 THEN
    RETURN 'A';
  ELSIF p_sal >= 2000 THEN
    RETURN 'B';
  ELSE
    RETURN 'C';
  END IF;
END;
$$;

CREATE OR REPLACE VIEW v_emp_grade AS
SELECT empno, ename, sal, get_grade(sal) AS grade
  FROM emp_m;
EOF

psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>' -v ON_ERROR_STOP=1 -f pg_05_func_view.sql

psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>' -c "SELECT * FROM v_emp_grade ORDER BY empno"

cat > pg_06_triggers.sql <<'EOF'
-- ===== trg_orders_bi : 등록일만 (번호는 IDENTITY가 채움) =====
CREATE OR REPLACE FUNCTION trg_orders_bi_fn()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.reg_date IS NULL THEN
    NEW.reg_date := statement_timestamp()::timestamp(0);   -- MySQL NOW() = 문장 시작 시각
  END IF;
  RETURN NEW;                       -- BEFORE 트리거는 NEW를 돌려줘야 행이 들어감
END;
$$;

DROP TRIGGER IF EXISTS trg_orders_bi ON orders;
CREATE TRIGGER trg_orders_bi
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION trg_orders_bi_fn();

-- ===== trg_orders_ai : 주문 이력 기록 =====
CREATE OR REPLACE FUNCTION trg_orders_ai_fn()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO order_hist
  VALUES (NEW.order_id, 'INSERT', NEW.amount, statement_timestamp()::timestamp(0));
  RETURN NULL;                      -- AFTER 트리거는 돌려주는 값을 무시함
END;
$$;

DROP TRIGGER IF EXISTS trg_orders_ai ON orders;
CREATE TRIGGER trg_orders_ai
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION trg_orders_ai_fn();
EOF

psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>' -v ON_ERROR_STOP=1 -f pg_06_triggers.sql

-- 트리거만 먼저 테스트 (트랜잭션으로 묶고 되돌림)
BEGIN;
INSERT INTO orders (cust_name, amount) VALUES ('테스트', 1) RETURNING *;
SELECT * FROM order_hist ORDER BY order_id DESC LIMIT 1;
ROLLBACK;
\q

-- ① MySQL 프로시저를 문법만 바꿔 옮긴 것
CREATE OR REPLACE PROCEDURE add_order (p_cust VARCHAR(255), p_amt NUMERIC(38,10))
LANGUAGE plpgsql
AS $$
DECLARE
  v_msg TEXT;
BEGIN
  START TRANSACTION;
  INSERT INTO orders (cust_name, amount) VALUES (p_cust, p_amt);
  COMMIT;
  SELECT CONCAT('주문 성공 : ', p_cust) AS result;
EXCEPTION
  WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS v_msg = MESSAGE_TEXT;
    ROLLBACK;
    SELECT CONCAT('주문 실패 : ', v_msg) AS result;
END;
$$;

CALL add_order('배민', 33000);

-- ② 화면 출력만 RAISE NOTICE로 바꾼 것
CREATE OR REPLACE PROCEDURE add_order (p_cust VARCHAR(255), p_amt NUMERIC(38,10))
LANGUAGE plpgsql
AS $$
DECLARE
  v_msg TEXT;
BEGIN
  START TRANSACTION;
  INSERT INTO orders (cust_name, amount) VALUES (p_cust, p_amt);
  COMMIT;
  RAISE NOTICE '주문 성공 : %', p_cust;
EXCEPTION
  WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS v_msg = MESSAGE_TEXT;
    ROLLBACK;
    RAISE NOTICE '주문 실패 : %', v_msg;
END;
$$;

CALL add_order('배민', 33000);
SELECT COUNT(*) FROM orders;
\q

cat > pg_07_procedures.sql <<'EOF'
-- ===== add_order : START TRANSACTION 지움, COMMIT은 예외처리 블록 밖으로, SELECT 출력은 RAISE NOTICE =====
CREATE OR REPLACE PROCEDURE add_order (p_cust VARCHAR, p_amt NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
  BEGIN                                       -- 안쪽 블록 : 실패하면 여기만 되돌림 (MySQL EXIT HANDLER + ROLLBACK)
    INSERT INTO orders (cust_name, amount) VALUES (p_cust, p_amt);
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE '주문 실패 : %', SQLERRM;
      RETURN;
  END;

  COMMIT;                                     -- 바깥 블록에는 EXCEPTION이 없으므로 가능
  RAISE NOTICE '주문 성공 : %', p_cust;
END;
$$;

-- ===== raise_sal : ROW_COUNT() → GET DIAGNOSTICS =====
CREATE OR REPLACE PROCEDURE raise_sal (p_deptno INT, p_pct NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
  v_cnt INTEGER;
BEGIN
  UPDATE emp_m
     SET sal = sal * (1 + p_pct / 100)
   WHERE deptno = p_deptno;

  GET DIAGNOSTICS v_cnt = ROW_COUNT;
  RAISE NOTICE '%명 급여 인상', v_cnt;
  COMMIT;
END;
$$;

-- ===== send_report : 다른 DB(<REPORT_USER>) → 다른 스키마, CURDATE() → CURRENT_DATE =====
CREATE OR REPLACE PROCEDURE send_report ()
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO <REPORT_USER>.rpt_daily
  SELECT CURRENT_DATE, COUNT(*), SUM(amount)
    FROM orders;
  COMMIT;
END;
$$;
EOF

psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>' -v ON_ERROR_STOP=1 -f pg_07_procedures.sql

psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>' -c "\df add_order"

mysql -t -e "SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema = DATABASE() ORDER BY 1, 2;
SELECT trigger_name, action_timing FROM information_schema.triggers WHERE trigger_schema = DATABASE();
SELECT table_name AS view_name FROM information_schema.views WHERE table_schema = DATABASE();"

psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>'

-- PostgreSQL (psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>')
SELECT CASE prokind WHEN 'p' THEN 'PROCEDURE' ELSE 'FUNCTION' END AS object_type, proname
  FROM pg_proc
 WHERE pronamespace = '<USERNAME>'::regnamespace
 ORDER BY 1, 2;

SELECT tgrelid::regclass AS table_name, tgname, tgenabled
  FROM pg_trigger
 WHERE NOT tgisinternal
 ORDER BY 1, 2;

SELECT viewname FROM pg_views WHERE schemaname = '<USERNAME>';
\q

cat > compare.sql <<'EOF'
SELECT * FROM v_emp_grade ORDER BY empno;
SELECT grade, COUNT(*), SUM(sal) FROM v_emp_grade GROUP BY grade ORDER BY grade;
SELECT COUNT(*), SUM(amount) FROM orders;
SELECT COUNT(*) FROM order_hist;
EOF

echo "===== MySQL ====="
mysql -t < compare.sql
echo "===== PostgreSQL ====="
psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>' -f compare.sql

-- ① 정상 주문
CALL add_order('배민', 33000);

SELECT * FROM orders     ORDER BY order_id DESC LIMIT 1;
SELECT * FROM order_hist ORDER BY order_id DESC LIMIT 1;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM order_hist;

-- ② 실패하는 주문 (고객명 30자 초과)
CALL add_order(repeat('가', 31), 1000);
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM order_hist;

-- ③ 급여 인상 — 0%로 호출해서 값은 바꾸지 않고 동작만 확인
CALL raise_sal(10, 0);

-- ④ 다른 스키마에 리포트 적재
CALL send_report();
SELECT * FROM <REPORT_USER>.rpt_daily;
\q

mysql -t -e "CALL raise_sal(10, 0)"

UPDATE emp_m SET sal = sal * 1 WHERE deptno = 10;
-- Query OK, 0 rows affected
-- Rows matched: 2  Changed: 0  Warnings: 0

ls -l *.sql *.sh data/