Perl - 資料庫訪問

上一頁
下一頁

本章教你如何在 Perl 指令碼中訪問資料庫。從 Perl 5 開始，使用 **DBI** 模組編寫資料庫應用程式變得非常容易。DBI 代表 **資料庫獨立介面**，這意味著 DBI 在 Perl 程式碼和底層資料庫之間提供了一個抽象層，使您可以非常輕鬆地切換資料庫實現。

DBI 是 Perl 程式語言的資料庫訪問模組。它提供了一組方法、變數和約定，這些方法、變數和約定提供了與資料庫一致的介面，而與使用的實際資料庫無關。

DBI 應用程式的架構

DBI 獨立於後端可用的任何資料庫。無論您是使用 Oracle、MySQL 還是 Informix 等，都可以使用 DBI。從下面的架構圖可以清楚地看出這一點。

Perl Database Module DBI Architecture

這裡 DBI 負責透過 API（即應用程式程式設計介面）獲取所有 SQL 命令，並將它們分派給相應的驅動程式以進行實際執行。最後，DBI 負責從驅動程式獲取結果並將其返回給呼叫指令碼。

符號和約定

在本章中，將使用以下符號，建議您也遵循相同的約定。

$dsn    Database source name
$dbh    Database handle object
$sth    Statement handle object
$h      Any of the handle types above ($dbh, $sth, or $drh)
$rc     General Return Code  (boolean: true=ok, false=error)
$rv     General Return Value (typically an integer)
@ary    List of values returned from the database.
$rows   Number of rows processed (if available, else -1)
$fh     A filehandle
undef   NULL values are represented by undefined values in Perl
\%attr  Reference to a hash of attribute values passed to methods

資料庫連線

假設我們將使用 MySQL 資料庫。在連線到資料庫之前，請確保以下事項。如果您不瞭解如何在 MySQL 資料庫中建立資料庫和表，可以參考我們的 MySQL 教程。

  • 您已建立了一個名為 TESTDB 的資料庫。

  • 您已在 TESTDB 中建立了一個名為 TEST_TABLE 的表。

  • 此表包含欄位 FIRST_NAME、LAST_NAME、AGE、SEX 和 INCOME。

  • 已設定使用者 ID“testuser”和密碼“test123”以訪問 TESTDB。

  • Perl 模組 DBI 已正確安裝在您的機器上。

  • 您已閱讀 MySQL 教程以瞭解 MySQL 基礎知識。

以下是連線到 MySQL 資料庫“TESTDB”的示例：

#!/usr/bin/perl

use DBI
use strict;

my $driver = "mysql"; 
my $database = "TESTDB";
my $dsn = "DBI:$driver:database=$database";
my $userid = "testuser";
my $password = "test123";

my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

如果與資料來源建立連線，則返回資料庫控制代碼並將其儲存到 $dbh 以供進一步使用，否則 $dbh 設定為 undef 值，並且 $DBI::errstr 返回錯誤字串。

INSERT 操作

當您想在表中建立一些記錄時，需要 INSERT 操作。這裡我們使用表 TEST_TABLE 來建立我們的記錄。因此，一旦我們的資料庫連線建立，我們就可以在 TEST_TABLE 中建立記錄了。以下是建立單個記錄到 TEST_TABLE 的過程。您可以使用相同的概念建立任意數量的記錄。

記錄建立需要以下步驟：

  • 使用 INSERT 語句準備 SQL 語句。這將使用 **prepare()** API 完成。

  • 執行 SQL 查詢以從資料庫中選擇所有結果。這將使用 **execute()** API 完成。

  • 釋放語句控制代碼。這將使用 **finish()** API 完成。

  • 如果一切順利，則 **提交** 此操作，否則您可以 **回滾** 整個事務。提交和回滾將在下一節中解釋。

my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                       (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
                         values
                       ('john', 'poul', 'M', 30, 13000)");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

使用繫結值

可能存在要輸入的值事先未給定的情況。因此，您可以使用繫結變數，這些變數將在執行時獲取所需的值。Perl DBI 模組在實際值的位置使用問號，然後在執行時透過 execute() API 傳遞實際值。以下是示例：

my $first_name = "john";
my $last_name = "poul";
my $sex = "M";
my $income = 13000;
my $age = 30;
my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                        (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
                          values
                        (?,?,?,?)");
$sth->execute($first_name,$last_name,$sex, $age, $income) 
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

讀取操作

任何資料庫上的讀取操作都意味著從資料庫中獲取一些有用的資訊，即從一個或多個表中獲取一個或多個記錄。因此，一旦我們的資料庫連線建立，我們就可以在此資料庫中進行查詢了。以下是查詢所有年齡大於 20 的記錄的過程。這將分四個步驟進行：

  • 根據所需條件準備 SQL SELECT 查詢。這將使用 **prepare()** API 完成。

  • 執行 SQL 查詢以從資料庫中選擇所有結果。這將使用 **execute()** API 完成。

  • 逐個獲取所有結果並列印這些結果。這將使用 **fetchrow_array()** API 完成。

  • 釋放語句控制代碼。這將使用 **finish()** API 完成。

my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM TEST_TABLE 
                        WHERE AGE > 20");
$sth->execute() or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array()) {
   my ($first_name, $last_name ) = @row;
   print "First Name = $first_name, Last Name = $last_name\n";
}
$sth->finish();

使用繫結值

可能存在條件事先未給定的情況。因此，您可以使用繫結變數，這些變數將在執行時獲取所需的值。Perl DBI 模組在實際值的位置使用問號，然後在執行時透過 execute() API 傳遞實際值。以下是示例：

$age = 20;
my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM TEST_TABLE
                        WHERE AGE > ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array()) {
   my ($first_name, $last_name ) = @row;
   print "First Name = $first_name, Last Name = $last_name\n";
}
$sth->finish();

更新操作

任何資料庫上的更新操作都意味著更新資料庫表中已有的一個或多個記錄。以下是更新所有性別為“M”的記錄的過程。這裡我們將所有男性的年齡增加一年。這將分三個步驟進行：

  • 根據所需條件準備 SQL 查詢。這將使用 **prepare()** API 完成。

  • 執行 SQL 查詢以從資料庫中選擇所有結果。這將使用 **execute()** API 完成。

  • 釋放語句控制代碼。這將使用 **finish()** API 完成。

  • 如果一切順利，則 **提交** 此操作，否則您可以 **回滾** 整個事務。有關提交和回滾 API，請參見下一節。

my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1 
                        WHERE SEX = 'M'");
$sth->execute() or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

使用繫結值

可能存在條件事先未給定的情況。因此，您可以使用繫結變數，這些變數將在執行時獲取所需的值。Perl DBI 模組在實際值的位置使用問號，然後在執行時透過 execute() API 傳遞實際值。以下是示例：

$sex = 'M';
my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1
                        WHERE SEX = ?");
$sth->execute('$sex') or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

在某些情況下，您希望設定一個事先未給定的值，因此您可以按如下方式使用繫結值。在此示例中，所有男性的收入將設定為 10000。

$sex = 'M';
$income = 10000;
my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   INCOME = ?
                        WHERE SEX = ?");
$sth->execute( $income, '$sex') or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();

刪除操作

當您想從資料庫中刪除一些記錄時，需要 DELETE 操作。以下是從 TEST_TABLE 中刪除所有年齡等於 30 的記錄的過程。此操作將執行以下步驟。

  • 根據所需條件準備 SQL 查詢。這將使用 **prepare()** API 完成。

  • 執行 SQL 查詢以從資料庫中刪除所需的記錄。這將使用 **execute()** API 完成。

  • 釋放語句控制代碼。這將使用 **finish()** API 完成。

  • 如果一切順利，則 **提交** 此操作，否則您可以 **回滾** 整個事務。

$age = 30;
my $sth = $dbh->prepare("DELETE FROM TEST_TABLE
                         WHERE AGE = ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows deleted :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

使用 do 語句

如果您正在執行 UPDATE、INSERT 或 DELETE 操作，則不會從資料庫返回任何資料，因此可以使用快捷方式執行此操作。您可以使用 **do** 語句執行以下任何命令：

$dbh->do('DELETE FROM TEST_TABLE WHERE age =30');

**do** 如果成功則返回真值，如果失敗則返回假值。實際上，如果成功，它會返回受影響的行數。在示例中，它將返回實際刪除的行數。

COMMIT 操作

Commit 是向資料庫發出最終確認更改的訊號的操作，在此操作之後，無法將任何更改恢復到其原始位置。

以下是如何呼叫 **commit** API 的簡單示例：

$dbh->commit or die $dbh->errstr;

ROLLBACK 操作

如果您對所有更改不滿意或在任何操作過程中遇到錯誤，您可以使用 **rollback** API 將這些更改恢復。

以下是如何呼叫 **rollback** API 的簡單示例：

$dbh->rollback or die $dbh->errstr;

開始事務

許多資料庫支援事務。這意味著您可以發出大量修改資料庫的查詢，但實際上沒有任何更改。然後，最後，您發出特殊的 SQL 查詢 **COMMIT**，所有更改將同時進行。或者，您可以發出查詢 ROLLBACK，在這種情況下，所有更改都將被丟棄，資料庫保持不變。

Perl DBI 模組提供了 **begin_work** API，它啟用事務（透過關閉 AutoCommit），直到下次呼叫 commit 或 rollback 為止。在下一次提交或回滾後，AutoCommit 將自動再次開啟。

$rc  = $dbh->begin_work  or die $dbh->errstr;

AutoCommit 選項

如果您的事務很簡單，您可以避免發出大量提交的麻煩。當您進行連線呼叫時，您可以指定一個 **AutoCommit** 選項，該選項將在每個成功的查詢後執行自動提交操作。以下是它的樣子：

my $dbh = DBI->connect($dsn, $userid, $password,
              {AutoCommit => 1}) 
              or die $DBI::errstr;

這裡 AutoCommit 可以取值 1 或 0，其中 1 表示 AutoCommit 已開啟，0 表示 AutoCommit 已關閉。

自動錯誤處理

當您進行連線呼叫時，您可以指定一個 RaiseErrors 選項，該選項會自動為您處理錯誤。發生錯誤時，DBI 將中止您的程式，而不是返回失敗程式碼。如果您只希望在發生錯誤時中止程式，這可能很方便。以下是它的樣子：

my $dbh = DBI->connect($dsn, $userid, $password,
              {RaiseError => 1})
              or die $DBI::errstr;

這裡 RaiseError 可以取值 1 或 0。

斷開資料庫連線

要斷開資料庫連線，請使用 **disconnect** API，如下所示：

$rc = $dbh->disconnect  or warn $dbh->errstr;

disconnect 方法的事務行為，遺憾的是，是未定義的。某些資料庫系統（如 Oracle 和 Ingres）將自動提交任何未完成的更改，但其他系統（如 Informix）將回滾任何未完成的更改。不使用 AutoCommit 的應用程式應在呼叫 disconnect 之前顯式呼叫 commit 或 rollback。

使用 NULL 值

未定義值或 undef 用於指示 NULL 值。您可以像插入和更新非 NULL 值一樣插入和更新具有 NULL 值的列。這些示例使用 NULL 值插入和更新 age 列：

$sth = $dbh->prepare(qq {
         INSERT INTO TEST_TABLE (FIRST_NAME, AGE) VALUES (?, ?)
       });
$sth->execute("Joe", undef);

這裡 **qq{}** 用於向 **prepare** API 返回帶引號的字串。但是，在嘗試在 WHERE 子句中使用 NULL 值時必須小心。考慮：

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

將 undef（NULL）繫結到佔位符將不會選擇 age 為 NULL 的行！至少對於符合 SQL 標準的資料庫引擎而言。有關此原因，請參閱資料庫引擎的 SQL 手冊或任何 SQL 書籍。要顯式選擇 NULL，您必須說“WHERE age IS NULL”。

一個常見的問題是讓程式碼片段處理在執行時可能已定義或未定義（非 NULL 或 NULL）的值。一個簡單的技巧是根據需要準備適當的語句，併為非 NULL 案例替換佔位符：

$sql_clause = defined $age? "age = ?" : "age IS NULL";
$sth = $dbh->prepare(qq {
         SELECT FIRST_NAME FROM TEST_TABLE WHERE $sql_clause
       });
$sth->execute(defined $age ? $age : ());

其他一些 DBI 函式

available_drivers

@ary = DBI->available_drivers;
@ary = DBI->available_drivers($quiet);

透過搜尋 @INC 中的 DBD::* 模組，返回所有可用驅動程式的列表。預設情況下，如果某些驅動程式被同一名稱的早期目錄中的其他驅動程式隱藏，則會發出警告。將 $quiet 的值為真將抑制警告。

installed_drivers

%drivers = DBI->installed_drivers();

返回當前程序中所有“已安裝”（已載入）驅動程式的驅動程式名稱和驅動程式控制代碼對列表。驅動程式名稱不包含“DBD::”字首。

data_sources

@ary = DBI->data_sources($driver);

返回透過指定驅動程式可用的資料來源（資料庫）列表。如果 $driver 為空或未定義，則使用 DBI_DRIVER 環境變數的值。

quote

$sql = $dbh->quote($value);
$sql = $dbh->quote($value, $data_type);

透過跳脫字元串中包含的任何特殊字元（例如引號）並在字串周圍新增所需的型別的外引號，為在 SQL 語句中用作文字值而引用字串文字。

$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
                $dbh->quote("Don't");

對於大多數資料庫型別，quote 將返回 'Don''t'（包括外部引號）。quote() 方法返回一個計算結果為所需字串的 SQL 表示式是有效的。例如 -

$quoted = $dbh->quote("one\ntwo\0three")

may produce results which will be equivalent to

CONCAT('one', CHAR(12), 'two', CHAR(0), 'three')

所有控制代碼的通用方法

err

$rv = $h->err;
or
$rv = $DBI::err
or
$rv = $h->err

返回上次呼叫的驅動程式方法的本機資料庫引擎錯誤程式碼。該程式碼通常是整數，但你不應該假設它一定是整數。這等效於 $DBI::err 或 $h->err。

errstr

$str = $h->errstr;
or
$str = $DBI::errstr
or
$str = $h->errstr

返回上次呼叫的 DBI 方法的本機資料庫引擎錯誤訊息。這與上面描述的“err”方法具有相同的生命週期問題。這等效於 $DBI::errstr 或 $h->errstr。

rows

$rv = $h->rows;
or
$rv = $DBI::rows

返回先前 SQL 語句影響的行數，等效於 $DBI::rows。

trace

$h->trace($trace_settings);

DBI 具有一個非常有用的功能，可以生成其正在執行的操作的執行時跟蹤資訊，這在嘗試跟蹤 DBI 程式中奇怪問題的根源時可以節省大量時間。您可以使用不同的值來設定跟蹤級別。這些值從 0 到 4 不等。值 0 表示停用跟蹤，值 4 表示生成完整的跟蹤。

禁止內插語句

強烈建議不要使用如下所示的內插語句 -

while ($first_name = <>) {
   my $sth = $dbh->prepare("SELECT * 
                          FROM TEST_TABLE 
                          WHERE FIRST_NAME = '$first_name'");
   $sth->execute();
   # and so on ...
}

因此，不要使用內插語句，而是使用 **繫結值** 來準備動態 SQL 語句。

列印頁面
上一頁
下一頁
廣告