Home » SQL & PL/SQL » SQL & PL/SQL » Correct way of usingCursor (Oracle 9i)
Correct way of usingCursor [message #317257] Wed, 30 April 2008 04:58 Go to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I have to use Cursors extensively in a procedure as there are many select statements to be done from many tables for various information.

Please suggest as which is the better way with respect to preformance while creating cursor.

For example, I have 15 columns in a table and have to select 3 columns only.
One way is I select thse 3 columns while creating cursor and fetch them in 3 different variables as :
1).
CURSOR cur_3cols IS SELECT COL1, COL2, COL3 FROM TABLE1;
OPEN cur_3cols;
FETCH cur_3cols INTO v_col1, v_col2, v_col3;

And another way is select all and fetch them in a recordset
2).
declare
  cursor cur_t is select * from test_for_cursor;
  rec_t test_for_cursor%rowtype;
begin
  open cur_t;
  loop
    fetch cur_t into rec_t;
    exit when cur_t%notfound;
end loop;
end;


Please advice as which will be better according to performance.

Thanks,
Soni
Re: Correct way of usingCursor [message #317259 is a reply to message #317257] Wed, 30 April 2008 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) Why using a cursor and not just SELECT?
2) Use BULK COLLECT if there are more than 1 row.

Regards
Michel
Re: Correct way of usingCursor [message #317274 is a reply to message #317259] Wed, 30 April 2008 05:53 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi Michel,
I have the following procedure. I have got the task of tuning procedures as they are taking long time. If you see the cursors below, some tables have more than 50 columns and they all are getting passed throught the refcursor to the calling procedure.
Actually only some of the columns are required by the calling procedures. So in this case do I need to change the way its been coded that is selecting * from tables or selecting only required columns which will require numbers of variables.

I hope this might I made the question clear.

PROCEDURE Get_EBI_InvestShieldLife
(
    
          v_ProductId        VARCHAR2,
          v_CIRRatesType    INT,
          v_MortalityType   INT,
          v_Sex                INT,
          v_Age                INT,
          v_Term            INT,
          v_SumAssured        INT,
          v_YEAR            INT,
          v_Scenario        INT,
          v_ProductSubName    VARCHAR2,
            v_cursor_1 OUT sys_refcursor,
          v_cursor_2 OUT sys_refcursor,
          v_cursor_3 OUT sys_refcursor,
          v_cursor_4 OUT sys_refcursor,
          v_cursor_5 OUT sys_refcursor,
            v_cursor_6 OUT sys_refcursor
)
AS
BEGIN

OPEN   v_cursor_1 FOR 
SELECT *   FROM EBIENG_PRODUCTMASTER
WHERE  PRODUCTSUBNAME = v_ProductSubName
AND EBIENG_PRODUCTMASTER.productcode = v_ProductId;  -- 105 columns

OPEN   v_cursor_2 FOR   
SELECT * FROM EBIENG_CIRRATES
WHERE  EBIENG_CIRRATES.PRODUCTID = v_ProductId
AND EBIENG_CIRRATES.SEX = v_Sex AND 
EBIENG_CIRRATES.TYPE = v_CIRRatesType ;   -- 5 columns

OPEN   v_cursor_3 FOR 
SELECT * FROM EBIENG_MORTALITYCHARGES   -- 42 columns
WHERE
EBIENG_MORTALITYCHARGES.PRODUCTID = v_ProductId
AND
EBIENG_MORTALITYCHARGES.TYPE =  v_MortalityType; 

OPEN   v_cursor_4 FOR 
SELECT * FROM 
EBIENG_PREMIUMALLOCATIONCHARGE     -- 5 columns
WHERE
EBIENG_PREMIUMALLOCATIONCHARGE.PRODUCTID = v_ProductId;

OPEN   v_cursor_5 FOR 
SELECT  *  FROM  EBIENG_FUNDVALUEFACTOR  -- 3 columns
WHERE  
 EBIENG_FUNDVALUEFACTOR.PRODUCTID = v_ProductId;

OPEN   v_cursor_6 FOR
SELECT * FROM EBIENG_SURRENDERVALUE  -- 32 columns
WHERE
EBIENG_SURRENDERVALUE.PRODUCTID =  v_ProductId;

END;


Thanks,
Soni
Re: Correct way of usingCursor [message #317310 is a reply to message #317274] Wed, 30 April 2008 07:52 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look at this article on tuning PL/SQL: http://www.orafaq.com/node/1399

Ross Leishman
Previous Topic: Query optimization
Next Topic: Help Needed
Goto Forum:
  


Current Time: Thu Dec 08 21:56:11 CST 2016

Total time taken to generate the page: 0.22306 seconds