10.2. IDENTIFICANDO Y ADMINISTRANDO OBJETOS PL/SQL.

IDENTIFICANDO Y ADMINISTRANDO OBJETOS PL/SQL.

PL/SQL es un lenguaje de programación desarrollado específicamente para la Base de Datos Oracle.  Extiende el lenguaje SQL para proporcionar estructuras procedimentales y características para generar interfaces de usuario. Teóricamente un administrador de base de datos puede no necesitar ser un experto en PL/SQL – el trabajo de programación será hecho por los desarrolladores. Pero en la práctica, el dba conoce más PL/SQL. Por otra parte, en la mayoría de las instalaciones, el dba se espera que ayude a los programadores a escribir código cuando sea necesario crear objetos PL/SQL.

SQL y Lenguajes procedimentales.

La base de datos oracle, como todas las bases de datos relacionales compatibles con ISO, soporta el uso de SQL, SQl. Es un lenguaje diseñado para recuperar y manipular datos en entornos cliente-servidor.  Es muy eficiente en esto, pero hay muchas ocasiones cuando programadores desean manipular filas de uno en uno, en lugar de grupos. SQL por si solo no puede manejar esto. Además, SQL no tiene capacidad para diseño de interfaces de usuario. Por el contrario, lenguajes procedimentales pueden manipular filas individuales. Ellos tienen comandos que permiten navegación de uno en uno, y pueden incluir control del flujo.

Para combinar las ventahas de SQL con estructuras de control de un lenguaje procedimental, programadores necesitan utilizar un lenguaje con ambos elementos. El enfoque universal aceptada es la de insertar comandos SQL en código procedimental. Hay dos métodos para hacer esto. El cliente-servidor es ejecutar el código del procedimiento en una maquina cliente (cualquier terminal cliente o aplicación servidor) y enviar los comandos sql generados al servidor de bases de datos ene ejecución. Una alternativa es ejecutar el código procedimental, así como el SQL. Dentro de la base de datos. De alguna manera, el segundo enfoque es más eficiente: no hay sobrecarga de la red, y todo el código se almacena y gestiona centralmente. Pero esto significa que el lenguaje es propiedad de: ejecutara dentro de la base de datos que está diseñado para esto y en ninguna otra. Un segundo problema es que toda la sobrecarga es concentrada en la base de datos.

PL/SQL es ·GL propiedad de Oracle que se ejecuta dentro de la Base de Datos. Usted puede utilizarlo para recuperar y manipular datos con SQL, mientras utiliza construcciones procedimentales tales como IF THEN ELSE o FOR o WHILE. El código PL/SQL puede ser almacenado en una maquina cliente y enviado al servidor para su ejecución, o puede ser almacenado dentro de la base de datos  como un block de código.

Desde la versión 8i de la Base de Datos Oracle, es también posible utilizar java dentro de la Base de Datos. Como PL/SQL, java puede ser utilizado para una mezcla de código procedimental con declaraciones SQL. Dado que java puede ejecutarse en su máquina cliente (típicamente como un servidor de aplicaciones) o dentro de la base de datos, te da la opción de distribuir la carga de procesamiento –a costa de mayor tráfico en la red. Y a diferencia de PL/SQL, es un estándar de la industria no propietario: si la aplicación está escrita en Java, debe ser portable a cualquier  base de datos compatible. Pero java es mucho menor nivel de lenguaje, con un ciclo de desarrollo de mucho más tiempo.

La elección de lenguaje depende de muchos factores, pero PL/SQL  debe considerarse en el entorno Oracle. Es un lenguaje rápido y fácil para trabajar, todo DBA debería estar familiarizado, aun que sea para ayudar  a los programadores.

Examen.

PL/SQL siempre se ejecuta dentro de la Base de Datos donde está almacenado. Java puede ejecutarse dentro de la Base de Datos o sobre una maquina de usuario.

PL/SQL Almacenado o Anonimo.

PL/SQL se ejecuta dentro de la Base de Datos, pero se puede almacenar en el cliente o en el servidor. El código PL/SQL puede también ser ingresado interactivamente desde un prompt de SQL*PLUS. PL/SQL almacenado es cargado en la Base de Datos y almacenado dentro del diccionario de datos como un objeto llamado PL/SQL. Cuando está guardado en la Base de Datos. Este compilado: el proceso de compilación comprueba errores sintácticos y también recoge los errores relacionado a los objetos de datos  las direcciones de código. Esto ahorra tiempo cuando el código se ejecuta realmente, y significa que programadores deben recoger los errores en tiempo de compilación, antes que los usuarios lo utilicen. Código almacenado remotamente o ad hoc código emitido en el prompt de SQL*PLUS, es PL/SQL anónimo, es compilado dinámicamente; este impacta sobre el rendimiento, y también platea la posibilidad  que  errores inesperados ocurran.

La figura 10-2 muestra un ejemplo de la ejecución de un Block de PL/SQL anónimo y de la creación y ejecución de un procedimiento almacenado.

El block anónimo en la figura 10-2 crea una variable llamada INCREASE con la declaración DECLARE y la establece a 10.  Entonces el código procedimental (dentro de las declaraciones BEGIN…END) utiliza la variable dentro de una declaración SQL que actualiza una columna de una tabla.

El segundo ejemplo en la figura crea un procedimiento llamado INC_SAL, almacenado dentro del diccionario de datos. Tomara un argumento numérico llamado INCREASE y utilizara este en una declaración SQL UPDATE.  Entonces el procedimiento es invocado con el comando EXECUTE, pasando un valor para el argumento.

Este ejemplo es muy simple, pero debería ilustrar como PL/SQL anónimo  se ejecuta una vez y por lo tanto debe ser compilado en tiempo de ejecución,  mientras PL/SQL almacenado puede ser compilado por adelantado y luego se ejecuta muchas veces.

En el trabajo.

PL/SQL anónimo es menos eficiente que PL/SQL almacenado y también causa problemas con las gestión de código fuente, como el código puede ser distribuido a través de muchas maquinas.

OBJETOS PL/SQL

Existen seis tipos de objetos PL/SQL.

·         Procedimientos – Procedure.

·         Funciones – Function.

·         Paquetes – Package.

·         Cuerpo de paquetes – Package Body.

·         Disparadores – Trigger.

·         Cuerpos – Type Body.

Todos estos son objetos de esquema almacenados dentro del Diccionario de Datos. Procedimientos y funciones hacen cosas. Paquetes son colecciones de procedimientos y funciones, agrupados para la gestión. Para crear estos objetos PL/SQL usted puede utilizar Enterprise Manager Database Control, SQL*PLUS, SQL Developer o productos de terceros.  Triggger son una categoría de objetos que no pueden ser empaquetados: ellos están asociados con tablas y corren cada vez que una sentencia DML es ejecutada contra la tabla. Los otros tipos de objetos no se cubren para estar certificación.

En el Trabajo.

SQL*PLUS y Database Control son solo adecuados para PL/SQL pequeños. Para trabajo real sus programadores necesitaran un IDE apropiado que asista con comprobación de sintaxis, debugging y gestión de código fuente.

Procedimiento y Funciones

Un Procedimiento es un block de código que ejecuta alguna acción. Puede opcionalmente ser definido con un número de argumentos. Estos argumentos son remplazados con los parámetros actuales dado cuando el procedimiento es invocado. Los argumentos puede ser IN, significa que son utilizados para pasar datos al procedimiento, o argumentos OUT significa que son modificados por el procedimiento y después de la ejecución los nuevos valores son pasados fuera del procedimiento. Los argumentos pueden ser IN-OUT, donde hace las dos cosas antes mencionadas (where the one variable serves both purposes-donde la variable un tanto a fines de). Dentro de un procedimiento, usted puede definir cualquier número de variables que, a diferencia de los argumentos, son privados para el procedimiento. Para ejecutar un procedimiento, llamar desde dentro de un block de PL/SQL o utilizar el comando interactivo EXECUTE.

Una función es similar conceptualmente a un procedimiento, pero no tiene argumentos OUT y no puede ser invocado con EXECUTE. Regresa un solo valor, con la declaración RETURN.

Cualquier cosas que una función puede hacer, un procedimiento puede también. Funciones son generalmente utilizadas para operaciones relativamente simples: pequeños bloques de código que serán utilizado muchas veces. Procedimientos son más comúnmente utilizados para dividir código en modulos y pueden mucho y procesos complejos.

La figura 10-3 muestra un ejemplo de la creación e invocación de una función.

La primera línea es una instrucción para crear la función, o si ya existe para sobrescribirla. La función toma un argumento numérico y retornara un valor varchar2. Dentro el BEGIN…END es el código procedimental. Que incluye construcciones de flujo de control IF…THEN…ELSE…END…IF.

En el trabajo.

Si usted utiliza CREATE, en lugar que CREATE o REPLACE, usted tendrá que eliminar el objeto primero si ya existe.

La figura 10-4 muestra un ejemplo de creación e invocación de un procedimiento que utiliza una estructura de iteración, que llama a una función de usuario.

En el trabajo

Algunas personas se ponen molestas por la terminología. Para resumir, un argumento es la variable definida cuando usted crea una función o procedimiento;  un parámetro es el valor pasado al argumento cuando usted está ejecutando la función o procedimiento.

Paquetes

Para agrupar procedimiento y funciones relacionados en conjunto, sus programadores crean paquetes. Un paquete consiste de dos objetos: una especificación y un cuerpo. Un Package specification lista las funciones y procedimientos en el paquete, con sus llamadas especificaciones: los argumentos y sus tipos de datos. Y puede también definir variables y constantes acesible a todos los procedimientos y funciones en el paquete. El Package Body contiene el código PL/SQL que implementa el paquete: el código que crea los procedimientos y funciones.

Para crear una package spacification, utilice el comando CREATE PACKAGE, por ejemplo:

SQL> create or replace package numbers

2 as

3 function odd_even(v1 number) return varchar2;

4 procedure ins_ints(v1 in number);

5 end numbers;

6 /

Package created.

Entonces para crear el Package Body, utilice la declaración CREATE O REPLACE PACKAGE BODY para crear los procedimientos y funciones individuales.

Hay cientos de paquetes proveídos de forma estándar con la Base de Datos Oracle. Estos paquetes suministrados son, en su mayor parte, creados cuando usted crea la Base de Datos. Algunos de ellos son para el uso de los administradores de bases de datos (tales como el DBMS_WORKLOAD_REPOSITORY package, que permite gestionar el Automatic Workload Repository) otro para desarrolladores (tal como DBMS_OUTPUT package que permite escribir a una sesión de proceso usuario). Para invocar un packaged procedure, usted debe prefijar el nombre del procedimiento con el nombre del paquete:

SQL> exec numbers.odd_even(5);

Esto ejecutara el procedimiento ODD_EVEN en el paquete NUMBERS. El paquete debe existir en el esquema  al que el usuario es conectado. O sería necesario anteponer el nombre del paquete con el nombre del esquema. El usuario también tendría que tener el privilegio EXECUTE sobre el paquete.

Database Triggers

Database Triggers son una categoría especial de objetos PL/SQL, ellos no pueden ser invocados manualmente. Un trigger se ejecuta automáticamente, cuando una acción particular es llevada acado  o una situación; este es el triggering event. Existe un número de posibles triggering events, para muchos de ellos los trigger pueden ser configurados para dispararse ya sea antes o después del eventos. Es también posible tener tanto antes como después  de la definición de triggers para el mismo evento. El DML trigger, que se dispara cunado una fila es insertada, actualizada o eliminada puede ser configurado para dispararse una vez para cada fila afectada, o una vez por la ejecución de la declaración.

Todos los trigger tienen un factor en común: su ejecución es completamente fuera del control del usuario que ha causado el triggering event, no puede incluso conocer que el trigger se disparo. Esto hace a trigger una herramienta para auditar acciones e implementar seguridad,

Tenga en cuenta que ho hay tal cosa como un trigger sobre SELECT, aunque en el capítulo 14 vera como la auditoria de grano fino puede ser utilizada para producir un efecto similar. Existen números usos para los Triggers. Estos pueden incluir:

· Auditando acciones de usuario, un trigger puede capturar todos los detalles de lo que se hizo y quien lo hizo y los escribe a una tabla de auditoría.

· Ejecutar ediciones complejas, una acción en una fila puede en términos de negocio, requiere un número  de acciones asociadas sobre otras tabas. El trigger puede desarrollar estas automáticamente.

· Seguridad, un trigger puede comprobar el tiempo, la dirección ip del usuario, el programa en ejecución y cualquier otro factor que debe limitar que la sesión pueda hacer.

· Aplicación de restricciones complejas, una acción puede estar bien en términos de restricciones sobre una tabla pero puede tener que ser validado contra el contenido de varias tablas.

Considere un sistema HR. Antes que un empleado es eliminado de la tabla de empleados. Es necesario transferir todo su detalle  de un  número de tablas a tablas de archivado, esto puede hacerse mendiante un trigger como sigue:

create or replace trigger archive_emp

before delete on current_emps

for each row

begin

archive_emp(:old.employee_id);

end;

cada vez que una sesión elimine una fila de la tabla CURRENT_EMPS, antes que cada fila se elimine el procedimiento ARCHIVE_EMP será ejecutado. Este procedimiento hara lo que sea necesario para archivar los datos de un empleado. Esto ilustra un punto importante: en general se considera una buena práctica para mantener activa pequeña, y para hacer la mayor parte del trabajo con un procedimiento almacenado.

Examen.

Es imposible ejecutar un trigger por cualquier otro medio que un triggering event.

EJERCICIO 10-2

Crear objetos PL/SQL.

En este ejercicio utilizara el Database Control para crear objetos PL/SQL y ejecutarlos con SQL*PLUS.

1.Conectase a su base de datos como usuario SYSTEM con SQL*PLUS.

2.Crear una tabla para utilizar en este ejercicio.
create table integers(c1 number, c2 varchar2(5));

3.Conectarse a su Base de Datos como usuario SYSTEM con el Database Control.

4.Desde la pagina principal de la Base de datos, tome la pestaña Schema luego el link Packages en la seccion de Programs. Click Create.

5.En la ventana Create Package, ingrese NUMBERS como el nombre del paquete, y el código fuente para el paquete como se muestra en la siguiente ilustración. Clic en OK para crear el paquete.

6.Desde la pagina principal de la base de datos, tome la pestaña Schema y luego Package Bodies en la sección de programas. Click Create.

7.En la ventana Create Package Body, ingresar NUMBERS como el nombre del paquete, y el código fuente para el package body como en la siguiente ilustración. Clic OK para crear el Package Body.

8.       En su sesin de SQL*PLUS, describa el paquete, ejecute el procedimiento y compruebe resultados, como en la ilustración:

9.       Poner en orden, eliminando el paquete (la tabla será utilizada en el siguiente ejercicio):

drop package numbers;

Tenga en cuenta este DROP commit los insert de las filas.