Re: How to create cursor inside begin/end block in pl/sql
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <GMei@xxxxxx>, <Harvinder.Singh@xxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 6 Dec 2006 00:06:00 +0100
Hi Singh,
> if (dummy=1)
> then
> cursor c1 is select id_acc from tab1;
> else
> cursor c1 is select id_acc from tab2;
you may additional to dynamic sql (something like OPEN cv FOR v_sql_text) try
statical cursor with union all.
CURSOR cv(i_id VARCHAR2) IS
SELECT id FROM tab1 where i_id = 1
union all
SELECT id FROM tab2 where i_id != 1
The filters in the execution plan force based on the actual value of the
parameter that at most one part of the union will be performed. See execution
plan below.
This is - I guess - what you mean. (tested in 10g)
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 77 (100)|
| 1 | UNION-ALL | | | | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL| TAB1 | 9999 | 29997 | 38 (0)|
|* 4 | FILTER | | | | |
| 5 | TABLE ACCESS FULL| TAB2 | 9999 | 29997 | 38 (0)|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER(:B1)=1)
4 - filter(TO_NUMBER(:B1)<>1)
Of course for a complex select you should check the execution plan carefully.
Regards,
Jaromir D.B. Nemec
----- Original Message -----
From: Guang Mei
To: Harvinder.Singh@xxxxxxxxxxxxx ; oracle-l
Sent: Tuesday, December 05, 2006 8:48 PM
Subject: RE: How to create cursor inside begin/end block in pl/sql
yes, use dynamic sql
Other related posts:
