Procedimiento almacenado

Un procedimiento almacenado (también denominado 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 usos de los procedimientos almacenados incluyen la validación de datos (integrados en la base de datos) o 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 declaraciones SQL se puede guardar en procedimientos almacenados y todas las aplicaciones llaman a los procedimientos. Se pueden utilizar procedimientos almacenados anidados ejecutando un procedimiento almacenado desde otro.

Los procedimientos almacenados pueden devolver conjuntos de resultados , es decir, los resultados de una SELECTdeclaración. Dichos conjuntos de resultados se pueden procesar mediante cursores , mediante otros procedimientos almacenados, asociando un localizador de conjuntos de resultados o mediante 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 declaraciones de control de flujo de procedimientos almacenados normalmente incluyen declaraciones IF, WHILE, LOOP, REPEATy , y más. CASELos procedimientos almacenados pueden recibir variables, devolver resultados o modificar variables y devolverlas, dependiendo de cómo y dónde se declara 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 deben invocarse 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. Dependiendo del 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 declaraciones SQL por sí mismos.

La creciente adopción de procedimientos almacenados llevó a la introducción de elementos procedimentales al lenguaje SQL en los estándares SQL:1999 y SQL:2003 en la parte SQL/PSM . Eso 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 para SQL/JRT .

Comparación con SQL estático

Gastos generales: Debido a que las declaraciones de 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 declaraciones y otros métodos para evitar la compilación repetitiva de declaraciones SQL dinámicas). Además, si bien evitan algunos SQL precompilados, las declaraciones aumentan la complejidad de crear un plan de ejecución óptimo porque no todos los argumentos del SQL La declaración se proporciona en el momento de la compilación. Dependiendo de la implementación y configuración de la base de datos específica, se verán resultados de rendimiento mixtos en los procedimientos almacenados versus consultas genéricas o funciones definidas por el usuario.

Evitar el tráfico de red: Una ventaja importante de los procedimientos almacenados es que pueden ejecutarse directamente dentro del motor de la base de datos . En un sistema de producción, esto normalmente significa 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 declaraciones 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 otros lugares de los programas cliente. Esto puede resultar en 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 coherencia de los datos con la ayuda de procedimientos almacenados.

Delegar 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.

Alguna protección contra ataques de inyección SQL: Se pueden utilizar procedimientos almacenados para proteger 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 de parámetro. Sin embargo, un procedimiento almacenado que a su vez genera SQL dinámico utilizando la entrada sigue siendo vulnerable a las inyecciones de SQL a menos que se tomen las precauciones adecuadas.

Otros usos

En algunos sistemas, se pueden utilizar procedimientos almacenados para controlar la gestión de transacciones; en otros, los procedimientos almacenados se ejecutan dentro de una transacción de manera que las transacciones son efectivamente transparentes para ellos. Los procedimientos almacenados también se pueden invocar desde un desencadenador de base de datos o un controlador de condiciones. Por ejemplo, un procedimiento almacenado puede activarse mediante una inserción en una tabla específica o la actualización de un campo específico en una tabla, y se ejecutará el código dentro del procedimiento almacenado. Escribir procedimientos almacenados como manejadores 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 SELECTdeclaraciones, siempre que no manipulen datos. Sin embargo, los procedimientos no pueden incluirse en SELECTlas declaraciones.
Un procedimiento almacenado puede devolver varios valores utilizando el OUTparámetro o no devolver ningún valor.
Un procedimiento almacenado ahorra 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 declaraciones preparadas toman una declaración o consulta ordinaria y la parametrizan para que se puedan usar diferentes valores literales más adelante. Al igual que los procedimientos almacenados, se almacenan en el servidor para mayor eficiencia y brindan cierta protección contra ataques de inyección SQL. Aunque son más simples y declarativas, las declaraciones preparadas normalmente no se escriben para utilizar lógica procedimental y no pueden operar con variables. Debido a su interfaz simple y sus implementaciones del lado del cliente, las declaraciones preparadas son más ampliamente reutilizables entre DBMS.

Comparación con contratos inteligentes

Contrato inteligente es un término que se aplica al código ejecutable almacenado en una cadena de bloques en lugar de un RDBMS. A pesar de que los mecanismos de consenso de resultados de ejecución de las redes públicas de blockchain difieren en principio de las bases de datos privadas o federadas tradicionales, aparentemente realizan la misma función que los procedimientos almacenados, aunque generalmente con un sentido de valor de transacción.

Desventajas

Los lenguajes de procedimientos almacenados suelen ser específicos del proveedor. Cambiar de proveedor de bases de datos normalmente requiere reescribir los procedimientos almacenados existentes.
Es más difícil realizar un seguimiento de los cambios en los procedimientos almacenados dentro de un sistema de control de versiones que en otros códigos. Los cambios deben reproducirse como scripts que se almacenarán en el historial del proyecto para incluirlos, y las diferencias en los procedimientos pueden ser más difíciles de fusionar y rastrear correctamente.
Los errores en los procedimientos almacenados no se pueden detectar como parte de una compilación o paso de construcción en el IDE de una aplicación; lo mismo ocurre si un procedimiento almacenado falta o se elimina accidentalmente.
Los lenguajes de procedimientos almacenados de diferentes proveedores tienen diferentes niveles de sofisticación.
- Por ejemplo, pgpsql de Postgres tiene más funciones de lenguaje (especialmente a través de extensiones) que T-SQL de Microsoft. ^{[ cita necesaria ]}
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 su 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. Idiomas procesales". 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
Una descripción general del soporte del lenguaje procesal PostgreSQL
Usando un procedimiento almacenado en Sybase ASE
Procedimientos PL/SQL
Referencia del lenguaje PL/SQL de la base de datos Oracle
Referencia de SQLScript de SAP HANA