PDF de programación - PL/SQL: Introducción

PL/SQL: Introduccióónn
PL/SQL: Introducci

BDSI -- Grupo A
Grupo A
BDSI

Introduccióónn
Introducci

PL/SQL: Lenguaje imperativo para
PL/SQL: Lenguaje imperativo para
manejar los datos del esquema
manejar los datos del esquema
Bucles, procedimientos, funciones
Bucles, procedimientos, funciones
Tratamiento de excepciones
Tratamiento de excepciones
Cursores
Cursores
Triggers
Triggers

ÍÍndice
ndice

Primer Ejemplo: bloque anóónimonimo
Primer Ejemplo: bloque an

1.1.

2.2.

3.3.

4.4.

5.5.

6.6.

7.7.

8.8.

Bloques
Bloques
Variables y constantes
Variables y constantes
Select …… intointo
Select
Instrucciones de control
Instrucciones de control
Excepciones
Excepciones
Procedimientos
Procedimientos
Cursores
Cursores
Triggers
Triggers

SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON;

Esto es para que los mensajes que se muestran con dbms_output.put_line
Esto es para que los mensajes que se muestran con

dbms_output.put_line (el
(el
) se muestren por la salida estáándar. Por defecto est
ndar. Por defecto estáá

writeln) se muestren por la salida est

equivalente a writeln
equivalente a
a OFF. Sóólo es necesario hacerlo una vez.
lo es necesario hacerlo una vez.
a OFF. S

Un bloque an

Un bloque anóónimo en PL/SQL:
nimo en PL/SQL:

BEGIN
BEGIN

dbms_output.put_line(('Animula vagula, blandula, …');
dbms_output.put_line
END;
END;

El bloque se carga y ejecuta a la vez. No queda grabado y no pue

El bloque se carga y ejecuta a la vez. No queda grabado y no puede usarse
de usarse
de nuevo
de nuevo

Estructura de un bloque
Estructura de un bloque

Ver los errores
Ver los errores

DECLARE
DECLARE
/*variables, types
/*variables,

types, , andand local

local subprograms

subprograms. */
. */

BEGIN
BEGIN
/* /* Executable
/* /* ThisThis isis thethe onlyonly section

Executable section

section: : procedural

procedural andand SQL
block that

section ofof thethe block

SQL statements
that isis required

required. */
. */

statements gogo herehere. */
. */

EXCEPTION
EXCEPTION
/* /* Exception

Exception handling
*/ */
END;
END;

handling section

section: error

: error handling

handling statements

statements gogo herehere. .

A menudo obtendremos:
A menudo obtendremos:
Advertencia: Procedimiento creado con errores de

compilación.

Para ver qu

Para ver quéé error ha habido:
error ha habido:

SHOW ERRORS

Esto hay que hacerlo cada vez que hay un error
Esto hay que hacerlo cada vez que hay un error

1

Variables
Variables

Variables: ejemplo
Variables: ejemplo

DECLARE
DECLARE
nombre VARCHAR(20);
nombre VARCHAR(20);
---- tipo de la columna importe de ventas
tipo de la columna importe de ventas
precio ventas.importe%Type
precio
---- tipo de la fila ventas
tipo de la fila ventas
filaventas ventas%ROWTYPE
ventas%ROWTYPE;;
filaventas
---- una constante
una constante
limite CONSTANT NUMBER := 5000;
limite CONSTANT NUMBER := 5000;

ventas.importe%Type; ;

Valor por defecto de toda variable: NULL
Valor por defecto de toda variable: NULL
Se pueden asignar valores con :=
Se pueden asignar valores con :=

DECLARE
DECLARE

a NUMBER := 3;
a NUMBER := 3;

BEGIN
BEGIN

a := a + 1;
a := a + 1;

END;
END;
---- (es un bloque totalmente in

(es un bloque totalmente inúútil)til)

Select …… intointo
Select

Instrucciones de control: IfIf
Instrucciones de control:

DECLARE
DECLARE
trabajadores.nombre%Type; ;
n n trabajadores.nombre%Type
a a trabajadores.apellidos%TYPE
trabajadores.apellidos%TYPE; ;
BEGIN
BEGIN
SELECT nombre,apellidos INTO n,a
FROM trabajadores;
WHERE DNI=‘‘001001’’; ;
WHERE DNI=
INSERT INTO T1 VALUES(n,a
INSERT INTO T1
END;
END;

VALUES(n,a); );

Si no hay exactamente un par de valores

Si no hay exactamente un par de valores nombre,apellidos
provocaráá una excepci
provocar
única fila

una excepcióón n los select … into deben devolver una

nombre,apellidos se se



IF <IF <condition

condition> THEN <

> THEN <statement_list

statement_list> ELSE <

> ELSE <statement_list

statement_list> END IF;
> END IF;

IF <IF <condition
<<condition_n

condition_1> THEN ... ELSIF <

_1> THEN ... ELSIF <condition
condition_n> THEN ... ELSE ... END IF;
> THEN ... ELSE ... END IF;

condition_2> THEN ... ... ... ELSIF
_2> THEN ... ... ... ELSIF

DECLARE

a NUMBER;
b NUMBER;

BEGIN

ELSE

END IF;

END;

SELECT e,f INTO a,b FROM T1 WHERE e>1;
IF b=1 THEN

INSERT INTO T1 VALUES(b,a);

INSERT INTO T1 VALUES(b+10,a+10);

Instrucciones de control: loop
loop
Instrucciones de control:

Instrucciones de control: whilewhile, , forfor
Instrucciones de control:

DECLARE
DECLARE
i NUMBER := 1;
i NUMBER := 1;
BEGIN
BEGIN
LOOP
LOOP
INSERT INTO T1 VALUES(i,i
INSERT INTO T1
i := i+1;
i := i+1;
EXIT WHEN i>100;
EXIT WHEN i>100;
END LOOP;
END LOOP;
END;
END;

VALUES(i,i); );

Si se usa

Si se usa loop

loop hay que poner alg

hay que poner algúún n exitexit (con o sin

(con o sin whenwhen))

condition> LOOP
> LOOP

WHILE <condition
WHILE <
<<loop_body
END LOOP;
END LOOP;

loop_body> >

> IN <start

start>..<>..<finish

finish> LOOP
> LOOP

FOR <varvar> IN <
FOR <
<<loop_body
END LOOP;
END LOOP;

loop_body> >

Ejercicio: crear una tabla con un solo atributo numéérico
Ejercicio: crear una tabla con un solo atributo num

rico a y y

con 10 filas 1,2…….10.10
con 10 filas 1,2

2

Ejercicio: solucióónn
Ejercicio: soluci

Excepciones
Excepciones

drop table t;
drop table t;
create table t(at(a number primary key);
number primary key);
create table

DECLARE
DECLARE

i number;
i number;

BEGIN
BEGIN

For i in 1..10 loop
For i in 1..10 loop
insert into t values(i
insert into t
end loop;
end loop;
end;
end;

values(i););



Ejemplo:
Ejemplo:

DECLARE
DECLARE
---- Declaraciones
Declaraciones
BEGIN
BEGIN
---- Ejecuci
…………
…………

Ejecucióónn

NO_DATA_FOUND THENTHEN
ZERO_DIVIDE THENTHEN

Se ejecuta cuando ocurre una excepcióón de tipo
Se ejecuta cuando ocurre una excepcióón de tipo

EXCEPTION
EXCEPTION
WHEN NO_DATA_FOUND
WHEN
---- Se ejecuta cuando ocurre una excepci
WHEN ZERO_DIVIDE
WHEN
---- Se ejecuta cuando ocurre una excepci
WHEN OTHERS THEN
WHEN OTHERS THEN
---- Se ejecuta cuando ocurre una excepci
Se ejecuta cuando ocurre una excepcióón de un tipo no tratado
n de un tipo no tratado
en los bloques anteriores
---- en los bloques anteriores
ENDEND; ;

n de tipo NO_DATA_FOUND
NO_DATA_FOUND
n de tipo ZERO_DIVIDE
ZERO_DIVIDE

Excepciones en Oracle (I)
Excepciones en Oracle (I)

Excepciones en Oracle (II)
Excepciones en Oracle (II)

ACCESS_INTO_NULL El programa intent
ACCESS_INTO_NULL

El programa intentóó asignar valores a los atributos de un objeto no
asignar valores a los atributos de un objeto no

COLLECTION_IS_NULL El programa intent
COLLECTION_IS_NULL

El programa intentóó asignar valores a una tabla anidada a

asignar valores a una tabla anidada aúún no
n no

inicializado --6530
6530
inicializado

inicializada --6531
6531
inicializada

CURSOR_ALREADY_OPEN El programa intent
abrir un cursor que ya se encontraba abierto.
El programa intentóó abrir un cursor que ya se encontraba abierto.
CURSOR_ALREADY_OPEN
Recuerde que un cursor de ciclo FOR automááticamente lo abre y ello no se debe especificar con
ticamente lo abre y ello no se debe especificar con
Recuerde que un cursor de ciclo FOR autom
6511
la sentencia OPEN --6511
la sentencia OPEN
DUP_VAL_ON_INDEX El programa intent
El programa intentóó almacenar valores duplicados en una columna que se
almacenar valores duplicados en una columna que se
DUP_VAL_ON_INDEX

mantiene con restriccióón de integridad de un
mantiene con restricci

INVALID_CURSOR El programa intent
INVALID_CURSOR
INVALID_NUMBER En una sentencia SQL, la conversi
INVALID_NUMBER
nnúúmero falla cuando esa cadena no representa un n

index) ) --1 1
ndice úúnico (
El programa intentóó efectuar una operaci
lida sobre un cursor --1001
1001
En una sentencia SQL, la conversióón de una cadena de caracteres hacia un
n de una cadena de caracteres hacia un
mero váálido

mero falla cuando esa cadena no representa un núúmero v

unique index
n no váálida sobre un cursor

nico (unique
efectuar una operacióón no v

n de integridad de un ííndice

lido --1722
1722

El programa intentóó conectarse a Oracle con un nombre de usuario o

conectarse a Oracle con un nombre de usuario o password
password

LOGIN_DENIED El programa intent
LOGIN_DENIED
lido --1017
1017

invinváálido

NO_DATA_FOUND Una sentencia SELECT INTO no devolvi
NO_DATA_FOUND

Una sentencia SELECT INTO no devolvióó valores o el programa

valores o el programa referenci

referencióó un un

elemento no inicializado en una tabla indexada 100
elemento no inicializado en una tabla indexada 100

NOT_LOGGED_ON El programa efectu
NOT_LOGGED_ON

El programa efectuóó una llamada a Oracle sin estar conectado

una llamada a Oracle sin estar conectado --1012
1012

PROGRAM_ERROR PL/SQL tiene un problema interno
PROGRAM_ERROR
ROWTYPE_MISMATCH Los elementos de una asignaci
ROWTYPE_MISMATCH

PL/SQL tiene un problema interno --6501
6501
Los elementos de una asignacióón (el valor a asignar y la variable que lo
n (el valor a asignar y la variable que lo
n se presenta este error cuando un paráámetro
metro

) tienen tipos incompatibles. Tambiéén se presenta este error cuando un par

contendráá) tienen tipos incompatibles. Tambi
contendr
pasado a un subprograma no es del tipo esperado --6504
6504
pasado a un subprograma no es del tipo esperado

SELF_IS_NULL El par
SELF_IS_NULL
STORAGE_ERROR La memoria se termin
La memoria se terminóó o est
STORAGE_ERROR
SUBSCRIPT_BEYOND_COUNT El programa est
SUBSCRIPT_BEYOND_COUNT

corrupta --6500
6500
El programa está