UNIVERSIDAD POLITECNICA AMAZONICA
INGENIERÍA DE SISTEMAS Y TELEMÁTICAS
Docente : Marco Aurelio Porro Chulli
Asignatura :BASE DE DATOS II.
Ciclo : VIII "A"
Integrantes : Yanina Bustamante Jibaja.
Yenny Isabel Cuello Morón.
PROCEDIMIENTOS ALMACENADOS
1. Contenido
Ø
Definición
Un procedimiento almacenado es un
objeto perteneciente a una
base de datos,
que contiene un conjunto de
instrucciones SQL, tanto de consulta, como de manipulación de datos, como de control
de la secuencia
del programa, asociados
a un nombre,
y que son
ejecutados en conjunto. Puede contener parámetros tanto de entrada
como de salida (parámetros
pasados por referencia), así como devolver un valor de retorno.
Son precompilados al ejecutarse por primera
vez, y no
vuelven a ser
compilados con las subsiguientes ejecuciones,
lo que proporciona
una cierta mejora
en el rendimiento.
No obstante si se desea se puede forzar su recompilación.
Una de
las principales ventajas
de este tipo
de objetos, es
que al residir
en la propia
base de datos son
compartibles por todos
los usuarios, pudiendo
de esta manera
beneficiarse de los distintos cachés del servidor. Al mismo
tiempo al ser código externo a la aplicación puede ser alterado sin que
exista siempre la necesidad de modificar
el código de la misma.
Al ser objetos de la base de
datos se hallan sujetos a los esquemas de seguridad determinados por el administrador de la misma:
Existen diversas clases
de procedimientos almacenados,
entre los que
se encuentra los procedimientos almacenados
del sistema, que
sirven de herramientas
para la realización
de distintas tareas de administración.
Un procedimiento almacenado de SQL Server
es un grupo de una o varias instrucciones Transact-SQL o una referencia a un
método de Common Runtime Language (CLR) de Microsoft .NET Framework. Los
procedimientos se asemejan a las construcciones de otros lenguajes de
programación, porque pueden:
· Aceptar parámetros de entrada y
devolver varios valores en forma de parámetros de salida al programa que
realiza la llamada.
· Contener
instrucciones de programación que realicen operaciones en la base de datos.
Entre otras, pueden contener llamadas a otros procedimientos
· Devolver
un valor de estado a un programa que realiza una llamada para indicar si la
operación se ha realizado correctamente o se han producido errores, y el motivo
de estos.
Tipos de Procedimientos Almacenados
· Procedimientos Almacenados del sistema, se utilizan para administrar el SQL Server y para mostrar información
sobre base de datos y sobre usuarios.
· Procedimientos almacenados Extendidos, son bibliotecas de vínculos dinámicos (dynamic-link libraries, DLLs)
que SQL puede cargar y ejecutar de manera dinámica.
· Procedimientos Almacenados sencillos definidos por el usuario, son los procedimientos creados por los
usuarios y están personalizados para llevar a cabo la tarea deseada por el
usuario.
Ventajas de usar procedimientos almacenados
Tráfico de red reducido entre el cliente
y el servidor
Los comandos de
un procedimiento se ejecutan en un único lote de código. Esto puede reducir
significativamente el tráfico de red entre el servidor y el cliente porque
únicamente se envía a través de la red la llamada que va a ejecutar el
procedimiento.
Mayor seguridad
Varios usuarios
y programas cliente pueden realizar operaciones en los objetos de base de datos
subyacentes a través de un procedimiento, aunque los usuarios y los programas
no tengan permisos directos sobre esos objetos subyacentes. El procedimiento
controla qué procesos y actividades se llevan a cabo y protege los objetos de
base de datos subyacentes. Esto elimina la necesidad de conceder permisos en
cada nivel de objetos y simplifica los niveles de seguridad.
Reutilización del código
El código de
cualquier operación de base de datos redundante resulta un candidato perfecto
para la encapsulación de procedimientos. De este modo, se elimina la necesidad
de escribir de nuevo el mismo código, se reducen las inconsistencias de código
y se permite que cualquier usuario o aplicación que cuente con los permisos
necesarios pueda acceder al código y ejecutarlo.
Mantenimiento más sencillo
Cuando las
aplicaciones cliente llaman a procedimientos y mantienen las operaciones de
base de datos en la capa de datos, solo deben actualizarse los cambios de los
procesos en la base de datos subyacente. El nivel de aplicación permanece
independiente y no tiene que tener conocimiento sobre los cambios realizados en
los diseños, las relaciones o los procesos de la base de datos.
Rendimiento mejorado
De forma
predeterminada, un procedimiento se compila la primera vez que se ejecuta y
crea un plan de ejecución que vuelve a usarse en posteriores ejecuciones. Como
el procesador de consultas no tiene que crear un nuevo plan, normalmente
necesita menos tiempo para procesar el procedimiento.
Ø
SINTAXIS
La sintaxis de dicha instrucción es básicamente la
siguiente:
create procedure NOMBREPROCEDIMIENTO
@NOMBREPARAMETRO TIPO =VALORPORDEFECTO
as SENTENCIAS;
Ø Administración de Procedimientos Almacenados
Creación
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 CREATEPROCEDURE no se puede combinar con otras instrucciones de
Transact-SQL en un solo proceso por lotes.
·
La
definición de CREATEPROCEDURE puede incluir cualquier número y tipo de
instrucciones de Transact-SQL, con la excepción de las siguientes instrucciones
de creación de objetos: CREATE DEFAULT, CREATEPROCEDURE, CREATE RULE,
CREATETRIGGER y CREATE VIEW. En un procedimiento almacenado se pueden crear
otros objetos de la base de datos y deben calificarse con el nombre del
propietario del objeto.
·
Para
ejecutar la instrucción CREATEPROCEDURE, debe ser miembro de la función de administradores
del sistema (sysadmin), de la función de propietario de la base de datos
(db_owner) o de la función de administrador del lenguaje de definición de datos
(db_ddladmin), o debe haber recibido el permiso CREATEPROCEDURE.
·
El
tamaño máximo de un procedimiento almacenado es 128 megabytes (MB), según la
memoria disponible.
Sintaxis parcial:
CREATE PROCEDURE OverdueOrders
AS
SELECT * FROM dbo.Orders
WHERE RequiredDate
Go
Modificación
Para
modificar un procedimiento almacenado existente y conservar la asignación de
los permisos, use la instrucción ALTER PROCEDURE.
Se
recomienda encarecidamente que no modifique de forma directa los procedimientos
almacenados del sistema. En su lugar, copie las instrucciones desde un
procedimiento almacenado del sistema existente para crear un procedimiento
almacenado del sistema definido por el usuario y, a continuación, modifíquelo
para adaptarlo a sus necesidades.
Cuando
use la instrucción ALTER PROCEDURE, tenga en cuenta los hechos siguientes:
·
Si
desea modificar un procedimiento almacenado que se creó con opciones, como con
la opción WITHENCRYPTION, debe incluir la opción en la instrucción ALTER
PROCEDURE para conservar la funcionalidad que proporciona la opción.
·
ALTER
PROCEDURE sólo altera un procedimiento. Si el procedimiento llama a otros
procedimientos almacenados, los procedimientos almacenados anidados no se ven
afectados.
·
El
permiso para ejecutar esta instrucción se concede de forma predeterminada a los
creadores del procedimiento almacenado inicial, a los miembros de la función de
servidor sysadminy a los miembros de las funciones fijas de base de datos
db_ownery db_ddladmin. No se pueden conceder permisos para ejecutar ALTER
PROCEDURE
ALTER PROC [ EDURE ]
nombreProcedimiento[ ; número ] [ { @tipoDatos parámetro } [ VARYING ] [ =
valorPredeterminado] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION |
RECOMPILE , ENCRYPTION } ] [ FORREPLICATION ] AS instrucciónSQL[...n]
Eliminación
Los procedimientos
almacenados se eliminan con "drop procedure".
Sintaxis:
drop procedure NOMBREPROCEDIMIENTO;
Eliminamos el procedimiento
almacenado llamado "pa_libros_autor":
drop procedure pa_libros_autor;
Si el procedimiento que
queremos eliminar no existe, aparece un mensaje de error, para evitarlo,
podemos emplear esta sintaxis:
if object_id('NOMBREPROCEDIMIENTO') is not
null
drop procedure NOMBREPROCEDIMIENTO;
Eliminamos, si existe, el
procedimiento "pa_libros_autor", si no existe, mostramos un mensaje:
if object_id('pa_libros_autor') is not null
drop procedure pa_libros_autor
else
select 'No existe el procedimiento
"pa_libros_autor"';
"drop procedure"
puede abreviarse con "drop proc".
Se recomienda ejecutar el
procedimiento almacenado del sistema "sp_depends" para ver si algún
objeto depende del procedimiento que deseamos eliminar.
Podemos eliminar una tabla
de la cual dependa un procedimiento, SQL Server lo permite, pero luego, al
ejecutar el procedimiento, aparecerá un mensaje de error porque la tabla
referenciada no existe.
Ejemplos CREATE
1. La
tabla es la siguiente:
Creamos el
procedimiento almacenado.
CREATE PROCEDURE ObtenerNombreApellido
@idAlumno INT
AS BEGIN
SELECT nombre + ' ' + apellido
FROM estudiantes
WHERE id=@idAlumno
END
Ahora que ya está creado el
procedimiento almacenado, podemos llamarlo con la palabra EXECUTE.
EXECUTE
ObtenerNombreApellido
1. Instrucción
SQL
USE
AdventureWorks
Select
name, Color, ListPrice, SellStartDate
FROM
Production.Product
WHERE
SellStartDate > '1/1/2003'
ORDER
BY SellStartDate, Name
Procedimiento
con instrucción anterior
CREATE PROCEDURE PROCE1#CARNET
AS
Select
name, Color, ListPrice, SellStartDate
FROM
Production.Product
WHERE
SellStartDate > '1/1/2003'
ORDER
BY SellStartDate, Name
GO
Para probar el nuevo procedimiento, abra
una nueva consulta de SQL Server y escriba y ejecute el código siguiente.
USE AdventureWorks
EXEC PROCE1#CARNET
Ejemplos ALTER
Ejemplo 2
Con
el procedimiento anterior se verifico lo de ingresar valores nulos, ahora vamos
a ver que el nombre completo del usuario (nombre y apellido) no debe repetirse
en la base de datos, para ello utilizamos el siguiente código
2. Resumen
Un
procedimiento almacenado es un objeto perteneciente a
una base de
datos, que contiene un conjunto de instrucciones SQL,
tanto de consulta, como de manipulación de datos, como de control de la
secuencia del programa,
asociados a un nombre,
y que son
ejecutados en conjunto. Puede contener parámetros tanto de entrada
como de salida (parámetros
pasados por referencia), así como devolver un valor de retorno.
Tipos de Procedimientos Almacenados
· Procedimientos Almacenados del sistema
· Procedimientos almacenados Extendidos
· Procedimientos Almacenados sencillos
Ventajas de usar
procedimientos almacenados
·
Tráfico
de red reducido entre el cliente y el servidor
·
Mayor
seguridad
·
Reutilización
del código
·
Mantenimiento
más sencillo entre otras más.
3. Sumary
A stored procedure is an
object belonging to a database, which contains a set of SQL instructions, both
for query, data handling, and control of the sequence of the program,
associated with a name, and which are executed in set. It can contain both
input and output parameters (parameters passed by reference), as well as return
a return value.
Types of Stored Procedures
• Stored system procedures
• Extended Stored Procedures
• Simple Stored Procedures
Advantages of using stored
procedures
• Reduced network traffic
between the client and the server
• Greater security
• Code reuse
• Easier maintenance among
others.
4. Recomendaciones
· Al realizar las llamadas a los procedimientos use siempre el modelo
exacto ya sea mayúsculas y minúsculas de los nombres de procedimientos del
sistema.
· Al ejecutar un procedimiento definido por el usuario, se recomienda
calificar el nombre del procedimiento con el nombre de esquema. Esta práctica proporciona un
pequeño aumento del rendimiento porque el Motor de base de datos no tiene que
buscar en varios esquemas.
· Evita la
ejecución del procedimiento incorrecto si una base de datos tiene
procedimientos con el mismo nombre en varios esquemas.
5 Conclusiones
· Un procedimiento que se ejecuta automáticamente
funciona con los mismos permisos que los miembros del rol fijo de
servidor sysadmin .Todos
los mensajes de error generados por el procedimiento se escriben en el registro
de errores de SQL
Server.
· No existe límite en cuanto al número de procedimientos de
inicio que se pueden crear, aunque debe tener en cuenta que cada uno consume un
subproceso de trabajo mientras se ejecuta. Si es necesario ejecutar
múltiples procedimientos en el inicio, pero no es necesario que se ejecuten en
paralelo, haga que un procedimiento sea el procedimiento de inicio y que éste llame a los restantes.
5. Apreciación del Equipo
· En lo
posible siempre declarar e inicializar las variables al comienzo del código del
stored procedure, tratar de usar la cantidad de variables mínimas necesarias.
· 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.
6. Glosario de Términos
COMPILAR: La tarea de compilar se refiere al proceso de
traducción del código fuente, entendiéndose
por código fuente las líneas de código que se han escrito en un lenguaje de
programación, en este caso un lenguaje de programación de alto nivel.
RECOMPILACIÓN: Es volver a realizar el proceso de compilación.
TRANSACT-SQL : Es un lenguaje sumamente potente que nos
ayuda a definir tareas mientras trabajamos con bases de datos.
(CLR): Common Runtime Language es
un entorno de ejecución para los códigos de los programas que corren sobre la
plataforma Microsoft .NET.
ARCHIVO TEMPORAL: Un archivo temporal es un archivo
que puede ser creado por un programa cuando no puede asignar memoria suficiente
para sus tareas o bien el sistema operativo en algunos casos son copias de
seguridad realizadas antes de la modificación de determinado archivo por
motivos de seguridad
CREATE DEFAULT: Crear por Defecto
CREATE PROCEDURE: Crear Procedimiento
CREATE RULE: Crear Regla
CREATE TRIGGER: Crear Disparador
CREATE VIEW: Crear
Vista
8. Linkografías
https://edoc.site/procedimientos-almacenados-y-triggers-en-sql-server-pdf-free.html https://www.researchgate.net/profile/Jorge_Dominguez_Chavez2/publication/274634086_Triggers_funciones_y_procedimientos/links/5523e0ad0cf24f160943af03/Triggers-funciones-y-procedimientos.pdf
Link de la Diapositiva
No hay comentarios:
Publicar un comentario