3_ol-oracle02t-v2

OL-ORACLE02T PL/ SQL Módulo 2

MASTER ORACLE ONLINE OL02T1

PL-SQL



Dirigido a:

Desarrolladores de aplicaciones.

Analistas/Programadores.

Administradores de Base de Datos.

Técnicos de Sistemas

Duración:

Dos semanas de autoestudio

Propósito:

Introducir al alumno en el conocimiento del lenguaje de programación PL/

SQL como lenguaje del motor de base de datos Oracle.

Conocer las estructuras y sentencias de control básicas y su uso en bases

de datos.

Aprender el uso y ventajas de los elementos de programación almacenados

en la base datos: procedimientos, funciones, paquetes y triggers



Contenido

PL/SQL ........................................................................................................................................... 4

Características ............................................................................................................................... 5

Ventajas......................................................................................................................................... 6

Elementos del lenguaje.................................................................................................................. 7

Bloques PL / SQL ............................................................................................................................ 8

Tipos de Bloques............................................................................................................................ 9

Variables...................................................................................................................................... 10

Tipos de Datos Escalares .............................................................................................................. 11

Declaración de Variables.............................................................................................................. 11

El Atributo %TYPE ........................................................................................................................ 12

El atributo %ROWTYPE................................................................................................................. 12

Variables Boolean ........................................................................................................................ 13

Mostrar mensajes: DBMS_OUTPUT.PUT_LINE.............................................................................. 14

Entorno de trabajo, el primer programa....................................................................................... 15

Lectura de variables por teclado, las variables & .......................................................................... 18

Funciones SQL.............................................................................................................................. 22

Utilizables en sentencias procedurales:........................................................................................ 22

No utilizables en sentencias procedurales: ................................................................................... 22

Operadores.................................................................................................................................. 25

SELECT ......................................................................................................................................... 26

Manipulación de Datos ................................................................................................................ 27

La sentencia MERGE ......................................................................... ¡Error! Marcador no definido.

Estructuras de Control ................................................................................................................. 28

Flujo de control............................................................................................................................ 28

Sentencia if: ................................................................................................................................. 28

Sentencia CASE ............................................................................................................................ 30

Control Iterativo: Sentencias LOOP .............................................................................................. 32

Tipos de Datos Compuestos ......................................................................................................... 36

Cursores ...................................................................................................................................... 43



PL/SQL

Es la respuesta de Oracle a las limitaciones de SQL.

Es un lenguaje de programación estructurado que combina sentencias SQL con

bucles, sentencias de control,...

Es específico de Oracle, por lo que su uso no es válido en otras bases de datos.

PL/SQL es un sofisticado lenguaje de programación que se utiliza para acceder a

bases de datos Oracle desde distintos entornos. PL/SQL está integrado con el

servidor de base de datos, de modo que el código PL/SQL puede ser procesado de

forma rápida y eficiente.

PL/SQL combina la potencia y flexibilidad de un lenguaje SQL con las

estructuras procedimentales de un 3GL.



Características

Juega un papel central tanto en las herramientas de Oracle (Developer, Forms,

...) como en el propio servidor (a través de funciones, triggers, ... almacenados en

el propio servidor).

El motor PL/SQL presente tanto en las herramientas como en el servidor, filtra

las sentencias SQL incluidas en los programas y las envía a la Base de Datos,

procesando el resto de las sentencias procedurales en el propio motor con datos

que son internos a la aplicación, lo que reduce las consultas a la base de datos.

PL/SQL amplía la funcionalidad de SQL añadiendo estructuras de las que

pueden encontrarse en otros lenguajes procedimentales, como:

• Variables y tipos (tanto predefinidos como definidos por el usuario).

• Estructuras de control, como bucles y órdenes IF-THEN-ELSE.

• Procedimientos y funciones.

• Tipos de objetos y métodos (en PL/SQL versión 8 o superior).

Las construcciones procedimentales están perfectamente integradas con Oracle

SQL, lo que da como resultado un lenguaje potente y estructurado.



Ventajas

La modularización que caracteriza a los programas escritos en PL/SQL permite:

• Agrupar sentencias relacionadas lógicamente, en bloques.

• Anidar subbloques en bloques mayores para desarrollar aplicaciones

complejas.

• Guardar los programas en librerías reutilizables.

• PL/SQL permite controlar los errores.

• Almacenamiento de unidades de programación (procedimientos, funciones,

paquetes, triggers)

• Los procedimientos, funciones y paquetes almacenados pueden ser

invocados desde herramientas (Forms, Developer) o desde la propia

consola.

PL/SQL integra tanto las estructuras procedimentales necesarias como el acceso

a bases de datos. El resultado es un lenguaje robusto y potente, bien adaptado al

diseño de aplicaciones complejas.

Su capacidad de reutilización, de poderse almacenar en paquetes y su control de

las excepciones.

Su conocimiento permite el acceso a las distintas herramientas de Oracle que lo

utilizan.



Elementos del lenguaje

Se pueden clasificar en:

• Identificadores

• Literales

• Comentarios

Identificadores

Pueden contener hasta 30 caracteres.

Deben empezar con un carácter alfabético.

Pueden contener caracteres alfanuméricos, $, _ y signos numéricos.

No pueden contener guiones, / , y espacios.

No debería usarse el mismo nombre que tenga la columna de una tabla.

No debería usar palabras reservadas.

Literales y comentarios

Los literales de caracteres y fechas deben ir entre comillas simples.

Los números pueden ser valores simples o científicos.

Un slash (/) ejecuta la sentencia contenida en un Script o en el entorno SQL .

-- Marca comentarios de una línea.

/*Delimita comentarios de más de una línea */



Bloques PL / SQL

Los programas en PL/SQL pueden ser también llamados bloques PL/SQL.

Pueden tener un nombre esto es, ser un procedimiento, una función, un paquete

o un trigger o ser un bloque PL anónimo.

La estructura de un programa sea del tipo que sea es la siguiente:

DECLARE

declaraciones (variables, cursores, excepciones definidas por el usuario)

BEGIN

sentencias del programa (PL / SQL y SQL)

EXCEPTION

Procedimientos a seguir en caso de excepciones

END ;

Ejemplo de bloque PL anónimo:

DECLARE

mi_variable NUMBER (5);

BEGIN

SELECT salario INTO mi_variable

FROM empleados

WHERE numero_empleado = 1234;

EXCEPTION

WHEN nombre_excepción THEN ...

END;



Tipos de Bloques

Anónimos

Subprogramas: Procedimientos, Funciones

Anónimos Subprogramas

Procedimientos Funciones

[DECLARE] PROCEDURE nombre FUNCTION nombre

IS RETURN tipo IS

BEGIN BEGIN BEGIN

--sentencias --sentencias --sentencias

[EXCEPTION] [EXCEPTION] [EXCEPTION]

END ; END ; END ;



Variables

Las variables pueden utilizarse para el almacenamiento temporal de datos,

trabajar con valores almacenados, ser usadas repetidamente, etc...

Se declaran e inicializan en la sección de declaraciones.

Se les asignan nuevos valores en la sección de ejecución.

Se les pueden pasar valores a través de parámetros.

Se inicializan a nulo.

Tipos de Variables

Variables PL / SQL:

• Escalares à sólo pueden recoger un valor.

• Compuestas à recogen fila o filas.

• Referencias àpunteros que apuntan a objetos de otros programas.

• LOB (large objects) à contienen localizadores que indican la localización de

objetos de gran tamaño (por ejemplo imágenes).



Tipos de Datos Escalares

CHAR [(longitud máxima)]

VARCHAR2 (longitud máxima)

NUMBER [(precisión, escala)]

BINARY_INTEGER

PLS_INTEGER

BOOLEAN

DATE

Declaración de Variables

Dos variables pueden tener el mismo nombre si pertenecen a dos bloques

distintos.

El identificador debería ser distinto a los nombres de las columnas de la tabla

usada en el bloque.

Ejemplos:

v_job VARCHAR2 (9);

cuenta BINARY_INTEGER NOT NULL : = 0;

fecha_fut DATE : = SYSDATE + 7;

factor CONSTANT NUMBER (3,2) : = 8.25 ;



El Atributo %TYPE

Define el tipo de la variable en función del de una columna de una tabla, o del de

una variable previamente declarada.

Ejemplos:

var_nombre emp.ename%TYPE;

var_primer_apelllido VARCHAR2(50);

var_segundo_apellido var_primer_apellido%TYPE ;

El atributo %ROWTYPE

Lo utilizaremos normalmente con cursores (se ven más adelante), en el

ejemplo definimos una variable que es del mismo tipo que lo que haya en la tabla

DEPT. Se utiliza para copiar la estructura de campos y de tipos de datos de una

variable que es una tabla a otra.

VARIABLE_TABLE DEPT%ROWTYPE;



Variables Boolean

Únicamente se les pueden asignar los valores TRUE, FALSE y NULL.

Para realizar comprobaciones entre variables de este tipo se utilizan los

operadores AND, OR y NOT.

Se pueden utilizar expresiones aritméticas, caracteres o fechas para conseguir un

valor boolean.

EJEMPLO:

DECLARE

ENCONTRADO BOOLEAN :=FALSE;

BEGIN

IF A=….. THEN

ENCONTRADO := TRUE….



Mostrar mensajes: DBMS_OUTPUT.PUT_LINE

Es una manera de mostrar los datos de un bloque PL/SQL, enseñando una

cadena de caracteres predeterminada. Es un paquete de Base de Datos.

Puesto que sólo es capaz de mostrar un dato, cuando se quiere mostrar más de

uno será necesario el uso del operador de concatenación.

Ejemplo

DECLARE

v_sal NUMBER(9,2) : = 60000 ;

BEGIN

v_sal : = v_sal/12;

DBMS_OUTPUT.PUT_LINE(‘El salario es :’||v_sal);

END;

NOTA: Las sentencias pueden continuar a lo largo de varias líneas.



Entorno de trabajo, el primer programa

Inicialmente el entorno de trabajo será SQL*PLUS aunque existen otros entornos

(SQL Developer etc) con los que también es posible trabajar.

NOTA IMPORTANTE: Las pantallas que se muestran aquí son de Oracle 10g.

En Oracle 11g es igual pero a través de la pantalla que se abre a través de línea

de comando (ya vista en Sql).

Una vez dentro del sql*plus lanzar el comando:

SET SERVEROUTPUT ON. Este comando pertenece al entorno sql*plus no

es una sentencia PL/SQL por lo tanto deberá ir fuera de los programas.

Sirve para habilitar la salida de mensajes por pantalla y deberá ponerse en

cada nueva conexión.



1) Escribiremos el programa con el editor del sistema, en este caso el

notepad.



2) Y ahora lo ejecutaremos. Esto puede hacerse de dos maneras:

a. “copiar y pegar” directamente en sql*plus

b. Ejecutando el fichero con:

@c:\hola_mundo.sql (suponermos que está guardado con extensión

sql en el directorio c:)

mailto:@c:\hola_mundo.sql



Lectura de variables por teclado, las variables &

El carácter & es un carácter especial en Oracle. Provoca que el programa pare y

pida un valor cada vez que encuentra una variable precedida de este símbolo.

El uso que veremos es sencillo. Si la variable es carácter irá entrecomillada, si es

numérica no.

Podemos decir que ni la entrada de datos (con &), ni la salida de mensajes con

DBMS_OUTPUT son muy sofisticados en este lenguaje…. Es necesario tener en

cuenta que se trata de un lenguaje enfocado al tratamiento masivo de la

información.



Segundo programa con lectura de variables.

El programa es el siguiente:

BEGIN

-- ESTE ES EL SEGUNDO PROGRAMA

-- NO TIENE PARTE DECLARATIVA

-- NI DE EXCEPCIONES

-- ES PARA DEMOSTRAR COMO SE LEEN VARIABLES

-- NOMBRE SE CONSIDERA DE TIPO CARÁCTER Y AÑOS

-- NUMÉRICA

DBMS_OUTPUT.PUT_LINE('BUENOS DIAS ' || '&NOMBRE' ||

' TIENES ' || &AÑOS || ' AÑOS ');

END;

/



La salida queda como sigue:



Quizá un poco liosa ¿no?. Vamos a eliminar los mensajes de “antiguo nuevo”….

Para ello lanzaremos el comando: SET VERIFY OFF. La salida queda de la

siguiente manera:

Así queda algo más clara.



Funciones SQL

Utilizables en sentencias procedurales:

• Numéricas a nivel de una fila

• De carácter a nivel de una fila

• De conversión de tipos Igual que en SQL

• De fecha

• De intervalos de tiempo

No utilizables en sentencias procedurales:

• DECODE

• Funciones de Grupo (válidas en sentencias SQL)

Ejemplo de funciones SQL

Concatenando datos en una cadena con espacios en blanco:

v_nombre_completo := v_nombre||’ ‘||

v_primer_apellido ||’ ‘||

v_segundo_apellido;

Utilizando la conversión a minúsculas:

v_nombre := LOWER(v_nombre);



Funciones de conversión

Las funciones tiene el mismo funcionamiento que en SQL , así:

DECLARE

v_date DATE := TO_DATE(‘12,ENE,99’,’DD,MON,YY’);

BEGIN...

La sentencia :

V_date:= ‘Enero 20, 1999’ à Sería errónea



Bloques Anidados

Los bloques se pueden anidar en cualquier sitio que se permitan sentencias

ejecutables, convirtiéndose el bloque en una sentencia.

Características

• Una sección EXCEPTION puede contener bloques anidados, es decir, ser

compartida por todos los bloques anidados.

• El ámbito de un identificador es la zona en la que se puede referenciar ese

identificador. Un identificador es visible para el bloque que ha sido

declarado y para sus bloques anidados.

Ámbitos

....

X NUMBER;

BEGIN

....

DECLARE à aunque puede hacerse no es una buena práctica….

y NUMBER(7); ámbito de x

BEGIN

y:=x; ámbito de y

END;

....

END;



Operadores

Operador de Asignación :=

Operador de Concatenación ||

Operadores Aritméticos +,-,*,/,**(potencia), resto de la división entre A y

B mod(A,B)

Operadores de Relación =,>,<,>=,<=, IN (contenido), NOT IN,

BETWEEN, NOT BETWEEN, LIKE, NOT LIKE, IS

NULL, IS NOT NULL

Operadores Lógicos AND, NOT ,OR

Paréntesis para controlar el orden de las operaciones



Interacción con el servidor Oracle

Sentencias SQL

La extracción de datos de la base de datos se realiza a través del uso de

sentencias SELECT.

El cambio de datos en las filas de las tablas se realiza a través de comandos DML.

El control de transacciones se lleva a cabo con los comandos COMMIT,

ROLLBACK o SAVEPOINT.

Formato general:

SELECT expresion INTO {var1[,var2] | registro }

FROM nombre_tabla [WHERE condición];

SELECT

La claúsula INTO es imprescindible, es necesario guardar el valor leído en una

variable.

Todas las instrucciones SELECT tienen que llevar un INTO a excepción de

los cursores.

Las búsquedas deben devolver sólo una fila. Cuando devuelven más es

necesario usar otra estructura de datos.

DECLARE

suma NUMBER(10,2);

departamento VARCHAR2(3) : =20;

BEGIN

SELECT SUM(sal) INTO suma

FROM emp

WHERE deptno = departamento;

DBMS_OUTPUT.PUT_LINE(‘El total es ’|| suma);

END;

/



Manipulación de Datos

Se realiza con los comando INSERT, UPDATE y DELETE de SQL, pudiendo

combinarlo con el uso de variables, etc..

DECLARE

incremento emp.sal%TYPE : = 50;

BEGIN

UPDATE emp SET sal = sal + incremento

WHERE deptno = 20;

END;

/



Estructuras de Control

Flujo de control.

Sentencia IF:

La ejecución lógica de un programa se puede cambiar con el uso del condicional

IF.

• IF – THEN – END IF

• IF – THEN ELSE – END IF

• IF – THEN ELSIF – END IF

Se utilizan para la toma de decisiones.



La estructura básica es la siguiente

IF SIMPLE IF DOBLE

IF CONDIDION THEN IF CONDIDION THEN

SENTENCIA/S; SENTENCIA/S;

END IF; ELSE

SENTENCIA/S;

END IF;

IF MULTIPLE

IF CONDIDION THEN

SENTENCIA/S;

ELSIF CONDICION1 THEN

SENTENCIA/S;

ELSIF CONDICION2 THEN

SENTENCIA2/S;

ELSE

SENTENCIA/S;

END IF;



Sentencia de selección múltiple: CASE

Aparece en Oracle 9i, es equivalente al if múltiple. Es una sentencia de selección

múltiple que selecciona un valor entre varios y lo devuelve.

………

CASE v_nota

WHEN 9 THEN V_calificacion : = ‘Sobresaliente’

WHEN 7 THEN V_calificacion : = ‘Notable’

WHEN 5 THEN V_calificacion : = ‘Aprobado’

ELSE V_calificacion : = ‘Suspenso’

END CASE;

......



Manejando Valores Nulos

Al trabajar con valores nulos, se pueden evitar algunos errores si se tiene en

cuenta:

• Las comparaciones simples que incluyen nulls, siempre devuelven null.

• Aplicar el operador lógico NOT a un campo null devuelve null.

• En una sentencia de control condicional, si la condición devuelve null , las

secuencia asociada de sentencias, no se ejecuta.



Control Iterativo: BUCLES (LOOP)

Repiten una sentencia o secuencia de sentencias varias veces.

Hay tres tipos de bucles:

• Loop Básico à es conveniente cuando las sentencias se deban ejecutar al

menos una vez.

• Loop FOR à se usa cuando se conoce el número de iteraciones.

• Loop WHILE àcuando se quiera comprobar la condición al comienzo de

cada iteración.



Ejemplo para mostrar por pantalla los 50 primeros números. Se realiza con

los tres tipos de bubles.

Bucle Básico

DECLARE

-- SACAR POR PANTALLA LOS 50 PRIMEROS NUMEROS

-- CON LOOP

CONTADOR NUMBER(2):= 1;

BEGIN

LOOP

DBMS_OUTPUT.PUT_LINE(CONTADOR);

EXIT WHEN CONTADOR = 50;

CONTADOR := CONTADOR +1;

END LOOP;

END;

/



Bucle WHILE

DECLARE

V_CONT NUMBER;

BEGIN

V_CONT :=0;

WHILE V_CONT<50 LOOP

DBMS_OUTPUT.PUT_LINE(V_CONT);

V_CONT:=V_CONT + 1;

END LOOP;

END;

/



Bucle FOR

DECLARE

-- SACAR POR PANTALLA LOS 50 PRIMEROS NUMEROS

-- CON FOR

BEGIN

FOR I IN 1..50 LOOP

DBMS_OUTPUT.PUT_LINE(I);

END LOOP;

END;

/

DECLARE

-- SACAR POR PANTALLA LOS NUMEROS DEL 50 AL 1

-- CON FOR

BEGIN

FOR I IN REVERSE 1..50 LOOP

DBMS_OUTPUT.PUT_LINE(I);

END LOOP;

END;

/



Tipos de Datos Compuestos

Los tipos de datos escalares o simples pueden agruparse formando un nuevo tipo

de dato llamado estructurado o compuesto, que será un nuevo tipo de dato

definido por el programador.

Hay dos tipos:

• PL / SQL RECORDS (registros).

• PL / SQL Collections :

- INDEX BY Table (Tablas).

- Tablas Anidadas.

- VARRAY

Aquí se tratan las primeras.



Registros

Son estructuras de datos que agrupan información de una tabla o de otro registro

y que se tratan como una unidad lógica.

Son convenientes para recoger datos de una fila para su procesamiento.

Ejemplo:

....

TYPE datos_personales IS RECORD à declaración de tipo de dato

(

nombre VARCHAR2(10),

apellido VARCHAR2(10),

edad NUMBER (3)

);

Registro1 datos_personales; à declaración de variable del tipo anterior

....



Tablas

Son matrices dinámicas, compuestas por un índice y una columna de tipo escalar

o registro.

Sintaxis:

DECLARE

TYPE nombre_tabla IS TABLE OF

(tipo_columna | variable%TYPE | tabla.columna%TYPE) [NOT NULL]

| tabla.%ROWTYPE

INDEX BY BINARY_INTEGER;

identificador nombre_tabla;

....

Ejemplo de declaración:

DECLARE

T_MESES IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;

GANACIAS T_MESES;



Ejemplo de programa completo:

DECLARE

-- PROGRAMA DE EJEMPLO DE UTILIZACIÓN DE TABLAS

TYPE TABLA IS TABLE OF NUMBER(5)

INDEX BY BINARY_INTEGER;

-- DECLARAMOS DOS VARIABLES DEL TIPO TABLA

NUMEROS_1 TABLA;

NUMEROS_2 TABLA;

-- DECLARAMOS EL ÍNDICE

XBINARY_INTEGER;

VALOR NUMBER(5):=0;

BEGIN

-- ASIGNAR VALORES DIRECTAMENTE

NUMEROS_1(1) :=100;

NUMEROS_1(2) :=200;

NUMEROS_1(3) :=300;

-- ASIGNAR VALORES CON UN BUCLE

FOR X IN 1..3 LOOP

VALOR:=VALOR+100;

NUMEROS_2(X) :=VALOR;

END LOOP;



-- MOSTRAMOS LOS VALORES INTRODUCIDOS EN LAS DOS TABLAS

FOR X IN 1..3 LOOP

DBMS_OUTPUT.PUT_LINE('TABLA 1 ELEMENTO ' || X || ' = ' ||

NUMEROS_1(X));

DBMS_OUTPUT.PUT_LINE('TABLA 2 ELEMENTO ' || X || ' = ' ||

NUMEROS_2(X));

END LOOP;

-- OPERAMOS CON LOS ELEMENTOS DE LA TABLA

NUMEROS_1(1) :=VALOR*NUMEROS_2(3);

-- MOSTRAMOS EL RESULTADO

DBMS_OUTPUT.PUT_LINE('TABLA 1 ELEMENTO 1 ' || NUMEROS_1(1));

END;



Atributos de las Tablas

Una tabla está compuesta por una serie de elementos y se puede recorrer con

un índice que no tiene por qué ser consecutivo. Es por ello que para poder

recorrerla se necesitan una serie de atributos, para acceder a ellos:

Nombre_tabla.atributo_al_que_acceder

COUNT à indica el número de elementos que una tabla contiene.

Tabla1.count;

DELETE à

Tabla1.delete à elimina todos los elementos.

Tabla1.delete(4) à elimina un elemento determinado.

Tabla1.delete(4,7) à elimina un rango de elementos..

EXISTS à devuelve true si un elemento de la tabla existe.

Tabla1.exists(7);

FIRST à devuelve el índice del primer elemento de la tabla

LAST à devuelve el índice del último elemento de la tabla

NEXTà devuelve el índice del elemento inmediatamente posterior al de índice i

PRIORà devuelve el índice del elemento inmediatamente anterior al de índice i



Ejemplo de Tablas de registros

SET SERVEROUTPUT ON

DECLARE

TYPE emp_table_type is table of

employees%ROWTYPE INDEX BY BINARY_INTEGER;

my_emp_table emp_table_type;

v_count NUMBER(3) := 104;

BEGIN

FOR i IN 100 .. v_count

LOOP

SELECT INTO my_emp_table(i) FROM employees

WHERE employee_id = i;

-- ASUME QUE EXISTEN LOS EMPLEADOS CON NÚMEROS DEL 100 AL 104

-- SI NO ES ASÍ, LA SELECT FALLARÍA

END LOOP;

FOR i IN my_emp_table.FIRST .. my_emp_table.LAST

LOOP

DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);

END LOOP;

END;



Cursores

Cuando se ejecuta una sentencia SQL el servidor abre un área de memoria en la

que el comando se lleva a cabo, esa área es el cursor.

Hay dos tipos de cursor:

• Implícitos (abiertos por PL/SQL).

• Explícitos (declarados por el programador).



Cursores implícitos

Para manejar un cursor tenemos unos atributos:

SQL%FOUND, Devuelve verdadero si el cursor tiene alguna fila, falso en caso

contrario.

SQL%NOTFOUND Verdadero si el cursor no ha devuelto ninguna fila.

SQL%ROWCOUNT, número de filas al que afecta la operación.

LA SENTENCIA SELECT..INTO NUNCA VA A SALIR POR SQL%NOTFOUND

Si una sentencia SELECT no encuentra filas genera una excepción, que es

un tipo de error que puede ser tratado o no, el error es NO_DATA_FOUND.

Oracle abre,cierra y trata los cursores, nosotros tratamos los atributos.



Ejemplo Cursor Implícito

Doblar el salario al empleado 7902 si existe, si no existe se inserta en la

tabla EMP

BEGIN

UPDATE EMP

SET SAL=SAL *2

WHERE EMPNO=7902;

IF SQL%NOTFOUND THEN

INSERT INTO EMP(EMPNO,ENAME,JOB) VALUES

(1234,'LUIS ', 'ANALISTA ');

END IF;

END;

/



Cursores explícitos

Es el mecanismo de PL/SQL para tratar individualmente el conjunto de filas

recuperado por una sentencia SELECT.

Es una estructura en memoria que recupera filas de 1 o varias tablas, donde se

accede a los datos de forma secuencial.

Los cursores se tratan de la siguiente forma:

1. Declaración del cursor

2. Apertura del cursor

3. Recogida de datos en variables PL

4. Cierre del cursor.

Son definidos por el programador.



Ciclo de vida de un cursor:

Declaración

Para poder tratar una a una las filas, debemos declarar el cursor como si fuese

una variable.

En la declaración, no se crea el cursor, ya que no se ejecuta sentencia alguna.

CURSOR nombre_cursor IS sentencia_select;

Es necesario declarar también una variable de cursor para posteriormente tratar

los valores leídos.

Ejemplo:

DECLARE

CURSOR C1 IS

SELECT *

FROM empleados

WHERE salario BETWEEN 1000 AND 2000;

VAR_C1 C1%ROWTYPE;

....

BEGIN

…..



Apertura

Tras haberlo declarado, el cursor se abre con la sentencia OPEN nombre_cursor;

dentro de la sección ejecutable.

Al abrir el cursor se ejecuta el SELECT y almacena el resultado en la memoria

principal, el cursor queda apuntando a la primera fila.

Se puede abrir un cursor varias veces seguidas, lo que serviría para ver si ha

habido actualizaciones.

Ejemplo:

DECLARE

CURSOR C1 IS

SELECT *

FROM empleados


VAR_C1 C1%ROWTYPE;

....

BEGIN

OPEN C1;

…..



Extracción

La extracción de las filas para su tratamiento se realiza con la claúsula FETCH:

• FETCH nombre_cursor INTO variable1 [,variable2,...]; à los tipos deben

coincidir.

• FETCH nombre_cursor INTO registro;

Ejemplo:

DECLARE

CURSOR C1 IS

SELECT *

FROM empleados


VAR_C1 C1%ROWTYPE;

....

BEGIN

OPEN C1;

FETCH C1 INTO VAR_C1;

….



Cierre

Cuando se han recorrido todas las filas con FETCH, se debe cerrar el cursor para

liberar memoria.

La cláusula CLOSE cierra el cursor.

Ejemplo:

DECLARE

CURSOR C1 IS

SELECT *

FROM empleados


VAR_C1 C1%ROWTYPE;

....

BEGIN

OPEN C1;

FETCH C1 INTO VAR_C1;

….

CLOSE C1;



Atributos

%FOUND à devuelve true si el último FETCH efectuado devolvió una nueva fila,

false en caso contrario.

%NOTFOUND à devuelve true si el último FETCH no devolvió una nueva fila, se

utiliza frecuentemente para salir de bucles.

%ISOPEN à Devuelve verdadero si el cursor está abierto.

%ROWCOUNT àDevuelve el nº de filas extraídas hasta el momento.



Bucles de cursores

Puesto que se trata de conjunto de filas se necesita un bucle para procesar las

filas: tenemos que leer una fila tratarla, leer otra, tratarla, ect..

Existen tres tipos:

• LOOP Básico

• WHILE (requiere una lectura previa)

• FOR, es un atajo para procesar cursores, ya que implícitamente

lo abre, trabaja con él, sale y lo cierra.

El registro está declarado de manera implícita.



BUCLE LOOP

DECLARE

--Primero declaramos el cursor.

CURSOR C1 IS SELECT…..

VAR_CI C1%ROWTYPE;

--Defino la variable para guardar los datos del cursor

BEGIN

OPEN CI;

--Abrimos el cursor

LOOP

--Leemos con el cursor la primera fila y la guardamos en la variable.

FETCH C1 INTO VAR_CI;

-- salir si no hay datos

EXIT WHEN C1%NOTFOUND;

--también es posible:

[IF C1%NOFOUND THEN EXIT; END IF:]

....

--tratamiento de filas

END LOOP;

--Cerramos el cursor

CLOSE CI;

…..



BUCLE WHILE

En este tipo de bucle la condición se evalúa al principio.

DECLARE

--LA PARTE DECLARATIVA ES IGUAL AL OTRO TIPO DE BUCLE


VAR_CI C1%ROWTYPE;

--Defino la variable para guardar los datos del cursor

BEGIN

OPEN CI;

--Abrimos el cursor

--Leemos con el cursor la primera fila y la guardamos en la variable.


--Mientras encuentres datos se trabaja

WHILE C1%FOUND LOOP

…

--tratamiento de filas

--Antes de salir volvemos a leer.


END LOOP;

--Cerramos el cursor

CLOSE C1;

…..



BUCLE FOR

En el bucle for, no se declara la variable de cursor. No se abre si se cierra, no se

hace un FETCH, está implicito.

No se comprueba si se ha llegado al final de los datos. Es el más cómodo de los

tres, pero no siemptre se puede utilizar.

DECLARE

--LA PARTE DECLARATIVA ES IGUAL AL OTRO TIPO DE BUCLE


BEGIN

FOR V_C1 IN C1 LOOP

…..

END LOOP;

…..



FOR Loops con Subconsultas

No es necesario declarar el cursor

BEGIN

FOR emp_record IN

(SELECT apellido, departamento FROM empleados) LOOP

-- Implícitamente se abre y carga

IF emp_record.departmento = 80 THEN

......

END LOOP; -- se produce el cierre implícito

END;



Cursores Avanzados

Cursores con parámetros

A los cursores se les pueden pasar parámetros en el momento de su apertura, de

manera que se puedan abrir con distintos datos según los parámetros pasados en

diferentes aperturas.

Es un cursor variable, cuya cláusula where no es siempre la misma depende del

valor de un parámetro. Se puede abrir y cerrar las veces que queramos, y cada

vez con valores distintos.

Se puede utilizar con parámetros y con variables en la cláusula where.

Ningún parámetro debe tener longitud ni precisión.



Ejemplo:

Realizamos un tratamiento distinto para los empleados que ganen 1000 y otro

diferente para los que ganen 5000.

Primero lo abrimos y realizamos la primera operación, cerramos y realizamos la

segunda operación.

DECLARE

CURSOR C1(PARAMETRO1 NUMBER) IS

SELECT * FROM EMP

WHERE SAL < PARAMETRO1;

BEGIN

OPEN C1(1000);

….

CLOSE C1;

…

OPEN C1(5000);

…

CLOSE C1;

….

Con un bucle for, ni se abre ni se cierra, y sería:

FOR V_C1 IN C1(3000) LOOP



Cursores de Actualización (FOR UPDATE)

La cláusula FOR UPDATE bloquea las filas que se van a actualizar hasta que

concluye la transacción con COMMIT o ROLLBACK.

DECLARE

CURSOR mi_cursor IS SELECT FROM ALUMNOS FOR UPDATE;

- - FOR UPDATE debe ser la última claúsula en la sentencia

BEGIN

OPEN mi_cursor;

Ejemplo:

FOR UPDATE

DECLARE

CURSOR emp_cursor IS

SELECT employee_id, last_name, department_name

FROMemployees, departments

WHERE employees.department_id =

departments.department_id

AND employees.department_id = 80

FOR UPDATE OF salary;

WHERE CURRENT OF

Referencia la fila actual del cursor para proceder a su tratamiento sin tener que

recurrir a su índice.

Se debe utilizar junto con FOR UPDATE para bloquear las filas.

BEGIN

FOR mi_registro IN mi_cursor

LOOP

UPDATE alumnos SET edad = edad + 1

WHERE CURRENT OF mi_cursor;

END LOOP;

COMMIT;



Ejemplos:

Listar a los empleados de la tabla emp que sean analistas

1) Con bucle loop

Primero : SET SERVEROUTPUT ON;

DECLARE

CURSOR C1 IS SELECT ENAME,SAL,JOB FROM EMP WHERE JOB='ANALYST';

VAR_CI C1%ROWTYPE;

BEGIN

OPEN C1;

LOOP


DBMS_OUTPUT.PUT_LINE('ANALISTAS:' || VAR_CI.ENAME);

EXIT WHEN C1%NOTFOUND;

END LOOP;

CLOSE C1;

END;

/



2) Con bucle WHILE


DECLARE


VAR_CI C1%ROWTYPE;

BEGIN

OPEN C1;


WHILE C1%FOUND LOOP

DBMS_OUTPUT.PUT_LINE('ANALISTAS:' || VAR_CI.ENAME);


END LOOP;

CLOSE C1;

END;

/



3) Con bucle FOR


DECLARE


BEGIN

FOR VAR_C1 IN C1 LOOP

DBMS_OUTPUT.PUT_LINE('ANALISTAS:' || VAR_C1.ENAME);

END LOOP;

END;

/



Gestión de Excepciones

Manejando Excepciones

Una excepción es un identificador que se lanza durante la ejecución. Se utilizan

para controlar errores en tiempo de ejecución. Si no se contemplan y la situación

de error se produce el programa falla.

Existen las predefinidas por Oracle y las definidas por el programador.

Hablaremos también de RAISE_APPLICATION_ERROR.

Se lanzan:

• Al producirse un error en tiempo de ejecución (de manera implícita).

• Al lanzarse explícitamente (por el programador).

Para manejarla se puede:

• Atraparla con un manejador, contemplarla en el programa.

• Propagarla al bloque superior.



Características.

• Excepciones predefinidas : están recogidas en el paquete STANDARD así

NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, INVALID_CURSOR

etc.

• Excepciones definidas por el programador : se deben declarar

explícitamente en la sección DECLARE.

• Las excepciones se pueden producir en cualquier parte del programa.

• Sólo se pueden tratar de manera adecuada en la parte de tratamiento de

excepciones.

• El ámbito es el del bloque en la que se produzcan.



Generación

Las excepciones predefinidas se elevan de forma automática en el momento de

producirse. Se paraliza la ejecución y busca algún gestor de excepciones,

aplicándolo o concluyendo la ejecución en caso que no lo haya.

Tratamiento

Se tratan en el bloque EXCEPTION que puede contener un conjunto de gestores,

cada uno orientado a una excepción.

EXCEPTION

WHEN NO_DATA_FOUND THEN

<sentencias> - - Gestor 1

WHEN mi_excepcion THEN

<sentencias> - - Gestor 2

WHEN OTHERS THEN

<sentencias> - - Gestor 3, en este caso el gestor por defecto.

END;

Ejemplo excepciones del programador:

Características:

-Se deben de declarar, porque es algo que se define el programador.

-Están en la parte ejecutable del programa.

-Se tratan en el bloque de excepciones.



Ejemplo:

Bloque PL para comprobar si existen nombres nulos en la tabla EMP.

DECLARE

CURSOR CUR1 IS

SELECT ENAME,JOB,EMPNO FROM EMP;

NOMBRE_NULO EXCEPTION;

NUM_EMP EMP.EMPNO%TYPE;

BEGIN

FOR X IN CUR1 LOOP

IF X.ENAME IS NULL THEN

NUM_EMP :=X.EMPNO;

--ROMPE EL FLUJO DEL PROGRAMA, SE VA A BUSCAR LA EXCEPCIÓN A LA

--ZONA DE EXCEPCIONES

RAISE NOMBRE_NULO;

END IF;

DBMS_OUTPUT.PUT_LINE('EMPLEADO NUMERO: ' || X.EMPNO);

END LOOP;

EXCEPTION

WHEN NOMBRE_NULO THEN

DBMS_OUTPUT.PUT_LINE('EMPLEADO NUMERO' || NUM_EMP ||

'TIENE EL NOMBRE A NULOS ');

--ES NECESARIO VOLCAR EL NOMBRE EN UNA VARIABLE NUM_EMP PORQUE

--AL SALIR DEL BUCLE DEL CURSOR LA VARIABLE X PIERDE SU VALOR Y SU

–ÁMBITO

END;

/



Excepciones de Oracle.

Son las predefinidas por Oracle. Las diferencia con las anteriores:

• No se declaran, el motor de la base de datos las conoce.

• No se lanzan con Raise, el programa detecta la condición de error

Tienen en común en que se tratan en la zona de excepciones, como las anteriores.

Listado de las más usadas.

CURSOR_ALREADY_OPEN, si está ya abierto el cursor.

DUP_VAL_ON_INDEX, valor duplicado en índice, intentar insertar una fila

duplicada en una columna indexada con un índice único.

INVALID_CURSOR, ejecutar una operación con un cursor cerrado.

INVALID_NUMBER, se intenta convertir una cadena de caracteres a un numero.

LOGON_DENIED, conexión a Oracle denegada, porque usuario o contraseña son

incorrectos.

NO_DATA_FOUND, Ejecutar una sentencia select que no devuelve ninguna fila,

ejemplo: busco al empleado que gana más de 6000 euros al mes….

TOO_MANY_ROWS, Ejecutar una select que devuelve más de una fila, buscar a

los empleados que ganan entre 1000 y 1500 euros al mes.



NOT_LOGED_ON, acceder a la base de datos sin haber iniciado la sesión.

STORAGE_ERROR, pl se queda sin memoria ejecutando una sentencia.

TIMEOUT_ON_RESOURCE, se acaba el tiempo de espera para un determinado

recurso.

TRANSACTION_BACKED_OUT, una transacción por el motivo que sea no se

completa.

VALUE_ERROR, error aritmético de conversión o de truncado.

ZERO_DIVIDE, cuando se intenta dividir por cero.



Ejemplo

Pedir dos números por pantalla para realizar una división. Se contempla que el

divisor pueda ser 0.

DECLARE

--EJEMPLO DE EXCEPCION PREDEFINIDA POR ORACLE(ZERO DIVIDE)

NUM1 NUMBER(2):=&NUMERO1;


RESUL NUMBER(5,2);

BEGIN

RESUL :=NUM1/NUM2;

DBMS_OUTPUT.PUT_LINE('NUM1/NUM2 = ' || RESUL);

EXCEPTION

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE('IMPOSIBLE DIVIDIR POR 0');

END;

/



WHEN OTHERS

Existen ocasiones donde no nos interesará contemplar las excepciones una a

una. Para ello existe una excepción genérica OTHERS donde el programa bifurca

cuando se produzca una condición de error.

Ejemplo:

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('SE HA PRODUCIDO UN ERROR');

SQLCODE, SQLERRM

Es posible saber en que acceso se ha producido el error, y también podemos

saber el código y el mensaje de error, accediendo a las variables:

SQLCODE

SQLERRM

Cada error tiene su código interno y su texto de error.



Ejemplo:

Repitamos el anterior pero utilizando la excepción genérica WHEN OTHERS.

DECLARE

V_NOMBRE EMP.ENAME%TYPE;

V_EMPNO EMP.EMPNO%TYPE:=&EMPNO;

V_CODIGO NUMBER;

V_MENSAJE VARCHAR2(60);

BEGIN

SELECT ENAME INTO V_NOMBRE

FROM EMP

WHERE EMPNO=V_EMPNO;

DBMS_OUTPUT.PUT_LINE('NOMBRE: ' || V_NOMBRE);

EXCEPTION

WHEN OTHERS THEN

V_CODIGO:=SQLCODE;

V_MENSAJE:=SUBSTR(SQLERRM,1,60);

DBMS_OUTPUT.PUT_LINE('SE HA PRODUCIDO UN ERROR'

|| V_CODIGO || ' '|| V_MENSAJE);

END;

/



Propagación

Si se produce una excepción en un bloque interno y no es tratada en él, se

transmite al bloque inmediatamente superior para su tratamiento.

RAISE_APPLICATION_ERROR

Este procedimiento se puede utilizar para propagar mensajes de error

personalizados desde subprogramas almacenados.

Tiene como característica que para la ejecución del programa allá donde sea

invocado.

Tiene dos argumentos: un código que va desde -20000 hasta -20999 y un texto

de error.

Las usaremos mas adelante en triggers.

Ejemplo:

BEGIN

DELETE FROM EMP WHERE EMPNO= &NUMERO;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR (-20300, ‘EL EMPLEADO NO

EXISTE’);

END IF;

END;

/



Subprogramas

Un subprograma es un bloque PL / SQL nominado, que puede aceptar

parámetros y ser invocado desde un entorno llamante.

Provee modularidad (al estar basado en bloques PL / SQL), reusabilidad (al ser

almacenable) y facilidad de mantenimiento.

Tipos:

• Procedimientos, que realizan una acción.

• Funciones, que computan y devuelven un solo valor.

• Paquetes, conjunto de procedimientos y funciones (admiten otros objetos)

• Triggers, son especiales porque están asociados a una tabla y a una acción

sobre esa tabla.



Funciones

Una función es un bloque nominado PL / SQL que devuelve un valor.

Una función puede ser almacenada en una base de datos como un objeto para su

reutilización continuada. Si la función está almacenada se denomina global y

puede ser compartida por otros usuarios de la base de datos. Si no está

almacenada se llama local y entonces forma parte de otra unidad de

programación pero no es independiente. Veremos un ejemplo más adelante.

Una función es llamada como parte de una expresión.

Sintaxis de Creación

Función Global

CREATE [OR REPLACE] FUNCTION nombre

[(parametro1 [MODO] tipo,

parametro2 [MODO] tipo,....)]

RETURN tipo à tipo de dato del valor devuelto

IS | AS à equivalente a DECLARE en un pl anónimo

Bloque PL/SQL;

Existen en general, 3 tipos de parámetros: IN (entrada), OUT (salida),

INOUT(entrada/salida)

Puesto que las funciones devuelven valores a través de la cláusula RETURN sólo .

deberían declararse parámetros IN

El bloque PL / SQL debe contener al menos una sentencia RETURN.

Tras compilar SHOW ERRORS mostraría posibles errores de compilación.



Ejemplo:

CREATE OR REPLACE FUNCTION obten_sueldo (p_id IN empl.empno%TYPE)

RETURN NUMBER

IS

v_sueldo emp.sal%TYPE : = 0;

BEGIN

SELECT sal

INTO v_sueldo

FROM empl

WHERE empno = p_id;

RETURN v_sueldo;

END obten_sueldo;

/



Ejecución

Las funciones se invocan como parte de una expresión PL / SQL.

Se debe crear una variable para recoger el valor devuelto por la función.

Ejemplos de llamada:

1) A través de la tabla dual:

SQL> SELECT OBTEN_SUELDO(7902)

FROM DUAL; à devuelve el resultado por pantalla

2) Dentro de otro programa pl:

DECLARE

CUANTO_GANA NUMBER;

BEGIN

CUANTO_GANA:= OBTEN_SUELDO(&NUMERO_EMP);

DBMS_OUTPUT.PUT_LINE(‘GANA’ || CUANTO_GANA);

END;

/



Localización de las Llamadas

Claúsulas SELECT.

Condiciones en las claúsulas WHERE y HAVING.

Comandos CONNECT BY, START WITH, ORDER BY y GROUP BY.

En la parte VALUES de una orden INSERT.

En la sección SET de un comando UPDATE.

Restricciones en el Uso

Para poder ser invocadas desde expresiones SQL, una función definida por el

usuario debe:

• Ser una función almacenada.

• Aceptar únicamente parámetros IN.

• Aceptar únicamente tipos de datos válidos para SQL, no tipos específicos

de PL / SQL como son los parámetros.

• Devolver tipos de datos válidos para SQL.

• Funciones llamadas desde sentencias UPDATE / DELETE en una tabla T

no pueden contener DML, ni hacer consultas sobre la misma tabla T.

• Las funciones llamadas no pueden contener sentencias que finalicen las

transacciones.

• Llamadas a subprogramas que contravengan estas limitaciones

provocarían excepciones.



Ejemplo:

CREATE OR REPLACE FUNCTION va_dar_error

(p_sal NUMBER)

RETURN NUMBER IS

BEGIN

INSERT INTO empleados (nuempl, apellido, feching, nombre)

VALUES (‘00120’, ‘Perez’,’14-NOV-2001’, ‘Jose’);

RETURN (p_sal + 100);

END va_dar_error; /

UPDATE empleados SET salar = va_dar_error(12300)

WHERE nuempl = ‘00152’;

ERROR at line 1

ORA-04091 table EMPLEADOS is mutating....



Eliminación

Para borrar una función de la base de datos:

Sintaxis:

DROP FUNCTION nombre_funcion

Ejemplo:

DROP FUNCTION va_dar_error;



Funciones locales

Ejemplo:

FUNCTION SUMAR (N1 IN NUMBER,N2 IN NUMBER) RETURN NUMBER

IS

-- ESTE IS ES COMO EL DECLARE DE UN BLOQUE PL, ES DECIR, DEBAJO DE

-- EL Y ANTES DEL BEGIN VAN LAS VARIABLES DEL BLOQUE

V_RESUL NUMBER(4);

BEGIN

V_RESUL := N1+N2;

RETURN (V_RESUL);

-- PODRIA SER TAMBIEN, RETURN N1+N2

END SUMAR;

/



Ejecución:

Con un bloque pl. ¡Recordar que no está almacenada en la bd, está dentro de

otro programa!

DECLARE



V_RESULTADO NUMBER (4);

FUNCTION SUMAR (N1 IN NUMBER,N2 IN NUMBER) RETURN NUMBER

IS V_RESUL NUMBER(4);

BEGIN

V_RESUL := N1+N2;

RETURN (V_RESUL);

-- PODRIA SER TAMBIEN, RETURN N1+N2

END SUMAR;

BEGIN -- DEL BLOQUE PL

V_RESULTADO:=SUMAR(NUM1,NUM2);

DBMS_OUTPUT.PUT_LINE('EL RESULTADO ES '|| V_RESULTADO);

END;

/



Procedimientos

Un procedimiento es un bloque nominado PL / SQL que puede devolver de 0 a n

valores.

Un procedimiento puede ser almacenado en una base de datos como un objeto

para su reutilización continuada. Si está almacenado se denomina global y puede

ser compartido por otros usuarios de la base de datos. Si no está almacenado se

llama local y entonces forma parte de otra unidad de programación pero no es

independiente. Veremos un ejemplo más adelante.

Sintaxis de Creación

Procedimiento Global

CREATE [OR REPLACE] PROCEDURE nombre

[(parametro1 [IN | OUT | IN OUT] tipo, parametro2,...)]

IS | AS à equivalente a DECLARE en un pl anónimo

Bloque PL / SQL;

Existen en general, 3 tipos de parámetros: IN (entrada), OUT (salida),

INOUT(entrada/salida)

Tras compilar SHOW ERRORS mostraría posibles errores de compilación.



Tipos de Parámetros

Parámetros IN

CREATE OR REPLACE PROCEDURE subir_salario

(p_nuempl IN empleados.nuempl%TYPE)

IS

BEGIN

UPDATE empleados

SET salar = salar 1.1

WHERE nuempl = p_nuempl;

END subir_salario;

/

Parámetros OUT

CREATE PROCEDURE cuadrado_cubo (p_numero IN NUMBER , p_cuadrado OUT

NUMBER, p_cubo OUT NUMBER)

IS

BEGIN

p_cuadrado : = p_numero* p_numero;

p_cubo : = p_cuadrado * p_numero;

END cuadrado_cubo;

/

Sin parámetros

CREATE OR REPLACE PROCEDURE poner_departamentos

(p_nombre IN departamentos.nomdep%TYPE DEFAULT ‘Innominioso’, p_numero

IN departamentos.numdep%TYPE DEFAULT ‘A01’)

IS

BEGIN

INSERT INTO departamentos (NUMDEP,NOMDEP)

VALUES (p_numero, p_nombre);

END poner_departamentos;

/



Formato de Paso de Parámetros

BEGIN

poner_departamentos;

poner_departamentos (‘Formación’, ‘F99’);

poner_departamentos (p_numero => ‘AAA’, p_nombre =>’Domingo’);

poner_departamentos(p_numeros => ‘BBB’);

END;

/

SELECT numdep, nomdep FROM departamentos;

Llamadas:

1) Desde bloques anónimos

BEGIN

SUBIR_SALARIO(&NUMEMP,&SALARIO);

.......

END;

2) Desde sql*plus:

SQL> EXECUTE SUBIR_SALARIO(7902, 200);



Procedimientos locales

Ejemplo:

DECLARE

NUM1 NUMBER(2) := &NUMERO1;

NUM2 NUMBER(2) := &NUMERO2;

V_RESUL NUMBER(4);

PROCEDURE MULTIPLICAR

(N1 IN NUMBER, N2 IN NUMBER,N3 OUT NUMBER) IS

BEGIN

N3:= N1 * N2;

END MULTIPLICAR;

BEGIN

MULTIPLICAR(NUM1,NUM2,V_RESUL);

DBMS_OUTPUT.PUT_LINE('EL RESULTADO ES ' || V_RESUL);

END;

/

Es similar a lo dicho en funciones.



Eliminación

Para borrar un procedimiento de la base de datos:

• Sintaxis

DROP PROCEDURE nombre_procedimiento;

• Ejemplo

DROP PROCEDURE poner_departamentos;



Paquetes

Agrupan lógicamente subprogramas, tipos PL / SQL y elementos que son

homogéneos.

Consiste en dos partes:

• Especificación (cabecera)

• Cuerpo, desarrollo de los componentes declarados en la cabecera.

A diferencia de los procedimientos y funciones, los paquetes no pueden estar

contenidos dentro de un bloque local, sólo se pueden almacenar en la base de

datos.

Se compilan y se almacenan por separado.

La especificación de un paquete puede existir sin su cuerpo, pero lo contrario es

imposible.

Sintaxis de la cabecera

CREATE [OR REPLACE] PACKAGE nombre IS | AS

declaraciones de elementos y tipos

especificación de subprogramas

END nombre;

Las variables declaradas en la especificación del paquete se inicializan como

NULL por defecto.

Todos los elementos declarados en el paquete son visibles a los usuarios con los

privilegios concedidos para ello.

Sintaxis del Cuerpo

CREATE OR REPLACE PACKAGE BODY nombre

IS | AS

declaración de tipos y elementos privados

cuerpo de subprogramas

END nombre;



Ejemplo:

Parte declarativa de un paquete

CREATE OR REPLACE PACKAGE EJEMPLO_PAQ IS

PROCEDURE MULTIPLICAR (N1 IN NUMBER, N2 IN NUMBER);

FUNCTION SUMAR(N1 IN NUMBER,N2 IN NUMBER) RETURN NUMBER;

END EJEMPLO_PAQ;

Parte ejecutable de un paquete

CREATE OR REPLACE PACKAGE BODY EJEMPLO_PAQ IS

FUNCTION SUMAR(N1 IN NUMBER,N2 IN NUMBER) RETURN NUMBER

IS V_RESUL NUMBER(4);

BEGIN

V_RESUL := N1+N2

RETURN (V_RESUL);

END SUMAR;

PROCEDURE MULTIPLICAR (N1 IN NUMBER, N2 IN NUMBER,N3 OUT

NUMBER) IS

BEGIN

N3:= N1 * N2;

END MULTIPLICAR;

END EJEMPLO_PAQ;



Ejecutar un procedimiento o función de un paquete

1.Ejecución de un procedimiento desde sqlplus:

EXEC EJEMPLO_PAQ.MULTIPLICAR(2,9);

2.Ejecución de una función de un paquete

DECLARE

NUM1 NUMBER:=&NUM1;

NUM2 NUMBER:=&NUM2;

V_RESULTADO NUMBER;

BEGIN

V_RESUTADO:=EJEMPLO_PAQ.SUMAR(NUM1,NUM2);

DBMS_OUPUT.PUT_LINE ('EL RESUTADO ES' ||V_RESULT);

END;



Eliminación

Para eliminar tanto el cuerpo como la especificación:

DROP PACKAGE mi_paquete;

Para eliminar sólo el cuerpo:

DROP PACKAGE BODY mi_paquete;



Triggers

En esta sección veremos triggers que son bloques o procedimientos PL / SQL

asociados a una tabla. Se disparan al producirse un evento (sentencia DML)

sobre una tabla.

Se asemejan con los procedimientos y funciones en que son bloques PL/SQL

nominados con secciones declarativa, ejecutable y de manejo de excepciones y en

que se almacenan en la base de datos.

Se diferencian en que se ejecutan implícitamente cuando se ejecuta una

sentencia de manipulación de datos sobre una tabla (INSERT, UPDATE,

DELETE), y en que el disparador no admite argumentos.

Existen otros tipos de triggers que no serán vistos en esta sección.



Uso de Triggers

Los disparadores se utilizan principalmente para:

• Para el mantenimiento de restricciones de integridad complejas, que no

sean posibles con las restricciones declarativas definidas en el momento de

crear la tabla.

• En auditoría de la información contenida en la tabla, registrando los

cambios realizados y la identidad de quien lo llevó a cabo.

• El aviso automático a otros programas de que hay que llevar a cabo una

determinada acción, cuando se realiza un cambio en la tabla.



Timing de los triggers

• BEFORE: se ejecuta antes que el evento DML que ha lo ha lanzado.

• AFTER: se ejecuta después que el evento que lo origina.

• INSTEAD OF; se ejecuta el cuerpo del trigger en vez de la sentencia

que lo lanza. Se emplea en vistas que no son modificables de otra

manera. No es muy común.



Tipos de trigger

La tipología depende de la frecuencia con la que queremos que se ejecute, así:

• A nivel de sentencia: el cuerpo del trigger se ejecuta una única vez al

producirse el evento. Es el por defecto. Se dispara aunque no haya

filas afectadas.

• A nivel de fila: el cuerpo se ejecuta una vez por cada fila afectada por el

evento, no se dispara si el evento no afecta a fila alguna.

Secuencia de disparo

En caso de concurrencia de eventos, la secuencia de disparo los triggers, sería:

• BEFORE statement trigger

• BEFORE row trigger (n veces)

• AFTER row trigger (n veces)

• AFTER Statement trigger

RESTRICCIONES

Hay determinadas expresiones que no se pueden poner en el cuerpo de un

TRIGGER:

• COMMIT, ROLLBACK, SAVEPOINT.

• No pueden realizar modificaciones de datos (INSERT, UPDATE,

DELETE) sobre una tabla mutante (aquella que está siendo modificada

por la instrucción que disparó el TRIGGER).



Una sentencia de trigger DML contiene:

• Condición de tiempo : BEFORE, AFTER

• Un evento: INSERT, UPDATE o DELETE.

• Una tabla: ON nombre_tabla | nombre_vista.

• Un tipo: sentencia o fila.

• Una claúsula WHEN : condición restrictiva.

• El cuerpo del trigger.

• CREATE [OR REPLACE] TRIGGER nombre

timing

event1 [OR event2] [OR event3]

ON nombre_tabla

trigger_body

Los nombres de los triggers deben ser únicos con respecto a otros triggers del

mismo esquema.



Ejemplos:

Disparador de sentencia:

CREATE OR REPLACE TRIGGER EJEMPLO1 AFTER INSERT ON DEPT

BEGIN

-- DISPARADOR DE SENTENCIA

DBMS_OUTPUT.PUT_LINE('SE HA INSERTADO UN REGISTRO');

END;

/

Disparador de fila:

OLD y NEW

En un trigger a nivel de fila, se puede referenciar el valor de una columna antes y

después del cambio de datos.

CREATE OR REPLACE TRIGGER EJEMPLO2

BEFORE UPDATE ON DEPT

FOR EACH ROW --DISPARADOR DE FILA

BEGIN

DBMS_OUTPUT.PUT_LINE('SE SUSTITUIRA LA SIGUIENTE FILA: ');

DBMS_OUTPUT.PUT_LINE(:OLD.DEPTNO || ' ' || :OLD.DNAME ||' '||

:OLD.LOC);

DBMS_OUTPUT.PUT_LINE('POR ESTA OTRA:');

DBMS_OUTPUT.PUT_LINE(:NEW.DEPTNO ||' '||:NEW.DNAME||'

'||:NEW.LOC);

END;

/



Restricciones a nivel de fila

Para restringir la acción del trigger a aquellas filas que satisfagan una

determinada condición se emplea la cláusula WHEN

CREATE OR REPLACE TRIGGER tigre2 BEFORE INSERT OR UPDATE OF

salary ON employees FOR EACH ROW

WHEN (NEW.job_id = ‘SA_REP’)

BEGIN

IF INSERTING THEN :NEW.commission_pct := 0;

ELSIF :OLD.commission_pct IS NULL

THEN :NEW.commission_pct := 0;

ELSE :NEW.commission_pct := :OLD.commission_pct + 0.05;

END IF;

END;



Gestión de Triggers

Al crearse un TRIGGER se activa, se puede desactivar con la orden:

SQL> ALTER TRIGGER Vigila DISABLE;

Se puede volver a activar:

SQL> ALTER TRIGGER Vigila ENABLE;

La tabla USER_TRIGGERS es útil para conseguir información sobre los

disparadores.

SQL> SELECT TRIGGER_NAME, TRIGGER_TYPE , TRIGGERING_EVENT

FROM USER_TRIGGER;

Borrado

La claúsula DROP permite eliminar un trigger de la base de datos:

DROP TRIGGER Vigila;



Más acerca de triggers

Integridad de los datos con triggers

Los triggers pueden complementar las herramientas propias del sistema, dando

valores por defecto, implementando chequeos adicionales,...

CREATE OR REPLACE TRIGGER check_salary BEFORE UPDATE OF

salary ON employees FOR EACH ROW

WHEN (NEW.salary < OLD.salary)

BEGIN

RAISE_APPLICATION_ERROR(-20508,’No bajar el salario’);

END;