MySQL存储过程循环变量并插入临时表 [英] MySQL stored procedure loop through variables and insert into temporary table

问题描述

我有一个存储过程，它运行一个 select 语句，该语句返回一行并将内容插入到临时表中.select 语句在 where 子句中有 6 个条件语句，我基本上需要遍历 3 组附加条件并将这些结果插入到临时表中.这是我目前所拥有的:

I have a stored procedure that runs a select statement that returns one row and inserts the contents into a temporary table. The select statement has 6 conditional statements in a where clause and I essentially need to loop through 3 additional sets of criteria and insert those results into the temp table. Here is what I have so far:

CREATE DEFINER=`sleuser`@`%` PROCEDURE `CostDashboard`()
BEGIN

create temporary table TempTable (
ProjectID int, 
Phase varchar(100), 
OriginalCommitments float, 
ApprovedCommitmentChanges float, 
CurrentAssigned float, 
PendingScopeChanges float,
EAC float,
PercentComplete float
);

insert into TempTable(
SELECT project_id,
'FP' as Phase,
OriginalCommitments,
ApprovedCommitmentChanges,
OriginalCommitments+ApprovedCommitmentChanges as CurrentAssigned,
sum(ProjectCostBudget.PendingChangeOrders) as PendingScopeChanges

sum(ProjectCost.CurrentWorkCompleted) + 
sum(ProjectCost.EstimateToComplete) as EAC,

(sum(ProjectCost.CurrentWorkCompleted) + 
sum(ProjectCost.EstimateToComplete) / 
(sum(ProjectCostBudget.OriginalContractPrice + 
ProjectCostBudget.RegisteredChangeOrders))) as PercentComplete

FROM `RCLY-DEV`.project

inner join ImportCost on ImportCost.ProjectID = project.pmis
inner join ProjectCostBudget on ProjectCostBudget.ProjectID = 
project.project_id
inner join ProjectCost on ProjectCost.ProjectID = project.project_id

where ImportCost.ProjectID = 'RLCY-BB-01' 
and ImportCost.Task = "020.0000.000"  
and ProjectCostBudget.ProjectID = 2 
and ProjectCostBudget.ServiceNumber = "020.0000.000" 
and ProjectCost.MonthYear != '' 
and ProjectCost.MonthYear like 'July%2018'
);

select * from TempTable
;

END

这行得通，并在 where 子句中插入带有硬编码值的一条记录，但我需要为 3 组变量运行它，所以我创建了一个额外的临时表，如下所示:

This works and inserts the one record with hard coded values in the where clause, but I need to run it for 3 sets of variables, so I created an additional temporary table like this:

|ImpCostID|ImpCostTask |PCBID|PCBServNum  |MonthYear|
-----------------------------------------------------
|XXY-01-01|030.0000.000|3    |030.0000.000|July%2018|
|QWY-01-01|040.0000.000|4    |040.0000.000|May%2018 |
|ZXF-01-01|040.0000.000|5    |050.0000.000|June%2018|

但我不确定如何将这些值集分配给变量，然后循环遍历它们.有什么建议吗?

but I'm not sure how to assign these sets of values to variables and then loop through them. Any suggestions?

推荐答案

尽量避免 SQL 语句的序列化(可能代价高昂).在这种情况下，您可以简单地在实际查询中使用带有参数表的 JOIN.如果您只是使用临时表 TempTable 为您保存结果，那么您也不需要它，因为您可以在一个查询中获得所有结果:

Try to avoid serialization of SQL statements whenever possible (can be costly). In this case you can simply use a JOIN with the parameter table with your actual query. If you just used the temp table TempTable to hold the results for you, you do not need that either as you get all the results in one query:

CREATE DEFINER=`sleuser`@`%` PROCEDURE `CostDashboard`()
BEGIN

create temporary table query_params_tmp (
ImpCostID varchar(20),
ImpCostTask varchar(20),
PCBID int,
PCBServNum varchar(20),
MonthYear varchar(20)
);
insert into query_params_tmp values 
('XXY-01-01', '030.0000.000', 3, '030.0000.000', 'July%2018'),
('QWY-01-01', '040.0000.000', 4, '040.0000.000', 'May%2018'),
('ZXF-01-01', '040.0000.000', 5, '050.0000.000', 'June%2018');


SELECT project_id,
  'FP' as Phase,
  OriginalCommitments,
  ApprovedCommitmentChanges,
  OriginalCommitments+ApprovedCommitmentChanges as CurrentAssigned,
  sum(ProjectCostBudget.PendingChangeOrders) as PendingScopeChanges
  sum(ProjectCost.CurrentWorkCompleted) + 
  sum(ProjectCost.EstimateToComplete) as EAC,

  (sum(ProjectCost.CurrentWorkCompleted) + 
  sum(ProjectCost.EstimateToComplete) / 
  (sum(ProjectCostBudget.OriginalContractPrice + 
  ProjectCostBudget.RegisteredChangeOrders))) as PercentComplete
FROM `RCLY-DEV`.project
  join ImportCost on ImportCost.ProjectID = project.pmis
  join ProjectCostBudget on ProjectCostBudget.ProjectID = project.project_id
  join ProjectCost on ProjectCost.ProjectID = project.project_id
  join query_params_tmp qp on 
    qp.ProjectID = ImportCost.ProjectID and 
    qp.ImpCostTask = ImportCost.Task and
    qp.PCBID = ProjectCostBudget.ProjectID and
    ProjectCost.MonthYear like qp.MonthYear
GROUP BY OriginalCommitments, ApprovedCommitmentChanges;

END

这篇关于MySQL存储过程循环变量并插入临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！