Perl连接Oracle SQL时间字段显示空白求助（需MM/DD/YYYY HH:MI:SS AM格式）

问题说明 #

运行以下Perl脚本时，EFFECTIVE_TIME和SUBMIT_TIME对应字段显示空白，需要将这两个字段以MM/DD/YYYY HH:MI:SS AM格式展示，求解决思路。

原脚本 #

#!/usr/bin/perl
use DBI;
unlink("D:\\Perl32\\scripts\\NEW.html");

my $host = '${Node.Caption}';
my $user = '${USER}';
my $pwd = '${PASSWORD}';

my $driver = "Oracle";
$dsnslam = "DBI:$driver:host=$host;service_name=PROD;port=1727";
$dbhslam = DBI->connect($dsnslam, "$user", "$pwd") || die "connect failed: ";
$sthslam = $dbhslam->prepare("select mti.FACILITY, TO_CHAR(MAX(td.SUBMIT_TIME), 'MM/DD/YYYY HH:MI:SS AM') AS MAX_SUBMIT_TIME, TO_CHAR(MAX(td.SUBMIT_TIME), 'MM/DD/YYYY HH:MI:SS AM') AS MAX_EFFECTIVE_TIME
from fl.erip_data td, fl.managed_eripid mti
where td.insert_TIME>sysdate-1
and td.ERIP_ID=mti.ERIP_ID and td.PLAZA_ID=mti.PLAZA
group by mti.FACILITY");

$sthslam->execute;
$msg = "Up";
$Count = 0;
$Output = "";
$Temp="";
$tbl = "<TABLE border=1  bordercolor=orange cellspacing=0 cellpadding=1>";
$tblhd = "<TR><TH>FACILITY</TH><TH align=center>MAX(TD.SUBMIT_TIME)</TH><TH align=center>MAX(TD.EFFECTIVE_TIME)</TH>";

while(  my $ref = $sthslam->fetchrow_hashref() ) {
        $Count++;
        $Temp = "$Temp<TR><TD align=left rowspan=1 valign=top height=10>$ref->{'FACILITY'}</TD><TD align=left rowspan=1 valign=top height=10>$ref->{'MAX(TD.SUBMIT_TIME)'}</TD><TD align=center rowspan=1 valign=top height=10>$ref->{'MAX(TD.EFFECTIVE_TIME)'}</TD>";
        $Output = "$Output$Temp";
        $Temp="";
}
$dbhslam->disconnect;
$Output="$tbl$tblhd$Output</TABLE>";
my $filename1 = 'D:\\Perl32\\Scripts\\NEW.html';
open(my $fh1, '>', $filename1) or die "Could not open file '$filename1' $!";
print $fh1 "$Output";
close $fh1;

if ( $Count > 0 )
{
$msg = $Output;
}
elsif ( ($Count == 0) || (!defined $Count) )
{
    $Count = 0;
    $msg="NO ROWS RETURNED";
}

print "\nMessage: $msg";
print "\nStatistic: $Count";

核心解决思路 #

1. 修正SQL语句的逻辑错误 #

• 字段混淆问题：当前SQL中MAX_EFFECTIVE_TIME的取值错误指向了MAX(td.SUBMIT_TIME)，而非MAX(td.EFFECTIVE_TIME)，导致EFFECTIVE_TIME的真实值未被查询。修正后的SQL：

  select 
      mti.FACILITY, 
      TO_CHAR(MAX(td.SUBMIT_TIME), 'MM/DD/YYYY HH:MI:SS AM') AS MAX_SUBMIT_TIME, 
      TO_CHAR(MAX(td.EFFECTIVE_TIME), 'MM/DD/YYYY HH:MI:SS AM') AS MAX_EFFECTIVE_TIME
  from fl.erip_data td, fl.managed_eripid mti
  where td.insert_TIME>sysdate-1
    and td.ERIP_ID=mti.ERIP_ID 
    and td.PLAZA_ID=mti.PLAZA
  group by mti.FACILITY
  

• 处理NULL值：如果分组后无符合条件的记录，MAX聚合结果为NULL，TO_CHAR会返回空白。可以用NVL函数替换空白为提示文本：

  NVL(TO_CHAR(MAX(td.SUBMIT_TIME), 'MM/DD/YYYY HH:MI:SS AM'), '无有效数据') AS MAX_SUBMIT_TIME
  

2. 修正Perl哈希键的匹配错误 #

脚本中fetchrow_hashref返回的哈希键是SQL定义的别名MAX_SUBMIT_TIME和MAX_EFFECTIVE_TIME，但代码中误用了MAX(TD.SUBMIT_TIME)和MAX(TD.EFFECTIVE_TIME)作为键名，导致无法取到值。修改循环内代码：

$Temp = "$Temp<TR><TD align=left rowspan=1 valign=top height=10>$ref->{'FACILITY'}</TD><TD align=left rowspan=1 valign=top height=10>$ref->{'MAX_SUBMIT_TIME'}</TD><TD align=center rowspan=1 valign=top height=10>$ref->{'MAX_EFFECTIVE_TIME'}</TD>";

同时表头也要对应修改，保持数据与表头一致：

$tblhd = "<TR><TH>FACILITY</TH><TH align=center>MAX_SUBMIT_TIME</TH><TH align=center>MAX_EFFECTIVE_TIME</TH>";

3. 调试与验证步骤 #

• 先在Oracle客户端（如SQL*Plus）中单独执行修正后的SQL，确认时间字段有值且格式正确，排除数据库层面问题。
• 在Perl脚本中添加调试代码，打印哈希实际内容，验证键值是否正确：

  use Data::Dumper;
  while(  my $ref = $sthslam->fetchrow_hashref() ) {
      print Dumper($ref); # 打印哈希内容，确认键值对
      # 剩余代码...
  }
  

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