有如下存储过程：
DROP PROCEDURE IF EXISTS pro_import_under_take_count; CREATE PROCEDURE pro_import_under_take_count () BEGIN /* 定义每月的第一天 */ DECLARE MonthFirstDay varchar(12) ;//定义的变量没哟写@符号,后期在使用的过程中也不要用使用@varible,直接写成最原始定义的变量名， DECLARE Month_now varchar(20) ; DECLARE under_year varchar(4) ; DECLARE under_month varchar(2) ;//declare 定义的变量声明一定要在select赋值语句的上面，不然创建存储过程报错！！！折磨了我大半天，谨记 DECLARE done INT DEFAULT 0; DECLARE totalRow INT DEFAULT 0; DECLARE orgid VARCHAR(50); /* 首先检查每个部门工单表中是否有本月工作考核的数据 使用游标进行遍历*/ DECLARE result CURSOR FOR SELECT org_id FROM event_undertake GROUP BY org_id ; #HAVING under_take_time>=@MonthFirstDay AND under_take_time<=@Month_now;//这是错误的，谨记！！！ DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET done = 1; SELECT DATE_FORMAT(NOW(), ‘%Y-%m-01‘) INTO MonthFirstDay; //select赋值语句 变量值：“2017-09-03”格式 SELECT NOW() INTO Month_now; //select赋值语句 变量值：“2017-09-03 19:32:23”格式 SELECT DATE_FORMAT(NOW(), ‘%Y‘) INTO under_year; //select赋值语句 变量值：“2017”格式(获取年份) SELECT DATE_FORMAT(NOW(), ‘%m‘) INTO under_month; //select赋值语句 变量值：“09”格式（获取月份） OPEN result; REPEAT FETCH result INTO orgid; IF done !=1 THEN /* 判断该部门是否存在本月的承办工单统计数据 */ SELECT COUNT(*) INTO totalRow from event_undertake_count euc WHERE euc.under_take_year=under_year AND euc.under_take_month=under_month AND euc.count_type=‘1‘ AND euc.org_id = orgid; IF totalRow != 0 THEN /*存在，进行更新操作*/ SET totalRow = 6; UPDATE event_undertake_count SET under_take_count=(SELECT COUNT(*) from event_undertake e WHERE e.org_id=orgid AND e.under_take_time>=MonthFirstDay AND e.under_take_time<=Month_now) ,not_end_count=(SELECT COUNT(*) from event_undertake e WHERE e.org_id=orgid AND e.end_status=‘0‘ AND e.under_take_time>=MonthFirstDay AND e.under_take_time<=Month_now ) ,end_count=(SELECT COUNT(*) from event_undertake e WHERE e.org_id=orgid AND e.end_status=‘1‘ AND e.under_take_time>=MonthFirstDay AND e.under_take_time<=Month_now ) ,on_time_end_count=(SELECT COUNT(*) from event_undertake e WHERE e.org_id=orgid AND e.over_time_status=‘0‘ AND e.under_take_time>=MonthFirstDay AND e.under_take_time<=Month_now ) ,over_time_end_count=(SELECT COUNT(*) from event_undertake e WHERE e.org_id=orgid AND e.over_time_status=‘1‘ AND e.under_take_time>=MonthFirstDay AND e.under_take_time<=Month_now) ,do_well_count=(SELECT COUNT(*) from event_undertake e WHERE e.org_id=orgid AND e.do_well_status=‘1‘ AND e.under_take_time>=MonthFirstDay AND e.under_take_time<=Month_now) WHERE under_take_year=under_year AND under_take_month=under_month AND count_type=‘1‘ AND org_id = orgid; ELSE /*不存在，进行插入操作*/ INSERT INTO event_undertake_count (id, org_id, under_take_year, under_take_month, count_type, under_take_count, not_end_count, end_count, on_time_end_count, over_time_end_count, do_well_count, create_time) SELECT (SELECT REPLACE (UUID(), ‘-‘, ‘‘)) AS id, (SELECT orgid) AS org_id, (SELECT under_year) AS under_take_year, (SELECT under_month) AS under_take_month, (SELECT ‘1‘) AS count_type, (SELECT COUNT(*) from event_undertake e WHERE e.org_id=orgid AND e.under_take_time>=MonthFirstDay AND e.under_take_time<=Month_now ) AS under_take_count , (SELECT COUNT(*) from event_undertake e WHERE e.org_id=orgid AND e.end_status=‘0‘ AND e.under_take_time>=MonthFirstDay AND e.under_take_time<=Month_now ) AS not_end_count , (SELECT COUNT(*) from event_undertake e WHERE e.org_id=orgid AND e.end_status=‘1‘ AND e.under_take_time>=MonthFirstDay AND e.under_take_time<=Month_now ) AS end_count , (SELECT COUNT(*) from event_undertake e WHERE e.org_id=orgid AND e.over_time_status=‘0‘ AND e.under_take_time>=MonthFirstDay AND e.under_take_time<=Month_now ) AS on_time_end_count , (SELECT COUNT(*) from event_undertake e WHERE e.org_id=orgid AND e.over_time_status=‘1‘ AND e.under_take_time>=MonthFirstDay AND e.under_take_time<=Month_now ) AS over_time_end_count , (SELECT COUNT(*) from event_undertake e WHERE e.org_id=orgid AND e.do_well_status=‘1‘ AND e.under_take_time>=MonthFirstDay AND e.under_take_time<=Month_now ) AS do_well_count , (SELECT Month_now) AS create_time; END IF; /*ceshi */ #SELECT orgid,totalRow,under_year,under_month; END IF; UNTIL done END REPEAT; CLOSE result; #SELECT MonthFirstDay,Month_now,under_year,under_month; END #测试 CALL pro_import_under_take_count();
重要点：
1、游标中 FETCH result INTO orgid; 中orgid不可以跟result中取出来的字段一样，否则取不出来数据，（郁闷了我半天，午睡都没有睡）
2、存储过程定义declare只能放在最上面，其次是select语句；
3、SQL语句生产32位UUID：SELECT REPLACE(UUID(),‘-‘,‘‘) AS id;
4、select 查询 涉及 where group by ,having 的时候，查询的顺序
select COUNT(*)as ‘>20岁人数‘,classid from Table1 where sex=‘男‘ group by classid,age having age>20
5、mysql中变量申明定义
原文：http://www.cnblogs.com/sdll/p/7521423.html