Rutinas, ejercicio 8.

8.   Simular un autoincrementado. Rutina para asignar un IdPedido automáticamente a cada nuevo Pedido que no tenga un valor explícito para el campo IdPedido. El valor que se le debe asignar al IdPedido debe ser el entero inmediatamente superior al mayor valor de dicho campo en la tabla Pedidos.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` TRIGGER Before_Insert_Pedidos

BEFORE INSERT ON Pedidos

FOR EACH ROW

BEGIN

         DECLARE v_maxIdPedido INT;

         IF new.IdPedido IS NULL OR new.IdPedido=0 THEN

                     SELECT MAX(IdPedido) INTO v_maxIdPedido FROM Pedidos;

                    

                     SET new.IdPedido = v_maxIdPedido + 1;

         END IF;

END $$

DELIMITER ;

Tabla de referencia:

Rutinas, ejercicio 7.

7.   Rutina para actualizar automáticamente el IdPedido de la tabla DetallesPedidos en el caso de que se modifique el IdPedido de algún registro de la tabla Pedidos.

use Neptuno;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` TRIGGER After_Update_Pedidos

AFTER UPDATE ON Pedidos

FOR EACH ROW

BEGIN

         IF old.IdPedido != new.IdPedido THEN

                     UPDATE DetallesPedidos SET IdPedido = new.IdPedido

                                 WHERE IdPedido = old.IdPedido;

         END IF;

END $$

DELIMITER ;

Tabla de referencia:

Rutinas, ejercicio 6.

6. Rutina que calcula el valor de la comisión que le corresponde al empleado que ha gestionado un pedido concreto.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `calculaComisionPorPedido`(v_idPedido INT) RETURNS float

BEGIN

         DECLARE v_idEmpleado, v_comision INT;

         DECLARE v_comisionPorPedido, v_totalPedido FLOAT;

         -- Seleccionamos el idEmpleado de ese pedido

         SELECT IdEmpleado INTO v_idEmpleado FROM Pedidos

         WHERE IdPedido = v_idPedido;

         -- Seleccionamos el porcentaje de comisión asociada a ese empleado

         SELECT Comision INTO v_comision FROM Empleados

         WHERE IdEmpleado = v_idEmpleado;

         -- Calculamos la cuantía total del pedido

         SELECT totalPedido(v_idPedido) INTO v_totalPedido;

         -- Calculamos cuánto supone el porcentaje de comisión de esa cuantía

         SELECT v_totalPedido * v_comision / 100 INTO v_comisionPorPedido;

RETURN v_comisionPorPedido;

END

$$

DELIMITER ;

Tabla de referencia:

Rutinas, ejercicio 5.

5.     Crea una rutina que elimine, de la tabla “acumulado”, los datos correspondientes al año en el que se ejecuta la rutina y, posteriormente, inserte en dicha tabla los puntos acumulados en ese año por cada uno de los tipos de sanción.

Para ello, utiliza un cursor que recorra los registros de “tipoSancion” y, por cada uno de ellos, ejecute una consulta con la que se inserte en la tabla “acumulado” los puntos acumulados por dicho tipo de sanción: 

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `puntosPorTipoSancion`()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE v_idTipoSancion, sumaPuntos INT;

  DECLARE cur CURSOR FOR SELECT idtipoSancion FROM tipoSancion;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

         DELETE FROM acumulado WHERE anyo = YEAR(CURRENT_DATE);

  OPEN cur;

  read_loop: LOOP

    FETCH cur INTO v_idTipoSancion;

    IF done THEN

      LEAVE read_loop;

    END IF;

         SELECT SUM(puntos) INTO sumaPuntos FROM sancion

         WHERE idtipoSancion = v_idTipoSancion AND YEAR(fecha) = YEAR(CURRENT_DATE);

         INSERT INTO acumulado VALUES (YEAR(CURRENT_DATE), v_idTipoSancion, sumaPuntos);

  END LOOP;

  CLOSE cur;

END

Tabla de referencia:

Rutinas, ejercicio 4.

4.     Crea un script que ejecute el procedimiento anterior el día 1 de enero de cada año.

CREATE EVENT   regalarPuntosAnyo

    ON SCHEDULE EVERY 1 YEAR

         STARTS '2014-01-01 00:00:01'

    DO CALL regalarPunto;

Podéis comprobar la creación correcta del evento con SHOW EVENTS

Tabla de referencia:

Rutinas, ejercicio 3.

3.   Crea un script en el que se le otorgue un punto extra a aquellos conductores que no han tenido ninguna sanción en el año anterior al que se ejecuta el script.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `regalarPunto`()

BEGIN

          UPDATE conductor SET puntos = puntos + 1

         WHERE dni NOT IN (

                     SELECT DISTINCT conductor_dni FROM vehiculo,sancion

                     WHERE vehiculo.matricula = vehiculo_matricula AND

                     fecha BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR) AND CURRENT_DATE

         );

END $$

DELIMITER $$

Resultado: La llamada a la rutina debe indicar que se actualizan 78 filas

Tabla de referencia:

Rutinas, ejercicio 2.

2.     Crea un script para que, cada vez que se inserta una sanción, se le reste automáticamente, al conductor del vehículo denunciado, los puntos correspondientes a dicha sanción.

USE dgt;

DELIMITER $$

CREATE TRIGGER afterInsertSancion AFTER INSERT

    ON sancion FOR EACH ROW

BEGIN

         DECLARE v_dni CHAR(9);

         SELECT conductor_dni INTO v_dni FROM vehiculo WHERE matricula = new.vehiculo_matricula;

          UPDATE conductor SET puntos = puntos - new.puntos

         WHERE dni = v_dni;

   

END$$

DELIMITER ;

Resultado:

INSERT INTO `dgt`.`sancion` (`vehiculo_matricula`, `puntos`, `idtipoSancion`) VALUES ('7592HFT', 2, 3);

Debe hacer que el conductor con DNI '56953022N' pase de tener 12 puntos a tener 10

Tabla de referencia:

Rutinas, ejercicio 1.

1.     Crea un script al que le enviamos un DNI de un conductor y nos devuelve la suma de puntos que ha perdido debido a sanciones: 

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `totalPuntosConductor`(v_dni char(10)) RETURNS int(11)

BEGIN

         DECLARE v_puntos INT;

         SELECT SUM(puntos) INTO v_puntos FROM sancion, vehiculo

         WHERE vehiculo_matricula = matricula AND

         conductor_dni = v_dni;

         RETURN v_puntos;

END $$

DELIMITER;

Resultado:

La llamada a la rutina con el dni '95270924K' debe devolver un valor de 5

Tabla de referencia:

Rutinas almacenadas para la administración de mysql (CURSORES).

Los CURSOR o "cursores", almacenan los registros devueltos por una sentencia sql y recorren su contenido.

Ejemplo de sintaxis de un cursor:

CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE a CHAR(16);

  DECLARE cur CURSOR FOR SELECT i FROM tablaPrueba;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP

    FETCH cur INTO a;

    IF done THEN

      LEAVE read_loop;

    END IF;

       ….

  END LOOP;

  CLOSE cur;

END;

Explicación traducida:

CREAR PROCEDURE demo_cursor()

COMENZAR

  DECLARAR hecho ENTERO FALSO POR DEFECTO;

  DECLARAR a CARACTERES(16);

  DECLARAR cursor CURSOR PARA SELECCIONAR i DESDE tablaPrueba;

  DECLARAR CONTINUAR CONTROLADOR PARA SET NO ENCONTRADO hecho = VERDADERO;

  ABRIR cursor;

  leer_bucle: BUCLE

    BUSQUEDA cursor DENTRO DE a;

    SI hecho ENTONCES

      DEJAR leer_bucle;

    FIN DEL IF;

       ….

  FIN DEL BUCLE ;

  CIERRE DEL cursor;

FIN;

Las partes escritas en mayúsculas, deben ir así siempre al realizar la sentencia correctamente. 

Rutinas almacenadas para la administración de mysql (EVENTOS)

Los EVENT, "eventos" o disparadores temporales, son rutinas asociadas a un esquema determinado y las cuales son ejecutadas con los permisos del propio creador del evento.

Se ejecutan para intervalos de tiempo con el comando EVERY, y para un instante con el comando AT.

Los eventos están disponibles en la versión 5.1 de Mysql.

Ejemplo sintaxis:

CREATE EVENT   event_name

    ON SCHEDULE schedule

    DO event_body;

schedule:

    AT timestamp [+ INTERVAL interval] ...

  | EVERY interval

    [STARTS timestamp [+ INTERVAL interval] ...]

    [ENDS timestamp [+ INTERVAL interval] ...]

interval:

    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Explicación traducida:

CREAR EVENTO   nombre_evento

    EN HORARIO horario

    REALIZAR cuerpo_evento;

horario:

    EN timestamp [+ INTERVALO intervalo] ...

  | CADA intervalo

    [COMIENZA timestamp [+ INTERVALO intervalo] ...]

    [FINALIZA timestamp [+ INTERVALO intervalo] ...]

intervalo:

    cantidad {AÑO | Cuartos | mes | día | Hora | MINUTO |

               SEMANA | SEGUNDO | MES AÑO | HORAS DEL DÍA | MINUTO día |

               SEGUNDO DIA | MINUTO HORA | SEGUNDA HORA | MINUTO SEGUNDO}

Las partes escritas en mayúsculas, deben ir así siempre al realizar la sentencia correctamente.

Rutinas almacenadas para la administración de mysql (TRIGGER).

Los trigger o "disparadores", son procedimientos que no usan sql dinámico, no devuelven ningún valor.

Los trigger están asociados a eventos de tablas del tipo insert, delete y update, y dependen del evento NEW y OLD.

Ejemplo de sintaxis de un trigger:

CREATE TRIGGER trigger_name trigger_time trigger_event

    ON tbl_name FOR EACH ROW trigger_body

Explicación traducida:

CREAR DISPARADOR nombre_disparador tiempo_disparador evento_disparador

    EN nombre_tabla PARA CADA FILA cuerpo_disparador

Las partes escritas en mayúsculas, deben ir así siempre al realizar la sentencia correctamente. 

Rutinas almacenadas para la administración de mysql (FUNCTION).

Las function o "funciones", son procedimientos que no usan sql dinámico, devuelven un valor de tipo dado, pero no un conjunto de estos.

Solo aceptan parámetros de entrada.

Ejemplo de sintaxis de una función:

CREATE

    [DEFINER = { user | CURRENT_USER }]

    FUNCTION sp_name ([func_parameter[,...]])

    RETURNS type

    routine_body

func_parameter:

    param_name type

type:

    Any valid MySQL data type

routine_body:

    Valid SQL routine statement

Explicación traducida:

CREAR

    [DEFINER = { usuario | USUARIO_ACTUAL }]

    FUNCIÓN nombre_sp ([parametro_función[,...]])

    DEVOLUCIONES tipo

    cuerpo_rutina

parametro_función:

  nombre tipo_parametro

tipo:

    Cualquier tipo de datos MySQL válida

cuerpo_rutina:

    Declaración de rutina de SQL válido

Las partes escritas en mayúsculas, deben ir así siempre al realizar la sentencia correctamente. 

Rutinas almacenadas para la administración de mysql (PROCEDURE).

Los procedures o procedimientos, son rutinas que tienen la ventaja de poder utilizar sql dinámico.

Los procedures, utilizan parámetros de entrada, salida o entrada-salida y devuelven uno o varios datos.

Para invocar un procedure usamos el comando call.

Ejemplo de sintaxis de un procedure:

CREATE

    PROCEDURE sp_name ([proc_parameter[,...]])

    routine_body

proc_parameter:

    [ IN | OUT | INOUT ] param_name type

type:

    Any valid MySQL data type

routine_body:

    Valid SQL routine statement

Explicación traducida:

CREAR

    PROCEDIMIENTO sp_name ([parametro_procedimiento[,...]])

    cuerpo_rutina

parámetros_procedimiento:

    [ ENTRADA | SALIDA |  ENTRADA/SALIDA] parametro_nombre tipo

tipo:

    Cualquier tipo de datos MySQL válida

cuerpo_rutina:

   Declaración de rutina de SQL válido


Las partes escritas en mayúsculas, deben ir así siempre al realizar la sentencia correctamente.

Capítulo 3

        

COMPONENTES  BÁSICOS

Un edificio inteligente está dotado de un sistema de control que pretende optimizar de forma integrada ciertas funciones inherentes a la operatividad, administración y mantenimiento del edificio. Para conseguir esta finalidad, el sistema de control necesita comunicarse con el entorno y es necesario un conjunto de sensores que le suministren información, una serie de actuadores que ejecuten sus acciones de control, así como una infraestructura de comunicaciones que los conecte entre sí, y las interfaces y acondicionadores de señal que adapten la señal entre el controlador y los sensores y actuadores. En dicho sistema de control, la elección del hardware será importante, pero también lo es el diseño adecuado del software de control.

Visualizacion de un temporizador software

/* Visualizacion de un temporizador software. */

#include<stdio.h>

#define RETAR 12800000

struct mi_hora {

      int horas;

      int minutos;

      int segundos;

};

/*************************************/

/* Prototipo de funciones ************/

/*************************************/

void mostrar(struct mi_hora *t);

void actualizar(struct mi_hora *t);

void retardo(void);

/*************************************/

int main(void)

{

      struct mi_hora hora;

     

      hora.horas = 0;

      hora.minutos = 0;

      hora.segundos = 0;

     

      for(;;)

      {

            actualizar(&hora);

            mostrar(&hora);

      }// fin de for

           

      system("PAUSE");

      return 0; // terminacion exitosa

     

}// fin de main

void actualizar(struct mi_hora *t)

{

      t-> segundos++;

      if(t-> segundos == 60)

      {

            t-> segundos = 0;

            t-> minutos++;

      }

     

      if(t-> minutos == 60)

      {

            t-> minutos = 0;

            t-> horas++;

      }

     

      if(t-> horas == 24)

            t-> horas = 0;

           

      retardo();

}// fin actualizar

void mostrar(struct mi_hora *t)

{

      printf("%02d:", t-> horas);

      printf("%02d:", t-> minutos);

      printf("%02d:", t-> segundos);

      printf("\n");

           

}// fin de mostrar

void retardo(void)

{

      long int t;

     

      /* cambiese esto segun sea necesario */

      for(t = 1; t < RETAR ; ++t);

}// fin de retardo

Programa de lista de correos (arrays de estructuras)

/*Un sencillo programa de lista de correos que ilustra el uso de

arrays de estructuras. */

#include<stdio.h>

#include<stdlib.h>

#define MAX 100

struct dir {

      char nombre[30];

      char calle[40];

      char ciudad[20];

      char provincia[3];

      unsigned long int codigo;

} lista_dir[MAX];

/* Prototipo de funciones **************/

void inic_lista(void), intro(void);

void borrar(void), listar(void);   

int menu(void), busca_libre(void); 

/***************************************/

int main(void)

{

      char opcion;

     

      inic_lista();     // inicializar el array de estructuras

     

      for(;;)

      {

            opcion = menu();

            switch(opcion)

            {

                  case 1: intro();

                        break;

                  case 2: borrar();

                        break;

                  case 3: listar();

                        break;

                  case 4:     exit(0);

            } //fin de switch

      } //fin de for

     

      system("PAUSE");

      return 0; //terminacion exitosa

}// fin de main

/****************************************************************/

/* Inicializar la lista. ****************************************/

/****************************************************************/

void inic_lista(void)

{

      register int t;

     

      for(t = 0; t < MAX; ++t)     lista_dir[t].nombre[0] = '\0';

}

/****************************************************************/

/*************************************************/

/* Seleccionar una operacion. ********************/

/*************************************************/

int menu(void)

{

      char s[80];

      int c;

     

      printf("1. Introducir un nombre\n");

      printf("2. Borrar un nombre\n");

      printf("3. Listar el archivo\n");

      printf("4. Salir\n");

     

      do

      {

            printf("\nIntroduce tu opcion:  ");

            gets(s);

            c = atoi(s);

      }while(c < 0 || c > 4);  //fin de do_while

     

      return c;

} //fin de menu

/***************************************************/

/**************************************/

/* Introducir direcciones en la lista */

/**************************************/

void intro(void)

{

      int sitio;

      char s[80];

     

      sitio = busca_libre();

     

      if(sitio == -1)

      {

            printf("\nLista llena");

            return;

      }

     

      printf("Introduce el nombre: ");

      gets(lista_dir[sitio].nombre);

     

      printf("Introduce la calle: ");

      gets(lista_dir[sitio].calle);

     

      printf("Introduce la ciudad: ");

      gets(lista_dir[sitio].ciudad);

     

      printf("Introduce las siglas de la provincia:  ");

      gets(lista_dir[sitio].provincia);

     

      printf("Introduzca el codigo postal:  ");

      gets(s);

      lista_dir[sitio].codigo = strtoul(s, '\0', 10);

     

} // fin de intro

/*********************************************************/

/************************************/

/* Buscar una estructura no usada ***/

/************************************/

int busca_libre(void)

{

      register int t;

     

      for( t = 0; lista_dir[t].nombre[0] && t < MAX; ++t);

     

      if(t == MAX) return -1; // no hay sitio libre

      return t;

     

} // fin de busca_libre

/*********************************************************/

/*****************************************************/

/* Eliminar una direccion ****************************/

/*****************************************************/

void borrar(void)

{

      register int sitio;

      char s[80];

     

      printf("Introduce el numero de registro:  ");

      gets(s);

      sitio = atoi(s);

     

      if(sitio >= 0 && sitio < MAX)

            lista_dir[sitio].nombre[0] = '\0';

     

} // fin de borrar

/*****************************************************/

/*****************************************************/

/*  Mostrar la lista en la pantalla ******************/

/*****************************************************/

void listar(void)

{

      register int t;

     

      for(t = 0; t < MAX; ++t)

      {

            if(lista_dir[t].nombre[0])

            {

                  printf("%s\n", lista_dir[t].nombre);

                  printf("%s\n", lista_dir[t].calle);

                  printf("%s\n", lista_dir[t].ciudad);

                  printf("%s\n", lista_dir[t].provincia);

                  printf("%lu\n\n", lista_dir[t].codigo);

           

            }//fin de if

      }//fin de for

      printf("\n\n");

}//fin de listar

/******************************************************/