F: [Err] 1109 - Unknown table 'sqlstr' in field list
Q:(使用DECLARE声名变量是不可以在CONCAT中使用!)
(OUT变量直接在SQL语句中使用)
F:mysql 打印变量
Q:select @变量名可以把你定义的调试变量select出来的,在调用存储过程之后
F:如何注释
Q: 使用双 --加空格
函数 年 year() ,季度 quarter() 字符串长度length()
F:select into from 提示 Undeclared variable.....错误的解决办法
Q: mysql 数据库是不支持 SELECT INTO FROM 这种语句的,但是经过研究是可以通过另外一种变通的方法解决这个问题的,下面就来说说解决这个错误的办法吧!
进过搜索相关资料以及实验结果证实,可以使用
Create table Table2 (Select * from Table1);
这种语句代替
SELECT vale1, value2, value3 into Table2 from Table1;
DELIMITER $$
DROP PROCEDURE if EXISTS `getReport`$$
CREATE PROCEDURE getReport(in $years INT,in $quarter INT)
BEGIN
DECLARE aid int;
DECLARE aname VARCHAR(64);
DECLARE adate date;
DECLARE done INT DEFAULT FALSE;
DECLARE _Cur CURSOR FOR select _id, _activityName,_date from tb_activity where year(_date) = $years AND quarter(_date ) = $quarter ;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET @sqlstr =' SELECT c.na, c.uid ,c.uname ,';
OPEN _Cur;
read_loop: LOOP
FETCH _Cur INTO aid, aname, adate;
IF done THEN
LEAVE read_loop;
END IF;
SET @sqlstr = CONCAT(@sqlstr, 'sum(if( _activityId=',aid,', _cv, 0)),');
END LOOP;
CLOSE _Cur;
SET @len=LENGTH(@sqlstr) -1;
-- select @len;
SET @sqlstr = LEFT(@sqlstr ,@len);
-- select @sqlstr;
SET @sqlstr = CONCAT(@sqlstr,' from tb_activity_detail right outer join
(SELECT b.dept_name as na,a.user_id as uid,a.user_name as uname from `user` a
join department b on b.dept_no = (select left (dept_no,3) from department where dept_id=a.dept_id)) c on
_realid = c.uid GROUP BY c.na, c.uid ,c.uname ');
-- select @sqlstr;
PREPARE s1 from @sqlstr;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END $$
DELIMITER ;
CALL getReport(2015,2);