Procedimientos almacenados en SQL Server, Diapositivas de Principios de la Gestión de Bases de Datos

¡Descarga Procedimientos almacenados en SQL Server y más Diapositivas en PDF de Principios de la Gestión de Bases de Datos solo en Docsity!

Programa de Ingeniería de Sistemas

GESTIÓN DE

DATOS

E

INFORMACIÓN II

Sesión 6

Tema:

Procedimientos almacenados

Resultado de

aprendizaje

Implementa procedimientos almacenados, cursores y triggers sobre una base de datos utilizando T-SQL para resolver las diversas actividades de una organización.

Evidencia de

aprendizaje

Los estudiantes demostrarán su comprensión y habilidades sobre el uso de procedimientos almacenados ,diseñando, implementando y optimizando para abordar necesidades específicas en una base de datos empresarial.

Revisa el siguiente video: https://youtu.be/jTYttzh63Uo?si=d3KGlGH3TC3Nitvt

Después de haber visualizado el video

en la slide anterior, reflexionamos y

respondemos las siguientes

interrogantes:

0 1 ¿Qué es un procedimiento almacenado? 0 2 0 3 ¿Para qué sirve un procedimiento almacenado? ¿Cuales son las ventajas de utilizar un procedimiento almacenado?

Gestión de Datos e Información II – Sesión 6

PROCEDIMIEMTOS

ALMACENADOS

Está formado por un conjunto de instrucciones Transact-SQL que definen un determinado proceso, puede aceptar parámetros de entrada y devolver un valor o conjunto de resultados. Este procedimiento se guarda en el servidor y puede ser ejecutado en cualquier momento.  (^) SQL Server incorpora procedimientos almacenados del sistema, se encuentran en la base de datos master y se reconocen por su nombre, todos tienen un nombre que empieza por sp_.  (^) Permiten recuperar información de las tablas del sistema y pueden ejecutarse en cualquier base de datos del servidor.

Gestión de Datos e Información II – Sesión 6

PROCEDIMIEMTOS

ALMACENADOS

 (^) También están los procedimientos de usuario, los crea cualquier usuario que tenga los permisos oportunos.  (^) Los procedimientos almacenados se diferencian de las instrucciones SQL ordinarias y de los lotes de instrucciones SQL en que están precompilados.  (^) La primera vez que se ejecuta un procedimiento, el procesador de consultas de SQL Server lo analiza y prepara un plan de ejecución que se almacena en una tabla del sistema.

Gestión de Datos e Información II – Sesión 6

PROCEDIMIEMTOS

ALMACENADOS

 (^) Éste existe hasta que se cierra la conexión que el usuario utilizó para crearlo, y hasta que se completan todas las versiones del procedimiento que se estuvieran ejecutando mediante otras conexiones.  (^) Una vez cerrada la conexión que se utilizó para crear el procedimiento, éste ya no se puede volver a ejecutar, sólo podrán finalizar las conexiones que hayan empezado a ejecutar el procedimiento.  (^) Tanto los procedimientos temporales como los no temporales se crean y ejecutan de la misma forma, el nombre que le pongamos indicará de qué tipo es el procedimiento.  (^) Los procedimientos almacenados se crean mediante la sentencia CREATE

Gestión de Datos e Información II – Sesión 6

PROCEDIMIEMTOS

ALMACENADOS

Para ejecutarlo también se puede utilizar el nombre del procedimiento almacenado sólo, siempre que sea la primera palabra del lote. Para eliminar un procedimiento almacenado utilizamos la sentencia DROP PROCEDURE. CREATE {PROC|PROCEDURE } [NombreEsquema.]NombreProcedimiento [{@parametro tipo} [VARYING] [= valorPredet] [OUT|OUTPUT] ] [,...n] AS { <bloque_instrucciones> [ ...n] }[;] <bloque_instrucciones> ::= {[ BEGIN ] instrucciones [ END ] }

Gestión de Datos e Información II – Sesión 6

PROCEDIMIEMTOS

ALMACENADOS

 (^) Si queremos definir un procedimiento temporal local el nombre deberá empezar por una almohadilla (#) y si el procedimiento es temporal global el nombre debe de empezar por ##.  (^) El nombre completo de un procedimiento almacenado o un procedimiento almacenado temporal global, incluidas ## , no puede superar los 128 caracteres.  (^) El nombre completo de un procedimiento almacenado temporal local, incluidas # , no puede superar los 116 caracteres.  (^) Transact-SQL permite abreviar la palabra reservada PROCEDURE por PROC sin que ello afecte a la funcionalidad de la instrucción.

Gestión de Datos e Información II – Sesión 6

PROCEDIMIEMTOS

ALMACENADOS

 (^) @parametro: representa el nombre de un parámetro. Se pueden declarar uno o más parámetros indicando para cada uno su nombre (debe de empezar por arroba) y su tipo de datos, y opcionalmente un valor por defecto (=valorPredet) este valor será el asumido si en la llamada el usuario no pasa ningún valor para el parámetro.  (^) Un procedimiento almacenado puede tener un máximo de 2. parámetros.  (^) Los parámetros son locales para el procedimiento; los mismos nombres de parámetro se pueden utilizar en otros procedimientos. De manera predeterminada, los parámetros sólo pueden ocupar el lugar de expresiones constantes; no se pueden utilizar en lugar de nombres de tabla, nombres de

Gestión de Datos e Información II – Sesión 6

PROCEDIMIEMTOS

ALMACENADOS

 (^) VARYING Sólo se aplica a los parámetros de tipo cursor. OUTPUT | OUT (son equivalentes) Indica que se trata de un parámetro de salida. El valor de esta opción puede devolverse a la instrucción EXECUTE que realiza la llamada.  (^) El parámetro variable OUTPUT debe definirse al crear el procedimiento y también se indicará en la llamada junto a la variable que recogerá el valor devuelto del parámetro.  (^) El nombre del parámetro y de la variable no tienen por qué coincidir; sin embargo, el tipo de datos y la posición de los parámetros deben coincidir a menos que se indique el nombre del parámetro en la llamada de la forma @parametro=valor.

Gestión de Datos e Información II – Sesión 6

PROCEDIMIEMTOS ALMACENADOS (PARAMETROS DE

ENTRADA)

CREATE PROCEDURE VerUsuariosPoblacion @pob CHAR(30),@pro CHAR(30) AS SELECT * FROM usuarios WHERE poblacion=@pob AND provincia = @pro; GO EXEC VerUsuariosPoblacion Madrid, Valencia

Gestión de Datos e Información II – Sesión 6

PROCEDIMIEMTOS ALMACENADOS (PARAMETROS DE

SALIDA)

Un procedimiento almacenado puede también devolver resultados, definiendo el parámetro como OUTPUT o bien utilizando la instrucción RETURN. Para poder recoger el valor devuelto por el procedimiento, en la llamada se tiene que indicar una variable donde guardar ese valor. CREATE PROC ultimo_contrato @ofi INT , @fecha DATETIME OUTPUT AS SELECT @fecha=( SELECT MAX(contrato) FROM empleados WHERE oficina=@ofi) GO

Gestión de Datos e Información II – Sesión 6

PROCEDIMIEMTOS ALMACENADOS (RETURN)

RETURN

Ordena salir incondicionalmente de una consulta o procedimiento, se puede utilizar en cualquier punto para salir del procedimiento y las instrucciones que siguen a RETURN no se ejecutan. Además los procedimientos almacenados pueden devolver un valor entero mediante esta orden. RETURN [expresion_entera] Expresion_entera es el valor entero que se devuelve. A menos que se especifique lo contrario, todos los procedimientos almacenados del sistema devuelven el valor 0. Esto indica que son correctos y un valor distinto de cero indica que se ha producido un error.