Oracle 常用函數與進階技巧實戰速查

高頻函數與實用模式集錦

一、管道化表函數：高效返回集合

-- 1. 定義物件類型與集合類型
CREATE TYPE row_typedepluju AS OBJECT (rootid NUMBER(19), projectid NUMBER(19));
CREATE TYPE table_typedepluju AS TABLE OF row_typedepluju;

-- 2. 管道化函數
CREATE OR REPLACE FUNCTION getlujudepinfobyaccount (useraccount IN VARCHAR2)
RETURN table_typedepluju PIPELINED AS
  v row_typedepluju;
BEGIN
  FOR myrowdep IN (
    SELECT t.id AS rootid, pinfo.id AS projectid
    FROM base_security_department t
    LEFT JOIN pbs_projectinfo pinfo ON pinfo.constructiondep_id = t.id
    WHERE t.useflag = 1 AND t.grade IN (60,130)
    START WITH t.id = (SELECT a.departmentid FROM base_security_userinfo a WHERE a.account = useraccount)
    CONNECT BY PRIOR t.parentid = t.id
  ) LOOP
    v := row_typedepluju(myrowdep.rootid, myrowdep.projectid);
    PIPE ROW (v);
  END LOOP;
  RETURN;
END;

優勢：流式返回，無需在記憶體中構建整個集合，適合大結果集 ETL/報表。

二、MINUS 集合差集：找出關聯表中不存在的值

INSERT INTO base_security_deptproject (departmentid, projectid)
SELECT v.DEPARTMENTID, v.PROJECTID FROM (
  -- 逗號分隔字串拆行
  WITH temp0 AS (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 20)
  SELECT g.id AS DEPARTMENTID,
         SUBSTR(g.PROJECTIDS, INSTR(g.PROJECTIDS, ',', 1, tv.lv) + 1,
                INSTR(g.PROJECTIDS, ',', 1, tv.lv + 1) - INSTR(g.PROJECTIDS, ',', 1, tv.lv) - 1) AS PROJECTID
  FROM BASE_SECURITY_DEPARTMENT g
  JOIN temp0 tv ON tv.lv <= LENGTH(g.PROJECTIDS) - LENGTH(REPLACE(g.PROJECTIDS, ',')) 
  WHERE g.GRADE = '145' AND g.USEFLAG = '1' AND g.PROJECTIDS IS NOT NULL
) v
WHERE v.PROJECTID IS NOT NULL
MINUS
SELECT t.DEPARTMENTID, TO_CHAR(t.PROJECTID) FROM BASE_SECURITY_DEPTPROJECT t;

三、層次查詢：START WITH ... CONNECT BY

-- 展現整棵組織樹
SELECT * FROM base_security_department t
CONNECT BY PRIOR t.parentid = t.id
START WITH t.id = 2400;

四、字串截取 SUBSTR 進階

-- 取編碼最後兩位並 +1 生成新編碼
SELECT DECODE(MAX(SUBSTR(t.bscode, LENGTH(t.bscode)-1, 2)), NULL, 0,
       MAX(SUBSTR(t.bscode, LENGTH(t.bscode)-1, 2)) + 1) AS codenum
FROM base_security_department t
WHERE t.useflag = 1 AND t.bscode LIKE 'XKHJ01SG07SY__';

五、序列 NEXTVAL 特性

• 每次調用 seq.NEXTVAL 必然遞增，即使語句回滾、事務失敗，序列值也不回退 (Gap 是正常現象)。
• seq.CURRVAL 僅在當前 Session 已調用過 NEXTVAL 後可用。

六、常用內建函數速查表