Wie kann ich zwei Prozeduren in einer kombinieren, um eine Tabelle zu füllen, anstatt für jede der beiden Prozeduren eine eigene Tabelle zu füllen?
Ich habe mit Sequel Pro zwei Tabellen erstellt, die jeweils mit einer anderen Prozedur in MySQL gefüllt wurden. Obwohl jede Tabelle nach dem Ausführen der entsprechenden Prozedur die richtigen Informationen enthält, denke ich, dass meine Daten weniger verstreut sind, wenn ich einige der Tabellen weiter konsolidiere.
Also, was ich tun möchte, ist die Daten aus beiden Tabellen in einer zu kombinieren. Unten ist der Code, mit dem ich versucht habe, dies zu erreichen. Leider hat es nicht funktioniert und jede Hilfe mit dem Code wäre sehr dankbar.
create table code:
-- 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
Verfahrenscode:
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
Ich erhalte den folgenden Fehler:
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
Hier ist ein Screenshot der ersten Tabelle, die ich bereits erstellt habe "ip_subtotal"
Hier ist ein Screenshot der zweiten Tabelle, die ich bereits erstellt habe "ER_subtotal"
Aktualisieren
Hier ist im Wesentlichen derselbe Code, an dem ich geringfügige Änderungen vorgenommen habe:
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
Hier ist der Fehler:
Incorrect number of FETCH variables
Ich habe versucht zu sehen, was passiert, wenn keine Variablen abgerufen werden, und versucht, "accum_ip", "accum_er", "earn_run_avg" hinzuzufügen, aber es hat nicht funktioniert ... Es scheint, als wären die acht Variablen diejenigen, die die Die letzten drei + in Ihrer Fetch-Anweisung sollten diejenigen sein, die benötigt werden ...
Hier ist der Tabellencode:
-- 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
aktualisieren
Hier ist der Code, der auf Ihren Änderungen basiert, aber die Spalten ER, ER_total und STD_ERA sind nur mit "Null" -Werten gefüllt.
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
$
Hier ist ein Screenshot der Tabelle:
Kann es sein, dass in einer bestimmten Cursoroperation nur eine einzige Formel verarbeitet werden kann?
SET accum_ip := accum_ip + in_pit;
SET accum_er := accum_er + ER;
SET earned_run_avg := (accum_er / accum_ip) * 9
Danke für deine Hilfe
Darwin, Ok, hier ist der bearbeitete Code, der die folgenden Spalten mit korrekten Werten auffüllt: Starting_Pitcher, Game_Date, Game_Number, Innings Pitched und ER aus der Tabelle ip_ER_ERA_subtotal. Die Spalten ER_total und STD_ERA enthalten alle "0".
Hier ist der 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
$
Ich erhalte den folgenden Fehler:
Unknown column 'ER' in 'field list'
Hier ist der Screenshot der Tabelle:
Jetzt ist die Spalte "ER" ausgefüllt, aber nicht mehr "ip_total"
Ok, es hat endlich mit dem folgenden Code geklappt. Ich bin mir nicht sicher, aber ich hatte die Vermutung, dass der Name des Feldes "ER" aus der Tabelle, die wir aufriefen, die Werte (starting_pitcher_game_log), die in die neue Tabelle eingefügt werden sollen, nicht gefallen hat. Weiß nicht warum es nicht gefallen hat ...
EDIT: Hier ist mein bearbeiteter Code, um mit "NULL" und durch Null dividierenden Situationen umzugehen:
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
$
Error
Ihre SQL-Syntax ist fehlerhaft. In dem Handbuch, das Ihrer MySQL-Serverversion entspricht, finden Sie die richtige Syntax für near '(innings_pitched, 0). Ersetzen Sie NULL durch 0, wenn IFNULL (earn_runs,' in Zeile 25
UPDATE: Screenshots der Tabelle beim Sortieren nach STD_ERA mit abnormal hohen STD_ERA-Werten an einem Ende des Bereichs für dieses Feld oder ausgegrauten "NULL" -Werten am anderen Ende des Bereichs.
