Re: SELECTing from a DML statement

On Jul 4, 12:35 pm, "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote:
[..]

>

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>

--
Jeroen