• 首页
• 新闻中心
  • 内部新闻
  • 公共新闻
  • 业界新闻
  • 产品新闻
• 产品应用
  • 程序开发工具
  • 企业级应用
  • 服务器软件
  • 应用工具
  • 数字娱乐
  • 硬件
• 解决方案
  • 系统架构经验谈
  • 系统部署应用
  • 办公自动化
  • 案例集锦
  • 参考手册
• 开源应用支持
  • 认识Unix
  • 系统管理
  • 网络管理
  • 桌面应用
  • 存储备份
  • 引导安装
  • 服务应用
  • 群集应用
  • 数据库
  • 系统安全
• 程序设计与开发
  • Perl编程
  • 设计与开发
  • 应用编程开发
  • Web设计与编程
• 关于我们
  • 关于本站

Perl DBI数据库访问使用入门

2014-04-25 18:07:09 阿炯

自Perl 5中已经变得非常容易使用DBI编写数据库应用程序，DBI代表独立于数据库的接口，Perl DBI为Perl代码和底层数据库之间提供了一个抽象层，即可以很容易地切换数据库的实现方式。

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

DBI应用程序的体系结构

DBI是独立于现有的任何数据库在后端。无论你正在使用Postgresql，MySQL，Informix等，您可以使用DBI。下面是一个结构图。


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 - by www.yiibai.com
undef   NULL values are represented by undefined values in Perl
\%attr  Reference to a hash of attribute values passed to methods

$dsn    驱动程序对象的句柄
$dbh    一个数据库对象的句柄
$sth    一个语句或者一个查询对象的句柄
$h      通用的句柄 ($dbh, $sth, 或 $drh)，依赖于上下文
$rc     操作代码返回的布什值（true 或 false）
$rv     操作代码返回的整数值
@ary    查询返回的一行值的数组（列表）
$rows   操作代码返回的行数值
$fh     文件句柄
undef   NULL 值表示未定义
\%attr  引用属性的哈希值并传到方法上

DBI 接口 API

下面为最为常见与使用的接口函数，可以满足在 Perl 程序中使用 SQLite 数据库的基本需求；若需要了解更多细节，请查看 Perl DBI 官方文档。

序号	API & 描述
1	DBI->connect($data_source, "", "", \%attr) 建立一个到被请求的 $data_source 的数据库连接或者 session。如果连接成功，则返回一个数据库处理对象。 数据源形式如下所示：DBI:SQLite:dbname='test.db'。其中，SQLite 是 SQLite 驱动程序名称，test.db 是 SQLite 数据库文件的名称。如果文件名 filename 赋值为 ':memory:'，那么它将会在 RAM 中创建一个内存数据库，这只会在 session 的有效时间内持续。 如果文件名 filename 为实际的设备文件名称，那么它将使用这个参数值尝试打开数据库文件；如果该名称的文件不存在，那么将创建一个新的命名为该名称的数据库文件。 可以保留第二个和第三个参数为空白字符串，最后一个参数用于传递各种属性。
2	$dbh->do($sql) 该例程准备并执行一个简单的 SQL 语句。返回受影响的行数，如果发生错误则返回 undef。返回值 -1 意味着行数未知，或不适用 ，或不可用。在这里，$dbh 是由 DBI->connect() 调用返回的处理。
3	$dbh->prepare($sql) 该例程为数据库引擎后续执行准备一个语句，并返回一个语句处理对象。
4	$sth->execute() 该例程执行任何执行预准备的语句需要的处理。如果发生错误则返回 undef。如果成功执行，则无论受影响的行数是多少，总是返回 true。在这里，$sth 是由 $dbh->prepare($sql) 调用返回的语句处理。
5	$sth->fetchrow_array() 该例程获取下一行数据，并以包含各字段值的列表形式返回。在该列表中，Null 字段将作为 undef 值返回。
6	$DBI::err 这相当于 $h->err。其中，$h 是任何的处理类型，比如 $dbh、$sth 或 $drh。该程序返回最后调用的驱动程序（driver）方法的数据库引擎错误代码。
7	$DBI::errstr 这相当于 $h->errstr。其中，$h 是任何的处理类型，比如 $dbh、$sth 或 $drh。该程序返回最后调用的 DBI 方法的数据库引擎错误消息。
8	$dbh->disconnect() 该例程关闭之前调用 DBI->connect() 打开的数据库连接。

数据库连接

假设要使用MySQL数据库。连接到数据库之前，请确保以下：
已经创建了数据库 TESTDB。
已经创建表TEST_TABLE 在 TESTDB中。
这个表有以下几个字段 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返回一个错误字符串。

插入操作

要创建或写入表TEST_TABLE记录时，INSERT操作是必需的。因此一旦建立数据库连接，准备创建记录TEST_TABLE。以下是创建单记录到TEST_TABLE的步骤。以类似的方式，可以创建多条记录。记录创建需要以下步骤：
准备SQL语句使用INSERT语句。这项工作将使用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;
 
使用绑定值

当没有事先给出被输入的值时，有可能出现一种情况。在这样的情况下，绑定的值被使用。使用一个问号代替实际值和实际值，然后通过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基于SQL查询所需的条件。这项工作将使用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();

使用绑定值

还没有事先给出的条件时，有可能出现一种情况。在这样的情况下，绑定的值被使用。采用的是一个问号代替实际值和实际值，然后通过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;

使用绑定值

如果没有事先给出的条件时，有可能出现一种情况。在这样的情况下，绑定的值被使用。采用的是一个问号代替实际值和实际值，然后通过 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所有年龄等于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 返回一个值假如它执行成功, 如果失败返回false值。其实，如果成功的话返回受影响的行数。在这个例子中，它会返回实际删除的行数。

提交操作

Commit是操作数据库来完成的变化给出了一个绿色的信号，此操作后没有变化，可以恢复。下面是一个简单的例子来调用commit API.

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

回滚操作

如果不满意的变化，想恢复这些变化，然后使用 rollback API，下面是一个简单的例子来调用 rollback API.

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

开始事务

许多数据库支持事务。这意味着可以做一大堆的查询将要修改的数据库，但实际上是没有变化。然后在结束时发出特殊的SQL查询语句：COMMIT，然后所有的修改同时进行。或者可以发出查询 ROLLBACK，在这种情况下，所有查询都被丢弃。

begin_work API使事务（通过关闭自动提交），直到下一次调用commit或rollback。一个COMMIT或ROLLBACK后，自动提交将被自动打开了。
$rc  = $dbh->begin_work   or die $dbh->errstr;

该方式就不需要在连接数据库的时候设置 AutoCommit＝0 。可以一次数据库连接进行多次事务操作，不用每一次事务的开始都去连接一次数据库。

#####################
##这里执行一些 SQL 操作
#####################

$dbh->commit; # 成功后操作
-----------------------------
$dbh->rollback; # 失败后回滚

自动提交选项

如果事务很简单，可以保存，不用发出了很多提交。当您连接调用，可以指定一个自动提交选项，将执行自动提交操作后，每一个成功的查询。这里它看起来像：
my $dbh = DBI->connect($dsn, $userid, $password, {AutoCommit => 1})   or die $DBI::errstr;

在这里，将AutoCommit值设置为1或0。在连接的时候设置了AutoCommit为false，也就是说当对数据库进行更新操作的时候，它不会自动地把那些更新直接写到数据库里，而是要程序通过 $dbh->commit 来使数据真正地写到数据库里或 $dbh->rollback 来回滚刚才的操作。

自动错误处理

当连接并引发错误时可指定调用一个处理的选项，自动处理错误。当发生错误时，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）将回滚任何未完成的更改。 不使用自动提交的应用程序必须显式的调用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{} q引号的字符串prepare API用于返回。

但是，必须小心当想要在一个WHERE子句中使用NULL值的时候。试想一下：
SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

绑定一个undef（NULL）的占位符不会选择行有一个NULL的年龄！至少在符合SQL标准的数据库引擎。这种情况的原因，请参阅SQL手册，为您的数据库引擎或SQL书。要明确地选择你必须说“年龄WHERE IS NULL”空值。

一个常见的问题是有一个代码片段处理的值可以在运行时定义为undef（非NULL 或 NULL）。一个简单的方法是根据需要准备相应的语句, and substitute the placeholder for non-NULL cases:和非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);

DBD::*模块通过@INC目录中搜索所有可用驱动程序返回一个列表，默认情况下，发出警告，如果某些驱动程序先前目录中隐藏和其他有相同的名称。传递一个值为$quiet ，将抑制警告（不发出警告）。

installed_drivers
%drivers = DBI->installed_drivers();
返回一个列表的驱动程序名称和驱动程序句柄对所有驱动程序的安装（装载）到当前进程。驱动程序的名称不包括DBD::“前缀。

data_sources
@ary = DBI->data_sources($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");
对于大多数的数据库类型，引用将返回“Don't”（包括外引号）。 '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 or $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语句，并找出它是如何运行的查询。如果你有很多类似的查询，这是浪费时间的。

其次，它不会工作，如果$first_name包含奥布莱恩或D'Fecto的或一些其他的名字以'的名称，如 '. The ' 在SQL中有特殊的含义，并且该数据库将无法理解，当你问它准备一个SQL语句。

最后，如果你要构建根据用户输入的查询，然后简单地插入直接输入到查询它是不安全的，因为用户可以构建，企图欺骗你的程序做的事情，而没意料到一个陌生的输入。例如，假设用户输入以下变量$input怪异的值：
x' or first_name = first_name or first_name = 'y

现在，我们的查询变得非常令人吃惊的事情：
SELECT * FROM TEST_TABLE WHERE first_name = 'x'  or first_name = first_name or first_name = 'y'

这个查询，我们的用户希望部分是第二个or子句。 这个子句选择所有记录的的first_name是等于first_name，也就是说，所有的人。

因此，不使用插值语句，而不是使用绑定值来编写动态SQL语句。