Procedimiento almacenado

Un procedimiento almacenado (también denominado prc , proc , storp , sproc , StoPro , StoredProc , StoreProc , sp o SP ) es una subrutina disponible para las aplicaciones que acceden a un sistema de gestión de bases de datos relacionales (RDBMS). Dichos procedimientos se almacenan en el diccionario de datos de la base de datos .

Los procedimientos almacenados se utilizan para la validación de datos (integrados en la base de datos) o para mecanismos de control de acceso . Además, los procedimientos almacenados pueden consolidar y centralizar la lógica que se implementó originalmente en las aplicaciones. Para ahorrar tiempo y memoria, el procesamiento extenso o complejo que requiere la ejecución de varias sentencias SQL se puede guardar en procedimientos almacenados, y todas las aplicaciones pueden llamar a los procedimientos. Se pueden utilizar procedimientos almacenados anidados ejecutando un procedimiento almacenado desde dentro de otro.

Los procedimientos almacenados pueden devolver conjuntos de resultados , es decir, los resultados de una SELECTinstrucción. Dichos conjuntos de resultados se pueden procesar utilizando cursores , por otros procedimientos almacenados, asociando un localizador de conjunto de resultados o por aplicaciones. Los procedimientos almacenados también pueden contener variables declaradas para procesar datos y cursores que le permiten recorrer varias filas de una tabla. Las instrucciones de control de flujo de procedimientos almacenados suelen incluir instrucciones IF, WHILE, LOOP, REPEATy CASE, entre otras. Los procedimientos almacenados pueden recibir variables, devolver resultados o modificar variables y devolverlas, según cómo y dónde se declare la variable.

Implementación

Los procedimientos almacenados son similares a las funciones definidas por el usuario (UDF). La principal diferencia es que las UDF se pueden utilizar como cualquier otra expresión dentro de las sentencias SQL, mientras que los procedimientos almacenados se deben invocar mediante la CALLsentencia. ^[1]

Procedimiento de LLAMADA(...)

EJECUTAR procedimiento(...)

La implementación exacta y correcta de los procedimientos almacenados varía de un sistema de base de datos a otro. La mayoría de los principales proveedores de bases de datos los admiten de alguna forma. Según el sistema de base de datos, los procedimientos almacenados se pueden implementar en una variedad de lenguajes de programación , por ejemplo , SQL , Java , C o C++ . Los procedimientos almacenados escritos en lenguajes distintos de SQL pueden o no ejecutar sentencias SQL por sí mismos.

La creciente adopción de procedimientos almacenados condujo a la introducción de elementos procedimentales en el lenguaje SQL en los estándares SQL:1999 y SQL:2003 en la parte SQL/PSM . Esto convirtió a SQL en un lenguaje de programación imperativo . La mayoría de los sistemas de bases de datos ofrecen extensiones propietarias y específicas del proveedor, que superan a SQL/PSM. Existe una especificación estándar para los procedimientos almacenados de Java , así como SQL/JRT .

Comparación con SQL estático

Arriba: Debido a que las instrucciones de los procedimientos almacenados se almacenan directamente en la base de datos, pueden eliminar total o parcialmente la sobrecarga de compilación que normalmente se necesita cuando las aplicaciones de software envían consultas SQL en línea (dinámicas) a una base de datos. (Sin embargo, la mayoría de los sistemas de bases de datos implementan cachés de instrucciones y otros métodos para evitar la compilación repetitiva de instrucciones SQL dinámicas). Además, si bien evitan parte del SQL precompilado, las instrucciones aumentan la complejidad de la creación de un plan de ejecución óptimo porque no todos los argumentos de la instrucción SQL se suministran en el momento de la compilación. Según la implementación y la configuración específicas de la base de datos, se observarán resultados de rendimiento mixtos de los procedimientos almacenados en comparación con las consultas genéricas o las funciones definidas por el usuario.

Cómo evitar el tráfico de red: Una de las principales ventajas de los procedimientos almacenados es que pueden ejecutarse directamente dentro del motor de base de datos . En un sistema de producción, esto suele significar que los procedimientos se ejecutan completamente en un servidor de base de datos especializado con acceso directo a los datos. El beneficio es que ahorra costos de red, lo que se destaca cuando se trata de una serie de sentencias SQL.

Encapsulando la lógica empresarial: Los procedimientos almacenados permiten a los programadores incorporar lógica empresarial como una API en la base de datos, lo que puede simplificar la gestión de datos y reducir la necesidad de codificar la lógica en otras partes de los programas cliente. Esto puede dar como resultado una menor probabilidad de corrupción de datos por parte de programas cliente defectuosos. El sistema de base de datos puede garantizar la integridad y la coherencia de los datos con la ayuda de procedimientos almacenados.

Delegación de derechos de acceso: En muchos sistemas, a los procedimientos almacenados se les pueden otorgar derechos de acceso a la base de datos que los usuarios que ejecutan esos procedimientos no tienen directamente.

Algunas protecciones contra ataques de inyección SQL: Los procedimientos almacenados se pueden utilizar para protegerse contra ataques de inyección. Los parámetros de los procedimientos almacenados se tratarán como datos incluso si un atacante inserta comandos SQL. Además, algunos DBMS comprobarán el tipo del parámetro. Sin embargo, un procedimiento almacenado que a su vez genera SQL dinámico utilizando la entrada sigue siendo vulnerable a las inyecciones SQL a menos que se tomen las precauciones adecuadas.

Otros usos

En algunos sistemas, los procedimientos almacenados se pueden utilizar para controlar la gestión de transacciones; en otros, los procedimientos almacenados se ejecutan dentro de una transacción de modo que las transacciones sean efectivamente transparentes para ellos. Los procedimientos almacenados también se pueden invocar desde un disparador de base de datos o un controlador de condiciones. Por ejemplo, un procedimiento almacenado se puede activar mediante una inserción en una tabla específica o la actualización de un campo específico en una tabla, y se ejecutaría el código dentro del procedimiento almacenado. Escribir procedimientos almacenados como controladores de condiciones también permite a los administradores de bases de datos rastrear errores en el sistema con mayor detalle mediante el uso de procedimientos almacenados para detectar los errores y registrar cierta información de auditoría en la base de datos o en un recurso externo como un archivo.

Comparación con funciones

Una función es un subprograma escrito para realizar ciertos cálculos.
Una función escalar devuelve solo un valor (o NULL), mientras que una función de tabla devuelve una tabla (relacional) que comprende cero o más filas, cada fila con una o más columnas.
Las funciones deben devolver un valor (usando la RETURNpalabra clave), pero para los procedimientos almacenados esto no es obligatorio.
Los procedimientos almacenados pueden usar RETURNpalabras clave pero sin pasar ningún valor.
Se pueden utilizar funciones en SELECTlas instrucciones, siempre que no manipulen datos. Sin embargo, no se pueden incluir procedimientos en SELECTlas instrucciones.
Un procedimiento almacenado puede devolver múltiples valores utilizando el OUTparámetro o no devolver ningún valor.
Un procedimiento almacenado ahorra el tiempo de compilación de la consulta.
Un procedimiento almacenado es un objeto de base de datos .
Un procedimiento almacenado es un objeto material.

Comparación con declaraciones preparadas

Las sentencias preparadas toman una sentencia o consulta ordinaria y la parametrizan de modo que se puedan utilizar diferentes valores literales en un momento posterior. Al igual que los procedimientos almacenados, se almacenan en el servidor para lograr eficiencia y brindar cierta protección contra ataques de inyección SQL. Aunque son más simples y declarativas, las sentencias preparadas no se escriben normalmente para utilizar lógica procedimental y no pueden operar sobre variables. Debido a su interfaz simple y a sus implementaciones del lado del cliente, las sentencias preparadas se pueden reutilizar más ampliamente entre DBMS.

Comparación con contratos inteligentes

El término contrato inteligente se aplica al código ejecutable almacenado en una cadena de bloques, en lugar de en un sistema de gestión de bases de datos relacionales (RDBMS). A pesar de que los mecanismos de consenso de los resultados de ejecución de las redes de cadenas de bloques públicas difieren en principio de las bases de datos privadas o federadas tradicionales, en apariencia cumplen la misma función que los procedimientos almacenados, aunque normalmente con un sentido de transacción de valor.

Desventajas

Los lenguajes de procedimientos almacenados suelen ser específicos de cada proveedor. Cambiar de proveedor de bases de datos suele requerir reescribir los procedimientos almacenados existentes.
Es más difícil hacer un seguimiento de los cambios en los procedimientos almacenados dentro de un sistema de control de versiones que de otros códigos. Los cambios deben reproducirse como scripts que se almacenarán en el historial del proyecto para que se incluyan, y las diferencias en los procedimientos pueden ser más difíciles de combinar y rastrear correctamente.
Los errores en los procedimientos almacenados no se pueden detectar como parte de un paso de compilación o construcción en el IDE de una aplicación; lo mismo sucede si un procedimiento almacenado se pierde o se elimina accidentalmente.
Los lenguajes de procedimientos almacenados de diferentes proveedores tienen diferentes niveles de sofisticación.
El soporte de herramientas para escribir y depurar procedimientos almacenados a menudo no es tan bueno como para otros lenguajes de programación, pero esto difiere entre proveedores y lenguajes.
- Por ejemplo, tanto PL/SQL como T-SQL tienen IDE y depuradores dedicados. PL/PgSQL se puede depurar desde varios IDE.

Referencias

^ "Db2 12 - Programación de aplicaciones y SQL - Llamada a un procedimiento almacenado desde la aplicación". www.ibm.com . Consultado el 26 de mayo de 2022 .
^ "Capítulo 11. Guía del lenguaje de procedimientos SQL". Documentación de OpenLink . Consultado el 11 de septiembre de 2019 .
^ "Capítulo 42. Lenguajes procedimentales". Documentación de PostgreSQL . 9 de noviembre de 2023 . Consultado el 20 de noviembre de 2023 .

Enlaces externos

Preguntas frecuentes sobre procedimientos almacenados en MySQL
Descripción general de la compatibilidad del lenguaje procedimental PostgreSQL
Uso de un procedimiento almacenado en Sybase ASE
Procedimientos PL/SQL
Referencia del lenguaje PL/SQL de Oracle Database
Referencia de SQLScript para SAP HANA