sql-server - 如何使用 Perl 获取存储过程结果？

Question

我是 sql 初学者。我创建了如下程序

create procedure  testprocedure2 as
select 'one'
select 'three'
select 'five'

当我对数据库执行查询时，它显示了三个结果 一三五。 sql 查询是 exec TEST_ABC_DB.dbo.testprocedure2

当我在 Perl 中运行相同的查询时，它只给出一条记录，即 one

$sth = $dbh->prepare("exec TEST_ABC_DB.dbo.testprocedure2");
$sth->execute();
while (@row= $sth->fetchrow_array())  
{
    print $row[0]."\t";
    print "\n";
}

不知道是什么问题。我该如何解决？我希望这个答案对 yesterday's question 有所帮助

Answer 1

通过驱动程序(例如 DBD::ODBC )

Since you're using DBD::ODBC , 你可以使用 more_results provided by that driver在一次执行中获取多个查询的结果。

这是他们在文档中显示的示例。

do {
   my @row;
   while (@row = $sth->fetchrow_array()) {
      # do stuff here
   }
} while ($sth->{odbc_more_results});

如果我们想对您的示例查询执行此操作，则几乎是一样的。您运行存储过程，然后继续执行 do {} while 构造(请注意，这不是一个 block ，您不能 next 出它!)。

my $sth = $dbh->prepare("exec TEST_ABC_DB.dbo.testprocedure2");
$sth->execute;

do {
    while (my @row = $sth->fetchrow_array()) {
        print $row[0]."\t";
        print "\n";
    }
} while ($sth->{odbc_more_results});

这应该会打印出您预期的结果。

one
three
five

其他一些驱动程序也提供此功能。如果他们这样做，您可以调用 $sth->more_results 而不是使用如下所述的内部结构。

---

如果您的驱动程序不支持此解决方案

DBI 本身没有办法一次返回多个查询的结果。您可以运行它们，但您无法获得结果。

如果您确实需要在您的过程中进行三个单独的查询并想要所有结果，请通过 Shakheer 获得答案和 Shahzad使用 UNION 是正确的。

但是，您的示例可能是人为设计的。您可能在每个查询中没有相同数量的列，并且您需要区分每个查询的结果。

我们必须为此更改 SQL 和 Perl 代码。

要让它发挥作用，您可以插入额外的行，稍后您可以使用这些行将每个结果堆栈映射到每个查询。

假设程序如下所示:

create procedure testprocedure3 as
select 'one'
select 'three', 'three', 'three'
select 'five', 'five', 'five', 'five', 'five'

这仍然只是每个查询的一行，但它应该作为一个例子。使用 UNION 方法，它首先变成这样:

create procedure testprocedure3 as
select 'one'
union all
select 'three', 'three', 'three'
union all
select 'five', 'five', 'five', 'five', 'five'

如果你运行它，它可能会失败。在 ANSI SQL 中，UNION 需要在其所有查询中具有相同数量的列，因此我假设 SQLServer 也需要这一点。我们需要用 NULL 填充它们。将它们添加到所有查询中，以便它们与列数最多的查询中的列数相匹配。

create procedure testprocedure3 as
select 'one', NULL, NULL, NULL, NULL
union all
select 'three', 'three', 'three', NULL, NULL
union all
select 'five', 'five', 'five', 'five', 'five'

如果我们现在使用以下代码在 Perl 中循环它，我们将得到一些东西。

use Data::Dumper;
my $sth = $dbh->prepare("exec TEST_ABC_DB.dbo.testprocedure3");
$sth->execute;
while ( my $row = $sth->fetchrow_arrayref ) {
    print Dumper $row;
}

我们会看到类似这样的输出(我没有运行代码，而是手动编写了输出):

$VAR1 = [ 'one', undef, undef, undef, undef ];
$VAR1 = [ 'three', 'three', 'three', undef, undef ];
$VAR1 = [ 'five', 'five', 'five', 'five', 'five' ];

我们无法知道哪一行属于查询的哪一部分。因此，让我们插入一个分隔符。

create procedure testprocedure3 as
select 'one', NULL, NULL, NULL, NULL
union all
select '-', '-', '-', '-', '-'
union all
select 'three', 'three', 'three', NULL, NULL
union all
select '-', '-', '-', '-', '-'
union all
select 'five', 'five', 'five', 'five', 'five'

现在 Perl 代码的结果如下所示:

$VAR1 = [ 'one', undef, undef, undef, undef ];
$VAR1 = [ '-', '-', '-', '-', '-' ];
$VAR1 = [ 'three', 'three', 'three', undef, undef ];
$VAR1 = [ '-', '-', '-', '-', '-' ];
$VAR1 = [ 'five', 'five', 'five', 'five', 'five' ];

这可能不是分隔符的最佳选择，但它很好地说明了我打算做什么。我们现在要做的就是将其拆分为单独的结果。

use Data::Dumper;

my @query_results;
my $query_index = 0;
my $sth = $dbh->prepare("exec TEST_ABC_DB.dbo.testprocedure3");
$sth->execute;
while ( my $row = $sth->fetchrow_arrayref ) {
     # move to the next query if we hit the delimiter
     if ( join( q{}, @$row ) eq q{-----} ) {
         $query_index++;
         next;
     }

     push @{ $query_results[$query_index] }, $row;
}

print Dumper \@query_results;

我定义了两个新变量。 @query_results 包含所有结果，按查询编号排序。 $query_index 是该数组的索引。它从 0 开始。

我们迭代所有结果行。 $row 在这里是 lexical 很重要。它必须在循环头中使用my 创建。 (您正在使用 use strict，对吗？)如果我们看到分隔符，我们会增加 $query_index 并继续。如果我们没有常规结果行，那么我们将其粘贴到当前查询索引内的 @query_results 数组中。

整体结果是一个数组，其中包含数组的数组。

$VAR1 = [
   [
       [ 'one', undef, undef, undef, undef ]
   ], 
   [
       [ 'three', 'three', 'three', undef, undef ]
   ], 
   [
       [ 'five', 'five', 'five', 'five', 'five' ]
   ], 
];

如果您有返回许多行的实际查询，这就很有意义了。

当然，您不必存储所有结果。您也可以直接在循环中处理每个查询的结果。

---

^{免责声明:我没有运行此答案中的任何代码，因为我无权访问 SQLServer。它可能包含 Perl 和 SQL 中的语法错误。但它确实展示了这种方法。}