开发手册 欢迎您！
软件开发者资料库

Perl - 数据库访问

Perl数据库访问 - 从简单和简单的步骤学习Perl，从基本到高级概念，包括简介，环境，语法，数据类型，变量，标量，数组，哈希，IF ... ELSE，循环，运算符，日期和时间，子程序，引用，格式，文件I / O，目录，错误处理，特殊变量，编码标准，正则表达式，发送电子邮件，套接字编程，面向对象，数据库访问，CGI编程，包和模块，流程管理，嵌入式文档，功能参考。

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

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

DBI应用程序的体系结构

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

Perl数据库模块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 filehandleundef   NULL values are represented by undefined values in Perl\%attr  Reference to a hash of attribute values passed to methods

数据库连接

假设我们要使用MySQL数据库.在连接到数据库之前，请确保以下内容.如果您不了解如何在MySQL数据库中创建数据库和表，您可以学习我们的MySQL教程.

  • 您已经创建了一个名为TESTDB的数据库.

  • 您已在TESTDB中创建了一个名为TEST_TABLE的表.

  • 此表包含字段FIRST_NAME，LAST_NAME，AGE，SEX和INCOME.

  • 用户ID"testuser"和密码" test123"设置为访问TESTDB.

  • Perl模块DBI已正确安装在您的计算机上.

  • 您已经通过MySQL教程了解MySQL基础知识.

以下是连接MySQL数据库"TESTDB"的示例 :

#!/usr/bin/perluse DBIuse 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 value和$ DBI :: errstr返回错误字符串.

INSERT操作

当你想创建一些时需要INSERT操作记录到表中.这里我们使用表TEST_TABLE来创建我们的记录.因此，一旦建立了数据库连接，我们就可以在TEST_TABLE中创建记录了.以下是在TEST_TABLE中创建单个记录的过程.您可以使用相同的概念创建任意数量的记录.

记录创建采取以下步骤 :

  • 使用INSERT语句准备SQL语句.这将使用 prepare() API完成.

  • 执行SQL查询以从数据库中选择所有结果.这将使用 execute() API完成.

  • 释放Stattement句柄.这将使用 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传递实际值.以下是示例 :

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;

读取操作

读取任何数据库的操作意味着从数据库中获取一些有用的信息，即一个或多个来自一个或多个表的更多记录.因此，一旦建立了数据库连接，我们就可以对此数据库进行查询.以下是查询AGE大于20的所有记录的过程.这将需要四个步骤&减去;

  • 准备SQL基于所需条件的SELECT查询.这将使用 prepare() API完成.

  • 执行SQL查询以从数据库中选择所有结果.这将使用 execute() API完成.

  • 逐个获取所有结果并打印这些结果.这将是使用 fetchrow_array() API完成.

  • 释放Stattement句柄.这将使用 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();

使用绑定值

可能会出现未事先提供条件的情况.因此，您可以使用绑定变量，它将在运行时获取所需的值. 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();

更新操作

更新任何数据库上的操作意味着更新数据库表中已有的一个或多个记录.以下是将SEX更新为"M"的所有记录的过程.在这里，我们将所有男性的年龄增加一年.这将需要三个步骤 :

  • 根据所需条件准备SQL查询.这将使用 prepare() API完成.

  • 执行SQL查询以从数据库中选择所有结果.这将使用 execute() API完成.

  • 释放Stattement句柄.这将使用 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;

使用绑定值

可能会出现未事先提供条件的情况.因此，您可以使用绑定变量，它将在运行时获取所需的值. Perl DBI模块使用问号代替实际值，然后在运行时通过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删除所有记录的过程，其中AGE等于30.此操作将采取以下步骤.

  • 根据所需条件准备SQL查询.这将使用 prepare() API完成.

  • 执行SQL查询以从数据库中删除所需的记录.这将使用 execute() API完成.

  • 释放Stattement句柄.这将使用 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，则没有数据可用从数据库返回，所以有一个快捷方式来执行此操作.您可以使用执行语句执行以下任何命令.

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

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

COMMIT操作

提交是向数据库发出绿色信号的操作最终确定更改，在此操作之后，没有任何更改可以恢复到其原始位置.

这是一个调用 commit API的简单示例.

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

ROLLBACK操作

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

以下是调用回滚 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 Option

如果您的交易很简单，您可以省去必须发布大量的交易提交.进行连接调用时，可以指定 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,              {AutoCommit => 1})               or die $DBI::errstr;

这里RaiseError可以取值1或0.

断开数据库

要断开数据库连接，请使用 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 {} 用于将带引号的字符串返回到准备 API.但是，在WHERE子句中尝试使用NULL值时必须小心.考虑&减去;

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

将undef(NULL)绑定到占位符将不会选择具有NULL年龄的行！至少对于符合SQL标准的数据库引擎.有关此原因，请参阅数据库引擎或任何SQL书的SQL手册.要显式选择NULL，你必须说"WHERE age IS NULL".

一个常见的问题是让代码片段处理一个可以定义或取消定义的值(非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 ::'前缀.

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 toCONCAT('one', CHAR(12), 'two', CHAR(0), 'three')

所有句柄共有的方法

错误

$rv = $h->err;or$rv = $DBI::error$rv = $h->err

从最后一个调用的驱动程序方法返回本机数据库引擎错误代码.代码通常是一个整数但你不应该假设.这相当于$ DBI :: err或$ h->错误.

errstr

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

从最后一个调用的DBI方法返回本机数据库引擎错误消息.这与上述"错误"方法具有相同的寿命问题.这相当于$ DBI :: errstr或$ h-> errstr.

行

$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语句.