cbd003B
-
Upload
omar-guevara-gonzalez -
Category
Documents
-
view
215 -
download
0
description
Transcript of cbd003B
-
1PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Introduccin aPL/SQL
Humberto Rueda Rivero
PL/SQL
El lenguaje estndar para ejecucin de procesos en la base de datos.
Integrado con las dems herramientas. Rendimiento mejorado Trfico en la red reducido Modular Portable Rutinas, Funciones, Packages
PL/SQL
Manejo de Errores Mantenimiento sencillo Integridad de datos - Transacciones Claridad en el cdigo Procedimientos almacenados en la
base de datos Lgica del Negocio
Cap.1
Declarando Variables
Bloque PL/SQL
DECLARE (opcional)...BEGIN (obligatoria)...EXCEPTION (opcional)...END; (obligatoria)
Tipos de Bloques (Construcciones de Programa) Bloques Annimos Procedures Functions Packages Triggers Objects
-
2PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Tipos de Variables
Variables PL/SQL: Scalar Composite Reference LOB (Large Objects)
Variables no PL/SQL: Bind y Host
Declaracin de Variables
Nombre (CONSTANT) tipo (not null) ( := expresion) EJ:
lnea NUMBER(8,0); direcc VARCHAR2(60) := Centro; retiro DATE := SYSDATE; meses CONSTANT NUMBER(1) := 8;
Codificacin
Estndares No usar nombres de variables, similares
a nombres de columnas Una variable en cada lnea No usar la clusula NOT NULL
(Rendimiento)
Variables Escalares
CHAR VARCHAR2 LONG LONG RAW NUMBER BINARY_INTEGER PLS_INTEGER BOOLEAN
Escalares para Fechas
DATE TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND
Atributo %TYPE
Declara la vble, segn la especificacin de una columna en la BD o segn otra variable previamente definida.
El tipo de dato se obtiene en tiempo de compilacin.
Facilita el mantenimiento del cdigo
-
3PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
%TYPE
DECLARErlinea linea.nro_linea%TYPE;tSaldo NUMBER(14,2);pSaldo tSaldo%TYPE;...
BOOLEANOS
DECLAREexiste BOOLEAN;esMayor BOOLEAN;
BEGINexiste := TRUE;esMayor := 200 < 1000;
Tipos de Datos Compuestos
RECORD: definido por el usuario TABLE: similares a los vectores NESTED TABLE: VARRAY:
Variables tipo LOB
CLOB: slo texto, un byte por carcter (in/out)
BLOB: imgenes (in/out) BFILE: Pelculas, Video (out) NCLOB: slo texto, mltiples bytes por
carcter (in/out) (Unicode)
Cap. 2
Escribiendo Sentencias Ejecutables
Identificadores
Hasta 30 caracteres Iniciar con una letra Puede contener caracteres especiales No puede contener espacios, tildes o / No usar nombres de la base de datos Obviamente no deben ser palabras
reservadas
-
4PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Comentarios
/* ... */ para mltiples lneas
-- para comentariar una sola lnea
Funciones SQL en PL/SQL
Todas las funciones de SQL puede usarse en PL/SQL, excepto: DECODE Funciones de Grupo
Manipulacin de Strings, Fechas, Nmeros y Conversiones entre tipos
Anidacin de Bloques
Un pgma PL/SQL es de hecho un conjunto de bloques anidados.
Un bloque anidado es tratado como una instruccin.
El alcance de las variables definidas en un bloque anidado, finaliza cuando el mismo termina su ejecucin.
Alcance de Variables...X NUMBER(5);BEGIN
...DECLARE
y NUMBER(5);BEGIN
Y := X;END;...
END;
Operadores
Iguales a SQL
+ - * / || IS NULL , etc, etc
** Exponente
Cap 3.
Interactuando con la BD
-
5PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Sentencias SQL
Extraccin de datos con SELECT Manipulacin de datos con sentencias
DML Control de Transaccin con COMMIT,
ROLLBACK y SAVEPOINT
SELECT SELECT columnas | expresiones
INTO variablesFROM tablasWHERE ....
La cantidad de columnas seleccionadas, debe coincidir en tipo y cantidad con las especificadas en la clusula INTO
SELECT
Debe retornar una y slo una fila, de lo contrario se generan excepciones que el programa debe preveer.
Siempre pensar si la consulta recupera 0 Filas 1 Fila N Filas
SELECTSET SERVEROUTPUT ONDECLARE
sSaldo NUMBER(14,2);plinea saldo.linea%TYPE = 6799833;
BEGINSELECT SUM(facturado)
INTO sSaldoFROM saldoWHERE linea = plinea;
DBMS_OUTPUT.PUT_LINE(Suma= || sSaldo);END;/
INSERT
DECLAREfecha DATE := SYSDATE;
BEGININSERT INTO log_sesion
VALUES (USER, fecha);END;
UPDATE
DECLAREhoras NUMBER(6,2) := 1/24;
BEGINUPDATE log_sesion
SET fecha = fecha + horasWHERE usuario = HRR;
END;
-
6PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
DELETE
DECLAREvusuario log_sesion.usuario%TYPE := HRR;
BEGINDELETE FROM log_sesion
WHERE usuario = vusuario;END;
Nomenclatura Nombres
Usar convenciones para evitarambiguedades
Nombres de Columnas deben ser distintos a los nombres de las variables
Pueden presentarse errores de sintaxis pues PL/SQL examina primero la base de datos.
El Cursor SQL
Un cursor es un rea de trabajo privada para SQL
Tipos: Implcito / Explcito Implcitos: Usados por la BD Explcitos: Declarados por el
programador.
Atributos del Cursor SQL
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN
Ejemplo
SET SERVEROUTPUT ONBEGIN
DELETE FROM empleadoWHERE departamento = 20;
DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT|| registros borrados);
END;
Control de Transacciones
Una transaccin es una unidad lgica de cdigo que debe ejecutarse en su totalidad.
COMMIT ROLLBACK Una transaccin finaliza y comienza la
siguiente, cuando se ejecuta uno de estos comandos
-
7PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Cap. 4
Estructuras de Control
IFIF condiciones THEN
...ELSIF condiciones THEN
...ELSIF condiciones THEN
...ELSE
...END IF
CASE
CASE selectorWHEN expr1 THEN ret1WHEN expr2 THEN ret2...WHEN exprN THEN retNELSE retN+1
END;
Ejemplo CASE
DECLAREnEstado NUMBER(1);vEstado VARCHAR2(20);
BEGINvEstado := CASE nEstado
WHEN 1 THEN BuenoWHEN 2 THEN RegularWHEN 3 THEN MaloELSE Desconocido
END;
Consideraciones con NULL
Comparaciones con NULL dan NULL
Ej:x := 5;y := NULL;IF x != y THEN
(No se ejecuta!)END IF;
LOOPS
LOOPInstrucciones...(EXIT WHEN condiciones)
END LOOP;
Se usa cuando las instrucciones deben ejecutarse al menos 1 vez
-
8PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
WHILE
WHILE condiciones LOOPinstrucciones...
END LOOP;
Usarlo cuando debe evaluarse primero la condicin, antes de ejecutar las instrucciones.
FOR
FOR contador IN (REVERSE) desde..hasta LOOP
Instruccin...
END LOOP;
No es necesario declarar el contador pues se asume por defecto una variable INTEGER; Su mbito es la instruccin FOR
Cap. 5
Tipos de Datos Compuestos
Tipos de Datos Compuestos
PL/SQL Records PL/SQL Collections
INDEX BY Table Nested Table VARRAY
Poseen campos o componentes internos y son reutilizables.
PL/SQL Records
Debe contener un o mas campos escalares, registros, o INDEX BY tables
Similares en estructura a los registros de los lenguajes de 3GL
Util para obtener mltiples columnas de una tabla.
Permite tratar un conjunto de campos, como una sla unidad.
Sintaxis - Ejemplos
DECLARE...TYPE tEmpleado IS RECORD
( cedula NUMBER(10,0),nombre VARCHAR2(60),ingreso DATE
);
rEmpleado tEmpleado;
-
9PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Utilizacin
BEGINSELECT cedula, nombre, hire_date
INTO rEmpleadoFROM empleadoWHERE apellido = King;
DBMS_OUTPUT.PUT_LINE(rEmpleado.cedula||rEmpleado.nombre || rEmpleado.ingreso);
END;
Atributo %ROWTYPE Asigna a una variable, un tipo de variable
Record ya definido o la estructura completa de una tabla de la BD.
Ej:DECLARE...
Emp rEmpleado%ROWTYPE;Dep departments%ROWTYPE;
Ventajas de Usar %ROWTYPE
No se necesita conocer los campos que conforman la tabla en la BD
Estos campos pueden variar, tanto en nmero, como en tipos de dato en tiempo de ejecucin.
INDEX BY Tables
Similares a los vectores de otros lenguajes de programacin
Compuestos por una llave de acceso que es un BINARY_INTEGER
Mas una columna de escalares o registros.
Pueden incrementar su tamao dinmicamente.
Sintaxis
DECLAREtVector IS TABLE OF NUMBER(10)
INDEX BY BINARY_INTEGER;
vDatos tVector;
tEmp IS TABLE OF empleado%ROWTYPEINDEX BY BINARY_INTEGER;
vEmpleados tEmp;
Mtodos Utilizados
EXISTS COUNT FIRST, LAST PRIOR NEXT TRIM DELETE
-
10
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
EjemploDECLARE
TYPE tEmp IS TABLE OF employees%ROWTYPEINDEX BY BINARY_INTEGER;
hasta NUMBER(3) := 104;vEmp tEmp;
BEGINFOR i IN 100 .. Hasta LOOP
SELECT *INTO vEmp(i) FROM employeesWHERE employee_id = i;
END LOOP;FOR i IN vEmp.FIRST .. vEmp.LAST LOOP
DBMS_OUTPUT.PUT_LINE(vEmp(i).name);END LOOP;
END;
Cap. 6
Escribiendo Cursores Explcitos
Cursores
Se definen en la clusula DECLARE
OPEN FETCH (N veces) CLOSE
Declaracin
DECLARECURSOR nombre IS
SELECT cedula, nombreFROM empleado;
Abrir el Cursor
OPEN nombre;
Abre el cursor y se prepara para hacer la ejecucin del SELECT
Si la consulta no recupera registros, no se genera ninguna excepcin.
Deben verificarse los atributos del cursor, para revisar su estado.
Obteniendo datos del Cursor
FETCH nombrecursor INTO variable;
Recupera el registro actual en las variables especificadas.
Concordancia en nmero y tipos de datos.
Chequear si el cursor arroj datos
-
11
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Cerrar el Cursor
CLOSE nombrecursor;
Debe cerrarse luego de finalizado el procesamiento.
Puede reabrirse si se requiere. Un comando FETCH posterior, genera
error!
Atributos del CURSOR
%ISOPEN %NOTFOUND %FOUND %ROWCOUNT
Cursores y RegistrosDECLARE
CURSOR cEmpleados ISSELECT cedula, nombre, dpto
FROM empleados;rEmp cEmpleados%ROWTYPE;
BEGINOPEN cEmpleados;LOOP
FETCH cEmpleados INTO rEmp;...
Otra forma de Recorrer
FOR j IN nombreCursor LOOP...
END LOOP;
No requiere comandos OPEN, FETCH, CLOSE.
EjemploDECLARE
CURSOR cEmpleados ISSELECT cedula, nombre, dpto
FROM empleados;BEGIN
FOR j IN cEmpleados LOOP-- puedo usar j.cedula, j.nombre, j.dpto...
END LOOP;END;
La forma mas Simple
BEGINFOR j IN (SELECT cedula, nombre, dpto
FROM empleados) LOOP-- puedo usar j.cedula, j.nombre, j.dpto...
END LOOP;END;
Se est utilizando un cursor implcito que no es necesario declarar
-
12
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Cap. 7
Conceptos Avanzados de Cursores
Cursor con Parmetros
Se especifican variables de entrada como parte de la declaracin del cursor.
Sirven para complementar y depurar registros provenientes del SELECT
Todos los valores se especifican al abrir el cursor.
EjemploDECLARE
CURSOR cEmpleados(x NUMBER) ISSELECT cedula, nombre, salario
FROM empleadosWHERE dpto = x;
rEmp cEmpleados%TYPE;BEGIN
OPEN cEmpleados(20);LOOP
FETCH cEmpleados INTO rEmp;...
Ejemplo 2DECLARE
CURSOR cEmpleados(x NUMBER) ISSELECT cedula, nombre, salario
FROM empleadosWHERE dpto = x;
BEGINFOR i IN cEmpleados(20) LOOP
...END LOOP;
END;
Ejemplo 3DECLARE
x NUMBER := 20;BEGIN
FOR i IN (SELECT cedula, nombre, salarioFROM empleadosWHERE dpto = x) LOOP
...END LOOP;
END;
Cursor FOR UPDATE
La definicin del cursor es igual, pero el SELECT tiene al final la clusula
FOR UPDATE (OF column) (NOWAIT)
Util cuando los registros suministrados por el cursor deben ser actualizados.
-
13
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Cursor FOR UPDATEDECLARECURSOR cEmp IS
SELECT cedula, nombre, salarioFROM empleadosFOR UPDATE OF salario;
BEGINFOR i IN cEmp LOOP
IF i.salario < 5000 THENUPDATE empleados
SET salario = 5000WHERE CURRENT OF cEmp;
END IF;...
Cap. 8
Manejando las Excepciones
Excepciones en PL/SQL
Es un identificador de PL/SQL que se alcanza durante la ejecucin del programa.
Cmo se alcanza? Por un error ORACLE Porque el programador la invoca
Cmo se puede manejar? Capturarla con un Handler o manejador Propagarla al entorno de llamado de la rutina o
Calling Environment
ExceptionDECLARE...BEGIN
...EXCEPTION
...END;
Todo error generado en el cuerpo del programa hace saltar la ejecucin al bloque EXCEPTION.
Si esta excepcin no est controlada, el control se pasa al bloque que hizo el llamado.
Capturar las excepciones
EXCEPTIONWHEN excep1 THEN
...WHEN excep2 THEN
...WHEN others THEN
...
Excepciones mas Comunes
NO_DATA_FOUND TOO_MANY_ROWS INVALID_CURSOR ZERO_DIVIDE DUP_VAL_ON_INDEX
-
14
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Otras Excepciones (de la BD)DECLARE
falla EXCEPTION;PRAGMA EXCEPTION_INIT (falla, -2292);
BEGINDELETE FROM departments
WHERE dept_id = 20;COMMIT;EXCEPTION
WHEN falla THENDBMS_OUTPUT (Violacin de FK);
END;
Otras Excepciones (del Pgmador)DECLARE
falla EXCEPTION;BEGIN
DELETE FROM departmentsWHERE dept_id = 20;
IF SQL%NOTFOUND THENRAISE falla;
END IF;EXCEPTION
WHEN falla THENDBMS_OUTPUT (Cero Registros);
END;
Qu hacer en estas Rutinas?
SQLCODE SQLERRM Bitcoras Reversar procesos Generar mensajes por Pantalla o
alarmas.
RAISE_APPLICATION_ERROR
Raise_application_error (numero, txt);
Finaliza o aborta la ejecucin del programa, generando el mensaje de error especificado como parametro.
Ejemplo
BEGINDELETE FROM departments
WHERE dept_id = 20;IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR (-20111,Imposible Continuar);
END IF;...
END;
Cap. 9
Creando Procedures
-
15
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Definicin
CREATE OR REPLACE PROCEDURE nombre (parms) IS
-- Declaracion de VariablesBEGIN-- cuerpo del programa
END;
Una vez creado, el procedure puede invocarse desde cualquier herramienta.
Parmetros de Entrada
Especificar el tipo de dato sin usar la precisin.
Colocar el modo de utilizacin: IN / OUT Puede usarse la clusula DEFAULT
para colocar valores iniciales a los mismos.
Ejemplos de LlamadoCREATE OR REPLACE PROCEDURE
sumaSueldos ( dpto IN NUMBER,sueldo IN NUMBER,suma OUT NUMBER) IS
BEGIN...END;
DECLARExyz NUMBER;
BEGINsumaSueldos (20, 0, xyz);sumaSueldos (sueldo=>0, dpto=>20, xyz);sumaSueldos(20,0); -- ERROR!!sumaSueldos(20,0,3); -- ERROR!!
END;
Ejemplos de LlamadoCREATE OR REPLACE PROCEDURE
sumaSueldos ( dpto IN NUMBER,sueldo IN NUMBER DEFAULT 100,suma OUT NUMBER) IS
BEGIN...END;
DECLARExyz NUMBER;
BEGINsumaSueldos (dpto=>20, suma =>xyz);
END;
Control de Flujo en Excepcion
PROCEDURE p1ISBEGIN..P2(args);..EXCEPTION...END;
PROCEDURE p2ISBEGIN.... (excepcion !!!!!)..EXCEPTION...END;
ExcepcionEncontrada
1
2
3
Control de Flujo en Excepcion
PROCEDURE p1ISBEGIN..P2(args);..EXCEPTION...END;
PROCEDURE p2ISBEGIN.... (excepcion !!!!!)..EXCEPTION...END;
ExcepcionNO Encontrada
1
2
3
El control de la Excepcion se propaga hasta encontrar unhandler apropiado. Si NO lo encuentra, el programa finaliza con error
-
16
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Alterar o Borrar un Procedure
CREATE OR REPLACE Editor de Texto y SQL*Plus SQL Navigator, TOAD Procedure Builder
DROP PROCEDURE nombre;
Cap. 10
Creacin de Funciones
Overview
Es un bloque PL/SQL que retorna un valor
Puede ser almacenada en la BD para posterior y repetida ejecucin.
Puede usarse el llamado dentro de unaexpresion
Puede usarse en clausulas SELECT
Creacin
CREATE OR REPLACE FUNCTIONnombre (argumentos)RETURN tipo_de_datos IS
...BEGIN
...RETURN xxx;
END;
Ejemplo
Dada una cdula, traer el SalarioCREATE OR REPLACE miSalario(id NUMBER)
RETURN NUMBER ISx empleado.sueldo%TYPE;BEGIN
SELECT sueldoINTO xFROM empleadoWHERE cedula = id;
RETURN x;END;
Ejemplo
Utilizacin de la funcin
BEGIN...X := 91263541;salX := miSueldo(x);DBMS_OUTPUT.PUT_LINE(salX);...
-
17
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Ejemplo
SELECT cedula, miSueldo(cedula)FROM empleado
La funcin miSueldo podra hacer operaciones complejas que resulta conveniente ocultar o encapsular
Ventajas de las Funciones
Extienden el SQL para hacer operaciones complejas o incluso no disponibles en SQL.
Pueden usarse en la clusula WHERE de un SELECT, para filtrar datos (Incluso se les pueden crear indices)
Ocultan detalles Biblioteca de funciones
Restricciones desde SQL
La funcin debe estar almacenada en la base de datos
Acepta solo parametros de entrada (IN) Tipos de datos sencillos como
parametros. Tipos retornados deben ser sencillos No pueden usar operaciones DML
Restricciones
Funciones llamadas en operaciones DML sobre una tabla T, no pueden contener DMLs ni SELECTs sobre T
No pueden contener sentencias que finalicen la transaccin activa.
Editar o Borrar Funciones
CREATE OR REPLACE FUNCTION Editor y SQL*Plus SQLNavigator, TOAD
DROP FUNCTION nombre;
Otros Aspectos Importantes
Privilegios de Ejecucin Dependencia entre Objetos USER_OBJECTS USER_SOURCE USER_ERRORS
Depuracin
-
18
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Cap. 11
Creacin de Packages
Packages
Agrupan cdigo PL/SQL, tipos de datos ysubprogamas relacionados
Conformados por: Specification y Body No pueden ser invocados, parametrizados o
anidados Son cargados en memoria completamente al primer
llamado Un Specification puede existir sin Body, pero un
Body no puede existir sin una Specification: Ej: variables globales para la sesin.
Se recomienda crearlos en archivos .SQL separados
SintaxisCREATE OR REPLACE PACKAGE nombre IS
-- variables publicas-- declaracion de subprogramas
END nombre;
CREATE OR REPLACE PACKAGE LiqNomina ISpCedula empleado.cedula%TYPE;
PROCEDURE seleccionar (pdpto IN NUMBER);PROCEDURE liquidar (nit IN NUMBER);FUNCTION valorNovedad (nit NUMBER, fecha)
RETURN NUMBER;END LiqNomina;
Sintaxis del Body
CREATE OR REPLACE PACKAGE BODY LiqNomina ISPROCEDURE seleccionar (pdpto IN NUMBER) IS
BEGIN...
END;
PROCEDURE liquidar (nit IN NUMBER) ISBEGIN
...END;
END LiqNomina;
Utilizacin del Package
Liquidar(x); -- dentro del package liqNomina.liquidar(x); -- desde una
rutina ubicada fuera del package user.liqNomina.liquidar(x); -- haciendo
el llamado desde otro usuario distinto del propietario del package.
Borrado de Packages
DROP PACKAGE liqNomina;
DROP PACKAGE BODY liqNomina;
-
19
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Guas para el Desarrollo
Uso general 1o. La specification En ella, solo incluir las rutinas de uso
pblico Colocar variables en la specification si
se requiere preservarlas Compilar la specification, requiere de la
compilacin del body
Ventajas
Encapsulamiento Facilita el desarrollo Persistencia en variables globales Mejora el rendimiento Una sola copia del cdigo para todos los
usuarios Dependencia entre objetos se simplifica Overloading
Cap. 12
Mas conceptos de Packages
Overloading Un mismo package puede tener rutinas con
el mismo nombre, diferenciadas por losparametros de entrada.
CREATE OR REPLACE PACKAGE LiqNomina ISPROCEDURE liquidar (nit IN NUMBER);PROCEDURE liquidar ( nit IN NUMBER,
dpto IN NUMBER);PROCEDURE liquidar ( nit IN NUMBER ,
dpto IN NUMBER,saldo IN NUMBER);
END LiqNomina;
Utilizacin de Funciones
Aplican las mismas restricciones que al llamado de funciones desde SQL.
SELECT id,liqNomina.valorNovedad (id, hire_date) valor
FROM employees;
Forward Declarations
Consiste en declarar una rutina antes de que pueda ser llamada.
Se genera error se hace un llamado a una rutina antes de ser declarada.
No es muy recomendable ...
-
20
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Procedimientos de Inicializacin
Es un bloque PL/SQL escrito antes del END que finaliza el package body.
Se ejecuta una sola vez para la sesin activa e inmediatamente despus del primer uso o acceso al package.
Cap. 13
Packages suministrados por Oracle
DBMS_SQL
Para la creacin de sentencias SQL en tiempo de ejecucin, es decir, SQL Dinmico. OPEN_CURSOR PARSE BIND_VARIABLE EXECUTE FETCH_ROWS CLOSE_CURSOR
Reemplazado en 9i por ...
EXECUTE IMMEDIATE
EXECUTE IMMEDIATE cadena(INTO variables)USING (IN | OUT | IN OUT parametros)
INTO se utiliza solamente cuando dentro de la cadena, se tiene un SELECT que recupera una fila.
USING se usa cuando se deben especificar parmetros al comando
EXECUTE IMMEDIATE
CREATE PROCEDURE DelTabla(tabla VARCHAR2, filas OUT NUMBER) IS
BEGINEXECUTE IMMEDIATE DELETE FROM ||tabla;filas := SQL%ROWCOUNT;
END;
DBMS_DDL
Ejecucin de algunas instrucciones DDL desde PL/SQL.
ALTER_COMPILE ANALYZE_OBJECT
-
21
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
DBMS_JOB
Para programar una cola de procesos que se ejecutan en una fecha y hora predeterminadas.
SUBMIT - REMOVE CHANGE - WHAT NEXT_DATE - INTERVAL BROKEN - RUN
Enviar trabajosDBMS_JOB.SUBMIT(job, (out)
what,next_date,interval,no_parse);
Ejemplo
Ej: Desconectar morosos el 31 de jul a las 10amDECLARE
trabajo NUMBER;BEGIN
DBMS_JOB.SUBMIT (trabajo,pak_cierre.desconectar;,to_date(200207311000,yyyymmddhh24mi));
DBMS_OUTPUT.PUT_LINE (# Proceso: || trabajo);END;
/SELECT * FROM DBA_JOBS;
UTL_FILE
Para interactuar con archivos del S.Operativo.
Solo puede abrir archivos ubicados en los paths especificados en el parmetro UTL_FILE_DIR del archivo de inicializacin INIT.ORA
UTL_FILE
Subprogramas: FOPEN IS_OPEN GET_LINE PUT, PUT_LINE, PUTF NEW_LINE FFLUSH FCLOSE, FCLOSE_ALL
UTL_FILE
Puede generar las siguientes excepciones: INVALID_PATH INVALID_MODE INVALID_FILEHANDLE INVALID_OPERATION READ_ERROR WRITE_ERROR INTERNAL_ERROR
-
22
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
UTL_FILE: Ejemplo
DECLAREf UTL_FILE.FILE_TYPE;
BEGINf := UTL_FILE.FOPEN (/facturas, salarios, W);FOR i IN (SELECT nombre, salario
FROM empleado) LOOPUTL_FILE.PUT_LINE(f, nombre || , || salario);
END LOOP;UTL_FILE.FCLOSE(f);
END;
Otros Packages
UTL_HTTP UTL_TCP DBMS_ALERT DBMS_SESSION DBMS_UTILITY DBMS_LOB
Existe un manual exclusivo para explicar todos estos packages
Manipulando LOBs
LOBs son tipos de datos usados para el almacenamiento de datos no estructurados
Un LOB puede o n, almacenarse dentro de la BDatos
CLOB BLOB BFILE
Directory Path Virtual dentro de la BD que especifica el
camino o path real en que estos archivos se encuentran.
CREATE DIRECTORY nombre ASpath real;
Para crear un Directory, el usuario debe tener los privilegios: CREATE ANY DIRECTORY DROP ANY DIRECTORY
Ejemplo BFILES: Fotos Employees
1. Agregar la columna a la Tabla DestinoALTER TABLE employees ADD foto BFILE;
2. Si an no existe, crear el Directory apuntando al filesystemapropiado.
CREATE DIRECTORY fotos AS /nomina/fotos;
3. Otorgar privilegios de lectura sobre el DirectoryGRANT READ ON fotos TO PUBLIC;
DECLAREf BFILE;
BEGINFOR i IN (SELECT cedula FROM empleado) LOOP
f := BFILENAME(fotos, i.cedula||.gif);IF DBMS_LOB.FILEEXISTS(f) = 1 THEN
DBMS_LOB.FILEOPEN(f);UPDATE empleado
SET foto = fWHERE cedula = i.cedula;
DBMS_LOB.FILECLOSE(f);DBMS_OUTPUT.PUT_LINE(Ok:||i.cedula);
ELSEDBMS_OUTPUT.PUT_LINE(Bad:||i.cedula);
END IF;END LOOP;
END;
-
23
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
Cap. 14
Triggers
Definicin
Bloque PL/SQL que se ejecuta o dispara con la ocurrencia de un evento.
Application Triggers Database Triggers
Guas
Para realizar operaciones relacionadas Centralizar acciones NO para duplicar otros triggers Si el cdigo es muy largo, es mejor
crear un procedure e invocarlo desde eltrigger.
Cuidado con el cdigo excesivo!!!
Componentes
Cundo: BEFORE, AFTER, INSTEAD OF
Evento: INSERT, UPDATE, DELETE Dnde: tabla o vista afectada Tipo: row / registro Restricciones?: WHEN Cuerpo PL/SQL
Nuevos Eventos
Slo para 9i: AFTER SERVERERROR AFTER LOGON BEFORE LOGOFF AFTER STARTUP BEFORE SHUTDOWN
Ejemplo
CREATE OR REPLACE TRIGGER au_salarioAFTER UPDATE OF salary ON employeesBEGIN
IF :old.salary < :new.salary * 2 THENRAISE_APPLICATION_ERROR (-20111,Actualizacion de salario rechazada!);
END IF;END;
-
24
PPRROOYYEECCTTOO
IINNTTEELLIIGGEENNTTEE
Curso de Formacin en Base de DatosCurso de Formacin en Base de Datos
EjemploCREATE OR REPLACE TRIGGER aiud_salario
AFTER INSERT,UPDATE,DELETE ON employeesBEGIN
IF INSERTING THEN INSERT INTO bitacora ...
ELSIF UPDATING THENvalidaSalario(:new.salary);
ELSEINSERT INTO bitacora ...
END IF;END;
EjemploCREATE OR REPLACE TRIGGER aiu_salario
AFTER INSERT,UPDATE ON employeesWHEN :new.dpto = 20BEGIN
IF INSERTING THEN INSERT INTO bitacora ...
ELSE THENvalidaSalario(:new.salary);
END IF;END;
Triggers INSTEAD OF
Reemplazan o suplantan la operacin especificada por otra
Se usan para hacer operaciones DML a travs de vistas
Caractersticas de los Triggers
No pueden incluir comandos para finalizar una transaccin
Son implcitamente invocados o ejecutados Pueden habilitarse o deshabilitarse No pueden referenciar la tabla sobre la cul
han sido creados Se eliminan al borrar la tabla con DROP
TABLE Se ejecutan siempre, sin importar la
herramienta que genera el evento que los dispara.
Utilidad
Seguridad Bitcoras Reglas de Integridad avanzadas Reglas del Negocio Campos u operaciones derivadas Replicacin de datos