关于一个简单的mysql存储过程实例

这是一个根据传过来的一个pid  int类型的值来查询别的表的数据然后进行添加操作

CREATE DEFINER = `root`@`localhost` PROCEDURE `NewProc`(in pid int)BEGIN#定义一个INT类型的memberId DECLARE memberId INT(11);#定义一个DECIMAL类型的xpiceDECLARE xprice DECIMAL(10);#定义一个DECIMAL类型的xs_piceDECLARE xs_priceDECIMAL(10);#定义一个DECIMAL类型的xss_piceDECLARE xss_priceDECIMAL(10);#将传过来的pid赋值给memberIdSET memberId=pid;#显示一下（作为校验）SELECT memberId;#修改一张表的数据UPDATE zyx_price_d_free SET num=num-1,end_date=DATE_FORMAT(NOW(),'%Y-%m-%d') WHERE member_id=memberId;#查询出一张表的数据将查到的列赋值给xprice 这里的赋值只能是查到的结果集为一条#多个列的情况使用,号隔开max(status), avg(status) into max, avgSELECT price INTO xprice FROM zyx_price_d_free WHERE member_id=memberId;#显示一下（作为校验）SELECT xprice;#查询出一张表的数据将查到的列赋值给xs_price SELECT mem.s_price INTO xs_price FROM (SELECT member_id,s_price FROM zyx_price_d_info WHERE member_id=memberId ORDER BY id DESC) mem GROUP BY mem.member_id; #做一个计算求和SET xss_price=xprice;#判断是否为空IF (xs_price is not null) THEN   SET xss_price=xs_price+xss_price;END IF;#插入一条数据INSERT INTO zyx_price_d_info VALUE(NULL,1,memberId,'每日返回电子积分2%',1,xss_price,xprice,41,NULL,DATE_FORMAT(NOW(),'%Y-%m-%d'),DATE_FORMAT(NOW(),'%Y-%m-%d %T'),0);END;

这个存储过程是在mysql数据库中添加的，添加的为函数存储过程，下面是截图

下面是一个存储过程加游标的案例，根据查询出来的id的结果集进行循环然后操作

BEGIN #定义一个INT类型的memberId    DECLARE memberId INT(11);  #定义一个DECIMAL类型的xpice DECLARE xprice DECIMAL(10); #定义一个DECIMAL类型的xs_pice DECLARE xs_priceDECIMAL(10); #定义一个查询结果集的游标，这里只查了一个列   DECLARE NUMBER_XS CURSOR FOR     SELECT member_id FROM zyx_price_d_free WHERE adddate!=DATE_FORMAT(NOW(),'%Y-%m-%d'); #设置 循环使用 变量 memberId < 0 跳出循环 DECLARE CONTINUE HANDLER FOR NOT FOUND SET memberId=0; #打开游标   OPEN NUMBER_XS; #游标向下走一步---赋值   FETCH NUMBER_XS INTO memberId; #进行while并设置循环条件   WHILE (memberId > 0) DO#参考上一个存储过程UPDATE zyx_price_d_free SET num=num-1,end_date=DATE_FORMAT(NOW(),'%Y-%m-%d') WHERE member_id=memberId;SELECT price INTO xprice FROM zyx_price_d_free WHERE member_id=memberId;SELECT xprice;SELECT mem.s_price INTO xs_price FROM (SELECT member_id,s_price FROM zyx_price_d_info WHERE member_id=memberId ORDER BY id DESC) mem GROUP BY mem.member_id; SET xs_price=xs_price+xprice;INSERT INTO zyx_price_d_info VALUE(NULL,1,memberId,'每日返回电子积分2%',1,xs_price,xprice,41,NULL,DATE_FORMAT(NOW(),'%Y-%m-%d'),DATE_FORMAT(NOW(),'%Y-%m-%d %T'),0);#游标向下走一步---赋值FETCH NUMBER_XS INTO memberId;  #结束本次循环   END WHILE; #关闭游标   CLOSE NUMBER_XS;END

根据传过来的表名做为条件去查询tname代表表名

需要用到字符类型的插入或者修改时加上""即可,如下图

BEGINDECLARE memberId INT(11);DECLARE  xzprice FLOAT;DECLARE  sprice FLOAT;DECLARE tname VARCHAR(255);SET memberId = member;SET tname=tablename;SET sprice=price;SET @sjprice=0;#SELECT type,memberId,remark,title,sprice,tname;SET @SqlCmd = CONCAT('SELECT  s_price into @sjprice  FROM ' ,tname,' WHERE member_id=',memberId,' AND del=0 ORDER BY addtime DESC LIMIT 0,1');  PREPARE stmt from @SqlCmd;EXECUTE stmt;#SELECT @SqlCmd;#SELECT @sjprice;SET xzprice=@sjprice;IF(xzprice IS NOT NULL) THENIF(type=10) THENSET sprice=xzprice+sprice;END IF;IF(type=7) THENSET sprice=xzprice-sprice;END IF;END IF;SET @Sqlins = CONCAT('INSERT INTO ' ,tname,'(type,member_id,title,remark,s_price,price,admin_id,adddate,addtime,del) VALUE(',type,',',memberId,',"',title,'",',remark,',',sprice,',',price,',',adminId,',"',CURDATE(),'","',NOW(),'",',0,')');  #SELECT @Sqlins;PREPARE inssql from @Sqlins;EXECUTE inssql;#INSERT INTO zyx_price_w_info(type,member_id,title,remark,s_price,price,admin_id,adddate,addtime,del) VALUE(type,memberId,title,remark,sprice,price,41,DATE_FORMAT(NOW(),'%Y-%m-%d'),DATE_FORMAT(NOW(),'%Y-%m-%d %T'),0);END