在本文中，我們將學習如何編寫簡單的 Perl 程序來提取或操作 IBM® DB2® Universal Database™（DB2 UDB）中存儲的數據。我們將從一個簡單的任務入手：從數據庫中選擇一行數據放入一個 Perl 程序中；接著逐步介紹一些高級主題，包括處理大對象和調用存儲過程。
簡介
Perl（Practical Extraction and Report Language）是一種功能強大而又非常簡單易用的編程語言，在很多操作系統上都可以使用。Perl 是免費的。我們可以（以源代碼或二進制的格式）下載這個語言，并可以免費地使用它。
Perl 日漸成為一種廣受歡迎的語言。它包含了 C 編程語言的特性，以及 UNIX® 中的一些命令，例如 awk 和 sed。Perl 是一種解釋語言，可以在單獨的應用程序中使用，也可以與 Apache 一起來構建 Web 應用程序。
我們可以使用 Perl 快速操作來自文件或 RDBMS 的大型數據集。DBI 是在 Perl 腳本中連接 RDBMS 的標準，它是在 1994 年開始引入的。在 http://dbi.perl.org/ 站點上可以找到 DBI 驅動程序的源代碼及其文檔。
IBM 在 1992 年為 Perl 開發了 DB2 的驅動程序，并隨著 DBI 規范的發展周期性地對其進行更新。這個驅動程序的最新版本（在撰寫本文時）是 0.78。在 http://www.ibm.com/software/data/db2/perl/ 上可以找到主要的 DBD::DB2（這是 Perl 語言中采用的命名機制）驅動程序信息。
本文將展示如何編寫簡單的 Perl 程序來提取或操作 DB2 UDB 中存儲的數據。我們將從一個簡單的任務入手：從數據庫中選擇一行數據放入一個 Perl 程序中；接著逐步介紹一些高級主題，包括處理大對象和調用存儲過程。
開始
圖 1 展示了 Perl 環境如何與數據庫環境進行交互：
圖 1. Perl 環境 
正如可以從這個圖中看出的，Perl 程序使用了一個標準的 API 來與 DBI（Perl 的數據庫接口模塊）進行通信。Perl DBI 模塊只能支持動態 SQL。它定義了一組方法、變量和約定來提供一個與實際使用的數據庫獨立的一致數據庫接口。DBI 為 API 提供了一個一致的接口，它可以適用于程序員想使用的任何數據庫。DBD::DB2 是一個 Perl 模塊，當與 DBI 一起使用時，它就可以讓 Perl 與 DB2 UDB 進行通信。
因此，為了運行訪問 DB2 數據庫的 Perl 腳本，需要在系統上安裝以下組件：
- Perl 語言環境
- DBI 驅動程序（可以用于任何 RDBMS）
- DBD::DB2 驅動程序
- DB2 Runtime Client
- C 編譯器
- DB2 數據庫服務器的連接信息
在 http://www.ibm.com/software/db2/perl/ 的 Web 站點上可以看到所有的安裝配置指南。
連接到 DB2 數據庫上
為了讓 Perl 程序訪問 DB2 數據庫，需要建立到數據庫的連接。為了讓 Perl 加載 DBI 模塊，需要在 Perl DB2 應用程序中包含下面的內容：
use DBI;
當使用 DBI->connect 語句（語法如下）來創建一個數據庫句柄 時，DBI 模塊會自動加載 DBD::DB2 驅動程序。
清單 1. 創建數據庫句柄
use DBI;
$dbh = DBI->connect (“dbi:DB2:dbalias", $UserId, $password);
|
其中：
| $dbh |
—— 表示 connect 語句所返回的數據庫句柄 |
| dbalias |
—— 表示分類進 DB2 數據庫目錄中的 DB2 別名 |
| $userID |
—— 表示用來連接數據庫的用戶 ID |
| $password |
—— 表示這個用戶 ID 的密碼 |
清單 2 展示了一個簡單的 Perl 程序，它建立到數據庫 SAMPLE 的連接，并返回今天的日期。這個程序執行一條動態生成的 DB2 SQL 語句，從數據庫中獲取 CURRENT DATE。它使用 DBI -> bind_col 方法將數據庫中的值傳遞到一個本地變量中，稍后我們就會討論這個問題。
清單 2. 連接到數據庫上并執行語句
#!/usr/local/bin/perl -w
use DBI;
use strict;
# Open a connection
my $dbh = DBI->connect("dbi:DB2:sample", “DB2ADMIN", “db2admin“, {RaiseError => 1});
# use VALUES to retrieve value from special register
my $stmt = "Values CURRENT DATE";
my $sth = $dbh->prepare($stmt);
$sth->execute();
# associate variables with output columns...
my $col1;
$sth->bind_col(1,\$col1);
while ($sth->fetch) { print "Today is: $col1\n"; }
$sth->finish();
$dbh->disconnect();
|
錯誤處理 —— SQLCODE 和 SQLSTATE
為了返回與某個 Perl DBI 數據庫句柄或語句句柄相關的 SQLSTATE，可以調用 state 方法。例如，要返回與數據庫句柄 $dbhandle 相關的 SQLSTATE ，可以在應用程序中使用下面的 Perl 語句：
my $sqlstate = $dbhandle->state;
為了返回與某個 Perl DBI 數據庫句柄或語句句柄相關的 SQLCODE，可以調用 err 方法。例如，要返回與數據庫句柄 $dbhandle 相關的 SQLCODE，可以在應用程序中使用下面的 Perl 語句：
my $sqlcode = $dbhandle->err;
errstr 方法返回與某個 Perl DBI 數據庫句柄或語句句柄相關的 SQLCODE 的消息。我推薦使用這個方法，因為這個方法可以給出有關 SQL 語句失敗的更多信息。
清單 3 中的例子展示了這個方法的用法：
清單 3. 返回錯誤消息的 errstr 方法
$dbh = DBI->connect("dbi:DB2:sample",“USERID",“password") or
die “Can't connect to sample database: $DBI::errstr";
$sth = $dbh->prepare(“SQL statement“) or die "Cannot prepare: " $dbh->errstr;
$sth->>execute() or die "Cannot execute: " $sth->errstr;
|
現在讓我們進一步看一下第一個實驗。它展示了一個 Perl 程序，這個程序使用作為參數傳入的用戶 ID 和密碼來連接數據庫 SAMPLE。當傳遞了有效的 ID 和密碼時，它會返回一條消息說明連接成功了。下面是 lab1.pl 的代碼：
清單 4. lab1.pl
#!/usr/local/bin/perl –w
use DBI;
$db2user = $ARGV[0];
$pasw = $ARGV[1];
# Open a connection
$dbh = DBI->connect("dbi:DB2:sample", $db2user, $pasw) or
“Can't connect to sample database: $DBI::errstr";
print "Connection is successful !!\n";
|
圖 2 給出了使用有效身份驗證和無效身份驗證來執行這個程序的結果：
圖 2. 執行 lab1.pl
執行 SQL 語句
下面讓我們來編寫一個程序，在數據庫 SAMPLE 中創建一個表 PT_ADDR_BOOK。要執行在編寫應用程序時就已經知道的 SQL 語句，可以使用 $dbh->do 方法。這個方法的語法如下所示：
my $cnt = $dbh->do(SQL statement);
其中 $cnt 是這條 SQL 語句所影響的行數。
使用這個方法，我們的程序創建了一個 DB2 表，如清單 5 所示：
清單 5. 用來創建 DB2 表的程序
#!/usr/local/bin/perl -w
use DBI;
use DBD::DB2::Constants;
$dbh = DBI->connect("dbi:DB2:sample","","") or
die “Can't connect to sample database: $DBI::errstr";
$rcount = $dbh->do (“CREATE TABLE PT_addr_book(name char(30),
phone char(10))");
print “Returns: $rcount\n";
|
可以使用相同的 do 方法向 PT_addr_book 表中插入幾行數據。請注意，所插入行的值在編寫這個程序時都是已知的，因此可以硬編碼在代碼中。
清單 6. 使用 do 方法插入幾行數據
#!/usr/local/bin/perl -w
use DBI;
use DBD::DB2::Constants;
$dbh = DBI->connect("dbi:DB2:sample","","") or
die "Can't connect to sample database: $DBI::errstr";
$rcount = $dbh-> do ("Insert into PT_ADDR_BOOK values
('Gregory Whales','9142712020'),
('Robert Moses', 2127652345')");
print "Returns: $rcount \n";
|
正如從這個例子中可以看到的，這條 SQL 語句會影響兩行數據。通過在 DB2 CLP 中對這個表運行 SELECT 語句，可以確定有兩行數據已經插入了這個表中。
清單 7. test_do_exs.pl
$perl test_do_exs.pl
Returns : 2
$db2 “select * from PT_ADDR_BOOK"
NAME PHONE
------------------------------ ----------
Gregory Whales 9142712020
Robert Moses 2127652345
2 record(s) selected.
|
下面讓我們從 下載 一節中實驗 2 的練習開始，編寫并執行一個簡單的 Perl 程序來更新 PR_ADDR_BOOK 表。
INSERT、UPDATE 和 DELETE 語句 —— 沒有占位符
執行一條在編寫應用程序時還未確定的 SQL 語句（動態 SQL）需要使用一種不同的技術，可以使用 $dbh->prepare 方法來實現。動態 SQL 可以通過在程序執行過程中自己修改列、表和謂詞（操作）的能力來實現。動態 SQL 需要由 UDB 優化器來準備 執行，目的是為這條語句創建一個訪問計劃。如果動態 SQL 沒有參數標記（占位符），它就可以在這個步驟之后立即執行。清單 8 給出了 $dbh->prepare 和 $sth->execute 方法的語法，可以在 Perl 程序中用來執行沒有占位符的嵌入式 SQL 語句：
清單 8. 動態 SQL 的準備和執行
$stmt = “SQL Statement without placeholder“;
$sth = $dbh->prepare($stmt);
$sth->execute();
|
請注意，$dbh->prepare 方法的結果是 SQL 語句句柄。
清單 9 中給出的 Perl 程序展示了如何使用這些方法將一行數據插入到 PT_ADDR_BOOK 表中：
清單 9. 插入一行數據
#!/usr/local/bin/perl -w
use DBI;
use DBD::DB2::Constants;
$dbh = DBI->connect("dbi:DB2:sample","","") or
die “Can't connect to sample database: $DBI::errstr";
$stmt = "INSERT INTO PT_addr_book values ('JOHN SMITH','9145556677')“;
$sth = $dbh->prepare($stmt);
$sth->execute();
print "We inserted row into addr_book\n";
$sth->finish();
|
具有參數標記的 SQL 語句
現在讓我們來看一下如何執行在編寫應用程序時還不確定并且具有參數標記（即占位符）的 SQL 語句 —— 即真正的動態 SQL 語句。請注意，我們推薦使用這種 SQL 語句，因為它們具有性能和安全性方面的優點。從性能的角度來看，動態 SQL 語句只需要準備一次就可以執行多次，可以重用相同的數據庫訪問計劃。從安全性的角度來看，占位符可以確保只有一個值可以插入到這條語句中，從而可以避免出現 SQL 語句錯誤。
為了讓 Perl 程序將動態 SQL 語句轉換成可執行的格式，需要首先使用 prepare 方法，然后使用 bind_param 方法來綁定參數。只有這樣才可以執行這條語句。要綁定每個參數，需要指定參數標記的個數，以及包含這些參數標記值的本地 Perl 變量的名字。清單 10 給出了讓 Perl 程序執行動態 SQL 語句所使用的方法的語法：
清單 10. 動態 SQL 的方法
$stmt = “SQL Statement with parameter marker“;
$sth = $dbh->prepare($stmt);
$sth->bind_param(1,$parm,\% attr);
$sth->execute();
|
清單 11 展示了對表 PT_ADDR_BOOK 執行 INSERT 操作的方法。現在，所插入列的值不再是在 SQL 語句中硬編碼的了，而是使用參數標記動態傳遞給這條語句的；這些參數標記已經使用 $sth->bind_param 方法與這條語句進行了綁定。只有完成這些設置之后，才開始執行這條語句。
清單 11. 對表 PT_ADDR_BOOK 執行 INSERT 操作
#!/usr/local/bin/perl -w
use DBI;
use DBD::DB2::Constants;
$dbh = DBI->connect("dbi:DB2:sample","","") or
die “Can't connect to sample database :DBI::errstr";
$name ="STEVE BROWN";
$phone = "7184358769";
$stmt = "INSERT INTO PT_addr_book values (?,?)";
$sth = $dbh->prepare($stmt);
$sth->bind_param(1,$name);
$sth->bind_param(2,$phone);
$sth->execute();
print "We inserted row into addr_book\n";
$sth->finish();
|
從數據庫中檢索數據 —— 單個結果
為了將數據庫中的值放到 Perl 程序中使用，需要按照下面的步驟執行：
- 準備 SELECT 語句。
- 執行所準備的語句。
- 使用 $sth->bind_col 方法把某列的值（或函數）關聯到一個本地 Perl 變量上。
- 使用 $sth->fetch 方法將一個值取到本地變量中。
下面的偽碼展示了如何從數據庫中檢索單個值：
清單 12. 從數據庫中檢索單個值
$stmt = “SQL SELECT Statement to be executed“;
$sth = $dbh->prepare($stmt);
$sth->execute();
$result = $sth->bind_col(col, \variable [, \%attr ]);
while ($sth->fetch){
process result of the fetch;
}
|
下面這個例子展示了如何為一個聚合函數 max 從 EMPLOYEE 表中檢索 salary 的值：
清單 13. 為聚合函數檢索值
#!/usr/local/bin/perl -w
use DBI;
use DBD::DB2::Constants;
$dbh = DBI->connect("dbi:DB2:sample","","") or
die “Can't connect to sample database: $DBI::errstr";
$stmt = "SELECT max(salary) from EMPLOYEE";
$sth = $dbh->prepare($stmt);
$sth->execute();
#associate variable with output columns...
$sth->bind_col(1,\$max_sal);
while ($sth->fetch) {
print "The biggest salary is: $max_sal\n";
}
|
下面是執行這個 Perl 程序后的結果：
清單 14. 聚合結果值
$perl test_return_value.pl
The biggest salary is: 52750.00
|
從數據庫中檢索數據 —— 多個結果
從數據庫中返回一個結果集（即多個結果）到 Perl 程序中的技術與前面所采用的技術非常類似。在準備并執行返回多個結果的 SQL 語句并將返回值（列）綁定到本地變量上之后，可能需要使用 $sth->fetch 方法來檢索這些值。為了展示這個方法是如何工作的，下面讓我們從 PT_ADDR_BOOK 表中檢索出 phone 和 name 列的內容。清單 15 給出了使用一個循環來使用 $sth_fetch 方法的用法：
清單 15. 從數據庫中檢索多個值
$stmt = "SELECT name, phone from PT_ADDR_BOOK";
$sth = $dbh->prepare($stmt);
$sth->execute();
#associate variables with output columns...
$sth->bind_col(1,\$name);
$sth->bind_col(2,\$phone);
print "NAME PHONE \n";
print "------------------------- -----------\n";
while ($sth->fetch) {
print $name ;
print $phone; print "\n"; }
print "DONE \n";
$sth->finish();
|
執行這段代碼的結果如下：
清單 16. 從數據庫中檢索多個值
C:\Dev_POT\PERL\Labs>perl multi.pl
NAME PHONE
------------------------- -----------
JOHN SMITH 9145556677
STEVE BROWN 7184358769
DONE
|
也可以使用 $sth->fetchrow 方法從結果集中檢索數據。$sth ->fetch 方法會將每個值作為單獨一項返回，而 $sth ->fetchrow() 方法則將一行作為一個數組返回，每列的值都是該數組中的一個元素。
可以使用 fetchrow 方法編寫功能相同的 Perl 程序，如下所示：
清單 17. 從數據庫中檢索多個值
$stmt = "SELECT name, phone from PT_ADDR_BOOK";
$sth = $dbh->prepare($stmt);
$sth->execute();
print "NAME PHONE \n";
print "------------------------- -----------\n";
while (($name, $phone) = $sth->fetchrow())
{ print "$name $phone\n"; }
|
我們可以從 實驗 3 開始編寫并執行一個 Perl 程序，它可以創建一個 DB2 表，向該表中插入一些數據，并返回插入該表中的行數。
調用存儲過程
下面讓我們來創建一個多個步驟的場景，從而詳細了解一下如何從 Perl 程序中調用 DB2 的存儲過程。首先，創建一個簡單的存儲過程 SP_GET_LOC，它從表 ORG 中返回某個給定部門的位置（步驟 1）。
清單 18. 步驟 1：創建存儲過程
create procedure sp_get_loc (in deptin int, out loc varchar(13))
begin
select location into loc from org where deptnumb = deptin;
end @
|
請注意，這個存儲過程有一個輸入參數和一個輸出參數。當我們在 DB2 的命令行處理器（CLP）中運行這個過程時，它對部門 10 會返回位置 NEW YORK。
清單 19. 步驟 2：運行存儲過程
$db2 "call sp_get_loc(10,?)"
Value of output parameters
--------------------------
Parameter Name : LOC
Parameter Value : New York
Return Status = 0
|
下面讓我們來編寫一個簡單的 Perl 程序來調用存儲過程 SP_GET_LOC（步驟 3，請參看 清單 20）。我們的動態 SQL 語句實際上與在 DB2 CLP 中執行的語句相同，形式為 SP_GET_LOC(?,?)，不過在 CLP 中是傳遞硬編碼的 10（部門編號），在動態 SQL 語句中將使用一個參數標記。采用這種方法，就可以對 ORG 表的部門編號列的任何值查詢位置了。
在構造 SQL 語句之后，剩下的步驟就與其他具有參數標記的動態語句完全相同了。可以使用 $sth = $dbh->prepare 方法進行準備。使用 $sth->bind_param 方法將部門編號綁定為輸入參數，使用 $sth->bind_param_inout 方法將返回的位置綁定為輸出參數，然后再執行動態 SQL 語句。
該程序如下：
清單 20. 步驟 3：調用存儲過程
#!/usr/bin/perl -w
use DBI;
use DBD::DB2::Constants;
$dbh = DBI->connect("dbi:DB2:sample","","") or
die "Can't connect to sample database: $DBI::errstr";
# Prepare our call statement
$sth = $dbh->prepare( "CALL SP_GET_LOC(?,?)" );
# Bind input parameter for department number
$sth->bind_param(1, 10);
# Bind output parameter - location
$sth->bind_param_inout (2, \$location, 13, db2_param_type=>SQL_PARAM_OUTPUT});
# Call the stored procedure
$sth->execute();
printf("Stored procedure returned location: %s\n", $location);
$sth->finish();
$dbh->disconnect;
|
如果執行這個程序，就會看到給定部門（本例中為 10）的 LOCATION：
清單 21. 步驟 4：執行調用 DB2 存儲過程的 Perl 程序
$perl test_call_sp.pl
Stored Procedure returned location: New York
|
大對象的操作
使用 Perl 來處理文件比使用其他更復雜的語言（例如 C 或 Java®）都要簡單。下面我們就會看到如何將數據直接從文件中導入 DB2 的大對象數據（LOB）列中。插入大對象數據的最有效的方法是，將這個文件直接綁定到 DB2 表中一個與 LOB 類型的列相關聯的輸入參數上。Perl 驅動程序可以直接從文件中讀取數據，并將數據傳遞給數據庫服務器。要將一個文件綁定到某個輸入 LOB 參數上，可以在執行 INSERT 操作過程中使用 bind_param 方法來指定 { db2_file => 1} 參數屬性。
在我們的數據庫模式 POT 下面有一個表 MAP。這個表有一個 picture 列，它被聲明成 BLOB 類型的，用來存放某個地區的圖像。下面是創建該表所使用的 DDL：
清單 22. 創建 MAP 表使用的 DDL
create table POT.MAPS
( map_id INT,
map_name VARCHAR(13),
area INT ,
photo_format CHAR(3),
picture BLOB) ;
|
另外，我們還有一個文件 pearcson.jpg，其中包含了 Pearson Airport 的地圖。
圖 3. 示例地圖
現在讓我們來編寫一個程序，向表 POT.MAP 中插入一行數據，即將 JPG 文件的圖像插入到 PICTURE 中。首先，需要構造一個具有 5 個參數標記的動態 SQL 語句。然后，需要對這條語句進行準備。在將參數綁定到準備好的語句上之前，需要指定包含要插入的圖像的文件名，并將其賦給一個本地 Perl 變量（$picture_file）?，F在可以將所有參數全部綁定到需要插入到 MAP 表中的值上。請注意，我們為最后一個參數指定了 db2_file =>1 屬性。最后一個步驟是執行這條 INSERT 語句。這個程序的代碼如下：
清單 23. 插入 LOB
#!/usr/bin/perl -w
use DBI;
use DBD::DB2::Constants;
%conattr = ( AutoCommit => 1,
# Turn Autocommit On
db2_info_applname => 'Maps Module', );
# Identify this appl
$dbh = DBI->connect("dbi:DB2:sample","", "",\%conattr) or die "$DBI::errstr";
$dbh->do("SET CURRENT SCHEMA POT");
$sql = "INSERT INTO MAPS(map_id, map_name, area, photo_format, picture)
VALUES(?,?,?,?,?)";
$sth = $dbh->prepare($sql);
$picture_file = "pearson.jpg"; # File containing our picture
$sth->bind_param(1, 100); # map_id
$sth->bind_param(2, "Pearson airport"); # map_name
$sth->bind_param(3, 416); # area
$sth->bind_param(4, "JPG"); # photo_format
$sth->bind_param(5, $picture_file, {db2_file => 1});
$rows_affected = $sth->execute();
printf("%d rows affected", $rows_affected);
$sth->finish();
$dbh->disconnect;
|
從數據庫中讀取 LOB 數據
可以使用標準的 fetch 方法來檢索 LOB 數據，例如 fetchrow_array 或 fetchrow_arrayref。DBI 讓我們可以使用 LongReadLen 連接屬性來設置每次 fetch 可以檢索的最大字節數。對于 LOB 列來說，缺省值為 32,700 個字節。要實現這種功能，需要執行以下步驟：
- 構造 SQL 語句從 MAP 表中選擇 picture 列的數據。
- 準備 SQL 語句。
- 為保存所檢索到的圖像使用的文件分配一個名字。
- 打開該文件。
- 執行這條 SQL 語句。
- 使用 fetch 方法將結果取到文件中。
下面是展示如何從數據庫中檢索 LOB 數據的代碼：
清單 24. 從數據庫中讀取 LOB 數據
#!/usr/bin/perl
use DBI;
use DBD::DB2::Constants;
%conattr =
(
AutoCommit => 1,
# Turn Autocommit On
db2_info_applname => 'Maps Module',
# Identify this appl
LongReadLen => 80000
# Don't retrieve LOBs
);
# Connect to our database
$dbh = DBI->connect("dbi:DB2:sample","", "",\%conattr) or
die "$DBI::errstr";
# Set the current schema to 'POT'
$dbh->do("SET CURRENT SCHEMA POT");
$sql = "SELECT picture FROM maps WHERE map_name ='Pearson airport'";
# Prepare the statement
$sth = $dbh->prepare($sql);
# Open output file
$out_file = "mypic.jpg";
open(OUTPUT, ">$out_file") or die "Cannot open $out_file because $!";
binmode OUTPUT;
$sth->execute;
@row = $sth->fetchrow;
print OUTPUT $row[0];
@row = "";
close(OUTPUT);
print “Picture in the file $out_file\n";
$sth->finish();
$dbh->disconnect;
|
在運行這個程序之后，圖像就保存到 mypic.jpg 文件中了。
清單 25. 從數據庫中讀取 LOB 數據
$perl test_lobread.pl
Picture in the file mypic.jpg
|
請使用 實驗 4 中的練習來編寫并執行一個 Perl 程序，它從一個表中檢索出二進制大對象，并將其保存到一個文件中。
結束語
本文是為那些具有關系數據庫經驗并且希望學習如何編寫 Perl 程序來訪問 DB2 數據庫的 Perl 程序員編寫的。在本文中，我們已經學習了如何連接數據庫，如何通過 INSERT、UPDATE 和 DELETE 語句來操作數據庫的內容。還學習了如何從數據庫中檢索數據，并介紹了一些高級主題，包括調用存儲過程和操作大數據對象（LOB 和 BLOB）。現在我們應該已經準備好使用自己剛掌握的 Perl DB2 編程技巧來開發自己的程序了。
原文鏈接：http://www-128.ibm.com/developerworks/cn/db2/library/techarticles/dm-0512greenstein/index.html