[ABE.pm] DBI -> PL/SQL question
Phil R Lawrence
phil at five-lawrences.com
Thu Mar 4 10:57:23 CST 2004
[copy also sent to philly.pm]
This question is about bind variables and performance.
With Perl/DBI, I use bind variables in my SQL statements. Then I
prepare them only once, and execute (with varying bind values)
iteratively. This is efficient.
Cool. So now I'm in PL/SQL. But I don't see that PREPARE is available.
All examples are like this:
FUNCTION ...
...
OPEN c_cursor( v_val1, v_val2 );
FETCH c_cursor INTO v_result;
CLOSE c_cursor;
RETURN v_result;
END...
or this:
FUNCTION ...
...
EXECUTE IMMEDIATE
'SELECT foo
INTO v_result
FROM some_table
WHERE bar1 = :1
AND bar2 = :2'
USING v_val1, v_val2;
RETURN v_result;
END...
or this:
FUNCTION ...
...
TYPE cv_type IS REF CURSOR;
dyncur cv_type;
...
OPEN dyncur
FOR f_gimme_a_str_of_sql('bar1','bar2')
USING v_val1, v_val2;
FETCH dyncur INTO v_result
RETURN v_result;
END...
etc., etc., there are lots of variations. So my question is this: How
do I ensure that Oracle will be preparing my SQL statements *only once*?
Or do I not need to worry about this at all? Or do I need to be
careful not to CLOSE my cursors, and then Oracle will do the right thing?
Also, would a package level cursor be relavent to this question? (I am
puttign enerything into a package.) Perhaps somethign like this?
PACKAGE BODY...
CURSOR c_cursor ( in_bar1 VARCHAR2, in_bar2 VARCHAR2 )
IS
SELECT foo
FROM some_table
WHERE bar1 = in_bar1
AND bar2 = in_bar2;
FUNCTION f_get_result ( in_bar1 VARCHAR2, in_bar2 VARCHAR2 )
AS
v_result VARCHAR2(30);
BEGIN
OPEN c_cursor( in_bar1, in_bar2 );
FETCH c_cursor INTO v_result;
-- DON'T CLOSE CURSOR (?)
-- (so Oracle doesn't have to re-prepare next
-- time f_get_result is called???)
RETURN v_result;
END f_get_result;
Or does all that fanciness gain me nothing over just using native
dynamic SQL everytime f_get_result is called?
Thanks,
prl
More information about the ABE-pm
mailing list