MySQL
ストアドルーチン（手続きおよび関数）

パラメーター

備考

関数を作成する

次の（簡単な）関数例は、単に定数INT値12返します。

DELIMITER ||
CREATE FUNCTION functionname()
RETURNS INT
BEGIN
    RETURN 12;
END;
||
DELIMITER ;

最初の行は、区切り文字（ DELIMITER || ）をどのように変更するかを定義します;デフォルトのままにしておくと、関数が作成される前に設定する必要があります;その後最初の;関数本体の中にあるものはCREATE文の終わりとみなされますが、これは通常は何も望まれません。

CREATE FUNCTIONを実行した後、デリミタをデフォルトの値に戻す必要があります;上記の例の機能コード（ DELIMITER ; ）の後に表示されています。

この機能の実行は次のとおりです。

SELECT functionname();
+----------------+
| functionname() |
+----------------+
|             12 |
+----------------+

もう少し複雑な（しかしそれでもやっかいな）例は、パラメータをとり、それに定数を追加します：

DELIMITER $$
CREATE FUNCTION add_2 ( my_arg INT )
  RETURNS INT
BEGIN
  RETURN (my_arg + 2);
END;
$$
DELIMITER ;

SELECT add_2(12);
+-----------+
| add_2(12) |
+-----------+
|        14 |
+-----------+

DELIMITERディレクティブとは異なる引数を使用することに注意してください。 CREATE文の本文には現れない文字列を実際に使用することができますが、通常は\\ 、 ||ような英数字以外の2文字を使用します。または$$ 。

関数、プロシージャまたはトリガの作成または更新の前後にパラメータを常に変更することは、コマンドラインからクエリを実行するときに常に区切り文字を設定する必要がある一方、一部のGUIでは区切り文字を変更する必要がないため、よい方法です。

作成された準備を含むプロシージャの作成

DROP PROCEDURE if exists displayNext100WithName;
DELIMITER $$
CREATE PROCEDURE displayNext100WithName
(    nStart int,
    tblName varchar(100)
)
BEGIN
    DECLARE thesql varchar(500); -- holds the constructed sql string to execute

    -- expands the sizing of the output buffer to accomodate the output (Max value is at least 4GB)
    SET session group_concat_max_len = 4096; -- prevents group_concat from barfing with error 1160 or whatever it is

    SET @thesql=CONCAT("select group_concat(qid order by qid SEPARATOR '%3B') as nums ","from (    select qid from ");
    SET @thesql=CONCAT(@thesql,tblName," where qid>? order by qid limit 100 )xDerived");
    PREPARE stmt1 FROM @thesql; -- create a statement object from the construct sql string to execute
    SET @p1 = nStart; -- transfers parameter passed into a User Variable compatible with the below EXECUTE
    EXECUTE stmt1 USING @p1;
     
    DEALLOCATE PREPARE stmt1; -- deallocate the statement object when finished
END$$
DELIMITER ;

ストアドプロシージャを作成すると、多くのクライアントツールで必要なDELIMITERがラップされます。

呼び出し例：

call displayNext100WithName(1,"questions_mysql");

%3B （セミコロン）セパレータ付きサンプル出力：

IN、OUT、INOUTパラメータを含むストアドプロシージャ

DELIMITER $$
 
DROP PROCEDURE IF EXISTS sp_nested_loop$$
CREATE PROCEDURE sp_nested_loop(IN i INT, IN j INT, OUT x INT, OUT y INT, INOUT z INT)
BEGIN
    DECLARE a INTEGER DEFAULT 0;
    DECLARE b INTEGER DEFAULT 0;
    DECLARE c INTEGER DEFAULT 0;
    WHILE a < i DO
        WHILE b < j DO
            SET c = c + 1;
            SET b = b + 1;
        END WHILE;    
        SET a = a + 1;
        SET b = 0;        
    END WHILE;
    SET x = a, y = c;
    SET z = x + y + z;
END $$
DELIMITER ;

ストアドプロシージャを呼び出す（ CALL ）

SET @z = 30;
call sp_nested_loop(10, 20, @x, @y, @z);
SELECT @x, @y, @z;

結果：

+------+------+------+
|  @x  |  @y  |  @z  |
+------+------+------+
|  10  |  200 |  240 |
+------+------+------+

INパラメータは、値をプロシージャに渡します。プロシージャは値を変更する可能性がありますが、プロシージャが戻るときには変更は呼び出し側には表示されません。

OUTパラメータは、プロシージャからの値を呼び出し側に戻します。その初期値はプロシージャ内ではNULLであり、その値はプロシージャが戻るときに呼び出し側に表示されます。

INOUTパラメーターは、呼び出し側によって初期化され、プロシージャーによって変更され、プロシージャーによって行われた変更は、プロシージャーが戻ったときに呼び出し側に表示されます。

Ref： http : //dev.mysql.com/doc/refman/5.7/en/create-procedure.html

カーソル

カーソルを使用すると、クエリの結果を1行ごとに繰り返し処理できます。 DECLAREコマンドは、カーソルを初期化し、特定のSQLクエリに関連付けるために使用されます。

DECLARE student CURSOR FOR SELECT name FROM studend;

いくつかの種類の製品を販売しています。各タイプの商品がいくつあるかをカウントしたいと考えています。

私たちのデータ：

CREATE TABLE product
(
  id   INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(50)      NOT NULL,
  name VARCHAR(255)     NOT NULL

);
CREATE TABLE product_type
(
  name VARCHAR(50) NOT NULL PRIMARY KEY
);
CREATE TABLE product_type_count
(
  type  VARCHAR(50)      NOT NULL PRIMARY KEY,
  count INT(10) UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO product_type (name) VALUES
  ('dress'),
  ('food');

INSERT INTO product (type, name) VALUES
  ('dress', 'T-shirt'),
  ('dress', 'Trousers'),
  ('food', 'Apple'),
  ('food', 'Tomatoes'),
  ('food', 'Meat');

カーソルを使用してストアドプロシージャを使用して目標を達成することができます。

DELIMITER //
DROP PROCEDURE IF EXISTS product_count;
CREATE PROCEDURE product_count()
  BEGIN
    DECLARE p_type VARCHAR(255);
    DECLARE p_count INT(10) UNSIGNED;
    DECLARE done INT DEFAULT 0;
    DECLARE product CURSOR FOR
      SELECT
        type,
        COUNT(*)
      FROM product
      GROUP BY type;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    TRUNCATE product_type;

    OPEN product;
    
    REPEAT
      FETCH product
      INTO p_type, p_count;
      IF NOT done
      THEN
        INSERT INTO product_type_count
        SET
          type  = p_type,
          count = p_count;
      END IF;
    UNTIL done
    END REPEAT;
    
    CLOSE product;
  END //
DELIMITER ;

プロシージャを呼び出すことができます：

CALL product_count();

結果はproduct_type_countテーブルにあります：

type   | count
----------------
dress  |   2
food   |   3

これはCURSOR良い例ですが、プロシージャー全体をどのように置き換えることができるのかを確認してください

INSERT INTO product_type_count
        (type, count)
    SELECT type, COUNT(*)
        FROM product
        GROUP BY type;

これははるかに速く実行されます。

複数の結果セット

SELECTステートメントとは異なり、 Stored Procedureは複数の結果セットを返します。 Perl、PHPなどでCALLの結果を収集するために使用される異なるコードが必要です。

（ここや他の場所で特定のコードが必要です！）

関数を作成する

DELIMITER $$
CREATE
    DEFINER=`db_username`@`hostname_or_IP`
    FUNCTION `function_name`(optional_param data_type(length_if_applicable))
    RETURNS data_type
BEGIN
    /*
    SQL Statements goes here
    */
END$$
DELIMITER ;

RETURNS data_typeは任意のMySQLデータ型です。