Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS
Date: Sat, 18 Jun 2011 10:57:55 +1000
Message-ID: <87y610c5ws.fsf_at_puma.rapttech.com.au>
Andreas Mosmann <mosmann_at_expires-30-06-2011.news-group.org> writes:
> 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
>
If I understand you correctly, I think your approach is flawed. If you are going to select data simply as input to use in a join with data from another query, your better off just doing it all as 1 sql statement.
The problem with your approach is that you are working hard to circumvent all of Oracle's efforts to make things as optimized as possible.
Consider two simple scenarios. In the first one, you use various SQL statements to extract the final set of data you want. The statements are placed inside a procedure that allows you to both pass various values used to define/control the result set (via bind variables) and set a ref cursor to pass back which you can then query and further process or display the data. Oracle will e able to use any relevant indexes, exploit caching of SQL statements and take full advantage of the optimiser.
Now consider your (as I understand it) approach. You want to define procedures that will return a result set whih you then want to use in further queries involving joins and other operations. In this case, you want to effectively generate a pseudo table via a procedure call which you then use in joins and additonal SQL to extract the final result. However, there wil be no indexes, optimiser statistics and little caching opportunities that the DB can use to make things as efficient as possible.
I also suspect there are other pitfalls with the approach you are adopting. At a guess, I would think you have hit upon this idea because you observed that many of your SQL queries had a common element, making it fairly logical to want to isolate that bit of common SQL in one place - less typing, potentially more maintainable, cleaner code etc. All good objectives to strive for. However, you also need to consider how Oracle processes queries and strive for a balance.
Unfortunately, its really difficult to provide precise advice without a deeper understanding of the problem and outcome you are after. I could easily be totally misunderstanding what you are doing and advice which may be perfect in one situation is absolutely wrong in another even when the problems look similar or you are dealing with the same problem domain.
If you are looking at ways to minimise SQl and ensure consistency or make things easier to maintain, maybe look at other Oracle features. For example, rather that your suggestion of a procedure that returns a result set, perhaps a better solution would be to define a materialised view and join with that view to get your final result? Perhaps use functions/procedures that will return a different ref cursor depending on some argument or maybe the whole performance can be improved by using a PL/SQL collection type, which is passed around to different procedures for additional processing so that there is only 1 initial query etc.
Tim
-- tcross (at) rapttech dot com dot auReceived on Fri Jun 17 2011 - 19:57:55 CDT