Mysql存储过程实例

mysql存储过程
CREATE PROCEDURE generateGoods()
BEGIN
-- 定义帐号类型变量
DECLARE user_type VARCHAR(10);
-- 定义变量
DECLARE myGoodsName VARCHAR(255);
DECLARE myModel VARCHAR(255);
DECLARE myRetail FLOAT(10);
DECLARE myMarket FLOAT(10);
DECLARE myCity FLOAT(10);
DECLARE myProv FLOAT(10);
DECLARE myAgent FLOAT(10);
DECLARE myRegion FLOAT(10);
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 定义一个游标
DECLARE priceCur CURSOR FOR SELECT price_id,goods_name,model,retail_price,market_price,city_price,prov_price,agent_price,region_price FROM price;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

-- 删除所有的产品
DELETE FROM goods;
-- 获取当前帐号的类型
SELECT user_level INTO user_type FROM user_info;

IF user_type='5' THEN

-- 打开游标 
OPEN priceCur;
myLoop:LOOP
FETCH priceCur INTO myGoodsName,myModel,myRetail,myMarket,myCity,myProv,myAgent,myRegion;
IF done THEN
LEAVE myLoop;
END IF;
-- 业务操作
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myRetail,myRegion,0);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myMarket,myRegion,1);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myCity,myRegion,2);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myProv,myRegion,3);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myAgent,myRegion,4);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myRegion,myRegion,5);
-- 提交事务  
COMMIT;
-- 结束自定义循环体
END LOOP myLoop; 
-- 关闭游标 
CLOSE priceCur; 

ELSEIF user_type='4' THEN

-- 打开游标 
OPEN priceCur;
myLoop:LOOP
FETCH priceCur INTO myGoodsName,myModel,myRetail,myMarket,myCity,myProv,myAgent,myRegion;
IF done THEN
LEAVE myLoop;
END IF;
-- 业务操作
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myRetail,myAgent,0);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myMarket,myAgent,1);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myCity,myAgent,2);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myProv,myAgent,3);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myAgent,myAgent,4);
-- 提交事务  
COMMIT;
-- 结束自定义循环体
END LOOP myLoop; 
-- 关闭游标 
CLOSE priceCur; 

ELSEIF user_type='3' THEN

-- 打开游标 
OPEN priceCur;
myLoop:LOOP
FETCH priceCur INTO myGoodsName,myModel,myRetail,myMarket,myCity,myProv,myAgent,myRegion;
IF done THEN
LEAVE myLoop;
END IF;
-- 业务操作
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myRetail,myProv,0);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myMarket,myProv,1);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myCity,myProv,2);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myProv,myProv,3);
-- 提交事务  
COMMIT;
-- 结束自定义循环体
END LOOP myLoop; 
-- 关闭游标 
CLOSE priceCur; 

ELSEIF user_type='2' THEN

-- 打开游标 
OPEN priceCur;
myLoop:LOOP
FETCH priceCur INTO myGoodsName,myModel,myRetail,myMarket,myCity,myProv,myAgent,myRegion;
IF done THEN
LEAVE myLoop;
END IF;
-- 业务操作
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myRetail,myCity,0);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myMarket,myCity,1);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myCity,myCity,2);
-- 提交事务  
COMMIT;
-- 结束自定义循环体
END LOOP myLoop; 
-- 关闭游标 
CLOSE priceCur; 

ELSEIF user_type='1' THEN

-- 打开游标 
OPEN priceCur;
myLoop:LOOP
FETCH priceCur INTO myGoodsName,myModel,myRetail,myMarket,myCity,myProv,myAgent,myRegion;
IF done THEN
LEAVE myLoop;
END IF;
-- 业务操作
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myRetail,myMarket,0);
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myMarket,myMarket,1);
-- 提交事务  
COMMIT;
-- 结束自定义循环体
END LOOP myLoop; 
-- 关闭游标 
CLOSE priceCur; 

ELSE

-- 打开游标 
OPEN priceCur;
myLoop:LOOP
FETCH priceCur INTO myGoodsName,myModel,myRetail,myMarket,myCity,myProv,myAgent,myRegion;
IF done THEN
LEAVE myLoop;
END IF;
-- 业务操作
INSERT INTO goods VALUES(DEFAULT,myGoodsName,myModel,myRetail,myRetail,0);
-- 提交事务  
COMMIT;
-- 结束自定义循环体
END LOOP myLoop; 
-- 关闭游标 
CLOSE priceCur; 

END IF;
END;

-- sql 执行存储过程  
call generateGoods(); 

-- sql 删除存储过程  
drop procedure generateGoods;  
 

转载于:https://my.oschina.net/royweb/blog/781990