Perl 程式設計/DBI - Perl 資料庫介面
有一個全面的模組庫，用於從 Perl 程式連線到資料庫。它由 Tim Bunce 維護，被稱為 DBI - 資料庫介面。DBI 的主要網站是 http://dbi.perl.org/.
要使用 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();