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/