MySQL儲存過程語句及呼叫

暖楓無敵發表於2018-11-30

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

mySQL語法中的儲存過程及if語句的使用簡例
2017-03-27
MySql儲存過程
mysql如何呼叫儲存過程
2021-09-11
MySql儲存過程
MySql儲存過程的作用及語法
2017-05-17
MySql儲存過程
用PHP呼叫MySQL儲存過程
2017-11-08
PHPMySql儲存過程
c / c + + 呼叫mysql儲存過程
2014-02-10
MySql儲存過程
mysql儲存過程及c#呼叫標準版
2009-07-02
MySql儲存過程C#
mysql 儲存過程，以及mybatis如何呼叫
2016-08-30
MySql儲存過程MyBatis
PHP呼叫MYSQL儲存過程例項
2016-01-20
PHPMySql儲存過程
mysql-定時呼叫儲存過程
2015-03-18
MySql儲存過程
呼叫儲存過程
2024-03-16
儲存過程
MySQL儲存過程詳解 mysql 儲存過程
2016-07-26
MySql儲存過程
Mysql儲存過程變數，條件，迴圈語句用法
2018-06-26
MySql儲存過程變數
asp.net分頁的SQL語句及儲存過程
2011-10-26
ASP.NETSQL儲存過程
oracle 儲存過程以及plsql語句塊
2016-04-13
Oracle儲存過程SQL
Oracle 儲存過程中的DDL語句
2011-10-10
Oracle儲存過程
mysql多次呼叫儲存過程的問題
2021-09-09
MySql儲存過程
Winform呼叫儲存過程
2022-03-09
ORM儲存過程
perl呼叫儲存過程
2008-09-04
儲存過程
jdbc呼叫儲存過程
2011-12-11
JDBC儲存過程
深入解讀MySQL InnoDB儲存引擎Update語句執行過程
2022-12-18
MySql儲存引擎
SQL 儲存過程裡呼叫另一個儲存過程
2021-04-03
SQL儲存過程
MySQL儲存過程詳解 mysql 儲存過程link
2012-07-07
MySql儲存過程
PostgreSQL獲取建表語句儲存過程
2021-06-07
SQL儲存過程
EF中使用SQL語句或儲存過程
2018-04-10
SQL儲存過程
Mysql 儲存過程
2017-05-26
MySql儲存過程
linux呼叫儲存過程
2022-06-19
Linux儲存過程
PB中呼叫儲存過程
2015-04-23
儲存過程
java 呼叫oracle 儲存過程
2012-11-03
JavaOracle儲存過程
php呼叫mssql儲存過程
2007-07-07
PHPSQL儲存過程
java中呼叫儲存過程
2012-08-20
Java儲存過程
在儲存過程A中呼叫儲存過程B的結果
2016-06-13
儲存過程
Entity Framework Code First執行SQL語句、檢視及儲存過程
2013-10-12
FrameworkSQL儲存過程
儲存過程語法與Java程式的呼叫方式
2013-05-23
儲存過程Java
儲存過程與 SQL Server語句大比拼
2008-07-03
儲存過程SQLServer
在.NET中用儲存過程執行SQL語句
2010-04-20
儲存過程SQL
Oracle儲存過程乾貨（二）：PLSQL控制語句
2024-03-06
Oracle儲存過程SQL
MySql儲存過程—2、第一個MySql儲存過程的建立
2014-04-19
MySql儲存過程
通過SQL語句提取儲存過程中的內容
2010-12-14
SQL儲存過程

MySQL儲存過程語句及呼叫

相關文章