MySQL存储过程-游标

BEGIN

DECLARE g_id, g_sum INT DEFAULT 0;
DECLARE mark INT DEFAULT 0;      #定义状态变量(写在上边)      #所有的DECLARE　必须写在上面
DECLARE curl CURSOR  FOR  SELECT goods_id FROM goods;   #创建游标
DECLARE CONTINUE HANDLER FOR NOT found SET mark=1; #处理性事件

OPEN curl;      #打开游标, 数据查询

REPEAT
	SET g_id=0;
	FETCH curl INTO g_id;   #读取每行记录,  和定义对应     定义有几列, 就定义几个变量

	IF g_id>0 THEN
	SET g_sum=g_sum+g_id;
	END IF;

	UNTIL mark=1  #如果没行则退出循环
END REPEAT;
CLOSE curl;  # 关闭游标

SELECT g_sum;

END

附表

DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
  `goods_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(120) NOT NULL DEFAULT '',
  `cat_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `brand_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `goods_sn` char(15) NOT NULL DEFAULT '',
  `goods_number` smallint(5) unsigned NOT NULL DEFAULT '0',
  `shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `click_count` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`goods_id`)
) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;




INSERT INTO `goods` VALUES ('1', 'kd876', '4', '8', 'ecs000000', '1', '1388.00', '1665.60', '9');
INSERT INTO `goods` VALUES ('4', '诺基亚n85原装充电器', '8', '1', 'ecs000004', '17', '58.00', '69.60', '0');
INSERT INTO `goods` VALUES ('3', '诺基亚原装5800耳机', '8', '1', 'ecs000002', '24', '68.00', '81.60', '3');
INSERT INTO `goods` VALUES ('5', '索爱原装m2卡读卡器', '11', '7', 'ecs000005', '8', '20.00', '24.00', '3');
INSERT INTO `goods` VALUES ('6', '胜创kingmax内存卡', '11', '0', 'ecs000006', '15', '42.00', '50.40', '0');
INSERT INTO `goods` VALUES ('7', '诺基亚n85原装立体声耳机hs-82', '8', '1', 'ecs000007', '20', '100.00', '120.00', '0');
INSERT INTO `goods` VALUES ('8', '飞利浦9@9v', '3', '4', 'ecs000008', '1', '399.00', '478.79', '10');
INSERT INTO `goods` VALUES ('9', '诺基亚e66', '3', '1', 'ecs000009', '4', '2298.00', '2757.60', '20');
INSERT INTO `goods` VALUES ('10', '索爱c702c', '3', '7', 'ecs000010', '7', '1328.00', '1593.60', '11');
INSERT INTO `goods` VALUES ('11', '索爱c702c', '3', '7', 'ecs000011', '1', '1300.00', '0.00', '0');
INSERT INTO `goods` VALUES ('12', '摩托罗拉a810', '3', '2', 'ecs000012', '8', '983.00', '1179.60', '13');
INSERT INTO `goods` VALUES ('13', '诺基亚5320 xpressmusic', '3', '1', 'ecs000013', '8', '1311.00', '1573.20', '13');
INSERT INTO `goods` VALUES ('14', '诺基亚5800xm', '4', '1', 'ecs000014', '1', '2625.00', '3150.00', '6');
INSERT INTO `goods` VALUES ('15', '摩托罗拉a810', '3', '2', 'ecs000015', '3', '788.00', '945.60', '8');
INSERT INTO `goods` VALUES ('16', '恒基伟业g101', '2', '11', 'ecs000016', '0', '823.33', '988.00', '3');
INSERT INTO `goods` VALUES ('17', '夏新n7', '3', '5', 'ecs000017', '1', '2300.00', '2760.00', '2');
INSERT INTO `goods` VALUES ('18', '夏新t5', '4', '5', 'ecs000018', '1', '2878.00', '3453.60', '0');
INSERT INTO `goods` VALUES ('19', '三星sgh-f258', '3', '6', 'ecs000019', '12', '858.00', '1029.60', '7');
INSERT INTO `goods` VALUES ('20', '三星bc01', '3', '6', 'ecs000020', '12', '280.00', '336.00', '14');
INSERT INTO `goods` VALUES ('21', '金立 a30', '3', '10', 'ecs000021', '40', '2000.00', '2400.00', '4');
INSERT INTO `goods` VALUES ('22', '多普达touch hd', '3', '3', 'ecs000022', '1', '5999.00', '7198.80', '16');
INSERT INTO `goods` VALUES ('23', '诺基亚n96', '5', '1', 'ecs000023', '8', '3700.00', '4440.00', '17');
INSERT INTO `goods` VALUES ('24', 'p806', '3', '9', 'ecs000024', '100', '2000.00', '2400.00', '35');
INSERT INTO `goods` VALUES ('25', '小灵通/固话50元充值卡', '13', '0', 'ecs000025', '2', '48.00', '57.59', '0');
INSERT INTO `goods` VALUES ('26', '小灵通/固话20元充值卡', '13', '0', 'ecs000026', '2', '19.00', '22.80', '0');
INSERT INTO `goods` VALUES ('27', '联通100元充值卡', '15', '0', 'ecs000027', '2', '95.00', '100.00', '0');
INSERT INTO `goods` VALUES ('28', '联通50元充值卡', '15', '0', 'ecs000028', '0', '45.00', '50.00', '0');
INSERT INTO `goods` VALUES ('29', '移动100元充值卡', '14', '0', 'ecs000029', '0', '90.00', '0.00', '0');
INSERT INTO `goods` VALUES ('30', '移动20元充值卡', '14', '0', 'ecs000030', '9', '18.00', '21.00', '1');
INSERT INTO `goods` VALUES ('31', '摩托罗拉e8 ', '3', '2', 'ecs000031', '1', '1337.00', '1604.39', '5');
INSERT INTO `goods` VALUES ('32', '诺基亚n85', '3', '1', 'ecs000032', '4', '3010.00', '3612.00', '9');