Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS
Date: Wed, 22 Jun 2011 17:25:16 +1000
Message-ID: <87zklae3ab.fsf_at_puma.rapttech.com.au>
Andreas Mosmann <mosmann_at_expires-30-06-2011.news-group.org> writes:
> Tim X schrieb am 18.06.2011 in <87y610c5ws.fsf_at_puma.rapttech.com.au>:
>
>> 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.
> No, because I had within every statement the complex logic of the procedure
> above. I did not mention but in fact the result set of the stored procedure is
> to be combined many times with different other tables/views from users, that do
> not have to understand the logic behind the scene.
>
Your right, I don't understand the details of what you need. However, I still suggest you need to give up on the approach you have settled on and come from a different direction.
>> 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.
> I have to decide whether I prefer some seconds more answering time and a minute
> to develop the query or a very short answering time and a many hour develop of
> query for each user.
The former is always better. However, I would suggest there is something very wrong somewhere if the options are that simple.
> I know, I could solve my problem by creating a table/temp table as result of my
> stored procedure. But that is not my question. There are about 10000 records to
> be processed, this can not so much time that I want to create/drop tables with
> 5 rows each time I use the procedure.
Temporary tabes etc are almost never the right solution. If you only have 10,000 records and yet ALL the SQL is becoming extremely complex with lots of repetition, I would begin to suspect that there is something very wrong with the underlying design of the database.
>
>> 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 am not absolutely sure, because the result set of the procedure is very
> small. For this I do not need an index. I know, that oracle likes corresponding
> indexes in two combined tables but nevertheless I can tell it to use an index
> for the corresponding table.
> Something like
> select /*+ index(B) */ ...
> from MyResultSet A
> join AnyOtherTable B on A.B_ID=B.A_ID
> So I suppose this wouldn't be my problem.
>
> There are many ways to solve my problem but there sure is no simple sql I could
> use. I definitely need program code and definitely have to combine it with
> other tables. But I would prefer to do it on the server and not on the client.
> And I prefer to do it without temporary tables. And I do not want to write the
> same procedure 1000 times for each scenario a user could need.
All admirable goals and worth striving for. However, the model you are trying to apply doesn't look right.
>
>> 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.
> This I try. But I can not pass the pitfalls before I know how to return some
> rows by a PL/SQL stored procedure and use this in a select.
>
Well, he bad news is I don't think you can do it the way your hoping. Others have shown how you could use a procedure, but I don't think you can do what your wanting. One of the problems I can see is that apart from ref cursors, I don't know of a way you can return multiple values from a function (procedures don't return values) other than by using PL/SQl collection types, but SQL doesn't understand PL/SQL specific data types.
>> 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.
> I think I exactly look for the answer of my question. Materialized views do not
> work because I probably need 1 View for each row of my table. With collection
> types I still did not work but I suppose I could not combine it with other
> tables by select statement.
>
Don't think of the materialised view as representing just the rows of interest. Instead, think of the materialised view as representing a smaller sub-set of your data - a part of your sql - which has been factored out into the view. You then join with the view and add additional conditions to further refine the result set. This should/could make your SQL simpler and avoid the problems of having multiple instances of very complex SQL that is difficult to maintain.
>> Tim
> Thank you
> Andreas
>
> By the way:
> function MyProc(Param1 in char,Param2 in char...) return MyPackage.MyRefTable;
> can be compiled but if I use it like
>
> Select MyProc('P1','P2') from dual
>
> I get the error ora-00902,if
>
> Select * from MyProc('P1','P2')
>
> I get ora-00933
Look at the section from the SQL Reference manual that talks about the limitations of user defined functions that need to be followed in order to make the function callable from SQL. The basic rule is that the function can only accept and return values that are understood by SQL. While you don't show your definition of 'MyRefTable, I suspect it is a PL/SQL type which is not recognised by SQL
Remember that SQL and PL/SQL are two different processing engines with their own data types. While PL/SQL knows about most (all?) the types SQL has, the opposite is not true. There are many types (especially collection types) which SQL knows nothing about or can only partially parse.
Tim
-- tcross (at) rapttech dot com dot auReceived on Wed Jun 22 2011 - 02:25:16 CDT