Tecnicas backup oracle

http://rapidshare.com/files/399060306/TECNICAS_BACKUP_oracle.pdf

Tutorial de Oracle Forms Developer 10G

http://rapidshare.com/files/396345817/Oracle_Forms_Developer_10g.pdf

Libro de SQL Server 2000

http://rapidshare.com/files/396344786/SQL2000.pdf

Manual de Mysql 5

Algunos de los temas en el manual:
- Información General
- Instalar MySQL
- Tutorial de MySQL
- Usar los programas de MySQL
- Administración de Base de Datos
- Replicación en MySQL, etc

http://rapidshare.com/files/396343677/Manual_de_MySql.pdf

Tutorial de Erwin

Descargar libro de la sgte dirección:
http://rapidshare.com/files/396341996/ldErwin.PDF

Base de Datos con SQL Server 200

El texto está diseñado para programadores, analistas o técnicos de sistemas que deseen conocer la mecánica de trabajo de las bases de datos a través del uso de una de ellas en concreto: Microsoft SQL Server 2000.
Aspectos que se revisan:
1- Conceptos teóricos sobre Bases de Datos.
2- Diseño y modelización de datos tomando como base el modelo Entidad/Relación
3- Generalidades de SQL Server 2000
4- Lenguaje SQL a través de la implementación del mismo en SQL Server 2000 (Transact SQL)
5- Ejemplos de diseño utilizando una herramienta CASE: Power Designor

Descargar

Procedimientos almacenados en Mysql 5

Hoy, mientras leía mensajes del correo antiguo rescaté unos apuntes de un curso de programación que hice el pasado verano. Los apuntes eran de MySQL 5, más específicamente de cómo hacer procedimientos almacenados , triggers , handlers, funciones y toda esa parafernalia que MySQL implementa en su versión 5.

MySQL ha sido siempre un motor de bases de datos muy rápido y muy utilizado para proyectos open source de código abierto, sobre todo para proyectos web dada su gran velocidad. Pero también a sido muy criticado por la falta de características avanzadas que otros Sistemas Gestores de Bases de Datos , como Oracle, SQLServer de Microsoft o PostgreSQL si tenían. Estas características avanzadas son sobre todo los procedimientos almacenados, triggers, transacciones y demás cosas.

MySQL, que ahora forma parte de Sun, se puso las pilas y en su versión 5 implementó muchas de estas características, dejando un SGBD muy rápido y además muy bien preparado para implementar bases de datos realmente grandes y mantenibles.

Pero, ¿qué es realmente un procedimiento almacenado? Pues es un programa que se almacena físicamente en una tabla dentro del sistema de bases de datos. Este programa esta hecho con un lenguaje propio de cada Gestor de BD y esta compilado, por lo que la velocidad de ejecución será muy rápida.

Principales Ventajas :

• Seguridad:Cuando llamamos a un procedimiento almacenado, este deberá realizar todas las comprobaciones pertinentes de seguridad y seleccionará la información lo más precisamente posible, para enviar de vuelta la información justa y necesaria y que por la red corra el mínimo de información, consiguiendo así un aumento del rendimiento de la red considerable.
• Rendimiento: el SGBD, en este caso MySQL, es capaz de trabajar más rápido con los datos que cualquier lenguaje del lado del servidor, y llevará a cabo las tareas con más eficiencia. Solo realizamos una conexión al servidor y este ya es capaz de realizar todas las comprobaciones sin tener que volver a establecer una conexión. Esto es muy importante, una vez leí que cada conexión con la BD puede tardar hasta medios segundo, imagínate en un ambiente de producción con muchas visitas como puede perjudicar esto a nuestra aplicación… Otra ventaja es la posibilidad de separar la carga del servidor, ya que si disponemos de un servidor de base de datos externo estaremos descargando al servidor web de la carga de procesamiento de los datos.
­­
• Reutilización: el procedimiento almacenado podrá ser invocado desde cualquier parte del programa, y no tendremos que volver a armar la consulta a la BD cada que vez que queramos obtener unos datos.

Desventajas:

El programa se guarda en la BD, por lo tanto si se corrompe y perdemos la información también perderemos nuestros procedimientos. Esto es fácilmente subsanable llevando a cabo una buena política de respaldos de la BD.

Tener que aprender un nuevo lenguaje… esto es siempre un engorro, sobre todo si no tienes tiempo.

¿Alguna otra?

Por lo tanto es recomendable usar procedimientos almacenados siempre que se vaya a hacer una aplicación grande, ya que nos facilitará la tarea bastante y nuestra aplicación será más rápida.

Vamos a ver un ejemplo :

Abrimos una consola de MySQL seleccionamos una base de datos y empezamos a escribir:

Vamos a crear dos tablas en una almacenaremos las personas mayores de 18 años y en otra las personas menores.

CREATE TABLE ninos(edad int, nombre varchar(50));
CREATE TABLE adultos(edad int, nombre varchar(50));

Imagínate que ahora queremos introducir personas en las tablas pero dependiendo de la edad queremos que se introduzcan en una tabla u otra, si estamos usando PHP podríamos comprobar mediante código si la persona es mayor de edad. Lo haríamos así:

$nombre = $_POST[‘nombre’];
$edad = $_POST[‘edad’];
 
if($edad < 18){
 mysql_query(‘insert into ninos values(’ . $edad . ‘ , “’.$nombre.’”)’);
}else{
mysql_query(‘insert into adultos values(’ . $edad . ‘ , “’.$nombre.’”)’);
}

Si la consulta es corta como en este caso, esta forma es incluso más rápida que tener que crear un procedimiento almacenado, pero si tienes que hacer esto muchas veces a lo largo de tu aplicación es mejor hacer lo siguiente:

Creamos el procedimiento almacenado:

delimiter //
 
CREATE procedure  introducePersona(IN edad int,IN nombre varchar(50))
begin
IF edad < 18 then
INSERT INTO ninos VALUES(edad,nombre);
else
INSERT INTO adultos VALUES(edad,nombre);
end IF;
end;
 
//

Ya tenemos nuestro procedimiento , vamos a por una visión más detallada…

La primera linea es para decirle a mySQL que a partir de ahroa hasta que no introduzcamos // no se acaba la sentencia, esto lo hacemos así por que en nuestro procedimiento almacenado tendremos que introducir el carcter “;” para las sentencias, y si pulamos enter MySQL pensará que ya hemos acabado la consulta y dará error.

Con create procedure empezamos la definición de procedimiento con nombre introducePersona. En un procedimiento almacenado existen parámetros de entrada y de salida, los de entrada (precedidos de “in”) son los que le pasamos para usar dentro del procedimiento y los de salida (precedidos de “out”) son variables que se establecerán a lo largo del procedimiento y una vez esta haya finalizado podremos usar ya que se quedaran en la sesión de MySQL.

En este procedimiento simple solo vamos usar de entrada, más adelante veremos como usar parámetros de salida.

Para hacer una llamada a nuestro procedimiento almacenado usaremos la sentencia call:

call introducePersona(25,”JoseManuel”);

Una vez tenemos ya nuestro procedimiento simplemente lo ejecutaremos desde PHP mediante una llamada como esta:

$nombre = $_POST['nombre'];
$edad = $_POST['edad'];
 
mysql_query(‘call introducePersona(’ . $edad . ‘ ,“ ’.$nombre.’ ”);’);

De ahora en adelante, usaremos siempre el procedimiento para introducir personas en nuestra BD de manera que si tenemos 20 scritps PHP que lo usan y un buen día decidimos que la forma de introducir personas no es la correcta, solo tendremos que modificar el procedimiento introducePersona y no los 20 scrits.

Transacciones anidadas - MySQL

Las transacciones anidadas
Recordemos como funcionan las transacciones. Tenemos 4 sentencias que son:
• BEGIN TRAN: Comienza una transacción y aumenta en 1 @@TRANCOUNT
• COMMIT TRAN: Reduce en 1 @@TRANCOUNT, y si @@TRANCOUNT llega a 0 guarda la transacción
• ROLLBACK TRAN: Deshace la transacción actual, o si estamos en transacciones anidadas deshace la más externa y todas las internas. Además pone @@TRANCOUNT a 0
• SAVE TRAN: Guarda un punto (con nombre) al que podemos volver con un ROLLBACK TRAN si es que estamos en transacciones anidadas y no queremos deshacer todo hasta la más externa.

Un par de ejemplos dejarán las cosas más claras:BEGIN TRAN
-- Primer BEGIN TRAN y ahora @@TRANCOUNT = 1
BEGIN TRAN
-- Ahora @@TRANCOUNT = 2
COMMIT TRAN
-- Volvemos a @@TRANCOUNT = 1
-- Pero no se guarda nada ni se hacen efectivos los posibles cambios
COMMIT TRAN
-- Por fin @@TRANCOUNT = 0
-- Si hubiera cambios pendientes se llevan a la base de datos
-- Y volvemos a un estado normal con la transacción acabada

Uso del commitBEGIN TRAN
-- Primer BEGIN TRAN y @@TRANCOUNT = 1
BEGIN TRAN
-- Ahora @@TRANCOUNT = 2
COMMIT TRAN
-- Como antes @@TRANCOUNT = 1
--Y como antes nada se guarda
ROLLBACK TRAN
-- Se cancela TODA la transacción. Recordemos que el COMMIT
-- de antes no guardo nada, solo redujo @@TRANCOUNT
-- Ahora @@TRANCOUNT = 0
COMMIT TRAN
-- No vale para nada porque @@TRANCOUNT es 0 por el efecto del ROLLBACK

Uso del rollback

En cuanto al SAVE TRAN podemos recordarlo con el siguiente ejemplo:CREATE TABLE Tabla1 (Columna1 varchar(50))
GOBEGIN TRAN
INSERT INTO Tabla1 VALUES (‘Primer valor’)
SAVE TRAN Punto1
INSERT INTO Tabla1 VALUES (‘Segundo valor’)
ROLLBACK TRAN Punto1
INSERT INTO Tabla1 VALUES (‘Tercer valor’)
COMMIT TRANSELECT * FROM Tabla1
Columna1
--------------------------------------------------
Primer valor
Tercer valor
(2 filas afectadas)

Un ROLLBACK a un SAVE TRAN no deshace la transacción en curso ni modifica @@TRANCOUNT, simplemente cancela lo ocurrido desde el ‘SAVE TRAN nombre‘ hasta su ‘ROLLBACK TRAN nombre’

Transacciones y procedimientos almacenados…
Cuando trabajamos con procedimientos almacenados debemos recordar que cada procedimiento almacenado es una unidad. Cuando se ejecuta lo hace de manera independiente de quien lo llama. Sin embargo si tenemos un ROLLBACK TRAN dentro de un procedimiento almacenado cancelaremos la transacción en curso, pero si hay una transacción externa al procedimiento en el que estamos trabajando se cancelará esa transacción externa.
Con esto no quiero decir que no se pueda usar, simplemente que hay que tener muy claras las 4 normas sobre las sentencias BEGIN, ROLLBACK y COMMIT que comentamos al principio de este artículo.
Veamos como se comporta una transacción en un procedimiento almacenado llamado desde una transacción.CREATE PROCEDURE Inserta2
AS
BEGIN TRAN --Uno
INSERT INTO Tabla1 VALUES ('Valor2')
ROLLBACK TRAN --Uno
GOCREATE PROCEDURE Inserta1
AS
BEGIN TRAN --Dos
INSERT INTO Tabla1 VALUES ('Valor 1')
EXEC Inserta2
INSERT INTO Tabla1 VALUES ('Valor3')
COMMIT TRAN --Dos
GO

En principio parece que si ejecutamos el procedimiento ‘Inserta1’ el resultado sería:EXECUTE inserta1
SELECT * FROM tabla1txt
--------------------------------------------------
Valor1
Valor3
(2 filas afectadas)

Pero lo que obtenemos es:EXECUTE inserta1
SELECT * FROM tabla1 (1 filas afectadas)
(1 filas afectadas)
Servidor: mensaje 266, nivel 16, estado 2, procedimiento Inserta2, línea 8
El recuento de transacciones después de EXECUTE indica que falta una
instrucción COMMIT o ROLLBACK TRANSACTION. Recuento anterior = 1,
recuento actual = 0.
(1 filas afectadas)
Servidor: mensaje 3902, nivel 16, estado 1, procedimiento Inserta1, línea 8
La petición COMMIT TRANSACTION no tiene la correspondiente BEGIN TRANSACTION.
txt
--------------------------------------------------
Valor3
(1 filas afectadas)

Si analizamos estos mensajes vemos que se inserta la primera fila, se salta al segundo procedimiento almacenado y se inserta la segunda fila. Se ejecuta el ‘ROLLBACK TRAN –Dos’ del segundo procedimiento almacenado y se deshacen las dos inserciones porque este ROLLBACK cancela la transacción exterior, la del primer procedimiento almacenado.
A continuación se termina el segundo procedimiento almacenado y como este procedimiento tiene un ‘BEGIN TRAN –Dos’ y no tiene un COMMIT o un ROLLBACK (recordemos que el ‘ROLLBACK TRAN –Dos’ termina el ‘BEGIN TRAN –Uno’) se produce un error y nos avisa que el procedimiento almacenado termina con una transacción pendiente.
Al volver al procedimiento almacenado externo se ejecuta el INSERT que inserta la tercera fila y a continuación el ‘COMMIT TRAN –-Uno’. Aquí aparece otro error puesto que este ‘COMMIT TRAN –Uno’ estaba ahí para finalizar una transacción que ya ha sido cancelada anteriormente.

El modo correcto
Para que nuestras transacciones se comporten como se espera dentro de un procedimiento almacenado podemos recurrir al SAVE TRAN.
Veamos como:CREATE PROCEDURE Inserta2
AS
SAVE TRAN Guardado
INSERT INTO Tabla1 VALUES ('Valor2')
ROLLBACK TRAN Guardado
GOCREATE PROCEDURE Inserta1
AS
BEGIN TRAN
INSERT INTO Tabla1 VALUES ('Valor 1')
EXEC Inserta2
INSERT INTO Tabla1 VALUES ('Valor 3')
COMMIT TRAN
GO

Ahora el ‘ROLLBACK TRAN guardado’ del segundo procedimiento almacenado deshace la transacción sólo hasta el punto guardado. Además este ROLLBACK no decrementa el @@TRANCOUNT ni afecta a las transacciones en curso.
El resultado obtenido al ejecutar este procedimiento almacenado es:EXECUTE inserta1
SELECT * FROM tabla1(1 filas afectadas)
(1 filas afectadas)
(1 filas afectadas)
txt
--------------------------------------------------
Valor 1
Valor 3
(2 filas afectadas)

Una vez vistos estos ejemplos queda claro que no hay problema en utilizar transacciones dentro de procedimientos almacenados siempre que tengamos en cuenta el comportamiento del ROLLBACK TRAN y que utilicemos el SAVE TRAN de manera adecuada.

Control de flujo en procedimientos almacenados para MySQL 5

Seguimos con los procedimientos almacenados. Vamos a ver como llevar a cabo el control de flujo de nuestro procedimiento. También es interesante observar el uso de las variables dentro de los procedimientos. Si se declara una variable dentro de un procedimiento mediante el código :

declare miVar int;

Esta tendrá un ámbito local y cuando se acabe el procedimiento no podrá ser accedida. Una vez la variable es declarada, para cambiar su valor usaremos la sentencia SET de este modo :

SET miVar = 56 ;

Para poder acceder a una variable a la finalización de un procedimiento se tiene que usar parámetros de salida.
Vamos a ver unos ejemplos para comprobar lo sencillo que es :

IF THEN ELSE

delimiter //
CREATE procedure miProc(IN p1 int)     /* Parámetro de entrada */
    begin
        declare miVar int;        /* se declara variable local */
        SET miVar = p1 +1 ;        /* se establece la variable */
        IF miVar = 12 then
            INSERT INTO lista VALUES(55555);
        else
INSERT INTO lista VALUES(7665);
        end IF;
    end;
//

SWITCH

delimiter //
CREATE procedure miProc (IN p1 int)
    begin
        declare var int ;
        SET var = p1 +2 ;
        case var
            when 2 then INSERT INTO lista VALUES (66666);
            when 3 then INSERT INTO lista VALUES (4545665);
            else INSERT INTO lista VALUES (77777777);
        end case;
    end;
//

Creo que no hacen falta explicaciones.

COMPARACIÓN DE CADENAS

delimiter //
CREATE procedure compara(IN cadena varchar(25), IN cadena2 varchar(25))
    begin
        IF strcmp(cadena, cadena2) = 0 then
            SELECT "son iguales!";
        else
            SELECT "son diferentes!!";
        end IF;
    end;
//

La función strcmp devuelve 0 si las cadenas son iguales, si no devuelve 0 es que son diferentes.

USO DE WHILE

delimiter //
CREATE procedure p14()
    begin
        declare v int;
        SET v = 0;
        while v < 5 do
            INSERT INTO lista VALUES (v);
            SET v = v +1 ;
        end while;
    end;
//

Un while de toda la vida.

USO DEL REPEAT

delimiter //
CREATE procedure p15()
    begin
        declare v int;
        SET v = 20;
        repeat
            INSERT INTO lista VALUES(v);
            SET v = v + 1;
        until v >= 1
        end repeat;
    end;
//

El repeat es similar a un “do while” de toda la vida.

LOOP LABEL

delimiter //
CREATE procedure p16()
    begin
        declare v int;
        SET v = 0;
        loop_label : loop
            INSERT INTO lista VALUES (v);
            SET v = v + 1;
            IF v >= 5 then
            leave loop_label;
            end IF;
        end loop;
    end;
//

Este es otro tipo de loop, la verdad es que teniendo los anteriores no se me ocurre aplicación para usar este tipo de loop, pero es bueno saber que existe por si algún día te encuentras algún procedimiento muy antiguo que lo use. El código que haya entre loop_label : loop y end loop; se ejecutara hasta que se encuentre la sentencia leave loop_label; que hemos puesto en la condición, por lo tanto el loop se repetirá hasta que la variable v sea >= que 5.
El loop puede tomar cualquier nombre, es decir puede llamarse miLoop: loop, en cuyo caso se repetirá hasta que se ejecute la sentencia leave miLoop.

TEMA 17: Programar procedimientos almacenados

Parámetros y variables

Los parámetros y las variables son una parte fundamental de hacer dinámico a un procedimiento almacenado. Los parámetros de entrada habilitan al usuario que está ejecutando el procedimiento a pasar valores al procedimiento almacenado. Los parámetros de salida amplían la salida de los procedimientos almacenados mas allá del conjunto de resultados retornado por una consulta. Los datos desde un parámetro de salida son capturados en memoria cuando el procedimiento se ejecuta. Para retornar un valor desde el parámetro de salida se debe crear una variable para que lo mantenga. Se puede mostrar el valor con los comando SELECT o PRINT, o usar el valor para completar otro comando en el procedimiento.

En resumen, un parámetro de entrada se define en el procedimiento almacenado y un valor es provisto cuando el procedimiento se ejecuta. Un parámetro de salida se define usando la palabra clave OUTPUT. Cuando se ejecuta el procedimiento, se guarda en memoria un valor para el parámetro de salida. Para utilizarlo, se declara una variable para mantener al valor. Los valores de salida típica de muestran en pantalla una vez que terminó la ejecución del procedimiento.

El siguiente procedimiento muestra el uso de ambos parámetros, de salida y de entrada:

USE Pubs

GO

CREATE PROCEDURE dbo.SalesForTitle

   @Title.varchar(80), --Parámetro de entrada

   @YtdSales int OUTPUT, --Primer parámetro de salida

   @TitleText varchar(80) OUTPUT –Segundo parámetro de salida

AS

-- Asigna la columna de datos a los parmetros de salida y

-- controla por un título que coincida con el parámetro de entrada

SELECT @YtdSales = ytd_sales, @TitleText = title

FROM titles WHERE title LIKE @Title

GO

El parámetro de entrada es @Title (título), y los de salida son @YtdSales y @TitleText. Fíjese que los tres parámetros tienen definidos sus tipos de datos. Los parámetros de salida incluye la palabra clave OUTPUT. Después que se definen los parámetros, el comando SELECT utiliza a los tres parámetros. Primero, a los parámetros de salida se les asigna a las columnas de la consulta. Cuando se ejecuta la consulta, los parámetros de salida contendrán los valores de esas dos columnas. La cláusula WHERE del comando SELECT contiene el parámetro de entrada, @Title. Cuando se ejecuta el procedimiento, se debe proveer un valor para el parámetro de entrada o fallará la consulta.

El siguiente comando ejecuta el procedimiento almacenado que vimos:

-- Declara variables para recibir las salidas del procedimiento

DECLARE @y_YtdSales int, @t_TitleText varchar(80)

EXECUTE SalesForTitle

-- configura los valores de variables con los parámetros de salida

@YtdSales = @y_YtdSales OUTPUT,

@Titletext = @t_TitleText OUTPUT,

@Title = “%Garlic%” – especifica un valor para el parámetro de entrada

-- Muestra las variables retornadas por el procedimiento

SELECT “Title” = @t_TitleText, “Número de Ventas” = @y_YtdSales

GO

Se declaran dos variables: @y_YtdSales y @t_TitleText. Estas dos variables recibirán los valores almacenados en los parámetros de salida. Advierta que el tipo de datos de la variables que reciben son los mismos que los tipos de los correspondientes parámetros de salida. Estas dos variables pueden tener el mismo nombre que los parámetros de salida correspondientes dado que las variables en un procedimiento almacenado son locales al batch que las contiene. Por claridad, los nombres de las variables son diferentes a los de los parámetros de salida. Cuando se declara una variable, esta no se asigna con un parámetro de salida. Las variables son asignadas con parámetros de salida después del comando EXECUTE. Fíjese que la palabra OUTPUT se indica cuando el parámetro se asigna a la variable. Si no se especifica OUTPUT, la variable no puede mostrar los valores en el comando SSELECT al final del código. Por ‘ultimo, el parámetro de entrada @Title es igualado a %Garlic%. Este valor se envía a la cláusula WHERE del comando SELECT del procedimiento almacenado. Dado que la cláusula WHERE usa la palabra clave KEYWORD, se pueden usar caracteres comodines (tal como %) para que que la consulta busque aquellos títulos que contienen la palabra Garlic.

A continuación se muestra un modo mas sucinto  de ejecutar el procedimiento. Fíjese que no es necesario asignar específicamente las variables desde el procedimiento almacenado al valor del parámetro de salida o a las variables de salida declaradas aquí:

DECLARE @y_YtdSales int, @t_TitleText varchar(80)

EXECUTE SalesForTitle

“%Garlic%”, -- configura el valor del parámetro de entrada

@y_YtdSales OUTPUT -- recibe el primer parámetro de salida

@t_TitleText OUTPUT -- recibe el segundo parámetro de salida

-- muestra las variables retornadas por la ejecución del procedimiento

SELECT “Title” = @t_TitleText, “Número de ventas” = @y_YtdSales

GO

Cuando se ejecuta el procedimiento, este retorna lo siguiente:

Title	Number Return of Sales
NULL	NULL

Un interesante resultado de este procedimiento es que se retorna sólo una fila. Aún cuando el comando SELECT en el procedimiento devuelve múltiples filas, cada variable mantiene sólo un valor (la última fila de datos retornada). Mas adelante veremos algunas soluciones a este problema.

El comando RETURN y el manejo de errores

A menudo, se necesita codificar para manejar errores. SQL Server provee funciones y comandos para tratar con los errores que se producen durante la ejecución de un procedimiento almacenado. Las dos categorías primarias de errores de computo, tales como una base no disponible o errores de usuarios. Se utilizan códigos de retorno y la función @@ERROR para manejar errores que se producen cuando se ejecuta el procedimiento.

Los códigos de retorno pueden usarse para otros propósitos además de manejar errores. El comando RETURN se usa para generar códigos y salidas de un batch, y puede proveer d cualquier valor entero al programa que llamador. Veremos ejemplos de códigos que usan los códigos de retorno para proveer un valor tanto como para manejar errores o para otros fines.

El comando RETURN se usa primariamente para el manejo de errores porque cuando se corre el comando RETURN, el procedimiento almacenado termina inmediatamente.

Considere el procedimiento almacenado SalesForTitle del punto anterior. Si el valor especificado para el valor de entrada (@Title) no existe en la base ded atos, ejecutar el procedimiento retorna el siguiente conjunto de resultados:

Title	Number Return of Sales	Code
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean	375	0

Es mas claro explicar el usuario que no se han encontrado registroa. El ejemplo siguiente muestra como modificar el procedimiento almacenado SalesForTitle para usar un código de retorno (y proveer un mensaje mas útil):

ALTER PROCEDURE dbo.SalesForTitle

@Title varchar(80), @YtdSales int OUTPUT,

@titletext varchar (80) OUTPUT

AS

-- Controla para ver si el titulo esta la base. Sino, sale y devuelve 1

IF (SELECT COUNT(*) FROM titles WHERE title LIKE @Title) = 0

    RETURN(1)

ELSE

SELECT @YtdSales = ytd_sales, @TitleText = title

FROM titles WHERE title LIKE @Title

GO

La sentencia IF posterior la palabra clave AS determina si el parámetro de entrada es provisto cuando el procedimiento se ejecuta y si coincide con algún registro de la base de datos. Si la función COUNT retorna 0, entonces el código de retorno es puesto en 1, RETURN(1). En otro caso, se ejecuta la consulta SELECT por las ventas anuales y la información del libro. En este caso el código de retorno es igual a 0.

Para invocar el procedimiento se necesitan hacer algunos cambios. El ejemplo siguiente configura al parámetro de entrada @Title igual a Garlic%:

--Agrega @r_Code para mantener el resultado

DECLARE @y_YtdSales int, @t_TitleText varchar(80), @r_Code int

-- Ejecuta el procedimeinto e iguala @r_Code al el procedimiento

EXECUTE @r_Code = SalesForTitle

@YtdSales = @y_YtdSales OUTPUT,

@TitleText = @t_TitleText OUTPUT,

@Title = "Garlic%"

-- Determina el valor de @r_Code y ejecuta el codigo

IF @r_Code = 0

SELECT "Title" = @t_TitleText,

"Number of Sales" = @y_YtdSales, "Return Code" = @r_Code

ELSE IF @r_Code = 1

PRINT 'No matching titles in the database. Return code=' + CONVERT(varchar(1),@r_Code)

GO

TEMA 16: Creando, ejecutando, modificando y borrando procedimientos almacenados

Como se guarda un procedimiento

Cuando se crea un procedimiento, SQL Server chequea la sintaxis de los comandos Transact-SQL que incluye. Si la sintaxis es incorrecta, SQL Server generará un mensaje  de error “sintax incorrect” (sintaxis incorrecta), y el procedimiento no será creado. Si el procedimiento pasa el chequeo de sintaxis, el procedimiento se guarda, escribiéndose su nombre y otras informaciones (tal como un número autogenerado de identificación) en la tabla SysObject. El texto usado para crear el procedimiento se escribe en la tabla SysComments de la base de datos actual.

El siguiente comando SELECT consulta a la tabla SysObjects en la base de datos Pubs para mostrar el número de identificación del procedimiento almacenado ByRoyalty:

SELECT [name].[id] FROM [pubs].[dbo].[SysObjects]

WHERE [name] = ‘byroyalty’

 Esta consulta retorna lo siguiente:

name	id
byroyalty	581577110

Usando la información retornado de la tabla SysObjects, el próximo comando SELECT consulta a la tabla SysComments al usar el número de identificación del procedimiento almacenado ByRoyalty:

SELECT [text] FROM [pubs].[dbo].[SysComments]

WHERE [id] = 581577110

Esta consulta retorna el texto del procedimiento almacenado, cuyo número de identificación es el 581577110.

Usar el procedimiento almacenado de sistema sp_helptext es una mejor opción para mostrar el texto usado para crear un objeto (tal como un procedimiento almacenado no encriptado), dado el texto es retornado en múltiples filas. 

Métodos para crear procedimientos almacenados

SQL Server provee muchos métodos que se pueden usar para crear un procedimiento almacenado: el comando Transact-SQL CREATE PROCEDURE, SQL-DMO (usando el objeto StoredProcedure y escapa alcance de este Kit), a través del árbol de consola del Enterprise Manager, el asistente para crear procedimientos almacenados (al cual se puede acceder a travé del Enterprise Manager).

El comando CREATE PROCEDURE

Se puede usar el comando CREATE PROCEDURE, o su versión abreviada, CREATE PROC, para crear un procedimiento almacenado en el Query Analyzer o una herramienta de comando como osql. Cuando utiliza CRETE PROC, se pueden realizar las siguientes tareas:

·        Especificar agrupamientos de procedimientos almacenados

·        Definir parámetros de entrada-salida, sus tipos de datos, y sus valores por defecto. Cuando se definen parámetros de entrada y salida, estos siempre van precedidos por el signo @, seguido del nombre del parámetro y luego una designación del tipo de dato. Los parámetros de salida deben incluir la palabra clave OUTPUT para diferenciarlos de los de entrada.

·        Usar códigos de retorno para mostrar información acerca del éxito o falla de una tarea.

·        Controlar si un plan de ejecución debería ser guardado temporariamente (en el cache) para un procedimiento.

·        Encriptar el contenido del procedimiento almacenado por razones de seguridad.

·        Especificar las acciones que deberá tomar el procedimiento almacenado cuando se ejecute.

Proveer de contexto a un procedimiento almacenado

Con la excepción de los procedimiento almacenado temporarios, un procedimiento almacenado se crea siempre en la   base de datos actual. Por lo tanto, Ud. siempre debería especificar la base de datos actual usando el comando USE nombre_base seguido por el por el comando GO ntes de crear un procedimiento almacenado- Se puede usar la lista desplegable Cahnge Database en el Query Analizer para seleccionar la base de datos actual.

El script siguiente selecciona la base Pubs y luego crea un procedimientos llamado ListAuthorNames (lista de los nombres de los autores) , que pertenece a dbo:

USE Pubs

GO

CREATE PROCEDURE [dbo].[ListAuthorNames]

AS

SELECT [au_fname], [aufname] FROM [pubs].[dbo].[authors]

 Fíjese que el nombre del procedimiento está totalmente calificado en el ejemplo. Un nombre  de procedimiento almacenado totalmente incluye el nombre de su propietario (en este caso dbo) y el nombre del procedimiento, ListAuthorNames. Se especifica dbo como propietario si se quiere asegurar que la tarea del procedimiento almacenado correrá sin importar la tabla propietaria en la base de datos. El nombre de la base de datos no es parte del nombre totalmente calificado de un procedimiento almacenado cuando se usa el comando CREATE PROCEDURE.

Crear procedimientos almacenados temporarios

Para crear un procedimiento almacenado temporal local, agregue adelante del nombre del procedimiento el símbolo #. Este signo numeral instruye al SQL Server para que cree el procedimiento en la TempDB. Para crear un procedimiento almacenado temporario global, , se agrega adelante del nombre del procedimiento un doble símbolo numeral ##, que también será creado en la TempDB. SQL Server ignora la base de datos actual cuando crea un procedimiento temporario. Por definición, un procedimiento almacenado sólo puede existir en la TempDb. Para crear un procedimiento almacenado directamente en la TempDB que no es ni local ni global haga que la TempDB sea la base de datos actual y luego cree el procedimiento. El ejemplo siguiente crea un procedimiento almacenado temporario local, un procedimiento almacenado temporario global y un procedimiento directamente en la TempDB:

·        crear un  procedimiento temporario local

CREATE PROCEDURE #localtemp

AS

SELECT * from [pubs].[dbo].[authors]

GO

·        crear un  procedimiento temporario global

CREATE PROCEDURE #globaltemp

AS

SELECT * from [pubs].[dbo].[authors]

GO

·        crear un procedimiento almacenado temporario que sea local en tempdb

USE TEMPDB

GO

CREATE PROCEDURE directtemp

AS

SELECT * from [pubs].[dbo].[authors]

GO

Nombres de bases de datos totalmente calificadas son especificadas en los comandos SELECT. Si el procedimiento no se ejecuta en el contexto de una base de datos específica, luego nombre de bases de datos totalmente calificadas aseguran que se está referenciando a la base de datos apropiada.

En el tercer parte del ejemplo, cuando se crea un procedimiento almacenado temporario directamente en la base de datos TempDB, se debe hacer a la base de datos TempDB la base actual antes de crearlo, o se debe calificar totalmente el nombre ([TempDb].[dbo].[directtemp]). Tal como los procedimientos almacenados del sistema, que se guardan en la base Masters, los procedimientos almacenados temporarios locales y globales están disponibles para ser usados por su nombre corto (sin importar la base de datos actual).

Agrupar, almacenar temporariamente en memoria y encriptar procedimientos almacenados

Los procedimientos almacenados puedes ser agrupados lógicamente al momento de su creación. Esta técnica es muy útil para procedimientos almacenados que deberían ser administrados cono una unidad independiente y que son usados por una aplicación específica. Para agrupar procedimientos almacenados, se debe asignar a cada procedimiento del grupo el mismo nombre y agregarle un número único separado por un punto y coma. Por ejemplo: el denominar a dos procedimientos almacenados con ProcAgrupado;1 y ProcAgrupado;2 al momento de su creación, los agrupa lógicamente. Cuando se ve el contenido de ProcAgrupado, se ve el código para ambos ProcAgrupado;1 y ProcAgrupado;2.

Por defecto, un plan de ejecución de un procedimiento almacenado se guarda en memoria temporal (cacheo) la primera vez que se ejecuta, y no es cacheado de nuevo hasta que el servidor se reinicie o hasta que la estructura de una tabla usada por el procedimiento almacenado cambie. Por razones de performance se podría no querer cachear un plan de ejecución de un procedimiento almacenado. Por ejemplo, cuando los parámetros de un procedimiento almacenado varían considerablemente de una ejecución a la siguiente, cachear un plan de ejecución es contraproducente. Para provocar que un procedimiento almacenado sea recomplilado cada vez que se ejecuta, agregue las palabras claves WITH RECOMPILE cuando crea el procedimiento almacenado. Se puede también forzar una recompilación mediante el uso del procedimiento almacenado del sistema sp_recompile o especificando WITH RECOMPILE cuando se ejcuta el procedimiento.

Encriptar un procedimiento almacenado protege su contenido de ser visto. Para encriptar un procedimiento almacenado, use la palabra clave WITH ENCRYPTION cuando crea el procedimiento. Por ejemplo, el código siguiente crea un procedimiento encriptado llamado Protected:

USE Pubs

GO

CREATE PROC [dbo].[protected] WITH ENCRYPTION

AS

SELECT [au_fname], [au_lname] FROM [pubs].[dbo].[authors]

Las palabras claves WITH ENCRYPTION encriptan la columna de texto del procedimiento almacenado en la tabla SysComments. Un modo simple para determinar si un procedimiento almacenado está encriptado es usar la función OBJECTPROPERTY:

•      controla si el procedimiento almacenado esta encriptado retornando 1 para IsEncrypted

SELECT OBJECTPROPERTY(object_id(‘protected’), ‘IsEncrypted’)

O llamando al procedimiento con sp_halptext:

•      si el procedimiento esta encriptado, retorna “The objects comments have been encrypted.”

EXEC sp_helptext protected

Un procedimiento almacenado encriptado no puede ser replicado. Después que un procedimiento almacenado es encriptado, SQL Server lo desencripta para sólo para su ejecución. Su definición no puede ser desencriptada para ser vista por nadie, incluido el propietario del procedimiento almacenado. Por lo tanto, se debe estar seguro que se cuenta con una copia en algún lugar seguro antes de encriptar un procedimiento almacenado.

Enterprise Manager

Se puede crear un procedimiento almacenado directamente en el Enterprise Managrer. Para ello, se expande el árbol de la consola para su servidor y luego se expande la base de datos donde se creará el procedimiento almacenado. Clic derecho sobre el nodo Stored Procedure, y luego clic sobre New Stored Procedure. Cuando aparezca el cuadro de diálogo Stored Procedure Properties – New Stored Procedure se ingresa el contenido del nuevo procedimiento almacenado. La figura muestra el cuadro de diálogo Stored Procedure Properties – New Stored Procedure que contiene el código del ejemplo anterior.

Se puede, además, controlar la sintaxis del procedimiento almacenado antes de su creación y guardar una plantilla que aparecerá siempre que se cree un nuevo procedimiento almacenado y se configuren permisos. Una vez que el procedimiento almacenado se crea, se puede abrir las propiedades del procedimiento y configurar los permisos. Por defecto el propietario del procedimiento y el administrador tienen permisos totales sobre el procedimiento almacenado.

Las plantillas son muy útiles porque proveen un marco de trabajo para crear documentación consistente sobre los procedimientos almacenados. Comúnmente se agrega texto al encabezamiento que describe como se deberá documentar cada procedimiento almacenado que se crea.

El asistente Create Stored Procedure

El asistente Create Stored Procedure (crear procedimientos almacenados) permite crear nuevos procedimientos almacenados paso a paso. Se puede acceder al asistente seleccionando Wizars (asistentes) desde el menú Tools (herramientas). En la ventana Select Wizard (seleccionar asistente), expandir la opción Database (base de datos), y luego seleccionar Create Stored Procedure Wizard (asistente para crear procedimientos almacenados) y presionar Ok. A partir de quí se deben completar los pasos indicados por el asistente. La Figura muestra la pantalla de bienvenida del Create Stored Procedure Wizard mostrando las cosas que se pueden hacer con él.

El asistente Create Stores Procedures lo habilita para que Ud. cree procedimientos que inserten, eliminen, o actualicen datos en las tablas. Para modificar los procedimientos almacenados que crea el asistente, se puede editarlos dentro del asistente o utilizar otras herramientas (como el Query Analizer).

Crear y agregar procedimientos almacenados extendidos

Después de crear un procedimiento almacenado extendido, se debe registrar con SQL Server.  Sólo los usuarios que tiene permisos de administrador pueden registrar un procedimiento almacenado con el SQL Server. Para registrar un procedimiento almacenado extendido, se puede usar el procedimiento del sistema sp_addextendedproc en el Query analyzer o usar el Enterprise Manager. En el Enterprise Manager, expanda la base de datos Master, clic derecho sobre el nodo Extended Stored Procedure (procedimiento almacenado extendido), y clic sobre la opción New Extended Stored Procedure (nuevo procedimiento almacenado extendido). Los procedimientos almacenados extendidos sólo pueden ser agregados a la base de datos Master. 

Resolución diferida de nombres

Cuando se crea un procedimiento almacenado, SQL Server no controla por la existencia de los objetos que este pueda referenciar. Esto es así porque es posible que un objeto, tal como una tabla referenciada en un procedimiento almacenado no existe al momento de la creación del procedimiento almacenado. Esta capacidad se denomina resolución diferida de nombres. La verificación de la existencia de los objetos se produce al momento de la efectiva ejecución del procedimiento.

Cuando se refiere a un objeto (como una tabla) en un procedimiento almacenado se debe asegurar que se especifica el propietario del objeto. Por defecto, SQL Server  asume que el creador del procedimiento almacenado es también el propietario de los objetos referenciados en el procedimiento. Para evitar confusión, considere especificar a dbo como el propietario de todos objetos (tanto los procedimientos almacenados como los objetos referenciados dentro de estos).

Ejecutar un procedimiento almacenado

Como ya vimos, se puede correr un procedimiento almacenado en el Query Analyzer simplemente ingresando su nombre y cualquier parámetro requerido. Por ejemplo se vió el contenido de un procedimiento almacenado ingresando sp_helptext seguido del nombre del procedimiento almacenado a ser visto. El nombre del procedimiento almacenado a ser visto es en este caso el valor del parámetro.

Si el procedimiento almacenado no es el primer comando en un batch, para ejecutarlo se debe preceder el nombre del procedimiento con las palabras claves EXECUTE o EXEC. 

Llamar un procedimiento almacenado para ejecución

Cuando se especifica el nombre de un procedimiento, el nombre puede ser totalmente calificado, tal como [nombre_base].[propietario].[nombre_proc] o, si la base que contiene al procedimiento es la base actual, mediante el comando USE, entonces se puede ejecutar el procedimiento almacenado especificando sólo [propietario].[nombre_proc]. Si el nombre de el nombre del procedimiento es único en la base de datos activa, se puede simplemente especificar [nombre_proc].

Nota: En este tema se han utilizado los identificadores entre [] en los nombres de los objetos, aún cuando dichos nombres no violan la reglas de nombres de objetos, con fines de claridad de los ejemplos.

Nombres totalmente calificados no son necesarios cuando se ejecutan procedimientos del sistema que tienen el prefijo sp_, ni para procedimientos almacenados temporarios locales, o globales. SQL Server  buscará en la base Master por cualquier procedimiento almacenado que tenga el prefijo sp_ donde el propietario sea dbo. Para evitar confusión, no se debe nombrar procedimientos almacenados locales con los mismos nombres de procedimientos almacenados en la base Master. Si ello ocurre, se debe asegurar que el propietario de la base sea otro que dbo. SQL Server  no busca automáticamente en la base master por procedimientos almacenados extendidos, Por lo tanto, o se lo invoca por su nombre totalmente calificado o se cambia la base actual a la que pertenece el procedimiento. 

Especificar parámetros y sus valores

Si un procedimiento almacenado requiere valores de parámetros, se deben especificar cuando se ejecuta el procedimiento. Cuando se definen parámetros de entrada y salida se los precede del símbolo @, seguido por el nombre del parámetro y la designación del tipo de dato. Cuando se los invoca para ser ejecutados, se debe incluir un valor para el parámetro (y opcionalmente, el nombre del parámetro). Los próximos dos ejemplos corren el procedimiento almacenado au_info de la base Pubs con dos parámetros: @lastname (apellido) y @firstname (nombre):

 --llama al procedimiento almacenado con los valores de los parámetros

USE Pubs

GO

EXECUTE au_info Green, Marjorie

--llama al procedimiento alamcenado con los valores y nombres de los parámetros

USE Pubs

GO

EXECUTE au_info

Green, Marjorie

@lastname = ‘Green’, @firstname = ‘Marjorie’

En el primer ejemplo, los valores de los parámetros fueron especificados pero los nombres no. Si se especifican los valores sin sus correspondientes nombres, los valores se deben indicar en el mismo orden con se especificaron en la definición del procedimiento. En el segundo ejemplo, los nombres y los valores son ambos indicados. En este caso, se los puede poner en cualquier orden. Aquellos parámetros a los que se les especifica un valor por defecto en la definición del procedimiento no necesitan ser obligatoriamente especificados cuando se los llama.

La lista siguiente muestra algunas sintaxis opcionales disponibles cuando se ejecuta un procedimiento almacenado:

• Un variable de código de retorno de tipo entero (integer) para almacenar valores retornados desde el procedimiento almacenado. La palabra clave RETURN con un valor (o unos valores) debe ser especificada en el procedimiento almacenado para que este variable opcional funcione.
• Un punto y coma seguida por un número de grupo. Para procedimientos almacenados agrupados, se puede o ejecutar todos los procedimientos en el grupo simplemente especificando el nombre del procedimiento almacenado, o se puede incluir un número para indicar cual procedimiento almacenado se desea ejecutar. Por ejemplo, si se crean dos procedimientos almacenados llamados ProcAgrupado;1 y ProcAgrupado;2, se puede correr a ambos ingresando EXEC ProcAgrupado. O, se puede ejecutar el procedimiento 2 escribiendo EXEC ProcAgrupado;2. Si se definen parámetros en los procedimientos almacenados agrupados, cada nombre de parámetro debe ser único en el grupo.
• Variables para mantener los parámetros definidos en el procedimiento almacenado. Las variables son definidas con el uso de la palabra clave DECLARE antes de usar EXECUTE.

Ejecutar procedimiento almacenado cuando arranca SQL Server

Ya sea por razones de performance, administración, o cumplimiento de tareas, se puede indicar que al arrancar el SQL Server se ejecuten procedimientos almacenados. Se utiliza para ello el procedimiento almacenado sp_procoption. Este procedimiento acepta tres parámetros: @ProcName, @OptionName y @OptionValue. El comando siguiente configura un procedimiento almacenado llamado AutoStart para que arrnque automáticamente:

USE Master

GO

EXECUTE sp_procoption

@procname = autostart,

@optionname = startup,

@optionvlue = true

Sólo los procedimientos de dbo y ubicados en la base master pueden ser configurados para ejecutarse automáticamente. Para ejecutar al arrancarse el SQL Server a procedimientos ubicados en otras bases deberán ser llamados desde un procedimiento almacenado ubicado en la Master y configurado para arrancar automáticamente. Llamar a un procedimiento desde otro se denomina llamadas anidadas.

Se puede configurar a un procedimiento para que arranque automáticamente desde el Enterprise Manager. Se accede a la base Master, se hace clic en el nodo Stored Procedures. Se selecciona al procedimiento almacenado, y luego clic derecho. Se abre el cuadro de diálogo Properties (Propiedades) y se marca la casilla de selección Execute Whenever SQL Server Start (ejecutar siempre que el SQL Server arranca).

Para determinar si un procedimiento almacenado arranca automáticamente, se puede ejecutar la función OBJECTPROPERTY y controlar la propiedad ExecIsStartup. Por ejemplo el código siguiente invoca a la función para determinar si el procedimiento AutoStart está configurado para ejecutarse automáticamente:

USE Master

SELECT OBJECTPROPERTY(object_id(‘autostart’), ‘ExecIsStartup’)

Para deshabilitar procedimientos para que no continúen ejecutándose automáticamente, se puede correr el procedimiento almacenado sp_configure. El siguiente comando configura al SQL Server para que los procedimientos almacenados configurados para ejecutarse automáticamente no se arranquen la próxima vez que se encienda el SQL Server:

EXECUTE sp_configure

@configname = ‘scan for startup procs’, @configvalue = 0

RECONFIGURE

GO

Modificar procedimientos almacenados

Se puede usar el comando ALTER PROCEDURE (ALTER PROC en su versión abreviada),para modificar el contenido de un procedimiento almacenado de usuario, utilizando el Query Analyzer o una herramienta de comandos como osql. La sintaxis del comando ALTER PROCEDURE es casi idéntica a la del CREATE PROCEDURE.

La ventaje de usar el comando ALTER PROCEDURE  en vez de eliminar el procedimiento y crearlo de nuevo es que con este comando se retiene la mayoría de la propiedades configuradas para el procedimiento, tales como su ID. El conjunto de permisos, y su bandera de arranque. Para mantener la encriptación o la configuración de recompilación, se debe especificar con las palabras claves WITH ENCRYPTION o WITH RECOMPILE cuando se ejecuta ALTER PROCEDURE.

Se puede usar el Enterprise Manager o el Query Analyzer para modificarprocedimientos almacenados de usuario. En el Enterprise Manager, clic derecho sobre el procedimiento almacenado y luego clic en Properties. En el cuadro de diálogo Stored Procedure Properties, modifique los comandos del procedimiento que aparecen en la caja Text, y luego clic en OK. Usando el Query Analyzer, clic derecho en el procedimiento almacenado y clic en Edit. Después de modificar un procedimiento almacenado ejecútelo.

Para modificar el nombre de un procedimiento almacenado definido por el usuario, se usa el procedimiento almacenado sp_rename. El comando siguiente renombra el procedimiento almacenado ByRoyalty a RoyaltyByAuthorID:

USE PUBS

GO

EXECUTE sp_rename

@objname = ‘byroyalty’, @newname = ‘RoyaltyByAutorID’,

@objtype = ‘object’

Procedimientos almacenados definidos por el usuario pueden ser renombrados en el Enterprise Manager haciendo clic derecho en el nombre del procedimiento almacenado y clic en Rename (renombrar)

Se deberá tener cuidado cuando se renombre un procedimiento almacenado u otros objetos, como tablas. Los procedimientos almacenados pueden ser anidados. Si se produce una llamada a un objeto renombrado, el procedimiento que llama no podrá ubicar al objeto renombrado y generará un mensaje de error.

Eliminar procedimientos almacenados

Se puede usar el comando DROP PROCEDURE, o su versión abreviada DROP PROC, para eliminar un procedimiento almacenado definido por el usuario, varios procedimientos a la vez o un conjunto de procedimientos agrupados. El comando siguiente borra dos procedimientos en la base de datos Pubs: Procedure01 y Procedure02:

USE Pubs

GO

DROP PROCEDURE procedure01, procedure02

Fíjese que primero se configuró a Pubs como la base actual. No se puede especificar un nombre de base de datos cuando se elimina un procedimiento. El nombre totalmente calificado para eliminar procedimientos es [propietario].[nombre_procedimiento]. Si se ha creado un procedimiento almacenado del sistema definido por el usuario (con prefijo sp_), el comando DROP PROC buscará primero en la base actual y luego en la Master.

Para borrar un grupo de procedimientos almacenados, especifique el nombre del procedimiento. No se puede borrar parte de un grupo de procedimientos almacenados con el comando DROP PROCEDURE. Por ejemplo si se tiene un grupo de procedimientos llamados ProcAgrupados conteniendo ProcAgrupados;1 y ProcAgrupados;2 no se puede borrar ProcAgrupados;1 sin borrar ProcAgrupados;2. Si se necesita eliminar un procedimiento dentro de un grupo de procedimientos, se elimina el grupo y luego se lo recrea sin el procedimiento a eliminar.

Los cuidados indicados al final del punto anterior también son válidos para las operaciones de eliminación.

TEMA 15: Introducción a los procedimientos almacenados

Hasta ahora hemos utilizado el Query Analizer (o la herramienta on-line) para ejecutar comandos o grupos de comandos en el lenguaje Transact SQL. A los grupos de comandos los llamaremos scripts (guiones).

Cuando se ejecuta un script, los comandos son ejecutados por SQL Server para mostrar un conjunto de resultados, configurar un comportamiento y administrar al servidor o para manipular datos contenidos en una base de datos.

A estos scripts se les puede dar un nombre y guardar como procedimientos almacenados en el SQL Server. Posteriormente se los puede invocar de diversas maneras, tal como desde el Query Analizer, para que realicen el procesamiento de los comandos Transact SQL incorporados.

El SQL Server provee una serie de procedimientos almacenados del sistema, algunos de los cuales ya hemos utilizado en este Kit. Estos procedimientos son guardados en la base de datos Master y contienen comando Transact SQL que facilitan muchas tareas.

Propósito y ventajas de los procedimientos almacenados

Los procedimientos almacenados proporcionan ventajas de performance, un marco de trabajo, y mayores capacidades de seguridad. La mejora en el rendimiento se logra a través de un almacenamiento local (en la base de datos), código precompilado, y manejo de cachés  (almacenamientos temporarios). El marco de programación se logra a través de construcciones comunes de programación tales como parámetros de entrada/salida y reutilización de los procedimientos. Las capacidades de seguridad incluye encriptación y limitaciones de privilegios que permiten mantener a los usuarios fuera de la vista de la estructura de la base de datos subyacente, mientras se los habilita a ejecutar procedimientos almacenados  que actúan sobre la base de datos.

Rendimiento

Cada vez que un comando Transact-SQL, o conjunto de comandos, es enviado el servidor para su procesamiento, el servidor debe determinar si el remitente tiene suficientes privilegios para ejecutar esos comandos y si los comandos son válidos. Una vez que los permisos y la sintaxis de los comandos se han verificado, SQL Server construye un plan de ejecución para procesar el pedido.

Los procedimientos almacenados son más eficientes en parte porque el procedimiento es almacenado en el SQL Server cuando se crea. La sintaxis de los comandos contenidos en un procedimiento almacenado se comprueba que este libre de errores antes de ser guardado. El nombre del procedimiento almacenado se almacena en la tabla SysObjects, mientras que el texto del procedimiento se guarda en la tabla SysComments. Por otro lado, invocar al procedimiento almacenado implica ejecutar un solo comando en vez de cientos de comandos que un procedimiento almacenado podría contener.

La primera vez que se ejecuta el procedimiento, se crea un plan de ejecución y se compila al procedimiento almacenado. Los procesamientos subsecuentes del procedimiento almacenado son mucho más rápidos ya que el SQL Server no vuelve a controlar la sintaxis, ni recrea un plan de ejecución, ni se recompila el procedimiento. Por último se verifica el caché por si ya existe un plan de ejecución para ese procedimiento antes de generar un nuevo plan de ejecución.

La relativa pérdida de rendimiento producida por ubicar los planes de ejecución de los procedimientos almacenados en el caché de procedimiento se reduce ya que los planes de ejecución para todos los comandos SQL se guardan ahora en el caché de procedimientos. Por lo que un comando Transact-SQL tratará de utilizar un plan de ejecución ya existente en todos casos posibles.

Marco de programación

Una vez que se crea un procedimiento almacenado, puede ser llamado todas las veces que sea necesario. Esta capacidad provee modulación y habilita la reutilización del código. La reutilización del código mejora el mantenimiento de la base de datos al aislar la base de datos de los cambios en las prácticas del negocio. Si las reglas de negocios cambian en una organización, se puede modificar a los procedimientos almacenados para cumplir con las nuevas reglas de negocio. Todas las aplicaciones que llaman a esos procedimientos almacenados cumplirán con la nuevas reglas, sin tener que ser directamente modificados.

Tal y como otros lenguajes de programación, los procedimientos almacenados pueden aceptar parámetros de ingreso, retornar parámetros de salida, producir información de retroalimentación de la ejecución en la forma de códigos de estatus y texto descriptivo, y llamar a otros procedimientos. Por ejemplo, un procedimiento almacenado puede retornar un código de estatus a un procedimiento que lo llamó para que este procedimiento realice una operación según el código recibido.

Los desarrolladores de software pueden escribir sofisticados en un lenguaje como el C++; luego, se puede utilizar un tipo especial de procedimiento almacenado, denominado procedimiento almacenado extendido, para invocar al programa desde dentro del SQL Server.

Ante cualquier tarea simple, se debería escribir un procedimiento almacenado. Mientras más genérico sea el procedimiento más útil será para muchas bases de datos. Por ejemplo; el procedimiento almacenado sp_rename cambia el nombre de un objeto creado por el usuario, tal como una tabla, una columna o un tipo de datos definido por el usuario en la base de datos actual, pudiéndose aplicar a cualquier base de datos.

Seguridad

Otro capacidad importante de los procedimientos almacenados es que mejoran la seguridad a través de la encriptación y el aislamiento. Los usuarios de las bases de datos pueden tener permisos de ejecutar un procedimiento almacenado sin tenerlos para acceder directamente a los objetos de la bases de datos sobre las que opera el procedimiento almacenado. Además un procedimiento almacenado puede ser encriptado cuando se lo crea o modifica inhabilitando a los usuarios a leer los comandos Transact-SQL contenidos en el procedimiento almacenado. Estas capacidad de seguridad permiten aislar la estructura de la base de datos del usuario de la base de datos, con la consiguiente ganancia en seguridad.

Categorías de procedimientos almacenados

Existen cinco categorías de procedimientos almacenados: procedimientos almacenados del sistema, procedimientos almacenados locales, procedimientos almacenados temporarios, procedimientos almacenados extendidos y procedimientos almacenados remotos.

Procedimientos almacenados del sistema

Los procedimientos almacenados del sistema son guardados en la base de datos Master y son típicamente identificados por el prefijo sp_ . Ellos realizan una amplia variedad de tareas para soportar las funciones del SQL Server soportando: llamadas de aplicaciones externas para datos de las tablas del sistema, procedimientos generales para administración de las bases de datos, y funciones de administración de seguridad. Por ejemplo, se pueden ver los privilegios de una tabla usando el procedimiento almacenado de catálogo sp_table_privileges. El comando siguiente utiliza este procedimiento almacenado para mostrar los privilegios de la tabla stores en la base de datos Pubs:

USE PubsGO
EXECUTE sp_table_privileges Stores

Una tarea común de administración de bases de datos  es consultar la información acerca de los usuarios y procesos que se están ejecutando en una base de datos. Este paso es importante cuando se debe dar de baja la base de datos. El siguiente comando usa el procedimiento sp_who para mostrar todos los procesos en uso por el usuario LAB1\Administrator:

EXECUTE sp_who @loginame=’LAB1\administrator’

La seguridad de base de datos es crítica para la mayoría de las organizaciones que almacenan datos privados en su base de datos. El comando siguiente usa el procedimiento almacenado del sistema sp_validatelogins para mostrar cualquier usuario y grupo de usuarios Windows NT o Windows 2000 huérfanos que no existan más y que aún tengan entradas en las tablas del sistema del SQL Server:

EXECUTE sp_validatelogins

Hay cientos de procedimientos almacenados del sistema incluidos con el SQL Server. Para una lista completa de los procedimientos almacenados, consultar “System Store Procedures” en SQL Server Books Online.

Procedimientos almacenados locales

Los procedimientos almacenados locales son usualmente almacenados en una base de datos y están típicamente diseñados para completar tareas en la base de datos donde residen. Un procedimiento almacenado local se podría crear también para personalizar código de los procedimientos almacenados del sistema. Para crear una tarea personalizada basada sobre un procedimiento almacenado del sistema, primero copie el contenido del procedimiento almacenado del sistema y guarde el nuevo procedimiento almacenado y guarde el nuevo procedimiento almacenado como un procedimiento almacenado local.

Procedimientos almacenados temporarios

Un procedimiento almacenado temporario es similar a un procedimiento almacenado local, pero existe sólo hasta que se cierre la conexión que lo creó o se dé de baja el SQL Server, dependiendo del tipo de procedimiento almacenado. Estos procedimientos tienen una existencia volátil debido a que son creados son almacenados en la base de datos TempDB. TempDB se recrea cuando se reinicia el servidor; por lo tanto, todos los objetos dentro de la base de datos desaparecen después de la base de datos. Los procedimientos almacenados temporarios son muy útiles si Ud. está accediendo a versiones anteriores del SQL Server que no soportan la reutilización de los planes de ejecución y si no se quiere almacenar las tareas que serán ejecutadas con distintos parámetros.

Hay tres tipos de procedimiento almacenado temporarios: locales (también llamados privados), globales, y procedimientos almacenados en TempDB. Un procedimiento almacenado temporario local siempre comienza con #, un procedimiento almacenado temporario global siempre comienza con ##. Mas adelante se explicará como crear cada tipo de procedimiento almacenado temporario. El alcance de ejecución de un procedimiento almacenado local esta limitado a la conexión que lo creó. Todos los usuarios que tienen conexión a la base de datos, sin embargo, pueden ver el procedimiento almacenado en la ventana del Object Browser del Query Analizer. Dado que se encuentra limitado en su alcance, no existe la posibilidad de colisión de nombres con otras conexiones que crean procedimientos almacenados temporarios. Para asegurar unicidad, SQL Server  agrega al nombre de un procedimiento almacenado con una serie de caracteres _ y un número único de conexión. Los privilegios no pueden ser garantizado a otros usuarios para el procedimiento almacenado temporario. Cuando la conexión que creó el procedimiento almacenado temporario se cerró, el procediemiento se elimina de la TempDB.

Cualquier conexión para la base de datos puede ejecutar un procedimiento almacenado temporario global. Este tipo de procedimientos debe tener un nombre único , dado todas las conexiones pueden ejecutarlo y, cómo todos los procedimientos almacenados temporarios, este es creado en TempDB. El permiso para ejecutar procedimientos almacenados es automáticamente garantizado al rol público y no puede ser cambiado. Un procedimiento almacenado temporario global es casi tan volátil como los locales. Este tipo de procedimiento es removido cuando la conexión usada para crear el procedimiento se cierra y cualquier conexión que este ejecutando el procedimiento almacenado es completada.

Los procedimientos almacenados temporarios creados directamente en la TempDB son diferentes a los procedimientos almacenados locales y globales en lo siguiente:

·        Se pueden configurar permisos para ellos.

·        Existen aún después que la conexión que los creó se terminan

·        No son removidos hasta que el SQL Server  no sea apagado.

Ya que este tipo de procedimiento almacenado se crea directamente en la TempDB, es importante calificar completamente los objetos de base de datos referenciados por comandos Transact-SQL en el código. Por ejemplo, se debe referenciar la tabla Authors, la cual es propiedad de dbo en la base de datos Pubs, como pubs.dbo.authors.

Procedimientos almacenados extendidos

Un procedimiento almacenado extendido usa un programa externo, compilado como una DLL, librería de vínculos dinámicos (dynamic link library) para expandir las capacidades de un procedimiento almacenado. Un número de procedimiento almacenado del sistema son también calificados como procedimientos almacenados extendidos. Por ejemplo, el programa xp_sendmail, el cual envía un mensaje y un archivo de conjunto de resultados de una consulta adjunto a una dirección específica de email, es tanto un procedimiento almacenado de sistema como un procedimiento almacenado extendido. La mayoría de los procedimientos almacenados extendidos usan el prefijo xp_ como un convención de nombre. Sin embargo, hay algunos procedimientos almacenados extendidos que comienzan con el prefijo sp_, y hay algunos procedimientos almacenados del sistema que no son procedimientos extendidos y usan el prefijo xp_. Por lo tanto, no se puede depender sobre convención de nombres para identificar procedimientos almacenados del sistema y procedimientos almacenados extendidos.

Se puede usar la función OBJECTPROPERTY para determinar si un procedimiento almacenado es extendido o no. OBJECTPROPERTY retorna un valor de 1 si es un procedimiento extendido (IsExtendedProc), indicando un procedimiento almacenado extendido, o retorna un valor de 0, indicando que no es un procedimiento extendido. El siguiente ejemplo demuestra que el sp_prepare es un procedimiento almacenado extendido y que xp_logininfo no es un procedimiento almacenado extendido:

USE Master

·        un procedimiento almacenado extendido que usa el prefijo sp_ SELECT OBJECTPROPERTY(object_id(‘sp_prepare’), ‘IsExtendedProc’)

 Este ejemplo retorna un valor de 1

USE Master

·        un procedimiento almacenado que no es extendido y usa el prefijo xp_ SELECT OBJECTPROPERTY(object_id(‘xp_logininfo’), ‘IsExtendedProc’)

 Este ejemplo retorna un valor de 0.