[PERL] 21- 使用資料庫 @新精讚

自動目錄

安裝
資料庫連線
指定編碼
取得資料 SELECT
- 準備sql字串
- 準備具有條件的sql字串
傾印取回的資料
逐筆取回資料
新增修改或刪除 INSERT/UPDATE/DELETE
- 取回最後異動的id
- 寫入空值

資料庫有很多套，基本上大同小異，此範例以MYSQL為例。

這個單元很不好寫，我寫了足足快一年，因為項目實在太多，只能挑撿一些我比較熟析或我覺得很重要的內容以饗讀者，其它比較不那麼重要或是進階的部分就留給有興趣的讀者自行研究，請參考最下面的參考資料。

安裝

使用mysql資料庫，在centos linux 7上直接用yum 安裝

yum install "perl(DBD::mysql)"

或是用 cpan 來安裝(Centos <7)

# cpan -i DBD::mysql

如果有CPAN的問題，可參考 [PERL] 使用CPAN安裝模組

這樣就能使用mysql的資料庫

資料庫連線

use DBD::mysql
my ($DB_NAME, $DB_IP, $DB_PORT, $DB_TYPE, $DB_USER, $DB_PWD )=
    ("dbname","127.0.0.1","3306","mysql", "username", "password"); 
# 給定連線字串
my $DSN = "DBI:$DB_TYPE:database=$DB_NAME;host=$DB_IP;port=$DB_PORT";
my $dbh = DBI->connect($DSN, $DB_USER, $DB_PWD) or die "無法連到資料庫 $DBI::errstr\n";

第1行資料庫連線使用模組 DBD::mysql
第2-3行給定連線參數
第6行連線成功後取得參照變數$dbh，否則報錯。

指定編碼

這行很重要，尤其是有用到中文字時

my $sth = $dbh->prepare("set names utf8");
$sth->execute();

取得資料 SELECT

準備sql字串

$sql="select * from sometable";
my $sth = $dbh->prepare($sql);
$sth->execute();

第2行叫用dbh(資料庫處理器中)的prepare方法產生 $sth，$sth是語法處理器物件^[3]。
第3行叫用execute方法後，資料庫的內容就取回到$sth的物件中（記憶體）。

準備具有條件的sql字串

sql字串果有帶條件時，寫成如下

$sql="select * from sometable where name like ? and age=?";
my $sth = $dbh->prepare($sql);

第1行使用問號'?' 作為變數放置的位置。

給定變數值有兩個方法，一個是叫用 bind_param ，另一個是叫用 execute時帶入。

$sth->bind_param(1, 'john%'); #第1個問號
$sth->bind_param(2, 30); #第2個問號

或是在execute代入即可

$sth->execute("john%",30);

因為有2個問號，在執行execute時要帶相對應的參數，所以依次序放入變數的值。

由於PERL優異的彈性，你也可以放入一個清單或是陣列

@p = ("john%",30);
$sth->execute(@p);

有趣的是，就算你不放入任何變數也不會出錯，換句話說，不會產生錯誤的sql 字串，但是結果可能不是你要的。

當然，你也可以自己組合成字串，例如：

$sql="select * from sometable where name like 'john%' and age=30";

在 $sth叫用execute方法後，資料庫的內容就取回到$sth的物件中（記憶體），接下來使用幾種方法讀取或使用這些資料。

傾印取回的資料

當要查看執行結果時，可以使用下面的方法一次傾印全部的資料

DBI::dump_results($sth);

以下範例對上面說明的內容作個整合，並傾印出來：

use DBD::mysql;

my ($DB_NAME, $DB_IP, $DB_PORT, $DB_TYPE, $DB_USER, $DB_PWD )=
    ("ddns","127.0.0.1","3306","mysql", "ddns", "1qaz2wsx");
my $DSN = "DBI:$DB_TYPE:database=$DB_NAME;host=$DB_IP;port=$DB_PORT";
my $dbh = DBI->connect($DSN, $DB_USER, $DB_PWD) or die "無法連到資料庫 $DBI::errstr\n";
my $sql = "SELECT * from `unit` where `uid`=? or `uid`=?";
my $sth = $dbh->prepare($sql);
@p=(440,444)
$sth->execute(@p);
DBI::dump_results($sth);

逐筆取回資料

接下來要使用$sth提供的方法，取回在記憶體中的資料。

主要介紹下面幾種方法：

fetchrow_hashref, fetchrow_arrayref, fetchrow_array, fetchall_arrayref, fetchall_hashref

* 上面是屬於STATEMENT HANDLE。另外還有一些方法如selectrow_array, selectrow_arrayref, selectrow_hashref, selectall_arrayref, selectall_array, selectall_hashref, selectcol_arrayref是屬於DATABASE HANDLE，相關的差異在O'Reilly 的「Programming the Perl DBI」專書有介紹[3]，簡單的來說 STATEMENT HANDLE是DATABASE HANDLE的子層，透過STATEMENT叫用資料庫更安全。

取得一行使用 fetchrow_hashref()

my $ref = $sth->fetchrow_hashref();

fetchrow_hashref的方法是使用參照*的方式取回一行，指標一開始在資料的第一筆，每執行一次取回一行並把指標往下。

如果取不到資料，$ref為undef。你可以印出$sth->err 來看錯誤的原因。

* 如果對參照不熟析的話，可參考 [PERL] 17-參照這篇。

是否有資料？

使用 if 直接來判斷是否有取回一行的資料：

if($ref){ ...執行操作... }

取得特定欄位的資料

要取得此行每個欄位的值，可叫用指標方法，例如取回欄位'zone'的資料

my $item = $ref->{'zone'};

這邊要注意的是，雖然Mysql的於資料庫、資料表有分大小寫，但是對於欄位是大小寫不分的(cf: 程式、函數、資料庫命名的風格 )

但是你取資料用的hash鍵值一定要和欄位名稱一致，也就是要分大小寫。

使用迴圈取回全部欄位

使用迴圈來取回全部的資料，假設我要印出zone和count兩個欄位的資料，使用 while來傾印全部的 $sth資料。

while(my $ref = $sth->fetchrow_hashref()) { 
  print $ref->{'zone'}. " ". $ref->{'count'}. "\n";
}

結束取回資料

當已經不需要再從 $sth 物件取得任何資料時，可以叫用 finish 方法，他會釋放記憶體的占用，每次資料取完後務必加上這行。

$sth->finish;

關閉資料庫連結

當已經不需要再從資料庫做任何操作時，可以叫用 disconnect 方法，他會釋放占用的埠，每次結束後務必加上這行。

$dbh->disconnect;

取回全部使用 fetchall_hashref()

fetchall_hashref 會一次把資料放到變數中，來看以下範例：

my $ref = $sth->fetchall_hashref('uid');
while (($key, $value) = each($ref))
{
     print $key.", ".$value->{'prefix'}. "\n";
}

第1行把所有的資料倒到 $ref變數中，這是一個雜湊的參照，fetchall_hashref 中代入的參數是這個雜湊的鍵值，非常重要，你應該指定資料庫欄位中的「主鍵」或是「唯一鍵」，否則會因覆寫導致資料遺失的問題，此例中指定資料庫欄位uid設為鍵值。

第2-5行印出鍵值和欄位`prefix`的內容

如此每一個資料項就是 $ref->{'鍵值'}->{'欄位名'}，你還是要特別注意，前面提過的欄位名是有分大小寫。

結果：

396, 24
1422, 25
482, 25
949, 24
499, 25
1151, 24
1204, 24

如果你不在乎欄位名，或是你已經知道既有的資料順序，那麼就使用array來替你取回資料

fetchrow_arrayref, fetchrow_array, fetchall_arrayref

取得一行使用 fetchrow_array()

類於於 fetchrow_hashref，取回一行為一個陣例，是個非常簡單的用法

@arr= $sth->fetchrow_array();

同樣的，也可以使用迴圈讀回全部資料

while(@arr=$sth->fetchrow_array()){
print "@arr[0]\n";
}

取得一行使用 fetchrow_arrayref()

他的別名是 fetch()，直接叫用 $sth->fetch就好

由於他取回的是一個陣列參照，所以要寫成這樣，例如取回所有的第2項

while($arr=$sth->fetchrow_arrayref){
print $arr->[1]. "\n";
}

給定欄位名稱

fetch或 fetchrow_arrayref常會搭配欄位名稱來使用，取回的欄位是不帶名稱的，可以使用 bind_column方法給定每個項目的名稱。

$sth->bind_columns(\$zone, \$ip, \$prefix);
while( $sth->fetchrow_arrayref()){
   print "$key $zone, $ip, $prefix\n";
}

第1行給定陣例項目值設為變數 $zone, $ip, $prefix，因為是使用參照，所以前面有加一個反斜線'\'，千萬別漏掉。

第3行可以直接取用給定的變數了。

特別要注意的就是取回項目和給定名稱的數量要相同，否則會報警告，例如資料欄位有8個，但只給定3個變數。

DBD::mysql::st bind_columns failed: bind_columns called with 3 values but 8 are needed

取得主部使用 fetchall_arrayref()

類似於 fetchall_hashref

陣列也許很大，可以用中刮號[索引1,索引2]指定切片範圍，能以限定取回資料數量

$ref = $sth->fetchall_arrayref; #全取
$ref = $sth->fetchall_arrayref( [2,4] ); #取3和5項
$ref = $sth->fetchall_arrayref( [2..5] ); #取3到6項
$ref = $sth->fetchall_arrayref( [-3,-1] ); #取倒數3和倒數1項
$ref = $sth->fetchall_arrayref( [1..4], 20); #取2到5項，20筆

使用迴圈印出

$ref = $sth->fetchall_arrayref();  #全取
while (($key, $value) = each($ref))
{
   print $key.", ".$value->[4]. "\n";
}

印出其中的鍵值和第5項，因為是陣列，鍵值就是索引 0, 1, 2, ....

如果有切片的話，鍵值以切片後為準

$ref = $sth->fetchall_arrayref([2..4],10); #全取
while (($key, $value) = each($ref))
{
print $key.", $value->[0]\n";
}

上面的例子中，切片是第3-5項目，所以新陣列只有3個項目10行

$value->[0]取回的第一個項目是切片的第1個項目，也就是原資料的第3個項目。

新增修改或刪除 INSERT/UPDATE/DELETE

無論是新增或修改或刪除，sql語法的產生還是調用 prepare 方法來完成，例如

$sql ="insert into tabel(col1, col2) values (?, ?)";
sth = $dbh->prepare($sql);

還是叫用 execute方法來執行。

sth->execute("value1", value2);

修改和刪除也是一樣。

但是prepare提供陣列的方法bind_param_array, execute_arra可以大量新增或修改資料，目前引用[2]的範例，有興趣的人再前往參考

$dbh->{RaiseError} = 1;        # save having to check each method call
$sth = $dbh->prepare("INSERT INTO staff (first_name, last_name, dept) VALUES(?, ?, ?)");
$sth->bind_param_array(1, [ 'John', 'Mary', 'Tim' ]);
$sth->bind_param_array(2, [ 'Booth', 'Todd', 'Robinson' ]);
$sth->bind_param_array(3, "SALES"); # scalar will be reused for each row
$sth->execute_array( { ArrayTupleStatus => \my @tuple_status } );

簡單說明一下：prepare有三個問號，要準備3個變數，第5行本來也應該準備3個項目，但傳入的是字串，所以會重覆使用，因此新增的三個欄目分別是：

John, Booth, SALES
Mary, Todd, SALES
Tim, Robinson, SALES

取回最後異動的id

Mysql新增或修改後，可取回最後異動的id(主鍵)，叫用last_insert_id方法

$last = $sth->last_insert_id();

寫入空值

要寫入空值的話，只要把值指定為 undef即可，這就是 mysql的null值。

$sth->bind_param(1, undef);

參考資料

[1] https://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html

[2] https://metacpan.org/pod/DBI

[3] http://www.cs.unb.ca/~wdu/cs4025f02/DBI-Programming.htm

上一篇 20- 檔案處理
回到目錄 01-撰寫第一隻PERL程式
下一篇 22-日期和時間

END