如何使用Perl获取存储过程结果?

2021-12-10 00:00:00 perl sql-server stored-procedures

我是 sql 初学者.我创建的程序如下

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

create procedure  testprocedure2 as
select 'one'
select 'three'
select '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]."	";
    print "
";
}

我不知道是什么问题.我该如何解决?我希望这个答案有助于 昨天的问题

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]."	";
        print "
";
    }
} 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 和 <一个 href="https://stackoverflow.com/a/41440490/1331451">Shahzad 使用 UNION 是正确的.

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 在这里是词法 很重要.它必须在循环头中用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.

相关文章