Erstellen cursor mit Dynamischem SQL in MYSQL

Schreibe ich eine gespeicherte Prozedur, die Sie öffnet einen cursor auf eine Tabelle und dann eine Iteration durch alle Einträge. In der Iteration Prozess erstelle ich eine dynamische Abfrage auf der Grundlage der Ergebnisse des ersten cursor. Ich brauche zu öffnen, den cursor auf dynamische sql, aber MySQL ist nicht so zu tun, mich so, wie accoriding zu den offiziellen doc von mysql "Cursor muss deklariert werden, bevor Sie deklarieren Handler. Variablen und Bedingungen müssen deklariert werden, bevor Sie deklarieren, entweder mit Cursor oder Handler". Hier ist das Skript

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_test$$

CREATE PROCEDURE `sp_test`()
BEGIN
    -- Declarations

    DECLARE prepared_sql VARCHAR(1000);
    DECLARE index_count INT;

    -- Cursors
    DECLARE cursor1 CURSOR FOR SELECT * from table1;
    -- Continue Handler for Cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
    -- Open cursors
    OPEN cursor1;

    -- Business Logic
    all_alerts_loop: LOOP
        -- Fetch record from cursor1 and create a dynamic sql

        -- Check if cursor has reached to end than leave the loop
        IF no_more_rows THEN
            LEAVE all_alerts_loop;
        END IF;


        WHILE @some_other_variable <> 0
        DO
                              -- I want to open cursor 2 on this sql
            -- set @prepared_sql =  'create dynamic sql here';  
                    END WHILE;

                    -- This works fine
        PREPARE stmt FROM @prepared_sql;
        EXECUTE stmt;

                    -- But can't define cursor here? so what is the solution
                    -- Gives syntax error, I have tried with @prepared_sql also rather than stmt
        DECLARE cursor2 CURSOR FOR stmt;

    END LOOP;

    -- closing cursors
    CLOSE cursor1;
    END$$

DELIMITER ;

Eine Idee, wie man erstellen Sie cursor für eine dynamische Abfrage? in MYSQL

InformationsquelleAutor Muhammad Ummar | 2011-05-21

4

Erstellen Sie eine weitere Prozedur und den code schreiben, der cursor in diese neue Prozedur und rufen Sie dann die Prozedur aus, wo u wollen, deklarieren Sie einen cursor...

Ist das wirklich die beste Lösung? So umständlich!

InformationsquelleAutor karni

Es ist nicht erlaubt zu DEFINIEREN, cur CURSOR FÜR prepared_statement, müssen Sie eine gültige SQL-Anweisung. Die gute Nachricht ist, dass Sie definieren können Sie den cursor auf eine Ansicht, die sich dynamisch erstellt später. Zum Beispiel...

DROP PROCEDURE IF EXISTS my_dynamic_proc;
DELIMITER //
CREATE PROCEDURE my_dynamic_proc(tablename varchar(64), fieldname varchar(64), country VARCHAR(64))
BEGIN
    DECLARE adr_value varchar(500);
    DECLARE done BOOLEAN DEFAULT FALSE;
    -- Cursor definition
    DECLARE cur1 CURSOR FOR SELECT address FROM tmp_view_address;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- Dynamic view definition and creation
    SET @v = concat('CREATE OR REPLACE VIEW tmp_view_address as SELECT `',fieldname,'` as address FROM ',tablename,' WHERE country_name = "',country,'" group by 1 order by count(1) desc');
    PREPARE stm FROM @v;
    EXECUTE stm;
    DEALLOCATE PREPARE stm;
    -- Open cursor
    OPEN cur1;
    read_loop: LOOP
      FETCH cur1 INTO adr_value;
      IF done THEN
        LEAVE read_loop;
      END IF;
      -- Basic output result
      SELECT concat("My address is ",adr_value);
      -- Use every result in a dynamic update
      SET @u = concat('update ',tablename,' set new_field_address = "',adr_value,'" where country_name = "',country,'" and new_field_address is null');
      PREPARE stm FROM @u;
      EXECUTE stm;
      DEALLOCATE PREPARE stm;
  END LOOP;
  CLOSE cur1;
END//
DELIMITER ;

InformationsquelleAutor czuriaga

1

Als Sie nicht verwenden können dynamische Abfragen mit cursor, weil Sie nicht SET vor DECLARE. Auch Sie können gespeicherte Prozeduren verwenden CALL mit CURSOR FOR

DECLARE CURSOR cursor_name FÜR select_statement

CALL ist nicht ein select_statement.

Als workaround:

Sie sollten 3 Behandlungen anstelle von nur 1.
1. Temporäre Tabellen/views generator
  
  Schreiben Sie eine gespeicherte Prozedur zum generieren von temporären Tabellen oder sichten, die für Ihre dynamische Abfragen.
2. Die Berechnung der Ergebnisse
  
  Ihre aktuellen Verfahren CURSOR FOR SELECT FROM die temporären Tabellen. Sie sollten sich aber versichern, laufen die temporären Tabellen/views Verfahren zunächst - um aktualisierte Ergebnisse zu erhalten. Und Sie können nicht CALL Verfahren vor DECLARE den cursor. Dies ist der Grund, müssen Sie den Dritten Schritt.
3. Laufen alle zusammen
  Eine endgültige gespeicherte Prozedur CALL das Verfahren die Generierung temporärer Tabellen/sichten und dann CALL Ihre beabsichtigte Vorgehensweise zur Berechnung der Ergebnisse. Sie sollte schließlich das Letzte Verfahren als das einer Durchführung zu Ihrem Ergebnis.
InformationsquelleAutor Mahmoud Rabea
0

Sehe ich 2 mögliche Probleme in Ihrem Skript:

1) "ERKLÄREN cursor2 CURSOR FÜR stmt;" wahrscheinlich muss verschoben werden, an die Spitze des Verfahrens mit allen anderen Deklarationen vor allen ausführbaren Anweisungen.

2) der Cursor kann nicht auf dynamischen SQL (d.h. ich glaube nicht, können Sie bauen es auf eine vorbereitete Anweisung). Um diese Einschränkung zu umgehen, können Sie deklarieren Sie den cursor auf eine Ansicht basiert, dann erstellen Sie die Ansicht mit dynamischem SQL, vor dem öffnen des cursor. Das problem bei diesem Ansatz ist, dass die Ansichten öffentlich sind - die cursor-Deklaration hat einen festen Namen für die Ansicht ein, so dass mehrere gleichzeitige Benutzer könnte versehentlich zu sehen, die andere dynamisch definiert. Mein work-around ist zu prüfen, für die Existenz der Ansicht und Verzögerungen der Ausführung der Prozedur, bis die Anzeige gelöscht wird. Dies bedeutet, dass, um machbar sein, in einer belebten Umgebung, sollten Sie eine Ansicht erstellen, in einer Schleife durch den cursor, und klicken Sie dann löschen Sie die Ansicht, so schnell wie möglich. Technisch nicht elegant, aber dieser Ansatz funktionierte in meinem low-traffic-situation, und vermeidet den Aufwand von temporären Tabellen. Alternativ, wie andere haben vorgeschlagen, temporäre Tabellen sind thread-sicher, aber auf die Leistung auswirken kann.

InformationsquelleAutor MikeS
0

karni Ansatz ist weniger umständlich. Erstellen Sie zwei oder mehr SPs zu befriedigen jede bedingte Verzweigung (für die jeweils ein dynamisches sql erforderlich gewesen wäre). Erstellen Sie eine wrapper-SP-und fan-out Anrufe von dieser SP 'brancher' SPs.

Die alternative ist die 'Prepared view' - Ansatz benötigen mehr cpu-Zyklen und Speicher und zusätzlichen Speicherplatz während der Ausführung der Prozedur.

InformationsquelleAutor Saji Antony

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.