Oracle在where子句中使用with子句和双函数会导致处理未联接的行

SQL> SELECT fld1 FROM t1
2 INNER JOIN t2 ON t1.id_pk = t2.id_pk
3 WHERE SUBSTR(fld1,1,1)='A'
4 AND t2.fld2 = 'AAA'
5 and fn2(fn1(fld1))=1;
no rows selected
fn1: A0000001
fn2: A0000001
fn1: A0000002
fn2: A0000002
fn1: A0000003
fn2: A0000003
fn1: A0000004
fn2: A0000004

通过查看执行计划，最容易理解这一点：

SELECT /*+ gather_plan_statistics */fld1 FROM t1
INNER JOIN t2 ON t1.id_pk = t2.id_pk
WHERE SUBSTR(fld1,1,1)='A'
AND t2.fld2 = 'AAA'
and fn2(fn1(fld1))=1;

select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +PREDICATE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ gather_plan_statistics */fld1 FROM t1 INNER JOIN t2 ON
t1.id_pk = t2.id_pk WHERE SUBSTR(fld1,1,1)='A' AND t2.fld2
= 'AAA' and fn2(fn1(fld1))=1

Plan hash value: 2987537596

----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
|* 3 | TABLE ACCESS FULL | T1 |
|* 4 | INDEX UNIQUE SCAN | SYS_C006046 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T2 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter((SUBSTR("T1"."FLD1",1,1)='A' AND
"FN2"("FN1"("T1"."FLD1"))=1))
4 - access("T1"."ID_PK"="T2"."ID_PK")
5 - filter("T2"."FLD2"='AAA')

您可以在步骤3中看到，它对T1执行全表扫描。并应用：

fn2(fn1(fld1))=1
此时的谓词。
T1一共有四排。调用FN1和FN2 ，分别提供8个dbms_输出行。这就是我们所看到的。这就是为什么您在第二个示例中得到值错误的原因。Oracle首先尝试将t1中的每一行转换为数字。但它不能，因为有的里面有字母！
您可以通过使with子句中的查询不可合并来克服这个问题。例如，通过向其添加rownum ：

CREATE OR REPLACE FUNCTION fn1 (vfld VARCHAR2) RETURN VARCHAR2
AS
BEGIN
dbms_output.put_line('fn1: ' || vfld);
RETURN vfld;
END fn1;
/
CREATE OR REPLACE FUNCTION fn2 (vfld VARCHAR2) RETURN INTEGER
AS
iret INTEGER;
BEGIN
dbms_output.put_line('fn2: ' || vfld);
iret := TO_NUMBER(vfld);
RETURN iret;
END fn2;
/
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;
INSERT INTO t1 VALUES (1, '10000001');
INSERT INTO t1 VALUES (2, '10000002');
INSERT INTO t1 VALUES (3, '1A000003');
INSERT INTO t1 VALUES (4, '1A000004');
INSERT INTO t2 VALUES (1, 'AAA');
INSERT INTO t2 VALUES (2, 'AAA');
INSERT INTO t2 VALUES (3, 'BBB');
INSERT INTO t2 VALUES (4, 'BBB');
SQL> WITH w_test AS (
2 SELECT fld1 FROM t1
3 INNER JOIN t2 ON t1.id_pk = t2.id_pk
4 WHERE SUBSTR(fld1,1,1)='1'
5 AND t2.fld2 = 'AAA')
6 SELECT fn1(fld1) FROM w_test WHERE fn2(fn1(fld1))=1;
SELECT fn1(fld1) FROM w_test WHERE fn2(fn1(fld1))=1
*
ERROR at line 6:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "CHRIS.FN2", line 6
fn1: 10000001
fn2: 10000001
fn1: 10000002
fn2: 10000002
fn1: 1A000003
fn2: 1A000003
SQL>
SQL> WITH w_test AS (
2 SELECT fld1, rownum FROM t1
3 INNER JOIN t2 ON t1.id_pk = t2.id_pk
4 WHERE SUBSTR(fld1,1,1)='1'
5 AND t2.fld2 = 'AAA')
6 SELECT fn1(fld1) FROM w_test WHERE fn2(fn1(fld1))=1;
no rows selected
fn1: 10000001
fn2: 10000001
fn1: 10000002
fn2: 10000002

这样可以确保在将中的表与连接后， Oracle执行fn2(fn1( fld1))=1的操作。