Home » Other » Training & Certification » convert sql query to plsql FUNCTION (windows xp)
convert sql query to plsql FUNCTION [message #359386] Sat, 15 November 2008 11:13 Go to next message
dreidu
Messages: 2
Registered: November 2008
Location: Sta Iria
Junior Member
hello i'm new to this oracle pl-sql business and i'm trying to do something for my school project that i need to have some query on a function in oracle pl-sql

i'm having some problems understanding it. i'm not very good at sql but i'll try to make you understand my problem.

imagine i have tables A and table B

A has id
and name

B has
id
idA
name

and i want to have a simple function returning the top 5 A elements

something like this in SQL

Select top 5 a
from A as a, B as b
where a.id = b.idA and b.name like %VAR%
order by a.id

how could i have a function that would give me something like this in pl sql?


TYPE typ_b IS REF CURSOR RETURN B%ROWTYPE;


FUNCTION top5( word IN B.name%TYPE)
RETURN typ_b;




[Updated on: Sat, 15 November 2008 11:13]

Report message to a moderator

Re: convert sql query to plsql FUNCTION [message #359389 is a reply to message #359386] Sat, 15 November 2008 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59819
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you try so far?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

A good reading: PL/SQL User's Guide and Reference

Regards
Michel
Re: convert sql query to plsql FUNCTION [message #359391 is a reply to message #359386] Sat, 15 November 2008 11:42 Go to previous messageGo to next message
dreidu
Messages: 2
Registered: November 2008
Location: Sta Iria
Junior Member
sorry i'm trying to update the message but i don't find
the button, but i did it one time.

i really don't no what to do exactly can i just put it like that?

the whole function thing is pretty new to me.

is something like this supposed to work?

FUNCTION top5( word IN B.name%TYPE)
RETURN typ_b;

FUNCTION top5( word IN B.name%TYPE)
RETURN CURSOR IS
Select top 5 a
from A as a, B as b
where a.id = b.idA and b.name like %VAR%
order by a.id
END top5;  


i'm gonna take a look at your link


Re: convert sql query to plsql FUNCTION [message #359401 is a reply to message #359391] Sat, 15 November 2008 12:34 Go to previous message
Michel Cadot
Messages: 59819
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Top 5" does not exist in Oracle SQL.
Search for "top n" here. For instance:
http://www.orafaq.com/forum/t/53075/2/
http://www.orafaq.com/forum/t/57916/2/

Regards
Michel
Previous Topic: OCA Certification
Next Topic: plsql project
Goto Forum:
  


Current Time: Fri Nov 28 10:14:17 CST 2014

Total time taken to generate the page: 0.06402 seconds