ORACLE SQL性能优化系列(八)

16.       通过内部函数提高SQL效率.
     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; (译者按: 经常在论坛中看到如 ’能不能用一个SQL写出….’ 的贴子, 殊不知复杂的SQL往往牺牲了执行效率. 能够把握上面的运用函数解决问题的方法在实际工作中是非常有意义的)