MySQL中关于行转列的存储过程的写法(使用游标)

最新推荐文章于 2024-07-22 03:08:20 发布

杰锋

最新推荐文章于 2024-07-22 03:08:20 发布

阅读量715

点赞数

分类专栏：数据库与缓存文章标签： mysql 存储 sql null join structure

数据库与缓存专栏收录该内容

77 篇文章

订阅专栏

本文深入解析了MySQL存储过程`sp_pro_rm_cs`的实现细节，包括其结构、参数使用及核心逻辑。通过具体代码实例，详细阐述了如何构建并执行该存储过程，以实现特定业务需求。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- ----------------------------
-- 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存储过程的相关资料：

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