使用Perl DBI获取所有记录

Question

我有一个使用DBI连接的Perl脚本。我使用子例程打开和读取SQL脚本文件。我只打印了一条记录，而应该有两条记录（总共三条记录）。如何获取所有记录？

Alert:OUTBOUND_DATA:0

脚本：

my $dbh_oracle = DBI->connect(
          $CFG{oracle_dbi_connect},
          $CFG{db_user},
          $CFG{db_cred},
          {AutoCommit => 0,
           RaiseError => 0,
           PrintError => 0}) or die ("Cannot connect to the database: ".$DBI::errstr."\n");

my ($val1, $val2) = get_data();
print "Alert:$val1:$val2\n";

send_email("Alert:$val1:$val2");

sub get_data
{
  undef $/;
  open (my $QFH, "< /sql/summary.sql") or die "error can't open this file $!";
  my $sth= $dbh_oracle->prepare(<$QFH>) or
      die ("Cannot connect to the database: ".$DBI::errstr."\n");
  $sth->execute;
  close $QFH;
  my $row = $sth->fetchrow_hashref;
  $sth->finish;
  return @$row{'MYTABLE','FLAG'};
}

sub send_email {
    my $message = shift;
    open (MAIL, "|/usr/sbin/sendmail -t") or die "Can't open sendmail: $!";
    print MAIL "To: me\@test.com\n";
    print MAIL "From: Data\n";
    print MAIL "\n";
    print MAIL $message;
    close MAIL;
}
exit;

运行查询的结果：（超过1个记录）

MYTABLE                  FLAG
----------------------- ----------
OUTBOUND_DATA         0
MSGS_BY_DIM                  0
INBOUND_DATA         0

3 rows selected.

- cjd143SD

---

你可以设置 RaiseError => 1，从而消除那些 die() 调用的需要。 - pilcrow

另外，我注意到您已禁用RaiseError和PrintError两个选项。第一个会导致错误致命，而第二个至少会将它们打印出来（但不会终止您的代码）。您应该至少将其中一个设置为true值，以免您的错误被悄悄地丢弃。 - Ovid

Answer 1

您可以通过多种方式从语句句柄中检索数据。最常见的方法非常简单，下面展示了它们的使用：

my @row_array = $sth->fetchrow_array;
my $array_ref = $sth->fetchrow_arrayref;
my $hash_ref  = $sth->fetchrow_hashref;

首先是fetchrow_array，它将按顺序返回每一行作为一个数组。以下是使用上述select返回的数据的示例：

while (my @row_array = $sth->fetchrow_array) {
    print $row_array[0], " is ", $row_array[1], " years old, and has a " , 
          $row_array[2], "\n";
}

第二个示例与第一个类似，但返回的是数组引用而不是数组：

while (my $array_ref = $sth->fetchrow_arrayref) {
    print $array_ref->[0], " is ", $array_ref->[1], 
          " years old, and has a " , $array_ref->[2], "\n";
}

第三个例子，fetchrow_hashref，通常是最易读的:

while (my $hash_ref = $sth->fetchrow_hashref) {
    print $hash_ref->{name}, " is ", $hash_ref->{age}, 
          " years old, and has a " , $hash_ref->{pet}, "\n";
}

- Ωmega

---

关于fetchrow_hashref的一点说明，除非您更改FetchHashKeyName属性，否则键将被大写。 - gpojd

这个答案不完整，如果没有提到 @gpojd 的答案中的 selectall_ 函数。 - gregn3

Answer 2

这一行应该是一个循环：

my $row = $sth->fetchrow_hashref;

应该是：

my @rows;
while ( my $row = $sth->fetchrow_hashref ) {
    push @rows, $row;
}
return @rows;

如果您更喜欢由DBI为您执行循环，请查看selectall_arrayref或selectall_hashref

- gpojd

---

我应该把$sth->finish放在哪里？是在return @$row之后吗？ - cjd143SD

@cjd143SD，当你完成循环时，$sth->fetchrow_hashref将返回undef并自动“结束”。 - gpojd

Answer 3

这也取决于你如何构建整个脚本。你的get_data()调用只允许返回一对值。我看到至少有几种选择：要么返回包含所有数据的哈希（引用），让main组装它，要么使用之前提到的循环结构，在子例程内制造消息正文，仅返回单个标量字符串。

为了将所有数据作为哈希引用返回，get_data子例程可能如下所示（注意，我正在使用fetchall_hashref而不是fetchrow_hashref）：

sub get_data
{
  undef $/;
  open (my $QFH, "< /sql/summary.sql") or die "error can't open this file $!";
  my $sth= $dbh_oracle->prepare(<$QFH>) or
      die ("Cannot connect to the database: ".$DBI::errstr."\n");
  $sth->execute;
  close $QFH;
  my $hash_ref = $sth->fetchall_hashref('MYTABLE');
  $sth->finish;
  return $hash_ref;
}

然后你从 main 中调用它并按以下方式使用输出：

my $hash_ref = get_data();
my $message = "";
foreach my $table (sort keys %$hash_ref) {
    $message .= join(":", "Alert", $table, $$hash_ref{$table}{'FLAG'}) . "\n";
}

这将导致$message包含以下内容：

Alert:INBOUND_DATA:0
Alert:MSGS_BY_DIM:0
Alert:OUTBOUND_DATA:0

你可能希望礼貌地：

$dbh_oracle->disconnect;

在退出之前。

这段代码存在一些问题，例如你将SQL放在外部脚本中，但我已经采取硬编码密钥（MYTABLE，我假设在你的查询中是唯一的）和值（FLAG）的方式写入脚本中，这将会在以后想要扩展功能时受到限制。

- gkaupas

---

这正是我一直在寻找的。我一直在尝试使用子程序进行构建。感谢您提供main示例作为组装所有输出的方法。 - cjd143SD

Answer 4

fetchrow_方法只能一次获取一行数据。

如果你需要获取某些列的所有行，你可以低效地操作数据结构，或者使用适合你情况的调用。

在我看来，你应该使用selectcol_arrayref，例如：

my $ary_ref = $dbh->selectcol_arrayref(
    "select id, name from table",
    { Columns=>[1,2] }
);

列索引是指结果集中列的位置，而不是原始表格中的位置。

您使用返回结果的方式也需要更改以处理返回的所有行。

此外，您还有：

sub get_data
{
  undef $/;

因为您 slurp 包含 SQL 的文件。但是，$/ 是全局变量。应该在尽可能小的范围内使用 local $/。所以：

my $sql = do { local $/; <$fh> };

- Sinan Ünür

---

是的，在那些情况下我想要查询中的所有内容，selectcol_arrayref 似乎是一个不错的选择。关于使用 local $/ 的建议，我应该将其包含在子程序中还是放在外面？谢谢。 - cjd143SD