MySQL 5.6 リファレンスマニュアル  /  ...  /  CREATE PROCEDURE および CREATE FUNCTION 構文

13.1.15 CREATE PROCEDURE および CREATE FUNCTION 構文

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

これらのステートメントは、ストアドルーチンを作成します。デフォルトでは、ルーチンはデフォルトデータベースに関連付けられます。ルーチンを明示的に特定のデータベースに関連付けるには、そのルーチンの作成時に、その名前を db_name.sp_name として指定します。

CREATE FUNCTION ステートメントはまた、UDF (ユーザー定義関数) をサポートするために MySQL でも使用されます。セクション24.3「MySQL への新しい関数の追加」を参照してください。UDF は、外部のストアドファンクションと見なすことができます。ストアドファンクションは、その名前空間を UDF と共有します。各種の関数への参照をサーバーが解釈する方法を記述したルールについては、セクション9.2.4「関数名の構文解析と解決」を参照してください。

ストアドプロシージャーを呼び出すには、CALL ステートメントを使用します (セクション13.2.1「CALL 構文」を参照してください)。ストアドファンクションを呼び出すには、式でその関数を参照します。その関数は、式の評価中に値を返します。

CREATE PROCEDURE および CREATE FUNCTION には、CREATE ROUTINE 権限が必要です。このセクションのあとの方で説明されているように、DEFINER 値によっては SUPER 権限も必要になる可能性があります。バイナリロギングが有効になっている場合は、セクション20.7「ストアドプログラムのバイナリロギング」で説明されているように、CREATE FUNCTIONSUPER 権限が必要になることがあります。

デフォルトでは、MySQL は、ルーチン作成者に ALTER ROUTINE および EXECUTE 権限を自動的に付与します。この動作は、automatic_sp_privileges システム変数を無効にすることによって変更できます。セクション20.2.2「ストアドルーチンと MySQL 権限」を参照してください。

DEFINER および SQL SECURITY 句は、このセクションのあとの方で説明されているように、ルーチンの実行時にアクセス権限を確認するときに使用されるセキュリティーコンテキストを指定します。

ルーチン名が組み込みの SQL 関数の名前と同じである場合は、そのルーチンを定義するか、またはあとで呼び出すときに名前とそれに続く括弧の間にスペースを使用しないかぎり、構文エラーが発生します。このため、ユーザー独自のストアドルーチンに既存の SQL 関数の名前を使用することは避けてください。

IGNORE_SPACE SQL モードは、ストアドルーチンではなく、組み込み関数に適用されます。ストアドルーチン名のあとのスペースは、IGNORE_SPACE が有効になっているかどうかには関係なく、常に許可されます。

括弧で囲まれたパラメータリストは、常に存在する必要があります。パラメータが存在しない場合は、() の空のパラメータリストを使用するようにしてください。パラメータ名は大文字と小文字が区別されません。

各パラメータは、デフォルトでは IN パラメータです。それ以外のパラメータを指定するには、パラメータ名の前にキーワード OUT または INOUT を使用します。

注記

INOUT、または INOUT としてのパラメータの指定は、PROCEDURE に対してのみ有効です。FUNCTION の場合、パラメータは常に IN パラメータと見なされます。

IN パラメータは、プロシージャーへの値を渡します。プロシージャーはその値を変更する可能性がありますが、そのプロシージャーから戻ったとき、その変更は呼び出し元に表示されません。OUT パラメータは、プロシージャーから呼び出し元に値を渡します。その初期値はプロシージャー内では NULL であり、そのプロシージャーから戻ったとき、その値は呼び出し元に表示されます。INOUT パラメータは呼び出し元によって初期化され、プロシージャーで変更できます。そのプロシージャーから戻ったとき、プロシージャーによって行われた変更はすべて呼び出し元に表示されます。

OUT または INOUT パラメータごとに、プロシージャーを呼び出す CALL ステートメントでユーザー定義変数を渡して、プロシージャーから戻ったときにその値を取得できるようにします。そのプロシージャーを別のストアドプロシージャーまたはストアドファンクション内から呼び出している場合は、IN または INOUT パラメータとしてルーチンパラメータまたはローカルルーチン変数を渡すこともできます。

ルーチン内に準備されたステートメントでルーチンパラメータを参照することはできません。セクションD.1「ストアドプログラムの制約」を参照してください。

次の例は、OUT パラメータを使用する単純なストアドプロシージャーを示しています。

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

この例では、プロシージャーの定義中に mysql クライアントの delimiter コマンドを使用して、ステートメント区切り文字を ; から // に変更しています。これにより、プロシージャー本体で使用される ; 区切り文字を、mysql 自体が解釈するのではなく、サーバーに渡すようにすることができます。セクション20.1「ストアドプログラムの定義」を参照してください。

RETURNS 句は、FUNCTION (これには必須です) に対してのみ指定できます。これは関数の戻り型を示すものであり、関数本体には RETURN value ステートメントが含まれている必要があります。RETURN ステートメントが異なる型の値を返した場合、その値は正しい型に強制的に変更されます。たとえば、ある関数が RETURNS 句で ENUM または SET 値を指定しているが、RETURN ステートメントが整数を返した場合、その関数から返される値は SET メンバーのセットの対応する ENUM メンバーを示す文字列になります。

次の関数例はパラメータを受け取り、SQL 関数を使用して操作を実行したあと、結果を返します。この場合は、関数定義に内部の ; ステートメント区切り文字が含まれていないため、delimiter を使用する必要はありません。

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

パラメータ型と関数の戻り型は、任意の有効なデータ型を使用するように宣言できます。前に CHARACTER SET 属性がある場合は、COLLATE 属性を使用できます。

routine_body は、有効な SQL ルーチンステートメントで構成されます。これは SELECTINSERT などの単純なステートメントでも、BEGINEND を使用して記述された複合ステートメントでもかまいません。複合ステートメントには、宣言、ループ、およびその他の制御構造ステートメントを含めることができます。これらのステートメントの構文については、セクション13.6「MySQL 複合ステートメント構文」で説明されています。

MySQL では、ルーチンに CREATEDROP などの DDL ステートメントを含めることが許可されます。MySQL ではまた、ストアドプロシージャーに COMMIT などの SQL トランザクションステートメントを含めることも許可されます (ただし、ストアドファンクションには許可されません)。ストアドファンクションには、明示的または暗黙的なコミットまたはロールバックを実行するステートメントを含めることはできません。これらのステートメントのサポートは、SQL 標準では必要ありません。SQL 標準では、各 DBMS ベンダーがこれらのステートメントを許可するかどうかを決められると定めています。

結果セットを返すステートメントはストアドプロシージャー内で使用できますが、ストアドファンクション内では使用できません。この禁止には、INTO var_list 句を含まない SELECT ステートメントや、SHOWEXPLAINCHECK TABLE などのその他のステートメントが含まれます。結果セットを返すことを関数の定義時に判定できるステートメントの場合は、Not allowed to return a result set from a function エラーが発生します (ER_SP_NO_RETSET)。結果セットを返すことを実行時にしか判定できないステートメントの場合は、PROCEDURE %s can't return a result set in the given context エラーが発生します (ER_SP_BADSELECT)。

ストアドルーチン内での USE ステートメントは許可されていません。ルーチンが呼び出されると、暗黙的な USE db_name が実行されます (また、そのルーチンが終了すると元に戻されます)。これにより、そのルーチンには実行中、特定のデフォルトデータベースが割り当てられます。ルーチンのデフォルトデータベース以外のデータベース内のオブジェクトへの参照は、適切なデータベース名で修飾するようにしてください。

ストアドルーチン内では許可されないステートメントの詳細は、セクションD.1「ストアドプログラムの制約」を参照してください。

MySQL インタフェースを備える言語で記述されたプログラム内からのストアドプロシージャーの呼び出しについては、セクション13.2.1「CALL 構文」を参照してください。

MySQL は、ルーチンが作成または変更されたときの有効な sql_mode システム変数の設定を格納し、ルーチンが実行を開始したときの現在のサーバー SQL モードには関係なく、常にそのルーチンを強制的にこの設定で実行します。

呼び出し元の SQL モードからそのルーチンの SQL モードへの切り替えは、引数を評価し、結果として得られる値をルーチンパラメータに割り当てたあとに実行されます。あるルーチンを厳密な SQL モードで定義したが、その呼び出しを非厳密モードで行なった場合は、引数のルーチンパラメータへの割り当てが厳密モードで実行されません。ルーチンに渡される式を厳密な SQL モードで割り当てる必要がある場合は、そのルーチンを厳密モードが有効な状態で呼び出すようにしてください。

COMMENT 特性は MySQL 拡張であり、そのストアドルーチンの説明のために使用できます。この情報は、SHOW CREATE PROCEDURE および SHOW CREATE FUNCTION ステートメントによって表示されます。

LANGUAGE 特性は、そのルーチンが記述されている言語を示します。サーバーはこの特性を無視します。SQL ルーチンのみがサポートされています。

ルーチンは、同じ入力パラメータに対して常に同じ結果を生成する場合は決定的と見なされ、それ以外の場合は非決定的と見なされます。ルーチン定義で DETERMINISTICNOT DETERMINISTIC のどちらも指定されていない場合、デフォルトは NOT DETERMINISTIC になります。関数が決定的であることを宣言するには、明示的に DETERMINISTIC を指定する必要があります。

ルーチンの性質の評価は、作成者の誠実さに基づいています。MySQL は、DETERMINISTIC と宣言されたルーチンに非決定的な結果を生成するステートメントが含まれていないかどうかをチェックしません。ただし、ルーチンの誤った宣言は、その結果やパフォーマンスに影響を与える可能性があります。非決定的なルーチンを DETERMINISTIC として宣言すると、オプティマイザが正しくない実行計画を選択するために、予期しない結果を招くことがあります。決定的なルーチンを NONDETERMINISTIC として宣言すると、使用可能な最適化が使用されなくなるために、パフォーマンスが低下することがあります。

バイナリロギングが有効になっている場合、DETERMINISTIC 特性は、MySQL がどのルーチン定義を受け入れるかに影響を与えます。セクション20.7「ストアドプログラムのバイナリロギング」を参照してください。

NOW() 関数 (または、そのシノニム) あるいは RAND() を含むルーチンは非決定的ですが、引き続きレプリケーションに対して安全である可能性があります。NOW() の場合、バイナリログにはタイムスタンプが含まれ、正しくレプリケートされます。RAND() もまた、ルーチンの実行中に 1 回だけ呼び出されるかぎり、正しくレプリケートされます。(ルーチン実行のタイムスタンプや乱数シードは、マスターとスレーブ上で同一の暗黙的な入力と見なすことができます。)

いくつかの特性によって、ルーチンによるデータ使用の性質に関する情報が提供されます。MySQL では、これらの特性はアドバイザリにすぎません。サーバーがこれらを使用して、あるルーチンにどのような種類のステートメントの実行を許可するかを制約することはありません。

  • CONTAINS SQL は、そのルーチンに、データの読み取りや書き込みを行うステートメントが含まれていないことを示します。これは、これらのどの特性も明示的に指定されていない場合のデフォルトです。このようなステートメントの例として、実行されてもデータの読み取りや書き込みを行わない SET @x = 1 または DO RELEASE_LOCK('abc') があります。

  • NO SQL は、そのルーチンに SQL ステートメントが含まれていないことを示します。

  • READS SQL DATA は、そのルーチンに、データを読み取るステートメント (SELECT など) が含まれているが、データを書き込むステートメントは含まれていないことを示します。

  • MODIFIES SQL DATA は、そのルーチンに、データを書き込む可能性のあるステートメント (INSERTDELETE など) が含まれていることを示します。

SQL SECURITY 特性は、セキュリティーコンテキストを指定する DEFINER または INVOKER のどちらかです。これは、そのルーチンがルーチンの DEFINER 句で指定されたアカウント、またはそのルーチンを呼び出すユーザーのどちらの権限を使用して実行されるかを示します。このアカウントには、そのルーチンが関連付けられているデータベースにアクセスするためのアクセス権が必要です。デフォルト値は DEFINER です。そのルーチンを呼び出すユーザーには、それに対する EXECUTE 権限が必要です。また、そのルーチンが定義者のセキュリティーコンテキストで実行される場合は、DEFINER アカウントにもその権限が必要です。

DEFINER 句は、SQL SECURITY DEFINER 特性を持つルーチンのルーチン実行時にアクセス権限を確認するときに使用される MySQL アカウントを指定します。

DEFINER 句に user 値を指定する場合は、'user_name'@'host_name' (GRANT ステートメントで使用されるのと同じ形式)、CURRENT_USER、または CURRENT_USER() として指定された MySQL アカウントにするようにしてください。DEFINER のデフォルト値は、CREATE PROCEDURE または CREATE FUNCTION ステートメントを実行するユーザーです。これは、明示的に DEFINER = CURRENT_USER を指定するのと同じです。

DEFINER 句を指定した場合は、次のルールによって有効な DEFINER ユーザーの値が決定されます。

  • SUPER 権限がない場合、許可される唯一の user 値は、リテラルで指定するか、または CURRENT_USER を使用して指定した自分のアカウントです。定義者をほかのアカウントに設定することはできません。

  • SUPER 権限がある場合は、構文として有効な任意のアカウント名を指定できます。そのアカウントが実際に存在しない場合は、警告が生成されます。

  • 存在しない DEFINER アカウントでルーチンを作成することはできますが、SQL SECURITY 値が DEFINER であるが、定義者アカウントが存在しない場合は、ルーチン実行時にエラーが発生します。

ストアドルーチンのセキュリティーの詳細は、セクション20.6「ストアドプログラムおよびビューのアクセスコントロール」を参照してください。

SQL SECURITY DEFINER 特性を使用して定義されたストアドルーチン内で、CURRENT_USER は、そのルーチンの DEFINER 値を返します。ストアドルーチン内のユーザー監査については、セクション6.3.13「SQL ベースの MySQL アカウントアクティビティーの監査」を参照してください。

mysql.user テーブルにリストされている MySQL アカウントの数を表示する次のプロシージャーを考えてみます。

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

このプロシージャーには、それがどのユーザーによって定義されている場合でも、'admin'@'localhost'DEFINER アカウントが割り当てられます。また、それがどのユーザーから呼び出された場合でも、そのアカウントの権限で実行されます (デフォルトのセキュリティー特性は DEFINER であるため)。このプロシージャーは、呼び出し元にそれに対する EXECUTE 権限があり、かつ 'admin'@'localhost'mysql.user テーブルに対する SELECT 権限があるかどうかに応じて成功または失敗します。

ここで、このプロシージャーが SQL SECURITY INVOKER 特性を使用して定義されているとします。

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
SQL SECURITY INVOKER
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

このプロシージャーは、依然として 'admin'@'localhost'DEFINER を持っていますが、この場合は呼び出し元ユーザーの権限で実行されます。そのため、このプロシージャーは、呼び出し元にそれに対する EXECUTE 権限と、mysql.user テーブルに対する SELECT 権限があるかどうかに応じて成功または失敗します。

サーバーは、ルーチンパラメータ、DECLARE を使用して作成されたローカルルーチン変数、または関数の戻り値のデータ型を次のように処理します。

  • データ型の不一致やオーバーフローがないかどうか割り当てがチェックされます。変換やオーバーフローの問題によって警告が発生するか、または厳密な SQL モードではエラーが発生します。

  • スカラー値のみを割り当てることができます。たとえば、SET x = (SELECT 1, 2) などのステートメントは無効です。

  • 文字データ型で、宣言内に CHARACTER SET 属性が存在する場合は、指定された文字セットとそのデフォルトの照合順序が使用されます。COLLATE 属性も存在する場合は、デフォルトの照合順序ではなく、その照合順序が使用されます。

    CHARACTER SET および COLLATE 属性が存在しない場合は、ルーチンの作成時に有効なデータベース文字セットおよび照合順序が使用されます。サーバーでデータベース文字セットおよび照合順序が使用されないようにするには、文字データパラメータとして明示的な CHARACTER SET および COLLATE 属性を指定します。

    データベースのデフォルトの文字セットまたは照合順序を変更する場合は、データベースのデフォルトを使用するストアドルーチンを削除および再作成して、それらが新しいデフォルトを使用するようにする必要があります。

    データベース文字セットおよび照合順序は、character_set_database および collation_database システム変数の値で指定されます。詳細は、セクション10.1.3.2「データベース文字セットおよび照合順序」を参照してください。


User Comments
  Posted by John Hirbour on April 8, 2008
if you are using the Pear DB package you just need to add

'client_flags' => 65536

do your DSN array if you are getting: "can't retun a result set in the given context"
  Posted by Sander Stuurwold on November 14, 2008
I do most of the time Microsoft SQL Server, so I needed some time to look how to create a function with variables... here it is. The function gets an XML value from a char field based on the tag...

Sample:

CREATE FUNCTION fnGetXMLinfoVraag4 (xmlTag varchar(30),message text) returns varchar(255)
begin
declare lenField int;
declare xmlTagBegin varchar(30);
declare xmlTagEnd varchar(30);
declare fieldresult varchar(255);
set xmlTagBegin = concat('<', xmlTag, '>');
set xmlTagEnd = concat('</', xmlTag, '>');
set lenField = length(xmlTag) + 2;
set fieldresult = case when locate(xmlTagBegin,message) = 0 then ''
else substring(message,locate(xmlTagBegin,message) + lenField,locate(xmlTagEnd,message) - (locate(xmlTagBegin,message) + lenField)) end;
return fieldresult;
end

When binlogging (for f.e. replication) is enabled the syntax should be
extended like next:

CREATE FUNCTION fnGetXMLinfoVraag4 (xmlTag varchar(30),message text) returns varchar(255)
DETERMINISTIC
READS SQL DATA
begin
declare lenField int;
declare xmlTagBegin varchar(30);
declare xmlTagEnd varchar(30);
declare fieldresult varchar(255);
set xmlTagBegin = concat('<', xmlTag, '>');
set xmlTagEnd = concat('</', xmlTag, '>');
set lenField = length(xmlTag) + 2;
set fieldresult = case when locate(xmlTagBegin,message) = 0 then ''
else substring(message,locate(xmlTagBegin,message) + lenField,locate(xmlTagEnd,message) - (locate(xmlTagBegin,message) + lenField)) end;
return fieldresult;
end

  Posted by Lesley B on November 8, 2010
An example using varchar and character sets

delimiter //
CREATE FUNCTION db.fnfullname ( id smallint(5) unsigned ) RETURNS varchar(160) CHARACTER SET utf8
COMMENT 'Returns the full name of person in db.people table referenced by id where FirstName and FamilyName are not null but MiddleName may be null'
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE fulname varchar(160) CHARACTER SET utf8;
SELECT CONCAT_WS(' ', db.people.FirstName, db.people.MiddleName, db.people.FamilyName) into fulname from db.people where db.people.id=id;
RETURN fulname;
END
//

delimiter ;

  Posted by Yogesh Gadiya on April 6, 2011
Sample for Procedure with insert to trace error logs

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_error_log`(
`error_level` int(11),
`error_level_name` varchar(512),
`error_message` longtext,
`error_file` text,
`error_line` int(11),
`error_context` longtext,
`error_query_string` longtext,
`error_time` text ,
`user_id` int(11),
`post_data` longtext,
`user_msg` text)
BEGIN
INSERT INTO tbl_error_log(error_level, error_level_name, error_message, error_file,error_line, error_context,error_query_string,error_time, user_id, post_data, user_msg)
values(error_level, error_level_name, error_message, error_file,error_line, error_context,error_query_string, error_time, user_id, post_data, user_msg);
END$$
  Posted by Brett Vandale on December 14, 2011
I didn't find a lot of examples for MySQL Stored Procs so I thought I'd add one. This one uses a temp table and iterates through the rows. It was a pain in the butt to debug in phpMyAdmin. There was a lot of dropping and re-creating.

############################

delimiter $$

create procedure assignKeywords( currCID int )
begin

declare currKeywordID int;
declare currTitle varchar(255);
declare currKeyword varchar(255);
declare currContent text;
declare keywordsCSV varchar(255);

-- get content
select id, title, content from content where id = currCID into currCID, currTitle, currContent;

-- create the keywords temp table to iterate
drop temporary table if exists keywords_temp;
create temporary table keywords_temp as
select id, display_val from vars where name = 'KEYWORDS' order by display_order desc;

set keywordsCSV = ",";
while (select count(*) from keywords_temp) > 0
do

select id, display_val from keywords_temp limit 1 into currKeywordID, currKeyword;

-- search the content for current keyword
if instr(currContent,currKeyword) > 0 || instr(currTitle,currKeyword) > 0
then
set keywordsCSV = CONCAT(keywordsCSV, currKeywordID);
set keywordsCSV = CONCAT(keywordsCSV, ",");
end if;

delete from keywords_temp where id = currKeywordID;

end while;

drop temporary table if exists keywords_temp;

update content set keywords = keywordsCSV where id = currCID;

end;

###############################
  Posted by Rafal Jaworowski on December 28, 2011
CREATE TABLE IF NOT EXIST Osob
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (P_Id)
);
CREATE TABLE uzytkownicy2
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) References Osob(P_Id)
);
-----------------------------------
INSERT INTO `osob`(`O_Id`, `OrderNo`, `P_Id`) VALUES (1,2,3)
INSERT INTO `uzytkownicy2`(`O_Id`, `OrderNo`, `P_Id`) VALUES (1,2,3)
-----------------------------------
DELIMITER $$
CREATE PROCEDURE wam2(OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM trenowanie_mysql.osob;
END$$
DELIMITER ;
------------------------------------
CALL wam2(@a);
Sign Up Login You must be logged in to post a comment.