Moin moin,
nachdem ich meine ersten Erfahrungen mit Stored Procedures gemacht habe, stecke ich an einer Stelle nun erneut fest:
Ich muss eine Case-Anweisung ausführen, die je nach Fall ein
SQL SELECT-Statement zusammenbaut. Aus diesem Grund baut meine SP einen String vom Typ TEXT zusammen, der das
SQL SELECT-Statement enthält. Um das darin enthaltene Statement ausführen zu können, müssen die folgenden Befehle verwendet werden:
PREPARE stmt_name FROM preparable_stmt
und
EXECUTE stmt_name [USING @var_name [, @var_name] ...]
Problem ist jedoch, dass das Resultset des besagten SELECT-Statement danach einem Cursor zugewiesen werden soll, der dies in eine temporäre Tabelle schreibt.
Laut
MySQL-Referenz kann ein Cursor jedoch nicht mit dynamischem
SQL bzw EXECUTE etwas anfangen:
Zitat:
However, a cursor cannot be used for a dynamic statement that is prepared and executed with PREPARE and EXECUTE. The statement for a cursor is checked at cursor creation time, so the statement cannot be dynamic.
Wie kann ich dieses Problem auf einer andere Weise lösen? Weiss jmd eine andere Lösung, wie ich z.B. innerhalb der Case-Anweisung bereits den Cursor mit dem SELECT-Statement verknüpfe? Dann müsste ich den ganzen Umweg mit Stringkonvertierungen und dynamic
sql nicht gehen...
Besten Dank schonmal für die Hilfe, anbei noch mein Code der Stored Procedure (vereinfacht):
SQL-Code:
DELIMITER $$
DROP PROCEDURE IF EXISTS `value_cur` $$
CREATE PROCEDURE `value_cur`(in_domain
VARCHAR(45), in_startdate DATE, in_enddate DATE)
READS
SQL DATA
BEGIN
DECLARE used_pilots VARCHAR(45);
DECLARE used_duration VARCHAR(45);
DECLARE temp_datum DATE;
DECLARE temp_value FLOAT(7,2);
DECLARE done INT
DEFAULT 0;
DECLARE select_statement TEXT;
/*Has to be 'EXECUTE' instead of 'SELECT', but cursors only support 'SELECT'*/
DECLARE cur1
CURSOR FOR SELECT sql_stmt;
DECLARE CONTINUE HANDLER
FOR NOT FOUND
SET done=1;
/*read config, select pilots*/
SELECT duration, pilots
INTO used_duration, used_pilots
FROM config c
WHERE c.domain=in_domain;
/*CASE for duration*/
CASE
WHEN (used_duration = 5)
THEN
SET select_statement = CONCAT('
SELECT datum, ',
'
SUM(CASE WHEN typ_id = 1 THEN wert ELSE 0 END) ',
'
+SUM(CASE WHEN typ_id = 2 THEN wert ELSE 0 END) ',
'
-SUM(CASE WHEN typ_id = 3 THEN wert ELSE 0 END) ',
'
-SUM(CASE WHEN typ_id = 4 THEN wert ELSE 0 END) ',
'
+SUM(CASE WHEN typ_id = 5 THEN wert ELSE 0 END)AS wert ',
'
FROM element e ',
'
WHERE e.datum BETWEEN ? AND ? ',
'
AND e.pilot_id IN (?) ',
'
GROUP BY datum;');
WHEN (used_duration = 15)
THEN
SET select_statement = CONCAT('
SELECT datum, ',
'
SUM(CASE WHEN typ_id = 1 THEN wert ELSE 0 END) ',
'
+SUM(CASE WHEN typ_id = 2 THEN wert ELSE 0 END) ',
'
-SUM(CASE WHEN typ_id = 3 THEN wert ELSE 0 END) ',
'
-SUM(CASE WHEN typ_id = 4 THEN wert ELSE 0 END) ',
'
+SUM(CASE WHEN typ_id = 5 THEN wert ELSE 0 END) ',
'
-SUM(CASE WHEN typ_id = 6 THEN wert ELSE 0 END)AS wert ',
'
FROM element e ',
'
WHERE e.datum BETWEEN ? AND ? ',
'
AND e.pilot_id IN (?) ',
'
GROUP BY datum;');
WHEN (used_duration = 20)
THEN
SET select_statement = CONCAT('
SELECT datum, ',
'
SUM(CASE WHEN typ_id = 1 THEN wert ELSE 0 END) ',
'
+SUM(CASE WHEN typ_id = 2 THEN wert ELSE 0 END) ',
'
-SUM(CASE WHEN typ_id = 3 THEN wert ELSE 0 END) ',
'
-SUM(CASE WHEN typ_id = 4 THEN wert ELSE 0 END) ',
'
+SUM(CASE WHEN typ_id = 5 THEN wert ELSE 0 END) ',
'
-SUM(CASE WHEN typ_id = 6 THEN wert ELSE 0 END) ',
'
-SUM(CASE WHEN typ_id = 7 THEN wert ELSE 0 END)AS wert ',
'
FROM element e ',
'
WHERE e.datum BETWEEN ? AND ? ',
'
AND e.pilot_id IN (?) ',
'
GROUP BY datum;');
END CASE;
/* create tmp variables for dynamic-sql parameters*/
SET @tmp_sql = select_statement;
SET @tmp_in_startdate = in_startdate;
SET @tmp_in_enddate = in_enddate;
SET @tmp_used_pilots = used_pilots;
PREPARE sql_stmt
FROM @tmp_sql;
EXECUTE sql_stmt
USING @tmp_in_startdate, @tmp_in_enddate, @tmp_used_pilots;
DROP TEMPORARY TABLE IF EXISTS temp_table_value;
CREATE TEMPORARY TABLE temp_table_value (datum DATE, offeredcalls
FLOAT(7,2));
OPEN cur1;
temp_loop:LOOP
FETCH cur1
INTO temp_datum, temp_value;
IF done=1
THEN
LEAVE temp_loop;
END IF;
IF done=0
THEN
INSERT INTO temp_table_value (datum, value)
VALUES (temp_datum, temp_value);
END IF;
END LOOP temp_loop;
CLOSE cur1;
DEALLOCATE PREPARE x1;
END $$
DELIMITER ;