• Author / Uploaded
• Pedro Javier Moreno García

Citation preview

I.E.S. TRASSIERRA Córdoba http://www.iestrassierra.com

Departamento de Informática

APUNTES DE PL/SQL Etapa:

Formación Profesional Específica.

Ciclo:

“Desarrollo de Aplicaciones Web”.

Nivel:

Superior.

Módulo:

“Bases de Datos”.

Profesor:

Juan Carlos Pérez Jorge

INDICE: Tema 1.- PL/SQL ................................................................................................ 1 Tema 2.- Procedimientos, Funciones y Paquetes ........................................... 24 Tema 3.- Disparadores (Triggers) ................................................................. 45

I.E.S. TRASSIERRA - Córdoba

PL/SQL

TEMA 1.- PL/SQL.1.1.- Introducción. 1.2.- El bloque PL/SQL. 1.3.- Tipos de datos, conversiones, ámbito y visibilidad. 1.4.- Zona de declaraciones: DECLARE. 1.4.1.- Declaración de variables y constantes. 1.4.2.- Declaración de registros. 1.4.3.- Declaración de cursores. 1.4.4.- Declaración de excepciones. 1.5.- Zona de proceso: BEGIN. 1.5.1.- Sentencias propias de PL/SQL. 1.5.2.- Sentencias LMD. 1.5.3.- Sentencias transaccionales. 1.6.- Zona de excepciones: EXCEPTION. 1.6.1.- Control de errores. 1.6.2.- Excepciones predefinidas. 1.6.3.- Excepciones definidas por el usuario 1.6.4.- Ejecución de excepciones: RAISE 1.6.5.- SQLCODE.1.6.6.- SQLERRM.1.6.7.- Algunos ejemplos de tratamiento de excepciones.1.7.- Ejercicios. 1.7.1.- Ejercicios resueltos. 1.7.2.- Ejercicios propuestos.

1.1.- INTRODUCCIÓN.PL/SQL es un lenguaje de programación procedural estructurado en bloques que amplía el lenguaje estándar SQL, uniendo la potencia de éste con la capacidad de los lenguajes de programación tradicionales. De esta forma, PL/SQL no sólo nos permite manipular los datos de la base de datos ORACLE, sino que dispone de técnicas procedurales como los bucles o el control condicional. Una de las mayores limitaciones del SQL es la imposibilidad de tratar de forma independiente las filas devueltas por una consulta. Esto se consigue con PL/SQL mediante el uso de cursores que ORACLE abre para ejecutar una sentencia SELECT. También nos permite controlar los errores que se pudieran producir (excepciones), como por ejemplo que una consulta no devuelva filas. Existen errores propios de Oracle (excepciones predefinidas) y errores que puede provocar y definir el usuario. El motor PL/SQL se encuentra en el Núcleo de Oracle (RDBMS)

Dep. Informática

1

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Por extensión al SQL, PL/SQL puede acceder a la base de Datos ORACLE porque soporta:     

Sentencias LMD de SQL (SELECT, INSERT, UPDATE, DELETE) Sentencias transaccionales (COMMIT, ROLLBACK, SAVEPOINT) Todas las funciones SQL (numéricas, de caracteres, de fechas, de grupos...) Todos los tipos de predicados (sentencia WHERE) Las propias sentencias de PL/SQL.

1.2.- EL BLOQUE PL/SQL.PL/SQL es un lenguaje estructurado en bloques. Un bloque es un conjunto de sentencias procedurales y SQL. Un bloque PL/SQL consta de tres zonas: Declaraciones: donde se definen las variables, cursores y excepciones de usuario. Proceso: donde se escriben todas las sentencias ejecutables. Excepciones: zona donde se define el control de errores. DECLARE Declaración de variables y ctes ; Declaración de cursores ; Declaración de excepciones ; BEGIN Sentencias ejecutables ; EXCEPTION Control de excepciones ; END ;

DECLARE .......... BEGIN .... DECLARE BEGIN .... EXCEPTION .... END ; ..... EXCEPTION ............ END ;

Los bloques PL/SQL se pueden anidar tanto en la zona BEGIN como en la EXCEPTION, pero no en la zona DECLARE, que es única para cada bloque. Cada bloque debe acabar con el carácter '/' como único de la última línea. Dep. Informática

2

I.E.S. TRASSIERRA - Córdoba

PL/SQL

1.3.- TIPOS DE DATOS, CONVERSIONES, ÁMBITO Y VISIBILIDAD.1.3.1.- Tipos de datos.Cada constante y variable posee un tipo de dato el cual especifica su forma de almacenamiento, restricciones y rango de valores válidos. Con PL/SQL se proveen diferentes tipos de datos predefinidos. Un tipo escalar no tiene componentes internas. Un tipo compuesto tiene otras componentes internas que pueden ser manipuladas individualmente. Un tipo referencia almacena valores, llamados punteros, que designan a otros elementos de programa. Un tipo lob (large object) especifica la ubicación de un tipo especial de datos que se almacenan de manera diferente. En la siguiente figura se muestran los diferentes tipos de datos predefinidos.

Tipos de datos de PL/SQL Los distintos tipos de datos escalares o simples son: 

NUMÉRICOS. Binary_integer.- binario con desbordamiento a number. Usado para almacenar enteros con signo. PL/SQL tiene predefinidos los siguientes subtipos de binary_integer: NATURAL NATURALN POSITIVE POSITIVEN SIGNTYPE Dep. Informática

No negativo. No negativo, no admite nulos. Positivo. Positivo, no admite nulos. -1, 0 y 1, usado en lógica trivaluada.

3

I.E.S. TRASSIERRA - Córdoba

PL/SQL

 Number.- Numérico. PL/SQL tiene predefinidos los siguientes subtipos: DEC DECIMAL NUMERIC DOUBLE PRECISION FLOAT REAL INTEGER INT SMALLINT

Números con punto fijo con precisión máxima de 38 dígitos decimales

Números con punto flotante con precisión máxima de 38 dígitos decimales Números con punto flotante con precisión máxima de 18 dígitos decimales Números enteros con una precisión máxima de 38 dígitos

 Pls_integer.- binario sin desbordamiento. Es más actualizado que su equivalente binary_integer, el cual se mantiene por razones de compatibilidad. 

ALFANUMÉRICOS.    

Char(n).- Array de n caracteres, máximo 2000 bytes. Si no especificamos longitud sería 1 Long.- Array de caracteres con un máximo de 32760 bytes Raw.- Array de bytes con un número máximo de 2000 Long raw.- Array de bytes con un máximo de 32760 Varchar2.- Para almacenar cadenas de longitud variable con un máximo de 32760



BOOLEANOS.- Solo pueden tomar 3 valores TRUE, FALSE y NULL. Usados para lógica trivaluada.



DATE.- Para almacenar datos de tipo fecha.

1.3.2.- Conversiones.Además de las conversiones explícitas realizadas por las funciones de conversión, cuando se hace necesario, PL/SQL puede convertir un tipo de dato a otro de forma implícita. Esto significa que la interpretación que se dará a algún dato será la que mejor se adecue dependiendo del contexto en que se encuentre, por ejemplo cuando variables de tipo char se operan matemáticamente para obtener un resultado numérico. Si PL/SQL no puede decidir a qué tipos de dato de destino puede convertir una variable se generará un error de compilación. Hasta Desde BIN_INT CHAR DATE LONG NUMBER PLS_INT RAW ROWID VARCHAR2

BIN_INT CHAR DATE LONG NUMBER PLS_INT RAW ROWID VARCHAR2 X X

X X

X

X X X X X X X X

X X X

X X

X X

X

X

X

X

X X X

X X

X

X

X

X

X X X X X X X X

X

Tabla de conversiones implícitas Dep. Informática

4

I.E.S. TRASSIERRA - Córdoba

PL/SQL

1.3.3.- Ámbito y visibilidad.Dentro de un programa, las referencias a un identificador son resueltas de acuerdo a su ámbito y visibilidad. El ámbito de un identificador (variable o constante) es aquella región de la unidad de programa (bloque, subprograma o paquete) en la que el identificador mantiene su valor sin perderlo. La visibilidad se refiere a las zonas en que se puede referenciar (usar). Los identificadores declarados en un bloque de PL/SQL se consideran locales al bloque y globales a todos sus sub-bloques anidados. Por eso un mismo identificador no puede declararse dos veces en un mismo bloque pero sí en varios bloques diferentes, cuantas veces se desee. La siguiente figura muestra el alcance y visibilidad de la variable x, la cual está declarada en dos bloques cerrados diferentes.

Puede observarse que la variable más externa tiene un ámbito más amplio pero cuando es referenciada en el bloque en que se ha declarado otra variable con el mismo nombre, es esta última la que puede ser manipulada y no la primera.

1.4.- ZONA DE DECLARACIONES.Es la parte del bloque PL/SQL utilizada cuando es necesario definir variables y/o constantes, cursores o excepciones. Es una zona opcional de forma que si no hacen falta declaraciones se omite la palabra reservada DECLARE. Sintaxis:

DECLARE declaración_variables; declaración_cursores; declaración_excepciones;

La palabra reservada DECLARE, determina el comienzo de un bloque PL/SQL. Las definiciones realizadas en esta zona son declaraciones locales que solo se reconocerán en el bloque PL/SQL actual y en los bloques anidados que pudiese contener. Dep. Informática

5

I.E.S. TRASSIERRA - Córdoba

PL/SQL

1.4.1.- Declaración de variables y constantes.Las variables se utilizan para guardar valores devueltos por una consulta o almacenar cálculos intermedios. Las constantes son campos que se definen y no alteran su valor durante el proceso. Sintaxis:

Identificador

[CONSTANT]

[NOT NULL] [:= ]; dónde: identificador

Nombre de la variable o constante.

[CONSTANT]

Palabra reservada para la definición de constantes.

tipo_de_dato

Tipo de dato de la variable.

identificador%TYPE Declara la variable o constante con el mismo tipo de dato que una variable definida anteriormente o que una columna de una tabla. Identificador es el nombre de la variable PL/SQL definida anteriormente, o el nombre de la tabla y la columna de la base de datos. Identiticador%ROWTYPE Este atributo declara una fila variable con campos con los mismos nombres y tipos que las columnas de una tabla o de una fila recuperada de un cursor. Al declarar una fila %ROWTYPE, no se admite ni la constante, ni la asignación de valores. NOT NULL

Obliga a que siempre tenga valor.

expresión_plsql / valor_constante Asigna a la variable o constante el valor inicial como resultado de una operación (expresión pl/sql) o con un valor constante. Ejemplos: varn_dept total respuesta var2 registro1

tdepto.numde%TYPE ; number(10,2):=0 ; char(1) ; varn_dept%TYPE ; dept%ROWTYPE ;

1.4.2.- Declaración de registros.Los registros son grupos de variables que pueden recibir la información de un cursor. Se declaran según la siguiente sintaxis: TYPE nombre_registro campo1 campo2 .... var nombre_registro;

IS RECORD ( tabla.columna%TYPE tabla.columna%TYPE ) ;

Ejemplo: TYPE

reg IS RECORD ( empleado temple.nomem%TYPE salario temple.salar% TYPE r_empleado reg;

Dep. Informática

-- Declaración del tipo de dato reg ); -- Declaración de una variable de tipo reg

6

I.E.S. TRASSIERRA - Córdoba

PL/SQL

1.4.3.- Declaración de cursores.La declaración de un cursor le proporciona un nombre y le asocia una consulta (SELECT). El cursor es un área de trabajo que utiliza ORACLE para consultas que devuelven más de una fila, permitiendo la lectura y manipulación de cada una de ellas. Un cursor tiene tres componentes:  La tabla de resultado obtenida al ejecutar la SELECT.  Un orden establecido entre sus filas.  Un puntero que señala la posición sobre la tabla resultado. Tipos de cursores: Estáticos Se declaran en la DECLARE con su cláusula SELECT asociada, y se abren, ejecutan y cierran en la zona BEGÍN. Pueden ser simples o parametrizados. Dinámicos La SELECT asociada al cursor no se especifica en la zona DECLARE, sino en BEGIN, con lo que el resultado de su ejecución es dinámico. Según se declare o no el registro que recibirá los datos de la SELECT, los cursores dinámicos pueden ser prefijados o no prefijados.  Estático simple Un cursor estático simple se declara en la DECLARE con su cláusula SELECT y se abre, ejecuta y cierra en la zona BEGIN. Sintaxis:

CURSOR

nombre_cursor IS sentencia SELECT ;

Ejemplo: DECLARE CURSOR SELECT

departa IS numde, nomde FROM tdepto ;

 Estático parametrizado Los cursores estáticos parametrizados permiten obtener con el mismo cursor diferentes resultados en función del valor que se le pase al parámetro. Su sintaxis es: CURSOR nombre_cursor (nombre_parámetro tipo_parámetro) IS sentencia_SELECT_utilizando_los_parámtetros ; Los parámetros son variables únicamente de entrada, nunca para recuperación de datos. Estas variables son locales para el cursor y solo se referencian en la SELECT. Si se definen parámetros, éstos deben especificarse en la SELECT y se utilizan igual que si utilizásemos un valor constante. Al abrir el cursor se sustituyen los parámetros por los valores correspondientes. Ejemplo: ACCEPT valor PROMPT Departamento: DECLARE CURSOR empleados (dept_pl NUMBER) IS SELECT numem, nomem FROM temple WHERE numde = dept_pl ; .......... BEGIN OPEN empleados (&valor) ; -- Apertura del cursor y paso del parámetro Dep. Informática

7

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Si en cualquier cursor quisiéramos modificar las filas que nos devuelve, deberíamos añadir a la Select asociada al cursor la cláusula: FOR UPDATE OF nombre_columna ; Ejemplo: DECLARE CURSOR

empleados IS SELECT numem, nomem FROM temple FOR UPDATE OF nomem ;

 Dinámico no prefijado Los cursores dinámicos son aquellos que la SELECT no aparece en la zona DECLARE. En los no prefijados no se concreta la proyección de la SELECT . Sintaxis:

TYPE tipo_cursor IS REF CURSOR ; ... nombre_cursor tipo_cursor ;

Ejemplo: DECLARE TYPE CurEmp IS REF CURSOR .... C_emple1 CurEmp ;

-- Declaración del tipo de cursor -- Declaración de un cursor del tipo anterior

 Dinámico prefijado Los cursores dinámicos prefijados declaran el registro que va a recibir los datos del cursor con RETURN . Sintaxis:

TYPE tipo_cursor IS REF CURSOR RETURN tabla%ROWTYPE ; ... nombre_cursor tipo_cursor ;

Ejemplo: DECLARE TYPE .... Datos.emp

Datos IS REF CURSOR RETURN temple%ROWTYPE ; Datos ;

1.4.4.- Declaración de excepciones. Si vamos a utilizar excepciones definidas por el usuario, es necesario declararlas en la zona DECLARE. Las excepciones de usuario se declaran simplemente escribiendo la palabra EXCEPTION detrás del nombre asignado a la excepción que queramos definir: Sintaxis: nombre_excepción

EXCEPTION ;

El tratamiento de las excepciones, tanto las internas de Oracle como las de usuario, se realiza en la zona EXCEPTION, y lo veremos en el apartado 1.6.

Dep. Informática

8

I.E.S. TRASSIERRA - Córdoba

PL/SQL

1.5.- ZONA DE PROCESO: BEGIN.En esta zona del bloque PL/SQL se escriben todas las sentencias ejecutables. El comienzo del bloque PL/SQL se especifica con la palabra BEGIN. En el bloque se permiten: -

Sentencias propias de PL/SQL.

-

Sentencias DML de SQL (SELECT, INSERT, UPDATE, DELETE)

-

Sentencias transaccionales (COMMIT, ROLLBACK, SAVEPOINT)

1.5.1.- Sentencias propias de PL/SQL.Las sentencias propias del lenguaje PL/SQL se agrupan en: Asignaciones Manejo de cursores EXIT Control condicional Bucles GOTO NULL 1.5.1.1.- Asignación.- La asignación de valores a una variable se hace con el operador ":=". Algunos ejemplos son: salario := 2251 ; comision := substr(cod_postal,2 ,3) ; aumento := salario * 0.1 ; total_sueldo := salario + aumento + comision ;

1.5.1.2.- Manejo de Cursores.- Los cursores que vayan a utilizarse deberán haber sido definidos en la zona DECLARE. Para poder realizar una lectura de todas las filas recuperadas en un cursor, es necesario realizar un bucle. Existen bucles ya predefinidos para recorrer cursores, aunque también se pueden utilizar bucles simples y hacer el recorrido de forma más controlada. Para el manejo de los cursores, podemos utilizar los siguientes atributos predefinidos: %NOTFOUND

Devuelve TRUE si la última lectura falla porque no hay mas filas disponibles o FALSE si recupera una fila. Se utiliza para detectar el final y romper el bucle de lectura de un cursor.

%FOUND

Es lo contrario de %NOTFOUND

%ROWCOUNT

Devuelve el número de fila, procesada por el cursor.

%ISOPEN

Devuelve TRUE si el cursor esta abierto y FALSE si esta cerrado.

Para manejar un cursor primero hay que abrirlo (OPEN), luego leerlo (FETCH) y por último cerrarlo (CLOSE).

Dep. Informática

9

I.E.S. TRASSIERRA - Córdoba



PL/SQL

Abrir el cursor: OPEN. La sentencia OPEN, evalúa la SELECT asociada al cursor y lo prepara para permitir su lectura. La sentencia OPEN, abre el cursor y ejecuta la consulta asociada a dicho cursor. Tipo de cursor

Sintaxis

cursor estático simple

OPEN nombre_cursor ;

cursor estático parametrizado

OPEN nombre_cursor (parm1 [,parm2] .. ) ;

cursor dinámico

OPEN nombre_cursor FOR sentencia_select ;

Los parámetros se pueden pasar posicionalmente: el primer parámetro sustituye al primero que espera el cursor y así sucesivamente, o por asociación de nombres: DECLARE CURSOR departamentos (dept_pl NUMBER, loc_pl CHAR) IS SELECT dept_no, dname, loc FROM dept WHERE dept_no = dept_pl and loc = loc_pl ; BEGIN OPEN departamentos (10, 'MADRID') ; OPEN departamentos (v_dept, v_loc) ; OPEN departamentos (loc_pl => 'MADRID', dept_pl => 10) ; OPEN departamentos (loc_pl => v_loc, dept_pl => v_dept) ;



Leer el Cursor: FETCH. La sentencia FETCH, recupera la siguiente fila del cursor hasta detectar el final. Los datos recuperados deben almacenarse en variables o registros.  Sintaxis para recuperación en variables: FETCH nombre_cursor INTO var1 [,var2] ; Todas las variables que aparecen en la cláusula INTO deben haber sido definidas previamente en la zona de declaración de variables DECLARE. Será necesario tener tantas variables como columnas estemos recuperando en la SELECT asociada al cursor. La recuperación es posicional, es decir la primera columna seleccionada se almacenará en la primera variable, la segunda en la segunda, y así sucesivamente, por lo tanto, los tipos de las variables deberán ser compatibles con los valores de las columnas que van a almacenar.  Sintaxis para recuperación en registro: FETCH nombre_cursor INTO nombre_registro ; Es la sintaxis más usada debido a la facilidad para declarar el registro en la zona DECLARE con la opción de atributo %ROWTYPE/. En la zona BEGIN, y mientras el cursor esté abierto, las columnas del cursor pueden utilizarse haciendo referencia al nombre del registro, del que son variables miembro. En el supuesto del párrafo anterior, y caso de que la select asociada al cursor tenga columnas con expresiones distintas a nombres de columna (SUM(salar), por ejemplo), deberán usarse alias de columna, por cuyo nombre serán referenciadas.

Dep. Informática

10

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Ejemplo: DECLARE CURSOR curdep IS SELECT numde, nomde, presu FROM tdepto ; reg_dept curdep%ROWTYPE ; BEGIN FETCH curdep INTO reg_dept ; IF reg_dep.numde < 150 THEN ...



Cerrar el Cursor: CLOSE. La sentencia CLOSE cierra el cursor. Una vez cerrado no se puede volver a leer (FETCH), pero si se puede volver a abrir. Cualquier operación que se intente realizar con un cursor cerrado, provoca la excepción predefinida INVALID_CURSOR. Sintaxis : CLOSE

nombre_cursor ;

Ejemplo: CLOSE curdep; 1.5.1.3.- EXIT.EXIT nos permite salir de un bucle de forma radical o evaluando una condición. Su sintaxis es: EXIT [nombre_bucle] [WHEN condición] ; Si se omite el nombre del bucle sale del bucle actual. Si se especifica WHEN, solo sale si se cumple la condición. Si se omiten ambos, sale del bucle actual incondicionalmente. 1.5.1.4.- Control condicional. Permite ejecutar una o varias sentencias dependiendo de una condición. Su sintaxis es: IF [ELSIF [ELSE END IF ;

condición THEN sentencias ; condición THEN sentencias ;] sentencias ;]

La condición puede ser cualquier expresión PL/SQL de comparación. Al procesar la sentencia, se evalúan una a una todas las condiciones por orden empezando por la que le sigue a la palabra IF. Si alguna de las condiciones se cumple, se ejecutan las sentencias especificadas y se pasa a la siguiente instrucción tras END IF . Si no se cumple ninguna condición, pero existe un ELSE, se ejecutan las sentencias siguientes al ELSE, si no es así, no se ejecuta ninguna sentencia. Sólo una secuencia de sentencias se ejecuta al procesar una instrucción IF. Ejemplos:

IF tipo = 1 ELSIF tipo = 2 ELSE END IF ;

THEN sal := sal * 0.01 ; THEN sal := sal * 0.02 ; sal := sal * 0.03 ;

1.5.1.5.- Bucles. Existen 5 tipos de bucles:     

Bucles básicos Bucles condicionales (WHILE) Bucles numéricos (FOR) Bucles sobre cursores Bucles para una SELECT

Dep. Informática

11

I.E.S. TRASSIERRA - Córdoba



PL/SQL

Bucles Básicos. Son bucles infinitos que responden a la siguiente sintaxis: LOOP sentencias ; END LOOP ; Para romper el bucle (salir de él) deberemos utilizar EXIT, GOTO o RAISE. Ejemplo: LOOP FETCH cursor_cl INTO registro ; EXIT WHEN cursor_cl%NOTFOUND ; END LOOP ;



Bucles Condicionales (WHILE). La condición se evalúa antes de cada entrada al bucle. Si la condición se cumple se ejecutan las sentencias, y si no es así, se pasa el control a la sentencia siguiente al final del bucle. Su sintaxis es: WHILE condición LOOP sentencias; END LOOP; Ejemplo: WHILE



a>0 LOOP a := a - 1 ; END LOOP ;

Bucles Numéricos (FOR). Son bucles que se ejecutan una vez para cada elemento definido dentro del rango numérico. Su sintaxis es: FOR índice IN [REVERSE] exp_n1 .. exp_n2 LOOP Sentencias ; END LOOP ; índice

es la variable numérica de control del bucle, comienza con el valor de exp_n1 y se va incrementando de 1 en 1 hasta el valor de exp_n2.

REVERSE

realiza la cuenta al revés, desde exp_n2 hasta exp_n1.

exp_n1, exp_n2 pueden ser valores constantes o cualquier expresión numérica. Ejemplo: FOR x IN 1 .. 10 LOOP valor:= valor + x ; END LOOP ;



Bucles sobre Cursores. Son bucles que se ejecutan para cada fila del cursor. Cuando se inicia un bucle sobre un cursor automáticamente se realizan los siguientes pasos: - Se declara implícitamente el registro especificado como nombre_cursor%ROWTYPE. - Se abre el cursor. - Se realiza la lectura y se ejecutan las sentencias del bucle hasta que no hay mas filas. - Se cierra el cursor.

Dep. Informática

12

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Sintaxis: FOR nombre_registro IN nombre_cursor LOOP Sentencias ; END LOOP ; La variable de control del bucle se incrementa automáticamente y no es necesario inicializarla, pues lo está implícitamente como variable local de tipo integer. El ámbito del contador es el bucle y no puede accederse a su valor fuera de él. Dentro del bucle, el contador puede referenciarse como una constante pero no se le puede asignar un valor. También le podemos pasar parámetros al cursor, tal y como se indicó en el apartado de manejo de cursores. En este caso los parámetros se indican entre paréntesis tras el nombre del cursor: FOR nombre_registro IN nombre_cursor(lista_de_parametros) ....... Si se sale del bucle prematuramente (p.e. con EXIT), o se detecta un error (excepción), el cursor se cierra. Ejemplo: DECLARE CURSOR curdep IS SELECT numde, nomde FROM tdepto; reg_dept curdep%ROWTYPE ;

-- (*)

BEGIN FOR reg_dept IN curdep LOOP ......; END LOOP; (*) Esta sentencia sobra, pues es declarada implícitamente por este tipo de bucle. 

Bucles para Sentencias SELECT. Es el mismo concepto que el del cursor, excepto que en vez de declarar un cursor con su SELECT, se escribe directamente la sentencia SELECT y Oracle utiliza un cursor interno para hacer la declaración. Su sintaxis es: FOR nombre_registro IN sentencia_SELECT LOOP sentencias ; END LOOP ; Si se sale del bucle prematuramente, o se detecta un error, el cursor se cierra. Hay que resaltar que las columnas del cursor no pueden ser usadas con fuera de estos dos últimos bucles, ya que cierran automáticamente el cursor que tratan. Ejemplo: FOR registro IN SELECT numde FROM tdepto LOOP sentencias ; END LOOP ;

Dep. Informática

13

I.E.S. TRASSIERRA - Córdoba

PL/SQL

1.5.1.6.- GOTO. Esta sentencia transfiere el control a la sentencia o bloque PL/SQL siguiente a la etiqueta indicada. Su sintaxis es: GOTO etiqueta; La etiqueta se especifica entre los símbolos “”, por ejemplo: GOTO A .....

La sentencia GOTO puede ir a otra parte del bloque o aun sub-bloque, pero nunca podrá ir a la zona de excepciones. La sentencia siguiente a una etiqueta debe ser ejecutable. En caso de no existir, por ejemplo por ser el final del programa, se puede utilizar la sentencia NULL. 1.5.1.7.- NULL. Significa inacción. El único objeto que tiene es pasar el control a la siguiente sentencia. Su sintaxis es: NULL Ejemplo: IF tipo = 1 THEN vsal := vsal * 0.01 ELSE NULL; END IF;

1.5.2. Sentencias DML Oracle abre un cursor implícito (no definido por el usuario) por cada sentencia SQL que tenga que procesar. PL/SQL nos permite referimos al cursor implícito mas reciente como "SQL%". Estos cursores los maneja Oracle, por lo que no se pueden abrir, leer o cerrar, pero si podemos utilizar algunos atributos que nos dan información sobre la ejecución de la sentencia SQL (INSERT, UPDATE, DELETE, SELECT) para la que se haya abierto. Estos atributos son: %NOTFOUND

Devuelve TRUE si un INSERT, UPDATE o DELETE no ha procesado ninguna fila o si la SELECT no recupera nada. En este último caso se provoca la excepción predefinida NO_DATA_FOUND

%FOUND

Devuelve TRUE si un INSERT, UPDATE o DELETE ha procesado alguna fila o una SELECT recupera datos

%ROWCOUNT

Devuelve el número de filas procesadas por un INSERT, UPDATE o DELETE o las recuperadas por una SELECT .

1.5.2.1.- SELECT. La sentencia SELECT recupera valores de la Base de Datos que serán almacenados en variables PL/SQL. La sintaxis completa de la SELECT ya se estudió en la parte de SQL, la única variante en programación es la opción INTO que permite almacenar los valores recuperados en variables. Para poder recuperar valores en variables, la consulta sólo debe devolver una fila (no funciona con predicados de grupo). Su sintaxis es: SELECT FROM Dep. Informática

{* / col1 [,coI2] } INTO {reg / var1 [,var2] } tabla resto_de_la_select ;

14

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Las excepciones predefinidas más comunes son: A.- Si la consulta no devuelve ninguna fila: Se produce la excepción predefinida NO_DATA_FOUND El error Oracle (SQLCODE) ORA-01403 El mensaje de error (SQLERRM) "no data found" SQL%NOTFOUND es TRUE SQL%FOUND es FALSE SQL%ROWCOUNT es 0 B.- Si la consulta devuelve más de una fila: Se produce la excepción predefinida TOO_MANY _ROWS El error Oracle (SQLCODE) ORA-O1422 El mensaje de error (SQLEERM) 'single-row query returns more than one row" SQL%NOTFOUND es FALSE SQL%FOUND es TRUE SQL%ROWCOUNT es 1 (solo puede devolver 0 o 1 fila)

1.5.2.2.- INSERT. INSERT crea filas en la tabla o vista especificada. Su sintaxis es idéntica a la de SQL. Se puede realizar el INSERT con valores de las variables PL/SQL o con los datos de una SELECT. En este caso hay que tener en cuenta que ésta última no lleva cláusula INTO. En ambos casos el número de columnas a insertar deberá ser igual al número de valores y/o variables o a las columnas especificadas en la SELECT. En caso de omitir las columnas de la tabla en la que vamos a insertar, se deberán especificar valores para todas las columnas de la tabla y en el mismo orden en el que esta haya sido creada. Su sintaxis es la misma de SQL: INSERT INTO tabla [ (col1 [,col2] .....) ] { VALUES (exp1 [,exp2]..... ) / Sentencia_Select }; VALUES

permite insertar una tupla indicando expresiones constantes o variables.

Sentencia_Select

permite insertar varias tuplas a la vez.

Si no se inserta ninguna fila los atributos devuelven los siguientes valores: SQL%NOTFOUND es TRUE SQL%FOUND es FALSE SQL%ROWCOUNT es 0 Si se dan de alta una o mas filas los atributos devuelven los siguientes valores:

Dep. Informática

15

I.E.S. TRASSIERRA - Córdoba

PL/SQL

SQL%NOTFOUND es FALSE SQL%FOUND es TRUE SQL%ROWCOUNT es número de filas insertadas Ejemplo: DECLARE x number(2) := 80; y char(14) := 'INFORMATICA'; c char(13) := 'MADRID'; BEGIN INSERT INTO dept (loc, dep_no, dname) VALUES (c, x, y); x := 90; y := 'PERSONAL' c := 'MALAGA' INSERT INTO dept (loc, dep_no, dname) VALUES (c, x, y); INSERT INTO dept (loc, dep_no, dname) VALUES (92, 'ADMINISTRACION', 'SEVILLA') ; END;

1.5.2.3.- UPDATE La sentencia UPDA TE permite modificar los datos almacenados en las tablas o vistas. Sintaxis: UPDATE SET

tabla columna = { sentencia select / expresión_plsql / constante / variable_plsql } [ WHERE { condición / CURRENT OF nombre_cursor} ] ;

La cláusula WHERE CURRENT OF debe ser utilizada después de haber realizado la lectura del cursor que debe haber sido definido con la opción FOR UPDATE OF. Esta cláusula no es admitida en cursores cuya select utilice varias tablas (obtenidos con select con yunción). Si no se actualiza ninguna fila los atributos devuelven los siguientes valores: SQL%NOTFOUND es TRUE SQL %FOUND es FALSE SQL %ROWCOUNT es 0 Si se actualizan una o más filas los atributos devuelven los siguientes valores: SQL %NOTFOUND es FALSE SQL%FOUND es TRUE SQL%ROWCOUNT el número de filas actualizadas Ejemplo: UPDATE temple SET salar = salar * 0.1 WHERE numde = 110 ;

Dep. Informática

16

I.E.S. TRASSIERRA - Córdoba

PL/SQL

1.5.2.4.- DELETE. La sentencia DELETE permite borrar filas de la tabla o vista especificada. Sintaxis: DELETE

[ FROM] [ WHERE

tabla {condición / CURRENT OF nombre_cursor} ] ;

Al igual que con UPDATE, la cláusula WHERE CURRENT OF debe ser utilizada después de haber leído el cursor que debe haber sido definido con la opción FOR UPDATE OF. Si no se borra ninguna fila los atributos devuelven los siguientes valores: SQL%NOTFOUND es TRUE SQL%FOUND es FALSE SQL%ROWCOUNT es 0 Si se borran una o más filas los atributos devuelven los siguientes valores: SQL%NOTFOUND es FALSE SQL%FOUND es TRUE SQL%ROWCOUNT es número de filas borradas Ejemplo: DELETE WHERE

FROM temple numde = 110;

1.5.3. Sentencias transaccionales El bloque PL/SQL admite las sentencias transaccionales ya estudiadas en el bloque de SQL.

1.6.- ZONA DE EXCEPCIONES: EXCEPTION.La zona de excepciones es la última parte del bloque PL/SQL y en ella se realiza la gestión y el control de errores. Con las excepciones ser pueden manejar los errores cómodamente sin necesidad de mantener múltiples chequeos por cada sentencia escrita. También provee claridad en el código ya que permite mantener las rutinas correspondientes al tratamiento de los errores en forma separada de la lógica del negocio. Sintaxis:

EXCEPTION control_de_errores ;

Sólo se activa un error de todos los definidos en dicha zona. La zona EXCEPTION termina cuando se detecta la palabra reservada END (fin de la zona y fin del bloque PL/SQL). La ejecución de las sentencias asignadas a un error se produce:

Dep. Informática

17

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Automáticamente Oracle detecta un error, para el proceso y pasa el control a la zona EXCEPTION, buscando si existe tratamiento al error detectado. Manualmente

En el proceso llega un punto en el que nos interesa realizar lo mismo que si se hubiese detectado un error y ejecutamos la excepción.

La zona EXCEPTION es opcional. Si se omite y se detecta algún error, el bloque PL/SQL termina incorrectamente, terminando el proceso y devolviendo el control al punto de partida. Existen dos variables para poder recuperar los errores Oracle que se pueden producir: SQLCODE y SQLERRM. 1.6.1.- Control de errores. Cuando en el proceso se detecta un error, el proceso se para, pasando el control a la zona EXCEPTION. Sintaxis:

WHEN nombre_excepción THEN sentencias ;

nombre_excepción sentencias

es el nombre de la que se quiere controlar (predefinida o de usuario). es el conjunto de sentencias que se ejecutan si se produce el error.

1.6.2.- Excepciones predefinidas. En Oracle existen las siguientes excepciones predefinidas: Nombre de excepción CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX INVALID_CURSOR INVALID_NUMBER LOGIN_DENIED NO_DATA_FOUND NOT_LOGGED_ON PROGRAM_ERROR STORAGE_ERROR TIMEOUT_ON_RESOURCE TOO_MANY_ROWS VALUE_ERROR ZERO_DIVIDE OTHERS

Tipo de error indicado Cursor abierto previamente Valor duplicado en índice Cursor no válido Número no válido Denegada la conexión a Oracle La consulta no recupera ninguna fila No conectado a Oracle Problema interno Fuera de memoria o error de memoria Exceso de recursos consumidos La consulta devuelve más de una fila Valor incorrecto División por cero Cualquier otro error no especificado

SQLCODE -6511 -1 -1001 -1722 -1017 +100 -1012 -6501 -6500 -51 -1422 -6502 -1476

1.6.3.- Excepciones definidas por el usuario. Existen dos tipos de excepciones a definir por los usuarios y son: 

Basadas en errores ORACLE (EXCEPTION INIT).Asigna un nombre a un error ORACLE existente. Sintaxis: PRAGMA EXCEPTION_INIT (nombre_excepción, -número_error)

Dep. Informática

18

I.E.S. TRASSIERRA - Córdoba

PL/SQL

nombre_excepción Deberá estar definida como excepción de usuario. número_error Es el numero del error Oracle que se desea controlar (el que nos devuelve el SQLCODE). La definición se realiza en la zona del DECLARE y el control de la excepción en la zona EXCEPTION. Solo debe haber una excepción por cada error Oracle. 

Otras excepciones. Se trata de un control de errores para el usuario. Este tipo de excepciones se deben declarar según vimos en el apartado de DECLARE. Una vez declarada la excepción, la podemos provocar cuando sea necesario utilizando la función RAISE que se detalla a continuación. El tratamiento dentro de la zona de excepciones es exactamente igual que el de una excepción predefinida. Sintaxis: DECLARE A EXCEPTION; BEGIN RAISE A; EXCEPTION WHEN A THEN .... ; END;

1.6.4. - Ejecución de excepciones: RAISE Para la ejecución del bloque PL/SQL y pasa el control a la zona de excepciones. Sintaxis:

RAISE

nombre_excepción

El nombre_excepción es el nombre de la excepción que se desea ejecutar, pueden ser excepciones predefinidas o excepciones de usuario. Ejemplo:

DECLARE error EXCEPTION; BEGIN RAISE error; RAISE TOO_MANY _ROWS; EXCEPTION WHEN error THEN ...; WHEN TOO_MANY _ROWS THEN ... ; END;

1.6.5.- SQLCODE.La función SQLCODE nos devuelve el número del error que se ha producido. Sólo tiene valor cuando ocurre un error Oracle. Esta función solo se habilita en la zona de Excepciones, ya que es el único sitio donde se pueden controlan los errores. No se puede utilizar directamente, pero si se puede guardar su valor en una variable.

Dep. Informática

19

I.E.S. TRASSIERRA - Córdoba

PL/SQL

1.6.6.- SQLERRM.La función SQLERRM devuelve el mensaje del error del valor actual del SQLCODE. Al igual que SQLCODE, no se puede utilizar directamente, sino que debemos declarar una variable alfanumérica lo suficientemente grande para contener el mensaje de error.

1.6.7.- Algunos ejemplos de tratamiento de excepciones.1.- Excepciones predefinidas. EXCEPTION WHEN zero_divide THEN Rollback; WHEN value_error THEN INSERT INTO errores VALUES… Commit; WHEN others THEN NULL; END;

2.- Excepción de usuario. DECLARE emp_sal temple.salar%TYPE; emp_no temple.numem%TYPE; salario_muy_alto EXCEPTION; BEGIN SELECT numem, salar INTO emp_no, emp_sal FROM temple WHERE nomem = 'DURAN, LIVIA'; IF emp_sal * 1.05 > 4000 THEN RAISE salario_muy_alto ELSE UPDATE temple SET..... END IF; EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; WHEN salario_muy_alto THEN INSERT INTO emp_alto_sal VALUES(emp_no); COMMIT; END;

3.- Cualquier error se graba en el fichero errores. DECLARE err_num NUMBER; err_msg VARCHAR2(100); BEGIN … EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; err_msg := SUBSTR(SQLERRM, 1, 100); INSERT INTO errores VALUES(err_num, err_msg); END;

Dep. Informática

20

I.E.S. TRASSIERRA - Córdoba

PL/SQL

4.- Ante la inexistencia de cualquier error se manda un mensaje al buffer de salida que será visualizado al final del programa: SET SERVEROUTPUT ON SIZE 5000

-- Parámetro de entorno que activa el buffer de salida y le -- asigna un tamaño de 5000 bytes.

DECLARE cur1 IS .......... no_existe EXCEPTION ; BEGIN OPEN cur1; FETCH cur1 INTO registro; IF cur1%NOTFOUND THEN RAISE no_existe; END IF; ........ EXCEPTION WHEN no_existe THEN DBMS_OUTPUT.PUT_LINE('Empleado inexistente'); END;

-- Envio al buffer del literal

1.7.- EJERCICIOS.1.7.1.- EJERCICIOS RESUELTOS.1.- Programa que haciendo uso de un bucle simple, inserte sucesivamente tuplas con el valor de un contador entre 1 y 14 en la tabla TEMPORAL, previamente creada y con una sola columna: NUMERO number(2). DROP TABLE temporal ; CREATE TABLE temporal (numero number(2)); DECLARE i number(2):=1; BEGIN LOOP INSERT INTO temporal VALUES (i); i:=i+1; EXIT WHEN i>14; END LOOP; END; / SELECT * FROM temporal ;

2.- Idem al anterior, pero usando un bucle WHILE. DROP TABLE temporal ; CREATE TABLE temporal (numero number(2)); DECLARE i number(2):=1; BEGIN WHILE i < 15 LOOP INSERT INTO temporal VALUES (i); i:=i+1; END LOOP; END; / SELECT * FROM temporal ;

Dep. Informática

21

I.E.S. TRASSIERRA - Córdoba

PL/SQL

3.- Idem al anterior, pero usando un bucle FOR. DROP TABLE temporal ; CREATE TABLE temporal (numero number(2)); DECLARE i number(2):=1; BEGIN FOR I IN 1 .. 14 LOOP INSERT INTO temporal VALUES (i); END LOOP; END; / SELECT * FROM temporal ;

4.- Programa que crea la tabla TEST, con dos únicas columnas numéricas de longitud 3: NR1 y NR2. Y posteriormente asigna a NR1 un valor decreciente del 19 hasta el cero, y a NR2 el valor de un contador creciente de 1 a 20. Y ello con el uso de un bucle de cursor. DROP TABLE test; CREATE TABLE test (nr1 NUMBER(3), nr2 NUMBER(3)) ; BEGIN -- inserta valores en la tabla TEST FOR i IN 1..20 LOOP INSERT INTO test values( NULL, i) ; END LOOP; COMMIT; END; / DECLARE x NUMBER(4) := 0; CURSOR t_cur IS SELECT * FROM test ORDER BY nr2 desc FOR UPDATE OF nr1; BEGIN FOR t_rec IN t_cur LOOP UPDATE test SET nr1 = x WHERE CURRENT OF t_cur; x := x + 1; END LOOP; COMMIT; END; / SELECT * FROM test ; SET ECHO OFF;

1.7.2.- EJERCICIOS PROPUESTOS.1.- Codificar el programa PL/SQL que permita aumentar en un 10% el salario de un empleado cuyo número se introduce por teclado. 2.- Modificar el anterior programa para controlar la inexistencia del empleado tecleado, no haciendo nada, solo evitando que el programa finalice con error. 3.- Modificar el anterior programa de forma que si el empleado no existe se visualice un mensaje de error.

Dep. Informática

22

I.E.S. TRASSIERRA - Córdoba

PL/SQL

4.- Codificar el programa PL/SQL que solicite por pantalla un número de departamento y calcule la suma total de los salarios y comisiones de ese departamento. Después inserte la tupla correspondiente en la tabla TOTALES, previamente creada con la siguiente estructura: deptno

number(3)

total

number(10,2)

Realizar el ejercicio utilizando un bucle simple y tratando el cursor. 5.- Modificar el programa anterior para que en la tabla TOTALES no se inserten los departamentos inexistentes. 6.- Realizar el mismo ejercicio con un bucle sobre el cursor, controlando que no se inserten en TOTALES los departamentos inexistentes. 7.- Mejorar el programa anterior para que, tanto en caso de inexistencia del departamento como en caso de que ya haya sido introducido (que exista ya en totales), muestre sendos mensajes de error.

Dep. Informática

23

I.E.S. TRASSIERRA - Córdoba

PL/SQL

TEMA 2.- PROCEDIMIENTOS, FUNCIONES Y PAQUETES.2.1.- Introducción. 2.2.- Procedimientos. 2.2.1.- Creación y borrado. 2.2.2.- Tipos de argumentos: in, out, in out. 2.2.3.- Polimorfismo. 2.3.- Funciones. 2.3.1.- Creación y borrado. 2.3.2.- Recursividad. 2.3.3.- Funciones PL/SQL en sentencias SQL. 2.4.- Paquetes. 2.4.1.- Creación y borrado. 2.4.2.- La depuración 2.5.- Ventajas de los procedimientos, funciones y paquetes. 2.6.- Manejo de excepciones. 2.7.- Ejercicios. 2.7.1.- Ejercicios resueltos. 2.7.2.- Ejercicios propuestos.

2.1.- INTRODUCCIÓN.Además de los bloques de código ya vistos, Oracle permite desarrollar subprogramas, es decir, agrupar una serie de definiciones y sentencias para ejecutarlas bajo un único nombre, estos subprogramas son: procedimientos, funciones y paquetes. Los procedimientos son trozos de código que realizan un trabajo sobre unos argumentos de entrada que son opcionales y depositan unos valores sobre unos argumentos de salida, también opcionales. Las funciones son similares a los procedimientos con la diferencia de que devuelven siempre un valor. Los paquetes son agrupaciones de procedimientos, funciones y definiciones de datos; es lo que en otros entornos de programación se conoce como librería. Tanto los procedimientos como las funciones disponen de la siguiente estructura: cabecera del procedimiento o función declaración de variables, cursores, sobprocedimientos, etc. BEGIN zona ejecutable EXCEPTION manipuladores de excepciones END [nombre_procedimiento] ; Dep. Informática

24

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Desarrollar un subprograma para almacenar un bloque PL/SQL en la base de datos es muy útil cuando es necesario ejecutarlo repetidamente. Un subprograma puede invocarse desde multitud de entornos: SQL *Plus, Oracle Forms, otro subprograma, y desde cualquier otra herramienta o aplicación Oracle. Para crear un procedimiento o función hay que seguir los pasos siguientes: 1.- Escribir el texto de la sentencia CREATE PROCEDURE / FUNCTION y guardarlo en un fichero de texto con la extensión .SQL. Mientras se compone esta sentencia se debe ir pensando en el manejo de errores de ejecución. Es importante que los ficheros de texto acaben con una línea que contenga el carácter '/' como único carácter de la columna uno. Por ejemplo (la tabla alumnos debe estar creada): CREATE PROCEDURE altalum (v_num number, v_nom varchar2) IS BEGIN INSERT INTO alumnos VALUES (v_num, v_nom); END ; /

2.- Compilar el fichero de texto ejecutándolo con el comando START . La ejecución convierte el código fuente en código compilado y lo almacena en la base de datos. La ejecución se realiza desde SQL*Plus u otro interfaz que permita sentencias LDD. El código fuente se almacena siempre en la B.D. El código compilado solo se almacena si no se han producido errores. En este caso pueden verse los errores de compilación con el comando SHOW ERRORS. 3.- Invocar el procedimiento o función desde un entorno Oracle con el comando EXECUTE. Por ejemplo: EXECUTE altalum (1, 'JOSÉ ANGEL FERNÁNDEZ') ;

La mayoría de las aplicaciones Oracle, entre ellas SQLPlús, permiten el paso de parámetros posicionalmente o por asociación de nombres (de forma similar a la apertura de cursores parametrizados), incluso de forma mixta. Supongamos que tenemos las variables v_a de tipo number y v_b de tipo char: SQL>variable v_a number; SQL>variable v_b varchar2(50); SQL>execute :v_a:=2; SQL>execute :v_b:= 'ANA PALACIOS';

Nota.- El comando variable permite definir variables desde el prompt de SQL. Al referenciar variables desde el promt de SQL es necesario precederlas de dos puntos (:). Desde un programa o subprograma las variables se referencian directamente.

Las siguientes son formas válidas de invocación: Con paso de parámetros posicional: EXECUTE altalum(:v_a, :v_b); EXECUTE altalum(:v_a+1 , 'MARIA VALVERDE'); EXECUTE altalum(3, 'JOSE SANTAELLA');

Con paso de parámetros por asociación: EXECUTE altalum(:v_b=>’FRANCISCO RUIZ’, :v_a=>4);

Dep. Informática

25

I.E.S. TRASSIERRA - Córdoba

PL/SQL

2.2.- PROCEDIMIENTOS.Un procedimiento es un subprograma que realiza una acción específica. Pueden recibir parámetros pero que no devuelven ningún valor.

2.2.1.- Creación y borrado.Se crea un nuevo procedimiento con la sentencia CREATE PROCEDURE, que declara una lista de argumentos y define la acción a ejecutar por el bloque PL/SQL estándar. Sintaxis: CREATE [OR REPLACE] PROCEDURE nombre_proc ( argumento [tipo_arg] tipo_dato [,argumento [tipo] tipo_dato ] .... ) AS [IS] bloque PL/SQL dónde: nombre_proc

Es el nombre del procedimiento.

argumento

Es el nombre del argumento.

tipo_arg

Indica el tipo de argumento: IN (por defecto), OUT o IN OUT.

tipo_dato

El tipo de dato del argumento sin precisión.

bloque PL/SQL El cuerpo procedural que define la acción a realizar. La opción REPLACE se especifica cuando se desea reemplazar un procedimiento del mismo nombre sin necesidad de borrarlo previamente. AS e IS son equivalentes. El bloque PL/SQL debe comenzar con la palabra BEGIN o con una declaración de variable local. Nunca debe comenzar con DECLARE. Esta diferencia con los bloques PL/SQL anónimos ocurre porque AS (o IS) llevan implícito el DECLARE. El procedimiento debe finalizar con la palabra END seguida opcionalmente por el nombre del procedimiento y un punto y coma (;). Para borrar un procedimiento usaremos la sentencia: DROP PROCEDURE nombre_proc ;

2.2.2. Tipos de Argumentos.El número de argumentos y sus tipos de datos deben corresponderse con los de los parámetros que se le pasan al procedimiento, pero los nombres puede ser diferentes. Esto permite que el mismo procedimiento pueda invocarse desde diferentes lugares. Para transferir valores desde y hacia el entorno de llamada a través de argumentos debemos escoger entre uno de los tres tipos de argumento: In, Out e In Out.

Dep. Informática

26

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Tipo IN

Descripción Se usa para pasar parámetros por valor. El parámetro actúa como constante por lo que no se le puede asignar ni cambiar su valor dentro del procedimiento. Este parámetro puede referenciarse en la llamada mediante una constante, variable inicializada o expresión.

OUT

Permite devolver valores desde el procedimiento al entorno de llamada. El argumento correspondiente actúa como una variable no inicializada, por lo que dentro del subprograma solo puede asignársele valores, no pudiendo aparecer en la parte derecha de una expresión. En la invocación del subprograma, este parámetro solo puede sustituirse por una variable.

IN OUT

Se usa para pasar parámetros por referencia y recogerlos del procedimiento o función. El argumento correspondiente actúa como una variable inicializada, por lo que se le puede cambiar su valor y usarse en la parte derecha de las expresiones. En la llamada, este parámetro solo puede sustituirse por una variable.

Los parámetros pueden declararse para que tomen valores por defecto, veamos como: CREATE PROCEDURE prueba(v1 varchar2 default 'Sin Nombre', v2 number default 5) is ..

Las llamadas válidas que podemos hacer a este procedimiento son las siguientes: EXECUTE prueba; EXECUTE prueba ('Paco'); EXECUTE prueba ('Marta', 9);

/* equivalente a execute prueba('Sin Nombre', 5);*/ /* equivalente a execute prueba('Paco', 5); */

Si quisiéramos darle un valor a v2 y tomar el de v1 por defecto, deberíamos usar la notación nominal: EXECUTE prueba(v2=>8);

Es útil y aconsejable declarar argumentos y variables utilizando los atributos %TYPE o %ROWTYPE. Veamos algunos ejemplos del uso de los tipos de argumentos:  Argumentos IN Almacenar información acerca de un nuevo empleado: CREATE OR REPLACE PROCEDURE alta_emp (v_emp_numero IN temple.numem%TYPE , v_emp_departa IN temple.numde%TYPE , v_emp_telefono IN temple.extel%TYPE , v_emp_fechanac IN temple.fecna%TYPE , v_emp_fechaing IN temple.fecin%TYPE , v_emp_salario IN temple.salar%TYPE , v_emp_comision IN temple.comis%TYPE , v_emp_hijos IN temple.numhi%TYPE , v_emp_nombre IN temple.nomem%TYPE ) IS

Dep. Informática

27

I.E.S. TRASSIERRA - Córdoba

PL/SQL

BEGIN INSERT INTO temple VALUES (v_emp_numero, v_emp_departa, v_emp_telefono, v_emp_fechanac, v_emp_fechaing, v_emp_salario, v_emp_comision, v_emp_hijos, v_emp_nombre) ; COMMIT WORK ; END alta_emp ; /

Recordemos que la orden show errors es muy útil para depurar el código. Si todo va bien nos aparecerá el mensaje de Procedimiento creado. Ahora para ejecutarlo debemos escribir la sentencia execute, por ejemplo: EXECUTE alta_emp(555, 111, 780, '24/06/1979', sysdate, 777, null, 3, 'ARGUDO, MANUEL')

lo que provocará la ejecución del procedimiento y por ende el alta del empleado tecleado. Se pueden eliminar valores innecesarios de entrada a los procedimientos derivando dichos valores internamente dentro del procedimiento o relacionándolos con un valor por defecto de columna al definir la tabla. Por ejemplo para: -

Generar la clave primaria de una tabla utilizando un generador de se secuencia. Registrar el usuario partiendo de la variable USER. Registrar la fecha actual basándose en la variable SYSDATE. Almacenar valores por defecto, cuando sea apropiado. Utilizar reglas de negocio para calcular valores de entrada de forma automática utilizando una fórmula.

El siguiente ejemplo elimina los valores de entrada innecesarios en una rutina de altas de empleados, dejando solo como variables de entrada la información esencial: CREATE OR REPLACE PROCEDURE alta_emp (v_emp_numero v_emp_departa v_emp_telefono v_emp_fechanac v_emp_hijos v_emp_nombre v_emp_fechaing temple.fecin%TYPE ; v_emp_salario temple.salar%TYPE ; v_emp_comision temple.comis%TYPE ; BEGIN v_emp_fechaing := SYSDATE ; IF (v_emp_departa IN (110, 111, 112)) THEN v_emp_comision := 0 ; ELSE v_emp_comision := NULL; END IF;

IN temple.numem%TYPE , IN temple.numde%TYPE , IN temple.extel%TYPE , IN temple.fecna%TYPE , temple.numhi%TYPE , temple.nomem%TYPE ) IS

SELECT min(salar) INTO v_emp_salario FROM temple WHERE numde = v_emp_departa ; INSERT INTO temple VALUES (v_emp_numero, v_emp_departa, v_emp_telefono, v_emp_fechanac, v_emp_fechaing, v_emp_salario, v_emp_comision, v_emp_hijos, v_emp_nombre) ; COMMIT WORK ; END alta_emp ; /

Dep. Informática

28

I.E.S. TRASSIERRA - Córdoba

PL/SQL

 Argumentos OUT Recuperar valores desde un procedimiento a el entorno de llamada a través de argumentos OUT. En el siguiente ejemplo, recuperar información sobre un empleado. CREATE OR REPLACE PROCEDURE consulta_emp (v_emp_numero IN temple.numem%TYPE , v_emp_nombre OUT temple.nomem%TYPE , v_emp_salario OUT temple.salar%TYPE , v_emp_comision OUT temple.comis%TYPE) IS BEGIN SELECT nomem, salar, comis INTO v_emp_nombre, v_emp_salario, v_emp_comision FROM temple WHERE numem = v_emp_numero; END consulta_emp; /

Para invocar a este procedimiento hay que pasarle todos los parámetros, ya sean IN, OUT o IN OUT. Aunque lo habitual es invocar a un procedimiento desde un bloque PL, el siguiente ejemplo muestra la forma de invocarlo con argumentos IN y OUT desde el prompt de SQL: SQL> var x varchar2(50); SQL> var y number; SQL> var z number; SQL> execute consulta_emp(390, :x, :y, :z);

Incluso podríamos ver el contenido de las variables OUT modificadas por el procedimiento. SQL> PRINT :x; .......

 Argumentos IN OUT El siguiente ejemplo transforma una secuencia de nueve dígitos en un numero de teléfono. Recupera valores desde el entorno de trabajo hacia el procedimiento, y devuelve los diferentes valores posibles desde el procedimiento al entorno de llamada utilizando argumentos IN OUT. CREATE OR REPLACE PROCEDURE con_guion (v_telf_no IN OUT VARCHAR2 ) IS BEGIN v_telf_no := SUBSTR (v_telf_no, 1, 3) || '-' || SUBSTR(v_telf_no, 4); END con_guion; /

El valor de un argumento tipo IN OUT debe darlo el procedimiento, bien mediante una sentencia de asignación, o bien por mediante una sentencia SELECT .. INTO.

2.2.3.- Polimorfismo.El polimorfismo es una característica que permite definir más de un objeto con el mismo nombre. En PL/SQL podemos definir distintas funciones y procedimientos que compartan un nombre común, pero han de diferenciarse en la cantidad o tipo de los parámetros que reciben o devuelven.

Dep. Informática

29

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Ejemplo: PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS BEGIN FOR i IN 1..n LOOP tab(i) := SYSDATE; END LOOP; END initialize; PROCEDURE initialize (tab OUT RealTabTyp, n INTEGER) IS BEGIN FOR i IN 1..n LOOP tab(i) := 0.0; END LOOP; END initialize;

Estos procedimientos sólo difieren en el tipo de dato del primer parámetro. Para efectuar una llamada a cualquiera de ellos, se puede implementar lo siguiente: DECLARE TYPE DateTabTyp IS TABLE OF DATE INDEX BY BINARY_INTEGER; TYPE RealTabTyp IS TABLE OF REAL INDEX BY BINARY_INTEGER; hiredate_tab DateTabTyp; comm_tab RealTabTyp; indx BINARY_INTEGER; … BEGIN indx := 50; initialize(hiredate_tab, indx); -- llama a la primera versión initialize(comm_tab, indx); -- llama a la segunda versión ... END;

2.3.- FUNCIONES.Las funciones son subprogramas que reciben parámetros y devuelven un único valor. Desde el punto de vista estrictamente sintáctico, un procedimiento conteniendo un argumento OUT puede ser reescrito como una función, y uno que contenga argumentos OUT múltiples puede reescribirse como una función que devuelva un argumento tipo registro. El como de largo será la invocación de la rutina, es lo que determina que su implementación se realice mediante un procedimiento o una función. Lo normal será que sus argumentos sean de tipo IN. Los de tipo OUT o IN OUT, aunque son válidos dentro de las funciones, se utilizan raras veces.

2.3.1.- Creación y borrado.La sentencia CREATE FUNCTION es idéntica a CREATE PROCEDURE, excepto por la cláusula extra RETURN. Con ella se genera una nueva función que declara una lista de argumentos, declara el argumento RETURN y define la acción a realizar por el bloque PL/SQL estándar. Su sintaxis es: CREATE [OR REPLACE] FUNCTION nombre_func (argumento [tipo] tipo_dato [,argumento [tipo] tipo_dato ] ..) RETURN tipo_dato AS [IS] bloque PL/SQL Dep. Informática

30

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Dónde los elementos significan lo mismo que en la sentencia CREATE PROCEDURE, salvo: RETURN tipo_dato que indica el tipo de dato devuelto por la función. Este tipo de dato no puede incluir escala o precisión. En una función puede haber varias sentencias RETURN, con la única condición de que al menos haya una. Ejemplo: recuperar el salario de un empleado. CREATE OR REPLACE FUNCTION tomar_salario (v_emp_numero IN temple.numem%TYPE) RETURN number IS v_emp_salario temple.salar%TYPE := 0; BEGIN SELECT FROM WHERE RETURN END tomar_salario; /

salar INTO v_emp_salario temple numem = v_emp_numero; (v_emp_salario);

Para usar la función podremos hacer: SELECT nomem, tomar_salario(numem) FROM temple;

Al igual que los procedimientos, las funciones pueden invocarse pasándoles los parámetros con notación posicional, nominal o mixta, y se les pueden asignar valores por defecto. Para borrar una función usaremos la sentencia: DROP FUNCTION nombre_func ;

2.3.2.- Recursividad.Las funciones pueden invocarse recursivamente, tanto en autollamada como en interllamada. Veamos la típica función de factorial (autoinvocación): CREATE FUNCTION factorial (n number) RETURN number IS BEGIN IF n = 0 THEN RETURN 1; ELSE RETURN n* factorial (n-1); END IF; END ; /

Como las funciones devuelven un valor, si queremos llamar a una función desde el prompt de SQL debemos declarar una variable para almacenar dicho valor y posteriormente visualizarlo: SQL> variable v number; SQL> EXECUTE :v:=factorial(4); SQL> print v; Dep. Informática

31

I.E.S. TRASSIERRA - Córdoba

PL/SQL

La recursividad no es una herramienta considerada fundamental en la programación PL/SQL. Cualquier problema que requiera su utilización también puede ser resuelto utilizando iteración. Una versión iterativa de un programa es usualmente más fácil de diseñar y de entender. Sin embargo, la versión recursiva es más simple, pequeña y más fácil de depurar. A modo de ejemplo, observe las siguientes dos versiones de cómo calcular el número n-ésimo de la serie de Fibonacci: -- Versión recursiva FUNCTION fib (n POSITIVE) RETURN INTEGER IS BEGIN IF (n = 1) OR (n = 2) THEN RETURN 1; ELSE RETURN fib(n – 1) + fib(n – 2); END IF; END fib; -- Versión iterativa FUNCTION fib (n POSITIVE) RETURN INTEGER IS pos1 INTEGER := 1; pos2 INTEGER := 0; cum INTEGER; BEGIN IF (n = 1) OR (n = 2) THEN RETURN 1; ELSE cum := pos1 + pos2; FOR i IN 3..n LOOP pos2 := pos1; pos1 := cum; cum := pos1 + pos2; END LOOP; RETURN cum; END IF; END fib;

La versión recursiva es más elegante que la iterativa, sin embargo esta última es más eficiente, corre más rápido y utiliza menos memoria.

2.3.3.- Funciones PL/SQL en sentencias SQL.Dentro de una expresión SQL se pueden referenciar funciones definidas por el usuario. Donde pueda haber una función SQL, puede situarse también una función PL/SQL. Para invocar una función desde una sentencia SQL se debe ser el propietario de la función o tener el privilegio EXECUTE. Las situaciones en que puede invocarse una función de usuario son las siguientes:  La lista de expresiones del comando SELECT.  Condiciones de las cláusulas WHERE y HAVING.  La cláusula VALUES de la sentencia INSERT.  La cláusula SET de la sentencia UPDATE. NO se puede invocar una función PL/SQL desde la cláusula CHECK de una vista.

Dep. Informática

32

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Para poder invocar desde una expresión SQL, una función PL/SQL definida por el usuario debe cumplir ciertos requerimientos básicos. 

Debe ser una función almacenada, no una función dentro de un Bloque PL/SQL anónimo o un subprograma.



Debe ser una función de fila única y NO una función de columna (grupo). Esto ocurre porque no se puede tomar el valor de todos los datos de una columna como argumento.



Todos los argumentos deben ser del tipo IN. No se admiten los tipos OUT o IN OUT.



Los tipos de datos de los argumentos deben ser tipos de datos internos del Servidor Oracle, tales como CHAR, DATE o NUMBER, y no tipo PL/SQL como BOOLEAN, RECORD o TABLE.



El valor resultante de la función también debe ser un tipo de dato Oracle.

Para ejecutar una sentencia SQL que llama a una función almacenada, el servidor Oracle debe conocer si la función provoca efectos colaterales. Éstos pueden ser cambios en tablas de la base de datos o en variables públicas de paquetes (que se declaran en la zona de especificación del paquete). Los efectos colaterales pueden aplazar la ejecución de una consulta, producir resultados dependientes del orden (por lo tanto indeterminados), o requerir que el estado del paquete se deba mantener a través de las sesiones de usuario (lo cual no está permitido). Por ello, las siguientes restricciones se aplican a funciones almacenadas invocadas desde expresiones SQL: 

Las funciones no pueden modificar datos en las tablas. No pueden ejecutar INSERT, UPDATE o DELETE.



Sólo las funciones locales que se referencian en la lista de la SELECT, en la cláusula , VALUES de un INSERT o en la cláusula SET de un UPDATE pueden actualizar una variable definida en un paquete.



No pueden actualizar variables dentro de un paquete las funciones referenciadas en las cláusulas CONNECT BY, START WITH, ORDER BY o GROUP BY.



Las funciones remotas no pueden leer, ni escribir el estado de un paquete. Cada vez que se accede aun enlace de base de datos, se establece una nueva sesión en la base de datos remota.



Las funciones que leen o escriben en el estado de un paquete no pueden utilizar paralelismo.

2.4.- PAQUETES.Los paquetes son un conjunto de procedimientos, funciones y datos. Un paquete es muy similar a una librería con la ventaja de que podemos ocultar información, ya que se compone de una parte de declaraciones visibles desde el exterior y una serie de implementaciones a las que no puede accederse desde fuera sino es a través del interfaz declarado en la parte pública. Las ventajas que ofrecen los paquetes son: modularidad, facilidad en el diseño de aplicaciones, ocultamiento de la información, añaden funcionalidad al sistema, y mejoran el rendimiento ya que la primera llamada a la librería carga ésta en memoria, de forma que las sucesivas invocaciones se evitan acceder a disco.

Dep. Informática

33

I.E.S. TRASSIERRA - Córdoba

PL/SQL

2.4.1.- Creación y borrado.Los paquetes se crean con el comando CREATE PACKAGE, cuya sintaxis es: - Parte de declaraciones: CREATE [OR REPLACE] PACKAGE nombre_paq AS [IS] bloque_pl_sql_paq_decl - Cuerpo del paquete (implementación): CREATE [OR REPLACE] PACKAGE body nombre_paq AS [IS] bloque_pl_sql_paq_body Un paquete puede contener procedimientos, funciones, variables, constantes, cursores y excepciones. Además, siempre que no se cambie la parte de declaraciones, se puede cambiar la implementación de una rutina o el cuerpo de un cursor sin necesidad de volver a compilar las aplicaciones que los usaban, ya que el interfaz no se ha cambiado. Un paquete presenta normalmente el siguiente formato: create package nombre is /* especificación (parte visible) */ /* declaraciones de tipos y objetos públicos */ /* especificación de procedimientos y funciones */ end; / create package body nombre is /* cuerpo (parte oculta)*/ /*declaraciones de tipos y objetos privados */ /* implementación de procedimientos y funciones */ [ begin /* sentencias de inicialización */ ] end; / En la cabecera del paquete se especifican todos aquellos objetos a los que se puede acceder, y en el cuerpo del paquete se implementan dichos objetos. En el cuerpo podemos declarar e implementar cuantos objetos estimemos oportunos teniendo en cuenta que a ellos solo se va a poder acceder desde el cuerpo del paquete. El cuerpo del paquete puede incluir opcionalmente una zona de inicialización (BEGIN...) que Solo se ejecuta la primera vez que se hace referencia al paquete dentro de una misma sesión. Ya sabemos que los procedimientos y las funciones se declaran con la palabra reservada CREATE y se invocan con EXECUTE. Esto es así cuando los creamos desde el prompt de SQL, pero dentro de un paquete no hay que poner ni CREATE OR REPLACE, ni EXECUTE. Una vez que tenemos un paquete creado y compilado podemos acceder a los objetos declarados en la parte visible desde el prompt de SQL o desde otros procedimientos y funciones; para ello tan solo hay que anteponerle al nombre del procedimiento o función el nombre del paquete que lo contiene separado por un punto. /* para el caso de que la llamada se haga desde el prompt de SQL*/ SQL > execute nombre_paquete.nombre_procedimiento(argumentos);

Dep. Informática

34

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Cuando queramos borrar un paquete lo haremos con la sentencia DROP PACKAGE nombre_paquete ; A continuación se expone un ejemplo de un paquete completo: create or replace package acciones_empleados is type registro_empleado is record (empleado number, salario number); cursor salario_descendente (empleado number) return registro_empleado; procedure alta_empleado (nombre varchar2 , trabajo varchar2 , salario number, dpto number) ; procedure baja_empleado (id_empleado number); end; / create or replace package body acciones_empleados is cursor salario_descendente (empleado number) return registro_empleado is select no_empleado, salario from empleados order by salario desc; procedure alta_empleado ( nombre varchar2 , trabajo varchar2, salario number, dpto number) is begin insert into empleados values (sec_empleado.nextval, nombre, trabajo, salario, dpto); end; procedure baja_empleado (id_empleado number) is begin delete from empleados where empleado = id_empleado ; end; end; /

2.4.2.- La depuración en los paquetes.Al objeto de identificar los errores en tiempo de compilación, en vez de esperar a la ejecución, puede especificarse el nivel de depuración de una función empaquetada cuando se crea el paquete. El nivel de depuración determina que operaciones puede realizar la función en la base de datos. Para especificar en nivel de depuración se utiliza el paquete predefinido RESTRICT_REFERENCES. Sintaxis: PRAGMA RESTRICT_REFERENCES ( nombre_func, WNDS [,WNPS] [RNDS] [,RNPS] ) ; Donde: WNDS

Indica que la función no puede modificar tablas de la base de datos. (Writes No Database State). En este paquete es obligatoria.

WNPS

Hace que la función no pueda cambiar los valores de variables de un paquete público. (Writes No Package Status).

RNDS

No permite que la función pueda consultar ninguna tabla de la base de datos. (Reads No Database State).

RNPS

Indica que la función no puede referenciar los valores de las variables de un paquete público. (Reads No Database Status).

Dep. Informática

35

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Este paquete sirve para alertar a los desarrolladores de aplicaciones con código procedural que incumplen las restricciones de las funciones PL/SQL dentro de sentencias SQL. Indica al compilador PL/SQL qué tipo de acciones pueden realizar las funciones. Las sentencias SQL que incumplan estas reglas producen un error de compilación.

Ejemplo: Crear una función llamada COMP, dentro del paquete FINANZAS. La función será invocada desde sentencias SOL en bases de datos remotas. Por lo tanto será necesario indicar los niveles de depuración RNPS y WNPS porque las funciones remotas no pueden nunca escribir o leer el estado del paquete cuando se le invoca desde una sentencia SQL. CREATE PACKAGE finanzas AS interes REAL; FUNCTION comp (anos IN NUMBER cant IN NUMBER porc IN NUMBER) RETURN NUMBER; PRAGMA RESTRICT _REFERENCES(comp, WNDS, RNPS, WNPS); END finanzas; / CREATE PACKAGE BODY finanzas AS FUNCTION comp (anos IN NUMBER cant IN NUMBER porc IN NUMBER) RETURN NUMBER IS BEGIN comp RETURN cant * POWER((porc/100) +1, anos) END comp; END finanzas; /

El nivel de depuración de una función se debe especificar en la zona de especificación del paquete. Si el paquete contiene varias funciones con el mismo nombre, el nivel de depuración se aplica a la última. La invocación a una función empaquetada se hace de la misma forma que si fuera una función SQL. Por ejemplo, para llamar a la función COMP (que se encuentra en el paquete FINANZAS residente enm la BD de Barcelona) desde una sentencia SELECT, dentro de un bloque PL/SQL, haríamos: DECLARE interes NUMBER; BEGIN SELECT finanzas.comp@bcn(anos,cant,porc) INTO interes FROM conta WHERE no_cuenta = cuenta_id END;

Dep. Informática

36

I.E.S. TRASSIERRA - Córdoba

PL/SQL

2.5.- VENTAJAS DE PROCEDIMIENTOS, FUNCIONES Y PAQUETES.El uso de procedimientos, funciones de usuario y paquetes presenta una serie de ventajas: 

Modula el desarrollo de las aplicaciones.



Aumenta la seguridad y la integridad en la Base de Datos. - Mayor independencia de los datos, al ser analizados dentro del servidor Oracle en vez de desde dentro de una aplicación. - Control de acceso indirecto a objetos de la B.D. para usuarios no privilegiados. Asegura que las acciones que estén relacionadas se ejecutan conjuntamente.



Aumenta del rendimiento - Permite realizar cálculos complejos, lentos o no disponibles en SQL - Preanálisis de sentencias que ejecutan varios usuarios, utilizando el SQL compartido. - Las sentencias PL/SQL se analizan en tiempo de compilación y no en ejecución. - Reducción del número de llamadas a la Base de Datos y el tráfico por la red gracias a la agrupación de comandos. - Posibilidad de manipular nuevos tipos de datos (por ejemplo latitud o longitud), codificando cadenas de caracteres y utilizando funciones para operar con las cadenas.



Mejora en la utilización de la memoria - Una única copia del código almacenado en la B.D. en lugar de múltiples copias del mismo código en aplicaciones diferentes. - Utiliza SQL compartido para evitar el exceso de cursores.



Fácil mantenimiento - Permite modificar procedimientos on-line sin interferir al resto de los usuarios (que ejecutan una versión anterior). - La modificación de una rutina afecta a todas las aplicaciones que trabajen con ella. Esto implica eliminar la duplicidad de las comprobaciones.

2.6.- MANEJO DE EXCEPCIONES.Los procedimientos y funciones deben prever cualquier tipo de excepción ocurrida mientras se ejecutan, bien propagando el error al entorno de llamada o bien desviándolo a una zona del subprograma. Como sabemos hay dos tipos de exepciones: las de Oracle y las definidas por el usuario. La siguiente tabla muestra como conseguir la comunicación interactiva del error o realizar una operación en la base de datos. Tipo de excepción

Efecto a conseguir

Método de manejo

Comunicar el error interactivamente Oracle

Definida por usuario

Dep. Informática

Omitir en el procedimiento el manejo de errores Realizar una operación en la base de datos Incluir el manejo de excepciones dentro adaptando el mensaje de error. del procedimiento. Comunicar el error interactivamente Llamar al procedimiento RAISE APPLICATION ERROR. Realizar una operación en la base de datos Incluir el manejo de excepciones dentro adaptando el mensaje de error. del procedimiento

37

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Las excepciones de Oracle pueden ser controladas por el usuario con la sentencia EXCEPTION_INIT ya vista, que permite comunicar el código y el mensaje de error para una excepción de Oracle de forma interactiva omitiendo el manejo de excepciones. Si se omite el manejo de errores y el procedimiento termina con un fallo, devolverá al entorno de llamada el error "UNHANDLED EXCEPTION". Ejemplo: Propagar una excepción del Servidor Oracle (usando la base de datos de ejemplo de oracle) 

Mientras se está almacenando información para un empleado nuevo, comprobar que su jefe existe como empleado: CREATE OR REPLACE PROCEDURE alta_emp (v_emp_no IN emp.empno%TYPE, v_emp_name IN emp.ename%TYPE, v_emp_job IN emp.job%TYPE, v_mgr_no IN emp.mgr%TYPE, v_emp_sal emp.sal%TYPE) IS v_emp_hiredate emp.hiredate%TYPE ; v_emp_comm emp.comm%TYPE ; v_emp_deptno emp.deptno%TYPE ; BEGIN v_emp_hiredate:=sysdate; v_emp_comm:=0; SELECT deptno INTO v_emp_deptno FROM emp WHERE empno = v_mgr_no; INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (v_emp_no, v_emp_name, v_emp_job, v_mgr_no, v_emp_hiredate, v_emp_sal, v_emp_comm, v_emp_deptno); END alta_emp; /

La siguiente sentencia provocaría la respuesta: SQL> EXECUTE alta_emp (1, 'PEPE', 'CLERK', 9999, 1000); BEGIN alta_emp (1, 'PEPE', 'CLERK', 9999, 1000); END; * ERROR en línea 1: ORA-01403: no se han encontrado datos ORA-06512: en "SYSTEM.ALTA_EMP", línea 14 ORA-06512: en línea 1

Ejemplo: Propagar una excepción definida por el usuario. Recordemos que el procedimiento RAISE_APPLICATION_ERROR su usa para mostrar una excepción definida por el usuario de forma interactiva devolviendo un código de error no estándar y un mensaje. 

Mientras se intenta borrar a un empleado, comprobar que el empleado especificado existe: CREATE OR REPLACE PROCEDURE baja_emp (v_emp_numero IN temple.numem%TYPE) IS BEGIN DELETE FROM temple WHERE numem = v_emp_numero; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR (-20300, 'Empleado inexistente'); END IF; END baja_emp; /

Dep. Informática

38

I.E.S. TRASSIERRA - Córdoba

PL/SQL

La siguiente sentencia provocaría la respuesta: SQL> EXECUTE baja_emp(9999); BEGIN baja_emp(9999); END; * ERROR en línea 1: ORA-20300: Empleado inexistente ORA-06512: en "SYSTEM.BAJA_EMP", línea 6 ORA-06512: en línea 1

Si se omite el manejo de excepciones RAISE_APPLICATION_ERROR para una excepción definida por el usuario y el procedimiento finaliza con un fallo, devolverá un mensaje indicando que la excepción definida por el usuario ha fallado. Si se incluye el manejo de excepciones, el proceso se direcciona a la zona de excepciones. El desvío de excepciones definidas por el usuario actúa de forma similar a las excepciones Oracle, pero incluyendo su correspondiente manejador. Hay dos etapas adicionales: la declaración de la excepción e invocarla de manera explícita con la sentencia RAISE.

Ejemplo: Desviar el flujo del programa a una excepción Oracle predefinida. (usando la base de datos de ejemplo de oracle) 

Mientras se almacena información acerca de un nuevo empleado, comprobar que su jefe existe como empleado. CREATE OR REPLACE PROCEDURE alta_emp (v_emp_no IN emp.empno%TYPE, v_emp_name IN emp.ename%TYPE, v_emp_job IN emp.job%TYPE, v_mgr_no IN emp.mgr%TYPE, v_emp_sal emp.sal%TYPE) IS v_emp_hiredate emp.hiredate%TYPE ; v_emp_comm emp.comm%TYPE ; v_emp_deptno emp.deptno%TYPE ; BEGIN v_emp_hiredate:=sysdate; v_emp_comm:=0; SELECT deptno INTO v_emp_deptno FROM emp WHERE empno = v_mgr_no; INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (v_emp_no, v_emp_name, v_emp_job, v_mgr_no, v_emp_hiredate, v_emp_sal, v_emp_comm, v_emp_deptno); EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201, 'El jefe indicado:'|| v_mgr_no||' no es empleado'); END alta_emp; /

La siguiente sentencia provocaría la respuesta: SQL> EXECUTE alta_emp (1, 'PEPE', 'CLERK', 9999, 1000); BEGIN alta_emp (1, 'PEPE', 'CLERK', 9999, 1000); END; * ERROR en línea 1: ORA-20201: El jefe indicado:9999 no es empleado ORA-06512: en "SYSTEM.ALTA_EMP", línea 21 ORA-06512: en línea 1

Dep. Informática

39

I.E.S. TRASSIERRA - Córdoba

PL/SQL

Para direccionar el flujo de programa a una excepción Oracle no predefinida, invocándola con la sentencia EXCEPTION_INIT hay que seguir los siguientes pasos: -

Declarar la excepción. Asociar la excepción declarada con un número de error Oracle utilizando la sentencia EXCEPTION_INIT.

Ejemplo: Desviar el flujo del programa a una excepción Oracle NO-PREDEFINIDA. 

Mientras se almacena información acerca de un nuevo empleado, comprobar que su jefe existe como empleado. CREATE OR REPLACE PROCEDURE alta_emp (v_emp_no IN emp.empno%TYPE, v_emp_name IN emp.ename%TYPE, v_emp_job IN emp.job%TYPE, v_mgr_no IN emp.mgr%TYPE, v_emp_hiredate emp.hiredate%TYPE , v_emp_sal emp.sal%TYPE, v_emp_comm emp.comm%TYPE ) IS v_emp_deptno emp.deptno%TYPE ; e_jefe_erroneo EXCEPTION; PRAGMA EXCEPTION_INIT(e_jefe_erroneo, +100); BEGIN SELECT deptno INTO v_emp_deptno FROM emp WHERE empno = v_mgr_no; INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (v_emp_no, v_emp_name, v_emp_job, v_mgr_no, v_emp_hiredate, v_emp_sal, v_emp_comm, v_emp_deptno); EXCEPTION WHEN e_jefe_erroneo THEN RAISE_APPLICATION_ERROR (-20291, 'Jefe '|| v_mgr_no||' no es empleado'); END alta_emp; /

La siguiente sentencia provocaría la respuesta: SQL> EXECUTE alta_emp(1, ‘PEPE', 'CLERK', 9999, '01-JUL-98', 1000, NULL); BEGIN alta_emp(1, 'PEPE', 'CLERK', 9999, '01-JUL-98', 1000, NULL); END; * ERROR en línea 1: ORA-20291: Jefe 9999 no es empleado ORA-06512: en "SYSTEM.ALTA_EMP", línea 20 ORA-06512: en línea 1

2.7.- EJERCICIOS.2.7.1.- Ejercicios resueltos.1.- Crear el procedimiento aumento_salario para aumentar el salario de un empleado en una cantidad. El número de empleado y el aumento se le pasarán a la función, la cual deberá controlar los errores producidos por la inexistencia del empleado y del salario (valor nulo).

Dep. Informática

40

I.E.S. TRASSIERRA - Córdoba

PL/SQL

CREATE PROCEDURE aumento_salario (empleado integer, incremento real) IS salario_actual real; falta_salario exception; BEGIN SELECT salario INTO salario_actual FROM empleados WHERE no_empleado = empleado; IF salario_actual IS NULL THEN RAISE falta_salario; ELSE UPDATE empleados SET salario = salario + incremento WHERE no_empleado = empleado; END IF EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO mensajes VALUES (empleado, 'No existe el empleado'}; WHEN falta_salario THEN INSERT INTO mensajes VALUES (empleado, 'No tiene salario'); END; /

2.- Determinar si un número es par o impar. Recursividad por interinvocación. CREATE OR REPLACE package pq_par IS FUNCTION par(n number) RETURN boolean; FUNCTION impar(n number) RETURN boolean; END pq_par; / CREATE OR REPLACE PACKAGE BODY pq_par IS FUNCTION par (n number) RETURN boolean IS BEGIN IF n = 0 THEN RETURN TRUE; ELSE RETURN impar(n-1); END IF; END; FUNCTION impar (n number) RETURN boolean IS BEGIN IF n = 0 THEN RETURN FALSE; ELSE RETURN par(n-1); END IF; END; END pq_par;

/ 3.- Ejemplo completo de paquete: Consideremos el siguiente paquete emp_actions. La parte de especificación del paquete declara los siguientes tipos de datos, variables y subprogramas:     

Tipos EmpRecTyp y DeptRecTyp Cursor desc_salary Excepción invalid_salary Funciones hire_employee y nth_highest_salary Procedimientos fire_employee y raise_salary

Tras la escritura del paquete, podemos escribir subprogramas que hagan referencia a los tipos que declara, a su cursor y a su excepción, por que el paquete se almacena en la base de datos para uso público. Dep. Informática

41

I.E.S. TRASSIERRA - Córdoba

PL/SQL

CREATE PACKAGE emp_actions AS /* Declaración pública (visible desde fuera) de tipos, cursor y excepción */ TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL); TYPE DeptRecTyp IS RECORD (dept_id INT, location VARCHAR2); CURSOR desc_salary RETURN EmpRecTyp; invalid_salary EXCEPTION; /* Declaración de subprogramas. */ FUNCTION hire_employee ( ename VARCHAR2, job VARCHAR2, mgr REAL, sal REAL, comm REAL, deptno REAL) RETURN INT; PROCEDURE fire_employee (emp_id INT); PROCEDURE raise_salary (emp_id INT, grade INT, amount REAL); FUNCTION nth_highest_salary (n INT) RETURN EmpRecTyp; END emp_actions; / CREATE PACKAGE BODY emp_actions AS number_hired INT;

-- visible solo desde este paquete

CURSOR desc_salary RETURN EmpRecTyp IS SELECT empno, sal FROM emp ORDER BY sal DESC; FUNCTION hire_employee (

ename VARCHAR2, job VARCHAR2, mgr REAL, sal REAL, comm REAL, deptno REAL) RETURN INT IS

new_empno INT; BEGIN SELECT empno_seq.NEXTVAL INTO new_empno FROM dual; INSERT INTO emp VALUES (new_empno, ename, job, mgr, SYSDATE, sal, comm, deptno); number_hired := number_hired + 1; RETURN new_empno; END hire_employee; PROCEDURE fire_employee (emp_id INT) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; /* Definiciones locales de funciones accesibles solo desde este paquete. */ FUNCTION sal_ok (rank INT, salary REAL) RETURN BOOLEAN IS min_sal REAL; max_sal REAL; BEGIN SELECT losal, hisal INTO min_sal, max_sal FROM salgrade WHERE grade = rank; RETURN (salary >= min_sal) AND (salary = 18 ) THEN RAISE_APPLICATION_ERROR( -20502, 'Solo se puede actualizar datos de empleados durante las horas de trabajo') ; END IF;

END ; /

ORACLE dispara el disparador siempre que una instrucción INSERT, UPDATE o DELETE afecte a la tabla EMP del esquema SCOTT. El disparador realiza las siguientes operaciones: 1.- Si la modificación se intenta realizar un sábado o un domingo, el disparador no permite la modificación y da un mensaje de error. 2.- Si la hora en que se intenta modificar no está entre las 8:00 AM y las 6:00 PM, el disparador da un mensaje de error.

Dep. Informática

53

I.E.S. TRASSIERRA - Córdoba

PL/SQL

3.- Este ejemplo crea un disparador fila BEFORE llamado VALIDA_SALARIO. El disparador garantizará que siempre que un empleado nuevo se añada a la tabla de empleados EMP, o se modifique el salario o el tipo de trabajo de un empleado, el salario del empleado se mantiene dentro del rango establecido en SAL_GUIDE para su categoría. La tabla SAL_GUIDE deberemos crearla: DROP TABLE SAL_GUIDE; CREATE TABLE SAL_GUIDE (JOB VARCHAR2(9) primary key, MINSAL NUMBER(7,2), MAXSAL NUMBER(7,2) ) ; INSERT INTO SAL_GUIDE VALUES ('CLERK', 800, 1300); INSERT INTO SAL_GUIDE VALUES ('ANALYST', 3000, 3500); INSERT INTO SAL_GUIDE VALUES ('SALESMAN', 1250, 1600); INSERT INTO SAL_GUIDE VALUES ('MANAGER', 2450, 2975); INSERT INTO SAL_GUIDE VALUES ('PRESIDENT', 5000, 5500); CREATE OR REPLACE TRIGGER scott.valida_salario BEFORE INSERT OR UPDATE OF sal, job ON scott.emp FOR EACH ROW DECLARE v_minsal sal_guide.minsal%TYPE; v_maxsal sal_guide.maxsal%TYPE; BEGIN /* Calcula en menor y mayor salario de esa categoría de la tabla SAL_GUIDE*/ SELECT minsal, maxsal INTO v_minsal, v_maxsal FROM sal_guide WHERE job = :new.job; /* Si el salario del empleado cae fuera del rango, se provoca un error*/ IF (:new.sal< v_minsal OR :new.sal > v_maxsal) THEN RAISE_APPLICATION_ERROR( -20601, 'Salario ' ||:new.sal || ' fuera de rango de la categoria ' || :new.job || ' para el empleado ' || :new.ename ) ; END IF; END; /

ORACLE dispara el disparador siempre que se ejecute una de las siguientes instrucciones: -

Una instrucción INSERT que añada una fila a la tabla EMP. Una instrucción UPDATE que modifique los valores de las columnas SAL o JOB de la tabla EMP.

El disparador realiza las siguientes operaciones: 1.- Consulta la tabla de salarios para obtener el salario máximo y mínimo. 2.- Compara el salario del empleado con el salario máximo y mínimo. 3.- Si el salario del empleado no está dentro del rango, el disparador dá un mensaje de error y aborta la actualización.

3.6.2.- Ejercicios propuestos.1.- Partiendo del ejercicio resuelto 2, desarrollar el disparador TR_TEMPLE para que funcione con la tabla Temple, mejorándolo para que también evite las actualizaciones en los días de fiesta. Para ello deberá crearse la tabla FESTIVOS con una única columna Fiestas contendría las fechas de los días no laborables distintos a sábados y domingos. Lógicamente esta tabla se actualizaría anualmente. Dep. Informática

54

I.E.S. TRASSIERRA - Córdoba

PL/SQL

2.- Crear el disparador DIR_SET_NULL, que controle que antes de borrar un empleado que sea director ponga a nulo el campo direc del departamento o departamentos de los que era director, y que si se modifica la clave primaria de un director, actualice automáticamente el campo direc de los departamentos en los que él es director.

3.- Crear el disparador DEP_DEL_CASCADE que permita que antes de borrar un departamento se borren todos los empleados del mismo.

4.- Sobre la tabla TDEPTO, codificar el disparador de nombre TDEPTO_PRESU_SAL que impedirá que el presupuesto de un departamento sea inferior a la suma de los salarios y comisiones de los empleados de ese departamento. Si una actualización intenta violar la anterior restricción, el disparador devolverá el código de error número -20112 y el mensaje 'El presupuesto no puede ser inferior a la suma de los sueldos de sus empleados'.

5.- Crear el disparador DIRPRO que evite que un director lo sea en propiedad en más de un departamento. Ojo, problema de las tablas mutantes. Caso de que se intente violar la anterior restricción, el disparador devolverá el código de error -20800, y el mensaje 'El jefe ya lo es en propiedad de otro departamento.'

6.- Modificar el disparador VALIDA_SALARIO del ejercicio resuelto nº 3 para que vele por que se cumpla que los salarios de todos los empleados salvo el presidente: - Se encuentren comprendidos entre el valor de minsal y maxsal de su categoría (JOB). - Que no disminuyan. - Que no se vean incrementados en más del 10 % de una vez. El tratamiento de los errores se hará con RAISE_APPLICATION_ERROR.

Dep. Informática

55