Desarrollo y explotación de aplicaciones empresariales con...
Transcript of Desarrollo y explotación de aplicaciones empresariales con...
1
Desarrollo y explotación de aplicaciones empresariales con
base de datos Oracle:
1835.090Julio de 2004
2
Presentación del curso
3
© Luis Vinuesa Martínez - 2004
Presentación
DirectorLuis Vinuesa Martínez
ProfesoresAlberto Manuel Fernández ÁlvarezÁngel Lombas Fernández Luis Vinuesa Martínez
4
© Luis Vinuesa Martínez - 2004
Presentación
Asistencia obligatoria (80%)Evaluación
Trabajo individual (propuesto por los profesores o por el alumno, con aprobación por parte de los profesores)
Mail del [email protected]
5
© Luis Vinuesa Martínez - 2004
Presentación
CalendarioEntrega de prácticas
PlazosElección de prácticaCalificaciones
Software
6
© Luis Vinuesa Martínez - 2004
Presentación
Directorio de trabajoCread un directorio en el ordenadorSOLO TRABAJAR EN ESTE DIRECTORIO.
Página del cursowww.di.uniovi.es/~vinuesa/cursos/curso_oracle/curso90.htm
7
Introducción
8
© Luis Vinuesa Martínez - 2004
Introducción
Bases de datos relacionalesConceptosEvoluciónUtilidadSituación actual
SQL
9
Arquitectura Oracle
10
© Luis Vinuesa Martínez - 2004
Arquitectura
11
© Luis Vinuesa Martínez - 2004
Arquitectura
InstanciasSchemaUser/PasswordSQLNET o NET8 SQLNET LISTENER
12
© Luis Vinuesa Martínez - 2004
Arquitectura - memoriaSGA (Shared Global Area)
Database Buffer/caché de buffer de base de datosRedo-log-buffer /caché de rehacerShared Pool / área de agrupamiento compartido
Dictionary cache /caché de diccionarioLibrary cache (caché de SQL)
PGA (Program/Process Global Area)
13
© Luis Vinuesa Martínez - 2004
Arquitectura - procesos
DBWR (database writer)LGWR (log writer)SMON (system monitor)PMON (process monitor)ARCH (archiver)CKPT (checkpoint process)
14
© Luis Vinuesa Martínez - 2004
Arquitectura – organización lógica
Base de datosTablespace (Espacio de tablas)SegmentosExtensiones (Extent)
15
© Luis Vinuesa Martínez - 2004
Arquitectura – Ficheros
Datafiles/archivos de base de datosBloques[Rowid]Control Files/archivos de controlRedo-log Files/ficheros de rehacerArchive LogPFILE y SPFILE
16
© Luis Vinuesa Martínez - 2004
Arquitectura - transacciones
CommitRollbackDML explícitoDDL implícito
17
© Luis Vinuesa Martínez - 2004
Arquitectura – seguridad
UsuariosPrivilegios
De sistemaDe objetos
RolesPerfiles
18
© Luis Vinuesa Martínez - 2004
Arquitectura - objetosTablasVistasSinónimosÍndicesClustersDatabase linksSecuencias (sequence)Pseudocolumnas
19
Administración
20
© Luis Vinuesa Martínez - 2004
Administración
Servicios de OracleExplicación de la arquitectura OEM
21
© Luis Vinuesa Martínez - 2004
ArquitecturaPráctica: Instalación del cliente
Instalar: SQL+, oci, jdbc, odbcConfiguración del sqlnet para la conexión a la base de datos (ficheros sqlnet.ora y tnsnames.ora)Conexión con el SQL+ Variable de entorno tns_admin (para el sqlnet)Edición del registro de Windows
22
© Luis Vinuesa Martínez - 2004
AdministraciónPráctica: OEM, creación de usuario
Crear usuario de la BBDD con el role DBA (curso90/curso90)Crear usuario de oem con permiso de superadministrador (oem90/oem90)Entrar al oem con el usuario recién creadoCrear un nuevo usuario (individual para cada uno, permisos dba)Conectarse con ese nuevo usuario
23
© Luis Vinuesa Martínez - 2004
Administracion
Práctica: OEM, usohttp://156.35.150.125 (normalmente 3339)Descubrir nodosDBA studio:
Creación de tablespacesCreación de datafilesCreación de usuarios
24
© Luis Vinuesa Martínez - 2004
Administración
Práctica: configuración de una instanciaModificación del init%.ora (instancemanager)
NLS_DATE_FORMAT = “DD/MM/YYYY”NLS_LANGUAGE = SPANISHNLS_TERRITORY = SPAINUTL_FILE_DIR = *REMOTE_DEPENDENCIES_MODE = SIGNATURE/TIMESTAMP
25
© Luis Vinuesa Martínez - 2004
AdministraciónPráctica: Creación de nueva instancia
Crear una nueva instancia Contraseñas estándar Comprobar el listener.oraVer la estructura de directorios creada: oracle\admin y oracle\oradata y \databaseConfigurar los clientes (sqlnet) para que se conecten a ellaConfigurar el oem para que se conecte a ella (primero rearrancar el Agent)
26
© Luis Vinuesa Martínez - 2004
Administración
PrácticaExportImportSQL Loader
27
SQL
Explotación de la Información
28
© Luis Vinuesa Martínez - 2004
SQLManejo del SQL+
Connect usuario[/contraseña][@string]Todas las sentencias acaban en “;”EdEjecución de la ultima sentenciaManejo del ratónDescVariables y parámetros
29
© Luis Vinuesa Martínez - 2004
SQLCommit/RollbackTabla dualVista tabsSysdateValores nulos (null y NVL)Uso de comodines (% y _ )TO_CHAR / TO_DATE / TO_NUMBERDECODE
30
© Luis Vinuesa Martínez - 2004
SQLTablas de ejemplo
TpaisTprovinciaTciudadTcalleTdomicilioTpersonaTpersona_domicilio
31
© Luis Vinuesa Martínez - 2004
SQL
Uso de aliasEn las columnasEn las tablas
32
© Luis Vinuesa Martínez - 2004
SQLConsultas
Order byOperadoresFuncionesSubconsultasJoinSelfjoinOuter JoinGroup ByConnect byConjuntos
33
© Luis Vinuesa Martínez - 2004
SQL
ORDER BYLa ordenación puede ser de distintos tipos, y depende de NLS_SORTASC, DESC
34
© Luis Vinuesa Martínez - 2004
SQLOperadores
PrecedenciaAritméticos (+,-,*,/)Concatenación (||)Comparación(=,<>,>,<,IN, SOME, LIKE, BETWEEN, IS NULL.....)Lógicos (AND, OR, NOT)Conjuntos (UNION [ALL], INTERSECT, MINUS)
35
© Luis Vinuesa Martínez - 2004
SQL
FuncionesDe un valor
NuméricasDe caracterDe fechaDe conversiónOtras
Agregación
36
© Luis Vinuesa Martínez - 2004
SQL - subconsultasOPCIÓN A
SELECT deptnoFROM empWHERE empno=7369; (resultado 20)
SELECT empnoFROM empWHERE deptno=20;
OPCIÓN BSELECT empno
FROM empWHERE deptno=(SELECT deptno
FROM emp
WHERE empno=7369);
37
© Luis Vinuesa Martínez - 2004
SQL - subconsultasLa subconsulta puede aparecer en la cláusula WHERE y en la cláusula FROMPueden devolver un valor único o una lista de valores.
SELECT ename,deptnoFROM empWHERE (sal,deptno) IN (SELECT MAX(sal),deptno
FROM empGROUP BY deptno);
38
© Luis Vinuesa Martínez - 2004
SQL - Joins
JoinSelfjoinOuter join
39
© Luis Vinuesa Martínez - 2004
SQL - JoinsSELECT e1.empnoFROM emp e1WHERE e1.deptno=(SELECT deptno
FROM empWHERE empno=7369)
SELECT e1.empnoFROM emp e1, emp e2WHERE e1.deptno=e2.deptnoAND e2.empno=7369
40
© Luis Vinuesa Martínez - 2004
SQL – Group by
Agrupa filas y devuelve información sumariada de cada grupo de filas.Necesaria cuando se desea recuperar valores de funciones de grupo (MAX; MIN, SUM, etc.) junto a valores no de grupo.
41
© Luis Vinuesa Martínez - 2004
SQL – GROUP BYSELECT deptno, MIN(sal), MAX (sal)
FROM empWHERE job = 'CLERK'GROUP BY deptno;
SELECT deptno, MIN(sal), MAX (sal)FROM empWHERE job = 'CLERK'GROUP BY deptnoHAVING MIN(sal) < 1000;
42
© Luis Vinuesa Martínez - 2004
SQL – CONNECT BY
Permite realizar consultas jerárquicas (árbol genealógico)
SELECT LPAD(' ',6*(LEVEL-1)) ||ename nombreFROM empSTART WITH job='PRESIDENT'CONNECT BY
PRIOR empno = mgr;
43
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Calles de Oviedo y de Gijón que empiecen por A o por C
44
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Calles de Oviedo y de Gijón que empiecen por A o por C
SELECT des_calle,des_ciudadFROM tcalle ca, tciudad ciWHERE ca.cod_pais=ci.cod_pais
AND ca.cod_provincia=ci.cod_provinciaAND ca.cod_ciudad=ci.cod_ciudadAND ca.cod_pais=3 --España AND ca.cod_provincia=2 -- Asturias AND (UPPER(ca.des_calle) LIKE 'A%'
OR UPPER(ca.des_calle) LIKE 'C%')AND (UPPER(des_ciudad) LIKE '%OVIEDO'
OR UPPER(des_ciudad) LIKE '%GIJON')
45
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Personas con más de un domicilio
46
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Personas con más de un domicilioSELECT DISTINCT(p.dni)
FROM tpersona_domicilio pWHERE 1<(SELECT COUNT(*)
FROM tpersona_domicilio p2WHERE p.dni=p2.dni)
SELECT p2.dniFROM tpersona_domicilio p2GROUP BY p2.dniHAVING COUNT(*)>1
47
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Nombre de ciudad, código, código de provincia y % total del calles que tiene esa ciudad sobre el total de calles de ciudades que tengan en su nombre la palabra AVILES, en España (cod_pais=3)
48
© Luis Vinuesa Martínez - 2004
SQL - EjerciciosSELECT c.des_ciudad, c.cod_ciudad,
c.cod_provincia,a.num_calles/b.total_calles "%calles"FROM (SELECT ca.cod_pais,ca.cod_ciudad,ca.cod_provincia, COUNT(*)
num_callesFROM tcalle ca, tciudad ciWHERE ca.cod_pais=3 --España
AND ca.cod_pais=ci.cod_paisAND ca.cod_provincia=ci.cod_provinciaAND ca.cod_ciudad=ci.cod_ciudadAND des_ciudad LIKE '%AVILES'
GROUP BY ca.cod_pais,ca.cod_ciudad,ca.cod_provincia) a,(SELECT COUNT(*) total_calles
FROM tcalle ca,tciudad ciWHERE ca.cod_pais=3 /*España*/
AND ca.cod_pais=ci.cod_paisAND ca.cod_provincia=ci.cod_provinciaAND ca.cod_ciudad=ci.cod_ciudadAND des_ciudad LIKE '%AVILES') b,
tciudad cWHERE c.cod_pais=a.cod_paisAND c.cod_provincia=a.cod_provinciaAND c.cod_ciudad=a.cod_ciudad
49
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Obtener el nombre y código de la calle y la ciudad a la que pertenece que tiene el nombre más largo dentro de cada ciudad
50
© Luis Vinuesa Martínez - 2004
SQL - EjerciciosSELECT des_calle, cod_calle, ca.cod_pais,
ca.cod_provincia, ca.cod_ciudad
FROM tcalle ca,( SELECT cod_pais, cod_provincia, cod_ciudad, MAX(LENGTH(des_calle)) longitudFROM tcalleGROUP BY cod_pais, cod_provincia,
cod_ciudad) bWHERE b.cod_pais=ca.cod_paisAND b.cod_provincia=ca.cod_provinciaAND b.cod_ciudad=ca.cod_ciudadAND LENGTH(des_calle)= longitud
ORDER BY cod_pais, cod_provincia, cod_ciudad, des_calle;
51
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Departamento con mayor número de empleados y el número de ellos
52
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Departamento con mayor número de empleados y el número de ellos
53
© Luis Vinuesa Martínez - 2004
SQL - EjerciciosSELECT deptno, cuantos
FROM (SELECT deptno,COUNT(*) cuantosFROM empGROUP BY deptno) a
WHERE cuantos=(SELECT MAX(cuantos)FROM (SELECT deptno,
COUNT(*) cuantosFROM empGROUP BY deptno) a)
SELECT * FROM (SELECT deptno, COUNT(*) cuantos
FROM empGROUP BY deptnoORDER BY cuantos DESC) a
WHERE ROWNUM<2
54
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Calle con mas domicilios de una ciudad dada (p.ej: cod_pais=1, cod_provincia=1 y cod_ciudad=1)
55
© Luis Vinuesa Martínez - 2004
SQL - EjerciciosSELECT ca.cod_calle,des_calle,cuantos
FROM tcalle ca, (SELECT cod_pais, cod_provincia, cod_ciudad, cod_calle, COUNT(*) cuantos
FROM tdomicilioWHERE cod_pais=1
AND cod_provincia=1AND cod_ciudad=1
GROUP BY cod_pais, cod_provincia, cod_ciudad, cod_calle) o
WHERE ca.cod_pais=o.cod_paisAND ca.cod_provincia=o.cod_provinciaAND ca.cod_ciudad=o.cod_ciudadAND ca.cod_calle=o.cod_calleAND o.cuantos=(SELECT MAX(cuantos)
FROM (SELECT cod_pais, cod_provincia, cod_ciudad, cod_calle, COUNT(*) cuantos
FROM tdomicilioWHERE cod_pais=1AND cod_provincia=1AND cod_ciudad=1
GROUP BY cod_pais, cod_provincia, cod_ciudad, cod_calle) h)
56
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Obtener la media de domicilios que hay por cada ciudad.
57
© Luis Vinuesa Martínez - 2004
SQL - EjerciciosSELECT (SELECT COUNT(*) FROM tdomicilio)
/(SELECT COUNT(*)FROM (SELECT cod_pais, cod_provincia, cod_ciudad
FROM tdomicilioGROUP BY cod_pais,
cod_provincia,cod_ciudad)) FROM dual;
SELECT COUNT(*)/COUNT( DISTINCTcod_pais*10000000+cod_provincia*10000+cod_ciudad)
FROM tdomicilio;SELECT AVG(cuantos) FROM(SELECT COUNT(*) cuantosFROM tdomicilioGROUP BY cod_pais,cod_provincia,cod_ciudad) b;
58
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Obtener el total de domicilios que hay por país, por provincia, por ciudad y totales.
59
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Obtener el total de domicilios que hay por país, por provincia, por ciudad y totales.
SELECT DECODE(GROUPING(cod_pais),1,'Todos Paises',cod_pais),DECODE(GROUPING(cod_provincia),1,'Todas las
provincias',cod_provincia),DECODE(GROUPING(cod_ciudad),1,'Todas las
ciudades',cod_ciudad), COUNT(*) total_domicilios
FROM tdomicilioGROUP BY ROLLUP (cod_pais,cod_provincia,cod_ciudad)
60
© Luis Vinuesa Martínez - 2004
SQL - Ejercicios
Obtener un árbol genealógico de los descendientes de la persona con DNI=200
61
© Luis Vinuesa Martínez - 2004
SQL - EjerciciosObtener un árbol genealógico de los descendientes de la persona con DNI=200
SELECT LPAD(' ',6*(LEVEL-1)) ||nombreFROM tpersonaSTART WITH dni=200CONNECT BY
PRIOR dni = padre;
62
PL/SQL
Explotación avanzada de la información
63
© Luis Vinuesa Martínez - 2004
PL/SQL BloqueTipos de datosEstructuras de controlSentencias SQLTransaccionesCursoresExcepcionesSubprogramas: Procedimientos y FuncionesPackagesTriggersPackages estándar
64
© Luis Vinuesa Martínez - 2004
PL/SQL - Bloque
DECLAREDeclaración de Variables;
BEGINSentencias Ejecutables;
[EXCEPTIONDeclaración de Excepciones;]
END;
65
© Luis Vinuesa Martínez - 2004
PL/SQL – Tipos de DatosMás usados
NUMBER [(precisión,escala)]CHAR [(longitud_máxima)]VARCHAR2 [(longitud_máxima)]DATEBOOLEANRECORDTABLE
66
© Luis Vinuesa Martínez - 2004
PL/SQL – Tipos de Datos
Muy importantes:Variables y constantes: %TYPE y %ROWTYPESintaxis %TYPE:
<nombre variable%TYPE>;<nombre propietario.tabla.columna%TYPE>;
Sintaxis %ROWTYPE<nombre propietario.tabla.columna%ROWTYPE>;
67
© Luis Vinuesa Martínez - 2004
PL/SQL – Tipos de Datos
Tipo tabla:TYPE nombre IS TABLE OF
{variable%TYPE|tipo_columna|tabla.columna%TYPE} [NOT NULL] INDEX BY BINARY_INTEGER;
Tipo record:TYPE nombre IS RECORD
(campo1 {tipo_dato|variable%TYPE| tabla.columna%TYPE| tabla%ROWTYPE} [NOT NULL],
campo2 {tipo_dato|variable%TYPE| tabla.columna%TYPE| tabla%ROWTYPE} [NOT NULL],...................................);
68
© Luis Vinuesa Martínez - 2004
PL/SQL - DeclaracionesSección DECLARE
<nombre> [CONSTANT] <tipo de dato> [NOT NULL] [:=<expresión>];
Ejemplo (ver ej1)DECLARE
impuestos NUMBER;continua BOOLEAN;multiplicador CONSTANT NUMBER(3,2):=0.10;fecha DATE;sal_max NUMBER(11,2):=500*multiplicador;nombre CHAR(30)
BEGIN
69
© Luis Vinuesa Martínez - 2004
PL/SQL - Declaraciones
EjemploDECLARE
numero_dep dept.deptno%TYPE;nuevo_balance NUMBER(8):=10;referencia nuevo_balance%TYPE;reg_emp emp%ROWTYPE;
BEGINSELECT * INTO reg_emp
FROM empWHERE......;
IF reg_emp.deptno=20 THEN..............END;
70
© Luis Vinuesa Martínez - 2004
PL/SQL - ÁmbitoDECLARE
a CHAR;b NUMBER;
BEGIN--a CHAR y BDECLARE
a NUMBER;c DATE;
BEGIN--a NUMBER, b y c
END;DECLARE
d BOOLEAN;BEGIN
--a CHAR, b y dEND;
END;
71
© Luis Vinuesa Martínez - 2004
PL/SQL – Asignación y Operadores
Asignación :=Operadores (mayor a menor precedencia)
InclusiónOR
UniónAND
Comparación=,!=,<,>,<=,>= IS NULL, LIKE , IN, BETWEEN
Suma, resta, concatenación+,-,||
Multiplicación, división*,/
Identidad, negación+,-
Exponenciación, negación**,NOT
72
© Luis Vinuesa Martínez - 2004
PL/SQL – Estructuras de control
CondicionalIterativoSecuencial
73
© Luis Vinuesa Martínez - 2004
PL/SQL – Control condicionalIF THENIF condición THEN
conjunto de sentencias;END IF;
IF THEN ELSEIF condición THEN
conjunto de sentencias1;ELSE
conjunto de sentencias2;END IF;
IF THEN ELSIFIF condición1 THEN
conjunto de sentencias1;ELSIF condición2 THEN
conjunto de sentencias2;ELSE
conjunto de sentencias3;END IF;
74
© Luis Vinuesa Martínez - 2004
PL/SQL – Control condicionalEjemplosIF x>y THEN
alto:=x;END IF;IF cadena=‘pe’ THEN
UPDATE tabla .......;ELSE
UPDATE tabla2 ......;END IF;IF contador<20 THEN
cambia:=50;ELSIF contador>50 THEN
cambia:=30;ELSE
cambia:=20;END IF;
75
© Luis Vinuesa Martínez - 2004
PL/SQL – Control iterativo
LOOP
LOOPconjunto de sentencias;
EXIT WHEN condición de salidaconjunto de sentencias;
END LOOP;
76
© Luis Vinuesa Martínez - 2004
PL/SQL – Control iterativo
WHILE
WHILE condición de permanencia LOOPconjunto de sentencias;
END LOOP;
77
© Luis Vinuesa Martínez - 2004
PL/SQL – Control iterativo
Bucle FOR numérico
FOR variable IN [REVERSE] rango_mínimo..rango_máximo LOOP
conjunto de sentencias;END LOOP
78
© Luis Vinuesa Martínez - 2004
PL/SQL – Control iterativoDECLARE
i NUMBER(3):=1;BEGIN
END;
FOR i IN 1..9 LOOPINSERT INTO tab
VALUES(i);END LOOP;
LOOPINSERT INTO tab
VALUES(i);i:=i+1;
EXIT WHEN i>9;END LOOP;
WHILE i<10 LOOPINSERT INTO tab
VALUES(i);i:=i+1;
END LOOP;
79
© Luis Vinuesa Martínez - 2004
PL/SQL – Control secuencial
GOTO (no usar)NULL indica de forma explícita inacción.
IF ratio>90 THENcalcula(dni);
ELSENULL;
END IF;
80
© Luis Vinuesa Martínez - 2004
PL/SQL - Transacciones
COMMIT ROLLBACKSAVEPOINT punto de salvaguarda
81
© Luis Vinuesa Martínez - 2004
PL/SQL - TransaccionesEjemplo
BEGININSERT INTO tabla VALUES (‘uno’);SAVEPOINT A;INSERT INTO tabla VALUES (‘dos’);SAVEPOINT B;INSERT INTO tabla VALUES (‘tres’);SAVEPOINT C;.......IF x THENROLLBACK TO B;
ELSIF y THENROLLBACK;
ELSEROLLBACK TO A;
END IF;COMMIT;
END;
82
© Luis Vinuesa Martínez - 2004
PL/SQL - Cursores
DeclaraciónCURSOR nombre IS
sentencia SELECT [cláusula FOR UPDATE OF ] ;
Forma de uso (el más sencillo)FOR variable IN nombre_cursor LOOP
conjunto de sentenciasEND LOOP;
83
© Luis Vinuesa Martínez - 2004
PL/SQL - Cursores
Ejemplo (ver ej2)
DECLARECURSOR c1 IS
SELECT * FROM tpersona;BEGIN
FOR i IN c1 LOOPIF i.dni=‘ffff’ THEN ......;
END LOOP;END;
84
© Luis Vinuesa Martínez - 2004
PL/SQL - Excepciones
TiposPredefinidasDefinidas por el usuario
85
© Luis Vinuesa Martínez - 2004
PL/SQL - ExcepcionesControl de excepciones
......EXCEPTION
WHEN nombre_de_excepción THENconjunto de sentencias;
WHEN nombre_de_excepción THENconjunto de sentencias;
.......................WHEN OTHERS THEN
conjunto de sentencias;END;
86
© Luis Vinuesa Martínez - 2004
PL/SQL - Excepciones
Excepciones predefinidas:
SUBSCRIPT_BEYOND_COUNT
SUBSCRIPT_OUTSIDE_LIMITCOLLECTION_IS_NULL
ACCES_INTO_NULLCURSOR_ALREADY_OPEN
ROWTYPE_MISMATCHTRANSACTION_BACKED_OUT
ZERO_DIVIDEVALUE_ERROR
TOO_MANY_ROWSTIMEOUT_ON_RESOURCE
STORAGE_ERRORPROGRAM_ERROR
NOT_LOGGED_ONNO_DATA_FOUND
LOGON_DENIEDINVALID_NUMBER
INVALID_CURSORDUP_VAL_ON_INDEX
87
© Luis Vinuesa Martínez - 2004
PL/SQL - ExcepcionesDefinidas por el usuarioSintaxisNombre EXCEPTION;[PRAGMA EXCEPTION_INIT(nombre,-número);]
EjemploDECLARE
mi_error EXCEPTION;PRAGMA EXCEPTION_INIT(mi_error,-1999);......
Invocación mediante RAISERAISE mi_error;
88
© Luis Vinuesa Martínez - 2004
PL/SQL - Excepciones
Funciones usadas con excepciones:SQLCODE devuelve el código de error que ha ocurridoSQLERRM devuelve la cadena de texto correspondiente al error ocurrido
89
© Luis Vinuesa Martínez - 2004
PL/SQL - ExcepcionesEjemplo (ver ej3)
DECLAREfatal EXCEPTION;.....
BEGIN....IF campo IS NULL THEN RAISE fatal;....EXCEPCTION
WHEN fatal THEN.......
WHEN OTHERS THENcod:=SQLCODE;mensaje:=SQLERRM;INSERT INTO errores VALUES (cod,mensaje);COMMIT;
END;
90
© Luis Vinuesa Martínez - 2004
PL/SQL - Subprogramas
Tipos Procedimientos (PROCEDURE)Funciones (FUNCTION)
InvocaciónDesde otro subprogramaDesde aplicacionesDesde SQL+ (EXECUTE)
91
© Luis Vinuesa Martínez - 2004
PL/SQL - Procedimientos
Sintaxis
CREATE [OR REPLACE] PROCEDURE [esquema.]nombre[(argumento1 [{IN|OUT|IN OUT}] tipo_dato [{:=|DEFAULT} valor],
........argumenton [{IN|OUT|IN OUT}] tipo_dato [{:=|DEFAULT} valor])]
{IS|AS}cuerpo de procedimiento
92
© Luis Vinuesa Martínez - 2004
PL/SQL - ProcedimientosEstructura
CREATE OR REPLACE PROCEDURE nombre IS/*sección declarativa*/
BEGIN/*sección ejecutable*/
EXCEPTION/*sección de excepciones*/
END [nombre];
93
© Luis Vinuesa Martínez - 2004
PL/SQL - Procedimientos
Ejemplo
CREATE OR REPLACE PROCEDURE ejemplo (p_departamento NUMBER,p_emp temp.empno%TYPE,p_accion NUMBER(1) DEFAULT 1) IS
v_aux NUMBER;v_nuevo_dep tdept.deptno%TYPE;
BEGIN.......
EXCEPTIONWHEN .....
END ejemplo;
94
© Luis Vinuesa Martínez - 2004
PL/SQL - Funciones
Sintaxis
CREATE [OR REPLACE] FUNCTION [esquema.]nombre[(argumento1 [{IN|OUT|IN OUT}] tipo_dato [{:=|DEFAULT}
valor],........
argumenton [{IN|OUT|IN OUT}] tipo_dato [{:=|DEFAULT} valor])]
RETURN tipo_dato_retorno {IS|AS}cuerpo de función
95
© Luis Vinuesa Martínez - 2004
PL/SQL - Funciones
En el cuerpo de la función es necesario que se use la orden RETURN para devolver el valor de retorno de la función(También se puede usar en proceduressin ningún parámetro y sirve para provocar la salida inmediata del mismo)
96
© Luis Vinuesa Martínez - 2004
PL/SQL - FuncionesEjemplo
CREATE OR REPLACE FUNCTION calcula (p_dni tpersona%TYPE,p_sal OUT NUMBER)
RETURN NUMBER ISv_porcen NUMBER(3,2);
BEGIN.......IF v_porcen>0.25 THEN
RETURN 1;ELSE
RETURN 2;END calcula;
97
© Luis Vinuesa Martínez - 2004
PL/SQL - Subprogramas
Manejo de excepcionesRAISE (para excepciones predefinidas o definidas por el usuario)RAISE_APPLICATION_ERROR (para crear excepciones con sus propios mensajes de error)
RAISE_APPLICATION_ERROR(número,mensaje)El número debe estar entre –20000 y –20999El mensaje puede ser de hasta 512 caracteres
98
© Luis Vinuesa Martínez - 2004
PL/SQL - Subprogramas
Privilegio EXECUTEParámetro REMOTE_DEPENDENCIES_MODE
SIGNATURETIMESTAMP
99
© Luis Vinuesa Martínez - 2004
PL/SQL - Paquetes
Pueden incluir:ProcedimientosFuncionesCursoresTiposVariables (permiten variables privadas y “globales”)Excepciones
100
© Luis Vinuesa Martínez - 2004
PL/SQL - Paquetes
Constan de EspecificaciónCuerpo
101
© Luis Vinuesa Martínez - 2004
PL/SQL - Paquetes
EspecificaciónCREATE [OR REPLACE] PACKAGE nombre {IS|AS}
especificación_procedimientos |especificación_funciones |declaración_variables |definición_tipo |declaración_excepciones |declaración_cursores
END [nombre];
102
© Luis Vinuesa Martínez - 2004
PL/SQL - Paquetes
CuerpoCREATE [OR REPLACE] PACKAGE BODY nombre
{IS|AS}código_procedimientos |código_funciones |
[BEGINcódigo_inicialización_paquete]
END [nombre];
103
© Luis Vinuesa Martínez - 2004
PL/SQL - Paquetes
Permiten la sobrecarga de procedimientos y funcionesPara referirnos a ellos:
Esquema.paquete.objeto
104
© Luis Vinuesa Martínez - 2004
PL/SQL - Disparadores
Subprogramas que se ejecutan cuando ocurre un suceso de disparo (orden DML, INSERT, UPDATE o DELETE) sobre una tabla de la base de datos
105
© Luis Vinuesa Martínez - 2004
PL/SQL Disparadores
Un disparador puede ejecutarseBEFOREAFTER
De una operaciónINSERT UPDATEDELETE
106
© Luis Vinuesa Martínez - 2004
PL/SQL - Disparadores
Sintaxis
CREATE [OR REPLACE] TRIGGER esquema.nombre{BEFORE|AFTER} {[OR]DELETE| [OR] INSERT|[OR] UPDATE
[OF columna]} ON esquema.tabla[REFERENCING OLD [AS] antiguo NEW[AS] nuevo]—NO SE USA[FOR EACH ROW] [WHEN condición]Bloque PL/SQL
107
© Luis Vinuesa Martínez - 2004
PL/SQL - Disparadores
:NEW:OLDUPDATINGINSERTINGDELETING
108
© Luis Vinuesa Martínez - 2004
PL/SQL - Disparadores
Orden de ejecución
1. BEFORE2. BEFORE ROW3. AFTER ROW4. ........5. BEFORE ROW6. AFTER ROW7. AFTER
109
© Luis Vinuesa Martínez - 2004
PL/SQL – DisparadoresSi hay dos disparadores con el mismo evento orden de ejecución aleatorioNo se puede acceder a una tabla mutante (aquella que está siendo modificada, directamente o a través de ON DELETE CASCADE)No se puede modificar una tabla referenciada en las columnas implicadas en restricciones de integridad referencial que tiene que comprobar la sentencia que dispara el trigger.
110
© Luis Vinuesa Martínez - 2004
PL/SQL – Paquetes estándar
DBMS_JOBDBMS_OUTPUTUTL_FILE