数据库访问（Database Access）

优质

小牛编辑

144浏览

2023-12-01

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

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

DBI应用程序的体系结构

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

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

符号和约定

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

$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 Module 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操作。这里我们使用表TEST_TABLE来创建记录。因此，一旦建立了数据库连接，我们就可以在TEST_TABLE中创建记录了。以下是在TEST_TABLE中创建单个记录的过程。您可以使用相同的概念创建任意数量的记录。

记录创建采取以下步骤 -

使用INSERT语句准备SQL语句。这将使用prepare() API完成。
执行SQL查询以从数据库中选择所有结果。这将使用execute() API完成。
释放Stattement句柄。这将使用finish() API finish() 。
如果一切正常，则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完成。
释放Stattement句柄。这将使用finish() API finish() 。

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对任何数据库的操作意味着更新数据库表中已有的一个或多个记录。以下是将SEX更新为“M”的所有记录的过程。在这里，我们将所有男性的年龄增加一年。这将需要三个步骤 -

根据所需条件准备SQL查询。这将使用prepare() API完成。
执行SQL查询以从数据库中选择所有结果。这将使用execute() API完成。
释放Stattement句柄。这将使用finish() API finish() 。
如果一切正常，则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操作。以下是从TEST_TABLE中删除所有记录的过程，其中AGE等于30.此操作将采取以下步骤。

根据所需条件准备SQL查询。这将使用prepare() API完成。
执行SQL查询以从数据库中删除所需的记录。这将使用execute() API完成。
释放Stattement句柄。这将使用finish() API finish() 。
如果一切正常，则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 语句

如果您正在执行UPDATE，INSERT或DELETE，则没有数据从数据库返回，因此执行此操作有一个快捷方式。您可以使用do语句执行以下任何命令。

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

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

COMMIT操作

提交是向数据库发出绿色信号以完成更改的操作，在此操作之后，任何更改都不能恢复到其原始位置。

这是一个调用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。

断开数据库 (Disconnecting Database)

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

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

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

使用NULL值

未定义的值或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 age IS NULL”。

一个常见的问题是让代码片段在运行时处理可以定义或undef（非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 : ());

其他一些DBI函数 (Some Other DBI Functions)

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为空或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（）方法返回一个计算结果为所需字符串的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方法返回本机数据库引擎错误消息。这与上述“错误”方法具有相同的寿命问题。这相当于$ 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 ...
}