クイックコマンド集

DBIには数多くののコマンドが用意されていますが、下記のコマンドをおぼえておけば問題ないでしょう。

アウトライン使用法

Perlからデータベースに接続するには、最初にDBIモジュールを読み込みます。

# DBI モジュールの読み込み
use DBI;

その後に、connectメソッドを使ってデータベースに接続します。connectメソッドは、データソース、ユーザ名、パスワードの引数を受け取ります。データソースには、先頭にDBI 、次にDBMS名（MySQLやOracleなど）、データベース名、ホスト名、ポート名をコロン（ :
）で区切って指定します。

# データソースの指定
$dsn = 'DBI:mysql:mydb:www:3306';

上記は、MySQLにあるmydbというデータベースに、wwwというホストから 3306
ポート（デフォルトのポート番号）で接続しています。ホスト名とポート番号は省略可能です。

# データソース
$dsn = 'DBI:mysql:mydb';
# ユーザ名
$user = 'webuser';
# パスワード
$password = 'mypass';
# データベースへ接続
$dbh = DBI->connect($dsn, $user, $password,
{ RaiseError => 1, AutoCommit => 0 });

connectメソッドの1つ目の引数はデータソースの指定、2つ目と3つ目は、データベース・アカウントのユーザ名と、そのパスワードを指定します。

connectメソッドで接続がすめば、あとはデータを挿入したり変更するメソッドを実行させます。実際には挿入、削除、変更、選択などの命令文をprepareメソッドで準備し
、executeメソッドで用意したSQL文を実行させます。

- SELECT

# prepareが成功した場合、ステートメントハンドルが返されます
my $sth = $dbh->prepare(
"SELECT f2, f3 FROM table WHERE f1=?"
);
# SQL文の実行
$sth->execute;
# データの取得と出力
while(@row = $sth->fetchrow_array) {
print "@row\n";
}

 - プレースホルダの利用

データを検索したり、データに登録する値をユーザの入力や外部ファイルから取得する際は、セキュリティを考慮してプレースホルダを利用することが望ましいです。プレースホルダは値を安全にするための機構で、実行時に受け取った値を検査し、実行時に問題となる文字列などを置換した後に、先に準備していたSQL文に埋め込みます。MySQLではプレースホルダ名をクエスチョン( ? )で表します。

# SQL命令文の準備（プレースホルダの利用）
# クエスチョン( ? )の部分が、実行時に変数で渡された値と置換されます
$sth = $dbh->prepare(
"INSERT INTO table VALUES (?,?,?)"
);
while(<CSV>) {
chop;
my ($foo,$bar,$baz) = split /,/;
# CSVから取得したバインド値を指定してSQL文を実行
$sth->execute($foo,$bar,$baz);
}

 - データベースに変更を確定(AutoCommitがオフの場合)

$dbh->commit;
$dbh->rollback;

 - データソースでの作業を終了

$dbh->disconnect;

高レベル取り出しメソッド

DBIにはprepare、execute、取り出しループ、finishを組み合わせた便利なメソッドがあります。
たとえば次のようなメソッド、selectrow_array、selectcol_arrayref、selectall_arrayrefなどです。

selectrow_arrayは1つの行または列の値を取り出すときに便利です。リストコンテキストで呼ばれると、先頭行が配列で返されます。

@row = $dbh->selectrow_array(
"SELECT * FROM table "
);
print $row[0], $row[1], "\n" if @row;

スカラコンテキストの場合は、先頭行の最初の列の値を返します。

$ref = $dbh->selectrow_array(
"SELECT f1 FROM table "
);
print $ref, "\n" if $ref;

問い合わせの結果が行を返されなければ、どちらのコンテキストで呼ばれたかによって、空リストかundefを返します。

行を返さない問い合わせにはdoメソッドを使います。doメソッドはステートメントを実行し、影響のあった行を返すか、エラーを示すundefを返します。

$count = $dbh->do("INSERT INTO table SET f1='***'");
$count = $dbh->do("DELETE FROM table LIMIT 1);

プレースホルダとバインド値

プレースホルダ

プレースホルダを解説する前に、その使い方を紹介します。

 - クエスチョン( ? )を利用したステートメント1

INSERT INTO table VALUES (?, ?, ?)

 - クエスチョン( ? )を利用したステートメント2

SELECT f2 FROM table WHERE f1 = ?

上記で使用したクエスチョン( ? )マークをプレースホルダと呼びます。プレースホルダは、そのままでは値を持っていないので、後で実際の値を結びつけます。参照される値をバインド値、実際の値との結びつけをバインドと呼びます。

SQLのLIKEと一緒にプレースホルダを使う場合、プレースホルダは文字列全体を置き換えます。そのためLIKE ?とする場合、LIKEの値にワイルドカードが必要な際は、ワイルドカードも含める必要があります。

# プレースホルダを利用したSQL文の用意
my $sth = $dbh->prepare("INSERT INTO table VALUES (?, ?, ?)");
# 3番目の引数でワイルドカードを使用
$sth->execute($f1, $f2, "%$f3");

※いくつかのドライバはプレースホルダとバインド値をサポートしない。

プレースホルダの意義

プレースホルダを使えば、一度だけINSERT文を準備(prepare)します。各行に対応するバインド値はexecuteメソッドを呼び出すときに与えることができます。何度も準備する必要がなく、アプリーケーションは通常、何倍も速くなります。

# プレースホルダを利用したSQL文の用意
my $sth = $dbh->prepare("INSERT INTO table VALUES (?, ?, ?)")
or die $dbh->errstr;
# 何らかのループ処理
.... {
# バインド値を用意
my($product_code, $qty, $price) = split /,/;
# SQL文の実行
$sth->execute($product_code, $qty, $price);
}
# プレースホルダを利用したSQL文の用意
my $sth = $dbh->prepare(
q{ INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?) }
) or die $dbh->errstr;
# 何らかのループ処理
.... {
# バインド値を用意
my($product_code, $qty, $price) = split /,/;
# SQL文の実行
$sth->execute($product_code, $qty, $price);
}

この例の中で使用されたq{}スタイルの引用は、SQLステートメントの中で使用されるかもしれない引用との衝突を回避します。文字列の中に解釈して欲しい変数がある場合、qq{...}を利用すれば、ダブル・クォートで囲んだのと同じ効果が得られます。

Perlの変数をSELECT文の出力カラムに結びつけるために使われるメソッドについてはbind_columnsを参照してください。

NULL値

NULL値は、Perlでいう未定義の値（undef）になります。ただし、SELECT文での修飾詞にNULL値を使う場合には注意が必要です。

select description from products where product_code = ?

バインド値にundefや空文字をセットしても、期待した結果は得られません。NULLを選択するためには、IS NULLを使います。

SELECT * FROM db_name WHERE name IS NULL