感谢您的光临! Learnfk在线教程网正在翻译编辑中,请您稍等(v^_^)v Learnfk在线教程网
英文链接源链接 [412]
contenteditable="true"

Perl - Database Access 介绍

本章教您如何在Perl脚本中访问数据库。从Perl 5开始,使用 DBI 模块编写数据库应用程序变得非常容易。 DBI代表Perl的数据库独立接口,这意味着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教程。

以下是连接MySQL数据库" TESTDB"的Example-

#!/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;

插入操作

要在表中创建一些记录时,需要执行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传递实际值。以下是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;

读操作

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传递。以下是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操作

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传递。以下是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;

在某些情况下,您想设置一个值,该值不会事先提供,因此可以按以下方式使用绑定值。在此Example中,所有男性的收入将设置为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的所有记录的过程。此操作将执行以下步骤。

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

如果成功,则返回true值;如果失败,则返回false值。 实际上,如果成功,它将返回受影响的行数。 在该Example中,它将返回实际删除的行数

提交操作

提交是向数据库发出commit信号以完成更改的操作,此操作完成后,任何更改都无法还原到其原始位置。

这是调用 commit API的简单Example。

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

回滚操作

如果您对所有更改都不满意,或者在任何操作之间都遇到错误,则可以还原这些更改以使用回滚API。

这是调用 rollback API的简单Example。

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

开始Transaction

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

Perl DBI模块提供了 begin_work API,该API启用事务(通过关闭AutoCommit),直到下一次调用提交或回滚为止。在下一次提交或回滚之后,将自动再次打开AutoCommit。

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

自动提交options

如果您的交易很简单,则可以省去必须提交大量提交的麻烦。进行connect调用时,可以指定 AutoCommit options,该options将在每次成功查询后执行自动提交操作。这是它的样子-

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

此处AutoCommit可以取值为1或0,其中1表示AutoCommit打开,而0表示AutoCommit关闭。

自动错误处理

进行连接调用时,可以指定一个RaiseErrorsoptions,该options将自动为您处理错误。 发生错误时,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;

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

使用NULL值

未定义的值或undef用于指示NULL值。您可以像使用非NULL值一样插入和更新NULL值的列。这些Example使用NULL值插入和更新列寿命-

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

这里的qq {}用于返回带引号的字符串以准备API。 但是,在WHERE子句中尝试使用NULL值时必须小心。 考虑-

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

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

available_drivers

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

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

installed_drivers

%drivers = DBI->installed_drivers();

返回所有已"安装"(加载)到当前进程的驱动程序的驱动程序名称和驱动程序句柄对的列表。驱动程序名称不包含" DBD ::"前缀。

数据源

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

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

引用

$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将返回'Do n'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语句。

点我分享笔记