本教程将教您如何在Perl脚本中访问数据库。从Perl 5开始,使用 DBI 模块编写数据库应用程序变得非常容易。 DBI代表Perl的数据库独立接口,这意味着DBI在Perl代码和基础数据库之间提供了一个抽象层,使您可以真正轻松地切换数据库实现。
DBI是Perl编程语言的数据库访问模块。它提供了一组方法,变量和约定,它们提供了一致的数据库接口,而与所使用的实际数据库无关。
DBI应用程序的体系结构
DBI独立于后端中可用的任何数据库。无论是与Oracle,MySQL还是Informix等一起使用,都可以使用DBI。从以下架构图可以清楚地看出这一点。
在这里,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教程。
-
您已经创建了一个名称为TESTDB的数据库。
-
您已经在TESTDB中创建了一个名称为TEST_TABLE的表。
-
此表的字段为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::错误;
If a connection is established with the datasource then a Database Handle is returned and saved into $dbh for further use otherwise $dbh is set to undef value and $DBI::错误 returns an error string.
插入操作
要在表中创建一些记录时,需要执行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::错误;
$sth->finish();
$dbh->commit or die $DBI::错误;
使用绑定值
在某些情况下,可能不会事先给出要输入的值。因此,您可以使用绑定变量,该变量将在运行时获取所需的值。 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::错误;
$sth->finish();
$dbh->commit or die $DBI::错误;
读操作
读操作 on any databasse means to fetch some useful information from the database ie one or more records from one or more tables. So once our database connection is established, we are ready to make a query into this database. Following is the procedure to query all the records having AGE greater than 20. This will take four steps
-
基于所需条件的SQL SELECT查询。这将使用 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::错误;
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::错误;
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操作
UPDATE操作 on any databasse means to update one or more records already available in the database tables. Following is the procedure to update all the records having SEX as 'M'. Here we will increase AGE of all the males by one year. This will take three steps
-
根据所需条件进行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::错误;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::错误;
使用绑定值
有时可能没有事先给出条件。因此,您可以使用绑定变量,该变量将在运行时获取所需的值。 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::错误;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::错误;
在某些情况下,您希望设置一个事先未提供的值,因此可以如下使用绑定值。在此示例中,所有男性的收入将设置为10000。
$sex='M';
$income=10000;
my $sth=$dbh->prepare("UPDATE TEST_TABLE
SET INCOME=?
WHERE SEX=?");
$sth->execute( $income, '$sex') or die $DBI::错误;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
删除操作
要从数据库中删除某些记录时,需要执行DELETE操作。以下是从TEST_TABLE删除AGE等于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::错误;
print "Number of rows deleted :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::错误;
Using do Statement
如果您正在执行UPDATE,INSERT或DELETE,则没有从数据库返回的数据,因此执行此操作有捷径。您可以使用 do 语句执行以下任何命令。
$dbh->do('DELETE FROM TEST_TABLE WHERE age =30');
do 如果成功则返回true值,如果失败则返回false值。实际上,如果成功,它将返回受影响的行数。在该示例中,它将返回实际删除的行数。
提交操作
提交是向数据库发出绿色信号以完成更改的操作,此操作完成后,任何更改都无法还原到其原始位置。
这是调用 commit API的简单示例。
$dbh->commit or die $dbh->错误;
回滚操作
如果您对所有更改都不满意,或者在两次操作之间都遇到错误,则可以还原这些更改以使用 rollback API。
这是调用 rollback API的简单示例。
$dbh->rollback or die $dbh->错误;
开始交易
许多数据库支持事务。这意味着您可以进行一堆查询,这些查询将修改数据库,但实际上未进行任何更改。然后最后发出特殊的SQL查询 COMMIT ,所有更改都同时进行。或者,您可以发出查询ROLLBACK,在这种情况下,所有更改都将被丢弃,数据库保持不变。
Perl DBI模块提供了 begin_work API,该API启用事务(通过关闭AutoCommit),直到下一次调用提交或回滚为止。在下一次提交或回滚之后,将自动再次打开AutoCommit。
$rc =$dbh->begin_work or die $dbh->错误;
自动提交options
如果您的交易很简单,则可以省去必须提交大量提交的麻烦。进行connect调用时,可以指定 AutoCommit options,该options将在每次成功查询后执行自动提交操作。看起来是这样的:
my $dbh=DBI->connect($dsn, $userid, $password,
{AutoCommit => 1})
or die $DBI::错误;
此处AutoCommit可以取值为1或0,其中1表示AutoCommit打开,而0表示AutoCommit关闭。
自动错误处理
进行连接调用时,可以指定一个RaiseErrorsoptions,该options将自动为您处理错误。发生错误时,DBI将中止您的程序,而不返回错误代码。如果只想在出错时中止程序,这将很方便。看起来是这样的:
my $dbh=DBI->connect($dsn, $userid, $password,
{RaiseError => 1})
or die $DBI::错误;
这里RaiseError可以取值1或0。
断开数据库
要断开数据库连接,请使用 disconnect API,如下所示:
$rc=$dbh->disconnect or warn $dbh->错误;
不幸的是,断开连接方法的事务行为是不确定的。某些数据库系统(例如Oracle和Ingres)将自动提交任何未完成的更改,而其他数据库系统(例如Informix)将回滚任何未完成的更改。不使用AutoCommit的应用程序应在调用断开连接之前显式调用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"。
一个常见的问题是让代码片段处理一个在运行时可以定义或未定义(非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->数据源($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将返回'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
Returns the native database engine error code from the last driver method called. The code is typically an integer but you should not assume that. This is equivalent to $DBI::err or $h->err.
错误
$str=$h->错误; or $str=$DBI::错误 or $str=$h->错误
Returns the native database engine error message from the last DBI method called. This has the same lifespan issues as the "err" method described above. This is equivalent to $DBI::错误 or $h->错误.
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语句。
