PROCEDIMIENTOS ALMACENADOS
1.
DEFINICIÓN:
Un procedimiento almacenado (store procedure) es un
programa (o procedimiento) el cual es almacenado físicamente en una base de
datos. Generalmente son escritos en un lenguaje de bases de datos propietario
como SQL. 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 solo necesita enviar
sus resultados de regreso al usuario, deshaciéndose de la sobrecarga resultante
de comunicar grandes cantidades de datos salientes y entrantes.
Los procedimientos,
se usan para realizar consultas SQL sobre los objetos de la base de datos de
una manera abstracta. Un procedimiento almacenado permite agrupar en forma
exclusiva parte de algo específico que se desee realizar y SQL es apropiado
para dicha acción.
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 conjunto de datos para producir
un resultado resumido.
La
ventaja de un procedimiento almacenado, en respuesta a una petición de usuario,
está directamente bajo el control del motor del gestor de bases de datos, que
corre generalmente en un servidor distinto del servidor web, aumentando con
ello la rapidez de procesamiento de las peticiones del usuario. 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.
2. SINTAXIS:
Los
procedimientos almacenados pueden recibir y devolver información; para ello se
emplean parámetros, de entrada y salida, respectivamente.
Los
parámetros de entrada posibilitan pasar información a un procedimiento.
Para que un procedimiento almacenado admita
parámetros de entrada se deben declarar variables como parámetros al crearlo.
La sintaxis es:
CREATE
PROCEDURE NombreProcedimiento
@NOMBREPARAMETRO TIPO =VALORPORDEFECTO
as
SENTENCIAS;
Los
parámetros se definen luego del nombre del procedimiento, comenzando el nombre
con un signo arroba (@). Los parámetros son locales al procedimiento, es decir,
existen solamente dentro del mismo. Pueden declararse varios parámetros por
procedimiento, se separan por comas.
Cuando
el procedimiento es ejecutado, deben explicitarse valores para cada uno de los
parámetros (en el orden que fueron definidos), a menos que se haya definido un
valor por defecto, en tal caso, pueden omitirse. Pueden ser de cualquier tipo
de dato (excepto cursor).
Luego de definir un parámetro y su tipo,
opcionalmente, se puede especificar un valor por defecto; tal valor es el que
asume el procedimiento al ser ejecutado si no recibe parámetros. Si no se
coloca valor por defecto, un procedimiento definido con parámetros no puede
ejecutarse sin valores para ellos. El valor por defecto puede ser
"null" o una constante, también puede incluir comodines si el
procedimiento emplea "like".
ü Creamos
un procedimiento que recibe el nombre de un autor como parámetro para mostrar
todos los libros del autor solicitado:
create procedure pa_libros_autor
@autor varchar(30)
as
select
titulo, editorial,precio
from libros
where autor= @autor;
ü
El procedimiento se ejecuta colocando
"execute" (o "exec") seguido del nombre del procedimiento y
un valor para el parámetro:
exec pa_libros_autor 'Borges';
ü Creamos
un procedimiento que recibe 2 parámetros, el nombre de un autor y el de una
editorial:
create procedure pa_libros_autor_editorial
@autor varchar(30),
@editorial varchar(20)
as
select titulo, precio
from libros
where autor= @autor and
editorial=@editorial;
ü El
procedimiento se ejecuta colocando "execute" (o "exec")
seguido del nombre del procedimiento y los valores para los parámetros
separados por comas:
exec pa_libros_autor_editorial 'Richard
Bach','Planeta';
Los
valores de un parámetro pueden pasarse al procedimiento mediante el nombre del
parámetro o por su posición. La sintaxis anterior ejecuta el procedimiento
pasando valores a los parámetros por posición. También podemos emplear la otra
sintaxis en la cual pasamos valores a los parámetros por su nombre:
exec
pa_libros_autor_editorial @editorial='Planeta', @autor='Richard Bach';
Cuando
pasamos valores con el nombre del parámetro, el orden en que se colocan puede
alterarse.
No
podríamos ejecutar el procedimiento anterior sin valores para los parámetros.
Si queremos ejecutar un procedimiento que permita omitir los valores para los
parámetros debemos, al crear el procedimiento, definir valores por defecto para
cada parámetro:
create procedure pa_libros_autor_editorial2
@autor varchar(30)='Richard Bach',
@editorial varchar(20)='Planeta'
as
select titulo, autor,editorial,precio
from libros
where autor= @autor and
editorial=@editorial;
3. ADMINISTRACIÓN
DE PROCEDIMIENTOS ALMACENADOS (Creación, Modificación y Eliminación):
ü
CREACIÓN
DE UN PROCEDIMIENTO ALMACENADO:
Los procedimientos almacenados se crean con
la instrucción CREATE PROCEDURE. Considere los hechos siguientes cuando cree
procedimientos almacenados:
Los
procedimientos almacenados pueden hacer referencia a tablas, vistas, funciones
definidas por el usuario y otros procedimientos almacenados, así como a tablas
temporales.
Si un
procedimiento almacenado crea una tabla local temporal, la tabla temporal sólo
existe para atender al procedimiento almacenado y desaparece cuando finaliza la
ejecución del mismo.
Una
instrucción CREATE PROCEDURE no se puede combinar con otras instrucciones de
Transact-SQL en un solo proceso.
Sintaxis:
CREATE PROCEDURE
nombreProcedimiento [ ; número ]
[ {
@tipoDatos procedimiento }
[ VARYING ] [ = predeterminado ] [ OUTPUT ]
WITH
RECOMPILE | ENCRYPTION | RECOMPILE ,
ENCRYPTION
FOR REPLICATION
AS instrucciónSql
ü
MODIFICACIÓN
DE UN PROCEDIMIENTO ALMACENADO:
Para
modificar un procedimiento almacenado existente y conservar la asignación de
los permisos, use la instrucción ALTER PROCEDURE.
SQL Server sustituye la definición anterior
del procedimiento almacenado cuando se modifica con ALTER PROCEDURE.
ALTER PROCEDURE nombreProcedimiento [ ;
número ]
[ {
@tipoDatos parámetro }
[ VARYING ] [ = valorPredeterminado ] [
OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION
}
]
[ FOR REPLICATION ]
AS
instrucciónSQL [...n]
ü ELIMINACIÓN DE UN PROCEDIMIENTO ALMACENADO:
La
instrucción DROP PROCEDURE se utiliza para quitar procedimientos almacenados
definidos por el usuario de la base de datos actual.
Sintaxis:
DROP
PROCEDURE { procedimiento } [ ,...n ]
4.
EJEMPLOS:
ü Procedimiento almacenado para guardar un
nuevo cliente:
CREATE PROCEDURE spCliente_N
@dniruc varchar(11),
@contacto varchar(30),
@ciudad varchar(20),
@direccion varchar(30),
@telefono varchar(20),
@email varchar(20),
@usuario varchar(20),
@clave varchar(20),
@observacion varchar(50)
AS INSERT INTO clientes values
(@dniruc,@contacto,@ciudad,@direccion,@telefono,@email,@usuario,@clave,@observacion)
GO
ü Procedimiento almacenado para modificar un cliente:
CREATE PROCEDURE spCliente_M
@dniruc varchar(11),
@compania varchar(20),
@contacto varchar(30),
@ciudad varchar(20),
@direccion varchar(30),
@telefono varchar(20),
@email varchar(20),
@usuario varchar(20),
@clave varchar(20),
@observacion varchar(50)
as Update clientes set dniruc = @dniruc,
compania = @compania, contacto = @contacto, ciudad = @ciudad, direccion =
@direccion, telefono = @telefono, email = @email, usuario = @usuario, clave =
@clave, observacion = @observacion where dniruc = @dniruc
GO
ü Procedimiento almacenado para eliminar un
cliente.
CREATE PROCEDURE
spCliente_E @dniruc char(11) as DELETE FROM clientes WHERE dniruc = @dniruc GO
RESUMEN:
Un Procedimiento Almacenado es
un programa auto controlado escrito en lenguaje del DBMS, son almacenados como
parte de la Base de Datos y sus metadatos.
Una vez creado un procedimiento almacenado, se puede
invocar directamente desde una aplicación, o sustituir el nombre de una tabla o
vista, por el nombre de procedimiento en cláusulas SELECT. Los procedimientos
almacenados pueden recibir parámetros de entrada y retornar valores a la
aplicación.
Las ventajas de
usar los procedimientos almacenados incluyen:
§ Diseño modular.
§ Aplicaciones que acceden la misma Base de Datos
pueden compartir los procedimientos almacenados, eliminando el código doble y
reduciendo el tamaño de las aplicaciones.
§ El fácil mantenimiento.
§ Cuando un procedimiento se actualiza, los cambios se
reflejan automáticamente en todas las aplicaciones, sin la necesidad de
recompilar y relinkear. Las aplicaciones son compiladas sólo una vez para cada
cliente.
§ Los procedimientos almacenados son ejecutados por el
servidor, no por el cliente lo que reduce el tráfico en la red y mejora el
performance o desempeño, especialmente para el acceso del cliente remoto.
Un procedimiento
almacenado es una colección con nombre de instrucciones de TransactSQL que se
almacena en el servidor. Los procedimientos almacenados son un método para encapsular
tareas repetitivas. Admiten variables declaradas por el usuario, ejecución condicional
y otras características de programación muy eficaces.
SQL Server admite
cinco tipos de procedimientos almacenados:
v Procedimientos
almacenados del sistema (sp_): Almacenados en la
base de datos master e identificados mediante el prefijo sp_, proporcionan un
método efectivo de recuperar información de las tablas del sistema. Permiten a
los administradores del sistema realizar tareas de administración de la base de
datos que actualizan las tablas del sistema, aunque éstos no tengan permiso
para actualizar las tablas subyacentes directamente. Los procedimientos
almacenados del sistema se pueden ejecutar en cualquier base de datos.
v Procedimientos
almacenados locales: Los procedimientos
almacenados locales se crean en las bases de datos de los usuarios
individuales.
v Procedimientos
almacenados temporales: Los procedimientos
almacenados temporales pueden ser locales, con nombres que comienzan por un
signo de almohadilla (#), o globales, con nombres que comienzan por un signo de
almohadilla doble (##). Los procedimientos almacenados temporales locales están
disponibles en la sesión de un único usuario, mientras que los procedimientos
almacenados temporales globales están disponibles para las sesiones de todos
los usuarios.
v Procedimientos
almacenados remotos: Los procedimientos
almacenados remotos son una característica anterior de SQL Server. Las
consultas distribuidas admiten ahora esta funcionalidad.
v Procedimientos
almacenados extendidos (xp_): Los
procedimientos almacenados extendidos se implementan como bibliotecas de
vínculos dinámicos (DLL, Dynamic-Link Libraries) que se ejecutan fuera del
entorno de SQL Server. Se identifican mediante el prefijo xp. Se ejecutan de
forma similar a los procedimientos almacenados.
Los procedimientos almacenados en SQL Server son
similares a los procedimientos de otros lenguajes de programación ya que
pueden:
ü Contener instrucciones que realizan operaciones en
la base de datos; incluso tienen la capacidad de llamar a otros procedimientos
almacenados.
ü Aceptar parámetros de entrada.
ü Devolver un valor de estado a un procedimiento
almacenado o a un proceso por lotes que realiza la llamada para indicar que se
ha ejecutado correctamente o que se ha producido algún error, y la razón del mismo.
ü Devolver varios valores al procedimiento almacenado
o al proceso por lotes que realiza la llamada en forma de parámetros de salida
SUMMARY:
A Stored Procedure is a self-controlled program written in the DBMS
language, stored as part of the Database and its metadata.
Once a stored procedure is created, it can be invoked directly from an
application, or substitute the name of a table or view, by the procedure name
in SELECT clauses. The stored procedures can receive input parameters and
return values to the application.
The advantages of using stored procedures include:
§
Modular design.
§
Applications that access the same
Database can share the stored procedures, eliminating the double code and
reducing the size of the applications.
§
Easy maintenance.
§
When a procedure is updated, the
changes are automatically reflected in all the applications, without the need
to recompile and re-link. The applications are compiled only once for each
client.
§
The stored procedures are executed by
the server, not by the client, which reduces traffic on the network and
improves performance or performance, especially for remote client access.
A stored procedure is a collection with TransactSQL instruction name
that is stored on the server. Stored procedures are a method to encapsulate
repetitive tasks. They admit variables declared by the user, conditional
execution and other very efficient programming features.
SQL Server supports five types of stored procedures:
v
System stored procedures (sp_):
Stored in the master database and identified by the prefix sp_, they provide an
effective method of retrieving information from the system tables. They allow
system administrators to perform database administration tasks that update the
system tables, even though they do not have permission to update the underlying
tables directly. The stored procedures of the system can be executed in any
database.
v
Local stored procedures: Local stored
procedures are created in the databases of individual users.
v
Temporary stored procedures:
Temporary stored procedures can be local, with names beginning with a pound
sign (#), or global, with names beginning with a double pound sign (##). Local
temporary stored procedures are available in the session of a single user,
while global temporary stored procedures are available for all user sessions.
v
Remote stored procedures: Remote
stored procedures are a prior feature of SQL Server. Distributed queries now
support this functionality.
v
Extended stored procedures (xp_):
Extended stored procedures are implemented as dynamic link libraries (DLL,
Dynamic-Link Libraries) that run outside of the SQL Server environment. They
are identified by the prefix xp. They run similarly to stored procedures.
The procedures stored in SQL Server are similar to the procedures of
other programming languages because they can:
ü
Contain instructions that perform
operations in the database; They even have the ability to call other stored
procedures.
ü
Accept input parameters.
ü
Return a status value to a stored
procedure or to a batch process that makes the call to indicate that it has
been executed correctly or that an error has occurred, and the reason for it.
ü Return multiple values to the stored procedure or to the batch process
that makes the call in the form of output parameters.
RECOMENDACIONES:
ü Ante consultas complejas y que se utilizan en
varias ocasiones, se pueden crear vistas para un manejo más adecuado.
ü Utilice la sentencia Try para capturar
adecuadamente los errores en tiempo de ejecución dentro del stored procedure y
hacer un manejo acorde en dicha situación.
ü Evite utilizar sub-consultas (SELECT A.column1 FROM
(SELECT data1 FROM) as A …), utilice la sentencia INNER JOIN siempre que sea
posible.
ü No escriba el nombre del stored procedure
comenzando con el prefijo “sp_”, esto es reservado para los sotred procedures
de sistema de SQL Server. Cuando se hace la solicitud de un stored procedure
que comienza con ese prefijo el motor de base de datos intenta buscar primero
en la base de datos “master” y luego en las demas.
ü Utilice tablas temporales solamente cuando es
necesario. Los stored procedure suelen utilizar un plan de ejecución,
almacenado en caché, para aumentar el rendimiento, cuando se utilizan tablas
temporales se requiere hacer la compilación en cada ejecución y no se puede
aprovechar el plan de ejecución.
ü Utilice cursores solo cuando es necesario. Los
cursores requieren más memoria y por tanto se degrada el rendimiento del stored
procedure. En algunos casos se puede utilizar la variable de tabla y el
bucle WHILE para iterar sobre un el
conjunto de resultados.
ü Utilice la sentencia SELECT TOP 1 para la
comprobación/obtención de un dato en base a una condición.
ü En los casos donde se puede aplicar una instrucción
CASE es mejor que el anidamiento de sentencias IF-ELSE.
ü Utilice las instrucciones ORDER BY y DISTINCT solo
cuando se requieran. Caso contrario el motor de SQL Server obtendrá el primer
resultado y lo hará de nuevo si requiere utilizar algunas de estas
instrucciones.
CONCLUCIONES:
Los procedimientos almacenados permiten aprovechar los recursos del
servidor; ahorrar tiempo creando consultas complejas desde el servidor y
permiten interactuar con otros objetos de una Base de Datos como: Triggers,
Generadores y Tablas.
Los procedimientos almacenados son instrucciones que se utilizan para
ejecutar una acción y quedan guardadas en el servidor.
Los procedimientos almacenados permiten aprovechar los recursos del
servidor, ahorrar tiempo creando consultas complejas desde el servidor y
permiten interactuar con otros objetos de InterBase-triggers, generadores,
excepciones, tablas y vistas-. Por lo tanto, es importante para todo desarrollador
que utilice InterBase el utilizarlos correctamente y saber todo lo que se puede
hacer con ellos.
Uno de los procedimientos más usados en el diseño de la base de datos,
son los Procedimiento almacenados, puesto que nos permiten agilizar los
procesos de consultas de datos, aumentar la seguridad, reutilizar código y
permiten desarrollo de software más ágil evitar hacer más código.
APRECIACIÓN DEL EQUIPO:
ü Los procedimientos empiezan con el prefijo sp_, puesto que aparecen
lógicamente en todas las bases de datos definidas por el usuario y por el
sistema.
ü Se pueden ejecutar desde cualquier base de datos sin necesidad de
calificar totalmente el nombre del procedimiento.
ü Un procedimiento no debe sobrepasar las 400 líneas de código. En caso de
que tengas un procedimiento demasiado grande es más adecuado particionarlo.
Tener en cuenta que las tablas temporales y transacciones se mantienen en
procedimientos dependientes.
GLOSARIO DE TÉRMINOS:
·
API: Una
API (Application Programming Interface) es un conjunto de reglas (código) y
especificaciones que las aplicaciones pueden seguir para comunicarse entre
ellas: sirviendo de interfaz entre programas diferentes de la misma manera en
que la interfaz de usuario facilita la interacción humano-software.
· Cursor: Es un elemento que
representará a un conjunto de datos determinado por una consulta T-SQL, el
cursor permitirá recorrer fila a fila, leer y eventualmente modificar dicho
conjunto de resultados.
·
Comodín:
Es
un caracter que representa cualquier otro caracter o cadena de caracteres.
Algunos de los caracteres comodines son: *(asterisco), %(por ciento), _(guion
bajo), ?(signo de interrogación). Los caracteres comodines cambian de una
aplicación a otra.
· INNER JOIN: Las
combinaciones internas se realizan mediante la instrucción INNER JOIN.
Devuelven únicamente aquellos registros/filas que tienen valores idénticos en
los dos campos que se comparan para unir ambas tablas. Es decir, aquellas que
tienen elementos en las dos tablas, identificados éstos por el campo de
relación.
· ORDER BY: Se usa para poner en
orden ascendente, en orden descendente un dato en una consulta, también se basa
en valores numéricos o de texto.
· DISTINCT: Devuelve
una tabla de una columna que contiene los valores distintos de la columna
especificada. En otras palabras, los valores duplicados se quitan y solo se
devuelven los valores únicos.
· InterBase: Es un
sistema de administración de bases de datos relacionales (RDBMS) actualmente
desarrollado y comercializado por Embarcadero Technologies.
LINKOGRAFÍA:
ühttp://www.tutorialesprogramacionya.com/sqlserverya/temarios/descripcion.php?cod=122&punto=122&inicio=
ü https://hacker0106.files.wordpress.com/2011/08/implementacic3b3n-de-procedimientos-almacenados.pdf
ü https://hacker0106.files.wordpress.com/2011/08/implementacic3b3n-de-procedimientos-almacenados.pdf
ühttps://www.emagister.com/uploads_user_home/Comunidad_Emagister_7261_7.1.-_Procedimientos_almacenados.pdf
ü https://jsilupu.wordpress.com/2013/11/04/recomendaciones-en-transact-sql/
ü https://es.slideshare.net/LisbethOcaaBueno/procedimientos-almacenados-95953520
Comentarios
Publicar un comentario