分类: Oracle
2008-05-23 20:50:38
来源: |
|
16. 通过内部函数SQL效率. 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;
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
CURSOR C1 IS SELECT ENAME FROM EMP WHERE EMPNO=EMP; BEGIN OPEN C1; FETCH C1 INTO ENAME; CLOSE C1; RETURN (NVL(ENAME,’?’)); END;
GROUP BY H.EMPNO , H.HIST_TYPE; (译者按: 经常在中看到如 ’能不能用一个SQL写出….’ 的贴子, 殊不知复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的在实际工作中是非常有意义的) |