Los datos expuestos en este blog, son solo de índole informativo. Por favor realiza siempre una copia de seguridad antes de realizar cualquier cambio en tu proyecto.
Oracle/PLSQL: procedimiento para generar cursores PLSQL dinámicos
Problema: en Oracle, tengo una tabla llamada “vino” y un procedimiento almacenado que genera un cursor basado en la tabla “vino”.
Creé un formulario HTML donde el usuario puede ingresar cualquier combinación de tres valores para recuperar los resultados de la tabla “vino”. Mi problema es que necesito una instrucción SELECT genérica que funcione sin importar el valor que ingrese el usuario.
ejemplo
parámetro_1 = “Chianti”
parámetro_2 = “10”
El usuario no ingresa el parámetro_3=, pero tengo que usarlo en la declaración de selección. Aquí está mi problema. ¿Cómo puedo inicializar este parámetro para obtener todas las filas de la columna 3?
SELECT * FROM wine WHERE column1 = parameter_1 AND column2 = parameter_2 AND column3 = parameter_3;
La salida de mi procedimiento almacenado debe ser un cursor.
Respuesta: Para resolver su problema, necesita exportar un cursor PLSQL dinámico en Oracle.
Veamos cómo podemos hacer esto. Dividimos este proceso en 3 pasos.
Paso 1 – Definición de la tabla
Primero, necesitamos crear una tabla llamada “vino” en Oracle. A continuación se muestra la declaración de creación de la mesa de vinos.
CREATE TABLE wine ( col1 varchar2(40), col2 varchar2(40), col3 varchar2(40) );
Para propósitos de demostración, mantenemos esta definición de tabla muy simple.
Paso 2 – Crear paquete
A continuación, creamos un paquete llamado “winepkg” que contiene nuestras definiciones de cursor. Esto es necesario para que podamos usar el cursor como un parámetro de salida en el procedimiento almacenado.
CREATE OR REPLACE PACKAGE winepkg IS /* Define the REF CURSOR type. */ TYPE wine_type IS REF CURSOR RETURN wine%ROWTYPE; END winepkg;
El cursor aceptará todos los campos de la tabla “vino”.
Paso 3: crear un procedimiento almacenado
Nuestro paso final es crear un procedimiento almacenado para devolver el cursor. Acepta tres parámetros (ingresados por el usuario en el formulario HTML) y devuelve el cursor (c1) de tipo “wine_type” declarado en el paso 2.
El procedimiento determinará el cursor apropiado para devolver en función del valor ingresado por el usuario (parámetro de entrada).
CREATE OR REPLACE PROCEDURE find_wine2
(col1_in in varchar2,
col2_in in varchar2,
col3_in in varchar2,
c1 out winepkg.wine_type)
IS
BEGIN
/* all columns were entered */
IF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
THEN
OPEN c1 FOR
SELECT *
FROM wine
WHERE wine.col1 = col1_in
AND wine.col2 = col2_in
AND wine.col3 = col3_in;
/* col1 and col2 were entered */
ELSIF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
THEN
OPEN c1 FOR
SELECT *
FROM wine
WHERE wine.col1 = col1_in
AND wine.col2 = col2_in;
/* col1 and col3 were entered */
ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
THEN
OPEN c1 FOR
SELECT *
FROM wine
WHERE wine.col1 = col1_in
wine.col3 = col3_in;
/* col2 and col3 where entered */
ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
THEN
OPEN c1 FOR
SELECT *
FROM wine
WHERE wine.col2 = col2_in
AND wine.col3 = col3_in;
/* col1 was entered */
ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) = 0)
THEN
OPEN c1 FOR
SELECT *
FROM wine
WHERE wine.col1 = col1_in;
/* col2 was entered */
ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
THEN
OPEN c1 FOR
SELECT *
FROM wine
WHERE wine.col2 = col2_in;
/* col3 was entered */
ELSIF (length(col1_in) = 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
THEN
OPEN c1 FOR
SELECT *
FROM wine
WHERE wine.col3 = col3_in;
END IF;
END find_wine2;