material del curso(mysql)

7/23/2019 Material Del Curso(Mysql)

1/68

Base de Datos para WEB (MySQL) 2013

Isc. Rubn Torres Fras

INTRODUCCION

Una base de datos relacional es un conjunto ordenado de datos, los cuales normalmente estn almacenados enuno o ms ficheros. Los datos estn estructurados en tablas, y estas pueden tener referencias cruzadas. Laexistencia de estas referencias o relaciones es lo que da el nombre de relacional a este tipo de bases de datos.

ARQUITECTURA CLIENTE-SERVIDOR

Cliente-Servidor:este paradigma es un modelo general de interaccin entre procesos software donde los procesosque interactan estn divididos en: Clientes (requieren servicios), y Servidores (ofrecen los servicios).El proceso cliente desempea un papel activo (de manera autnoma genera peticiones de servicios), mientras queel proceso servidores reactivo (lleva a cabo una accin slo como resultado de una peticin de un cliente).

Arquitectura de dos capas (Two-TierArchitecture) :El cliente es a la vez el interfaz de usuario y el gestor de la aplicacin.

Arquitectura de tres capas (Three-TierArchitecture) : Incorpora un segundo servidor llamado servidor deaplicaciones, responsable de gestionar la lgica de aplicacin comn a muchos clientes.

El cliente slo es responsable del interfaz con el usuario final (que puede ser implementado usando navegadoresweb)


2/68



QUE SE BUSCA AL INTEGRAR LAS TECNOLOGIAS WEB CON LOS SGBD:

Poder acceder a datos corporativos de forma segura. Disponer de conectividad independiente de los datos y el vendedor, para dar libertad a la hora de elegir

SGBD.

Poder interactuar con la BD de forma independiente a cualquier navegador o servidor web. Una arquitectura abierta que permita la interoperabilidad con diferentes sistemas y tecnologas: servidores

web, Java, etc. Conseguir la escalabilidad, crecimiento y cambios a costes aceptables. Soportar transacciones que abarcan mltiples peticiones HTTP. Soportar autenticacin basada en sesiones y usuarios. Rendimiento aceptable.

MYSQL

MySQL es un sistema de bases de datos relacional. A pesar de su popularidad, este sistema todava no tiene

algunas de las caractersticas que poseen otros sistemas comerciales. Sin embargo, estas diferencias son cada vezms pequeas. Algunas de las compaias que usan MySQL son Yahoo!, BBC News, CNET, Nokia, YouTube, Flickr,Google, ..

CARACTERISTICAS

Sistema de base de datos relacionales

Arquitectura cliente/servidor

Soporta Subconsultas, Vistas, Procedimientos Almacenados, Triggers

Manejo de campos Full-text search, dicha caracterstica acelera y simplifica la bsqueda de palabras que se

encuentran almacenadas en campos de tipo texto

Replicacin. Permite copiar el contenido de una base de datos a mltiples ordenadores. Esta caracterstica se

usa para mejorar la proteccin contra fallos, y parta acelerar las consultas.

Transacciones. Una transaccin es un conjunto de operaciones sobre una base de datos que funcionan como

un bloque. Es sistema asegura que o bien se han ejecutado todas las operaciones, o ninguna. Esto es vlido

incluso si hay una cada del sistema, un fallo elctrico, o cualquier otro desastre.

Independiente de plataforma. El servidor MySQL puede funcionar sobre una variedad de sistemas operativos

como Mac OS X, Linux, Microsoft Windows, y todas las variastes Unix (AIX, BSDI, FreeBSD, HP-UX, OpenBSD,

SGI, Sun Solaris)

LIMITACIONES

Cuando MySQL usa las tablas del tipo MyISAM, el sistema de bloqueo (locking) de datos solo funciona para

tablas enteras. Eso significa que si queremos modificar una tabla y no queremos que nadie ms puedainterferir en la operacin, la nica maneras es bloquear totalmente el acceso a la tabla entera. Este problema

se puede resolver usando tablas del tipo InnoDB que soportan bloqueo por registro.

MySQL no permite aadir tipos de datos definidos por el usuario.

MySQL no ofrece funcionalidad para aplicaciones en tiempo real.

El sistema de triggers an no est maduro.


3/68



ARQUITECTURA DE MYSQL

TIPOS DE INSTRUCCIONES

Data Definition Language (DDL):son las instrucciones que sirven para disear la base de datos: CREATE TABLE,ALTER TABLE, ...

Data Manipulation Language (DML):SELECT, INSERT, UPDATE y DELETE, y varias instrucciones ms sirven para leerdatos de las tablas, y para almacenar y modificarlos. Son la parte central del lenguaje.

Data Control Language (DCL): son las instrucciones usadas para definir los mecanismos de seguridad de las base dedatos: GRANT, REVOKE.


4/68



LENGUAJE DE DEFINICION DE DATOS:

Antes de revisar las instrucciones para definir los datos, revisaremos los tipos de datos que maneja MySQL

TIPOS DE DATOS

CONSIDERACIONES

Cuando utiliza el tipo de dato entero y si desea utilizar un entero de cuatro dgitos se declara de la siguientemanera int(4), el cual indica el ancho de los valores de la columna. Ahora bien cuando se usa ZEROFILLel relleno deespacios se reemplaza por ceros

Int(5) zerofill -> Valor 4 -> 00004

Int maneja un atributo opcional UNSIGNED para valores positivos

La definicin de Floaty Doublees la siguiente Tipo de Dato(Valor a mostrar, Precisin )Decimaly Numericse usan para guardar una precisin exacta, ejemplo datos monetarios.

Ejemplo salario decimal(5,2), El rango va desde -999.99 a 999.99

TIPOS NUMERICOS

Tipo Bytes Valor Minimo Valor Maximo

Tinyint 1 -128 127

0 255

Smallint 2 -32768 32767

0 65535

Mediumint 3 -8388608 8388608

0 16777215

Int 4 -2147483648 2147483648

0 4294967295

Bigint 8 -9223372036854775808

9223372036854775808

0 18446744073709551615

Float 4 .......Double 8


5/68



Tipos de Fecha DateTime: Usado para valores que contienen informacion con fecha y hora

Formato YYYY-MM-DD HH:MM:SS Rango 1000-01-01 00:00:00 a 9999-12-31 23:59:59

Date: Se usa cuando necesita valores que contienen informacion de fecha.

Formato YYYY-MM-DD Rango 1000-01-01 a 9999-12-31

Se puede usar una sintaxis relajada '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', '98@12@31 11^30^45'

Como cadena sin delimitadores YYYYMMDDHHMMSS o YYMMDDHHMMSS Ejemplo: 19970523091528 & 970523091528 = 1997-05-23 09:15:28

Los valores ilegales de Datetime y Date se convierten a valor cero Si a datetime se le asigna un valordatela parte de la hora se pone en 0s 00:00:00

Si a datese le asigna un valor datetimela parte de la hora se borra El formato relajado puede ser problematico

Ejemplo: 10:11:12 puede parecer hora por el delimitador : pero si se usa en un contexto

de fecha se interpreta como 2010-11-12 10:45:15 se convierte en 0000-00-00 ya que 45 no es un mes legal.

Los rangos para los elementos de las fechas son las siguiente: Ao => 10009999 Mes => 0012 Dia => 0031

Cualquier fecha fuera de esos rangos se convierte en 0000:00:00 Fechas de digitos con dos digitos son ambiguas pues no se conoce el siglo

Para 0069 se convierten a 2000 - 2069 Para 7099 se convierten a 1970 - 1999

Time: Representa una hora del dia < 24 o el tiempo transcurrido entre un periodo Ejemplo la hora de entrada a trabajar de un empleado Formato: HH:MM:SS ejemplo 101112 se traduce en 10:11:12 pero 109712 es ilegal puesto que no

tiene un valor correcto para los minutos y pasa a ser 00:00:00 El Rango para este tipo de dato es: -838:59:59 a 838:59:59 Usando una constante CURRENT_TIMEobtenemos el tiempo en el cundo fue ingresado el dato. Para valores menores a 10, 8:3:2 tenemos una equivalencia con 08:03:02 El valor 1112 representa 00:11:12 almacenado en la base de datos El valor 1112representa 11:12:00 almacenado en la base de datos

YEAR: Tipo de dato de un byte usado para representar aos El formato para este tipo de dato es:YYYY El rango esta entre:1901 a 2155 El rango cuando usamos dos caracteres es:00 99

Rango00-69 se almacenan como2000-2069 Rango70-99 se almacenan como1970-1999

Los valores ilegales se convierte en0000


6/68



Tipos de Cadena Char y Varchar: Se declaran con una longitud que indica el nmero de caracteres a almacenar.

Ejemplo Char(3) , Varchar(3) Char

El rango es: 0-255 Cuando un valor se almacena se aaden espacios a la derecha hasta la longitud especifica

VARCHAR Son cadenas de caracteres de longitud variable, la longitud mxima es de 65532 Almacena los valores solo usando los caracteres necesarios ms un byte adicional para la

longitud y dos bytes para columnas declaradas mayores a 255 Los valores retornados son los mismos pues se eliminan los espacios

BLOB Tipo de dato binario que tratar los valores como cadenas de bits

TINYBLOB BLOB MEDIUMBLOB LONGBLOB

TEXT: Almacena cadenas de caracteres muy grandes

TINYTEXT TEXT MEDIUMTEXT LONGTEXT

Los campos blob se tratan como cadenas binarias utilizados por ejemplo para almacenar imgenes y lascolumnas Text se utilizan para almacenar por ejemplo citas de libros, etc.

Si asigna un valor a una columna Blob o Text que exceda la longitud mxima del tipo de columna, el valor setrunca.

Tanto Blob como Text no pueden tener valores por default

Valor Char(4) Almacenamiento Varchar(4) Almacenamiento

4 bytes 1 byte

ab ab 4 bytes ab 3 bytes

abc abc 4 bytes abcd 5 bytesabcdefgh abcd 4 bytes abcd 5 bytes


7/68



CONECTARNOS A MYSQL DESDE CONSOLAPara conectarse al servidor, generalmente se le proporcionar a MySQL un nombre de usuario y una contrasea. Siel servidor se est ejecutando en un ordenador distinto a donde est estableciendo la conexin, tambin se deberespecificar el nombre de host.

Una vez que conozca los parmetros apropiados, debera poder conectarse de este modo:

shell> mysql -h host -u user -pEnter password: ********

Si todo funciona bien, se ver una informacin de ingreso seguida por el prompt mysql>

shell> mysql -h host -u user -pEnter password: ********Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 25338 to server version: 5.0.9-beta-standardType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql>

Algunas instalaciones de MySQL permiten conectarse como usuario annimo (sin nombre) si el servidor se estejecutando en el ordenador local. Si esto sucede en su caso, debera poder conectarse al servidor ejecutandoMYSQL sin ningn parmetro:

shell> mysql

Para desconectarse en cualquier momento escriba QUIT (o \q) en el prompt mysql>

mysql> QUITBye

Una vez dentro de MySQL podremos realizar las consultas que deseemos, veamos algunas:

mysql> select version(), current_date;+-----------+--------------+| version() | current_date |+-----------+--------------+| 5.5.27 | 2012-08-26 |+-----------+--------------+1 row in set (0.05 sec)

mysql> SELECT SIN(PI()/4), (4+1)*5;+--------------------+---------+| SIN(PI()/4) | (4+1)*5 |

+--------------------+---------+| 0.7071067811865475 | 25 |+--------------------+---------+1 row in set (0.02 sec)


8/68



Si deseamos realizar varias instrucciones a la vez podemos realizarlas separndolas por una coma (;)

mysql> SELECT VERSION(); SELECT NOW();+-----------+| VERSION() |

+-----------+| 5.5.27 |+-----------+1 row in set (0.00 sec)

+---------------------+| NOW() |+---------------------+| 2012-08-26 21:38:26 |+---------------------+1 row in set (0.03 sec)

No es necesario que un comando sea ingresado en una sola lnea, de ese modo, comandos extensos que requieren

varias lneas no son un problema MYSQL determina cuando una sentencia ha llegado a l final observando si terminaen un punto y coma, no si se lleg al final de la lnea fsica.

mysql> SELECT-> USER()-> ,-> CURRENT_DATE;

+----------------+--------------+| USER() | CURRENT_DATE |+----------------+--------------+| root@localhost | 2012-08-26 |+----------------+--------------+1 row in set (0.00 sec)

Si durante el ingreso de un comando decide que no quiere ejecutarlo, canclelo tecleando \c:

mysql> SELECT-> USER()-> \cmysql>

La siguiente tabla muestra cada uno de lospromptque podr visualizar al utilizar MySQL desde consola.

Prompt Significadomysql> Listo para un nuevo comando.

-> Esperando la siguiente lnea en un comando de mltiples lneas.

'> Esperando la siguiente lnea, se encuentra abierta una cadena que comienza conapostrofo (''').

"> Esperando la siguiente lnea, se encuentra abierta una cadena que comienza concomillas dobles ('"').

`> Esperando la siguiente lnea, se encuentra abierta una cadena que comienza contilde ('`').

/*> Esperando la siguiente lnea, se encuentra abierto un comentario que comienza con/*.


9/68



Una vez que se sabe la forma de ingresar comandos, ahora es el momento de introducir comandos para ladefinicin de una base de datos.

INSTRUCCIONES DEL LENGUAJE DE DEFINICION DE DATOS

CREAR BASE DE DATOS

CREATE DATABASE NOMBRE_BD

CONSIDERACIONES:Al crear una base de datos, esta no se selecciona para su uso, debe hacerlo explcitamente

EJEMPLO:

mysql> CREATE DATABASE DBW;Query OK, 1 row affected (0.00 sec)mysql> USE DBW;Database changedmysql>

La seleccin de la base de datos puede realizarse con el comando USE como lo realizamos en la instruccin anteriorpero tambin puede realizarlo al momento de autenticarse, simplemente debe indicar en los parmetros deconexin el nombre de la base de datos como se indica a continuacin:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -h localhost -u root -p DBWEnter password: ********

BORRAR BASE DE DATOS

DROP DATABASE NOMBRE_BD

CREAR TABLA

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nombre_tabla (

nombre_columna1, tipo_columna opciones_de_columna referencia,

nombre_columna2, tipo_columna opciones_de_columna referencia, ...

[ , index1, index2 ...] )

[ ENGINE = MyISAM|InnoDB|HEAP ]

[ DEFAULT CHARSET = csname [ COLLATE = collname ]]

BORRAR TABLA

DROP TABLE NOMBRE_TABLA

VACIAR TABLA

DELETE * FROM NOMBRE_TABLA

TRUNCATE TABLE NOMBRE_TABLA

Esta ltima es ms rpida que DELETE pues MYSQL realiza un DROP y CREATE para realizar esta instruccin


10/68



AGREGAR UNA COLUMNA ALTER TABLE NOM_TABLA ADD NOM_COLUMNA TIPO_DATO

EJEMPLO : ALTER TABLE EMPLOYEES ADD PASS VARCHAR(20);

MODIFICAR LA DEFINICION DE UNA COLUMNA

ALTER TABLE NOM_TABLA CHANGE NOM_COL_ANTIGUO NOM_COL_NUEVO TIPO_DATO

EJEMPLO : ALTER TABLE EMPLOYEES CHANGE PASS PASSNEW CHAR(4); ALTER TABLE NOMBRE_TABLA MODIFY NOM_COL NUEVO_TIPO_DATO

EJEMPLO : ALTER TABLE EMPLOYEES MODIFY PASSNEW VARCHAR(20);

ELIMINAR UNA COLUMNA ALTER TABLE NOM_TABLA DROP NOM_COLUMNA

EJEMPLO : ALTER TABLE EMPLOYEES DROP PASSNEW;

RENOMBRAR TABLA ALTER TABLE NOM_TABLA RENAME NUEVO_NOM_TABLA

EJEMPLO : ALTER TABLE EMPLOYEES RENAME EMPLEADOS;

ESQUEMA DE INTEGRIDADEs aquel que nos permite mantener la consistencia de los datos mediante la definicin de manera precisa de lasreglas de integridad de dominio y referencial que se deben cumplir al actualizar nuestra base de datos.

INTEGRIDAD REFERENCIAL Relacionada con las claves forneas: Una clave fornea es un atributo de una relacin, cuyos valores se

corresponden con los de la clave primaria en otra o en la misma relacin. Las situaciones donde puede violarse la integridad referencial es el borrado de registros o en la modificacin de

claves principales. Si se elimina un registro cuya llave primaria se usa como fornea en otra relacin, las registros con esos valores

de llave fornea contendrn valores sin referenciar.

Algunas formas de conservar la integridad referencial

Restringir operaciones: Borrar o modificar registros cuya clave primaria es clave fornea en otros registros, solo

estar permitido si no existe ningn registro con ese valor de clave en ninguna otra relacin.

Transmisin en cascada: Borrar o modificar registros cuya clave primaria es clave fornea en otras implica

borrar o modificar los registros con los mismos valores de clave fornea.

LLAVES FORANEASSe pueden definir llaves forneas en cualquier tipo de tabla de Mysql, pero solo tienen sentido cuando se usan

tablas de tipo InnoDB.

Para trabajar con llaves forneas es necesario considerar lo siguiente: Ambas tablas deben ser del tipo InnoDB y no ser tablas temporales.

Usar FOREIGN KEY( campo_fk ) REFERENCES nombre_tabla ( nombre_campo) INDEX nom_index ( campos_indx )

Las columnas de tipo BLOB y TEXT no pueden incluirse en una llave fornea.

En Mysql no se crean los ndices de manera automtica en las llaves forneas o en las


11/68



claves referenciadas, por lo que es necesario crearlas de manera explcita. Ya que son necesarios para que laverificacin de las llaves forneas sea ms rpida.

Ejemplo:CREATE TABLE cliente

(id_cliente INT NOT NULL,nombre VARCHAR(30),PRIMARY KEY ( id_cliente ))TYPE = INNODB;

CREATE TABLE venta(

id_factura INT NOT NULL,id_cliente INT NOT NULL,cantidad INT,PRIMARY KEY(id_factura),INDEX (id_cliente),

FOREIGN KEY (id_cliente) REFERENCES cliente(id_cliente)) TYPE = INNODB;

La sintaxis completa de una restriccin de clave fornea es la siguiente:

[CONSTRAINT smbolo] FOREIGN KEY (nombre_columna, ...)REFERENCESnombre_tabla (nombre_columna, ...)[ON DELETE{CASCADE | SET NULL | NO ACTION}][ON UPDATE{CASCADE | SET NULL | NO ACTION}]

CASCADE: Borra o actualiza el registro en la tabla padre y automticamente borra o actualiza los registroscoincidentes en la tabla hija

SET NULL:Borra o actualiza el registro en la tabla padre y establece en NULL la o las columnas de la llave fornea enla tabla hija.NO ACTION:Cuando se borra o actualiza un valor de la llave primaria no ser permitido si en la tabla referenciadahay un valor de llave fornea relacionado.

CONSIDERACIONES Las columnas referenciadas en la restriccin de integridad, deben tener tipos de datos similares para que

puedan ser comparadas sin necesidad de hacer una conversin de tipos. En lo que respecta a los tipos enteros el tamao y el signo deber ser el mismo En las columnas de tipo carcter, el tamao no tiene que ser el mismo necesariamente Si se especifica una accin SET NULL, hay que asegurarse de que las columnas en la tabla hija no se han

definido como NOT NULL.

DEFINIR LLAVE UNA VEZ CREADA LA TABLA

ALTER TABLE nombre_tabla ADD [CONSTRAINT smbolo] FOREIGN KEY(...)REFERENCES otra_tabla(...) [acciones_ON_DELETE][acciones_ON_UPDATE]

BORRADO DE LLAVE


12/68



ALTER TABLEnombre_tabla DROPnombre_llave foranea

INTEGRIDAD DE DOMINIOEspecifica un conjunto de valores que son vlidos a ingresar sobre una columna especificada para una tabla de la

base de datos. Esta integridad se verifica a travs de la validacin de los valores de datos que se ingresan y el tipode los datos a introducir (numrico, alfanumrico, alfabtico, etc.).

DEFAULT:Esta restriccin asigna un valor especfico a una columna cuando el valor para ello no haya sido explcitamenteproporcionado para tal columna en una sentencia INSERT, al insertar un nuevo registro en la tabla.

Ejemplo: La regla del negocio dice que no se pueden contratar menores de edad, en la columna EDAD en la tablaEMPLEADOS se restringe a que si una edad para un empleado que ingresa no es sealada explcitamente, el DBMSasigne el valor 18 que es la mayora de edad.

CREATE TABLE EMPLEADO(

ID_EMP INT,EDAD INT NOT NULL DEFAULT 18

)

UNIQUE:Con esta restriccin se previene la duplicacin de valores en columnas que tienen valor nico y que no son llavesprimarias pero que pueden ser una llave alternativa o candidata. Al igual que las llaves primarias no se le estpermitido que se introduzcan valores nulos.

CREATE TABLE CLIENTE(

NUM_CTE INT NOT NULL,NOM_CTE CHAR(30) NOT NULL,DIR_CTE CHAR(30),FAX_CTE INT,E_MAIL CHAR(30) UNIQUE NOT NULL

)

INSPECCIONAR META DATOS

Para poder obtener datos al respecto de los datos que se encuentran almacenados en la base de datos, debemosutilizar el comando SHOW, a continuacin mostramos las formas ms comunes de este comando.

Instruccin FuncinSHOW DATABASES Muestra una lista de todas las bases de datosSHOW TABLES FROM NOM_BD Muestra una lista de todas las tablas en la BD indicadaSHOW [FULL] COLUMNS FROM TABLA Muestra informacin detalla de todas las columnas de la tablaSHOW INDEX FROM TABLA Muestra una lista de los ndices de la tabla indicada

DESCRIBE


13/68



Este comando nos permite conocer la estructura de alguna de las tablas de la base de datos

PRACTICA 1:

GENERAR EL SCRIPT DE LA BASE DE DATOS EN MYSQL PARA EL SIGUIENTE DIAGRAMA


14/68



PRACTICA 2:

GENERAR EL SCRIPT DE LA BASE DE DATOS EN MYSQL PARA EL SIGUIENTE DIAGRAMA


15/68



PRACTICA 3

"Se desea disear una base de datos para almacenar y gestionar la informacin empleada por una empresa

dedicada a la venta de automviles, teniendo en cuenta los siguientes aspectos:

La empresa dispone de una serie de coches para su venta. Se necesita conocer la matrcula, marca y modelo, elcolor y el precio de venta de cada coche.

Los datos que interesa conocer de cada cliente son el NIF, nombre, direccin, ciudad y nmero de telfono:

adems, los clientes se diferencian por un cdigo interno de la empresa que se incrementa automticamente

cuando un cliente se da de alta en ella. Un cliente puede comprar tantos coches como desee a la empresa. Un

coche determinado solo puede ser comprado por un nico cliente.

El concesionario tambin se encarga de llevar a cabo las revisiones que se realizan a cada coche. Cada revisin tiene

asociado un cdigo que se incrementa automticamente por cada revisin que se haga. De cada revisin se desea

saber si se ha hecho cambio de filtro, si se ha hecho cambio de aceite, si se ha hecho cambio de frenos u otros. Los

coches pueden pasar varias revisiones en el concesionario".

Tambin desea llevar un registro de las reparaciones y refacciones que se utilizan en los coches del cliente asi como

de los proveedores que le surten

Genere el diagrama ER y el script de la base de datos en MySQL

SENTENCIAS DE MANIPULACION DE DATOS INSERCION:

EXTENDIDO

INSERT INTO TABLA ( CAMPO1, CAMPO2,.,CAMPON) VALUES(Valor Cadena,Valor Num,..,Valor);

NORMAL INSERT INTO TABLA VALUES(Valor Cadena,Valor Num,..,Valor);

INSERCION MULTIPLE INSERT INTO TABLA ( CAMPO1, CAMPO2,.,CAMPON) VALUES(Valor Cadena,Valor Num,..,Valor), (Valor Cadena,Valor

Num,..,Valor)..

ACTUALIZACION UPDATE

UPDATE TABLA SET CAMPO = VALOR CADENA, CAMPO=VALOR_NUMERICO [WHERE EXPRESION] [LIMIT #]

RECUPERAR INFORMACION DE UNA TABLA SELECT [CAMPO1,CAMPO2.,.,CAMPO N | * ]

FROM TABLA [ WHRE EXPRESION ] [ORDER BY CAMPO [ ASC | DESC ]]


16/68



BORRADO DE REGISTROS DELETE FROM NOMBRE_TABLA [ WHERE EXPRESION][ORDER BY] [LIMIT FILAS]

CORRESPONDENCIA DE PATRONES

SELECT [CAMPO1,CAMPO2.,.,CAMPO N | * ]

FROM TABLA WHRE CAMPO LIKE < VALOR% | %VALOR | %VALOR% >

LIMITACION DEL NUMERO DE REGISTROS LIMIT NO_RESULTADOS | INICIO, NO_REGISTROS

CONSIDERACIONESLIMIT 0 : NO DEVUELVE RESULTADOSLIMIT 0,1: COMIENZA DESDE EL PRIMER REGISTRO Y NOS MOSTRARA UN RENGLONLIMIT 2,3: COMIENZA EN EL RENGLON 3 Y DEVUELVE 3 REGISTROS

EJEMPLO : SELECT * FROM EMPLOYEES LIMIT 2,3;

COMBINAR COLUMNAS SELECT CONCAT(COLUMNA1,COLUMNA2,.,COLUMNA N)

[ FROM TABLA WHERE EXPRESION ]

AGRUPADO DE CONSULTAS COMBINAR TABLAS INSERCIONESSELECT

INSERT INTO TABLA (COL1,COL2,..,COLN) SELECT COL1,COL2,,COLN FROM TABLA WHERE

EXPRESION EJEMPLO : INSERT INTO EMPLOYEES (FIRSTNAME,LASTNAM) SELECT ISC.,TORRES; INSERT INTO TABLA SET COL1=VALOR,COL2=VALOR2; EJEMPLO : INSERT INTO EMPLOYEES SET FIRSTNAME = ISC., LASTNAME = TORRES;

VARIABLES DE USUARIO SELECT @VAR := VALOR FROM TABLA; SET @VAR = VALOR; EJEMPLO: SET @ID = 1 SELECT * FROM EMPLOYEES WHERE EMPLOYEEID = @ID;

INDICES FULLTEXT

Los ndices FULLTEXT pueden usarse slo con tablas MyISAM; pueden ser creados desde columnas CHAR,VARCHAR, o TEXT como parte de un comando CREATE TABLE o aadidos posteriormente usando ALTER TABLE oCREATE INDEX. Para conjuntos de datos grandes, es mucho ms rpido cargar los datos en una tabla que no tengandice FULLTEXT y crear el ndice posteriormente, que cargar los datos en una tabla que tenga un ndice FULLTEXTexistente.

mysql> CREATE TABLE articles (

-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,-> title VARCHAR(200),


17/68



-> body TEXT,-> FULLTEXT (title,body)-> );

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES-> ('MySQL Tutorial','DBMS stands for DataBase ...'),-> ('How To Use MySQL Well','After you went through a ...'),-> ('Optimizing MySQL','In this tutorial we will show ...'),-> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),-> ('MySQL vs. YourSQL','In the following database comparison ...'),-> ('MySQL Security','When configured properly, MySQL ...');

mysql> SELECT * FROM articles-> WHERE MATCH (title,body) AGAINST ('database');+----+-------------------+------------------------------------------+| id | title | body |

+----+-------------------+------------------------------------------+| 5 | MySQL vs. YourSQL | In the following database comparison ... || 1 | MySQL Tutorial | DBMS stands for DataBase ... |+----+-------------------+------------------------------------------+

Por defecto, la bsqueda se realiza de forma insensible a maysculas.

Cuando se usa MATCH() en una clusula WHERE , como en el ejemplo precedente, los registros retornados seordenan automticamente con la relevancia mayor primero. Los valores relevantes son nmeros en coma flotanteno negativos. Relevancia cero significa que no tiene similaridad. La relevancia se computa basada en el nmero depalabras en el registro, el nmero de palabras nicas en este registro, el nmero total de palabras en la coleccin, yel nmero de documentos (registros) que contienen una palabra partculas.

mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')-> FROM articles;+----+-----------------------------------------+| id | MATCH (title,body) AGAINST ('Tutorial') |+----+-----------------------------------------+| 1 | 0.65545833110809 || 2 | 0 || 3 | 0.66266459226608 || 4 | 0 || 5 | 0 || 6 | 0 |+----+-----------------------------------------+

Algunas palabras se ignoran en las bsquedas full-text:

Cualquier palabra demasiado corta se ignora. La longitud mnima de las palabras que se encuentran en bsquedasfull-text es de cuatro caracteres por defecto. Las palabras en la lista de palabras de parada se ignoran. Una palabra de parada es una palabra tal como el o

algn que estan comn que se considera que no tiene valor semntico. Hay una lista de palabras de parada, peropuede reescribirse con una lista de palabras definidas por el usuario.


18/68



Una tcnica de este tipo funciona mejor con colecciones grandes (de hecho, se ajust con cuidado para funcionarde este modo).

mysql> SELECT * FROM articles

-> WHERE MATCH (title,body) AGAINST ('MySQL');Empty set (0.00 sec)

El resultado de bsqueda es vaco porque la palabra MySQL est presente al menosen el 50% de los registros.Como tal, se trata efectivamente como una palabra de parada.

PRACTICA 4

GENERAR EL SCRIPT PARA LA SIGUIENTE BASE DE DATOS

Consideraciones:Las llaves primarias de las tablas incrementan su valor

automticamente

Valide que solo pueda capturar una M o una F para el sexo

No pueden existir dos correos iguales

Dos pelculas no deben llamarse igual

La fecha por default debe ser la fecha del da

Agregue un ndice en el nombre del cliente

Analice la tabla pelculas y agregue un ndice en el campo

donde considere que sea ms necesario.

Agregue CONSTRAINTS en la definicin de las llaves

primarias y las llaves forneas.

Aplique un borrado en cascada en las llaves foraneas

El precio no debe quedar nulo y debe tener un valor por

default de 0Realice al menos 3 inserciones extendidas en cada tabla

VISTAS ( DESDE V. 5.0 DE MYSQL )

Una vista es una tabla virtual que muestra la informacin relevante para el usuario adems que permite encapsularla complejidad de su implementacin.

Una vista puede mostrar toda la informacin de una tabla o de la integracin de informacin de ms de una tabla.Es decir se puede afirmar que las vistas trabajan como especie de filtros de las tablas subyacentes que brindan lainformacin presentada por la vista.

Entre las principales ventajas del empleo de las vistas podemos mencionar: Permite mostrar un subconjunto de filas y/o columnas de una tabla. Permite mostrar informacin de ms de una tabla. Permite realizar uniones entre dos o ms tablas.


19/68



Permite mostrar informes Puede definirse a partir de otras vistas

Las vistas tambin resultan tiles para cuestiones de seguridad ya que nos permitira filtrar solo los datos quenosotros deseamos permitirle al usuario ver.

VENTAJAS DE USAR VISTAS

Ocultar informacin:Permite el acceso a algunos datos y manteniendo oculto el resto de la informacin que no se incluye en la vista. Elusuario opera con los datos de una vista como si se tratara de una tabla.

Simplificar la administracin de los permisos de usuario:Se pueden dar al usuario permisos para que solamente pueda acceder a los datos a travs de vistas, en lugar deconcederle permisos para acceder a ciertos campos, as se protegen las tablas base de cambios en su estructura.

Mejorar el rendimiento:Se puede evitar escribir instrucciones repetidamente almacenando en una vista el resultado de una consultacompleja que incluya informacin de varias tablas.

CREAR VISTAS

SINTAXIS

CREATE [OR REPLACE]VIEW nombre_vista [(columnas)]AS sentencia_select

MODIFICAR

ALTERVIEW nombre_vista [(columnas)]AS sentencia_select

mysql> create view vista_emp-> as-> select * from employees;


Se necesita el permiso CREATE_VIEW_PRIV y el privilegio SELECT en cada columna seleccionada.

Esta sentencia crea una vista nueva o reemplaza una existente si se incluye la clusula OR REPLACE.

mysql> create or replace view vista_emp-> as-> select employeeid,firstname from employees;


Toda vista pertenece a una base de datos. Por defecto, las vistas se crean en la base de datos actual.


20/68



Para crear una vista en una base de datos especfica, indquela con base_de_datos.nombre_vistaal momentode crearla.

mysql> CREATE VIEW test.v AS SELECT * FROM t;

Las tablas y las vistas comparten el mismo espacio de nombres en la base de datos, por eso, una base de datosno puede contener una tabla y una vista con el mismo nombre.

Por defecto, los nombres de las columnas devueltos por la sentencia SELECT se usan para las columnas de lavista

Para dar explcitamente un nombre a las columnas de la vista utilice la clusula columnaspara indicar una listade nombres separados con comas. La cantidad de nombres indicados en columnas debe ser igual a la cantidadde columnas devueltas por la sentencia SELECT.

mysql> alter view vista_emp (id,nombre)-> as-> select employeeid,firstname from employees;


mysql> select * from vista_emp limit 5-> ;

+----+----------+| id | nombre |+----+----------+| 1 | Nancy || 2 | Andrew || 3 | Janet || 4 | Margaret || 5 | Steven |+----+----------+

5 rows in set (0.00 sec)

LIMITACIONES

La sentencia SELECT no puede contener una subconsulta en su clusula FROM. La sentencia SELECT no puede hacer referencia a variables del sistema o del usuario. La sentencia SELECT no puede hacer referencia a parmetros de sentencia preparados. Dentro de la vista, la definicin no puede hacer referencia a parmetros de la rutina o a variables locales. La definicin no puede hacer referencia a una tabla TEMPORARY, ni se puede crear una vista TEMPORARY. Las tablas mencionadas en la definicin de la vista deben existir siempre No se puede asociar un disparador con una vista.

En la definicin de una vista est permitido ORDER BY, pero es ignorado si se seleccionan columnas de una vistaque tiene su propio ORDER BY.

mysql> alter view vista_emp (id,nombre)-> as-> select employeeid,firstname from employees-> order by firstname desc;



21/68



mysql> select * from vista_emp;+----+----------+| id | nombre |+----+----------+| 5 | Steven |

| 7 | Robert || 1 | Nancy || 6 | Michael || 4 | Margaret || 8 | Laura || 3 | Janet || 9 | Anne || 2 | Andrew |+----+----------+9 rows in set (0.00 sec)

mysql> select * from vista_emp order by nombre;

+----+----------+| id | nombre |+----+----------+| 2 | Andrew || 9 | Anne || 3 | Janet || 8 | Laura || 4 | Margaret || 6 | Michael || 1 | Nancy || 7 | Robert || 5 | Steven |+----+----------+9 rows in set (0.00 sec)

BORRADO DE VISTAS

DROP VIEW [IF EXISTS]nombre_vista [, nombre_vista] ...

DROP VIEW elimina una o ms vistas de la base de datos. Se debe poseer el privilegio DROP en cada vista aeliminar.

ACTUALIZAR VISTAS

Algunas vistas son actualizables. Esto significa que se las puede emplear en sentencias como UPDATE, DELETE, oINSERT para actualizar el contenido de la tabla subyacente. Para que una vista sea actualizable, debe haber unarelacin uno-a-uno entre los registros de la vista y los registros de la tabla subyacente. Hay otros elementos queimpiden que una vista sea actualizable. Ms especficamente, una vista no ser actualizable si contiene:

Funciones agregadas (SUM(), MIN(), MAX(), COUNT(), etctera) DISTINCT GROUP BY


22/68



HAVING UNION o UNION ALL Una subconsulta en la lista de columnas del SELECT Join Hace referencia solamente a valores literales en tal caso no hay una tabla subyacente para actualizar.

Con respecto a la posibilidad de agregar registros mediante sentencias INSERT, es necesario que las columnas de lavista actualizable tambin cumplan los siguientes requisitos adicionales:

La vista debe contemplar todas las columnas de la tabla en la base de datos que no tengan indicado unvalor por defecto.

Las columnas de la vista deben ser referencias a columnas simples y no columnas derivadas. Una columnaderivada es una que deriva de una expresin.

No puede insertar registros en una vista conteniendo una combinacin de columnas simples y derivadas,pero puede actualizarla si actualiza nicamente las columnas no derivadas.

EXPLORAR METADATOS

SHOW CREATE TABLE nombre_tablaMuestra la instruccin que se utiliz para crear una determinada tabla

SHOW CREATE VIEW nombre_vistaMuestra el conjunto de sentencias CREATE VIEW que se utiliz para crear la vista.

SHOW CREATE PROCEDURE nombre_procedimientoMuestra el conjunto de sentencias CREATE PROCEDURE que se utiliz para crear un determinado procedimientoalmacenado

PROCEDIMIENTOS ALMACENADOS

Un procedimiento almacenado (store procedure) no es ms que una secuencia ordenada de instrucciones Transact-SQL, que pueden recibir y proporcionar parmetros provistos por el usuario y se pueden guardar en el servidor conun nombre, para posteriormente ser invocados y ejecutados, por lo regular desde una aplicacin (Escritorio oWeb).

VENTAJAS DE USAR PROCEDIMIENTOS ALMACENADOS

Compilacin:La primera vez que se invoca un SP, el motor lo compila y a partir de ah, se sigue usando la versincompilada del mismo, hasta que se modifique. Esto significa que se tendr un mejor rendimiento que las consultas

directas que usan las instrucciones T-SQL, que se compilan cada vez que se invocan.

Automatizacin: si tenemos un conjunto de instrucciones T-SQL, las cuales queremos ejecutar de maneraordenada, un SP es la mejor manera de hacerlo.Administracin: cuando realizamos aplicaciones con un gran nmero de lneas de cdigo, y queremos hacercambios, solo implica modificar un SP y no toda la aplicacin, lo que significa solo cambiamos los SP en el servidor yno tenemos que actualizar la aplicacin en todos los equipos cliente.


23/68



Seguridad: una parte importante es que a los usuarios de nuestra aplicacin, solo les proporcionamos los permisospara ejecutar los procedimientos almacenados y no el acceso a todos los objetos de la base.

Programabilidad:Los SP admiten el uso de variables y estructuras de control como IF, Bucles, Case, etc. ademsdel manejo de transaccin incluidas las llamadas a otros procedimientos. y permite controlar excepciones.

Aceptar parmetros de entrada y devolver varios valores en forma de parmetros de salida al lote o alprocedimiento que realiza la llamada.

Trafico de Red: Pueden reducir el trfico de la red, debido a que se trabaja sobre el motor (en el servidor), y si unaoperacin incluye hacer un trabajo de lectura primero y en base a eso realizar algunas operaciones, esos datos quese obtienen no viajan por la red.

Ejemplo:

Cmo actualizara las existencias de un producto cuando se da una salida del mismo sin utilizar procedimientos

almacenados?

1.- Consultar las existencias y mandarlas a la aplicacin2.- Validar en la aplicacin si la cantidad se puede surtir3.- Suponiendo que la respuesta es s, entonces mandar la consulta de actualizacin desde la aplicacin

Ahora bien como actualizarlas utilizando procedimientos almacenados?

1.- Ejecutar el procedimiento almacenado definido para actualizar existencias, enviando como parmetro,el id del producto as como la cantidad de salida.

APLICACIN DE LOS PROCEDIMIENTOS ALMACENADOS

Cuando mltiples aplicaciones cliente se escriben en distintos lenguajes o funcionan en distintas plataformas,pero necesitan realizar la misma operacin en la base de datos.

Cuando la seguridad es muy importante. Los bancos, por ejemplo, usan procedimientos almacenados paratodas las operaciones comunes. Esto proporciona un entorno seguro y consistente, y los procedimientospueden asegurar que cada operacin se loguea apropiadamente. En tal entorno, las aplicaciones y los usuariosno obtendran ningn acceso directo a las tablas de la base de datos, slo pueden ejecutar algunosprocedimientos almacenados.

PERMISOS

Los procedimientos almacenados requieren la tabla proc de la base de datos MySQL. El permiso CREATE ROUTINEse necesita para crear procedimientos almacenados. El permiso ALTER ROUTINEse necesita para modificar o borrar procedimientos almacenados, este permiso se

da automticamente al creador de la rutina. El permiso EXECUTEse requiere para ejecutar procedimientos almacenados. Sin embargo, este permiso se da

automticamente al creador de la rutina.


24/68



CONSIDERACIONES:

Los procedimientos almacenados se asocian a una base de datos. Cuando se borra una base de datos, todos los procedimientos almacenados asociados a ella tambin se borran. Los comandos USEno se permiten dentro de un procedimiento almacenado.

Para hacer referencia un procedimiento almacenado de otra base de datos utilice la siguiente regla:NOM_DB.NOM_SP()

Cuando se borra una base de datos, todos los procedimientos almacenados asociados a ella tambin se borran

SINTAXIS

Los procedimientos almacenados y rutinas se crean con comandos CREATE PROCEDURE y CREATE FUNCTION . Una rutina es un procedimiento o una funcin. Un procedimiento se invoca usando un comando CALL , y slo puede pasar valores usando variables de salida. Una funcin puede llamarse desde dentro de un comando como cualquier otra funcin (esto es, invocando el

nombre de la funcin), y puede retornar un valor escalar. Las rutinas almacenadas pueden llamar otras rutinasalmacenadas

CREATE PROCEDURE sp_nom ( [ IN | OUT | INOUT ] nombre_parametro )INSTRUCCIONES SQL

CREATE FUNCTION sp_nom ( [ IN | OUT ] nombre_parametro )RETURNS < CUALQUIER TIPO DE DATO VALIDO>INSTRUCCIONES SQL

IN, indica que el parmetro es slo de entrada, OUTque es una variable de salida y INOUTque es de ambas formas,si no se especifica el modo del parmetro por defecto es de tipo IN

EJEMPLO 1:

Crear un procedimiento almacenado que me muestre un reporte de productos.

mysql> create procedure proc_products()-> select productid, productname from products limit 5;


Ejecutamos el procedimiento almacenado

mysql> call proc_products;+-----------+------------------------------+| productid | productname |+-----------+------------------------------+| 1 | Chai || 2 | Chang || 3 | Aniseed Syrup || 4 | Chef Anton's Cajun Seasoning |


25/68



| 5 | Chef Anton's Gumbo Mix |+-----------+------------------------------+5 rows in set (0.00 sec)


Otra forma de llamar al procedimiento almacenado

mysql> call proc_products();+-----------+------------------------------+| productid | productname |+-----------+------------------------------+| 1 | Chai || 2 | Chang || 3 | Aniseed Syrup || 4 | Chef Anton's Cajun Seasoning || 5 | Chef Anton's Gumbo Mix |

+-----------+------------------------------+5 rows in set (0.00 sec)


EJEMPLO 2:

Realizar un procedimiento almacenado que obtenga el nombre y la cantidad de productos que nos ha comprado undeterminado cliente.

mysql> CREATE PROCEDURE prodxcte ( IN idcte CHAR(5) )-> SELECT productname, SUM(quantity) as Total

-> FROM products p JOIN orderdetails od-> ON p.productid = od.productid-> JOIN orders o ON od.orderid = o.orderid-> WHERE o.customerid = idcte-> GROUP BY productname;


mysql> CALL PRODXCTE('ANTON');+-------------------------------+-------+| productname | Total |+-------------------------------+-------+| Alice Mutton | 18 |

| Boston Crab Meat | 10 || Chang | 20 || Chocolade | 15 || Geitost | 38 || Gumbr Gummibrchen | 30 || Ipoh Coffee | 15 || Louisiana Hot Spiced Okra | 4 || Perth Pasties | 25 || Queso Cabrales | 74 |


26/68



| Raclette Courdavault | 15 || Ravioli Angelo | 5 || Rhnbru Klosterbier | 30 || Sasquatch Ale | 40 || Singaporean Hokkien Fried Mee | 20 |

+-------------------------------+-------+15 rows in set (0.04 sec)

EJEMPLO 3

Realizar un procedimiento almacenado que nos obtenga el nmero de existencias de cualquier producto.

mysql> CREATE PROCEDURE existencias( in idprod int, out cantexis int )-> SELECT UnitsInStock INTO cantexis-> FROM products-> WHERE productid = idprod-> ;


mysql> describe products;+-----------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-----------------+-------------+------+-----+---------+----------------+| ProductID | int(11) | NO | PRI | NULL | auto_increment || ProductName | varchar(40) | NO | | NULL | || SupplierID | int(11) | NO | MUL | NULL | || CategoryID | int(11) | NO | MUL | NULL | || QuantityPerUnit | varchar(20) | YES | | NULL | || UnitPrice | float | YES | | 0 | |

| UnitsInStock | smallint(6) | YES | | 0 | || UnitsOnOrder | smallint(6) | YES | | 0 | || ReorderLevel | smallint(6) | YES | | 0 | || Discontinued | tinyint(4) | NO | | 0 | |+-----------------+-------------+------+-----+---------+----------------+10 rows in set (0.00 sec)

mysql> call existencias(1,@cant);Query OK, 1 row affected (0.02 sec)

mysql> select @cant;+-------+| @cant |+-------+| 39 |+-------+1 row in set (0.00 sec)

MODIFICAR PROCEDIMIENTO

ALTER PROCEDURE sp_nom ( [ IN | OUT | INOUT ] )


27/68



INSTRUCCIONES SQL

BORRAR PROCEDIMIENTO:

DROP PROCEDURE [ IF EXISTS ] sp_nom

mysql> drop procedure if exists existencias;Query OK, 0 rows affected (0.00 sec)

SENTENCIAS COMPUESTAS:

BEGIN[ LISTA_SENTENCIAS ]

END

Se utilizan para escribir sentencias compuestas que aparecen el interior de procedimientos almacenados o triggers.

Cada sentencia dentro de la lista debe terminar con ; , por lo que se debe cambiar el delimitador de fin desentencia ; por otro por ejemplo //

EJEMPLO 4:

Realizar un procedimiento almacenado que nos obtenga el nmero de existencias de cualquier producto.

mysql> CREATE PROCEDURE existencias( in idprod int, out cantexis int )-> BEGIN-> SELECT UnitsInStock INTO cantexis-> FROM products-> WHERE productid = idprod

-> END-> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manualthat corresponds to your MySQL server version for the right syntax to use near'END' at line 6

Como podemos observar esta ejecucin nos lleva a un error, pues no hemos cambiado el delimitador para ejecutarsentencias compuestas.

mysql> delimiter //mysql> CREATE PROCEDURE existencias( in idprod int, out cantexis int )

-> BEGIN

-> SELECT UnitsInStock INTO cantexis-> FROM products-> WHERE productid = idprod;-> END//

Query OK, 0 rows affected (0.03 sec)mysql> call existencias(1,@cant)//Query OK, 1 row affected (0.00 sec)

mysql> select @cant//


28/68



+-------+| @cant |+-------+| 39 |+-------+

1 row in set (0.00 sec)ESTRUCTURAS DE PROGRAMACION EN STORE PROCEDURES

DECLARE:

DECLARE nom_var[,.] type [DEFAULT value]

Se utiliza para definir variables locales a la rutina. La visibilidad de una variable local es dentro del bloque BEGIN.END.

Puede usarse en bloques anidados excepto aquellos que declaren una variable con el mismo nombre.

WHILE serach_condition DOlista_sentencias

END WHILE

mysql> create procedure ciclo( out var int )-> begin-> declare v1 int default 2;-> while v1 < 5 do-> set v1 = v1+1;-> end while;-> select v1 into var;-> end//


mysql> call ciclo(@valor)//Query OK, 1 row affected (0.00 sec)

mysql> select @valor//+--------+| @valor |+--------+| 5 |+--------+1 row in set (0.00 sec)

SENTENCIA REPEAT

Esta instruccin permite que los comandos dentro de esta instruccin se repitan hasta que la condicin seacierta.

REPEATstatement_list

UNTIL search_condition END REPEAT


29/68



mysql> CREATE PROCEDURE REP_EJEMPLO( valor INT )-> BEGIN-> SET @x = 0;-> REPEAT

-> SET @x = @x + 1;-> UNTIL @x > valor END REPEAT;-> END//


mysql> CALL REP_EJEMPLO(200)//Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @x//+------+| @x |+------+

| 201 |+------+1 row in set (0.00 sec)

SENTENCIA IF

IF search_condition THEN statement_list[ELSEIF search_condition THEN statement_list] ...[ELSE statement_list]

END IF

SENTENCIA CASE

CASE case_valueWHEN when_value THEN statement_list[WHEN when_value THEN statement_list] ...[ELSE statement_list]

END CASECASE

WHEN search_condition THEN statement_list[WHEN search_condition THEN statement_list] ...[ELSE statement_list]

END CASE

EJEMPLO IF:

mysql> CREATE PROCEDURE bdw.acbcatif ( IN idcat int, IN nomcat varchar(15), INdescrip text, IN opc int )

-> BEGIN-> IF opc = 1 THEN-> INSERT INTO categories ( categoryname, description ) values(nomcat,descrip);


30/68



-> ELSEIF opc = 2 THEN-> UPDATE categories SET categoryname = nomcat, description =descrip WHERE categoryid = idcat;

-> ELSEIF opc = 3 THEN-> DELETE FROM categories WHERE categoryid = idcat;

-> ELSE-> SELECT OPCION NO VALIDA;-> END IF;-> END-> //


mysql> call bdw.acbcatif(1,'otra cat','otra categoria if',1)//Query OK, 1 row affected (0.05 sec)

EJEMPLO CASE:

mysql> CREATE PROCEDURE bdw.acbcat ( IN idcat int, IN nomcat varchar(15), INdescrip text, IN opc int )-> BEGIN-> CASE opc-> WHEN 1 THEN-> INSERT INTO categories ( categoryname, description ) values(nomcat,descrip);

-> WHEN 2 THEN-> UPDATE categories SET categoryname = nomcat, description =descrip WHERE categoryid = idcat;

-> WHEN 3 THEN-> DELETE FROM categories WHERE categoryid = idcat;-> ELSE SELECT OPCION NO VALIDA;-> END CASE;-> END-> //


mysql> CALL acbcat(1,'cat de proc','categoria de procedimiento',1)//Query OK, 1 row affected (0.19 sec)

TRANSACCIONES:

Las peticiones a la base de datos tienen lugar de forma lineal, una detrs de otra. Cuando varios usuarios acceden a

una base de datos, resulta importante garantizar la coherencia de los resultados.

Para ello se utilizan las transacciones, que no son ms que un grupo de instrucciones a la base de datos que se

procesan de manera conjunta, para obtener un resultado.

PROPIEDADES DE LAS TRANSACCIONES

ATOMICIDAD

COHERENCIA

AISLAMIENTO


31/68



DURABILIDAD

ATOMICIDAD

La atomicidad significa que debe completarse toda la transaccin, de lo contrario se anulara toda la transaccin. Es

decir con esta propiedad se garantiza que la base de datos nunca incluir transacciones parciales, lo que conlleva aque la integridad no se cumpla.

Ejemplo:

Si extraemos dinero de una cuenta bancaria, pero falla la segunda peticin y el sistema no logra colocar el dineroen otra cuenta bancaria, ambas peticiones fallaran, entonces el dinero no puede perderse ni descontarse de laprimera cuenta.

COHERENCIA

La coherencia hace referencia al estado en el que se encuentran los datos cuando tienen lugar determinadas

condiciones.

Ejemplo:

Una condicin puede ser que cada factura este asociada con un cliente de la tabla clientes. Y al final de laelaboracin de la transaccin la factura deber tener esta referencia al cliente que la elaboro, y si no existe estarelacin entonces la transaccin deber dejar coherente los datos existentes en la base de datos.

AISLAMIENTO:

Esta propiedad significa que todos los datos utilizados durante el procesamiento de una transaccin no pueden serutilizados por otra transaccin hasta que no se haya completado la primera.

Ejemplo:

Si dos personas depositan 100 dlares en una cuenta con un saldo inicial de 900 dlares es lgico que al final elsaldo de la cuenta sea de 1100 dlares. Entonces qu pasara si la segunda transaccin lee 900, antes de que secomplete la primera, ambas transacciones parecieran haberse llevado a cabo satisfactoriamente, pero entonceshabran desaparecido 100 dlares pues el saldo final sera solo de 1000 dlares, entonces es sumamenteimportante aplicar un candado para que la segunda transaccin deba esperar hasta que pueda acceder a los datosde manera solitaria y su ejecucin quede aisladade las dems.

DURABILIDAD

Esta propiedad hace referencia al hecho de que una vez que se han confirmado los datos de una transaccin, sus

efectos permanecern, incluso tras un fallo del sistema.

TRANSACCIONES EN TABLAS INNODB

De manera predeterminada, cada cliente que se conecta al servidor MySQL comienza en modo autocommit

habilitado, lo cual automticamente confirma o realiza un commit en cada sentencia SQL ejecutada siendoentonces cada sentencia una transaccin.


32/68



Ahora bien para poder utilizar transacciones de mltiples sentencias se puede deshabilitar el modo autocommitcon la sentencia SET AUTOCOMMIT = 0 y emplear COMMIT y ROLLBACK para confirmar o cancelar latransaccin.

Ahora bien si se desea dejar activado el autocommit, se pueden encerrar las transacciones entre las siguientes

instrucciones START TRANSACTION y utilizar COMMIT o ROLLBACK tambin para confirmar o cancelar latransaccin;

CREAREMOS LA TABLA TBLPRUEBA EN LA BASE DE DATOS BDW:use bdw;create table tblprueba( A INT,

B CHAR(10)) engine = innodb;

INSERTAREMOS UN REGISTRO:insert into tblprueba values( 1, ruben);

SALDREMOS Y ENTRAREMOS A MYSQL Y REVISAREMOS EL CONTENDIDO DE TBLPRUEBAQuitC:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -u root -pEnter password: ********mysql> use bdw;Database changedmysql> select * from tblprueba;+------+--------+| A | B |+------+--------+z| 1 | ruben |+------+--------+1 row in set (0.00 sec)

DESHABILITAMOS EL MODO AUTOCOMMIT:mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec)

INSERTAMOS OTRO REGISTRO EN LA TABLA:mysql> insert into tblpruebavalues(2,isctorres');Query OK, 1 row affected (0.00 sec)mysql> select * from tblprueba;+------+-----------+| A | B |+------+------------+| 1 | ruben || 2 | isctorres |+------+------------+2 rows in set (0.00 sec)


33/68



PARA REVISAR LA DIFERENCIA CON EL AUTOCOMMIT DESACTIVADO, CERREMOS SESION Y VOLVAMOS AENTREAR, PARA REVISAR LA TABLAmysql> select * from tblprueba;+------+--------+| A | B |

+------+--------+| 1 | ruben |+------+--------+1 row in set (0.00 sec)

VOLVEMOS A HABILITAR EL MODO AUTOCOMMIT:mysql> set autocommit = 1;Query OK, 0 rows affected (0.00 sec)

INSERTAMOS OTRO REGISTRO EN LA TABLA:mysql> start transaction;Query OK, 0 rows affected (0.00 sec)

mysql> insert into tblprueba values(22,'ruben');Query OK, 1 row affected (0.00 sec)mysql> select * from tblprueba;+------+-------+| A | B |+------+-------+| 1 | ruben || 22 | ruben |+------+-------+2 rows in set (0.00 sec)

SALGAMOS, ENTREMOS Y REVISEMOS

mysql> quitByemysql> select * from tblprueba;+------+--------+| A | B |+------+--------+| 1 | ruben |+------+--------+1 row in set (0.00 sec)

Notamos entonces que al indicar explcitamente que se est realizando una transaccin, debemos entoncesejecutar un commit, para que haya durabilidad en la transaccin.

INSERTAMOS OTRO VEZ EL REGISTRO EN LA TABLA Y HAGAMOS COMMIT:mysql> use bdwDatabase changedmysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into tblprueba values(22,'ruben');Query OK, 1 row affected (0.00 sec)mysql> commit;


34/68



Query OK, 0 rows affected (0.07 sec)Salimos, entramos y revisamos la tablamysql> quitByemysql> select * from tblprueba;

+------+--------+| A | B |+------+--------+| 1 | ruben || 22 | ruben |+------+--------+2 row in set (0.00 sec)

MANEJO DE FUNCIONES

Diferencia entre procedimientos almacenados y funciones

PROCEDURE FUNCTION

Llamada Solo con CALL Posible en todas las instruccionesSQL(SELECT,UPDATE)

Retorno Puede retornar uno o mas Retorna un valor nico de un tipo determinadoParmetros Por valor y referencia Solo parmetros por valorInstruccionesPermitidas

Todas las SQL -----

Llamadas aFunciones oProcedimientos

Puede llamar a otros procedimientos y/ofunciones

Solo puede llamar a otras funciones

EJERCICIOS CON FUNCIONES:

Crear una funcin que reciba como parmetro una cadena y muestre un mensaje

mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)-> RETURN CONCAT('Hello, ',s,'!');-> //


mysql> select hello('ruben')//+----------------+| hello('ruben') |+----------------+

| Hello, ruben! |+----------------+1 row in set (0.00 sec)

Crear una funcin que permita cambiar el formato de fecha que maneja la base de datos en el formato castellano,lo cual sirve para evitar que el lenguaje de programacin tenga que realizar dichas conversiones y la base de datosminimice el trabajo de la aplicacin.

mysql> CREATE FUNCTION `detalle`(fecha DATETIME) RETURNS varchar(150)


35/68



-> BEGIN-> DECLARE dia INT;-> DECLARE mes INT;-> DECLARE dia_s VARCHAR(20);-> DECLARE mes_s VARCHAR(20);

-> DECLARE fecha_f VARCHAR(150);-> SET dia = DAYOFWEEK(fecha);-> SET mes = MONTH(fecha);-> CASE dia-> WHEN 1 THEN SET dia_s = Domingo;-> WHEN 2 THEN SET dia_s = Lunes;-> WHEN 3 THEN SET dia_s = Martes;-> WHEN 4 THEN SET dia_s = Miercoles;-> WHEN 5 THEN SET dia_s = Jueves;-> WHEN 6 THEN SET dia_s = Viernes;-> WHEN 7 THEN SET dia_s = Sabado;-> END CASE;

-> CASE mes-> WHEN 1 THEN SET mes_s = Enero;-> WHEN 2 THEN SET mes_s = Febrero;-> WHEN 3 THEN SET mes_s = Marzo;-> WHEN 4 THEN SET mes_s = Abril;-> WHEN 5 THEN SET mes_s = Mayo;-> WHEN 6 THEN SET mes_s = Junio;-> WHEN 7 THEN SET mes_s = Julio;-> WHEN 8 THEN SET mes_s = Agosto;-> WHEN 9 THEN SET mes_s = Septiembre;-> WHEN 10 THEN SET mes_s = Octubre;-> WHEN 11 THEN SET mes_s = Noviembre;-> WHEN 12 THEN SET mes_s = Diciembre;-> END CASE;-> SET fecha_f = CONCAT(dia_s, ,DAY(fecha), ,mes_s, ,YEAR(fecha));-> RETURN fecha_f;-> END-> //


mysql> select orderid,detalle(orderdate) from orders limit 5//+---------+----------------------+| orderid | detalle(orderdate) |+---------+----------------------+| 10248 | Jueves 4 Julio 1996 |

| 10249 | Viernes 5 Julio 1996 || 10250 | Lunes 8 Julio 1996 || 10251 | Lunes 8 Julio 1996 || 10252 | Martes 9 Julio 1996 |+---------+----------------------+5 rows in set (0.00 sec)


36/68



CURSORES:

Antes de continuar revisando el uso de las funciones es importante adentrarnos en el uso de los cursores, donde uncursor funciona como un puntero a un registro. Con ellos se puede iterar sobre todos los registros de una tabla.Normalmente se usan para facilitar el diseo de funciones y procedimientos que modifican los datos de una tabla

El uso de cursores requiere varios pasos. Primero hay que declarar el cursor con:DECLARE nombre_cursor CURSOR FOR SELECT campo1,campo2...;

Se puede usar cualquier comando SELECT. Despus hay que activar el cursor con:OPEN nombre_cursor;

A partir de ese momento se puede usar el comando FETCH:FETCH nombre_cursor INTO var1, var2, ...;

De esta manera, la primera columna del registro al que apunta nombre_cursor se almacena en la variable var1, la

segunda en var2, y as sucesivamente. Estas variables tienen que haber sido declaradas con anterioridad, y han deser del tipo correcto.

Para saber cundo se han acabado de leer los registros correspondientes al SELECT del cursor, MySQL emite elerror 1329 (no data to fetch) que corresponde a SQLSTATE 02000. Este error no se puede evitar, pero se puedecapturar con un handler. De esta manera, siempre que usemos cursores tendremos que definir el handlercorrespondiente. Normalmente se usa NOT FOUND como condicin, donde se engloban todos los SQLSTATE0snnn.

El cursor se puede cerrar con:CLOSE nombre_cursor;

LIMITACIONES Los cursores son de solo lectura, no se pueden modificar los datos a los que apunta Los cursores solo pueden avanzar, de manera que los datos deber ser procesados en el orden en el que han

sido proporcionados por el servidor. No se puede cambiar la estructura de las tablas mientras se est leyendo datos con un cursor.

EJEMPLO

Crear una funcin que permita obtener las ventas de un empleado para un ao especfico.

Lo primero que haremos es crear la consulta que nos permitir obtener la informacin que necesitamos para un

ao especifico y para un empleado especifico, sabiendo que despus haremos la modificacin pertinente para quenuestra funcin sea genrica, vayamos entonces a la a la consulta.

mysql> SELECT sum(unitprice*quantity*(1-discount)) as valor-> FROM employees e JOIN orders o-> ON e.employeeid = o.employeeid-> JOIN orderdetails od ON o.orderid = od.orderid-> WHERE YEAR(orderdate) = 1996-> GROUP BY e.employeeid-> HAVING e.employeeid = 1


37/68



-> ;+-------------------+| valor |+-------------------+| 35764.51521332211 |+-------------------+1 row in set (0.18 sec)

Hecho lo anterior y una vez que nos aseguramos que funciona bien nuestra consulta, realizaremos la creacin denuestra funcin para eso determinaremos que parmetros necesitamos de entrada y que valores necesitamos desalida, en este caso nuestros parmetro de entrada son el id del empleado y el ao en el cual requerimos las ventasy nuestro valor de salida seria la venta total obtenida con dichos parmetros de entrada.

mysql> CREATE FUNCTION vtaemp( idemp INT, anio INT ) RETURNS FLOAT-> BEGIN-> Declare valor FLOAT;-> Declare rs CURSOR FOR-> SELECT sum(unitprice*quantity*(1-discount))

-> FROM employees e JOIN orders o-> ON e.employeeid = o.employeeid-> JOIN orderdetails od ON o.orderid = od.orderid-> WHERE YEAR(orderdate) = anio-> GROUP BY e.employeeid-> HAVING e.employeeid = idemp;-> OPEN rs;-> FETCH rs INTO valor;-> CLOSE rs;-> Return valor;-> END-> //


mysql> select vtaemp(1,1996)//+----------------+| vtaemp(1,1996) |+----------------+| 35765 |+----------------+1 row in set (0.04 sec)

Utilicemos la funcin anterior para obtener un reporte de ventas, para cada empleado, para los aos de 1996 y1997

mysql> select firstname,vtaemp(employeeid,1996) as '1996',

-> vtaemp(employeeid,1997) as '1997'-> from employees//+-----------+-------+--------+| firstname | 1996 | 1997 |+-----------+-------+--------+| Nancy | 35765 | 93148 || Andrew | 21757 | 70444 || Janet | 18224 | 108026 || Margaret | 49945 | 128810 || Steven | 18384 | 30716 |


38/68



| Michael | 16643 | 43126 || Robert | 15232 | 60471 || Laura | 22240 | 56033 || Anne | 9895 | 26310 |+-----------+-------+--------+9 rows in set (0.11 sec)

MANEJO DE ERRORES

Durante la ejecucin de instrucciones SQL dentro de un SP pueden producirse errores

SQL define un mecanismo de handlers para gestionar esos errores. Un handler debe serdefinido despus de la declaracin de variables, cursores y condiciones.

Un handler en MySQL es invocado cuando se da un evento o condicin definida con anterioridad.

SINTAXIS:

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:puede ser CONTINUE o EXIT.El primero significa que la ejecucin del programa continuar a pesar del error.EXIT significa salir del bloque BEGIN-END.

Este evento est siempre asociado con un condition_value, pero puedes tener tres formas de definir el error:

Como cdigo de error de MySQL DECLARE CONTINUE HANDLER FOR1062SETduplicate_key=1;

Como cdigo SQLSTATE ANSI-standard DECLARE CONTINUE HANDLER FORSQLSTATE '23000'SETduplicate_key=1;

Un cdigo de error SQLSTATE es definido por ANSI estndar y son independientes de la base de datos, lo quesignifica que deberas tener el mismo valor de error para cualquier base de datos ANSI compatible.

Como nombre de condicin, ejemplo: SQLEXCEPTION, SQLWARNING y NOT FOUND.

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SETierr = 1;

DECLARE CONTINUE HANDLER FOR SQLWARNING SETierr = 1;

SQLWARNINGes una abreviacin para todos los cdigos SQLSTATE que comienzan con 01.

NOT FOUNDes una abreviacin para todos los cdigos SQLSTATE que comienzan con 02.SQLEXCEPTIONes una abreviacin para todos los cdigos SQLSTATE no tratados por SQLWARNING o NOT FOUND

ERRORES COMUNES EN MySQL Y CODIGOS SQLSTATE

Cdigo de errorMySQL

CdigoSQLSTATE

Mensaje de error


39/68



1011 HY000 Error on delete of %s (errno: %d)

1021 HY000 Disk full (%s); waiting for someone to free some space . . .

1022 23000 Cant write; duplicate key in table %s

1027 HY000 %s is locked against change

1036 HY000 Table %s is read only

1048 23000 Column %s cannot be null

1062 23000 Duplicate entry %s for key %d

1099 HY000 Table %s was locked with a READ lock and cant be updated

1100 HY000 Table %s was not locked with LOCK TABLES

1104 42000 The SELECT would examine more than MAX_JOIN_SIZE rows; check yourWHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_WHERE anduse SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT isokay

1106 42000 Incorrect parameters to procedure %s

1114 HY000 The table %s is full

1150 HY000 Delayed insert thread couldnt get requested lock for table %s

1165 HY000 INSERT DELAYED cant be used with table %s because it is locked withLOCKTABLES

1242 21000 Subquery returns more than 1 row

1263 22004 Column set to default value; NULL supplied to NOT NULL column %s at row%ld

1264 22003 Out of range value adjusted for column %s at row %ld

1265 1000 Data truncated for column %s at row %ld

1312 0A000 SELECT in a stored program must have INTO

1317 70100 Query execution was interrupted

1319 42000 Undefined CONDITION: %s

1325 24000 Cursor is already open

1329 2000 No data to FETCH

1336 1337 42000 42000 USEis not allowed in a stored program Variable or condition declaration after


40/68



cursor or handler declaration

1338 42000 Cursor declaration after handler declaration

1339 20000 Case not found for CASE statement

1348 1357 HY000 HY000 Column %s is not updatable Cant drop a %s from within another storedroutine

1358 1362 HY000 HY000 GOTO is not allowed in a stored program handler

1363 HY000 Updating of %s row is not allowed in %s trigger There is no %s row in %strigger

EJEMPLOSi ejecutamos la funcin de ventas que elaboramos en lneas anteriores y damos como parmetro un ao en el cualalguno de los empleados no vendi, entonces nos dar el error que se muestra a continuacin.

mysql> select firstname,vtaemp(employeeid,1995),-> vtaemp(employeeid,1996)-> from employees;

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

mysql> CREATE FUNCTION vtaemp( idemp INT, anio INT ) RETURNS INT-> BEGIN-> Declare valor INT;-> DECLARE err INT DEFAULT 0;-> Declare rs CURSOR FOR-> SELECT sum(unitprice*quantity*(1-discount))-> FROM employees e JOIN orders o

-> ON e.employeeid = o.employeeid-> JOIN orderdetails od ON o.orderid = od.orderid-> WHERE YEAR(orderdate) = anio-> GROUP BY e.employeeid-> HAVING e.employeeid = idemp;-> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET err = 1;-> OPEN rs;-> FETCH rs INTO valor;-> CLOSE rs;->-> IF err = 1 THEN-> set VALOR = 0;-> END IF;-> Return valor;-> END-> //


mysql> select firstname,vtaemp(employeeid,1995),-> vtaemp(employeeid,1996)-> from employees//

+-----------+-------------------------+-------------------------+| firstname | vtaemp(employeeid,1995) | vtaemp(employeeid,1996) |


41/68



+-----------+-------------------------+-------------------------+| Nancy | 0 | 35765 || Andrew | 0 | 21757 || Janet | 0 | 18224 || Margaret | 0 | 49945 || Steven | 0 | 18384 || Michael | 0 | 16643 || Robert | 0 | 15232 || Laura | 0 | 22240 || Anne | 0 | 9895 |+-----------+-------------------------+-------------------------+

Elabore las siguientes funciones y la instruccin SQL que genere lo siguiente:

EJERCICIOS DE CLASE1.- Obtenga un reporte de las compras de cada cliente para un ao especfico.2.- Obtenga un reporte que obtenga el IVA que se pag en los aos 1995,96 y la diferencia entre dichos aos.3.- Obtenga un reporte de los gastos de envi generados por cada una de las paqueteras en enero de 1995 y

enero de 1996

USO DE CURSORES CON CICLOS

Generemos una funcin que permita calcular el monto de la comisin que se le dar a un empleado durante unao especifico, el % de la comisin est dada por la siguiente tabla:

RANGO PORCENTAJE DE COMISION

VENTA VENTA 10000 16%

mysql> CREATE FUNCTION comision( nomemp varchar(10), anio INT ) RETURNS FLOAT-> BEGIN-> DECLARE VALOR FLOAT;-> DECLARE COMISION FLOAT DEFAULT 0;-> DECLARE BAN INT DEFAULT 1;-> Declare rs CURSOR FOR-> SELECT SUM(QUANTITY*UNITPRICE*(1-DISCOUNT))-> FROM EMPLOYEES E JOIN ORDERS O-> ON E.EMPLOYEEID = O.EMPLOYEEID-> JOIN ORDERDETAILS OD-> ON O.ORDERID = OD.ORDERID-> WHERE YEAR(ORDERDATE) = anio-> GROUP BY MONTH(ORDERDATE),FIRSTNAME-> HAVING FIRSTNAME = nomemp;->-> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET BAN = 0;-> OPEN rs;->-> REPEAT-> FETCH rs INTO VALOR;-> IF VALOR SET COMISION = COMISION + (VALOR*.08);-> ELSEIF VALOR SET COMISION = COMISION + (VALOR*.12);-> ELSEIF VALOR > 10000 THEN


42/68



-> SET COMISION = COMISION + (VALOR*.16);-> END IF;-> UNTIL BAN = 0 END REPEAT;->-> CLOSE rs;->

-> Return COMISION;-> END-> //


mysql> SELECT FIRSTNAME,COMISION(FIRSTNAME,1997) AS COMISION FROM EMPLOYEES//+-----------+-----------------+| FIRSTNAME | COMISION |+-----------+-----------------+| Nancy | 15272.521484375 || Andrew | 9670 || Janet | 18309.83984375 || Margaret | 21591.158203125 || Steven | 3060.0002441406 |

| Michael | 5523 || Robert | 7577.1196289063 || Laura | 6261.4404296875 || Anne | 2893.3200683594 |+-----------+-----------------+9 rows in set (0.05 sec)

MANEJO DE ERRORES EN PROCEDIMIENTOS ALMACENADOS

Generemos un procedimiento almacenado con errores que revise y actualice las existencias

mysql> CREATE PROCEDURE REV_EXIS( IDPROD INT, CANT INT)-> BEGIN-> DECLARE exis INT DEFAULT 0;-> DECLARE err INT DEFAULT 0;-> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err = 1;-> DECLARE CONTINUE HANDLER FOR SQLWARNING SET err = 1;-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET err = 1;->-> SELECT unitsinstock INTO EXIS FROM products-> WHERE productid = IDPROD;->-> IF EXIS > CANT THEN-> UPDATE products SET unitinstock = unitsinstock-CANT-> WHERE productid= IDPROD;-> END IF;->

-> IF err = 1 THEN-> SET @ERROR = OCURRIO UN ERROR;-> ELSE-> COMMIT;-> END IF;-> END//


mysql> SELECT PRODUCTID, UNITSINSTOCK FROM PRODUCTS LIMIT 5//+-----------+--------------+


43/68



| PRODUCTID | UNITSINSTOCK |+-----------+--------------+| 1 | 20 || 2 | 17 || 3 | 13 || 4 | 53 || 5 | 0 |+-----------+--------------+5 rows in set (0.00 sec)

mysql> CALL REV_EXIS(1,3)//Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @ERROR//+------------------+| @ERROR |+------------------+| OCURRIO UN ERROR |+------------------+

1 row in set (0.00 sec)

mysql> SELECT PRODUCTID, UNITSINSTOCK FROM PRODUCTS LIMIT 5//+-----------+--------------+| PRODUCTID | UNITSINSTOCK |+-----------+--------------+| 1 | 20 || 2 | 17 || 3 | 13 || 4 | 53 || 5 | 0 |+-----------+--------------+5 rows in set (0.00 sec)

BORREMOS Y HAGAMOS LA CORRECCION PERTINENTE

mysql> DROP PROCEDURE REV_EXIS//Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE REV_EXIS( IDPROD INT, CANT INT)-> BEGIN-> DECLARE exis INT DEFAULT 0;-> DECLARE err INT DEFAULT 0;->-> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err = 1;-> DECLARE CONTINUE HANDLER FOR SQLWARNING SET err = 1;-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET err = 1;

->-> SELECT unitsinstock INTO EXIS FROM products-> WHERE productid = IDPROD;->-> IF EXIS > CANT THEN-> UPDATE products SET unitsinstock= unitsinstock-CANT-> WHERE productid= IDPROD;-> END IF;-> IF err = 1 THEN-> ROLLBACK;


44/68



-> ELSE-> COMMIT;-> END IF;-> END//


mysql> CALL REV_EXIS(1,3)//Query OK, 1 row affected (0.05 sec)

mysql> SELECT PRODUCTID, UNITSINSTOCK FROM PRODUCTS LIMIT 5//+-----------+--------------+| PRODUCTID | UNITSINSTOCK |+-----------+--------------+| 1 | 17 || 2 | 17 || 3 | 13 || 4 | 53 || 5 | 0 |+-----------+--------------+

5 rows in set (0.00 sec)

ELABORES UN PROCEDIMIENTO DONDE INSERTEMOS UN DETALLE DE VENTA Y HECHO ESTO ACTUALICEMOS LAEXISTENCIA

Hagamos las correcciones pertinentes al procedimiento que revisara las existencias

mysql> CREATE PROCEDURE REV_EXISTENCIA( IDPROD INT, CANT INT, OUT ERROR INT )-> BEGIN-> DECLARE exis INT DEFAULT 0;-> DECLARE err INT DEFAULT 0;->-> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err = 1;

-> DECLARE CONTINUE HANDLER FOR SQLWARNING SET err = 1;-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET err = 1;->-> SELECT unitsinstock INTO EXIS FROM products-> WHERE productid = IDPROD;->-> IF EXIS > CANT THEN-> UPDATE products SET unitsinstock= unitsinstock-CANT-> WHERE productid= IDPROD;-> IF err = 1 THEN-> SET ERROR = 1;-> ROLLBACK;-> ELSE-> COMMIT;-> END IF;-> ELSE-> SET ERROR = 1;-> END IF;-> END-> //


mysql> CREATE PROCEDURE ins_detalle


45/68



-> ( idord INT, idpro INT, precio DECIMAL(5,2), cant INT, des FLOAT(3,3))-> BEGIN-> DECLARE err INT DEFAULT 0;-> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err = 1;-> DECLARE CONTINUE HANDLER FOR SQLWARNING SET err = 1;-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET err = 1;->-> CALL REV_EXISTENCIA(idpro, cant, @ERROR );-> IF @ERROR = 1 THEN-> set @otroerror = 'ERROR DE EXISTENCIA';-> ELSE-> INSERT INTO orderdetails VALUES(idord,idpro,precio,cant,des);-> IF err = 0 THEN-> set @otroerror = 'INSERT DETALLE';-> COMMIT;-> ELSE-> set @otroerror = 'ERROR DEL DETALLE';-> ROLLBACK;-> END IF;

-> END IF;-> END-> //


mysql> call ins_detalle(10248,1,10,20,0)//Query OK, 0 rows affected (0.00 sec)

mysql> select productid,unitsinstock from products limit 5//+-----------+--------------+| productid | unitsinstock |+-----------+--------------+| 1 | 15 || 2 | 17 || 3 | 13 || 4 | 53 || 5 | 0 |+-----------+--------------+5 rows in set (0.00 sec)

mysql> select * from orderdetails limit 10//+---------+-----------+-----------+----------+----------+| OrderID | ProductID | UnitPrice | Quantity | Discount |+---------+-----------+-----------+----------+----------+| 10248 | 11 | 14 | 12 | 0 || 10248 | 42 | 9.8 | 10 | 0 || 10248 | 72 | 34.8 | 5 | 0 |

| 10249 | 14 | 18.6 | 9 | 0 || 10249 | 51 | 42.4 | 40 | 0 || 10250 | 41 | 7.7 | 10 | 0 || 10250 | 51 | 42.4 | 35 | 0.15 || 10250 | 65 | 16.8 | 15 | 0.15 || 10251 | 22 | 16.8 | 6 | 0.05 || 10251 | 57 | 15.6 | 15 | 0.05 |+---------+-----------+-----------+----------+----------+10 rows in set (0.00 sec)


46/68



mysql> select @otroerror//+---------------------+| @otroerror |+---------------------+| ERROR DE EXISTENCIA |+---------------------+1 row in set (0.00 sec)

mysql> call ins_detalle(10248,1,10,2,0)//Query OK, 0 rows affected (0.05 sec)

mysql> select productid,unitsinstock from products limit 5//+-----------+--------------+| productid | unitsinstock |+-----------+--------------+| 1 | 13 || 2 | 17 || 3 | 13 || 4 | 53 |

| 5 | 0 |+-----------+--------------+5 rows in set (0.00 sec)

mysql> select * from orderdetails limit 10//+---------+-----------+-----------+----------+----------+| OrderID | ProductID | UnitPrice | Quantity | Discount |+---------+-----------+-----------+----------+----------+| 10248 | 1 | 10 | 2 | 0 || 10248 | 11 | 14 | 12 | 0 || 10248 | 42 | 9.8 | 10 | 0 || 10248 | 72 | 34.8 | 5 | 0 || 10249 | 14 | 18.6 | 9 | 0 || 10249 | 51 | 42.4 | 40 | 0 || 10250 | 41 | 7.7 | 10 | 0 || 10250 | 51 | 42.4 | 35 | 0.15 || 10250 | 65 | 16.8 | 15 | 0.15 || 10251 | 22 | 16.8 | 6 | 0.05 |+---------+-----------+-----------+----------+----------+10 rows in set (0.00 sec)

mysql> select @otroerror//+----------------+| @otroerror |+----------------+| INSERT DETALLE |+----------------+

1 row in set (0.00 sec)

EJERCICIOS EN CLASE

1.- ELABORAR UN ABC DE PAQUETERIAS QUE CAPTURE LOS POSIBLES ERRORES QUE SE PUEDANGENERAR DENTRO DEL MISMO.

2.- GENERAR UN PROCEDIMIENTO ALMACENADO QUE VALIDE QUE UN EMPLEADO NO TENGA MAS DE 5EMPLEADOS A SU CARGO, CAPTURE LOS ERRORES.


47/68



3.- OBTENER UN PROCEDIMIENTO ALMACENADO QUE APLIQUE UN 10% DE DESCUENTO AL PRODUCTOQUE SE VENDIO SI LA VENTA DE ESTE EXCEDE LOS 800 PESOS.

4.- AGREGUE UN CAMPO A LA TABLA EMPLEADO DENOMINADO COMISION QUE PERMITA ALMACENARLA COMISION DEL MES DEL EMPLEADO, EL CUAL CORRESPONDE A UN 15% DE LA VENTA TOTALMENSUAL Y SE ACTUALIZARA CON UN PROCEDIMIENTO ALMACENADO.

TRIGGERS (DISPARADORES)

Un trigger es un bloque PL/SQL asociado a una tabla, que se ejecuta como consecuencia de una determinadainstruccin SQL (una operacin DML: INSERT, UPDATE o DELETE) sobre dicha tabla.

Un disparador es un objeto con nombre dentro de una base de datos, el cual se asocia con una tabla y se activacuando ocurre en sta un evento en particular.

SINTAXIS:

CREATE TRIGGER nombre_disp evento_dispON nombre_tabla FOR EACH ROW sentencia_disp

El disparador quedara asociado a la tabla nombre_tabla, la cual debe ser una tabla permanente, no puede ser unatabla temporal ni una vista.

momento_dispes el momento en que el disparador entra en accin. Puede ser BEFORE(antes) o AFTER(despus),para indicar que el disparador se ejecute antes o despus que la sentencia que lo activa.

evento_disp indica la clase de sentencia que activa al disparador. Puede ser INSERT, UPDATE, o DELETE. Porejemplo, un disparador BEFOREpara sentencias INSERTpodra utilizarse para validar los valores a insertar.

No puede haber dos disparadores en una misma tabla que correspondan al mismo momento y sentencia. Porejemplo, no se pueden tener dos disparadores BEFOREUPDATE. Pero s es posible tener los disparadores BEFOREUPDATEy BEFORE INSERTo BEFORE UPDATEy AFTER UPDATE.

sentencia_disp es la sentencia que se ejecuta cuando se activa el disparador. Si se desean ejecutar mltiplessentencias, deben colocarse entre BEGIN ... END, el constructor de sentencias compuestas. Esto adems posibilitaemplear las mismas sentencias permitidas en rutinas almacenadas.

Nota: Actualmente, los disparadores no son activados por acciones llevadas a cabo en cascada por las

restricciones de claves extranjeras.

BORRAR DISPARADORDROP TRIGGER [nombre_esquema.]nombre_disp

Nota: cuando se actualice desde una versin anterior de MySQL 5 a MySQL 5.0.10 o superior, se deben eliminartodos los disparadores antes de actualizar y volver a crearlos despus, o DROP TRIGGER no funcionar luego de laactualizacin.

EJEMPLO:


48/68



mysql> create table cuenta( numcta int, monto decimal(10,2)) engine = innodb;Query OK, 0 rows affected (0.16 sec)mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON cuenta

-> FOR EACH ROW SET @sum = @sum + NEW.monto;

Definicin:

La palabra clave BEFOREindica el momento de accin del disparador. En este caso, el disparador debera activarseantes de que cada registro se inserte en la tabla. La otra palabra clave posible aqu es AFTER.

La palabra clave INSERTindica el evento que activar al disparador. En el ejemplo, la sentencia INSERT causar laactivacin. Tambin pueden crearse disparadores para sentencias DELETE y UPDATE.

La sentencia siguiente, FOR EACH ROW, define lo que se ejecutar cada vez que el disparador se active, lo cualocurre una vez por cada fila afectada por la sentencia activadora. En el ejemplo, la sentencia activada es un sencilloSET que acumula los valores insertados en la columna monto. La sentencia se refiere a la columna comoNEW.monto, lo que significa el valor de la columnamonto que ser insertado en el nuevo registro.

mysql> SET @sum = 0;Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO cuenta VALUES(137,14.98),(141,1937.50),(97,-100.00);Query OK, 3 rows affected (0.03 sec)Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT @sum AS 'Monto Total Insertado';+-----------------------+| Monto Total Insertado |+-----------------------+

| 1852.48 |+-----------------------+1 row in set (0.00 sec)

LIMITACIONES:

El disparador no puede referirse a tablas directamente por su nombre, incluyendo la misma tabla a la que estasociado. Sin embargo, se pueden emplear las palabras clave OLD y NEW.

OLD se refiere a un registro existente que va a borrarse o que va a actualizarse antes de que esto ocurra.

NEW se refiere a un registro nuevo que se insertar o a un registro modificado luego de que ocurre la

modificacin.

El disparador no puede invocar procedimientos almacenados utilizando la sentencia CALL. El disparador no puede utilizar sentencias que inicien o finalicen una transaccin, tal como START

TRANSACTION, COMMIT, o ROLLBACK. En un disparador para INSERT, solo puede utilizarse NEW.nom_col; ya que no hay una versin anterior del

registro. En un disparador para DELETE slo puede emplearse OLD.nom_col, porque no hay un nuevo registro.


49/68



En un disparador para UPDATE se puede emplear OLD.nom_colpara referirse a las columnas de un registroantes de que sea actualizado, y NEW.nom_colpara referirse a las columnas del registro luego de actualizarlo.

Una columna precedida por OLDes de slo lectura. Es posible hacer referencia a ella pero no modificarla. Una columna precedida por NEWpuede ser referenciada si se tiene el privilegio SELECT sobre ella. En un disparador BEFORE, tambin es posible cambiar su valor con SET NEW.nombre_col = valor si se tiene el

privilegio de UPDATE sobre ella. Esto significa que un disparador puede usarse para modificar los valores antesque se inserten en un nuevo registro o se empleen para actualizar uno existente.

EJEMPLO:

Creemos un trigger que valide la actualizacin del monto en la tabla cuenta, dicho monto no deber ser menor acero, dicha validacin se deber realizar antes de actualizar.

mysql> delimiter //mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON cuenta

-> FOR EACH ROW-> BEGIN-> IF NEW.monto < 0 THEN

-> SET NEW.monto = 0;-> ELSEIF NEW.monto > 100 THEN-> SET NEW.monto = 100;-> END IF;-> END;//


Consultemos la tabla cuenta

mysql> select * from cuenta//+--------+---------+| numcta | monto |+--------+---------+

| 137 | 14.98 || 141 | 1937.50 || 97 | -100.00 |+--------+---------+3 rows in set (0.00 sec)

Realicemos la actualizacin del monto donde la cantidad sea menor a cero para alguna de las cuentas

mysql> UPDATE cuenta set monto = -1000 where numcta = 137//Query OK, 1 row affected (0.07 sec)Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from cuenta//+--------+---------+| numcta | monto |+--------+---------+| 137 | 0.00 || 141 | 1937.50 || 97 |

material del curso(mysql)

Documents