MySQLカスタム関数とストアドプロシージャの詳細な説明

序文

この記事では主にMySQLのカスタム関数とストアドプロシージャに関する関連コンテンツを紹介し、皆さんの参考と学習のために共有します。詳しい紹介を見てみましょう。

1. 前提条件

MySQL データベースには user_info テーブルがあり、その構造とデータは次のとおりです。

mysql> desc user_info;
+-----------+----------+------+------+-------+-------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+-----------+----------+------+------+-------+-------+
| id | int(10) | NO | PRI | NULL | |
| 名前 | char(20) | NO | | NULL | |
| パスワード | char(40) | NO | | NULL | |
| 電子メール | char(20) | NO | | NULL | |
| 電話 | char(20) | NO | | NULL | |
| 役割 | char(10) | NO | | NULL | |
| 性別 | char(10) | NO | | NULL | |
| ステータス | int(10) | NO | | NULL | |
| createAt | 日時 | NO | | NULL | |
| exprAt | 日時 | NO | | NULL | |
| 有効日数 | int(10) | NO | | NULL | |
| delAt | 日時 | YES | | NULL | |
+-----------+----------+------+------+-------+-------+
12 行セット (0.10 秒)

mysql> user_info から * を選択します。
+----+--------------+-----------+-------------+------------+----------+---------+----------------------+----------------------+--------+-------+
| id | 名前 | パスワード | 電子メール | 電話 | 役割 | 性別 | ステータス | 作成日時 | 有効期限 | 有効期限 |
+----+--------------+-----------+-------------+------------+----------+---------+----------------------+----------------------+--------+-------+
| 1 | StephenWang7 | py123456 | [email protected] | 15103887470 | 管理者 | 男性 | 200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 | 30 | NULL |
| 2 | StephenWang8 | 123456 | [email protected] | 15103887470 | 視聴者 | 男性 | 200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 | 30 | NULL |
+----+--------------+-----------+-------------+------------+----------+---------+----------------------+----------------------+--------+-------+
セット内の 2 行 (0.00 秒)

2. カスタム関数

関数: 特定の機能を実行できる SQL ステートメントのセット。 MySQL は、特定のビジネス機能を完了するためのカスタム関数をサポートしています。

ユーザー定義関数 (UDF) を作成するための構文は次のとおりです。

関数 <関数名> を作成します ([パラメータ 1] [タイプ 1]、[パラメータ N] [タイプ N])
<type> を返します
戻る
<関数本体>

UDF を呼び出すための構文は次のとおりです。

select <関数名> ([パラメータ])

パラメータなしのUDFの作成

例1: user_infoテーブル内のレコード数を照会する

#関数の定義 mysql> create function user_info_count()
 -> int(10) を返します
 -> 戻る
 -> (user_info から count(*) を選択);

user_info_count()関数を呼び出す

mysql> user_info_count() を選択します。
+-------------------+
| ユーザー情報カウント() |
+-------------------+
| 2 |
+-------------------+
セット内の 1 行 (0.00 秒)

パラメータ付きUDFの作成

例 2: ID に基づいてユーザー名を照会します。

#関数の定義 mysql> create function queryNameById(uid int(10))
 -> char(20) を返します
 -> 戻る
 -> (id=uid の場合、user_info から名前を選択);
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

ID 1 のユーザー名を照会する関数を呼び出します。

mysql> queryNameById(1) を選択します。
+------------------+
| クエリ名ID(1) |
+------------------+
翻訳:
+------------------+
セット内の 1 行 (0.00 秒)

UDFを表示

システム内のすべてのUDFをクエリする

関数のステータスを表示します。

指定されたUDFをクエリする

#
作成関数を表示する関数名;
mysql> 関数 queryNameById を表示します。
エラー 1064 (42000): SQL 構文にエラーがあります。1 行目の 'queryNameById' 付近で使用する正しい構文については、MySQL サーバーのバージョンに対応するマニュアルを確認してください。
mysql> 関数 queryNameById() を表示します。
エラー 1064 (42000): SQL 構文にエラーがあります。1 行目の 'queryNameById()' 付近で使用する正しい構文については、MySQL サーバーのバージョンに対応するマニュアルを確認してください。
mysql> 作成関数 queryNameById() を表示します。
エラー 1064 (42000): SQL 構文にエラーがあります。1 行目の '()' 付近で使用する正しい構文については、MySQL サーバーのバージョンに対応するマニュアルを確認してください。
mysql> show create function queryNameById;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+----------------------+
| 関数 | sql_mode | 関数の作成 | character_set_client | collation_connection | データベース照合 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+----------------------+
| queryNameById | ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `queryNameById`(uid int(10)) RETURNS char(20) CHARSET latin1
戻り値 (id=uid の場合、user_info から名前を選択) | utf8 | utf8_general_ci | latin1_swedish_ci |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+----------------------+
セット内の1行（0.00秒）

UDFの変更

関数の内容を変更する場合は、関数を削除してから再作成してください。

UDF の削除

UDF を削除するための構文は次のとおりです。

関数 <関数名> を削除します。

例 3: queryNameId 関数を削除し、再度呼び出して現象を観察します。

mysql> 関数 queryNameById を削除します。
クエリは正常、影響を受けた行は 0 行 (0.45 秒)

mysql> queryNameById(1) を選択します。
エラー 1305 (42000): FUNCTION rms.queryNameById が存在しません
マイSQL>

3. ストアドプロシージャ

ストアド関数はカスタム関数に似ており、特定の機能を実行する SQL ステートメントのセットでもあります。複雑なSQLや頻繁に呼び出されるSQLを事前に記述し、名前を付けます。使いたいときに呼び出すだけ。

ストアドプロシージャを定義するための構文は次のとおりです。

CREATE PROCEDURE <プロシージャ名> ( [プロシージャパラメータ[,…] ] ) <プロシージャ本体>
[プロシージャパラメータ[,…]] 形式
[ IN | OUT | INOUT ] <パラメータ名> <タイプ>
#構文定義: http://c.biancheng.net/view/2593.html

パラメータなしのストアドプロシージャの作成

例 4: ユーザー名を照会します。

mysql> 区切り文字 //
mysql> プロシージャ queryName() を作成します
 -> 開始
 -> user_info から名前を選択します。
 -> 終了 //

DELIMITER コマンドに関しては、MySQL コマンドを終了する文字を変更します。デフォルトの終了コマンド文字はセミコロンです。ストアドプロシージャに複数のステートメントが含まれている場合、最初のセミコロンはストアドプロシージャの終了の記号として使用されます。これは予想どおりではないため、デフォルトのコマンド終了文字を変更する必要があります。 DELIMITER // は終了コマンド文字を // に変更します。ストアドプロシージャを呼び出すコマンドは、call stored procedure name です。

#この時点でのコマンドの終了記号は // ではありません;
mysql> queryName() を呼び出す //
+--------------+
| 名前 |
+--------------+
翻訳:
翻訳:
+--------------+
セット内の 2 行 (0.00 秒)

クエリは正常、影響を受けた行は 0 行 (0.00 秒)

パラメータ付きのストアドプロシージャの作成

例 5: ID に基づいて名前をクエリします。

mysql> プロシージャ queryNameById を作成する
 -> (uid int(15)内)
 -> 開始
 -> user_info から id=uid となる名前を選択します。
 -> 終了
 -> //
クエリは正常、影響を受けた行は 0 行 (0.03 秒)

ストアドプロシージャqueryNameByIdを呼び出す

mysql> queryNameById(1); を呼び出します。
 -> //
+--------------+
| 名前 |
+--------------+
翻訳:
+--------------+
セット内の1行（0.03秒）

クエリは正常、影響を受けた行は 0 行 (0.04 秒)

ストアドプロシージャを変更する

ストアドプロシージャの内容を作成する場合は、ストアドプロシージャを削除してから再作成することができます。

ストアドプロシージャを表示する

show create procedure <プロシージャ名>

mysql> show create procedure queryNameById; -> // +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | queryNameById | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `queryNameById`(In uid int(15)) begin select name from user_info where id=uid; end | utf8 | utf8_general_ci | latin1_swedish_ci | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.04 sec)

ストアドプロシージャの削除

プロシージャ <プロシージャ名> を削除します

ストアドプロシージャqueryNameByIdを削除します

mysql> プロシージャ queryNameById を削除します //
クエリは正常、影響を受けた行は 0 行 (0.02 秒)


mysql> queryNameById(1) を呼び出す //
エラー 1305 (42000): PROCEDURE rms.queryNameById が存在しません

4. まとめ

カスタム関数とストアドプロシージャはどちらも特定の機能を実行する SQL セットですが、それらの違いは何でしょうか。

a. 異なる呼び出し方法