Perl DBI数据库访问

Perl DBI数据库管理，Perl MySQL，学习例子，实例代码，在线教程等。

本次会议将教你如何使用PERL访问Oracle数据库和其他数据库。

Oraperl模块以访问Oracle，请查看Oraperl手册。
DBI模块使用通用的方式来访问数据库。在本章中，我们将讨论这个模块。

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

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

DBI应用程序的体系结构

DBI是独立于现有的任何数据库在后端。无论你正在使用Oracel，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.gitbook.net
  undef   NULL values are represented by undefined values in Perl
  \%attr  Reference to a hash of attribute values passed to methods

数据库连接

假设我们要使用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(); // by www.gitbook.net
  $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;

自动提交选项

如果你的事务很简单，你可以保存，不用发出了很多提交。当您连接调用，您可以指定一个自动提交选项，将执行自动提交操作后，每一个成功的查询。这里它看起来像：

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

在这里，将AutoCommit值设置为1或0。

自动错误处理

当你连接调用，你可以指定一个引发错误的选项，自动为您处理错误。当发生错误时，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语句。