pl sql -oraclepdi.topografia.upm.es/m.manso/docencia/bbddee2010/5.-plsql_orac… · •sql es un...

30/11/2010

1

PL SQL -Oracle

Miguel Ángel Manso

ETSI en Topografía, Geodesia y Cartografía - UPM

Índice

• ¿Qué es PL/SQL?

• ¿Qué permite?

• Generalidades:

– fundamentos,

– delimitadores,

– literales,

– tipos de datos

• PL/SQL en Oracle

30/11/2010

2

Introducción. ¿Qué es PL SQL?

• SQL es un lenguaje de consulta para los sistemas de bases de datos relaciónales, si bien no posee la potencia de los lenguajes de programación

• PL/SQL amplia SQL con los elementos característicos de los lenguajes de programación, variables, sentencias de control de flujo, bucles ...

• Unifica la capacidad de consulta del SQL y la versatilidad de los lenguajes de programación tradicionales. PL/SQL es el lenguaje de programación que proporcionan las bases de datos como Oracle, PostGreSQL, etc. para extender el SQL estándar con otro tipo de instrucciones

¿Qué permite PL SQL?

• Desarrollar unidades de programa para las bases de datos como:

– Procedimientos almacenados

– Funciones

– Triggers

– Scripts

30/11/2010

3

Fundamentos

• No es sensible a las mayúsculas/minúsculas

• Las UNIDADES LEXICAS son:

– DELIMITADORES

– IDENTIFICADORES

– LITERALES

– COMENTARIOS

– EXPRESIONES

Delimitadores e identificadores

• DELIMITADOR: Es un símbolo simple o compuesto que tiene una función especial en PL/SQL. Estos pueden ser: – Operadores Aritméticos – Operadores Lógicos – Operadores Relacionales

• IDENTIFICADOR: Son empleados para nombrar objetos de programas en PL/SQL así como a unidades dentro del mismo, estas unidades y objetos incluyen: – Constantes – Cursores – Variables – Subprogramas – Excepciones – Paquetes

30/11/2010

4

Literales y comentarios

• LITERAL: Es un valor de tipo numérico, carácter, cadena o lógico no representado por un identificador (es un valor explícito)

• COMENTARIO: Es una aclaración que el programador incluye en el código. Son soportados 2 estilos de comentarios, el de línea simple y de multi-línea, para lo cual son empleados ciertos caracteres especiales como son: -- Línea simple/*

Conjunto de Líneas*/

Tipos de datos

• Cada constante y variable esta asociado a un tipo de dato que define el formato de almacenamiento, las restricciones y el rango de valores permitidos

• PL/SQL proporciona una variedad predefinida de tipos de datos . Casi todos los tipos de datos manejados por PL/SQL son similares a los soportados por SQL del SGBD

30/11/2010

5

PL/SQL ORACLE

Contenido

• Operadores• Estructuras de control• Bloques• Cursores• Excepciones• Operaciones con cursores• Procedimientos• Funciones• Triggers• Creación de paquetes• Uso de Registros y Tablas con PL/SQL• Varrays• SQL dinámico• Funciones disponibles

30/11/2010

6

Operadores

• Asignación (=):= (dos puntos + igual)

• Operadores aritméticos+ (suma) - (resta) * (multiplicación)/ (división) ** (exponente)

• Operadores de relación o de comparación= (igual a) <> (distinto de) < (menor que) > (mayor que) >= (mayor o igual a) <= (menor o igual a)

• Operadores lógicosAND (y lógico)NOT (negación)OR (o lógico)

• Operador de concatenación||

Estructuras de control• Control de flujo

IF (expresión) THEN

-- Instrucciones

ELSIF (expresión) THEN

-- Instrucciones

ELSE

-- Instrucciones

END IF;

• Sentencia GOTO

DECLAREflag NUMBER;

BEGINflag :=1 ;IF (flag = 1) THEN

GOTO paso2;END IF;

<<paso1>>dbms_output.put_line('Ejecución de paso 1');

<<paso2>>dbms_output.put_line('Ejecución de paso 2');

END;

30/11/2010

7

Estructuras de control

• LOOPLOOP

-- InstruccionesIF (expresión) THEN

-- InstruccionesEXIT;

END IF; END LOOP;

• WHILEWHILE (expresión) LOOP

-- InstruccionesEND LOOP;

• FORFOR contador IN [REVERSE] inicio..final LOOP

-- Instrucciones END LOOP;

Bloques anónimoDECLARE /* declaraciones */

variable NUMBER;

BEGIN /* ejecución */SELECT…

EXCEPTION /* excepciones */ WHEN OTHERS THENdbms_output.put_line('Se ha producido un error');

END;

30/11/2010

8

Bloque no anónimo (procedimiento)

CREATE PROCEDURE procedimiento_simple IS

DECLARE

BEGIN

EXCEPTION

END;

Ejemplo de procedimiento

CREATE OR REPLACE PROCEDURE SYSTEM.CREA_ALUM (DNI IN VARCHAR2) ASsql_str VARCHAR2(1000);

BEGINsql_str := ('CREATE USER A'||DNI||' IDENTIFIED BY A'||DNI); EXECUTE IMMEDIATE sql_str; COMMIT;sql_str := ('GRANT Alum_BDE TO A'||DNI); EXECUTE IMMEDIATE sql_str; COMMIT;sql_str := ('GRANT CREATE TABLE TO A'||DNI);EXECUTE IMMEDIATE sql_str; COMMIT;sql_str := ('GRANT CREATE ANY INDEX TO A'||DNI);EXECUTE IMMEDIATE sql_str; COMMIT;sql_str := ('GRANT CREATE SEQUENCE TO A'||DNI);EXECUTE IMMEDIATE sql_str; COMMIT;sql_str := ('ALTER USER A'||DNI||' QUOTA 100M ON USERS');EXECUTE IMMEDIATE sql_str; COMMIT;sql_str := ('CREATE SYNONYM A'||DNI||'.CRUCE FOR SYSTEM.CRUCE');EXECUTE IMMEDIATE sql_str; COMMIT;

END CREA_ALUM;

30/11/2010

9

Cursores

• Es: el conjunto de registros resultado de una instrucción SQL. También son fragmentos de memoria reservados para procesar los resultados de una consulta

• Tipos:

– Implícitos: consulta genera un único registro (SELECT INTO)

– Explícitos: consulta genera varios (pensado programación)

Excepciones de los cursores implícitos

• NO_DATA_FOUND: Se produce cuando una sentencia SELECT intenta recuperar datos pero ninguna fila satisface sus condiciones. Es decir, cuando "no hay datos“

• TOO_MANY_ROWS: Dado que cada cursor implícito sólo es capaz de recuperar una fila , esta excepción detecta la existencia de más de una fila

30/11/2010

10

Cursores

• Simples:

declarecursor nombre is SELECT ……[FOR UPDATE];

• Con parámetros:

declare cursor nombre (par1 IN tipo1) is SELECT ….;

La idea es que el cursor es como una función devolverá resultados dependiendo de los parámetros distintos registros

Ejemplo de cursores

CURSOR datos_tramo IS SELECT id_vial, id_tramo FROM tramo;

CURSOR datos_vial IS SELECT id_vial, ine_mun_pr, dgc_viaFROM vialWHERE (dgc_via <> 0 AND dgc_via IS NOT NULL)

OR ((UPPER (tip_via)) NOT LIKE '%CTRA%');

CURSOR datos_ccpp (geo municipio.geom%TYPE) IS SELECT id_ccpp, geom FROM ccpp WHERE ( (sdo_geom.relate(geo, geom, ‘mask=CONTAINS', 0.5) = ‘TRUE') OR (sdo_geom.relate (geo, geom, ‘mask=COVERS’, 0.5) = ‘TRUE‘) );

30/11/2010

11

Operaciones con cursores

• Declarar el cursor (revisado)

• Abrir el cursor con la instrucción OPEN

• Leer los datos del cursor con la instrucción FETCH, FOR

• Cerrar el cursor y liberar los recursos con la instrucción CLOSE

Abrir, leer datos y cerrar un cursor

• Abrir:

– OPEN nombre_cursor;

– OPEN nombre_cursor(valor1, valor2, ..., valorN);

• Leer datos:

– FETCH nombre_cursor INTO lista_variables

– FETCH nombre_cursor INTO registro_PL/SQL;

• Cerrar cursor:

– CLOSE nombre_cursor;

30/11/2010

12

Ejemplos apertura y lectura

DECLARECURSOR cpaises ISSELECT CO_PAIS, DESCRIPCION,

CONTINENTE FROM PAISES;

co_pais VARCHAR2(3);descripcion VARCHAR2(50);continente VARCHAR2(25);

BEGINOPEN cpaises;FETCH cpaises INTO

co_pais,descripcion,continente;

….CLOSE cpaises;

END;

DECLARE CURSOR cpaises (p_continente

VARCHAR2) ISSELECT CO_PAIS, DESCRIPCION,

CONTINENTE FROM PAISESWHERE CONTINENTE = p_continente;

registro cpaises%ROWTYPE;

BEGINOPEN cpaises('EUROPA');FETCH cpaises INTO registro;

…….CLOSE cpaises;

END;

Más ejemplos (bucles-cursores)

DECLARECURSOR cpaises ISSELECT COD_PAIS, DESC, CONTINENTE FROM PAISES;

cod_pais VARCHAR2(3);desc VARCHAR2(50);continente VARCHAR2(25);

BEGINOPEN cpaises;LOOP

FETCH cpaises INTO cod_pais, desc, continente;EXIT WHEN cpaises%NOTFOUND;

END LOOP; CLOSE cpaises;

END;

30/11/2010

13

Otro ejemplo con control While

OPEN nombre_cursor;

FETCH nombre_cursor INTO lista_variables;

WHILE nombre_cursor%FOUND LOOP

FETCH nombre_cursor INTO lista_variables;

END LOOP;

CLOSE nombre_cursor;

Otro ejemplo con FOR

area_cp := 0;

FOR var_datos2 IN datos_ccpp(var_datos1.geom)

LOOP

area_cp := area_cp + sdo_geom.sdo_area(var_datos2.geom, 0.5, 'unit=HECTARE');

END LOOP;

Geometría de un municipio

30/11/2010

14

Excepciones

Predefinidas

DECLARE

BEGIN

EXCEPTIONWHEN NO_DATA_FOUND THENWHEN ZERO_DIVIDE THENWHEN OTHERS THEN

END;

Definidas por usuarioDECLARE

VALOR_NEGATIVO EXCEPTION;

valor NUMBER;

BEGIN

valor := -1;

IF valor < 0 THEN

RAISE VALOR_NEGATIVO;

END IF;

EXCEPTION

WHEN VALOR_NEGATIVO THEN

END;

Procedimientos almacenados

CREATE [OR REPLACE] PROCEDURE<procedure_name> [(<param1> [IN|OUT|IN OUT] <type>, <param2> [IN|OUT|IN OUT] <type>, ...)] IS-- Declaración de variables locales

BEGIN-- Sentencias

[EXCEPTION]-- Sentencias control de excepción

END [<procedure_name>];

30/11/2010

15

Ejemplo procedimiento

CREATE OR REPLACE PROCEDURE Actualiza_Saldo(cuenta NUMBER, nuevo_saldo NUMBER DEFAULT 10 ) IS

BEGINUPDATE SALDOS_CUENTAS SET SALDO = nuevo_saldo, FX_ACTUALIZACION = SYSDATEWHERE CO_CUENTA = cuenta;

END Actualiza_Saldo;

Uso:BEGIN

Actualiza_Saldo(200501,2500);COMMIT;

END;

Otro ejemplo de procedimiento

CREATE OR REPLACE PROCEDURE quinta_comple (nombre IN VARCHAR2) ASCURSOR datos IS SELECT * FROM toponimia WHERE id_topo IS NULL OR

ine_mun IS NULL OR texto IS NULL OR tipo IS NULL OR fuente IS NULL OR geom IS NULL;

nombre_exp VARCHAR2 (50);BEGIN

nombre_exp := nombre;

FOR var_datos IN datosLOOP

INSERT INTO errores.errores VALUES (error.NEXTVAL, 24, '2', var_datos.id_topo, 'toponimia', nombre_exp, NULL);COMMIT;

END LOOP;END quinta_comple;

30/11/2010

16

Funciones

CREATE [OR REPLACE]FUNCTION <fn_name>[(<param1> IN <type>, <param2> IN <type>, ...)] RETURN <return_type>IS result <return_type>;

BEGINreturn(result);

[EXCEPTION]-- Sentencias control de excepción

END [<fn_name>];

Ejemplo de función

CREATE OR REPLACE FUNCTION fn_Obtener_Precio(precio_producto VARCHAR2) RETURN NUMBER IS result NUMBER;

BEGINSELECT PRECIO INTO resultFROM PRECIOS_PRODUCTOSWHERE CO_PRODUCTO = precio_producto;return(result);

EXCEPTION WHEN NO_DATA_FOUND THENreturn 0;

END ;

Ejemplo de Utilización:DECLARE

Valor NUMBER;BEGIN

Valor := fn_Obtener_Precio('000100');END;

30/11/2010

17

Otro ejemplo de función

CREATE FUNCTION celsius_to_fahrenheit (degree NUMBER) RETURN NUMBER IS

buffer NUMBER;

BEGIN

buffer := (degree * 9/5) + 32;

RETURN buffer;

END celsius_to_fahrenheit;

Función no almacenada, ejemplo:DECLARE

CURSOR myAllLecturer IS SELECT first_name, last_name FROM lecturer;

v_FormattedName VARCHAR2(50);

FUNCTION FormatName(p_FirstName IN VARCHAR2, p_LastName IN VARCHAR2) RETURN VARCHAR2 IS

BEGIN

RETURN p_FirstName || ' ' || p_LastName;

END FormatName;

BEGIN

FOR v_StudentRecord IN myAllLecturer LOOP

v_FormattedName := FormatName(v_StudentRecord.first_name, v_StudentRecord.last_name);

DBMS_OUTPUT.PUT_LINE(v_FormattedName);

END LOOP;

END;

/

30/11/2010

18

Declaración de Trigger

• Un trigger es un bloque PL/SQL asociado a una tabla, que se ejecuta como consecuencia de una determinada instrucción SQL sobre dicha tabla

• Crear Trigger:CREATE [OR REPLACE] TRIGGER <nombre_trigger>

{BEFORE|AFTER} {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN][OR {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN]...]}ON <nombre_tabla>[FOR EACH ROW [WHEN (<condición>)]]

DECLARE-- variables locales

BEGIN-- Sentencias

[EXCEPTION]-- Sentencias control de excepción

END <nombre_trigger>;

Ejemplo de Trigger

CREATE OR REPLACE TRIGGER TR_PRODUCTOS_01AFTER INSERT ON PRODUCTOSFOR EACH ROW

DECLARE

BEGININSERT INTO PRECIOS_PRODUCTOS(CO_PRODUCTO,PRECIO,FX_ACTUALIZACION)VALUES(:NEW.CO_PRODUCTO,100,SYSDATE);

END ;

30/11/2010

19

Creación de paquetes PLSQL

• Un paquete es una estructura que agrupa objetos de PL/SQL compilados(procedimientos, funciones, variables, tipos ...) en la base de datos

• Creación de paquetes:CREATE [OR REPLACE] PACKAGE <pkgName> IS

-- Declaraciones de tipos y registros públicas{[TYPE <TypeName> IS <Datatype>;]}-- Declaraciones de variables y constantes públicas-- También podemos declarar cursores

{[<ConstantName> CONSTANT <Datatype> := <valor>;]}{[<VariableName> <Datatype>;]}-- Declaraciones de procedimientos y funciones públicas

{[FUNCTION <FunctionName>(<Parameter> <Datatype>,...) RETURN <Datatype>;]}{[PROCEDURE <ProcedureName>(<Parameter> <Datatype>, ...);]}

END <pkgName>;

Registros

• Un registro es una estructura de datos en PL/SQL, almacenados en campos, cada uno de los cuales tiene su propio nombre y tipo y que se tratan como una sola unidad lógica

• Declaración:TYPE <nombre> IS RECORD (

campo <tipo_datos> [NULL | NOT NULL] [,<tipo_datos>...]

);

• Ejemplo:TYPE PAIS IS RECORD (

CO_PAIS NUMBER ,DESCRIPCION VARCHAR2(50),CONTINENTE VARCHAR2(20)

);

30/11/2010

20

Tablas (equivalente a matrices)

• Permiten almacenar varios datos del mismo tipo

• Se declaran como un tipo:

TYPE <nombre_tipo_tabla> IS TABLE OF<tipo_datos> [NOT NULL]

INDEX BY BINARY_INTEGER ;

• Ejemplo:

TYPE PAISES IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;

Tabla_PAISES PAISES;

Más sobre tablas de registros

• Se pueden declarar tablas de tipo registro

• Se dispone en todo caso de métodos y propiedades para tratar las tablas:

– LAST, FIRST

– EXIST(i), COUNT, PRIOR(n),NEXT(n), TRIM, TRIM(n),DELETE, DELETE(n),DELETE(n,m)

• Ejemplo:IF courses.EXISTS(i) THEN courses(i) := new_course; END IF;

30/11/2010

21

VARRAYS

• Equivalentes a las tablas pero los índices comienzan en 1

• Declaración:TYPE <nombre_tipo> IS VARRAY (<tamaño_maximo>) OF

<tipo_elementos>;

• Ejemplo:TYPE t_cadena IS VARRAY(5) OF VARCHAR2(50);

v_lista t_cadena := t_cadena('Aitor', 'Alicia', 'Pedro','','');

SQL dinámico

• PL/SQL ofrece la posibilidad de ejecutar sentencias SQL a partir de cadenas de caracteres mediante EXECUTE IMMEDIATE

Ejemplo:DECLARE

ret NUMBER;FUNCTION fn_execute (nn VARCHAR2, cod NUMBER) RETURN NUMBER IS

sql_str VARCHAR2(1000);BEGIN

sql_str := 'UPDATE DATOS SET NOMBRE = :nn WHERE CODIGO = :cod;EXECUTE IMMEDIATE sql_str USING nn, cod;RETURN SQL%ROWCOUNT;

END fn_execute ;BEGIN

ret := fn_execute('Devjoker',1);END;

30/11/2010

22

Funciones integradas en PL/SQLSYSDATE

DECODE if-elseif-else DECODE (co_pais, 'ESP', 'ESPAÑA', 'MEX', 'MEXICO', 'PAIS '|| co_pais)

TO_DATE TO_DATE('01/12/2006‘,'DD/MM/YYYY')

TO_CHAR TO_CHAR(SYSDATE, 'DD/MM/YYYYY')

TO_NUMBERTRUNCLENGTH LENGTH('HOLA MUNDO')INSTR INSTR('AQUI ES DONDE SE BUSCA', 'BUSCA', 1, 1 )REPLACE REPLACE ('HOLA MUNDO','HOLA', 'VAYA')SUBSTR SUBSTR('HOLA MUNDO', 6, 5)UPPERLOWERROWIDETOCHAR ROWIDTOCHAR(ROWID)RPADLPAD LPAD('Hola Mundo', 50, '.')RTRIM RTRIM (' Hola Mundo ')LTRIM LTRIM (' Hola Mundo')TRIM TRIM (' Hola Mundo ')MOD MOD(20,15)