Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS

From: Andreas Mosmann <mosmann_at_expires-30-06-2011.news-group.org>
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> de

Received on Fri Jun 17 2011 - 02:14:26 CDT