Re: SELECTing from a DML statement
Date: Fri, 4 Jul 2008 09:26:04 -0700 (PDT)
Message-ID: <e84b6aa2-9d88-464b-9cf1-7b146a8ac075@l64g2000hse.googlegroups.com>
On Jul 4, 12:35 pm, "gym dot scuba dot kennedy at gmail"
<kenned..._at_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_at_oracle.com’, SYSDATE,
‘SH_CLERK’, 2400)
RETURNING salary*12, job_id
INTO :bnd1, :bnd2;
</q>
-- JeroenReceived on Fri Jul 04 2008 - 11:26:04 CDT