[SQL] Stored Procedures...

[glkgereon]

Hallo,

Ich versuche mich grade mit Stored Procedures in MySQL rumzuschlagen, und komme irgendwie nicht weiter :-/

Was ich machen will:
Ich habe eine Datenbank mit vielen Tabellen, die alle eine gewissen Grundstruktur haben: Sie werden über eine Spalte mit dem namen "id" identifiziert, auf dem ein Primary Key liegt.
Nun möchte ich eine SP haben, die guckt ob in der Tabelle tbl die gesuchte id schon existiert. Wenn ja, soll der Datensatz mit den gegebenen Angaben aktualisiert werden, ansonsten sollen die Daten eingefügt werden.

mein Code:

SQL-Code:

CREATE PROCEDURE p_insert_update (IN tbl VARCHAR(255), IN ids VARCHAR(255), IN cols TEXT, IN vals TEXT, IN updt TEXT)
BEGIN

SELECT COUNT(*) INTO @cnt FROM @tbl WHERE id=@ids /* gibts die ID schon? */

IF @cnt>0 THEN UPDATE @tbl SET @updt WHERE id=@ids LIMIT 1 /* ja -> UPDATE */
ELSE INSERT INTO @tbl (@cols) VALUES (@vals) /* nein -> INSERT */
END IF

END;

Es kommt die sehr aussagekräftige Fehlermeldung

Zitat:

You have an Error in your MySQL-Syntax *blabla* near
@tbl WHERE id=@ids

IF @cnt>0 THEN UPDATE @tbl SET @updt WHERE id=@ids LIMIT 1
E

Was mache ich da falsch?
Geht das überhaupt so wie ich mir das denke, dass ich die Tabelle als parameter übergebe und so?

[omata]

Welche MySQL-Version?

Du kannst nicht einfach den Tabellennamen über eine Variable übergeben, dafür müsstest du dynamisches SQL benutzen.

Dabei ist dann allerdings folgendes zu beachten:

Zitat von http://dev.mysql.com/doc/refman/5.1/de/routine-restrictions.html:

Vorbereitete SQL-Anweisungen (PREPARE, EXECUTE, DEALLOCATE PREPARE). Folge: Sie können in gespeicherten Routinen kein dynamisches SQL benutzen (indem Sie Anweisungen dynamisch als Strings generieren und dann ausführen). Diese Einschränkung wird in MySQL 5.0.13 für gespeicherte Prozeduren aufgehoben, gilt aber weiterhin für gespeicherte Funktionen und Trigger.

Gruss
Thorsten

[Hansa]

Bei MySQL kann ich nicht viel dazu sagen. Irgendwo Syntaxfehler ?

Zur Anregung eine FB-SP (ähnlicher Ansatz) :

SQL-Code:

CREATE PROCEDURE INS_UPD_SP (<zu aktualisierende Felder>)
AS
DECLARE VARIABLE AENDERN INTEGER;
BEGIN
  AENDERN = -1;
  SELECT ID FROM TABLEX WHERE (<Bedingungen>)

  INTO :AENDERN;
  IF (AENDERN < 0) THEN BEGIN
    INSERT ... ;
  END
  ELSE BEGIN
    UPDATE ... ;
  END
  SUSPEND;
END^

Von den Dingern habe ich bestimmt 100 Stück.

[marabu]

Hallo Gereon,

die Syntax des MySQL INSERT Statements kennt eine ON DUPLICATE UPDATE Klausel. Schau dir mal das entsprechende Kapitel in der Referenz an. Stored Procedures würde ich für andere Anwendungsfälle verwenden.

Grüße vom marabu

[omata]

Zitat von Hansa:

Von den Dingern habe ich bestimmt 100 Stück.

und genau das ist das Problem

Hier geht es ja gerade darum, dass der Tabellenname auch dynamisch ist. Dann wird nämlich nur eine Prozedur benötigt.

Ich habe mir nochmal Gedanken dazu gemacht...

Als erstes wird geprüft, ob es die Zeile schon gibt.

SQL-Code:

CREATE PROCEDURE `p_check_insert_update`(IN tbl VARCHAR(255),
                                         IN ids VARCHAR(255),
                                         IN cols TEXT,
                                         IN vals TEXT,
                                         IN updt TEXT)
BEGIN
  SET @s = CONCAT(
    'CALL p_make_insert_update(',
      '(SELECT COUNT(*) FROM ', tbl, ' WHERE id=', ids, '), ',
      '"', tbl, '", ',
      '"', ids, '", ',
      '"', cols, '", ',
      '"', vals, '", ',
      '"', updt, '"',
    ');'
  );
  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

Dann wird eine zweite Prozedur aufgerufen, die für das eigentliche Ändern zuständig ist. (Ich habe es leider nicht hinbekommen, den Rückgabewert vom EXECUTE in eine Variable zu bekommen, deshalb der Trick mit der zweiten Prozedur).

SQL-Code:

CREATE PROCEDURE `p_make_insert_update`(IN cnt INTEGER,
                                        IN tbl VARCHAR(255),
                                        IN ids VARCHAR(255),
                                        IN cols TEXT,
                                        IN vals TEXT,
                                        IN updt TEXT)
BEGIN
  IF (cnt > 0) THEN
    SET @s = CONCAT(
      'UPDATE ', tbl, ' ',
      'SET ', updt, ' ',
      'WHERE id=', ids
    );
  ELSE
    SET @s = CONCAT(
      'INSERT INTO ', tbl, ' (', cols, ') ',
      'VALUES (', vals, ')'
    );
  END IF;
  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

Getestest mit MySQL 5.0.19.

Vielleicht gibt es ja noch eine bessere Variante, bin gespannt.

Edit: ok, marabu hat ja gerade eine bessere Möglichkeit aufgezeigt, war trotzdem ein interessantes Problem.

Gruss
Thorsten

[glkgereon]

Ok, also ich habe die version von marabu genommen, und zwar weil:

Die Stored Procedure führt ja jedesmal ein Prepare aus...
Ich führe aber immer wieder das selbe aus, daher ist das eher ungünstig.

Bei Marabu kann ich das einfach einmal preparen und gut ist

Vielen Dank an alle!