bd ii tema i procedimientos almacenados

Universidad Bolivariana de Venezuela Direccin General Acadmica PFG. Informtica para la Gestin Social Unidad Curricular: Bases de Datos II Tema I

Tema 1: SMBD Y SQL AVANZADO 1.1. SMBD: Arquitectura de 3 esquemas. Lenguajes e interfaces del SMBD. Entorno del sistema de base de datos. La arquitectura de 3 esquemas es una propuesta para alcanzar y visualizar estas caractersticas:

El objetivo de esta arquitectura es separar las aplicaciones de usuario de la Base de Datos fsica. Se definen esquemas en tres niveles Niveles de la arquitectura de 3 esquemas (Arquitectura ANSI/SPARC): Nivel interno: o Tiene un esquema interno que describe la estructura de almacenamiento de la BD o Usa un modelo de datos de bajo nivel (registros, apuntadores, ndices, etc.) o Se ocupa de organizar y almacenar los datos fsicamente o No relacional. Nivel conceptual: o Tiene un esquema conceptual que describe la estructura de la BD completa. o Este esquema oculta los detalles de almacenamiento. o Se concentra en describir las entidades, tipos de datos, relaciones, operaciones de usuario y restricciones de los datos. o Se puede usar un modelo de datos de alto nivel o uno de implementacin. Vista Conceptual: Representacin de TODO el contenido de la Base de Datos.

1


Representacin abstracta de los datos. NO coincide con la representacin fsica. NO coincide con vista externa. Vista formada por el conjunto de REGISTROS CONCEPTUALES que definen entidades y relaciones entre entidades. Registros conceptuales no han de coincidir con registros externos o registros internos. o Vista definida mediante ESQUEMA CONCEPTUAL. Esquema Conceptual: o Definicin de cada tipo de registro conceptual. o Definido mediante DDL CONCEPTUAL (Data Definition Language). o Definicin del contenido de informacin. o No se han de incluir relaciones con el nivel interno (estructura de almacenamiento, tcnicas de acceso, campos y registros fsicos). o Se especifican controles de integridad y de seguridad. Nivel externo o de vistas: o Tiene varios esquemas externos o vistas. o Cada vista describe la parte de la BD que le interesa a un grupo de usuarios, ocultando el resto de la BD. o Se puede usar un modelo de datos de alto nivel o uno de implementacin. o Se ocupa de la forma en que los usuarios individuales perciben los datos. o o o o Nivel de usuarios individuales donde cada usuario tendr ciertos lenguajes a su disposicin: Usuario Final Lenguaje de consulta (cuarta generacin): SQL Lenguaje de la aplicacin (mens, comandos, sistemas, ventanas, etc.) Programador de aplicaciones o Lenguaje de consulta (cuarta generacin): SQL o Lenguaje de programacin de alto nivel (C, PL/I, COBOL, PASCAL...) DBA (Administrador de la base de Datos) o Todos los lenguajes utilizados por los anteriores usuarios.o o

Estos lenguajes han de incluir un sublenguaje de datos (DSL - Data SubLanguage): Encargado de interactuar con la Base de Datos Sublenguaje dentro del lenguaje de alto nivel (lenguaje anfitrin: C, COBOL, BASIC, etc.) DSL proporciona acceso a los datos o Lenguaje anfitrin proporciona las estructuras de control y las variables DSL - Data SubLanguage: Dividido en dos partes: DDL -Data Definition Language Definicin/declaracin de los objetos a la Base de Datos DML -Data Manipulation Language Manipulacin/procesamiento de los objetos definidos en la Base de Datoso

2


Nota: El SGBD transforma cada solicitud expresada en trminos del esquema externo a trminos de esquema conceptual y despus en una solicitud del esquema interno, que se procesar en la BD. 1.3 Procedimientos almacenados en el esquema: creacin de funciones y procedimientos PSM. Instrucciones simples en PSM. Instrucciones de bifurcacin. Consultas en PSM. Excepciones en PSM. BENEFICIOS DE LOS PROCEDIMIENTOS ALMACENADOS Los procedimientos almacenados y funciones ofrecen varios beneficios para el desarrollo de aplicaciones, distribucin y operacin: Sintaxis SQL ms flexible. Las rutinas almacenadas (procedimientos almacenados) pueden ser escritas utilizando extensiones para la sintaxis SQL, tales como composicin de sentencias y construcciones para el control de flujo, esto facilita la expresin de lgicas complejas. Capacidades para el manejo de errores. Una rutina almacenada puede crear manejadores de errores que son utilizados cuando surgen condiciones excepcionales. La ocurrencia de un error no necesitar causar la terminacin de la rutina sino que puede ser manejada apropiadamente. Conforme a estndares. La implementacin de MySQL de rutinas almacenadas conforme a la sintaxis estndar SQL. Rutinas escritas para MySQL deben ser razonablemente portables a otros servidores de base de datos que tambin sigan la sintaxis estndar SQL. Nota: aunque la implementacin esta basada sobre el estndar SQL, esta todava no es completa y tiene limitaciones. Por ejemplo, los cursores son de solo lectura (read-only) y no puede ser usada para modificar tablas. Los cursores tambin avanzan a travs del conjunto resultado un registro a la vez; es decir, ellos no permiten el libre desplazamiento. Se espera que estas restricciones sean removidas sobre la marcha. Encapsulacin y empaquetado del cdigo. Una rutina permite al cdigo que realiza una operacin sea almacenada una vez sobre el servidor y accedida desde mltiples aplicaciones. El cdigo no necesita ser incluido dentro de mltiples aplicaciones. Esto reduce la variacin potencial en como diferentes aplicaciones ejecutan la operacin. Menos reinvencin de la rueda". Una coleccin de rutinas almacenadas actan como una librera de soluciones a problemas. Los desarrolladores pueden utilizarlas en vez de tener que re-implementar el cdigo desde el principio. Las rutinas almacenadas tambin facilitan compartir el conocimiento y la experiencia. Un desarrollador con experiencia en SQL quien resuelve un problema dificultoso puede implementar la solucin en una rutina almacenada para ser utilizada por los desarrolladores con menos experticia. Separacin de la lgica. Factorizando hacia afuera la lgica de las operaciones especificas de la aplicacin en rutinas almacenadas reduce la complejidad de la lgica propia de la aplicacin y hace mas fcil su comprensin.

3


Fcil de mantener. Una nica copia de una rutina es ms fcil de mantener que una copia insertada dentro de cada aplicacin. La actualizacin de aplicaciones es ms fcil si todos utilizan una rutina en comn, debido a que si es necesario actualizar una sola rutina, no todas las aplicaciones que la usan. Reduccin de los requerimientos del ancho de banda en la red. Considere la ejecucin de la operacin de mltiples sentencias ejecutadas por un cliente sin el uso de una rutina almacenada: Cada sentencia y sus resultados cruzan la red, aun aquellos que calcular resultados intermedios. En cambio si la operacin es ejecutada dentro de una rutina almacenada, sentencias intermedias y los resultados son procesados enteramente sobre el servidor y no cruzaran la red. Esto mejora el desempeo y resulta en una menor contencin de recursos, particularmente para redes muy ocupadas o de bajo ancho de banda. (el beneficio potencial de este factor debe ser ponderado (medido) a travs del nmero de clientes y la cantidad de procesamiento del cliente que es movido hacia el servidor mediante el uso de rutinas almacenadas). Los clientes se benefician de las actualizaciones del servidor. La actualizacin del servidor (upgrades) mejoran el desempeo de las rutinas almacenadas que se ejecutan en el host. Esto mejora el desempeo para las aplicaciones clientes que usan las rutinas aun cuando las maquinas clientes no sean actualizadas. Mejor seguridad. Una rutina puede ser escrita para acceder datos sensibles sobre el definidor de la rutina para el invocador de la rutina, pero no retorna nada que el invocador no debera ver. Una rutina tambin puede ser utilizada para modificar tablas de manera segura, sin darle a los usuarios acceso directo a las tablas. Esto previene la realizacin de cambios posiblemente no seguros sobre ellos.

DIFERENCIAS ENTRE FUNCIONES Y PROCEDIMIENTOD La diferencia ms general entre procedimientos y funciones es que son invocados de forma diferente y son utilizadas para diferentes propsitos:

Un procedimiento no retorna un valor. En su lugar este es invocado con una sentencia CALL para realizar una operacin tales como la modificacin de una tabla o el procesamiento de registros recuperados. Una funcin es invocada dentro de una expresin y retorna un solo valor directamente al llamador para ser utilizado en la expresin. Es decir, una funcin es utilizada en expresiones de la misma forma como una constante, una funcin preconstruida, o una referencia a la columna de una tabla. No se puede invocar una funcin con una sentencia CALL, ni se puede invocar un procedimiento almacenado en una expresin.

La sintaxis para la creacin de rutinas difiere algo para los procedimientos y funciones: Los parmetros de procedimiento pueden ser definidos como de solo entrada, solo salida, o para ambos entrada y salida. Esto significa que un procedimiento puede pasar valores de regreso al llamador por la utilizacin de parmetros de salida. Estos valores pueden ser accedidos en sentencias que sigan la sentencia CALL. Las funciones solo tienen parmetros de entrada. Como resultado, aunque ambos4


los procedimientos y funciones pueden tener parmetros, la sintaxis de declaracin de parmetros de procedimiento difieren para las funciones. Las funciones retornan un valor, as que deben tener la clusula RETURNS en la defuncin de una funcin para indicar el tipo de datos del valor retornado. Adems, debe tener al menos una sentencia de RETURN dentro del cuerpo de la funcin para retornar un valor al llamador. RETURNS y RETURN no aparecen en la definicin de procedimientos.

Una extensin de MySQL para procedimientos almacenados (pero no para las funciones) es que un procedimiento puede generar un conjunto resultado, o incluso mltiples resultados, que el llamador procesa del mismo modo como el resultado producido por una sentencia SELECT. Sin embargo, los contenidos de tales resultados no pueden ser usados directamente en expresiones. EJEMPLOS: -- procedimientos y funciones USE plannude; CREATE PROCEDURE rect_area (width INT, height INT) SELECT width * height AS area;

CREATE FUNCTION circle_area (radius FLOAT) RETURNS FLOAT RETURN PI() * radius * radius; CALL rect_area(5,5); SELECT circle_area(5.5) as area;

-- Sentencias compuestas use plannude; delimiter // CREATE PROCEDURE world_record_count ()

5


BEGIN SELECT 'Cooperativas', COUNT(*) FROM cooperativa; SELECT 'Datos_nude', COUNT(*) FROM dato_nude; SELECT 'Detalle_obra', COUNT(*) FROM detalle_obra; END; // delimiter ;

-- Sentencias Compuestas drop procedure bloque; delimiter // CREATE PROCEDURE bloque() BEGIN inner_block: BEGIN IF DAYNAME(NOW()) = 'Wednesday' THEN LEAVE inner_block; END IF; SELECT 'Hoy no es Wednesday'; END inner_block; select dayname(now()); END; // delimiter ; CALL bloque();

-- Declaracin de Parmetros use plannude; drop function impuesto;

6


CREATE FUNCTION impuesto (costo DECIMAL(10,2), tasa DECIMAL(10,2)) RETURNS DECIMAL(10,4) RETURN costo * tasa; SELECT impuesto(1000,0.10) AS 'Impuesto a Pagar';

-- Declaracin de Parametros use plannude; drop procedure param_test; delimiter // CREATE PROCEDURE param_test (IN p_in INT, OUT p_out INT, INOUT p_inout INT) BEGIN SELECT p_in, p_out, p_inout; SET p_in = 100, p_out = 200, p_inout = 300; END; // delimiter ; CALL param_test(100,200,300);

-- Variables en Procedimientos Almacenados use plannude; drop procedure variables; delimiter //

7


CREATE PROCEDURE variables() BEGIN --Asignacin de valores a variables con SET --DECLARE var1, var2, var3 INT; --SET var1 = 1, var2 = 2; --SET var3 = var1 + var2; -- Asignacin de variables con SELECT ... INTO DECLARE nombre_variable CHAR(50); DECLARE cant_variable INT; SELECT nombre_cooperativa, cant_masculino INTO nombre_variable, cant_variable FROM cooperativa WHERE nombre_cooperativa = 'SUCRE'; SELECT nombre_variable, cant_variable; END; // delimiter ; CALL variables();

1.2 Cursor: concurrentes.

modificacin

por

cursor.

Proteccin

contra

modificaciones

Cursores

8


Un cursor permite acceder un resultado (result set), una fila (registro) a la vez. Debido a su orientacin en filas, los cursores a menudo son utilizados en ciclos (loops) que localizan y procesan una fila dentro de cada iteracin del ciclo. La implementacin del cursor en MySQL tiene las siguientes propiedades: este es proporcionado para cursores de solo lectura (read-only); no pueden ser utilizados para modificar tablas. Los cursores solo avanzan a travs de un resultado fila por fila; no permiten ir hacia atrs y luego hacia delatante libremente. Para usar un cursor en una rutina almacenada, se debe escribir la sentencia DECLARE CURSOR que nombra el cursor y asociarlo con una sentencia SELECT que produce un resultado (result set):DECLARE cursor_name CURSOR FOR select_statement

Cada cursor declarado dentro de un bloque debe tener un nombre diferente. Para abrir el cursor, coloque su nombre en una sentencia OPEN. Esto ejecuta la sentencia SELECT asociada con el cursor:OPEN cursor_name

La sentencia FETCH localiza la prxima fila o registro de un resultado de un cursor abierto. La sentencia nombra el cursor y proporciona una lista de variables dentro del cual localizar los valores de las columnas de la fila. Aqu debe estar una variable por columna en el resultado (result set). Puede obtener valores en las variables locales o parmetros de rutina:FETCH cursor_name INTO var_name [, var_name] ...

La extraccin ocurre a menudo en un bucle con el propsito de que todos los registros en el resultado pueden ser procesados. Eso plantea un asunto: qu ocurre cuando usted llega al final del resultado? La respuesta es que una condicin de No Data ocurre (SQLSTATE 02000), que se puede detectar declarando a un manejador para esa condicin. Por ejemplo:DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' statement;

Cuando termine de utilizar un cursor, cierre este con una sentencia CLOSE:CLOSE cursor_name

Cerrar un cursor es opcional. Cualquier cursor declarado en un bloque es cerrado automticamente si estn abiertos cuando el bloque termina. El siguiente ejemplo muestra cmo usar cada una de las sentencias relacionadas con cursores anteriormente discutidas. El ejemplo declara un cursor llamado c y lo relaciona con una sentencia que selecciona registros para pases africanos en la tabla Pas. Tambin

9


declara un manejador de condicin que detecta el final del resultado. (La declaracin del manejador est vaca porque el nico propsito para el manejador es transferir el control hasta el final del cuerpo de su bloque.)-- cursores use plannude; drop procedure p8; delimiter // create procedure p8() BEGIN DECLARE row_count INT DEFAULT 0; DECLARE code_var CHAR(3); DECLARE name_var CHAR(52); DECLARE c CURSOR FOR SELECT nombre_cooperativa,municipio FROM cooperativa WHERE nombre_cooperativa = 'SUCRE'; OPEN c; BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END; LOOP FETCH c INTO code_var, name_var; SET row_count = row_count + 1; END LOOP; END; CLOSE c; SELECT 'number of rows fetched =', row_count; END; // delimiter ; CALL p8();

El ejemplo anterior usa un bloque anidado debido a que un manejador EXIT termina el bloque dentro el cual este es declarado, ni el loop dentro el cual la condicin ocurre. Si no ha sido utilizado el bloque anidado, el manejador transferir el control al final del bloque principal una vez ledo el final del resultado (result set), y las sentencias CLOSE y SELECT siguientes al ciclo nunca se ejecutara. El siguiente ejemplo muestra una forma de hacerlo:-- cursores

BEGIN

1


DECLARE exit_flag INT DEFAULT 0; DECLARE row_count INT DEFAULT 0; DECLARE code_var CHAR(3); DECLARE name_var CHAR(52); DECLARE c CURSOR FOR SELECT Code, Name FROM Country WHERE Continent = 'Africa'; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_flag = 1; OPEN c; fetch_loop: LOOP FETCH c INTO code_var, name_var; IF exit_flag THEN LEAVE fetch_loop; END IF; SET row_count = row_count + 1; END LOOP; CLOSE c; SELECT 'number of rows fetched =', row_count; END;

-- Estructuras de control (Conditional Testing) --IF expr

1


-- THEN statement_list -- [ELSEIF expr THEN statement_list] ... -- [ELSE statement_list] --END IF use plannude; drop procedure p10; delimiter // create procedure p10() BEGIN DECLARE val INT; IF val IS NULL THEN SELECT 'val is NULL'; ELSE SELECT 'val is not NULL'; END IF; -- Primer caso de CASE --CASE case_expr -- WHEN when_expr THEN statement_list -- [WHEN when_expr THEN statement_list] ... -- [ELSE statement_list] --END CASE CASE val WHEN 0 THEN SELECT 'val is 0'; WHEN 1 THEN SELECT 'val is 1'; ELSE SELECT 'val is not 0 or 1'; END CASE;

-- segundo caso de CASE --CASE1


-- WHEN when_expr THEN statement_list -- [WHEN when_expr THEN statement_list] ... -- [ELSE statement_list] --END CASE CASE WHEN val IS NULL THEN SELECT 'val is NULL'; WHEN val < 0 THEN SELECT 'val is less than 0'; WHEN val > 0 THEN SELECT 'val is greater than 0'; ELSE SELECT 'val is 0'; END CASE; END; // delimiter ; CALL p10();

-- Construccin de CICLOS (LOOP REPEAT WHILE) --LOOP

1


-- statement_list --END LOOP use plannude; drop procedure p11; delimiter // create procedure p11() BEGIN DECLARE i INT DEFAULT 0; my_loop: LOOP SET i = i + 1; IF i >= 10 THEN LEAVE my_loop; END IF; END LOOP my_loop; -------------------------------------------------------------------------REPEAT -- statement_list --UNTIL expr --END REPEAT -----------------------------------------------------------------------REPEAT SET i = i + 1; UNTIL i >= 10 END REPEAT;

-------------------------------------------------------------------------WHILE expr DO1


-- statement_list --END WHILE -----------------------------------------------------------------------WHILE i < 10 DO SET i = i + 1; END WHILE; ------------------------------------------------------------------------- Transferencias de Control -- LEAVE label -- ITERATE label -----------------------------------------------------------------------SET i = 0; my_loop: LOOP SET i = i + 1; IF i < 10 THEN ITERATE my_loop; ELSEIF i > 20 THEN LEAVE my_loop; END IF; SELECT 'i is between 10 and 20'; END LOOP my_loop; END; // delimiter ; CALL p11();

1.4 El ambiente SQL: Ambientes. Esquemas. Catlogos. Clientes y servidores en el ambiente SQL.

1


Esquemas El esquema de la base de datos. Una vez construido el esquema conceptual, el diseo de bases de datos obliga a realizar varias tareas previas a la construccin del esquema lgico global del sistema, tambin llamado esquema de bases de datos. Por el momento, basta saber que el esquema de la base de datos representa la descripcin de los datos de la base de datos, mientras que el esquema conceptual representaba a la realidad. La primera de las tareas necesarias es la identificacin de los datos requeridos, para obtener como resultado las partes del rea de aplicacin que deben representarse mediante datos, y en que forma deben presentarse stos a los usuarios. El siguiente paso es el anlisis de datos, consistente en la definicin y clasificacin de esos datos, su descripcin, que suele presentarse en forma de diccionario de datos, como una "metabase de datos". Por ltimo, debe hacerse la especificacin de los paquetes de entrada y de salida, correspondientes con los datos que deben introducir y obtener como respuesta los usuarios, segn sus necesidades. Las tres tareas habrn permitido obtener tres documentos sobre descripcin del rea de aplicacin, definicin y clasificacin de los datos y especificacin de las caractersticas de los diversos paquetes, respectivamente. Tomando como punto de partida estos tres elementos, se construye la especificacin de esquema de la base de datos, que responder al contenido total de la base de datos y las caractersticas de las vas de acceso requeridas a travs de estos datos. Frente al anlisis de datos, que es la definicin y clasificacin de los datos, el esquema se encarga de la utilizacin de esos datos. Instancias y esquemas Las bases de datos cambian en el tiempo cuando la informacin es insertada y eliminada. A la informacin recolectada almacenada en la base de datos en un momento particular se le llama instancia de la base de datos. Al diseo total de la base de datos se le llama esquema de la base de datos. Los esquemas son cambiados infrecuentemente. El concepto de esquema de base de datos y sus instancias pueden ser comprendidos por analoga a la de un programa escrito en un lenguaje de programacin. Un esquema de base de datos corresponde a las declaraciones de variables (junto con su asociado tipo de definicin) en un programa. Cada variable tiene un valor particular en un instante dado. Los valores de las variables en un programa en un punto en el tiempo corresponden a una instancia de un esquema de la base de datos. Los sistemas de bases de datos tienen varios esquemas, particionado acorde a niveles de abstraccin. El esquema fsico describe el diseo de la base de datos en el nivel fsico. El esquema lgico describe el diseo de la base de datos en el nivel lgico. Una base de datos puede tener varios esquemas a nivel de vista, algunas veces llamados sub-esquemas, que describen diferentes vistas de la base de datos. De todos estos, el esquema lgico es el ms importante, en trmino de sus efectos en los programas de aplicacin, dado que los programadores construyen aplicaciones utilizando el esquema lgico. El esquema fsico esta oculto en beneficio del esquema lgico, y puede ser modificado fcilmente sin afectar los programas de aplicacin. Los programas de aplicacin se dicen

1


que exhiben independencia fsica de datos si ellos no dependen del esquema fsico, y adems no necesita reescribirlo si el esquema fsico cambia. Arquitectura cliente-servidor Caractersticas principales de este tipo de arquitectura de cara a base de datos. Esta arquitectura se divide en dos partes claramente diferenciadas, la primera es la parte del servidor y la segunda la de un conjunto de clientes. Normalmente el servidor es una mquina bastante potente que acta de depsito de datos y funciona como un sistema gestor de base de datos (SGBD). Por otro lado los clientes suelen ser estaciones de trabajo que solicitan varios servicios al servidor. Ambas partes deben estar conectadas entre s mediante una red. Una representacin grfica de este tipo de arquitectura sera la siguiente.

Este tipo de arquitectura es la ms utilizada en la actualidad, debido a que es la ms avanzada y la que mejor ha evolucionado en estos ltimos aos. Podemos decir que esta arquitectura necesita tres tipos de software para su correcto funcionamiento: o Software de gestin de datos: Este software se encarga de la manipulacin y gestin de los datos almacenados y requeridos por las diferentes aplicaciones. Normalmente este software se aloja en el servidor. o Software de desarrollo: este tipo de software se aloja en los clientes y solo en aquellos que se dedique al desarrollo de aplicaciones. o Software de interaccin con los usuarios: Tambin reside en los clientes y es la aplicacin grfica de usuario para la manipulacin de datos, siempre claro a nivel usuario (consultas principalmente).

1


A parte de estos existen ms aplicaciones software para el correcto funcionamiento de esta arquitectura pero ya estn condicionados por el tipo de sistema operativo instalado, el tipo de red en la que se encuentra, etc.

BIBLIOGRAFIA CONSULTADA: MySQL 5 Certification Study Guide (2005) Paul DuBois, Stefan Hinz, Carsten Pedersen Sams Publishing Tema1. Introduccin. Bases de datos y usuarios de bases de datos Elmasri/Navathe 2002 Desarrollo Webhttp://www.desarrolloweb.com/articulos/arquitectura-cliente-servidor.html

Elaborado por: Prof. Juan C. Herrera R.

1

bd ii tema i procedimientos almacenados

Documents