Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: "instead of select" trigger
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 LabReceived on Fri May 28 2004 - 08:36:18 CDT