Criando Procedures com Cursor como parâmetro

[Maiko]

Boa noite galera,

Esse é meu primeiro Post aqui no Fórum, espero passar/repassar conhecimento e adquirir muito conhecimento com vocês.
Enfim...

Teria como passar em uma procedure um cursor como parâmetro, por exemplo como temos no Delphi e em tantas outras linguagens a passagem de classe.

Abraços.

[paulochagas]

Ola Maiko

dá uma pesquisada sobre REF_CURSOR, aqui mesmo no forum existe bons exemplos para uso.

[Maiko]

Olá paulochagas,

Sim, já havia visto.
Mas, assim...

Tenho um cursor "pré-definido" e quero usa-lo, exempo tosco:

Selecionar tudo

 
type cExemplo is Ref cursor; 

Procedure Teste ( <Passar o Cursor aqui> ) is
begin
end;

 

Obrigado.

[gfkauer]

Olá Maiko

Eu até hoje não vi construções em que se passa um cursor por parametro.
Mas já vi algo próximo a isto...

Na assinatura da rotina em vez de uma variavel do tipo cursos tem uma variavel do tipo Varchar2 pela qual se passa o select em questão e dentro da rotina este select era executado via execute immediate.

Talvez existam outras maneiras, porém eu na minha pobre ignorância sempre opto pelo que considero mais "comum" para evitar problemas com manutenções futuras...

Abs[]

[SergioLBJr]

Cara tu pode usar o refcursor como já lhe foi sugerido, alias aqui na empresa nos utilizamos para montar forms baseados em procedures, no qual o retorno da procedure é um cursor que popula o bloco.

Também da pra passar records como parâmetros.

Selecionar tudo

CREATE OR REPLACE PROCEDURE teste_ser (p_teste dual%ROWTYPE) IS
BEGIN
     dbms_output.put_line('1');
END;

Selecionar tudo

DECLARE
       CURSOR C_TESTE IS
              SELECT 'A' 
              FROM dual 
              UNION 
              SELECT 'B' 
              FROM dual;
       
       R_TESTE   C_TESTE%ROWTYPE; 
BEGIN
     OPEN C_TESTE;
     FETCH C_TESTE INTO R_TESTE;
     CLOSE C_TESTE;
     
     teste_ser(R_TESTE);
END;

[fsitja]

Olá, como boa prática é recomendado utilizar cursor variable (também chamado de ref cursor), na passagem de parâmetros quando se precisa transferir um result set.

Há várias vantagens:

1 - Quando é feita passagem de SQL por parâmetro com uso de SQL dinâmico (seja nativo ou execute immediate), há uma preocupação séria com SQL injection e fraqueza nos testes, pois o SQL não é validado na compilação (grants, permissões e sintaxe do SQL) e o binding de variáveis precisa ser feito manualmente para não prejudicar o desempenho fazendo milhares de parses no banco de dados, do mesmo SQL.

2 - Passando a tabela (result set) toda via parâmetro pode parecer tentador também, mas ocupa uma área grande em memória na chamada da procedure e obriga a interface entre o banco e o programa a transferir tudo via rede, além de poder causar dores-de-cabeça na conversão de data types entre origem (Oracle) e o frontend (Delphi, Java, C#, etc).

Como sugestão, você pode usar sys_refcursor, ou um ref cursor com strong types (mais seguros e fáceis de usar).

No exemplo abaixo, usei o mais flexível, sys_refcursor, porém mais sujeito a erros de tipificação durante o fetch.

Selecionar tudo

SQL> set serveroutput on
SQL> create or replace procedure teste_param_cursor(p_cur in out sys_refcursor) as
  2  begin
  3    open p_cur for
  4      select t.owner, t.table_name
  5        from all_tables t
  6       where rownum <= 10;
  7  end;
  8  /
 
Procedure created
SQL> declare
  2    cur_param sys_refcursor;
  3    type t_cur_all_tables is record (owner all_tables.owner%type,
  4                                  table_name all_tables.table_name%type);
  5    v_all_tables t_cur_all_tables;
  6  begin
  7    teste_param_cursor(cur_param);
  8    loop
  9      fetch cur_param
 10        into v_all_tables;
 11      exit when cur_param%notfound;
 12      dbms_output.put_line('OWNER = ' || v_all_tables.owner || ' / TABLE = ' || v_all_tables.table_name);
 13    end loop;
 14  end;
 15  /
 
OWNER = SYS / TABLE = DUAL
OWNER = SYS / TABLE = SYSTEM_PRIVILEGE_MAP
OWNER = SYS / TABLE = TABLE_PRIVILEGE_MAP
OWNER = SYS / TABLE = STMT_AUDIT_OPTION_MAP
OWNER = SYS / TABLE = WRI$_ADV_ASA_RECO_DATA
OWNER = SYSTEM / TABLE = DEF$_TEMP$LOB
OWNER = SYSTEM / TABLE = OL$
OWNER = SYSTEM / TABLE = OL$HINTS
OWNER = SYSTEM / TABLE = OL$NODES
OWNER = SYS / TABLE = IMPDP_STATS
 
PL/SQL procedure successfully completed
 
SQL> 

[fsitja]

Segue abaixo a documentação com exemplos e referência de uso:
http://download.oracle.com/docs/cd/E118 ... LNPLS01312
http://download.oracle.com/docs/cd/E118 ... LNPLS01312

[fsitja]

Desculpe, linkei duas vezes a mesma URL:
http://download.oracle.com/docs/cd/E118 ... ADFNS00903