TEMA 19: PROGRAMACI�N AVANZADA by c8Y2SV

VIEWS: 0 PAGES: 20

									TEMA 19: PROGRAMACIÓN AVANZADA

19.1 Introduccion

En los temas anteriores hemos usado PL/SQL para crear procedimientos que se
almacenan de manera aislada en la base de datos y que deben invocados explícitamente
para que se ejecuten.
En esta unidad aprenderemos a crear disparadores que se ejecutaran automáticamente al
realizar determinadas operaciones en una tabla, asi como empaquetar los
procedimientos junto con variables, tipos, cursores y otros elementos del lenguaje.
Definiremos nuestros propios tipos usando SQL dinamico para crear y modificar la
definición de tablas y usuarios y trabajaremos con objetos cuyas características se
desconocen en tiempo de compilación

19.2 Triggers de base de datos

Los triggers de base de datos son bloques PL/SQL almacenados asociados a una tabla
que se ejecutan o disparan automáticamente, cuando se producen ciertos eventos o
sucesos que afectan a la tabla, como puede ser una inserción, un borrado o una
modificación de sus filas.
Los triggers se pueden utilizar para:

      Implementar restricciones complejas de seguridad e integridad.
      Prevenir transacciones erróneas.
      Implementar reglas administrativas complejas.
      Generar automáticamente valores derivados.
      Auditar las actualizaciones incluso enviar alertas.
      Gestionar replicas remotas de tablas.

Sintaxis:

CREATE [OR REPLACE] TRIGGER nombre_trigger
{BEFORE | AFTER} {DELETE | INSERT | UPDATE} [OF <lista_columnas>]
[OR {BEFORE | AFTER} {DELETE | INSERT | UPDATE} [OF <lista_columnas>]
ON nombre_tabla
[FOR EACH {STATEMENT | ROW } [ WHEN (condicion)]

DECLARE
…………..
…………..

Ejemplo: Crear un trigger que se llame “MOFIDICAR_PAIS”, el trigger se dispara
después de cada modificación de la columna pais para la tabla clientes, su efecto sera
insertar una fila en la tabla audita_clientes con el valor antiguo, el nuevo y el numero
para ese cliente modificado.

Primero creamos la tabla:

CREATE TABLE AUDITA_CLIENTE(
NUM INTEGER,
PAIS_VIEJO CHAR,
PAIS_NUEVO CHAR
);

Creamos el trigger:

CREATE OR REPLACE TRIGGER MODIFICAR_PAIS
AFTER UPDATE OF CLT_PAIS
ON CLIENTES
FOR EACH ROW

BEGIN

      INSERT INTO AUDITA_CLIENTE VALUES
(:OLD.CLT_NUM,:OLD.CLT_PAIS,:NEW.CLT_PAIS);

END;
/

Actualiza la tabla clientes:

SQL>UPDATE CLIENTES SET CLT_PAIS=’G’ WHERE CLT_PAIS=’F’;

4 rows updated.

Y compruebo si ha insertado en la tabla “AUDITA_CLIENTE”:

SQL> SELECT * FROM AUDITA_CLIENTE;

  NUM P P
--------- - -
      3FG
      4FG
      6FG
      8FG


19.3 Elementos de un trigger

Un trigger esta compuesto por los siguientes elementos:

    a) Nombre del trigger
    b) Evento de disparo: es el suceso que producira la ejecución del trigger y sera
       siempre una orden DML: “INSERT”, “UPDATE” Y “DELETE”. En el caso del
       “UPDATE” se podran especificar opcionalmente las columnas cuya
       modificación poducira el disparo. Se puede especificar varios eventos de disparo
       para un mismo trigger utilizando las clausulas “OR”. En el evento de disparo se
       incluye la tabla a la que el trigger queda asociado mediante la clausula “ON”
       seguida del nombre de la tabla.
    c) Tipos de triggers: hace referencia a 2 cuestiones:
            a. El momento en que se ejecute que puede ser antes “BEFORE” o después
               “AFTER” de que se ejecute la orden de manipulación de datos.
            b. El nivel de disparo del trigger que puede ser a su vez:
                    i. A nivel de orden (por defecto), el trigger se activara una sola vez
                       para cada orden independientemente del numero de filas
                       afectadas por ella. Se puede incluir la clausula “FOR EACH
                       STATEMENT” aunque no es necesario ya que se asume por
                       omisión.
                   ii. A nivel de fila, el trigger se activara una vez por cada fila
                       afectada por la orden. Para ello se incluira la clausula “FOR
                       EACH ROW”.

    d) Restricción del trigger. La clausula “WHEN” seguida de una condicion
       restringe, esta condicion tiene algunas limitaciones:
           a. Solamente se puede utilizar los triggers a nivel de fila.
           b. Se trata de una condicion SQL.
           c. No puede incluir una consulta a vistas o a otras tablas.

    e) Cuerpo del trigger. Es el codigo que se ejecutara cuando se cumplan las
       condiciones especificadas en la cabecera. Se trata de un bloque SQL.

Ejemplo: crear un trigger que audite la tabla tiendas y crearemos una tabla que se llame
tiendas_borradas (tda_num,tda_ger,tda_pob).

CREATE TABLE TIENDAS_BORRADAS AS (SELECT * FROM TIENDAS);
DELETE TIENDAS_BORRADAS;

Que dara:

SQL> @PL28

Table created.


12 rows deleted.

El trigger seria:

CREATE OR REPLACE TRIGGER borra_tiendas
BEFORE DELETE
ON TIENDAS
FOR EACH ROW

BEGIN

INSERT INTO TIENDAS_BORRADAS
VALUES(:OLD.TDA_NUM,:OLD.TDA_POB,:OLD.TDA_GER);

END;
/
Borramos una fila para comprobar:

SQL> DELETE FROM TIENDAS WHERE TDA_NUM=12;

1 row deleted.

Comprobamos si ha insertado dentro la tabla que creamos antes:

SQL> SELECT * FROM TIENDAS_BORRADAS;

  TDA_NUM TDA_POB                   TDA_GER
--------- --------------- -------------------------
     12 PARIS             FOUET, Paul


********** para recuperar los datos ejecutamos la orden “ROLLBACK;” **********


19.4 Variables OLD y NEW

Se pueden hacer referencia a los valores anteriores y posteriores a una actualizacion a
nivel de fila. Normalmente sera con “:OLD.nombre_columna”y
“:NEW.nombre_columna”.
Al utilizar los valores OLD y NEW deberemos tener en cuenta los eventos de disparo:

    a) Cuando el evento que causa el disparo es “DELETE”, deberemos hacer
       referencia a “:OLD.nombre_columna” ya que el valor de “NEW” es NULL.
    b) En el “INSERT” debemos hacer referencia a “:NEW.nombre_columna” ya que
       “OLD” no existe.
    c) Para el “UPDATE” se usan los 2 (“OLD Y NEW”).

En el caso de que queramos hacer referencia a los valores “NEW” y “OLD”, al indicar
la restricción del trigger en la clausula “WHEN”, lo haremos si poner los dos puntos “:”.
Si queremos que en lugar de “OLD” y “NEW” aparezcan otras palabras, lo indicaremos
con la clausula “REFERENCING”.

Iria después del nombre de tabla:

REFERENCING NEW AS NUEVO, OLD AS VIEJO

Y despues se pondría:

:NUEVO.nombre_columa

conviene recordar que solo se puede hacer referencia a los valores “NEW” y “OLD” a
los disparadores a nivel de fila.
19.5 Tipos de disparadores y orden de ejecucion

Una misma tabla puede tener varios disparadores, el orden de disparo sera el siguiente:

   1) Antes de comenzar la orden que produce el disparo se ejecutara los disparadores
      “BEFORE... FOR EACH STATEMENT”.
   2) Para fila afectada por la orden se ejecutaran los disparadores:
         a. “BEFORE... FOR EACH ROW”.
         b. Se ejecuta la actualizacion de la fila “INSERT”, “UPDATE”y
              “DELETE”y en este momento se bloquea la fila hasta que la transacción
              se confirme.
         c. Se ejecutan los diparadores “AFTER ... EACH ROW”. Una vez
              realizada la actualizacion se ejecutaran los disparadores.
   3) Y el ultimo caso seria “AFTER... FOR EACH STATEMENT”.

Observaciones:

a) Cuando se dispara un trigger, este forma parte de la operación de manipulacion que
   lo disparo, de manera que si el trigger falla, Oracle dara por fallida la manipulación
   completa. Aunque el fallo se produzca de una sola fila, Oracle hara “ROLLBACK”
   de todas las filas manipuladas.
b) En ocasiones en lugar de asociar varios triggers a una misma tabla, podremos optar
   por la actualizacion de un solo trigger con multiples eventos de disparo.


19.6 Multiples eventos de disparo y predicados condicionales

Un mismo trigger puede ser disparado por distintas operaciones o eventos de disparo,
para indicarlo se utilizara el operador “OR”. En estos casos es probable que una parte de
las acciones del bloque PL/SQL dependa del tipo de evento que disparo el trigger.
Para facilitar este control Oracle permite la utilización de predicados condicionales que
devolveran un valor verdadero o falso para cada una de las posibles operaciones.

Tipos de predicados:

   1) “INSERTING”: devuelve TRUE, si el evento que disparo el trigger fue un
      comando “INSERT”
   2) “DELETING”: devuelve TRUE, si el evento que disparo el trigger fue un
      comando “DELETE”.
   3) “UPDATING”: devuelve TRUE, si el evento que disparo el trigger fue un
      comando “UPDATE”.
   4) “UPDATING (‘nombre_columna’)”: si el evento que disparo el trigger fue un
      comando “UPDATE” sobre una columna en concreto.


   Ejemplo: Hacer un trigger que controle “INSERT”,”DELETE” de la tabla clientes,
   si hacemos “INSERT”,”DELETE” debe decirnos que operación realizo y UPDATE
   sobre la columna clt_pais.
   CREATE OR REPLACE TRIGGER QUE_HACE
   BEFORE INSERT OR DELETE OR UPDATE
   ON CLIENTES
   FOR EACH ROW

   BEGIN

   IF INSERTING THEN

       DBMS_OUTPUT.PUT_LINE('INSERTADO');

   END IF;

   IF DELETING THEN

       DBMS_OUTPUT.PUT_LINE('BORRADO');

   END IF;

   IF UPDATING('CLT_PAIS') THEN

       DBMS_OUTPUT.PUT_LINE('ACTUALIZADO CLT_PAIS');

   END IF;

   END;
   /

Insertamos:

INSERT INTO CLIENTES (CLT_NUM) VALUES (33);
INSERTADO

1 row created.

********** recordad de poner SET SERVEROUTPUT ON ****************


19.7 Restricciones para la creación de un trigger

   a) El bloque PL/SQL no puede contener sentencias de control de transacciones
      como “COMMIT”,”ROLLBACK” o “SAVEPOINT”.
   b) No puede usar comandos de DDL.
   c) Un trigger no puede contener instrucciones que consultan o modifiquen tablas
      mutantes. Una tabla mutante es aquella que esta siendo modificada por una
      sentencia “UPDATE”,”DELETE” o “INSERT” en la misma sesion.
   d) No se puede cambiar valores de las columnas que sean claves primarias unicas o
      ajenas de tablas de restricción.
   e) Los triggers a nivel de comando “FOR EACH STATEMENT” no se veran
      afectados para las restricciones para las tablas mutantes y tablas de restricción,
        excepto cuando el trigger se dispare como resultado de una restricción “ON
        DELETE CASCADE”.


19.8 Disparadores de sustitución

Se pueden crear triggers que no se ejecuten ni antes ni después, sino en lugar de la orden
de manipulación “INSTEAD OF” que da lugar al disparo del trigger. Se le denominan
disparadores de sustitución.
Sintaxis:

CREATE OR REPLACE TRIGGER nombre
INSTEAD OF {DELETE | INSERT | UPDATE [ OF <lista_columnas>]}
[OR {DELETE | INSERT | UPDATE…..
ON nombre_lista
[FOR EACH ROW]
[DECLARE
……….

Los disparadores de sustitucion tiene estan caracteristicas diferenciales:
   1) Sólo se usan en triggers asociados de vistas, y son realmente utiles para realizar
       operaciones de actualizaciones complejas.
   2) Actua siempre a nivel de fila no a nivel de orden, por tanto a diferencia que
       ocurria a nivel de filas la opcion por omisión es “FOR EACH ROW”
   3) No se puede especificar una restricciones de disparo mediante la clausula
       “WHEN” pero se puede conseguir la funcionalidad similar usando estructuras
       alternativas del bloque PL/SQL.


Ejemplo: crear una vista que se llame CLIENTES_VENTAS, crear un trigger que se
llame BORRAR, que cuando se borre de la vista se borre las tablas

Se crea la vista:

CREATE VIEW CLIENTES_VENTA AS
SELECT
A.CLT_NUM,A.CLT_NOM,A.CLT_APELL,A.CLT_POB,A.CLT_PAIS,B.VNT_TDA,B.V
NT_ART,B.VNT_CANT,B.VNT_PRECIO
FROM CLIENTES A,VENTAS B WHERE A.CLT_NUM=B.VNT_CLT;

Se crea el trigger:

CREATE OR REPLACE TRIGGER BORRAR
INSTEAD OF DELETE
ON CLIENTES_VENTA
FOR EACH ROW

BEGIN

        DELETE FROM CLIENTES WHERE CLT_NUM = :OLD.CLT_NUM;
       DELETE FROM VENTAS WHERE VNT_CLT= :OLD.CLT_NUM;

END;
/
Borramos de la vista para comprobar:

SQL> DELETE FROM CLIENTES_VENTA WHERE VNT_CLT =5
 2 ;

2 rows deleted.

Comprobamos si ha borrado:

SQL> SELECT * FROM CLIENTES WHERE CLT_NUM=5

no rows selected


19.9 Consideraciones y opciones de utilización

Para crear un trigger hay que tener privilegios “CREATE TRIGGER” asi como los
privilegios sobre la tabla asi como otros objetos referenciados al trigger.
Con el nombre del trigger se puede especificar el esquema en el que queremos crear el
trigger usando la notación del punto.
Por omisión nuestro esquema actual es el que usamos al crear el trigger. El privilegio
“CREATE ANY TRIGGER” permite crear triggers en cualquier esquema, normalmente
un trigger se asocia a una tabla pero tambien se puede asociar a una vista. Asi mismo la
tabla o vista puede pertenecer a un esquema distinto del actual, siempre que se tengan
los privilegios correspondientes se usara la notación del punto.
No puede asociar un trigger a una tabla o esquema “SYS”.
Un trigger forma parte de la operación de actualizacion que lo disparo y si falla Oracle
dara por fallida la actualizacion, esta característica puede servir para impedir desde
trigger que se realiza una determinada operación.


19.10 Activar, desactivar compilar y eliminar un trigger

ALTER TRIGGER { ENABLE | DISABLE | COMPILE };

DROP TRIGGER nombre_trigger;


19.11 Vistas con información sobre triggers

USER_TRIGGERS
DBA_TRIGGERS
19.12 Registros en PL/SQL

El concepto registro en PL/SQL es igual a la mayoria de los lenguajes de programación.
Se trata de una estructura compuesta por otras mas simples llamadas campos que
pueden ser de distintos tipos, en PL/SQL hemos visto la estructura “ROWTYPE” para
crear una estructura de datos idéntica a la fila de una tabla, esta estructura de datos es un
registro. Esta forma de crear los registros es muy sencilla y rapida pero plantea algunas
limitaciones:
    1) Todos los nombres de los campos con sus tipos quedaran determinados por los
        nombres y tipos de las columnas de la tabla, sin que exista la posibilidad de
        varias algunos de ellos, sin poder excluir algunos ni incluir otros.
    2) No se incluyen restricciones como “NOT NULL” ni valores por defecto.
    3) La posibilidad de creación de variables de registros usando este formato quedara
        condicionada a la existencia de la tabla y sus posibles variaciones.

PL/SQL permite salvar todas estas limitaciones definiendo nosotros mismos el registro
y declarando después todas las variables de este tipo que necesitemos, para ello
procederemos según la sintaxis:

TYPE nombre_tipo IS RECORD
(nombre_campo1 tipo campo1 [NOT NULL][{:= | DEFAULT } valor],
.......

los tipos de campos se pueden especificar tambien usando %TYPE y %ROWTYPE.
Los tipos que contengan el valor NOT NULL deben ser inicializados. Por ejemplo:

TYPE persona IS RECORD
(DNI VARCHAR2(10) NOT NULL := valor_inicial,
nombre VARCHAR2(20)........
);

Se declaran las variables que se necesiten según el formato

R_persona persona

Podemos usar esta notación como si se tratase de cualquier variable, hay que tener
cuidado con las asignaciones del tipo var1 := var2, ya que las 2 deben ser del mismo
tipo. En caso contrario, aun cuando coincidan los tipos longitud y los tipos nombre de
los campos daria un error.
Un campo en un registro, puede ser a su vez un registro.

Ejemplo: Crear un tipo cliente que tenga la estructura de la tabla clientes.

TYPE t_cliente IS RECORD
( clt_num integer,
clt_nom varchar2,
…
);
v_cliente t_cliente
v_vliente.clt_num:=1
TYPE T_VENTAS IS RECORD
(NUM_CLT INTEGER,
CANTIDAD INTEGER,
PRECIO INTEGER,
D_CLT T_CLIENTES);


19.13 Tablas PL/SQL(tablas quiere decir como Arrays)

El tipo de datos “TABLE” sirve para definir estructuras de datos similares a los Arrays
que usan otros lenguajes. Por tanto es completamente distinto del concepto de tabla que
usa los SGBDR.
Las tablas PL/SQL tienen unas características que las hacen distintas de los que suelen
ser los Arrays de los lenguajes de programación.
Tienen 2 columnas, una es el indice o clave de tipo BINARY_INTEGER y la otra sera
la que guardara la información, cuyo tipo debera definir el programador.
El indice no tiene que ser necesariamente secuencial y los Arrays no ocupan posiciones
continuas de memoria.
No tienen una longitud predeterminada, el numero de filas puede incrementarse
dinámicamente.
El limite lo determina el rango de BINARY_INTEGER.

Definición del tipo TABLE y declaración de variables:

TYPE nombre_tipo IS TABLE OF tipo_elementos [NOT NULL] INDEX BY
BINARY_INTEGER

Nombre_tipo: es el especificador que identifica el tipo base de la tabla y que se usara
para definir las tablas.
Tipo_elementos: especifica el tipo de elementos que va a contener.

Puede hacerse de varias maneras:
   1) Indicando el tipo y la longitud si procede como si fuera la columna de una tabla.
   2) Utilizando nombre_variable%TYPE, indicando
       nombre_tabla.nombre_columna%TYPE.

Ejemplo: t_tabla_clientes, la tabla se llamara v_t_tabla_clientes y contendra la tabla
clientes.

TYPE t_tabla_clientes IS TABLE OF clientes%ROWTYPE INDEX BY
BINARY_INTEGER;
V_t_tabla_clientes t_tabla_clientes;

Una vez definido el tipo base se pueden declarar tablas de ese tipo como se desee
usando el formato “nombre_tabla nombre_tipo”.

Utilización de variables de tablas
Para referirnos a ella usaremos el nombre_tabla y entre paréntesis “()” el numero del
elemento. Haremos lo mismo para introducir valores, los elementos peuden ser usados
como si se tratasen de cualquier variable.
Cuando los elementos de la tabla son de tipo registro usaremos la notación del punto.

Nombre_tabla(numero).nombre_campo

Ejemplo: meter en el tipo a en su posición 1, el cliente numero 1.

SELECT * INTO A(1) FROM CLIENTES WHERE CLT_NUM=1;
DBMS_OUTPUT.PUT_LINE (A(1).CLT_NOM);


Atributos de tablas PL/SQL

Estas facilitan la gestion de las variables de tipo TABLE permitiendo recorrer la tabla,
contar y borrar los elementos.

Variable_tabla.atributo[(parámetros)]

Los parámetros hacen referencia a valores de indice

Tipos de atributos:

   -   FIRST, devuelve el valor BYNARY_INTEGER de la clave o indice del primer
       elemento.

       Variable.FIRST

   -   LAST, devuelve el valor BINARY_INTEGER del elemento la clave del ultimo
       elemento.

   -   PRIOR, devuelve el valor BINARY_INTEGER del elemento anterior al
       elemento n.


   -   NEXT, devuelve el valor BINARY_INTEGER del elemento siguiente al
       elemento n.

PRIOR y NEXT se usa para recorrer una tabla en cualquier sentido, pero deben ser con
valores de indices consecutivos. No obstante debemos tener cuidado con los valores que
devolveran de los extremos sera NULL.

i := var1.FIRST

WHILE i IS NOT NULL LOOP
     ……….
     i := VARIABLE.NEXT(i)

END LOOP;
   -   COUNT, devuelve el numero de filas que tiene una tabla.

       Variable.COUNT

   -   EXISTS devuelve TRUE si existe el elemento n. Se usa para evitar el error Ora-
       1503, que se produce al acceder a un registro que no existe, la cual levantara la
       excepcion NO_DATA_FOUND.

   -   DELETE, se usa para borrar elementos de la tabla.

       Variable.delete(n) borra ese elemento
       Variable.delete  borra todo
       Variable.delete(n1,n2)  borra de n1 a n2 incluidos

       Si n1 es <= que n2.

       Una tabla se elimina al salir del ambito del programador en el que se creo.


19.14 Paquetes. Elementos de un paquete

Un paquete se usa para guardar subprogramas y otros objetos de la base de datos, en los
paquetes podemos encontrar 2 elementos:
   - Especificación, contiene declaraciones publicas (accesibles desde cualquier parte
       del aplicación, de subprogramas, tipos, constantes...) actua como una interfaz
       con otros programas.
   - Cuerpo contiene los detalles de implementacion y de declaraciones privadas
       accesibles sólo desde los objetos del paquete.


19.15 Creacion de un paquete

Creación de cabecera o especificación:

CREATE [OR REPLACE] PACKAGE nombre_paquete AS
<declaracion de tipos,constantes, variables, curosres, excepciones y
otros objetos publicos>

<especificación de subprograma>


Creación del cuerpo del paquete:

CREATE [OR REPLACE] PACKAGE BODY nombre_paquete AS
<
       >
<especificacion de subprograma>
END [nombre_paquete]
Ejemplo: buscar cliente, tiene tipo registro que se llamara t_reg_cliente, tendra unos
procedimientos que se llamara ver_por_numero, ver_por_apellido, ver_por_nombre,
crearemos una funcion que se llamara datos que devolvera los datos de cliente.

create or replace package p_cliente as
type t_registro_cliente is record
(id clientes.clt_num%type,
apell clientes.clt_apell%type,
nom clientes.clt_nom%type,
loc clientes.clt_pais%type,
pob clientes.clt_pob%type
);
procedure ver_por_numero(a clientes.clt_num%type);
procedure ver_por_nombre(a clientes.clt_nom%type);
procedure ver_por_apellido(a clientes.clt_apell%type);
end p_cliente;
/

********* la funcion se declara en el cuerpo del paquete *********

create or replace package body p_cliente as

cli t_registro_cliente;

procedure ver;

procedure ver_por_numero(a clientes.clt_num%type)
is

begin
       select * into cli from clientes where clt_num=a;
       ver;
end ver_por_numero;

procedure ver_por_nombre(a clientes.clt_nom%type)
is

begin
        select * into cli from clientes where clt_nom=a;
        ver;

end ver_por_nombre;

procedure ver_por_apellido(a clientes.clt_apell%type)
is

begin
        if a='' then
                 dbms_output.put_line('No hay datos');
       else
       select * into cli from clientes where clt_apell=a;

       ver;
       end if;
end ver_por_apellido;

procedure ver
is
begin
      dbms_output.put_line(cli.id||cli.apell||cli.nom||cli.loc||cli.pob);

end ver;

end p_cliente;
/


Una vez creada la cabecera del paquete el cuerpo del paquete tendra el codigo
correspondiente a las declaraciones formales realizadas en la cabecera. Podra incluir
otros objetos locales al paquete como pueden ser


19.16 Utilizacion de los objetos definidos en un paquete

   a) Desde el mismo paquete, todos los objetos declarados en el paquete pueden ser
      utilizados por los demas objetos del mismo, con independencia de que hayan
      sido o no declarados en la especificación. Ademas, no necesitan utilizar la
      notación de punto para referirse a los objetos del mismo paquete (llamadas a una
      funcion/procedimiento) con sus parámetros.
   b) Desde fuera del paquete, los subprogramas y otros objetos contenidos en el
      paquete son accesibles desde fuera solamente si han sido declarados en la
      especificación. En nuestro ejemplo no se podria hacer referencia fuera del
      paquete al procedimiento ver_datos ya que no ha sido declarado en la cabecera.

Para ejecutar un procedimiento de un paquete desde SQL+ se usara el formato:

   EXECTURE nombre_paquete.nombre_procedimiento(lista_parámetros);


19.17 Declaracion de cursores en paquetes

Para declarar cursores en paquetes de manera que esten accesibles en la especificación,
deberemos separar la declaración del cursor del cuerpo. La declaración del cursor se
incluira en la cabecera del paquete indicando el nombre del cursor, los parámetros si
procede y el tipo de devuelve, este ultimo se indicara mediante “RETURN tipo_dato”.
Para cursores que devuelvan filas enteras se utilizara %ROWTYPE.


CREATE PACKAGE P1 AS
       ……….
       CURSOR C1 RETURN CLIENTES%ROWTYPE;

END P1;

CREATE PACKAGE BODY P1 AS

       ………..
       CURSOR C1 RETURN CLIETNES%ROWTYPE;
       SELECT * FROM CLIENTES WHERE CLT_POB=’E’;

END P1;


19.18 Ambitos y otras características de las declaraciones

Los objetos declarados en la especificación del paquete son globales al paquete y
locales al contexto. Asi mismo todas las variables declaradas en la especificación del
paquete mantienen su valor durante la sesion, por tanto el valor no se pierde entre las
llamadas de los subprogramas.
Tambien cabe mencionar respecto a las variables, constante y cursores declarados en el
paquete los siguientes:
         El propietario es la sesion
         Cuando se creen los objetos sus valores son nulos a menos que se inicialicen
         Durante la sesion los valores pueden cambiarse
         En la sesion no se crean los objetos hasta que se referencia al paquete.
         Al salir de la sesion los valores se pierden.


19.19 Caracteristicas de almacenamiento y compilación

Tanto el codigo fuente como el codigo compilado en los paquetes se almacena en la
base de datos. Al igual que ocurria con los subprogramas almacenados el paquete puede
tener 2 estados “VALID” o “INVALID”. Cuando se borra o modifica algunos de los
objetos referenciados el paquete pasara a “INVALID”, si la especificación del paquete
se encuentra no disponible, Oracle invalida cualquier paquete que haga referencia al
objeto.
Cuando recompilamos la especificación todos los objetos que hacen referencia al
paquete, pasan a no disponible, hasta que sean compilados de nuevo. Cualquier
referencia o llamada a uno de estos objetos antes de ser llamadas, producira que Oracle
automáticamente los recompile.
Si se cambia la definición de una funcion o procedimiento incluida en un paquete, no
hay que recompilar todos los programas que llaman al subprograma, ha no ser que se
cambie la especificación de cada objeto en el paquete.
19.20 Paquetes suministrados por Oracle

Oracle incluye con su gestor de base de datos diversos paquetes como STANDARD,
DBMS_OUTPUT, DBMS_STANDAR, DBMS_SQL.

En STANDARD se declaran tipos, funciones, excepciones disponibles desde el entorno
PL/SQL, tiene las siguientes funciones:

FUNCTION ABS(n number) RETURN NUMBER;
FUNCTION TO_CHAR…..
………….

DBMS_STANDAR incluye procedimientos como RAISE_APPLICATION_ERROR.
DBMS_OUTPUT el PUT_LINE, dentro de este tambien está ENABLE y DISABLE
que permiten configurar y borrar el buffer.
DMBS_SQL lo que usa es procedimientos y funciones que permiten usar SQL
dinamico.


19.21 SQL dinamico

Cuando se compila un procedimiento PL/SQL comprueba en el diccionario de datos y
completa todas las referencias a objetos de la base de datos como pueden ser columnas,
tablas, datos... de esta forma si el objeto no existe en el momento de la compilación el
proceso fallara, mostrando el error correspondiente.
Esta manera de trabajar tiene importantes ventajas, especialmente en cuanto a la
velocidad en la ejecución y la seguridad de que las referencias de los objetos han sido
comprobadas previamente, pero tambien tiene algunos inconvenientes:

   -   Todos los objetos tienen que existir en tiempo de compilación.
   -   No se pueden crear objetos nuevos con el programa.
   -   No se pueden cambiar la definición de los objetos.

Esta forma de trabajar con SQL se denomina SQL estatico, el paquete DBMS_SQL
permite superar estas limitaciones y ejecutar instrucciones de definiciones de datos, asi
como resolver referencias a objetos en el momento de la ejecución.

El paquete DBMS_SQL va a tener una serie de procedimientos:

   -   OPEN_CURSOR, abre el cursor y devuelve un numero de identificación para
       poder utilizarlo (de tipo entero).
   -   PARSE analiza la instrucción que se va a ejecutar, este paso es necesario ya que
       cuando se compila el programa la instrucción no pudo ser analizada porque aun
       no habia sido construido.
   -   EXECUTE que se encarga de la ejecución.
   -   CLOSE_CURSOR cierra el cursor.
Ejemplo: Crear el usuario DUM1 con contraseña DUM1

CREATE OR REPLACE PROCEDURE CREAR_CLIENTE

IS

        ID INTEGER;

BEGIN

     ID:=DBMS_SQL.OPEN_CURSOR;
     DBMS_SQL.PARSE (ID,'CREATE USER DUM1 IDENTIFIED BY
DUM1',DBMS_SQL.NATIVE);
     DBMS_SQL.CLOSE_CURSOR(ID);

END;
/

Eemplo: crear una tabla pasándole el nombre y los campos.

CREATE OR REPLACE PROCEDURE CREAR_TABLA (NOMBRE
VARCHAR2,CONTENIDO VARCHAR2)

IS

        ID INTEGER;

BEGIN

      ID:= DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(ID,'CREATE TABLE
'||NOMBRE||'('||CONTENIDO||')',DBMS_SQL.NATIVE);
      DBMS_SQL.CLOSE_CURSOR(ID);

END;
/

Le pasamos el argumento después de compilarlo:

EXEC CREAR_TABLA(‘PETETE’,’ID INTEGER, NOM VARCHAR2(20)’);
Existe una funcion del DBMS_SQL que es BIND_VARIABLE que sirve para
manipular datos(inserciones).

********* NO FURULA *************
CREATE OR REPLACE PROCEDURE INSER_TABLA(VALOR INTEGER)

IS

        ID INTEGER;
        CADENA VARCHAR2(50);
        MI_VALOR INTEGER;

BEGIN

        CADENA:='INSERT INTO PETETE VALUES(:MI_VALOR)';
        ID:=DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(ID,CADENA,DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(ID,':MI_VALOR',VALOR);
        DBMS_SQL.CLOSE_CURSOR(ID);

END;
/


Existe una funcion que se usa para hacer consultas, DEFINE_COLUMN que sirve para
especificar las variables que recibiran los valores de la selección, se utilizara una
llamada para cada variable indicando:
    1) El identificador del cursor.
    2) El numero de la columna de la clausula SELECT que recibira el valor.
    3) El nombre de la variable.
    4) Si la variable es de tipo CHAR,VARCHAR2 o RAW hay que especificar la
        longitud.

DBMS_SQL.DEFINE_COLUMN(id,1,variable_a,15);


COLUMN_VALUE deposita el valor de un elemento del cursor recuperado cuya
posición se especefica en una variable PL/SQL.

Para recorrer existe FETCH_ROWS


Ejemplo: consultar la tabla cliente, le pasamos una condicion clt_num=1, y un valor

set serveroutput on
create or replace procedure selec(a char,b number)
is
        id integer;
        instruccion varchar2(100);
        apellido clientes.clt_apell%type;
        nombre clientes.clt_nom%type;
        resultado integer;

begin

        id:=dbms_sql.open_cursor;

        instruccion:='select clt_nom, clt_apell from clientes where '||a||' :mivariable';

        dbms_sql.parse(id,instruccion,dbms_sql.native);

        dbms_sql.bind_variable(id,':mivariable',b);

        resultado:=dbms_sql.execute(id);

        dbms_sql.define_column(id,1,nombre,10);

        dbms_sql.define_column(id,2,apellido,10);

        while dbms_sql.fetch_rows(id)>0 loop

               dbms_sql.column_value(id,1,nombre);

               dbms_sql.column_value(id,2,apellido);

               dbms_output.put_line(nombre || apellido);

        end loop;

end;
/

Se ejecuta:

EXEC SELEC('CLT_NUM=',1);

Que daria:

Margarita BORRAS

PL/SQL procedure successfully completed.

Ejercicios


SET SERVEROUTPUT ON
DROP TABLE AUDITAREMPLE;
CREATE TABLE AUDITAREMPLE (COL1 VARCHAR2(200));

CREATE OR REPLACE TRIGGER TRIG_AUDI
AFTER DELETE OR
INSERT
ON EMPLE
FOR EACH ROW

IS

        CADENA VARCHAR2(200)

BEGIN

        IF INSERTING THEN

           CADENA:=
SYSDATE,:NEW.EMP_NO,:NEW.APELLIDO,'INSERTADO';

        END IF;

        IF DELETING THEN

             CADENA:= SYSDATE,:OLD.EMP_NO,:OLD.APELLIDO,'BORRADO';

        END IF;

INSERT INTO AUDITAREMPLE VALUES (CADENA);

END;
/


19.22 Pasos para utilizar SQL dinamico

19.23 Comandos de definición de datos

19.24 Comandos de manipulación de datos

19.25 Consultas

								
To top