5. September 2012

DELETE und UPDATE: Welche(!) Zeilen waren betroffen?

DELETE and UPDATE: Which(!) rows were affected?
In diesem Blog Posting stelle ich die RETURNING-Klausel für SQL DML-Anweisungen nochmals vor. Für SQL INSERT Kommandos hat die sicherlich fast jeder schonmal gesehen (dennoch erkläre ich es kurz) und danach zeige ich, wie man mit der RETURNING-Klausel feststellen kann, welche Zeilen von einem DELETE oder UPDATE erfasst wurden.
create sequence seq_id 
/

create table test (id number, name varchar2(200))
/

insert into test (id, name) values (seq_id.nextval, 'Max Mustermann')
/
Nun stellt sich die Frage, wie man an die während dem INSERT generierte ID wieder herankommt. Nachträgliches Abfragen funktioniert nicht, da der einzige, eindeutige Schlüssel (eben die ID) noch nicht bekannt ist. Eine Variante wäre ein ...
select seq_id.currval from dual
/

CURRVAL
----------
         1
Aber das bedeutet das Absetzen einer neuen SQL-Abfrage. Viel eleganter wäre es doch, wenn man die ID direkt beim INSERT zurückbekommen könnte - und das geht mit der RETURNING-Klausel ganz einfach.
declare
  l_id test.id%type;
begin
  insert into test (id, name) values (seq_id.nextval, 'Fritz') returning id into l_id;
  dbms_output.put_line(l_id);
end;
/
Weniger bekannt ist, dass man RETURNING auch bei UPDATE und DELETE-Kommandos einsetzen kann. Da hier auch mal mehr als eine Zeile zurückkommen kann, muss man aber noch mit der BULK COLLECT-Klausel arbeiten. Ein Beispiel für DELETE.
declare
  type t_numlist is table of number index by binary_integer;
  la_empno t_numlist;
begin
  delete from emp where deptno = 20 
  returning empno bulk collect into la_empno;

  for i in la_empno.first..la_empno.last loop
    dbms_output.put_line('deleted row with EMPNO '|| la_empno(i));
  end loop;
end;
/
sho err
Die Ausgabe ist dann wie folgt:
SQL> @returning
deleted row with EMPNO 7369
deleted row with EMPNO 7566
deleted row with EMPNO 7788
deleted row with EMPNO 7876
deleted row with EMPNO 7902

PL/SQL-Prozedur erfolgreich abgeschlossen.
Auch bei einem Update kann die RETURNING-Klausel eingesetzt werden, dabei werden aber stets die neuen Werte zurückgegeben, nicht die alten. Aber die Primärschlüsselspalten dürften ohnehin die interessanteren sein. Hier also das Beispiel für ein UPDATE mit RETURNING-Klausel.
declare
  type t_numlist is table of number index by binary_integer;
  la_empno t_numlist;
  la_sal   t_numlist;
begin
  update emp set sal = sal * 2 where deptno = 20 
  returning empno, sal bulk collect into la_empno, la_sal;

  for i in la_empno.first..la_empno.last loop
    dbms_output.put_line('updated row with EMPNO '|| la_empno(i)||' - SAL: '||la_sal(i));
  end loop;
end;
/
sho err
Ich arbeite nun seit mehr als 10 Jahren mit Oracle - und RETURNING nutze ich regelmäßig. Aber auf die Kombination mit DELETE oder UPDATE wurde ich erst vor zwei Wochen aufmerksam.
In this blog posting I'll introduce the RETURNING clause for SQL DML commands. Some of you might have seen a RETURNING clause as part of a SQL INSERT statement. I'll show this as well but the more interesting bit is an UPDATE or DELETE command with a RETURNING clause, since this allows you to retrieve the affected rows. Let's start with a typical, simple example with a SQL INSERT and a sequence.
create sequence seq_id 
/

create table test (id number, name varchar2(200))
/

insert into test (id, name) values (seq_id.nextval, 'Max Mustermann')
/
Now, the question is: How can we get the ID number generated by the sequence. We cannot select the table since we don't know the unique ID - and all other columns might be non-unique. A working approach is ...
select seq_id.currval from dual
/

CURRVAL
----------
         1
But this required another SQL Query - it would be much more elegant to get the generated number directly from the SQL INSERT statement. And the returning clause makes it easy.
declare
  l_id test.id%type;
begin
  insert into test (id, name) values (seq_id.nextval, 'Fritz') returning id into l_id;
  dbms_output.put_line(l_id);
end;
/
You can use the RETURNING clause for DELETE statements as well. But since these typically affect more than one row, you'll also need the BULK COLLECT clause.
declare
  type t_numlist is table of number index by binary_integer;
  la_empno t_numlist;
begin
  delete from emp where deptno = 20 
  returning empno bulk collect into la_empno;

  for i in la_empno.first..la_empno.last loop
    dbms_output.put_line('deleted row with EMPNO '|| la_empno(i));
  end loop;
end;
/
sho err
This PL/SQL block produces the following output.
SQL> @returning
deleted row with EMPNO 7369
deleted row with EMPNO 7566
deleted row with EMPNO 7788
deleted row with EMPNO 7876
deleted row with EMPNO 7902

PL/SQL procedure successfully completed.
RETURNING does also work for UPDATE statements. In this case the new column values will be returned, not the "old" ones. But in practice, RETURNING will most often be used the get the non-changing primary key values back. Here is the example for a SQL UPDATE statement with a RETURNING clause.
declare
  type t_numlist is table of number index by binary_integer;
  la_empno t_numlist;
  la_sal   t_numlist;
begin
  update emp set sal = sal * 2 where deptno = 20 
  returning empno, sal bulk collect into la_empno, la_sal;

  for i in la_empno.first..la_empno.last loop
    dbms_output.put_line('updated row with EMPNO '|| la_empno(i)||' - SAL: '||la_sal(i));
  end loop;
end;
/
sho err
I'm working with Oracle for more than 10 years now and I frequently work with RETURNING. But I got aware of the combination with DELETE about three weeks ago.

Kommentare:

Niels Hecker hat gesagt…

Hallo Carsten,

das wundert mich jetzt aber, daß Du erst vor zwei Wochen darauf aufmerksam geworden bist.

Ich benutze die Möglichkeit bei Updates schon seit Jahren um die Werte von Feldern zu erhalten, die durch an den Tabellen hängenden Triggern geändert wurden und bei Deletes um abhängige Daten zu löschen, z.B. Adressen bei denen nur die ID in den Kundendaten gespeichert ist, und man von einer Webseite aus nur die ID des Kunden kennt.

Gruß, Niels

Carsten Czarski hat gesagt…

Hallo Niels,

ja ... aber Du weisst ja, wie das ist: Auch nach einigen Jahren Erfahrung mit der Datenbank findet man immer wieder eine Funktion, die man bis dahin nicht auf dem Radar hatte ... :-)

Das ist ja auch das Schöne an unserem Job ... wäre auch langweilig sonst ...

Beste Grüße
-Carsten

Beliebte Postings