Embed Size (px)
Citation preview
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)
