本文只针对mysql初学者,整理一篇注释比较清楚的过程语句,大牛请飘过.
代码块
DROP PROCEDURE IF EXISTS useCursor;
CREATE PROCEDURE useCursor() -- 新建过程
BEGIN -- 开始
DECLARE oneAddr varchar(40) default '';
DECLARE fsum DECIMAL(20,4) DEFAULT 0; #合计的变量,所有变量一定要定义一个初始值,否则在计算中null会导致整个结果是null
DECLARE v_cut DECIMAL(20,4);
DECLARE done INT DEFAULT FALSE;
DECLARE curl CURSOR FOR select table_name from information_schema.tables where table_schema='CosmeticBrand' and table_name like 'CR_SaleBill\____';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN curl;
read_loop: LOOP
FETCH curl INTO oneAddr;
IF done THEN
LEAVE read_loop;
END IF;
SET @cut = v_cut;
SET @cut = 0;
SET @sql = CONCAT('select sum(RealPrice) into @cut from ',oneAddr,' where year(SaleTime)=2016 and category < 30');
PREPARE stmt FROM @sql;
EXECUTE stmt;
SET fsum = fsum + IFNULL(@cut,0);
END LOOP;
CLOSE curl;
select fsum;
END;
call useCursor();