71946780 Manual Pl SQL Espanol

PL/SQL I

VERSIN 1 JPV-99 1 / 67

PPLL//SSQQLL II

Javier Prez-Vigo (Octubre 1999)

PL/SQL I


INTRODUCCIN

ORACLE es un sistema de base de datos relacional potente y flexible.

El lenguaje de programacin PL/SQL est diseado para manipular los

datos desde distintos entornos.

PL/SQL est integrado con el servidor de bases de datos y est en

algunas herramientas de cliente ORACLE, (Developer).

Su conocimiento es imprescindible para la manipulacin de informacin

almacenada en bases de datos ORACLE y para el desarrollo de

cualquier aplicacin en ese entorno.

En esta unidad didctica se definen las principales caractersticas de

este lenguaje y su sintaxis, se enumeran las principales funciones

predefinidas.

As mismo se estudian las rdenes SQL disponibles: de control, de

manipulacin de datos y transaccionales.

Posteriormente se introduce el concepto de cursor y por ltimo se explica

cmo se disea el tratamiento de errores.

PL/SQL I


CONTENIDO

1. DEFINICIN Y CARACTERSTICAS DE PL/SQL

2. EL BLOQUE PL/SQL

ZONA DE DECLARACIN: DECLARACIN DE VARIABLES YCONSTANTES

ZONA DE PROCESO

ZONA DE EXCEPCIONES

3. FUNCIONES SQL PREDEFINIDAS

FUNCIONES DE CARCTER QUE DEVUELVEN VALORESDE CARCTER

FUNCIONES DE CARCTER QUE DEVUELVEN VALORESNUMRICOS

FUNCIONES NUMRICAS

FUNCIONES DE FECHA

FUNCIONES DE CONVERSIN

FUNCIONES DE GRUPO

OTRAS FUNCIONES

4. SENTENCIAS DE CONTROL

5. SENTENCIAS DE DML

6 CURSORES

MANEJO DE CURSORES

BUCLES SOBRE CURSORES

EJEMPLO DE CURSORES

7. REGISTROS Y TABLAS

REGISTROS PL/SQL

TABLAS EN PL/SQL

8. SENTENCIAS TRANSACCIONALES

9 CONTROL DE ERRORES

10 RESUMEN

11 EVALUACIN

PL/SQL I


OBJETIVOS

El alumno tras el estudio de esta unidad didctica podr:

Conocer la sintaxis y los elementos que componen el lenguaje

PL/SQL.

Consultar y manipular la informacin almacenada en servidores de

Bases de Datos Oracle.

Desarrollar estructuras bsicas de programacin en PL/SQL.

EXPECTATIVAS

PL/SQL I


1. DEFINICIN Y CARACTERSTICAS DE PL/SQL

Combina la flexibilidad de SQL con la potencia y configurabilidad de un 3GL al integrar

tanto las necesarias estructuras procedimentales como el acceso a bases de datos.

Nos permite utilizar tcnicas procedurales como bucles, control de flujo, tratamiento de

variables y control de errores en el procesamiento de las filas devueltas en una

SELECT.

Permite tener cdigo ejecutable almacenado en el servidor de base de datos.

PL/SQL, (Procedural Language / SQL), es un lenguaje de programacin

procedural estructurado en bloques que amplia la funcionalidad del lenguaje

standard SQL a travs de estructuras como:

Variables y tipos, (predefinidos y definidos por el usuario)

Estructuras de control, (bucles y condiciones)

Procedimientos y funciones

Tipos de objetos y mtodos, (a partir de versin 8)

IMPORTANTE

DQL. Data Query Language (select)

DML Data manipulation language (insert)

DDL Data definition language (create table)

NOTA

PL/SQL I


Es un lenguaje estructurado en bloques: Bloque es la unidad bsica en PL/SQL

Incorpora sentencias DML (INSERT, UPDATE, DELETE)

Maneja diversas estructuras de datos (registros, arrays)

Dispone de:

Procedimientos y funciones (igual que en C por ejemplo).

Paquetes (Grupos de identificadores, variables y funciones y/o procedimientos

agrupados en una misma construccin, cuya finalidad es el encapsulamiento,

la abstraccin y el control de acceso).

Disparadores (Eventos que se ejecutan cuando ocurre una operacin de

manipulacin de datos sobre una tabla)

La versin objeto de estudio ser la 2.3.

NOTA

Tambin se pueden incorporar rdenes DDL mediante SQL Dinmico.

NOTA

PL/SQL I


2. EL BLOQUE PL/SQL

Todos los bloques tienen tres secciones diferenciadas:

Seccin declarativa.

Seccin ejecutable, (la nica obligatoria).

Seccin de excepciones.

EESSTTRRUUCCTTUURRAA::

[DECLARE]

Declaracin de variables y constantes

Variable

Constante Se inicializa con un valor que no se puede modificar.

Cursor Area de trabajo que contiene los datos de la fila de la tabla en

uso. El cursor es el resultado de una sentencia SELECT.

ExcepcinVariables para control de errores.

El Bloque PL/SQL es la unidad bsica en todo programa PL/SQL.

Un programa PL/SQL ser un conjunto de bloques PL/SQL, situados de

manera secuencial o anidados

IMPORTANTE

PL/SQL I


BEGIN

Sentencias ejecutables y sentencias DML

[EXCEPTION]

Control de excepciones

Es el punto al que se transfiere el control del programa siempre que exista un

problema. Los indicadores de excepcin pueden ser definidos por el usuario o por el

sistema, como es por ejemplo la excepcin ZERO_DIVIDE. Las excepciones se

activan automticamente al ocurrir un error, existiendo la definicin de la excepcin

OTHERS que considera aquellos errores no definidos y que siempre se ubica al final

de todas las excepciones.

END; [Nombre del bloque]

Fin del Bloque.

ZZOONNAA DDEE DDEECCLLAARRAACCIINN:: DDEE VVAARRIIAABBLLEESS YY CCOONNSSTTAANNTTEESS

Las variables son utilizadas para guardar valores devueltos por una consulta o

almacenar clculos intermedios.

Las constantes son campos que se definen y no alteran su valor en el proceso.

Nombre_campo [CONSTANT] Tipo de dato := valor_constante / expresin_pl_sql

Este tipo de bloque se le suele llamar annimo, y es el ms utilizado en Scripts.

NOTA

PL/SQL I


Otra manera de declarar las variables: identificador%TYPE.

Donde el identificador es un objeto accesible por el usuario. Normalmente una

columna de una tabla:

Se puede tambin declarar una fila variable cuyos campos tienen el mismo nombre y

tipo que las columnas de una tabla o fila recuperada de un cursor:

Es obligatorio slo en bloques PL/SQL annimos.

Var1 number(59);

Var2 Var1%TYPE;

Var3 tabla.identificador%TYPE;

registro tabla%ROWTYPE

PL/SQL I

VERSIN 1 JPV-99 10 / 67

TIPOS DE DATOS.

Number (n [, d]) numrico n

PL/SQL I

VERSIN 1 JPV-99 11 / 67

ZZOONNAA DDEE PPRROOCCEESSOO

Empieza con BEGIN.

En esta zona es donde se escriben todas las sentencias ejecutables: sentencias DML,

asignaciones, manejo de cursores, bucles, etc.

Es importante recordar que todas las SELECT ejecutadas dentro de un bloque PL/SQL

llevan asociado un INTO.

Una SELECT que no devuelva datos o que devuelva ms de uno provoca un ERROR,

saltando la ejecucin directamente al EXCEPTION.

ASIGNACIN DE VALORES

Las dos formas que existen para asignar valores a variables de memoria, son:

- Con el operador de asignacin :=, como cuando calculamos el promedio de las

ventas asignndole valor a la variable xprom con la siguiente sentencia:

xprom:=xtotal/xcant;

- Con la sentencia SELECT que contiene la orden INTO, como se muestra, es la

asignacin de valores a las variables xtotal y xcant con el siguiente cdigo:

SELECT SUM(valor),

COUNT(valor)

INTO xtotal,xcant

FROM ventas

WHERE fecha=sysdate;

FIN DE BLOQUE PL/SQL

Todo bloque PL/SQL termina con END.

PL/SQL I

VERSIN 1 JPV-99 12 / 67

COMENTARIOS

Hay dos tipos de comentarios:

Con el signo -- se indica comentario de una sola lnea.

Mediante /* al principio del comentario y */ al final de ste.

ZZOONNAA DDEE EEXXCCEEPPCCIIOONNEESS

Las excepciones saltan cuando se ha producido un error en la ejecucin.

Es seccin no obligatoria. Esta seccin termina con un END.

Es la parte del bloque PL/SQL donde se realiza el control de errores.

Manejo directo de excepciones ya tratadas con el uso de un WHEN:

Excepciones que pueden incluirse directamente despus del WHEN:

Excepcin Nmero de error

CURSOR_ALREADY_OPEN ORA06511 6511

DUP_VAL_ON_INDEX ORA00001 1

INVALID_CURSOR ORA01001 1001

INVALID_NUMBER ORA01722 1722

LOGIN_DENIED ORA01017 1017

NO_DATAFOUND ORA01403 +100

NOT_LOGGED_ON ORA01012 1012

PROGRAM_ERROR ORA06501 6501

PL/SQL I

VERSIN 1 JPV-99 13 / 67

ROWTYPE_MISMATCH ORA06504 6504

STORAGE_ERROR ORA06500 6500

TIMEOUT_ON_RESOURCE ORA00051 51

TOO_MANY_ROWS ORA01422 1422

VALUE_ERROR ORA06502 6502

ZERO_DIVIDE ORA01476 1476

EJERCICIO N 1

Crear un bloque PL/SQL con las siguientes caractersticas:

Definir una constante de tipo numrico, de nombre x, a la que le vamos aasignar el valor de 12.

Definir una variable, y, del mismo tipo que el campo hiredate, de la tablaEMP, de la base de datos DEMO.

Definir una variable de tipo numrico que se llame z.

Asignar a la variable y la fecha actual. Asignar a la variable z el resultadode multiplicar x por 10.

Mostrar por pantalla el contenido de las variables y e z.

EJERCICIO

PL/SQL I

VERSIN 1 JPV-99 14 / 67

EJERCICIO N 2

Se trata de hacer una consulta a la tabla EMP, concretamente del campoename de tal forma que el usuario introduzca un nombre por teclado y el bloquePL/SQL sea capaz de recuperar el campo job de dicho empleado a travs dedicha consulta.

Pedir que se introduzca por teclado un nombre, el cual loalmacenaremos en la variable nombre.

Una vez introducido el nombre, lanzar un bloque PL/SQL (previamentecreado) de tal forma que sea capaz de seleccionar la fila de la tabla EMPcuyo ename sea igual al introducido por teclado y mostrar el campo jobcorrespondiente a ese empleado por pantalla.

EJERCICIO

PL/SQL I

VERSIN 1 JPV-99 15 / 67

3. FUNCIONES SQL PREDEFINIDAS

FFUUNNCCIIOONNEESS DDEE CCAARRCCTTEERR QQUUEE DDEEVVUUEELLVVEENN VVAALLOORREESS DDEE

CCAARRCCTTEERR

CHR Chr(x)

Devuelve el carcter dado su nmero ASCII .

CONCAT Concat (cadena1, cadena2)

Devuelve las dos cadenas encadenadas (Es idntico a usar ||).

INITCAP Initcap (cadena)

Devuelve la misma cadena, con el primer carcter de cada palabra en

mayscula y el resto en minscula.

LOWER Lower (cadena)

Devuelve la misma cadena con todos los caracteres en minscula.

LPAD Lpad (cadena1,x [,cadena2])

Devuelve la cadena1 rellena hacia la izquierda con los caracteres de la

cadena2 hasta completar la longitud x

Existen diversas funciones predefinidas a las que se puede llamar desde

una orden SQL.

IMPORTANTE

PL/SQL I

VERSIN 1 JPV-99 16 / 67

LTRIM Ltrim(cadena1, cadena2)

Devuelve la cadena1, quitando los caracteres de la izquierda contenidos

en cadena2.

REPLACE Replace (cadena, cad_busc [,cad_subst])

Devuelve cadena, reemplazando todas las apariciones de la cadena

cad_busc con la cadena cad_subst. Si no se especifica cad_subst, se

elimina todas las apariciones de cad_busc.

RPAD Rpad (cadena1, x [,cadena2])

Devuelve la cadena1 rellena por la derecha con los caracteres de la

cadena2 hasta completar la longitud x

RTRIM Ltrim(cadena1, cadena2)

Devuelve la cadena1, quitando los caracteres de la derecha contenidos

en cadena2.

SUBSTR Substr (cadena, a [,b])

Devuelve una porcin, de b caracteres de longitud, de la cadena

proporcionada como argumento, empezando a partir de la posicin a. S

a es cero, se trata como si fuera 1. Si a es positivo, se devuelven los

caracteres contando a partir de la izquierda. Si a fuera negativo, se

contara desde el final de la cadena.

UPPER Upper (cadena)

Devuelve la cadena proporcionada con todas las letras en maysculas.

PL/SQL I

VERSIN 1 JPV-99 17 / 67

FFUUNNCCIIOONNEESS DDEE CCAARRCCTTEERR QQUUEE DDEEVVUUEELLVVEENN VVAALLOORREESS

NNUUMMRRIICCOOSS

ASCII ASCII (cadena)

Devuelve la representacin decimal del primer byte de la cadena.

INSTR INSTR (cadena1, cadena2 [,a[,b]])

Devuelve la posicin de la cadena1 donde est contenida la cadena2.

(La cadena se explora desde la izquierda, empezando a partir de la

posicin a. S a es negativo, entonces la cadena1 se explora desde la

derecha. El valor devuelto es el de la aparicin nmero b de la cadena

de la bsqueda).

SQL> SELECT LPAD (Short String, 20, XY) Cadena FROM dual;

> XYXYXYXYShort String

SQL> SELECT LTRIM (xxxFin de la cadena, x) Cadena FROM dual;

> Fin de la cadena

SQL> SELECT REPLACE (This and That, Th, B) Cadena FROM dual;

> Bit and Bat

SQL> SELECT SUBSTR (abc123def, 5, 4) Cadena FROM dual;

> 23de

PL/SQL I

VERSIN 1 JPV-99 18 / 67

LENGTH Length (cadena)

Devuelve la longitud de la cadena.

FFUUNNCCIIOONNEESS NNUUMMRRIICCAASS

ABS ABS (x)

Devuelve el valor absoluto de x

ACOS ACOS (x)

Devuelve el arco coseno de x

ASIN ASIN (x)

Devuelve el arco seno de x

ATAN ATAN (x)

Devuelve el arco tangente de x

CEIL CEIL (x)

Devuelve el menor entero que sea mayor o igual que x

COS COS (x)

Devuelve el coseno de x (en radianes)

SQL> SELECT INSTR(Scotts spot, ot, 1, 2) Cadena FROM dual;

> 11

PL/SQL I

VERSIN 1 JPV-99 19 / 67

COSH COSH (x)

Devuelve el coseno hiperblico de x

EXP EXP (x)

Devuelve e elevado a la potencia x

FLOOR FLOOR (x)

Devuelve el mayor entero que sea menor o igual que x

LN LN (x)

Devuelve el logaritmo natural de x

LOG LOG (x,y)

Devuelve el logaritmo de y en base x

MOD MOD (x)

Devuelve el resto resultante de dividir z por y

POWER POWER (x,y)

Devuelve el nmero x elevado a la potencia de y

ROUND ROUND (x [,y])

Devuelve x redondeado a y posiciones a la derecha del punto decimal.

SIN SIN (x)

Devuelve el seno de x (en radianes)

SINH SINH (x)

Devuelve el seno hiperblico de x

SQRT SQRT (x)

Devuelve la raiz cuadrada de X

PL/SQL I

VERSIN 1 JPV-99 20 / 67

TAN TAN (x)

Devuelve la tangente de x (en radianes)

TANH TANH (x)

Devuelve la tangente hiperblica de x (en radianes)

TRUNC TRUNC (x)

Devuelve el valor x truncado a y posiciones decimales

FFUUNNCCIIOONNEESS DDEE FFEECCHHAA

ADD_MONTHS ADD_MONTHS (d,x)

Devuelve la fecha d ms x meses

LAST_DAY LAST_DAY (d)

Devuelve la fecha del ltimo da del mes al que d pertenece

MONTHS_BETWEEN MONTHS_BETWEEN (fecha1, fecha2)

Devuelve el nmero de meses existentes entre fecha1 y fecha2

SQL> SELECT TRUNC(15.79,1) Truncate FROM DUAL ;

> 15.7

SQL> SELECT MIN(hiredate) Minimum Date FROM emp ;

> 17-DEC-80

PL/SQL I

VERSIN 1 JPV-99 21 / 67

NEXT_DAY NEXT_DAY(d, cadena)

Devuelve la fecha del da nombrado en cadena que es posterior a la

fecha d

ROUND ROUND (d [, formato])

Redondea la fecha d a la unidad especificada.

SYSDATE SYSDATE

Devuelve la fecha y hora actuales, de tipo DATE

TRUNC TRUNC (d [, formato])

Devuelve la fecha d, truncada a la unidad especificada.

SQL> SELECT TO_CHAR(ADD_MONTHS(hiredate,1),

'DD-MON-YYYY') "Next month"

FROM emp

WHERE ename = 'SMITH' ;

> 17-JAN-1981

SQL> SELECT TO_CHAR(

ADD_MONTHS(

LAST_DAY(hiredate),5),

'DD-MON-YYYY') "Five months"

FROM emp

WHERE ename = 'MARTIN' ;

> 28-FEB-1982

PL/SQL I

VERSIN 1 JPV-99 22 / 67

FFUUNNCCIIOONNEESS DDEE CCOONNVVEERRSSIINN

CONVERSIONES IMPLCITAS

PL/SQL Convierte automticamente entre los siguientes tipos de datos:

CONVERSIONES EXPLCITAS

Estas son las conversiones entre tipos que pueden hacerse:

TO_CHAR TO_CHAR (d [,formato [, paramls]])

Convierte la fecha d a una cadena de caracteres

TO_DATE TO_DATE (d [,formato [, paramls]])

Convierte la cadena de caracteres a una fecha

PL/SQL I

VERSIN 1 JPV-99 23 / 67

TO_NUMBER TO_NUMBER (d [,formato [, paramls]])

Convierte la cadena a un valor numrico.

SQL> SELECT TO_DATE(

'January 15, 1989, 11:00 A.M.',

'Month dd, YYYY, HH:MI A.M.',

'NLS_DATE_LANGUAGE = American')

FROM DUAL;

> 15/01/1989,11:00:00

SQL> SELECT TO_CHAR(HIREDATE, 'Month DD, YYYY')

"New date format"

FROM emp

WHERE ename = 'SMITH' ;

> December 17, 1980

SQL> SELECT TO_NUMBER('-AusDollars10000','L9G99999D99',

' NLS_NUMERIC_CHARACTERS = '',.''

NLS_CURRENCY = ''AusDollars'' ') "Amount"

FROM DUAL;

> -10000

PL/SQL I

VERSIN 1 JPV-99 24 / 67

FORMATOS NUMRICOS

Element Example Description

9 9999 significant digit

0 0999 leading zero instead of a blank

$ $999 leading dollar sign

B B999 leading blank instead of a zero

MI 999MI trailing minus sign

S S999 leading plus or minus sign

PR 999PR angle brackets for negative values

D 99D99 decimal character

G 9G99 group separator

C C999 ISO currency symbol

L L999 local currency symbol

, 9,999 comma

. 99.99 period

V 999V99 10n multiplier; n is the number of 9's after the V

EEEE 9.99EEEE scientific notation (you must have 4 E's)

RN, rn RN Upper or lower case roman numeral

PL/SQL I

VERSIN 1 JPV-99 25 / 67

FORMATOS DE FECHA:

CC, SCC Century (S prefixes BC dates with a minus sign)

YYYY, SYYYY Year (S prefixes BC dates with a minus sign)

IYYY Year based on ISO standard

YYY, YY, Y Last three, two or one digits of the year

IYY, IY, I Last three, two or one digits of the ISO year

Y,YYY (Four Y's with comma) put a comma in the year (1,995)

YEAR, SYEAR Year spelled out (S prefixes BC dates with a minus sign)

RR Last two digits of year in another century (allows for year 2000)

BC, AD BC or AD indicator

B.C., A.D. BC or AD indicators with periods

Q Numeric quarter of the year (1-4 with Jan-Mar=1)

MM 2 number month (Jan = 01)

RM Roman numeral month

MONTH Name of month spelled out (upper case - month is upper case)

MON abbreviated name of month (upper case - month is upper case)

WW Week of the year (1-53)

IW Week of the year (1-52, 1-53) based on ISO standard

W Week of month (1-5)

DDD day of year (1-366) (Don't forget leap years)

DD day of month (1-31)

PL/SQL I

VERSIN 1 JPV-99 26 / 67

D day of week (1-7)

DAY Name of day (upper case, day is upper case)

DY Abbreviated name of day

J Julian day (Number of days since January 1, 4712 BC)

AM,PM Meridian indicator

A.M., P.M. Meridian indicator with periods.

HH, HH12 Hour of day (0-12)

HH24 Use 24 hour clock for hours (1-24)

MI Minute (0-59)

SS Second (0-60)

SSSSS (five S's) seconds past midnight. (0-86399)

None Date must be in the format 'DD-MON-YY';

FFUUNNCCIIOONNEESS DDEE GGRRUUPPOO

AVG AVG ([Distinct | All] col)

Devuelve el promedio de los valores de la columna

COUNT COUNT (* | [Distinct |all] col)

Devuelve el nmero de filas en la consulta

MAX MAX ([Distinct | All] col)

Devuelve el valor mximo de la consulta de seleccin.

MIN MIN ([Distinct | All] col)

Devuelve el valor mnimo de la consulta de seleccin.

PL/SQL I

VERSIN 1 JPV-99 27 / 67

STDDEV STDDEV ([Distinct | All] col)

Devuelve la desviacin estndar del elemento de lista de seleccin.

SUM SUM ([Distinct | All] col)

Devuelve la suma de los valores para el elemento de lista de seleccin.

VARIANCE VARIANCE ([Distinct | All] col)

Devuelve la varianza estadstica del elemento de lista de seleccin.

OOTTRRAASS FFUUNNCCIIOONNEESS

DECODE DECODE (expr_base, compara1, valor1,

compara2, valor2

predeterminado)

La funcin DECODE es similar a una serie de rdenes IF-THEN-ELSE

anidadas. La expresin expr_base se compara sucesivamente con

compara1, compara2, etc. Si expr_base coincide con el elemento de

comparacin nmero i, se devuelve el valor i. Si expr_base no coincide

con ninguno de los valores de comparacin, se devuelve el valor

predeterminado.

GREATEST GREATEST (expr1 [expr2] )

Devuelve la expresin con mayor valor de todos sus argumentos. Cada

expresin es convertida de forma implcita al tipo de expr1 antes de

realizar las comparaciones. Si expr1 es de tipo carcter, se realizan

comparaciones de carcter sin relleno de blancos, y el resultado es de

tipo VARCHAR2.

PL/SQL I

VERSIN 1 JPV-99 28 / 67

LEAST LEAST (expr1 [expr2] )

Devuelve la expresin con menor valor de todos sus argumentos. Cada

expresin es convertida de forma implcita al tipo de expr1 antes de

realizar las comparaciones. Si expr1 es de tipo carcter, se realizan

comparaciones de carcter sin relleno de blancos, y el resultado es de

tipo VARCHAR2.

NVL NVL (expr1 ,expr2)

Si el valor de expr1 es NULL, la funcin devuelve el valor de expr2; si

no, devuelve el valor de expr1. El valor devuelto es del mismo tipo que

expr1, salvo si expr1 es una cadena de caracteres, en cuyo caso el

valor de retorno es del tipo VARCHAR2.

USER USER

Devuelve un valor VARCHAR2 que contiene el nombre del usuario

actual de Oracle.

USERENV USERENV (opcin)

Devuelve un valor VARCHAR2 que contiene informacin acerca de la

sesin actual, segn la opcin seleccionada.

OPCIONES

LANGUAGE Devuelve el idioma y pas utilizados

SESSIONID Devuelve el identificador del elemento de registro

de la sesin

TERMINAL Devuelve un identificador, dependiente del

sistema operativo, para el terminal de la sesin actual.

PL/SQL I

VERSIN 1 JPV-99 29 / 67

SQL> SELECT ename, NVL(TO_CHAR(COMM),'NOT APPLICABLE')

"COMMISSION"

FROM emp

WHERE deptno = 30;

>ENAME COMMISSION

---------- -----------------

ALLEN 300

WARD 500

MARTIN 1400

BLAKE NOT APPLICABLE

TURNER 0

JAMES NOT APPLICABLE

SQL> SELECT TEXT

FROM USER_SOURCE

WHERE NAME = UPPER('&1')

ORDER BY NAME, DECODE(TYPE,'PACKAGE','S','PACKAGE BODY','B',

'PROCEDURE','R','FUNCTION','F'),TYPE,LINE;

SQL> SELECT USERENV('sessionid'), USERENV ('terminal'), USER FROM

dual;

> 6234 JPVF SCOTT

PL/SQL I

VERSIN 1 JPV-99 30 / 67

EJERCICIO N 3

-Crear una tabla empleado como copia de la tabla emp. El nuevo nombre ser

emp + n de usuario.

-Modificar los registros de la tabla creada de modo que en lugar del nombre del

empleado aparezca el nombre del empleado terminado en @ para aquellos

empleados llamados JONES cuyo curso es el el 2

EJERCICIO N 4

-Hallar el da de la semana actual en castellano en letra mediante un DECODE

y sin utilizar la mscara d.

EJERCICIO N 5

- Obtener las nuevas comisiones de los empleados teniendo en cuenta que se

subirn un 5% el prximo ao y que aquellos que no tengan les correspondern

como tales la cuarta parte de su salario.

PRCTICA

PL/SQL I

VERSIN 1 JPV-99 31 / 67

4. SENTENCIAS DE CONTROL

SSEENNTTEENNCCIIAA IIFF

IF Condicin THEN sentencias_ejecutables

[ELSIF condicin THEN sentencias_ejecutables; ]

ELSE sentencias_ejecutables; ]

END IF;

SQL> DECLARE

v_NumberSeats rooms.number_seats%TYPE;

v_Comment VARCHAR2(35);

BEGIN

/* Recibe el nmero de asientos de la sala identificada por ID 99999.

Almacena resultado en v_NumberSeats. */

SELECT number_seats

INTO v_NumberSeats

FROM rooms

WHERE room_id = 99999;

IF v_NumberSeats < 50 THEN

v_Comment := 'Fairly small';

ELSIF v_NumberSeats < 100 THEN

v_Comment := 'A little bigger';

ELSE

v_Comment := 'Lots of room';

PL/SQL I

VERSIN 1 JPV-99 32 / 67

BBUUCCLLEE IINNFFIINNIITTOO::

LOOP

sentencias_ejecutables;

[EXIT when ....;]

END LOOP;

END IF;

END;

/

SQL> DECLARE

ranking_level NUMBER(3) := 1;

BEGIN

IF max_rank_in >= 1

THEN

LOOP

set_rank (ranking_level);

ranking_level := ranking_level + 1;

EXIT WHEN ranking_level > max_rank_in;

END LOOP;

END IF;

END;

/

PL/SQL I

VERSIN 1 JPV-99 33 / 67

BBUUCCLLEESS CCOONNDDIICCIIOONNAALLEESS::

WHILE condicin

LOOP


[EXIT when .... ;]

END LOOP;

SQL> DECLARE

v_Counter BINARY_INTEGER := 1;

BEGIN

-- Permanece dentro del bucle hasta

-- comprobar que se han hecho 50 iteraciones

WHILE v_Counter

PL/SQL I

VERSIN 1 JPV-99 34 / 67

BBUUCCLLEESS NNUUMMRRIICCOOSS::

FOR ndice IN [REVERSE] nmero_inicio..nmero_fin

LOOP


[EXIT when ....;]

END LOOP;

SQL> DECLARE

v_Counter NUMBER := 7;

BEGIN

Inserta el valor 7 en temp_table.

INSERT INTO temp_table (num_col)

VALUES (v_Counter);

Este bucle declara de nuevo v_Counter como BINARY_INTEGER, lo que

anula la declaracin NUMBER de v_Counter.

FOR v_Counter IN 20..30 LOOP

-- Dentro del bucle, el rango de v_Counter es de 20 a 30.


VALUES (v_Counter);

END LOOP;

PL/SQL I

VERSIN 1 JPV-99 35 / 67

-- Inserta otro 7 en temp_table.


VALUES (v_Counter);

END;

/

EJERCICIO 7

EJERCICIO SOBRE BUCLES CONDICIONALES

Crear una tabla DIAS con un campo de tipo numrico.

Introducir una fila por cada da del mes actual de tal forma que el resultado

final sea algo parecido a esto:

DAS

1

2

3

4

5

...

29

30

Realizar el ejercicio utilizando for...loop , loop...exit y while.

EJERCICIO

PL/SQL I

VERSIN 1 JPV-99 36 / 67

5. SENTENCIAS DE DML

Atributos de estos cursores:

SQL%NOTFOUND Devuelve TRUE si el INSERT, UPDATE o DELETE

no procesa ninguna fila.

SQL%FOUND Devuelve TRUE si un INSERT, UPDATE o DELETE ha

procesado alguna fila

SQL%ROWCOUNT Devuelve el nmero de fila leda procesadas por un

INSERT, UPDATE o DELETE.

Para el manejo de errores de una SELECT utilizaremos las excepciones

NO_DATA_FOUND, TOO_MANY_ROWS, etc.

PL/SQL puede incluir sentencias de manejo de datos.

Oracle abre un cursor implcito por cada sentencia SQL que tenga que

procesar.

Nos referiremos a l como SQL%

IMPORTANTE

PL/SQL I

VERSIN 1 JPV-99 37 / 67

SQL> update emp

set salary = salary + 100

where name = SMITH;

if SQL%NOTFOUND then

dbms_output.put_line(Smith no trabaja ya en la empresa);

end if;

EJERCICIO 8

EJERCICIO SOBRE SENTENCIAS DML

Introducir desde teclado el nmero de un departamento y mostrar por

pantalla el nombre de dicho departamento y domicilio.

Utilizar la tabla DEPT y un registro para recuperar los datos de dicha tabla.

Probar que ocurre si se introduce un nmero de departamento que no figura

en la tabla DEPT.

EJERCICIO

PL/SQL I

VERSIN 1 JPV-99 38 / 67

EJERCICIO 9

Crear un bloque PL/SQL que modifique la tabla EMP de tal forma aquellos

empleados cuyo nmero de empleado sea 1 pasen a tener como job

CLERK.

Si no existiera ningn empleado con nmero 1, insertar dicho empleado

con las siguientes caractersticas:

ENAME: PEDRO PEREZ

JOB: CLERK

MGR: 7934

DPTNO: 20

Capturar el error utilizando el cursor implcito SQL%.

EJERCICIO

PL/SQL I

VERSIN 1 JPV-99 39 / 67

6. CURSORES

El conjunto de filas resultantes de una consulta con la sentencia SELECT, puede estar

compuesto por ninguna, una o varias filas, dependiendo de la condicin que define la

consulta.

Para poder procesar individualmente cada fila de la consulta debemos definir un

cursor (que es un rea de trabajo de memoria) que contiene los datos de las filas de la

tabla consultada por la sentencia SELECT.

Cursor parametrizado: cursor que utiliza parmetros en la SELECT.

CURSOR NOMBRE_CURSOR (nombre_parametro tipo_parametro)

IS SENTENCIA SELECT utilizando los parmetros;

Variable de tipo %ROWTYPE.

Un cursor es un rea de trabajo que utiliza ORACLE para consultas que

devuelven ms de una fila, permitiendo la lectura y manipulacin de cada

una de ellas.

Declaracin:

CURSOR NOMBRE_CURSOR IS SENTENCIA SELECT;

Para recuperar los datos de un cursor primero hay que abrirlo (OPEN),

luego leerlo (FETCH), y por ltimo cerrarlo (CLOSE).

IMPORTANTE

PL/SQL I

VERSIN 1 JPV-99 40 / 67

MMAANNEEJJOO DDEE CCUURRSSOORREESS

Se declaran en la zona DECLARE.

Para recuperar todas las filas del cursor utilizaremos un bucle.

Atributos utilizados en el manejo:

Nombre_cursor%NOTFOUND Devuelve TRUE si falla y no tiene ms

filas que devolver

Nombre_cursor%FOUND Contrario de %notfound

Nombre_cursor%ROWCOUNT Devuelve el nmero de fila leda.

Nombre_cursor%ISOPEN Devuelve TRUE si el cursor est abierto.

Para abrir el cursor utilizamos la sentencia OPEN.

Si el cursor est parametrizado: OPEN nombre_cursor (parmetro).

Para cursores sin parmetros recuperamos una fila utilizando:

FETCH NOMBRE_CURSOR INTO VARIBLE1, VARIABLE2, VARIABLE3...

Cerramos un cursor utilizando la sentencia CLOSE.

BBUUCCLLEESS SSOOBBRREE CCUURRSSOORREESS

Realiza automticamente las siguientes acciones:

Abre el cursor.

Se realiza la lectura y se ejecutan las sentencias del bucle hasta que no haya

ms filas.

PL/SQL I

VERSIN 1 JPV-99 41 / 67

Cierra el cursor.

FOR Nombre_registro IN Nombre_cursor

LOOP


[EXIT when ....;]

END LOOP;

EEJJEEMMPPLLOO DDEE CCUURRSSOORREESS

A continuacin veremos un ejemplo de cursor con las siguientes caractersticas:

Objetivo: Consultar las ventas de una fecha dada ordenadas de mayor a

menor.

Nombre:CVENTAS.

Parmetros: cfecha, variable que contiene la fecha a consultar.

PL/SQL I

VERSIN 1 JPV-99 42 / 67

PROCEDURE VENTAS5 (xfecha DATE) is

BEGIN

DECLARE CURSOR cventas (cfecha DATE)

IS SELECT articulo,valor

FROM ventas

WHERE fecha=cfecha

ORDER BY valor DESC;

xarticulo ventas.articulo%TYPE;

xvalor ventas.valor%TYPE;

BEGIN

OPEN cventas(xfecha);

FOR i IN 1..5 LOOP

FETCH cventas INTO xarticulo,xvalor;

EXIT WHEN cventas%NOTFOUND;

INSERT INTO ventamayor VALUES

(xfecha,xarticulo,xvalor);

COMMIT;

END LOOP;

CLOSE cventas;

END;

END;

PL/SQL I

VERSIN 1 JPV-99 43 / 67

Para llamar al procedimiento ventas5 en una fecha dada, se puede escribir, por

ejemplo:

ventas5(to_date('15/11/95','DD/MM/YY')

ventas5(sysdate).

A continuacin detallaremos las sentencias usadas en este procedimiento:

DECLARE cursor

Define el cursor, su consulta y la lista de parmetros que se pasan a la orden

WHERE, es solo la declaracin del cursor y no la realizacin de la consulta.

xarticulo ventas.articulo%TYPE;

Define la variable xarticulo igual a la columna articulo de la tabla ventas, que

con el uso del atributo de variable %TYPE permite declarar una variable del

mismo tipo que una columna de la tabla. No es necesario conocer cmo est

definida esa columna en la tabla y, en caso que la definicin de la columna sea

modificada, automticamente se cambia la variable xarticulo.

OPEN cventas(xfecha);

Realiza la consulta asociada al cursor, pasando el valor del parmetro y

guardando sus resultados en un rea de la memoria, desde la cual,

posteriormente, se pueden leer estas filas.

FOR i IN 1..5 LOOP

Ciclo numrico de repeticin para poder consultar las 5 primeras ventas

devueltas por el cursor.

FETCH cventas INTO xarticulo,xvalor;

PL/SQL I

VERSIN 1 JPV-99 44 / 67

Lee la siguiente fila de datos del cursor cventas y pasa los datos de la consulta

a las variables xarticulo y xvalor.

EXIT WHEN cventas%NOTFOUND;

Garantiza la salida del ciclo antes de la ltima repeticin, en caso que para una

fecha dada se hayan efectuado menos de 5 ventas, ya que en esta situacin la

consulta del cursor devuelve menos de 5 filas.

%NOTFOUND es un atributo de cursor que es verdadero cuando la ltima

sentencia FETCH no devuelve ninguna fila.

INSERT INTO ventamayor

VALUES(xfecha,xarticulo,xvalor);

Insertar en la tabla ventamayor los valores ledos desde el cursor.

COMMIT;

Actualizacin de la tabla ventamayor.

END LOOP;

Fin del ciclo.

CLOSE cventas;

Cierra el cursor, eliminado sus datos del rea de memoria.

EJERCICIO 10

Insertar en la tabla nueva de empleado los valores de aquellos empleados que

pertenezcan a los departamentos 20 y 10 en este orden y aadirles 1000 al

campo salario.

EJERCICIO

PL/SQL I

VERSIN 1 JPV-99 45 / 67

EJERCICIO 11

EJERCICIO SOBRE CURSORES

Crear una tabla de totales por departamentos con dos campos: uno para los

nmeros de departamento y otro numrico para meter los totales por

departamento.

El proceso consiste en pedir por pantalla el nmero de departamento y

calcular el total de la suma de los salarios mas las comisiones. Utilizar un

cursor.

Insertar el resultado en la tabla recin creada. Si un departamento no tuviera

empleados introducir un 0 en el campo total.

EJERCICIO

EJERCICIO 12

EJERCICIO SOBRE CURSORES Y SENTENCIAS DML

Crear una tabla con los campos job y total_empleados.

Contar cuntos empleados hay de cada tipo de job. Utilizar un cursor para

obtener los distintos tipos de trabajo, y otro cursor parametrizado en el cual

se le pasa el trabajo recuperado con el cursor anterior.

Introducir en la tabla slo aquellos trabajos que tengan ms de 1 empleado.

EJERCICIO

PL/SQL I

VERSIN 1 JPV-99 46 / 67

7. REGISTROS Y TABLAS

RREEGGIISSTTRROOSS PPLL//SSQQLL

Una vez declarado un registro es necesario definir una variable de este tipo.

Podemos utilizar tambin %TYPE y %ROWTYPE.

Se puede asignar un registro a otro del mismo tipo o sus campo individualmente, con

la condicin de que ambos sean del mismo tipo.

Los registros PL/SQL son estructuras de datos complejas que permiten

tratar variables diferentes, pero relacionadas, como si fuera una unidad.

TYPE NOMBRE_REGISTRO IS RECORD

( campo1 tipo1 [:=expr1],

campo2 tipo2 [:=expr2],

.....

campon tipon [:=exprn])

IMPORTANTE

PL/SQL I

VERSIN 1 JPV-99 47 / 67

EJEMPLO:

DECLARE

TYPE t_Rec1Type IS RECORD (

Field1 NUMBER,

Field2 VARCHAR2(5));

TYPE t_Rec2Type IS RECORD (

Field1 NUMBER,

Field2 VARCHAR2(5));

v_Rec1 t_Rec1Type;

v_Rec2 t_Rec2Type;

BEGIN

/* Aunque v_Rec1 y v_Rec2 sean los mismos campos

y tipos, el tipo RECORD en s mismo es diferente

Sera una asignacin ilegal que elevara el error PLS-382. */

v_Rec1 := v_Rec2;

/* Estos campos, son del mismo tipo, por los que la asignacin seralegal. */

v_Rec1.Field1 := v_Rec2.Field1;

v_Rec2.Field2 := v_Rec2.Field2;

END;

/

PL/SQL I

VERSIN 1 JPV-99 48 / 67

TTAABBLLAASS EENN PPLL//SSQQLL

Las tablas en PL/SQL se asemejan a los arrays del lenguaje C.

Sintcticamente se las trata de la misma forma que a las matrices, aunque su

implementacin es distinta.

DEFINICIN DE UNA TABLA

Una tabla PL/SQL es similar a una tabla de base de datos pero como si

tuviera dos columnas una de ellas es el ndice y la otra el valor.

La clave es siempre BINARY_INTEGER y el tipo valor es el tipo que hubiera

especificado en la definicin.

El mximo nmero de filas viene especificado por el nmero mximo de

valores que se pueden representar mediante el tipo BINARY_INTEGER.

IMPORTANTE

PL/SQL I

VERSIN 1 JPV-99 49 / 67

Dispone de control automtico de asignacin de memoria, a diferencia de C, en el que

hay que reservarla antes.

TYPE NOMBRE_TABLA IS TABLE OF TIPO_DE_DATO

INDEX BY BINARY_INTEGER;

ATRIBUTOS DE UNA TABLA

TABLA.COUNT-> Devuelve el nmero de filas de una tabla.

TABLA.DELETE->Borra filas de la tabla.

TABLA.EXISTS-> Devuelve TRUE si existe en la tabla el elemento

especificado.

TABLA. FIRST-> Devuelve el ndice de la primera fila desde la tabla.

TABLA.LAST-> Devuelve el ndice de la ltima fila de la tabla.

TABLA.NEXT-> Devuelve el ndice de la fila de la tabla que sigue a la fila

especificada.

TABLA.PRIOR-> Devuelve el ndice de la fila de la tabla que antecede a la fila

especificada.

EJEMPLO

DECLARE

TYPE t_LastNameTable IS TABLE OF students.last_name%TYPE


v_LastNames t_LastNameTable;

v_Index BINARY_INTEGER;

BEGIN

Inserta filas en la tabla.

PL/SQL I

VERSIN 1 JPV-99 50 / 67

v_LastNames(43) := 'Mason';

v_LastNames(50) := 'Junebug';

v_LastNames(47) := 'Taller';

-- Asigna 43 a v_Index.

v_Index := v_LastNames.FIRST;

-- Asigna 50 a v_Index.

v_Index := v_LastNames.LAST;

END;

/

TABLAS DE REGISTROS

Una tabla puede contener datos de tipo registro.

EJEMPLO

DECLARE

TYPE t_StudentTable IS TABLE OF students%ROWTYPE


/* Cada elemento de v_Students es un registro */

v_Students t_StudentTable;

BEGIN

/* Obtiene el registro record con id = 10,001 and y lo almacena env_Students(10001) */

SELECT *

INTO v_Students(10001)

FROM students

WHERE id = 10001;

v_Students(10001).first_name := 'Larry';

DBMS_OUTPUT.PUT_LINE(v_Students(10001).first_name);

END;

/

PL/SQL I

VERSIN 1 JPV-99 51 / 67

EJERCICIO 13

EJERCICIO SOBRE REGISTROS

Crear un cursor parametrizado que recupere los campos nombre, trabajo,

salario y comision de la tabla EMP del trabajo introducido por teclado.

Recuperar los datos con un registro y mostrar por pantalla cada una de las

filas recuperadas en el cursor de la siguiente manera:

SMITH

------>CLERK 800

JAMES

------>CLERK 950

ADAMS

------>CLERK 1100

PEPE

------>CLERK 1300 750

PEDRO PEREZ

------>CLERK

Pedro Perez

------>CLERK

Utilizar solamente una sentencia dbms_output.put_line.

EJERCICIO

PL/SQL I

VERSIN 1 JPV-99 52 / 67

EJERCICIO 14

EJERCICIO SOBRE TABLAS

Crear un bloque PL/SQL que contenga un array de tipo varchar2(25).

Realizar las siguientes operaciones:

Insertar en la posicin 1 la cadena Uno

Insertar en la posicin 3 la cadena Tres

Insertar en la posicin 2 la cadena Menos dos

Insertar en la posicin 0 la cadena Cero

Insertar en la posicin 100 la cadena Cien

Mostrar un mensaje con el nmero total de filas de la tabla

Elimina la fila de la posicin 100

Mostrar un mensaje con el nmero de filas de la tabla

Borrar las filas desde la posicin 1 a la 3

Mostrar un mensaje con el nmero de filas de la tabla

Borrar el elemento que se encuentre en la primera posicin

Mostrar el contenido la ltima posicin

Mostrar el contenido de la penltima posicin

Mostrar el contenido de la posicin 1

Borrar toda la tabla

EJERCICIO

PL/SQL I

VERSIN 1 JPV-99 53 / 67

EJERCICIO 15

EJERCICIO SOBRE TABLAS DE REGISTROS

Crear una tabla (en el ncleo) VENDEDORES, compuesta por tres

columnas:

Empno - number(4),

sal number(7, 2) ,

comm number(7, 2).

Se trata de hacer un bloque PL/SQL capaz de cargar en un array de

registros todos aquellos empleados de la tabla emp que tengan comisin no

nula. Una vez realizada la carga del array se extraern en orden inverso al

que fueron introducidos y se insertarn en la tabla vendedores.

Utilizar un cursor para recuperar los campos empno, sal y comm

de la tabla EMP.

El registro de constar de los campos empno, sal y comm.

Sera interesante guardar los resultados en un fichero de texto

para comparar lo que devuelve la SELECT con lo que se ha

obtenido con el cursor.

Utilizar un cursor implcito.

EJERCICIO

PL/SQL I

VERSIN 1 JPV-99 54 / 67

8. SENTENCIAS TRANSACCIONALES

Savepoint:

Marcas que pone el usuario durante la transaccin para deshacer los cambios

por partes en vez de deshacer toda la transaccin.

SAVEPOINT ;

Commit:

Realiza todas las transacciones pendientes de la actual transaccin

hacindolas visibles para los dems usuarios.

COMMIT [WORK];

Rollback:

Deshace todos los cambios pendientes de la transaccin actual.

ROLLBACK [WORK] [ TO [SAVEPOINT] punto_salvaguarda; ]

Una transaccin es una serie de rdenes SQL que se completan o fallan

como una unidad. Es decir, todas las rdenes se ejecutan o bien todas

fallan.

Una transaccin termina con la orden COMMIT o ROLLBACK.

IMPORTANTE

PL/SQL I

VERSIN 1 JPV-99 55 / 67

EJEMPLO

DECLARE

v_NumIterations NUMBER;

BEGIN

-- Loop from 1 to 500, inserting these values into temp_table.

-- Commit every 50 rows.

FOR v_LoopCounter IN 1..500 LOOP

INSERT INTO temp_table (num_col) VALUES (v_LoopCounter);

v_NumIterations := v_NumIterations + 1;

IF v_NumIterations = 50 THEN

COMMIT;

v_NumIterations := 0;

END IF;

END LOOP;

END;

/

EJERCICIO 16

a) Crear una tabla temp, compuesta por estas columnas:

Codigo - number(4),

Mensaje varchar2(80)

b) Actualizar los vendedores con una comisin mayor que 350$ con un

incremento del 15% de su salario. Si la operacin afecta a ms de

tres empleados, deshacer la transaccin, en cualquier otro caso

validar la transaccin. Introducir en la tabla TEMP la operacin que

se ha realizado.

PRCTICA

PL/SQL I

VERSIN 1 JPV-99 56 / 67

EJERCICIO 17

EJERCICIO SOBRE UTILIZACIN DE SENTENCIAS TRANSACCIONALES

Crear un bloque PL/SQL que modifique el sal de los empleados que no

tienen comisin a 1000.

Dentro del mismos bloque PL/SQL actualizar los empleados con una

comisin mayor de 350 $ con un incremento del 15% de su salario. Si la

operacin afecta a ms de tres empleados, deshacer la transaccin, en

cualquier otro caso validar dicha transaccin.

Utilizar slo un commit en el bloque PL/SQL.

PRCTICA

PL/SQL I

VERSIN 1 JPV-99 57 / 67

9. CONTROL DE ERRORES

TTIIPPOOSS DDEE EEXXCCEEPPCCIIOONNEESS::

EXCEPCIONES PREDEFINIDAS:

NO_DATA_FOUND,

TOO_MANY_ROWS,

DUP_VAL_ON_INDEX....

El control de errores se realiza en la zona de excepciones del bloque

PL/SQL que es la parte final del bloque PL/SQL annimo o del

procedimiento almacenado.

WHEN NOMBRE_EXCEPCION1 [OR NOMBRE_EXCEPCION2....]

THEN SENTENCIAS;

IMPORTANTE

La zona de excepciones no es obligatoria.

NOTA

PL/SQL I

VERSIN 1 JPV-99 58 / 67

DEFINIDAS POR EL USUARIO:

Mediante:

a) Exception_init, basadas en errores ORACLE

PRAGMA EXCEPTION_INIT (nombre_excepcin, nmero_error);

b) Declarndola como excepcin con el tipo de dato EXCEPTION en el DECLARE.

EEJJEECCUUCCIINN DDEE EEXXCCEEPPCCIIOONNEESS

La ejecucin de excepciones se produce:

Automticamente: Cuando ORACLE detecta un error, para el proceso y

pasa automticamente a la zona de EXCEPTION (si existe).

Manualmente: Cuando el proceso llega a un punto en que interesa que

halla un error. La sentencia utilizada es el RAISE.

RAISE ;

Nombre_excepcin hay que definirlo antes con EXCEPTION.

El nmero de error es un sqlcode que no se halla definido para otra excepcin

de Oracle , (p. Ej. NO DATA FOUND).

NOTA

PL/SQL I

VERSIN 1 JPV-99 59 / 67

FFUUNNCCIIOONNEESS

La funcin SQLCODE nos devuelve el nmero de error producido. Slo tiene valor

cuando ocurre un error ORACLE

Slo se habilita en la zona de excepciones.

La funcin SQLERRM nos devuelve el mensaje del error del valor actual del

SQLCODE.

EEJJEEMMPPLLOOSS YY TTRRAATTAAMMIIEENNTTOO

PARA VER LOS ERRORES INTERNOS DE ORACLE E INSERTARLOS EN

UNA TABLA:

PREVIO: Creacin de tabla donde almacenarlos:

Se puede emplear la funcin predefinida RAISE_APPLICATION_ERROR para

la creacin de mensajes de error por parte del usuario.

Su sintaxis es:

RAISE_APPLICATION_ERROR (cdigo, Mensaje).

Donde cdigo es un nmero entre 20000 y 20999.

NOTA

PL/SQL I

VERSIN 1 JPV-99 60 / 67

REM

REM Creacin de tabla de errores

REM

create table errors (err_code number(6), err_msg varchar2(100));

DECLARE

err_msg VARCHAR2(100);

BEGIN

FOR err_num IN 1 .. 20000

LOOP

err_msg := SQLERRM (-err_num);

IF SQLERRM NOT LIKE '%MESSAGE %'

THEN

BEGIN

INSERT INTO errors

VALUES (-err_num, err_msg);

EXCEPTION

WHEN DUP_VAL_ON_INDEX

THEN

NULL;

END;

END IF;

END LOOP;

END;

/

PL/SQL I

VERSIN 1 JPV-99 61 / 67

USO HABITUAL DE LAS EXCEPCIONES:

DECLARE

pe_ratio NUMBER(3,1);

BEGIN

SELECT price / earnings

INTO pe_ratio

FROM stocks

WHERE symbol = 'XYZ'; -- puede causar error de divisinbyzero

INSERT INTO stats (symbol, ratio)

VALUES ('XYZ', pe_ratio);

COMMIT;

EXCEPTION -- COMIENZA EL BLOQUE DE EXCEPCIN

WHEN ZERO_DIVIDE THEN -- Maneja el error de divisin por cero

INSERT INTO stats (symbol, ratio)

VALUES ('XYZ', NULL);

COMMIT;

...

WHEN OTHERS THEN -- maneja todos los dems errores

ROLLBACK;

END; -- fin de las excepciones y del bloque.

/

PL/SQL I

VERSIN 1 JPV-99 62 / 67

CREACIN DE UN PRAGMA PARA EL MANEJO DE ERRORES:

DECLARE

insufficient_privileges EXCEPTION;

PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);

------------------------------------------------------

Oracle devuelve el error number -1031 si, por ejemplo,

intentas MODIFICAR una tabla para la que slo

tienes privilegios de consulta

-------------------------------------------------------

BEGIN

...

EXCEPTION

WHEN insufficient_privileges THEN

Maneja el error

...

END;

/

ELEVACIN DE MENSAJES DE ERROR PROPIOS DE USUARIO.

DECLARE

...

null_salary EXCEPTION;

/* Nmero de Error mapeado devuelto por raise_application_error

de una excepcin definida por el usuario. */

PRAGMA EXCEPTION_INIT(null_salary, 20101);

PL/SQL I

VERSIN 1 JPV-99 63 / 67

BEGIN

...

raise_salary(:emp_number, :amount);

EXCEPTION

WHEN null_salary THEN

INSERT INTO emp_audit

VALUES (:emp_number, ...);

...

END;

/

EJERCICIO 18

EJERCICIO SOBRE MANEJO DE EXCEPCIONES

Crear un bloque PL/SQL con una nica sentencia SELECT sobre la tabla

EMPLEADOS, de tal forma que va a ir recuperando el salario del empleado

del trabajo que se introduzca por teclado.

En el caso de recuperar un solo empleado elevar una excepcin que se va

a llamar SOLO_UN_EMPLEADO mostrando un mensaje por pantalla.

En el caso de recuperar ms utilizaremos la excepcin TOO_MANY_ROWS

y mostraremos un mensaje indicativo en la pantalla.

En el caso de no recuperar ninguno utilizaremos las excepcin

NO_DATA_FOUND y mostraremos el respectivo mensaje.

PRCTICA

PL/SQL I

VERSIN 1 JPV-99 64 / 67

10. RESUMEN

PL/SQL, (Procedural Language / SQL), es un lenguaje de programacin

procedural estructurado en bloques que amplia la funcionalidad del lenguaje

standard SQL a travs de estructuras como:

Variables y tipos, (predefinidos y definidos por el usuario)

Estructuras de control, (bucles y condiciones)

Procedimientos y funciones

Tipos de objetos y mtodos, (a partir de versin 8)

El Bloque PL/SQL es la unidad bsica en todo programa PL/SQL.

Un programa PL/SQL ser un conjunto de bloques PL/SQL, situados de

manera secuencial o anidados

Existen diversas funciones predefinidas a las que se puede llamar desde

una orden SQL.

PL/SQL puede incluir sentencias de manejo de datos.

Oracle abre un cursor implcito por cada sentencia SQL que tenga que

procesar.

Nos referiremos a l como SQL%

Un cursor es un rea de trabajo que utiliza ORACLE para consultas que

devuelven ms de una fila, permitiendo la lectura y manipulacin de cada

una de ellas.

PL/SQL I

VERSIN 1 JPV-99 65 / 67

Declaracin de un cursor:

CURSOR NOMBRE_CURSOR IS SENTENCIA SELECT;

Para recuperar los datos de un cursor primero hay que abrirlo (OPEN),

luego leerlo (FETCH), y por ltimo cerrarlo (CLOSE).

Los registros PL/SQL son estructuras de datos complejas que permiten

tratar variables diferentes, pero relacionadas, como si fuera una unidad.

Una tabla PL/SQL es similar a una tabla de base de datos pero como si

tuviera dos columnas una de ellas es el ndice y la otra el valor.

La clave es siempre BINARY_INTEGER y el tipo valor es el tipo que

hubiera especificado en la definicin.

El mximo nmero de filas viene especificado por el nmero mximo de

valores que se pueden representar mediante el tipo BINARY_INTEGER.

Una transaccin es una serie de rdenes SQL que se completan o fallan

como una unidad. Es decir, todas las rdenes se ejecutan o bien todas

fallan.

Una transaccin termina con la orden COMMIT o ROLLBACK.

El control de errores se realiza en la zona de excepciones del bloque

PL/SQL que es la parte final del bloque PL/SQL annimo o del

procedimiento almacenado.

PL/SQL I

VERSIN 1 JPV-99 66 / 67

11. EVALUACIN

1 En PL/SQL, pueden incorporarse rdenes DDL

a) Siempre

b) Slo a travs de SQL Dinmico

c) Nunca

2 En un bloque PL/SQL, la declaracon de variables se hace en:

a) En la zona declarativa

b) Tanto en la zona declarativa como en la zona de proceso

c) Solamente en el bloque de excepciones

3 Qu valor nos dara la variable resultado en la siguiente consulta?

declare a number:=2;

resultado varchar2(20);

begin

select decode(a,3,'Menor',4,'Siguiente',5,'Mayor','Otro') into resultado

from dual;

dbms_output.put_line(resultado);

end;

/

a) No compilara porque en la declaracin no puede asignarseuna variable a un nmero

b) Mayor

c) Otro

PL/SQL I

VERSIN 1 JPV-99 67 / 67

4 La sentencia ROLLBACK sirve para:

a) Volver al bloque BEGIN / END anterior al que pertenece

b) Reenva de nuevo la transaccin al usuario

c) Deshace todos los cambios pendientes de la transaccin actual

5 Defina bloque PL/SQL annimo:

...............................................................................................................

...............................................................................................................

...............................................................................................................

...............................................................................................................

6 Definicin y usos del tipo PLS_Integer:

...............................................................................................................

...............................................................................................................

...............................................................................................................

...............................................................................................................

7 Definicin, usos y declaracin de un cursor:

...............................................................................................................

...............................................................................................................

...............................................................................................................

...............................................................................................................

PL/SQL II


PPLL//SSQQLL IIII

Javier Prez-Vigo (Octubre 1999)

PL/SQL II


INTRODUCCIN

En la unidad didctica PL/SQL I se estudiaron los bloques PL/SQL

annimos que se compilaban cada vez que eran ejecutados.

Las estructuras que se vern en esta unidad didctica, (procedimientos,

funciones, paquetes y disparadores), son bloques nominados y pueden

ser almacenados en la base de datos para ser ejecutados tantas veces

como sea necesario.

PL/SQL slo puede contener rdenes DML y no DDL. Sin embargo esta

restriccin se supera a partir de la versin 2.1 con el paquete

DBMS_SQL. Este paquete implementa cdigo PL/SQL y SQL dinmico,

al que se llama desde otros bloques PL/SQL.

En el captulo 4 de esta unidad se ver cmo utilizar el paquete

DBMS_SQL y sus implicaciones.

PL/SQL II


CONTENIDO:

1. PROCEDIMIENTOS Y FUNCIONES

PROCEDIMIENTOS

FUNCIONES

SUBPROGRAMAS LOCALES.

CONTROL DE PROCEDIMIENTOS Y FUNCIONES.

2. DESARROLLO Y UTILIZACIN DE PAQUETES

PAQUETES (PACKAGES)

FUNCIONES DENTRO DE PAQUETES.

3. DISPARADORES (TRIGGERS)

CONCEPTO DE DISPARADORES (TRIGGERS)

CREACIN DE DISPARADORES

CONCEPTO DE TABLA MUTANTE

4. PL/SQL DINMICO

CONCEPTO

MTODOS DE IMPLEMENTACIN

5. EJERCICIO DE DESARROLLO

6. RESUMEN

7. EVALUACIN

PL/SQL II


OBJETIVOS

El alumno tras el estudio de esta unidad didctica podr:

Conocer y utilizar los procedimientos y funciones almacenados a

travs del lenguaje PL/SQL.

Crear y utilizar los paquetes, packages.

Conocer y utilizar los disparadores.

Entender los fundamentos del PL/SQL dinmico.

EXPECTATIVAS

PL/SQL II


1. PROCEDIMIENTOS Y FUNCIONES

CONCEPTO DE PROCEDIMIENTOS Y FUNCIONES

Los bloques PL/SQL forman una estructura de bloques annimos, es decir, se

compilan contra la Base de Datos y una vez ejecutados se pierden.

Se hace necesario tener estructuras de bloques nominados, tal que puedan

almacenarse en la Base de Datos y ser llamados cuando se necesiten.

Un procedimiento es un conjunto de instrucciones PL/SQL que puede ser

llamado usando el nombre que se le haya asignado.

Una funcin es un conjunto de instrucciones en PL/SQL, que pueden ser

llamados usando el nombre con que se le haya creado.

Se diferencian de los procedimientos, en que las funciones retornan un

valor al ambiente desde donde fueron llamadas.

IMPORTANTE

Una vez creados un procedimiento o una funcin pueden llamarse desde

multitud de lados: SQL*, Forms o cualquier otra herramienta ORACLE.

NOTA

PL/SQL II


PPRROOCCEEDDIIMMIIEENNTTOOSS

CREACIN DE PROCEDIMIENTOS

Utilizaremos la siguiente sentencia:

CREATE [OR REPLACE] PROCEDURE nombre_procedimiento

[(argumento1 TIPO tipo_dato1,..., argumentoN TIPO tipo_datoN)] IS O AS

Bloque PL/SQL

Su estructura es semejante a:

CREATE OR REPLACE PROCEDURE PROCEDIMIENTO IS

tmpVar NUMBER;

/****************************************************************************** NAME: PROCEDIMIENTO PURPOSE: To calculate the desired information. REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 10/09/99 1. Created this procedure.******************************************************************************/

BEGIN

tmpVar := 0;

EXCEPTION

WHEN NO_DATA_FOUND THEN

Null;

WHEN OTHERS THEN

Null;

END PROCEDIMIENTO;

/

PL/SQL II


Los tipos de los argumentos son: IN, OUT, IN OUT

Indican si el parmetro es de entrada, salida o ambos.

Si no se pone nada se supone que es IN.

IS o AS llevan implcito el DECLARE, as que las variables se declaran

inmediatamente despus de IS o AS.

Un procedimiento termina con END o con END seguido del nombre del

procedimiento.

No puede haber declaracin de argumentos, es decir, en las declaraciones de

tipo NUMBER, VARCHAR2 o DATE no puede figurar ni la escala ni la precisin.

Es aconsejable utilizar los atributos %TYPE y %ROWTYPE para definir

argumentos y variables.

NOTA

El uso de OR REPLACE permite sobreescribir un procedimiento existente.

Si se omite, y el procedimiento ya existe, se producir un error.

Es conveniente utilizar al principio slamente CREATE, ya que de existir el

procedimiento en el ncleo podemos destruirlo.

NOTA

PL/SQL II


BORRADO

Para borrar un procedimiento se utiliza:

DROP PROCEDURE Nombre_procedimiento;

EJECUCIN

Los procedimientos (o funciones) que se declaran como locales a un bloque PL/SQL

se situarn al final del DECLARE.

Las formas de llamar a un procedimiento son las siguientes:

Exec nombre_procedimiento(var1, var2)

Exec nombre_procedimiento(var2 => xxx, var1 => yyy)

Un procedimiento se invoca con la sentencia EXEC[UTE] desde SQL*.

Desde un bloque PL/SQL simplemente es necesario nombrarlo para

ejecutarlo.

IMPORTANTE

Como argumentos en un procedimiento puede haber registros o tablas.

NOTA

PL/SQL II


EJERCICIO SOBRE PROCEDIMIENTOS

EJERCICIO 1

Crear un tabla, EMPLEADOS, con los campos: nombre, trabajo, salario.

Crear un bloque PL/SQL que extraiga de la tabla EMP aquellos empleados

con comisin nula (utilizando un cursor). Aumentar su salario un 10% e

introducir cada uno de ellos en la tabla EMPLEADOS utilizando un

procedimiento cuyos argumentos van a ser nombre, salario y trabajo.

EJERCICIO 2

Ampliar el procedimiento creado para el ejercicio anterior de tal manera que

si el empleado tiene como trabajo CLERK y su nmero de departamento es

el 20 se insertar en la tabla EMP con una comisin = 1000 y no se insertar

en la tabla EMPLEADOS. En cualquier otro caso introducir el empleado en

la tabla EMPLEADOS.

Modificar el nmero de argumentos pasados al procedimiento aadiendo

uno que sea insertados el cual va a indicar el nmero de elementos que se

han insertado en la tabla empleados. Mostrar dicho nmero desde el bloque

PL/SQL con un mensaje.

PRCTICA

PL/SQL II

VERSIN 1 JPV-99 10 / 50

FFUUNNCCIIOONNEESS

CREACIN DE FUNCIONES

Utilizaremos la siguiente sentencia:

CREATE [OR REPLACE] FUNCTION nombre_funcin

[(argumento1 TIPO tipo_dato1,...., argumentoN TIPO tipo_datoN)]

RETURN tipo_dato IS O AS

Bloque PL/SQL

Su estructura es semejante a:

CREATE OR REPLACE FUNCTION FUNCION RETURN NUMBER IS

tmpVar NUMBER;

/****************************************************************************** NAME: FUNCION PURPOSE: To calculate the desired information. REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 10/09/99 1. Created this function.******************************************************************************/

BEGIN

tmpVar := 0;

EXCEPTION


Null;

WHEN OTHERS THEN

Null;

RETURN tmpVar;

END FUNCION;

/

PL/SQL II

VERSIN 1 JPV-99 11 / 50

Como TIPO: IN, OUT, IN OUT

Return de una funcin no lleva ni escala ni precisin.

Return puede llevar expresiones.

El tipo de dato no puede incluir escala o precisin.

Una funcin puede tener ms de un return.

Si declaramos una sin return da un error.

BORRADO

Para borrar una funcin se utiliza:

DROP FUNCTION Nombre_procedimiento;

EJECUCIN

Los argumentos OUT o IN OUT son vlidos dentro de las funciones pero se

usan raramente.

NOTA

Una funcin se invoca con la sentencia EXECUTE, como ocurre en los

procedimientos, pero hay que tener en cuenta que tiene que asignarse a

alguna variable del mismo tipo que se devuelve.

IMPORTANTE

PL/SQL II

VERSIN 1 JPV-99 12 / 50

Una funcin se puede invocar desde una sentencia SELECT, WHERE, HAVING,

clusula VALUES de un INSERT y SET de un UPDATE.

Pueden pasarse y devolver REGISTROS y TABLAS.

FUNCIONES PL/SQL DENTRO DE SENTENCIAS SQL

Para incluir una funcin dentro de una sentencia SQL:

Debe de ser una funcin de fila nica (devuelven un nico valor). No pueden ser ni

registros ni tablas.

Todos los argumentos deber ser de tipo IN.

Los tipos de datos: BOOLEAN, RECORD o TABLE no estn permitidos (no son

tipo SQL).

El valor resultante tiene que ser: NUMBER, CHAR, VARCHAR2, DATE.

Las funciones no pueden modificar datos en las tablas. No se puede utilizar

INSERT, UPDATE, DELETE dentro de ellas.

Las funciones pueden incluirse tanto en el bloque WHERE de una SELECT

como directamente en una VISTA.

NOTA

PL/SQL II

VERSIN 1 JPV-99 13 / 50

SSUUBBPPRROOGGRRAAMMAASS LLOOCCAALLEESS..

Al igual que los procedimientos y funciones, es posible incluir en un bloque PL/SQL un

subprograma.

Los subprogramas se incluyen en el DECLARE del bloque y es necesario

incluir el subprograma entero.

IMPORTANTE

EJERCICIOS SOBRE FUNCIONES

EJERCICIO 3

Crear una funcin que nos permita realizar una consulta del salario del

empleado de la tabla EMP que se le pasa como parmetro desde SQL* (se

le pasa el nmero del empleado). Si la consulta ha sido satisfactoria la

funcin devuelve un SI, en caso contrario devuelve un NO.

Utilizar desde el SQL para llamar a la funcin: dbms_output.put_line(Existe

el empleado?||di_si_existe(&empleado));

PRCTICA

PL/SQL II

VERSIN 1 JPV-99 14 / 50

EJERCICIO 4

Utilizar una sentencia SELECT sobre la tabla EMP que nos permita ver los

campos empno, ename y el salario de los empleados. Si el empleado tiene

una salario mltiplo de 5 y mayor de 1500 mostrar su salario multiplicado por

15, en caso contrario su salario permanece invariable.

EJERCICIO 5

Utilizar una SELECT para extraer de la tabla EMP cada uno de los

empleados y su mximo jefe.

Obtener su mximo jefe utilizando una funcin.

EJERCICIO 6

Crear un bloque PL/SQL que sea capaz de cargar un array de registros

cuyos campos son nombre_empledo y ranking y muestre el contenido de

dicho array ordenado.

El campo ranking lo obtendremos a travs de un procedimiento a partir del

cual se va a obtener el puesto que ocupa cada empleado en la empresa en

cuestin de salario + comisin.

Utilizar un cursor para cargar la tabla.

PL/SQL II

VERSIN 1 JPV-99 15 / 50

CCOONNTTRROOLL DDEE PPRROOCCEEDDIIMMIIEENNTTOOSS YY FFUUNNCCIIOONNEESS..

DOCUMENTACIN DE PROCEDIMIENTOS Y FUNCIONES.

A travs de la vista del diccionario de datos USER_OBJECTS se pueden obtener los

nombres de los siguientes elementos de un esquema.

DATABASE LINK

FUNCTION

INDEX

PACKAGE

PACKAGE BODY

PROCEDURE

SEQUENCE

SYNONYM

TABLE

TRIGGER

VIEW

A travs de la vista del diccionario de datos USER_SOURCE se puede obtener el

cdigo de los siguientes elementos dentro de un esquema:

FUNCTION

PACKAGE

PACKAGE BODY

PROCEDURE

PL/SQL II

VERSIN 1 JPV-99 16 / 50

Para visualizar el nombre de un procedimiento o funcin y su lista de argumentos

utilizaremos el comando DESCRIBE.

DESC[RIBE] Nombre_procedimiento o Nombre_funcin;

Para mostrar el texto de los errores de compilacin utilizaremos la vista

USER_ERRORS.

Para visualizar los errores de compilacin utilizaremos la siguiente sentencia:

SHOW ERRORS PROCEDURE o FUNCTION o PACKAGE o

PACKAGE BODY o TRIGGER nombre_objeto;

DEPURACIN DE PROCEDIMIENTOS Y FUNCIONES.

Para activar este procedimiento utilizaremos la opcin

SET SERVEROUTPUT ON

Para preparar el texto del mensaje de salida se utiliza PUT.

DBMS_OUTPUT.PUT (Texto);

Para la depuracin de procedimientos y funciones se utiliza el

procedimiento suministrado por Oracle: DBMS_OUTPUT

IMPORTANTE

PL/SQL II

VERSIN 1 JPV-99 17 / 50

Para visualizar por pantalla el mensaje utilizamos NEW_LINE.

DBMS_OUTPUT.NEW_LINE;

Para preparar y visualizar el texto se utiliza PUT_LINE (opcin ms utilizada)

Estos procedimientos estn sobrecargados y pueden mostrar directamente datos

de los siguientes formatos:

DBMS_OUTPUT.PUT_LINE (varchar2)

DBMS_OUTPUT.PUT_LINE (number)

DBMS_OUTPUT.PUT_LINE (date)

No hace falta transformar un entero a varchar2 para utilizarlo en la sentencia

DBMS_OUTPUT.

NOTA

PL/SQL II

VERSIN 1 JPV-99 18 / 50

Este sera el diagrama en el que se explica el funcionamiento del DBMS_OUTPUT:

La salida DBMS_OUTPUT es independiente para cada sesin.

NOTA

Para que no nos d un error debido a que el buffer es muy pequeo, conviene

inicializarlo mediante el comando:

SET SERVEROUTPUT ON size 1000000

NOTA

PL/SQL II

VERSIN 1 JPV-99 19 / 50

2. DESARROLLO Y UTILIZACIN DE PAQUETES

PPAAQQUUEETTEESS ((PPAACCKKAAGGEESS))

Estructura de un paquete:

PBLICA

Variable Pblica

Declaracin del Procedimiento AProcedimiento Pblico

Variable Privada

Definicin del Procedimiento B

Definicin del Procedimiento A

Variable local

Procedimiento Privado

Procedimiento Pblico

PRIVADA

PACKAGE

PACKAGE

BODY

Zona de Especificacin

Cuerpo del paquete

Un paquete es un grupo de identificadores y rutinas agrupadas dentro de la

misma construccin.

IMPORTANTE

PL/SQL II

VERSIN 1 JPV-99 20 / 50

CREACIN DE PAQUETES

Para crear un paquete:

CREATE [OR REPLACE] PACKAGE Nombre_paquete IS O AS

Declaracin variable

Declaracin cursor

Declaracin excepcin

Declaracin procedimiento

Declaracin funcin

END Nombre_paquete

Para la creacin del cuerpo del paquete:

CREATE [OR REPLACE] PACKAGE BODY Nombre_paquete IS O AS

Declaracin variable

Declaracin cursor

Declaracin excepcin

Cdigo del procedimiento

Cdigo de la funcin

[BEGIN Cdigo de inicializacin del paquete]

END Nombre_paquete

Para borrar un paquete y el cuerpo del paquete se utiliza.

DROP PACKAGE Nombre_paquete

PL/SQL II

VERSIN 1 JPV-99 21 / 50

Para borrar el cuerpo del paquete se utiliza.

DROP PACKAGE BODY Nombre_paquete

Para invocar un procedimiento o funcin de un paquete desde fuera del mismo se

utiliza la orden EXECUTE desde SQL*, (desde un form no es necesario el

EXECUTE).

EXEC[UTE] Nombre_paquete.Nombre_funcin

Nombre_procedimiento

Siempre es posible declarar una variable en un bloque PL/SQL del mismo tipo que

uno que figure dentro del package:

DECLARE

V_hist ClassPackage.t_lista;

Cualquier objeto de la cabecera de un paquete est dentro del mbito y

visible desde fuera.

NOMBRE_PAQUETE.NOMBRE_OBJETO

NOTA

Dentro del cuerpo del paquete se puede hacer referencia a los objetos sin

necesidad del "."

NOTA

PL/SQL II

VERSIN 1 JPV-99 22 / 50

Los packages disponen de la opcin de sobrecarga de procedimientos y funciones,

es decir, con el mismo nombre y distintos argumentos.

No se pueden sobrecargar si difieren slo en el modo (IN OUT).

No se pueden sobrecargar si difieren slo en el tipo de dato devuelto.

No se pueden sobrecargar si difieren slo en la familia del tipo de dato.

NOTA

DECLARE

FUNCTION value_ok (date_in IN DATE)

RETURN BOOLEAN IS

BEGIN

RETURN date_in 0; Number Version

END;

BEGIN

IF value_ok (SYSDATE) THEN ... END IF;

IF value_ok (total_sales) THEN ... END IF;

END;

PL/SQL II

VERSIN 1 JPV-99 23 / 50

Podemos cambiar el cuerpo sin cambiar la cabecera.

Si cambiamos la cabecera y no el cuerpo ste queda invlido, siendo necesario

volver a compilarlo.

Para compilar los objetos invlidos utilizaremos la siguiente sentencia:

select

'alter '||

decode ( object_type, 'PACKAGE BODY', 'PACKAGE', object_type )||

' '||

object_name||

' compile'||

decode ( object_type, 'PACKAGE BODY', ' body;', ';' )

from

user_objects

where

status = 'INVALID'

/

FFUUNNCCIIOONNEESS DDEENNTTRROO DDEE PPAAQQUUEETTEESS..

Es necesario indicar el nivel de depuracin de una funcin dentro de un paquete

cuando se crea un paquete (en la especificacin).

El nivel de depuracin determina qu operaciones no van a ser realizadas por la

funcin del paquete.

No se puede utilizar una funcin que est dentro de un paquete en una sentencia

SQL. Es necesario indicar el nivel de depuracin.

PL/SQL II

VERSIN 1 JPV-99 24 / 50

Para ello utilizaremos la sentencia:

PRAGMA RESTRICT_REFERENCES (NOMBRE_FUNCION,

WNDS [,WNPS] [,RNDS] [,RNPS]);

El pragma se indica en la especificacin del paquete detrs de todas las

funciones.

El argumento WNDS debe indicarse SIEMPRE que se utilice

RESTRICT_REFERENCES.

WNDS -> (Writes No Database State) Indica que la funcin no va

modificar tablas de la base de datos

WNPS -> (Writes No Package Status) Indica que la funcin no va a

cambiar los valores de variables de un paquete pblico.

RNDS -> (Reads No Database State) Indica que la funcin no va

aconsultar ninguna tabla de la base de datos.

RNPS-> (Reads No Package Status) Indica que la funcin no va a

referenciar los valores de las variables de un paquete pblico.

CREATE OR REPLACE PACKAGE PAQUETE IS

FUNCTION MyFuncName ( inVal Number ) Return Number;

PROCEDURE MyProcName ( inVal Number, JobId VARCHAR2 );

pragma restrict_references ( MyFuncName, WNDS, WNPS, RNDS );

END PAQUETE;

/

PL/SQL II

VERSIN 1 JPV-99 25 / 50

CREATE OR REPLACE PACKAGE BODY PAQUETE AS

FUNCTION MyFuncName ( inVal Number ) Return Number IS

TmpVar NUMBER;

BEGIN

tmpVar := 0;

EXCEPTION


Null;

WHEN OTHERS THEN

Null;

RETURN tmpVar;

END MyFuncName;

PROCEDURE MyProcName ( inVal Number, JobId VARCHAR2 ) IS

TmpVar NUMBER;

BEGIN

tmpVar := 0;

EXCEPTION


Null;

WHEN OTHERS THEN

Null;

END MyProcName;

END PAQUETE;

/

PL/SQL II

VERSIN 1 JPV-99 26 / 50

EJERCICIOS SOBRE PAQUETES

EJERCICIO 7

Crear un paquete con los siguientes elementos: una tabla para almacenar

registros con los campos nombre, salario, un procedimiento

pr_insertar(nombre, salario) y un ndice.

Se tratara de recuperar desde un bloque PL/SQL los elementos de la tabla

EMP cuya suma de su salario + comisin > 2000 e ir introducindolos en la

tabla del paquete recin creado a partir de la funcin pr_insertar.

Consultar desde el bloque PL/SQL el ndice del paquete y obtener el nmero

de registros introducidos en dicha tabla.

EJERCICIO 8

Ampliar el paquete creado en el ejercicio con las funciones fu_recupera_sal

y fu_inicializa.

La nueva funcin, fu_recupera_sal, nos va a permitir recuperar el salario de

un empleado si se le pasa el nombre como argumento. En el caso de halla

varias personas con el mismo nombre recuperar la primera. En el caso de

que no hubiera ninguna mostrar por pantalla el mensaje de error ORA-

20001 Persona no encontrada. Probar esta funcin desde SQL*.

La funcin fu_inicializa borra la tabla y pone el ndice a 0.

PRCTICA

PL/SQL II

VERSIN 1 JPV-99 27 / 50

EJERCICIO 9

Crear un paquete con tres funciones que se van a llamar (las tres)

fu_recupera.

Si se le pasa un nmero me va a devolver el salario de la persona

que se encuentra en la posicin que se le est pasando como

argumento de la tabla del paquete anterior. En caso de no existir

dicho elemento en la tabla mostrar un mensaje por pantalla.

Si se pasa una cadena de caracteres tiene que hacer una

consulta sobre la tabla EMP de tal forma que nos tiene que contar

el nmero de personas con el mismo nombre que la cadena que

se le est pasando.

Si se le pasa una fecha tiene que introducir en una tabla (no

pblica) las personas con una antigedad mayor que la pasada

como parmetro de forma ordenada por fecha . Se trata de una

tabla de registros con los campos Nombre, antigedad. Extraer

cada uno de esos empleados de la tabla, visualizando el nombre

del empleado y el nmero de das que lleva en la empresa.

EJERCICIO 10

Ampliar el primer paquete creado con la funcin fu_media_comision la cual

nos va a dar la media de las comisiones de todos los empleados que tienen

tabajo CLERK.

Realizar una consulta en SQL* sobre la tabla EMP de forma que extraiga los

empleados cuya comisin sea mayor que el valor devuelto por la funcin

fu_media_comision.

PL/SQL II

VERSIN 1 JPV-99 28 / 50

3. DISPARADORES EN BASE DE DATOS

DDIISSPPAARRAADDOORREESS ((TTRRIIGGGGEERRSS))

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 implcitamente cuando se ejecuta una sentencia de

manipulacin de datos sobre una tabla (INSERT, UPDATE, DELETE), y en que el

disparador no admite argumentos.

Los disparadores se utilizan principalmente para:

Para el mantenimiento de restricciones de integridad compleja, que no sean

posibles con las restricciones declarativas definidas en el momento de crear la

tabla. (Comn en desnormalizaciones).

En la auditora de la informacin contenida en la tabla, registrando los cambios

realizados y la identidad de quien lo llev a cabo.

El aviso automtico a otros programas de que hay que llevar a cabo una

determinada accin, cuando se realiza un cambio en la tabla.

Tipos de disparadores:

Disparadores de sentencia: Se ejecutan una sola vez por cada sentencia.

Un disparador es un bloque PL/SQL asociado a una tabla que se ejecuta

cuando una determinada instruccin SQL se va a ejecutar sobre dicha

tabla.

IMPORTANTE

PL/SQL II

VERSIN 1 JPV-99 29 / 50

Disparadores de fila: Se activan una vez por cada fila afectada por la orden que

provoc el disparo.

Se pueden combinar diferentes acontecimientos en un nico disparador, utilizando los

predicados condicionales INSERTING, UPDATING y DELETING dentro del cuerpo del

disparador (devuelven un boolean TRUE o FALSE cuando se consultan).

Se puede visualizar el contenido de un disparador a travs de la vista del diccionario

de datos USER_TRIGGERS.

Clasificacin:

Segn el momento en el que salta el disparador *BEFORE

* AFTER

Segn el tipo de operacin que provoca su actuacin * INSERT

* UPDATE

* DELETE

Segn las veces que se ejecuta el cuerpo del disparador * Sentencia

* Fila

En la figura de abajo se disparan los triggers para la operacin que contiene la

clusula WHERE: WHERE ENAME LIKE 'J%':

Dentro de un disparador estn prohibidas sentencias transaccionales:

COMMIT, ROLLBACK y SAVEPOINT.

NOTA

PL/SQL II

VERSIN 1 JPV-99 30 / 50

Para borrar un trigger utilizaremos:

DROP TRIGGER Nombre_trigger

CCRREEAACCIINN DDEE DDIISSPPAARRAADDOORREESS

DISPARADORES DE SENTENCIA

Se utiliza las siguientes sentencia para los disparadores de sentencia:

CREATE [OR REPLACE] TRIGGER Nombre_disparador

MOMENTO ACONTECIMIENTO ON Nombre_tabla

BLOQUE PL/SQL

DISPARADORES DE FILA

Para los disparadores de fila se utiliza esta otra sentencia:

CREATE [OR REPLACE] TRIGGER Nombre_disparador

MOMENTO ACONTECIMIENTO ON Nombre_tabla

FOR EACH ROW [WHEN Condicin_restriccin]

[REFERENCING OLD O NEW AS Nombre_sustituto_old_o_new]

Dentro de un disparador de fila, se puede referenciar el valor de la columna antes

de ser modificado anteponiendo el cualificador :OLD y referenciar el nuevo valor de

la columna anteponiendo el cualificador :NEW.

Durante la insercin el valor antiguo es NULL, durante el borrado el valor nuevo es

NULL.

El cualificador :NEW puede aparecer a la izquierda de una asignacin en el cuerpo

PL/SQL solamente en disparadores de fila BEFORE. No puede aparecer en la

PL/SQL II

VERSIN 1 JPV-99 31 / 50

parte izquierda de una asignacin en el cuerpo PL/SQL de un disparador AFTER.

Ni en el predicado condicional DELETING.

El cualificador :OLD no puede aparecer nunca en la parte izquierda de una

sentencia de asignacin.

ACTIVACIN DE DISPARADORES

Para activar un disparador:

ALTER TRIGGER Nombre_disparador ENABLE;

Para desactivar un disparador:

ALTER TRIGGER Nombre_disparador

Aunque no surga un error de compilacin, hay que tener en cuenta que :OLD no

est definido para las rdenes INSERT, as como no lo estar :NEW para el

DELETE.

NOTA

A pesar de que :NEW y :OLD sean tratados sintcticamente como registros del

tipo tabladisparo%Rowtype, en realidad no son registros (por tanto no se

pueden asignar como registros completos); sino que son variables de

acoplamiento.

NOTA

PL/SQL II

VERSIN 1 JPV-99 32 / 50

ORDEN DE EJECUCIN DE LOS DISPARADORES

Los disparadores se ejecutan segn el siguiente orden:

1. Ejecutar, si existe, el disparador de tipo BEFORE (disparador previo) de tipo

sentencia.

2. Para cada fila a la que afecte la orden.

a) Ejecutar, si existe, el disparador BEFORE a nivel de fila.

b) Ejecutar la propia orden.

c) Ejecutar, si existe, el disparador de tipo AFTER con nivel de fila.

3. Ejecutar, si existe, el disparador AFTER, de tipo sentencia.

CCOONNCCEEPPTTOO DDEE TTAABBLLAA MMUUTTAANNTTEE

Un trigger de sentencia nunca provoca el error de tabla mutante.

Supongamos un trigger for each row que se dispara cuando se modifiquen datos de

una tabla. El trigger a su vez realiza una modificacin sobre la misma tabla. Esto

provoca que la tabla quede mutante por la incosistencia de la informacin en ese

instante y da un error por pantalla.

Una tabla mutante es una tabla que est modificndose actualmente por

una orden DML (INSERT, UPDATE, DELETE).

Para un disparador, sta es la tabla sobre la que est definido.

IMPORTANTE

PL/SQL II

VERSIN 1 JPV-99 33 / 50

La solucin del error de la tabla mutante consistira en crear un disparador de

sentencia BEFORE en el que se van almacenando los datos (no se consulta ni

modifica la misma tabla), y otro de fila AFTER que utilizarn los valores almacenados

por el disparador de sentencia:

BEFORE -> Inicializa

BEFORE EACH ROW -> Almacena

AFTER -> Procesa

Si una operacin INSERT afecta a una nica fila, entonces los disparadores de

fila previo y posterior por dicha fila no tratarn a la tabla como mutante.

NOTA

CREATE OR REPLACE TRIGGER DISPARADOR

BEFORE INSERT OR UPDATE ON MyTableName

FOR EACH ROW

tmpVar NUMBER;

BEGIN

tmpVar := 0;

Select MySeq.NextVal into tmpVar from dual;

:NEW.SequenceColumn := tmpVar;

:NEW.CreatedDate := Sysdate;

PL/SQL II

VERSIN 1 JPV-99 34 / 50

:NEW.CreatedUser := User;

EXCEPTION

WHEN OTHERS THEN

Null;

END DISPARADOR;

/

EJERCICIOS SOBRE TRIGGERS

EJERCICIO 11

Crear un trigger sobre EMP de forma que cuando se vaya a insertar un

nuevo registro verifique que no haya ms de 20 registros ya insertados. Si

ya los hubiera, mostrar un mensaje de error indicando la imposibilidad de

insertar el nuevo registro.

EJERCICIO 12

Hacer un disparador de base de datos sobre la tabla DEPT, en el cual

cuando se inserte o se actualice el nmero de departamento, compruebe

que este nmero sea mltiplo de diez.

PRCTICA

PL/SQL II

VERSIN 1 JPV-99 35 / 50

EJERCICIO 13

Crear la tabla CONTABILIDAD con los campos departamento, movimiento y

dinero.

Crear un disparador de fila de base de datos en el cual cada vez que se

hace una operacin sobre la tabla EMP (insercin, modificacin,borrado),

realice una insercin sobre la tabla CONTABILIDAD con el movimiento

realizado sobre EMP. Los movimientos a insertar son de tres tipo:

INSERCION, MODIFICACION, BORRADO.

EJERCICIO 14

Realizar un disparador de fila sobre la tabla EMP que se ejecute cuando se

actualiza o se inserte sobre la tabla EMP, comprobar que el salario se

encuentra entre el mximo y el mnimo de ese trabajo para un

departamento.

En el caso de que no exista ninguna fila en la tabla EMP cuyo

departamento sea igual al insertado, hay que comprobar que existe el

departamento en la tabla DEPT. En el caso de existir, insertar la fila en

EMP sin ms comprobaciones. En caso de no existir insertar el

departamento en la tabla DEPT con el campo dname a NULL y el campo

loc a NULL.

Si se modifica el departamento de un director hay que cambiar el

departamento a sus empleados.

EJERCICIO 15

Crear VIGENCIAS con los campos: empleado, f_desde y f_hasta.

Realizar un disparador sobre la tabla VIGENCIAS del manera que

compruebe que cada fila tratada no se solape con otra ya insertada.

PL/SQL II

VERSIN 1 JPV-99 36 / 50

EJERCICIO 16

Realizar un disparador de fila sobre la tabla emp que se ejecute cuando se

actualiza sobre la tabla emp el campo trabajo (JOB) y que compruebe que si

se trata de un vendedor (SALESMAN) su comisin no sea mayor que la

del jefe con menor comisin.

Modificar los empleados de la tabla emp que tienen trabajo CLERK y

sustituirlo por SALESMAN. Observar lo que pasa.

Sustituir el trigger de fila por dos, uno antes de modificar (for each row) y

otro after (de sentencia) y un paquete que solucione el error de la tabla

mutante.

PL/SQL II

VERSIN 1 JPV-99 37 / 50

4. PL/SQL DINMICO

CCOONNCCEEPPTTOO

Mediante el DBMS_SQL podemos manejar tambin rdenes DML.

Las rdenes SQL Dinmicas tienen muy pocas restricciones, porque si se desea, la

orden completa SQl se puede determinar en el momento de la ejecucin, en lugar de

en la compilacin.

Desventajas:

El SQL Dinmico no se ejecuta con la misma eficiencia que el esttico.

Aquellas tablas a las que se hace referencia no aparecern en

USER_DEPENDENCES.

El PL/SQL dinmico nace de la necesidad de implementar rdenes DDL

dentro de un bloque de PL/SQL.

Se implementa en el paquete DBMS_SQL

IMPORTANTE

En ocasiones, la necesidad de incluir rdenes DML dinmicamente surge al

hacer referencia a una tabla, que se crear en ejecucin; por lo que si la orden

que la refiere estuviera en SQL esttico, no compilara.

NOTA

PL/SQL II

VERSIN 1 JPV-99 38 / 50

Cuando se borra una tabla dinmicamente, es labor del usuario el cambiar el

estado de los paquetes que hagan referencia a dicha tabla.

PROCEDIMIENTOS DE SQL DINMICO:

Funcin DBMS_SQL.OPEN_CURSOR RETURN INTEGER;

Abre el puntero para el cursor

Procedure DBMS_SQL.PARSE (cursor IN INTEGER, sentencia_a_ejecutar IN

VARCHAR2, DBMS_SQL.NATIVE IN INTEGER);

Asocia el puntero con la sentencia SQL

Funcin DBMS_SQL.IS_OPEN (c IN INTEGER) RETUR

Documents

71946780 Manual Pl SQL Espanol