Perl 简明教程

Perl - Database Access

本章将教您如何在 Perl 脚本中访问数据库。从 Perl 5 开始，利用 DBI 模块编写数据库应用程序变得很容易。DBI 代表 Perl 的 Database Independent Interface ，这意味着 DBI 在 Perl 代码和底层数据库之间提供了一个抽象层，让您可以非常轻松地切换数据库实现。

DBI 是 Perl 编程语言的数据库访问模块。它提供了一组方法、变量和约定，这些内容提供了一致的数据库接口，而与正在使用的实际数据库无关。

Architecture of a DBI Application

DBI 独立于后端中提供的所有数据库。无论是否使用 Oracle、MySQL 或 Informix 等，都可以使用 DBI。这一点从以下体系结构图中可以清楚地看出。

在此，DBI 负责通过 API（即应用程序编程接口）获取所有 SQL 命令，并将它们分派给相应的驱动程序以实际执行。最后，DBI 负责获取驱动程序的结果并将其返回给调用脚本。

Notation and Conventions

在本章中将使用以下符号，建议您也遵循相同的约定。

$dsn    Database source name
$dbh    Database handle object
$sth    Statement handle object
$h      Any of the handle types above ($dbh, $sth, or $drh)
$rc     General Return Code  (boolean: true=ok, false=error)
$rv     General Return Value (typically an integer)
@ary    List of values returned from the database.
$rows   Number of rows processed (if available, else -1)
$fh     A filehandle
undef   NULL values are represented by undefined values in Perl
\%attr  Reference to a hash of attribute values passed to methods

Database Connection

假设我们准备使用 MySQL 数据库。在连接到数据库之前，请确保以下事项。如果您不知道如何在 MySQL 数据库中创建数据库和表，可以利用我们的 MySQL 教程。

您已创建一个名为 TESTDB 的数据库。
您已在 TESTDB 中创建了一个名为 TEST_TABLE 的表。
此表有字段 FIRST_NAME、LAST_NAME、AGE、SEX 和 INCOME。
设置了用户 ID“testuser”和密码“test123”来访问 TESTDB。
Perl 模块 DBI 已在您的机器上正确安装。
您已经学习了 MySQL 教程以了解 MySQL 基础知识。

以下是连接到 MySQL 数据库“TESTDB”的示例 −

#!/usr/bin/perl

use DBI
use strict;

my $driver = "mysql";
my $database = "TESTDB";
my $dsn = "DBI:$driver:database=$database";
my $userid = "testuser";
my $password = "test123";

my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

如果与数据源建立了连接，那么将返回一个数据库句柄并将其保存到 $dbh 中以供进一步使用，否则 $dbh 会设置为未定义的值，而 $DBI::errstr 会返回一个错误字符串。

INSERT Operation

当您想在表中创建一些记录时，需要执行 INSERT 操作。我们在此处使用表 TEST_TABLE 来创建我们的记录。因此，一旦数据库连接建立以后，就可以准备在 TEST_TABLE 中创建记录。以下是向 TEST_TABLE 中创建单个记录的过程。您可以利用相同的概念创建任意多条记录。

记录创建采用以下步骤：

使用 INSERT 语句准备 SQL 语句。这将使用 prepare() API 来完成。
执行 SQL 查询以从数据库中选择所有结果。这将使用 execute() API 来完成。
释放语句句柄。这将使用 finish() API 来完成。
如果一切都正常，那么将会 commit 此操作，否则您能 rollback 完成交易。提交和回滚将在下一部分进行说明。

my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                       (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
                         values
                       ('john', 'poul', 'M', 30, 13000)");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Using Bind Values

可能出现这种情况，即需要输入的值未预先给出。因此，您可以使用绑定变量，它将在运行时获取所需值。Perl DBI 模块在实际值的位置使用问号，然后在运行时通过 execute() API 传递实际值。以下是示例 −

my $first_name = "john";
my $last_name = "poul";
my $sex = "M";
my $income = 13000;
my $age = 30;
my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                        (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
                          values
                        (?,?,?,?)");
$sth->execute($first_name,$last_name,$sex, $age, $income)
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

READ Operation

对任何数据库执行 READ 操作意味着从数据库获取一些有用的信息，即从一个或多个表中获取一个或多个记录。因此，一旦数据库连接建立以后，就可以准备查询此数据库。以下是查询所有 AGE 大于 20 的记录的过程。这将分四步执行 −

根据所需条件准备 SQL SELECT 查询。这将使用 prepare() API 来完成。
执行 SQL 查询以从数据库中选择所有结果。这将使用 execute() API 来完成。
逐个获取所有结果并打印这些结果。这将使用 fetchrow_array() API 来完成。
释放语句句柄。这将使用 finish() API 来完成。

my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM TEST_TABLE
                        WHERE AGE > 20");
$sth->execute() or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array()) {
   my ($first_name, $last_name ) = @row;
   print "First Name = $first_name, Last Name = $last_name\n";
}
$sth->finish();

Using Bind Values

可能有一种情况是条件未提前给出。因此，您可以使用绑定变量，它会在运行时获取所需的值。Perl DBI 模块使用问号代替实际值，然后通过 execute() API 在运行时传递实际值。以下示例 −

$age = 20;
my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM TEST_TABLE
                        WHERE AGE > ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array()) {
   my ($first_name, $last_name ) = @row;
   print "First Name = $first_name, Last Name = $last_name\n";
}
$sth->finish();

UPDATE Operation

对任何数据库执行 UPDATE 操作意味着更新数据库表中已有的一个或多个记录。以下是更新所有 SEX 为“M”的记录的过程。这里我们将所有男性的年龄增加一年。这将需要三个步骤 −

根据所需条件准备 SQL 查询。这将使用 prepare() API 来完成。
执行 SQL 查询以从数据库中选择所有结果。这将使用 execute() API 来完成。
释放语句句柄。这将使用 finish() API 来完成。
如果一切顺利，则 commit 此操作，否则可以 rollback 完成事务。有关提交和回滚 API，请参见下一部分。

my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1
                        WHERE SEX = 'M'");
$sth->execute() or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Using Bind Values

$sex = 'M';
my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1
                        WHERE SEX = ?");
$sth->execute('$sex') or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

在某些情况下，您希望设置一个未提前给出的值，因此您可以按如下方式使用绑定值。在此示例中，所有男性的收入都将设置为 10000。

$sex = 'M';
$income = 10000;
my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   INCOME = ?
                        WHERE SEX = ?");
$sth->execute( $income, '$sex') or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();

DELETE Operation

当您想从数据库中删除一些记录时，需要使用 DELETE 操作。以下是删除 TEST_TABLE 中 AGE 等于 30 的所有记录的过程。此操作需要以下步骤。

根据所需条件准备 SQL 查询。这将使用 prepare() API 来完成。
执行 SQL 查询以从数据库中删除所需的记录。这将使用 execute() API 来完成。
释放语句句柄。这将使用 finish() API 来完成。
如果一切顺利，则 commit 此操作，否则可以 rollback 完成事务。

$age = 30;
my $sth = $dbh->prepare("DELETE FROM TEST_TABLE
                         WHERE AGE = ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows deleted :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Using do Statement

如果您要执行 UPDATE、INSERT 或 DELETE，并且数据库中不会返回任何数据，则有用于执行此操作的快捷方式。您可以使用 do 语句按如下方式执行任何命令。

$dbh->do('DELETE FROM TEST_TABLE WHERE age =30');

do 如果成功，则返回 true 值，如果失败，则返回 false 值。实际上，如果成功，则返回受影响的行数。在该示例中，它将返回实际已删除的行数。

COMMIT Operation

提交是给数据库一个绿灯信号以完成更改的操作，而且此操作之后，没有任何更改可以恢复到其原始位置。

以下是调用 commit API 的简单示例。

$dbh->commit or die $dbh->errstr;

ROLLBACK Operation

如果您对所有更改不满意或在任何操作中遇到错误，则可以使用 rollback API 恢复这些更改。

以下是调用 rollback API 的简单示例。

$dbh->rollback or die $dbh->errstr;

Begin Transaction

许多数据库支持事务。这意味着您可以进行大量修改数据库的查询，但不会实际进行任何更改。然后最后，您发出特殊的 SQL 查询 COMMIT ，所有更改同时进行。或者，您可以发出查询 ROLLBACK，在这种情况下，所有更改都将被抛弃，并且数据库保持不变。

Perl DBI 模块提供 begin_work API，它启用事务（通过关闭 AutoCommit），直到下一次调用 commit 或 rollback。在下次 commit 或 rollback 之后，AutoCommit 将自动重新打开。

$rc  = $dbh->begin_work  or die $dbh->errstr;

AutoCommit Option

如果你的事务很简单，你可以免去执行许多提交的麻烦。当发出连接调用时，你可以指定一个 AutoCommit 选项，它将在每次查询成功后自动执行提交操作。其外观如下 −

my $dbh = DBI->connect($dsn, $userid, $password,
              {AutoCommit => 1})
              or die $DBI::errstr;

这里 AutoCommit 变量可以取 1 或 0 的值，其中 1 表示 AutoCommit 已开启，而 0 表示 AutoCommit 已关闭。

Automatic Error Handling

当你发出连接调用时，你可以指定 RaiseErrors 选项，它会自动为你处理错误。当错误发生时，DBI 将中止你的程序，而不是返回一个错误代码。如果你所需要的只是在发生错误时中止程序，这样做会很方便。其外观如下 −

my $dbh = DBI->connect($dsn, $userid, $password,
              {RaiseError => 1})
              or die $DBI::errstr;

这里 RaiseErrors 变量可以取 1 或 0 的值。

Disconnecting Database

要断开数据库连接，请按照下面所示使用 disconnect API −

$rc = $dbh->disconnect  or warn $dbh->errstr;

令人遗憾的是，断开连接方式的事务行为是未定义的。一些数据库系统（例如 Oracle 和 Ingres）将自动提交任何未完成的更改，而其他数据库（例如 Informix）将回滚任何未完成的更改。没有使用 AutoCommit 的应用程序应在调用断开连接之前显式调用提交或回滚。

Using NULL Values

未定义的值或 undef 用来指示 NULL 值。你可以插入和更新具有 NULL 值的列，就像更新非 NULL 值一样。以下示例插入并更新具有 NULL 值的年龄列 −

$sth = $dbh->prepare(qq {
         INSERT INTO TEST_TABLE (FIRST_NAME, AGE) VALUES (?, ?)
       });
$sth->execute("Joe", undef);

这里 qq{} 用来向 prepare API 返回一个带引号的字符串。但是，在 WHERE 子句中尝试使用 NULL 值时必须小心。考虑以下情况 −

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

将 undef（NULL）绑定到占位符不会选择具有 NULL 年龄的行！至少对于符合 SQL 标准的数据库引擎是这样。有关此原因，请参阅数据库引擎的 SQL 手册或任何 SQL 书籍。若要显式选择 NULL 值，你必须说“WHERE 年龄 IS NULL”。

常见的问题是，代码片段在运行时处理的值既可以是已定义的，也可以是未定义的（非 NULL 或 NULL）。一种简单的方法是根据需要准备适当的语句，并将占位符替换为非 NULL 情况 −

$sql_clause = defined $age? "age = ?" : "age IS NULL";
$sth = $dbh->prepare(qq {
         SELECT FIRST_NAME FROM TEST_TABLE WHERE $sql_clause
       });
$sth->execute(defined $age ? $age : ());

Some Other DBI Functions

available_drivers

@ary = DBI->available_drivers;
@ary = DBI->available_drivers($quiet);

通过搜索 @INC 中目录中的 DBD::* 模块，返回所有可用驱动程序的列表。默认情况下，如果某些驱动程序被更早目录中同名其他驱动程序隐藏，则会发出警告。为 $quiet 传递 true 值将抑制此警告。

installed_drivers

%drivers = DBI->installed_drivers();

返回当前进程中所有驱动程序的驱动程序名称和驱动程序句柄对的列表，即返回“已安装”（已加载）的驱动程序的列表。驱动程序名称不包含“DBD::”前缀。

data_sources

@ary = DBI->data_sources($driver);

返回可通过指定驱动程序使用的可用的数据源（数据库）的列表。如果 $driver 为空或 undef，则使用 DBI_DRIVER 环境变量的值。

quote

$sql = $dbh->quote($value);
$sql = $dbh->quote($value, $data_type);

引用一个字符串文字以在 SQL 语句中用作文字值，方法是对字符串中包含的任何特殊字符（例如引号）进行转义，并添加所需的类型的外引号。

$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
                $dbh->quote("Don't");

对于大多数数据库类型，quote 会返回“Don’t”（包括外引号）。对于 quote() 方法来说，返回求值结果为所需字符串的 SQL 表达式是有效的。例如 −

$quoted = $dbh->quote("one\ntwo\0three")

may produce results which will be equivalent to

CONCAT('one', CHAR(12), 'two', CHAR(0), 'three')

Methods Common to All Handles

err

$rv = $h->err;
or
$rv = $DBI::err
or
$rv = $h->err

返回从调用的最后一个驱动程序方法中返回的原生数据库引擎错误代码。此代码通常是一个整数，但你不应进行此假设。这等效于 $DBI::err 或 $h→err。

errstr

$str = $h->errstr;
or
$str = $DBI::errstr
or
$str = $h->errstr

返回从调用的最后一个 DBI 方法中返回的原生数据库引擎错误消息。它与上述“err”方法有相同的使用寿命问题。这等效于 $DBI::errstr 或 $h→errstr。

rows

$rv = $h->rows;
or
$rv = $DBI::rows

它返回上一个 SQL 语句影响的行数，等效于 $DBI::rows。

trace

$h->trace($trace_settings);

DBI 具备非常有用的能力，可以生成运行时跟踪信息，说明它正在做什么，当试图跟踪 DBI 程序中的奇怪问题时，这可以节省大量时间。你可以使用不同的值来设置跟踪级别。这些值从 0 到 4 不等。值 0 表示禁用跟踪，4 表示生成完整跟踪。

Interpolated Statements are Prohibited

强烈建议不要使用以下插入的语句 -

while ($first_name = <>) {
   my $sth = $dbh->prepare("SELECT *
                          FROM TEST_TABLE
                          WHERE FIRST_NAME = '$first_name'");
   $sth->execute();
   # and so on ...
}