Desarrollo de aplicaciones en lenguajes de 4º generación 2º Evaluación El lenguaje SQL como lenguaje Huésped El lenguaje SQL puede actuar como si fuera un lenguaje huésped, es decir, que puede interactuar dentro de un lenguaje distinto como puede ser el cobol, el visual Basic, el C++, etc. Un ejemplo de esto en c sería: #include EXEC SQL BEGIN DECLARE SECTION; CHAR dni[6]; CHAR cod_cli[7]; CHAR user_name[7]; CHAR password[8]; FLOAT cuota; EXEC SQL END DECLARE SECTION; VOID sql_error ( ); MAIN ( ) { EXEC SQL WHENEVER sqlerror DO sql_error (Error Oracle) ... ... EXEC SQL CONNECT :username IDENTIFY BY :password; EXEC SQL SELECT cod_cli INTO :cod_cli FROM clientes WHERE dni = :dni; ... ... EXEC SQL COMMIT RELEASE; EXIT(0) 1

} VOID sql_error (CHAR *msg) { PRINTF (\N%S,msg) EXEC SQL ROLLBACK RELEASED EXIT(1); } El oracle mantiene una variable llamada SQLSTATE en la cual guarda codigos de retorno cuando se interactua con el. De esta forma se puede saber si se ha producido un error a la hora de manejar el oracle. Otras variables de este tipo son SQLCODE y SQLERR. Para hacer el control de errores se ha escrito la linea: EXEC SQL WHENEVER sqlerror DO sql_error (Error Oracle) Con esta linea le especificamos al oracle que en el momento en el que ocurra un error ejecute la función SQL_ERROR. También le podíamos haber dicho que hiciera otras cosas como: • CONTINUE • GOTO etiqueta • Función También se puede controlar con esta sentencia que cuando no halla error haga algo, cambiando SQLERROR por NOTFOUND después de la palabra WHENEVER. El nombre de las variables que van a intervenir en las sentencias de SQL van siempre precedidas por el carácter `:'. A la hora de hacer un select tenemos que especificarle a donde van a ir a parar las filas que son el resultado. Para ello tenemos la palabra INTO, con el cual le especificamos a donde va a ir el resultado del select. CURSORES A la hora de hacer un select en el cual llegan muchas filas tenemos el objeto cursor. #include EXEC SQL BEGIN DECLARE SECTION CHAR USERNAME[7]; CHAR DNI[9] FLOAT CUOTA; EXEC SQL END DECLARE SECTION; 2

EXEC SQL DECLARE socios CURSOR FOR SELECT cod_cli, cuota FROM clientes ORDER BY cuota; ... ... EXEC SQL OPEN socios; FOR (I=1;I<=5;I++) { EXEC SQL FETCH socios INTO :cod_cli,:cuota ... ... } EXEC SQL CLOSE socios; ... ... EXEC SQL COMMIT RELEASE EXIT (0) } Se declara primero un cursor, con una select en concreto, y cuando se abre dicho cursor se ejecuta la select, pero sin hacer ninguna salida. Después tendremos que ir recuperándolas de una en una. El oracle tendrá todas las filas guardadas con un apuntador que se irá desplazando a medida que las vamos utilizando. Las filas se recuperan con la línea FETCH. Cada vez que se ejecuta una de estas sentencias el apuntador pasará a la siguiente fila a utilizar, y este apuntador no podrá volver atrás puesto que las líneas que ya se han recuperado son borradas de la memoria del oracle. Así que tenemos una estructura de recuperación de líneas que leerá las filas de resultado de las sentencias oracle de forma secuencial. PRECOMPILACIÓN Estos progamas huesped requieren de un programa precompilador que pasará el codigo del programa diseñado en ambos lenguajes (huesped y anfitrion) en el lenguaje del anfitrión para después poder compilarlo de forma normal y corriente. Así, los ejemplos anteriores pasarían a ser programas en C++, sustitullendo las funciones y llamadas de oracle por funciones de c equivalentes, para después poder usar el compilador de C++ en el codigo resultante. Estas partes de programas que interpreta el precompilador son llamadas implicitas a funciones de manejo del oracle. 3

PROGRAMACIÓN CON LLAMADAS EXPLICITAS Una programación de este tipo es una programación en la que no hay llamadas a funciones de SQL, sino que directamente se usarían librerias para el manejo de la base de datos de oracle. De esta forma no sería necesario una precompilación del código puesto que el programa estaría totalmente escrito en un lenguaje anfitrión. Para ello se usarían funciones especiales. La librería que tiene dichas funciones se llama OCI (Oracle Call Interface). Trabajar de esta forma es mucho más difícil y rebuscado que de la otra forma, puesto que tenemos que hacer muchas llamadas a funciones. ODBC: Open DataBase Connectivity Podemos hacer llamadas a funciones dinamicas del OBCD que serán resueltas con llamadas a las funciones de la base de datos. La ventaja de esto es que no se tiene que cambiar la codificación del programa para pasar de una base de datos a otra, es decir, que no importa que base de datos estemos utilizando por debajo del programa porque el codigo será siempre el mismo. PL−SQL Es un lenguaje que añade al SQL las características de un lenguaje procedimental . Las siglas son Procedural Lenguaje − SQL. Este lenguaje tiene que ser resuelto por el sistema gestor de base de datos, pero hay herramientas que tienen un motor de PL−SQL para que solo vaya al sistema gestor de base de datos los datos necesarios mientras que el programa hará otro tipo de procedimientos. En este lenguaje tendremos: • BLOQUES ANONIMOS • TRIGGERS O DISPARADORES • PROCEDIMIENTOS • PAQUETES En los tres últimos el código de PL−SQL está almacenado en la base de datos oracle, y el código de los bloques anónimos está en el fichero de texto que nosotros programemos. Para ello utilizaremos el SQL−Plus ESTRUCTURA DE LOS PROGRAMAS PL−SQL Un programa PL−SQL esta formado por bloques, y un programa ha de estar formado al menos por un bloque. La estructura de cada uno de estos bloques es de la forma siguientes, formado por tres párrafos: [DECLARE declaraciones;] [BEGIN sentencias;] [EXCEPTION excepciones;] El único párrafo obligatorio es el de sentencias, y los demás son opcionales. 4

Cualquier programa de PL−SQL está formado por sentencias gramaticales que admiten los siguientes caracteres: • Las letras del alfabeto en mayusculas y minusculas • Digitos numéricos • Tab, esp, retorno de carro, ... • Signos aritméticos • Signos lógicos • (,),{,},[,],¿,!,... IDENTIFICADORES En el PL−SQL para identificar objetos se usan los identificadores que son un grupo de caracteres definidos de entre los admitidos. Estos deben empezar siempre por una letra y pueden continuar por más letra, dígitos numéricos, símbolo de dólar, símbolo almohadilla o símbolo _ . El máximo numero de caracteres es de 30. Hay una serie de palabras que no se pueden utilizar puesto que son palabras reservadas para el lenguaje PL−SQL como por ejemplo la palabra BEGIN. DELIMITADORES Son los caracteres que separan varibles y constantes para formar expresiones. Los admitidos por el PL−SQL son los siguientes: ARITMETICOS:+, −, *, /, ** COMPARACION: <, >, <=, >=, <>, ¡= DE INICION/FIN DE EXPRESON: (, ) DE TERMINACION DE ORDEN: ; ATRIBUTO: % DE CADENA DE CARACTERES: `' SEPARADOR DE ELEMENTOS: , INDICADOR DE VARIABLES DE ASIGNACION: : OPERADOR DE ASIGNACION: := CONCATENACION DE CADENAS: || COMENTARIO DE LINEA: −− COMENTARIO DE PARRAFO /* */ VARIABLES Y CONSTANTES PL−SQL se comunica con la base de datos oracle por medio de variables y constantes. Estas se declaran en la parte de DECLARE del bloque que hemos visto antes. La estructura para declarar variables en PL−SQL es la siguiente: 5

DECLARE Nombre_variable tipo [CONSTANTE][NOT NULL][:=valor (tambien DEFAULT valor]; Si una variable la definimos como constantes tendremos que asignarle una valor obligatoriamente y después no se podrá modificar dicho valor. Cualquier variable que se halla declarado pero que no se le halla dado un valor inicial tendrán el valor NULL. En el caso de que la declaremos como NOT NULL será obligatorio asignarle un valor a la hora de definirlo. Posibles valores de las variables • BINARY_INTEGER: Este tipo de variable tendrá números enteros en el rango −2147483647 hasta 2147483647. Este tipo de dato admite también subtipos de datos: NATURAL: Números naturales desde 0 hasta 2147483647 POSITIVE: Números naturales estrictamente positivos: desde 1 hasta 2147483647 • NUMBER(precisión, escala): Números en formato numero reales con el rango 1x10−130 hasta 9.99...x10 125 y −9.9...x10 125 hasta −1x10−130 • CHAR (l): Almacena cadenas de caracteres de longitud fija. L establece la longitud de esa cadena, y se no se especifica l adquiere como longitud por defecto 1. En PL−SQL este tipo de dato admite hasta 32767 caracteres, mientras que en oracle solo podía ser de 256 caracteres. Esto quiere decir que no podremos meter en una columna char de oracle una de estas variables. • VARCHAR2(l): Almacena cadenas de caracteres de longitud variable de longitud máxima especificada por l. Es obligatorio especificar la longitud máxima. En PL−SQL la longitud máxima permitida es de 32767 mientras que en oracle el varchar2 podía almacenar hasta 2000 caracteres, con lo cual tenemos el mismo problema que antes. Hay dos subtipos de datos equivalentes a este tipo de dato que se llaman VARCHAR y STRING. • LONG: Contiene cadenas de caraceres de longitud variable de hasta 32760 caracteres. En el caso de que un campo long de oracle tenga un dato de más de 32760 caracteres no se podrá meter en una variable de este tipo. • RAW y LONG RAW: En el caso de RAW son cadenas de longitud variable de datos binarios de longitud l que admiten como máximo 32767. Seguimos teniendo el mismo problema que con el los tipos char. Con el LONG RAW contiene cadenas de caracteres variables de tipo RAW de hasta 32760 bytes. Seguimos teniendo el mismo problema que con el tipo de dato LONG • DATE: Es exactamente el mismo tipo de dato que el de oracle. • BOOLEAN: Solamente admite tres valores: TRUE, FALSE Y NULL. Suele aparecer en estructuras de control. • ROWID: Sirve para almacenar identificadores de filas. **????** • TABLE y RECORD Atrituto type A la hora de declarar variables se pueden definir con el atributo %TYPE para declarar que dicha variable tiene las mismas características que otra variable: v_cod_usuario v_cod_cliente%TYPE; Para que esto sea admitido la variable de ejemplo tiene que estar definida antes de hacer esta declaración. También se puede hacer que una variable sea del mismo tipo de dato que una columna de una tabla de oracle a 6

la que hagamos referencia: v_cod_cliente clientes.cod_cliente%TYPE; SENTENCIAS DE CONTROL En PL−SQL tenemos las sentencias de control de tipo repetitivas, alternativas y Sentencias alternativas Sentencia IF clasica: IF condicion THEN Sentencias; ... ... END IF; Sentencia IF alternativa: IF condicion THEN Sentencias; ...; ELSE Sentencias; ...; END IF Sentencias IF anidadas: IF condicion THEN Sentencias; ...; ELSIF condicion THEN Sentencias; ...;

7

ELSE Sentencias; ...; END IF Sentencia NULL: Es una sentencia que no hace nada. Sentencias repetitivas: El bucle más sencillo y elemental es el LOOP: LOOP Sentencias; ...; ...; END LOOP; Este bucle sería infinito a no ser que en las sentencias le especifiquemos una sentencia de ruptura de bucle: LOOP Sentencias; EXIT; END LOOP; Si queremos que haya una condición de salida podríamos meter el EXIT dentro de un IF o podríamos usar la sentencia EXIT WHEN condición; También se pueden hacer bucles anidades, con lo cual necesitamos hacer sentencias EXIT a niveles. Para ello debemos etiquetar los diferentes bucles de un anidamiento. Para anidar un bucle se hace lo siguientes: <> LOOP ... Un ejemplo de bucles anidados sería el siguiente: <>

8

LOOP Sentencias; <> LOOP Sentencias; EXIT bucle2 WHEN condicion; Sentencias; END LOOP bucle2; Sentencias; END LOOP bucle1; Otro tipo de bucle es el WHILE: WHILE condicion LOOP Sentencias; ...; END LOOP; Las sentencias se ejecutaran mientras la condición sea cierta. Otra estructura repetitiva es el FOR: FOR indice IN [REVERSE] valor_inicial..valor_final LOOP Sentencias; ...; END LOOP; Se repiten las sentencias que hay dentro del for por cada unidad que se pasa desde le valor inicial hasta el valor final. No existe ninguna clausula para que avance de varios en varios. Si se le pone la clausula reverse los valores inicial y final se tomaran al contrario, es decir, que el menor siempre estará en el inicial y el mayor en el final, o de lo contrario da error. Sentencias de control de flujo Sentencia GOTO: Esta sentencia permite bifurcar el flujo del programa a otra posición que tiene una etiqueta. GOTO etiqueta; 9

Después de la etiqueta tiene que haber una sentencia ejecutable. No se puede hacer un goto a una etiqueta que tenga justamente después el final del programa. Para ello tenemos el NULL. CURSORES Declaración de cursores Para declara un cursor se hace lo siguiente: DECLARE CURSOR nombre_cursor IS SELECT .... Ejemplo: CURSOR c1 IS SELECT apellidos, nombre FROM CLIENTES En este momento solo se asocia el nombre de un cursor a una sentencia, pero en ese momento la sentencia SQL todavía no se ha ejecutado. Ejecución de un cursor: Esto se hace dentro del BEGIN. Se ejecuta de la siguiente forma: OPEN nombre_cursor; Es en este momento en el que se ejecuta la cosulta asociada a dicho cursor. Consulta de los datos almacenados en el cursor: Para hacer la consulta primero tenemos que haber abierto el cursor del que queremos obtener los datos. La recuperación de los datos se hace de la forma siguiente: FETCH nombre_cursor INTO variable1, variable2, .... Las variables hay que ponerlas en el mismo orden en el que se han recuperado las colunas. Habra que meter esta sentencia en una estructura repetitiva para que se controle que se metan todos los datos, o que se vean todos los datos. Cerrar un cursor: Una vez que hemos leido todas las filas del cursor se cierra: CLOSE nombre_cursor; Cuando se declara un cursor en la sentencia SQL puede que aparezcan variables, por ejemplo en la cláusula WHERE, para obtener diferentes resultados según un parámetro. Para ello, cuando se abre el cursor, se tendrá en cuenta el valor de las variables que se han puesto en el cursor. El orden de acciones que lleva a cabo es el siguiente: • Sustitución de variables • Envía la sentencia SQL a ORACLE 10

CURSOR c1 IS SELECT cod_cliente FROM clientes WHERE cuota>v_cuota; Cuando se declara un cursor se puede al mismo tiempo declarar las variables que utiliza el mismo cursor: DECLARE CURSOR nombre_cursor [variable tipo [:=valor]] IS SELECT .... Ejemplo: CURSOR c1 (cuota_minima NUMBER) IS SELECT descripcion FROM actividades WHERE cuota > cuota_minima; Después a la hora de abrir el cursor se tendrá que incluir los valores para esas variables: OPEN c1 (5000); En el caso de que sean varias las variables los valores en la llamada irán separados por `,'. Atributos Hay cuatro atributos diferentes cuando se trabaja con cursores y que podemos utilizar: %NOTFOUND: Este cursor devuelve el valor TRUE cuando al ejecutar una FECTH no se ha encontrado ninguna fila en el resultado. Será falso si el último FETCH ha devuelto una fila. Será NULL cuando se ha hecho un OPEN al cursor pero todavía no se ha hecho ningún FETCH. La sintaxis es: nombre_cursor%NOTFOUND Es util para controlar el bucle de lectura de los registros del resultado de un cursor. LOOP FETCH nombre_cursor INTO variables; Sentencias; EXIT WHEN nombre_cursor%NOTFOUND; END LOOP; %FOUND: Este atributo devolverá TRUE cuando el último FETCH ha encontrado una fila como resultado y será FALSE cuando no haya devuelto ninguna fila como resultado. Devolverá NULL cuando no se halla ejecutado ningún FETCH desde el último OPEN del cursor que ha sido ejecutado. LOOP WHILE nombre_cursor%FOUND; FETCH nombre_cursor INTO variables; Sentencias; END LOOP; 11

%ROWCOUNT: Este atributo devuelve el numero de filas recuperadas por el FETCH. Sirve para recuperar un numero de filas en concreto. Este número se irá incrementando cada vez que se ejecuta un comando FETCH. LOOP FETCH nombre_cursor INTO variables; Sentencias; EXIT WHEN nombre_cursor%ROWCOUNT > 100; END LOOP; %ISOPEN: Este atributo nos devuelve TRUE si el cursor está abierto y FALSE si está cerrado. Ejercicios: • Actualizar el nombre del cliente , `A1111' a Antonio José • Actualizar la cuota del cliente `A1111' incrementándosela en un 10% si la antigüedad es mayor de 2 años o un 8% si es mayor de tres años o un 5% en otro caso. <<>> El campo ROWID sierve como identificativo de cada fila de una tabla. Tiene el formato: ROWID XXXXXXXX.YYYY.ZZZZ XXXXXXXX Es el numero del datablock en la que está metida dicha fila dentro de un tablespace YYYY Es el numero de fila que ocupa en el datablock de un tablespace concreto ZZZZ Es el identificativo del fichero en el que está metido dicho datablock Cursores implicitos Cada vez que nosotros hacemos un SELECT sin usar ningún cursor, en realidad el SQL esta creando un cursor. De esta forma, cuando nosotros sabemos que el resultado es un unico valor y lo introducimos en una variable podemos usar las propiedades de los cursores, con la excepción de que no hay nombre y tendremos que usar las propiedades de la siguiente forma: SQL%NOTFOUND Un cursor de este tipo nunca está abierto, porque cuando se ejecuta una sentencia de este tipo hace el solo las sentencias OPEN, FETCH y CLOSE en la misma sentencia, y por lo tanto no se podrá usar la propiedad ISOPEN puesto que siempre va a estar cerrado. Cláusula WHERE CURRENT OF Con esta clausula nos podemos referir a la fila actual que hay en el fetch para poder modificar los valores de dicha fila.

12

DECLARE V_cod_cliente VARCHAR2(2); CURSOR C1 IS SELECT cod_cliente, nombre FROM CLIENTES WHERE cod_cliente = `A1111' V_nombre VARCHAR(20); BEGIN OPEN c1; LOOP FETCH c1 INTO v_cod_cliente, v_nombre; UPDATE clientes SET v_nombre='Pedro' WHERE CURRENT OF c1; END LOOP; END; REGISTROS DE DATOS RECORD Y TABLE Tablas: Una tabla es un conjunto de datos de tipo uniforme y los datos van referenciados por un indice que es de tipo binary_integer. Para hacer se declara un tipo de tabla y una vez hecho esto se declara una tabla asociada a ese tipo de dato de tabla. Declarar un tipo de tabla se hace de la siguiente forma: TYPE tipo_tabla IS TABLE OF {tipo_dato|variable%TYPE | table.columna%TYPE } [NOT NULL] INDEX BY BINARY_INTEGER Después se puede declarar una o varias tablas con el tipo de dato que hemos definido. Nombre_tabla tipo_tabla El tamaño máximo de la tabla viene dado por el numero máximo de ocurrencias que puede tener el índice. El espacio que ocupa una tabla en memoria es dinamico, es decir, que va aumentando según le vamos metiendo datos. Registros: Es un conjunto de campos que no son necesariamente del mismo tipo de datos a los que nos podremos referir en conjunto. Un ejemplo de declaración de registro es el siguiente: TYPE tipo_registro IS RECORD

13

( campo1 {tipo_dato | variable%TYPE | tabla.columna%TYPE} campo2 { } ... ) Después, para crear un registro se hace lo siguiente: Nombre_registro tipo_registro; Hay un atributo que se llama ROWTYPE que permite crear un registro con los tipos de datos de una tabla. De esta forma tendriamos un registro que tiene los mismos datos que una tabla. Nombre_registro tabla%ROWTYPE INSTRUCCIÓN FOR PARA REGISTROS FOR nombre_registro IN nombre_cursor LOOP ... ... END LOOP Con esta instrucción no tenemos que preocuparnos de abrir el cursor porque lo hace el mismo y en cada pasada se encarga de recuperar la fila siguiente del cursor. Se puede romper la estructura repetitiva con la sentencia EXIT. EJERCICIOS: • Crear un programa SQL que actualice las cuotas de los clientes activos con el criterio: • Antigüedad menor de 2 años a 10% • Antigüedad menor de 3 años y mayor de 2 a 8% • Antigüedad mayor de 3 años a 5% • Crear una tabla como la de clientes. Construir el PL/SQL que carge en la nueva tabla las filas de la tabla clientes. • Para mantener los saldos de la tabla de prestamos de empleados. • Preparar un programa para mantener la tabla de pagos de prestamos. • Crear una tabla HIST_INCR de histórico de incrementos en la aplicación de GIMNASIOS Columnas: Cod_cliente Fecha_incremento

14

Porc_incremento Cuota_previa (hija de clientes) Preparar un PL/SQL que incremente la cuota de los clientes del gimnasio que tienen actividad `Gimnasia de mantenimiento' en: 5% si solo tienen esa actividad, 3% si tienen alguna actividad más. Y mantener la tabla HIST_INCR Transacciones Es un conjunto de operaciones que se trataba como un todo. Son un conjunto de operaciones que no son operativas hasta que se ejecuta un commit. Funcionan de una manera de todo o nada. Una transacción comienza cuando termina la anterior o cuando comienza el programa o bien después de una sentencia de definición de datos. El final de una transacción se sucede cuando se ejecuta un commit y además los cambios son confirmados en la base de datos; cuando se ejecuta la instrucción rollback, momento en el que los cambios no se confirman; cuando se codifica un comando DDL; o cuando se finaliza el programa. En un programa PL/SQL se asume por defecto que si el programa acaba con éxito se ejecute la sentencia commit, y en el caso de que se produzca un error se ejecute la sentencia rollback. También se puede poner a lo largo del programa un savepoint en el cual se van a salvar los datos hasta ese punto en caso de que se suceda un error en el resto del programa. La forma de usar estos puntos seguros es especificando en cualquier punto del programa: SAVEPOINT identificativo; Después se puede especificar un rollback to identificativo, con lo cual se ejecutaría un rollback hasta el savepoint que hemos especificado. Si ejecutáramos un Rollback se desharía toda la transacción. SET TRANSACTION Es una sentencia que tiene que ser la primera de un programa PL/SQL. Con esta sentencia se puede codificar que una transacción sea solo de consulta: (SET TRANSACTION READ ONLY). Con esta cualidad además el programa tendrá integridad de lectura, esto quiere decir que al principio de un programa se guardaran los datos que se van a usar, para que si cualquiera de las tablas es modificada durante la ejecución por otro usuario, nosotros trabajemos con las originales del principio del programa. También nos permite usar segmentos de rollback especificos (SET TRANSACTION USE ROLLBACK SEGMENT nombre_segmento) Esto sirve para especificar segmentos más grandes para el caso de que el segemento por defecto que nos asigna oracle se quede pequeño. Apartado EXCEPTION Sirve para codificar programa en el caso de que se produzca un error durante la ejecución del apartado BEGIN. Cuando ocurre un error en el programa lo primero que hace es ir a la sección de exception para ver si hay una rutina para dicho error. WHEN no_data_found THEN ... 15

Cuando ocurre un error se acabaría el bloque begin de esa excepción y pasaría al anterior en el caso de que existiera. Si no existe se acaba el programa. Si no encuetra ese error en le bloque exceptión de ese bloque pasará al del bloque anterior hasta que le encuentre o hasta que se termine el programa. Algunos errores son: • CURSOR_ALREADY_OPEN • DUP_VAL_ON_INDEX • INVALID_CURSOR • NO_DATA_FOUND • TOO_MANY_ROWS • ZERO_DIVIDE • NOT_LOGGED_ON: cuando se ha perdido la conexión con el oracle • PROGRAM_ERROR: error interno del programa. • TIMEOUT_ON_RESOURCE: cuando no se puede coger un recurso porque le tiene cogido otro usuario. Se puede usar la clausula OTHER para indicar el caso de que ocurra cualquier error que no este expecificado. CONTROLES DE ERRORES Nosotros podemos declarar también un control de error exception DECLARE ... ... cuota_mala EXCEPTION; .. BEGIN ... ... if registro_cuota IS NULL THEN RAISE cuota_mala; ... EXCEPTION ... WHEN cuota_mala THEN Sentencias; 16

También podemos asociar un error del Oracle a un error del PL/SQL con la instrucción: PRAGMA EXCEPTION INIT (nombre excepcion, numero de error) Ejemplo: PRAGMA EXCEPTION INIT (segment_rollback_lleno,−1626) Y después podremos preguntar por el en la sección de EXCEPCIONES. Variables de errores Oracle devuelve dos tipos de variables cuando se produce un error SQLCODE: devuelve el código del error que se ha producido en el oracle SQLERRM: devuelve el mensaje de error. Nosotros podemos usar esas dos variables en la sección de EXCEPIONES. En el caso de que esté controlada en la zona de OTHER (en la que se controlan todos los errores no especificados) se podrá usar la variable para saber que error es. Subprogramas en PL/SQL El Pl/SQL va a tener dos tipos de subprogramas: procedimientos y funciones. Un procedimiento es una parte de programa que realiza una serie de operaciones y una función es un subprograma que ejecuta una serie de operaciones y que devuelve un valor. Para definir un procedimiento se hace lo siguiente: PROCEDURE nombre_procedimiento [listado parametros] IS [Declaraciones;] BEGIN Sentencias; EXCEPTION Sentencias; END; La declaración de un procedimiento tiene dos partes, la primera que se llama zona de especificaciones, que es donde aparecen el nombre del procedimiento y la lista de parámetros, y después está el cuerpo del procedimiento. Para invocar a un procedimiento se pone el nombre del procedimiento y después entre paréntesis la lista de parámetros. Antes de poder invocar a un procedimiento este tiene que estar declarado. Un procedimiento puede estar archivado en la propia base de datos, lo que da a un nuevo objeto de oracle que se llama procedimiento. La lista de parámetros, cada parámetro, esta compuesta por el nombre del parámetro y el tipo de parámetro y a continuación indicar cual es el tipo de dato. Después se le puede asignar un valor por defecto. Un parámetro es de tipo IN cuando se le pasa al procedimiento y este no le puede modificar, porque el procedimiento lo trata como una constante. Este es el tipo de parámetro por defecto. El tipo de parámetro OUT es un parámetro que te va a devolver un valor del procedimiento. Un parámetro de tipo IN OUT es un parámetro de los que se le 17

pasa al procedimiento con un valor y te puede devolver otro valor. Cuando se le especifica el tipo de dato no se puede especificar la longitud del dato. En una base de datos podemos crear procedimientos. Esto va a ser un programa PL/SQL que está guardado dentro de la base de datos de Oracle. Para crear un procedimiento dentro de una base de datos usaremos la sentencia siguiente: CREATE PROCEDURE nombre del procedimiento [listado de parámetros] AS [Declaraciones;] BEGIN Sentencias; [EXCEPTION Sentencias;] END; Para consultar los procedimientos que tenemos, los podemos consultar en la tabla USER_SOURCE. Si queremos que otro usuario pueda usar nuestros procedimientos tenemos que darle el privilegio execute: GRANT EXECUTE ON nombre_procedimiento TO usuario. Funciones Para invocar a una función tendrá que estar metida dentro de una expresión. Para definir una función se hara lo siguiente: FUNCTION nombre_función (lista de parámetros) RETURN tipo_dato IS [Declare local] BEGIN Sentencias; [EXCEPTION sentencias;] END; En este caso tampoco hay que especificar la palabra clave declare. La lista de parámetros de las funciones tiene la misma estructura que la de las funciones. Las funciones también son un objeto de oracle que se pueden insertar en la base de datos oracle. Para crear una función existe el comando CREATE [OR REPLACE] FUNCTION nombre_función (lista de parámetros) RETURN ... 18

Para dar privilegios sobre las funciones a otros usuarios se hace lo mismo que con los procedimientos. PACKAGES Es un objeto de la base de datos que permite agrupar de una forma lógica procedimientos, funciones, cursores, declaraciones de tipo de variable, declaraciones de excepciones, etc...todo aquello que se pueda imaginar que forma parte del desarrollo de una aplicación. Agrupar todo esto en un paquete facilita el hecho de simplificar la gestión de los permisos ALTER PROCEDURE nombre COMPILE; ALTER FUNCTION nombre COMPILE; ALTER PACKAGE nombre_paquete COMPILE; Oracle puede leer de una sola vez diversos objetos que hay dentro de un paquete, con lo que aumenta el rendimiento de la aplicación. Un package tiene dos partes: una que son las expecificaciones del paquete y otra que es el cuerpo del paquete. Cuando se crea el cuerpo del paquete se define el cuerpo de los procedimientos (o cualquier otro objeto) que hay en el paquete. En las expecificaciones se definen que objetos hay. CREATE [OR REPLACE] PACKAGE nombre IS Declaraciones de tipo; Declaraciones de variables; Declaraciones de constantes; Declaraciones de excepciones; Declaraciones de cursores; Declaraciones de subprogramas (procedimientos y funciones) END nombre_paquete; EJEMPLO DE CREACION DE PAQUETE CREATE PACKAGE emp_activ AS FUNCTION create_dept (dname VARCHAR2, loc VARCHAR2) RETURN number; PROCEDURE remove_emp (no_emp NUMBER); PROCEDURE remove_dept (no_dept NUMBER); PROCEDURE incr_sal (no_emp NUMBER, sal_incr NUMBER); No_comm EXCEPTION;

19

No_sal EXCEPTION; END emp_activ; Paquetes por defecto DBMS_OUTPUT: Tiene una series de subprogramas de los cuales uno de ellos permite mandar mensajes al usuario por pantalla desde el programa PL/SQL: PUT y PUT_LINE PUT permite mandar un mensaje por pantalla al usuario pasándole una variable de tipo char. PUT_LINE: Para invocar a este procedimiento es pasarle como parametro una variable de tipo character. En realidad envía el texto a un buffer intermedio. Cuando estemos utilizando SQL Plus y ejecutamos este procedimiento esos mensajes nos los escribe a la pantalla cuando se finaliza el programa PL/SQL. No escribe el mensaje cada vez que se hace una llamada al procedimiento. Para que esto sea así hay que activar a On una variable del Oracle llamada SERVEROUTPUT. DBMS_STANDARD RAISE_APLICATION_ERROR: Esta función tiene una serie de parametros que nos permiten activar errores de usuario. Disparador o Trigger Es un objeto, que es un conjunto de PL/SQL, que se ejecuta inmediatamente después de ejecutar una instrucción de la base de datos como puede ser un INSERT o un DELETE. Cuando se ejecuta dicha instrucción se dispara el trigger (de ahí viene su nombre). La sintaxis para crear un disparador es la siguiente: CREATE [OR REPLACE] TRIGGER nombre {BEFORE|AFTER} {DELETE [OR INSERT][OR UPDATE[OF columnas]]} ON tabla [REFERENDIG {OLD AS | NEW AS } [FOR EACH ROW] [WHEN condición] DECLARE BEGIN END; Puede haber disparadores de dos tipos: Hay disparadores que se ejecutan una vez por cada fila que se produce (Triger a nivel de fila), o de los que solo se ejecuta una vez para todas la filas (Trigger a nivel de sentencia). Para especificar que se ejecute para cada fila se pone la expresión FOR EACH ROW. La condición limita el rango de filas para las cuales se ejecuta el trigger. Si queremos que la condición sea a valores de antes de modificarse la fila tendremos que especificar old antes de la columna, y new para decirle que sea el valor después de modificar la fila. La clausula REFERENDING sirve para renombrar las palabras old o new por cualquier otra palabra. EJERCICIOS: • Borrar las filas de CLIENTES2. Crear un procedimiento que copie desde CLIENTES los N clientes de mayor cuota. N será un parámetro pasado al procedimiento. Contemplar el caso que haya más de un cliente con la misma cuota en cuyo caso si aparece uno debe aparecer el otro. Preparar un PL/SQL que ejecute el procedimiento para un caso concreto. 20

• Crear una tabla de mensajes con dos columnas ID_mensaje, mensaje de tipos number y varchar2(2000) para introducir mensajes de las aplicaciones. Preparar un PL/SQL para recuperar todos los departamentos y empleados de cada departamento, introduciendo los resultados en la tabla mensajes. Utilizar para ello dos cursores en lugar de un JOIN entre las dos tablas. • Tratamiento de excepciones. Preparamos un PL/SQL para seleccionar el sueldo concreto de un empleado. Le pedimos este sueldo a través de una variable de usuario de SQL*Plus (utilizar un accept/prompt): • Si hay más de un empleado con el sueldo utilizar a través de una rutina de excepciones, un mensaje `Mas de un empleado con salario: . • Si no hay ninguno, tratar el error e introducir un mensaje en tal sentido • Sí hay un empleado con ese sueldo introducir en la tabla de mensajes el nombre del empleado y su salario. • Gestionar cualquier otro error introduciendo un mensaje del tipo `Se ha producido algún otro error' • Tratamiento de excepciones de usuario. A partir de un salario introducido por teclado por el usuario y almacenado en una variable de usuario (utilizar ACCEPT/PROMPT) escribir un bloque PL/SQL que nos diga cuantos empleado tienen un salario mayor o menor a 20000 el salario introducido por el usuario. De tal forma que si no hay ninguno le muestre un mensaje indicándole tal condición, controlando esta situación con una excepción. Si hay uno o más empleados mostrar un mensaje indicándole al usuario el número de empleados, controlando esta situación con una excepción también. Y por último controlar cualquier otro tipo de error con otro mensaje. • Manejo de funciones. En la aplicación de empleados crear una función que devuelva un mensaje al usuario con la descripción de un Departamento cuando se la pasa el código de un Departamento, controlar con una excepción la posibilidad de que el departamento no exista con un mensaje al usuario. Preparar posteriormente un PL/SQL que utilice la función a partir de un código de departamento introducido por el usuario. • Se trata de hacer una programación con PL/SQL, utilizando las tablas de la aplicación de empleados , para realizar el cálculo de la nómina para los empleados de la empresa ficticia cuyos datos se soportan en estas tablas. No se trata de un cálculo de nómina real y algunos supuestos no se ajustan a ninguna normativa legislativa laboral o fiscal existente. 1.− Vamos primero a realizar algunas adaptaciones de los datos en las tablas: 1.1.− En la columna salario_base debe aparecer el salario anual. Luego multiplicar por 14 el valor de esta columna para convertir salario mensual en salario anual. 1.2.− Modificar en prestamo_empleado la columna de interés para los prestamos activos con los siguientes criterios: • Prestamo Ordenador y Electrodomésticos : Interés de 2% • Prestamo Vivienda: 2,5% • Prestamo Automóvil : 3% Nota: La fórmula para calcular el interés compuesto es p*(1+i/100)**n. Donde la cantidad prestada es : p , el interés aplicado es: i, y el número de años es :n 1.3.− Modificar la tabla de Categorías para recoger las siguientes: • Cat 1: Directivo • Cat 2: Analista Funcional • Cat 3: Programador. • Cat 4: Técnico de Explotación. 21

• Cat 5: Tecnico de Sistemas. 1.4.− Modificar los empleados para que tengan este nuevo rango de categorías. 1.5.− Crear una tabla con la codificacion de horas extraordinarias: • Cod_extr number(1) • Descripción varchar2(15) Cargarla con los siguientes datos : (1,Estructurales),(2,Requerimientos), (3,Urgentes) 1.6.− Crear una tabla de valoración de horas extraordinarias (debe ser hija de la tabla anterior): • Cod_extr • Cod_cat • Valoracion number(4) Cargarla con los siguientes datos: INSERT INTO xxxxxx VALUES (1,2,4000); INSERT INTO xxxxxx VALUES (2,2,4500); INSERT INTO xxxxxx VALUES (3,2,5000); INSERT INTO xxxxxx VALUES (1,3,3000); INSERT INTO xxxxxx VALUES (2,3,3500); INSERT INTO xxxxxx VALUES (3,3,4000); INSERT INTO xxxxxx VALUES (1,4,3000); INSERT INTO xxxxxx VALUES (2,4,3500); INSERT INTO xxxxxx VALUES (3,4,4000); INSERT INTO xxxxxx VALUES (1,5,4000); INSERT INTO xxxxxx VALUES (2,5,4500); INSERT INTO xxxxxx VALUES (3,5,5000); 1.7.−Crear una tabla para recoger el número de horas extraordinarias realizadas por los empleados (esta tabla de be ser hija de la tabla de empleados y de la tabla de Códigos de Horas extraordinarias): • Cod_empl • Cod_extr • Mes • Año • Nº de horas extraordinarias

22

Cargarla con los siguientes datos: INSERT INTO xxxxxx VALUES (1,2,12,1999,10); INSERT INTO xxxxxx VALUES (1,3,12,1999,3); INSERT INTO xxxxxx VALUES (4,2,12,1999,28); INSERT INTO xxxxxx VALUES (5,3,12,1999,10); INSERT INTO xxxxxx VALUES (8,1,12,1999,15); INSERT INTO xxxxxx VALUES (9,2,12,1999,40); INSERT INTO xxxxxx VALUES (12,3,12,1999,10); INSERT INTO xxxxxx VALUES (6,3,12,1999,6); INSERT INTO xxxxxx VALUES (7,1,12,1999,18); 1.8.− Crear una tabla para recoger los gastos de viaje que se han podido generar (debe ser hija de la tabla de empleados): • Cod_empl • Fecha_viaje • Importe Gastos Cargarla con los siguientes datos INSERT INTO xxxxxx VALUES (1,to_date('dd/mm/yyyy','12/12,1999'),25344); INSERT INTO xxxxxx VALUES (3,to_date('dd/mm/yyyy','4/12,1999'),32123); INSERT INTO xxxxxx VALUES (6,to_date('dd/mm/yyyy','18/12,1999'),143533); INSERT INTO xxxxxx VALUES (1,to_date('dd/mm/yyyy','21/12,1999'),25344); INSERT INTO xxxxxx VALUES (8,to_date('dd/mm/yyyy','15/12,1999'),54321); INSERT INTO xxxxxx VALUES (11,to_date('dd/mm/yyyy','3/12,1999'),13765); INSERT INTO xxxxxx VALUES (8,to_date('dd/mm/yyyy','3/12,1999'),52123); INSERT INTO xxxxxx VALUES (10,to_date('dd/mm/yyyy','14/12,1999'),132324); INSERT INTO xxxxxx VALUES (2,to_date('dd/mm/yyyy','2/12,1999'),21005); 1.9.− Crear una tabla de resultados de nómina (que será hija de la tabla de empleados): Cod_empl Fecha_nómina 23

Mes_nom Ano_nom Salario Antig Gast_viaje Hor_extr Ret_IRPF Ret_seg_soc Ret_paro Ret_prest 2.− Vamos a realizar algunas consideraciones que serán necesarias para realizar el cálculo. 2.1.− El porcentaje de retención de IRPF para un trabajador se calculará en función de su salario anual: por cada 220000 ptas que supere el 1500000 de salario anual se le retendrá un punto con una límite del 56%. Este porcentaje se aplicará sobre la suma de todos los conceptos excepto sobre los Reembolsos por Pagos de Gastos de Viaje que no tienen retención. 2.2.− El porcentaje de contribución a la Seguridad Social será fijo para todos los trabajadores: 4,7%. Se aplica exclusivamente sobre el concepto salario. 2.3.− El porcentaje de retención para Desempleo y Formación Profesional, será fijo para todos los trabajadores: 1,7% 2.4.− Los trienios en esta empresa se pagan a 3535 ptas de manera lineal a todos los trabajadores. Los trienios se cuentan por años naturales. 2.5.− El número de horas extraordinarias por trabajador y mes no pueden superar las 25 horas. Sí se superan se traslada el exceso al mes siguiente para proceder a su pago en la próxima nómina. 3.− Se trata con este ejercicio de hacer un programa que calcule la nómina de los empleados de esta empresa ficticia. Utilizar para ello todos los procedimientos y funciones que se estimen necesarios. En el cálculo de la nómina se han de tener en cuenta los siguientes puntos: • Pago de Salario. • Pago de horas Extraordinarias. Con sus restricciones. • Pago de Antigüedad (trienios). • Pago de Gastos de Viaje. • Retenciones por IRPF. • Retenciones por pagos a la Seg. Social y Desempleo y Formación Profesional • Retenciones por pagos de prestamos activos. Y tener en cuenta la siguiente consideración: 24

• El cálculo de la nómina tiene que hacerse de tal forma que sí se produce un error en el cálculo de la nómina de un empleado, se debe dar por válido el cálculo hasta el empleado inmediatemente anterior, no considerar lo realizado para el empleado con problemas y que el programa se pueda rearrancar desde el cálculo del empleado donde nos canceló la última vez. Se precisan obtener los siguientes productos: 3.1.− EL cálculo de la nómina de los empleados. Los resultados de nómina se irán recogiendo en la tabla de Resultados de Nómina creada en el apartado 1.9. 3.2.− Un informe con el importe de la retención aplicada para Hacienda. Este informe irá desglosado por empleados (nombre, dni, importe de la retención) con un total de la retención aplicada en conjunto. Informe para Hacienda. 3.3.− Informe para la Seg. Social con el total de lo retenido en la empresa por los dos conceptos. 3.4.− Informe de Horas extraordinarias. Se deberá incluir una línea con el total de horas de cada tipo por categoría. Y una suma total de horas de cada tipo. En el informe aparecerá, sí se dá el caso, el nombre y númerod e horas de exceso, el empleado que superó el número de horas máximo por mes. 3.5.− Informe de transferencia bancaria. Un informe con el total de la nómina. Esta información será utilizada para la transferencia bancaria. 3.6.− Un informe con la nómina de los empleados. Con una cabecera con los datos del empleado y posteriormente los diferentes conceptos de la nómina. Para todos estos informes utilizar la tabla de Mensajes a la que se añadirá una nueva columna para poder identificar el Informe. Así cada línea deberá tener un identificativo de Informe un número consecutivo dentro del informe y la información propiamente dicha del Report. Ir dejando las líneas de los diferentes informes en esta tabla para posteriormente con SQL*PLUS poder extraerlos. Desarrollo de Aplicaciones en lenguajes de 4º generación 2º Evaluación 1 21

25