Perl 資料庫連接

本章節我們將為大家介紹 Perl 資料庫的連結。

Perl 5 中我們可以使用 DBI 模組來連接資料庫。

DBI 英文全名為：Database Independent Interface，中文稱為資料庫獨立介面。

DBI 作為 Perl 語言中和資料庫進行通訊的標準接口，它定義了一系列的方法，變數和常數，提供一個和具體資料庫平台無關的資料庫持久層。

DBI 結構

DBI 和特定資料庫平台無關，我們可以將其應用在Oracle, MySQL 或 Informix, 等資料庫中。

圖表中DBI 取得所有API（Application Programming Interface：應用程式介面） 發送過來的SQL 數據，然後分發到對應的驅動程式上執行，最後再取得資料返回。

變數名稱約定

以下設定了比較常用的變數名稱命名方法：

$dsn    驱动程序对象的句柄
$dbh    一个数据库对象的句柄
$sth    一个语句或者一个查询对象的句柄
$h      通用的句柄 ($dbh, $sth, 或 $drh)，依赖于上下文
$rc     操作代码返回的布什值（true 或 false）
$rv     操作代码返回的整数值
@ary    查询返回的一行值的数组（列表）
$rows   操作代码返回的行数值
$fh     文件句柄
undef   NULL 值表示未定义
\%attr  引用属性的哈希值并传到方法上

資料庫連線

接下來我們以MySQL 資料庫為例示範Perl 是如何對資料庫進行操作的。

這裡我們在MySQL 資料庫建立php 資料庫，資料表為Websites，表格結構及資料如下圖：

#!/usr/bin/perl -w

use strict;
use DBI;

my $host = "localhost";         # 主机地址
my $driver = "mysql";           # 接口类型 默认为 localhost
my $database = "php";        # 数据库
# 驱动程序对象的句柄
my $dsn = "DBI:$driver:database=$database:$host";  
my $userid = "root";            # 数据库用户名
my $password = "123456";        # 数据库密码

# 连接数据库
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;
my $sth = $dbh->prepare("SELECT * FROM Websites");   # 预处理 SQL  语句
$sth->execute();    # 执行 SQL 操作

# 注释这部分使用的是绑定值操作
# $alexa = 20;
# my $sth = $dbh->prepare("SELECT name, url
#                        FROM Websites
#                        WHERE alexa > ?");
# $sth->execute( $alexa ) or die $DBI::errstr;

# 循环输出所有数据
while ( my @row = $sth->fetchrow_array() )
{
       print join('\t', @row)."\n";
}

$sth->finish();
$dbh->disconnect();

##執行步驟:

使用prepare() API 預處理SQL 語句。
使用 execute() API 執行 SQL 語句。
使用 finish()  API 釋放語句句柄。
最後如果一切順利就會提交以上執行操作。

• my $sth = $dbh->prepare("INSERT INTO Websites
                         (name, url, alexa, conutry )
                          values
                         ('Twitter', 'https://twitter.com/', 10, 'USA')");
  $sth->execute() or die $DBI::errstr;
  $sth->finish();
  $dbh->commit or die $DBI::errstr;

應用程式也可以綁定輸出和輸入參數,下面例子透過用變數取代? 佔位符的位置來執行一條插入查詢：

my $name = "Twitter";
my $url = "https://twitter.com/";
my $alexa = 10;
my $conutry = "USA";
my $sth = $dbh->prepare("INSERT INTO Websites
                       (name, url, alexa, conutry )
                        values
                       (?,?,?,?)");
$sth->execute($name,$url,$alexa, $conutry) 
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

執行步驟:

使用prepare() API 預處理SQL 語句。
使用 execute() API 執行 SQL 語句。
使用 finish()  API 釋放語句句柄。
最後如果一切順利就會提交以上執行操作。

• my $sth = $dbh->prepare("UPDATE Websites
                          SET   alexa = alexa + 1 
                          WHERE country = 'CN'");
  $sth->execute() or die $DBI::errstr;
  print "更新的记录数 :" + $sth->rows;
  $sth->finish();
  $dbh->commit or die $DBI::errstr;

應用程式也可以綁定輸出和輸入參數,下面例子透過用變數取代? 佔位符的位置來執行一條更新查詢：

$name = 'php中文网';

my $sth = $dbh->prepare("UPDATE Websites
                        SET   alexa = alexa + 1 
                        WHERE name = ?");
$sth->execute('$name') or die $DBI::errstr;
print "更新的记录数 :" + $sth->rows;
$sth->finish();

當然我們也可以綁定要設定的值，如下所示將country 為CN 的alexa 都修改為1000：

$country = 'CN';
$alexa = 1000：;
my $sth = $dbh->prepare("UPDATE Websites
                        SET   alexa = ?
                        WHERE country = ?");
$sth->execute( $alexa, '$country') or die $DBI::errstr;
print "更新的记录数 :" + $sth->rows;
$sth->finish();

執行步驟:

使用prepare() API 預處理SQL 語句。
使用 execute() API 執行 SQL 語句。
使用 finish()  API 釋放語句句柄。
最後如果一切順利就會提交以上執行操作。
以下資料將 Websites 中 alexa 大於 1000 的資料都刪除：

$alexa = 1000;
my $sth = $dbh->prepare("DELETE FROM Websites
                        WHERE alexa = ?");
$sth->execute( $alexa ) or die $DBI::errstr;
print "删除的记录数 :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

使用do 語句

do 語句可以執行UPDATE, INSERT, 或DELETE 操作，使用他比較簡短，執行成功回傳true，執行失敗回傳false，實例如下：

$dbh->do('DELETE FROM Websites WHERE alexa>1000');

COMMIT 操作

commit 為提交事務，完成資料庫的操作：

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

ROLLBACK 操作

如果在SQL 執行過程中發生錯誤，可以回滾數據，不做任何改變：

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

和其它的語言一樣, perl DBI對資料庫的操作也支援事務處理， 它的實作方式有兩個：

1、 在連接資料庫的時候就開始一個交易

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

以上程式碼在連接的時候設定了AutoCommit為false， 也就是說當你對資料庫進行更新操作的時候， 它不會自動地把那些更新直接寫到資料庫裡， 而是要程式通過$dbh->commit 來使資料真正地寫到資料庫裡， 或$dbh->rollback 來回滾剛才的操作。

2、 透過$dbh->begin_work()語句來開始一個交易

這種方式就不需要在連接資料庫的時候設定 AutoCommit ＝ 0 。

可以一次資料庫連線進行多次交易操作， 不用每一次交易的開始都會去連接一次資料庫。

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

#####################
##这里执行一些 SQL 操作
#####################

$dbh->commit;    # 成功后操作
-----------------------------
$dbh->rollback;  # 失败后回滚

斷開資料庫連接

如果我們需要斷開資料庫連接，可以使用 disconnect API：

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