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, entre otras cosas, para la validación de datos (integrada en la base de datos) o para mecanismos de control de acceso . Además, permiten consolidar y centralizar la lógica implementada originalmente en las aplicaciones. Para ahorrar tiempo y memoria, el procesamiento extenso o complejo que requiere la ejecución de varias sentencias SQL puede guardarse en procedimientos almacenados, que luego son llamados por todas las aplicaciones. Es posible utilizar procedimientos almacenados anidados, ejecutando uno desde dentro de otro.

Los procedimientos almacenados pueden devolver conjuntos de resultados , es decir, los resultados de una SELECTinstrucción. Dichos conjuntos de resultados pueden procesarse mediante cursores , por otros procedimientos almacenados, asociando un localizador de conjuntos de resultados o por aplicaciones. Los procedimientos almacenados también pueden contener variables declaradas para procesar datos y cursores que permiten iterar a través de varias filas en una tabla. Las instrucciones de control de flujo de los procedimientos almacenados suelen incluir instrucciones IF, WHILE, LOOP, REPEAT, y CASE, entre otras. Los procedimientos almacenados pueden recibir variables, devolver resultados o modificar variables y devolverlas, dependiendo de cómo y dónde se declare la variable.

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

Procedimiento de llamada (...)

o

EJECUTAR procedimiento(...)

La implementación precisa y correcta de los procedimientos almacenados varía según el sistema de base de datos. La mayoría de los principales proveedores de bases de datos los admiten de alguna forma. Dependiendo del sistema, los procedimientos almacenados pueden implementarse en diversos lenguajes de programación , como SQL , Java , C o C++ . Los procedimientos almacenados escritos en lenguajes distintos de SQL pueden o no ejecutar sentencias SQL.

La creciente adopción de procedimientos almacenados propició la introducción de elementos procedimentales en el lenguaje SQL, concretamente en los estándares SQL:1999 y SQL:2003, específicamente en la sección 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 van más allá de SQL/PSM. Existe una especificación estándar para procedimientos almacenados en Java, así como SQL/JRT .

Arriba

Dado que las sentencias de procedimientos almacenados se guardan directamente en la base de datos, pueden eliminar total o parcialmente la sobrecarga de compilación que normalmente se requiere cuando las aplicaciones de software envían consultas SQL dinámicas a una base de datos. (Sin embargo, la mayoría de los sistemas de bases de datos implementan cachés de sentencias y otros métodos para evitar la compilación repetitiva de sentencias SQL dinámicas). Además, si bien evitan parte del SQL precompilado, las sentencias aumentan la complejidad de crear un plan de ejecución óptimo, ya que no todos los argumentos de la sentencia SQL se proporcionan en tiempo de compilación. Dependiendo de la implementación y configuración específicas de la base de datos, se observarán resultados de rendimiento mixtos entre los procedimientos almacenados y las consultas genéricas o las funciones definidas por el usuario.

Evitar el tráfico de red

Una de las principales ventajas de los procedimientos almacenados es que pueden ejecutarse directamente en el motor de la 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 radica en el ahorro de costes de red, lo cual resulta especialmente relevante cuando se trata de una serie de sentencias SQL.

Encapsulando la lógica empresarial

Los procedimientos almacenados permiten a los programadores integrar la lógica de negocio como una API en la base de datos, lo que simplifica la gestión de datos y reduce la necesidad de codificar la lógica en otros programas cliente. Esto disminuye la probabilidad de que los datos se corrompan debido a fallos en dichos programas. El sistema de base de datos puede garantizar la integridad y la coherencia de los datos mediante procedimientos almacenados.

Delegación de derechos de acceso

En muchos sistemas, se pueden otorgar a los procedimientos almacenados derechos de acceso a la base de datos que los usuarios que ejecutan dichos procedimientos no tienen directamente.

Cierta protección contra ataques de inyección SQL

Los procedimientos almacenados pueden utilizarse para protegerse contra ataques de inyección SQL. Los parámetros de un procedimiento almacenado se tratan como datos incluso si un atacante inserta comandos SQL. Además, algunos sistemas de gestión de bases de datos (DBMS) verifican el tipo de parámetro. Sin embargo, un procedimiento almacenado que a su vez genera SQL dinámico a partir de la entrada sigue siendo vulnerable a las inyecciones SQL a menos que se tomen las precauciones adecuadas.

En algunos sistemas, los procedimientos almacenados se utilizan para controlar la gestión de transacciones; en otros, se ejecutan dentro de una transacción, de modo que esta resulta prácticamente transparente. Los procedimientos almacenados también pueden invocarse mediante un disparador 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, y el código que contiene se ejecutaría. Escribir procedimientos almacenados como controladores de condiciones también permite a los administradores de bases de datos realizar un seguimiento más detallado de los errores del sistema, ya que los utilizan para detectarlos y registrar información de auditoría en la base de datos o en un recurso externo, como un archivo.

• Una función es un subprograma escrito para realizar ciertos cálculos.
• Una función escalar devuelve un único valor (o NULL), mientras que una función de tabla devuelve una tabla (relacional) que consta de 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 pasarles ningún valor.
• Las funciones pueden utilizarse en SELECTlas sentencias, siempre que no manipulen los datos. Sin embargo, los procedimientos no pueden incluirse en SELECTlas sentencias.
• 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.

Las sentencias preparadas toman una sentencia o consulta ordinaria y la parametrizan para que se puedan usar diferentes valores literales posteriormente. Al igual que los procedimientos almacenados, se almacenan en el servidor para mayor eficiencia y ofrecen cierta protección contra ataques de inyección SQL. Si bien son más simples y declarativas, las sentencias preparadas no suelen escribirse para usar lógica procedimental y no pueden operar con variables. Debido a su interfaz sencilla y a su implementación en el lado del cliente, las sentencias preparadas son más reutilizables entre sistemas de gestión de bases de datos (DBMS).

El término contrato inteligente se aplica al código ejecutable almacenado en una cadena de bloques , a diferencia de un sistema de gestión de bases de datos relacionales (RDBMS). Si bien los mecanismos de consenso para el resultado de la ejecución en las redes de cadenas de bloques públicas difieren en principio de las bases de datos privadas o federadas tradicionales, en teoría cumplen la misma función que los procedimientos almacenados, aunque generalmente con un enfoque en la transacción de valor.

• Los lenguajes de procedimientos almacenados suelen ser específicos del proveedor. Cambiar de proveedor de base de datos generalmente requiere reescribir los procedimientos almacenados existentes.
• Los cambios en los procedimientos almacenados son más difíciles de controlar en un sistema de control de versiones que otros tipos de código. Los cambios deben reproducirse como scripts para almacenarse en el historial del proyecto y poder incluirse, y las diferencias entre 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 un paso de compilación o construcción en un IDE de aplicación; lo mismo ocurre si un procedimiento almacenado se pierde o se elimina accidentalmente.
• Los lenguajes de procedimientos almacenados de diferentes proveedores tienen distintos niveles de sofisticación.
• El soporte de herramientas para escribir y depurar procedimientos almacenados a menudo no es tan bueno como en otros lenguajes de programación, pero esto varía según los proveedores y los lenguajes.
  • Por ejemplo, tanto PL/SQL como T-SQL tienen entornos de desarrollo integrados (IDE) y depuradores específicos. PL/PgSQL se puede depurar desde diversos IDE.

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