Perl 简明教程

Perl - Database Access

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

This chapter teaches you how to access a database inside your Perl script. Starting from Perl 5 has become very easy to write database applications using DBI module. DBI stands for Database Independent Interface for Perl, which means DBI provides an abstraction layer between the Perl code and the underlying database, allowing you to switch database implementations really easily.

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

The DBI is a database access module for the Perl programming language. It provides a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used.

Architecture of a DBI Application

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

DBI is independent of any database available in backend. You can use DBI whether you are working with Oracle, MySQL or Informix, etc. This is clear from the following architure diagram.

dbiarch

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

Here DBI is responsible of taking all SQL commands through the API, (i.e., Application Programming Interface) and to dispatch them to the appropriate driver for actual execution. And finally, DBI is responsible of taking results from the driver and giving back it to the calling scritp.

Notation and Conventions

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

Throughout this chapter following notations will be used and it is recommended that you should also follow the same convention.

$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 教程。

Assuming we are going to work with MySQL database. Before connecting to a database make sure of the followings. You can take help of our MySQL tutorial in case you are not aware about how to create database and tables in MySQL database.

  1. You have created a database with a name TESTDB.

  2. You have created a table with a name TEST_TABLE in TESTDB.

  3. This table is having fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.

  4. User ID "testuser" and password "test123" are set to access TESTDB.

  5. Perl Module DBI is installed properly on your machine.

  6. You have gone through MySQL tutorial to understand MySQL Basics.

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

Following is the example of connecting with MySQL database "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 会返回一个错误字符串。

If a connection is established with the datasource then a Database Handle is returned and saved into $dbh for further use otherwise $dbh is set to undef value and $DBI::errstr returns an error string.

INSERT Operation

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

INSERT operation is required when you want to create some records into a table. Here we are using table TEST_TABLE to create our records. So once our database connection is established, we are ready to create records into TEST_TABLE. Following is the procedure to create single record into TEST_TABLE. You can create as many as records you like using the same concept.

记录创建采用以下步骤：

Record creation takes the following steps −

  1. Preparing SQL statement with INSERT statement. This will be done using prepare() API.

  2. Executing SQL query to select all the results from the database. This will be done using execute() API.

  3. Releasing Stattement handle. This will be done using finish() API.

  4. If everything goes fine then commit this operation otherwise you can rollback complete transaction. Commit and Rollback are explained in next sections.

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 传递实际值。以下是示例 −

There may be a case when values to be entered is not given in advance. So you can use bind variables which will take the required values at run time. Perl DBI modules make use of a question mark in place of actual value and then actual values are passed through execute() API at the run time. Following is the example −

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 的记录的过程。这将分四步执行 −

READ Operation on any databasse means to fetch some useful information from the database, i.e., one or more records from one or more tables. So once our database connection is established, we are ready to make a query into this database. Following is the procedure to query all the records having AGE greater than 20. This will take four steps −

  1. Preparing SQL SELECT query based on required conditions. This will be done using prepare() API.

  2. Executing SQL query to select all the results from the database. This will be done using execute() API.

  3. Fetching all the results one by one and printing those results.This will be done using fetchrow_array() API.

  4. Releasing Stattement handle. This will be done using 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 在运行时传递实际值。以下示例 −

There may be a case when condition is not given in advance. So you can use bind variables, which will take the required values at run time. Perl DBI modules makes use of a question mark in place of actual value and then the actual values are passed through execute() API at the run time. Following is the example −

$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”的记录的过程。这里我们将所有男性的年龄增加一年。这将需要三个步骤 −

UPDATE Operation on any database means to update one or more records already available in the database tables. Following is the procedure to update all the records having SEX as 'M'. Here we will increase AGE of all the males by one year. This will take three steps −

  1. Preparing SQL query based on required conditions. This will be done using prepare() API.

  2. Executing SQL query to select all the results from the database. This will be done using execute() API.

  3. Releasing Stattement handle. This will be done using finish() API.

  4. If everything goes fine then commit this operation otherwise you can rollback complete transaction. See next section for commit and rollback APIs.

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

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

There may be a case when condition is not given in advance. So you can use bind variables, which will take required values at run time. Perl DBI modules make use of a question mark in place of actual value and then the actual values are passed through execute() API at the run time. Following is the example −

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

In some case you would like to set a value, which is not given in advance so you can use binding value as follows. In this example income of all males will be set to 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 的所有记录的过程。此操作需要以下步骤。

DELETE operation is required when you want to delete some records from your database. Following is the procedure to delete all the records from TEST_TABLE where AGE is equal to 30. This operation will take the following steps.

  1. Preparing SQL query based on required conditions. This will be done using prepare() API.

  2. Executing SQL query to delete required records from the database. This will be done using execute() API.

  3. Releasing Stattement handle. This will be done using finish() API.

  4. If everything goes fine then commit this operation otherwise you can rollback complete transaction.

$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 语句按如下方式执行任何命令。

If you’re doing an UPDATE, INSERT, or DELETE there is no data that comes back from the database, so there is a short cut to perform this operation. You can use do statement to execute any of the command as follows.

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

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

do returns a true value if it succeeded, and a false value if it failed. Actually, if it succeeds it returns the number of affected rows. In the example it would return the number of rows that were actually deleted.

COMMIT Operation

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

Commit is the operation which gives a green signal to database to finalize the changes and after this operation no change can be reverted to its orignal position.

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

Here is a simple example to call commit API.

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

ROLLBACK Operation

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

If you are not satisfied with all the changes or you encounter an error in between of any operation , you can revert those changes to use rollback API.

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

Here is a simple example to call rollback API.

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

Begin Transaction

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

Many databases support transactions. This means that you can make a whole bunch of queries which would modify the databases, but none of the changes are actually made. Then at the end, you issue the special SQL query COMMIT, and all the changes are made simultaneously. Alternatively, you can issue the query ROLLBACK, in which case all the changes are thrown away and database remains unchanged.

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

Perl DBI module provided begin_work API, which enables transactions (by turning AutoCommit off) until the next call to commit or rollback. After the next commit or rollback, AutoCommit will automatically be turned on again.

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

AutoCommit Option

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

If your transactions are simple, you can save yourself the trouble of having to issue a lot of commits. When you make the connect call, you can specify an AutoCommit option which will perform an automatic commit operation after every successful query. Here’s what it looks like −

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

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

Here AutoCommit can take value 1 or 0, where 1 means AutoCommit is on and 0 means AutoCommit is off.

Automatic Error Handling

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

When you make the connect call, you can specify a RaiseErrors option that handles errors for you automatically. When an error occurs, DBI will abort your program instead of returning a failure code. If all you want is to abort the program on an error, this can be convenient. Here’s what it looks like −

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

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

Here RaiseError can take value 1 or 0.

Disconnecting Database

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

To disconnect Database connection, use disconnect API as follows −

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

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

The transaction behaviour of the disconnect method is, sadly, undefined. Some database systems (such as Oracle and Ingres) will automatically commit any outstanding changes, but others (such as Informix) will rollback any outstanding changes. Applications not using AutoCommit should explicitly call commit or rollback before calling disconnect.

Using NULL Values

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

Undefined values, or undef, are used to indicate NULL values. You can insert and update columns with a NULL value as you would a non-NULL value. These examples insert and update the column age with a NULL value −

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

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

Here qq{} is used to return a quoted string to prepare API. However, care must be taken when trying to use NULL values in a WHERE clause. Consider −

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

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

Binding an undef (NULL) to the placeholder will not select rows, which have a NULL age! At least for database engines that conform to the SQL standard. Refer to the SQL manual for your database engine or any SQL book for the reasons for this. To explicitly select NULLs you have to say "WHERE age IS NULL".

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

A common issue is to have a code fragment handle a value that could be either defined or undef (non-NULL or NULL) at runtime. A simple technique is to prepare the appropriate statement as needed, and substitute the placeholder for non-NULL cases −

$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 值将抑制此警告。

Returns a list of all available drivers by searching for DBD::* modules through the directories in @INC. By default, a warning is given if some drivers are hidden by others of the same name in earlier directories. Passing a true value for $quiet will inhibit the warning.

installed_drivers

%drivers = DBI->installed_drivers();

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

Returns a list of driver name and driver handle pairs for all drivers 'installed' (loaded) into the current process. The driver name does not include the 'DBD::' prefix.

data_sources

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

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

Returns a list of data sources (databases) available via the named driver. If $driver is empty or undef, then the value of the DBI_DRIVER environment variable is used.

quote

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

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

Quote a string literal for use as a literal value in an SQL statement, by escaping any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks.

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

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

For most database types, quote would return 'Don''t' (including the outer quotation marks). It is valid for the quote() method to return an SQL expression that evaluates to the desired string. For example −

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

Returns the native database engine error code from the last driver method called. The code is typically an integer but you should not assume that. This is equivalent to $DBI::err or $h→err.

errstr

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

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

Returns the native database engine error message from the last DBI method called. This has the same lifespan issues as the "err" method described above. This is equivalent to $DBI::errstr or $h→errstr.

rows

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

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

This returns the number of rows effected by previous SQL statement and equivalent to $DBI::rows.

trace

$h->trace($trace_settings);

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

DBI sports an extremely useful ability to generate runtime tracing information of what it’s doing, which can be a huge time-saver when trying to track down strange problems in your DBI programs. You can use different values to set trace level. These values varies from 0 to 4. The value 0 means disable trace and 4 means generate complete trace.

Interpolated Statements are Prohibited

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

It is highly recommended not to use interpolated statements as follows −

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

因此，不要使用插入的语句，而应使用 bind value 来准备动态 SQL 语句。

Thus don’t use interpolated statement instead use bind value to prepare dynamic SQL statement.