Perl學習筆記（十）--通過DBI訪問資料庫

標籤：

　　Perl訪問資料庫最常用的包是DBI，可以在www.cpan.org找到。另外還需要安裝對應資料庫的驅動包，例如DBD::MySQL、DBD::Oracle、DBD::Sybase或者DBD::ODBC等。

一、基本流程

　　一般來說，資料庫操作由以下幾個步驟組成一個常見的流程：

　　1． 建立一個資料庫連接

　　2． 通過建立的資料庫連接，執行SQL語句

　　3． 執行SQL後擷取返回的資料集

　　4． 在資料集中對記錄進行處理，一般是一個迴圈的過程

　　5． 處理完畢，關閉資料庫連接，釋放資源

下面是按照上述的流程，在Perl中訪問MySQL的一段代碼，以這段代碼為例，詳細說明DBI的使用方法。

#!/usr/bin/perl -wuse strict;use DBI;my $dbh = DBI->connect("DBI:mysql:test:192.168.1.2", ‘root‘, ‘password‘);my $sth = $dbh->prepare("SELECT * FROM test1");$sth->execute();while ( my @row = $sth->fetchrow_array() ){       print join(‘\t‘, @row)."\n";}$sth->finish();$dbh->disconnect();

 1.1 串連資料庫

my $dbh = DBI->connect("DBI:mysql:test:192.168.1.2", ‘root‘, ‘password‘);

 　　調用DBI的方法DBI->connect來建立一個資料庫的串連，如果串連成功則返回一個資料庫連接控制代碼，之後執行SQL等操作都要把這個串連控制代碼作為一個巨集指令引數。在connect調用中，首先要提供一個資料庫連接串。這個串連串用冒號分為了幾個部分：

小節	說明
DBI	介面類型
mysql	資料庫類型
test	資料庫名稱
192.168.1.2	資料庫主機地址

　　在前面例子中的串連串中，DBI表示這是DBI介面的一個串連串；mysql表示要串連的資料庫是MySQL資料庫（如果要串連Oracle資料 庫，這裡則是oracle），不同的資料庫有不同的串連串定義，可以參考DBI對應的訪問驅動的說明；test指明了串連到資料庫主機上的資料庫名 稱；192.168.1.2就是MySQL伺服器的IP地址。這裡要注意的是，串連串中的資料庫類型mysql必須小寫。如果省略了主機名稱，則預設為 localhost。connect方法的後面兩個參數是串連資料庫主機的使用者名稱和密碼，這個可是不可缺少的 J

　　如果在串連過程中出現任何錯誤，則connect的傳回值都會是undef（和C語言中的NULL是一回事）。這裡為了簡化而略去了錯誤檢查，實際做項目的時候應當對這些錯誤和傳回值的進行檢查。

1.2 執行SQL語句

my $sth = $dbh->prepare("SELECT * FROM test1");$sth->execute();$dbh->do(“UPDATE test1 SET time=now()”);

 　　串連上了資料庫，獲得了資料庫連接控制代碼，就可以利用這個控制代碼來對資料庫進行操作了。要執行一條SQL語句，為了提高效能，DBI分兩個步驟來做。先把 SQL語句通過prepare方法提交到資料庫，資料庫為該語句分配執行資源，之後調用execute方法通知數據庫執行該SQL語句。注意 prepare方法是通過資料庫連接控制代碼調用的，如果成功則返回一個該SQL的控制代碼，之後通過該SQL語句控制代碼調用execute執行SQL。 一般來說execute執行的都是返回資料的語句（例如SELECT語句）。反之如果執行INSERT、UPDATE、DELETE、CREATE TABLE等不需要返回資料的語句，則有一個更方便、快速的方法 $dbh->do(SQL語句)，可以省去prepare的步驟。do方法返回的是受該SQL影響的記錄數量。

1.2.1 技巧：對SQL進行排版

常寫大段SQL的朋友可能會對於SQL中的引號很頭痛，每每都因為引號的問題搞的SQL語句亂成一團分辨不清。還記得上篇文章講過的qq嗎？這裡正 是它的好用處。由於qq中的字串同雙引號” ”內的字串一樣會對變數進行解釋，同時qq還可以換行。因此使用它來對SQL進行排版是非常好的一個選擇，例如像這樣的一條SQL語句：

my $res_operator = $dbhandle->prepare( qq{       SELECT o_customerid, COUNT(*) AS totalMsgNum FROM mm4fcdrs       WHERE (m_date>‘$begindate‘) AND (m_date<‘enddate‘)        GROUP BY o_customerid });

 1.2.2 通過SQL語句中的參數最佳化查詢執行效率

　　在執行大量INSERT之類的語句的時候，反覆向資料庫伺服器提交同樣結構的一個SQL語句，在這種情況下可以利用prepare和SQL參數來最佳化執行效率：

　　1．先使用prepare提交一個SQL模板給資料庫伺服器，把其中值的部分用參數預留位置代替。

　　2．使用prepare讓伺服器為該SQL準備了執行資源後，調用execute並在該方法中傳入參數實際的值執行SQL。

　　3．之後可以反覆調用execute，不需要伺服器重新prepare

　　假設要執行這樣的一個系列的SQL

INSERT INTO test1 VALUES (NULL, ‘a’, ‘2005-04-01’)... ...INSERT INTO test1 VALUES (NULL, ‘z’, ‘2005-04-01’)

 其中第二個欄位的值是從a到z的字母。那麼可以這樣來最佳化執行效率：

my $sth = $dbh->prepare( qq{    INSERT INTO test1 VALUES (NULL, ?, ‘2005-04-01’)} );for my $value(‘a‘..‘z‘)  {    $sth->execute($value);}

 其中的問號就是前面說的參數預留位置了，它的意思就是告訴在準備執行資源的伺服器：這個SQL的這個位置會有一個值，但是現在還不知道，等下執行的時候再告 訴你。 prepare了之後，用一個迴圈產生a-z的字元給變數$value，然後將$value在execute方法中作為一個參數傳入，伺服器那裡會自動用 傳入的值替換前面的"?"。需要提醒的是，傳入的參數個數一定要和SQL中的預留位置的數量一樣。

1.3 讀取記錄

熟悉ADO的朋友一定知道裡面有一個DataReader對象，DBI中讀取資料的方法和它非常的相似。簡單來說，就是單向、流式的讀取資料，也就是每次只能向後讀一條資料直到沒有資料可以讀取。

文章開頭的例子中，用了 $sth->fetchrow_array() 方法來讀取資料。其實DBI讀取資料還有幾種常見的方法，這幾個方法是類似的，所不同的是返回記錄的形式。

1.3.1 fetchrow_array

返回一個由欄位的值組成的數組。該數組的第1個元素就是目前記錄第1個欄位的值。

while ( my @row = $sth->fetchrow_array() )  {    print "$row[0], $row[1], $row[2]\n";}

 或者這樣，不過要注意欄位對應的順序

while ( my ($id, $name, $time) = $sth->fetchrow_array() )  {    print "$id, $name, $time\n";}

 

1.3.2 fetchrow_arrayref

　　返回由欄位的值組成的數組的引用。同fetchrow_array的區別很明顯，fetchrow_arrayref返回的數組的引用。

while ( my $row_ref = $sth->fetchrow_arrayref() ) {    for (my $i = 0; $i < @{$row_ref}; $i++)       {        print "$row_ref->[$i]\t";    }    print "\n";}

 

這裡要注意的是，如果要取欄位的個數，需要把這個引用轉成數組的形式獲得 @{$row_ref} 。擷取數組元素的值的時候，因為$row_ref是引用，因此需要使用->操作符。

1.3.3 fetchrow_hashref

　　返回一個由”欄位名－欄位值”這樣的”鍵－值”對組成的HASH表。關鍵的不同就是，只有這個方法可以通過一個欄位名獲得它的值，而不必關心這個欄位是第幾個欄位。而前者只能依靠索引來訪問值。不過缺點就是，效率要比前面兩個差一些。

while ( my $record = $sth->fetchrow_hashref() ) {    for my $field( keys %{$record} ) {        print "$field: $record->{$field}\t";    }    print "\n";}

 

這裡需要複習一下HASH表的操作方法。keys操作符擷取HASH的鍵(key)的數組，$record->{$field}獲得HASH表中$field對應的值。注意這裡同樣是引用，因此要用->操作符。

使用上面三個方法可以基本解決問題了。此外，還有兩個方法fetchall_arrayref和selectall_arrayref可以直接通過SQL一次性擷取整個資料集，不過使用上稍微複雜一些，要涉及到 perl的scalar 操作符，這裡就不贅述了。有興趣的讀者可以參考DBI的相關資料。

最後是收尾工作。

1.4 結束一個SQL會話

$sth->finish();

 1.5 斷開資料庫連接

$dbh->disconnect();

 

http://dbi.perl.org/

http://www.perl.com/pub/1999/10/DBI.html

 

Perl學習筆記（十）--通過DBI訪問資料庫