Perl DBI數據庫訪問

Perl DBI數據庫管理，Perl MySQL，學習例子，實例代碼，教學等。

本次會議將教你如何使用PERL訪問Oracle數據庫和其他數據庫。

Oraperl模塊以訪問Oracle，請查看Oraperl手冊。
DBI模塊使用通用的方式來訪問數據庫。在本章中，我們將討論這個模塊。

自Perl 5中已經變得非常容易使用DBI編寫數據庫應用程序. DBI代表獨立於數據庫的接口Perl DBI為Perl代碼和底層數據庫之間提供了一個抽象層，這意味著, 讓你可以很容易地切換數據庫的實現方式。

DBI是一個Perl 編程語言的數據庫訪問模塊。它定義了一套方法，變量和約定來提供一個一致的數據庫接口, 獨立於實際使用的數據庫。

DBI應用程序的體係結構

DBI是獨立於現有的任何數據庫在後端。無論你正在使用Oracel，MySQL，Informix等，您可以使用DBI。下麵是一個結構圖。

DBI是負責所有通過API，應用程序編程接口執行SQL命令，並派遣他們實際執行相應的驅動程序。最後DBI是負責任的驅動程序，它給調用scritp結果。

標記和慣例

整個這一章中，將使用下麵的符號並且它建議，你也應該遵循相同的約定。

  $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 - by www.gitbook.net
  undef   NULL values are represented by undefined values in Perl
  \%attr  Reference to a hash of attribute values passed to methods

數據庫連接

假設我們要使用MySQL數據庫。連接到數據庫之前，請確保以下：

你已經創建了數據庫 TESTDB。
你已經創建表TEST_TABLE 在 TESTDB中。
這個表有以下幾個字段 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返回一個錯誤字符串。

插入操作

你要創建或寫入表TEST_TABLE記錄時，INSERT操作是必需的。因此，一旦我們建立數據庫連接，我們準備創建記錄TEST_TABLE。以下是創建單記錄到TEST_TABLE的步驟。以類似的方式，您可以創建多條記錄。

記錄創建需要以下步驟

準備SQL語句使用INSERT語句。這項工作將使用prepare() API.
執行SQL查詢從數據庫中選擇的結果。這將通過使用execute() API.
釋放語句句柄。通過使用finish() API
如果一切順利，然後 commit此操作，否則你可以rollback 完成事務。提交和回滾解釋在接下來的章節。

 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;

使用綁定值

當冇有事先給出被輸入的值時，有可能出現一種情況。在這樣的情況下，綁定的值被使用。使用一個問號代替實際值和實際值，然後通過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基於SQL查詢所需的條件。這項工作將使用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();

使用綁定值

還冇有事先給出的條件時，有可能出現一種情況。在這樣的情況下，綁定的值被使用。采用的是一個問號代替實際值和實際值，然後通過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();

更新操作

任何數據庫更新操作意味著已經可以在數據庫中更新一個或多個記錄。以下是更新所有的記錄SEX 更新為'M'的過程。在這裡，我們將增加AGE的所有男性一年。這將需要三個步驟

準備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(); // by www.gitbook.net
  $dbh->commit or die $DBI::errstr;

使用綁定值

如果冇有事先給出的條件時，有可能出現一種情況。在這樣的情況下，綁定的值被使用。采用的是一個問號代替實際值和實際值，然後通過 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 返回一個值假如它執行成功, 如果失敗返回false值。其實，如果成功的話返回受影響的行數。在這個例子中，它會返回實際刪除的行數。

提交操作

Commit是操作數據庫來完成的變化給出了一個綠色的信號，此操作後冇有變化，可以恢複。

下麵是一個簡單的例子來調用commit API.

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

回滾操作

如果您不滿意的變化，你想恢複這些變化，然後使用 rollback API.

下麵是一個簡單的例子來調用 rollback API.

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

開始事務

許多數據庫支持事務。這意味著，你可以做一大堆的查詢將要修改的數據庫，但實際上是冇有變化。然後在結束時發出特殊的SQL查詢語句：COMMIT，然後所有的修改同時進行。或者您可以發出查詢 ROLLBACK，在這種情況下，所有查詢都被丟棄。

begin_work API使事務（通過關閉自動提交），直到下一次調用commit或rollback。一個COMMIT或ROLLBACK後，自動提交將被自動打開了。

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

自動提交選項

如果你的事務很簡單，你可以保存，不用發出了很多提交。當您連接調用，您可以指定一個自動提交選項，將執行自動提交操作後，每一個成功的查詢。這裡它看起來像：

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

在這裡，將AutoCommit值設置為1或0。

自動錯誤處理

當你連接調用，你可以指定一個引發錯誤的選項，自動為您處理錯誤。當發生錯誤時，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）將回滾任何未完成的更改。不使用自動提交的應用程序必須顯式的調用commit或rollback，然後再調用斷開。

使用 NULL 值

未定義的值，或者undef，用於指示NULL值。您可以插入和更新的NULL值作為一個非NULL值的列。這些例子將一個NULL值插入和更新到年齡這個字段：

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

qq{} q引號的字符串prepare API用於返回。

但是，必須小心當想要在一個WHERE子句中使用NULL值的時候。試想一下：

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

綁定一個undef（NULL）的占位符不會選擇行有一個NULL的年齡！至少在符合SQL標準的數據庫引擎。這種情況的原因，請參閱SQL手冊，為您的數據庫引擎或SQL書。要明確地選擇你必須說“年齡WHERE IS NULL”空值。

一個常見的問題是有一個代碼片段處理的值可以在運行時定義為undef（非NULL 或 NULL）。一個簡單的方法是根據需要準備相應的語句, and substitute the placeholder for non-NULL cases:和非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);

DBD::*模塊通過@INC目錄中搜索所有可用驅動程序返回一個列表，默認情況下，發出警告，如果某些驅動程序先前目錄中隱藏和其他有相同的名稱。傳遞一個值為$quiet ，將抑製警告（不發出警告）。

installed_drivers

%drivers = DBI->installed_drivers();

返回一個列表的驅動程序名稱和驅動程序句柄對所有驅動程序的安裝（裝載）到當前進程。驅動程序的名稱不包括DBD::“前綴。

data_sources

@ary = DBI->data_sources($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");

對於大多數的數據庫類型，引用將返回“Don't”（包括外引號）。 '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 or $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語句，並找出它是如何運行的查詢。如果你有很多類似的查詢，這是浪費時間的。
其次，它不會工作，如果$first_name包含奧布萊恩或D'Fecto的或一些其他的名字以'的名稱，如 '. The ' 在SQL中有特殊的含義，並且該數據庫將無法理解，當你問它準備一個SQL語句。
最後，如果你要構建根據用戶輸入的查詢，然後簡單地插入直接輸入到查詢它是不安全的，因為用戶可以構建，企圖欺騙你的程序做的事情，而冇意料到一個陌生的輸入。例如，假設用戶輸入以下變量$input怪異的值：
```
x' or first_name = first_name or first_name = 'y
```
現在，我們的查詢變得非常令人吃驚的事情：
```
SELECT * 
FROM TEST_TABLE
 WHERE first_name = 'x' 
       or first_name = first_name or first_name = 'y'
```
這個查詢，我們的用戶希望部分是第二個or子句。這個子句選擇所有記錄的的first_name是等於first_name，也就是說，所有的人。

因此，不使用插值語句，而不是使用綁定值來編寫動態SQL語句。