Re: "instead of select" trigger

Serge Rielau wrote:

> Hmm, maybe.. let me clarify. Forgive me for my SQL Dialect. I'm
> confident Oracle supports table functions, but don't know whether the
> syntax is the same syntax that I know:
>
> CREATE TABLE T(c1 NUMBER);
> CREATE FUNCTION foo(a NUMBER) RETURNS TABLE(c2 NUMBER)
> ... some magic PL/SQL here composing and returning a table...
>
> SELECT F.c2 FROM T, TABLE(foo(T.c1)) AS F;
>
> foo() is the inner of a join with T. It is correlated in the sense that
> it requires a value from T to execute.
>
> Table functions are commonly equated to "parameterized view because:
> CREATE FUNCTION V(a NUMBER) RETURNS TABLE(c2 NUMBER)
> RETURN SELECT c3 FROM t2 WHERE t2.pk = a
>
> is semantically equivalent to (hypothetical syntax here):
> CREATE VIEW V(c2) _with_parameters(a_NUMBER)_ AS
> SELECT c3 FROM t2 WHERE t2.c2 = a;
>
> which (using the example above) would equate to:
> SELECT F.c2 FROM T, LATERAL(SELECT c3 FROM t2 WHERE t2.pk = T.c2) AS F(c2)
>
> Of cousre table fucntions provide a superset of "parameterized view"
> function because of teh procedural logic they can handle, which I
> believe was also required by the original poster(?)
>
> Having that said, now why do I, personally, and at least those that
> taught me, think that global variable usage in general needs to be limited?
> Because, from the outside, it is very hard to understand the way a
> routine works if the behaviour is governed by a state that is not declared.
> If, e.g. I have two nested routines, both of which happen to depend on
> the same global variable and I update the variable before calling the
> outer routine without knowing that this will influence the inner that is
> clearly asking for trouble.
>
> As such global variables are excellent for static application level
> information (all sorts of "who am I" or cookie kind of information).
>
> None of what I wrote here, btw, has anything to do with a specific
> vendor or even DBSM as such.
> If anything I want to note that Oracle's PL/SQL is very powerful and
> supports good coding practices. But one needs to choose to use the
> available tools appropriately.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab

Thanks for the clarification.

Oracle most certainly has a table function. For example:

SELECT t1.department_id, t2.*
FROM hr_info t1, TABLE(t1.people) t2
WHERE t2.department_id = t1.department_id;

But one must remember in the context of Oracle that a large amount of the infrastructure is based upon, maybe not global variables, but session variables.

Not only are session variables at the basis of DBMS_APPLICATION_INFO ... they are the basis for Fine Grained Access Control, the SYS_CONTEXT function, workspace management (DBMS_WM, LT), and many other very powerful features.

All powerful features, like fire, can be used for good or evil. I understand the concern ... but I think applying it to any and all situations is misplaced.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)