Perl 编程/DBI - Perl 数据库接口
有一个全面的模块库,用于从 Perl 程序连接到数据库。它由 Tim Bunce 维护,被称为 DBI - 数据库接口。DBI 的主要网站是 https://dbi.perl5.cn/.
要使用 DBI 连接到数据库,您至少需要两个 CPAN 模块。一个是主要的 DBI 模块,简称 DBI。另一个是 DBD - 一个 数据库驱动程序 模块。有许多主流数据库管理系统的 DBI 驱动程序,例如 MySQL 和 Oracle。本教程中的示例涉及 Oracle;因此,Oracle 的数据库驱动程序称为 DBD::Oracle。
因此,要运行本章中的示例,您需要 DBI 和 DBD::Oracle 模块。
安装 DBI 很简单。(有关安装 CPAN 模块的信息,请参见 关于 CPAN 的章节。)
安装DBD::Oracle时,您需要准备一些事项。
首先,安装DBD::Oracle时,请确保您获取 Pythian 提供的版本。Pythian 维护DBD::Oracle自 1.17 版本(2006 年 2 月)以来。
您需要创建环境变量ORACLE_USERID和ORACLE_SID。它们需要在安装 DBD::Oracle 后测试与数据库的连接。对于ORACLE_USERID必须是您数据库的有效用户名和密码。ORACLE_SID必须设置为数据库名称,如 TNSNAMES.ORA 中所示。在类 Unix 系统上运行
export ORACLE_USERID="用户名/密码"
export ORACLE_SID="数据库名称"
在 Windows 上,您只需要ORACLE_USERID:
set ORACLE_USERID="用户名/密码@数据库名称"
完成定义这些环境变量后,从 CPAN 以通常的方式安装DBD::Oracle。
这里有一个非常简单的测试脚本,可以帮助您入门。
use strict;
use warnings;
# There is no need to use DBD::Oracle. The DBD is loaded automatically later.
use DBI;
# Connecting to the database. Take the SID from TNSNAMES.ORA.
# Here the DBD is loaded.
# $dbh will be the database handle - a variable through which
# you connect to your database.
my $dbh = DBI->connect("dbi:Oracle:SID", "username", "password", { RaiseError => 1 });
# A simple date fetch
# Prepare the SQL statement.
# $sth is a statement handle - an environment for running an SQL statement.
my $sth = $dbh->prepare('select sysdate from dual'); # note no ';' at the end of the SQL
# Execute the SQL statement; don't print it yet
$sth->execute;
# This "loop" prints all the rows (actually just one, in this case)
while (my @row = $sth->fetchrow_array) {
print "@row\n";
}
# A real SELECT statement.
# Replace my_favourite_table with a name of a table from your database.
$sth = $dbh->prepare('select * from my_favourite_table');
$sth->execute;
# This is a real loop, that prints all the rows.
# It's very rudimentary; see the DBI documentation
# for better tools for pretty printing.
#
# $sth->fetchrow_array returns a Perl array,
# in which every member represents one of the retrieved columns.
# In turn, every row is an array of values.
while (my @row = $sth->fetchrow_array) {
# replace undefined values (NULLs in the db) with the string "NULL"
@row = map { defined($_) ? $_ : "NULL" } @row;
my $row_as_string = join("\t", @row);
printf "%s\n", row_as_string;
}
# A real SELECT statement using a hash reference and place holder.
# Replace my_favourite_table with a name of a table from your database.
$sth = $dbh->prepare('select * from my_favourite_table where my_field = ?');
my $field_value = 'australia';
$sth->execute($field_value);
# Here is the loop again, this time we use fetchrow_hashref
# which makes our code more resistant to breaks due to schema changes.
# It also spares us from remember which location a field is
# positioned in an array.
# Recall that %{$var} de-references the hashref $var
# and that $var->{field} specifies the key 'field'
while (my $row = $sth->fetchrow_hashref) {
foreach my $next_field (keys %{$row}) {
if (defined $row->{$next_field}) {
print $row->{$next_field};
}
else {
print 'NULL';
}
print "\t";
}
print "\n";
}
# gracefully disconnect from the database
$dbh->disconnect();