mysql 存储过程

DELIMITER $$

 

DROP PROCEDURE IF EXISTS `addData` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `addData`()

BEGIN

//声明变量

declare iid integer;

declare icategory_id integer;

declare icreate_date date;

declare iupdate_date date;

declare iitem_id integer;

declare icost_id integer;

declare idiscount_id integer;

declare iprice_id integer;

declare iinventory_id integer;

 

 

declare stop int default 0;

 

//创建游标

declare cur cursor for

SELECT id,category_id,create_date,update_date,item_id,cost_id,discount_id,price_id,inventory_id FROM item_detail_info i where i.id = (select max(id) from item_detail_info);

 

//打开游标

open cur;

 

//读取游标，并赋值，变量个数要与创建时的列一致，也可以用fetch cur into variable 变量列表，如(variable  a,b,c)

fetch cur into iid,icategory_id,icreate_date,iupdate_date,iitem_id,icost_id,idiscount_id,iprice_id,iinventory_id;

SELECT iid,icategory_id,icreate_date,iupdate_date,iitem_id,icost_id,idiscount_id,iprice_id,iinventory_id;

 

//插入数据

insert into item_detail_info

(id,category_id,create_date,update_date,item_id,cost_id,discount_id,price_id,inventory_id)

values

(iid+1,icategory_id,icreate_date,iupdate_date,iitem_id,icost_id,idiscount_id,iprice_id,iinventory_id);

 

close cur;

END $$

 

DELIMITER ;