Oracle 實用 SQL 技巧集錦：多表插入、字符串截取、重複檢測、緩存參數調優與動態 SQL 構建

多表插入：單次掃描多目標寫入

條件多表插入 (INSERT ALL)

INSERT ALL
  WHEN deptno = 10 THEN INTO dept10 SELECT *
  WHEN deptno = 20 THEN INTO dept20 SELECT *
  WHEN deptno = 30 THEN INTO dept30 SELECT *
  WHEN job = 'CLERK' THEN INTO clerk_emp SELECT *
  ELSE INTO other_emp SELECT *
SELECT * FROM emp;

首次匹配插入 (INSERT FIRST) - 互斥分支

INSERT FIRST
  WHEN sal > 5000 THEN INTO exec_emp SELECT *
  WHEN deptno = 10 THEN INTO dept10_emp SELECT *
  ELSE INTO other_emp SELECT *
SELECT * FROM emp;

差異：ALL 滿足所有條件均插入 (一行可進多表)；FIRST 僅進入第一個匹配分支。

字符串截取與解析

SUBSTR 基礎用法

SELECT SUBSTR(item_name, 1, 2) FROM product_market_price;  -- 前2字符
SELECT SUBSTR('ho鮮紅的鮮花', 1, 3) FROM dual;  -- 'ho鮮' (按字符計數)

進階解析：分隔符提取

-- 格式：'PROD_12345_CAT_ELECTRONICS'
SELECT 
    SUBSTR(str, 1, INSTR(str, '_') - 1) AS prefix,
    SUBSTR(str, INSTR(str, '_') + 1, INSTR(str, '_', 1, 2) - INSTR(str, '_') - 1) AS id,
    SUBSTR(str, INSTR(str, '_', 1, 3) + 1) AS suffix
FROM dual;

重複數據檢測與處理

檢測重複 HIREDATE (含時分秒)

-- 方法 1: 子查詢 + HAVING
SELECT * FROM scott.emp 
WHERE hiredate IN (
    SELECT hiredate FROM scott.emp 
    GROUP BY hiredate HAVING COUNT(*) > 1
);

-- 方法 2: 關聯子查詢 (性能更好)
SELECT t2.* FROM scott.emp t2,
    (SELECT hiredate, COUNT(*) cnt FROM scott.emp 
     GROUP BY hiredate HAVING COUNT(*) > 1) t1
WHERE t2.hiredate = t1.hiredate;

-- 含時分秒時：按日期分組
SELECT * FROM scott.emp 
WHERE TRUNC(hiredate) IN (
    SELECT TRUNC(hiredate) FROM scott.emp 
    GROUP BY TRUNC(hiredate) HAVING COUNT(*) > 1
);

SGA 緩存參數動態調優

核心參數與推薦值 (以 4GB 內存為例)

調整生效流程

-- 1. 動態調整 (即時生效，重啟後失效)
ALTER SYSTEM SET DB_CACHE_SIZE = 2G SCOPE=MEMORY;

-- 2. 持久化調整 (重啟生效)
ALTER SYSTEM SET DB_CACHE_SIZE = 2G SCOPE=SPFILE;

-- 3. 兩者兼備 (推薦)
ALTER SYSTEM SET DB_CACHE_SIZE = 2G SCOPE=BOTH;

-- 4. 驗證
SHOW PARAMETER db_cache;
SELECT component, current_size/1024/1024 MB FROM v$sga_dynamic_components;

動態 SQL 構建模式

PL/SQL 執行動態 SQL

DECLARE
    v_sql   VARCHAR2(4000);
    v_cnt   NUMBER;
BEGIN
    v_sql := 'SELECT COUNT(*) FROM ' || p_table_name || ' WHERE status = :1';
    EXECUTE IMMEDIATE v_sql INTO v_cnt USING 'ACTIVE';
    DBMS_OUTPUT.PUT_LINE('Active count: ' || v_cnt);
END;

DBMS_SQL 完整流程 (複雜場景)

DECLARE
    c       INTEGER;
    v_cnt   NUMBER;
BEGIN
    c := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(c, 'SELECT COUNT(*) FROM ' || p_table, DBMS_SQL.NATIVE);
    DBMS_SQL.DEFINE_COLUMN(c, 1, v_cnt);
    DBMS_SQL.EXECUTE(c);
    IF DBMS_SQL.FETCH_ROWS(c) > 0 THEN
        DBMS_SQL.COLUMN_VALUE(c, 1, v_cnt);
    END IF;
    DBMS_SQL.CLOSE_CURSOR(c);
END;

綜合實戰：批量歸檔腳本模板

CREATE OR REPLACE PROCEDURE archive_orders(p_months_old IN NUMBER := 12) AS
    v_sql   VARCHAR2(4000);
    v_cnt   NUMBER;
BEGIN
    -- 1. 動態構建歸檔表名
    v_sql := 'CREATE TABLE orders_arc_' || TO_CHAR(SYSDATE, 'YYYYMM') || 
             ' AS SELECT * FROM orders WHERE order_dt < ADD_MONTHS(TRUNC(SYSDATE), -' || p_months_old || ')';
    EXECUTE IMMEDIATE v_sql;
    
    -- 2. 刪除歸檔數據 (分批提交避免 UNDO 爆滿)
    v_sql := 'DELETE FROM orders WHERE order_dt < ADD_MONTHS(TRUNC(SYSDATE), -' || p_months_old || ')';
    LOOP
        EXECUTE IMMEDIATE v_sql || ' AND ROWNUM <= 10000';
        v_cnt := SQL%ROWCOUNT;
        COMMIT;
        EXIT WHEN v_cnt = 0;
    END LOOP;
    
    -- 3. 索引重建
    EXECUTE IMMEDIATE 'ALTER INDEX idx_orders_dt REBUILD ONLINE';
    
    DBMS_OUTPUT.PUT_LINE('Archive completed for ' || p_months_old || ' months ago.');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END archive_orders;