デベロッパー

デベロッパー

WindowsでPHPからストアドプロシージャ/ストアドファンクションを実行する

Octavia Andreea Anghel
2009年12月15日 / 10:00
 
 

はじめに

 MySQL 5.0の新機能にストアドプロシージャとストアドファンクションというものがあります。ストアドプロシージャとは、1つ以上のSQLステートメントを1つの手続きとしてまとめ上げ、それをデータベースサーバに格納したものです。

 本稿では、基本的なストアドプロシージャとストアドファンクションの作り方を示したうえで、PHPからデータベースエクステンションを利用してMySQLのストアドプロシージャとストアドファンクションを呼び出す方法を説明します。

ストアドプロシージャを使うメリット

 ストアドプロシージャには、パフォーマンスを高める効果があります。理由は、事前にコンパイルでき、しかもクライアントからサーバに名前と必要なパラメータだけを送信すれば実行でき、コードを丸ごと送信しなくて済むからです。そのほか次のメリットがあります。

  • 簡単に使える1つの単位として処理をまとめ上げることで複雑な操作を単純化できる
  • 十分に検証されたストアドプロシージャを再利用することで、エラーの発生を抑えることができる
  • 実行方法が言語や環境に依存しない。データベースサーバの側に置かれるので、呼び出すアプリケーション環境が違ってもストアドプロシージャそのものは変化しない
  • データへのアクセスを制限することでデータ破壊のリスクを減らせる
  • ネットワークトラフィックを削減できる。複雑なタスクを反復的に実行する場合は、データを取得して何らかのロジックを適用し、その結果に基づいて別のデータを取得するといったことが必要になるが、こうした複数ステップの処理がデータベースサーバ上で完結するため、クライアントとデータベースサーバの間で結果セットや新たなクエリを往復させなくて済む

MySQLでのストアドプロシージャの作成

 MySQL 5.0になって、ようやくストアドプロシージャの機能が導入されました。この実装では、個々のストアドプロシージャまたはストアドファンクションが特定のデータベースに関連付けられます。そのため次のような性質があります。

  • ストアドプロシージャ(またはストアドファンクション)を呼び出すと、データベースで自動的にUSE db_nameコマンドが実行され、ストアドプロシージャの終了までその効果が持続する
  • 特定のデータベースで作成できるストアドプロシージャの名前は、そのデータベース内で一意的に決まるものでなければならない。例えば、bookデータベースに関連付けられたストアドプロシージャprocまたはストアドファンクションfuncを呼び出すには、CALL book.proc()またはCALL book.func()と書く
  • データベースを削除すると、そのデータベースに関連付けられたストアドプロシージャやストアドファンクションもすべて削除される
 プロシージャまたはファンクションの定義は次の2ステップで行われます。

  1. プロシージャまたはファンクションの名前を定義し、そのパラメータを設定する
  2. プロシージャまたはファンクションの本体をステートメントBEGINENDの間に定義する
 基本的な構文は次のとおりです。

CREATE PROCEDURE procedure_name ([procedure_parameter[,...]])
    routine_body
 procedure_parameterは、パラメータと方向を示す次の引数から成るリストです。

  • IN:値をプロシージャに渡す。その値をプロシージャは変更できるが、プロシージャから復帰したとき、呼び出し元には変更は見えない
  • OUT:値をプロシージャから呼び出し元に戻す。プロシージャ内のパラメータの初期値はNULL。通常、プロシージャはこの値を変更し、プロシージャから復帰したとき呼び出し元には最終的な値が見える
  • INOUT:このINOUTパラメータを初期化するのは呼び出し元だが、プロシージャも値を変更でき、プロシージャから復帰したとき呼び出し元には最終的な値が見える
※著者注
 INOUT、またはINOUTパラメータは、プロシージャでのみ使用できます。ファンクションのパラメータは常にINパラメータになります。

MySQLでのストアドプロシージャの呼び出し

 MySQL内でストアドプロシージャを呼び出すには、次のcallメソッドを使います。

call books.proc(@a);
select @a;

MySQLでのストアドファンクションの作成

 ファンクション(すなわち関数)を作成するときは、ストアドプロシージャとの次の違いに注意してください。

  • キーワードprocedureをキーワードfunctionに置き換える
  • すべてINパラメータなので、パラメータの方向を指定する必要はない
  • パラメータリストに続け、キーワードRETURNSで戻り値の型を指定する
  • BEGIN...ENDブロックは必要ない
  • 関数を呼び出すには、構文select function(parameter_list)を使う
 関数を作成する構文は次のとおりです。

CREATE FUNCTION function_name ([function_parameter[,...]])
RETURNS type
routine_body
 次の例は、計算をして整数値(int)を返す簡単なストアドファンクションです。

CREATE FUNCTION simple_operation (price int) RETURNS int
RETURN price*1000
 これをSQL Serverから呼び出すには、次の構文を使います。

SELECT simple_operation(5)
 この例の戻り値は5000です。

PHPからストアドプロシージャを呼び出す

 PHPからMySQLのストアドプロシージャ/ストアドファンクションを呼び出すには、次のデータベースエクステンションが必要です。

 これらのエクステンションをインストールした後、PHPからMySQLのストアドプロシージャ/ストアドファンクションを呼び出すことができます。既に述べたように、MySQLのストアドプロシージャとファンクションは特定のデータベースに関連付けられます。以下の例では、次のSQLステートメントで作成したbooksデータベースを使用します。

create table bookstore 
   (id int not null auto_increment primary key,
    book varchar(50),
    author varchar(50),
    isbn varchar(50),
    price int);
 図1のブックストアテーブル(bookstore)の設定に用いたSQLステートメントは次のとおりです。

INSERT INTO bookstore (id,book,author,isbn,price) VALUES   
  (1,"Introduction to PHP","Mark User","3334-4424-334-3433",500)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES 
  (2,"DHTML and CSS","Teague Sanders","4545-23-23-23-23232",1500)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES 
  (3,"Introduction to PHP","Weeling Tom","4334-2323-23233-434",300)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES 
  (4," Web design"," Weeling Tom"," 4334-2323-23233-434",600)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES 
  (5," PHP 5"," Weeling Tom"," 444-87-67665-678678",600)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES 
  (6," JavaServer Pages"," Tick Own"," 897-9898-987-099",800)
 図1に、テーブルbookstoreの構造と一部の内容を示します。

図1 ブックストアテーブル: テーブルの内容と構造(booksデータベースから取得)
図1 ブックストアテーブル: テーブルの内容と構造(booksデータベースから取得)

MySQLデータベースエクステンションを使用してストアドプロシージャを呼び出す

 MySQLデータベースエクステンションはMySQLデータベースサーバーにアクセスできるようにするものです。php_mysql.dllを、他のエクステンションと同じようにインストールします。MySQLの関数の詳しい説明は、ここを参照してください。

 まず、簡単なストアドプロシージャが必要です。このプロシージャ(proc)は、前に作成したbookstoreテーブルのすべてのフィールドを選択します。

CREATE PROCEDURE proc ( )
BEGIN
  SELECT * from bookstore;
END
 次のPHPスクリプトは、MySQLサーバーに接続し、booksデータベースを選択し、procストアドプロシージャ(引数なし)を呼び出し、結果を出力します。

<?php
 //Create the connecting to MySQL
 $con = mysql_connect('localhost','root','',false,65536);
 mysql_select_db('books');
 
 //Call the proc() procedure
 $result= mysql_query("CALL proc();")
 or die(mysql_error());
 
//Output the result
while($row = mysql_fetch_row($result))
{
 for($i=0;$i<=6;$i++){
   echo $row[$i]."<br>";
 }
echo "---";
}
//Close the connection
mysql_close($con);
?>
※著者注
 構文$con = mysql_connect('localhost','root','');ではうまくいきません。ストアドプロシージャからPHPに結果セットを返すためには、マルチステートメント接続オプションかマルチ結果オプション(またはその両方)を使う必要があるからです。ルーチンから結果セットが返されなければ、どちらのオプションも必要ありません。
 出力は次のようになります。

1---Introduction to PHP---Mark User---3334-4424-334-3433---500--------
2---DHTML and CSS---Teague Sanders---4545-23-23-23-23232---1500-------
3---Introduction to PHP---Weeling Tom---4334-2323-23233-434---300-----
4---Web design---Weeling Tom---4334-2323-23233-434---600---------
5---PHP 5---Weeling Tom---444-87-67665-678678---600---------
6---JavaServer Pages---Tick Own---897-9898-987-099---800---------
 次のプロシージャ(total_price)は、bookstoreテーブルのpriceフィールドの総計を計算します。ここでは総計を保持するためにOUTパラメータを使用しています。

CREATE PROCEDURE total_price ( OUT total int)
BEGIN
SELECT sum(price) into total from bookstore;
END
 次のPHPスクリプトは、total_priceプロシージャを呼び出し、OUTパラメータtotalint型)を使用して結果を表示します。

<?php
 $con = mysql_connect('localhost','root','',false,65536);
 mysql_select_db('books');
 
 //Calling the total_price stored procedure using the @t OUT parameter
 $result= mysql_query("CALL total_price(@t);")
 or die(mysql_error());
 
 //Listing the result
 $rs = mysql_query( 'SELECT @t' );
 while($row = mysql_fetch_row($rs))
 {
  echo 'The total price is = '.$row[0];
 }
mysql_close($con);
?>
 出力は次のようになります。

The total price is = 4300

MySQLエクステンションを使用してストアドファンクションを呼び出す

 ここでは、関数の呼び出し例として、次の簡単なストアドファンクションを使います。

CREATE FUNCTION simple_operation (price int) RETURNS int(11)
RETURN price*1000
 simple_operation関数は、整数の引数を取り、簡単な計算を行って整数の結果を返します。

<?php
 $con = mysql_connect('localhost','root','',false,65536);
 mysql_select_db('books');
 
 //Calling the simple_operation function
 $rs = mysql_query( 'SELECT simple_operation(5)' );
 while($row = mysql_fetch_row($rs))
 {
  echo 'The total price is = '.$row[0];
 }
mysql_close($con);
?>
 出力は次のようになります。

The total price is = 5000

MySQLiを使用してストアドプロシージャを呼び出す

 MySQLi(MySQL Improved)エクステンションは、MySQL 4.1以上で提供される機能を利用できるようにするものです。MySQLiエクステンションのDLL(php_mysqli.dll)はデフォルトでは有効にならないので、php.iniの中で有効にする必要があります。MySQLiエクステンションの機能については、このリンクを参考にしてください。

 次の例では、前に説明した2つのプロシージャproc()total_price()を使います。MySQLiエクステンションを使用してストアドプロシージャを呼び出す構文は次のとおりです。

$mysqli->query( 'CALL proc()' );
 次のPHPスクリプトは、proc()ストアドプロシージャを呼び出し、結果を出力します。

<?php
//Connecting to the books database 
$mysqli = new mysqli('localhost', 'root', '', 'books');
//Calling the proc() procedure
$rs = $mysqli->query( 'CALL proc()' );
while($row = $rs->fetch_object())
{
print_r($row);
echo "<br />";
}
?>
 出力は次のようになります。

stdClass Object ( [id] => 1 [book] => Introduction to PHP [author] => 
   Mark User [isbn] => 3334-4424-334-3433 [price] => 500 ) 
stdClass Object ( [id] => 2 [book] => DHTML and CSS [author] => 
   Teague Sanders [isbn] => 4545-23-23-23-23232 [price] => 1500 ) 
stdClass Object ( [id] => 3 [book] => Introduction to PHP [author] => 
   Weeling Tom [isbn] => 4334-2323-23233-434 [price] => 300 ) 
stdClass Object ( [id] => 4 [book] => Web design [author] => 
   Weeling Tom [isbn] => 4334-2323-23233-434 [price] => 600 ) 
stdClass Object ( [id] => 5 [book] => PHP 5 [author] => 
   Weeling Tom [isbn] => 444-87-67665-678678 [price] => 600 ) 
stdClass Object ( [id] => 6 [book] => JavaServer Pages [author] => 
   Tick Own [isbn] => 897-9898-987-099 [price] => 800 )
 同様に、次の例では、total_price()ストアドプロシージャを呼び出し、結果を出力します。

<?php
$mysqli = new mysqli('localhost', 'root', '', 'books');
$rs = $mysqli->query( 'CALL total_price(@t)' );
$rs = $mysqli->query( 'SELECT @t' );
while($row = $rs->fetch_object())
{
print_r($row);
echo "<br />";
}
?>
 この例の出力は次のようになります。

The total price : stdClass Object ( [@t] => 4300 )

PDOを使用してストアドプロシージャを呼び出す

 PDOのドキュメントには次のように書かれています。

 「PHP Data Objects(PDO)エクステンションは、PHPからデータベースにアクセスするための軽量で一貫性のあるインターフェースを定義するものです。このPDOインターフェースを実装する個々のデータベースドライバが、データベース固有の機能をいつものエクステンション機能として公開できるようにします。PDOエクステンションだけではデータベースの機能は実行できません。データベースサーバにアクセスするためには、データベース固有のPDOドライバを使う必要があります」

※編集部注
 原文では上記引用内のURLがリンク切れだったため、修正しました。
 PDOエクステンションを使うと、アクセスメソッドを変えなくてもバックエンドデータベースを変更できる柔軟性が生まれます。PDOには、MySQLのバージョンに関係なく同じデータアクセスメソッドを使えるメリットがあります。そのため、旧バージョンのMySQLでは標準のMySQLエクステンションを使い、新バージョンではMySQLiエクステンションを使うといったことをしなくて済みます。また、MySQLのバージョンに関係なくオブジェクト指向コードを利用できるメリットもあります。従って、データベース抽象化レイヤに柔軟性が必要なときはPDOでコードの堅牢性と移植性を高め、アプリケーションの動作を高速化したいときは2つのインターフェースのどちらか(MySQLまたはMySQLi)を使います。

 このエクステンションを使うには、次の2行をphp.iniに追加します。

extension=php_pdo.dll
extension=php_pdo_mysql.dll
 上の2行目は、このエクステンションを動かすために必須のMySQLドライバです。次の例では、total_priceストアドプロシージャを呼び出します。

<?php
$pdo = new PDO('mysql:dbname=books;host=127.0.0.1', 'root', '');
print 'PDO: simple select';
$pdo->query( 'CALL total_price(@t)' );
foreach($pdo->query( 'SELECT @t' ) as $row)
{
print_r($row);
}
?>
 出力は次のようになります。

PDO: simple select
Array ( [@t] => 4300 [0] => 4300 )
 特定の行を選択するプロシージャを書くのは簡単です。例えば、次のプロシージャはbookstoreテーブルのレコードのうち、IDが3に等しいものを選択します。

CREATE PROCEDURE proc_new ()
BEGIN
SELECT * from bookstore where id=3;
END
 次の例では、proc_new()を呼び出し、結果を表示します。

<?php
//Connecting to MySQL server and to the books database
$pdo = new PDO('mysql:dbname=books;host=127.0.0.1', 'root', '');
print 'Calling the proc_new() stored procedure using PDO';
foreach($pdo->query( 'CALL proc_new()' ) as $row)
{
print_r($row);
}
?>
 出力は次のようになります。

Calling the proc_new() stored procedure using PDO
Array ( [id] => 3 [0] => 3 
        [book] => Introduction to PHP [1] => 
           Introduction to PHP 
        [author] => Weeling Tom [2] => Weeling Tom 
        [isbn] => 4334-2323-23233-434 [3] => 4334-2323-23233-434 
        [price] => 300 [4] => 300 )
 本稿では、簡単なストアドプロシージャ/ストアドファンクションの作り方と、それらをPHPから呼び出すためのデータベースエクステンション(MySQL、MySQLi、PDO)の使い方を説明しました。これらを必要なときにいつでも利用できるようにしておけば、コードの単純化と高速化を実現でき、同時にアプリケーションがバックエンドデータベースの変更に影響されないようになるので、エラーが全般的に抑えられます。

著者紹介

Octavia Andreea Anghel(Octavia Andreea Anghel)
経験豊富なPHP開発者。現在は、国内外のソフトウェア開発コンテストに参加するプログラミングチームの主任トレーナーを務める。国レベルの教育プロジェクト開発のコンサルティングも担当している。共著書に『XML technologies?XML in Java』があり、XML部分の執筆を担当。PHPやXMLのほか、ソフトウェアアーキテクチャ、Webサービス、UML、ハイパフォーマンスな単体テストについても関心を寄せている。
【関連記事】
Oracle、『MySQL』の処遇で EU に譲歩との報道を否定
よりシンプルで高速、より強力に:MySQL 最適化のヒント(1)
よりシンプルで高速、より強力に:MySQL 最適化のヒント(2)
欧州委員会、Sun 買収に関する Oracle の反論期間を1週間延長
PHP Rulesでスパゲッティコードをすっきりと

New Topics

Special Ad

ゆりかごからロケットまで、すべての乗り物をエンジョイ
ゆりかごからロケットまで、すべての乗り物をエンジョイ えん乗り」は、ゆりかごからロケットまで、すべての乗り物をエンジョイする、ニュース、コラム、動画などをお届けします! てんこ盛りをエンジョイするのは こちらから

Hot Topics

IT Job

Interviews / Specials

Popular

Access Ranking

Partner Sites