ストアドプロシージャとストアドファンクションを完全に理解：MariaDB ROUTINES テーブル徹底解説

ROUTINES テーブルの列

ROUTINES テーブルには、各ルーチンに関する以下の情報が含まれています。

ROUTINE_COMMENT: ルーチンに関するコメント
SQL_MODE: ルーチンの作成時に設定されていた SQL モード
LAST_ALTERED: ルーチンが最後に変更された日時
CREATED: ルーチンが作成された日時
COLLATION_NAME: ルーチンで使用される照合順序名
CHARACTER_SET_NAME: ルーチンで使用される文字セット名
IS_VOLATILE: ルーチンが揮発性かどうかを示すフラグ
IS_DETERMINISTIC: ルーチンが決定論的かどうかを示すフラグ
ROUTINE_DEFINITION: ルーチンの定義テキスト
CHARACTER_OCTET_LENGTH: ルーチンの戻り値の最大オクテット長 (ストアドファンクションの場合のみ)
CHARACTER_MAXIMUM_LENGTH: ルーチンの戻り値の最大文字長 (ストアドファンクションの場合のみ)
DATA_TYPE: ルーチンの戻り値データ型 (ストアドファンクションの場合のみ)
ROUTINE_TYPE: ルーチンのタイプ (PROCEDURE または FUNCTION)
ROUTINE_NAME: ルーチンの名前
ROUTINE_SCHEMA: ルーチンが格納されているスキーマ名
ROUTINE_CATALOG: ルーチンが格納されているカタログ名
SPECIFIC_NAME: ルーチンの個別名

ROUTINES テーブルの利点

ROUTINES テーブルを使用すると、次のような利点が得られます。

ルーチンに関するコメントにアクセスできます。
ルーチンで使用される文字セットと照合順序を確認できます。
ルーチンが決定論的か揮発性かを判断できます。
ルーチンの定義テキストを確認できます。
ルーチンの作成日、更新日、および SQL モードを確認できます。
データベース内のすべてのルーチンの包括的なリストを取得できます。

ROUTINES テーブルの使用例

ROUTINES テーブルは、さまざまな目的に使用できます。次に、いくつかの例を示します。

データベース内のすべてのストアドプロシージャを一覧表示します。

SELECT SPECIFIC_NAME, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME = 'my_database'
AND ROUTINE_NAME = 'my_function';

データベース内のすべての決定論的ルーチンを特定します。

SELECT SPECIFIC_NAME, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE IS_DETERMINISTIC = 1;

ルーチンの作成日と更新日を確認します。

SELECT SPECIFIC_NAME, ROUTINE_NAME, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES;

ルーチンの定義テキストを確認します。

SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME = 'my_database'
AND ROUTINE_NAME = 'my_procedure';

ROUTINES テーブルには、組み込み SQL 関数またはユーザー定義関数 (UDF) に関する情報は含まれません。
ROUTINES テーブルは、読み取り専用のテーブルです。つまり、このテーブルを直接変更することはできません。

データベース内のすべてのストアドプロシージャを一覧表示する

SELECT SPECIFIC_NAME, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

このクエリは、ROUTINE_TYPE 列が PROCEDURE であるすべての行を INFORMATION_SCHEMA.ROUTINES テーブルから返します。つまり、データベース内のすべてのストアドプロシージャの名前が表示されます。

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME = 'my_database'
AND ROUTINE_NAME = 'my_function';

このクエリは、SPECIFIC_NAME 列が my_database であり、ROUTINE_NAME 列が my_function である行を INFORMATION_SCHEMA.ROUTINES テーブルから返します。つまり、my_database スキーマ内の my_function ストアドファンクションに関するすべての情報が表示されます。

データベース内のすべての決定論的ルーチンを特定する

SELECT SPECIFIC_NAME, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE IS_DETERMINISTIC = 1;

このクエリは、IS_DETERMINISTIC 列が 1 であるすべての行を INFORMATION_SCHEMA.ROUTINES テーブルから返します。つまり、データベース内のすべての決定論的ルーチンの名前が表示されます。

ルーチンの作成日と更新日を確認する

SELECT SPECIFIC_NAME, ROUTINE_NAME, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES;

このクエリは、INFORMATION_SCHEMA.ROUTINES テーブルからすべての列を返します。つまり、データベース内のすべてのルーチンの名前、作成日、更新日が表示されます。

ルーチンの定義テキストを確認する

SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME = 'my_database'
AND ROUTINE_NAME = 'my_procedure';

このクエリは、SPECIFIC_NAME 列が my_database であり、ROUTINE_NAME 列が my_procedure である行の ROUTINE_DEFINITION 列を INFORMATION_SCHEMA.ROUTINES テーブルから返します。つまり、my_database スキーマ内の my_procedure ストアドプロシージャの定義テキストが表示されます。

SHOW ステートメント

MySQL 8.0 より前のバージョンでは、SHOW ステートメントを使用して、ROUTINES テーブルと同等の情報を取得できます。次に例を示します。

データベース内のすべてのストアドプロシージャを一覧表示

SHOW PROCEDURE STATUS;

SHOW CREATE FUNCTION my_database.my_function;

システムカタログビュー

MariaDB 10.2 以降では、次のシステムカタログビューを使用して、ROUTINES テーブルと同等の情報にアクセスできます。

sys.functions: ストアドファンクションに関する情報
sys.procedures: ストアドプロシージャに関する情報

これらのビューは、ROUTINES テーブルよりも多くの情報を含む場合があることに注意してください。

カスタムクエリ

場合によっては、カスタムクエリを使用して ROUTINES テーブルよりも柔軟な方法で情報にアクセスすることが必要な場合があります。たとえば、特定の条件に一致するルーチンのみを取得したい場合は、次のクエリを使用できます。

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME LIKE '%my_pattern%';

最適な方法を選択する

使用する方法は、ニーズによって異なります。 ROUTINES テーブルは、シンプルで使いやすいインターフェースを提供します。ただし、より多くの情報が必要な場合や、より柔軟なクエリを実行する必要がある場合は、SHOW ステートメント、システムカタログビュー、またはカスタムクエリを使用する方がよい場合があります。

カスタムクエリを使用する場合は、データベーススキーマに関する深い理解が必要であることに注意してください。
ROUTINES テーブル、SHOW ステートメント、およびシステムカタログビューは、すべてパフォーマンスに影響を与える可能性があることに注意してください。大量のデータにアクセスする場合は、注意して使用する必要があります。