function在查询语句里的运行次数

来源：互联网发布：c语言switch语句用法编辑：程序博客网时间：2024/06/10 21:37

首先建一package，我们后面要调用这个package，计数用

CREATE OR REPLACE PACKAGE PKG_TEST IS  ID NUMBER;  FUNCTION GET_ID RETURN NUMBER;  PROCEDURE SETID(V_ID NUMBER);END PKG_TEST;/CREATE OR REPLACE PACKAGE BODY PKG_TEST IS  FUNCTION GET_ID RETURN NUMBER IS  BEGIN    ID := ID + 1;    RETURN ID;  END;  PROCEDURE SETID(V_ID NUMBER) IS  BEGIN    ID := V_ID;  END;  END;/

首先直接查询

SQL> exec pkg_test.SETID(0);PL/SQL procedure successfully completedSQL> select pkg_test.get_id from dual group by pkg_test.get_id;    GET_ID----------         1

此时函数执行了一次。

SQL> exec pkg_test.SETID(0);PL/SQL procedure successfully completedSQL> select pkg_test.get_id from dual where pkg_test.get_id >=1 group by pkg_test.get_id;    GET_ID----------         2

此时函数执行了两次。

大家都知道用函数会很慢，有没有办法让它运行一次呢？

当然可以。

SQL> exec pkg_test.SETID(0);PL/SQL procedure successfully completedSQL> select id from (select rownum as sn,pkg_test.get_id as id from dual) where id >=0 group by id;        ID----------         1

如果是select里，即使引用了多次，也只会执行一次

SQL> exec pkg_test.SETID(0);PL/SQL procedure successfully completedSQL> select pkg_test.get_id from dual group by pkg_test.get_id;    GET_ID----------         1SQL> select pkg_test.get_id,pkg_test.get_id+1 as c2,pkg_test.get_id+2 as c3 from dual group by pkg_test.get_id;    GET_ID         C2         C3---------- ---------- ----------         2          3          4SQL> /    GET_ID         C2         C3---------- ---------- ----------         3          4          5SQL>

部分情况下可以用DETERMINISTIC优化

SQL> exec pkg_test.SETID(0);PL/SQL procedure successfully completedSQL> select pkg_test.get_id from dual connect by level <=5;    GET_ID----------         1         2         3         4         55 rows selected

加 DETERMINISTIC 后

CREATE OR REPLACE PACKAGE pkg_test IS  id NUMBER;  FUNCTION get_id RETURN NUMBER DETERMINISTIC;  PROCEDURE setid(v_id NUMBER);END pkg_test;/CREATE OR REPLACE PACKAGE BODY pkg_test IS  FUNCTION get_id RETURN NUMBER DETERMINISTIC AS    BEGIN    id := id + 1;    RETURN id;  END;  PROCEDURE setid(v_id NUMBER) IS  BEGIN    id := v_id;  END;END;/

SQL> select pkg_test.get_id from dual connect by level <=5;    GET_ID----------         1         1         1         1         15 rows selected

另外，函数有个小问题：当不使用group by 时一行多次引用，会执行多次

SQL> SELECT pkg_test.get_id AS a, pkg_test.get_id AS b, pkg_test.get_id AS c from dual;         A          B          C---------- ---------- ----------         1          2          31 row selected

即使当作表达式的一部分也不行

SQL> SELECT pkg_test.get_id + 0 AS a, pkg_test.get_id + 0 AS b, pkg_test.get_id + 0 AS c from dual;         A          B          C---------- ---------- ----------         4          5          61 row selected

对于这种问题同样可以采用前面的方法，嵌套一次查询，只是需要多用一个小技巧(rownum >=1)

SQL> exec pkg_test.SETID(0);PL/SQL procedure successfully completedSQL> SELECT id AS a, id AS b, id AS c from (SELECT pkg_test.get_id as id from dual where rownum >=1);         A          B          C---------- ---------- ----------         1          1          11 row selected

1 0