Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS
Date: Fri, 17 Jun 2011 09:04:43 +0200
Message-ID: <1308294283.04_at_user.newsoffice.de>
Tim X schrieb am 17.06.2011 in <877h8ldkxk.fsf_at_puma.rapttech.com.au>:
> Andreas Mosmann <mosmann_at_expires-30-06-2011.news-group.org> writes:
>> Hi,
>>
>> I look for a way to write a stored procedure, that returns more than 1 "row"s.
>>
>>
>> I want to do something like
>>
>> select MyProc('A','B','C') from dual;
>>
>> and get
>>
>> ID TEXT
>> 1 1stRow
>> 2 2ndRow
>> 3 3rdRow
>> .
>> 999 999thRow
>>
>> and so on.
>>
>> If Possible it would be nice to do something like
>>
>> select * from MyProc('A','B','C')
>>
>> or
>>
>> select * from MyProc('A','B','C') X
>> join MyTable Y on X.ID=Y.ID
>>
>> Is there a way? (Oracle 9/11)
> How are you wanting to do this i.e. from within PL/SQL, from java over > jdbc, from another language via odbc, dbi? The answers are going to be > different depending on your language/environment. > If your using pl/sql, java or perl, you can use a ref cursor, but there is no > need to do the select from dual, you can just call the procedure to get > the refcursor (as an IN/OUT parameter (or in the case of perl, but it > between begin ... end; - you could use a function that> returns a refcursor as well) and then just use the refcursor to query as > a resultset.
> For the second two - well, I've never wanted to do anything like that > and suspect perhaps you may be asking the wrong question? Maybe explain > why you want to do this as I suspect there is possibly a better solution > that will achieve what you want.
> Tim
Thank you for the answer, but I am afraid I am looking exactly for a
select construction.
I have data, that describes the changes of a road network. And I want to
write a PL/SQL- Procedure, where I give a piece of a road and want back
all the changes up to a special date. And because it is nessesary to
join the result in different ways to different tables I search for a
SELECT .. statement.
(very simplified) Example:
ID UNTIL NAME_OF_PIECE LENGTH FOLLOWER OPERATION 01 01.01.2011 RP01234 500 DELETED 02 01.01.2011 RP54321 1000 05 DIVIDED 03 01.01.2011 RP54321 1000 06 DIVIDED 04 10.01.2011 RP98765 500 07 CONNECTED 05 10.01.2011 RP54300 300 07 CONNECTED 06 02.06.2011 RP00021 700 .. 07 02.06.2011 RP98054 750 08 CHANGED 08 17.06.2011 RP98054 700 DELETED
If I Ask
WhatHappend(RP54321,200,500,31.12.2010,05.06.2011)
I need
STEP DATE NAME_OF_PIECE FROM TO A 31.12.2010 RP54321 200 500 B 01.01.2011 RP54300 200 300 B 01.01.2011 RP00021 0 100 C 10.01.2011 RP98054 700 800 C 01.01.2011 RP00021 0 100 D 02.06.2011 RP98054 700 750 D 01.01.2011 RP00021 0 100
Maybe it is a little heavy to understand, but I have to combine this
lines with other information via SQL, so that I need a "table" as
output.
Sure, I could fill a table, but I don't want to.
I work on Oracle 9 and 11, use PL/SQL
Thanks in advance
Andreas Mosmann
-- wenn email, dann AndreasMosmann <bei> web <punkt> deReceived on Fri Jun 17 2011 - 02:04:43 CDT