Cursor with parameters in PL/SQL
Ver este post em Portugûes
Here I'm gonna show another technique to work with cursors in PL/SQL. I had to do something at work where it was very useful: cursor that take arguments.
The biggest advantages of parametrized queries is that the server doesn't have to pass through the parse and plan phases ever again (after the first time, of course) and the compile-time checking for syntax errors (when we use string queries we only know any syntax errors in runtime).
That said let's go to examples:
cursor ProductCursor(pname in varchar2) is
select *
from product
where name like pname;
Nice, isn't it? It's like a procedure in terms of syntax. And opening the cursor is like this:
open ProductCursor('A%');
It'll open the cursor with all the products which name starts with 'A'.
We can create a rowtype based var from this cursor:
prod ProductCursor%rowtype;
Now I'm gonna join it all in a script:
declare
-- Cursor
cursor ProductCursor(pname in varchar2) is
select *
from product
where name like pname;
-- Simple loop
prod ProductCursor%rowtype;
-- bulk collect loop
type Tprods is table of ProductCursor%rowtype;
prods Tprods;
i integer;
begin
-- simple loop
open ProductCursor('A%');
loop
fetch ProductCursor into prod;
exit when ProductCursor%notfound;
DBMS_OUTPUT.put_line(prod.nome);
-- other commands
end loop;
close ProductCursor;
-- bulk collect loop
open ProductCursor('A%');
fetch ProductCursor bulk collect into prods;
for i in prods.first .. prods.last loop
DBMS_OUTPUT.put_line(prods(i).nome);
-- another commands using prods(i)
end loop;
close ProductCursor;
end;
Cool, isn't it? See you next time.
