UDF question [message #315817] |
Tue, 22 April 2008 20:12 |
Michael GIllespie
Messages: 8 Registered: January 2004
|
Junior Member |
|
|
I have a udf question. To give background i'm trying to create a udf to retrieve a value. The function will be used within an application. Here's the catch. I can only query one table within the application. The table has a primary key column of id. Which is an input variable i'm passing to the function. The function is bolean returning 0 or 1. The value I need to check is in a secondary table which I can access via a join on the id column in the function. The issue is there is a two column key in the second table of prof,id. So the id can exist for each prof. If I could pass the function the prof as input it would be easy however that is not an option. I need the function to return the proper value for each prof,id combination.
|
|
|
|
Re: UDF question [message #315845 is a reply to message #315817] |
Tue, 22 April 2008 23:41 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
> The function is bolean returning 0 or 1.
> I need the function to return the proper value for each prof,id combination.
Are not these two statements contradictory?
You may "return" the desired value in an OUT paramater in proper data type (collection or cursor).
|
|
|
|
Re: UDF question [message #315945 is a reply to message #315845] |
Wed, 23 April 2008 05:27 |
Michael GIllespie
Messages: 8 Registered: January 2004
|
Junior Member |
|
|
AFAIK u cant create a function that have more than one out variable
a function is that it gets any number of inputs and returns only one value
A procedure can have any number of in (input) variables and any number of out(return variable)variables.
A procedure can have variables that acts as both in and out variables
This is a function.
|
|
|
Re: UDF question [message #315948 is a reply to message #315945] |
Wed, 23 April 2008 05:39 |
|
Michel Cadot
Messages: 68716 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | AFAIK u cant create a function that have more than one out variable
|
Wrong.
Quote: | a function is that it gets any number of inputs and returns only one value
|
Wrong.
Quote: | A procedure can have any number of in (input) variables and any number of out(return variable)variables.
A procedure can have variables that acts as both in and out variables
|
As well as function.
SQL> create or replace function f (v1 out number, v2 out number) return number
2 is
3 begin
4 v1 := 1;
5 v2 := 2;
6 return 0;
7 end;
8 /
Function created.
SQL> declare
2 l0 number;
3 l1 number;
4 l2 number;
5 begin
6 l0 := f (l1, l2);
7 dbms_output.put_line ('l0='||l0||', l1='||l1||', l2='||l2);
8 end;
9 /
l0=0, l1=1, l2=2
PL/SQL procedure successfully completed.
Regards
Michel
[Updated on: Wed, 23 April 2008 05:39] Report message to a moderator
|
|
|
Re: UDF question [message #315950 is a reply to message #315817] |
Wed, 23 April 2008 05:47 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Maybe "u" cannot create more OUT parameters, but I see no reason why anybody else could not. Function may have same number and types of parameters as procedure; function additionally returns a value.
In both, there is a problem using dynamic number of parameters (not known in compile time); in that case you shall use collection or cursor - it depends on your preferences.
|
|
|