Prev: ASM I/O size
Next: Fresh Enterprise Manager Install Can't connect "Connection Closed"
From: Serge Rielau on 4 Jul 2008 09:44
jefftyzzer wrote:
> DB2 allows one to select from an insert, update, or delete, e.g.:
>
> select * from old table (delete from x where y = 1);
>
> --OR--
>
> select * from new table (update x set y = 1 where z = 2);
>
> --OR--
>
> select * from final table (insert into x (c1) values (1));
>
> Is there any equivalent in Oracle?
Jeff,

Oracle supports a clause after the UPDATE/DELETE/INSERT statement called
RETURN INTO (or RETURNING??). Somewhat similar to the OUTPUT clause of
SQL Server.
It may be sufficient for what you want to do.
If you operate on more than one row then you'd typically dump the result
into an associative array of rows in PL/SQL and use FORALL, etc for
further processing.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
From: Jeroen van den Broek on 4 Jul 2008 12:26
On Jul 4, 12:35 pm, "gym dot scuba dot kennedy at gmail"
<kenned...(a)verizon.net> wrote:
[..]
>
> Look up returning
> eg insert into ... returning ...;

Good suggestion, but ...
Just found this excellent paper "Returning Modified Rows - SELECT
Statements with Side Effects":
http://www.vldb.org/conf/2004/IND1P1.PDF
which seems to indicate (in par. 6) similarities between Oracle's
'returning' and DB2's use of transition tables (old/new/final), but no
equivalence:

<q>
Oracle has introduced a returning clause for insert, update and delete
statements [8]. The returning clause specifies which columns are
returned, followed by an into clause and a set of host variables in
which the values are stored. The approach allows returning more than
one row, in which case the host variables need to be declared as
arrays. Applications
require PL/SQL extensions to access the returned data; they are not
returned as a result set to the client. Oracle is using the returning
clause for update and delete statements in the Delivery and Payment
transactions of the TPC-C benchmark. In contrast to the DB2 approach,
an insert or update statement always returns all modified rows, even
if the target is a view with a where clause, and a before trigger
modifies a value so that it
violates the view predicate. The following is an example of an insert
with returning clause (the bind variables must first be declared).

INSERT INTO employees
(employee_id, last_name, email, hire_date, job_id, salary)
VALUES
(employees_seq.nextval, ‘Doe’, ‘john.doe(a)oracle.com’, SYSDATE,
‘SH_CLERK’, 2400)
RETURNING salary*12, job_id
INTO :bnd1, :bnd2;
</q>

--
Jeroen
From: Vladimir M. Zakharychev on 4 Jul 2008 14:57
On Jul 4, 8:26 pm, Jeroen van den Broek
<nlt...(a)baasbovenbaas.demon.nl> wrote:
> On Jul 4, 12:35 pm, "gym dot scuba dot kennedy at gmail"<kenned...(a)verizon.net> wrote:
>
> [..]
>
>
>
> > Look up returning
> > eg insert into ... returning ...;
>
> Good suggestion, but ...
> Just found this excellent paper "Returning Modified Rows - SELECT
> Statements with Side Effects":http://www.vldb.org/conf/2004/IND1P1.PDF
> which seems to indicate (in par. 6) similarities between Oracle's
> 'returning' and DB2's use of transition tables (old/new/final), but no
> equivalence:
>
> <q>
> Oracle has introduced a returning clause for insert, update and delete
> statements [8]. The returning clause specifies which columns are
> returned, followed by an into clause and a set of host variables in
> which the values are stored. The approach allows returning more than
> one row, in which case the host variables need to be declared as
> arrays. Applications
> require PL/SQL extensions to access the returned data; they are not
> returned as a result set to the client. Oracle is using the returning
> clause for update and delete statements in the Delivery and Payment
> transactions of the TPC-C benchmark. In contrast to the DB2 approach,
> an insert or update statement always returns all modified rows, even
> if the target is a view with a where clause, and a before trigger
> modifies a value so that it
> violates the view predicate. The following is an example of an insert
> with returning clause (the bind variables must first be declared).
>
> INSERT INTO employees
>   (employee_id, last_name, email, hire_date, job_id, salary)
> VALUES
>   (employees_seq.nextval, ‘Doe’, ‘john....(a)oracle.com’, SYSDATE,
> ‘SH_CLERK’, 2400)
> RETURNING salary*12, job_id
> INTO :bnd1, :bnd2;
> </q>
>
> --
> Jeroen

At least the following is only partially true: "Applications require
PL/SQL extensions to access the returned data; they are not returned
as a result set to the client." Indeed, the results are not returned
as a result set, but PL/SQL is NOT required, array-typed host
variables are sufficient. And since the returning clause can't be used
with DML on views with INSTEAD OF triggers I am not sure if this
remark is correct either: "an insert or update statement always
returns all modified rows, even if the target is a view with a where
clause, and a before trigger modifies a value so that it violates the
view predicate." Does it mean triggers on the view's base table? In
this case, the behavior is correct - the clause returns *all affected*
rows as specified, not just those that would be visible to the view.
One more reason to get rid of triggers, by the way. :)

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
First  |  Prev  | 
Pages: 1 2
Prev: ASM I/O size
Next: Fresh Enterprise Manager Install Can't connect "Connection Closed"