Home » SQL & PL/SQL » SQL & PL/SQL » Question on Functions
Question on Functions [message #185473] Tue, 01 August 2006 23:30 Go to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi,
I have a requirement like this.
I have to write a query using two tables and return the results.
One of the parameters should be a input frpm the user (e.g date).
i wanted to write a function for this which will get the input from user and return the resulset.
how thw function should be written and what should be the return type of the function ?
I am using oracle 9i.

regards,
vishal
Re: Question on Functions [message #185488 is a reply to message #185473] Wed, 02 August 2006 00:21 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Its pretty easy....
Here is the format..

Create FUNCTION name RETURN datatype IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];

In executable statements, you can write ur query...

Please try it out from ur end...and let us know if u have some problem....

Naveen
Re: Question on Functions [message #185489 is a reply to message #185488] Wed, 02 August 2006 00:29 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi,
Thanks Navkrish for your response.
Take a look at what i am doing :

Create FUNCTION abc (pDate date ) RETURN datatype IS
BEGIN
select a.book_name,b.supplier
from a , b
where a.id = b.id
and a.date = pDate;
return;
END ;

My question here is that how do i return the results of this query.
what should i use the Return datatype ?

Hope you have understood my question.

Thanks and Regards,
V
Re: Question on Functions [message #185495 is a reply to message #185473] Wed, 02 August 2006 00:41 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

If you want to return more than one value, better go for procedure...
Assume u want to return only book_name then here u go...
Create FUNCTION abc (pDate date ) RETURN varchar2 
IS
  v_book_name a.book_name%type;
BEGIN
select a.book_name into v_book_name
  from a , b
where a.id = b.id 
  and a.date = pDate;
return v_book_name;
END ;



Hope , u will get any other better soultion in this forum...
Naveen
Re: Question on Functions [message #185496 is a reply to message #185489] Wed, 02 August 2006 00:45 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

 in function you use return to return the result of the query.also i find some errors in your code.you have to use 

select col1,col2 into var1,var2...., and to return this 
use return var1||','||var2



regards,
Re: Question on Functions [message #185502 is a reply to message #185496] Wed, 02 August 2006 01:25 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi,
Thanks everyone for the response.
My requirement is that i have to return the resultset and not a single value.
So,it seems that function for my requirement is out of question.
Can someone give me a example to write this requirement using procedures ?
My requirement is that i have to return a resulset and not a single value .
do i have to use any cursor for this (i don't think so but still a question) ?

regards,
Vishal
Re: Question on Functions [message #185503 is a reply to message #185502] Wed, 02 August 2006 01:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Search for ref cursor. You should be able to find plenty examples.
Re: Question on Functions [message #185508 is a reply to message #185473] Wed, 02 August 2006 01:42 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

If u want to return one row with n columns go with procedure using out parameters.
Incase u want to return the resultant set of an query use cursor...

As Frank suggested try searching for ref cursor....Post ur script incase if you face any problem..

Naveen
Re: Question on Functions [message #185533 is a reply to message #185508] Wed, 02 August 2006 03:15 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi,
Thanks everyone.
I will try doing it with a Procedure but there also i have to use Print command to Print the refcursor as i saw in one of the examples in the previous forums replies.
Actually,i wanted this to be called from SQL commands and not from PL/SQL commands .
I know this is possible in SQL Server but i am not sure whether it can be done in oracle.

select * from function_name(parameter);

Regards,
Vishal
Re: Question on Functions [message #185560 is a reply to message #185473] Wed, 02 August 2006 06:05 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
RTFM
Re: Question on Functions [message #185577 is a reply to message #185560] Wed, 02 August 2006 07:09 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi,
Thanks orafaqer but i am not using collections here and table functions can be used only when we have to return collections .
My requirement is to just return a resultset .

regards,
vishal
Re: Question on Functions [message #185578 is a reply to message #185577] Wed, 02 August 2006 07:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What exactly is it you are trying to accomplish? Sounds like you don't need a function at all; can't it be done in pure SQL?
Could you please explain a bit more of your problem?
Re: Question on Functions [message #185581 is a reply to message #185577] Wed, 02 August 2006 07:46 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
vishal gupta wrote on Wed, 02 August 2006 16:09

Hi,
Thanks orafaqer but i am not using collections here and table functions can be used only when we have to return collections .
My requirement is to just return a resultset .

regards,
vishal

Seems like you don't understand what do you want. It's normal Smile
Maybe another RTFM will be helpful.
Re: Question on Functions [message #185616 is a reply to message #185577] Wed, 02 August 2006 12:20 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
A set of results can surely be expressed as either a ref cursor or a collection (especially a queriable one), though if we are suddenly being specific then I suppose "resultset" normally means a ref cursor.
Previous Topic: SQL Queries
Next Topic: Selecting multiple values from dropdown
Goto Forum:
  


Current Time: Sat Dec 03 13:45:31 CST 2016

Total time taken to generate the page: 0.24647 seconds