将一张表中的数据作为列名的存储过程
来源：程序员人生 发布时间：2015-03-25 11:04:16 阅读次数：3127次
问题：在做项目的进程中遇到1个人问题：那就是将A表的deco字段的值，作为B表的列来展现，而且这些值的数据是从C表中取到的
方法：本来是想用，1个视图来列出deco字段里面的值和C表中的数据的，但是发现几张表的关联比较复杂（对应我来讲）
使用单纯的select语句，是不能得到想要的效果的，在网上找了相干资料后，发现可以用游标来对查询结果集中的每条
记录来处理，所以自己写出了以下的存储进程，这个存储进程会创建两张表，这两张表的简单关联就能够得到我想要的数据集。
代码：
/*创建进程*/
DELIMITER //
DROP PROCEDURE IF EXISTS update_report //
CREATE PROCEDURE update_report()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE sql_alter VARCHAR(256) default '';
DECLARE sql_str VARCHAR(256) default '';
DECLARE a VARCHAR(200) DEFAULT '';
DECLARE b VARCHAR(200) DEFAULT '';
DECLARE c VARCHAR(200) DEFAULT '';
DECLARE kpi_value VARCHAR(200) DEFAULT '';
DECLARE mycursor CURSOR FOR SELECT id, name FROM dc_formula WHERE important=1;
DECLARE projectcursor CURSOR FOR SELECT id from management_project;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
set @sql_alter='CREATE TABLE project_kpis (
id int(11) NOT NULL PRIMARY key AUTO_INCREMENT,project_id VARCHAR(20) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8';
PREPARE sql_str from @sql_alter;
EXECUTE sql_str;
OPEN mycursor;
REPEAT
FETCH mycursor INTO a,b;
IF NOT done THEN
set b=REPLACE(b,'[','1');
set b=REPLACE(b,']','1');
set b=REPLACE(b,' ','_');
set b=REPLACE(b,'/','dv');
set @sql_alter=CONCAT('alter table project_kpis add ',b,' varchar(256)');
PREPARE sql_str from @sql_alter;
EXECUTE sql_str;
END IF;
UNTIL done END REPEAT;
set done=0;
CLOSE mycursor;
OPEN projectcursor;
projectcursor:LOOP
FETCH projectcursor INTO c;
IF done = 1 THEN
LEAVE projectcursor;
END IF;
insert into project_kpis(project_id) values(c);
open mycursor;
mycursor:LOOP
FETCH mycursor INTO a,b;
IF done = 1 THEN
LEAVE mycursor;
end IF;
set b=REPLACE(b,'[','1');
set b=REPLACE(b,']','1');
set b=REPLACE(b,' ','_');
set b=REPLACE(b,'/','dv');
set @project_id=c;
set @formula_id=a;
select case when value is NULL then '' else value END val into @kpi_value from v_dc_projectreport WHERE project_id=@project_id and formula_id=@formula_id and language_range='All_exclude_jp';
SET @sql_alter = CONCAT('UPDATE project_kpis set ',b,'="',@kpi_value,'" where project_id=',@project_id);
PREPARE sql_str from @sql_alter;
EXECUTE sql_str;
end LOOP mycursor;
CLOSE mycursor;
SET done=0;
END LOOP projectcursor;
CLOSE projectcursor;
END //
DELIMITER ;
DELIMITER //
DROP PROCEDURE if EXISTS kpi_report_pro //
CREATE PROCEDURE kpi_report_pro()
BEGIN
DROP table if EXISTS project_info;
create table project_info as SELECT * from (SELECT
A.*, B.project_year PROJECT_YEAR
FROM
(
SELECT
id PROJECT_ID,
NAME PROJECT_NAME,
version VERSION
FROM
management_project
) A LEFT JOIN dc_task B on A.PROJECT_ID=B.project_id GROUP BY A.PROJECT_ID) C;
ALTER TABLE project_info ENGINE=MyISAM;
DROP table if EXISTS project_kpis;
call update_report();
select * from project_info t,project_kpis p where t.PROJECT_ID=p.project_id;
END //
DELIMITER ;
/* 调用存储进程，存储进程调用后产生两张表，project_kpis和project_info 履行完后会输出报表结果*/
CALL kpi_report_pro()
生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
