logotipo

PROGRAMACIÓN EN PL/SQL

INTRODUCCIÓN

Oracle es una base de datos relacional y el lenguaje utilizado para acceder a las bases de datos relacionales es SQL. Muchas aplicaciones de bases de datos se construyen utilizando la tecnología cliente-servidor y Oracle nos permite utilizar esta tecnología.

Para trabajar con este temario nos basaremos en las siguientes tablas:

Tabla Students:

Nombre                                                     ¿Nulo?                 Tipo
----------------------------------------- --------                 --------------
ID                                                             NOT NULL         NUMBER(5)
FIRST_NAME                                                                     VARCHAR2(20)
LAST_NAME                                                                      VARCHAR2(20)
MAJOR                                                                                VARCHAR2(30)
CURRENT_CREDITS                                                         NUMBER(3)

También tendremos la tabla Major_Stats:

Nombre                                                 ¿Nulo?     Tipo
----------------------------------------- -------- ----------------------------
MAJOR                                                                  VARCHAR2(30)
TOTAL_CREDITS                                                 NUMBER
TOTAL_STUDENTS                                             NUMBER

Otra tabla que tendremos es la tabla Rooms:

Nombre                                                 ¿Nulo?         Tipo
----------------------------------------- -------- ----------------------------
ID                                                                           NUMBER(5)
BUILDING                                                            VARCHAR2(15)
ROOM_NUMBER                                                NUMBER(4)
NUMBER_SEATS                                                NUMBER(4)
DESCRIPTION                                                     VARCHAR2(50)

También tendremos la tabla Classes:

Nombre                                                 ¿Nulo?         Tipo
----------------------------------------- -------- ----------------------------
DEPARTMENT                                                       CHAR(3)
COURSE                                                                 NUMBER(3)
DESCRIPTION                                                       VARCHAR2(2000)
MAX_STUDENTS                                                  NUMBER(3)
CURRENT_STUDENTS                                         NUMBER(3)
NUM_CREDITS                                                     NUMBER(1)
ROOM_ID                                                              NUMBER(5)

Tenemos la tabla Registered_students

Nombre                                                     ¿Nulo?                 Tipo
-----------------------------------------     --------             ------------
STUDENT_ID                                         NOT NULL         NUMBER(5)
DEPARTMENT                                       NOT NULL         CHAR(3)
COURSE                                                 NOT NULL         NUMBER(3)
GRADE                                                                                CHAR(1)

Tendremos también la tabla RS_Audit, que almacenará los cambios registrados en registered_students.

Nombre                                                 ¿Nulo?                 Tipo
----------------------------------------- --------             ----------------
CHANGE_TYPE                                    NOT NULL     CHAR(1)
CHANGED_BY                                     NOT NULL     VARCHAR2(8)
TIMESTAMP                                         NOT NULL     DATE
OLD_STUDENT_ID                                                      NUMBER(5)
OLD_DEPARTMENT                                                    CHAR(3)
OLD_COURSE                                                              NUMBER(3)
OLD_GRADE                                                                CHAR(1)
NEW_STUDENT_ID                                                     NUMBER(5)
NEW_DEPARTMENT                                                   CHAR(3)
NEW_COURSE                                                             NUMBER(3)
NEW_GRADE                                                                CHAR(1)

Tenemos tres tablas más: log_table (que registrará los errores de Oracle), temp_table (que almacenará datos temporales) y debug_table (que almacenará la información de depuración).

IDENTIFICADORES

Los identificadores se utilizan para dar nombre a los objetos PL/SQL, tales como variables, cursores, tipos y subprogramas. Constan de una letra seguida por una secuencia opcional de caracteres. Pero si queremos usar identificadores usando espacios, diferenciando mayúsculas y minúsculas, etc. tendremos que poner estos identificadores entre comillas.

DECLARACIÓN DE VARIABLES

La comunicación en la base de datos tiene lugar mediante el uso de variables incluidas en los bloques de PL/SQL. La declaración de variables tiene lugar en la sección declarativa del bloque.

ESTRUCTURA DE CONTROL PL/SQL

Los bloques de control permiten que se realicen diferentes porciones de código según se cumpla una u otra condición.

DECLARE
v_NumberSeats rooms.number_seats%TYPE;
v_Comment VARCHAR2(35);
BEGIN
/* Retrieve the number of seats in the room identified by ID 99999.
Store the result in v_NumberSeats. */
SELECT number_seats
INTO v_NumberSeats
FROM rooms
WHERE room_id = 99999;
IF v_NumberSeats < 50 THEN
v_Comment := 'Fairly small';
ELSIF v_NumberSeats < 100 THEN
v_Comment := 'A little bigger';
ELSE
v_Comment := 'Lots of room';
END IF;
END;

BUCLES.

Como habíamos visto en la introducción tenemos varios tipos de bucles, vamos a verlos con unos ejemplos:

DECLARE
v_Counter BINARY_INTEGER := 1;
BEGIN
LOOP
-- Insert a row into temp_table with the current value of the
-- loop counter.
INSERT INTO temp_table
VALUES (v_Counter, 'Loop index');
v_Counter := v_Counter + 1;
-- Exit condition - when the loop counter > 50 we will
-- break out of the loop.
IF v_Counter > 50 THEN
EXIT;
END IF;
END LOOP;
END;
/

DECLARE
v_Counter BINARY_INTEGER := 1;
BEGIN
-- Test the loop counter before each loop iteration to
-- insure that it is still less than 50.
WHILE v_Counter <= 50 LOOP
INSERT INTO temp_table
VALUES (v_Counter, 'Loop index');
v_Counter := v_Counter + 1;
END LOOP;
END;

DECLARE
v_Counter BINARY_INTEGER;
BEGIN
-- This condition will evaluate to NULL, since v_Counter
-- is initialized to NULL by default.
WHILE v_Counter <= 50 LOOP
INSERT INTO temp_table
VALUES (v_Counter, 'Loop index');
v_Counter := v_Counter + 1;
END LOOP;
END;
/

BEGIN
FOR v_Counter IN 1..50 LOOP
INSERT INTO temp_table
VALUES (v_Counter, 'Loop Index');
END LOOP;
END;
/

DECLARE
v_Counter NUMBER := 7;
BEGIN
-- Inserts the value 7 into temp_table.
INSERT INTO temp_table (num_col)
VALUES (v_Counter);
-- This loop redeclares v_Counter as a BINARY_INTEGER, which hides
-- the NUMBER declaration of v_Counter.
FOR v_Counter IN 20..30 LOOP
-- Inside the loop, v_Counter ranges from 20 to 30.
INSERT INTO temp_table (num_col)
VALUES (v_Counter);
END LOOP;
-- Inserts another 7 into temp_table.
INSERT INTO temp_table (num_col)
VALUES (v_Counter);
END;
/

ÓRDENES GOTO Y ETIQUETAS

Las órdenes GOTO envían el proceso del código que se está ejecutando a una etiqueta. Veamos un ejemplo de orden GOTO.

DECLARE
v_Counter BINARY_INTEGER := 1;
BEGIN
LOOP
INSERT INTO temp_table
VALUES (v_Counter, 'Loop count');
v_Counter := v_Counter + 1;
IF v_Counter > 50 THEN
GOTO l_EndOfLoop;
END IF;
END LOOP;

<<l_EndOfLoop>>
INSERT INTO temp_table (char_col)
VALUES ('Done!');
END;
/

REGISTROS Y TABLAS

Los registros de PL/SQL son similares a las estructuras de lenguaje C. Un registro proporciona un mecanismo para tratar con variables diferentes pero relacionadas como si fueran una unidad.

Por ejemplo:

DECLARE

v_studentID NUMBER(5);

v_firstName VARCHAR2(20);

v_LastName VARCHAR2(20);

Estas tres variable tienen una relación lógica entre sí, las tres hacen referencia a campos de la tabla STUDENTS. Lo que podemos hacer es declarar un tipo con estas tres variables para que actúen de forma conjunta.

TYPE t_StudentRecord IS RECORD (

StudentId NUMBER(5),

FirstName VARCHAR2(20),

LastName VARCHAR2(20));

v_studentInfo t_studentRecord;

Para poder asignar un registro a otro ambos registros deben ser del mismo tipo. La asignación de los registros se haría como indicamos en el código siguiente:

DECLARE
TYPE t_Rec1Type IS RECORD (
Field1 NUMBER,
Field2 VARCHAR2(5));
TYPE t_Rec2Type IS RECORD (
Field1 NUMBER,
Field2 VARCHAR2(5));
v_Rec1 t_Rec1Type;
v_Rec2 t_Rec2Type;
BEGIN
/* Even though v_Rec1 and v_Rec2 have the same field names
and field types, the record types themselves are different.
This is an illegal assignment which raises PLS-382. */
v_Rec1 := v_Rec2;

/* However, the fields are the same type, so the following
are legal assignments. */
v_Rec1.Field1 := v_Rec2.Field1;
v_Rec2.Field2 := v_Rec2.Field2;
END;
/

Aunque también podemos asignar valores mediante una orden SELECT:

DECLARE
-- Define a record to match some fields in the students table.
-- Note the use of %TYPE for the fields.
TYPE t_StudentRecord IS RECORD (
FirstName students.first_name%TYPE,
LastName students.last_name%TYPE,
Major students.major%TYPE);

-- Declare a variable to receive the data.
v_Student t_StudentRecord;
BEGIN
-- Retrieve information about student with ID 10,000.
-- Note how the query is returning columns which match the
-- fields in v_Student.
SELECT first_name, last_name, major
INTO v_Student
FROM students
WHERE ID = 10000;
END;
/

TABLAS

Las tablas se asemejan a lo que se denominan matrices, se tratan de la misma manera aunque tienen una implementación distinta. Para poder declarar una tabla tenemos que definir primero su tipo y posteriormente una variable del mismo tipo. Una vez definido el tipo y la variable tendremos que referenciarla con la sintaxis:

nombreTabla(índice)

Estas tablas tienen una serie de atributos que son:

ATRIBUTO	TIPO DEVUELTO	DESCRIPCIÓN
COUNT	NUMBER	Devuelve el nº de filas de una tabla
DELETE		Borra las filas de una tabla
EXISTS	BOOLEAN	Devuelve TRUE si existe en la tabla el elemento especificado
FIRST	BINARY_INTEGER	Devuelve el índice de la primera fila de la tabla
LAST	BINARY_INTEGER
NEXT	BINARY_INTEGER
PRIOR	BINARY_INTEGER

SEUDOCOLUMNAS

Las seudocolumnas son funciones adicionales que sólo se pueden llamar desde una orden SQL. Sintácticamente se las trata como una columna de una tabla. Las seudocolumnas se emplean con las secuencias, que son un objeto que se emplea para crear series de números diferentes.

CURSORES

¿Qué es un cursor?

Para poder procesar una orden SQL oracle le asigna una área de memoria que recibe el nombre de área de contexto. Esta área contiene informaciones necesarias para completar el proceso, incluyendo el número de filas procesadas por la orden, el conjunto activo de filas,...

Un cursor es un puntero en el área de contexto que permite controlar lo que sucede en ese área, .... Veamos un ejemplo que lo que va a hacer es una extracción mediante cursor en que una consulta devuelve múltiples filas de datos.

DECLARE
/* Output variables to hold the results of the query */
v_StudentID students.id%TYPE;
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;

/* Bind variable used in the query */
v_Major students.major%TYPE := 'Computer Science';

/* Cursor declaration */
CURSOR c_Students IS
SELECT id, first_name, last_name
FROM students
WHERE major = v_Major;
BEGIN
/* Identify the rows in the active set, and prepare for further
processing of the data */
OPEN c_Students;
LOOP
/* Retrieve each row of the active set into PL/SQL variables */
FETCH c_Students INTO v_StudentID, v_FirstName, v_LastName;

/* If there are no more rows to fetch, exit the loop */
EXIT WHEN c_Students%NOTFOUND;
END LOOP;

/* Free resources used by the query */
CLOSE c_Students;
END;
/

Los cursores pueden ser de dos tipos:

* Implícitos: no los declaramos nosotros, se declaran automáticamente cuando ocurre una transacción.

* Explícitos: como el ejemplo anterior, lo declaramos nosotros y podemos decir que su proceso consta de cuatro pasos:

1. Declaración del cursor:

CURSOR c_Students IS
SELECT id, first_name, last_name
FROM students
WHERE major = v_Major;

2. Apertura del cursor para una consulta:

OPEN c_Students;
LOOP

3. Extracción de los datos de un cursor:

FETCH c_Students INTO v_StudentID, v_FirstName, v_LastName;

4. Cierre del cursor:

CLOSE c_Students;

ATRIBUTOS DE LOS CURSORES

Existen cuatro atributos de los cursores que podemos aplicar, se añaden dentro de un bloque PL/SQL al nombre del cursor, y estos atributos son:

%FOUND: es un atributo booleano, de forma que si la última orden FETCH devuelve una fila nos devuelve el valor TRUE, si no nos devuelve FALSE.
%NOTFOUND: se comporta de forma opuesta a %FOUND.
%ISOPEN: es también un atributo booleano que se utiliza para asociar si el cursor asociado está abierto o no.
%ROWCOUNT: este atributo numérico devuelve el número de filas extraídas por el cursor hasta ese momento.

PROCESAMIENTO DE CURSORES IMPLÍCITOS

Los cursores implícitos sirven para procesar órdenes SELECT que devuelven más de una fila. El cursor implícito sirve para procesar las órdenes INSERT, UPDATE y DELETE así como las de SELECT...INTO de una sola fila. Las órdenes OPEN, FETCH y CLOSE no son relevantes para este tipo de cursores, aunque si que permiten que se les asigne los atributos de cursor mencionados arriba. Veamos un bloque:

BEGIN
UPDATE rooms
SET number_seats = 100
WHERE room_id = 99980;
-- If the previous UPDATE statement didn't match any rows,
-- insert a new row into the rooms table.
IF SQL%NOTFOUND THEN
INSERT INTO rooms (room_id, number_seats)
VALUES (99980, 100);
END IF;
END;
/

BUCLES DE EXTRACCIÓN MEDIANTE CURSOR

La operación más común que se puede realizar con cursores consiste en extraer todas las filas del conjunto activo, y para hacerlo usaremos un bucle de extracción. Tenemos diferentes tipos de bucle:

1. Bucles simples: el bucle más simple con el que podemos trabajar es el LOOP ... END LOOP. Su sintaxis en un ejemplo sería:

DECLARE
-- Declare variables to hold information about the students
-- majoring in History.
v_StudentID students.id%TYPE;
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;

-- Cursor to retrieve the information about History students
CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name
FROM students
WHERE major = 'History';
BEGIN
-- Open the cursor and initialize the active set
OPEN c_HistoryStudents;
LOOP
-- Retrieve information for the next student
FETCH c_HistoryStudents INTO v_StudentID, v_FirstName, v_LastName;

-- Exit loop when there are no more rows to fetch
EXIT WHEN c_HistoryStudents%NOTFOUND;

-- Process the fetched rows. In this case sign up each
-- student for History 301 by inserting them into the
-- registered_students table. Record the first and last
-- names in temp_table as well.
INSERT INTO registered_students (student_id, department, course)
VALUES (v_StudentID, 'HIS', 301);

INSERT INTO temp_table (num_col, char_col)
VALUES (v_StudentID, v_FirstName || ' ' || v_LastName);

END LOOP;

-- Free resources used by the cursor
CLOSE c_HistoryStudents;

-- Commit our work
COMMIT;
END;
/

2. Bucles WHILE.

Podemos utilizar un bucle WHILE, el código quedaría como sigue:

DECLARE
-- Cursor to retrieve the information about History students
CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name
FROM students
WHERE major = 'History';

-- Declare a record to hold the fetched information.
v_StudentData c_HistoryStudents%ROWTYPE;
BEGIN
-- Open the cursor and initialize the active set
OPEN c_HistoryStudents;

-- Retrieve the first row, to set up for the WHILE loop
FETCH c_HistoryStudents INTO v_StudentData;

-- Continue looping while there are more rows to fetch
WHILE c_HistoryStudents%FOUND LOOP
-- Process the fetched rows, in this case sign up each
-- student for History 301 by inserting them into the
-- registered_students table. Record the first and last
-- names in temp_table as well.
INSERT INTO registered_students (student_id, department, course)
VALUES (v_StudentData.ID, 'HIS', 301);

INSERT INTO temp_table (num_col, char_col)
VALUES (v_StudentData.ID,
v_StudentData.first_name || ' ' || v_StudentData.last_name);

-- Retrieve the next row. The %FOUND condition will be checked
-- before the loop continues again.
FETCH c_HistoryStudents INTO v_StudentData;
END LOOP;

-- Free resources used by the cursor
CLOSE c_HistoryStudents;

-- Commit our work
COMMIT;
END;
/

3. Bucles de cursor FOR

Los bucles FOR se caracterizan porque hacen un procesamiento implícito del cursor. El código sería:

DECLARE
-- Cursor to retrieve the information about History students
CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name
FROM students
WHERE major = 'History';
BEGIN
-- Begin the loop. An implicit OPEN of c_HistoryStudents
-- is done here.
FOR v_StudentData IN c_HistoryStudents LOOP
-- An implicit FETCH is done here.

-- Process the fetched rows, in this case sign up each
-- student for History 301 by inserting them into the
-- registered_students table. Record the first and last
-- names in temp_table as well.
INSERT INTO registered_students (student_id, department, course)
VALUES (v_StudentData.ID, 'HIS', 301);

INSERT INTO temp_table (num_col, char_col)
VALUES (v_StudentData.ID,
v_StudentData.first_name || ' ' || v_StudentData.last_name);

-- Before the loop will continue, an implicit check of
-- c_HistoryStudents%NOTFOUND is done here.
END LOOP;
-- Now that the loop is finished, an implicit CLOSE of
-- c_HistoryStudents is done.

-- Commit our work.
COMMIT;
END;
/

SUBPROGRAMAS, PROCEDIMIENTOS Y FUNCIONES

Hasta ahora todos los bloques que hemos visto eran bloques anónimos, pero puede suceder que tengamos que generar bloques de código que tendremos que almacenar y utilizar posteriormente. Así, por ejemplo, podemos tener el siguiente bloque de código que creará un nuevo registro en la tabla de estudiantes:

CREATE OR REPLACE PROCEDURE AddNewStudent (
p_FirstName students.first_name%TYPE,
p_LastName students.last_name%TYPE,
p_Major students.major%TYPE) AS
BEGIN
-- Insert a new row in the students table. Use
-- student_sequence to generate the new student ID, and
-- 0 for current_credits.
INSERT INTO students (ID, first_name, last_name,
major, current_credits)
VALUES (student_sequence.nextval, p_FirstName, p_LastName,
p_Major, 0);

COMMIT;
END AddNewStudent;
/

Una vez cerrado el procedimiento podemos invocarlo desde otro procedimiento mediante el código:

BEGIN

AddNewStudent('Pepe','Lopez','Música');

END;

La sintaxis para la creación de un procedimiento sería como sigue:

CREATE OR REPLACE PROCEDURE nombre_proc

argumentos AS

procedimiento

Los parámetros formales pueden tener tres modos: IN, OUT o IN OUT. Si no se especifica su modo cogerá por defecto el modo IN. Veamos estos modos:

IN: el valor del parámetro real se pasa al procedimiento cuando éste es invocado. El parámetro se considera de sólo lectura y no puede ser cambiado. Es un RVALOR dentro de un procedimiento pudiendo aparecer únicamente en la parte derecha de la asignación.
OUT: se ignora cualquier valor que tenga el parámetro real cuando se llama al procedimientos. El parámetro formal se considera de sólo escritura. Es un LVALOR dentro del procedimiento y tiene que aparecer en la parte izquierda de la asignación.
IN OUT: el valor del parámetro real se pasa al procedimiento cuando éste es invocado, el parámetro formal puede ser tanto de lectura como de escritura.

Veamos un ejemplo:

CREATE OR REPLACE PROCEDURE ModeTest (
p_InParameter IN NUMBER,
p_OutParameter OUT NUMBER,
p_InOutParameter IN OUT NUMBER) IS

v_LocalVariable NUMBER;
BEGIN
/* Assign p_InParameter to v_LocalVariable. This is legal,
since we are reading from an IN parameter and not writing
to it. */
v_LocalVariable := p_InParameter; -- Legal --------> parte derecha de la asignación

/* Assign 7 to p_InParameter. This is ILLEGAL, since we
are writing to an IN parameter. */
p_InParameter := 7; -- Illegal

/* Assign 7 to p_OutParameter. This is legal, since we
are writing to an OUT parameter and not reading from
it. */
p_OutParameter := 7; -- Legal ---------------------------->parte izquierda de la asignación

/* Assign p_OutParameter to v_LocalVariable. This is
ILLEGAL, since we are reading from an OUT parameter. */
v_LocalVariable := p_outParameter; -- Illegal

/* Assign p_InOutParameter to v_LocalVariable. This is legal,
since we are reading from an IN OUT parameter. */
v_LocalVariable := p_InOutParameter; -- Legal

/* Assign 7 to p_InOutParameter. This is legal, since we
are writing to an IN OUT parameter. */
p_InOutParameter := 7; -- Legal
END ModeTest;
/

EL CUERPO DEL PROCEDIMIENTO

El cuerpo de un procedimiento es un bloque PL/SQL con sus secciones declarativa, ejecutable y de manejo de excepciones. Cuando trabajamos con variables, tenemos que tener en cuenta:

* Parámetros reales: DECLARE v_variable

* Parámetros formales: van dentro del cuerpo del procedimiento y no tengo que usar el DECLARE.

Los parámetros formales de un procedimiento o función pueden tener valores predeterminados. Si tiene un valor predeterminado, no tiene por que ser pasado desde el entorno que realiza la llamada.

Por ejemplo, para asignar como valor predeterminado la especialidad de economía a todos los nuevos estudiantes a menos que se le indique lo contrario, tendremos que usar la notación:

CREATE OR REPLACE PROCEDURE AddNewStudent (
p_FirstName students.first_name%TYPE,
p_LastName students.last_name%TYPE,
p_Major students.major%TYPE DEFAULT 'Economics') AS
BEGIN
-- Insert a new row in the students table. Use
-- student_sequence to generate the new student ID, and
-- 0 for current_credits.
INSERT INTO students VALUES (student_sequence.nextval,
p_FirstName, p_LastName, p_Major, 0);

COMMIT;
END AddNewStudent;
/

El parámetro predeterminado se empleará siempre que el parámetro formal p_major no tenga un parámetro real asociado en la llamada al procedimiento.

BEGIN

AddNewStudent('Pepe', 'Blues');

END;

Al emplear esta notación todos los parámetros asociados que utilicen un default tendrán que pasarse al final del procedimiento.

En cambio, podemos usar también la notación nominal para pasar los datos:

BEGIN

AddNewStudent(p_firstName =>'Pepe',

p_lastName='Blues');

END;

CREACIÓN DE FUNCIONES.

Una función es bastante similar a un procedimiento, ambos aceptan argumentos, ambos se pueden almacenar dentro de las bases de datos o ser declarados dentro de un bloque. Una llamada a una función es un RVALOR. Por ejemplo, la función que pasaremos a continuación devuelve el valor TRUE si la clase especificada tiene una ocupación mayor que 90% y false en caso contrario:

CREATE OR REPLACE FUNCTION AlmostFull (
p_Department classes.department%TYPE,
p_Course classes.course%TYPE)
RETURN BOOLEAN IS

v_CurrentStudents NUMBER;
v_MaxStudents NUMBER;
v_ReturnValue BOOLEAN;
v_FullPercent CONSTANT NUMBER := 90;
BEGIN
-- Get the current and maximum students for the requested
-- course.
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE department = p_Department
AND course = p_Course;

-- If the class is more full than the percentage given by
-- v_FullPercent, return TRUE. Otherwise, return FALSE.
IF (v_CurrentStudents / v_MaxStudents * 100) > v_FullPercent THEN
v_ReturnValue := TRUE;
ELSE
v_ReturnValue := FALSE;
END IF;

RETURN v_ReturnValue;
END AlmostFull;
/

Podemos llamar a la función AlmostFull desde el siguiente bloque PL/SQL, en la que veremos que se usa como parte de la orden IF situada dentro del bucle:

DECLARE
CURSOR c_Classes IS
SELECT department, course
FROM classes;
BEGIN
FOR v_ClassRecord IN c_Classes LOOP
-- Record all classes which don't have very much room left
-- in temp_table.
IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN
INSERT INTO temp_table (char_col) VALUES
(v_ClassRecord.department || ' ' || v_ClassRecord.course ||
' is almost full!');
END IF;
END LOOP;
END;
/

Dentro del cuerpo de la función la orden RETURN se emplea para devolver el control y un valor al entorno que realizó la llamada. Puede haber más de una orden RETURN aunque sólo se ejecutará una de las mismas.

Así podríamos tener un código como el siguiente:

CREATE OR REPLACE FUNCTION ClassInfo (
/* Returns 'Full' if the class is completely full,
'Some Room' if the class is over 80% full,
'More Room' if the class is over 60% full,
'Lots of Room' if the class is less than 60% full, and
'Empty' if there are no students registered. */
p_Department classes.department%TYPE,
p_Course classes.course%TYPE)
RETURN VARCHAR2 IS

v_CurrentStudents NUMBER;
v_MaxStudents NUMBER;
v_PercentFull NUMBER;
BEGIN
-- Get the current and maximum students for the requested
-- course.
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE department = p_Department
AND course = p_Course;

-- Calculate the current percentage.
v_PercentFull := v_CurrentStudents / v_MaxStudents * 100;

IF v_PercentFull = 100 THEN
RETURN 'Full';
ELSIF v_PercentFull > 80 THEN
RETURN 'Some Room';
ELSIF v_PercentFull > 60 THEN
RETURN 'More Room';
ELSIF v_PercentFull > 0 THEN
RETURN 'Lots of Room';
ELSE
RETURN 'Empty';
END IF;
END ClassInfo;
/

Las funciones comparten muchas de las características de los procedimientos. Así tendremos entre otras las siguientes características:

Las funciones pueden devolver más de un valor mediante la orden OUT
El código de la función tiene secciones declarativas, ejecutable y de manejo de excepciones
Las funciones pueden aceptar valores predeterminados
Puede llamarse a las funciones utilizando notación posicional o nominal.

EXCEPCIONES GENERADAS DENTRO DE UN SUBPROGRAMA

Si ocurre un error dentro de un subprograma se genera una excepción, que puede ser predefinida o definida por el usuario. Si el procedimiento no dispone de ninguna tarea para el manejo de este error, se devuelve el control al entorno que realizó la llamada la procedimiento. Aunque, nosotros podemos gestionar estos errores para tratar dichos errores.

CREATE OR REPLACE PROCEDURE RaiseError (
/* Illustrates the behavior of unhandled exceptions and
OUT variables. If p_Raise is TRUE, then an unhandled
error is raised. If p_Raise is FALSE, the procedure
completes successfully. */
p_Raise IN BOOLEAN := TRUE,
p_ParameterA OUT NUMBER) AS
BEGIN
p_ParameterA := 7;

IF p_Raise THEN
/* Even though we have assigned 7 to p_ParameterA, this
unhandled exception causes control to return immediately
without returning 7 to the actual parameter associated
with p_ParameterA. */
RAISE DUP_VAL_ON_INDEX;
ELSE
/* Simply return with no error. This will return 7 to the
actual parameter. */
RETURN;
END IF;
END RaiseError;
/

PROMPT Calling RaiseError...
DECLARE
v_TempVar NUMBER := 1;
BEGIN
INSERT INTO temp_table (num_col, char_col)
VALUES (v_TempVar, 'Initial value');
RaiseError(FALSE, v_TempVar);

INSERT INTO temp_table (num_col, char_col)
VALUES (v_TempVar, 'Value after successful call');

v_TempVar := 2;
INSERT INTO temp_table (num_col, char_col)
VALUES (v_TempVar, 'Value before 2nd call');
RaiseError(TRUE, v_TempVar);
EXCEPTION
WHEN OTHERS THEN
INSERT INTO temp_table (num_col, char_col)
VALUES (v_TempVar, 'Value after unsuccessful call');
END;
/

SELECT * FROM temp_table;

PAQUETES

Los paquetes son el tercer tipo de bloques PL/SQL nominados después de los procedimientos y de las funciones. Resultan muy útiles porque permiten un mecanismo para la extensión del lenguaje.

Un paquete es en esencia una sección declarativa nominada. La inclusión de objetos en paquetes proporciona la ventaja de que permite referenciarlos desde otros bloques PL/SQL. Los paquetes están formados por:

Especificación o cabecera del paquete: contiene información acerca del contenido del paquete. Sin embargo, no contiene el código de los procedimientos.
Cuerpo del paquete: es un objeto del diccionario de datos distinto de la cabecera, el cuerpo no se puede compilar a menos que se haya compilado previamente la cabecera del mismo. El cuerpo contiene el código de los objetos que se hayan especificado en la cabecera.

Así tendremos:

CREATE OR REPLACE PACKAGE ClassPackage AS
-- Add a new student into the specified class.
PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE);

-- Removes the specified student from the specified class.
PROCEDURE RemoveStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE);

-- Exception raised by RemoveStudent.
e_StudentNotRegistered EXCEPTION;

-- Table type used to hold student info.
TYPE t_StudentIDTable IS TABLE OF students.id%TYPE
INDEX BY BINARY_INTEGER;

-- Returns a PL/SQL table containing the students currently
-- in the specified class.
PROCEDURE ClassList(p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE,
p_IDs OUT t_StudentIDTable,
p_NumStudents IN OUT BINARY_INTEGER);
END ClassPackage;
/

CREATE OR REPLACE PACKAGE BODY ClassPackage AS
-- Add a new student for the specified class.
PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
INSERT INTO registered_students (student_id, department, course)
VALUES (p_StudentID, p_Department, p_Course);
COMMIT;
END AddStudent;

-- Removes the specified student from the specified class.
PROCEDURE RemoveStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
DELETE FROM registered_students
WHERE student_id = p_StudentID
AND department = p_Department
AND course = p_Course;

-- Check to see if the DELETE operation was successful. If
-- it didn't match any rows, raise an error.
IF SQL%NOTFOUND THEN
RAISE e_StudentNotRegistered;
END IF;

COMMIT;
END RemoveStudent;

-- Returns a PL/SQL table containing the students currently
-- in the specified class.
PROCEDURE ClassList(p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE,
p_IDs OUT t_StudentIDTable,
p_NumStudents IN OUT BINARY_INTEGER) IS

v_StudentID registered_students.student_id%TYPE;

-- Local cursor to fetch the registered students.
CURSOR c_RegisteredStudents IS
SELECT student_id
FROM registered_students
WHERE department = p_Department
AND course = p_Course;
BEGIN
/* p_NumStudents will be the table index. It will start at
0, and be incremented each time through the fetch loop.
At the end of the loop, it will have the number of rows
fetched, and therefore the number of rows returned in
p_IDs. */
p_NumStudents := 0;

OPEN c_RegisteredStudents;
LOOP
FETCH c_RegisteredStudents INTO v_StudentID;
EXIT WHEN c_RegisteredStudents%NOTFOUND;

p_NumStudents := p_NumStudents + 1;
p_IDs(p_NumStudents) := v_StudentID;
END LOOP;
END ClassList;
END ClassPackage;
/

Cualquier objeto que hayamos declarado en la cabecera del paquete diremos que está dentro del ámbito y que es visible fuera del paquete. Podemos efectuar una llamada a cualquier procedimiento incluido en el paquete simplemente indicando nombrePaquete.Objeto

Dentro del cuerpo del paquete podemos hacer referencia a otros objetos generados en el paquete.

DISPARADORES

Es el cuarto bloque PL/SQL nominado, comparten muchas de las características de los subprogramas, pero tienen diferencias significativas, como puede ser el hecho de que se disparen automáticamente cuando se genere una sentencia DML.

Los disparadores los podemos utilizar para, entre otras cosas:

Mantener restricciones de integridad complejas.
Auditoría de la información contenida en una tabla.
Aviso automático a otros programas de que hay que llevar a cabo una determinada acción.

Por ejemplo si queremos mantener una serie de estadísticas referentes a las diferentes especialidades en que se matriculan los alumnos, usaremos estos datos almacenándolos en la tabla de major_stats, y para actualizar las estadísticas de esta tabla podríamos generar un trigger que lo que haría sería modificar esta tabla cada vez que se modifique la tabla de students, el trigger quedaría de la siguiente manera:

CREATE OR REPLACE TRIGGER UpdateMajorStats
/* Keeps the major_stats table up-to-date with changes made
to the students table. */
AFTER INSERT OR DELETE OR UPDATE ON students
DECLARE
CURSOR c_Statistics IS
SELECT major, COUNT(*) total_students,
SUM(current_credits) total_credits
FROM students
GROUP BY major;
BEGIN
/* Loop through each major. Attempt to update the statistics
in major_stats corresponding to this major. If the row
doesn't exist, create it. */
FOR v_StatsRecord in c_Statistics LOOP
UPDATE major_stats
SET total_credits = v_StatsRecord.total_credits,
total_students = v_StatsRecord.total_students
WHERE major = v_StatsRecord.major;
/* Check to see if the row exists. */
IF SQL%NOTFOUND THEN
INSERT INTO major_stats (major, total_credits, total_students)
VALUES (v_StatsRecord.major, v_StatsRecord.total_credits,
v_StatsRecord.total_students);
END IF;
END LOOP;
END UpdateMajorStats;
/

Este trigger se divide en:

1. Creación del trigger:

CREATE OR REPLACE TRIGGER UpdateMajorStats
AFTER INSERT OR DELETE OR UPDATE ON students

2. Declaración de las variables que formarán parte del trigger, estas variables almacenarán los datos antes de pasarlos a la tabla definitiva que sería la de major_stats, en este caso los datos se almacenarán en un cursor:

DECLARE
CURSOR c_Statistics IS
SELECT major, COUNT(*) total_students,
SUM(current_credits) total_credits
FROM students
GROUP BY major;

3. Iniciaremos el proceso de lo que va a tener que realizar este trigger:

BEGIN
/* Hacemos un bucle para cada major, intentaremos updatar las estadísticas de la tabla major_stats con el registro correspondiente (v_statsRecordMajor), y si no existe insertaremos la fila.*/
FOR v_StatsRecord in c_Statistics LOOP
UPDATE major_stats
SET total_credits = v_StatsRecord.total_credits,
total_students = v_StatsRecord.total_students
WHERE major = v_StatsRecord.major;
/* Check to see if the row exists. */
IF SQL%NOTFOUND THEN
INSERT INTO major_stats (major, total_credits, total_students)
VALUES (v_StatsRecord.major, v_StatsRecord.total_credits,
v_StatsRecord.total_students);
END IF;
END LOOP;
END UpdateMajorStats;

COMPONENTES DE UN TRIGGER

Los componentes requeridos en un disparador son:

1. Nombre del disparador: es el conjunto de identificadores válidos que pueden emplearse como nombres de un objeto. Los disparadores existen en un espacio de nombre diferente con lo cual pueden tener el mismo nombre que una tabla, un procedimiento, aunque no puede existir dos triggers con el mismo nombre dentro de un mismo esquema.

2. Tipos de disparadores: implica cuando se va a ejecutar este disparador, podemos tener doce tipos posibles de trigger: 3 órdenes (insert, delete o update), dos opciones de temporización (before o update) y 2 niveles (a nivel de fila -se caracterizan porque llevan la cláusula FOR EACH ROW- o a nivel de orden).

3. Cuerpo del disparador: es un bloque PL/SQL, Pero tiene una serie de restricciones, ya que no podemos emitir ninguna orden de control de transacciones (commit, rollback o savepoint).

UTILIZACIÓN DE :OLD Y :NEW EN LOS DISPARADORES CON NIVEL DE FILA

Un disparador con nivel de fila se ejecuta una vez por cada fila procesada por la orden que provoca el disparo. Dentro del disparador podemos acceder a la fila que se está procesando actualmente mediante el uso de dos seudo-registros que son :old y :new.

La utilización del seudo-registro :new lo que hace es llamar al registro modificado para poder actual con él, mientras que el :old lo que hace es coger los valores antiguos del registro.

En los disparadores con nivel de fila podemos utilizar la cláusula WHEN, que ejecutará las órdenes correspondientes sólo en el caso en que se cumpla una condición. Por ejemplo:

CREATE OR REPLACE TRIGGER CheckCredits

BEFORE INSERT OR UPDATE OF Current_credits ON students

FOR EACH ROW

WHEN (new.current_credits>20)

BEGIN

......

END;

TABLAS MUTANTES

Hay ciertas restricciones sobre las tablas y columnas a las que puede acceder el cuerpo de un disparador. Para poder definir estas restricciones es necesario entender los conceptos de tablas mutantes y de tabla de restricción.

Una tabla mutante es una tabla que está modificándose actualmente por una sentencia DML. Para un disparador es la tabla a la que éste está asignado. Una tabla de restricción es una tabla de la que puede ser necesario leer para una restricción de integridad referencial. La tabla registered_students, que habíamos creado previamente, tenía dos restricciones de integridad referencial, si creamos un trigger sobre esta tabla tenemos que tener en cuenta que las órdenes SQL en el cuerpo del disparador no pueden leer o modificar ninguna tabla mutante de la orden que provoca el disparo, ni leer ni modificar las columnas de clave primaria, única o externa de una tabla de restricción de la tabla del disparador.

Podríamos modificar aquellas columnas que no formaran parte de claves primarias o externas, como en el ejemplo siguiente:

CREATE OR REPLACE TRIGGER CascadeRSInserts
/* Keep the registered_students, students, and classes
tables in synch. */
BEFORE INSERT ON registered_students
FOR EACH ROW
DECLARE
v_Credits classes.num_credits%TYPE;
BEGIN
-- Determine the number of credits for this class.
SELECT num_credits
INTO v_Credits
FROM classes
WHERE department = :new.department
AND course = :new.course;

-- Modify the current credits for this student.
UPDATE students
SET current_credits = current_credits + v_Credits
WHERE ID = :new.student_id;

-- Add one to the number of students in the class.
UPDATE classes
SET current_students = current_students + 1
WHERE department = :new.department
AND course = :new.course;
END CascadeRSInserts;
/

Veamos ahora un ejemplo útil de trigger de tabla mutante, supongamos que queremos como máximo 5 alumnos por clase:

CREATE OR REPLACE TRIGGER LimitMajors
/* Limits the number of students in each major to 5.
If this limit is exceeded, an error is raised through
raise_application_error. */
BEFORE INSERT OR UPDATE OF major ON students
FOR EACH ROW
DECLARE
v_MaxStudents CONSTANT NUMBER := 5;
v_CurrentStudents NUMBER;
BEGIN
-- Determine the current number of students in this
-- major.
SELECT COUNT(*)
INTO v_CurrentStudents
FROM students
WHERE major = :new.major;

-- If there isn't room, raise an error.
IF v_CurrentStudents + 1 > v_MaxStudents THEN
RAISE_APPLICATION_ERROR(-20000,
'Too many students in major ' || :new.major);
END IF;
END LimitMajors;
/

TRATAMIENTO DE ERRORES

Cualquier programa bien escrito debe ser capaz de tratar los errores de manera inteligente y de recuperarse de ellos. Los errores en PL/SQL los manejaremos a través de lo que se denominan excepciones que nos permitirán reconstruir el programa en el caso en que se produzca un error. Los errores los podemos clasificar en:

Errores de compilación: los detecta PL/SQL cuando iniciamos la compilación del código.

De ejecución: se detecta cuando estemos ejecutando el código, lo solucionaremos a través del manejo de excepciones.

Las excepciones se declaran en la sección declarativa de un bloque, se generan en la sección ejecutable y se tratan en la sección de excepciones. Existen dos tipos de excepciones:

Definidas por el usuario:

Es un error cuya definición se realiza en el programa; el error no tiene porque ser necesariamente un error de Oracle, podría ser un error relativo a los datos.

Se declaran en la sección declarativa del bloque PL/SQL y se les tiene que especificar el tipo:

DECLARE

e_DemasiadosEstudiantes EXCEPTION;

Excepciones predefinidad

Tenemos diferentes excepciones provocadas por errores de Oracle, estas excepciones pueden ser:

ORA-0001	DUP_VAL_ON_INDEX	Violación de una restricción de unicidad
ORA-0051	TIMEOUT_ON_RESOURCE	Se produjo el fin de un intervalo mientras se esperaba un cierto recurso
ORA-1001	INVALID_CURSOR	Operación ilegal con un cursor
ORA-1012	NOT_LOGGED_ON	No existe conexión con Oracle
ORA-1017	LOGIN_DENIED	Nombre de usuario o contraseña inválidos
ORA-1403	NO_DATA_FOUND	No se ha encontrado ningún dato
ORA-1422	TOO_MANY_ROWS	Hay más de una fila que corresponde a la orden SELECT...INTO
ORA-1476	ZERO_DIVIDE
ORA-1722	INVALID_NUMBER
ORA-6500	STORAGE_ERROR	SQL se queda sin memoria
ORA-6501	PROGRAM_ERROR	Error interno de PL/SLQ
ORA-6502	VALUE_ERROR	Error de conversión
ORA-6511	CURSOR_ALREADY_OPEN	Se ha intentado abrir un cursor que ya estaba abierto.

Existen muchos más errores definidos por Oracle que nosotros podremos gestionar.

GENERACIÓN DE EXCEPCIONES

Cuando se produce el error generado por una excepción esta excepción se crea. Para generarlas, tendremos que indicarle la opción RAISE,. Veremos un ejemplo de manejo de excepciones:

DECLARE
e_TooManyStudents EXCEPTION; -- Exception to indicate an error condition
v_CurrentStudents NUMBER(3); -- Current number of students registered
-- for HIS-101
v_MaxStudents NUMBER(3); -- Maximum number of students allowed for
-- HIS-101
BEGIN
/* Find the current number of registered students, and the maximum number of
students allowed. */
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE department = 'HIS' AND course = 101;
/* Check the number of students in this class. */
IF v_CurrentStudents > v_MaxStudents THEN
/* Too many students registered - raise exception. */
RAISE e_TooManyStudents;
END IF;
EXCEPTION
WHEN e_TooManyStudents THEN
/* Handler which executes when there are too many students registered
for HIS-101. We will insert a log message explaining what has happened. */
INSERT INTO log_table (info) VALUES ('History 101 has ' || v_CurrentStudents ||
'students: max allowed is ' || v_MaxStudents);
END;
/

Las excepciones predefinidas se generan automáticamente cuando tiene lugar el error asociado a esa excepción.

Cuando se genera una excepción el control pasa a la sección de excepciones del bloque, que está compuesta por gestores que contienen el código que se ejecutará cuando ocurra el error asociado con la excepción y ésta sea generada. Cada gestor de excepciones está compuesto por la cláusula WHEN y las órdenes que se ejecutarán cuando la excepción sea generada. La cláusula WHEN identifica la excepción correspondiente a cada gestor. Un mismo gestor puede utilizarse para más de una excepción enumerando sus nombres usando el operador OR: Así por ejemplo tendremos:

EXCEPTION

WHEN NOT_DATA_FOUNDOR TOO_MANY:ROWS THEN

INSERT INTO log_table (info) VALUES ('Ha ocurrido un error en la SELECT');

END;

Tenemos un gestor de excepciones especial que es el gestor OTHERS que se ejecutará para todas las excepciones generadas. Siempre tiene que ser el último gestor del bloque. Este gestor como tal, sólo determina que ha ocurrido un error, pero no determina que error ha ocurrido, para determinar que error ha ocurrido usaremos las funciones predefinidas SQLCODE y SQLERRM, que nos devolverán el número y el texto del error que se ha generado. Veamos unos ejemplos:

DECLARE
e_TooManyStudents EXCEPTION; -- Exception to indicate an error condition
v_CurrentStudents NUMBER(3); -- Current number of students registered
-- for HIS-101
v_MaxStudents NUMBER(3); -- Maximum number of students allowed for
-- HIS-101
BEGIN
/* Find the current number of registered students, and the maximum number of
students allowed. */
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE department = 'HIS' AND course = 101;
/* Check the number of students in this class. */
IF v_CurrentStudents > v_MaxStudents THEN
/* Too many students registered - raise exception. */
RAISE e_TooManyStudents;
END IF;
EXCEPTION
WHEN e_TooManyStudents THEN
/* Handler which executes when there are too many students registered
for HIS-101. We will insert a log message explaining what has happened. */
INSERT INTO log_table (info) VALUES ('History 101 has ' || v_CurrentStudents ||
'students: max allowed is ' || v_MaxStudents);
WHEN OTHERS THEN
/* Handler which executes for all other errors. */
INSERT INTO log_table (info) VALUES ('Another error occurred');
END;
/

Aquí tenemos el manejo del gestor OTHERS sin especificar ni el número ni el texto del mensaje de error que se ha generado, si queremos especificarlo, podremos usar la sentencia:

DECLARE
e_TooManyStudents EXCEPTION; -- Exception to indicate an error condition
v_CurrentStudents NUMBER(3); -- Current number of students registered
-- for HIS-101
v_MaxStudents NUMBER(3); -- Maximum number of students allowed for
-- HIS-101

v_ErrorCode NUMBER; -- Variable to hold the error message code
v_ErrorText VARCHAR2(200); -- Variable to hold the error message text

BEGIN
/* Find the current number of registered students, and the maximum number of
students allowed. */
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE department = 'HIS' AND course = 101;
/* Check the number of students in this class. */
IF v_CurrentStudents > v_MaxStudents THEN
/* Too many students registered - raise exception. */
RAISE e_TooManyStudents;
END IF;
EXCEPTION
WHEN e_TooManyStudents THEN
/* Handler which executes when there are too many students registered
for HIS-101. We will insert a log message explaining what has happened. */
INSERT INTO log_table (info) VALUES ('History 101 has ' || v_CurrentStudents ||
'students: max allowed is ' || v_MaxStudents);
WHEN OTHERS THEN
/* Handler which executes for all other errors. */
v_ErrorCode := SQLCODE;
v_ErrorText := SUBSTR(SQLERRM, 1, 200); -- Note the use of SUBSTR here.
INSERT INTO log_table (code, message, info) VALUES
(v_ErrorCode, v_ErrorText, 'Oracle error occurred');
END;
/

En este ejemplo hemos trabajado con el SQLCODE, podemos ver como usaríamos el SQLERRM en el siguiente bloque:

DECLARE
v_ErrorText log_table.message%TYPE; -- Variable to hold error message text
BEGIN
/* SQLERRM(0) */
v_ErrorText := SUBSTR(SQLERRM(0), 1, 200);
INSERT INTO log_table (code, message, info)
VALUES (0, v_ErrorText, 'SQLERRM(0)');

/* SQLERRM(100) */
v_ErrorText := SUBSTR(SQLERRM(100), 1, 200);
INSERT INTO log_table (code, message, info)
VALUES (100, v_ErrorText, 'SQLERRM(100)');

/* SQLERRM(10) */
v_ErrorText := SUBSTR(SQLERRM(10), 1, 200);
INSERT INTO log_table (code, message, info)
VALUES (10, v_ErrorText, 'SQLERRM(10)');

/* SQLERRM with no argument */
v_ErrorText := SUBSTR(SQLERRM, 1, 200);
INSERT INTO log_table (code, message, info)
VALUES (NULL, v_ErrorText, 'SQLERRM with no argument');

/* SQLERRM(-1) */
v_ErrorText := SUBSTR(SQLERRM(-1), 1, 200);
INSERT INTO log_table (code, message, info)
VALUES (-1, v_ErrorText, 'SQLERRM(-1)');

/* SQLERRM(-54) */
v_ErrorText := SUBSTR(SQLERRM(-54), 1, 200);
INSERT INTO log_table (code, message, info)
VALUES (-54, v_ErrorText, 'SQLERRM(-54)');

END;
/

La longitud máxima de un mensaje de error de Oracle es de 512 caracteres.

También podemos asociar una excepción nominada con un error de Oracle determinado lo que nos permitirá interceptar de forma específica dicho error. Para hacerlo usaremos el pragma EXCEPTION_INIT, que se emplea de la siguiente forma:

PRAGMA EXCEPTION_INIT (nombre_excepción, número_error_oracle);

Por ejemplo si queremos asignar al hecho de insertar una columna con valores NULL donde no se permiten estos valores podremos generar el código como sigue:

DECLARE
e_MissingNull EXCEPTION;
PRAGMA EXCEPTION_INIT(e_MissingNull, -1400);
BEGIN
INSERT INTO students (id) VALUES (NULL);
EXCEPTION
WHEN e_MissingNull then
INSERT INTO log_table (info) VALUES ('ORA-1400 occurred');
END;
/

Podemos utilizar también la función predefinida RAISE_APPLICATION_ERROR, para crear nuestros propios mensajes de error que pueden ser más descriptivos que los mensajes generados por las excepciones. Veamos un ejemplo de su funcionamiento:

CREATE OR REPLACE PROCEDURE Register (
/* Registers the student identified by the p_StudentID parameter in the class
identified by the p_Department and p_Course parameters. Before calling
ClassPackage.AddStudent, which actually adds the student to the class, this
procedure verifies that there is room in the class, and that the class
exists. */
p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) AS

v_CurrentStudents NUMBER; -- Current number of students in the class
v_MaxStudents NUMBER; -- Maximum number of students in the class

BEGIN
/* Determine the current number of students registered, and the maximum
number of students allowed to register. */
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE course = p_Course
AND department = p_Department;

/* Make sure there is enough room for this additional student. */
IF v_CurrentStudents + 1 > v_MaxStudents THEN
RAISE_APPLICATION_ERROR(-20000, 'Can''t add more students to ' ||
p_Department || ' ' || p_Course);
END IF;

/* Add the student to the class. */
ClassPackage.AddStudent(p_StudentID, p_Department, p_Course);

EXCEPTION
WHEN NO_DATA_FOUND THEN
/* Class information passed to this procedure doesn't exist. Raise an error
to let the calling program know of this. */
RAISE_APPLICATION_ERROR(-20001, p_Department || ' ' || p_Course ||
' doesn''t exist!');
END Register;
/

En este procedimiento comprobamos si existe suficiente espacio en un curso antes de permitir la inscripción de los alumnos al mismo. Si no hay espacio suficiente levantamos el error ORA-20000, donde indicamos que no hay dicho espacio.

PROPAGACIÓN DE EXCEPCIONES

Las excepciones pueden producirse en la sección declarativa, ejecutable o de excepciones de un bloque PL/SQL. Entonces, ¿cómo controlamos las excepciones que se producen fuera del bloque?. El proceso que gobierna estas situaciones se conoce como propagación de excepciones.

Excepciones generadas en la sección ejecutable.

Cuando se genera una excepción en la sección ejecutable de un bloque PL/SQL utiliza el siguiente proceso:

1. Si el bloque actual tiene un gestor para la excepción, lo ejecuta y termina el bloque con éxito, el control pasa entonces al bloque de nivel superior.

2. Si no hay gestor para la excepción actual propaga la excepción generándola en el bloque del nivel superior.

Ámbito de las excepciones

El ámbito de las excepciones es igual que el de las variables, si una excepción se propaga fuera de su ámbito, no podrá ser referenciada por su nombre. En general si es necesario propagar un error definido por el usuario fuera de un bloque, es mejor definir la excepción en un paquete para que continúe siendo visible fuera del bloque.

OBJETOS

Los objetos son una de las principales características de Oracle 8 y PL/SQL. Oracle presenta un tipo de programación orientada a objetos. Así, por ejemplo, en una escuela tendremos diferentes objetos: alumnos, secretarias, profesores,... Cada uno de estos objetos tiene atributos y entre los objetos existen relaciones.

Los objetos se crean con tipos de objetos que describen los atributos y los métodos asociados con una clase de objeto en particular. Los objetos tienen que estar instalados y para saber si están instalados cuando entremos a SQL tendremos que ver lo siguiente:

Oracle8 Enterprise Edition....

With the Partitioning and Objects Option

...

Un tipo de objeto es similar a un paquete con una especificación y un cuerpo. La especificación del tipo contiene los atributos y las declaraciones formales para los métodos, el cuerpo contiene el código real de los métodos. Así. por ejemplo podemos crear un objeto para el tipo Estudiantes de la siguiente manera:

CREATE OR REPLACE TYPE EstudianteObj AS OBJECT (

ID NUMBER(5),

First_name VARCHAR2(20),

Last_Name VARCHAR2(30),

Major VARCHAR2(30),

current_credits number(3));

Al igual que cualquier otra variable PL/SQL un objeto se declara simplemente incluyéndolo en la sección declarativa del bloque. Para inicializar un objeto se requiere un constructor que es una función que devuelve un objeto inicializado y toma como argumentos los valores de los atributos del objeto.

DECLARE
-- Creates the object instance, with the attributes set.
v_Student StudentObj :=
EstudianteObj(10020, 'Chuck', 'Choltry', NULL, 0);
BEGIN
-- Modifies the major attribute to 'Music'. Note the use of dot
-- notation to refer to the attribute.
v_Student.major := 'Music';
END;
/

Los métodos se declaran en la especificación del tipo de objeto, detrás de los atributos y se implementan en el cuerpo del tipo. Para declararlo, es necesario usar una especificación muy similar a la estándar excepto que va precedido por la palabra MEMBER:

CREATE OR REPLACE TYPE StudentObj AS OBJECT (
ID NUMBER(5),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
major VARCHAR2(30),
current_credits NUMBER(3),

-- Returns the first and last names, separated by a space.
MEMBER FUNCTION FormattedName
RETURN VARCHAR2,
PRAGMA RESTRICT_REFERENCES(FormattedName, RNDS, WNDS, RNPS, WNPS),

-- Updates the major to the specified value in p_NewMajor.
MEMBER PROCEDURE ChangeMajor(p_NewMajor IN VARCHAR2),
PRAGMA RESTRICT_REFERENCES(ChangeMajor, RNDS, WNDS, RNPS, WNPS),

-- Updates the current_credits by adding the number of
-- credits in p_CompletedClass to the current value.
MEMBER PROCEDURE UpdateCredits(p_CompletedClass IN ClassObj),
PRAGMA RESTRICT_REFERENCES(UpdateCredits, RNDS, WNDS, RNPS, WNPS),

-- ORDER function used to sort students.
ORDER MEMBER FUNCTION CompareStudent(p_Student IN StudentObj)
RETURN NUMBER
);
/

Tenemos que aclarar unas cosas respecto a los objetos:

Todos los métodos tienen que ir precedidos de la palabra clave MEMBER en la declaración formal
En lugar de un punto y coma al final de cada declaración (o pragma) se incluye una coma.
Las declaraciones de métodos deben hacerse después de las declaraciones de atributos.
El pragma RESTRIC_REFERENCES puede usarse para permitir que se llame a un método desde una orden SQL.

Los tipos de objetos son muy similares a los paquetes en muchos detalles:

Ambos favorecen la abstracción de datos, separando la definición y el cuerpo del objeto en diferentes diccionarios de datos.
Ambos crean objetos en el diccionario de datos.

Sin embargo existen diferencias significativas:

Un cuerpo de paquete puede incluir declaraciones adicionales que no estén en la especificación.
Los tipos de objetos son tipos PL/SQL.
Los paquetes pueden contener una sección de inicialización, los objetos no.
Un cuerpo de un tipo de objeto sólo puede contener la implementación de los métodos indicados en la especificación.

Para llamar a un método tendremos que usar la sintaxis nombre_objeto.nombre_método