Procedimiento almacenado
Un procedimiento
almacenado (stored
procedure en inglés) es un programa (o procedimiento) el cual es almacenado físicamente
en una base de datos. Su implementación varía de un gestor de bases de datos a otro. La ventaja de un procedimiento almacenado es que al ser ejecutado, en respuesta a una petición
de usuario, es ejecutado directamente en el motor de bases de datos, el cual
usualmente corre en un servidor separado. Como tal, posee acceso directo a los
datos que necesita manipular y sólo necesita enviar sus resultados de regreso
al usuario, deshaciéndose de la sobrecarga resultante de comunicar grandes
cantidades de datos salientes y entrantes.
Usos típicos para
procedimientos almacenados incluyen la validación de datos siendo
integrados a la estructura de base de datos (los procedimientos almacenados
utilizados para este propósito a menudo son llamados disparadores; triggers en
inglés), o encapsular un proceso grande y complejo. El último ejemplo
generalmente ejecutará más rápido como un procedimiento almacenado que de haber
sido implementado como, por ejemplo, un programa corriendo en el sistema
cliente y comunicándose con la base de datos mediante el envío de consultas SQL y recibiendo sus resultados.
Los procedimientos pueden ser
ventajosos: Cuando una base de datos es manipulada desde muchos programas
externos. Al incluir la lógica de la aplicación en la base de datos utilizando
procedimientos almacenados, la necesidad de embeber la misma lógica en todos
los programas que acceden a los datos es reducida. Esto puede simplificar la
creación y, particularmente, el mantenimiento de los programas involucrados.
Podemos ver un claro ejemplo de estos
procedimientos cuando requerimos realizar una misma operación en un servidor
dentro de algunas o todas las bases de datos y a la vez dentro de todas o
algunas de las tablas de las bases de datos del mismo. Para ello podemos
utilizar a los Procedimientos
almacenados auto creables que es una forma de generar ciclos
redundantes a través de los procedimientos almacenados.
Implementación
Estos procedimientos, se usan a
menudo, pero no siempre, para realizar consultas SQL sobre los objetos del banco de datos de una manera abstracta, desde el
punto de vista del cliente de la aplicación. Un procedimiento almacenado permite agrupar en forma exclusiva parte de algo
específico que se desee realizar o, mejor dicho, el SQL apropiado para dicha
acción.
Usos
Los usos 'típicos' de los procedimientos almacenados se aplican en la validación de datos, integrados
dentro de la estructura del banco de datos. Los procedimientos almacenados usados con tal propósito se llaman comúnmente disparadores,
o triggers. Otro uso común es la 'encapsulación' de un API para un proceso complejo o grande que podría requerir la 'ejecución' de
varias consultas SQL, tales como la manipulación de un 'dataset' enorme para
producir un resultado resumido.
También pueden ser usados para el
control de gestión de operaciones, y ejecutar procedimientos almacenados dentro
de una transacción de tal manera que las transacciones sean efectivamente
transparentes para ellos.
Ventajas
La ventaja de un procedimiento almacenado, en respuesta a una petición de usuario, está directamente
bajo el control del motor del manejador de bases de datos, lo cual corre
generalmente en un servidor separado de manejador de bases de datos aumentando
con ello, la rapidez de procesamiento de requerimientos del manejador de bases
de datos. El servidor de la base de datos tiene acceso directo a los datos
necesarios para manipular y sólo necesita enviar el resultado final al usuario.
Los procedimientos almacenados pueden permitir que la lógica del negocio se
encuentre como un API en la base de datos, que pueden simplificar la gestión de
datos y reducir la necesidad de codificar la lógica en el resto de los
programas cliente. Esto puede reducir la probabilidad de que los datos sean corrompidos
por el uso de programas clientes defectuosos o erróneos. De este modo, el motor
de base de datos puede asegurar la integridad de los datos y la consistencia,
con la ayuda de procedimientos almacenados. Algunos afirman que las bases de
datos deben ser utilizadas para el almacenamiento de datos solamente, y que la
lógica de negocio sólo debería ser aplicada en la capa de negocio de código, a
través de aplicaciones cliente que deban acceder a los datos. Sin embargo, el
uso de procedimientos almacenados no se opone a la utilización de una capa de
negocio.
Procedimientos
almacenados en MySQL
Desde MySQL 5 los procedimientos
almacenados empezaron a ser soportados, como suele suceder en MySQL las
sentencias se ejecutan luego de escribir el signo punto y coma (;), por esta
razón antes de escribir el procedimiento almacenado la función del punto y coma
se asigna a otros caracteres usando la sentencia DELIMITER seguida de un caracter tal como |, de esta manera el
procedimiento puede ser escrito usando los punto y comas sin que se ejecute
mientras se escribe; después de escrito el procedimiento, se escribe nuevamente
la sentencia DELIMITER ; para asignar al punto y coma su función habitual.
El siguiente es un ejemplo de
procedimiento almacenado en MySQL:
DELIMITER |
CREATE PROCEDURE autos(IN velocidad INT,IN marca VARCHAR(50))
BEGIN
IF velocidad < 120 THEN
INSERT INTO
familiares VALUES(velocidad,marca);
ELSE
INSERT INTO
deportivos VALUES(velocidad,marca);
END IF;
END;
|
Usar un procedimiento almacenado con parámetros de entrada
SQL Server 2008 R2
Un SQL Server procedimiento almacenado al que se puede
llamar es aquel que contiene uno o más parámetros IN, parámetros que se pueden
usar para pasar datos al procedimiento almacenado. El controlador JDBC de
Microsoft SQL Server proporciona la clase SQLServerPreparedStatement, que puede usar para llamar a este
procedimiento almacenado y para procesar los datos que devuelve.
Si usa el controlador JDBC para llamar a un
procedimiento almacenado con los parámetros IN, debe usar la secuencia de
escape de SQL call junto con el método prepareCall de la clase SQLServerConnection. La sintaxis de la secuencia de
escape call con los parámetros IN es la
siguiente:
{call procedure-name[([parameter][,[parameter]]...)]}
 Nota: |
|
Para
obtener más información acerca de las secuencias de escape de SQL, consulte Usar secuencias
de escape SQL.
|
Al crear la secuencia de escape call , especifique los parámetros IN mediante el carácter ?
(signo de interrogación). Este carácter actúa como un marcador de posición para
los valores de parámetros pasados al procedimiento almacenado. Para especificar
un valor de un parámetro, puede usar uno de los métodos del establecedor de la
clase SQLServerPreparedStatement. El método del establecedor que
puede usar se determina mediante el tipo del parámetro IN.
Cuando pasa un valor al método establecedor, debe
especificar no solo el valor real que se usará en el parámetro, sino el lugar
ordinal que ocupa el parámetro en el procedimiento almacenado. Por ejemplo, si
el procedimiento almacenado contiene un solo parámetro IN, su valor ordinal
será 1. Si el procedimiento almacenado contiene dos parámetros, el primer valor
ordinal es 1 y el segundo 2.
Como ejemplo de cómo llamar a un procedimiento que
contiene un parámetro IN, use el procedimiento almacenado
uspGetEmployeeManagers de la base de datos de ejemplo AdventureWorks de SQL
Server 2005. Este procedimiento almacenado acepta un solo parámetro de entrada
llamado EmployeeID (Id. del empleado), que es un valor entero, y devuelve una
lista recursiva de empleados y sus jefes según el EmployeeID especificado. El
código Java para llamar a este procedimiento almacenado es el siguiente:
public static void executeSprocInParams(Connection
con) {
try {
PreparedStatement pstmt = con.prepareStatement("{call
dbo.uspGetEmployeeManagers(?)}");
pstmt.setInt(1, 50);
ResultSet
rs = pstmt.executeQuery();
while
(rs.next()) {
System.out.println("EMPLOYEE:");
System.out.println(rs.getString("LastName") + ", " +
rs.getString("FirstName"));
System.out.println("MANAGER:");
System.out.println(rs.getString("ManagerLastName") + ",
" + rs.getString("ManagerFirstName"));
System.out.println();
}
rs.close();
pstmt.close();
}
catch
(Exception e) {
e.printStackTrace();
}
}
Usar un procedimiento
almacenado con parámetros de salida
SQL Server 2008
R2
Un procedimiento almacenado de SQL Server al que se
puede llamar es el que devuelve uno o más parámetros OUT, que son los
parámetros que el procedimiento almacenado usa para devolver los datos a la
aplicación que realiza la llamada. El controlador JDBC de Microsoft SQL Server
ofrece la claseSQLServerCallableStatement, que puede usar para llamar a
este tipo de procedimiento almacenado y procesar los datos que devuelve.
Cuando se llama a este tipo de procedimiento
almacenado con el controlador JDBC, se debe usar la secuencia de escape de
SQL call junto con el método prepareCall de la clase SQLServerConnection. La sintaxis para la secuencia
de escape call con parámetros OUT es la siguiente:
{call
procedure-name[([parameter][,[parameter]]...)]}
|
Nota: |
|
Para obtener
más información acerca de las secuencias de escape de SQL, consulte Usar secuencias de escape SQL.
|
Al crear la secuencia de escape call , especifique los parámetros
OUT mediante el carácter ? (signo de interrogación). Este carácter actúa como
un marcador de posición para los valores de parámetros devueltos por el
procedimiento almacenado. Para especificar un valor para un parámetro OUT, debe
especificar el tipo de datos de cada parámetro mediante el método registerOutParameter de la
clase SQLServerCallableStatement antes de ejecutar el procedimiento
almacenado.
El valor especificado para el parámetro OUT en el
método registerOutParameter debe ser uno de los tipos de datos JDBC
incluidos en java.sql.Types, que se asigna a su vez a uno de los tipos de datos
de SQL Server nativos. Para obtener más información acerca de los tipos de
datos JDBC y de SQL Server, consulteDescribir los
tipos de datos del controlador JDBC.
Cuando pasa un valor al
método registerOutParameter para un parámetro OUT, debe especificar
no solo los tipos de datos usados para el parámetro, sino también la posición
ordinal del parámetro o el nombre del mismo en el procedimiento almacenado. Por
ejemplo, si el procedimiento almacenado contiene un solo parámetro OUT, su
valor ordinal es 1 y, si el procedimiento almacenado contiene dos parámetros,
el primer valor ordinal es 1 y el segundo 2.
|
Nota: |
|
El controlador
JDBC no admite el uso de los tipos de datos CURSOR, SQLVARIANT, TABLE y
TIMESTAMP SQL Server como parámetros OUT.
|
Cree, a modo de ejemplo, el siguiente procedimiento
almacenado en la base de datos de ejemplo AdventureWorks de SQL Server 2005:
@employeeID INT,
@managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
END
Este procedimiento almacenado devuelve un solo
parámetro OUT (managerID), que es un entero, en función del parámetro IN
(employeeID) especificado, que también es un entero. El valor devuelto en el
parámetro OUT es ManagerID en función de EmployeeID en la tabla
HumanResources.Employee.
En el siguiente ejemplo, se pasa una conexión abierta
a la base de datos de ejemplo AdventureWorks a la función y se usa el
método execute para llamar al
procedimiento almacenado GetImmediateManager:
try {
CallableStatement cstmt =
con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
cstmt.setInt(1, 5);
cstmt.registerOutParameter(2,
java.sql.Types.INTEGER);
cstmt.execute();
System.out.println("MANAGER ID: " + cstmt.getInt(2));
}
catch (Exception e) {
e.printStackTrace();
}
}
Este ejemplo utiliza las posiciones ordinales para
identificar los parámetros. También puede identificar un parámetro utilizando
su nombre en lugar de su posición ordinal. En el ejemplo de código siguiente se
modifica el ejemplo anterior para demostrar cómo utilizar los parámetros con
nombre en una aplicación Java. Observe que los nombres de parámetros se
corresponden con los nombres de parámetros en la definición del procedimiento
almacenado:
try {
CallableStatement cstmt =
con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
cstmt.setInt("employeeID", 5);
cstmt.registerOutParameter("managerID",
java.sql.Types.INTEGER);
cstmt.execute();
System.out.println("MANAGER ID: "
+ cstmt.getInt("managerID"));
cstmt.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
|
Nota: |
|
En estos
ejemplos se usa el método execute de la
clase SQLServerCallableStatement para ejecutar el procedimiento
almacenado. Se usa dicho método porque el procedimiento almacenado no ha
devuelto ningún conjunto de resultados. En caso contrario, se usaría el
método executeQuery.
|
Los procedimientos almacenados también pueden devolver
recuentos de actualizaciones y múltiples conjuntos de resultados. El
controlador JDBC de Microsoft SQL Server sigue la especificación de JDBC 3.0,
que indica que se deben recuperar varios conjuntos de resultados y
actualizaciones antes de que se recuperen los parámetros OUT. Es decir, la
aplicación debería recuperar todos los objetos ResultSet y recuentos
de actualizaciones antes de recuperar los parámetros OUT con los
métodos CallableStatement.getter. De lo contrario, los
objetos ResultSet y recuentos de actualizaciones que aún no se hayan
recuperado se perderán cuando se recuperen los parámetros OUT. Para obtener más
información acerca de los recuentos de actualizaciones y los conjuntos de resultados
múltiples, veaUsar un
procedimiento almacenado con un recuento de actualizaciones y Usar múltiples
conjuntos de resultados.
