perl dbi是perl访问数据库的一个接口。
简单介绍如何用perl dbi访问oracle和mysql。
1. 环境的准备
1.1 首先，操作系统环境都是安装好的，如果没有安装好，先安装windows xp。
1.2 安装Oracle 10.2 for windows
1.3 安装Mysql 5.0 for windows
1.4 安装perl 5.10 for windows
前面几步完成之后，还需要安装dbd-oracle, dbd-mysql这几个驱动。dbi只是一个接口，或者说是规范，访问具体的数据库，还需要对应的驱动文件。在命令行输入 ppm，会出来perl package管理界面，上面可以搜索dbd-mysql dbd-oracle这些包，然后安装，当然，假设机器是可以上网的。
1.5 安装vim
这一步不是必须的，个人比较习惯用vim, 安装好vim后，简单配置一下
##C:\Program Files\Vim\_vimrc
set expandtab
set tabstop=4
set shiftwidth=4

上面的设置让vim把tab转换成4个空格，自动缩进设置为4个空格.

2. 连接数据库
环境准备好了之后，就可以开始编程了，首先是连接数据库，假设oracle, mysql服务都开启了，可以正常访问。
#test.pl
use DBI;
my $oracle_conn;
my $mysql_conn;

$oracle_conn = DBI->connect("dbi:Oracle:tns_entry_1", "user", "password" ,{}) or die "Connect to oracle database error:". DBI->errstr;

my $str_conn = "dbi:mysql:database=information_schema;host=127.0.0.1;port=3306";

$mysql_conn = DBI->connect(
??? $str_conn,
??? "user", "password") or die "Connect to oracle database error:". DBI->errstr;

$oracle_conn->{FetchHashKeyName} = 'NAME_lc';
正常情况下，应当可以连上数据库，注意，连oracle用了tns,需要在tnsnames.ora里有相应的配置，
可以用tnsping tns_entry_1 测试是否可以ping通，或用sqlplus user/password@tns_entry_1测试是否可以连接。
mysql可以这样测试一下：mysql -u user -ppassword -h 127.0.0.1 -P3306

3. 连上数据库后，就可以执行sql了
执行sql的基本步骤可以简单分成这么几步:
a) prepare sql
b) bind variables
c) execute the query
d) fetch result
以下是一个简单的sample:
#test.pl
my $str_sql_ora_tabs = "select lower(table_name) table_name, lower(temporary) temporary, lower(tablespace_name) tablespace_name from dba_tables where owner = ? and rownum < 2";
my $str_sql_ora_tab_cols = "select column_name, column_id, data_type, data_length, data_precision, data_scale, nullable from dba_tab_columns where owner = ? and table_name = ? ";

sub get_ora_tab_cols() {
? my $dbconn = shift;
? my $owner = shift;
? my $table_name = shift;

? my $stmt = $dbconn->prepare($str_sql_ora_tab_cols) or die "cant prepare $str_sql_ora_tabs";
? $stmt->bind_param(1, $owner);
? $stmt->bind_param(2, $table_name);
? $stmt->execute();

? my $tabcols = $stmt->fetchall_hashref("column_name");
? print "here\n";
? return $tabcols;

}

sub get_ora_tables() {
? my $dbconn = shift;
? my $schema = shift;
?
? my $stmt = $dbconn->prepare($str_sql_ora_tabs);
? $stmt->bind_param(1, $schema);
? $stmt->execute();

? my $tmp = $stmt->fetchall_hashref("table_name");

? for my $i (keys %$tmp) {
??? print "$i, $tmp->{$i}{tablespace_name}\n";

??? my $tabcols = &get;_ora_tab_cols( $dbconn, $schema, uc($i));
??? print "$tabcols\n";

??? for my $c (keys %$tabcols) {
????? print "$c: $tabcols->{$c}{data_type}\n";
??? }
? }
}

sub get_mysql_tables() {
? my $str_conn = "dbi:mysql:database=information_schema;host=127.0.0.1;port=3306";
? my $mydb = DBI->connect(
??? $str_conn,
??? "user", "password") or die "Connect to oracle database error:". DBI->errstr;

? my $str_stmt = "select table_schema, table_name, table_type, engine from tables";

? my $stmt = $mydb->prepare($str_stmt);

? $stmt->execute();

? my @row;

? while(@row = $stmt->fetchrow_array()) {
??? print "@row \n"
?? }

? print "eof\n";
}

&get;_ora_tables($oracle_conn, "USER");
&get;_mysql_tables();

上面的sample分别从oracle和mysql的数据字典里取出了表的信息并打印，
基于这个简单的方法，可以将企业内部所有表结构的信息整合到一起，通过web的方式展现，维护。

(未完待续)
4. 上面的sample只用到了select语句，接下来介绍一下怎么运行delete,update,insert等语句，怎么控制事务。
#create table dml_test(a int, b varchar2(30), c clob, d date);
#alter table dml_test add constraint pk_dml_test_a primary key(a);
sub dml_test() {
??? my $dbconn = shift;
??? my $str_insert = "insert into dml_test(a, b, c, d) values(?,?,?, sysdate)";
??? my $str_update = "update dml_test set b = ?, d = sysdate where a = ?";
??? my $str_delete = "delete from dml_test where a = ?";
??? my $stmt;

??? $dbconn->{AutoCommit} = 0;

??? $stmt = $dbconn->prepare($str_delete);
??? $stmt->bind_param(1, 1);
??? $stmt->execute();

??? $stmt = $dbconn->prepare($str_insert);
??? $stmt->bind_param(1, 1);
??? $stmt->bind_param(2, "test");
??? $stmt->bind_param(3, "clob" x 1001);
??? $stmt->execute();

??? $stmt = $dbconn->prepare($str_update);
??? $stmt->bind_param(1, "xxxx");
??? $stmt->bind_param(2, 1);
??? $stmt->execute();

??? $dbconn->commit();
};

&dml;_test($dbconn);

4.1 array dml
sub dml_array_test() {
??? my $dbconn = shift;
??? my $str_insert = "insert /*array*/ into dml_test(a, b, c, d) values(?,?,?, sysdate)";
??? my $str_update = "update /*array*/ dml_test set b = ?, d = sysdate where a = ?";
??? my $str_delete = "delete /*array*/ from dml_test where a = ?";
??? my $stmt;

??? $dbconn->{AutoCommit} = 0;
??? $dbconn->{RaiseError} = 1;

??? my $cola = [1,2,3,4,5];
??? my $colb = ["a", "b", "c", "d", "e"];
??? my $colc = ["aaa", "bbb", "ccc", "ddd", "eee"];

??? my @colbb = qw(a b c d e);
??? my @colcc = qw(aa bb cc dd ee);
??? my @colccc = qw(aaxx bbxx ccxx ddxx eexx);

??? my %results;

??? $stmt = $dbconn->prepare($str_delete);
??? $stmt->bind_param_array(1, $cola);
??? my $rows = $stmt->execute_array({ArrayTupleStatus=>\my @status});

??? if($rows) {
??????? print "$rows records inserted\n";
??? } else {
??????? for my $status (@status) {
??????????? print "status: $status\n";
??????? }
??? }

??? $stmt = $dbconn->prepare($str_insert);
??? $stmt->bind_param_array(1, $cola);
??? $stmt->bind_param_array(2, $colb);
??? $stmt->bind_param_array(3, \@colcc);
??? $stmt->execute_array(\%results);

??? $stmt = $dbconn->prepare($str_update);
??? $stmt->bind_param_array(1, \@colccc);
??? $stmt->bind_param_array(2, $cola);
??? $stmt->execute_array(\%results);

??? $dbconn->commit();
};

&dml;_array_test($dbconn);

5. 异常处理

dbi有哪些接口，可以很方便搜索到，下面提供一个链接
http://search.cpan.org/