PHP页面执行Oracle SQL返回0 客户端运行正常故障排查

算力阿峰

2026-8-27

问题描述

PHP页面执行Oracle查询返回结果为空，但同一段SQL在Oracle客户端执行可正常返回结果，已尝试调整SQL末尾的逗号、分号写法未解决，相关代码如下：

<?php
$database = new Connection();
$db = $database->openConnection();
$sql = "SELECT c.total,b.full,a.empty
from
(
SELECT SUM(CASE WHEN depth='1' THEN to_char(nvl('36',0)*simplecount) 
WHEN depth='2' THEN to_char(nvl('52',0)*simplecount)WHEN depth='3' THEN 
to_char(nvl('68',0)*simplecount) 
WHEN depth='4' THEN to_char(nvl('84',0)*simplecount) WHEN depth='5' THEN 
to_char(nvl('110',0)*simplecount) WHEN depth='6' THEN 
to_char(nvl('116',0)*simplecount)
WHEN(depth)='7' THEN to_char(nvl('132',0)*simplecount) WHEN(depth)='8' 
THEN to_char(nvl('148',0)*simplecount) WHEN(depth)='9' THEN 
to_char(nvl('164',0)*simplecount) 
WHEN(depth)='10' THEN to_char(nvl('180',0)*simplecount) WHEN(depth)='11' 
THEN to_char(nvl('196',0)*simplecount) WHEN(depth)='12' THEN 
to_char(nvl('212',0)*simplecount) 
WHEN(depth)='13' THEN to_char(nvl('228',0)*simplecount) WHEN(depth)='14' 
THEN to_char(nvl('244',0)*simplecount) WHEN(depth)='15' THEN 
to_char(nvl('260',0)*simplecount)
WHEN(depth)='16' THEN to_char(nvl('276',0)*simplecount) WHEN(depth)='17' 
THEN to_char(nvl('292',0)*simplecount) WHEN(depth)='18' THEN 
to_char(nvl('308',0)*simplecount)
WHEN(depth)='19' THEN to_char(nvl('324',0)*simplecount) WHEN(depth)='20' 
THEN to_char(nvl('340',0)*simplecount) WHEN(depth)='21' THEN 
to_char(nvl('356',0)*simplecount)
WHEN(depth)='22' THEN to_char(nvl('372',0)*simplecount)
ELSE '372' END)total
FROM SIMPLE_VIEW
WHERE FACILITY='W03')c,

(
SELECT SUM(CASE WHEN depth='1' THEN to_char(nvl('36',0)*simplecount) 
WHEN depth='2' THEN to_char(nvl('52',0)*simplecount)WHEN depth='3' THEN 
to_char(nvl('68',0)*simplecount) 
WHEN depth='4' THEN to_char(nvl('84',0)*simplecount) WHEN depth='5' THEN 
to_char(nvl('110',0)*simplecount) WHEN depth='6' THEN 
to_char(nvl('116',0)*simplecount)
WHEN(depth)='7' THEN to_char(nvl('132',0)*simplecount) WHEN(depth)='8' 
THEN to_char(nvl('148',0)*simplecount) WHEN(depth)='9' THEN 
to_char(nvl('164',0)*simplecount) 
WHEN(depth)='10' THEN to_char(nvl('180',0)*simplecount) WHEN(depth)='11' 
THEN to_char(nvl('196',0)*simplecount) WHEN(depth)='12' THEN 
to_char(nvl('212',0)*simplecount) 
WHEN(depth)='13' THEN to_char(nvl('228',0)*simplecount) WHEN(depth)='14' 
THEN to_char(nvl('244',0)*simplecount) WHEN(depth)='15' THEN 
to_char(nvl('260',0)*simplecount)
WHEN(depth)='16' THEN to_char(nvl('276',0)*simplecount) WHEN(depth)='17' 
THEN to_char(nvl('292',0)*simplecount) WHEN(depth)='18' THEN 
to_char(nvl('308',0)*simplecount)
WHEN(depth)='19' THEN to_char(nvl('324',0)*simplecount) WHEN(depth)='20' 
THEN to_char(nvl('340',0)*simplecount) WHEN(depth)='21' THEN 
to_char(nvl('356',0)*simplecount)
WHEN(depth)='22' THEN to_char(nvl('372',0)*simplecount)
ELSE '372' END)full
FROM SIMPLE_VIEW
WHERE FACILITY='W03'
AND nullcount='0')b,

(
Select SUM(CASE WHEN depth='1' THEN to_char(nvl('36',0)*simplecount) 
WHEN depth='2' THEN to_char(nvl('52',0)*simplecount)WHEN depth='3' THEN 
to_char(nvl('68',0)*simplecount) 
WHEN depth='4' THEN to_char(nvl('84',0)*simplecount) WHEN depth='5' THEN 
to_char(nvl('110',0)*simplecount) WHEN depth='6' THEN 
to_char(nvl('116',0)*simplecount)
WHEN depth='7' THEN to_char(nvl('132',0)*simplecount) WHEN(depth)='8' 
THEN to_char(nvl('148',0)*simplecount) WHEN(depth)='9' THEN 
to_char(nvl('164',0)*simplecount) 
WHEN(depth)='10' THEN to_char(nvl('180',0)*simplecount) WHEN(depth)='11' 
THEN to_char(nvl('196',0)*simplecount) WHEN(depth)='12' THEN 
to_char(nvl('212',0)*simplecount) 
WHEN(depth)='13' THEN to_char(nvl('228',0)*simplecount) WHEN(depth)='14' 
THEN to_char(nvl('244',0)*simplecount) WHEN(depth)='15' THEN 
to_char(nvl('260',0)*simplecount)
WHEN(depth)='16' THEN to_char(nvl('276',0)*simplecount) WHEN(depth)='17' 
THEN to_char(nvl('292',0)*simplecount) WHEN(depth)='18' THEN 
to_char(nvl('308',0)*simplecount)
WHEN(depth)='19' THEN to_char(nvl('324',0)*simplecount) WHEN(depth)='20' 
THEN to_char(nvl('340',0)*simplecount) WHEN(depth)='21' THEN 
to_char(nvl('356',0)*simplecount)
WHEN(depth)='22' THEN to_char(nvl('372',0)*simplecount)
ELSE '372' END)empty
FROM SIMPLE_VIEW
WHERE FACILITY='W03'
  AND nullcount<>'0')a;
";

$stmt = $db->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
foreach ($result as $row) {
?>
<tr class="gradeX">
<td><?=number_format($row["c.total"],0,'.',',') ?></td>
<td><?=number_format($row["b.full"],0,'.',',') ?></td>
<td><?=number_format($row["a.empty"],0,'.',',') ?></td>
</tr>
<?php
} 
$database->closeConnection();
?>

排查思路与修复方案

核心问题是取数字段名写错：Oracle返回结果集的列名不会携带子查询别名前缀，默认返回大写的列名TOTAL、FULL、EMPTY，用$row["c.total"]、$row["b.full"]、$row["a.empty"]取值自然拿不到数据，看起来就像查询返回0结果。把取值的键名改成大写的列名即可，比如$row["TOTAL"]。
去掉SQL里多余的类型转换：在SUM聚合函数里套to_char把计算结果转成字符串既没必要，还可能因为PHP和Oracle的字符集配置差异导致读取异常，直接返回数值类型即可。
优化SQL写法：当前SQL三次扫描SIMPLE_VIEW做聚合，性能很差，可以直接用条件聚合一次查询出三个值，避免多表连接的潜在问题，优化后SQL参考如下：

SELECT 
    SUM(/* 原total的计算逻辑，去掉to_char */) AS TOTAL,
    SUM(CASE WHEN nullcount='0' THEN /* 原full的计算逻辑，去掉to_char */ ELSE 0 END) AS FULL,
    SUM(CASE WHEN nullcount<>'0' THEN /* 原empty的计算逻辑，去掉to_char */ ELSE 0 END) AS EMPTY
FROM SIMPLE_VIEW
WHERE FACILITY='W03'

调试时不要靠猜：执行完语句后先打印$stmt->errorInfo()确认有没有数据库报错，再直接var_dump($result)看实际返回的结果结构和字段名，能快速定位问题。

内容的提问来源于stack exchange，提问作者FFUD