使用 Perl 执行查询

要使用 Perl 查询 Vertica：

1. 使用 Perl DBI 模块的 prepare 函数准备查询语句。此函数将返回一个语句句柄，您可以使用此句柄来执行查询和获取结果集。

2. 通过对该语句句柄调用 execute 函数来执行预定义的语句。

3. 使用多种方法之一从语句句柄检索查询结果，例如，调用语句句柄的 fetchrow_array 函数以检索数据行，或者调用 fetchall_array 以获取包含整个结果集的一系列数组（如果结果集可能非常大，则此方法不太适用！）。

以下示例演示了查询由使用 Perl 批量加载数据中所示的示例创建的表。此示例将执行查询以检索该表的所有内容，然后重复调用 fetchrow_array 函数以获取数组中的数据行。此示例将重复上述过程直至 fetchrow_array 返回 undef 为止，该返回值表示没有更多数据行可供读取。

#!/usr/bin/perl
use strict;
use DBI;
my $attr = {RaiseError => 1 }; # Make errors fatal to the Perl script.
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123",
                        $attr);
# Prepare a query to get the content of the table
my $sth = $dbh->prepare("SELECT * FROM TEST ORDER BY C_ID ASC");
# Execute the query by calling execute on the statement handle
$sth->execute();
# Loop through result rows while we have them, getting each row as an array
while (my @row = $sth->fetchrow_array()) {
    # The @row array contains the column values for this row of data
    # Loop through the column values
    foreach my $column (@row) {
        if (!defined $column) {
            # NULLs are signaled by undefs. Set to NULL for clarity
            $column = "NULL";
        }
        print "$column\t"; # Output the column separated by a tab
    }
    print "\n";
}
$dbh->disconnect();

该示例在运行后输出了以下内容：

1    1.111    Hello World!    2001-01-01    01:01:01    2001-01-01 01:01:01    1
2    2.22222    How are you?    2002-02-02    02:02:02    2002-02-02 02:02:02    0
4    4.22222    NULL    2002-02-02    02:02:02    2002-02-02 02:02:02    0

将变量绑定到列值

检索查询结果的另一种方法是使用语句句柄的 bind_columns 函数将变量绑定到结果集中的列。如果需要对返回的数据执行大量处理，您可能会这种方法很方便，因为代码可以使用变量而非数组引用来访问数据。以下示例演示了将变量绑定到结果集而非在行值和列值中循环。

#!/usr/bin/perl
use strict;
use DBI;
my $attr = {RaiseError => 1 }; # Make SQL errors fatal to the Perl script.
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN32","ExampleUser","password123",
                        $attr);
# Prepare a query to get the content of the table
my $sth = $dbh->prepare("SELECT * FROM TEST ORDER BY C_ID ASC");
$sth->execute();
# Create a set of variables to bind to the column values.
my ($C_ID, $C_FP, $C_VARCHAR, $C_DATE, $C_TIME, $C_TS, $C_BOOL);
# Bind the variable references to the columns in the result set.
$sth->bind_columns(\$C_ID, \$C_FP, \$C_VARCHAR, \$C_DATE, \$C_TIME,
                    \$C_TS, \$C_BOOL);

# Now, calling fetch() to get a row of data updates the values of the bound
# variables. Continue calling fetch until it returns undefined.
while ($sth->fetch()) {
    # Note, you should always check that values are defined before using them,
    # since NULL values are translated into Perl as undefined. For this
    # example, just check the VARCHAR column for undefined values.
    if (!defined $C_VARCHAR) {
        $C_VARCHAR = "NULL";
    }
    # Just print values separated by tabs.
    print "$C_ID\t$C_FP\t$C_VARCHAR\t$C_DATE\t$C_TIME\t$C_TS\t$C_BOOL\n";
}
$dbh->disconnect();

此示例的输出与上一个示例的输出相同。

准备、查询和返回单个行

如果希望查询结果是单个行（例如在执行 COUNT (*) 查询时），您可以使用 DBI 模块的 selectrow_array 函数将语句执行和数组检索合并为单个结果。

以下示例显示了使用 selectrow_array 执行 SHOW LOCALE 语句并获取其结果。此示例还演示了使用 do 函数更改区域设置。

#!/usr/bin/perl
use strict;
use DBI;
my $attr = {RaiseError => 1 }; # Make SQL errors fatal to the Perl script.
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123",
                        $attr);
# Demonstrate setting/getting locale.
# Use selectrow_array to combine preparing a statement, executing it, and
# getting an array as a result.
my @localerv = $dbh->selectrow_array("SHOW LOCALE;");
# The locale name is the 2nd column (array index 1) in the result set.
print "Locale: $localerv[1]\n";
# Use do() to execute a SQL statement to set the locale.
$dbh->do("SET LOCALE TO en_GB");
# Get the locale again.
@localerv = $dbh->selectrow_array("SHOW LOCALE;");
print "Locale is now: $localerv[1]\n";
$dbh->disconnect();

运行此示例后的结果如下所示：

Locale: en_US@collation=binary (LEN_KBINARY)
Locale is now: en_GB (LEN)

执行查询和 ResultBufferSize 设置

当您在准备好的语句上调用 execute() 函数时，客户端库会一直检索结果，直到达到结果缓冲区大小。结果缓冲区大小是使用 ODBC 的 ResultBufferSize 设置进行设置的。

Vertica 不允许每个连接有多个活动查询。不过，您可以模拟多个活动查询，方法是将结果缓冲区设置得大到足以容纳第一个查询的全部结果。为了确保 ODBC 客户端驱动程序的缓冲区大到足以存储第一个查询的结果集，您可以将 ResultBufferSize 设置为 0。将此参数设置为 0 会使结果缓冲区大小不受限制。ODBC 驱动程序分配足够的内存来读取整个结果集。将第一个查询的整个结果集存储在结果集缓冲区后，数据库连接便可以自由地执行另一个查询。您的客户端可以执行第二个查询，即使它尚未处理第一个查询中的整个结果集。

但是，如果您将 ResultBufferSize 设置为 0，您可能会发现对 execute() 的调用会导致操作系统终止您的 Perl 客户端脚本。如果 ODBC 驱动程序分配了太多内存来存储大型结果集，操作系统可能会终止您的脚本。

此行为的解决方法是限制查询返回的行数。然后，您可以将 ResultBufferSize 设置为适合此有限结果集的值。例如，您可以估计为了存储单行查询结果而所需的内存量。然后，使用 LIMIT 和 OFFSET 子句来获得适合您借助于 ResultBufferSize 所分配空间的具体行数。如果查询结果能够适合有限结果集缓冲区，则您可以使用相同的数据库连接来执行其他查询。此解决方案使您的代码更加复杂，因为您需要执行多个查询才能获得整个结果集。此外，如果您需要一次对整个结果集进行操作，而不是一次只对其一部分进行操作，那么此解决方案也不适合。

更好的解决方案是针对要执行的每个查询使用单独的数据库连接。与处理大型数据集所需的资源相比，建立额外数据库连接所需的开销很小。