Perl DBI编程 的读书笔记，原文见：

http://www.ibm.com/developerworks/cn/linux/l-cn-perldbi/

为了和数据库进行通讯，Perl 的社区开发出了统一数据库通讯接口模块：DBI。DBI 作为 Perl 语言中和数据库进行通讯的标准接口，它定义了一系列的方法，变量和常量，成功地提供一个和具体数据库平台无关的数据库持久层。

回页首

DBI 模块的体系结构

概述

整个 DBI 模块的结构可以被分成两个主要的部分：DBI 模块本身和实现与具体数据库平台通讯的驱动模块。DBI 模块用于定义提供给 Perl 开发者使用的编程接口，和对不同数据库驱动模块的调用方法。具体的数据库通讯驱动模块的实现和特定的数据库平台有关，并且负责和具体数据通讯的实际操作。

DBI 模块中的三种句柄

在 DBI 模块的定义中，用户可以创建三种不同类型的句柄。

• 驱动模块句柄（Driver Handle）：驱动模块句柄代表一个在内存中加载的驱动，它在 DBI 模块加载其对应的驱动模块被创建。它和实际的驱动模块之间是一一对应的关系。驱动模块句柄提供了两个主要的方法是 data_sources() 和 connect()。
• 数据库句柄（Database Handles）：数据库句柄是程序员使用 DBI 模块和后台数据库进行通讯的第一步，它包含了一个到特定数据库的某个独立的连接。表一给出了对于常见数据库的连接字符串：
• 执行语句句柄（Statement Handles）：执行语句句柄在 DBI 规范中被定义为和数据库进行交互和操作的接口。这些句柄包装了一条条 SQL 语句，并将它们交送给后台数据库执行。一个使用执行语句句柄执行 SQL 语句的例子见程序一。

表 1. 常见数据库的连接字符串

清单 1. 使用 DBI 模块连接数据库并执行 SQL 语句

 $dbh = DBI->connect ($connection_string, $userid, $passwd);  $sth = $dbh->prepare (“SELECT * FROM tbl”);  $sth->execute();  while (@row = $sth->fetchrow_array()) {   print “Each record: @row \n”;  }  $sth->finish();  $dbh->disconnect();

建立和释放连接

使用 DBI 模块提供的 connect（）方法创建一个数据库句柄是，程序员必须提供一个数据源用于指定要连接的数据库。DBI 模块的规范要求数据源的名字并须以 dbi: 开头，然后接上数据库通讯驱动模块的名字并且以‘ : ’结尾，比如‘ dbi:db2: ’。和 connect（）方法相对应，DBI 模块中还定义了一个 disconnect（）方法。

通过调用 DBI->available_drivers（）方法，程序员可以得到已安装在当前机器上的所有数据库通讯驱动模块的列表。接着用驱动模块名作为参数，调用 DBI->data_sources（）方法，可以得到对应此驱动模块的所有数据源的列表，程序二给出了一个这样的例子 .

清单 2. 得到所有支持驱动模块的所有数据源

 my @drivers = DBI->available_drivers();  die “No dirvers defined! \n” unless @drivers;  foreach my $driver (@drivers) {   print “Driver: $driver \n”;   my @data_sources = DBI->data_sources ($driver) {   foreach my $data_source (@data_sources) {     print “\t Data Source: $data_source \n”;   }  print “\n”;  }

错误处理

在 DBI 模块中提供了两种错误处理的方法。第一种方法依靠程序员手工检测被调用方法的返回值；第二种方法通过 DBI 模块对错误进行自动检测，这种方法类似于程序的异常处理机制。对于手工错误处理，可以通过将“PrintError”和“RaiseError”两个变量设定为 0 来激活。在默认情况下，“PrintError”参数是被激活的。

方法一，在调用 DBI 的 connect（）函数的参数中设置：

 %attr = (PrintError => 0, RaiseError=>0);  my $dbh = DBI->connect (“dbi:Oracle:testdb”, “username”, “password”, \%attr);

方法二，在数据库句柄中直接设置：

 $dbh->{PrintError} = 0; $dbh->{RaiseError} = 0;

对于自动错误检测，DBI 提供了两种不同级别的错误处理方法。用于句柄的“PrintError”参数在被设置为 1 的时候，DBI 模块会调用 warn（）函数进行错误处理。它会将错误信息打印到屏幕，但是并不会中止进程。而用于句柄的“RaiseError”参数在被设置为 1 的时候，DBI 模块会调用 die（）函数，并且中止进程。这两个不同级别的错误处理方法可以在 DBI 模块的任何一个有效的句柄中被激活。

除了错误处理手段以外，DBI 模块还提供了对错误信息进行诊断的方法。这些方法可以对于任何一个有效的句柄进行使用，它们的返回值包括错误号和错误信息。

 $rv = $h->err(); $str = $h->errstr(); $str = $h->state();

$h->err（）方法返回一个由底层数据库生成的错误号；$h->errstr（）方法则返回一个由底层数据库生成的错误信息描述；$h->state（）方法返回一个 5 位的 SQLSTATE 错误字符串。除了上面的三种方法会返回错误信息供排错之外，在 DBI 模块级别，$DBI::err，$DBI::errstr，$DBI::state 会返回和上述函数同样的值。一个利用 DBI 模块内置错误处理方法的例子见程序三。

清单 3. 利用 DBI 模块的内置错误处理方法

 while (1) {  my $dbh;  # disable automatic error handle  until {   $dbh = DBI->connect ($connection_string, $userid, $username);   $dbh->{PrintError} = 0;   $dbh->{RaiseError} = 0;   warn “Unable to connect: $DBI::errstr. sleep for 5 minutes. \n”;   sleep (300);  }  # enable automatic error handle  eval {  $dbh->{RaiseError} = 1;  my $sth = $dbh->prepare (“SELECT foo, bar from tbl”);  while (1) {   $sth->execute();   while ( my @row = $sth->fetchrow_array()) {   print “Row: @row \n”;  }  sleep 60;  }  }  warn “Monitoring aborted by error: $@\n” if $@;  sleep 5;  }

回页首

操作数据库

执行简单查询

应用程序与数据库之间最常见的操作是从数据库中查询并且提取数据。在标准的 SQL 语句规范中，这一过程是使用关键字 SELECT 的。

一个通过 DBI 执行的标准简单查询包括以下四个阶段：

1. 准备阶段（Perpare SQL statement）：

   通过调用 prepare（）方法，准备阶段解析 SQL 语句，对 SQL 语句进行验证，并且返回一个执行语句句柄，这个句柄代表将在数据库内被执行的这条 SQL 语句。

2. 执行阶段（Execute select statement）：

   通过调用 execute（）方法，执行阶段执行 SQL 语句，查询数据库，并且以被查询的数据填充 Perl 的数据结构。但是，在这一阶段中，你的 Perl 应用程序并未真正地访问到被查询的数据。

3. 数据抽取阶段（fetching date）：

   第三阶段被称为数据抽取阶段，在这一阶段实际的数据从数据库中被抽取出来，通过调用 fetch（）方法族的一组方法。数据抽取阶段从数据库得到查询所得的数据，以每一条数据为单位，注入 Perl 的数据结构。DBI 提供了多种方法对数据进行抽取，可以将被抽取的数据用一个列表，一个指向数组的引用，或是一个指向哈希表的引用的方式提供给应用程序。而且每条记录中字段的顺序也是由 SQL 语句中指定的顺序所决定。一个包含三种方法的例子见程序四。

4. 完成阶段（finishing date fetch）：

   最后一个阶段被称为完成阶段，这个阶段主要释放资源，并且清理相关数据结构中保存的历史信息，通过显式地调用 finish（）方法来完成。当一个执行语句句柄（statement handler）被成功执行后，它的状态会被标记成为活跃。你可以通过访问执行语句句柄的 Active 属性来访问它。在用户执行 fetch（）方法，从数据库中抽取了最后一列数据之后，数据库驱动自动关闭数据库中正在进行地和这个执行语句句柄有关的工作，并且重置 Active 属性为不活跃状态。这一切工作都是在读取了最后一列数据之后被自动触发，在大多数情况下，用户并不需要额外地关心这一过程中后台所作的工作。某些额外情况，需要应用程序主动调用 finish（）方法释放资源。一个典型的例子就是：当数据库占用了数目可观的磁盘空间存储临时文件，存放查询结果，而应用程序又不需要保存所有的查询结果。比如执行一条形如 “SELECT EMP_DEP, count(*) FROM EMP GROUP BY EMP_DEP ORDER BY count(*) DESC”的 SQL 语句，在应用程序只需要部分统计结果的情况，应该显示调用 finish（）方法，释放被申请和占用的机器资源。

清单 4. 三种不同的数据抽取方法

 $sth->execute();  # fetch data by an array  while ( @row = $sth->fetchrow_array() ) {   print “Column1: $row[0] \t Column2: $row[1]. \n”;  }  # fetch data by a reference pointing to array  while ( $array_ref = $sth->fetchrow_arrayref() ) {   print “Column1: $array_ref->[0] \t Column2: $array_ref->[1]. \n”;  }  # fetch data by a reference pointing to hash table  while ( $hash_ref = $sth->fetchrow_hashref() ) {   print “Column1: $hash_ref->{column1} \t Column2: $hash_ref->{column2}. \n”;  }

执行非查询语句

在数据库的常用 DML 语句中，除了 SELECT 语句之外，还有 INSERT，DELETE，UPDATE 三种， 我们统称这三种语句为非查询语句。与查询语句 SELECT 不同，它们只是改变了数据库中的部分纪录，而不会返回一个记录集给应用程序。所以，相对对于查询语句中的 prepare-execute-fetch-deallocate 序列来说，非查询语句不需要数据抽取阶段，同时也可以将 prepare 和 execute 阶段用一个 do（）方法来完成。一个调用 do（）方法的例子如下 :

  $affected_row_number = $dbh->do (“DELETE FROM tbl WHERE foo = ‘ bar ’”);

DBI 模块提供了一个 do（）方法用以简化工作，取代了原先需要被调用的 prepare（）和 execute（）方法。实际上，DBI 模块中的 do（）方法只是简单地包含了 prepare（）和 execute（）方法。这种方法在使用添写方法生成 SQL 执行语句的时候和分别使用 prepare（）方法和 execute（）方法没有任何性能上面的区别，但是如果使用了参数绑定的 SQL 执行语句生成方法，两者在性能上将会有显著地差别。因为使用 do（）方法的话，以 INSERT 操作为例，对于每条被插入的记录，数据库必须解析每条插入语句，并且生成执行计划。然而使用 prepare（）方法的话，可以在 prepare（）方法中使用占位符，而使得所有的插入语句可以共用一个执行计划，达到了提升效率的目地，具体的例子见程序六。

参数绑定

在准备阶段，有一个重要的概念就是数据绑定，与之相关的有三个术语：占位符（placeholder），参数（parameter）和绑定（binding），他们都是用来根据程序上下文动态地生成 SQL 语句。将变量放入 SQL 语句的方法有两种：第一种通过添写方法生成 SQL 语句（interpolated SQL statement creation），该方法将变量直接通过字符串和 SQL 语句的其它部分连接起来，生成可以被执行的 SQL 语句。程序五展示了一个利用 Perl 字符串技术生成的一个 SQL 执行语句。

清单 5. 使用 Perl 字符串技术动态生成 SQL 执行语句的例子

 foreach $table_name ( qw (table1, table2, table3) ) {  $sth = $dbh->prepare(“SELECT count(*) FROM $table_name”);  $sth->execute();  }

第二种方法在 SQL 语句中使用占位符，并且通过 bind_param（）方法将变量与之绑定，生成 SQL 语句。无论何时，bind_param（）方法必须在 execute（）方法之前被调用，否则被绑定的参数无法填入 SQL 语句中，而对 SQL 语句的调用也注定会失败。采用绑定方法是成 SQL 语句（Bind value SQL statement creation）的一个典型的例子见程序六。

清单 6. 使用占位符动态生成 SQL 执行语句的例子

  $sth = $dbh->prepare (“SELECT foo, bar FROM table WHERE foo = ? AND bar = ?”);   $sth->bind_param (1, “FOO”);   $sth->bind_param (2, “BAR”);   $sth->execute();

第一种方法使用 Perl 的字符串处理函数，生成一条完整的 SQL 语句，并将它送往后台数据库；而绑定方法则不同，它将含有占位符的 SQL 语句和绑定值分开传送给数据，在数据后台处理数据绑定，然后执行绑定后的 SQL 语句。两种不同的处理方法将会带来性能上的巨大差异，尤其是在有大量相似的 SQL 语句需要被处理的情况下。主流的大型数据库都有一种被称为“Shared SQL Cache”的部件，它存储了诸如查询语句执行计划之类的辅助数据结构，帮助数据库执行 SQL 语句。在得到一个新的处理 SQL 语句的请求时，如果 SQL 语句已经存在于“Shared SQL Cache”中，数据库就不需要重新处理这条语句，而可以重用 Cache 中存储的信息，这样就可以带来性能上的显著提升。

调用存储过程

存储过程运行在后台数据库上，可以有效地减少客户端和数据库之间的通讯量。在这种工作模式下，客户端不再需要将每一条需要被执行的 SQL 语句发往后台数据库，借助存储过程可以将所有要执行的 SQL 语句定义在一个存储过程内，统一执行并且返回运算结果给客户端。

在 DBI 模块中有一个和 bind_param（）方法相似的方法，叫做 bind_param_inout（）方法，可以从执行语句句柄中直接返回值。这个方法最主要的应用在于调用存储过程用来接收参数和返回结果。需要注意的是，某些数据库（比如 MySql）不支持这种方法。bind_param_inout（）方法通过传递引用的方式向数据库传入一个可以接受返回值的参数，一个简单的例子如下：

  $sth->bind_param_inout (1, \$bar, 50);

上面例子当中，bind_param_inout（）方法的第三个参数是返回值的最大长度。

一个在 DB2 数据库中调用存储过程的完整示例见程序七。

清单 7. 调用 DB2 数据库中存储过程的例子

 create procedure proc (in foo char(6), out bar integer)  specific proc_example  dynamic result sets 0  modifies sql data  not deterministic  language sql  begin atomic   insert into tbl ( ‘ foo ’ , current date);   select count(*) into bar from tbl where foo = ‘ foo ’ ;  end  # start perl script to call sql procedure  $sth = $dbh->prepare (“CALL proc (?,?)”);  $sth->bind_param (1, $foo);  $sth->bind_param_inout (2, \$bar, 50);  $sth->execute();  print “stored procedure returned $bar. \n”;

执行数据库事务

数据库事务是一种将一组相互之间有密切关系的 SQL 语句放到一起执行的技术，它们要么都被成功执行，要么都执行失败，我们称之为“all-or-nothing”模式。一个事务从它的第一条可被执行的 SQL 语句开始，到被提交（commit）或者回滚（rollback）结束。如果一个事务被提交，那么它对数据库所作的所有修改都会被保存并且对其它并发过程可见；如果一个事务被回滚，它对数据库所作的所有修改都会被放弃。

并不是所有的数据库软件都支持事务，但是对于所有支持事务的数据库，DBI 模块提供了统一的接口用于操作事务。尽管数据库的实现各不相同，DBI 模块提供了事务自动提交（auutomatic transcation committing）和手工事务处理（powerful manual transaction）的两种不同处理方式。如果用户在创建一个数据库句柄（database handle）时，将句柄的参数“AutoCommit”设置为 1，那么通过这个数据库句柄执行的每一条 SQL 语句的操作都会被立即提交，而不需要任何显式语句进行提交或回滚；反之，如果参数“AutoCommit”被设置为 0，则每个事物都必须以显示地调用 commit（）方法或 rollback（）方法来结束。如果后台数据库不支持事务处理，那么当用户试图修改“AutoCommit”参数为 0 的时候，DBI 模块将会抛出错误。在 DBI 模块中定义了 commit（）方法，用来显式地提交在一个事务范围内对数据库所作的修改。该方法通过数据库句柄来调用，如下：

 $dbh->commit();

如果调用 commit（）方法的数据库句柄中的“AutoCommit”属性被设置为 1，那么调用 commit（）方法之后会得到一个“commit ineffective with AutoCommit”的警告信息。如果后台数据库不支持事务处理，那么“AutoCommit”属性默认打开，每次调用 commit（）方法之后也会得到一个相同的警告信息。与之相似的是 rollback（）方法，每次调用 rollback（）方法会回滚事务范围内对数据库所作的所有修改。如果“AutoCommit”设置为一或者是后台数据库不支持事务处理，同样的警告信息也将会被返回。一个调用 rollback（）方法的例子如下：

 $dbh->rollback();

不幸的事情是，当“AutoCommit”属性被设置为 0 的时候，显式地调用 disconnect（）方法终止和数据库的连接后触发的行为无法预测。在某些数据库的版本中，在 disconnect（）方法被 DBI 模块调用之前，会自动调用 commit（）方法，提交所有对数据库的修改；也有某些数据库版本会调用 rollback（）方法，回滚所有对数据库所作的修改。一个综合示例见程序八。

清单 8. DBI 模块处理数据库事务的详细例子

 $dbh->connect();  $dbh->{AutoCommit} = 0;   $dbh->{RaiseError} = 1;  eval {  load_some_data_to_database();  insert_some_data_to_database();  delete_some_data_from_database();  $dbh->commit()   }  if ($@) {   $dbh->rollback();  }  $dbh->disconnect();

回页首

DBI 模块中的高级主题

DBI 中的句柄属性

除了与数据库句柄和执行语句句柄绑定的一组方法以外，DBI 模块也提供了一组和这些句柄有关的属性，供用户对他们的执行环境进行调优。这些句柄属性的实质是一个由“键 / 值”对组成的哈希表，可以通过和操作哈希表引用一样的方法来访问和修改这些属性。一个典型的例子见程序九。

清单 9. 修改和显示句柄的属性

 $dbh->connect($connect_string, $userid, $passwd);   $dbh->{AutoCommit} = 1;   print “AutoCommit: $dbh->{AutoCommit} \n”;

当用户访问或者设置这些句柄属性时，DBI 模块自动检查用户输入的属性名称。如果用户试图引用一个位置属性时，DBI 模块将会产生一个错误。同理，如果用户试图修改一个只读属性时，DBI 模块会以同样的方式抛出一个错误。无论用户有没有在创建数据库句柄时设置了“RaiseError”属性，在检测到上述错误之后，DBI 模块都会使用 die（）方法抛出错误，返回一个错误值。

句柄属性的命名规则

句柄属性的命名看似杂乱无章，其实微言大义，在 DBI 模块的兼容性问题发挥了重要的作用。句柄属性的大小写命名规则直接反映了是谁定义了这个属性并对其赋值，其规则有三：

• 全大写命名方式（UPPER_CASE）：全大写属性通常是由外部标准所定义的，比如说 ISO SQL 或者 ODBC。
• 混合命名方式（Mixed_Case）：此类属性的名字通常由大写字母开头，但是其中也混有小写字母。这类属性通常由 DBI 模块标准自身定义。
• 全小写命名方式（lower_case）：此类属性由各自的数据库驱动所定义，被称为“驱动相关”属性。

公共句柄属性

表二列出了部分最常见的数据库句柄（database handle）和执行语句句柄（statement handle）都支持的句柄属性。

表 2. 常见公共句柄属性

数据库句柄的属性和数据库元数据

表三列出了最常见的数据库句柄所支持的句柄属性。

表 3. 常见数据库句柄属性

数据库原数据（database metadata）被称为“描述数据的数据”，用来描述数据本身。当用户有动态生成 SQL 执行语句或者创建动态视图需求的时候，数据库元数据所提供的信息显得格外地重要。不用数据库厂商存储数据库元数据的方法和保存的数据库元数据各不相同，大多数主流数据提供了一个系统目录（system catalog），通过表和视图保存这些信息。尽管因为上述的种种差异，使得 DBI 模块提供一个统一的访问数据库元数据的接口成了一件任重道远的任务，在 DBI 模块的定义中依然提供了两个可供调用的方法，用来访问数据库元数据。

第一个方法 $dbh->tables（）返回一个包含调用它的数据库句柄（database handle）所能发现的所有表和视图的数组，见程序十。

清单 10. 利用数据库句柄返回数据库的元数据

  my @tables = $dbh->tables();    foreach my $table (@tables) {      print “Table Name: ” . $table . “ \n”;    }

第二个方法 $dbh->table_info（）返回一个包含更多详细信息的执行语句句柄（statement handle），可以通过和访问普通执行语句句柄一样的方法，抽取其中的信息。

执行语句句柄属性和表原数据

同数据库句柄一样，执行语句句柄（statement handle）也有自己的句柄属性。执行语句句柄从创建它的数据库句柄继承了部分属性，并且它的大部分属性是用来表示语句的执行状态和结果的，属于只读属性。表四列出了常见的执行语句属性：

表 4. 常见执行语句句柄属性

通过读取某些执行语句的句柄属性，可以得到表的原数据，示例如下：

清单 11. 利用执行语句句柄返回表的元数据

 my $sth = $dbh->prepare (“SELECT * FROM tbl”);  $sth->execute();  my $field_number = $sth->{NUM_OF_FIELDS};  print “NUMBER OF FIELDS: $field_number \n”;  print “Column Name                                  Type   Nullable? \n”;  print “--------------------------------------- ---- --------- \n”;  for (my $index=0; $index<$field_number; $index++) {   my $name = $sth->{NAME}->[$index];   my $type = $sth->{TYPE}->[$index];   my $nullable = (“No”, “Yes”, “Unknown”)[$sth->{NULLABLE}->[$index]];   printf “%-30s %4d $s \n”, $name, $type, $nullable;  }  $sth->finish();

符合 DBI 模块接口的 DBD 模块

DBI 模块定义了一组和用户交互的接口，而 DBD 实现与数据库之间的具体接口。两者之间的关系可以类比为面向对象编程中接口和继承接口的类之间的关系。有两种不同的数据库驱动程序的实现方式，第一种是基于纯粹的 Perl 语言，不依赖任何 C 语言编译器。这种方法实现最为简单，却不被大多数的数据库所支持，比较典型的 DBD 模块例子有 DBD::File 和 DBD::CSV。另一种方法更为普遍，使用了 C 程序的帮助和数据库进行通讯，被称为 C/XS 驱动程序。

对于纯粹 Perl 语言开发的数据库驱动而言，整个 DBD 模块的核心在一个 DBD::Driver.pm 的模块上。根据实际要连接的数据库的不同，Driver 的名字可以被替换成为 DBD::Oracle 或者 DBD::DB2。在 DBD::Driver 包中，通常包含子包，不同的子包有着不同的作用，关于它们的详细信息见表五。

表 5. 典型的 DBD 模块的内部设计

基于 C/XS 驱动程序的 DBD 模块十分复杂，在这里不作介绍。

回页首

基于 MySql 数据库的例子

我们对 MySql 数据库进行了简单的测试。测试的结果见表六。

表 6. MySql 数据库对 DBI 模块的测试结果

回页首

基于 DB2 数据库的例子

同样我们也有一个 SQL 脚本用于在 DB2 数据创建测试环境。（见附件三）测试结果见表六。

使用 DBI 模块连接 DB2 数据库的示例见程序十二。

表 7. DB2 数据库对 DBI 模块的测试结果

清单 12. 使用 DBI 模块连接 DB2 数据库

 sub setup_connections {   my $connections_reference = shift;   my $passwd = "xxxxxxxx";   my $db2_connection_string = "dbi:DB2:test";   my $db2_userid = "db2inst1";   my %db2_connection = ();   $db2_connection{dbName} = "DB2";   $db2_connection{connStr} = $db2_connection_string;   $db2_connection{userid} = $db2_userid;   $db2_connection{passwd} = $passwd;   push (@{$connections_reference}, \%db2_connection);  }

回页首

总结

本文详细地介绍了 Perl 语言中用于和数据库通讯的 DBI 模块。以细腻的笔法和生动地示例给读者讲述了 DBI 模块的主要组成部分，结构和供用户编程时调用的方法。同时，本文也涉及了一些 DBI 模块中的高级主题，如对存储过程的调用，对并发事务的处理和数据库及执行语句句柄属性的设置和元数据的处理。除此之外，本文还对 DBD 模块的接口给予了入门级的介绍，希望可以对读者在专研第三方数据库的 DBD 模块时可以有所帮助。在本文的最后，通过定义了一套通用的 DBI 模块测试接口，对三种主流数据库 DB2，Sybase 和 MySql 进行测试，通过比较测试结果，得出了 DBI 模块在这三种数据库上的异同点和支持上的盲点。