如何使用perl获取存储过程结果？-Howtogetthestoredprocedureresultusingperl?

6  

Through the driver (e.g. DBD::ODBC)

Since you're using DBD::ODBC, you can use more_results provided by that driver to get the results of multiple queries in one execute.

由于您使用的是DBD :: ODBC,因此可以使用该驱动程序提供的more_results在一次执行中获取多个查询的结果。

This is the example they show in the documentation.

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

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

If we want to do this with your example queries, it's pretty much the same. You run your stored procedure, and then proceed with the do {} while construct (note that this is not a block, you cannot next out of it!).

如果我们想对您的示例查询执行此操作,则它几乎相同。您运行存储过程,然后继续执行do {} while构造(请注意,这不是块,您不能接下来!)。

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});

This should print your expected result.

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

one
three
five

Some other drivers also provide this. If they do, you can call $sth->more_results instead of using the internals as described below.

其他一些司机也提供这个。如果他们这样做,您可以调用$ sth-> more_results而不是使用内部,如下所述。

Workaround if your driver doesn't support this

There is no way for DBI itself to return the result of multiple queries at once. You can run them, but you cannot get the results.

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

If you really need three separate queries in your procedure and want all of the results, the answers by Shakheer and Shahzad to use a UNION are spot on.

如果你真的需要在你的程序中进行三次单独的查询并想要所有的结果,那么Shakheer和Shahzad使用UNION的答案就是现场。

However, your example is probably contrived. You probably don't have the same amount of columns in each of those queries, and you need to distinguish the results of each of the queries.

但是,你的例子可能是人为的。您可能在每个查询中没有相同数量的列,您需要区分每个查询的结果。

We have to change SQL and Perl code for this.

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

To get that to work, you can insert additional rows that you can later use to map each stack of results to each query.

要使其工作,您可以插入其他行,以后可以使用这些行将每个结果堆栈映射到每个查询。

Let's say the procedure looks like this:

让我们说程序看起来像这样:

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

This is still just one row per query, but it should do as an example. With the UNION approach, it first becomes this:

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

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

If you run this, it might fail. In ANSI SQL a UNION needs to have the same number of columns in all its queries, so I assume SQLServer also wants this. We need to fill them up with NULLs. Add them to all the queries so they match the number of columns in the one with the largest number of columns.

如果你运行它,它可能会失败。在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'

If we now loop over it in Perl with the following code, we'll get something back.

如果我们现在使用以下代码在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;
}

We'll see output similar to this (I didn't run the code, but wrote the output manually):

我们将看到类似于此的输出(我没有运行代码,但手动编写输出):

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

We have no way of knowing which line belongs to which part of the query. So let's insert a delimiter.

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

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'

Now the result of the Perl code will look as follows:

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

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

This might not be the best choice of delimiter, but it nicely illustrates what I am planning to do. All we have to do now is split this into separate results.

这可能不是分隔符的最佳选择,但它很好地说明了我打算做的事情。我们现在要做的就是把它分成不同的结果。

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;

I've defined two new variables. @query_results holds all the results, sorted by query number. $query_index is the index for that array. It starts with 0.

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

We iterate all the resulting rows. It's important that $row is lexical here. It must be created with my in the loop head. (You are using use strict, right?) If we see the delimiter, we increment the $query_index and move on. If we don't we have a regular result line, so we stick that into our @query_results array within the current query's index.

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

The overall result is an array with arrays of arrays in it.

总体结果是一个包含数组数组的数组。

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

If you have actual queries that return many rows this starts making a lot of sense.

如果你有实际的查询返回许多行,这开始很有意义。

Of course you don't have to store all the results. You can also just work with the results of each query directly in your loop.

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

^{Disclaimer: I've run none of the code in this answer as I don't have access to an SQLServer. It might contain syntax errors in the Perl as well as the SQL. But it does demonstrate the approach.}

免责声明:我没有在这个答案中运行任何代码,因为我无法访问SQLServer。它可能包含Perl中的语法错误以及SQL。但它确实证明了这种方法。

2  

The procedure you created is returning 3 result sets. And you are capturing only 1 result. If you are not bother about sets, make them as single result with UNION ALL

您创建的过程返回3个结果集。而你只捕获了1个结果。如果您对集合不感兴趣,请使用UNION ALL将它们作为单个结果

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

Edit:

If you want to capture multiple resultsets returned from stored procedure, here is a good example explained with MySQL database Multiple data sets in MySQL stored procedures

如果你想捕获从存储过程返回的多个结果集,这里有一个很好的例子用MySQL数据库解释MySQL存储过程中的多个数据集

1  

simple use union all like this then only one table is shown with data.

简单的使用union就像这样只有一个表显示数据。