Cursor com parâmetros em PL/SQL

View this post in English

Aqui vou mostrar outra técnica para trabalhar com cursores em PL/SQL. Eu tive que fazer um trabalho recentemente onde isso foi muito útil: Cursores que aceitam argumentos.

As maiores vantagem de usar queries parametrizadas no banco de dados é que o servidor não precisa fazer o parse e traçar um plano de execução (ele só faz isso na primeira vez que a query é executada) e ela fica certa em tempo de compilação (ao contrário das queries em string que nós só sabemos se há um erro de sintaxe em tempo de execução).

Dito isso vamos a um exemplo:

cursor ProdutoCursor(pnome in varchar2) is
    select *
    from produto
    where nome like pnome;

Legal, não? A sintaxe fica muito parecida com uma procedure. E o método de abrir o cursor também é legal:

open ProdutoCursor('A%');

Aqui vai abrir o cursor trazendo todos os produtos onde o nome começa com a letra 'A'.

Podemos ainda criar um rowtype a partir deste cursor:

prod ProdutoCursor%rowtype;

Agora vou criar um script para juntar tudo:

declare
    -- Cursor
    cursor ProdutoCursor(pnome in varchar2) is
        select *
        from produto
        where nome like pnome;

    -- Para loop simples
    prod ProdutoCursor%rowtype;

    -- Para loop com bulk collect
    type Tprods is table of ProdutoCursor%rowtype;
    prods Tprods;
    i integer;

begin
    -- Loop simples
    open ProdutoCursor('A%');
    loop
        fetch ProdutoCursor into prod;
        exit when ProdutoCursor%notfound;
        -- comandos a serem executados com o registro prod
        DBMS_OUTPUT.put_line(prod.nome);
    end loop;
    close ProdutoCursor;

    -- Loop com bulk collect
    open ProdutoCursor('A%');
    fetch ProdutoCursor bulk collect into prods;
    for i in prods.first .. prods.last loop
        -- comandos a serem executaos com o registro prods(i)
        DBMS_OUTPUT.put_line(prods(i).nome);
    end loop;
    close ProdutoCursor;
end;

Muito legal, né? Até a próxima.