Mysqlのストレージ・プロシージャと関数
10753 ワード
Mysqlのストレージ・プロシージャと関数
ストレージ・プロシージャは、1つまたは複数のSQL文の集合であり、バッチ・ファイルとして表示されますが、バッチのみではありません.
ストレージプログラムは、1、ストレージプロセス2、関数に分けられます.
Call文を使用してストアド・プロシージャを呼び出し、出力変数のみで値を返します.
一、ストレージプロセスの作成
構文:
ストレージパラメータのリストを次のように指定します.
[IN | OUT | INOUT] param_name type
ただしINは入力パラメータ、OUTは出力パラメータ、INOUTは入力も出力も可能
param_nameはパラメータ名を表します
typeは、Mysqlデータベース内の任意のタイプのパラメータタイプを表します.
characteristicsは、ストレージ・プロシージャのプロパティを指定します.次の値があります.
LANGUAGE SQL:説明routine_body部分はSQL文で構成されており、現在システムでサポートされている言語はSQLであり、SQLはLANGUAGE特性の唯一の値である.
[NOT]DETERMINISTIC:ストレージ・プロシージャの実行結果が正しいかどうかを示します.
1.DETERMINISTICは結果が正しいことを示します.ストレージ・プロシージャを実行するたびに、同じ入力で同じ出力が得られます.
2.NOT DETERMINISTICは結果が不確定であることを示し,同じ入力で異なる出力が得られる可能性がある.いずれかの値が指定されていない場合、デフォルトはNOT DETERMINISTICです.
{CONTAINS SQL|NO SQL|REDAS SQL DATA|MODIFIES SQL DATA}:サブルーチンがSQL文を使用する制限を示します.
1. CONTAINS SQL:サブルーチンにSQL文が含まれているが、読み書きデータが含まれていないことを示す文.
2.NO SQL:サブルーチンにSQL文が含まれていないことを示します.
3.REDAS SQL DATA:サブルーチンにデータが含まれていることを示す文.
4.MODIFIESSQL DATA:サブルーチンに書き込みデータが含まれていることを示す文.デフォルトはCONTAINS SQLです.
SQL SECURITY{DEFINER|INVOKER}:実行する権限がある人を示します.
1. DEFINERは、定義者のみが実行できることを示します.
2.INVOKERは、権限を持つ呼び出し元が実行できることを示します.デフォルトでは、システムはDEFINERとして指定されています.
COMMENT‘string’:ストレージ・プロシージャまたは関数を記述するために使用できるコメント情報.
routine_bodyはSQLコードの内容です.通常はbegin......endでSQLコードの開始と終了を表します.
ストレージ・プロシージャの作成は簡単なことではありません.ストレージ・プロシージャには複雑なSQL文が必要であり、ストレージ・プロシージャを作成する権限が必要になる可能性があります.しかし、ストレージ・プロシージャを使用すると、操作が簡素化され、冗長な操作手順が削減されるとともに、操作中のミスを低減し、効率を向上させることができるため、ストレージ・プロシージャは非常に有用であり、できるだけ使用することを学ばなければならない.
例1:
例2:
例3:
************************
mysqlストアドプロシージャのパラメータはストアドプロシージャの定義に用いられ,3種類のパラメータタイプ,IN,OUT,INOUTがある.
Create procedure|function([[IN|OUT|INOUT]パラメータ名データクラス...])
IN入力パラメータ
このパラメータの値は、ストアド・プロシージャを呼び出すときに指定する必要があります.ストアド・プロシージャでパラメータを変更した値は、デフォルト値として返されません.
OUT出力パラメータ
この値は、ストレージ・プロシージャ内で変更され、戻ることができます.
INOUT入出力パラメータ
呼び出し時に指定し、変更および戻すことができます.
INパラメータ例:
実行結果:
OUTパラメータ例
実行結果:
INOUTパラメータ例:
実行結果:
****************************
二、特定異常
MySQLでは、特定の例外には特定の処理が必要です.これらの例外は、エラー、およびサブルーチンの一般的なプロセス制御に関連します.定義例外は、プログラムの実行中に発生する問題であり、例外処理は、問題が発生した場合に取るべき処理方法を定義し、記憶プロセスまたは関数がエラーが発生した場合または警告が発生した場合に実行を継続できることを保証する.
1異常定義
1.1構文
1.2説明
1.3例
ERROR 1148(42000)の定義エラー.名前はcommand_not_allowed.次の2つの方法があります.
2カスタム例外処理
2.1例外処理構文
2.2パラメータの説明
2.3異常捕獲方法
三、関数
関数の役割:コードの多重化率の向上
関数は、いくつかの機能を実現するために関数のメソッドを呼び出すことができます.
Now()を使用して空のパラメータ関数を実現します.
------------------------------------------------------------------------------------
関数は空のパラメータ関数とパラメータ関数に分けられます
注意:関数にはreturnsで説明する戻り値タイプが必要です.
returnsの後ろには関数体がついています
関数体が単一のものしかない場合は直接説明します
関数体returnsの後ろにbeginが始まる関数体が複数ある場合はend終了と書きます
endの前に必ず戻り値を決定してください
-----------------------------------------------------------------------------------------------
パラメータ関数を作成するには、次の手順に従います.
stuテーブルにユーザーを追加するマルチファンクションボディパラメータ関数を作成します.
ストレージ・プロシージャは、1つまたは複数のSQL文の集合であり、バッチ・ファイルとして表示されますが、バッチのみではありません.
ストレージプログラムは、1、ストレージプロセス2、関数に分けられます.
Call文を使用してストアド・プロシージャを呼び出し、出力変数のみで値を返します.
一、ストレージプロセスの作成
構文:
create procedure sp_name(proc_parameter)
[characteristics……] routine_body
sp_name， ：proc_parameterストレージパラメータのリストを次のように指定します.
[IN | OUT | INOUT] param_name type
ただしINは入力パラメータ、OUTは出力パラメータ、INOUTは入力も出力も可能
param_nameはパラメータ名を表します
typeは、Mysqlデータベース内の任意のタイプのパラメータタイプを表します.
characteristicsは、ストレージ・プロシージャのプロパティを指定します.次の値があります.
LANGUAGE SQL:説明routine_body部分はSQL文で構成されており、現在システムでサポートされている言語はSQLであり、SQLはLANGUAGE特性の唯一の値である.
[NOT]DETERMINISTIC:ストレージ・プロシージャの実行結果が正しいかどうかを示します.
1.DETERMINISTICは結果が正しいことを示します.ストレージ・プロシージャを実行するたびに、同じ入力で同じ出力が得られます.
2.NOT DETERMINISTICは結果が不確定であることを示し,同じ入力で異なる出力が得られる可能性がある.いずれかの値が指定されていない場合、デフォルトはNOT DETERMINISTICです.
{CONTAINS SQL|NO SQL|REDAS SQL DATA|MODIFIES SQL DATA}:サブルーチンがSQL文を使用する制限を示します.
1. CONTAINS SQL:サブルーチンにSQL文が含まれているが、読み書きデータが含まれていないことを示す文.
2.NO SQL:サブルーチンにSQL文が含まれていないことを示します.
3.REDAS SQL DATA:サブルーチンにデータが含まれていることを示す文.
4.MODIFIESSQL DATA:サブルーチンに書き込みデータが含まれていることを示す文.デフォルトはCONTAINS SQLです.
SQL SECURITY{DEFINER|INVOKER}:実行する権限がある人を示します.
1. DEFINERは、定義者のみが実行できることを示します.
2.INVOKERは、権限を持つ呼び出し元が実行できることを示します.デフォルトでは、システムはDEFINERとして指定されています.
COMMENT‘string’:ストレージ・プロシージャまたは関数を記述するために使用できるコメント情報.
routine_bodyはSQLコードの内容です.通常はbegin......endでSQLコードの開始と終了を表します.
ストレージ・プロシージャの作成は簡単なことではありません.ストレージ・プロシージャには複雑なSQL文が必要であり、ストレージ・プロシージャを作成する権限が必要になる可能性があります.しかし、ストレージ・プロシージャを使用すると、操作が簡素化され、冗長な操作手順が削減されるとともに、操作中のミスを低減し、効率を向上させることができるため、ストレージ・プロシージャは非常に有用であり、できるだけ使用することを学ばなければならない.
例1:
mysql> delimiter // # SQL //， ， （‘\’） ， Mysql 。
mysql> create procedure p1()
-> begin
-> select * from t;
-> end //
mysql> delimiter ;
mysql> show procedure status \G #
mysql> call p1 # 例2:
mysql> delimiter //
mysql> create procedure p2(n int)
-> begin
-> select * from t where id = n;
-> end //
mysql> delimiter ;
mysql> show procedure status \G
mysql> call p2(1) # 例3:
mysql> create database db_proc;
mysql> use db_proc
mysql> CREATE TABLE `proc_test` (
-> `id` tinyint(4) NOT NULL AUTO_INCREMENT,
-> `username` varchar(20) NOT NULL,
-> `password` varchar(20) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;
mysql> delimiter //
mysql> create procedure mytest(in name varchar(20),in pwd varchar(20))
-> begin
-> insert into proc_test(username,password) values(name,pwd);
-> end //
mysql> delimiter ;
mysql> call mytest('lxq','password') ;
mysql> select * from proc_test; # ************************
mysqlストアドプロシージャのパラメータはストアドプロシージャの定義に用いられ,3種類のパラメータタイプ,IN,OUT,INOUTがある.
Create procedure|function([[IN|OUT|INOUT]パラメータ名データクラス...])
IN入力パラメータ
このパラメータの値は、ストアド・プロシージャを呼び出すときに指定する必要があります.ストアド・プロシージャでパラメータを変更した値は、デフォルト値として返されません.
OUT出力パラメータ
この値は、ストレージ・プロシージャ内で変更され、戻ることができます.
INOUT入出力パラメータ
呼び出し時に指定し、変更および戻すことができます.
INパラメータ例:
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
-> BEGIN
-> SELECT p_in; #
-> SET p_in=2; #
-> select p_in; #
-> END //
mysql> DELIMITER ;実行結果:
mysql> set @p_in=1;
mysql> call sp_demo_in_parameter(@p_in);
mysql> select @p_in;
，p_in ， @p_id OUTパラメータ例
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
-> BEGIN
-> SELECT p_out; #
-> SET p_out=2; #
-> SELECT p_out; #
-> END //
mysql> DELIMITER ;実行結果:
mysql> SET @p_out=1;
mysql> CALL sp_demo_out_parameter(@p_out);
mysql> SELECT @p_out;
p_out , @p_out INOUTパラメータ例:
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
-> BEGIN
-> SELECT p_inout;
-> SET p_inout=2;
-> SELECT p_inout;
-> END;
mysql> DELIMITER ;実行結果:
set @p_inout=1;
call sp_demo_inout_parameter(@p_inout);
select @p_inout;****************************
二、特定異常
MySQLでは、特定の例外には特定の処理が必要です.これらの例外は、エラー、およびサブルーチンの一般的なプロセス制御に関連します.定義例外は、プログラムの実行中に発生する問題であり、例外処理は、問題が発生した場合に取るべき処理方法を定義し、記憶プロセスまたは関数がエラーが発生した場合または警告が発生した場合に実行を継続できることを保証する.
1異常定義
1.1構文
DECLARE condition_name CONDITION FOR [condition_type];1.2説明
condition_name ；
condition_type ，condition_type SQLSTATE [VALUE] sqlstate_value|mysql_error_code ：
sqlstate_value mysql_error_code MySQL ；
sqlstate_value 5 ；
mysql_error_code ；1.3例
ERROR 1148(42000)の定義エラー.名前はcommand_not_allowed.次の2つの方法があります.
# ： sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
# ： mysql_error_code
DECLARE command_not_allowed CONDITION FOR 1148;2カスタム例外処理
2.1例外処理構文
DECLARE handler_type HANDLER FOR condition_value [,...] sp_statement2.2パラメータの説明
handler_type: CONTINUE|EXIT|UNDO
handler_type ， 3 ；
CONTINUE ， ；
EXIT ；
UNDO ，MySQL ；
condition_value: SQLSTATE [VALUE] sqlstate_value| condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code
condition_value ；
SQLSTATE [VALUE] sqlstate_value 5 ；
condition_name DECLARE CONDITION ；
SQLWARNING 01 SQLSTATE ；
NOT FOUND 02 SQLSTATE ；
SQLEXCEPTION SQLWARNING NOT FOUND SQLSTATE ；
mysql_error_code ；2.3異常捕獲方法
： sqlstate_value
sqlstate_value 。 sqlstate_value "42S02"， CONTINUE ， "NO_SUCH_TABLE"
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';
： mysql_error_code
mysql_error_code 。 mysql_error_code 1146， CONTINUE ， "NO_SUCH_TABLE" ；
DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';
： ，
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';
： SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
： NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';
： SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR'; ：
mysql> create table test.t(s1 int,primary key(s1));
mysql> delimiter //
mysql> create procedure handlerdermo()
-> begin
-> declare CONTINUE HANDLER FOR SQLSTATE '23000' set @x2 = 1;
-> set @x = 1;
-> insert into test.t values (1);
-> set @x = 2;
-> insert into test.t values (1);
-> set @x = 3;
-> end //
mysql> delimiter ;
mysql> call handlerdermo();
mysql> select @x;
mysql> select * from test.t;三、関数
関数の役割:コードの多重化率の向上
関数は、いくつかの機能を実現するために関数のメソッドを呼び出すことができます.
Now()を使用して空のパラメータ関数を実現します.
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2018-08-16 18:19:09 |
+---------------------+
mysql> select date_format(now(),'%Y %m %d %H %i %s ');
+------------------------------------------------------+
| date_format(now(),'%Y %m %d %H %i %s ') |
+------------------------------------------------------+
| 2018 08 16 18 19 57 |
+------------------------------------------------------+------------------------------------------------------------------------------------
mysql> create function my_time() returns varchar(50)
-> return date_format(now(),'%Y-%m-%d %H-%i-%s');
Query OK, 0 rows affected (0.00 sec)
mysql> select my_time();
+---------------------+
| my_time() |
+---------------------+
| 2018-08-16 18-22-10 |
+---------------------+関数は空のパラメータ関数とパラメータ関数に分けられます
注意:関数にはreturnsで説明する戻り値タイプが必要です.
returnsの後ろには関数体がついています
関数体が単一のものしかない場合は直接説明します
関数体returnsの後ろにbeginが始まる関数体が複数ある場合はend終了と書きます
endの前に必ず戻り値を決定してください
-----------------------------------------------------------------------------------------------
パラメータ関数を作成するには、次の手順に従います.
mysql> CREATE FUNCTION cont_AVG(num1 int,num2 int) RETURNS decimal(8,2)
-> RETURN (num1+num2)/2;
Query OK, 0 rows affected (0.00 sec)
mysql> select cont_AVG(2,2);
+---------------+
| cont_AVG(2,2) |
+---------------+
| 2.00 |
+---------------+
1 row in set (0.00 sec)
mysql> select cont_AVG(3,2);
+---------------+
| cont_AVG(3,2) |
+---------------+
| 2.50 |
+---------------+stuテーブルにユーザーを追加するマルチファンクションボディパラメータ関数を作成します.
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> select * from stu;
Empty set (0.00 sec)mysql> delimiter //
mysql> create function adduse(u_id int unsigned,u_name varchar(10))
-> returns int unsigned
-> begin
-> insert stu values(u_id,u_name);
-> return last_insert_id();
-> end //
mysql> delimiter ;
mysql> select adduse(1,'zs');
+----------------+
| adduse(1,'zs') |
+----------------+
| 0 |
+----------------+
1 row in set (0.02 sec)
mysql> select adduse(2,'ls');
+----------------+
| adduse(2,'ls') |
+----------------+
| 0 |
+----------------+
1 row in set (0.01 sec)
mysql> select adduse(3,'ww');
+----------------+
| adduse(3,'ww') |
+----------------+
| 0 |
+----------------+
1 row in set (0.02 sec)
mysql> select * from stu;
+----+------+
| id | name |
+----+------+
| 1 | zs |
| 2 | ls |
| 3 | ww |
+----+------+
3 rows in set (0.00 sec)