跳至内容

Perl 编程/DBI - Perl 数据库接口

来自维基教科书,开放书籍,开放世界
前一页: CPAN/Bitcard 索引 下一页: CGI

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

[编辑 | 编辑源代码]

安装 DBI 很简单。(有关安装 CPAN 模块的信息,请参见 关于 CPAN 的章节。)

安装DBD::Oracle

[编辑 | 编辑源代码]

安装DBD::Oracle时,您需要准备一些事项。

获取正确版本

[编辑 | 编辑源代码]

首先,安装DBD::Oracle时,请确保您获取 Pythian 提供的版本。Pythian 维护DBD::Oracle自 1.17 版本(2006 年 2 月)以来。

准备环境变量

[编辑 | 编辑源代码]

您需要创建环境变量ORACLE_USERIDORACLE_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

使用 DBI

[编辑 | 编辑源代码]

这里有一个非常简单的测试脚本,可以帮助您入门。

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();


前一页: CPAN/Bitcard 索引 下一页: CGI
华夏公益教科书