INTRODUCCIÓN A PL/SQL
Es un lenguaje de programación para bases de datos que se caracteriza porque rompe los objetos básicos que contienen PL/SQL en bloques, cada uno de los cuales se procesan por separado. Podemos dividir el PL/SQL diciendo que consta de tres tipos de estamentos:
ELEMENTOS ESTRUCTURALES DE LOS BLOQUES DE PL/SQL
Cada estamento de los bloques PL/SQL debe terminarse con un punto y coma, y pueden ser de una línea o de más de una línea. Un estamento de PL/SQL puede tener hasta tres secciones: declaración, ejecución y excepción.
Declaración: empieza siempre con la palabra clave DECLARE y aquí declararemos todas las variables que vamos a utilizar para el desarrollo del código.
Ejecución: contiene los estamentos que realizará el bloque de PL/SQL. Empieza por la palabra BEGIN.
Excepción: protege a la aplicación de eventos o errores inesperados, aquí controlaremos que ocurre si se dispara un error.
Son sentencias condicionales que ejecutarán un código u otro dependiendo de si se cumple o no la condición. Su estructura básica es la siguiente:
| IF condicion THEN
estamento PL/SLQ; ELSE estamento PL/SLQ; END IF; |
Pero podemos tener más de una sentencia IF de forma anidada, quedaría de la siguiente manera:
| IF condicion THEN
estamento PL/SLQ; IF condicion THEN estamento PL/SLQ; END IF; ELSE estamento PL/SLQ; END IF; |
Los bucles ejecutan código de manera continua hasta que se finaliza el bucle o bien cuando a nosotros nos interesa salir del mismo. Tenemos tres tipos de bucle:
LOOP: su sintaxis general es:
| LOOP
estamento PL/SQL: EXIT WHEN condición; END LOOP; |
BUCLES WHILE: su sintaxis es:
| WHILE condición LOOP
estamento PL/SQL; END LOOP; |
BUCLES FOR: se usa cuando el estamento se repetirá un número determinado de veces, su sintaxis es:
| FOR contador IN a..b LOOP
estamento PL/SQL; END LOOP; |
Una variable no es más que una porción reservada de memoria que se encarga de almacenar un valor para su uso posterior. Una parte muy importante de las variables es el tipo de datos que generan estas variables. Podemos agrupar estos tipos de datos en tres grandes grupos:
Las variables se declaran en la sección de declaración de un bloque PL/SQL. Cada variable tiene que tener un tipo de datos asignado durante su creación. Además tenemos que asignarle un valor a la variable, y si no le indicamos un valor la variable tendrá por defecto el valor NULL.
Por ejemplo, tendremos:
| DECLARE
vn_movie_id PLS_INTEGER; vn_movie_descr VARCHAR2(240); vc_rating VARCHAR2(10):='PG-13'; vn_revenue NUMBER(8, 2) NOT NULL:=0.0; vb_discount BOOLEAN DEFAULT TRUE; BEGIN ... END; |
Para asignar los valores iniciales a una variable podemos usar el operador :=, o bien podemos utilizar la palabra clave DEFAULT. Aunque también podemos especificar el tipo de datos de una variable mediante el atributo %TYPE que referenciará esta variable a otra variable que ya esté asignada. Por ejemplo, la variable descuento, tendrá el mismo tipo de datos que la variable Precio.
| DECLARE
precio NUMBER; descuento precio%TYPE; BEGIN ..... END; |
CONSTANTES DE PL/SQL
Los valores de las constantes sólo los podemos asignar durante la fase de inicialización de los bloque PL/SQL. Cuando se ejecuta el bloque estas constantes mantienen su valor y no se puede modificar. Para declarar una constante tendremos que usar la palabra clave CONSTANT.
| DECLARE
va_tax CONSTANT NUMBER:=0.045; vn_subtotal NUMBER(8, 2):=100.00; vn_taxed_subtotal NUMBER(8, 2); BEGIN vn_taxed_subtotal:=vn_subtotal(1+va_tax); END; |
RECORDS DE PL/SQL
Se utilizan para crear tipos de datos del tipo RECORD, permite combinar un número de campos de diferentes tipos de datos. Para utilizarlos tendremos que crear primero un tipo de datos definido por el usuario que sea del tipo RECORD y una vez lo hemos hecho tendremos que pasarle los campos que van a formar parte.
Así, por ejemplo tendríamos:
| DECLARE
TYPE LocationType IS RECORD ( region VARCHAR2(80), area VARCHAR2(80), name VARCHAR2(80)); TYPE SalesDataType IS RECORD ( rental_total RENTAL.DAILY_RATE%TYPE :=0.0, category MOVIE.RATING%TYPE, location LocationType); city LocationType; daily_sales SalesDataType; daily_total RENTAL.DAILY_RATE%TYPE; BEGIN ..... END; |
En este ejemplo tenemos que el primer grupo de datos se utilizan para describir un localización geográfica incluyendo región, área y nombre de esta localización. El segundo bloque es una extensión del primero añadiendo el total de ventas generados por localización.
INDEX-BY-TABLES
El tipo de datos TABLE de PL/SQL es un tipo de datos similar a las matrices de otros lenguajes de programación. Como las matrices, los valores de este tipo de datos están diferenciados por un número, en este caso su tipo de datos es BINARY_INTEGER. Así, tendríamos un código similar a este:
| DECLARE
TYPE CustNameType IS TABLE OF VARCHAR2(80) NOT NULL INDEX BY BINARY_INTEGER; TYPE MovieTitleType IS TABLE OF MOVIE.TITLE%TYPE INDEX BY BINARY_INTEGER; customer_names CustNameType; movie_titles MovieTitleType; BEGIN ... END; |
El primer TABLE TYPE declarado, la de CustomerType guarda registros cuyas propiedades vienen heredadas de la tabla CUSTOMER. El segundo TABLE TYPE declarado LocationTableType contendrá elementos cuyo tipo de datos está definido por el record definido por el usuario LocationType.
Después de declararlo sus elementos se pueden rellenar o acceder a través de la tabla como se accede normalmente a las matrices. Por ejemplo, para almacenar el nombre Michelle Johnson, usaríamos la sentencia:
| customer_name(5) := 'Michelle Johnson'; |
Si por ejemplo tenemos que John Michael está almacenado en la posición -5, si quisiéramos asignar ese registro, tendríamos que hacer:
| current_customer := customer_names(-5); |
La estructura de la tabla no fuerza ningún tipo de cohesión entre sus índices, pudiendo contener elementos en cualquier posición. Si no encuentra el valor, disparará el error NO_DATA_FOUND. Para prevenir este error tendremos que usar el método EXISTS.
| IF customer_names.EXISTS(-5) THEN
current_customer := customer_names(-5); ELSE current_customer := NULL; END IF; |
Cuando Oracle Server recibe una sentencia SQL para ejecutarla se asegura primero que sea una sentencia SQL válida. Lo lleva a un área de memoria reservada, denominada SQL área y la comprueba.
DECLARACIÓN EXPLÍCITA DE CURSORES
La declaración explícita de cursores son objetos PL/SQL creados sobre una sentencia SELECT que permiten manipular los registros retornados por las consultas. Los cursores se declaran en la parte de DECLARE y tienen la sintaxis siguiente:
| CURSOR nombreCursor IS estamentoSelect; |
El estamento SELECT de un cursor no contiene la cláusula INTO. Así por ejemplo tendremos:
| DECLARE
CURSOR c_movie_rental_101 IS SELECT rental.customerID, rental.rent_dt, rental.return_DT FROM rental WHERE rental.movieID=101; CURSOR c_movie_rentals (movieID NUMBER) IS SELECT rental.customerID, rental.rent_dt, rental.return_dt FROM rental WHERE rental.movieID=movieID; BEGIN ... END; |
MÉTODOS Y ATRIBUTOS DE LOS CURSORES EXPLÍCITOS.
Los cursores explícitos se consideran un objeto especial de PL/SQL. Después de declarar el cursor podemos utilizar la sentencia OPEN nombreCursor para ejecutar la query contenida en el mismo y almacenar los registros en tiempo de ejecución. Si la declaración del cursor contiene parámetros, estos parámetros se tendrán que especificar en el momento en que ejecutemos el cursor:
| OPEN c_movie_rentals (movieId); |
El atributo %ISOPEN mantiene el estado del cursor. Si el cursor está abierto este atributo vale true, si no su valor será False. Si el cursor ya está abierto se disparará el error CURSOR_ALREADY_OPEN. Abriendo el cursor lo que haremos es identificar los registros que devolverá la query. Cuando se abre un cursor se sitúa un puntero en el primer registro del cursor. Cuando ejecutamos el estamento FETCH el primer registro se nos devuelve y el cursor avanza hasta el siguiente registro. Una vez usamos el estamento FETCH debemos utilizar seguidamente un estamento INTO. Para evitar errores en tiempo de ejecución estas variable tienen que tener el mismo tipo de datos y tamaño que los datos por los que nos estamos moviendo. Una manera de asegurarnos esto es declarar el tipo de datos de estas variables es definirlas con el atributo %TYPE. El número de registros por los que nos desplazamos se almacena en otra variable que se llama %ROWCOUNT. cuando abrimos el cursor este valor se resetea a 0 y después va incrementando el valor de uno en uno.
Para controlar las posibles excepciones que pueden ocurrir, tendremos que salir del bucle LOOP que se crea cuando trabajamos con cursores. Para hacerlo usaremos la sentencia:
| EXIT WHEN cursor_name%NOTFOUND; |
Pero puede ser que no exista ningún cursor. Para ello, tendremos que usar esta nueva sentencia:
| LOOP
FETCH c_movie_rentals INTO custID, rentID, returnDt; EXIT WHEN (c_movie_rentals%NOTFOUND OR c_movie_rentals%NOTFOUND IS NULL); ..... END LOOP; LOOP FETCH c_movie_rentals INTO custID, rentDT, returnDT; IF c_movie_rentals%FOUND OR c_movie_rentals%FOUND IS NULL THEN ... ELSE EXIT; END IF; END LOOP: |
Después de que un cursor esté abierto y usado, debemos cerrarlo y liberar los recursos que ocupa en la base de datos. Para ello usaremos la sentencia siguiente:
| CLOSE c_movie_rentals; |
Cuando tenemos que procesar alguno de los registros de un conjuntos de registros debemos construir el código alrededor de los estamentos descritos previamente. Para evitar todos los posibles errores que se pueden generar tendríamos que seguir los siguientes pasos:
1. Declarar el cursor en la sección de declaración.
2. Abrimos el cursor.
3. Creamos un LOOP con la condición EXIT.
4. Cerramos el cursor.
En resumen, el código quedaría así:
| DECLARE
CURSOR c_movie_rentals (movieID MOVIE.ID%TYPE) IS SELECT customerID, rentDt, returnDT FROM rental WHERE movie_Id = movieID; custId CUSTOMER.ID%TYPE; rentDT RENTAL.RENT_DT%TYPE; returnDT RENTAL.RETURN_DT%TYPE; BEGIN OPEN c_movie_rentals(121); LOOP FETCH c_movie_rentals INTO custID, rentDt, returnDT; EXIT WHEN c_movie_rentals%NOTFOUND OR c_movie_rentals%NOTFOUND IS NULL; END LOOP; CLOSE c_movie_rentals; END; |
USO DEL BUCLE FOR LOOP CON CURSORES EXPLÍCITOS
Podemos utilizar también un bucle FOR LOOP cuando estoy trabajando con cursores explícitos. Para usarlo tendríamos un código como el que sigue:
| DECLARE
CURSOR c_movie_rentals (movieId MOVIE.ID%TYPE) IS SELECT customer_Id, rent_dt, return_dt FROM rental WHERE movie_id=movieID; custID CUSTOMER.ID%TYPE; rentDt RENTAL.RENT_DT%TYPE; returnDT RENTAL.RETURN_DT%TYPE; BEGIN FOR current_rental IN c_movie_rentals(121) LOOP custID := current_rental.CUSTOMER_id; rentDt := current_rental.RENT_DT; returnDt := current_rental.RETURN_DT; END LOOP; END; |
VARIABLES CURSOR
Las variable cursor son referencias a cursores estáticos. Generalmente sigue los siguientes pasos:
El código quedaría como sigue:
| DECLARE
TYPE custCursorType IS REF CURSOR; TYPE custShortRecType IS RECORD ( id CUSTOMER.ID%TYPE, name VARCHAR2(80)); custCursor custCursorType; custShortRec custShortRecType; custFullRec CUSTOMER%ROWTYPE; BEGIN OPEN custCursor FOR SELECT id, firstName, lastName FROM CUSTOMER; FETCH custCursor INTO custShortRec; CLOSE custCursor; OPEN custCursor FOR SELECT * FROM CUSTOMER; FETCH custCursor INTO custFullRec; CLOSE custCursor; END; |
CURSORES IMPLÍCITOS
Oracle usa un cursor para estamento SELECT ... INTO que no está referenciado por un cursor explícito. También usa cursores para todos los INSERT, UPDATE o DELETE.
El código en Developer /2000 puede estar escrito en triggers y objetos de menú. Los triggers están ligados. El código escrito en estos objetos se presenta como bloques anónimos de PL/SQL.
Los procedimientos están definidos como objetos que pueden realizar ciertas acciones. Veamos un ejemplo que es una función:
| FUNCTION DaysBetween(first_dt IN DATE, second_dt IN DATE)
RETURN NUMBER IS dt_one NUMBER; dt_two NUMBER; BEGIN dt_one : = TO_NUMBRT(TO_CHAR(first_dt, 'DDD')); dt_two : = TO_NUMBER(TO_CHAR(second_dt, 'DDD')); RETURN(dt_two-dt_one); END; |
Otro bloque de programa sería:
| PROCEDURE Set_Cust_Phone (cust_id IN OUT NUMBER, cust_phone
IN OUT VARCHAR2) IS
BEGIN UPDATE customer SET phone=cust_phone WHERE id= cust_id; EXCEPTION WHEN OTHERS THEN MESSAGE('Ha ocurrido un error interno'); END; |
ARGUMENTOS EN UNIDADES DE PROGRAMA
Los argumentos definidos en el encabezado de una unidad de programa reciben el nombre de argumentos formales. Los que se pasan cuando llamamos a esta unidad de programa reciben el nombre de argumentos actuales que se pueden pasar usando la notación posicional o la notación nominal.
En la notación posicional tendríamos un código similar a:
| days := Days_Between(order_dt, delivery_dt);
Set_cust_phone(cust_id, new_phone); |
Si usamos la notación nominal tendremos:
| days:= Days_between(frist_dt => rent_dt, second_dt =>return_dt);
Set_Cust_phone(cust_phone => new_phone, cust_id =>customer_id); |
PAQUETES PL/SQL
Las funciones y procedimientos son objetos importantes de PL/SQL porque permiten agrupar los estamentos PL/SQL que son necesarios para el funcionamiento de la aplicación. Vendría a ser algo similar a lo que son las classes en JAVA. Estructuralmente un paquete está dividido en dos partes:
Los paquetes encapsulan en un objeto los elementos y las operaciones o métodos que los manipulan. Cuando declaramos los cursores en paquetes los que haremos será declarar en el body la definición del cursor y su sentencia SELECT y en la especificación pondríamos algo como:
| CURSOR c_movie_rentals(movieID MOVIE.ID%TYPE)
RETURN rental.DAILY_RATE%TYPE; /*en el cuerpo tendríamos algo así como:*/ CURSOR c_movie_rentals(movieID MOVIE.ID%TYPE) IS SELECT customer_id, rent_dt, return_dt FROM rental WHERE movie_id=movieID; |
Las excepciones son condiciones no normales del funcionamiento de un programa. Cuando ocurre un error, ese error lo tenemos que controlar y para hacerlo tendremos que trabajar con las excepciones. En Oracle pueden ocurrir dos tipos de excepciones:
* Internas: las excepciones internas están asociadas con un número de error del Servidor de Oracle, estos nombres que podemos encontrar son:
| Exceprion Name | Oracle Error | Descripción |
| NO_DATA_FOUND | ORA-01403 | Un estamento SELECT...INTO no retorna filas |
| TOO_MANY_ROWS | ORA-01427 | El estamento SELECT...INTO retorna más de una fila |
| ZERO_DIVIDE | ORA-01476 | Un valor numérico está dividido por cero. |
| VALUE_ERROR | ORA-01403 | Ha ocurrido un error cuando se estaba convirtiendo un tipo de datos |
| STORAGE_ERROR | ORA-06500 | No hay memoria suficiente. |
| PROGRAM_ERROR | ORA-06501 | Error interno de PL/SQL |
* Definidas por el usuario: son mensajes de error que genera el usuario debido a las posibles violaciones de las reglas de la aplicación. Este tipo de errores los tiene que disparar la aplicación mediante el estamento RAISE y enviando mediante un GOTO a la excepción.
Los formularios de Oracle utilizan tanto variable locales como variables globales. Las locales sólo son accesible desde dentro del bloque PL/SQL desde donde se habían generado, las globales son accesibles desde cualquier objeto, trigger, función y procedimiento en el módulo que se está ejecutando. El nombre de las variables globales siempre será GLOBAL.nombreVariable.
Tenemos que tener en cuenta una característica muy importante dentro de las variables que son el tipo de datos que las van a definir. Este tipo de datos puede venir dado por los objetos que formarán parte de los formularios como pueden ser WINDOW, ITEM y BLOQUES.
