mysqlストレージプロセスと関数

10080 ワード
mysql

ストアド・プロシージャと関数とは
ストレージ・プロシージャと関数は、事前にコンパイルされてデータベースに格納されたSQL文の集合であり、ストレージ・プロシージャと関数を呼び出すことで、アプリケーション開発者の多くの作業を簡素化し、データベースとアプリケーション・サーバでのデータの転送を低減することができ、データ処理の効率化に役立ちます.
ストアド・プロシージャと関数の違いは、関数には戻り値が必要ですが、ストアド・プロシージャにはありません.ストアド・プロシージャのパラメータはIN、OUT、INOUTタイプを使用できますが、関数のパラメータはINタイプのみです.他のタイプのデータベースからMySQLに移行する関数がある場合は、関数をストレージ・プロシージャに変更する必要がある可能性があります.
ストアド・プロシージャと関数の関連アクション
ストレージ・プロシージャまたは関数に関連する操作を行う場合は、まずユーザーが関連する権限を持っているかどうかを確認する必要があります.たとえば、ストレージ・プロシージャまたは関数の作成にはCREATE ROUTINE権限が必要です.ストレージ・プロシージャまたは関数の変更または削除にはALTER ROUTINE権限が必要であり、ストレージ・プロシージャまたは関数の実行にはEXECUTINE権限が必要です.
 
ストアド・プロシージャまたは関数の作成または変更
CREATE PROCEDURE sp_name ([proc_parameter[,....]])
    [characteristic...] routine_body


CREATE 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:
    LANQUAGE SQL
    | [NOT] DETERMINISTIC 
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER}
    | COMMENT 'string'
    
    routine_body:
     Valid SQL procedure statement or statements
    

ALTER { PROCEDURE | FUNCTION } sp_name [characteristic...]

    characteristic:

      { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER}
    | COMMENT 'string'

呼び出しプロシージャの構文は次のとおりです.
CALL sp_name([parameter[,....]]);

MySQLのストアド・プロシージャおよび関数には、DDL文を含めることができます.また、ストアド・プロシージャおよび関数では、コミット(Commit、確認前の変更)またはロールバック(Rollback、放棄前の変更)を実行できますが、ストアド・プロシージャおよび関数では、LOAD DATA INFILE文は実行できません.さらに、ストアド・プロシージャおよび関数では、他のプロシージャまたは関数を呼び出すことができます.
次は新しいプロシージャproc_を作成します.adder: 
mysql > DELIMITER $$
mysql > CREATE PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
      > BEGIN
      > DECLARE c int;
      > if a is null then
      >    set a = 0; 
      > end if;
      > if b is null then
      >     set b = 0;
      > end if;
      > set sum  = a + b;
      > END $$
Query Ok, 0 rows affected (0.00 sec)
mysql >
mysql > DELIMITER ;

上のストレージプロセスは比較的簡単に加算されます.
通常、作成プロセスと関数を実行する前に、「DELIMITER$$」コマンドを使用して文の終端を「;」プロシージャと関数の「;MySQLで文の最後に解釈されてエラーが表示されることはありません.ストレージ・プロシージャまたは関数の作成が完了したら、【DELIMITER;】終了文字を【;】に戻します.
実行結果:
mysql> set @b=5;
Query OK, 0 rows affected (0.00 sec)

mysql>  call proc_adder(2,@b,@s);
Query OK, 0 rows affected (0.00 sec)

mysql> select @s as sum;
+------+
| sum  |
+------+
|    7 |
+------+
1 row in set (0.00 sec)

mysql>

ストレージ・プロシージャの呼び出しはSQL文の直接実行と同じ効果があることがわかりますが、格納された利点は、処理ロジックがデータベース側にカプセル化されていることです.呼び出し値は中間の処理ロジックを理解する必要はありません.処理ロジックが変化すると、ストレージ・プロシージャを変更するだけで、呼び出し者のプログラムにはまったく影響しません.
また、ビューの作成構文とは少し異なり、ストレージ・プロシージャと関数のCREATE構文では、ストレージ・プロシージャと関数をCRATE OR REPLACEで変更することはサポートされておらず、既存のストレージ・プロシージャと関数を変更する必要がある場合はALTER構文を実行する必要があります.
characteristicフィーチャー値の部分を簡単に説明します.
  • LANAUAGE SQL:次の手順を説明するbodyはSQL言語で記述されています.これはシステムのデフォルトで、今後MySQLがサポートするSQL以外の言語でサポートされるストレージプロセスのために準備されています.
  • [NOT]DETERMINISTIC:DETERMINISTICで決定された、すなわち入力ごとに同じプログラムが出力され、NOT DETERMINISTICでは不確定であり、デフォルトでは非確定であり、現在この特徴値はまだ最適化プログラムで使用されていない.
  • {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIESSQL DATA}:これらの特徴値はサブルーチン使用データの内在情報を提供し、これらの特徴値は現在サーバに提供されているだけで、これらの特徴値に基づいてプロセスの実際の使用データを制約していない場合.CONTAINS SQL:サブルーチンにデータの読み書きが含まれている文を表し、NO SQL:サブルーチンにSQL文が含まれていないことを表し、READS SQL DATA:サブルーチンにデータの読み書きが含まれている文を表し、MODIFIES SQL DATA:サブルーチンにデータの書き込みが含まれている文を表し、これらの特徴値が明確に与えられていない場合、デフォルトで使用される値は:CONTAINS SQLである.
  • SQL SECURITY{DEFINER|INVOKER}:サブルーチンがサブルーチンを作成するライセンスで実行されるか、呼び出されたユーザのライセンスで実行されるかを指定できます.デフォルト値はDEFINERです.
  • COMMENT'string':プロシージャまたは関数のコメント情報を格納します.

    • ストアド・プロシージャまたは関数の削除
    一度に1つのストアド・プロシージャまたは関数しか削除できません.ストアド・プロシージャまたは関数を削除するには、そのプロシージャまたは関数のALTER ROUTINE権限が必要です.具体的な構文は次のとおりです.
    DROP { PROCEDURE | FOUNCTION }{IF EXISTS} sp_name;

    ストアド・プロシージャまたは関数の表示
    show { PROCEDURE | FOUNCTION } STATUS [like 'pattern'];

    ストアド・プロシージャの表示proc_adderの情報:
    
mysql> SHOW PROCEDURE STATUS LIKE 'proc_adder'\G;
*************************** 1. row ***************************
                  Db: daicooper
                Name: proc_adder
                Type: PROCEDURE
             Definer: [email protected]
            Modified: 2018-12-06 14:07:52
             Created: 2018-12-06 14:07:52
       Security_type: DEFINER
             Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

    ストアド・プロシージャまたは関数の定義の表示
    show CREATE { PROCEDURE | FOUNCTION } sp_name;

    ストアド・プロシージャの表示proc_adderの定義:
    
mysql> SHOW PROCEDURE STATUS LIKE 'proc_adder'\G;
*************************** 1. row ***************************
                  Db: daicooper
                Name: proc_adder
                Type: PROCEDURE
             Definer: [email protected]
            Modified: 2018-12-06 14:07:52
             Created: 2018-12-06 14:07:52
       Security_type: DEFINER
             Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

    informationで_schema.routinesストレージ・プロシージャと関数の情報の理解
    ストアド・プロシージャの表示proc_adderの定義:
    mysql> select * from information_schema.routines where ROUTINE_NAME = 'proc_adder'\G;
*************************** 1. row ***************************
           SPECIFIC_NAME: proc_adder
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: daicooper
            ROUTINE_NAME: proc_adder
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
    #Routine body goes here...

    DECLARE c int;
    if a is null then set a = 0; 
    end if;
  
    if b is null then set b = 0;
    end if;

    set sum  = a + b;
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2018-12-06 14:07:52
            LAST_ALTERED: 2018-12-06 14:07:52
                SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: [email protected]
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_unicode_ci
      DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

    変数の使用
    ストレージ・プロシージャと関数では変数を使用できます.変数は大文字と小文字を区別しません.
    1:変数の定義
    DECLAREによって1つの局部変数を定義することができて、量の作用の範文を変えるのはBEGINだけです....ENDブロックでは、ネストされたブロックで使用できます.
    変数の定義は複合文の先頭に書かなければならず、他の文の前に同じタイプの変数を一度に複数宣言することができます.必要に応じて、DEFAULTを使用してデフォルト値を割り当てることができます.
    変数を定義する構文は次のとおりです.
    DELARE var_name [,....] type [DEFAULT value]

    たとえば、last_という名前のDATEタイプの変数を定義します.month_start 
    DELARE last_month_start DATE;

    1:変数の割り当て
    変数は、直接値を割り当てるか、クエリーで値を割り当てることができます.直接付与はSETを使用し、定数または式を付与できます.構文は次のとおりです.
    set last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);

    また、クエリーを使用して結果を変数に割り当てることもできます.これにより、クエリーが返す結果は1行のみでなければなりません.具体的な文法は以下の通りです.
    select col_name [,....]  INTO var_name [,....] table_expr

    クエリー結果からlast_に値を割り当てるmonth_start : 
    select DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) INTO last_month_start from DUAL

    プロセス制御
    IF,CASE,LOOP,LEAVE,ITERATE,REPEAT,WHILE文を用いてフロー制御を行うことができる.
    1:IF文
    if条件判断を実現し、異なる条件を満たして異なる文リストを実行し、具体的な文法:
    IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ....
    [ELSE statement_list]
END IF

    2:CASE文
    CASE case_value 
    WHEN when_value THEN statement_list 
    [ WHEN when_value THEN statement_list ]...
    [ ELSE  statement_list]
END CASE 

  

CASE 
    WHEN search_condition THEN  statement_list
    [ WHEN search_condition THEN  statement_list ]...
    [ ELSE  statement_list]
END CASE

    たとえば、プロシージャproc_Caseは、入力したパラメータ値が0か1かotherかを判断します.
    CREATE PROCEDURE `proc_case`(IN type int)
BEGIN
   #Routine body goes here...
   DECLARE c varchar(500);
   CASE type
   WHEN 0 THEN
       set c = 'param is 0';
   WHEN 1 THEN
       set c = 'param is 1';
   ELSE
       set c = 'param is others, not 0 or 1';
   END CASE;
   select c;
END

    3:LOOP文
    LOOP文は簡単な循環を実現し、循環を終了する条件は他の文定義を使用する必要があり、通常はLEAVE文を使用して実現することができ、具体的な文法:
    [begin_label:] LOOP
    statement_list
END LOOP [end_label]

    statementにいなければlistにループを終了する文を追加すると、LOOP文は簡単なデッドループを実現するために使用できます.
    4:LEAVE文
    通常BEGINと...ENDまたはループを一緒に使用します.
    次に、LOOPとLEAVEを併用した簡単な例を示します.100回繰り返してtestテーブルにレコードを挿入し、100個のレコードを挿入した後、ループを終了します.
    CRATE PROCEDURE proc_Loop(OUT sum INT)
    BEGIN
        set @x = 0;
        ins:LOOP
            set @x = @x +1;
            IF  @x = 100 THEN 
                LEAVE  ins;
            END IF;
            set sum = sum + @x;
        END LOOP;
        select sum;
    END

    実行結果
    mysql> call proc_Loop(@sum);
+------+
| sum  |
+------+
| 4950 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

     
     
     
     
     
     
     
     
     
    JavaScriptを使用して言葉を騙す方法
    ビルのビデオチャットアプリケーションをVuejsと