Perl 数据库访问

上一页
下一页

本教程将教你如何在 Perl 脚本中访问数据库。从 Perl 5 开始，使用 **DBI** 模块编写数据库应用程序变得非常容易。DBI 代表 **数据库独立接口**，这意味着 DBI 在 Perl 代码和底层数据库之间提供了一个抽象层，允许您非常轻松地切换数据库实现。

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

DBI 应用程序的架构

DBI 独立于后端可用的任何数据库。无论您是使用 Oracle、MySQL 还是 Informix 等，都可以使用 DBI。从以下架构图可以清楚地看出这一点。

Perl Database Module DBI Architecture

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

符号和约定

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

$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

数据库连接

假设我们将使用 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 设置为 *undef* 值，并且 $DBI::errstr 返回错误字符串。

INSERT 操作

当您希望在表中创建一些记录时，需要使用 INSERT 操作。这里我们使用 TEST_TABLE 表来创建我们的记录。因此，一旦我们的数据库连接建立，我们就可以在 TEST_TABLE 中创建记录了。以下是创建单个记录到 TEST_TABLE 的过程。您可以使用相同的概念创建任意数量的记录。

记录创建需要以下步骤

  • 使用 INSERT 语句准备 SQL 语句。这将使用 **prepare()** API 完成。

  • 执行 SQL 查询以从数据库中选择所有结果。这将使用 **execute()** API 完成。

  • 释放语句句柄。这将使用 **finish()** API 完成

  • 如果一切正常，则 **提交** 此操作，否则您可以 **回滚** 整个事务。提交和回滚将在下一节中解释。

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;

使用绑定值

可能存在要输入的值没有预先给出。因此，您可以使用绑定变量，这些变量将在运行时获取所需的值。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;

读取操作

任何数据库上的读取操作都意味着从数据库中获取一些有用的信息，即一个或多个表中的一条或多条记录。因此，一旦我们的数据库连接建立，我们就可以对该数据库进行查询了。以下是查询所有年龄大于 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();

使用绑定值

可能存在条件没有预先给出。因此，您可以使用绑定变量，这些变量将在运行时获取所需的值。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();

更新操作

任何数据库上的更新操作都意味着更新数据库表中已有的一个或多个记录。以下是更新所有 SEX 为 'M' 的记录的过程。这里我们将所有男性的 AGE 增加一年。这将分三个步骤进行

  • 根据所需条件准备 SQL 查询。这将使用 **prepare()** API 完成。

  • 执行 SQL 查询以从数据库中选择所有结果。这将使用 **execute()** API 完成。

  • 释放语句句柄。这将使用 **finish()** API 完成

  • 如果一切正常，则 **提交** 此操作，否则您可以 **回滚** 整个事务。有关提交和回滚 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;

使用绑定值

可能存在条件没有预先给出。因此，您可以使用绑定变量，这些变量将在运行时获取所需的值。Perl DBI 模块使用问号代替实际值，然后在运行时通过 execute() API 传递实际值。以下是一个示例

$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 操作。以下是从 TEST_TABLE 中删除所有 AGE 等于 30 的记录的过程。此操作将采取以下步骤。

  • 根据所需条件准备 SQL 查询。这将使用 **prepare()** API 完成。

  • 执行 SQL 查询以从数据库中删除所需的记录。这将使用 **execute()** API 完成。

  • 释放语句句柄。这将使用 **finish()** API 完成

  • 如果一切正常，则 **提交** 此操作，否则您可以 **回滚** 整个事务。

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

使用 do 语句

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

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

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

提交操作

提交是向数据库发出最终确认更改的操作，此操作后无法将任何更改恢复到其原始位置。

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

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

回滚操作

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

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

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

开始事务

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

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

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

AutoCommit 选项

如果您的事务很简单，您可以避免发出大量提交的麻烦。在进行连接调用时，您可以指定一个 **AutoCommit** 选项，该选项将在每次成功查询后执行自动提交操作。以下是它的外观

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

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

自动错误处理

在进行连接调用时，您可以指定一个 RaiseErrors 选项，该选项会自动为您处理错误。发生错误时，DBI 将中止您的程序，而不是返回失败代码。如果您只想在发生错误时中止程序，这可能很方便。以下是它的外观

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

这里 RaiseError 可以取值 1 或 0。

断开数据库连接

要断开数据库连接，请使用 **disconnect** API，如下所示

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

disconnect 方法的事务行为，遗憾的是，是未定义的。一些数据库系统（如 Oracle 和 Ingres）将自动提交任何未完成的更改，但其他系统（如 Informix）将回滚任何未完成的更改。不使用 AutoCommit 的应用程序应在调用 disconnect 之前显式调用 commit 或 rollback。

使用 NULL 值

未定义值或 undef 用于指示 NULL 值。您可以像使用非 NULL 值一样插入和更新具有 NULL 值的列。这些示例使用 NULL 值插入和更新 age 列

$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）绑定到占位符不会选择 age 为 NULL 的行！至少对于符合 SQL 标准的数据库引擎而言。有关此原因，请参阅您的数据库引擎的 SQL 手册或任何 SQL 书籍。要显式选择 NULL，您必须说“WHERE age IS 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 : ());

其他一些 DBI 函数

available_drivers

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

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

installed_drivers

%drivers = DBI->installed_drivers();

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

data_sources

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

返回通过指定驱动程序可用的数据源（数据库）列表。如果 $driver 为空或未定义，则使用 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')

所有句柄的公共方法

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 表示生成完整跟踪。

禁止使用插值语句

强烈建议不要使用以下插值语句

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

因此，不要使用插值语句，而是使用 **绑定值** 来准备动态 SQL 语句。

打印页面
上一页
下一页
广告