Lenguaje PL/SQLTaller Base de DatosProf. Francisco Vargas B.
Lenguaje PL/SQLProcedural Language / Structured Query Language
SQL es un lenguaje de consulta para los sistemas de bases de datos relacionales (estndar).
PL/SQL amplia o potencia el lenguaje SQL con los elementos caractersticos de los lenguajes de programacin (oracle).
Lenguaje PL/SQLLenguaje de Programacin
Estructura de Programa.Estructuras de Datos.Estructuras de Asignacin.Estructuras de Decisin.Estructuras de Iteracin.
Lenguaje PL/SQLContenidos principales
Estructura del LenguajeCursoresFuncionesProcedimientos AlmacenadosTriggers
Lenguaje PL/SQLEstructura del Lenguaje
El bloque es la unidad de estructura bsica en los programas PL/SQL (rendimiento y proceso).Zona de Declaraciones. Opcional.Objetos locales (variables, constantes...). Zona de Instrucciones. Obligatoria. Zona de Excepciones. Opcional.Manejo de excepciones en el programa
Lenguaje PL/SQLEstructura del Lenguaje
Sintaxis de un BLOQUE PL/SQL.[ DECLARE | IS / AS ] BEGIN [ EXCEPTION ] END;/
Lenguaje PL/SQLZona de Declaraciones
Declaracin de Variables
nombre_variable [CONSTANT] tipo [NOT NULL] [:= valor_inicial];
Ejemplos:DECLAREv_location VARCHAR2(15) := Granada;c_comm CONSTANT NUMBER(3) := 160;v_nombre tabla_empleados.nombre%TYPE;
Lenguaje PL/SQLZona de Declaraciones
Tipo de Variables
tipo_escalar: NUMBER | DATE | CHAR | VARCHAR | BOOLEAN
identificador%TYPE: Campo de una Tabla o Escalar
identificador%ROWTYPE: Registro de una Tabla o Fila
Lenguaje PL/SQLZona de Declaraciones
Declaracin de Registros (estructurado)
Ejemplo:DECLARETYPE t_RegEstudiante IS RECORD (NombreVARCHAR2(20),ApellidoPatVARCHAR2(20),EdadNUMBER(3));
v_Estudiante t_Reg_Estudiante;
Lenguaje PL/SQLZona de Declaraciones
Declaracin de Cursores
CURSOR nombre_cursor [parmetros] IS consulta_SQL;
Ejemplo:DECLARECURSOR emp_cursor ISSELECT cod_emp, nom_empFROM empleados;
Lenguaje PL/SQLZona de Declaraciones
Declaracin de Excepciones
nombre_excepcin EXCEPTION;
Ejemplo:DECLAREdemasiados_empleados EXCEPTION;
Lenguaje PL/SQLZona de Instrucciones
Tipos de Instrucciones
Instrucciones de ASIGNACIN
Instrucciones de DECISIN (control de flujo)
Instrucciones de ITERACIN (ciclos o bucles)
Lenguaje PL/SQLZona de Instrucciones
Instrucciones de Asignacin
variable_objetivo := expresin_PL/SQL;
SELECT lista_selectINTO lista_variablesFROM ...WHERE... ;
Lenguaje PL/SQLZona de Instrucciones
Expresiones PL/SQL
Comilla simple para textos y formato decimal para numricos
Operadores nmeros: +, -, *, /, **(exponente), MOD (resto)Operadores para textos: || (concatenacin)Operadores lgicos: AND, OR, NOTOperadores de cursores:%ROWCOUNT%NOTFOUND%FOUND%ISOPEN
Lenguaje PL/SQLZona de Instrucciones
Expresiones PL/SQL
Comparadores clsicos: Comparadores SQL:[NOT] LIKEIS [NOT] NULL[NOT] BETWEEN lim1 AND lim2[NOT] IN (lista_valores)
Funciones predefinidas:Caracteres, Numricas, Fechas, Conversin, Control de Errores
Lenguaje PL/SQLZona de Instrucciones
Instrucciones de Decisin (control de flujos)
IF expresin_lgica THEN intrucciones_PL/SQL;[ELSIF expresin_lgica THEN intrucciones_PL/SQL;][ELSE intrucciones_PL/SQL;]END IF;
Lenguaje PL/SQLZona de Instrucciones
Instrucciones de Decisin (IF-ELSE)
DECLAREv_Inicio NUMBER;BEGINv_Inicio := funcion_prueba;IF v_Inicio > 100 THEN RETURN (2 * v_Inicio);ELSIF v_Inicio >= 50 THEN RETURN (.5 * v_Inicio);ELSE RETURN (.1 * v_Inicio);END IF;END;
Lenguaje PL/SQLZona de Instrucciones
Instrucciones de Iteracin (ciclos o bucles)
LOOP intrucciones_PL/SQL [EXIT WHEN exp_lgica] END LOOP;
WHILE exp_lgica LOOP intrucciones_PL/SQL END LOOP;
FOR ctrl_numerico IN [REVERSE] v_ini .. V_fin LOOP intrucciones_PL/SQL END LOOP;
FOR ctrl_cursor LOOP intrucciones_PL/SQL END LOOP;
Lenguaje PL/SQLZona de Instrucciones
Instrucciones de Iteracin (LOOP)
DECLAREv_Contador BINARY_INTEGER := 1;BEGINLOOPINSERT INTO tabla (cod_fila) VALUES (v_Contador);v_Contador := v_Contador + 1;EXIT WHEN v_Contador = 10;END LOOP;END;
Lenguaje PL/SQLZona de Instrucciones
Instrucciones de Iteracin (WHILE)
DECLAREv_Contador BINARY_INTEGER := 1;BEGINWHILE v_Contador
Lenguaje PL/SQLZona de Instrucciones
Instrucciones de Iteracin (FOR numrico)
BEGINFOR v_Contador IN 1..10 LOOPINSERT INTO tabla (cod_fila) VALUES (v_Contador);END LOOP;END;
Lenguaje PL/SQLZona de Excepciones
Generalidades
Qu es una excepcin?Se trata de una interrupcin que se produce cuando se identifica un error en tiempo de ejecucin.Cmo se activa?La puede activar ORACLE, al detectar el error.Puede ser programada, en base a lo que se defina como error.
Lenguaje PL/SQLZona de Excepciones (sintaxis)
EXCEPTION WHEN excepcion1 [OR excepcion2 . . .] THENinstruccion_1;instruccion_N; [WHEN excepcion3 [OR excepcion4 . . .] THENinstruccion_1;instruccion_N;] [WHEN OTHERS THENinstruccion_1;instruccion_N;]
Lenguaje PL/SQLZona de Excepciones (ejemplo)
EXCEPTIONWHEN NO_DATA_FOUND THENROLLBACK;TEXT_IO.PUT_LINE(TO_CHAR(v_prod_id)) || Es invalido.);WHEN TOO_MANY_ROWS THENROLLBACK;TEXT_IO.PUT_LINE (Datos Corruptos en S_PRODUCTO.);WHEN OTHERS THENROLLBACK;TEXT_IO.PUT_LINE (Otro error ocurrido.);
Lenguaje PL/SQLZona de Excepciones (propias)
DECLARE e_cant_restante EXCEPTION;. . .BEGIN. . . RAISE e_cant_restante;. . .EXCEPTION WHEN e_cant_restante THEN TEXT_IO.PUT_LINE (Hay suficiente inventario.');. . .END;
Lenguaje PL/SQLEstructura del Lenguaje
Transacciones en SQL
COMMIT: refleja en forma definitiva los cambios realizados.ROLLBACK: aborto explcito de la transaccin (todo vuelve a quedar como al principio).
Se considera una mala practica no usar commit o rollback explcitos.
Lenguaje PL/SQLEstructura del Lenguaje
BLOQUES ANIDADOS
DECLAREx BINARY_INTEGER;BEGIN...DECLAREy NUMBER;BEGIN...END;...END;Alcance XAlcance Y
Lenguaje PL/SQLEstructura del Lenguaje
CONVENCIONES DE CODIGO
INDENTACION DE NIVELES DE CODIGO
Comandos SQL Palabras claves PL/SQL Tipos de Datosidentificadores y Parmetros Tablas y Columnas BD SELECT, INSERTDECLARE, BEGIN, IFVARCHAR, BOOLEANv_sal, empl_cursor, g_sals_empl, fecha_orden, id
Lenguaje PL/SQLEstructura del Lenguaje
CONVENCIONES DE CODIGO
OJO CON PALABRAS RESERVADASVARIABLES CONSTANTESCURSORESEXCEPCIONESREGISTRO
v_contadorc_mensualidadempleado_cursore_salto_paginacliente_registro
Lenguaje PL/SQLEstructura del Lenguaje
CURSORESEs una estructura de datos que registra una consulta SELECT de SQL, que devuelve un resultado y que permite acceder a cada fila de dicha consulta. Implcitos: def. sistema (DML, PL/SQL). Explcitos: def. programador.
Lenguaje PL/SQLEstructura del Lenguaje
Ciclo del CursorDECLAREFETCHOPENCLOSECrea un rea de memoriaIdentifica y carga los registros a procesarObtiene y revisa los registrosControla si hay datos en el cursorLibera los registros y la memoria solicitadaNoSiDATA?
Lenguaje PL/SQLZona de Declaraciones
Declaracin de Cursores
CURSOR nombre_cursor [parmetros] IS consulta_SQL;
Ejemplo:DECLARECURSOR emp_cursor ISSELECT cod_emp, nom_empFROM empleados;
Lenguaje PL/SQLZona de Instrucciones
Abrir el Cursor
OPEN nombre_cursor;
Reservar memoria.Ejecutar SELECT.Apuntar primer registro.
Si no hay registros, ORACLE no retorna excepcin.
Lenguaje PL/SQLZona de Instrucciones
Recorrer el Cursor (ciclo)
FETCH nombre_cursor INTO lista_variables;
LOOPFETCH cursor INTO v_campo1, v_campoN;Instrucciones_PL/SQL;EXIT WHEN condicion_cursor;END LOOP;
Lenguaje PL/SQLZona de Instrucciones
Cerrar el Cursor
CLOSE nombre_cursor;
Liberar memoria.
Lenguaje PL/SQLEjemplo de Cursores (ciclo)
(declaracin de cursor y variables)OPEN emp_cursor;LOOPFETCH emp_cursor INTO v_cod_emp, v_nom_emp;DBMS_OUTPUT.PUT_LINE( v_cod_emp || y ' || v_nom_emp );EXIT WHEN emp_cursor%NOTFOUND;END LOOP;CLOSE emp_cursor;
Lenguaje PL/SQLZona de Instrucciones
Recorrer el Cursor (ciclo FOR de cursores)
IMPLICITO: open, fetch, variable y close.
FOR variableRegistro IN cursor LOOPInstrucciones_PL/SQL;END LOOP;
Lenguaje PL/SQLEjemplo de Cursores (for de cursores)
(declaracin de cursor)
FOR regEmp IN emp_cursor LOOPDBMS_OUTPUT.PUT_LINE(regEmp.cod_emp || y ' || regEmp.nom_emp );END LOOP;
Lenguaje PL/SQLCursores con parmetros
DECLARECURSOR cur_persona(dep NUMBER, pue CHAR(15)) IS SELECT nombre, apellidos FROM empleados WHERE departamento=dep AND puesto=pue; BEGIN OPEN cur_persona(12,administrativo); ... CLOSE cur_persona; END;
Lenguaje PL/SQLEstructura del Lenguaje
Otros programas (procedimientos almacenados, funciones y paquetes).Zona de Encabezado. Obligatoria.Identificadores y Parmetros. Zona de Declaraciones. Opcional.Objetos locales (variables, constantes...). Zona de Instrucciones. Obligatoria. Zona de Excepciones. Opcional.Manejo de excepciones en el programa
Lenguaje PL/SQLEstructura del Lenguaje
Sintaxis de un Procedimiento Almacenado.
CREATE [ OR REPLACE ] PROCEDURE nombre[ ( parmetro1 [modo] tipo_dato ) [, ] ]IS | ASBEGIN[ EXCEPTION ]END;
Lenguaje PL/SQLEstructura del Lenguaje
Sintaxis de una Funcin.
CREATE [ OR REPLACE ] FUNCTION nombre[ ( parmetro1 [modo] tipo_dato ) [, ] ]RETURN tipo_de_datosIS | ASBEGIN[ EXCEPTION ]END;
Lenguaje PL/SQLTRIGGERS
Es un conjunto de instrucciones PL/SQL que se ejecutan en el momento que una operacin INSERT, UPDATE o DELETE de SQL es aplicada a una tabla.
Permite implementar reglas de negocio.
Lenguaje PL/SQLTRIGGERS (casos tpicos)
Cambiar el valor de una columna basado en el valor de otras columnas de la misma u otra tabla.Validacin de valores de las columnas (posiblemente comparando con valores de otras tablas)Documentar los cambios que se realizan escribiendo sobre otra tabla (logfile).
Lenguaje PL/SQLTRIGGERS (sintaxis)
CREATE [OR REPLACE] TRIGGER trigger-name{BEFORE | AFTER} triggering-eventON table-name[FOR EACH ROW][WHEN (condition)]PL/SQL-block
Lenguaje PL/SQLTRIGGERS (componentes)
trigger-name nombre del trigger a ser creadotriggering-event: puede ser INSERT, UPDATE o DELETEtable-name: nombre de la tabla con la cual el trigger est asociadofor each row: causa el disparo del trigger por cada fila (opcional)condition: cuando es TRUE, habilita el trigger para ser disparado (opcional).PL/SQL-block: es el bloque de cdigo que se ejecuta cuando se dispara el trigger.
Lenguaje PL/SQLTRIGGERS (sintaxis)
Activar o desactivar triggers.ALTER TRIGGER nombre [DISABLE|ENABLE]
Eliminar triggersDROP TRIGGER nombre