A stored procedure that takes data from a table as a column name

Problem: In the process of doing the project encountered a person problem: that is, the value of the Deco field of Table A is presented as a column of table B, and the data of these values is taken from the C table

Method: Originally wanted to use, a view to list the values in the Deco field and the data in the C table, but found that the association of a few tables is more complex (for me)

The use of simple SELECT statement, is not able to get the desired effect, in the online search for relevant information, found that can be used cursors query results in the set of each

Record to process, so I wrote the following stored procedure, the stored procedure will create two tables, the simple association of the two tables can get the data set I want.

Code:

/* CREATE PROCEDURE */delimiter//drop PROCEDURE IF EXISTS update_report//create PROCEDURE update_report () BEGIN DECLARE done INT DEF    Ault 0;declare sql_alter varchar (n) Default ';D eclare sql_str varchar (") default '; DECLARE a varchar ($) Default ';D eclare B varchar ($) Default ';D eclare C VARCHAR ($) Default ';D eclare kpi_value VA    Rchar (+) DEFAULT '; DECLARE mycursor cursor for select ID, name from dc_formula WHERE important=1;declare projectcursor cursor for select ID F    Rom management_project; DECLARE CONTINUE HANDLER for not FOUND SET done=1;set @sql_alter = ' CREATE TABLE project_kpis (id int (one-by-one) not NULL PRIMARY K EY auto_increment,project_id VARCHAR (+) not NULL) Engine=myisam DEFAULT Charset=utf8 '; PREPARE sql_str from @sql_alter;    EXECUTE Sql_str;    OPEN MyCursor;        REPEAT FETCH MyCursor into a a a B; IF not done Thenset 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:loopfetch projectcursor to c;if done = 1 Thenleave projectcursor;  END If;insert into Project_kpis (project_id) values (c); open mycursor;     Mycursor:loop FETCH MyCursor into a a a B;     IF do = 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, value is NULL and then ' else value END Val-@kpi_value from V_dc_project Report WHERE [email protected]_id and [email protected]_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;D elimiter//drop PROCEDURE if EXISTS kpi_report_pro//create PROCEDURE kpi_report_pro () begindrop table if EXISTS Project_info;create table Project_info as SELECT * FROM (selecta.*, B.project_year project_yearfrom (Selectid PROJECT_ID, NAME project_name,version versionfrommanagement_project) A left JOIN dc_task B in a.project_id=b.project_id GROUP by A.PR oject_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;/* Call the stored procedure, generate two tables after the stored procedure call, Project_kpis and Project_info output report results after execution */call Kpi_report_pro ()

A stored procedure that takes data from a table as a column name