PLSQL y paquetes

PL/SQL (Procedural Language/SQL )

/• BLOQUES PL/SQL

• TIPOS DE DATOSTIPOS DE DATOS

• FUNCIONES Y PROCEDIMIENTOS

• PAQUETES

1


• PL/SQL es el lenguaje de programación quePL/SQL es el lenguaje de programación que proporciona Oracle para extender el SQL estándar con otro tipo de estructuras eestándar con otro tipo de estructuras e instrucciones1. Variables y tipos

2. Estructuras de controlst uctu as de co t o• Bucles y sentencias IF‐THEN‐ELSE

3 Procedimientos y funciones3. Procedimientos y funciones

4. Tipos de objetos y métodos

2


• PL/SQL permite:PL/SQL permite:– Manipular los datos de la BDManejar errores– Manejar errores

• Definidos por el usuario (excepciones)• Propios del sistema (predefinidos)• Propios del sistema (predefinidos)

• La unidad básica de cualquier programa• La unidad básica de cualquier programa PL/SQL es el bloqueC d bl li id d ló i d• Cada bloque realiza una unidad lógica de trabajo en el programa, separando así unas

dtareas de otras3


• Estructura básica de un bloque:Estructura básica de un bloque:

DECLARE/* Declaración de uso local: variables, cursores, excepciones de usuario, …*/

BEGIN/* Sección ejecutable (obligatoria): órdenes SQL y procedimentales */

EXCEPTION EXCEPTION /* Zona de control de errores */

ENDEND;4

PL/SQL ‐ Declaración de variables

• Sección de Declaración de VariablesSección de Declaración de Variables– Contiene la declaración de:

i d d• tipos de datos,

• las constantes

• variables

– También se declaran cursores y excepcionesdefinidas por el usuario.

– Las variables externas se declaran en SQL*Plus y se escriben precedidas de ‘:’.

5


• SintaxisSintaxis

nombre variable [CONSTANT] tiponombre_variable [CONSTANT] tipo

[NOT NULL]

[ l i i i l][:= valor_inicial];

lá l i di l d fi i ió d• La cláusula CONSTANT indica la definición de una constante que debe ser inicializada y cuyo valor no

d difi dpuede ser modificado

• [NOT NULL]: Obliga a tener valor

6


• SintaxisSintaxis

nombre_variable [CONSTANT] tipo

[NOT NULL]

[:= valor_inicial];

• Tipo: puede ser:Tipo de datos: tipo de dato de la variable– Tipo de datos: tipo de dato de la variable

– Identificador%TYPE→ Se refiere al tipo usado por una columna de la tablacolumna de la tabla

– Identificador%ROWTYPE→ Es una fila variable con los mismos nombres y tipos que las columnas de una tabla omismos nombres y tipos que las columnas de una tabla o fila recuperada de un cursor

7


• Variables y constantes. EjemploVariables y constantes. Ejemplo

DECLAREv_DNI NUMBER (8,0);

v Nombre VARCHAR (30);_ ( );

c_Factor CONSTANT NUMBER(3,2):=0.10;

v DNI2 cliente DNI%TYPE;v_DNI2 cliente.DNI%TYPE;

v_precio NUMBER:= 300;

R li t li t %ROWTYPEcur_Rcliente cliente%ROWTYPE;

8


• CURSORESCURSORES

– Cursor es un área de trabajo definida para las consultas que devuelven más de una fila.

– Permite la manipulación de datosp

– Se le pueden pasar parámetros

• Tipos de cursores

– Cursor simple

– Cursores con paso de parámetrosp p

– Cursores con actualización9


• CURSOR simple. SintaxisCURSOR simple. Sintaxis

CURSOR nombre cursor IS sentencia_SELECT;

• EjemploEjemploDECLARE

….

CURSOR c_cliente IS

SELECT cliente DNI cliente nombreSELECT cliente.DNI, cliente.nombre

FROM cliente

WHERE cliente tipo trabajo=´jefe´;WHERE cliente.tipo_trabajo= jefe ;

10


• CURSORES CON PASO DE PARÁMETROS. SintaxisCURSORES CON PASO DE PARÁMETROS. Sintaxis

CURSOR nombre cursor [PARAMETROS] ISCURSOR nombre cursor [PARAMETROS] IS sentencia_SELECT;

• Los parámetros de un cursor se pueden utilizar paraLos parámetros de un cursor se pueden utilizar para definir variables con valores de entrada.

• La consulta SQL asociada se ejecuta utilizando esos• La consulta SQL asociada se ejecuta utilizando esos valores

PARAMETROS• PARAMETROS ‐>

(nombre_parametro tipo_parametro, …)

11


• CURSORES CON PASO DE PARÁMETROS. SintaxisCURSORES CON PASO DE PARÁMETROS. Sintaxis

CURSOR nombre cursor [PARAMETROS] ISCURSOR nombre cursor [PARAMETROS] IS sentencia_SELECT;

• EJEMPLO:EJEMPLO:CURSOR cur_cliente (v_provincia cliente.provincia%TYPE) IS

SELECT dni nombreSELECT dni, nombre

FROM clientes

WHERE clientes provincia= v provincia;WHERE clientes.provincia= v_provincia;

12


• CURSORES CON ACTUALIZACIÓN. SintaxisCURSORES CON ACTUALIZACIÓN. Sintaxis

CURSOR nombre cursor IS

sentencia_SELECT

FOR UPDATE [OF nombre columna][ ]

S d j d difi l fil• Se dejan preparados para modificar las filas devueltas y se generan bloqueos exclusivos sobre l fil tilas filas activas

13


• CURSORES CON ACTUALIZACIÓN. SintaxisCURSORES CON ACTUALIZACIÓN. Sintaxis

CURSOR nombre cursor IS

sentencia_SELECT

FOR UPDATE [OF nombre columna][ ]

• EJEMPLO: lCURSOR cur_cliente IS

SELECT DNI, nombre

FROM cliente

WHERE tipo_trabajo=´jefe´

FOR UPDATE OF salario;14


• OPERACIONES CON CURSORESOPERACIONES CON CURSORES

– OPEN: abre los cursores

FETCH l l d t– FETCH : lee los datos

– CLOSE: cierra los cursores

• Pasos:1.‐ Declarar el cursor

2.‐ Abrir el cursor en la zona de procedimiento

3.‐ Leer el cursor. Es necesario ejecutar un bucle para leerj p

todos los cursores

4.‐ Cerrar el cursor

15


• ATRIBUTOS DE LOS CURSORESATRIBUTOS DE LOS CURSORES

– Se le añaden al nombre del cursor

%NOTFOUND → Se usa para detectar el final de un cursor. Devuelve TRUE si la ultima lecturaun cursor. Devuelve TRUE si la ultima lectura falla porque no hay filas disponibles y FALSE si recuperarecupera

%FOUND → El contrario de %NOTFOUND

%ROWCOUNT D l l ú d fil l íd%ROWCOUNT → Devuelve el número de fila leída

%ISOPEN→ Devuelve TRUE si el cursor esta abierto y FALSE si no lo está

16

PL/SQL – Declaración de variables

• Ejemplo uso cursorEjemplo uso cursor

DECLARE

CURSOR cur_emp IS SELECT DNI, nombre FROM empleado;

emp registro cur emp%ROWTYPE;emp registro cur emp%ROWTYPE;

BEGIN

OPEN cur_emp;

LOOP

FETCH cur_emp INTO emp_registro;

EXIT WHEN %NOTFOUNDEXIT WHEN cur emp%NOTFOUND;

...

END LOOP;END LOOP;

CLOSE cur_emp;

END;17


• EXCEPCIONESEXCEPCIONES

– Excepciones predefinidas en PL/SQL que informan de los errores producidos en la ejecución de las sentencias SQL por parte del sistema de gestión de bases de datos.

– Además de éstas, el programador puede definir , p g pexcepciones de uso específico.

– Controlan los errores de ejecución de unControlan los errores de ejecución de un programa, previstos por el programador

El control de las excepciones de usuario es– El control de las excepciones de usuario es enteramente gestionado por él. 18


• EXCEPCIONES. SintaxisEXCEPCIONES. Sintaxis

nombre_excepción EXCEPTION;

– Cada excepción trata un error

– No son variables y no se las puede tratar comoNo son variables y no se las puede tratar como tal

No pueden utilizarse como argumentos en– No pueden utilizarse como argumentos en funciones ni en procedimientos

S ti di t l t i RAISE– Se activan mediante la sentencia RAISE

RAISE nombre_excepción;

19

PL/SQL – Sección de excepciones

• EjemploEjemploDECLARE

e hay emp EXCEPTION;e_hay_emp EXCEPTION;

v_dep departamento.depnum%TYPE := 10;

BEGINIF (SELECT COUNT(*) FROM empleados WHERE depnum = v_dep)=0

THEN RAISE e_dep_vacio;

ELSE END IF;ELSE … END IF;

…

END;

20

PL/SQL – Tipos de datos estructurados

• Entre los tipos de datos estructurados queEntre los tipos de datos estructurados que proporciona PL/SQL están:

– los registros (RECORD)g ( )

– los vectores (TABLE y VARRAY).

• Se declaran en la sección DECLARE

21


• REGISTROS – SintaxisREGISTROS Sintaxis

TYPE nombre_tipo IS RECORD

(campo tipo[, campo tipo] ...);

– Ejemplo:TYPE tipo_empleado_reg IS RECORD

( nombre VARCHAR2(10),( ( ),

puesto VARCHAR2(8),

sueldo NUMBER(6)( )

);22


• REGISTROS – creación de variablesREGISTROS creación de variables

Nombre_variable tipo_reg;

– Ejemplo:

v_empleado_reg tipo_empleado_reg;

• Referenciar las variables

Nombre_variable.campo:=valor

23


• VECTORES Y TABLAS – SintaxisVECTORES Y TABLAS Sintaxis

TYPE nombre_tipo IS VARRAY (tamaño_max)

OF tipo_datos [NOT NULL];

TYPE nombre_tipo IS TABLE


– La tabla puede crecer ilimitadamente

– El vector solo puede crecer hasta el tamañoEl vector solo puede crecer hasta el tamaño máximo definido

24


• VECTORESVECTORES

TYPE nombre_tipo IS VARRAY (tamaño_max)


– Para poder utilizar los vectores, deben ser previamente creados vacíos o con elementos.

– Para insertar elementos adicionales se tienen quePara insertar elementos adicionales se tienen que extender mediante la cláusula EXTEND

– En los vectores no se pueden borrar elementos– En los vectores no se pueden borrar elementos25


• VECTORESVECTORES– Ejemplo:

DECLAREDECLARE

TYPE t_varray IS VARRAY (50) OF empleado.nombre%TYPE;

v varray t varray;v_varray t_varray;

BEGIN

...

v_varray := t_varray(‘Ana’, ‘Lola’);‐‐ se crea con dos elementos

v_varray.EXTEND;

v varray(3) ‘Luis’v_varray(3) := ‘Luis’;

v_varray.EXTEND;

v varray(4) := ‘Juan’; _ y( ) ;

....

END; 26


• TABLASTABLAS TYPE nombre_tipo IS TABLE

OF tipo datos [NOT NULL]OF tipo_datos [NOT NULL];

– En los tipo TABLE se pueden borrar elementos con la instrucción DELETE, pudiendo quedar huecos intermedios vacíos

– Se pueden volver a llenar con una asignación. La función EXISTS nos permite saber si un elemento se puede referenciar o ha sido borrado

27


• TABLAS – EjemploTABLAS EjemploDECLARE

TYPE t table IS TABLE OF empleado.nombre%TYPE;_ p ;

v_table t_table;

BEGIN

v_table := t_table(‘Ana’, ‘Lola’);

v_table(2) := NULL; ‐‐Dejar una posición vacía

v table1 DELETE(1); Así es como se borra una posiciónv_table1.DELETE(1);‐‐Así es como se borra una posición

v_table1.EXTEND;

v table1(3) := ‘Luis’;_ ( ) ;

IF v_table1(1).EXISTS

THEN ...;

ELSE v_table1(1) := ‘Pepe’; END IF;

END; 28


• VECTORES Y TABLAS – Métodos predefinidosVECTORES Y TABLAS Métodos predefinidos– COUNT → Devuelve el número de filas de la tabla

DELETE (nº)→ Borra las filas de una tabla– DELETE (nº) → Borra las filas de una tabla

– EXITS (nº) → Devuelve TRUE si existe en la tabla la fila especificadaespecificada

– FIRST → Devuelve el índice de la primera fila

– LAST→ Devuelve el índice de la última fila– LAST → Devuelve el índice de la última fila

– NEXT → Devuelve el índice de la fila de la tabla que sigue a la fila especificadala fila especificada

– PRIOR → Devuelve el índice de la fila de la tabla que precede a la fila especificadap p

29

PL/SQL – Sección Ejecutable

• Sección EjecutableSección Ejecutable

– Tres clases de instrucciones

• Instrucciones de asignacióng

• Instrucciones de control de flujo• Bucles• Bucles

– Instrucciones no permitidas son:

DROP CREATE ALTERDROP, CREATE, ALTER, …30


• Instrucciones de asignaciónInstrucciones de asignación

variable := expresión;p ;

• Expresiones• Expresiones:– Pueden incluir literales, variables y constantes definidas en el bloque

– También funciones aplicadas sobre literales, constantes y variables.

31


• Instrucciones de asignación‐ Asignación deInstrucciones de asignación Asignación de sentencias SQL

– Es posible realizar la asignación del resultado de una SELECT a una lista de variables o a un cursor.

– Si se asigna a una lista de variables, la consulta asociada sólo debe dar como resultado una únicaasociada sólo debe dar como resultado una única fila (en caso contrario se genera una excepción)

– Cuando no se conoce a priori el número de filas– Cuando no se conoce a priori el número de filas del resultado, resulta más conveniente utilizar cursorescursores.

32


• Literales:Literales:– Las cadenas de caracteres se delimitan por la comilla simplecomilla simple.

– Los números reales pueden especificarse tanto en formato decimal como científico

– Operadores sobre Números: +, ‐, *, /, **, MOD

– Operadores sobre cadenas: || (concatenación)Operadores sobre cadenas: || (concatenación).

– Operadores lógicos: AND, OR, NOT.

33


• Literales:Literales:– Operadores sobre cursores:

%ROWCOUNT, %NOTFOUND,

%FOUND, %ISOPEN.

– Comparadores clásicos:

< <= = != ^= < =<, <=, =, !=, =, <, =

– Comparadores SQL:

[ ] [ ][NOT] LIKE, IS [NOT] NULL,

[NOT] BETWEEN..AND..,

[NOT] IN34


• Funciones:Funciones:– Funciones sobre cadenas de caracteres:

ASCII CHR LENGTH LOWER SUBSTR UPPERASCII, CHR, LENGTH, LOWER, SUBSTR, UPPER, …

– Funciones numéricas: ABS, FLOOR, MOD,ROUND, SQRT, TRUNCTRUNC, …

– Funciones sobre fechas: ADD_MONTHS, LAST_DAY, MONTHS BETWEEN, NEXT DAY, SYSDATE, TRUNC.MONTHS_BETWEEN, NEXT_DAY, SYSDATE, TRUNC.

– Funciones de conversión: TO_CHAR, TO_DATE, TO_NUMBER._

– Funciones de control de errores: SQLCODE, SQLERRM

– …

35

PL/SQL – Sentencias de Control de flujo

• Sentencias de Control de flujoSentencias de Control de flujo– Realizan el control del comportamiento del bloquebloque.

– Tipos:

• Condicionales (IF)• Bucles

–Los bucles permiten repetir un número de veces un conjunto de instruccionesveces un conjunto de instrucciones PL/SQL.

Para romper el bucle se usa EXIT GOTO o–Para romper el bucle se usa EXIT, GOTO o RAISE 36


• CONDICIONALES (IF)CONDICIONALES (IF)– Ejecuta una o varias sentencias dependiendo de una condiciónuna condición

IF condición THEN instrucciones;

[ELSIF condición THEN instrucciones]

[ELSE instrucciones];

……

EN DIF

37


• BUCLESBUCLES– Bucles simples (LOOP).

– Bucles condicionales (WHILE)

– Bucles numéricos (FOR)

– Bucles sobre cursores

– Bucles sobre sentencias SELECTBucles sobre sentencias SELECT

38


• BUCLES SIMPLES (LOOP) – SintaxisBUCLES SIMPLES (LOOP) Sintaxis[nombre_bucle] LOOP sentencias;

END LOOPEND LOOP;

• EjemploDECLARE

v_contador BINARY_INTEGER:=1;

BEGINBEGIN

LOOP

INSERT INTO tabla_temporal

S ( d j l )VALUES (v contador,’ejemplo’);

v_contador:=v_contador+1;

EXIT WHEN v_contador>50;

END LOOP;

END; 39


• BUCLES CONDICIONALES (WHILE)BUCLES CONDICIONALES (WHILE)– La condición se evalúa antes de entrar en el buclebucle[nombre_bucle] WHILE condición

LOOP

sentencias;

END LOOP;

40


• BUCLES CONDICIONALES (WHILE)BUCLES CONDICIONALES (WHILE)– Ejemplo

DECLAREDECLARE

v_contador BINARY_INTEGER:=1;

BEGIN

WHILE v_contador<=50 LOOP


VALUES (v_contador,’ejemplo’);

v_contador:=v_contador+1;

END LOOP;

END;41


• BUCLES NUMÉRICOS (FOR)BUCLES NUMÉRICOS (FOR)– Se ejecutan una sola vez por cada elemento de rango definidorango definido[nombre_bucle] FOR indice IN [REVERSE] e p n1 e p n2 LOOPexp n1..exp n2 LOOP

sentencias;

END LOOPEND LOOP;

– indice: variable numérica de control empieza en 1 i 2exp_n1 y termina en exp_n2 .

– Se declara implícitamente como un BINARY_INTEGER

42


• BUCLES NUMÉRICOS (FOR)BUCLES NUMÉRICOS (FOR)– Se ejecutan una sola vez por cada elemento de rango definidorango definido[nombre_bucle] FOR indice IN [REVERSE] e p n1 e p n2 LOOP[REVERSE] exp n1..exp n2 LOOP

sentencias;

END LOOPEND LOOP;

[ V S ] L h l é– [REVERSE]: La cuenta se hace al revés

– Exp_n1, y exp_n2 pueden ser ctes o cualquier expresión que devuelva un número

43


• BUCLES NUMÉRICOS (FOR)BUCLES NUMÉRICOS (FOR)– Ejemplo

BEGIN

FOR v_contador IN 1..50 LOOP


VALUES (v_contador,’ejemplo’);

END LOOP;

END;

44


• BUCLES SOBRE CURSORESBUCLES SOBRE CURSORES

– En un bucle sobre cursores se declara de modo implícito la variable fila asociada.

– Asimismo, se realiza un OPEN del cursor al entrar en el bucle y un CLOSE al saliry

– Se produce un FETCH implícito en cada iteración del bucledel bucle.

45


• BUCLES SOBRE CURSORESBUCLES SOBRE CURSORES– Ejemplo:

DECLAREDECLARE

CURSOR cur_emp IS

SELECT num_empleado, nomb_empleado FROM empleado;

BEGINBEGIN

FOR emp_registro IN cur_emp LOOP

IF i t d d 30 THENIF emp_registro.edad>30 THEN ...

END LOOP;

END;46


• BUCLES SOBRE SENTENCIAS SELECTBUCLES SOBRE SENTENCIAS SELECT– Es el mismo concepto anterior pero sin declaración de cursoresdeclaración de cursores

[nombre bucle][nombre_bucle]

FOR nombre_registro IN (sentencia_select)

LOOP t iLOOP sentencias

END LOOP;

– Cuando se sale con exit o error del bucle el cursor interno que genera ORACLE se cierracursor interno que genera ORACLE se cierra

47


• BUCLES SOBRE SENTENCIAS SELECTBUCLES SOBRE SENTENCIAS SELECT– Ejemplo

BEGIN

FOR registro IN (SELECT DNI, nombre FROM cliente)g ( , )

LOOP

INSERT INTO tabla temporal_ p

(contador, texto)

VALUES (registro.DNI, registro.nombre);VALUES (registro.DNI, registro.nombre);

END LOOP;

END;END;

48


• GOTO y ETIQUETASGOTO y ETIQUETAS– transfiere el control a la sentencia o bloque siguiente a la etiqueta indicadasiguiente a la etiqueta indicada<<etiqueta>>

GOTO <<etiqueta>>;

– La sentencia puede ir a otro bloque o sub‐bloque pero nunca a la zona de excepciones.

– La sentencia siguiente a la etiqueta debe ser un

ejecutable.

– No se puede realizar saltos al interior de un IF o– No se puede realizar saltos al interior de un IF o de un bucle. 49


• Manejo de excepcionesManejo de excepciones

– Permite responder ante cualquier problema que– Permite responder ante cualquier problema que pueda ocurrir en la ejecución de cualquier operaciónoperación

– La ejecución de la aplicación finaliza al t l úpresentarse algún error grave

– Mediante el manejo de excepciones es posible realizar una serie de acciones y continuar la ejecución de la aplicación.

50



WHEN excepciones THEN instrucciones;WHEN excepciones THEN instrucciones;

– Donde excepciones:

excep_múltiples | OTHERS

– Donde excep múltiples:Donde excep_múltiples:

excepción | excepción OR excep_múltiples

51



– La activación de una excepción la realiza el– La activación de una excepción la realiza el sistema o el programa mediante utilización de la sentencia RAISEsentencia RAISE

– Cuando una excepción se activa, la ejecución ti ú l t d j d dcontinúa en la parte de manejadores de

excepciones

– Si no existe la excepción activada en la sección de excepciones, la excepción sigue activa por si tuviera que ser tratada en otro nivel.

52


• En la parte de manejadores la ejecución seEn la parte de manejadores, la ejecución se realiza del modo siguiente:

S b j d d l ió– Se busca un manejador que corresponda con la excepción activada,

La cláusula OTHERS corresponde a todas las excepciones– La cláusula OTHERS corresponde a todas las excepciones

– Es conveniente poner la cláusula OTHERS la última

Una vez encontrado el manejador se desactiva la– Una vez encontrado el manejador, se desactiva la excepción y se ejecutan las instrucciones asociadas

– Si se deseara mantener activa la excepción o se desea– Si se deseara mantener activa la excepción, o se desea activar cualquier otra, es posible incluir la sentencia RAISE que finalizaría el bloque y activaría la excepción asociada.q q y p

53


• Excepciones predefinidas por Oracle:Excepciones predefinidas por Oracle:

– NO_DATA_FOUND, TOO_MANY_ROWS →

Ocurren cuando un select no selecciona nada o selecciona varias filas cuando sólo se esperaba una

– INVALID_NUMBER, VALUE_ERROR, ZERO_DIVIDE →S d i i lid d t t i t dSe producen por operaciones invalidas de tratamiento de números

DUP VAL ON INDEX→– DUP_VAL_ON_INDEX →Se produce cuando se intenta insertar una clave primaria

duplicadaduplicada.

54


• Excepciones predefinidas por Oracle:Excepciones predefinidas por Oracle:

– CURSOR_ALREADY_OPEN →Ocurre al intentar abrir un cursor ya abierto

– INVALID_CURSOR →Ocurre al intentar hacer una operación invalida sobre un cursor

– PROGRAM_ERROR, STORAGE_ERROR, TIMEOUT ON RESOURCE→TIMEOUT_ON_RESOURCE →Detectan errores de almacenamiento o de ejecución.

55


• Manejo de excepciones – EjemploManejo de excepciones EjemploDECLARE

e hay emp EXCEPTION;e_hay_emp EXCEPTION;

v_dep departamento.depnum%TYPE := 10;

BEGINIF (SELECT COUNT(*) FROM empleados WHERE depnum = v_dep)=0

THEN RAISE e_dep_vacio;

ELSE END IF;ELSE … END IF;

EXCEPTION

WHEN e dep vacio THEN RAISE APPLICATION ERROR(‐20001, ‘ElWHEN e_dep_vacio THEN RAISE_APPLICATION_ERROR( 20001, El dep. TO_CHAR(v_dep) no tiene empleados .’);

WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘abortado por d id ’)error desconocido’);

END; 56

PLSQL y paquetes

Education

Transcript of PLSQL y paquetes