Re: "instead of select" trigger

From: Jeff Smith <glimmertwinfan48197_at_yahoo.com>
Date: 26 May 2004 04:54:38 -0700
Message-ID: <9da6204.0405260354.6b136907@posting.google.com>

"Christian \"Raistlin\" Gulich" <Christian.Gulich_at_IN.Stud.TU-Ilmenau.de> wrote in message news:<c8vfe9$d1d$1_at_piggy.rz.tu-ilmenau.de>...
> "dnh" <nogood_at_using.this.addr.com> schrieb im Newsbeitrag
> news:40b2c233$1_at_cpns1.saic.com...
> > One word - VIEWS
>
> I have to pass parameters to the procedures, depending to the current query.
> If it isn't possible, to get the querieng sql statement in a view to pass it
> to the procedures, views are not a solution for me.
>
> Christian

You can use a function and pass it parameters. It can only return a single value. You should be careful because whatever processing is done in the function is done for each row when used in a select statement. I believe this term is "in-line function". An example of how you could use this passing 2 parameters which in this case would be values from 2 columns in the employee table is:

SELECT EMPLOYEE.NAME,

       EMPLOYEE.PHONE, 
       FN_MYFUNCTION( EMPLOYEE.NAME, EMPLOYEE.PHONE ) AS SOME_ALIAS

FROM EMPLOYEE
WHERE EMPLOYEE.ID = 123; FUNCTION FN_MYFUNCTION ( PNAME VARCHAR2, PPHONE VARCHAR2) RETURNS VARCHAR2    O_RETURN_STRING VARCHAR2(10) := '';    BEGIN

       --DO WHATEVER YOU WANT, PUT THE DESIRED VALUE
       -- INTO RETURN VARIABLE
       O_RETURN_STRING := 'SOMEVAL';

   RETURN O_RETURN_STRING;
END FN_MYFUNCTION; Remember, it is easy to get carried away with these functions and really blow away performance and resources.

Jeff Received on Wed May 26 2004 - 06:54:38 CDT