André L. S. Desenvolvimento de Softwares, Tecnologia e Games

Recuperando cursor de uma procedure do Oracle no Java

Percebi que muitas pessoas acabam chegando aqui pesquisando uma forma de recuperar cursores através de procedures do Oracle, para estas pessoas eu coloco à disposição uma maneira de fazê-lo.

Para que o cursor possa ser retornado é preciso declara-lo como REF CURSOR no spec da Package.

  --Criando o tipo REF CURSOR que será o cursor
  type g_cursor is ref cursor;

Em ambos, spec e body, você precisa declarar uma variável out do tipo do REF CURSOR mencionado acima.

  procedure PRO_RETORNA_LISTA_CARROS(
    i_id     in     tbl_car.car_id%type,
    o_cursor in out g_cursor);

Para devolver o cursor com os resultados (caso haja), é necessário abri-lo no body da procedure, desta forma:

open o_cursor for
          select car_id, company, model, color, hp, price
          from tbl_car
          where car_id = i_id;

A Package completa ficará assim:

create or replace package PAC_CURSOR is
  --Criando o tipo REF CURSOR que será o cursor
  type g_cursor is ref cursor;

  --Procedure que retornará o cursor
  procedure PRO_RETORNA_LISTA_CARROS(
    i_id     in     tbl_car.car_id%type,
    o_cursor in out g_cursor); -- Nosso cursor

end PAC_CURSOR;
/

create or replace package body PAC_CURSOR is
  procedure PRO_RETORNA_LISTA_CARROS(
    i_id     in     tbl_car.car_id%type,
    o_cursor in out g_cursor) is

       begin
        --Abrindo o cursor para retornar os valores
        open o_cursor for
          select car_id, company, model, color, hp, price
          from tbl_car
          where car_id = i_id;

  end PRO_RETORNA_LISTA_CARROS;

end PAC_CURSOR;

Temos o lado do Oracle pronto, agora precisamos tratar a chamada no Java.

Como o cursor está sendo retornado por uma procedure, usaremos um java.sql.CallableStatement.

CallableStatement cs = conn.prepareCall("{call PAC_CURSOR.PRO_RETORNA_LISTA_CARROS(?,?)}");

O registerOutParameter receberá o tipo oracle.jdbc.OracleTypes.CURSOR e retornará um java.sql.ResultSet. Iteraremos o ResultSet do mesmo modo que iteramos um Iterator.
Cada coluna retornada pelo SELECT será representado como um mapa, usando o getter correpondente. Por exemplo, chamaremos o método getString(<nome coluna>) quando retornar um varchar, getDate(<nome coluna>) quando retornar um date e etc.

O código completo fica assim:

//Chamando o procedure
CallableStatement cs = conn.prepareCall("{call PAC_CURSOR.PRO_RETORNA_LISTA_CARROS(?,?)}");

//Definindo o tipo do retorno, no caso o cursor
cs.registerOutParameter("o_cursor", OracleTypes.CURSOR);
cs.setLong("i_id", id);

cs.execute();//Executando a chamada

//Recuperando o cursor como um Resultset
ResultSet rs = (ResultSet)cs.getObject("o_cursor");

//Iterando as linhas retornadas
while(rs.next()){
	//Obtendo o valor das colunas
	System.out.println("ID: " + rs.getLong("car_id"));
	System.out.println("Marca: " + rs.getString("company"));
	System.out.println("Modelo: " + rs.getString("model"));
	System.out.println("Cor: " + rs.getString("color"));
	System.out.println("HP: " + rs.getString("hp"));
	System.out.println("Preco: " + rs.getFloat("price"));
}

No final você conseguirá obter qualquer valor retornado em um SELECT.

Até a próxima!