Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: "instead of select" trigger

Re: "instead of select" trigger

From: Serge Rielau <srielau_at_ca.eye-be-em.com>
Date: Fri, 28 May 2004 09:36:18 -0400
Message-ID: <c97f8v$lmm$1@hanover.torolab.ibm.com>


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
Received on Fri May 28 2004 - 08:36:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US