Oracle SQL 效能優化實戰（八）：內部函數替代表連接提升效能

案例：以 PL/SQL 函數取代多表 JOIN

原查詢三表連接並 GROUP BY：

SELECT H.EMPNO, E.ENAME, H.HIST_TYPE, T.TYPE_DESC, COUNT(*)
FROM HISTORY_TYPE T, EMP E, EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO
  AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO, E.ENAME, H.HIST_TYPE, T.TYPE_DESC;

優化：建立查詢函數

FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2 AS
  TDESC VARCHAR2(30);
  CURSOR C1 IS SELECT TYPE_DESC FROM HISTORY_TYPE WHERE HIST_TYPE = TYP;
BEGIN
  OPEN C1; FETCH C1 INTO TDESC; CLOSE C1;
  RETURN NVL(TDESC,'?');
END;

FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2 AS
  ENAME VARCHAR2(30);
  CURSOR C1 IS SELECT ENAME FROM EMP WHERE EMPNO = EMP;
BEGIN
  OPEN C1; FETCH C1 INTO ENAME; CLOSE C1;
  RETURN NVL(ENAME,'?');
END;

重寫查詢

SELECT H.EMPNO, LOOKUP_EMP(H.EMPNO),
       H.HIST_TYPE, LOOKUP_HIST_TYPE(H.HIST_TYPE), COUNT(*)
FROM EMP_HISTORY H
GROUP BY H.EMPNO, H.HIST_TYPE;

優點：消除多表 JOIN 與 GROUP BY，邏輯封裝於函數，實務中極具參考價值。複雜 SQL 常犧牲效能，函數封裝是平衡可讀與效能的良策。