EZSPLIT 2012/11/17

SQLです。
mysqlのファンクションです。
カンマ区切りの文字列中の何番目の文字列を取り出す目的で作成しました。
３〜４週間前に作成したと思うのですが、いまいちその動機を思い出せないでいます。
やばいですね....
関数名としてEZSPLITというのもいまいちだなーと思いつつ

DROP FUNCTION EZSPLIT;
delimiter //
CREATE FUNCTION EZSPLIT(s TEXT, idx INT) RETURNS TEXT
NO SQL
BEGIN
     RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(s, ','), ',' , idx),
',' , -1);
END
//

 使い方は
こんな感じ

mysql> select EZSPLIT('a,b,c',2);
+--------------------+
| EZSPLIT('a,b,c',2) |
+--------------------+
| b                  |
+--------------------+
1 row in set (0.00 sec)

何番目かの指定は１スタートとなっています。

mysqlでストアドプロシージャ その6 - プロシージャ内から別のプロシージャを呼ぶ 2012/09/24

mysqlでストアドプロシージャ その6 です。

プロシージャ内から別のプロシージャを呼んでみます。

ついでにトランザクションに参加してるのか調べてみました。

参考

• プログラマメモ2: mysqlでストアドプロシージャ その4 - トランザクション

結論としてトランザクションに含まれるようですね。

DROP PROCEDURE IF EXISTS p000;
DROP PROCEDURE IF EXISTS p007;
delimiter //
-- sub procedure
CREATE PROCEDURE p000()
BEGIN
    INSERT INTO a VALUES(0, CURRENT_TIMESTAMP());
END;
-- main procedure
CREATE PROCEDURE p007()
BEGIN
    -- 以下の実験コードは、データベースエンジンがInnoDBとき確認できます。
    -- MyISAMだとロールバックされない。
  
    -- エラーの場合 ロールバック設定
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT "*** ROLLBACK!!";
        ROLLBACK;
    END;
  
    -- トランザクション開始
    START TRANSACTION;
  
    -- 呼び出されたprocedureもトランザクションに含まれるようだ
    CALL p000();
  
    -- PKの重複がおこるので、かならず失敗するのでロールバックされるはず
    INSERT INTO a SET id = last_insert_id();
    -- お決まりのコミット!!
    COMMIT;
END
//
CALL p007();-- ここで実行
DROP PROCEDURE IF EXISTS p007;
DROP PROCEDURE IF EXISTS p000;

mysqlでストアドプロシージャ その5 - ラベルを使ってcontinue,breakっぽく 2012/09/24

mysqlでストアドプロシージャ その5です。

ループでラベル使うとcontinue,breakっぽく使えますねと。

• 17.2.10. フローコントロール・コンストラクト
• 3 トリガーおよびストアド・プロシージャ

なんとなく下記のような感じで

DROP PROCEDURE IF EXISTS p004;
delimiter //
CREATE PROCEDURE p004()
BEGIN
  
    DECLARE done INT DEFAULT 0;
    DECLARE a_id INT;
    DECLARE cur CURSOR FOR
        SELECT id FROM a;
    -- この宣言で読み取り行がないことを検知します。
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  
    OPEN cur; -- カーソルオープン
  
    main:REPEAT -- 繰り返し
        FETCH cur INTO a_id;
        IF NOT done THEN
         
           --  continueっぽく使える
           IF a_id < 20 THEN ITERATE main; END IF;
          
           -- 通常inset にwhereが使えなかったのでdualを使ってるけどこの辺自信がない...
            INSERT INTO b (ida) SELECT a_id FROM dual WHERE NOT EXISTS(SELECT 1 FROM b WHERE ida=a_id);
          
            -- breakっぽく使える
            IF a_id = 22 THEN LEAVE main; END IF;
      
        END IF;
    UNTIL done END REPEAT main;
  
    -- クローズ!!
    CLOSE cur;

END
//
CALL p004();-- ここで実行
DROP PROCEDURE IF EXISTS p004;

mysqlでストアドプロシージャ その4 - トランザクション 2012/09/23

 mysqlでストアドプロシージャ その4 です。
トランザクションの実験です。

どうもDBのエンジンによってトランザクションが有効無効がある感じですね。

参考

• ストアドプロシージャでトランザクション処理をするにはどうすれば良いので
• MySQL ストアドプロシージャー で 失敗時の　トランザクション

というわけで、データベースエンジンを切り替えて確認してみました。

どんな実験したかというと、INSERT文を二回流して、２回目のINSERTは１回目にINSERTとしたPKを使ってかならず失敗させます。

トランザクションが効けば、１回目のINSERTも登録されないはずというわけです。

たしかにMyISAMで、はトランザクションが効かず、InnoDBでは効きました。


テーブルは以下

 create table a (
  id          smallint unsigned not null auto_increment primary key,
  tday      timestamp
);

エンジンの変更は、以下のSQLで行います。

ALTER TABLE a engine = InnoDB;
ALTER TABLE a engine = MyISAM;

以下ストアド

DROP PROCEDURE IF EXISTS p006;
delimiter //
CREATE PROCEDURE p006()
BEGIN
    DECLARE x INT;
    -- 以下の実験コードは、データベースエンジンがInnoDBとき確認できます。
    -- MyISAMだとロールバックされない。
   
    -- エラーの場合 ロールバック設定
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    -- トランザクション開始
    START TRANSACTION;
   
    INSERT INTO a VALUES(0, CURRENT_TIMESTAMP());
   
    -- PKの重複がおこるので、かならず失敗するのでロールバックされるはず
    INSERT INTO a SET id = last_insert_id();
    -- お決まりのコミット!!
    COMMIT;
END
//
CALL p006();-- ここで実行
DROP PROCEDURE IF EXISTS p006;

mysqlでストアドプロシージャ その3 - ROW_COUNT() 2012/09/22

mysqlでストアドプロシージャ その3です。
 ROW_COUNT()を使うのが今回の趣旨です。
ROW_COUNTは

先行するステートメントによって更新、インサート、または削除された行の数を戻します。

ってことらしい
参考
[MySQL]複数行を追加するためのinsert文Add Star
ROW_COUNT()

DROP PROCEDURE IF EXISTS p002;
delimiter //
CREATE PROCEDURE p002()
BEGIN
    -- 取得件数格納用
    DECLARE c INT;
  
    -- 1行で複数行追加する
    INSERT INTO a VALUES(0, CURRENT_TIMESTAMP()),(0, CURRENT_TIMESTAMP()),(0, CURRENT_TIMESTAMP());
    -- 直前の更新件数を取得
    SELECT ROW_COUNT() INTO c;
    -- 値を表示する
    SELECT CONCAT('rowcount:', c);
END
//
CALL p002();-- ここで実行
DROP PROCEDURE IF EXISTS p002;

mysqlの文字列結合のやりかたがわからない...
oracleだと||だったと思うんだけど...mysqlは....
row_countの結果で登録できたかどうかの判定できるのかな
下記のようなSQLで実験 判定できるみたい

DROP PROCEDURE IF EXISTS p003;
delimiter //
CREATE PROCEDURE p003()
BEGIN
    -- 取得件数格納用
    DECLARE c INT;
  
    -- 追加しないINSERT文
    INSERT INTO a(tday) SELECT  CURRENT_TIMESTAMP() FROM dual WHERE FALSE;
    -- 直前の更新件数を取得
    SELECT ROW_COUNT() INTO c;
    IF c = 0 THEN
        SELECT 'result:0';
    ELSE
        SELECT 'result:1';
    END IF;
  
END
//
CALL p003();-- ここで実行
DROP PROCEDURE IF EXISTS p003;

mysqlでストアドプロシージャ その２ 2012/09/22

mysqlでストアドです。
oracleほどではないですが、使えて便利そうな予感です。

参考

• 17.2.9. カーソル
• Insert query doesn't support where keyword*

ストアドプロシージャで実験

簡単なシナリオ

aテーブルに入っている値をbテーブルに挿入します。

SQL一本でできそうですが...

 以下テーブル

create table a (
  id          smallint unsigned not null auto_increment primary key,
  tday      timestamp
);

create table b (
  id          smallint unsigned not null auto_increment primary key,
  ida smallint,
  tday      timestamp
);

用意したプロシージャは以下
カーソル使ってます。

DROP PROCEDURE IF EXISTS p001;
delimiter //
CREATE PROCEDURE p001()
BEGIN
  
    DECLARE done INT DEFAULT 0;
    DECLARE a_id INT;
    DECLARE cur CURSOR FOR
        SELECT id FROM a;
    -- この宣言で読み取り行がないことを検知します。
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  
    OPEN cur; -- カーソルオープン
  
    REPEAT -- 繰り返し
        FETCH cur INTO a_id;
        IF NOT done THEN
            -- 通常inset にwhereが使えなかったのでdualを使ってるけどこの辺自信がない...
            INSERT INTO b (ida) SELECT a_id FROM dual WHERE NOT EXISTS(SELECT 1 FROM b WHERE ida=a_id);  
        END IF;
    UNTIL done END REPEAT;
  
    -- クローズ!!
    CLOSE cur;

END
//
CALL p001();-- ここで実行
DROP PROCEDURE IF EXISTS p001;

mysqlでストアドプロシージャ 2012/09/22

mysqlでストアドです。
まずストアドプロシージャのリストを表示は以下で

show procedure status;

参考

• List of Stored Procedures/Functions Mysql Command Line - Stack Overflow
• MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.5.4.23 SHOW PROCEDURE STATUS と SHOW FUNCTION STATUS 構文

まずひながた、これはコマンドに流すsqlを想定しています。
作成してストアドを作成して実行して、実行したプロシージャを削除するというふうにしてます。

実行は以下

$ mysql5 -u root work < test.sql

ひながた。
delimiterの必要性がいまいちわかってないけど。で、以下のコードは信頼性という意味ではちとまずくて、createできて、実行時にエラーがでるとcreateしっぱなしなので、一番最後のdrop procedureが効かないです。

DROP PROCEDURE IF EXISTS p;
delimiter //
CREATE PROCEDURE p()
BEGIN
    SELECT * FROM c;
END
//
CALL p();-- ここで実行
DROP PROCEDURE IF EXISTS p;

参考
oracle 3.2.3 ストアド・プロシージャでのエラー処理