きっと役に立つ！MySQL,MariaDB ストアドプロシージャ・ストアドファンクションテンプレート

MySQLおよびMariaDBにおいて、ストアドプロシージャを作成するときにいちいち、探しまわる時間がもったいないので、テンプレート的なものを用意しました、基本的、コンソールにコピペで行けるようにしておりますのでご自身の環境で作成する際のサンプルにご利用ください

[rakuten:murauchi-denki:72891916:detail]

ストアドファンクションと、ストアドプロシージャの違い

ストアドプロシージャ

• CALLで呼ぶ
• 複数の戻り値を返すことができる
• IN引数、OUT引数、INOUT引数を指定することができる

ストアドファンクション

• SELECT で呼び出すことができる
• 戻り値は、1つ
• 引数は INのみです

基本的に構文は、ストアドプロシージャとファンクションどちらも同じです。違うのは呼び方だけです。

例えば、myStored という名前のファンクションがあった場合、

• ストアドファンクション

SELECT myStored();

• ストアドプロシージャ

CALL myStored() ;

という記述になります。

また、コード（宣言文）の記述に関しては、以下のような違いがります。

• ストアドプロシージャ

CREATE PROCEDURE `testproc`(IN col1 int,INOUT col2,OUT result int)

• ストアドファンクション

CREATE FUNCTION sf_chk_tabs
(in_tabl_id BIGINT) RETURNS int 

通常系サンプル

通常系？っていう通常系って何か自分でもよくわかっていないですが・・とにかく上から下に処理していくストアドプロシージャのサンプルです 基本的にはSQLだらだらーって書いているだけですが、テンプレートとしては活用できるかと思います

DELIMITER $$

CREATE PROCEDURE `sp_put_t1`(
IN _kubun char(3),
IN _key int(11),
IN _ext blob,
INOUT _status_key int(11),
OUT _status int(11)
)
BEGIN

  SET _status = 0 ;
  
  BEGIN
    DECLARE _not_found tinyint UNSIGNED DEFAULT 0;
    DECLARE _error_flag tinyint UNSIGNED DEFAULT 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      SET _status = -99;
    END;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _not_found = 1;
    
    BEGIN


      IF _error_flag = 0 THEN
        CASE _kubun
          WHEN 'ADD' THEN
           INSERT INTO t1
           (
           kkey,
           ext
           )
           VALUES
           (
           _key,
           _ext
           ) ;
           
           SELECT last_insert_id() INTO _status_key;

          ELSE
            SET _status = -90 ;
        END CASE;
       ELSE
         SET _status = 2 ;
       END IF ;
    END ;
  END;
END $$

DELIMITER ;

格安レンタルサーバーをお探しなら99円レンタルサーバー

ループ系サンプル

カーソルを用いたループ系のサンプルになります。

• ストアドファンクション

DROP function IF EXISTS sf_chk_tabs ;

DELIMITER //
CREATE FUNCTION sf_chk_tabs
(in_tabl_id BIGINT) RETURNS int 
BEGIN
  # 変数宣言！
  DECLARE _RESULT INT;            
  
  DECLARE _tabl_id  BIGINT;            
  DECLARE _tab_seq  INT;            
  DECLARE _tab1_time  DATETIME;            

  DECLARE _old_tabl_id  BIGINT;            
  DECLARE _old_tab_seq  INT;            
  DECLARE _old_tab1_time  DATETIME;            


  DECLARE Cur CURSOR FOR 
  SELECT
    tabl_id, 
    tab_seq,
    tab1_time
  FROM
    tabs
  WHERE
    tabl_id = in_tabl_id
  ORDER BY
    tabl_id, 
    tab_seq,
    tab1_time
  ;

  DECLARE EXIT HANDLER FOR NOT FOUND SET done = 0; # レコードがEOFになった場合、done変数に0をセットします、ハンドラ
 
  SET done = 1;

  SET _RESULT=0 ;
  SET _old_tab_seq=0;
  SET _old_tab1_time='2000-01-01 00:00:00' ;
  
  # カーソルのオープン
  OPEN Cur;

  WHILE done DO # 条件が真（1）の間、処理を繰り返す
        FETCH Cur INTO _tabl_id, _tab_seq,_tab1_time;
        IF (_old_tab_seq >= _tab_seq) THEN
          SET _RESULT=1 ;
        ELSE
          SET _old_tab_seq = _tab_seq ;
          IF (_old_tab1_time > _tab1_time) THEN
            SET _RESULT=2 ;
          ELSE
            SET _old_tab1_time = _tab1_time ;
          END IF;
        END IF;
  END WHILE;

  # カーソルのクローズ
  CLOSE Cur;

  RETURN _RESULT ;

END
//
DELIMITER ;

データベースを利用しないストアド

別にデータベースを使わなくたっていいんです、下記例は、user_agentから区分を返すストアドファンクションです

• ストアドファンクション

DROP FUNCTION IF EXISTS sf_get_ua ;

DELIMITER $$

CREATE FUNCTION `sf_get_ua`(_user_agent text) RETURNS int(11)
BEGIN
   DECLARE _result int ;

   CASE
     WHEN _user_agent LIKE '%Android%' THEN 
       SET _result='1' ;
     WHEN _user_agent LIKE '%iPad%' THEN 
       SET _result='1' ;
     WHEN _user_agent LIKE '%iPhone%' THEN 
       SET _result='1' ;
   ELSE
     IF _user_agent IS NULL THEN
       SET _result='3' ;
     ELSE
       SET _result='2' ;
     END IF ;
   END CASE ;

   RETURN _result ;

END$$

DELIMITER ;

ちょっと時間なくてサンプル一個ですが、これから随時記載していきますので気長にお待ち下さい。