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.EMPNOAND H.HIST_TYPE = T.HIST_TYPEGROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
通過調用下面的函數可以提高效率.
FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2AS 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 HGROUP BY H.EMPNO , H.HIST_TYPE;(譯者按: 經常在論壇中看到如 ’能不能用一個SQL寫出….’ 的貼子, 殊不知復雜的SQL往往犧牲了執行效率. 能夠把握上面的運用函數解決問題的方法在實際工作中是非常有意義的)
