On Jul 4, 8:26=A0pm, Jeroen van den Broek
<nlt...@[EMAIL PROTECTED]
> wrote:
> On Jul 4, 12:35=A0pm, "gym dot scuba dot kennedy at
gmail"<kenned...@[EMAIL PROTECTED]
> 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
> =A0 (employee_id, last_name, email, hire_date, job_id, salary)
> VALUES
> =A0 (employees_seq.nextval, =91Doe=92, =91john....@[EMAIL PROTECTED]
> =91SH_CLERK=92, 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
|
