MySQLカスタム関数とストアド・プロシージャ
8425 ワード
一、Mysqlカスタム関数
カスタム関数(user-defined function UDF)はmysqlを拡張する方法であり、組み込み関数と同じ使い方をします.
カスタム関数構文を作成するには、次の手順に従います.
CREATE FUNCTION function_name(parameter_nametype,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body
簡単に言えば、
CREATE FUNCTION関数名(パラメータリスト)
RETURNS戻り値タイプ
かんすうたい
関数ボディについて:
1.関数体は合法的なSQL文から構成される.
2.関数体は、単純なselect文またはinsert文であってもよい.
3.関数体が適合構造であればbegin...endを使用する.
4.複合構造は宣言、循環、制御構造を含むことができる.
カスタム関数でローカル変数構文を定義するには、次の手順に従います.
変数に構文を割り当てます.
カスタム関数を削除するには
DROP FUNCTION function_name;
カスタム関数構文を呼び出します.
SELECT function_name(parameter_value,...)
例:
二、Mysqlストレージプロセス
上記の過程において,構文解析とコンパイルの段階を省略すれば,効率を向上させることができる.そのため、ストレージ・プロシージャを使用してこの問題を解決します.
ストアド・プロシージャ:SQL文と制御文のプリコンパイル・セットで、1つの名前で格納され、1つのユニットとして処理されます(類似関数).ストレージ・プロシージャはデータベースに格納され、アプリケーション・コールによって実行され、ユーザーが変数を宣言し、プロセス制御を行うことができます.ストレージ・プロシージャは、入力タイプのパラメータと出力タイプのパラメータを受け入れることができ、複数の戻り値が存在することができる.最初の呼び出し時にのみ文法解析とコンパイルを行い,以降の呼び出しは直接コンパイルを呼び出した結果,効率が大幅に向上した.
メリット:
1.SQL文の機能と柔軟性を強化する:制御文によってプロセスを制御と判断できる
2、より速い実行速度を実現する:クライアントが初めてストレージプロセスを呼び出す時、MySQLエンジンはそれに対して文法分析、コンパイルなどの操作を行い、それからコンパイル結果をメモリに保存するので、初めて以前の効率と同じであるが、後で直接メモリのコンパイル結果を呼び出し、効率が向上する
3、ネットワークトラフィックを減らす:例えば記録を削除し、DELETE FROM xx WHEREを入力するつもりだった...;転送する文字が多い場合は、ストレージ・プロシージャと書くと、ストレージ・プロシージャの名前と対応するパラメータを呼び出すだけでよいため、転送する文字の数が少ないため、ネットワーク・トラフィックが減少します.
ストアド・プロシージャ構文の作成:
create procedure sp_name(inプロシージャパラメータ、outプロシージャパラメータ、inoutプロシージャパラメータ...)
begin
/*これはプロシージャです*/
End
プロシージャボディについて
(1)プロセス体は合法的なSQL文から構成される.
(2)プロシージャボディは任意のSQL文であってもよい.テーブルを削除し、接続しますが、データテーブルは作成できません.
(3)プロセス体が複合構造である場合BEGINを用いる.END文
(4)複合構造は条件,ループなどの制御文を用いることができる.
パラメータタイプの説明
MySQLストアド・プロシージャのパラメータはストアド・プロシージャの定義に用いられ、3種類のパラメータ・タイプがあり、IN,OUT,INOUT
IN入力パラメータ:このパラメータの値は必ずストレージプロセスを呼び出す時に指定しなければならないことを表して、ストレージプロセスの中でこのパラメータの値を修正して返すことができなくて、デフォルト値です
OUT出力パラメータ:この値は記憶プロセス内部で変更され、戻ることができる
INOUT入出力パラメータ:呼び出し時に指定し、変更および返却可能
呼び出し:
CALL sp_name([parameter[,...]])
CALL sp_name[()]
ストアド・プロシージャの変更//プロシージャを変更できませんプロシージャを変更するにはストアド・プロシージャを削除し、再作成します
ALTER PROCEDURE sp_name [characteristic]
COMMENT 'string'
|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY{DEFINER|INVOKER}
ストアド・プロシージャの削除
DROP PROCEDURE [IF EXISTS] sp_name;
例:
無パラメトリックストレージ・プロシージャの作成
CREATE PROCEDURE sp1() SELECT VERSION();
INTタイプのパラメータを持つストアド・プロシージャの作成(入力IDに基づいてレコードを削除)
DELIMITER//
CREATE PROCEDURE removeUserByID(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;//パラメータ名はテーブルのフィールドと同じにしないほうがいいです.
END
//
DELIMITER ;
CALL removeUserById(3);
INとOUTタイプのパラメータを持つストアド・プロシージャの作成(データ・テーブルのid対応レコードを削除し、残りのレコード数を返す)
CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUTuserNums INT UNSIGNED)
-> BEGIN
-> DELETE FROM test WHEREid=p_id;
-> SELECT count(id) FROM testINTO userNums;//残りのidレコード数をパラメータuserNumsに入れるという意味です
-> END
->//
CALL removeUserAndReturnUserNums(27,@nums)
このうち27は削除するid=27が存在するレコードであり,@numsはユーザ変数であり,返される残りのレコード数を受信するために用いられる.
変数について
1.ユーザ変数:「@」で始まり、形式は「@変数名」である.ユーザー変数はmysqlクライアントにバインドされ、設定された変数は、現在のユーザーが使用しているクライアントにのみ有効です.
2.グローバル変数:定義時、set GLOBAL変数名またはset@@globalの2つの形式で現れる.変数名.すべてのクライアントで有効になります.グローバル変数を設定できるのはsuper権限のみです
3.セッション変数:接続されたクライアントにのみ有効です.
4.ローカル変数:beginからend文ブロックの間で動作します.declare文は、ローカル変数を定義するために使用されます.set文は、セッション変数とグローバル変数を含む異なるタイプの変数を設定します.
マルチoutタイプパラメータを作成するストレージ・プロシージャ(年齢に応じてユーザーを削除し、変更数と残りの数を返します)
DELIMITER//
CREATE PROCEDURE rmUserByAgeAndRtInfos(IN p_age SMALLINT UNSIGNED, OUTdelNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO delNums;
SELECT COUNT(id) FROM users INTO leftNums;
END
//
プロシージャの呼び出し:
CALL remove_user_return_infos(7,@削除記録数,@残り記録数);
変更の表示:
SELECT@削除記録数、@残り記録;
ストアド・プロシージャとカスタム関数の違い:
1、ストレージプロセス実現の機能は比較的に複雑で、関数の目標性が強い
2、ストアド・プロシージャは複数の値を返すことができ、関数には1つの戻り値しかない
3、ストレージプロセスは一般的に独立して実行され、関数はsql文の構成部分として現れることができる.
また、ストレージ・プロシージャもAPIインタフェースを介してプログラムを呼び出すよりも速い.
カスタム関数(user-defined function UDF)はmysqlを拡張する方法であり、組み込み関数と同じ使い方をします.
：1、 （ ， select version（））2 （ ）。 ， ， カスタム関数構文を作成するには、次の手順に従います.
CREATE FUNCTION function_name(parameter_nametype,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body
簡単に言えば、
CREATE FUNCTION関数名(パラメータリスト)
RETURNS戻り値タイプ
かんすうたい
関数ボディについて:
1.関数体は合法的なSQL文から構成される.
2.関数体は、単純なselect文またはinsert文であってもよい.
3.関数体が適合構造であればbegin...endを使用する.
4.複合構造は宣言、循環、制御構造を含むことができる.
カスタム関数でローカル変数構文を定義するには、次の手順に従います.
DECLARE 1[, 2,... ] [DEFAULT ];変数に構文を割り当てます.
SET parameter_name = value[parameter_name = value...];カスタム関数を削除するには
DROP FUNCTION function_name;
カスタム関数構文を呼び出します.
SELECT function_name(parameter_value,...)
例:
CREATE FUNCTION f1() RETURNS VARCHAR(30)RETURN DATE_FORMAT(NOW(),'%Y %m %d %h :%i :%s '); ：CREATE FUNCTION f2（num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED）RETURNS FLOAT(10，2) UNSIGNEDRETURN(num1+num2)/2; ：DELEMITER Eg：DELIMITER // /* ”;” ”//“ */ ， BEGIN...END ； ， DELIMITER ， ’;’ ， Eg：DELIMITER //CREATE FUNCTION ADD_USER(username VARCHAR(20))RETURNS INT UNSIGNEDBEGININSERT user(username) VALUES(username);RETURN LAST_INSERT_ID();END// /* ”//“ */二、Mysqlストレージプロセス
上記の過程において,構文解析とコンパイルの段階を省略すれば,効率を向上させることができる.そのため、ストレージ・プロシージャを使用してこの問題を解決します.
ストアド・プロシージャ:SQL文と制御文のプリコンパイル・セットで、1つの名前で格納され、1つのユニットとして処理されます(類似関数).ストレージ・プロシージャはデータベースに格納され、アプリケーション・コールによって実行され、ユーザーが変数を宣言し、プロセス制御を行うことができます.ストレージ・プロシージャは、入力タイプのパラメータと出力タイプのパラメータを受け入れることができ、複数の戻り値が存在することができる.最初の呼び出し時にのみ文法解析とコンパイルを行い,以降の呼び出しは直接コンパイルを呼び出した結果,効率が大幅に向上した.
メリット:
1.SQL文の機能と柔軟性を強化する:制御文によってプロセスを制御と判断できる
2、より速い実行速度を実現する:クライアントが初めてストレージプロセスを呼び出す時、MySQLエンジンはそれに対して文法分析、コンパイルなどの操作を行い、それからコンパイル結果をメモリに保存するので、初めて以前の効率と同じであるが、後で直接メモリのコンパイル結果を呼び出し、効率が向上する
3、ネットワークトラフィックを減らす:例えば記録を削除し、DELETE FROM xx WHEREを入力するつもりだった...;転送する文字が多い場合は、ストレージ・プロシージャと書くと、ストレージ・プロシージャの名前と対応するパラメータを呼び出すだけでよいため、転送する文字の数が少ないため、ネットワーク・トラフィックが減少します.
ストアド・プロシージャ構文の作成:
create procedure sp_name(inプロシージャパラメータ、outプロシージャパラメータ、inoutプロシージャパラメータ...)
begin
/*これはプロシージャです*/
End
プロシージャボディについて
(1)プロセス体は合法的なSQL文から構成される.
(2)プロシージャボディは任意のSQL文であってもよい.テーブルを削除し、接続しますが、データテーブルは作成できません.
(3)プロセス体が複合構造である場合BEGINを用いる.END文
(4)複合構造は条件,ループなどの制御文を用いることができる.
パラメータタイプの説明
MySQLストアド・プロシージャのパラメータはストアド・プロシージャの定義に用いられ、3種類のパラメータ・タイプがあり、IN,OUT,INOUT
IN入力パラメータ:このパラメータの値は必ずストレージプロセスを呼び出す時に指定しなければならないことを表して、ストレージプロセスの中でこのパラメータの値を修正して返すことができなくて、デフォルト値です
OUT出力パラメータ:この値は記憶プロセス内部で変更され、戻ることができる
INOUT入出力パラメータ:呼び出し時に指定し、変更および返却可能
呼び出し:
CALL sp_name([parameter[,...]])
CALL sp_name[()]
ストアド・プロシージャの変更//プロシージャを変更できませんプロシージャを変更するにはストアド・プロシージャを削除し、再作成します
ALTER PROCEDURE sp_name [characteristic]
COMMENT 'string'
|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY{DEFINER|INVOKER}
ストアド・プロシージャの削除
DROP PROCEDURE [IF EXISTS] sp_name;
例:
無パラメトリックストレージ・プロシージャの作成
CREATE PROCEDURE sp1() SELECT VERSION();
INTタイプのパラメータを持つストアド・プロシージャの作成(入力IDに基づいてレコードを削除)
DELIMITER//
CREATE PROCEDURE removeUserByID(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;//パラメータ名はテーブルのフィールドと同じにしないほうがいいです.
END
//
DELIMITER ;
CALL removeUserById(3);
INとOUTタイプのパラメータを持つストアド・プロシージャの作成(データ・テーブルのid対応レコードを削除し、残りのレコード数を返す)
CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUTuserNums INT UNSIGNED)
-> BEGIN
-> DELETE FROM test WHEREid=p_id;
-> SELECT count(id) FROM testINTO userNums;//残りのidレコード数をパラメータuserNumsに入れるという意味です
-> END
->//
CALL removeUserAndReturnUserNums(27,@nums)
このうち27は削除するid=27が存在するレコードであり,@numsはユーザ変数であり,返される残りのレコード数を受信するために用いられる.
変数について
1.ユーザ変数:「@」で始まり、形式は「@変数名」である.ユーザー変数はmysqlクライアントにバインドされ、設定された変数は、現在のユーザーが使用しているクライアントにのみ有効です.
2.グローバル変数:定義時、set GLOBAL変数名またはset@@globalの2つの形式で現れる.変数名.すべてのクライアントで有効になります.グローバル変数を設定できるのはsuper権限のみです
3.セッション変数:接続されたクライアントにのみ有効です.
4.ローカル変数:beginからend文ブロックの間で動作します.declare文は、ローカル変数を定義するために使用されます.set文は、セッション変数とグローバル変数を含む異なるタイプの変数を設定します.
マルチoutタイプパラメータを作成するストレージ・プロシージャ(年齢に応じてユーザーを削除し、変更数と残りの数を返します)
DELIMITER//
CREATE PROCEDURE rmUserByAgeAndRtInfos(IN p_age SMALLINT UNSIGNED, OUTdelNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO delNums;
SELECT COUNT(id) FROM users INTO leftNums;
END
//
プロシージャの呼び出し:
CALL remove_user_return_infos(7,@削除記録数,@残り記録数);
変更の表示:
SELECT@削除記録数、@残り記録;
ストアド・プロシージャとカスタム関数の違い:
1、ストレージプロセス実現の機能は比較的に複雑で、関数の目標性が強い
2、ストアド・プロシージャは複数の値を返すことができ、関数には1つの戻り値しかない
3、ストレージプロセスは一般的に独立して実行され、関数はsql文の構成部分として現れることができる.
また、ストレージ・プロシージャもAPIインタフェースを介してプログラムを呼び出すよりも速い.