Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS
Date: Fri, 17 Jun 2011 09:14:26 +0200
Message-ID: <1308294866.53_at_user.newsoffice.de>
A little mistake inside the needed result...
Andreas Mosmann schrieb am 17.06.2011 in <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
changed line 3,5,7, insteat of 100 must be 200
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 200 C 10.01.2011 RP98054 700 800 C 01.01.2011 RP00021 0 200 D 02.06.2011 RP98054 700 750 D 01.01.2011 RP00021 0 200 > 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:14:26 CDT