Re: SELECTing from a DML statement

On Jul 4, 8:26 pm, Jeroen van den Broek
<nlt..._at_baasbovenbaas.demon.nl> wrote:
> 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...._at_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 Received on Fri Jul 04 2008 - 13:57:20 CDT