如何使用Perl获得存储过程结果? [英] How to get the stored procedure result using Perl?

问题描述

我是sql的初学者.我创建了以下过程

I'm beginner in sql. I have created the procedure as follows

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

当我对数据库执行查询时，它显示三个结果one three five. sql查询为exec TEST_ABC_DB.dbo.testprocedure2

When I execute query into the database It shows the three result one three five. sql query is exec TEST_ABC_DB.dbo.testprocedure2

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

When I run the same query into the Perl it gives only one record which is one

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

我不知道这是什么问题.我该如何解决?我希望这个答案对

I don't know what is the problem. How can I fix it? I hope this answer will help in yesterday's question

推荐答案

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

由于您使用的是DBD，因此: :ODBC ，则可以使用该驱动程序提供的 more_results 在一个execute中获得多个查询的结果.

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.

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

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

如果我们想对您的示例查询执行此操作，则几乎相同.您运行存储过程，然后继续使用do {} while构造(请注意，这不是一个块，不能next！).

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!).

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

其他一些驱动程序也提供了此功能.如果是这样，则可以调用$sth->more_results而不是使用如下所述的内部方法.

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

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

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.

如果您的过程中确实需要三个独立的查询并需要所有结果，请使用 Shakheer 和<一个使用UNION的href ="https://stackoverflow.com/a/41440490/1331451"> Shahzad .

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.

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

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.

我们必须为此更改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.

假设过程如下:

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

这仍然是每个查询仅一行，但应作为示例.使用UNION方法，首先是这样的:

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

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填充它们.将它们添加到所有查询中，以使它们与列数最大的列中的列数匹配.

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.

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中对其进行循环，我们将获得某物.

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

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'

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

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

$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;

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

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.

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

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.

总体结果是其中包含数组的数组.

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.

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

^{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.}

这篇关于如何使用Perl获得存储过程结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！