Writing a row-to-column stored procedure in MySQL (using a cursor)

The Code is as follows:

-- -------------------------- <Br/> -- procedure structure for 'SP _ pro_rm_cs '<br/> -- -------------------------- <br/> drop procedure if exists 'SP _ pro_rm_cs '; <br/> delimiter; <br/> Create definer = 'root' @ 'localhost' procedure 'SP _ pro_rm_cs '(in p_bookno varchar (64 )) <br/> begin <br/> declare v_ SQL varchar (8000); <br/> declare v_rmname varchar (20); <br/> declare v_rmcode varchar (20 ); <br/> declare v_row int (11); <br/> declare v_done int (11); <br/> declare V_ I int (11 ); </P> <p> declare csr_rm cursor for <br/> select distinct pr_rmname, pr_rmcode from v_pro_rm_cs_info where pr_bookno = p_bookno; <br/> declare continue handler for not found set v_done = 1; </P> <p> drop table if exists v_pro_rm_cs_info; <br/> create temporary table v_pro_rm_cs_info (<br/> pr_bookno varchar (20) default null, <br/> pr_rmname varchar (20) default null, <br/> pr_rmcode varchar (20) default null, <br/> pr_pname varchar (20) default null, <br/> pr_rmloss decimal (10, 3) default null, <br/> pr_rmrate decimal (10, 3) default null <br/>); </P> <p> insert into ignore (pr_bookno, pr_rmname, pr_rmcode, pr_pname, pr_rmloss, pr_rmrate) <br/> select t1.rb _ book_no as pr_bookno, t1.rb _ rm_name as pr_rmname, t1.rb _ rm_code as pr_rmcode, <br/> t2.cs _ pname as pr_pname, t2.cs _ loss as comment, t2.cs _ rate as pr_rmrate <br/> from t_rm_backup T1 left join m_consumption T2 on t1.rb _ rm_name = t2.cs _ rmname and t1.rb _ rm_code = t2.cs _ rmcode; </P> <p> set v_done = 0; <br/> set v_ SQL = 'select distinct t1.pr _ pname, t2.pl _ pwgt_sum as pr_pwgt_s '; </P> <p> set V_ I = 1; <br/> open csr_rm; <br/> csr_loop: loop <br/> fetch csr_rm into v_rmname, v_rmcode; <br/> If v_done then <br/> Leave csr_loop; <br/> end if; <br/> set v_ SQL = Concat (v_ SQL ,', sum (case when t1.pr _ rmname = "', v_rmname,'" And t1.pr _ rmcode = "', v_rmcode,'" Then t1.pr _ rmloss else null end )"', 'pr _ ', V_ I, 'l "'); <br/> set v_ SQL = Concat (v_ SQL, ', sum (case when t1.pr _ rmname ="', v_rmname, '"And t1.pr _ rmcode ="', v_rmcode, '"Then t1.pr _ rmrate else null end)"', 'pr _ ', V_ I, 'r "'); </P> <p> set v_ SQL = Concat (v_ SQL, ', sum (case when t1.pr _ rmname = "', v_rmname, '" And t1.pr _ rmcode = "', v_rmcode, '"Then round (t2.pl _ pwgt_sum * t1.pr _ rmloss, 2) else null end)"', 'pr _ ', V_ I, 'rl "'); <br/> set v_ SQL = Concat (v_ SQL, ', sum (case when t1.pr _ rmname = "', v_rmname, '" And t1.pr _ rmcode = "', v_rmcode, '"Then round (t2.pl _ pwgt_sum * t1.pr _ rmloss * t1.pr _ rmrate)/(1-t1.pr _ rmrate), 2) else null end )"', 'pr _ ', V_ I, 'rr "'); </P> <p> set V_ I = V_ I + 1; <br/> end loop; </P> <p> close csr_rm; </P> <p> set v_ SQL = Concat (v_ SQL, 'From v_pro_rm_cs_info t1 '); </P> <p> set v_ SQL = Concat (v_ SQL, 'left join ('); <br/> set v_ SQL = Concat (v_ SQL, 'select distinct t3.pl _ pname, sum (t3.pl _ pwgt_sum) as pl_pwgt_sum, t4.mb _ bookno'); <br/> set v_ SQL = Concat (v_ SQL, 'From t_packlist T3 inner join t_manual_book T4 '); <br/> set v_ SQL = Concat (v_ SQL, 'on t3.pl _ ex_date> = t4.mb _ wo_stt and t3.pl _ ex_date <= t4.mb _ wo_end '); <br/> set v_ SQL = Concat (v_ SQL, 'Group by pl_pname'); <br/> set v_ SQL = Concat (v_ SQL ,') t2 on t1.pr _ bookno = t2.mb _ bookno and t1.pr _ pname = t2.pl _ pname '); </P> <p> set v_ SQL = Concat (v_ SQL, 'Group by t1.pr _ pname'); </P> <p> set @ SQL = v_ SQL; </P> <p> prepare SL from @ SQL; <br/> execute SL; <br/> deallocate prepare SL; <br/>/* <br/> select @ SQL; <br/> */<br/> end <br/>; <br/> delimiter;

 

Related information about the MySQL stored procedure:

Http://www.cnblogs.com/xnxylf/archive/2009/09/21/1571195.html

Http://www.cublog.cn/u/14321/showart_153404.html

Http://www.cnblogs.com/hsqzzzl/archive/2008/02/21/1076646.html

Http://www.ccvita.com/100.html

Http://www.php100.com/html/webkaifa/database/Mysql/2009/0418/1185.html