PDF de programación - PROGRAMACIÓN EN TRANSACT-SQL - SQL SERVER 2005

PROGRAMACIÓN EN
TRANSACT-SQL
SQL SERVER 2005

Manual de Referencia para usuarios

Salomón Ccance
CCANCE WEBSITE





PROGRAMACIÓN EN TRANSACT-SQL – SQL SERVER 2005



PROGRAMACIÓN EN TRANSACT-SQL

Hasta ahora hemos estudiado sentencias SQL orientadas a realizar una determinada tarea sobre la base
de datos como definir tablas, obtener información de las tablas, actualizarlas; estas sentencias las
hemos ejecutado desde el editor de consultas del MSSMS de una en una o a lo sumo una a continuación
de la otra dentro de la misma consulta formando un lote de instrucciones.

Las sentencias que ya conocemos son las que en principio forman parte de cualquier lenguaje SQL.
Ahora veremos que TRANSACT-SQL va más allá de un lenguaje SQL cualquiera ya que aunque no
permita:





Crear interfaces de usuario.
Crear aplicaciones ejecutables, sino elementos que en algún momento llegarán al servidor de
datos y serán ejecutados.


Incluye características propias de cualquier lenguaje de programación, características que nos permiten
definir la lógica necesaria para el tratamiento de la información:


Tipos de datos.

Estructuras de control de flujo.


 Definición de variables.

 Gestión de excepciones.
Funciones predefinidas.

Elementos para la visualización, que permiten mostrar mensajes definidos por el usuario

gracias a la cláusula PRINT.


Estas características nos van a permitir crear bloques de código orientados a realizar operaciones más
complejas. Estos bloques no son programas sino procedimientos o funciones que podrán ser llamados
en cualquier momento.

En SQL Server 2005 podemos definir tres tipos de bloques de código, los procedimientos almacenados,
los desencadenadores (o triggers) y funciones definidas por el usuario.

Nosotros estudiaremos los dos primeros.

Empezaremos por los procedimientos almacenados, veremos primero las sentencias para crear y
eliminar procedimientos almacenados, luego estudiaremos las instrucciones Transact-SQL más propias
de un lenguaje de programación nombradas en el tema de Introducción al Transact-SQL como son por
ejemplo los bucles y estructuras condicionales. Estas instrucciones las utilizaremos dentro de
procedimientos almacenados pero veremos que también nos servirán para definir otros bloques de
código.

Terminaremos esta unidad de programación estudiando los disparadores o Triggers muy similares a los
procedimientos almacenados que difieren básicamente en la forma en que entran en funcionamiento.


PROCEDIMIENTOS ALMACENADOS (STORE PROCEDURE)

Un procedimiento almacenado (STORE PROCEDURE) 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.

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

CCANCE WEBSITE | [email protected] | 999-638-991 | RPM #217344 | (98) 123*4110 | facebook.com/CCANCE



2




PROGRAMACIÓN EN TRANSACT-SQL – SQL SERVER 2005



una tabla del sistema. Posteriormente, el procedimiento se ejecuta según el plan almacenado. Puesto
que ya se ha realizado la mayor parte del trabajo de procesamiento de consultas, los procedimientos
almacenados se ejecutan casi de forma instantánea por lo que el uso de procedimientos almacenados
mejora notablemente la potencia y eficacia del SQL.

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.

También están los procedimientos de usuario, los crea cualquier usuario que tenga los permisos
oportunos.

Se pueden crear también procedimiento temporales locales y globales. Un procedimiento temporal local
se crea por un usuario en una conexión determinada y sólo se puede utilizar en esa sesión, un
procedimiento temporal global lo pueden utilizar todos los usuarios, cualquier conexión puede ejecutar
un procedimiento almacenado temporal global. É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 PROCEDURE y se ejecutan con
EXEC (o EXECUTE). 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.


ELIMINAR PROCEDIMIENTOS ALMACENADOS

Aunque no sabemos todavía crear un procedimiento comentaremos aquí la instrucción para eliminar
procedimientos y así podremos utilizarla en los demás ejercicios.

DROP {PROC|PROCEDURE} [nombreEsquema.]nombreProcedimiento [,...n
].

Transact-SQL permite abreviar la palabra reservada PROCEDURE por PROC sin que ello afecte a la
funcionalidad de la instrucción.
Ejemplos:

DROP PROCEDURE Dice_Hola;

Elimina el procedimiento llamado Dice_Hola.

DROP PROC Dice_Hola;

Es equivalente, PROC y PROCEDURE indican lo mismo.

Para eliminar varios procedimientos de golpe, indicamos sus nombres separados por comas:

DROP PROCEDURE Dice_Hola, Ventas_anuales;

Elimina los procedimientos Dice_Hola y Ventas_anuales.


CCANCE WEBSITE | [email protected] | 999-638-991 | RPM #217344 | (98) 123*4110 | facebook.com/CCANCE



3




PROGRAMACIÓN EN TRANSACT-SQL – SQL SERVER 2005



CREAR Y EJECUTAR UN PROCEDIMIENTO

Create procedure

Para crear un procedimiento almacenado como hemos dicho se emplea la instrucción CREATE
PROCEDURE:

CREATE {PROC|PROCEDURE} [NombreEsquema.]NombreProcedimiento
[{@parametro tipo} [VARYING] [= valorPredet] [OUT|OUTPUT] ]
[,...n]
AS { <bloque_instrucciones> [ ...n] }[;]
<bloque_instrucciones> ::=
{[BEGIN] instrucciones [END] }

Las instrucciones CREATE PROCEDURE no se pueden combinar con otras instrucciones SQL en el mismo
lote.

Después del verbo CREATE PROCEDURE indicamos el nombre del procedimiento, opcionalmente
podemos incluir el nombre del esquema donde queremos que se cree el procedimiento, por defecto se
creará en dbo. Ya que Sqlserver utiliza el prefijo sp_ para nombrar los procedimientos del sistema se
recomienda no utilizar nombres que empiecen por sp_.

Como se puede deducir de la sintaxis (no podemos indicar un nombre de base de datos asociado al
nombre del procedimiento) sólo se puede crear el procedimiento almacenado en la base de datos
actual, no se puede crear en otra base de datos.

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.

CREATE PROC Calcula_comision AS…

Es equivalente a

CREATE PROCEDURE calcula_comision AS…

@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.100 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 columna o
nombres de otros objetos de base de datos.

VARYING Sólo se aplica a los parámetros de tipo cursor por lo que se explicará cuando se expliquen los
cursores.

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

CCANCE WEBSITE | [email protected] | 999-638-991 | RPM #217344 | (98) 123*4110 | facebook.com/CCANCE



4




PROGRAMACIÓN EN TRANSACT-SQL – SQL SERVER 2005



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 vari