將一張表中的數據作為列名的存儲過程
來源：程序員人生 發布時間：2015-03-25 11:04:16 閱讀次數：3315次
問題：在做項目的進程中遇到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()
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
