| How to use bind variables in cursors? [message #606325] |
Wed, 22 January 2014 06:47  |
 |
adityalsr89
Messages: 3
Registered: January 2014
|
Junior Member |
|
|
Please help me how to use bind variables in a cursor. I have seen people helping out with parameterized cursor but is it possible to use bind variables in a cursor?
Thanks
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to use bind variables in cursors? [message #606366 is a reply to message #606365] |
Thu, 23 January 2014 00:44  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
No it is not.
The correct correct should be (not the format done using How to use [code] tags and make your code easier to read:
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = :1'
using i;
fetch l_rc into l_dummy;
close l_rc;
-- dbms_output.put_line(l_dummy);
end loop;
dbms_output.put_line
(round((dbms_utility.get_time-l_start)/100, 2) ||
' Seconds...' );
end;
/
See the difference in execution times.
Yours:
SQL> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for
10 'select object_name
11 from all_objects
12 where object_id = ' || i;
13 fetch l_rc into l_dummy;
14 close l_rc;
15 -- dbms_output.put_line(l_dummy);
16 end loop;
17 dbms_output.put_line
18 (round((dbms_utility.get_time-l_start)/100, 2) ||
19 ' Seconds...' );
20 end;
21 /
31.25 Seconds...
PL/SQL procedure successfully completed.
Mine:
SQL> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for
10 'select object_name
11 from all_objects
12 where object_id = :1'
13 using i;
14 fetch l_rc into l_dummy;
15 close l_rc;
16 -- dbms_output.put_line(l_dummy);
17 end loop;
18 dbms_output.put_line
19 (round((dbms_utility.get_time-l_start)/100, 2) ||
20 ' Seconds...' );
21 end;
22 /
.61 Seconds...
PL/SQL procedure successfully completed.
[Updated on: Thu, 23 January 2014 00:45] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: How to use bind variables in cursors? [message #606377 is a reply to message #606368] |
Thu, 23 January 2014 02:31 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
adityalsr89 wrote on Thu, 23 January 2014 07:15
But as you have mentioned in above posts, All PL/SQL variables / parameters used in sql are automatically treated as bind variables.
dariyoosh already pointed out that doesn't apply to dynamic sql.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
