MySQL存储过程语句及调用

栏目: 数据库 · 发布时间: 5年前

内容简介：存储过程调用方式：CALL Pro_Get_CO2('2018','','','');
BEGIN
/*
存储过程名称：Pro_Get_Carbon_Climate
存储过程功能说明：不同气候区碳排放占比
创建日期：
涉及的表或试图：
表或试图名称                  用途
存储过程调用：
存储过程名称                  用途
NONE                          
参数说明：
传入参数：
参数名                        		说明
ivF_Year                     			年度
返回字段说明：

调用示例：
CALL Pro_Get_Carbon_Climate(ivF_Year);
CALL Pro_Get_Carbon_Climate('2018');

*/
declare SQL1 varchar(5000);
declare SQL2 varchar(5000);
declare SQLTotal varchar(5000);
declare SQLQuery varchar(5000);
	-- 一次能源碳排放总量
	SET SQL1='select t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year, 
				sum(t2.F_Value*t3.F_NCV*t3.F_EF) as TPF_HL,sum(t2.F_Value*t3.F_Formular) as ZBM
				FROM t_organization t1
				join t_energy_data t2 ON t1.F_ID = t2.F_OrgID 
				join v_zjpflist t3 on t2.F_FieldID=t3.F_FieldID
				where t3.F_IsSum=1  ';
	SET SQL1 = CONCAT(SQL1,' and t2.F_Year= ', ivF_Year);		
	-- 二次能源碳排放总量
	SET SQL2='select t1.F_ID, t2.F_Year,
			sum(CASE WHEN t3.F_CalcType = 2 THEN(t2.F_Value * t3.F_EF)END )  as TPF_DL,
			sum(CASE WHEN t3.F_CalcType = 3 THEN(t2.F_Value * t3.F_EF) END )  as TPF_RL,
			sum(t2.F_Value*t3.F_Formular) as ZBM
			FROM t_organization t1
			join t_energy_data t2 ON t1.F_ID = t2.F_OrgID
			join v_jjpflist t3 on t2.F_FieldID=t3.F_FieldID AND t1.F_ID =T3.F_OrgID
			where t3.F_IsSum=1  ';
	SET SQL2 = CONCAT(SQL2,' and t2.F_Year=', ivF_Year);	

  	SET SQL1 = CONCAT(SQL1,' group by t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year ');
  	SET SQL2 = CONCAT(SQL2,' group by t1.F_ID, t2.F_Year ');

  	#SELECT SQL1;# 输出拼接 sql 语句,相当于print
	#SELECT SQL2;# 输出拼接sql语句,相当于print
  	SET SQLTotal='select AA.F_Climate, SUM(AA.F_BuildArea) AS F_BuildArea, SUM(AA.F_EnergyPerson) AS F_EnergyPerson,
  					SUM(AA.ZBM + BB.ZBM) AS F_TotalZBM,
					SUM(AA.TPF_HL + BB.TPF_DL + BB.TPF_RL) AS F_TotalTPF,
					SUM(AA.TPF_HL) AS TPF_HL,
					SUM(BB.TPF_DL) AS TPF_DL,
					SUM(BB.TPF_RL) AS TPF_RL ';		
	SET SQLTotal = CONCAT(SQLTotal,' from (', SQL1,') AA JOIN (',SQL2,') BB ON AA.F_ID=BB.F_ID  AND AA.F_Year=BB.F_Year ');		
	SET SQLTotal = CONCAT(SQLTotal,' GROUP BY AA.F_Climate ');
	#SELECT SQLTotal;
	SET SQLQuery ='select BBB.F_Climate, BBB.F_Name, AAA.F_TotalTPF ';
	SET SQLQuery = CONCAT(SQLQuery,' from (select a.F_Code as F_Climate ,a.F_Name,a.F_Order from t_dt_datadict a where a.F_DictType=2) BBB LEFT JOIN (',SQLTotal,') AAA ON AAA.F_Climate=BBB.F_Climate ');
	SET SQLQuery = CONCAT(SQLQuery,' ORDER BY BBB.F_Order ');
	#SELECT SQLQuery;
   SET @SQLQuery = SQLQuery;
  	prepare s1  from @SQLQuery;  
  	EXECUTE s1 ;  
 	deallocate prepare s1 ; 
					
END
BEGIN
/*
存储过程名称：Pro_Get_CO2
存储过程功能说明：不同类型机构碳排放占比
创建日期：
涉及的表或试图：
表或试图名称                  用途
存储过程调用：
存储过程名称                  用途
NONE                          
参数说明：
传入参数：
参数名                        		说明
ivF_Year                     			年度
返回字段说明：

调用示例：
CALL Pro_Get_Carbon_OrgType(ivF_Year);
CALL Pro_Get_Carbon_OrgType('2018');

*/
declare SQL1 varchar(5000);
declare SQL2 varchar(5000);
declare SQLTotal varchar(5000);
declare SQLQuery varchar(5000);
	-- 一次能源碳排放总量
	SET SQL1='select t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year, 
				sum(t2.F_Value*t3.F_NCV*t3.F_EF) as TPF_HL,sum(t2.F_Value*t3.F_Formular) as ZBM
				FROM t_organization t1
				join t_energy_data t2 ON t1.F_ID = t2.F_OrgID 
				join v_zjpflist t3 on t2.F_FieldID=t3.F_FieldID
				where t3.F_IsSum=1  ';
	SET SQL1 = CONCAT(SQL1,' and t2.F_Year= ', ivF_Year);		
	-- 二次能源碳排放总量
	SET SQL2='select t1.F_ID, t2.F_Year,
			sum(CASE WHEN t3.F_CalcType = 2 THEN(t2.F_Value * t3.F_EF)END )  as TPF_DL,
			sum(CASE WHEN t3.F_CalcType = 3 THEN(t2.F_Value * t3.F_EF) END )  as TPF_RL,
			sum(t2.F_Value*t3.F_Formular) as ZBM
			FROM t_organization t1
			join t_energy_data t2 ON t1.F_ID = t2.F_OrgID
			join v_jjpflist t3 on t2.F_FieldID=t3.F_FieldID AND t1.F_ID =T3.F_OrgID
			where t3.F_IsSum=1  ';
	SET SQL2 = CONCAT(SQL2,' and t2.F_Year=', ivF_Year);	

  	SET SQL1 = CONCAT(SQL1,' group by t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year ');
  	SET SQL2 = CONCAT(SQL2,' group by t1.F_ID, t2.F_Year ');

  	#SELECT SQL1;# 输出拼接sql语句,相当于print
	#SELECT SQL2;# 输出拼接sql语句,相当于print
  	SET SQLTotal='select AA.F_OrgType, SUM(AA.F_BuildArea) AS F_BuildArea, SUM(AA.F_EnergyPerson) AS F_EnergyPerson,
  					SUM(AA.ZBM + BB.ZBM) AS F_TotalZBM,
					SUM(AA.TPF_HL + BB.TPF_DL + BB.TPF_RL) AS F_TotalTPF,
					SUM(AA.TPF_HL) AS TPF_HL,
					SUM(BB.TPF_DL) AS TPF_DL,
					SUM(BB.TPF_RL) AS TPF_RL ';		
	SET SQLTotal = CONCAT(SQLTotal,' from (', SQL1,') AA JOIN (',SQL2,') BB ON AA.F_ID=BB.F_ID  AND AA.F_Year=BB.F_Year ');		
	SET SQLTotal = CONCAT(SQLTotal,' GROUP BY AA.F_OrgType ');
	#SELECT SQLTotal;
	SET SQLQuery ='select BBB.F_OrgType, BBB.F_Name, AAA.F_TotalTPF ';
	SET SQLQuery = CONCAT(SQLQuery,' from (select a.F_Code as F_OrgType ,a.F_Name,a.F_Order from t_dt_datadict a where a.F_DictType=1) BBB LEFT JOIN (',SQLTotal,') AAA ON AAA.F_OrgType=BBB.F_OrgType ');
	SET SQLQuery = CONCAT(SQLQuery,' ORDER BY BBB.F_Order ');
	#SELECT SQLQuery;
   SET @SQLQuery = SQLQuery;
  	prepare s1  from @SQLQuery;  
  	EXECUTE s1 ;  
 	deallocate prepare s1 ; 
					
END
BEGIN
/*
存储过程名称：Pro_Get_CO2
存储过程功能说明：获得年度统计分析(碳排放，折标煤)
创建日期：
涉及的表或试图：
表或试图名称                  用途
存储过程调用：
存储过程名称                  用途
NONE                          
参数说明：
传入参数：
参数名                        		说明
F_Year                     			年度
F_ProvinceCode                      地区
F_Climate                   			气候区
F_OrgType                        	机构类型
返回字段说明：

调用示例：
CALL Pro_Get_CO2(F_Year,F_ProvinceCode,F_Climate,F_OrgType);
CALL Pro_Get_CO2('2018','','','');

*/

declare SQL1 varchar(5000);
declare SQL2 varchar(5000);
declare SQLTotal varchar(5000);
-- 一次能源碳排放总量
SET SQL1='select t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year, 
				sum(t2.F_Value*t3.F_NCV*t3.F_EF) as TPF_HL,sum(t2.F_Value*t3.F_Formular) as ZBM
				FROM t_organization t1
				join t_energy_data t2 ON t1.F_ID = t2.F_OrgID 
				join v_zjpflist t3 on t2.F_FieldID=t3.F_FieldID
				where t3.F_IsSum=1  ';

-- 二次能源碳排放总量
SET SQL2='select t1.F_ID, t2.F_Year,
			sum(CASE WHEN t3.F_CalcType = 2 THEN(t2.F_Value * t3.F_EF)END )  as TPF_DL,
			sum(CASE WHEN t3.F_CalcType = 3 THEN(t2.F_Value * t3.F_EF) END )  as TPF_RL,
			sum(t2.F_Value*t3.F_Formular) as ZBM
			FROM t_organization t1
			join t_energy_data t2 ON t1.F_ID = t2.F_OrgID
			join v_jjpflist t3 on t2.F_FieldID=t3.F_FieldID AND t1.F_ID =T3.F_OrgID
			where t3.F_IsSum=1  ';
			
  IF F_Year IS NOT NULL AND F_Year <> '' THEN 
    SET SQL1 = CONCAT(SQL1,' and t2.F_Year=', F_Year);
    SET SQL2 = CONCAT(SQL2,' and t2.F_Year=', F_Year);
  END IF;
  
  IF F_ProvinceCode IS NOT NULL AND F_ProvinceCode <> '' THEN   
    SET SQL1 = CONCAT(SQL1,' and t1.F_ProvinceCode=''', F_ProvinceCode,''' ');
    SET SQL2 = CONCAT(SQL2,' and t1.F_ProvinceCode=''', F_ProvinceCode,''' ');
  END IF;
  IF F_Climate IS NOT NULL AND F_Climate <> '' THEN 
    SET SQL1 = CONCAT(SQL1,' and t1.F_Climate=''', F_Climate,''' ');
    SET SQL2 = CONCAT(SQL2,' and t1.F_Climate=''', F_Climate,''' ');
  END IF;
  
  IF F_OrgType IS NOT NULL AND F_OrgType <> '' THEN 
    SET SQL1 = CONCAT(SQL1,' and t1.F_OrgType=''', F_OrgType,''' ');
    SET SQL2 = CONCAT(SQL2,' and t1.F_OrgType=''', F_OrgType,''' ');
  END IF; 

  SET SQL1 = CONCAT(SQL1,' group by t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year ');
  SET SQL2 = CONCAT(SQL2,' group by t1.F_ID, t2.F_Year ');

  #SELECT SQL1;# 输出拼接sql语句,相当于print
  #SELECT SQL2;# 输出拼接sql语句,相当于print
  
  SET SQLTotal='select AA.F_OrgName, AA.F_ProvinceCode,AA.F_Climate, AA.F_OrgType, AA.F_BuildArea, AA.F_EnergyPerson, AA.F_Year, 
						AA.ZBM+BB.ZBM AS F_TotalZBM,
						CASE WHEN AA.F_BuildArea=0 THEN 0 ELSE (AA.ZBM+BB.ZBM )/AA.F_BuildArea END AS F_PerAreaZBM,
						CASE WHEN AA.F_EnergyPerson=0 THEN 0 ELSE (AA.ZBM+BB.ZBM )/AA.F_EnergyPerson END AS F_PerPersonZBM,
						AA.TPF_HL+BB.TPF_DL+BB.TPF_RL AS F_TotalTPF,AA.TPF_HL,BB.TPF_DL,BB.TPF_RL,
						CASE WHEN AA.F_BuildArea=0 THEN 0 ELSE (AA.TPF_HL+BB.TPF_DL+BB.TPF_RL )/AA.F_BuildArea END AS F_PerAreaTPF,
						CASE WHEN AA.F_EnergyPerson=0 THEN 0 ELSE (AA.TPF_HL+BB.TPF_DL+BB.TPF_RL )/AA.F_EnergyPerson END AS F_PerPersonTPF ';		
	SET SQLTotal = CONCAT(SQLTotal,' from (', SQL1,') AA JOIN (',SQL2,') BB ON AA.F_ID=BB.F_ID  AND AA.F_Year=BB.F_Year ');		
	SET SQLTotal = CONCAT(SQLTotal,' ORDER BY AA.F_ID,AA.F_Year ');

	#SELECT SQLTotal;# 输出拼接sql语句,相当于print						
  	SET @SQLTotal = SQLTotal;
  	prepare s1  from @SQLTotal;  
  	EXECUTE s1 ;  
 	deallocate prepare s1 ; 
END
BEGIN
/*
存储过程名称：Pro_Get_CO2
存储过程功能说明：获得近5年的能耗数据，碳排放数据
创建日期：
涉及的表或试图：
表或试图名称                  用途
存储过程调用：
存储过程名称                  用途
NONE                          
参数说明：
传入参数：
参数名                        		说明
ivF_Year                     			年度
返回字段说明：

调用示例：
CALL Pro_Get_EnergyData(ivF_Year);
CALL Pro_Get_EnergyData('2018');

*/
declare SQL0 varchar(5000);
declare SQL1 varchar(5000);
declare SQL2 varchar(5000);
declare SQLTotal varchar(5000);
declare SQLQuery varchar(5000);
	-- 构造近5年列表
	SET SQL0=CONCAT('SELECT @cdate :=@cdate-1 AS F_Year FROM (SELECT @cdate := ',ivF_Year+1,' FROM at_sys_errorlog LIMIT 5 ) t4');
	-- 一次能源碳排放总量
	SET SQL1='select t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year, 
				sum(t2.F_Value*t3.F_NCV*t3.F_EF) as TPF_HL,sum(t2.F_Value*t3.F_Formular) as ZBM
				FROM t_organization t1
				join t_energy_data t2 ON t1.F_ID = t2.F_OrgID 
				join v_zjpflist t3 on t2.F_FieldID=t3.F_FieldID
				where t3.F_IsSum=1  ';
	SET SQL1 = CONCAT(SQL1,' and t2.F_Year>= ', ivF_Year-4,' and t2.F_Year<= ', ivF_Year);		
	-- 二次能源碳排放总量
	SET SQL2='select t1.F_ID, t2.F_Year,
			sum(CASE WHEN t3.F_CalcType = 2 THEN(t2.F_Value * t3.F_EF)END )  as TPF_DL,
			sum(CASE WHEN t3.F_CalcType = 3 THEN(t2.F_Value * t3.F_EF) END )  as TPF_RL,
			sum(t2.F_Value*t3.F_Formular) as ZBM
			FROM t_organization t1
			join t_energy_data t2 ON t1.F_ID = t2.F_OrgID
			join v_jjpflist t3 on t2.F_FieldID=t3.F_FieldID AND t1.F_ID =T3.F_OrgID
			where t3.F_IsSum=1  ';
	SET SQL2 = CONCAT(SQL2,' and t2.F_Year>= ', ivF_Year-4,' and t2.F_Year<= ', ivF_Year);	

  	SET SQL1 = CONCAT(SQL1,' group by t1.F_ID, t1.F_OrgName, t1.F_ProvinceCode, t1.F_CityCode, t1.F_Climate, t1.F_OrgType, t1.F_BuildArea, t1.F_EnergyPerson, t2.F_Year ');
  	SET SQL2 = CONCAT(SQL2,' group by t1.F_ID, t2.F_Year ');

  	#SELECT SQL1;# 输出拼接sql语句,相当于print
	#SELECT SQL2;# 输出拼接sql语句,相当于print
  	SET SQLTotal='select AA.F_Year, SUM(AA.F_BuildArea) AS F_BuildArea, SUM(AA.F_EnergyPerson) AS F_EnergyPerson,
  					SUM(AA.ZBM + BB.ZBM) AS F_TotalZBM,
					SUM(AA.TPF_HL + BB.TPF_DL + BB.TPF_RL) AS F_TotalTPF,
					SUM(AA.TPF_HL) AS TPF_HL,
					SUM(BB.TPF_DL) AS TPF_DL,
					SUM(BB.TPF_RL) AS TPF_RL ';		
	SET SQLTotal = CONCAT(SQLTotal,' from (', SQL1,') AA JOIN (',SQL2,') BB ON AA.F_ID=BB.F_ID  AND AA.F_Year=BB.F_Year ');		
	SET SQLTotal = CONCAT(SQLTotal,' GROUP BY AA.F_Year ');
	#SELECT SQLTotal;
	SET SQLQuery ='select BBB.F_Year, AAA.F_BuildArea, AAA.F_EnergyPerson,AAA.F_TotalZBM,
					CASE WHEN AAA.F_BuildArea=0 THEN 0 ELSE AAA.F_TotalZBM/AAA.F_BuildArea END AS F_PerAreaZBM,
					CASE WHEN AAA.F_EnergyPerson=0 THEN 0 ELSE AAA.F_TotalZBM/AAA.F_EnergyPerson END AS F_PerPersonZBM,
					AAA.F_TotalTPF,
					CASE WHEN AAA.F_BuildArea=0 THEN 0 ELSE AAA.F_TotalTPF/AAA.F_BuildArea END AS F_PerAreaTPF,
					CASE WHEN AAA.F_EnergyPerson=0 THEN 0 ELSE AAA.F_TotalTPF/AAA.F_EnergyPerson END AS F_PerPersonTPF ';
	SET SQLQuery = CONCAT(SQLQuery,' from (', SQL0,') BBB LEFT JOIN (',SQLTotal,') AAA ON AAA.F_Year=BBB.F_Year ');
	SET SQLQuery = CONCAT(SQLQuery,' ORDER BY BBB.F_Year ');
	#SELECT SQLQuery;
   SET @SQLQuery = SQLQuery;
  	prepare s1  from @SQLQuery;  
  	EXECUTE s1 ;  
 	deallocate prepare s1 ; 
					
END

存储过程调用方式：

CALL Pro_Get_CO2('2018','','','');
CALL Pro_Get_EnergyData('2017');
CALL Pro_Get_Carbon_OrgType('2014');
CALL Pro_Get_Carbon_Climate('2014');


以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍
Web ReDesign 2.0

Web ReDesign 2.0

Kelly Goto、Emily Cotler / Peachpit Press / 2004-12-10 / USD 45.00

If anything, this volume's premise--that the business of Web design is one of constant change-has only proven truer over time. So much so, in fact, that the 12-month design cycles cited in the last ed......一起来看看 《Web ReDesign 2.0》 这本书的介绍吧!

码农工具
JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

RGB转16进制工具
RGB转16进制工具

RGB HEX 互转工具

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具

  • New
  • 文章
  • 话题
  • 教程
    • 关注 码农网 公众号
    版权所有，保留一切权利！© 2018-2024 码农网 粤ICP备17054400号-3