1

PL/SQL: IntroducciPL/SQL: Introduccióónn

BDSI BDSI -- Grupo AGrupo A

IntroducciIntroduccióónn

PL/SQL: Lenguaje imperativo para PL/SQL: Lenguaje imperativo para manejar los datos del esquemamanejar los datos del esquema

Bucles, procedimientos, funcionesBucles, procedimientos, funcionesTratamiento de excepcionesTratamiento de excepcionesCursoresCursoresTriggersTriggers

ÍÍndicendice

1.1. Bloques Bloques 2.2. Variables y constantesVariables y constantes3.3. SelectSelect …… intointo4.4. Instrucciones de controlInstrucciones de control5.5. ExcepcionesExcepciones6.6. ProcedimientosProcedimientos7.7. CursoresCursores8.8. TriggersTriggers

Primer Ejemplo: bloque anPrimer Ejemplo: bloque anóónimonimo

SET SERVEROUTPUT ON;SET SERVEROUTPUT ON;

Esto es para que los mensajes que se muestran con Esto es para que los mensajes que se muestran con dbms_output.put_linedbms_output.put_line (el (el equivalente a equivalente a writelnwriteln) se muestren por la salida est) se muestren por la salida estáándar. Por defecto estndar. Por defecto estááa OFF. Sa OFF. Sóólo es necesario hacerlo una vez.lo es necesario hacerlo una vez.

Un bloque anUn bloque anóónimo en PL/SQL:nimo en PL/SQL:

BEGINBEGINdbms_output.put_linedbms_output.put_line(('Animula vagula, blandula, …');

END;END;

El bloque se carga y ejecuta a la vez. No queda grabado y no pueEl bloque se carga y ejecuta a la vez. No queda grabado y no puede usarse de usarse de nuevode nuevo

Estructura de un bloqueEstructura de un bloque

DECLAREDECLARE/*variables, /*variables, typestypes, , andand local local subprogramssubprograms. */ . */

BEGIN BEGIN /* /* ExecutableExecutable sectionsection: : proceduralprocedural andand SQL SQL statementsstatements gogo herehere. */ . */ /* /* ThisThis isis thethe onlyonly sectionsection ofof thethe blockblock thatthat isis requiredrequired. */ . */

EXCEPTION EXCEPTION /* /* ExceptionException handlinghandling sectionsection: error : error handlinghandling statementsstatements gogo herehere. .

*/ */ END; END;

Ver los erroresVer los errores

A menudo obtendremos:A menudo obtendremos:Advertencia: Procedimiento creado con errores de

compilación.

Para ver quPara ver quéé error ha habido:error ha habido:

SHOW ERRORS

Esto hay que hacerlo cada vez que hay un errorEsto hay que hacerlo cada vez que hay un error

2

VariablesVariables

DECLARE DECLARE nombre VARCHAR(20); nombre VARCHAR(20); ---- tipo de la columna importe de ventastipo de la columna importe de ventasprecio precio ventas.importe%Typeventas.importe%Type; ; ---- tipo de la fila ventastipo de la fila ventasfilaventasfilaventas ventas%ROWTYPEventas%ROWTYPE;;---- una constanteuna constantelimite CONSTANT NUMBER := 5000; limite CONSTANT NUMBER := 5000;

Valor por defecto de toda variable: NULLValor por defecto de toda variable: NULLSe pueden asignar valores con :=Se pueden asignar valores con :=

Variables: ejemploVariables: ejemplo

DECLAREDECLARE

a NUMBER := 3;a NUMBER := 3;

BEGINBEGIN

a := a + 1;a := a + 1;

END;END;---- (es un bloque totalmente in(es un bloque totalmente inúútil)til)

SelectSelect …… intointo

DECLARE DECLARE n n trabajadores.nombre%Typetrabajadores.nombre%Type; ; a a trabajadores.apellidos%TYPEtrabajadores.apellidos%TYPE; ; BEGIN BEGIN SELECT nombre,apellidos INTO n,aFROM trabajadores;WHERE DNI=WHERE DNI=‘‘001001’’; ; INSERT INTO T1 INSERT INTO T1 VALUES(n,aVALUES(n,a); );

END; END;

Si no hay exactamente un par de valores Si no hay exactamente un par de valores nombre,apellidosnombre,apellidos se se provocarprovocaráá una excepciuna excepcióón n los select … into deben devolver una única fila

Instrucciones de control: Instrucciones de control: IfIf

IF <IF <conditioncondition> THEN <> THEN <statement_liststatement_list> ELSE <> ELSE <statement_liststatement_list> END IF; > END IF;

IF <IF <conditioncondition_1> THEN ... ELSIF <_1> THEN ... ELSIF <conditioncondition_2> THEN ... ... ... ELSIF _2> THEN ... ... ... ELSIF <<condition_ncondition_n> THEN ... ELSE ... END IF;> THEN ... ELSE ... END IF;

DECLARE a NUMBER; b NUMBER;

BEGIN SELECT e,f INTO a,b FROM T1 WHERE e>1; IF b=1 THEN

INSERT INTO T1 VALUES(b,a); ELSE

INSERT INTO T1 VALUES(b+10,a+10); END IF;

END;

Instrucciones de control: Instrucciones de control: looploop

DECLARE DECLARE i NUMBER := 1; i NUMBER := 1; BEGIN BEGIN LOOP LOOP

INSERT INTO T1 INSERT INTO T1 VALUES(i,iVALUES(i,i); ); i := i+1; i := i+1; EXIT WHEN i>100; EXIT WHEN i>100;

END LOOP; END LOOP; END; END;

Si se usa Si se usa looploop hay que poner alghay que poner algúún n exitexit (con o sin (con o sin whenwhen))

Instrucciones de control: Instrucciones de control: whilewhile, , forfor

WHILE <WHILE <conditioncondition> LOOP > LOOP <<loop_bodyloop_body> >

END LOOP; END LOOP;

FOR <FOR <varvar> IN <> IN <startstart>..<>..<finishfinish> LOOP > LOOP <<loop_bodyloop_body> >

END LOOP; END LOOP;

Ejercicio: crear una tabla con un solo atributo numEjercicio: crear una tabla con un solo atributo numéérico rico a y y con 10 filas 1,2con 10 filas 1,2…….10.10

3

Ejercicio: soluciEjercicio: solucióónn

drop table t;drop table t;create table create table t(at(a number primary key);number primary key);

DECLARE DECLARE

i number;i number;

BEGINBEGIN

For i in 1..10 loopFor i in 1..10 loopinsert into t insert into t values(ivalues(i););

end loop;end loop;end; end;

ExcepcionesExcepciones

Ejemplo:Ejemplo:

DECLAREDECLARE---- DeclaracionesDeclaraciones

BEGINBEGIN---- EjecuciEjecucióónn……………………

EXCEPTIONEXCEPTIONWHEN WHEN NO_DATA_FOUNDNO_DATA_FOUND THENTHEN

---- Se ejecuta cuando ocurre una excepciSe ejecuta cuando ocurre una excepcióón de tipo n de tipo NO_DATA_FOUNDNO_DATA_FOUNDWHEN WHEN ZERO_DIVIDEZERO_DIVIDE THENTHEN

---- Se ejecuta cuando ocurre una excepciSe ejecuta cuando ocurre una excepcióón de tipo n de tipo ZERO_DIVIDEZERO_DIVIDE

WHEN OTHERS THENWHEN OTHERS THEN---- Se ejecuta cuando ocurre una excepciSe ejecuta cuando ocurre una excepcióón de un tipo no tratadon de un tipo no tratado

---- en los bloques anterioresen los bloques anterioresENDEND; ;

Excepciones en Oracle (I)Excepciones en Oracle (I)

ACCESS_INTO_NULLACCESS_INTO_NULL El programa intentEl programa intentóó asignar valores a los atributos de un objeto no asignar valores a los atributos de un objeto no inicializado inicializado --65306530

COLLECTION_IS_NULLCOLLECTION_IS_NULL El programa intentEl programa intentóó asignar valores a una tabla anidada aasignar valores a una tabla anidada aúún no n no inicializada inicializada --6531 6531

CURSOR_ALREADY_OPENCURSOR_ALREADY_OPEN El programa intentEl programa intentóó abrir un cursor que ya se encontraba abierto. abrir un cursor que ya se encontraba abierto. Recuerde que un cursor de ciclo FOR automRecuerde que un cursor de ciclo FOR automááticamente lo abre y ello no se debe especificar con ticamente lo abre y ello no se debe especificar con la sentencia OPEN la sentencia OPEN --6511 6511

DUP_VAL_ON_INDEXDUP_VAL_ON_INDEX El programa intentEl programa intentóó almacenar valores duplicados en una columna que se almacenar valores duplicados en una columna que se mantiene con restriccimantiene con restriccióón de integridad de un n de integridad de un ííndice ndice úúnico (nico (uniqueunique indexindex) ) --1 1

INVALID_CURSORINVALID_CURSOR El programa intentEl programa intentóó efectuar una operaciefectuar una operacióón no vn no váálida sobre un cursor lida sobre un cursor --1001 1001 INVALID_NUMBERINVALID_NUMBER En una sentencia SQL, la conversiEn una sentencia SQL, la conversióón de una cadena de caracteres hacia un n de una cadena de caracteres hacia un

nnúúmero falla cuando esa cadena no representa un nmero falla cuando esa cadena no representa un núúmero vmero váálido lido --17221722LOGIN_DENIEDLOGIN_DENIED El programa intentEl programa intentóó conectarse a Oracle con un nombre de usuario o conectarse a Oracle con un nombre de usuario o passwordpassword

invinváálido lido --1017 1017 NO_DATA_FOUNDNO_DATA_FOUND Una sentencia SELECT INTO no devolviUna sentencia SELECT INTO no devolvióó valores o el programa valores o el programa referencireferencióó un un

elemento no inicializado en una tabla indexada 100 elemento no inicializado en una tabla indexada 100 NOT_LOGGED_ONNOT_LOGGED_ON El programa efectuEl programa efectuóó una llamada a Oracle sin estar conectado una llamada a Oracle sin estar conectado --1012 1012

Excepciones en Oracle (II)Excepciones en Oracle (II)

PROGRAM_ERRORPROGRAM_ERROR PL/SQL tiene un problema interno PL/SQL tiene un problema interno --6501 6501 ROWTYPE_MISMATCHROWTYPE_MISMATCH Los elementos de una asignaciLos elementos de una asignacióón (el valor a asignar y la variable que lo n (el valor a asignar y la variable que lo

contendrcontendráá) tienen tipos incompatibles. Tambi) tienen tipos incompatibles. Tambiéén se presenta este error cuando un parn se presenta este error cuando un paráámetro metro pasado a un subprograma no es del tipo esperado pasado a un subprograma no es del tipo esperado --6504 6504

SELF_IS_NULLSELF_IS_NULL El parEl paráámetro SELF (el primero que es pasado a un mmetro SELF (el primero que es pasado a un méétodo MEMBER) es nulo todo MEMBER) es nulo --30625 30625 STORAGE_ERRORSTORAGE_ERROR La memoria se terminLa memoria se terminóó o esto estáá corrupta corrupta --6500 6500 SUBSCRIPT_BEYOND_COUNTSUBSCRIPT_BEYOND_COUNT El programa estEl programa estáá tratando de referenciar un elemento de un tratando de referenciar un elemento de un

arreglo indexado que se encuentra en una posiciarreglo indexado que se encuentra en una posicióón mn máás grande que el ns grande que el núúmero real de elementos mero real de elementos de la coleccide la coleccióón n --6533 6533

SUBSCRIPT_OUTSIDE_LIMITSUBSCRIPT_OUTSIDE_LIMIT El programa estEl programa estáá referenciandoreferenciando un elemento de un arreglo un elemento de un arreglo utilizando un nutilizando un núúmero fuera del rango permitido (por ejemplo, el elemento mero fuera del rango permitido (por ejemplo, el elemento ““--11””) ) --6532 6532

SYS_INVALID_ROWIDSYS_INVALID_ROWID La conversiLa conversióón de una cadena de caracteres hacia un tipo n de una cadena de caracteres hacia un tipo rowidrowid fallfallóó porque porque la cadena no representa un nla cadena no representa un núúmero mero --1410 1410

TIMEOUT_ON_RESOURCETIMEOUT_ON_RESOURCE Se excediSe excedióó el tiempo mel tiempo mááximo de espera por un recurso en Oracle ximo de espera por un recurso en Oracle --51 51 TOO_MANY_ROWSTOO_MANY_ROWS Una sentencia SELECT INTO devuelve mUna sentencia SELECT INTO devuelve máás de una fila s de una fila --1422 1422 VALUE_ERRORVALUE_ERROR OcurriOcurrióó un error aritmun error aritméético, de conversitico, de conversióón o truncamiento. Por ejemplo, sucede n o truncamiento. Por ejemplo, sucede

cuando se intenta calzar un valor muy grande dentro de una variacuando se intenta calzar un valor muy grande dentro de una variable mble máás peques pequeñña a --6502 6502 ZERO_DIVIDEZERO_DIVIDE El programa intentEl programa intentóó efectuar una divisiefectuar una divisióón por cero n por cero --14761476

Excepciones: ejemploExcepciones: ejemploDECLARE

v_empid emp.empid%TYPE;BEGINSELECT e.empid INTO v_empidFROM emp eWHERE e.ename = 'BERTOLDO';

EXCEPTIONWHEN NO_DATA_FOUND THENRAISE OT_FAILURE;

WHEN TOO_MANY_ROWS THENRAISE OT_FAILURE;

WHEN OTHERS THENout_status_code := 'FAIL';out_msg := g_out_msg || ' ' || SUBSTR(SQLERRM, 1, 60);

END;

Excepciones del usuarioExcepciones del usuario

DECLAREDECLARE---- Declaramos una Declaramos una excepcionexcepcion identificada por identificada por VALOR_NEGATIVOVALOR_NEGATIVO

VALOR_NEGATIVOVALOR_NEGATIVO EXCEPTIONEXCEPTION;;valor valor NUMBERNUMBER;;BEGINBEGIN

---- EjecucionEjecucionvalor := valor := --1;1;IFIF valor < 0 valor < 0 THENTHENRAISERAISE VALOR_NEGATIVOVALOR_NEGATIVO;;END IF;END IF;

EXCEPTIONEXCEPTION---- ExcepcionExcepcion

WHEN WHEN VALOR_NEGATIVOVALOR_NEGATIVO THENTHENdbms_output.put_linedbms_output.put_line('El valor no puede ser negativo');('El valor no puede ser negativo');ENDEND; ;

4

Variables de cursorVariables de cursor

Se usan en lugar de las excepciones para Se usan en lugar de las excepciones para controlar el efecto de una sentencia controlar el efecto de una sentencia selectselect, , insertinsert, , updateupdate o o deletedelete

sql%rowcountsql%rowcount: n: núúmero de filas actualizadasmero de filas actualizadas

sql%notfoundsql%notfound: si no hay filas actualizadas. En el : si no hay filas actualizadas. En el caso de caso de selectselect no se puede usar, siempre salta no se puede usar, siempre salta la excepcila excepcióón. n. ÚÚtil para el resto (til para el resto (insertinsert, , deletedelete, , updateupdate).).

Variables de cursor: ejemploVariables de cursor: ejemplo

beginbeginupdateupdate ventasventas

set importe = 10000set importe = 10000wherewhere idvendedoridvendedor = 12;= 12;

ifif sql%notfoundsql%notfound thenthendbms_output.put_linedbms_output.put_line('No hay empleado 12 ...');('No hay empleado 12 ...');

endend ifif;;exceptionexceptionwhenwhen no_data_foundno_data_found thenthendbms_output.put_linedbms_output.put_line('Error no se encuentran los datos');('Error no se encuentran los datos');

endend;;

No se ejecuta la excepciNo se ejecuta la excepcióón, sino el n, sino el ifif, porque no es un , porque no es un selectselect

Variables de cursor: ejemploVariables de cursor: ejemplo

beginbeginupdateupdate ventasventas

set importe = 10000set importe = 10000wherewhere idvendedoridvendedor = 1;= 1;dbms_output.put_linedbms_output.put_line('('NNúúmm filas afectadas ' || filas afectadas ' || to_char(sql%rowcountto_char(sql%rowcount) );) );

exceptionexceptionwhenwhen no_data_foundno_data_found thenthendbms_output.put_linedbms_output.put_line('Error no se encuentran los datos');('Error no se encuentran los datos');

endend;;

Muestra: Muestra: Núm filas afectadas: 1

Variables de cursor: ejercicioVariables de cursor: ejercicio

Dada la tabla Dada la tabla empleados(empleados(nombrenombre,,…….) .) escribir cescribir cóódigo PL/SQL para borrar al digo PL/SQL para borrar al empleado Bertoldo. El cempleado Bertoldo. El cóódigo debe avisar digo debe avisar si la operacisi la operacióón se ha realizado con n se ha realizado con ééxitoxitoSoluciSolucióón:n:

ProcedimientosProcedimientos

---- esto es un comentarioesto es un comentarioCREATE OR REPLACE PROCEDURE hola IS CREATE OR REPLACE PROCEDURE hola IS ---- declaramos una variabledeclaramos una variableusuario VARCHAR2(8) := usuario VARCHAR2(8) := useruser; ; ---- Bloque principalBloque principalBEGIN BEGIN

dbms_output.put_linedbms_output.put_line(' Hola, ' || usuario || '!'); (' Hola, ' || usuario || '!'); END; END;

Llamar a un procedimientoLlamar a un procedimiento

Cuando se compile sin problemas Cuando se compile sin problemas Procedimiento creado.Para ejecutarlo:Para ejecutarlo:

EXEC hola

Hola, BERTOLDO43!

Procedimiento PL/SQL terminado correctamente..

5

ParParáámetros y metros y SelectSelect …… intointo

CREATE OR REPLACE PROCEDURE totalventas (vendedor VARCHAR2) IS num_ventas NUMBER;

BEGIN SELECT COUNT(*) INTO num_ventasFROM ventas WHERE idvendedor = vendedor;IF num_ventas > 1 THEN

dbms_output.put_line('Total: ' || num_ventas || ' ventas.'); ELSIF num_ventas = 1 THEN

dbms_output.put_line('sólo una venta'); ELSE dbms_output.put_line('Sin ventas.'); END IF; END;

Llamada con parLlamada con paráámetrosmetros

exec totalventas(2);

Total: 4 ventas.

Procedimiento PL/SQL terminado correctamente.

CursoresCursores

ÚÚtiles para trabajar tiles para trabajar ““fila a filafila a fila”” con el resultado de un con el resultado de un selectselect

Forma de proceder:Forma de proceder:Declarar el cursor como si se tratara de una variableDeclarar el cursor como si se tratara de una variableAbrir el cursor con la instrucciAbrir el cursor con la instruccióón n OPENOPENLeer cada fila con Leer cada fila con FETCHFETCHCerrar el cursor y liberar los recursos con la instrucciCerrar el cursor y liberar los recursos con la instruccióón n CLOSECLOSE

¡¡No deben usarse si se pueden sustituir fNo deben usarse si se pueden sustituir fáácilmente por cilmente por un un selectselect!!

Declarando CursoresDeclarando Cursores

En un bloque anEn un bloque anóónimo: nimo: declaredeclare

cursorcursor c_poemasc_poemas isisSELECTSELECT autor, poema autor, poema FROMFROM poemas;poemas;beginbegin/* Sentencias del bloque ...*//* Sentencias del bloque ...*/endend; ;

En un procedimiento: tras la cabeceraEn un procedimiento: tras la cabecera

Tratando cursores (1)Tratando cursores (1)

SET SERVEROUTPUT ONSET SERVEROUTPUT ON

DECLAREDECLARECURSORCURSOR cpaisescpaisesISISSELECT SELECT CO_PAISCO_PAIS, DESCRIPCION, CONTINENTE , DESCRIPCION, CONTINENTE FROMFROM PAISES;PAISES;

co_paisco_pais VARCHARVARCHAR(3);(3);descripciondescripcion VARCHARVARCHAR(50);(50);continente continente VARCHARVARCHAR(25);(25);

BEGINBEGINOPENOPEN cpaisescpaises;;FETCHFETCH cpaisescpaises INTOINTO co_pais,descripcion,continenteco_pais,descripcion,continente;;WHILEWHILE cpaises%cpaises%foundfoundLOOPLOOPdbms_output.put_line(descripciondbms_output.put_line(descripcion););FETCHFETCH cpaisescpaises INTOINTO co_pais,descripcion,continenteco_pais,descripcion,continente;;ENDEND LOOPLOOP; ; CLOSECLOSE cpaisescpaises;;

ENDEND;;

Tratando cursores (2)Tratando cursores (2)

DECLAREDECLARECURSOR c_direcciones IS

SELECT nombre, dirección FROM direcciones;

el_nombreel_nombre direcciones.nombre%TYPEdirecciones.nombre%TYPE;;la_callela_calle direcciones.calle%TYPEdirecciones.calle%TYPE;;contador NUMBER(1);contador NUMBER(1);

BEGINBEGINOPEN OPEN c_direccionesc_direcciones;;LOOPLOOPFETCH FETCH c_direccionesc_direcciones INTO INTO el_nombreel_nombre, , la_callela_calle;;EXIT WHEN EXIT WHEN c_direcciones%NOTFOUNDc_direcciones%NOTFOUND;;DBMS_OUTPUT.PUT_LINE(DBMS_OUTPUT.PUT_LINE(‘‘NombreNombre: ' || nombre || ', Calle: ' || calle);: ' || nombre || ', Calle: ' || calle);END LOOP;END LOOP;CLOSE CLOSE c_direccionesc_direcciones;;

ENDEND; ;

6

Tratando cursores (3)Tratando cursores (3)

SET SERVEROUTPUT ONSET SERVEROUTPUT ONDECLAREDECLAREv_empnov_empno employees.employee_id%TYPEemployees.employee_id%TYPE;;v_enamev_ename employees.last_name%TYPEemployees.last_name%TYPE;;v_count_rowv_count_row NUMBER(3);NUMBER(3);CURSOR emp_cursor ISSELECT employee_id, last_nameFROM employees; BEGINBEGINSELECT SELECT COUNT(rownumCOUNT(rownum) INTO ) INTO v_count_rowv_count_row FROM FROM employeesemployees;;OPEN OPEN emp_cursoremp_cursor;;FOR i IN 1..FOR i IN 1..v_count_rowv_count_row LOOPLOOP

FETCH FETCH emp_cursoremp_cursor INTO INTO v_empnov_empno, , v_enamev_ename;;DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_empnoDBMS_OUTPUT.PUT_LINE(TO_CHAR(v_empno)||)||’’ ‘‘||v_ename||v_ename););

END LOOP;END LOOP;ENDEND;;

Variables tipo cursorVariables tipo cursor

Atributo Antes de abrir

Al abrir

Durante la recuperación

Al finalizar la

recuperación

Después de cerrar

%NOTFOUND ORA-1001 NULL FALSE TRUE ORA-1001 %FOUND ORA-1001 NULL TRUE FALSE ORA-1001 %ISOPEN FALSE TRUE TRUE TRUE FALSE

%ROWCOUNT ORA-1001 0

Número de registros que ha recuperado hasta el momento

Número de total de registros

ORA-1001

Cursores Cursores paramparaméétricostricos

DECLARE DECLARE CURSORCURSOR cpaisescpaises ((p_continentep_continente VARCHARVARCHAR) )

ISISSELECT SELECT CO_PAISCO_PAIS, DESCRIPCION, CONTINENTE , DESCRIPCION, CONTINENTE FROMFROM PAISESPAISES

WHERE WHERE CONTINENTE = CONTINENTE = p_continentep_continente;;

registro registro cpaises%cpaises%ROWTYPEROWTYPE;;

BEGINBEGINOPENOPEN cpaisescpaises('EUROPA');('EUROPA');FETCHFETCH cpaisescpaises INTOINTO registro;registro;CLOSECLOSE cpaisescpaises;;

ENDEND; ;

TriggersTriggers

Un Un trigger es un procedimiento PL/SQL asociado con una es un procedimiento PL/SQL asociado con una tabla de la base de datos de Oracle y que se "dispara" tabla de la base de datos de Oracle y que se "dispara" cada vez que se ejecuta sobre dicha tabla una sentencia cada vez que se ejecuta sobre dicha tabla una sentencia INSERTINSERT, una sentencia , una sentencia UPDATEUPDATE o una sentencia o una sentencia DELETEDELETE

Algunas aplicaciones:Algunas aplicaciones:Para Para grabar grabar logslogsPara asegurar que se mantiene Para asegurar que se mantiene la coherencia del esquemala coherencia del esquema, , asociando a operaciones en una tabla operaciones en otras asociando a operaciones en una tabla operaciones en otras tablas cuando los mecanismos de Oracle resultan insuficientestablas cuando los mecanismos de Oracle resultan insuficientes

TriggersTriggers: caracter: caracteríísticassticas

Pueden disparar otros Pueden disparar otros triggerstriggers

Pueden llamar a otros procedimientosPueden llamar a otros procedimientos

No tienen parNo tienen paráámetrosmetros

No pueden ser llamados desde otros No pueden ser llamados desde otros procedimientosprocedimientos

TriggersTriggers: ventajas: ventajas

Seguridad: permiten restringir al acceso de las tablas a : permiten restringir al acceso de las tablas a nivel de detallenivel de detalle

EficienciaEficiencia: El plan de ejecuci: El plan de ejecucióón de un n de un triggertrigger ssóólo se lo se analiza una vez, utilizanaliza una vez, utilizáándose directamente a partir de ndose directamente a partir de entonces entonces

Desventajas: no debe abusarse de ellos y su existencia : no debe abusarse de ellos y su existencia debe estar bien documentada; de otra forma la debe estar bien documentada; de otra forma la utilizaciutilizacióón de la BBDD se vuelve difn de la BBDD se vuelve difíícil de entender.cil de entender.

7

TriggersTriggers: Tipos: Tipos

Hay 2 tipos de Hay 2 tipos de TriggersTriggers: los asociados a una : los asociados a una instrucciinstruccióón y los asociados a una filan y los asociados a una fila

A A nivel de instrucción: se ejecuta despu: se ejecuta despuéés de s de que una instruccique una instruccióón n insertinsert / / deletedelete / / updateupdate se se ha llevado a cabo con ha llevado a cabo con ééxitoxito

Asociado a una fila: Se puede ejecutar antes o : Se puede ejecutar antes o despudespuéés de la instruccis de la instruccióón y se llama por cada fila n y se llama por cada fila afectadaafectada

TriggersTriggers: sintaxis: sintaxis

CREATECREATE OR REPLACE TRIGGER nombreOR REPLACE TRIGGER nombre--deldel--triggertrigger[FOLLOWS nombre[FOLLOWS nombre--otrootro--triggertrigger]][BEFORE/AFTER][BEFORE/AFTER][INSERT/DELETE/UPDATE/[INSERT/DELETE/UPDATE/UPDATEUPDATE OF listaOF lista--

columnas] columnas] ONON nombrenombre--tablatabla[REFERENCING [OLD AS nombre[REFERENCING [OLD AS nombre--antiguo][NEW AS antiguo][NEW AS

nombrenombre--nuevo]] nuevo]] [FOR EACH ROW/FOR EACH STATEMENT][FOR EACH ROW/FOR EACH STATEMENT][WHEN {condiciones}][WHEN {condiciones}]{Bloque est{Bloque estáándar de sentencias PL/SQL... BEGIN, ndar de sentencias PL/SQL... BEGIN,

EXCEPTION}EXCEPTION}

TriggersTriggers: sintaxis: sintaxis

FollowsFollows: a partir de la versi: a partir de la versióón 11g; permite establecer un n 11g; permite establecer un orden entre varios orden entre varios triggerstriggers asociados a la misma asociados a la misma operacioperacióón: el n: el triggertrigger declarado seguirdeclarado seguiráá al que se indicaal que se indica

WhenWhen: condici: condicióón para que se ejecute el n para que se ejecute el triggertrigger en en disparadores a nivel de filadisparadores a nivel de fila

Diferencia entre Diferencia entre UpdateUpdate y y UpdateUpdate OfOf ListaColumnasListaColumnas: el : el primero al hacer un primero al hacer un updateupdate, el segundo s, el segundo sóólo si se lo si se modifican las columnas indicadasmodifican las columnas indicadas

TriggersTriggers: ejemplo: ejemplo

CREATE TRIGGER CREATE TRIGGER CompruebaEdadCompruebaEdadBeforeBefore INSERT OR UPDATE OF edad ON Persona INSERT OR UPDATE OF edad ON Persona FOR EACH ROW FOR EACH ROW BEGIN BEGIN

IF (:IF (:new.edadnew.edad < 0) THEN < 0) THEN RAISE_APPLICATION_ERRORRAISE_APPLICATION_ERROR((--20000, 20000,

‘‘edad no vedad no váálida'); lida'); END IF; END IF;

END; END; No se llega a insertar ningNo se llega a insertar ningúún daton dato

TriggersTriggers: orden de ejecuci: orden de ejecucióónn

Este es el orden de ejecuciEste es el orden de ejecucióón de una instruccin de una instruccióón DML:n DML:1. Ejecutar, si existe, el disparador de tipo BEFORE 1. Ejecutar, si existe, el disparador de tipo BEFORE (disparador previo) con nivel de instrucci(disparador previo) con nivel de instruccióónn2. Para cada fila a la que afecte la orden:2. Para cada fila a la que afecte la orden:

a. Ejecutar, si existe, el disparador de tipo BEFORE cona. Ejecutar, si existe, el disparador de tipo BEFORE connivel de fila.nivel de fila.

b. Ejecutar la propia orden.b. Ejecutar la propia orden.c. Ejecutar, si existe, el disparador de tipo AFTER c. Ejecutar, si existe, el disparador de tipo AFTER

(disparador posterior) con nivel de fila.(disparador posterior) con nivel de fila.3. Ejecutar, si existe, el disparador de tipo AFTER con 3. Ejecutar, si existe, el disparador de tipo AFTER con nivel de instruccinivel de instruccióón n

TriggersTriggers: : oldold y y newnew

Pseudo registros con los valores antiguos y Pseudo registros con los valores antiguos y nuevos de la filanuevos de la fila

No definido; a No definido; a nullnullValores de la fila Valores de la fila antes de borrarseantes de borrarse

DeleteDelete

Nuevos valores de la Nuevos valores de la filafila

Valores de fila antes Valores de fila antes de la actualizacide la actualizacióónn

UpdateUpdate

Valores a insertarValores a insertarNo definido; a No definido; a nullnullInsertInsert

::newnew::oldoldOperaciOperacióónn

8

TriggersTriggers: tabla mutando: tabla mutando……

Es habitual ver el siguiente error durante la Es habitual ver el siguiente error durante la ejecuciejecucióón de un n de un triggertrigger::

ORA-04091: table <tablename> is mutating, trigger/function may not see it

Ocurre cuando un disparado a nivel de filas Ocurre cuando un disparado a nivel de filas accede a la misma tabla en la que se basa y la accede a la misma tabla en la que se basa y la operacioperacióón no es un n no es un insertinsert de una sola filade una sola fila

Tabla mutando: EjemploTabla mutando: Ejemplo

createcreate tabletable t (col1 t (col1 numbernumber, col2 , col2 varcharvarchar(30)); (30)); create or replace trigger t_trg before insert or update or delete on t

for each rowdeclare i integer; beginselect count(*) into i from t; --más cosas …

end; \\insertinsert intointo t t valuesvalues (1, (1, ‘‘prueba');prueba');

1 1 rowrow createdcreated. . updateupdate t set col1 = 2; t set col1 = 2; update t * ERROR at line 1: ORA-04091: table SYSTEM.T is mutating, trigger/function

may not see it ORA-06512: at "SYSTEM.T_TRG", line 4 ORA-04088: error duringexecution of trigger 'SYSTEM.T_TRG'

TriggersTriggers: distinguir la operaci: distinguir la operacióónn

En lugar de varios En lugar de varios triggerstriggers para varias operaciones es para varias operaciones es buena idea crear uno y distinguir casos:buena idea crear uno y distinguir casos:

CREATE OR REPLACE TRIGGER CREATE OR REPLACE TRIGGER nombre_triggernombre_trigger BEFORE BEFORE INSERT OR UPDATE OR DELETE ON INSERT OR UPDATE OR DELETE ON nombre_tablanombre_tabla

FOR EACH ROW FOR EACH ROW BEGIN BEGIN IF INSERTING THEN... IF INSERTING THEN... ----do do whateverwhatever END IF; END IF; IF UPDATING THEN ... IF UPDATING THEN ... ----do do somethingsomething elseelse END IF; END IF; IF DELETING THEN ... IF DELETING THEN ... ----do do somethingsomething elseelse END IF; END IF;

END; END;

TriggersTriggers: gesti: gestióónn

TriggersTriggers existentes:existentes:select trigger_name from user_triggers;

Detalles de un Detalles de un triggertrigger::select trigger_type, triggering_event, table_name, referencing_names,

trigger_body from user_triggers where trigger_name = '<trigger_name>';

Borrar un trigger: drop trigger '<trigger_name>’;

En lugar de borrarlo un trigger puede desactivarse: alter trigger <trigger_name> {disable|enable};

TriggersTriggers: tambi: tambiéén para DDLn para DDL

Ejemplo:Ejemplo:

CREATE OR REPLACE TRIGGER CREATE OR REPLACE TRIGGER drop_triggerdrop_triggerBEFORE DROP ON BEFORE DROP ON bertoldo.SCHEMAbertoldo.SCHEMA BEGIN BEGIN RAISE_APPLICATION_ERRORRAISE_APPLICATION_ERROR ( ( numnum => => --20000, 20000, msgmsg => => ‘‘No se puede hacer No se puede hacer dropdrop'); END; '); END;

No permite borrar ninguna fila a No permite borrar ninguna fila a bertoldobertoldo

TriggersTriggers: tambi: tambiéén para el sistema (I)n para el sistema (I)

Ejemplo: queremos llevar un Ejemplo: queremos llevar un loglog de los usuarios que se de los usuarios que se han conectado en esta tabla:han conectado en esta tabla:

CREATE TABLE CREATE TABLE session_infosession_info ((usernameusername VARCHAR2(30), VARCHAR2(30), logon_datelogon_date DATE, DATE, session_idsession_id VARCHAR2(30), VARCHAR2(30), ip_addrip_addr VARCHAR2(30), VARCHAR2(30), hostnamehostname VARCHAR2(30),VARCHAR2(30),auth_typeauth_type VARCHAR2(30)); VARCHAR2(30));

9

TriggersTriggers: tambi: tambiéén para el sistema (II)n para el sistema (II)CREATE OR REPLACE TRIGGER CREATE OR REPLACE TRIGGER trg_session_infotrg_session_infoBEFORE LOGOFF ON DATABASE BEFORE LOGOFF ON DATABASE DECLARE DECLARE session_idsession_id VARCHAR2(30); VARCHAR2(30); ip_addrip_addr VARCHAR2(30); VARCHAR2(30); hostnamehostname VARCHAR2(30); VARCHAR2(30); auth_typeauth_type VARCHAR2(30); VARCHAR2(30); BEGIN BEGIN SELECT SELECT sys_contextsys_context ('USERENV', 'SESSIONID') INTO ('USERENV', 'SESSIONID') INTO session_idsession_id FROM dual;FROM dual;SELECT SELECT sys_contextsys_context ('USERENV', '('USERENV', 'IP_ADDRESSIP_ADDRESS') INTO ') INTO ip_addrip_addr FROM dual; FROM dual; SELECT SELECT sys_contextsys_context ('USERENV', 'HOST') INTO ('USERENV', 'HOST') INTO hostnamehostname FROM dual;FROM dual;SELECT SELECT sys_contextsys_context ('USERENV', '('USERENV', 'AUTHENTICATION_TYPEAUTHENTICATION_TYPE') ')

INTO INTO auth_typeauth_type FROM dual; FROM dual; INSERT INTO INSERT INTO session_infosession_info VALUES (VALUES (useruser, , sysdatesysdate, , session_idsession_id, , ip_addrip_addr, ,

hostnamehostname, , auth_typeauth_type); ); END; / END; /

Funciones Funciones úútiles en SQL de Oracle tiles en SQL de Oracle (I)(I)

Función Descripción

String Functions upper(s), lower(s) convert string s to upper/lower-case initcap(s) capitalise first letter of each word ltrim(s), rtrim(s) remove blank char. from left/right substr(s,start,len) sub-string of length len from position start

length(s) length of s

Funciones Funciones úútiles en SQL de Oracle tiles en SQL de Oracle (II)(II)

Date Functions sysdate current date (on Oracle server)

to_date(date, format) date formatting

Number Functions round(x) round real number x to integer

mod(n,p) n modulus p abs(x) absolute value of x dbms_random.random() generate a random integer

Funciones Funciones úútiles en SQL de Oracle tiles en SQL de Oracle (III)(III)

Type Conversion Functions to_char() convert to string to_date() convert to date to_number() convert to number

Miscellaneous Functions user current Oracle user