MySQL使用游标获取偶数行的列_MySQL中关于行转列的存储过程的写法(使用游标) | 学步园...

代码如下：

-- ----------------------------

-- Procedure structure for `sp_pro_rm_cs`

-- ----------------------------

DROP PROCEDURE IF EXISTS `sp_pro_rm_cs`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_pro_rm_cs`(in p_bookno varchar(64))

BEGIN

DECLARE v_sql VARCHAR(8000);

DECLARE v_rmname VARCHAR(20);

DECLARE v_rmcode VARCHAR(20);

DECLARE v_row INT(11);

DECLARE v_done INT(11);

DECLARE v_i INT(11);

DECLARE csr_rm CURSOR FOR

SELECT DISTINCT pr_rmname, pr_rmcode FROM v_pro_rm_cs_info WHERE pr_bookno = p_bookno;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

DROP TABLE IF EXISTS v_pro_rm_cs_info;

CREATE TEMPORARY TABLE v_pro_rm_cs_info (

pr_bookno varchar(20) DEFAULT NULL,

pr_rmname varchar(20) DEFAULT NULL,

pr_rmcode varchar(20) DEFAULT NULL,

pr_pname varchar(20) DEFAULT NULL,

pr_rmloss decimal(10,3) DEFAULT NULL,

pr_rmrate decimal(10,3) DEFAULT NULL

);

INSERT INTO v_pro_rm_cs_info (pr_bookno, pr_rmname, pr_rmcode, pr_pname, pr_rmloss, pr_rmrate)

SELECT t1.rb_book_no AS pr_bookno, t1.rb_rm_name AS pr_rmname, t1.rb_rm_code AS pr_rmcode,

t2.cs_pname AS pr_pname, t2.cs_loss AS pr_rmloss, t2.cs_rate AS pr_rmrate

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;

SET v_done = 0;

SET v_sql = 'select distinct t1.pr_pname, t2.pl_pwgt_sum AS pr_pwgt_s';

SET v_i = 1;

OPEN csr_rm;

csr_loop:loop

FETCH csr_rm INTO v_rmname, v_rmcode;

IF v_done THEN

LEAVE csr_loop;

END IF;

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"');

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"');

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"');

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"');

SET v_i = v_i + 1;

END LOOP;

CLOSE csr_rm;

SET v_sql = concat(v_sql, ' from v_pro_rm_cs_info t1 ');

SET v_sql = concat(v_sql, ' left join ( ');

SET v_sql = concat(v_sql, ' select distinct t3.pl_pname, SUM(t3.pl_pwgt_sum) as pl_pwgt_sum, t4.mb_bookno ');

SET v_sql = concat(v_sql, ' from t_packlist t3 inner join t_manual_book t4 ');

SET v_sql = concat(v_sql, ' on t3.pl_ex_date >= t4.mb_wo_stt and t3.pl_ex_date <= t4.mb_wo_end ');

SET v_sql = concat(v_sql, ' group by pl_pname ');

SET v_sql = concat(v_sql, ' ) t2 on t1.pr_bookno = t2.mb_bookno and t1.pr_pname = t2.pl_pname ');

SET v_sql = concat(v_sql, ' group by t1.pr_pname ');

SET @sql = v_sql;

PREPARE sl FROM @sql;

EXECUTE sl;

DEALLOCATE PREPARE sl;

/*

SELECT @sql;

*/

END

;;

DELIMITER ;

关于MySQL存储过程的相关资料：