#norelated
#title(mysqlストアドプロシージャサンプル)
* ストアドプロシージャ [#a0468088]
#contents
** カーソルを使用したストアドプロシージャの例 [#y4c0e19b]
*** 実行準備 [#udf03e23]
テーブルとデータを作成
DROP TABLE IF EXISTS `wtb_products_search2`;
CREATE TABLE IF NOT EXISTS `wtb_products_search2` (
`product_id` int(11) NOT NULL,
`brand_id` int(11) NOT NULL,
`season_id` smallint(6) NOT NULL default '0',
`item1_id` smallint(6) NOT NULL default '0',
PRIMARY KEY (`product_id`)
);
INSERT INTO `wtb_products_search2` VALUES
('1', '1', '1', '1'),
('2', '2', '1', '1'),
('3', '1', '1', '1');
*** ソースコード [#e497cfb9]
mysqlへの登録方法は[[mysql/ストアドプロシージャ登録方法]]を参照
delimiter //
DROP PROCEDURE IF EXISTS `prc_update_product_search_sale_amt`//
CREATE PROCEDURE `prc_update_product_search_sale_amt`
(
/*パラメータ*/
IN I_TARGET_DATE DATETIME,
IN I_BRAND_ID INT
)
BEGIN
/*カーソル用の変数宣言*/
DECLARE V_DONE TINYINT DEFAULT 0;
DECLARE C_PRODUCT_ID INT;
DECLARE C_SEASON_ID INT;
DECLARE C_BRAND_ID INT;
DECLARE C_ITEM1_ID INT;
/*カーソル定義*/
DECLARE CUR_PROD_SEARCH CURSOR FOR
SELECT
`product_id`,
`season_id`,
`brand_id`,
`item1_id`
FROM `wtb_products_search2`
WHERE I_BRAND_ID = ''
OR (I_BRAND_ID <> '' AND brand_id = I_BRAND_ID);
/*カーソルの終了条件に使用する変数を定義*/
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET V_DONE = 1;
/*カーソルをオープンしてループする*/
OPEN CUR_PROD_SEARCH;
/* SQLSTATE '02000'が発生するまでループ*/
LABEL1: REPEAT
FETCH CUR_PROD_SEARCH INTO
C_PRODUCT_ID, C_SEASON_ID, C_BRAND_ID, C_ITEM1_ID;
IF NOT V_DONE THEN
SELECT C_PRODUCT_ID, C_SEASON_ID, C_BRAND_ID, C_ITEM1_ID;
END IF;
UNTIL V_DONE END REPEAT;
CLOSE CUR_PROD_SEARCH;
END
//
delimiter ;
*** 実行 [#ia59da15]
mysql> call prc_update_product_search_sale_amt(CURRENT_TIMESTAMP, '1');
+--------------+-------------+------------+------------+
| C_PRODUCT_ID | C_SEASON_ID | C_BRAND_ID | C_ITEM1_ID |
+--------------+-------------+------------+------------+
| 1 | 1 | 1 | 1 |
+--------------+-------------+------------+------------+
1 row in set (0.00 sec)
+--------------+-------------+------------+------------+
| C_PRODUCT_ID | C_SEASON_ID | C_BRAND_ID | C_ITEM1_ID |
+--------------+-------------+------------+------------+
| 3 | 1 | 1 | 1 |
+--------------+-------------+------------+------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
