如何将两个过程合而为一来填充一个表，而不是两个过程中的每一个都填充其自己的表? [英] How can I combine two procedures in one to populate one table rather than each of the two procedures populating it's own table?
问题描述
我使用Sequel Pro在MySQL中创建了两个表，每个表中都填充了不同的过程.尽管每个表在运行各自的过程之后都具有正确的信息，但我认为，如果我将一些表合并得更多，那么我的数据将不再是分散的表.
I created two tables that were each populated with a different procedure in MySQL with Sequel Pro. While each table has the correct information in it after running the respective procedure, I'm thinking that my data will be less scattered tables if I consolidate some of the tables more.
因此，我想做的就是将两个表中的数据合并到一个表中.下面是我用来尝试完成此操作的代码.不幸的是，它没有用，对代码的任何帮助将不胜感激.
So, what I would like to do is combine the data from both tables into one. Below is the code I used to try to accomplish this. Unfortunately, it didn't work, and any help with the code would be much appreciated.
创建表代码:
-- Table: ip_ER_ERA_subtotal
-- DROP TABLE ip_ER_ERA_subtotal;
CREATE TABLE ip_ER_ERA_subtotal
(
Starting_Pitcher VARCHAR(8) NOT NULL,
Game_Date VARCHAR (10) NOT NULL,
Game_Number VARCHAR (1) NOT NULL,
innings_pitched double,
ER double,
ip_total double DEFAULT '0.0',
ER_total double DEFAULT '0.0',
ERA double DEFAULT '0.0',
CONSTRAINT ip_ER_ERA_subtotal_pk
PRIMARY KEY (Starting_Pitcher, Game_Date , Game_Number)
) ENGINE=InnoDB
程序代码:
DELIMITER $$
CREATE PROCEDURE accumulate_IP_ER()
BEGIN
DECLARE pit_id CHAR(10);
DECLARE gdate DATE;
DECLARE seq INT;
DECLARE in_pit REAL;
DECLARE earned_runs REAL;
DECLARE accum REAL;
DECLARE prev_year YEAR(4);
DECLARE end_of_cursor BOOLEAN;
DECLARE c1 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched
FROM ip_ER_subtotal
ORDER BY Starting_Pitcher, Game_Date, Game_Number;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
TRUNCATE TABLE ip_ER_subtotal;
INSERT INTO ip_ER_subtotal
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, 0.0
FROM starting_pitchers_game_log;
SET prev_year := 0;
OPEN c1;
fetch_loop: LOOP
FETCH c1 INTO pit_id, gdate, seq, in_pit;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
IF YEAR(gdate) != prev_year THEN
SET accum := 0.0;
SET prev_year := YEAR(gdate);
END IF;
SET accum := accum + in_pit;
UPDATE ip_ER_subtotal
SET ip_total = accum
WHERE Starting_Pitcher = pit_id
AND Game_Date = gdate
AND Game_Number = seq;
END LOOP;
CLOSE c1;
DECLARE c2 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, earned_runs
FROM ip_ER_subtotal
ORDER BY Starting_Pitcher, Game_Date, Game_Number;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
TRUNCATE TABLE ER_subtotal;
INSERT INTO ip_ER_subtotal
SELECT Starting_Pitcher, Game_Date, Game_Number, ER, 0.0
FROM starting_pitchers_game_log;
SET prev_year := 0;
OPEN c2;
fetch_loop: LOOP
FETCH c2 INTO pit_id, gdate, seq, earned_runs;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
IF YEAR(gdate) != prev_year THEN
SET accum := 0.0;
SET prev_year := YEAR(gdate);
END IF;
SET accum := accum + earned_runs;
UPDATE ip_ER_subtotal
SET ER_total = accum
WHERE Starting_Pitcher = pit_id
AND Game_Date = gdate
AND Game_Number = seq;
END LOOP;
CLOSE c2;
END
我收到以下错误:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE c2 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, e' at line 46
这是我已经创建的第一个表"ip_subtotal"的屏幕截图
Here is a screenshot of the first table I already created "ip_subtotal"
这是我已经创建的第二张表"ER_subtotal"的屏幕截图
Here is a screenshot of the second table I already created "ER_subtotal"
更新:
本质上，这是您对我进行较小更改的相同代码:
Here is essentially your same code that I made minor changes to:
DELIMITER $$
CREATE PROCEDURE accumulate_IP_ER_ERA()
BEGIN
DECLARE pit_id VARCHAR(8);
DECLARE gdate VARCHAR(10);
DECLARE seq VARCHAR(1);
DECLARE in_pit REAL;
DECLARE earned_runs INT;
DECLARE accum_ip REAL;
DECLARE accum_er INT;
DECLARE earned_run_avg REAL;
DECLARE prev_year YEAR(4);
DECLARE end_of_cursor BOOLEAN;
DECLARE c1 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER
FROM ip_ER_ERA_subtotal
ORDER BY Starting_Pitcher, Game_Date, Game_Number;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
TRUNCATE TABLE ip_ER_ERA_subtotal;
INSERT INTO ip_ER_ERA_subtotal
(Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER)
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER
FROM starting_pitcher_game_log;
SET end_of_cursor := FALSE;
SET prev_year := 0;
OPEN c1;
fetch_loop: LOOP
FETCH c1 INTO pit_id, gdate, seq, in_pit, earned_runs, accum_ip, accum_er, earned_run_avg;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
IF YEAR(gdate) != prev_year THEN
SET accum_ip := 0.0;
SET accum_er := 0;
SET prev_year := YEAR(gdate);
END IF;
SET accum_ip := accum_ip + in_pit;
SET accum_er := accum_er + ER;
SET earned_run_avg := (accum_er / accum_ip) * 9;
UPDATE ip_ER_ERA_subtotal
SET ip_total = accum_ip,
ER_total = accum_er,
STD_ERA = earned_run_avg
WHERE Starting_Pitcher = pit_id
AND Game_Date = gdate
AND Game_Number = seq
AND prev_year=YEAR;
END LOOP;
CLOSE c1;
END
这是错误:
Incorrect number of FETCH variables
我确实尝试查看是否没有获取任何变量，并尝试添加"accum_ip"，"accum_er"，"earned_run_avg"，但是它没有用...似乎八个变量是变量则fetch语句中的后三个+应该是所需的...
I did try to see what if any variables aren't being fetched and tried to add "accum_ip", "accum_er", "earned_run_avg" but it didn't work...It seems like the eight variables are the ones that the latter three + ones in your fetch statement should be the ones needed...
这是表格代码:
-- Table: ip_ER_ERA_subtotal
-- DROP TABLE ip_ER_ERA_subtotal;
CREATE TABLE ip_ER_ERA_subtotal
(
Starting_Pitcher VARCHAR(8) NOT NULL,
Game_Date VARCHAR(10) NOT NULL,
Game_Number INT(1) NOT NULL,
innings_pitched double,
ER double,
ip_total double DEFAULT '0.0',
ER_total double DEFAULT '0',
STD_ERA double DEFAULT '0.0',
CONSTRAINT ip_ER_ERA_subtotal_pk
PRIMARY KEY (Starting_Pitcher, Game_Date , Game_Number)
) ENGINE=InnoDB
更新:
这是基于您的更改的代码，但是ER，ER_total和STD_ERA列仅填充了"Null"值.
Here's the code based on your changes, but columns ER, ER_total, and STD_ERA are filled with only "Null" values.
DELIMITER $$
CREATE PROCEDURE accumulate_ip_ER_ERA()
BEGIN
DECLARE pit_id VARCHAR(8);
DECLARE gdate VARCHAR(10);
DECLARE seq VARCHAR(1);
DECLARE in_pit REAL;
DECLARE ER REAL;
DECLARE accum_ip REAL;
DECLARE accum_er REAL;
DECLARE earned_run_avg REAL;
DECLARE prev_year YEAR(4);
DECLARE end_of_cursor BOOLEAN;
DECLARE c1 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER
FROM ip_ER_ERA_subtotal
ORDER BY Starting_Pitcher, Game_Date, Game_Number;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
TRUNCATE TABLE ip_ER_ERA_subtotal;
INSERT INTO ip_ER_ERA_subtotal
(Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER)
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER
FROM starting_pitcher_game_log;
SET end_of_cursor := FALSE;
SET prev_year := 0;
OPEN c1;
fetch_loop: LOOP
FETCH c1 INTO pit_id, gdate, seq, in_pit, ER;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
IF YEAR(gdate) != prev_year THEN
SET accum_ip := 0.0;
SET accum_er := 0.0;
SET prev_year := YEAR(gdate);
END IF;
SET accum_ip := accum_ip + in_pit;
SET accum_er := accum_er + ER;
SET earned_run_avg := (accum_er / accum_ip) * 9;
UPDATE ip_ER_ERA_subtotal
SET ip_total = accum_ip,
ER_total = accum_er,
STD_ERA = earned_run_avg
WHERE Starting_Pitcher = pit_id
AND Game_Date = gdate
AND Game_Number = seq;
END LOOP;
CLOSE c1;
END
$$
这是表格的屏幕截图:
Here's a screenshot of the table:
在任何给定的光标操作中是否只能处理一个公式?
Could it be that only a single formula can be handled in any given cursor operation?
SET accum_ip := accum_ip + in_pit;
SET accum_er := accum_er + ER;
SET earned_run_avg := (accum_er / accum_ip) * 9
谢谢您的帮助.
达尔文，
好的，这是经过编辑的代码，该代码以正确的值填充以下各列:Starting_Pitcher，Game_Date，Game_Number，局限和来自表ip_ER_ERA_subtotal的ER.列ER_total和STD_ERA的所有位置均为"0".
这是代码:
Darwin,
Ok, here is the edited code that populates the following columns with correct values: Starting_Pitcher, Game_Date, Game_Number, innings pitched and ER from the table ip_ER_ERA_subtotal. Columns ER_total and STD_ERA have all "0"s in them.
Here is the code:
DELIMITER $$
CREATE PROCEDURE accumulate_ip_ER_ERA()
BEGIN
DECLARE pit_id VARCHAR(8);
DECLARE gdate VARCHAR(10);
DECLARE seq VARCHAR(1);
DECLARE in_pit REAL;
DECLARE earned_runs REAL;
DECLARE accum_ip REAL;
DECLARE accum_er REAL;
DECLARE earned_run_avg REAL;
DECLARE prev_year YEAR(4);
DECLARE end_of_cursor BOOLEAN;
DECLARE c1 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER
FROM ip_ER_ERA_subtotal
ORDER BY Starting_Pitcher, Game_Date, Game_Number;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
TRUNCATE TABLE ip_ER_ERA_subtotal;
INSERT INTO ip_ER_ERA_subtotal
(Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER)
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER
FROM starting_pitcher_game_log;
SET end_of_cursor := FALSE;
SET prev_year := 0;
OPEN c1;
fetch_loop: LOOP
FETCH c1 INTO pit_id, gdate, seq, in_pit, earned_runs;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
IF YEAR(gdate) != prev_year THEN
SET accum_ip := 0.0;
SET accum_er := 0.0;
SET earned_run_avg := 0.0;
SET prev_year := YEAR(gdate);
END IF;
SET accum_ip := accum_ip + in_pit;
SET accum_er := accum_er + ER;
SET earned_run_avg := (accum_er / accum_ip) * 9;
UPDATE ip_ER_ERA_subtotal
SET ip_total = accum_ip,
ER_total = accum_er,
STD_ERA = earned_run_avg
WHERE Starting_Pitcher = pit_id
AND ER = earned_runs
AND Game_Date = gdate
AND Game_Number = seq;
END LOOP;
CLOSE c1;
END
$$
我收到以下错误:
Unknown column 'ER' in 'field list'
这是表格的屏幕截图:
现在已填充"ER"列，但不再添加"ip_total"
Now the "ER" column populated but no more "ip_total"
好吧，终于可以使用下面的代码了.不确定，但是我有一种直觉，那就是它不喜欢表中我们正在调用要插入新表中的值(starting_pitcher_game_log)的字段"ER"的名称.不知道为什么它不喜欢它...
Ok, it finally worked with the below code. Not sure, but I had a hunch that it wasn't liking the name of the field "ER" from the table we were calling up the values (starting_pitcher_game_log) to be inserted into the new table. Don't know why it didn't like it...
这是我编辑的代码，用于处理"NULL"和被零除的情况:
Here's my edited code to handle "NULL" and divide-by-zero situations:
DELIMITER $$
CREATE PROCEDURE accumulate_ip_ER_ERA()
BEGIN
DECLARE pit_id VARCHAR(8);
DECLARE gdate DATE;
DECLARE seq INT;
DECLARE in_pit REAL;
DECLARE ER_id REAL;
DECLARE accum_ip REAL;
DECLARE accum_er REAL;
DECLARE earned_run_avg REAL;
DECLARE prev_year YEAR(4);
DECLARE end_of_cursor BOOLEAN;
DECLARE c1 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, earned_runs
FROM ip_ER_ERA_subtotal
ORDER BY Starting_Pitcher, Game_Date, Game_Number;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
TRUNCATE TABLE ip_ER_ERA_subtotal;
INSERT INTO ip_ER_ERA_subtotal (Starting_Pitcher, Game_Date, Game_Number, innings_pitched, earned_runs)
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, earned_runs,
IFNULL(innings_pitched, 0), -- replace NULL with 0, if
IFNULL(earned_runs, 0) -- column not initialized
FROM starting_pitcher_game_log;
END IF;
SET end_of_cursor := FALSE;
SET prev_year := 0;
OPEN c1;
fetch_loop: LOOP
FETCH c1 INTO pit_id, gdate, seq, in_pit, ER_id;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
IF YEAR(gdate) != prev_year THEN
SET accum_ip := 0.0;
SET accum_er := 0;
SET earned_run_avg := 0.0;
SET prev_year := YEAR(gdate);
END IF;
SET accum_ip := accum_ip + in_pit;
SET accum_er := accum_er + ER_id;
IF accum_er = 0 THEN -- prevent divide-by-zero
SET earned_run_avg := 0;
ELSE
SET earned_run_avg := (accum_er / accum_ip) * 9;
END IF;
UPDATE ip_ER_ERA_subtotal
SET ip_total = accum_ip,
ER_total = accum_er,
STD_ERA = earned_run_avg
WHERE Starting_Pitcher = pit_id
AND Game_Date = gdate
AND Game_Number = seq;
END LOOP;
CLOSE c1;
END
$$
错误:
您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取在'(innings_pitched，0)附近使用的正确语法，-如果将NULL替换为0，如果 IFNULL(earned_runs，'在第25行
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(innings_pitched, 0), -- replace NULL with 0, if IFNULL(earned_runs, ' at line 25
更新: 按STD_ERA排序时，表的屏幕截图显示了该字段范围的一端异常高的STD_ERA值，或者范围的另一端显示为灰色的"NULL"值.
UPDATE: Screenshots of table when sorting by STD_ERA showing abnormally high STD_ERA values at one end of the range for that field or grayed-out "NULL" values at the other end of the range.
推荐答案
正确；让我们看看这里有什么.
Right; let's see what we have here.
首先，必须按以下步骤阻止代码:
First, the code has to be blocked as follows:
variable declarations
cursor declarations
handler declarations
everything else
因此，您的DECLARE CURSOR c2 必须出现在DECLARE CURSOR c1和DECLARE CONTINUE HANDLER之间.另外，您只需要一个CONTINUE HANDLER，因为它从声明点到过程结束都有效.
So your DECLARE CURSOR c2 must appear between DECLARE CURSOR c1 and DECLARE CONTINUE HANDLER. Also, you only need one CONTINUE HANDLER because it takes effect from the point of declaration to the end of the procedure.
接下来是声明
INSERT INTO ip_ER_subtotal
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, 0.0
FROM starting_pitchers_game_log;
SELECT子句中的命名列是您选择的列 不是您要插入的列，因此它们必须是表starting_pitchers_game_log中的列.另外，由于未从starting_pitchers_game_log复制的列(即ip_total，er_total和era)都具有默认值，因此可以在INSERT语句上使用列列表，如下所示:
The named columns in the SELECT clause are the columns you're selecting from, not the ones you're inserting into, so they have to be columns in the table starting_pitchers_game_log. Also, since the columns not being copied from starting_pitchers_game_log (that is, ip_total, er_total and era) all have default values, you could use a column list on the INSERT statement, like so:
INSERT INTO pitcher_stats_temp
(Starting_Pitcher, Game_Date, Game_Number, innings_pitched, er)
SELECT pitcher_id, game_date, game_seq, innings_pitched, runs
FROM starting_pitchers_game_log;
这样可以节省键入内容，也可以记录您实际上要在其中插入值的列，并使INSERT语句与源表和目标表中列的物理顺序隔离开.
This saves typing, documents which columns you're actually inserting values into and insulates your INSERT statement from the physical order of columns in the source and target tables.
接下来，完成CURSOR c1循环后，不要截断表，否则您将丢失所有已完成的工作！ TRUNCATE TABLE删除表中当前的所有行，并在此处用于清除上一次运行的结果.
Next, once you finish the CURSOR c1 loop, don't truncate the table or you'll lose all the work you've just done! TRUNCATE TABLE deletes all rows currently in the table, and is used here to clear out the results of the previous run.
最后，两个循环必须具有不同的标签，例如fetch_loop_1和fetch_loop_2.在进入第二个循环之前，您还需要重置accum和end_of_cursor.但是，在这种情况下，我相信我们可以使用一个游标在一个循环中完成所有操作，这使代码更简单，因此更易于维护.
Finally, the two loops have to have different labels, say fetch_loop_1 and fetch_loop_2. You would also need to reset accum and end_of_cursor before entering the second loop. However, in this case I believe we can do everything in one loop with one cursor, which makes the code simpler and thus easier to maintain.
这是完整的过程:
DROP PROCEDURE IF EXISTS pitcher_stats_era;
DELIMITER $$
CREATE PROCEDURE pitcher_stats_era()
BEGIN
DECLARE pit_id CHAR(10);
DECLARE gdate DATE;
DECLARE seq INT;
DECLARE in_pit REAL;
DECLARE er INT;
DECLARE accum_ip REAL;
DECLARE accum_er INT;
DECLARE earned_run_avg REAL;
DECLARE prev_year YEAR(4);
DECLARE end_of_cursor BOOLEAN;
DECLARE no_table CONDITION FOR SQLSTATE '42S02';
DECLARE c1 CURSOR FOR
SELECT pitcher_id, game_date, game_seq, innings_pitched, earned_runs
FROM pitcher_stats_temp
ORDER BY pitcher_id, game_date, game_seq;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
DECLARE EXIT HANDLER FOR no_table
BEGIN
SIGNAL no_table
SET MESSAGE_TEXT = "Work table not initialized. Please call pitcher_stats_reset() before continuing",
MYSQL_ERRNO = 1146;
END;
------------------------------------------------------------------
-- The following steps are now performed by pitcher_stats_reset()
------------------------------------------------------------------
-- TRUNCATE TABLE ip_subtotal; -- Clear our work table for a new run
-- Copy data from main table into work table
-- INSERT INTO ip_subtotal
-- (pitcher_id, game_date, game_seq, innings_pitched, earned_runs)
-- SELECT pitcher_id, game_date, game_seq,
-- IFNULL(innings_pitched, 0), -- replace NULL with 0, if
-- IFNULL(runs, 0) -- column not initialized
-- FROM starting_pitchers_game_log;
---------------------------------------------------------------------
SET end_of_cursor := FALSE; -- reset
SET prev_year := 0; -- reset control-break
OPEN c1;
fetch_loop: LOOP
FETCH c1 INTO pit_id, gdate, seq, in_pit, er;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
-- check control-break conditions
IF YEAR(gdate) != prev_year THEN
SET accum_ip := 0.0;
SET accum_er := 0;
SET prev_year := YEAR(gdate);
END IF;
SET accum_ip := accum_ip + in_pit;
SET accum_er := accum_er + er;
IF accum_er = 0 THEN -- prevent divide-by-zero
SET earned_run_avg := 0;
ELSE
SET earned_run_avg := (accum_ip / accum_er) * 9;
END IF;
UPDATE pitcher_stats_temp
SET ip_total = accum_ip,
er_total = accum_er,
std_era = earned_run_avg
WHERE pitcher_id = pit_id
AND game_date = gdate
AND game_seq = seq;
END LOOP;
CLOSE c1;
END
$$
DELIMITER ;
那应该做的.如果有人发现错误，请务必指出.
That should do the job. If anyone finds a bug, by all means please point it out.
我刚刚添加了一些代码来说明如何防止源表中的空值，以及如何避免ERA计算被零除.
I've just added some code to illustrate how to protect against nulls coming from the source table, and how to avoid a divide-by-zero on the ERA calculation.
为了减少自己的困惑，我改回了原来的列名和表名.
I've changed back to my original column and table names in order to reduce my own confusion.
代码已更改为与 查看全文
