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

首先建一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 completed

SQL> 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 completed

SQL> 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 completed

SQL> 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 completed
SQL> select pkg_test.get_id from dual group by pkg_test.get_id;
    GET_ID
----------
         1

SQL> 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          4

SQL> /
    GET_ID         C2         C3
---------- ---------- ----------
         3          4          5

SQL> 

部分情况下可以用DETERMINISTIC优化

SQL> exec pkg_test.SETID(0);
PL/SQL procedure successfully completed

SQL> select pkg_test.get_id from dual connect by level <=5;
    GET_ID
----------
         1
         2
         3
         4
         5
5 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
         1
5 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          3
1 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          6
1 row selected

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

SQL> exec pkg_test.SETID(0);
PL/SQL procedure successfully completed

SQL> 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          1
1 row selected