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

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

CREATE DEFINER = `root`@`localhost` PROCEDURE `NewProc`(in pid int)
BEGIN
#定义一个INT类型的memberId 
DECLARE memberId INT(11);

#定义一个DECIMAL类型的xpice
DECLARE xprice DECIMAL(10);

#定义一个DECIMAL类型的xs_pice
DECLARE xs_price	DECIMAL(10);

#定义一个DECIMAL类型的xss_pice
DECLARE xss_price	DECIMAL(10);

#将传过来的pid赋值给memberId
SET 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, avg
SELECT 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_price	DECIMAL(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代表表名

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

BEGIN

DECLARE 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) THEN
	IF(type=10) THEN
		SET sprice=xzprice+sprice;
	END IF;
	IF(type=7) THEN
		SET 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