• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2359
  • Last Modified:

PL/SQL Declaring cursors with Parameters

Informational question

I have a contractor coding a parameterized cursor like I've never seen before. Their claim is this how they teach it in class.  That's their blanket response, but they can't explain why this is done or the performance implications.  I would like to expand my knowledge why it works and the performance implications.


Example of their coding:
DECLARE
     v_value NUMBER;

     CURSOR curs1 IS
     SELECT col1, col2
       FROM tab1
      WHERE col1 = v_value;

BEGIN
     FOR v_cnt IN 1..100 LOOP
         v_value := v_cnt;
         OPEN curs1;
         …../* some fetching, some processing, closing cursor */

     END LOOP;
END;
-------

The way I have seen it coded and was taught.

DECLARE

    CURSOR curs1(p1 number)IS
    SELECT col1, col2
      FROM tab1
     WHERE col1 = p1

BEGIN
    LOOP v_cnt IN 1..100 LOOP
        OPEN curs1(v_cnt);
         …../* some fetching, some processing, closing cursor */

    END_LOOP;
END;

--------
My gut says the contractor's method will be slower, since I believe the statement would need to be resolved every time it is called. Whereas, with a
parameterize cursor Oracle would create a substition variable and the statement will be in the buffer.

Actually, I was a bit surprised Oracle allowed it. Why?
0
hedjj01
Asked:
hedjj01
1 Solution
 
Daniel StanleyDatabase engineerCommented:
both examples are comparible in nature. it is questionable which would run faster; i doubt it would even be measurable since both are parameterized and just using different methods to do the binding.


good luck,
daniels@asix.com  
0
 
saxena_mohitCommented:
Hi there,
  After looking at you example I would say both statement are same as far as Oracle parsing is concerns . there only statement in the buffer would be
SELECT col1, col2
     FROM tab1
    WHERE col1 = :a;

so it doesn't matter how you pass the variables.But your method will reduce the extra step of assigning the values everytime. but performancewise both are same but your's statement is good practice to have if you have long code.
Thanks
Mohit
0
 
plusheyCommented:
Hi,

I too am a contractor and I have spent 3+ years contracting to Oracle. I agree with Mohit and my preference would be to go with your method as it's the cleanest and neatest solution.

Cheers,
Paul
0
 
rajeXshCommented:
This method

  CURSOR curs1(p1 number)IS
   SELECT col1, col2
     FROM tab1
    WHERE col1 = p1;

promotes cursor reuse. For eg, If this cursor is going to be used in several procedures, you can declare it in one package and use it everywhere.

With the other method

    CURSOR curs1 IS
    SELECT col1, col2
      FROM tab1
     WHERE col1 = v_value;

It becomes very difficult to reuse because the cursor is tied down to the v_value in the procedure/package/function where it is declared.

Performance wise, both will be the same.


rajeXsh

0
 
hedjj01Author Commented:
FYI

I ran a series of tests with the different methods.  A loop was constructed to iterate for 1,000,000 times the opening and closing of the cursor.  The time difference  between the 2 methods was micro seconds.  However, the method that did not pass a parameter was however slightly faster, but not appreciably.  

My conclusion is that there is no performance benefit to the cursor being declared without the parameter.  Further, based on the feedback received and my own experience; coding a cursor with a parameter improves readability and reuse.

Thank
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now