J.J.'s Blogs

J.J. Huang   2021-06-14   Perl   瀏覽次數：次   {{moment(1623632400000).fromNow()}}

Perl - 第二十四章 | Perl 資料庫訪問

📑 目錄
  1. 前置
  2. DBI結構
  3. 變量名約定
  4. 資料庫連接
    1. 連接
    2. SELECT 操作
  5. INSERT 操作
    1. UPDATE 操作
    2. DELETE 操作
  6. Do 語句
  7. COMMIT 操作
  8. ROLLBACK 操作
  9. 事務
  10. 斷開資料庫連接

Perl 5 中我們可以使用 DBI 模塊來連接資料庫。
DBI 英文全稱：Database Independent Interface，中文稱為資料庫獨立接口。
DBI 作為 Perl 語言中和資料庫進行通訊的標準接口，它定義了一系列的方法，變量和常量，提供一個和具體資料庫平台無關的資料庫持久層。

前置

DBI結構

DBI 和具體資料庫平台，我們可以將其應用在 Oracle、MySQL 或 Informix、等資料庫中。

Perl - 第二十四章 - 圖 1 (01)​‌‌‌​​​​​‌‌​​‌​‌​‌‌‌​​‌​​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​​​​​​‌‌​​‌​​​‌‌​​​‌​​‌‌​​​​​​‌‌​‌‌​​​‌‌​​​‌​​‌‌​‌​​​​‌​‌‌​‌​‌‌‌​​​​​‌‌​​‌​‌​‌‌‌​​‌​​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​‌​​

中 DBI 獲取所有 API（Application Programming Interface：應用圖形程序接口）發送過來的 SQL 資料，然後分發到對應的驅動上執行，最後再獲取資料返回。

變量名約定

以下設置了比較常用的變量名命名方法：

變量名 說明
$dsn 驅動程序對象的句柄
$dbh 一個資料庫對象的句柄
$sth 一個語句或者一個查詢對象的句柄
$h 通用的句柄 ($dbh, $sth, 或 $drh)，依賴於上下文
$rc 操作程式碼返回的布什值（true 或 false）
$rv 操作程式碼返回的整數值
@ary 查詢返回的一行值的數組（列表）
$rows 操作程式碼返回的行數值
$fh 文件句柄
undef NULL 值表示未定義
\%attr 引用屬性的哈希值並傳到方法上

資料庫連接

假設我們要使用 MySQL 資料庫。在連接到資料庫之前，請確保以下事項。如果你不知道如何在 MySQL 資料庫中創建資料庫和表，你可以藉你我們的 MySQL 教學。​‌‌‌​​​​​‌‌​​‌​‌​‌‌‌​​‌​​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​​​​​​‌‌​​‌​​​‌‌​​​‌​​‌‌​​​​​​‌‌​‌‌​​​‌‌​​​‌​​‌‌​‌​​​​‌​‌‌​‌​‌‌‌​​​​​‌‌​​‌​‌​‌‌‌​​‌​​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​‌​​

  • 你已經創建了一個名為你TESTDB 的資料庫。
  • 你已經在 TESTDB 中創建了一個名為 TEST_TABLE 的表。
  • 該表包含字段 FIRST_NAME、LAST_NAME、AGE、SEX 和 INCOME。
  • 用戶ID“root”和密碼“root”被設置為訪問TESTDB。
  • Perl你模塊 DBI 你正確安裝在你的機器上。
    1
    2
    $ cpan DBI
    $ cpan DBD::mysql
  • 你已經閱讀了 MySQL 教學以了解 MySQL 基礎知識。

註：可以使用 Docker 建立一個 MySQL資料庫。連結

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for TEST_TABLE
-- ----------------------------
DROP TABLE IF EXISTS `TEST_TABLE`;
CREATE TABLE `TEST_TABLE` (
  `FIRST_NAME` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `LAST_NAME` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `AGE` tinyint(1) DEFAULT NULL,
  `SEX` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `INCOME` decimal(10,4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of TEST_TABLE
-- ----------------------------
BEGIN;
INSERT INTO `TEST_TABLE` VALUES ('J', 'J', 18, 'male', 99999.0000);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

連接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#!/usr/bin/perl -w

use strict;
use DBI;

my $host = "localhost"; # 主機地址
my $driver = "mysql"; # 接口類型 預設為 localhost
my $database = "W3CSCHOOL"; # 資料庫
# 驅動程序對象的句柄
my $dsn = "DBI:$driver:database=$database:$host";
my $userid = "root"; # 資料庫用戶名
my $password = "123456"; # 資料庫密碼

# 連接資料庫
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

如果與資料源建立了連接，則返回一個資料庫句柄並將其保存到 $dbh 中以供進一步使用，否則 $dbh 設置為 undef 值並且 $DBI::errstr 返回錯誤字符串。

SELECT 操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#!/usr/bin/perl -w

use strict;
use DBI;

my $host = "localhost"; # 主機地址
my $driver = "mysql"; # 接口類型 預設為 localhost
my $database = "W3CSCHOOL"; # 資料庫
# 驅動程序對象的句柄
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 操作

# 循環輸出所有資料
while ( my @row = $sth->fetchrow_array() )
{
       print join('\t', @row)."\n";
}

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

執行以上程序，輸出結果為：​‌‌‌​​​​​‌‌​​‌​‌​‌‌‌​​‌​​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​​​​​​‌‌​​‌​​​‌‌​​​‌​​‌‌​​​​​​‌‌​‌‌​​​‌‌​​​‌​​‌‌​‌​​​​‌​‌‌​‌​‌‌‌​​​​​‌‌​​‌​‌​‌‌‌​​‌​​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​‌​​

1
J\tJ\t18\tmale\t99999.0000

INSERT 操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#!/usr/bin/perl

use strict;
use DBI;

my $host = "127.0.0.1"; # 主機地址
my $driver = "mysql"; # 接口類型 預設為 localhost
my $database = "TESTDB"; # 資料庫
# 驅動程序對象的句柄
my $dsn = "DBI:$driver:database=$database:$host";
my $userid = "root"; # 資料庫用戶名
my $password = "root"; # 資料庫密碼

# 連接資料庫
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;
my $sth = $dbh->prepare("INSERT INTO `TESTDB`.`TEST_TABLE` (`FIRST_NAME`, `LAST_NAME`, `AGE`, `SEX`, `INCOME`) VALUES ('I', 'I' , '99', 'male', '1234.0000')");

$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

執行以上程序，輸出結果為：

1
2
commit ineffective with AutoCommit enabled at ./main.pl line 20.
Died at ./main.pl line 20.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#!/usr/bin/perl

use strict;
use DBI;

my $host = "127.0.0.1"; # 主機地址
my $driver = "mysql"; # 接口類型 預設為 localhost
my $database = "TESTDB"; # 資料庫
# 驅動程序對象的句柄
my $dsn = "DBI:$driver:database=$database:$host";
my $userid = "root"; # 資料庫用戶名
my $password = "root"; # 資料庫密碼

# 連接資料庫
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

my $firstName = "I";
my $lastName = "I";
my $age = 99;
my $sex = "amle";
my $income = 1234.0000;
my $sth = $dbh->prepare("INSERT INTO `TESTDB`.`TEST_TABLE` (`FIRST_NAME`, `LAST_NAME`, `AGE`, `SEX`, `INCOME`) VALUES (?,?,?,? ,?)");

$sth->execute($firstName, $lastName, $age, $sex, $income)
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

執行以上程序，輸出結果為：​‌‌‌​​​​​‌‌​​‌​‌​‌‌‌​​‌​​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​​​​​​‌‌​​‌​​​‌‌​​​‌​​‌‌​​​​​​‌‌​‌‌​​​‌‌​​​‌​​‌‌​‌​​​​‌​‌‌​‌​‌‌‌​​​​​‌‌​​‌​‌​‌‌‌​​‌​​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​‌​​

1
2
commit ineffective with AutoCommit enabled at ./main.pl line 27.
Died at ./main.pl line 27.

UPDATE 操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#!/usr/bin/perl

use strict;
use DBI;

my $host = "127.0.0.1"; # 主機地址
my $driver = "mysql"; # 接口類型 預設為 localhost
my $database = "TESTDB"; # 資料庫
# 驅動程序對象的句柄
my $dsn = "DBI:$driver:database=$database:$host";
my $userid = "root"; # 資料庫用戶名
my $password = "root"; # 資料庫密碼

# 連接資料庫
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

my $sth = $dbh->prepare("UPDATE `TESTDB`.`TEST_TABLE` SET `FIRST_NAME` = 'Z', `LAST_NAME` = 'Z' WHERE `FIRST_NAME` = 'I' AND `LAST_NAME` = 'I '");

$sth->execute() or die $DBI::errstr;
print "更新的記錄數 :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

執行以上程序，輸出結果為：

1
2
3
commit ineffective with AutoCommit enabled at ./main.pl line 22.
Died at ./main.pl line 22.
1%

DELETE 操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#!/usr/bin/perl

use strict;
use DBI;

my $host = "127.0.0.1"; # 主機地址
my $driver = "mysql"; # 接口類型 預設為 localhost
my $database = "TESTDB"; # 資料庫
# 驅動程序對象的句柄
my $dsn = "DBI:$driver:database=$database:$host";
my $userid = "root"; # 資料庫用戶名
my $password = "root"; # 資料庫密碼

# 連接資料庫
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

my $sth = $dbh->prepare("DELETE FROM `TESTDB`.`TEST_TABLE` WHERE (`FIRST_NAME` = 'Z' AND `LAST_NAME` = 'Z')");

$sth->execute() or die $DBI::errstr;
print "刪除的記錄數 :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

執行以上程序，輸出結果為：

1
2
3
commit ineffective with AutoCommit enabled at ./main.pl line 22.
Died at ./main.pl line 22.
1%

Do 語句

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

1
2
my $c = $dbh->do('DELETE FROM TEST_TABLE WHERE age = 30');
print "Deleted $c rows\n";

執行以上程序，成功刪除，輸出結果為：

1
Deleted 1 rows

執行以上程序，沒有刪除，輸出結果為：

1
Deleted 0E0 rows

COMMIT 操作

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

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

ROLLBACK 操作

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

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

事務

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

  • 1、 在連接資料庫的時候就開始一個事務
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。

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

1
2
3
4
5
6
7
8
9
$rc = $dbh->begin_work or die $dbh->errstr;

#####################
##這裡執行一些 SQL 操作
#####################

$dbh->commit; # 成功後操作
-----------------------------
$dbh->rollback; # 失敗後回滾

斷開資料庫連接

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

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

註：以上參考了
Tutorialspoint, Perl - Database Access​‌‌‌​​​​​‌‌​​‌​‌​‌‌‌​​‌​​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​​​​​​‌‌​​‌​​​‌‌​​​‌​​‌‌​​​​​​‌‌​‌‌​​​‌‌​​​‌​​‌‌​‌​​​​‌​‌‌​‌​‌‌‌​​​​​‌‌​​‌​‌​‌‌‌​​‌​​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​‌​​

📚 相關文章

📄 公開段落採 CC BY 3.0 TW 授權，可自由轉載引用，需署名出處。
🔒 付費 / 會員段落 © J.J. Huang，保留所有權利，禁止轉載。