1

TEMA II: PL/SQL

INDICE Pgina

2.1. Introduccin 2

2.2. Declaracin de variables 4

2.3. Estructuras de control 7

2.4. Uso de cursores 9

2.5. Control de errores o excepciones 13

2.6. Procedimientos 20

2.7. Funciones 23

2.8. Disparadores de bases de datos (triggers) 25

2.9. Ejercicios 29

2.10. Tablas de ejemplo 43

2

2.1. INTRODUCCION

El PL/SQL es un lenguaje de programacin de ORACLE. Es un lenguaje de

procesamiento procedimental que dispone de estructuras de programacin

parecidas a las de la mayora de los lenguajes de programacin.

Es una extensin del SQL y su inters est en poder mezclar la potencia de las

instrucciones de SQL con la flexibilidad de un lenguaje procedimental en un

mismo programa.

Estos programas pueden ser ejecutados directamente por las herramientas de

Oracle (bloques) o a partir de objetos de la base de datos (procedimientos

almacenados y paquetes).

Las ventajas del lenguaje PL/SQL son diversas:

Integracin de SQL: se pueden usar las instrucciones DML, las de control de transacciones y las funciones SQL prcticamente con la misma sintaxis.

Procesamiento procedimental: la gestin de variables y las estructuras de control (condiciones o bucles) incrementan las posibilidades de gestin de

los datos.

Funcionalidades adicionales: la gestin de cursores y el tratamiento de errores ofrecen nuevas posibilidades de procesamiento.

Mejora del rendimiento: se pueden agrupar varias instrucciones en una misma unidad (bloque) que slo dar lugar a un acceso a la base de datos

(en lugar de un acceso por instruccin).

Integracin de los productos de Oracle: los bloques o procedimientos PL/SQL son compilados por el motor de PL/SQL. Este motor est

integrado en el motor de la base de datos, as como determinadas

herramientas de Oracle: Oracle*Forms, Oracle*Report, Oracle*Graphics.

La unidad de programacin es el bloque de cdigo. Todos los programas de

PL/SQL estn conformados por bloques. Tpicamente, cada bloque lleva a

cabo una accin lgica en el programa.

Los subprogramas locales se definen en la parte declarativa de un bloque. Se

pueden anidar sub-bloques en la parte ejecutable y en la de manejo de

excepciones.

3

Estructura del bloque PL/SQL:

DECLARE

// DECLARACIONES: variables, tipos, y

// subprogramas de uso local.

BEGIN

// SENTENCIAS EJECUTABLES

// Instrucciones procedimentales + SQL.

EXCEPTION

Declaracin de excepciones;

END;

La seccin DECLARE, que permite declarar las variables que van a utilizarse

en el bloque PL/SQL slo es necesaria cuando es preciso definir variables en el

bloque. Del mismo modo, la seccin EXCEPTION slo existir si se van a tratar

situaciones de error o excepcin.

Las nicas instrucciones SQL permitidas en un bloque PL/SQL son las

instrucciones para manipulacin de datos como INSERT, UPDATE, DELETE y

SELECT y las instrucciones para control de transacciones.

Otras instrucciones de SQL como DROP, CREATE o ALTER no estn

permitidas.

4

2.2. DECLARACION DE VARIABLES

Las variables son zonas de memoria con nombre que permiten almacenar un

valor. Los valores pueden proceder de la base de datos o de clculos. La

declaracin de variables se realiza de la siguiente forma:

Nom-variable tipo-datos;

El nombre de la variable puede tener una longitud de hasta 30 caracteres y

puede estar compuesto de letras, nmeros y los caracteres $, _ #. No puede

ser una palabra reservada.

Al igual que en SQL, PL/SQL no distingue entre mayscula y minscula.

TIPOS DE DATOS

Podemos distinguir 2 grupos:

Escalares Compuestos a) Tipos de datos escalares

Los ms comunes son:

NUMBER (precisin,decimal): Dato numrico CHAR(n): Cadena alfanumrica de longitud fija, con n comprendido entre 1

y 32767.

VARCHAR(long mx): Cadena alfanumrica de longitud variable (hasta 32.767 caracteres).

BOOLEAN: Booleano. Valores TRUE, FALSE o NULL. DATE: Fecha formato DD-MES-AA (Ej. 26-SEP-99) TIMESTAMP: Permite ampliar el tipo de datos DATE almacenando

fracciones de segundo adems de los datos de ao, mes, da, hora, minuto

y segundo.

5

ROWID: 16 dgitos que identifican un registro Oracle internamente de forma unvoca (objeto, fichero, bloque, n de fila en el bloque). Lo define el

sistema. Permite localizar un registro conocido su rowid (Se usa en forms)

La asignacin de valores a variables se puede realizar de dos formas:

X:= 3

SELECT ENAME INTO nom-variable FROM ....

Declaraciones de ejemplo:

DECLARE

Fecha_alta DATE;

Nombre VARCHAR2(15) NOT NULL := PEPE;

Salario NUMBER(7);

Respuesta BOOLEAN DEFAULT FALSE;

Ejemplo: Aumentar el salario de los empleados del departamento 30 tanto

como el que menos cobra de todos los departamentos.

DECLARE

SAL_MIN EMP.SALARIO%TYPE;

BEGIN

SELECT MIN(SALARIO) INTO SAL_MIN FROM EMP;

UPDATE EMP SET SALARIO=SALARIO+SAL_MIN

WHERE DEPT_NO=30;

END;

/

Ejercicio: Incrementar en un 10% el salario de los empleados del hospital cuyo

salario sea menor que el salario medio.

6

b) Tipos de datos compuestos

%TYPE: Declara una variable con el mismo tipo de datos de otra variable o columna de tabla.

Ejemplo1:

EMPNO EMP.EMPNO%type;

Ejemplo2:

ANNO NUMBER(4);

MES ANNO%TYPE;

%ROWTYPE: Declara una variable registro con la misma estructura que una tabla o vista. Las columnas de la fila y los campos de la variable tendrn los

mismos nombres y tipos.

Ejemplo:

DECLARE

REG_EMP EMP%ROWTYPE;

.......

BEGIN

SELECT * INTO REG_EMP FROM EMP WHERE condicin;

END;

USO DE REGISTROS DEFINIDOS POR EL USUARIO

El usuario puede definir sus propios registros.

TYPE nom-registro IS RECORD

(campo-1 {tipo-variable | variable%TYPE | tabla.campo%TYPE |

tabla%ROWTYPE} [NOT NULL] ,

...........);

7

Para referirnos a cada campo: nom-registro.campo-1

Ejemplo:

DECLARE

TYPE REG_EMP IS RECORD

(EMPNO NUMBER(4) NOT NULL,

ENAME VARCHAR2(10),

OFICIO VARCHAR2(14);

.....

DEPTNO NUMBER(4));

2.3. ESTRUCTURAS DE CONTROL

Las estructuras de control permiten elegir la forma en que se van a ejecutar las

diferentes instrucciones.

a) Alternativa de una o varias ramas

IF cond-1 THEN

Sentencias;

ELSIF cond-2 THEN

Sentencias;

ELSE

Sentencias;

END IF;

8

b) Alternativa con CASE

CASE variable CASE WHEN valor1 THEN WHEN cond1 THEN sentencias; sentencias; WHEN valor2 THEN WHEN condr2 THEN sentencias; sentencias; WHEN valor3 THEN WHEN cond3 THEN sentencias; sentencias; ELSE ELSE sentencias; sentencias; END CASE; END CASE; c) Repetitivas

LOOP Sentencias; If condicin then EXIT; Sentencias; END LOOP;

LOOP Sentencias; EXIT WHEN condicin-salida; Sentencias; END LOOP;

WHILE cond LOOP Sentencias; END LOOP;

FOR variable IN [REVERSE] valor-inic .. valor-fin LOOP Sentencias; END LOOP;

9

2.4. USO DE CURSORES

Cuando una SELECT recupera ms de una fila de una tabla, es preciso definir

un cursor. El cursor es una zona de memoria de tamao fijo utilizada por el

motor de la BD de Oracle para analizar o interpretar cualquier comando SQL.

Los estados de ejecucin del comando se almacenan en el cursor.

Existen dos tipos de cursores:

cursor implcito: cursor SQL generado y gestionado por Oracle para cada comando SQL.

cursor explcito: cursor SQL generado y gestionado por el usuario para tratar una instruccin SELECT de varias lneas.

A continuacin comentaremos el segundo tipo.

El procedimiento para usar un cursor es el siguiente:

1) Declarar el cursor: DECLARE

CURSOR nom-cursor IS Sentencia-select;

2) Abrir el cursor. Con ello se ejecuta la sentencia SELECT asociada y se trasfieren todos los registros a un rea de memoria.

OPEN nom-cursor

La apertura del cursor desencadena las siguientes acciones:

- la asignacin de memoria del cursor

- el anlisis sintctico y semntico de la instruccin SELECT

- la activacin de los posibles bloqueos (si SELECT FOR UPDATE)

3) Recuperar los registros uno por uno y tratarlos. Las filas devueltas por la SELECT son tratadas una por una; el valor de cada columna especificada en la

SELECT debe almacenarse en una variable

10

LOOP

FETCH nom-cursor INTO variables;

EXIT WHEN nom-cursor%NOTFOUND;

END LOOP;

La instruccin FETCH extrae una soloa fila cada vez; para tratar n filas es

necesario utilizar un bucle.

4) Cerrar el cursor. Despus de tratar todas las filas, se cierra el cursor para liberar el espacio en memoria

CLOSE nom-cursor;

Ejemplo: Recuperar los empleados del departamento 30.

DECLARE

REG_EMP EMP%ROWTYPE;

CURSOR C1 IS

SELECT * FROM EMP WHERE DEPT_NO=30;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO REG_EMP;

EXIT WHEN C1%NOTFOUND;

Sentencias;

END LOOP;

CLOSE C1;

Ejercicio: Hacer un cambio rotatorio de turnos para todo el personal de la

plantilla. Si el turno es de maana, poner tarde, si es de tarde poner noche, y si

es de noche poner maana.

Variables especiales:

Nom-cursor%ROWCOUNT: Es un atributo numrico. Informa del nmero de la fila recuperada por la ltima FETCH.

11

Nom-cursor%ISOPEN: Es un atributo de tipo booleano. Devuelve TRUE si el cursor est abierto.

Nom-cursor%FOUND: Es un atributo de tipo booleano. Devuelve TRUE si la ltima FETCH ha recuperado al menos una fila.

Nom-cursor%NOTFOUND: Es un atributo booleano. Devuelve TRUE si la ltima FETCH no ha recuperado nada.

CURSOR FOR

En los casos en los que el uso principal de un cursor es recorrer un conjunto de

flas extradas por la ejecucin de una instruccin SELECT asociada, puede ser

interesante utilizar una sintaxis ms simple para abrir el cursor y recorrer el

bucle. Oracle proporciona una variante para el bucle FOR, que declara

implcitamente la variable de recorrido, abre el cursor, ejecuta las extracciones

sucesivas (FETCH) y cierra el cursor. La sintaxis es la siguiente:

FOR variable IN nom-cursor LOOP

Sentencias;

END LOOP;

El cursor se declara igual. Con ello evitamos declarar la variable de recepcin

de datos, que se crea automticamente. En el momento de ejecutar la FOR, se

abre el cursor. No hace falta FETCH ni la condicin de salida del bucle. Se sale

cuando no encuentra nada.

Ejercicio: Repetir el ejercicio anterior usando FOR.

Ejercicio: Crear un bloque PL/SQL que recorra la tabla HOSPITAL, que

compruebe que el nmero de camas contiene un valor correcto (es decir, el

sumatorio de los nmeros de camas de las salas del hospital), y si no es as,

que lo modifique.

Ejercicio: n 1

12

Actualizacin con cursores

Se pueden definir cursores para actualizacin. Con ello se consigue bloquear

los registros que se vayan a actualizar para que nadie los modifique mientras

tanto. Sin este bloqueo, podran tener lugar modificaciones entre la lectura y la

actualizacin, lo que podra tener consecuencias desastrosas.

La clusula CURRENT OF permite acceder directamente a la fila que acaba de

ser extrada por el comando FETCH.

Sintaxis:

CURSOR C1 IS

SELECT campos-seleccin FROM Tabla

FOR UPDATE OF campo-1, campo-2;

LOOP

UPDATE .......WHERE CURRENT OF C1;

END LOOP;

Tiene la ventaja de que en la WHERE no hace falta especificar la condicin de

emparejamiento.

Ejercicio: n 2 y 3

13

2.5. CONTROL DE ERRORES O EXCEPCIONES

Durante la ejecucin pueden producirse muchos errores. Dichos errores

pueden ser consecuencia del hardware, de fallos de programacin o pueden

tener cualquier otro origen. El lenguaje PL/SQL proporciona un mecanismo de

deteccin de errores que permite dar una respuesta software a cualquier tipo

de error. Aunque no todos los errores podrn ser tratados, es posible prever

una salida correcta del programa cuando se producen.

Los errores ocurridos durante la ejecucin de la parte ejecutable de un bloque

PL/SQL se denominan excepciones. El control de errores se realiza en la zona

EXCEPTION del bloque PL/SQL. Esta seccin es opcional y slo debe

definirse cuando se desea realizar una gestin de los errores.

Cuando se detecta una condicin de error, se dispara el tratamiento de

excepcin asociado a ese error; la ejecucin secuencial de las sentencias del

bloque PL/SQL queda interrumpida definitivamente.

Sintaxis:

EXCEPTION

WHEN THEN

;

WHEN THEN

;

...........

WHEN OTHERS THEN

;

END;

14

Hay 2 tipos de excepciones:

Excepciones internas predefinidas por Oracle Excepciones definidas por el usuario

a) Excepciones internas predefinidas

Son excepciones ya definidas, asociadas a un tipo de error ORACLE, y que

pueden utilizarse sin necesidad de definirlas.

Los siguientes nombres de excepciones son excepciones estndar

predefinidas:

Excepcin Significado Error de Oracle

Valor de SQLCODE

DUP_VAL_ON_INDEX cuando se intenta insertar una

fila y se repite una clave nica

(CREATE UNIQUE INDEX)

ORA-00001 -1

INVALID_CURSOR cuando se hace referencia a un

cursor que no es vlido

ORA-01001 -1001

INVALID_NUMBER cuando una conversin de tipo

char a number falla (ej:

to_number(12a)

ORA-01722 -1722

LOGON_DENIED Cuando se ha utilizado un

USERNAME incorrecto

ORA-01017 -1017

NO_DATA_FOUND Cuando una consulta no

devuelve ninguna fila

ORA-01403 +100

TOO_MANY_ROWS Cuando una sentencia SELECT

devuelve ms de una fila

ORA-01422 -1422

Estas excepciones:

- no se declaran en DECLARE

- no se mencionan en BEGIN, slo en EXCEPTION:

WHEN nom-excep THEN sentencias;

15

Ejemplo TOO_MANY_ROWS: la select devuelve ms de una fila set serveroutput on; declare reg_d dept%rowtype; begin select * into reg_d from dept; exception when too_many_rows then dbms_output.put_line(demasiadas filas); end; / Ejemplo de DUP_VAL_ON_INDEX: insertar un departamento que ya existe set serveroutput on; declare begin insert into dept values (10,secretaria); exception when dup_val_on_index then dbms_output.put_line (ya existe); end; /

Ejercicio: n 4

b) Excepciones definidas por el usuario

Si el programador quiere tratar algn tipo de anomala (error lgico o error de

datos) o situacin excepcional puede definir excepciones para ello.

Hay dos tipos de excepciones de usuario:

Excepciones que tienen relacin con errores ORACLE (not found, etc.) Excepciones que no tienen relacin con errores ORACLE. Ambas deben ser declaradas en la zona DELARE.

b1) Excepciones que tienen relacin con errores ORACLE

- Tienen asignado un valor numrico: (Ej. ORA-9999)

- Hay que declararlas

16

DECLARE

Nom-excep EXCEPTION;

PRAGMA EXCEPTION_INIT(nom-excep,-cd_error);

- En el BEGIN no se pone nada

- En EXCEPTION

WHEN nom-excep THEN sentencia;

Ejemplo:

DECLARE

Mes-error EXCEPTION;

PRAGMA EXCEPTION_INIT(Mes_error,-1843);

BEGIN

Sentencias;

EXCEPTION

WHEN mes_error THEN sentencias;

.....

WHEN OTHERS THEN sentencias;

END;

b2) Excepciones que no tienen relacin con errores ORACLE.

DECLARE

Nom-excepcin EXCEPTION

BEGIN

IF condicin THEN RAISE nom-excepcin;

EXCEPTION

WHEN nom-excepcin THEN sentencias;

17

Ejemplo:

DECLARE

DEMASIADO EXCEPTION;

BEGIN

.........

IF NUM_CAMAS>1000 THEN

RAISE DEMASIADO;

EXCEPTION

WHEN DEMASIADO THEN

.........;

END;

Funciones usadas con excepciones

PL/SQL tiene unas funciones especiales para el control de errores:

SQLCODE: devuelve el cdigo de error ORACLE que ha ocurrido. SQLERRM: devuelve el string del mensaje correspondiente al error

ocurrido.

Adems se puede usar:

USER: nombre de usuario SYSDATE: Fecha y hora

Para registrar los posibles errores, el usuario puede crear una tabla de errores

donde va almacenando los mensajes correspondientes a los errores que se

producen. Por ejemplo:

CREATE TABLE TAB_ERRORES

(COD-ERR VARCHAR(10),

MENSAJE VARCHAR(100),

FECHA DATE,

USUARIO VARCHAR(100));

18

Ejemplo:

DECLARE

CODIGO TAB_ERRORES.MENS1%TYPE;

MENSAJE TAB_ERRORES.MENS2%TYPE;

EXCEPTION

...........

...........

WHEN OTHERS

CODIGO:=SQLCODE;

MENSAJE:=SQLERRM;

INSERT INTO TAB_ERRORES

VALUES(CODIGO,MENSAJE,SYSDATE,USER);

Sacar mensajes por pantalla:

En la primera lnea del programa o bien, antes de ejecutar el programa desde

SQLPLUS debe especificarse:

SET SERVEROUTPUT ON

Para sacar por pantalla otro mensaje distinto del standard la instruccin es:

DBMS_OUTPUT.PUT_LINE(mensaje) Saca mensaje y salta de lnea

DBMS_OUTPUT.PUT(mensaje) Saca mensaje pero no salta

DBMS_OUTPUT.PUT(variable) Saca valor de variable (del INTO)

Ejemplo:

DBMS_OUTPUT.PUT(el salario es)

DBMS_OUTPUT.PUT(SALARIO)

19

Ejercicio de a y b1:

1) Crear una tabla de prueba con las siguientes columnas:

DNI NUMBER(4) PK

NOMBRE CHAR(20)

SALARIO NUMBER(6)

2) Crear un bloque PL/SQL que recupere el salario del DNI 1111 en una

variable definida como NUMBER(3) (para que d error). Si no encuentra

filas en la tabla, emitir el mensaje No hay filas. S da el error 06502, sacar

el mensaje esa columna no tiene ese tipo de datos.

3) Ejecutar el bloque sin ninguna fila en la tabla (se producir el primer

error)

4) Insertar la fila 1111 Garca 200.000

5) Ejecutar el bloque de nuevo (se producir el 2 errr)

6) Crear la tabla de errores. A continuacin modificar el programa para que

cuando se produzca el error 06502, adems de visualizar el mensaje, se

inserte una lnea en la tabla de errores.

Ejercicio (b2): Mirar si el nmero de camas de la sala 3 del hospital 13 es superior a 15. En ese caso enviar el mensaje DEMASIADAS CAMAS.

20

2.6. PROCEDIMIENTOS

Los procedimientos son bloques PL/SQL con nombre, que pueden recibir

parmetros y ser invocados directamente.

El gestor permite almacenar un procedimiento en la Base de Datos para su

repetida ejecucin. Se crean desde SQL*PLUS.

Creacin de un procedimiento:

CREATE [OR REPLACE] PROCEDURE nom-proc

[parmetro1 IN/OUT tipo-datos, parmetro2 IN/OUT tipo-datos)] IS

;

BEGIN

;

[EXCEPTION]

END;

Los parmetros especifican las variables pasadas como parmetros que

pueden ser utilizadas en el bloque, y hay que especificar si son de entrada o

salida aadiendo:

De entrada: IN o blanco De salida: OUT OR REPLACE reemplaza la descripcin del procedimiento en el Diccionario de

Datos, si existe.

Compilar un procedimiento:

START nom-fichero @nom-fich

Los errores de compilacin se consultan en la tabla USER_ERRORS

21

(Select * from USER_ERRORS Select LINE,POSITION,TEXT from

USER_ERRORS). Tambin se pueden observar mediante la sentencia SHOW

ERRORS.

Ejecutar un procedimiento:

EXECUTE nom-proc (valor-parmetro-1, .....);

Tablas especiales:

USER_ERRORS: Devuelve la informacin de los errores que se producen USER_SOURCE: Vemos las lneas de nuestro procedimiento, evitando as

ir al programa.

Ejercicios: n 6, 7, 8, 9, 10

PROCEDIMIENTOS ANIDADOS

Un procedimiento anidado es un procedimiento almacenado llamado por otro.

Esta situacin puede ser til para generalizar el diseo modular de la

aplicacin. De este modo, un procedimiento almacenado puede ser una

subrutina que d servicio a un programa cliente, remoto o local, o a otro

procedimiento almacenado.

Ejemplo: Crear un procedimiento PPAL que reciba como parmetro un nmero

de departamento e imprima el nmero y nombre de dicho departamento. A

continuacin

Deber llamar a otro procedimiento (subrutina) que calcule el nmero de

empleados del mismo y devuelva ese valor como parmetro. El procedimiento

principal imprime el valor obtenido.

22

Contenido de PROG1.SQL:

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE PPAL(ND DEPT.DEPT_NO%TYPE) IS

REG_D DEPT%ROWTYPE;

TOT_EMP NUMBER;

BEGIN

SELECT * INTO REG_D FROM DEPT WHERE DEPT_NO=ND;

DBMS_OUTPUT.PUT_LINE ('DEPARTAMENTO '|| ND || ' ' ||

REG_D.DNOMBRE);

SUBRUT1(ND, TOT_EMP);

DBMS_OUTPUT.PUT_LINE ('N EMPLEADOS QUE TIENE: '|| TOT_EMP);

END;

/

Contenido de SUBRUT1.SQL:

CREATE OR REPLACE PROCEDURE SUBRUT1(ND IN EMP.DEPT_NO%TYPE, TOT_EMP

OUT EMP.SALARIO%TYPE) IS

BEGIN

SELECT COUNT(*) INTO TOT_EMP FROM EMP WHERE DEPT_NO=ND;

END;

/

Se compilan (1 la subrutina):

@SUBRUT1

@PROG1

Se ejecuta el procedimiento principal: EXECUTE PPAL(10) y el resultado es 3.

Ejercicio: n11

23

2.7. FUNCIONES

Una funcin es un fragmento de cdigo PL/SQL que devuelve un valor.

La programacin de funciones definidas por el usuario es muy similar a la de

los procedimientos almacenados.

La definicin de funciones es ms propia del ABD que del programador.

Una diferencia importante entre procedimientos y funciones es que los primeros

se invocan desde un programa, para lo cual hay que conocer la programacin.

Por el contrario, una funcin se puede invocar desde una sentencia SQL

directamente, por lo que est al alcance de los usuarios interactivos.

Sintaxis:

CREATE [OR REPLACE] FUNCTION

Nombre-funcin (arg-1 [IN] tipo-dato, ....)

RETURN tipo-dato

IS

Declaracin variables;

BEGIN

Cuerpo-funcin;

RETURN (variable);

END nom-fun;

/

OR REPLACE: Si la funcin ya existe, se reemplaza en el diccionario de datos.

arg: argumento o parmetro que se pasa como dato de entrada y que se usa como una variable dentro del bloque

RETURN tipo-dato: tipo del valor devuelto por la funcin

24

Ejemplo: Crear una funcin que nos devuelva el sueldo del empleado a partir

de su nmero de empleado.

En un fichero prog2.sql: CREATE OR REPLACE FUNCTION GET_SAL (F_EMP_NO IN EMP.EMP_NO%TYPE)

RETURN NUMBER

IS

F_EMP_SAL EMP.SALARIO%TYPE := 0;

BEGIN

SELECT SALARIO INTO F_EMP_SAL FROM EMP

WHERE EMP_NO=F_EMP_NO;

RETURN (F_EMP_SAL);

END GET_SAL;

Para ejecutar la funcin desde sqlplus (para el empleado de n 7369):

SELECT GET_SAL(7369) FROM DUAL;

--> 10400

Para ejecutar la funcin desde un programa que imprime el salario del

empleado 7369:

(fichero PROG3.SQL)

SET SERVEROUTPUT ON;

DECLARE

V_SAL EMP.SALARIO%TYPE;

BEGIN

V_SAL:=GET_SAL(7369);

DBMS_OUTPUT.PUT_LINE('EL SALARIO ES ' || V_SAL);

END;

/

Ejercicios: 12, 13, 14

25

2.8. DISPARADORES DE BASES DE DATOS (TRIGGERS) Un disparador a nivel de Base de Datos es un bloque PL/SQL asociado a

una tabla especfica, y permite especificar restricciones complejas o que tengan

en cuenta datos procedentes de varias filas o de varias tablas, por ejemplo,

para garantizar que un cliente no pueda tener ms de dos pedidos no pagados.

Sin embargo, los disparadores no deberan usarse cuando sea posible

establecer una restriccin de integridad (FOREIGN KEY, PRIMARY KEY,

CHECK, etc.). Las restricciones de integridad se definen a nivel de tabla y

forman parte de la estructura de la propia tabla, por lo que la verificacin de

estas restricciones es mucho ms rpida. Adems las restricciones de

integridad garantizan que todas las filas de las tablas respetan dichas

restricciones, mientras que los disparadores no tienen en cuenta los datos ya

contenidos en la tabla en el momento de definirlos.

Algunos ejemplos de situaciones en las que se pueden aplicar disparadores

son:

Condiciones de transicin: cuando intervienen los valores anterior y posterior de una modificacin. Por ejemplo, supongamos que en una

tabla de cuentas hay una columna con la fecha del ltimo movimiento

y que cada vez que llega un movimiento se cambia esta columna.

Entonces habr que comprobar que la nueva fecha sea posterior a la

que haba.

Cuando hay que llevar totales automticamente. Por ejemplo, supongamos que el saldo de una cuenta debe ser en todo momento

igual a la suma de sus movimientos, que estn en otra tabla. Esta

condicin se podra expresar mediante un disparador.

Un disparador o trigger define una accin que se desencadena (se dispara)

cada vez que se inserte, actualice o borre un dato, es decir, cada vez que se

aplique una INSERT, UPDATE o DELETE sobre la tabla. El bloque PL/SQL

que constituye el disparador, puede ejecutarse antes o despus de la

actualizacin. Adems se puede ejecutar para cada fila afectada por la

26

instruccin DML (opcin FOR EACH ROW) o una sola vez para cada

instruccin DML ejecutada (opcin predeterminada).

Los disparadores se ejecutan de manera automtica mientras se ejecuta una

sentencia SQL sobre la tabla sobre la que estn definidos.

Beneficios que aportan:

Proporcionan una seguridad adicional a la BD: evitan actualizaciones antes de que stas ocurran informar sobre ciertas actualizaciones

conflictivas que se producen.

Incrementan la integridad de los datos Mejoran los rendimientos (reducen el nmero de llamadas a ORACLE) Ahorran consumo de memoria Aumentan la productividad

Sintaxis:

CREATE [OR REPLACE] TRIGGER disparador {BEFORE|AFTER}

{DELETE| INSERT|UPDATE [OF columna [, columna] ....] }

[OR {DELETE| INSERT|UPDATE [OF columna [, columna] ....] } ON Tabla

FOR EACH ROW

[WHEN [condicin]]

La forma de recibir los datos es:

:NEW.nom-campo -> valor nuevo

:OLD.nom-campo -> valor viejo

Cuando se usa WHEN no hacen falta los :.

Para eliminar un trigger se utiliza la sentencia DROP TRIGGER nom-trigger.

27

Ejercicio: Coger las tablas EMP y DEPT y aadirles 2 columnas a cada una:

USUARIO y FECHA. Crear un trigger que cada vez que se realice una

insercin o modificacin, se registre el usuario y la fecha. Para ello utilizar las

variables del sistema USER y SYSDATE.

Estructura:

CREATE OR REPLACE TRIGGER nom-trigger

BEFORE INSERT OR UPDATE

FOR EACH ROW

DECLARE

BEGIN

:NEW.nom-campo:= valor;

:NEW.nom-campo:= valor;

END;

Se compila: START nom-fichero @nom-fichero

Y queda activo hasta eliminarlo con DROP TRIGGER.

NOTA: Si se elimina la tabla que contiene los disparadores, stos tambin

se eliminan.

Ejemplo:

Primer disparador para la tabla DEPT:

CREATE OR REPLACE TRIGGER T1

BEFORE INSERT OR UPDATE ON DEPT FOR EACH ROW

BEGIN

:NEW.FECHA:=SYSDATE;

:NEW.USUARIO:=USER;

END;

/

28

Segundo disparador, para la tabla EMP:

CREATE OR REPLACE TRIGGER T2

BEFORE INSERT OR UPDATE ON EMP FOR EACH ROW

BEGIN

:NEW.FECHA:=SYSDATE;

:NEW.USUARIO:=USER;

END;

/

Para consultar los triggers tenemos dos tablas en el diccionario de datos:

USER_TRIGGERS: Triggers del usuario ALL_TRIGGERS: Todos los triggers de todos los usuarios

Consulta: SELECT TRIGGER_NAME, TABLE_OWNER,TABLE_NAME

FROM USER_TRIGGERS;

Ejercicios:15, 16, 17, 18, 19, 20

29

2.9. EJERCICIOS DE PL/SQL

Ejercicio 1

Crear la tabla MEDICOS (COD_H, NOMBRE, NUM_MED) para contener el cdigo y nombre de cada hospital, junto con el nmero de doctores que trabajan en cada uno. A continuacin, crear un bloque PL/SQL que recorra los hospitales de la tabla HOSPITAL, y por cada uno inserte una fila en la tabla MEDICOS, donde el valor de NUM_MED ser el nmero de mdicos que trabajan en dicho hospital.

Ejercicio 2

Aumentar el salario a los empleados de la tabla EMP utilizando bloqueo mientras se actualiza.

Si el empleado es director: sumar un 8% de la media del salario de los directores.

Si el empleado es analista: sumar un 5% de la media del salario de los analistas.

Si es otro: sumar un 4% de la media del salario de todos los empleados. Si el empleado tiene comisin, adems de la subida normal, sumar el 2% de

la comisin del salario. Ejercicio 3 Aumentar el salario de los empleados de la tabla PLANTILLA de la siguiente forma:

Si es ENFERMERO/A: sumarle un 5% de la media del salario de los enfermeros y enfermeras.

Si es INTERNO: sumarle un 10% de la media del salario de los internos. Si es otro tipo de empleado: sumarle un 3% de la media de todos los

empleados. Adems, si el empleado en cuestin trabaja en turno de noche, adems de la subida normal, incrementarle su salario actualizado en otro 2%. Ejercicio 4 Comprobar el funcionamiento de la excepcin predefinida DUP_VAL_ON_INDEX. Para ello crear un bloque que recupere el n de doctor del doctor cuyo apellido sea NINO P. y realizar un alta en la tabla con ese mismo n de doctor. El mensaje que nos deber dar es El Doctor xxx ya existe.

30

Ejercicio 5 Comprobar que en todos los hospitales hay por lo menos un cardilogo. En caso negativo mandar un mensaje a pantalla tratndolo como una excepcin. (se puede hacer comprobando que el n de hospitales de la tabla HOSPITAL sea igual al n de hospitales que tienen cardilogos en la tabla DOCTOR). Ejercicio 6 Crear un procedimiento PL/SQL que reciba como parmetro la inscripcin de un enfermo. El procedimiento deber sacar un mensaje por pantalla indicando el nombre del doctor que trata a dicho enfermo as como cul es su especialidad. Adems, si el enfermo no existe en la tabla deber mandar un mensaje indicando tal hecho (tratarlo como excepcin). Por ltimo se deber comprobar si hay ms enfermos tratados por dicho mdico; en caso afirmativo se sacar un mensaje por pantalla que lo indique (tratarlo tambin como una excepcin).

Ejemplos: 1. El doctor que atiende al paciente 10995 es LOPEZ A., cuya especialidad

es CARDIOLOGIA Adems, existen otros enfermos tratados por el mismo mdico.

2. Ese enfermo no existe. 3. El doctor que atiende al paciente 39217 es ADAMS C., cuya

especialidad es NEUROLOGIA Ejercicio 7 Crear un procedimiento que tome como parmetro de entrada el nombre de un hospital y que visualice por pantalla los nombres de los doctores que trabajan en el hospital y su especialidad. Si el hospital no existe, visualizar un mensaje de error. Ejercicio 8 Crear un procedimiento al que se le pasen 2 parmetros (el nmero de empleado y el aumento de salario) y que incremente el el salario del empleado en cuestin. Adems:

Si no existe el empleado, meter en la tabla de errores el nempleado y el mensaje de no encontrado.

Si no tiene salario, meter en la tabla de errores el nempleado y el mensaje de salario nulo, y tambin visualizarlo por pantalla.

31

Ejercicio 9 Crear una tabla PORCEN con los siguientes campos: EMPNO NUMBER(4) TOTAL NUMBER(15) ARAS NUMBER(15) Mediante un procedimiento, recorrer la tabla de empleados e ir almacenando en la nueva tabla:

En EMPNO: el n empleado En TOTAL: el % que supone el salario de ese empleado respecto a los

dems

En ARAS: El % del Total acumulado

EMPNO TOTAL ARAS 1 3% 3%

2 5% 8%

3 10% 18%

. . . . . .

100%

Ejercicio 10 Crear la siguiente tabla: LIBRO

COD_LIB STOCK STOCK_MIN

L1 10 5

L2 5 4

L3 30 8

Crear un procedimiento PL/SQL que reciba dos parmetros de entrada: un cdigo de libro y el nmero de unidades vendidas. El procedimiento deber actualizar el stock del libro introducido. Adems debern tratarse como excepciones las siguientes situaciones:

32

En el caso de que el libro no exista en la BD, visualizar el mensaje Libro inexistente

En el caso de que el stock actualizado sea inferior al stock mnimo, visualizar el mensaje Stock por debajo del mnimo

En el caso de que el stock actualizado sea mayor que el triple del stock mnimo, visualizar el mensaje Demasiados libros en stock.

Ejercicio 11 Crear un procedimiento que d una lista de los apellidos de los doctores de una determinada especialidad junto con el nmero de enfermos que atiende cada uno. El procedimiento recibir como parmetro el nombre de la especialidad, y por cada doctor de la misma llamar a otro procedimiento (o subrutina). Este recibir como parmetro un nmero de doctor y devolver al procedimiento principal el nmero de pacientes que dicho doctor atiende. Ejercicio 12 Crear una funcin HOSP que obtenga el nmero de mdicos de una especialidad que trabajan en un determinado hospital. Se suministrarn como parmetros el cdigo del hospital y la especialidad. Ejercicio 13 Crear una funcin MEDIA_ENF que obtenga el nmero medio de pacientes que atienden los mdicos de un determinado hospital. Se suministrar el nombre del hospital (si no existe, indicarlo en un mensaje). b) Ejecutar la funcin desde SQLPlus c) Crear un procedimiento que saque por pantalla el nombre de cada hospital y el nmero medio de pacientes que atiende cada medico del hospital. Se utilizar la funcin MEDIA_ENF Ejercicio 14 1. Crear un procedimiento INCRE que realice lo siguiente: Dado el nombre de

un departamento, en primer lugar, comprobar que el departamento existe, y en caso contrario enviar el mensaje de error correspondiente. Seguidamente, recorrer los empleados de dicho departamento y por cada uno de ellos llamar a una subrutina que calcule el incremento salarial para el mismo (se pasar como parmetro el salario): Si su salario actual es menor que 200.000 , entonces el incremento ser

del 10% Si su salario actual est entre 200.000 y 300.000, el incremento ser del

5% Si su salario actual es superior a 300.000, el incremento ser del 2%

33

La subrutina devuelve al programa principal el valor calculado, y desde el programa principal se actualiza el salario en la tabla EMP y se visualiza por pantalla el nombre del empleado y el incremento aplicado. Ejecutar el procedimiento para el departamento de VENTAS 2. Realiza una versin del ejercicio anterior, utilizando una funcin (en lugar de la subrutina). Ejercicio 15 Usando triggers, comprobar que al modificar o insertar algo en la tabla DEPT, que el nmero de departamento modificado sea 10, 20, 30, 40, 50 60. Si no es as, sacar un mensaje de advertencia (aunque se le deje hacer).

Ejercicio 16 Usando triggers comprobar que al modificar o dar de alta a un nuevo doctor del hospital 13 o 18, su especialidad sea CARDIOLOGIA o GINECOLOGIA. En caso contrario enviar a pantalla el mensaje ESPECIALIDAD INCORRECTA. Ejercicio 17 Crear un trigger que controle si al dar de alta a un nuevo doctor o modificar su informacin la especialidad introducida es nueva, es decir no exista nadie antes con esa especialidad. Si esto ocurre que mande a pantalla el mensaje ESPECIALIDAD NUEVA. Ejercicio 18 Crear un trigger que no permita modificar los salarios de empleados en LUNES, ni reducir un salario cualquier da de la semana. En caso de que alguien lo intente, se enviar el correspondiente mensaje y se evitar la actualizacin de la BD. Ejercicio 19 Crear un trigger que controle que si al dar de alta a un empleado en la tabla EMP o modificar su informacin, haya ya en la tabla al menos 2 personas ms con el mismo valor en el atributo OFICIO. Si esto ocurre, enviar por pantalla el mensaje HAY n personas en la empresa con el oficio XXX. Ejercicio 20 Comprobar que al modificar o insertar algo en la tabla HOSPITAL, el nmero de hospital modificado sea 13, 18 22. Si no es as, sacar un mensaje de advertencia y que no permita la accin.

34

Ejercicio 21 Disponemos de las siguientes tablas: MEDICO (DNI_M, NOMBRE, ESPECIALIDAD, LOCALIDAD) TRATAMIENTOS (DNI_M, DNI_P, MEDICAMENTO, DOSIS, FECHA) NEGLIGENCIAS (DNI_M, NOMBRE, DOSIS) Crear un bloque PL/SQL que recorra los mdicos cuya especialidad sea la traumatologa y realice lo siguiente:

Si no ha tratado a ningn paciente, deber aparecer un mensaje por pantalla indicando EL MDICO XXX NO HA TRATADO A NINGN PACIENTE. Esto se deber tratar como una excepcin.

Si el mdico ha administrado pronerol en una cantidad superior a 100 a alguno de sus pacientes en cualquiera de los tratamientos deber insertar una fila en la tabla NEGLIGENCIAS.

Una vez realizado lo anterior, si el nmero de mdicos que ha cometido una negligencia es mayor que el 10% del nmero de mdicos que existen en la tabla MEDICO, deber aparecer un mensaje por pantalla indicando tal hecho. Esto tambin se deber tratar como una excepcin. Ejercicio 22 Disponemos de las siguientes tablas: ORDENADOR (COD_ORD, MARCA, MODELO, PRECIO) COMPONENTE (COD_COMP, DENOMINACION, PRECIO) COMPOSICION (COD_ORD, COD_COMP, CANTIDAD) La tabla ordenador representa todos los ordenadores que tenemos. El campo precio inicialmente est vaco. La tabla composicin representa los componentes que forman cada ordenador, y en qu cantidad. Por ejemplo, un ordenador de cdigo ord_2341 puede tener, entre otros, el componente unidad de disco duro en una cantidad de 2. La tabla componente contiene todos los componentes posibles de un ordenador. De cada componente se almacena un cdigo, su denominacin y su precio. Codificar un procedimiento PL/SQL que reciba como parmetro el cdigo de un ordenador y haga lo siguiente:

Primero comprobar que el cdigo del ordenador existe en la BD; si no es as se disparar una excepcin que visualice el mensaje Ese ordenador no est dado de alta en la BD.

En caso de que el ordenador exista, se deber calcular su precio (sumatorio del valor de cada uno de sus componentes, dependiendo de la cantidad). Una vez hecho esto, deber modificar la fila

35

correspondiente a ese ordenador en la tabla ordenador para introducir su precio.

Adems, si el precio del ordenador es mayor de 1800 euros deber aparecer un mensaje por pantalla comentando este hecho. Esto se deber tratar como una excepcin.

Ejercicio 23 EMP (EMP_NO, NOMBRE, DIRECCION, TF, SALARIO) PROYECTO (COD_P, NOM_P, PRESUP) PARTICIPAC (EMP_NO, COD_P, HORAS, FUNCION) Se desea crear un procedimiento que reciba como parmetro un cdigo de proyecto. Deber recorrer los registros de aquellos empleados que han participado en el proyecto e incrementarles el sueldo en un valor equivalente al nmero de horas invertidas (HORAS) durante ese mes multiplicado por el precio hora. Este incremento se calcular por medio de una subrutina a la que se le pasar como parmetro el cdigo de proyecto y el cdigo del empleado. El precio hora se calcular de la siguiente forma:

Si la funcin del empleado en el proyecto es DIRECTOR el precio/hora ser de 25 euros.

Si la funcin del empleado en el proyecto es DISEADOR el precio/hora ser de 20 euros.

Si la funcin es otra diferente, el precio/hora ser 15 euros. Se debern trata como excepciones, visualizando los correspondientes mensajes:

Proyecto inexistente Si el total de incrementos es superior al 10% del presupuesto del

proyecto, enviar el mensaje incremento salarial excesivo .

36

Ejercicio 24 Dadas las siguientes tablas con informacin de los alumnos matriculados en un curso acadmico en la titulacin Grado en Ingeniera Informtica:

Alumnos de Informtica: ALUM (DNI, NOM, DIR, TF, GRUPO) Grupos en la titulacin de informtica: GRUPOS (GRUPO, AULA) Matrculas de asignaturas efectuadas durante el curso actual:

MATRICULAS (DNI, COD_A, CALIF, CONVOC) Notas medias de alumnos en el curso actual, inicialmente vaca:

MEDIAS_CALIF (DNI, NOM, MEDIA, PENDIENTES, VALORAC) Se pide: Codificar un procedimiento en plsql que reciba como parmetro un grupo (2A, 1B, ...) y recorra los alumnos de dicho grupo realizando lo siguiente:

Por cada alumno se calcular la nota media teniendo en cuenta las calificaciones obtenidas en la tabla MATRICULAS, as como el nmero de asignaturas pendientes (suspensos o no presentados). Los NO PRESENTADOS se registran como 0.

Se insertar una fila en la tabla MEDIAS_CALIF con los datos del alumno, la nota media del mismo, el nmero de asignaturas pendientes y una valoracin (alta, media o baja).

La valoracin se calcular mediante una subrutina que recibir como parmetro la nota media del alumno de la siguiente forma:

Si la nota media es superior a 7, la valoracin ser alta. Si la nota media est entre 5 y 7, la valoracin ser media. Si la nota media es menor que 5, la valoracin ser baja.

Se tratarn como excepciones las siguientes situaciones: En el caso de que el grupo introducido como parmetro sea

incorrecto (es decir, no exista en la tabla de grupos), se producir una excepcin y se enviar el mensaje grupo XX incorrecto.

Al final del proceso se calcular el nmero de alumnos con valoracin baja. Si este nmero es superior a 50, se producir una excepcin y se visualizar el mensaje resultados poco satisfactorios

37

Ejercicio 25 Sea la siguiente BD con informacin referente al alquiler de casas rurales de un territorio: MUNICIPIO (COD_M, NOM_M) CASA_RURAL (COD_C, DIR, COD_M, PROPIETARIO, PRECIO_DIA, INGRESOS, GASTOS, VALORACIN) ALQUILERES (COD_C, FECHA_A, N_DIAS, CLIENTE) Se pide realizar un procedimiento en PL/SQL para actualizar los ingresos de las casas rurales de un municipio durante un periodo (normalmente anual). Se pasar como parmetro el nombre de un municipio. Se comprobar que el nombre es correcto, es decir, que existe en la tabla de municipios. En caso de error, se disparar una excepcin Oracle y se enviar el correspondiente mensaje por pantalla. Si el municipio es correcto, se recorrern todas las casa rurales correspondientes a ese municipio, incrementando el valor de la columna INGRESOS con el importe total de los alquileres. Para ello, por cada casa rural se llamar a una subrutina que calcular los ingresos generados a partir de los alquileres registrados en la tabla ALQUILERES del periodo considerado (en nuestro caso, el ao actual). Se le pasar como parmetro el COD_C y el PRECIO_DIA y la subrutina devolver el importe a incrementar. La subrutina calcular el importe como: nmero total de das que ha estado la casa alquilada durante el periodo * precio_dia. Una vez que se han actualizado los ingresos, se actualizar la columna VALORACIN de la siguiente forma:

Si el importe de alquileres menos gastos es inferior a 6.000 euros, la valoracin ser BAJA.

Si el importe de alquileres menos gastos est entre 6.000 y 20.000 euros, la valoracin ser MEDIA.

Si el importe de alquileres menos gastos es superior a 20.000 euros, la valoracin ser ALTA.

Una vez procesadas todas las casas rurales del municipio, si dicho municipio tiene ms de 5 casas se disparar un excepcin que indicar mediante un mensaje en nmero de casas rurales del municipio y el importe total generado por el alquiler del conjunto de casas durante el periodo.

38

Ejercicio 26 Dada una BD formada por las siguientes tablas: create table vehiculo(matricula varchar2(7) primary key, tipo varchar2(1)); create table cliente(dni varchar2(9) primary key, puntos number(8),

provincia varchar2(50)); create table alquiler(matricula varchar2(7) references vehiculo(matricula),

dni varchar2(9) references cliente(dni) ,siniestro varchar2(1), kilometros number(5));

Se pide codificar un procedimiento en PL/SQL que realice lo siguiente: El objetivo del procedimiento consiste en seleccionar el mejor cliente de una empresa de alquiler de vehculos para una determinada provincia. Por cada alquiler que realiza un cliente, se le dan una serie de puntos, y el cliente que ms puntos obtenga ser el ganador. El procedimiento se llamar MEJOR_CLIENTE, y recibir un parmetro con la provincia de la que se quiere obtener el mejor cliente. Realizar los siguientes pasos:

Comprobar que existe por lo menos un alquiler de algn cliente de la provincia recibida como parmetro. En caso de que no haya alquileres, se disparar una excepcin visualizando el mensaje NO HAY ALQUILERES CON CONDUCTORES DE ESA PROVINCIA). Si por el contrario se encuentran alquileres de algn cliente de esa provincia se visualizar el mensaje: EN LA PROVINCIA xxxxx SE HAN REALIZADO yyy ALQUILERES.

Recorrer todos los alquileres de los clientes de esa provincia, y por cada alquiler calcular los puntos correspondientes a ese alquiler, y actualizar los puntos del cliente en la tabla cliente sumando los puntos calculados a los que ya tena el cliente.

Para calcular los puntos de un determinado alquiler se llamar a una funcin llamada CALCULAR_PUNTOS, que devolver los puntos correspondientes al alquiler en base a los siguientes parmetros que recibe: siniestro(S N), tipo_vehculo(A,B,C D) y los kilmetros recorridos. Si durante el alquiler ha tenido lugar algn siniestro, no se dar ningn punto. Si no ha tenido lugar ningn siniestro se calcularn los puntos de la siguiente forma:

o Para el tipo de vehculo A se multiplican los kilmetros * 25 o Para el tipo de vehculo B se multiplican los kilmetros * 30 o Para el tipo de vehculo C se multiplican los kilmetros * 50 o Para el tipo de vehculo D se multiplican los kilmetros * 75

Una vez actualizados los puntos de todos los clientes, si no hay ningn cliente de esa provincia que haya superado los 10.000 puntos, se lanzar una excepcin y se visualizar el siguiente mensaje: GANADOR DESIERTO

39

Si hay ganador, se visualizar por pantalla los puntos del ganador: EL CLIENTE GANADOR HA OBTENIDO XXXX PUNTOS.

Ejercicio 27 Se dispone de una BD que recoge la informacin de los seguros de los coches para el ao actual, mediante las siguientes tablas: COCHE (Num_mat, marca, modelo, color, dni_prop) PROPIETARIOS(Dni, nombre, direccin, edad, sexo) ACCIDENTE (Dni, fecha, lugar, daos) SEGUROS (Num_poliza, num_mat, prima, fecha). La tabla Seguros inicialmente est vaca. Se pide codificar un procedimiento en PL/SQL (que lo ejecutaremos una sola vez el 30 de Diciembre del ao en curso) que realice lo siguiente: Recorrer todos los coches y por cada uno se insertar una fila en la tabla SEGUROS con la siguiente informacin:

Num_poliza: comienza desde 1 y se ir incrementando para cada seguro. Num_mat: nmero de matrcula del coche para el que estamos

calculando el importe del seguro.

Prima: importe del seguro que se calcula mediante la subrutina CALCIMP y se explica ms adelante.

Fecha: ao del seguro. Al final del proceso, si existen ms de 30 plizas con prima superior a 700 euros cada una, se deber indicar mediante un mensaje. Esto tambin se deber tratar como una excepcin. La prima del seguro se calcular mediante una subrutina a la que le pasaremos el nmero de matrcula y nos devolver dicha prima. El precio base son 600 euros; a dicho precio habr que aadirle 3 conceptos ms:

1. Si el propietario es varn y tiene menos de 30 aos, 70 euros. Si es varn y tiene 30 aos o ms, 50 euros. Si es mujer y tiene menos de 30 aos, 40 euros. Si es mujer y tiene 30 aos o ms, 20 euros.

2. Si el coche es de color negro, 50 euros. Si el coche es blanco, 10 euros. Si el coche es de cualquier otro color, 30 euros.

3. Si el propietario del coche ha tenido durante el ao considerado daos por un importe total superior a 1000 euros, 50 euros. Si el importe de daos es inferior o igual a 1000 euros, 25 euros. Si no ha tenido accidentes tendr una bonificacin de 50 euros.

40

Ejercicio 28 Sean las siguientes tablas que reflejan una competicin de videojuegos: JUGADOR(DNI, NOMBRE, DIR, FECHA_NAC, CACH) VIDEOJUEGO(COD_VIDEOJ, DENOM_VIDEOJ, TIPO, NIVEL) JUEGA(DNI, COD_VIDEOJ, N_JUGADA, PUNTOS) RESULTADO(NOMBRE, CACH , DENOM_VIDEOJ, PUNTOS) Inicialmente la tabla RESULTADO est vaca. Cada jugador juega a cada videojuego de la tabla, 3 veces, y se guardan los puntos obtenidos en la tabla JUEGA. 1. Crear un procedimiento pl/sql que realice lo siguiente: En primer lugar deber comprobar que t participas en la competicin. Si no participas (es decir, no apareces en la tabla JUEGA) se deber enviar un mensaje a pantalla indicando tal hecho. Tratarlo como una excepcin para que aborte la ejecucin del bloque. En segundo lugar, deber introducir en la tabla RESULTADO una fila por cada jugador y videojuego jugado indicando el nombre del jugador, su cach, la denominacin del videojuego y la puntuacin mxima que ha obtenido dicho jugador en dicho videojuego (la mayor de las 3 puntuaciones que debe tener). Despus de finalizar la introduccin de filas en la tabla RESULTADO se deber comprobar que slo hay un ganador de cada videojuego (es decir, que la puntuacin mxima de cada videojuego solo aparece una vez). Para ello ser preciso recorrer la tabla RESULTADO y comprobar que la puntuacin mxima de cada videojuego solo la tiene un jugador. En caso de que haya algn empate, se deber aumentar a cada jugador empatado el campo PUNTOS aadindole su cach (el cach es un campo numrico que tienen todos los jugadores de tal forma que no puede haber dos jugadores con el mismo cach). Adems, por cada videojuego y despus de resolver el empate (si es que lo ha habido) se deber sacar por pantalla el ganador del videojuego indicando la denominacin del videojuego, y el nombre del ganador: El ganador del videojuego aaa es xxx El ganador del videojuego bbb es yyy El ganador del videojuego ccc es zzz ... 2.- Crear una funcin a la que se le pase como parmetro la denominacin de un videojuego y saque por pantalla el nmero de participantes que han jugado a ese videojuego en la competicin.

41

Ejercicio 29 Se dispone de una BD que recoge la informacin de las calificaciones obtenidas por los alumnos que han finalizado sus estudios durante el curso 0910 en la Universidad de Deusto, teniendo en cuenta que se imparten varias titulaciones y que un alumno puede cursar varias titulaciones a la vez.

ALUMNO (DNI, NOM, DIR, TF, COD_TIT, NANIOS) TITULACION (COD_TIT, NOM_TIT) EXPEDIENTE_0910 (DNI, COD_A, CRED, COD_TIT, CALIF) MEDIA_EXPED (DNI, NOM, COD_TIT, NOTA_MEDIA, MENSAJE) Se pide: 1. Codificar un procedimiento PL/SQL que reciba como parmetro el nombre

de una titulacin y obtenga el nombre y nota media del alumno con el mejor expediente de la misma. En primer lugar comprobar que el nombre de titulacin introducido es

correcto, si no lo es, disparar una excepcin que indique el error mediante un mensaje.

A continuacin recorrer los alumnos de dicha titulacin y calcular para cada uno su nota media ponderada por crditos, e introducir una fila en la tabla MEDIA_EXPED con esa informacin y un mensaje.

Si el alumno ha tardado 2 aos en terminar (columna NANIOS), el mensaje ser TERMINA EN SU AO

Si el alumno ha tardado 3 aos en terminar, el mensaje ser TERMINA UN AO MS TARDE

Si el alumno ha tardado ms de 3 aos, el mensaje ser ALUMNO REZAGADO

La media del expediente se calcular mediante una funcin MEDIA_EXP a la que se le pasar como parmetro el DNI del alumno y el cdigo de la titulacion, y devolver la nota media del expediente del alumno para esa titulacin.

Finalmente se obtendr de la tabla MEDIA_EXPED el nombre y nota media de aquel alumno que tenga el mejor expediente, es decir, la nota media ms alta de la titulacin considerada (por simplificar, supondremos que no habr empate) y se sacar un mensaje por pantalla con tal informacin.

Al final del proceso, en el caso de que la media del mejor estudiante sea superior a 9, se producir una excepcin y se visualizar el mensaje PREMIO FIN DE CARRERA.

El clculo de la nota media ponderada del expediente de un alumno se realizar de la siguiente forma. Nota media ponderada = sumatorio (cred_asignatura*calif)/sumatorio de crditos de asignaturas de la titulacin Por ejemplo: Asignatura Crditos CalificacinA1 6 8 A2 4,5 10 A3 9 6 La nota media ponderada se calculara: (6*8+4,5*10+9*6)/6+4,5+9=7,54

42

Ejercicio 30 Sea una entidad bancaria que desea gestionar los regalos que enva a sus clientes en funcin de los puntos obtenidos por operaciones bancarias. Dispondr de una BD con las siguientes tablas: CLIENTE (DNI, NOMBRE, DIRECCIN, PUNTOS) REGALOS(COD_R, NOM_R, PUNTOS, STOCK) SOLICITUDES (DNI, COD_R, FECHA) ENVIOS (DNI, NOM_R, FECHA, TEXTO) Se pide codificar un procedimiento en PLSQL que reciba como parmetro dos fechas y gestione las solicitudes de regalos recibidas por los clientes del banco entre ambas fechas de la siguiente forma: En primer lugar se comprobar que el cliente existe. Si no es as, se disparar una excepcin y se enviar el mensaje EL CLIENTE NO EXISTE. A continuacin, se llamar a una subrutina REGALO a la que se le pasar como parmetro el DNI del cliente, el cdigo del regalo solicitado y los puntos de que dispone el cliente, y la misma devolver el nombre de regalo correspondiente y un TEXTO. La subrutina comprobar que el cliente tiene suficientes puntos para conseguir el regalo.

Si no tiene suficientes puntos, devolver al programa principal el nombre del regalo y el texto PUNTOS INSUFICIENTES.

Si dispone de suficientes puntos, comprobar que el STOCK del regalo es superior a 0. Si no es as devolver el nombre del regalo y el texto REGALO NO DISPONIBLE.

En caso de que s haya disponibilidad, actualizar el stock del regalo restando 1 unidad, adems se restarn los puntos del regalo del saldo de puntos del cliente y la subrutina devolver el nombre del regalo y el texto OK.

Una vez devuelto el control al programa principal, ste insertar una fila en la tabla ENVIOS, con el DNI del cliente, el nombre del regalo y texto recibidos y la fecha del sistema. Para terminar, se contabilizar el nmero de regalos que se van a atender satisfactoriamente en dicha fecha. En el caso de que sea superior a 100, se disparar una excepcin y se enviar el mensaje DEMASIADOS REGALOS.

43

DESCRIPCIN DE LAS TABLAS 1.- Base de datos HOSPITAL Tabla ENFERMO

INSCRIPCION Nmero de inscripcin del enfermo (5 dgitos numricos) APELLIDO Apellido (12 caracteres) DIRECCION Direccin (20 caracteres) FECHA_NAC Fecha de nacimiento (tipo fecha) S Sexo (1 carcter) Puede ser:

M: Masculino F: Femenino

NSS Nmero de Seguridad Social DOCTOR_NO Nmero del doctor (3 dgitos numricos) Tabla HOSPITAL

HOSPITAL_COD Cdigo del Hospital (2 dgitos numricos) NOMBRE Nombre (12 caracteres) DIRECCION Direccin (20 caracteres) TELEFONO Telfono (8 caracteres) NUM_CAMA Nmero de camas del Hospital (4 dgitos numricos) Tabla SALA

HOSPITAL_COD Cdigo del Hospital (2 dgitos numricos) SALA_COD Cdigo de la sala (2 dgitos numricos) NOMBRE Nombre de la sala (20 caracteres) NUM_CAMA Nmero de camas de la sala (4 dgitos numricos) Tabla PLANTILLA

HOSPITAL_COD Cdigo del Hospital (2 dgitos numricos) SALA_COD Cdigo de la sala (2 dgitos numricos) EMPLEADO_NO Nmero del empleado trabajando en esa sala (4 dgitos

numricos) APELLIDO Apellido del empleado (16 caracteres) FUNCION Funcin desempeada por el empleado (10 caracteres) TURNO Turno de trabajo (1 carcter). Puede ser:

M: Maana T: Tarde N: Noche

SALARIO Salario anual (10 dgitos numricos)

2.10. TABLAS DE EJEMPLO

44

Tabla DOCTOR

HOSPITAL_COD Cdigo del Hospital donde trabaja el mdico (2 dgitos numricos)

DOCTOR_NO Nmero del doctor (3 dgitos numricos) APELLIDO Apellido (16 caracteres) ESPECIALIDAD Especialidad (16 caracteres) 2.- Base de datos EMPRESA Tabla EMP

EMP_NO Nmero del empleado (4 dgitos numricos) APELLIDO Apellido (16 caracteres) OFICIO Oficio (10 caracteres) DIR Nmero del director o jefe del empleado (4 dgitos

numricos) FECHA_ALT Fecha de alta en la empresa (tipo fecha) SALARIO Salario mensual (9 dgitos numricos) COMISION Comisin (9 dgitos numricos) DEPT_NO Nmero de departamento donde trabaja (2 dgitos

numricos, dato obligatorio) Tabla DEPT

DEPT_NO Nmero del departamento DNOMBRE Nombre del departamento (14 caracteres) LOC Localizacin del departamento (13 caracteres)