Unidad 9. Ejercicio paso a paso: Procedimientos


Objetivo

Realizar procedimientos en que utilizaremos estructuras de control de flujo como BEGIN... END, IF y GO. Además, se hará un repaso a lo que ya hemos aprendido a lo largo del curso, puesto que los procedimientos, al fin y al cabo sirven para ejecutar sentencias SQL. Repasaremos cómo crear tablas e insertar datos en ellas.

Ejercicio paso a paso

Utilizar las sentencias desarrolladas en un ejercicio propuesto del tema anterior para crear la base de datos Gestion2  a partir de Gestion8 y crear otra base datos Gestion10 sobre la que realizarás todos los ejercicios de esta unidad.

Para ello vamos a definir tres procedimientos, CreaBase que permita crear una base de datos con el nombre que le indiquemos en la llamada, otro, BorrarBase que borre una determinada base de datos, y por último RellenaBase para rellenar con las tablas de Gestion8 la base de datos que indiquemos en la llamada.
Los procedimientos se crean en Gestion8.
Como no se puede utilizar una variable en un CREATE DATABASE, utilizamos la función EXEC('cadena SQL') que ejecuta cualquier cadena SQL.

PRINT 'Empieza el  ejercicio de procedimientos' -- Para visualizar un mensaje que indique lo que realiza esta sentencia.

USE Gestion8                  -- Para crear los proc.  en Gestion8     

if object_id('BorraBase') IS NOT NULL DROP PROC BorraBase

GO                        -- Necesario igual  que el siguiente GO para delimitar el CREATE PROC

CREATE PROCEDURE BorraBase @base sysname

AS

BEGIN TRY     -- Para que si  no existe la base de datos no mande ningún mensaje

EXEC('DROP DATABASE ' +  @base) -- Si en @base  se pasa el valor Gestion10,  se  ejecutará: DROP DATABASE Gestion10

      END TRY

      BEGIN CATCH -- Si se produce un  error no hará nada 

      END CATCH   -- no mandará  ningún mensaje de error y seguirá

GO

if object_id('CreaBase') IS NOT NULL DROP PROC CreaBase

GO

CREATE PROCEDURE CreaBase @base sysname

AS

      BEGIN TRY   -- Para que si ya  existe la base de datos la no mande ningún mensaje

            EXEC('CREATE DATABASE '+  @base) -- Creamos la  bd con los parámetros por defecto para simplificar

      END TRY

      BEGIN CATCH

      END CATCH   -- Si se produce  un error no hará nada (por ej. si ya existe la base no mandará ningún mensaje de error y seguirá

GO

if object_id('RellenaGestion') IS NOT NULL DROP PROC RellenaGestion

GO

CREATE PROC RellenaGestion @base sysname

AS

BEGIN

      EXEC('CREATE TABLE '+ @base+'.dbo.empleados(

            numemp      INT NOT NULL PRIMARY KEY CLUSTERED,

            nombre      CHAR(20) NULL,

            edad  INT NULL,

            oficina INT NULL,

            titulo      CHAR(20)  NULL,

            contrato  datetime NULL,

            jefe  INT NULL,

            cuota       MONEY NULL,

            ventas      MONEY NULL

      ) 

      CREATE TABLE '+@base+'.dbo.oficinas(

            oficina  INT NOT NULL,

            ciudad   CHAR(20)  NULL,

            region   CHAR(20) NULL,

            dir INT  NULL,

            objetivo MONEY NULL,

            ventas   MONEY NULL CONSTRAINT d1  DEFAULT (0),

            CONSTRAINT  PK_oficinas PRIMARY KEY (oficina),

      ) 

      CREATE TABLE '+@base+'.dbo.clientes(

            numclie INT NOT NULL,

            nombre      CHAR(20) NULL,

            repclie INT NULL,

            limitecredito INT NULL,

            CONSTRAINT PK_clientes PRIMARY KEY  (numclie)  

      ) 

      CREATE TABLE '+@base+'.dbo.productos(

            idfab           CHAR(5) NOT NULL,

            idproducto  CHAR(10) NOT NULL,

            descripcion CHAR(20) NULL,

            precio          MONEY NULL,

            existencias  INT NULL,

            CONSTRAINT  PK_productos PRIMARY KEY (idfab,idproducto)

      ) 

      CREATE TABLE '+@base+'.dbo.pedidos(

            codigo          INT IDENTITY NOT NULL,

            numpedido   DEC(10,0) NOT NULL,

            fechapedido DATETIME NOT NULL,

            clie      INT NOT NULL,

            rep       INT NOT NULL,

            fab       CHAR(5) NOT NULL,

            producto    CHAR(10) NOT NULL,

            cant      SMALLINT NULL,

            importe     MONEY NULL,

            CONSTRAINT PK_pedidos PRIMARY KEY  (codigo),

      ) 

      INSERT INTO '+@base+'.dbo.Productos  SELECT * FROM Gestion8.dbo.Productos;

      INSERT INTO '+@base+'.dbo.Empleados  SELECT * FROM Gestion8.dbo.Empleados;

      INSERT INTO '+@base+'.dbo.Clientes  SELECT * FROM Gestion8.dbo.Clientes;

      INSERT INTO '+@base+'.dbo.Oficinas  SELECT * FROM Gestion8.dbo.Oficinas;

      SET IDENTITY_INSERT '+@base+'.dbo.Pedidos ON;

      INSERT INTO '+@base+'.dbo.Pedidos (codigo,numpedido,fechapedido,clie,rep,fab,producto,cant,importe)

            SELECT * FROM  Gestion8.dbo.Pedidos;

      SET IDENTITY_INSERT '+@base+'.dbo.Pedidos OFF;

      ALTER TABLE '+@base+'.dbo.Empleados 

            ADD CONSTRAINT FK_empleados_jefe  FOREIGN KEY (jefe) REFERENCES empleados,

                  CONSTRAINT  FK_empleados_oficinas FOREIGN KEY (oficina) REFERENCES oficinas;

      ALTER TABLE '+@base+'.dbo.Oficinas

            ADD CONSTRAINT FK_oficinas_dir  FOREIGN KEY (dir) REFERENCES empleados

      ALTER TABLE '+@base+'.dbo.Clientes

            ADD CONSTRAINT FK_clientes_repclie  FOREIGN KEY (repclie) REFERENCES empleados;

      ALTER TABLE '+@base+'.dbo.Pedidos

            ADD CONSTRAINT FK_pedidos_rep  FOREIGN KEY (rep) REFERENCES empleados,

                CONSTRAINT FK_pedidos_clie FOREIGN KEY  (clie) REFERENCES clientes, 

                  CONSTRAINT  FK_pedidos_productos FOREIGN KEY (fab,producto) REFERENCES productos;')

END



GO

Empezar:

USE Gestion8

EXEC BorraBase 'Gestion10'

GO

EXEC CreaBase 'Gestion10'

GO

EXEC RellenaGestion 'Gestion10'

   Inicio    



.