作者：手机用户2502879747 | 来源：互联网 | 2023-09-10 14:40
带从句没有什么神奇的。如果将双函数调用添加到直线选择，则会得到相同的行为：SQLSELECTfld1FROMt12INNERJOINt2ONt1.id_pkt2.id_pk3
嗨，
我似乎无意中发现了我无法解释的先知行为，所以我来到了一个神奇的地方，叫汤姆寻求指引。
我创建了以下示例，以便在重现问题的同时尽可能缩短该示例：
2个表， 2个函数和数据...
CREATE TABLE t1 (id_pk INTEGER PRIMARY KEY, fld1 VARCHAR2(10));
CREATE TABLE t2 (id_pk INTEGER PRIMARY KEY, fld2 VARCHAR2(3));
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
BEGIN
dbms_output.put_line('fn2: ' || vfld);
RETURN 0;
END fn2;
INSERT INTO t1 VALUES (1, 'A0000001');
INSERT INTO t1 VALUES (2, 'A0000002');
INSERT INTO t1 VALUES (3, 'A0000003');
INSERT INTO t1 VALUES (4, 'A0000004');
INSERT INTO t2 VALUES (1, 'AAA');
INSERT INTO t2 VALUES (2, 'AAA');
INSERT INTO t2 VALUES (3, 'BBB');
INSERT INTO t2 VALUES (4, 'BBB');
正在执行以下查询：
SELECT fld1 FROM t1
INNER JOIN t2 ON t1.id_pk = t2.id_pk
WHERE SUBSTR(fld1,1,1)='A'
AND t2.fld2 = 'AAA'
A0000001
A0000002
这些是我在此查询中所期望的记录。
当我使用带有子句的函数时，我似乎也得到了正确的结果，没有记录：
WITH w_test AS (
SELECT fld1 FROM t1
INNER JOIN t2 ON t1.id_pk = t2.id_pk
WHERE SUBSTR(fld1,1,1)='A'
AND t2.fld2 = 'AAA')
SELECT fn1(fld1) FROM w_test WHERE fn2(fn1(fld1))=1;
no rows selected
但是...如果我看一下dbms_输出，我看到的是非常不同的东西：
fn1: A0000001
fn2: A0000001
fn1: A0000002
fn2: A0000002
fn1: A0000003
fn2: A0000003
fn1: A0000004
fn2: A0000004
fn1: A0000001
fn2: A0000001
fn1: A0000002
fn2: A0000002
fn1: A0000003
fn2: A0000003
fn1: A0000004
fn2: A0000004
t1表中的每个记录都由两个函数处理，即使我们在t2上有一个内连接，并且指定t2. fld2的where子句应该是'AAA'。
你能说明为什么会发生这种事吗？
我们有一个非常奇怪的20年的主机在使用中它每天都会被复制到Oracle数据库中。在其中一个ID字段中，如果ID以数字开头，则整个ID是数字的，并且新记录应该只加上1。如果它以字母开头，则应用一组特定的规则。在长镜头里不是很理想，但我无法控制那里。
现在，这对上面的例子有什么影响呢？
如果我在函数和数据中稍作改动：
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');
首先运行简单选择部分：
SELECT fld1 FROM t1
INNER JOIN t2 ON t1.id_pk = t2.id_pk
WHERE SUBSTR(fld1,1,1)='1'
AND t2.fld2 = 'AAA'
10000001
10000002
现在，在将其与函数一起使用时，会发生以下情况：
WITH w_test AS (
SELECT fld1 FROM t1
INNER JOIN t2 ON t1.id_pk = t2.id_pk
WHERE SUBSTR(fld1,1,1)='1'
AND t2.fld2 = 'AAA')
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: in "FN2", line 6
当然，这是因为t1中的所有行都由两个函数处理。
这对我来说就是魔法。我不懂魔法。请教我魔法。
致以亲切的问候，
马丁
带从句没有什么神奇的。如果将双函数调用添加到直线选择，则会得到相同的行为：
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的操作。
京公网安备 11010802041100号