Cursores y Procedimientos Almacenados

Introducción a Procedimientos y Funciones Almacenadas

• Las bases de datos ofrecen múltiples opciones de lenguajes de programación.
• Es importante conocer funcionalidades como cursores, procedimientos almacenados, funciones y excepciones
• Se plantea un caso práctico para aproximarse a la teoría de este tema.

Cursores

• Los cursores almacenan datos e información como resultado de una consulta.
• Los datos incluidos en los cursores, se pueden recorrer de forma parecida a un "array".
• Usar cursores es útil cuando una consulta resulta en múltiples filas de una tabla.

Creación de un Cursor

• Se usa la cláusula DECLARE para crear un cursor.
• La sintaxis es: DECLARE <nombre_cursor> [SCROLL] CURSOR FOR <consulta>;
• La cláusula SCROLL es opcional.
• SCROLL habilita el recorrido no secuencial del cursor (inversa o a saltos).
• Un cursor declarado se abre con la cláusula OPEN: OPEN <nombre_cursor>;

Recorrer un Cursor

• Se utiliza la cláusula FETCH para leer un nuevo registro con cada ejecución.
• La sintaxis es: FETCH [NEXT|PRIOR|FIRST|LAST|RELATIVE <num_registros>|ABSOLUTE <num_registros>] FROM <nombre_cursor> INTO <variable1>[, <variable2>, ..., <variableN>];
• Opciones disponibles:
  • NEXT: Devuelve el siguiente registro disponible (por defecto).
  • PRIOR: Devuelve el registro anterior.
  • FIRST: Devuelve el primer registro.
  • LAST: Devuelve el último registro.
  • RELATIVE: Devuelve el registro ubicado a <num_registros> del actual (positivo o negativo).
  • ABSOLUTE: Se ubica en el registro número <num_registros> (positivo o negativo).
• Si se utiliza una cláusula diferente a NEXT, se debe crear el cursor con la cláusula SCROLL.
• Para cerrar un cursor, se utiliza la cláusula CLOSE: CLOSE cursos_Socio;

Modificación o Borrado de Datos

• En SQL/PSM, se puede modificar o eliminar el registro actual del cursor.
• Cláusulas utilizadas:
  • UPDATE
  • DELETE
• Sintaxis:
  • UPDATE <nombre_tabla> SET <campo1> = <valor1>[, <campo2> = <valor2>, ..., <campoN> = <valorN>] WHERE CURRENT OF <nombre_cursor>;
  • DELETE <nombre_tabla> WHERE CURRENT OF <nombre_cursor>;
• Los cursores se combinan con estructuras de control como bucles.

Procedimientos y Funciones Almacenados

• Son bloques de código almacenados en el sistema para ser ejecutados cuando se requiera.
• También son conocidos como subrutinas.
• Características:
  • Procedimiento: Admite parámetros de entrada, manipula datos, y puede generar resultados por parámetros de salida. Se declara con PROCEDURE.
  • Función: Devuelve un valor (resultado de cálculos). Admite parámetros de entrada. Se declara con FUNCTION.

Sintaxis de Declaración de Procedimientos

• La sintaxis de creación es: CREATE PROCEDURE <nombre_proc> ([<param1>[, <param2>, ..., <paramN>]]) <declaraciones> BEGIN <lista_sentencias> END;
• Tipos de parámetros: [IN | OUT | INOUT] <nombre_parámetro> <tipo>
• Se invoca con la cláusula CALL: CALL <nombre_procedimiento> (parámetros);

Tipos de Parámetros de un Procedimiento

• De entrada (IN): No cambian su valor durante la ejecución del procedimiento.
• De salida (OUT): Cambian su valor dentro del procedimiento.
• De entrada y salida (INOUT): El procedimiento puede leer y/o modificar su valor.

Sintaxis de Funciones

• La sintaxis es: CREATE FUNCTION <nombre_funcion> ([<param1>[, <param2>, ..., <paramN>]]) RETURNS <tipo_dato> <declaraciones> BEGIN <lista_sentencias> RETURN <valor_a_devolver>; END;
• Las funciones solo pueden tener parámetros de entrada (IN).
• Requieren la cláusula RETURNS para especificar el tipo de dato que devuelven.
• Requieren la cláusula RETURN para indicar el valor que devuelve.
• Las funciones se invocan como expresiones.

Excepciones: Control de Errores

• Los programas almacenados pueden encontrar condiciones de error.
• Esto se puede dar por varias causas como casuísticas no tomadas cuenta o fallos del sistema.
• En esos casos, la ejecución se detiene y se devuelve un error predeterminado.

Gestor de Excepciones

• Permite indicar qué tarea debe ejecutarse al detectar una excepción.
• Los estados de error devuelven un código numérico.
• La sintaxis para crear un gestor de excepciones es: DECLARE {EXIT|CONTINUE|UNDO} HANDLER FOR <cod_estado1>[, <cod_estado2>, ..., <cod_estadoN>] {<sentencia>| BEGIN [ATOMIC] <lista_sentencias>; END;}
• Campos y cláusulas:
  • EXIT: Abandona la ejecución del código al detectar el error.
  • CONTINUE: Ignora el error y continúa la ejecución.
  • UNDO: Deshace los cambios desde el inicio del bloque de código que causó el error.
  • <cod_estadoN>: Código de error para el cual se define el comportamiento.
  • ATOMIC: Se usa si el handler se declara como UNDO.
  • <lista_sentencias>: Código que ejecutará el gestor de excepciones.

Forzar Excepciones

• A veces es necesario tratar ciertas situaciones como excepciones.
• Se utiliza la cláusula SIGNAL.