My Sql存储过程、游标实例

DELIMITER $$ 
DROP PROCEDURE IF EXISTS genChannelCategorySkuRelation $$
CREATE PROCEDURE genChannelCategoryRelation ()
BEGIN
 DECLARE  no_more_category, channel_id ,create_user_id , category_id_temp INT DEFAULT 0;
 DECLARE  categorys CURSOR FOR   SELECT DISTINCT category_id FROM erp$pro$category  WHERE STATUS = 1 ;  /*First: Delcare a cursor,首先这里对游标进行定义*/
 DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_category = 1; /*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/
 SET channel_id = 15;    /*替换成真实的渠道ID*/
 SET create_user_id= 141;  /*替换成用户Id*/
 DELETE FROM erp$pro$category_channel_relation;

 OPEN  categorys; /*Second: Open the cursor 接着使用OPEN打开游标*/
 
 
 REPEAT
 
 FETCH  categorys INTO category_id_temp; /*Third: now you can Fetch the row 把第一行数据写入变量中,游标也随之指向了记录的第一行*/
 SELECT category_id_temp;
 INSERT  INTO erp$pro$category_channel_relation(channel_id,category_id,create_by, create_time,last_update_by,last_update_time,STATUS )
 VALUES  (channel_id,category_id_temp, create_user_id, NOW(),create_user_id,NOW(),1);

 UNTIL  no_more_category = 1
 
 END REPEAT;
 
 CLOSE  categorys;  /*Finally: cursor need be closed 用完后记得用CLOSE把资源释放掉*/
 
END $$  

DELIMITER ;

CALL genChannelCategoryRelation ();

 

参考：http://blog.csdn.net/rdarda/article/details/7881648/