写一个简单的存储过程实例的笔记
1，运行在phpmyadmin 上，用于插入数据，关联产品与运费表
1 /*定义存储过程分解符*/ 2 delimiter // 3 DROP PROCEDURE IF EXISTS doShipping// 4 CREATE PROCEDURE doShipping() 5 BEGIN 6 /*声明变量*/ 7 DECLARE no_more_record INT DEFAULT 0; 8 DECLARE i INT; 9 DECLARE entityId BIGINT(10); 10 /*创建游标变量*/ 11 DECLARE cur_record CURSOR FOR SELECT entity_id FROM catalog_product_entity ORDER BY entity_id DESC; 12 DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record=1; 13 14 OPEN cur_record; 15 /*抓取游标变量*/ 16 FETCH cur_record INTO entityId; 17 /*循环获取每个产品id*/ 18 WHILE no_more_record !=1 DO 19 SET i=0; 20 /*循环关联为每个产品关联运费*/ 21 WHILE i<4 DO 22 CASE i 23 WHEN 0 THEN 24 INSERT INTO directory_country_shipping(group_product_id,ship_method,ship_cost,ship_eachadd,ship_listcountry, 25 delivery_time,show_order,created_time,update_time) VALUES(entityId,'free','0.00','0.00','All Country','15-30','0',NOW(),NOW()); 26 WHEN 1 THEN 27 INSERT INTO yo_directory_country_shipping(group_product_id,ship_method,ship_cost,ship_eachadd,ship_listcountry, 28 delivery_time,show_order,created_time,update_time) VALUES(entityId,'standard','10.00','2.00','All Country','10-15','1',NOW(),NOW()); 29 WHEN 2 THEN 30 INSERT INTO yo_directory_country_shipping(group_product_id,ship_method,ship_cost,ship_eachadd,ship_listcountry, 31 delivery_time,show_order,created_time,update_time) VALUES(entityId,'economical','9.00','3.00','All Country','5-10','2',NOW(),NOW()); 32 ELSE 33 INSERT INTO yo_directory_country_shipping(group_product_id,ship_method,ship_cost,ship_eachadd,ship_listcountry, 34 delivery_time,show_order,created_time,update_time) VALUES(entityId,'speedy','8.00','2.00','All Country','3-5','3',NOW(),NOW()); 35 END CASE; 36 SET i=i+1; 37 END WHILE; 38 FETCH cur_record INTO entityId; 39 END WHILE; 40 /*关闭游标变量*/ 41 CLOSE cur_record; 42 END;// 43 delimiter ;
