Si necesitas utilizar procedimientos almacenados con Oracle desde Java y te aburre o, simplemente, no te apetece leerte la documentación de Oracle (o no te parece clara), este este tu artículo.
Yo sabía SQL, pero nunca había hecho procedimientos almacenados en Oracle. Tras un pequeño, pero intenso, periodo de aprendizaje, fui capaz de renuir la información necesaria para realizar un proyecto J2EE sin utilizar ni un solo SQL en el código Java, todo a base de procedimientos almacenados (era un requerimiento del cliente). Bueno, y para no aburriros, iré directamente al grano. Os voy a resumir las técnicas que, a mi entender, son básicas pero suficientes para utilizar PL-SQL de Oracle desde Java.

Utilicemos la técnica de “un ejemplo vale más que mil palabras” que tanto nos gusta a los programadores más inquietos. En vez de extenderme en explicar en detalle cual es el formato y la estructura completa de un PL-SQL, (lo cual me llevaria muchas lineas y tiempo, además de que para eso ya hay tutorial o la propia documentación de Oracle), simplemente pondré un PL-SQL elemental, pero completito, y explicaré en detalle cada una de sus partes. Listo para cut&paste y search&replace.

Supongamos que tenemos una tabla llamada “tabla” con un solo campo llamado “campo” (original eh?)

CREATE OR REPLACE PACKAGE nombrePaquete IS

TYPE vCursor IS REF CURSOR;

PROCEDURE procedimiento (
  datoABuscar IN tabla.campo%TYPE,
  otroDato IN tabla.campo%TYPE,
  parametroTres OUT NUMBER,
  vUserCursor OUT vCursor
);

END nombrePaquete;

/

CREATE OR REPLACE PACKAGE BODY nombrePaquete IS

PROCEDURE procedimiento (
  datoABuscar IN tabla.campo%TYPE,
  otroDato IN tabla.campo%TYPE,
  parametroTres OUT NUMBER,
  vUserCursor OUT vCursor
) IS
vTotal NUMBER;
BEGIN

	SELECT COUNT(*) INTO vTotal
		FROM tabla WHERE campo = datoABuscar;

	IF vTotal = 0 THEN
		INSERT INTO tabla (campo) VALUES (otroDato);
	ELSE
		UPDATE tabla SET campo = otroDato WHERE campo = datoABuscar;
	END IF;

	SELECT COUNT(*) INTO parametroTres
		FROM tabla WHERE campo = datoABuscar;

	OPEN vUserCursor FOR
		SELECT campo FROM tabla WHERE campo = otroDato;

END procedimiento;

END nombrePaquete;
/

Lo que hace el PL-SQL es poco útil, casi absurdo. Su única funcionalidad es la de servir como ejemplo durante este artículo.

El PL-SQL debe ir en un fichero de texto independiente. De esta manera podemos modificarlo con nuestro editor favorito y volverlo a ejecutar depués cuando queramos.
Por esta misma razón, el script empieza con CREATE OR REPLACE, de manera que aunque se ejecute por primera vez (cuando los procedimientos no existen) o por segunda o tercera vez (cuando ya están creados) siempre funcionará.

El Script se compone de dos partes muy parecidas: la primera, que solo define las cabeceras de los procedimientos y la segunda, en la que se define el cuerpo de los mismos.
La diferencia entre una y otra es que la primera empieza con

CREATE OR REPLACE PACKAGE nombrePaquete IS

y la segunda con

CREATE OR REPLACE PACKAGE BODY nombrePaquete IS

Podríamos haberlas creado en dos archivos distintos y ejecutarlas una después de otra, pero me gusta más hacerlo todo junto. Esto es importante: dado que lo hacemos así (todo junto en mismo archivo), es necesario separar las dos partes por una barra invertida /. Podeis ver la barra de separación:

END nombrePaquete;

/

CREATE OR REPLACE PACKAGE BODY nombrePaquete IS

Esa barra separa las dos partes. Si no la ponéis no funcionará.

Bueno, cada parte define el nombre del paquete en “nombrePaquete”. Ni que decir que en las dos partes debe ser el mismo nombre y que deben de acabar con:

END nombrePaquete

Cuando creemos procedimientos en este paquete, para llamarlos tendremos que hacerlo con el nombre nombrePaquete.procedimiento

Volvamos a la parte de la declaración (la primera) de los procedimientos. Aquí se declaran las cabeceras de los mismos, es decir, se pone el procedimiento entero, tal cual, pero sin el cuerpo. Una recomendación es que primero se haga el cuerpo (en la segunda parte) y luego se copie y pegue para hacer la cabecera, así no nos equivocaremos en el nombre de los parámetros etc.
Además, tenemos también la declaración de nuevos tipos. Por ahora solo nos hace falta un CURSOR, el cual nos servirá para retornar conjuntos de datos a Java (por medio de un ResultSet). Esto se hace así:

TYPE vCursor IS REF CURSOR;

El nombre vCursor no es obligatorio, podeis usar el que queráis.

Para definir un procedimiento, debemos especificar el nombre del mismo y los parámetros que acepta. Los parámetros pueden ser de entrada (IN), de salida (OUT) o de entrada y salida (INOUT). Yo prefiero utilizar los IN por un lado y los OUT por otro, por lo que no uso los INOUT. Vamos a ver nuestro ejemplo:

PROCEDURE procedimiento (
  datoABuscar IN tabla.campo%TYPE,
  otroDato IN tabla.campo%TYPE,
  parametroTres OUT NUMBER,
  vUserCursor OUT vCursor
);

Aquí se definen 4 parámetros, 2 de entrada y 2 de salida. Al lado de cada parámetro hay que ponerle el tipo al que pertenece. Esto podemos hacerlo de dos formas: especificando directamente el tipo (NUMBER o nuestro cursor, vCursor, que es el tipo que hemos declarado justo antes como CURSOR), o la referencia al tipo de un campo de una tabla.
Así, en nuestro ejemplo, datoABuscar y otroDato son del tipo del campo “campo” de la tabla “tabla”. Es una manera de ahorrarnos pensar o buscar el tipo de una variable o campo, cuando este va a ser utilizado siempre con un campo (en un INSERT o UPDATE por ejemplo).
En este otro ejemplo veréis mucho mas claro la utilidad:

PROCEDURE updateUser (
  vId       IN USER.ID%TYPE,
  vNick     IN USER.NICK%TYPE,
  vEmail    IN USER.EMAIL%TYPE,
  vPwd      IN USER.PASSWORD%TYPE,
  vBirthday IN USER.BIRTHDAY%TYPE
) IS
BEGIN
	UPDATE USER
	SET
		NICK     = vUserNick,
		EMAIL    = vUserEmail,
		PASSWORD = vUserPwd,
		BIRTHDAY = vBirthday
    WHERE ID = vId;
END updateUser;

Mucho más comodo que poner uno a uno los tipo de cada variable. Usando los tipos de los campos en los que serán utilizados nos despreocupamos.

Ya hemos definido nuestra cabecera. En resumen, se compone de la definición de un tipo CURSOR y un procedimiento con parámetros. Ahora definamos el cuerpo de los procedimientos en la segunda parte.

En nuestro ejemplo utilizamos, además, una variable temporal llamada vTotal, de tipo NUMBER. Esta variable se declara entre el IS y el BEGIN de nuestro procedimiento. Es opcional, es decir, podemos dejar esta parte del procedimiento vacía.

El procedimiento usa la sentencia SELECT COUNT(*) para contar registros. El resultado se puede guardar en nuestra variable temporal vTotal, o se puede guardar en el parámetro de salida parametroTres, el cual podremos leer desde Java. Para esto usamos la cláusula INTO variable:

SELECT COUNT(*) INTO vTotal
	FROM tabla WHERE campo = datoABuscar;
...
SELECT COUNT(*) INTO parametroTres
	FROM tabla WHERE campo = datoABuscar;

Si queremos hacer una consulta y devolver sus resultados en un ResultSet, tenemos que utilizar el parámetro de salida de tipo CURSOR como medio de transporte. Esto se hace añadiendo OPEN vUserCursor FOR antes de la consulta, tal y como sigue:

OPEN vUserCursor FOR
	SELECT campo FROM tabla WHERE campo = otroDato;

Ya sabemos devolver valores simples y conjuntos de resultados en cursores.

El resto del procedimiento como las condiciones, el INSERT y el UPDATE se explican por si mismos.
Vamos directamente a utilizar este procedimiento desde Java.
Para llamar a nuestro procedimiento lo haremos así:

String sql ="{call nombrePaquete.procedimiento (?,?,?,?)}";
CallableStatement cs = con.prepareCall(sql);

Como esto es un poco rollo hacerlo así, sobre todo si tenemos 200 procedimientos distintos, y tenemos todas las llamadas desperdigadas por nuestro fuentes, primero crearemos un método Java que nos genere las cadenas SQL para llamara a estos procedimientos. (Y de paso, un método muy comodo para cerrar conexiones, resultset y todo)

public class SQLTools {
    public static String buildProcedureCall(String packageName, String procedureName, int paramCount) {
        StringBuffer sb = new StringBuffer("{call "+packageName+"."+procedureName+"(");
        for (int n = 1; n <= paramCount; n++) {
            sb.append("?");
            if (n < paramCount) sb.append(",");
        }
        return sb.append(")}").toString();
    }

    public static void close(ResultSet rs, Statement s, Connection c) {
        try { if (rs != null) rs.close(); } catch (Exception e) {}
        try { if (s != null) s.close(); } catch (Exception e) {}
        try { if (c != null) c.close(); } catch (Exception e) {}
    }
}

Y cuando queramos crear procedimientos, lo haremos dentro de una clase de constantes así:

public abstract class Constants {
    public static String PROCEDIMIENTO1 =
            SQLTools.buildProcedureCall("nombrePaquete", "procedimiento", 4);

    public static String PROCEDIMIENTO2 =
            SQLTools.buildProcedureCall("nombrePaquete", "otroProcedimiento", 6);

    public static String PROCEDIMIENTO3 =
            SQLTools.buildProcedureCall("nombrePaquete", "yOtroMasProcedimiento", 2);
}

Más comodo y sencillo, ?no?

Ahora vamos a utilizar nuestro ejemplo desde Java (por fín!):

import oracle.jdbc.OracleTypes;

public class EjemploDao {

  public int ejemplo(int datoABuscar, int otroDato) {
    Connection con = null;
    ResultSet rs = null;
    CallableStatement cs = null;
    int resultado = -1;
    try {
        con = dataSource.getConnection(); // Cambiar por tu implementación favorita
        cs = con.prepareCall(Constants.PROCEDIMIENTO1);
        int pos = 0;

        // Cargamos los parametros de entrada IN
        cs.setInt(++pos, datoABuscar);
        cs.setInt(++pos, otroDato);

        // Registramos los parametro de salida OUT
        cs.registerOutParameter(++pos, java.sql.Types.INTEGER);
        cs.registerOutParameter(++pos, OracleTypes.CURSOR);

        // Ejecutamos
        cs.execute();

        // Cosechamos los parametros de salida OUT
        resultado = cs.getInt(3);           // Nuestro number
        rs = (ResultSet) cs.getObject(4);   // Nuestro cursor, convertido en ResultSet
        while (rs.next()) {
            // Aqui hacemos lo que queramos...
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        SQLTools.close(rs, cs, con);
    }
    return resultado;
  }
}

Nuestro ejemplo absurdo ya tiene su codigo Java correspondiente que lo ejecuta. El código se explica por si mismo (espero), pero resumiremos los pasos utilizados:

• Crear una conexion y un CallableStatement. Se utiliza nuestra clase de constantes Constants, la cual utiliza SQLTools.buildProcedureCall para generar las cadenas SQL de llamada.
• Cargamos los parámetros de entrada. Fijaos el truco de utilizar un contador (pos): lo hace más cómodo que llevar la cuenta a mano, ?verdad? Sobre todo si luego tienes que insertar un parámetro más entre medias de otro, así no tienes que alterar el orden de los numeros.
• Registramos los parámetros de salida. En este caso tenemos un INT y un CURSOR, los cuales se mapean con sus clases correspondientes.
• Ejecutamos el procedimiento y recogemos los parámetros, utilizando el método apropiado y su casting correspondiente.
• Operamos el ResultSet, retornamos el valor del parametro OUT parametroTres y cerramos todos los objetos en el finally.

Bueno, espero que este artículo tan “particular” os haya ayudado a empezar a utilizar Oracle PL-SQL en Java de la manera más rápida posible.
Pese a que no es un verdadero tutorial ni explica realmente nada, me baso en que cualquier programador intrépido puede coger el código aquí presentado, modificarlo y aprovecharlo en su proyecto en mucho menos tiempo que si se lee la documentación de Oracle y Java y tiene que picarse todo a mano.
De todas formas, espero que esto sea solo una especie de “QuickStart Guide”. Con PL-SQL se pueden hacer muchísimas cosas más y muy utiles. Esto es solo el principio, desde aquí os animo a seguir investigando y leyendo documentación. No teneis excusa: la base ya la tenéis hecha!
Si alguien quiere más ejemplos o algo le parece poco claro, puede escribir un comentario. Saludos!

PD.: Si has llegado hasta aquí, significa que te ha interesado. Como regalo, aquí tienes un poco de documentación

Actualización: para crear una conexión con la base de datos, ver el post Crear una conexión JDBC

Categoría Java/Programación
Trackback | RSS 2.0 Comentarios | Hacer comentario »
| Insertar en del.icio.us