I need to make my stored procedure better...

[cwinans]

Here's the problem.

I created a stored procedure that took in a number of parameters. Those parameters were then checked to see if they contained the value of "ALL". If they did they were basically disregarded in the WHERE clause of the SELECT statement being created (by using the DECODE function).

The procedure is being used now in production but it is sloppy and my boss would like it cleaned up and commented so that others can use it as a sort of template.

This is what happens in the procedure:
[ol]
[li]Declare all internal variables
[li]Check parameters for relevance (discussed above)
[li]Select into a variable to get the TOTAL num of results
[li]Open a ref cursor to SELECT the results I currently want...
There are 3 SELECT statements nested here...
[ol]
[li]<<INNERMOST>> Same SELECT statement used to get total number of results
[li]<<INNER>> Wraps the previous and throws in ROWNUM in the SELECT portion to get count of records (1 - ?)
[li]<<OUTTER>> This one wraps the previous and takes ROWNUM values of 2 specified parameters (page_size and current_page)
[/ol]
[li]The cursor is a ref cursor so it is returned to the caller
[/ol]

What I want to do is reuse the initial SELECT statement instead of retyping it... seems very inefficient.

Is there anyway to Open a CURSOR... throw the primary SELECT statement into it.

Use cursor_name%ROWCOUNT ... I know that would work to find total results. But I would like to know if I could use the cursor as the FROM source for that later nastier SELECT statement?

Any comments are eager anticipated!! THANKS MUCH.
I hope this helped! ;-)
- Casey Winans

 

[Kenrae]

If I've understood what your saying, you need to select data from another select.
Then, you can do something like that:

SELECT ...
FROM(SELECT ...
FROM ...
WHERE ...)
WHERE ...

I don't know if it's more efficient, though. But it's surely more legible.

 

[sem]

You may try to use materialized view or temporary table to select from it.

 

[ljspop]

Hi -

If you use dynamic SQL you can put your SQL in a variable and
build the statements that way. That would also allow you to
get rid of the decode statement in the where clause. Functions
in the where clause really cripple the optimizers ability to do its
job.

Hope this helps.

Bob Bob Lowell
ljspop@yahoo.com

 

[cwinans]

Thanks for all your help.

I'm most interested in the use of a temporary table to hold the records from the primary SELECT statement.

That would be quite easy to get the row count from (total number of matches) and easy to reuse (put the name of the table in the FROM portion of the later more complex SELECT statement) ...

If this fails I'll resort to using dynamic SQL and place that in a variable.


Could someone give me some ideas how to create a temporary table and place the results I want into it?

Thanks in advance! I hope this helped! ;-)
- Casey Winans

 

[vrudenko]

Hi,

Oracle's temporary tables are very easy for use. You can get full information from a few pages of Application Developer's Guide - Fundamentals, Managing Schema Objects.

Regards,

Vira

 

[listener22]

A different way of handling the 'ALL' situation, different from using decodes is
something like the following


WHERE ((parameter1 = 'ALL') OR (column1 = parameter1))
AND ((parameter2 = 'ALL') OR (column2 = parameter2))

etc.

This structure has given good performance even in large queries
for several of our reports. Jim

oracle, vb

 

[Kenrae]

Yes, we've ended avoiding decode at all. We always use OR statements when needed. Not only the optimizer works better, but the query can be distributed better.
I changed myself a query which lasted for an hour. Now it only runs ten seconds.

 

[cwinans]

Thanks.

Ok, every time I try to use the &quot;CREATE GLOBAL TEMPORARY TABLE....&quot; in a stored procedure I get an error when attempting to compile the package? How do you go about creating the temp table????? I hope this helped! ;-)
- Casey Winans

 

[Kenrae]

You'll need dynamic SQL. Something like that:

EXECUTE IMMEDIATE 'create global temporary table' || table_name || table_definition;