PROCEDIMIENTOS ALMACENADOS

 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