Alternative zu dynamic sql-cursor problematik

[spaniac]

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:

SQL-Code:

PREPARE stmt_name FROM preparable_stmt

und

SQL-Code:

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 ;

[omata]

Dir könnte man bestimmt helfen, aber leider hast du nichts darüber verraten, wie deine Datenbankstruktur aussieht, du hast keine Beispieldaten gezeigt und du hast nicht gezeigt was du eigentlich haben bzw. machen möchtest. Du bist festgefahren in deinem Weg und den willst du weitergehen, obwohl du herausgefunden hast, dass er nicht funktioniert.

Mein erster Ansatz (ohne eigentlich irgendetwas von dem zu wissen, was du da eigentlich vor hast) ist folgender...

SQL-Code:

SELECT datum, 
       SUM(CASE WHEN    (used_duration =  5 AND typ_id = 1)
                     OR (used_duration = 15 AND typ_id = 2) 
                     OR (used_duration = 20 AND typ_id = 3) 
             THEN wert 
             ELSE 0 
           END) AS value
FROM element e, config c
WHERE e.datum BETWEEN ? AND ? 
  AND ',' + REPLACE(used_pilots, ' ', '') + ',' LIKE '%,' + e.pilot_id + ',%'
  AND c.domain = :in_domain
GROUP BY datum

Wobei die Sache mit used_pilots einfach nicht klar ist. Und wenn ich das richtig interpretiert haben sollte, dann ist deine Datenbankstruktur an der Stelle total unbrauchbar.

Wie auch immer, diesen Text einfach ignorieren, falls ich mal wieder völlig falsch liege...

[nahpets]

Hallo,

Du hast da doch eine temporäre Tabelle, die Du wegwirfst und neu erstellst und dann per Cursor füllen willst.

Wie wäre denn mit einem

SQL-Code:

Create Table temp_table_value as select ...

also ungefähr so:

SQL-Code:

    SET select_statement = CONCAT('Create Table temp_table_value as SELECT datum, ', 
      'SUM(CASE WHEN typ_id = 3 THEN wert ELSE 0 END) AS value ', 
         'FROM element e ', 
         'WHERE e.datum BETWEEN ? AND ? ', 
         'AND e.pilot_id IN (?) ', 
         'GROUP BY datum;'); 
...
  PREPARE sql_stmt FROM @tmp_sql; 
  EXECUTE sql_stmt USING @tmp_in_startdate, @tmp_in_enddate, @tmp_used_pilots;

Das sollte Dir den Cursor ersparen.

Mangels MySQL-Verfügbarkeit ungetestet.

Sofern möglich, ist die von Omata vorgeschlagene Variante vorzuziehen. Wenn MySQL Case im Select-Statement unterstützt, kommst Du mit einem Statement aus und hast damit Deinen Cursor.

[spaniac]

Hallo,

danke erst einmal für die Tipps. Ich hole noch einmal etwas weiter aus, um das Konstrukt zu verdeutlichen:

Ich habe meine Tabellenstruktur aus meinem alten Thread zu Grund liegend:

http://www.delphipraxis.net/internal...t.php?t=172714


DB-Struktur

SQL-Code:

SQL-Code:

Schema: element

SQL-Code:

SQL-Code:

id | datum | intervall | wert | typ_id | bereich_id
1 | 2009-01-01 | 8:00 | 3.0 | 2 | 2
2 | 2009-01-01 | 8:30 | 2.0 | 3 | 1
...

Die Abfrage für used_pilots könnt ihr getrost ignorieren, sie dient lediglich zur Einschränkung der pilot_id bzw. bereich_id! Ich habe die obige SQL-Anweisung noch einmal in den CASE-Schritten erweitert, ich dachte, die einfachere Berechnung trägt zum Verständnis bei, aber jetzt habe ich alle Schritte mit eingebunden (mehrere SUM-Anweisungen in der Abfrage!).

Die stored procedures

Zitat:

xxxx_cur

rechnen für einen bestimmten bereich (also für bestimmte used_pilots) einen Wert aus und packen den in einem temporäre tabelle (datum DATE, wert FLOAT(7,2)). Es gibt von diesen SP ca 5 Stk. die jeweils einen einzelnen Wert ausrechnen und den jeder in eine eigene temporäre Tabelle zusammen mit dem Datum packen.

Eine _main stored procedure ruft die ca 5 stk xxxx_cur stored procedures auf, und macht dann ein join über das datum, so dass am Ende eine _main temporäre Tabelle ensteht mit der Struktur (datum DATE, wert von xxxx1_cur, wert von xxxx2_cur, ...).

Die Problematik, warum ich überhaupt einen Case in der oben gezeigten Abfrage mache ist, dass verschiedene Bereiche (piloten) auch verschiedene Berechnungsmethoden für den eigentlich gleichen Wert haben. Das wird mit

Zitat:

used_duration

bewerkstelligt, die definiert, ob für den Bereich die Berechnung für 5, 15 oder 20s herangezogen wird.

Also, nochmal bildlich:

SP _main:

- ruft alle SP xxxx_cur auf und nimmt deren temp table entgegen
- joint die werte aller xxxx_cur an hand des datums
- format:
datum | xxxx_cur wert1 | xxxx_cur wert2| ...

SP xxxx_cur:

- berechnet einen wert an hand der used_duration (5, 15 oder 20) und den used_pilots (bereich)
- gibt temp table mit datum + einzelwert zurück.

Ist das ganze jetzt klarer geworden? :)

[omata]

Vielleicht so...

SQL-Code:

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)
      - CASE WHEN duration IN (15, 20) THEN SUM(CASE WHEN typ_id = 6 THEN wert ELSE 0 END) ELSE 0 END
      - CASE WHEN duration = 20        THEN SUM(CASE WHEN typ_id = 7 THEN wert ELSE 0 END) ELSE 0 END AS wert
FROM element e, config c
WHERE datum BETWEEN :von AND :bis
  AND pilot_id = :pilot_id
  AND domain = :domain
GROUP BY datum

[spaniac]

@omata: allerbest, das klappt schonmal. einziger fehler: die werte werden jedes mal mit dem faktor 6 multipliziert im gegensatz dazu, wenn ich die select-anweisungen wie oben in 3 blöcke aufgeteilt habe. hast du eine idee, woran das liegen kann? Sobald ich die von Dir vorgeschlagene SELECT-Anweisung einzeln laufen lasse, werden die Werte nicht mit 6 multipliziert, also scheinbar hat es etwas mit dem Einlesen durch meinen Cursor zu tun...

SQL-Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `wert_cur` $$
CREATE PROCEDURE `wert_cur`(in_domain VARCHAR(45), in_startdate DATE, in_enddate DATE)
    MODIFIES SQL DATA
BEGIN
  DECLARE used_pilots       VARCHAR(45);
  DECLARE used_duration       VARCHAR(45);
  DECLARE temp_datum            DATE;
  DECLARE temp_wert       FLOAT(7,2);
  DECLARE done                INT DEFAULT 0;

  /*CASE for used_duration; calculate values*/

  DECLARE cur1 CURSOR FOR
  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)
    -CASE WHEN used_duration IN (15, 20) THEN SUM(CASE WHEN typ_id = 6 THEN wert ELSE 0 END) ELSE 0 END
    -CASE WHEN used_duration = 20 THEN SUM(CASE WHEN typ_id = 7 THEN wert ELSE 0 END) ELSE 0 END AS wert
   FROM element e, config c
   WHERE datum BETWEEN in_startdate AND in_enddate
   AND e.pilot_id IN (used_pilots)
   GROUP BY datum;

  /*create continue handler*/
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

  /*create temporary table*/
  DROP TEMPORARY TABLE IF EXISTS temp_table_wert;
  CREATE TEMPORARY TABLE temp_table_wert (datum DATE, offeredcalls FLOAT(7,2));

  /*read config, select pilots*/
  SELECT duration, pilots
  INTO used_duration, used_pilots
  FROM config c
  WHERE c.domain=in_domain;

  /*fetch values into temporary table*/
  OPEN cur1;
  temp_loop:LOOP

    FETCH cur1 INTO temp_datum, temp_wert;

    IF done=1 THEN
      LEAVE temp_loop;
    END IF;

    IF done=0 THEN

      INSERT INTO temp_table_wert (datum, offeredcalls)
      VALUES (temp_datum, temp_wert);

    END IF;

  END LOOP temp_loop;

  CLOSE cur1;
  
  SELECT * FROM temp_table_wert;

END $$

DELIMITER ;

[omata]

Zitat:

Zitat von spaniac

...Sobald ich die von Dir vorgeschlagene SELECT-Anweisung einzeln laufen lasse, werden die Werte nicht mit 6 multipliziert...

Und warum lässt du dann die Prozedur nicht einfach weg und nimmt nur die SQL-Anweisung?

Was macht die Prozedur denn so tolles in der Schleife? Ich verstehe die einfach nicht.

[spaniac]

die prozedur liest es in einem temporären table ein, der von der hauptprozedur xxxx_main genutzt wird. Leider geht das nicht anders, da ich mit der xxxx_main kein "call()" der untermethode machen kann.

das einlesen unten macht eigentlich nichts anderes als alle werte aus dem cursor in die tabelle zu packen, solange bis der continue handler = 1 ist... (also die abfrage keine werte mehr liefert...)

[spaniac]

nachtrag bzw. korrektur: wenn ich die von omata vorgeschlagene select-anweisung einzeln laufen lasse, tritt der fehler mit dem *6 ebenfalls auf. ich hatte nur eine falsche anweisung kopiert...

[omata]

Wie viele zeilen hat die config-Tabelle pro domain? Zufällig sechs? Das Konstrukt (die config) verstehe ich auch nicht wirklich.

Das Umkopieren von allen Zeilen geht auch einfacher, da ist kein Cursor nötig...

SQL-Code:

DELETE FROM temp_tabelle;
INSERT INTO temp_tabelle
SELECT *
FROM ...SQL-Abfrage...;