如何在Perl中使多个数据库查询更有效？

I have a queries that reside in multiple methods each (query) of which can contain multiple parameters. I am trying to reduce file size and line count to make it more maintainable. Below is such an occurrence:

我有一个驻留在多个方法中的查询，每个方法（查询）可以包含多个参数。我正在尝试减少文件大小和行数，使其更易于维护。以下是这样的事件：

$sql_update = qq { UPDATE database.table
                    SET column = 'UPDATE!'
                   WHERE id = ?
                };

$sth_update = $dbh->prepare($sql_update);

if ($dbh->err) {
    my $error = "Could not prepare statement. Error: ". $dbh->errstr ." Exiting at line " . __LINE__;
    print "$error\n";
    die;
}

$sth_rnupdate->execute($parameter);

if ($dbh->err) {
    my $error = "Could not execute statement. Error: ". $dbh->errstr ." Exiting at line " . __LINE__;
    print "$error\n";
    die;
}

This is just one example, however, there are various other select examples that contain just the one parameter to be passed in, however there is also some with two or more parameters. I guess I am just wondering would it be possible to encapsulate this all into a function/method, pass in an array of parameters, how would the parameters be populated into the execute() function?

这仅是一个示例，然而，存在各种其他选择示例，其仅包含要传递的一个参数，但是也存在具有两个或更多个参数的一些参数。我想我只是想知道是否有可能将这一切封装到一个函数/方法中，传入一个参数数组，如何将参数填充到execute（）函数中？

If this was possible I could write a method that you simply just pass in the SQL query and parameters and get back a reference to the fetched records. Does this sound safe at all?

如果这是可能的，我可以编写一个方法，只需传入SQL查询和参数，然后获取对已获取记录的引用。这听起来安全吗？

5 个解决方案

#1

If line-count and maintainable code is your only goal, your best bet would be to use any one of the several fine ORM frameworks/libraries available. Class::DBI and DBIx::Class are two fine starting points. Just in case, you are worried about spending additional time to learn these modules - dont: It took me just one afternoon to get started and productive. Using Class::DBI for example your example is just one line:

如果行数和可维护代码是您唯一的目标，那么您最好的选择是使用几个可用的精细ORM框架/库中的任何一个。 Class :: DBI和DBIx :: Class是两个很好的起点。为了以防万一，你担心花费额外的时间来学习这些模块 - 不要：我只花了一个下午才开始并提高工作效率。以Class :: DBI为例，您的示例只是一行：

Table->retrieve(id => $parameter)->column('UPDATE!')->update;

The only down-side (if that) of these frameworks is that very complicated SQL statements required writing custom methods learning which may take you some additional time (not too much) to get around.

这些框架唯一的缺点（如果是这样）是非常复杂的SQL语句需要编写自定义方法学习，这可能需要一些额外的时间（不要太多）来解决。

#2

No sense in checking for errors after every single database call. How tedious!

在每次数据库调用之后检查错误都没有意义。多么乏味！

Instead, when you connect to the database, set the RaiseError option to true. Then if a database error occurs, an exception will be thrown. If you do not catch it (in an eval{} block), your program will die with a message, similar to what you were doing manually above.

相反，当您连接到数据库时，将RaiseError选项设置为true。然后，如果发生数据库错误，将引发异常。如果你没有捕获它（在eval {}块中），你的程序将会丢失一条消息，类似于你上面手动执行的操作。

#3

The "execute" function does accept an array containing all your parameters.

“execute”函数接受包含所有参数的数组。

You just have to find a way to indicate which statement handle you want to execute and you're done ...

你只需要找到一种方法来指示你想要执行哪个语句句柄，你就完成了......

It would be much better to keep your statement handles somewhere because if you create a new one each time and prepare it each time you don't really rip the benefits of "prepare" ...

将语句句柄保留在某个地方会好得多，因为如果你每次创建一个新语句并在每次你没有真正撕掉“准备”的好处时做好准备......

About returning all rows you can do that ( something like "while fetchrow_hashref push" ) be beware of large result sets that coudl eat all your memory !

关于返回所有可以执行此操作的行（类似于“while fetchrow_hashref push”），请注意大型结果集，这些结果集可以满足您的所有记忆！

#4

Here's a simple approach using closures/anonymous subs stored in a hash by keyword name (compiles, but not tested otherwise), edited to include use of RaiseError:

这是一个简单的方法，使用按关键字名称存储在哈希中的闭包/匿名子（编译，但未经过其他测试），编辑后包括使用RaiseError：

# define cached SQL in hash, to access by keyword
#
sub genCachedSQL {
  my $dbh = shift;
  my $sqls = shift;  # hashref for keyword => sql query
  my %SQL_CACHE;
  while (my($name,$sql) = each %$sqls) {
     my $sth = $dbh->prepare($sql);
     $SQL_CACHE{$name}->{sth} = $sth;

     $SQL_CACHE{$name}->{exec} = sub {  # closure for execute(s)
        my @parameters = @_;
        $SQL_CACHE{$name}->{sth}->execute(@parameters);

        return sub {  # closure for resultset iterator  - check for undef
           my $row; eval { $row = $SQL_CACHE{$name}->{sth}->fetchrow_arrayref(); };
           return $row;
        }  # end resultset closure

     }  # end exec closure

  }  # end while each %$sqls

  return \%SQL_CACHE;

}  # end genCachedSQL


my $dbh = DBI->connect('dbi:...', { RaiseError => 1 });

# initialize cached SQL statements
#
my $sqlrun = genCachedSQL($dbh,
 {'insert_table1' => qq{ INSERT INTO database.table1 (id, column) VALUES (?,?) },
  'update_table1' => qq{ UPDATE database.table1 SET column = 'UPDATE!' WHERE id = ? },
  'select_table1' => qq{ SELECT column FROM database.table1 WHERE id = ? }});

# use cached SQL
#
my $colid1 = 1;
$sqlrun->{'insert_table1'}->{exec}->($colid1,"ORIGINAL");
$sqlrun->{'update_table1'}->{exec}->($colid1);
my $result = $sqlrun->{'select_table1'}->{exec}->($colid1);
print join("\t", @$_),"\n" while(&$result());

my $colid2 = 2;
$sqlrun->{'insert_table1'}->{exec}->($colid2,"ORIGINAL");

# ...

#5

I'm very impressed with bubaker's example of using a closure for this.

对于bubaker使用封闭器的例子我印象非常深刻。

Just the same, if the original goal was to make the code-base smaller and more maintainable, I can't help thinking there's a lot of noise begging to be removed from the original code, before anyone embarks on a conversion to CDBI or DBIC etc (notwithstanding the great libraries they both are.)

同样，如果最初的目标是使代码库更小，更易于维护，我不禁想到，在任何人开始转换为CDBI或DBIC之前，有很多噪音要求从原始代码中删除等等（尽管它们都是伟大的图书馆。）

If the $dbh had been instantiated with RaiseError set in the attributes, most of that code goes away:

如果$ dbh已经在属性中设置了RaiseError实例化，那么大部分代码都会消失：

$sql_update = qq { UPDATE database.table
                   SET column = 'UPDATE!'
                   WHERE id = ?
              };
$sth_update = $dbh->prepare($sql_update);
$sth_update->execute($parameter);

I can't see that the error handling in the original code is adding much that you wouldn't get from the vanilla die produced by RaiseError, but if it's important, have a look at the HandleError attribute in the DBI manpage.

我无法看到原始代码中的错误处理增加了很多你不会从RaiseError生成的vanilla die中获得的，但是如果它很重要，请查看DBI联机帮助页中的HandleError属性。

Furthermore, if such statements aren't being reused (which is often the main purpose of preparing them, to cache how they're optimised; the other reason is to mitigate against SQL injection by using placeholders), then why not use do?

此外，如果没有重用这些语句（这通常是准备它们的主要目的，缓存它们的优化方式;另一个原因是通过使用占位符来缓解SQL注入），那么为什么不使用do呢？

$dbh->do($sql_update, \%attrs, @parameters);

#1

Table->retrieve(id => $parameter)->column('UPDATE!')->update;