第6回：ストアドプロシージャ (3/3)

TOP＞サーバ構築・運用＞ PostgreSQLのユーザ定義関数とMySQLのストアドファンクションの相違

徹底比較!! PostgreSQL vs MySQLパート2

第６回：ストアドプロシージャ
著者：NTTデータ藤塚勤也 2007/7/25

PostgreSQLのユーザ定義関数とMySQLのストアドファンクションの相違

   パラメータ値を使用してSELECT文を実行する際、その検索結果を入れるための変数を定義しています。PostgreSQL、MySQLともにDECLARE句にてその変数を定義しますが、PostgreSQLの場合はDECLARE句による変数定義をBEGIN句の外側にて行い、MySQLの場合はBEGIN句の内側で行うといった違いがあります。

   また、SELECT文の検索結果を変数に入れるための構文として、PostgreSQLは「SELECT INTO 変数名 SELECT項目」の順ですが、MySQLは「SELECT SELECT項目INTO 変数名」の順といった違いもあります。

   個々の違いはほんのわずかですが、ストアドプロシージャを実装する際や、どちらかのRDBMSで実装されているストアドプロシージャをもう一方のRDBMSにポーティングする際などには問題となる違いですので、注意が必要です。

MySQLのストアドファンクションとストアドプロシージャの相違

前述した通り、処理結果の返却値の記述方法が異なることがわかると思います。ストアドファンクションは、RETURN文にて値を返却していますが、ストアドプロシージャはOUT指定したパラメータに値をセットすることにより値を返却しています。

なお、ここでは具体的な例を示していませんが、PostgreSQLのユーザ定義関数でもOUT指定したパラメータを使って、値を返却することができます。

実行結果

今回作成した3種類のストアドプロシージャは、すべて実行している内容は同一です。

TABLE01内の4レコードが処理状態であることを確認
入力パラメータ値に’E’を指定し、SELECT文から呼び出し。COL01が’E’のレコードは存在しないためINSERTされる
入力パラメータ値に’A’を指定し、SELECT文から呼び出し。COL01が’A’のレコードは存在しているためUPDATEされる
TABLE01内の全レコードの内容を確認。COL01が’A’のレコードのCOL02の値が0に更新されている。COL01が’E’のレコードが挿入されている

表2：ストアドプロシージャの実行内容

以下は、それぞれのsp_test()の実行結果です。

まず、以下はPostgreSQLのユーザ定義関数の実行結果です。


		psql> select * from table01 order by col01;

		 A     |   100

		 B     |   100

		 C     |   100

		 D     |   100


		psql> select sp_test('E');

		 INSERT


		psql> select sp_test('A');

		 UPDATE


		psql> select * from table01 order by col01;

		 A     |     0

		 B     |   100

		 C     |   100

		 D     |   100

		 E     |     0

続いて、MySQLのストアドファンクションでの実行結果です。


		mysql> select * from table01 order by col01;

		+-------+-------+

		| col01 | col02 |

		+-------+-------+

		| A    |   100 |

		| B    |   100 |

		| C    |   100 |

		| D    |   100 |

		+-------+-------+


		mysql> select sp_test('E');

		+--------------+

		| sp_test('E') |

		+--------------+

		| INSERT       |

		+--------------+


		mysql> select sp_test('A');

		+--------------+

		| sp_test('A') |

		+--------------+

		| UPDATE       |

		+--------------+


		mysql> select * from table01 order by col01;

		+-------+-------+

		| col01 | col02 |

		+-------+-------+

		| A    |     0 |

		| B    |   100 |

		| C    |   100 |

		| D    |   100 |

		| E    |     0 |

		+-------+-------+

最後に、MySQLのストアドプロシージャでの実行結果です。


		mysql> select * from table01 order by col01;

		+-------+-------+

		| col01 | col02 |

		+-------+-------+

		| A     |   100 |

		| B     |   100 |

		| C     |   100 |

		| D     |   100 |

		+-------+-------+

		mysql> call sp_test('E',@ret);

		mysql> select @ret;

		+--------+

		| @ret  |

		+--------+

		| INSERT |

		+--------+

		mysql> call sp_test('A',@ret);

		mysql> select @ret;

		+--------+

		| @ret   |

		+--------+

		| UPDATE |

		+--------+

		mysql> select * from table01 order by col01;

		+-------+-------+

		| col01 | col02 |

		+-------+-------+

		| A     |     0 |

		| B     |   100 |

		| C     |   100 |

		| D     |   100 |

		| E     |     0 |

		+-------+-------+

MySQLのストアドプロシージャがCALL文を使用して呼び出しているところが特徴的です。CALL文を使用した場合、ストアドプロシージャの返却値はパラメータ内に入っていますので、それを取り出すためにSELECT文を使用して、そのパラメータ値を確認しています。

ストアドプロシージャの有効性

   ストアドプロシージャを使用する目的の1つに、クライアントとサーバ間でのSQL文の送受信量を削減することがあります。この目的のためにストアドプロシージャを使用するのであれば、PostgreSQL、MySQL共に必要十分な機能を有しています。

   十分にその魅力を紹介しきれていませんが、PostgreSQLのユーザ定義関数は、C言語での開発も可能であるなど、上記の目的以上の有効性を持つ機能です。そのため、PostgreSQL自身の追加機能の位置付けとして、ユーザ定義関数が使用されています。

   例えばpgstattuple()などが有名です。これらの実体はユーザ定義関数ですが、その用途としては、PostgreSQLによって開発されたデータベースを運用していくための機能となっています。

前のページ 1 2 3

著者プロフィール
株式会社NTTデータ藤塚勤也
基盤システム事業本部オープンソース開発センタシニアスペシャリスト。
日本タンデムコンピューターズ（現日本HP）を経て、2003年よりNTTデータにてOSS分野に参画。日頃はオリジナルOSSの開発や、OSSを用いたシステム構築への技術支援に従事。「RDBMS解剖学」（翔泳社）を共著。

INDEX
第6回：ストアドプロシージャ
	今はMySQLでもストアドプロシージャが使える！
	MySQLのストアドプロシージャ
	PostgreSQLのユーザ定義関数とMySQLのストアドファンクションの相違

徹底比較!! PostgreSQL vs MySQLパート2
第1回	同じようで違うデータ型
第2回	拡張部分によって違いがでてくるSQL文
第3回	実は差があるキャラクターセットの違い
第4回	PostgreSQLとMySQLに対応したGUIツールを使いこなす
第5回	バックアップとリストア
第6回	ストアドプロシージャ
第7回	トリガ
第8回	それぞれが持つビュー機能

タイプ

連載

特集

人気記事トップ10

新着記事

企画広告も役立つ情報バッチリ！ Sponsored