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/