mysql open 存储过程,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;