MODIFICACIÓN DE BASES DE DATOS. DATOS. LENGUAJE LENGUAJE DE MANIPULACIÓN DE DATOS ACTIVIDADES RESUELTAS
IES Luis Vélez de Guevara Departamento de Informátia
Página 1 de 35
ACTIVIDADES Práctica 1 I!SERT " U#DATE " DELETE
Haciendo uso del esquema E01 cuyo diseño físico realizamos en el tema 3, realiza las operaciones de manipulacin de datos indicadas a continuacin!
CREATE TABLE ALUMNO
( NUMMATRICULA NOMBRE FECHANACIMIENTO TELEFONO );
NUMBER(3) PRIMARY KEY, VARCHAR2(50), DATE, CHAR(9)
CREATE TABLE PROFESOR
( IDPROFESOR NIF_P NOMBRE ESPECIALIDAD TELEFONO );
NUMBER(2) PRIMARY KEY, CHAR(9) UNIQUE, VARCHAR2(50), VARCHAR2(50), CHAR(9)
CREATE TABLE ASIGNATURA
( CODASIGNATURA NOMBRE IDPROFESOR );
CHAR(6) PRIMARY KEY, VARCHAR2(30), NUMBER(2) REFERENCES PROFESOR(IDPROFESOR)
CREATE TABLE RECIBE
( NUMMATRICULA NUMBER(3) REFERENCES ALUMNO(NUMMATRICULA), REFERENCES ASIGNATURA(CODASIGNATURA CODASIGNATURA CHAR(6) ASIGNATURA(CODASIGNATURA), ), CURSOESCOLAR CHAR(9), PRIMARY KEY (NUMMATRICULA, CODASIGNATURA, CURSOESCOLAR) );
Página " de 35
1! #ealiza las siguientes inserciones $los datos puedes in%entarlos&! ' (nserta " profesores! ' (nserta ) asignaturas! ' (nserta 10 alumnos! ' *ada alumno de+e realizar al menos " asignaturas! -- ! I"#$%&'" $ *+# INSERT INTO PROFESOR VALUES (, 2.536.5M, PACO,
MATEM/TICAS, 93520.1) ;
INSERT INTO PROFESOR VALUES (2, 2.536.6N, LUCIA, LENGUAE,
INSERT INSERT INSERT INSERT
INTO ASIGNATURA INTO ASIGNATURA INTO ASIGNATURA INTO ASIGNATURA
VALUES (MATEMA, VALUES (LENGUA, VALUES (BIOLOG, VALUES (QUIMIC,
INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT
INTO ALUMNO INTO ALUMNO INTO ALUMNO INTO ALUMNO INTO ALUMNO INTO ALUMNO INTO ALUMNO INTO ALUMNO INTO ALUMNO INTO ALUMNO
VALUES (00, VALUES (02, VALUES (03, VALUES (0., VALUES (05, VALUES (06, VALUES (01, VALUES (0, VALUES (09, VALUES (0,
ANA, PEPE, UAN, RODOLFO, ANGUSTIAS, AURELIO, ANACLETO, EUSEBIO, EUSTAQUIO, AMAPOLO,
INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT
INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE INTO RECIBE
VALUES (00, VALUES (00, VALUES (02, VALUES (02, VALUES (03, VALUES (03, VALUES (0., VALUES (0., VALUES (05, VALUES (05, VALUES (06, VALUES (06, VALUES (01, VALUES (01, VALUES (0, VALUES (0, VALUES (09, VALUES (09, VALUES (0, VALUES (0,
MATEMA, LENGUA, MATEMA, LENGUA, MATEMA, LENGUA, MATEMA, LENGUA, BIOLOG, QUIMIC, BIOLOG, QUIMIC, BIOLOG, LENGUA, BIOLOG, QUIMIC, LENGUA, BIOLOG, QUIMIC, BIOLOG,
9920.1);
MATEM/TICAS, LENGUA, BIOLOGA, QUMICA,
0424993, 09424993, 0424992, 044990, 0944993, 044993, 24034992, 2040.4993, 25405499, 214064993,
2064201) ; 2064201) ; 2064201) ; 2064201) ; 2064201) ; 2064201) ; 2064201) ; 2064201) ; 2064201) ; 2064201) ; 2064201) ; 2064201) ; 200242003) ; 200242003) ; 2064201) ; 2064201) ; 200242003) ; 200242003) ; 20042002) ; 20042002) ;
Página 3 de 35
) ; 2); ); 2); 65110); 6592119); 66011109); 692110); 699101) ; 6951106); 62011105); 69110.); 6.1103) ; 691102);
"! (ntroduce " profesores con el mismo (-! ./u sucede .Por qu ------
2! I"#$%&'" $ %$'#+%# &" * 7'#7* &*8$ %'7*%'*! D* $%%% :$#+ ;:$ " :$$ <*=$% # %$'#+%# &" * 7'#7* &*8$ %'7*%'*! L* &*8$ %'7*%'* #'$7%$ +'$"$ *# %$#+%'&&'"$# $ :"'&'* > 8*% " ":! INSERT INTO PROFESOR VALUES (3, 2.536.0B, UAN, MATEM/TICAS, 63520.0); INSERT INTO PROFESOR VALUES (3, 2.536.C, OSE, LENGUAE,
6920.);
3! (ntroduce " alumnos con el mismo um2atrícula! ./u sucede .Por qu ------
3! D* * L* $
I"#$%&'" $ %$'#+%# &" * 7'#7* &*8$ %'7*%'*! $%%% :$#+ ;:$ " :$$ <*=$% # %$'#+%# &" 7'#7* &*8$ %'7*%'*! &*8$ %'7*%'* #'$7%$ +'$"$ *# %$#+%'&&'"$# :"'&'* > 8*% " ":! INSERT INTO ALUMNO VALUES (, E?A, 0424993, 6.110); INSERT INTO ALUMNO VALUES (, LISA, 09424993, 6.92119);
)! (ntroduce 3 alumnos para los cuales no conocemos el nmero de telfono! -- .! I"#$%&'" $ %$'#+%# &" 8*%$# ":#! 3 @%7*# '#+'"+*#! INSERT INTO ALUMNO VALUES (2, LORENA, 2040.4993, NULL) ; INSERT INTO ALUMNO (NUMMATRICULA, NOMBRE, FECHANACIMIENTO) VALUES (3, ?ANESA, 25405499) ; INSERT INTO ALUMNO (NOMBRE, FECHANACIMIENTO, NUMMATRICULA) VALUES (SIL?IA, 214064993, .) ;
5! 2odi4ca los datos de los 3 alumnos anteriores para esta+lecer un nmero de telfono! -- 5! A&+:*'*&'" $ &*7# $ %$'#+% &" 8*%$# ":#! UPDATE ALUMNO SET TELEFONO 652.56 WHERE NUMMATRICULA 2 ; UPDATE ALUMNO SET TELEFONO 653.56 WHERE NUMMATRICULA 3 ; UPDATE ALUMNO SET TELEFONO 669.56 WHERE NUMMATRICULA . ;
Página ) de 35
! Para todos los alumnos, poner "000 como año de nacimiento! -- 6! A&+:*'*&'" $ * $" :" &*7 $ @$&<*! UPDATE ALUMNO SET FECHANACIMIENTO TO_CHAR(FECHANACIMIENTO,DDMM)||2000;
6! Para los profesores que tienen nmero de telfono y (- no comience por 7, poner 8(nformática8 como especialidad! -- 1! A&+:*'*&'" $ &*7 #' #$ &:7$ :"* &"'&'"! UPDATE PROFESOR SET ESPECIALIDAD INFORMATICA WHERE TELEFONO IS NOT NULL AND NIF_P NOT LIKE 9;
9! *am+ia la asignacin de asignaturas para los profesores! Es decir, las asignaturas impartidas por un profesor las dará el otro profesor y %ice%ersa! -- ! A&+:*'*&'"! I"+$%&*7=' $ 8*%$#! -- P*%* $% <*&$% $ '"+$%&*7=' "$&$#'+*7# :" 8*% '"+$%7$'! UPDATE PROFESOR SET ESPECIALIDAD TEMP WHERE ESPECIALIDAD MATEM/TICAS ; UPDATE PROFESOR SET ESPECIALIDAD MATEM/TICAS WHERE ESPECIALIDAD LENGUAE ; UPDATE PROFESOR SET ESPECIALIDAD LENGUAE WHERE ESPECIALIDAD TEMP ;
7! En la ta+la #eci+e +orra todos los registros que pertenecen a una de las asignaturas! -- 9! B%%* $ %$'#+%# $" :"* R$*&'"! DELETE RECIBE WHERE CODASIGNATURA LENGUA ;
10! En la ta+la :signatura +orra dic;a asignatura! -- 0! B%%* $ %$'#+%# $ :"* E"+'* #'" %$*&'"$#! DELETE ASIGNATURA WHERE CODASIGNATURA LENGUA ;
Página 5 de 35
11!
! B%%* $ %$'#+%# $ :"* E"+'* &" %$*&'"$#! N "# $%7'+$ =%%*% '%$&+*7$"+$ # %$'#+%# $ * +*=* ASIGNATURA, >* ;:$ '&<# %$'#+%# *%+'&'*" $" :"* %$*&'"! N +$"%*7# $#+* '7'+*&'" #' $" $ '#$ @#'& <:='#$7# $@'"' * &*8$ @%"$* ;:$ *:"+* * CODASIGNATURA &" * &:#:* ON DELETE CASCADE! DELETE ASIGNATURA;
1"!
2! B%%* $ %$'#+%# $ :"* E"+'* &" %$*&'"$#! N "# $%7'+$ =%%*% '%$&+*7$"+$ # %$'#+%# $ * +*=* PROFESOR >* ;:$ '&<# %$'#+%# *%+'&'*" $" :"* %$*&'"! N +$"%*7# $#+* '7'+*&'" #' $" $ '#$ @#'& <:='#$7# $@'"' * &*8$ @%"$* ;:$ *:"+* * IDPROFESOR &" * &:#:* ON DELETE CASCADE! DELETE PROFESOR;
13!
3! B%%* $ %$'#+%# $ :"* E"+'* &" %$*&'"$#! N "# $%7'+$ =%%*% '%$&+*7$"+$ # %$'#+%# $ * +*=* ALUMNO >* ;:$ '&<# %$'#+%# *%+'&'*" $" :"* %$*&'"! N +$"%*7# $#+* '7'+*&'" #' $" $ '#$ @#'& <:='#$7# $@'"' * &*8$ @%"$* ;:$ *:"+* * NUMMATRICULA &" * &:#:* ON DELETE CASCADE! DELETE ALUMNO;
Página de 35
---------
NOTA E" *# 3 *&+'8'*$# *"+$%'%$# * #:&'" *#* % $'7'"*% * %$#+%'&&'" $ &*8$ @%"$* > 88$%* * **'% &" * &:#:* ON DELETE CASCADE! P$7# &"#$:'% $#+ &*7='*" $ '#$ @#'& ALTER TABLE RECIBE DROP CONSTRAINT F2!!!; ALTER TABLE RECIBE ADD CONSTRAINT F2!!! FOREIGN KEY(CODASIGNATURA) REFERENCES ASIGNATURA ON DELETE CASCADE;
-- ALTER TABLE ASIGNATURA DROP CONSTRAINT F!!!; -- ALTER TABLE ASIGNATURA ADD CONSTRAINT F!!! FOREIGN KEY(IDPROFESOR) REFERENCES PROFESOR ON DELETE CASCADE; --- ALTER TABLE RECIBE DROP CONSTRAINT F!!!; -- ALTER TABLE RECIBE ADD CONSTRAINT F!!! FOREIGN KEY(NUMMATRICULA) REFERENCES ALUMNO ON DELETE CASCADE; --------
S'" $7=*% * #:&'" #$ &7'&* =*#+*"+$ :$#+ ;:$ $" $ '#$ @#'& <*=*7# &%$* *# %$#+%'&&'"$# #'" "$%$ "7=%$! E#+$ $# :" $ # 7+'8# % ;:$ $# '7%+*"+$ "$% "7=%$ * *# %$#+%'&&'"$#! A#, #' :$ "$&$#'+*7# 7'@'&*% $ '#$ @#'& %$7# <*&$% $ @%7* #$"&'*!
Página 6 de 35
Práctica 2 I!SERT " U#DATE " DELETE
Haciendo uso del esquema E06 cuyo diseño físico realizamos en el tema 3, realiza las operaciones de manipulacin de datos indicadas a continuacin!
=eniendo en cuenta las siguientes restricciones que teníamos declaradas> ' o pueden ser nulos los siguientes campos> om+re de ?ocio, =ítulo de Película! ' ?e@o toma los %alores H o 2! ' Por defecto si no se indica nada un actor o actriz no es Protagonista $este campo toma %alores ? o &! ' -ec;aAe%olucin de+e ser mayor que -ec;a:lquiler! CREATE TABLE DIRECTOR
( NOMBRE VARCHAR2(.0) CONSTRAINT P_DIRECTOR PRIMARY KEY, NACIONALIDAD VARCHAR2(.0) ); CREATE TABLE PELICULA
(
ID TITULO PRODUCTORA NACIONALIDAD FECHA DIRECTOR
NUMBER CONSTRAINT P_PELICULA PRIMARY KEY, VARCHAR2(.0), VARCHAR2(.0), VARCHAR2(.0), DATE, VARCHAR2(.0) CONSTRAINT F_DIRECTOR
REFERENCES DIRECTOR(NOMBRE)
); CREATE TABLE EEMPLAR
( IDPELICULA NUMBER, NUMERO NUMBER(2), VARCHAR2(.0), ESTADO
Página 9 de 35
CONSTRAINT P_EEMPLAR PRIMARY KEY(IDPELICULA, NUMERO), CONSTRAINT F_EEMPLAR FOREIGN KEY(IDPELICULA) REFERENCES PELICULA(ID) ); CREATE TABLE ACTORES
( VARCHAR2(.0), NOMBRE NACIONALIDAD VARCHAR2(.0), SEJO CHAR(), CONSTRAINT P_ACTORES PRIMARY KEY(NOMBRE), CONSTRAINT C_SEJO CHECK (SEJO IN (H, M)) ); CREATE TABLE SOCIO
(
DNI NOMBRE DIRECCION TELEFONO A?ALADOR
CHAR(9), VARCHAR2(.0) CONSTRAINT NN_NOMBRE NOT NULL, VARCHAR2(.0), CHAR(9), CHAR(9), CONSTRAINT P_SOCIO PRIMARY KEY(DNI), CONSTRAINT F_SOCIO FOREIGN KEY(A?ALADOR) REFERENCES SOCIO(DNI) ); CREATE TABLE ACTUA
( ACTOR
VARCHAR2(.0) CONSTRAINT F_ACTUA REFERENCES ACTORES ON DELETE CASCADE, NUMBER IDPELICULA CONSTRAINT F2_ACTUA REFERENCES PELICULA ON DELETE CASCADE, PROTAGONISTA CHAR() DEFAULT N, CONSTRAINT P_ACTUA PRIMARY KEY(ACTOR, IDPELICULA), CONSTRAINT C_PROTAGONISTA CHECK (PROTAGONISTA IN (S, N)) ); CREATE TABLE ALQUILA
(
CHAR(9), DNI NUMBER, IDPELICULA NUMERO NUMBER(2), FECHA_ALQUILER DATE, FECHA_DE?OLUCION DATE, CONSTRAINT P_ALQUILA PRIMARY KEY(DNI, IDPELICULA, NUMERO, FECHA_ALQUILER), CONSTRAINT F_DNI FOREIGN KEY(DNI) REFERENCES SOCIO(DNI), CONSTRAINT F2_PELI FOREIGN KEY(IDPELICULA, NUMERO) REFERENCES EEMPLAR(IDPELICULA, NUMERO), CONSTRAINT C_FECHAS CHECK (FECHA_DE?OLUCION K FECHA_ALQUILER)
);
Página 7 de 35
1! #ealiza las siguientes inserciones $los datos puedes in%entarlos&! ' (nserta " directores! ' (nserta ) películas! =odas ellas están dirigidas por alguno de los directores anteriores! ' (nserta " eBemplares de cada película! ' (nserta ) socios! ' (nserta como mínimo actores! ' *ada película de+e tener al menos el actorCatriz protagonista asociado! ' =odos los eBemplares de+en tener al menos 1 alquiler! -- ! I"#$%&'" $ *+# INSERT INTO DIRECTOR VALUES (UAN, ESPAOLA); INSERT INTO DIRECTOR VALUES (PEDRO, ESPAOLA) ; INSERT INTO PELICULA VALUES (00, LA GRANA, TELE5, RUSA, 0403491, UAN) ; INSERT INTO PELICULA VALUES (002, MANDINGO, ANTENA3, APONESA, 40349, UAN) ; INSERT INTO PELICULA VALUES (003, FRANCO, INTERECONOMIA,ESPAOLA, 0404990, PEDRO); INSERT INTO PELICULA VALUES (00., COSMOS,?TELE5, ITALIANA, 54042000, PEDRO) ; INSERT INTO EEMPLAR VALUES (00, 0, BIEN) ; INSERT INTO EEMPLAR VALUES (00, 02, MAL) ; INSERT INTO EEMPLAR VALUES (002, 0, BIEN) ; INSERT INTO EEMPLAR VALUES (002, 02, MAL) ; INSERT INTO EEMPLAR VALUES (003, 0, BIEN) ; INSERT INTO EEMPLAR VALUES (003, 02, REGULAR) ; INSERT INTO EEMPLAR VALUES (00., 0, BIEN) ; INSERT INTO EEMPLAR VALUES (00., 02, REGULAR) ; INSERT INTO SOCIO VALUES (5.0591, DANIEL, C4 NUE?A, , 691565656, NULL) ; INSERT INTO SOCIO VALUES (5.05919, ANA,
C4 ANCHA, 5, 6915.5.5., 5.0591) ;
INSERT INTO SOCIO VALUES (5.0591, SIL?IA, C4 FORT,
., 691222, 5.05919) ;
INSERT INTO SOCIO
Página 10 de 35
VALUES (5.05912, JA?I,
INSERT INSERT INSERT INSERT INSERT INSERT
INTO ACTORES INTO ACTORES INTO ACTORES INTO ACTORES INTO ACTORES INTO ACTORES
INSERT INSERT INSERT INSERT INSERT INSERT
INTO ACTUA INTO ACTUA INTO ACTUA INTO ACTUA INTO ACTUA INTO ACTUA
C4 ANCHA, 2, 91232323, 5.0591) ;
VALUES (PENELOPE, VALUES (FRANCESCO, VALUES (ANA, VALUES (CARMEN, VALUES (ANDREA, VALUES (?LADIMIR,
ESPAOLA, ITALIANA, POLACA, ESPAOLA, ITALIANA, RUSA,
00, VALUES (ANDREA, VALUES (?LADIMIR, 00, VALUES (CARMEN, 002, VALUES (PENELOPE, 003, VALUES (FRANCESCO,003, 00., VALUES (ANA,
M); H) ; M); M); H); H);
S); N) ; S); S) ; N) ; S);
INSERT INTO ALQUILA VALUES (5.0591, 00,0, 0404201, 03404201) ; INSERT INTO ALQUILA VALUES (5.05919, 00,02, 0404201, 03404201) ; INSERT INTO ALQUILA VALUES (5.0591, 002,0, 04024201, 034024201) ; INSERT INTO ALQUILA VALUES (5.0591, 002,02, 04024201, 034024201) ; INSERT INTO ALQUILA VALUES (5.05912, 003,0, 04034201, 034034201) ; INSERT INTO ALQUILA VALUES (5.05912, 003,02, 04034201, 034034201) ; INSERT INTO ALQUILA VALUES (5.05919, 00.,0, 040.4201, 0340.4201) ; INSERT INTO ALQUILA VALUES (5.05919, 00.,02, 040.4201, 0340.4201) ;
"! (nserta %alores para compro+ar que la siguiente restriccin funciona correctamente> ' o pueden ser nulos los siguientes campos> om+re de ?ocio, =ítulo de Película! -- 2! C7%=*&'" $ %$#+%'&&'"$# INSERT INTO SOCIO VALUES (5.059, NULL, C4 ROSAL, 3, 691565656, NULL) ; -- E &*7 $ "7=%$ " :$$ #$% ":, :$#+ ;:$ +'$"$ -- :"* %$#+%'&&'" $ 8*% " ":!
Página 11 de 35
INSERT INTO PELICULA VALUES (00, NULL, TELE5, RUSA, 0403491, UAN) ;
-- E &*7 ++: # :$$ $#+*% * ":, :$#+ ;:$ " $# -- &*8$ %'7*%'*, "' +'$"$ %$#+%'&&'" $ 8*% " ":!
3! (nserta %alores para compro+ar que la siguiente restriccin funciona correctamente> ' ?e@o toma los %alores H o 2! -- 3! C7%=*&'" $ %$#+%'&&'"$# INSERT INTO ACTORES VALUES (PUTIN, RUSA, H); AMERICANA,M); INSERT INTO ACTORES VALUES (IM, ); INSERT INTO ACTORES VALUES (ROSA, RUSA, -- L* +'7* '"#$%&'" * $%%%, :$#+ ;:$ # #$ $%7'+$" -- 8*%$# H M *%* $ &*7 #$!
)! (nserta %alores para compro+ar que la siguiente restriccin funciona correctamente> ' Por defecto si no se indica nada un actor o actriz no es Protagonista $este campo toma %alores ? o &! -- .! C7%=*&'" $ %$#+%'&&'"$# INSERT INTO ACTUA (ACTOR, IDPELICULA) VALUES (ANA, 002); -- S' " '"'&*7# 8*% *%* $ &*7 %+*"'#+*, #$ '"+%:&$ -- $ '"'&* % $@$&+ $" * %$#+%'&&'", $# $&'%, 8*% N! SELECT FROM ACTUA WHERE ACTOR ANA AND IDPELICULA 002 ;
5! (nserta %alores para compro+ar que la siguiente restriccin funciona correctamente> ' -ec;aAe%olucin de+e ser mayor que -ec;a:lquiler! -- 5! C7%=*&'" $ %$#+%'&&'"$# INSERT INTO ALQUILA VALUES (5.05912, 00.,0, 04034201, 034024201) ; -- D*% $%%%, :$#+ ;:$ +$"$7# :"* %$#+%'&&'" +' CHECK ;:$ -- ='* * ;:$ * @$&<* $ $8:&'" #$* 7*>% * * @$&<* $ *;:'$%!
Página 1" de 35
! *am+ia la nacionalidad para los directores! Por eBemplo de 8Estadounidense8 a 8D?:8 o similar, dependiendo de los %alores que ;ayas introducido! -- 6! A&+:*'*&'" $ %$'#+%#! UPDATE DIRECTOR SET NACIONALIDAD ESP WHERE NACIONALIDAD ESPAOLA ;
6! *am+ia la nacionalidad para los actores! Por eBemplo de 8Estadounidense8 a 8D?:8 o similar, dependiendo de los %alores que ;ayas introducido! -- 1! A&+:*'*&'" $ %$'#+%#! UPDATE ACTORES SET NACIONALIDAD ESP WHERE NACIONALIDAD ESPAOLA ;
9! 2odi4ca los datos de todos los socios para que el a%alista sea un nico socio, siempre el mismo para todos, e@cepto para el a%alista mismo que no dispone de ninguno! -- ! A&+:*'*&'" $ %$'#+%#! UPDATE SOCIO SET A?ALADOR 5.0591 WHERE DNI 5.0591 ;
7! Elimina los socios cuyo nmero de telfono empiece por una cifra inferior a 5! ./u sucede.Por qu -- 9! E'7'"*&'" $ %$'#+%# ;:$ #'=$7$"+$ *%+'&'*" -- $" :"* %$*&'"! DELETE SOCIO WHERE REGEJP_LIE(TELEFONO, 0-.!) ; -- A =%%*% # %$'#+%#, #' '&<# %$'#+%# *%+'&'*" -- $" :"* %$*&'", % $$7 $" * %$*&'" *;:'$%, " "# -- 8* * $*% $'7'"*%#!
10! Elimina los socios cuyo nmero de telfono empiece por una cifra superior o igual a 5! ./u sucede.Por qu -- 0! E'7'"*&'" $ %$'#+%# ;:$ #'=$7$"+$ *%+'&'*" -- $" :"* %$*&'"! DELETE SOCIO WHERE REGEJP_LIE(TELEFONO, 5-9!) ;
Página 13 de 35
-- A =%%*% # %$'#+%#, #' '&<# %$'#+%# *%+'&'*" -- $" :"* %$*&'", % $$7 $" * %$*&'" *;:'$%, " "# -- 8* * $*% $'7'"*%#!
11! .*omo lo solucionarías los pro+lemas anteriores .Podría ;a+erse e%itado el pro+lema con otro diseño físico.*mo ------
! M'@'&*&'" $ F *%* **'% ON DELETE CASCADE! L* 7$% @%7* $ %&$$% #$%* $'7'"*% * &*8$ @%"$* F_DNI $ * +*=* ALQUILA > 88$%* * &%$*% &" * &:#:* ON DELETE CASCADE! D$#:# $ $#+, >* $7# $$&:+*% *# 2 #$"+$"&'*# *"+$%'%$#! ALTER TABLE ALQUILA DROP CONSTRAINT F_DNI; ALTER TABLE ALQUILA ADD CONSTRAINT F_DNI FOREIGN KEY(DNI) REFERENCES SOCIO ON DELETE CASCADE;
1"! Elimina todos los directores! ./u sucede.Por qu -- 2! M'@'&*&'" $ F! DELETE DIRECTOR; -- N $%7'+$ * $'7'"*&'" $ %$'#+%#, :$#+ ;:$ '&<# '%$&+%$# -- +'$"$" $&:*# *#&'**#! -- P%*7# 7'@'&*% * F_DIRECTOR $ * +*=* PELICULA *%* **'% -- * &:#:* ON DELETE CASCADE, $% $" $#+$ &*# $# 7# *&"#$*=$ -- $#+*=$&$% * &:#:* ON DELETE SET NULL! E" +*"+ ;:$ PELICULA $# -- :"* $"+'* @:$%+$ > $'#+$" *+# ;:$ &" +* #$:%'* $#$*%$7# -- 7*"+$"$%, $# 7$% $#+* #$:"* #:&'"! -- D$#:# $ $#+, >* $7# $'7'"*% # '%$&+%$# #'" *@$&+*% -- * *# $&:*#! ALTER TABLE PELICULA DROP CONSTRAINT F_DIRECTOR; ALTER TABLE PELICULA ADD CONSTRAINT F_DIRECTOR FOREIGN KEY(DIRECTOR) REFERENCES DIRECTOR ON DELETE SET NULL;
13! Elimina " películas, las que desees!./u sucede.Por qu.*omo lo solucionarías .Podría ;a+erse e%itado el pro+lema con otro diseño físico.*mo -- 3! M'@'&*&'" $ F! DELETE PELICULA WHERE ID 00 OR ID 002; -- N $%7'+$ * $'7'"*&'" $ %$'#+%#, :$#+ ;:$ '&<*# $&:*# -- +'$"$" $$7*%$# *#&'*# > *$7#, %=*=$7$"+$, **%$&*" $" * -- %$*&'" ACTUA! L* $"+'* EEMPLAR $# :"* $"+'* =', &:>* -- $'#+$"&'* " +'$"$ #$"+' #'" * $"+'* @:$%+$, > * %$*&'" -- ACTUA " :*%* *+# $ "'":"* $"+'*, # #:# %$*&'"$#! -- E" *7=# &*## # $# *&"#$*=$ :#*% ON DELETE CASCADE!
Página 1) de 35
-- D$#:# $ $#+, >* $7# $$&:+*% * #$"+$"&'* *"+$%'%! ALTER TABLE EEMPLAR DROP CONSTRAINT F_EEMPLAR; ALTER TABLE EEMPLAR ADD CONSTRAINT F_EEMPLAR FOREIGN KEY(IDPELICULA) REFERENCES PELICULA ON DELETE CASCADE; ALTER TABLE ACTUA DROP CONSTRAINT F2_ACTUA; ALTER TABLE ACTUA ADD CONSTRAINT F2_ACTUA FOREIGN KEY(IDPELICULA) REFERENCES PELICULA ON DELETE CASCADE;
Página 15 de 35
!$TA% Las si&uientes prátias se realizarán dentro del es'uema E(#LEAD$S)
Práctica 3 #L"S*L% Introdui+n -- SQLPLUS EMPLEADOS4EMPLEADOS -- SQLK SHO USER -- USER IS EMPLEADOS
1! #ealiza una cone@in utilizando el cliente ?/FPlus y muestra el %alor de las siguientes %aria+les> D?E#, E?*:PE, (E?(GE, *?EP, P:IE?(GE, E*H, ?/P#2P= SHO SHO SHO SHO SHO SHO SHO
USER ESCAPE LINESIVE COLSEP PAGESIVE ECHO SQLPROMPT
"! Aesde el cliente ?/FPlus muestra el %alor de las %aria+les :D=*22(= y ?E#JE#D=PD=! SHO AUTOCOMMIT -- *:+&77'+ OFF SHO SER?EROUTPUT -- #$%8$%:+:+ OFF
3! Aesde el cliente ?/FPlus eBecuta el comando HEP ?HK para %er la ayuda acerca del comando ?HK! HELP SHO
)! Aesde el cliente ?/FPlus eBecuta el comando HEP SET para %er la ayuda acerca del comando ?E=! HELP SET
Página 1 de 35
5! Aesde el cliente ?/FPlus pon a las %aria+les ?E#JE#D=PD= y :D=*22(=! SET SER?EROUTPUT ON SET AUTOCOMMIT ON
! *rea un esquema llamado P?/ con contraseña P?/ y rol A<: para realizar las siguientes acti%idades!EBecuta el siguiente +loque! (ndica cuál es la salida! BEGIN IF 0 K 5 THEN
DBMS_OUTPUT!PUT_LINE (C'$%+); ELSE
DBMS_OUTPUT!PUT_LINE (F*#); END IF; END;
4 -- S*'* -- C'$%+
6! EBecuta el siguiente +loque! (ndica cuál es la salida! BEGIN IF 0 K 5 AND 5 K THEN
DBMS_OUTPUT!PUT_LINE (C'$%+); ELSE
DBMS_OUTPUT!PUT_LINE (F*#); END IF; END;
4 -- S*'* -- C'$%+
9! EBecuta el siguiente +loque! (ndica cuál es la salida! BEGIN IF 0 K 5 AND 5 K 50 THEN
DBMS_OUTPUT!PUT_LINE (C'$%+); ELSE
DBMS_OUTPUT!PUT_LINE (F*#);
Página 16 de 35
END IF; END;
4 -- S*'* -- F*#
7! EBecuta el siguiente +loque! (ndica cuál es la salida! BEGIN CASE WHEN 0 K 5 AND 5 K 50
THEN
DBMS_OUTPUT!PUT_LINE (C'$%+) ; ELSE
DBMS_OUTPUT!PUT_LINE (F*#); END CASE; END;
4 -- S*'* -- F*#
10! EBecuta el siguiente +loque! (ndica cuál es la salida! BEGIN FOR ' IN !!0 LOOP
DBMS_OUTPUT!PUT_LINE (') ; END LOOP; END;
4 --------
S*'* N7$%# $"+%$ > 0 2 3 !!! 9 0
11! EBecuta el siguiente +loque! (ndica cuál es la salida! BEGIN FOR ' IN REVERSE !!0 LOOP DBMS_OUTPUT!PUT_LINE (') ; END LOOP; END;
4 -- S*'* N7$%# $"+%$ 0 >
Página 19 de 35
-------
0 9 !!! 2
1"! EBecuta el siguiente +loque! (ndica cuál es la salida! DECLARE ":7 NUMBER(3) 0; BEGIN WHILE ":7W00 LOOP
DBMS_OUTPUT!PUT_LINE (":7); ":7 ":7X2; END LOOP; END;
4 --------
S*'* N7$%# *%$# $"+%$ 0 > 00 0 2 . !!! 9 00
13! EBecuta el siguiente +loque! (ndica cuál es la salida! DECLARE ":7 NUMBER(3) 0; BEGIN LOOP
DBMS_OUTPUT!PUT_LINE (":7); IF ":7 K 00 THEN EXIT; END IF; ":7 ":7X2; END LOOP; END;
4 --------
S*'* N7$%# *%$# $"+%$ 0 > 02 0 2 . !!! 00 02
Página 17 de 35
1)! EBecuta el siguiente +loque! (ndica cuál es la salida! DECLARE ":7 NUMBER(3) 0; BEGIN LOOP
DBMS_OUTPUT!PUT_LINE (":7); EXIT WHEN ":7 K 00; ":7 ":7X2; END LOOP; END;
4 --------
S*'* N7$%# *%$# $"+%$ 0 > 02 0 2 . !!! 00 02
Página "0 de 35
Práctica 4 #L"S*L% #roedimientos , -uniones
1! *rea un procedimiento llamado E?*#(
DBMS_OUTPUT!PUT_LINE (HOLA MUNDO); END ESCRIBE;
4
"! *rea un procedimiento llamado E?*#( ESCRIBE_MENSAE (7$"#*$ VARCHAR2)
CREATE OR REPLACE PROCEDURE ESCRIBE_MENSAE (7$"#*$ VARCHAR2) IS BEGIN DBMS_OUTPUT!PUT_LINE (7$"#*$); END ESCRIBE_MENSAE;
4
3! *rea un procedimiento llamado ?E#(E que muestre por pantalla una serie de nmeros desde un mínimo ;asta un má@imo con un determinado paso! a forma del procedimiento será la siguiente> SERIE (7'"'7 NUMBER, 7*'7 NUMBER, *# NUMBER)
CREATE OR REPLACE PROCEDURE SERIE (7'"'7 NUMBER, 7*'7 NUMBER, *# NUMBER) IS ":7 NUMBER 7'"'7; BEGIN WHILE ":7 W 7*'7 LOOP DBMS_OUTPUT!PUT_LINE (":7);
Página "1 de 35
":7 ":7 X *#; END LOOP; END SERIE;
4
)! *rea una funcin :G:# que reci+a dos parámetros y genere un nmero al azar entre un mínimo y má@imo indicado! a forma de la funcin será la siguiente> AVAR (7'"'7 NUMBER, 7*'7 NUMBER) RETURN NUMBER
CREATE OR REPLACE FUNCTION AVAR (7'"'7 NUMBER, 7*'7 NUMBER) RETURN NUMBER IS %*" NUMBER 7*'7 - 7'"'7; BEGIN RETURN MOD(ABS(DBMS_RANDOM!RANDOM), %*") X 7'"'7 ; END AVAR;
4
5! *rea una funcin =: que reci+a un parámetros que será una nota numrica entre 0 y 10 y de%uel%a una cadena de te@to con la cali4cacin $?u4ciente, NOTA ("+* NUMBER) RETURN VARCHAR2
CREATE OR REPLACE FUNCTION NOTA ("+* NUMBER) RETURN VARCHAR2 IS BEGIN CASE WHEN "+*0 OR "+*9 THEN RETURN S=%$#*'$"+$; WHEN "+* OR "+*1 THEN RETURN N+*=$; WHEN "+*6 THEN RETURN B'$"; WHEN "+*5 THEN RETURN S:@'&'$"+$; WHEN "+*W5 AND "+*K0 THEN RETURN I"#:@'&'$"+$; ELSE RETURN N+* " 8'* ; END CASE; END NOTA; /
Página "" de 35
Práctica #L"S*L% Varia0les1 re&istros , ursores
1! Escri+e un procedimiento que muestre el nmero de empleados y el salario mínimo, má@imo y medio del departamento de -(:G:?! Ae+e ;acerse uso de cursores implícitos, es decir utilizar SELECT !!! (=! CREATE OR REPLACE PROCEDURE F'"*"*# AS ":7$% NUMBER; 7*'7 NUMBER; 7'"'7 NUMBER; 7$'* NUMBER; + NUMBER; BEGIN SELECT NUMDE INTO + FROM DEPARTAMENTOS WHERE UPPER(NOMDE) FINANVAS ; SELECT COUNT(), MAJ(SALAR), MIN(SALAR), ROUND(A?G(SALAR), 2) INTO ":7$%, 7*'7, 7'"'7, 7$'* FROM EMPLEADOS WHERE NUMDE +;
DBMS_OUTPUT!PUT_LINE(D$*%+*7$"+ $ FINANVAS); DBMS_OUTPUT!PUT_LINE(":7$% || E7$*#); DBMS_OUTPUT!PUT_LINE(7*'7 || Y $# $ #**%' 7'7); DBMS_OUTPUT!PUT_LINE(7'"'7 || Y $# $ #**%' 7"'7); DBMS_OUTPUT!PUT_LINE(7$'* || Y $# $ #**%' 7$'); EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT!PUT_LINE(N #$ <*" $"&"+%* *+#) ; END F'"*"*#; 4
"! Escri+e un procedimiento que su+a un 10N el salario a los E2PE:A? con más de " ;iBos y que ganen menos de "000 O! Para cada empleado se mostrará por pantalla el cdigo de empleado, nom+re, salario anterior y 4nal! Dtiliza un cursor e@plícito! a transaccin no puede quedarse a medias! ?i por cualquier razn no es posi+le actualizar todos estos salarios, de+e des;acerse el tra+aBo a la situacin inicial! CREATE OR REPLACE PROCEDURE S:='%_#**%'# AS CURSOR & IS SELECT NUMEM, NOMEM, SALAR, ROID FROM EMPLEADOS WHERE NUMHI K 2 AND SALAR W 2000; #*_":$8 NUMBER; BEGIN
Página "3 de 35
FOR %$'#+% IN & LOOP UPDATE EMPLEADOS SET SALAR %$'#+%!SALAR!0 WHERE ROID %$'#+%!ROID ; #*_":$8 %$'#+%!SALAR!0; IF SQLNOTFOUND THEN
DBMS_OUTPUT!PUT_LINE(A&+:*'*&'" " &7$+**); END IF;
DBMS_OUTPUT!PUT_LINE(%$'#+%!NUMEM || || %$'#+%!NOMEM || || %$'#+%!SALAR || --K || #*_":$8);
END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END S:='%_#**%'#;
4
3! Escri+e un procedimiento que reci+a dos parámetros $nmero de departamento, ;iBos&! Ae+erá crearse un cursor e@plícito al que se le pasarán estos parámetros y que mostrará los datos de los empleados que pertenezcan al departamento y con el nmero de ;iBos indicados! :l 4nal se indicará el nmero de empleados o+tenidos! CREATE OR REPLACE PROCEDURE D+_E7$*#_H'# ( ":7$% EMPLEADOS!NUMDE%TYPE, <'# EMPLEADOS!NUMHI %TYPE ) AS CURSOR &(":7$% EMPLEADOS!NUMDE %TYPE, <'# EMPLEADOS!NUMHI %TYPE) IS SELECT NUMEM, NOMEM, NUMHI, NUMDE FROM EMPLEADOS WHERE NUMDE ":7$% AND NUMHI <'#; &"+*% NUMBER; BEGIN
&"+*% 0; FOR %$'#+% IN & (":7$%, <'#) LOOP DBMS_OUTPUT!PUT_LINE(%$'#+%!NUMEM || || %$'#+%!NOMEM || || %$'#+%!NUMHI || || %$'#+%!NUMDE); &"+*% &"+*% X ; END LOOP;
DBMS_OUTPUT!PUT_LINE(&"+*% || E7$*# =+$"'#) ; END D+_E7$*#_H'#; 4
)! Escri+e un procedimiento con un parámetro para el nom+re de empleado, que nos muestre la edad de dic;o empleado en años, meses y días!
Página ") de 35
CREATE OR REPLACE PROCEDURE E*_E7$* ("7=%$ EMPLEADOS!NOMEM %TYPE) AS
-- U+''*7# :" &:%#% $&'+ % #' $'#+'$#$ 7# $ :" $7$* -- &" $ 7'#7 "7=%$! CURSOR &("7 EMPLEADOS!NOMEM %TYPE) IS SELECT NOMEM, FECNA FROM EMPLEADOS WHERE NOMEM "7; 7$#$# * 7
NUMBER; NUMBER; NUMBER; NUMBER;
BEGIN
DBMS_OUTPUT!PUT_LINE(EMPLEADO AOS MESES DAS) ; FOR %$'#+% IN &("7=%$) LOOP 7$#$# MONTHS_BETEEN (SZSDATE, %$'#+%!FECNA); * 7$#$#42; 7 MOD (7$#$#, 2); (7 - TRUNC (7))30;-- *%+$ $&'7* $ 7 7:+''&** % 30
DBMS_OUTPUT!PUT_LINE(%$'#+%!NOMEM || || TRUNC(*) || || TRUNC(7) || || TRUNC() ) ; END LOOP;
END E*_E7$*;
4
Página "5 de 35
Práctica ! #L"S*L% #a'uetes , E2epiones
1! Aesarrolla el paquete :#(=2E=(*: cuyo cdigo fuente %iene en este tema! *rea un arc;i%o para la especi4cacin y otro para el cuerpo! #ealiza %arias prue+as para compro+ar que las llamadas a funciones y procedimiento funcionan correctamente!
"! :l paquete anterior añade una funcin llamada #E?= que reci+a dos parámetros, el di%idendo y el di%isor, y de%uel%a el resto de la di%isin!
3! :l paquete anterior añade un procedimiento sin parámetros llamado :DA: que muestre un mensaBe por pantalla de los procedimientos y funciones disponi+les en el paquete, su utilidad y forma de uso! El resultado de los 3 eBercicios anteriores quedaría de las siguiente forma> -- PAQUETE ARITMETICA [ E#$&'@'&*&'" -- PACKAGE_ARITMETICA.SL CREATE OR REPLACE PACKAGE *%'+7$+'&* IS 8$%#'" NUMBER !0; PROCEDURE 7#+%*%_'"@; PROCEDURE *>:*; (* NUMBER, FUNCTION #:7* (* NUMBER, FUNCTION %$#+* FUNCTION 7:+''&* (* NUMBER, FUNCTION '8'$ (* NUMBER, FUNCTION %$#+ (* NUMBER! END *%'+7$+'&*;
= = = = =
NUMBER) RETURN NUMBER) RETURN NUMBER) RETURN NUMBER) RETURN NUMBER) RETURN
NUMBER; NUMBER; NUMBER; NUMBER; NUMBER;
4
-- PAQUETE ARITMETICA [ C:$% -- PACKAGE_BODY_ARITMETICA.SL CREATE OR REPLACE PACKAGE BODY *%'+7$+'&* IS PROCEDURE 7#+%*%_'"@ IS BEGIN
DBMS_OUTPUT!PUT_LINE (P*;:$+$ $ $%*&'"$# *%'+7+'&*#! ?$%#'" || 8$%#'"); END 7#+%*%_'"@;
Página " de 35
PROCEDURE *>:* IS BEGIN
DBMS_OUTPUT!PUT_LINE DBMS_OUTPUT!PUT_LINE DBMS_OUTPUT!PUT_LINE DBMS_OUTPUT!PUT_LINE DBMS_OUTPUT!PUT_LINE DBMS_OUTPUT!PUT_LINE DBMS_OUTPUT!PUT_LINE DBMS_OUTPUT!PUT_LINE DBMS_OUTPUT!PUT_LINE DBMS_OUTPUT!PUT_LINE DBMS_OUTPUT!PUT_LINE DBMS_OUTPUT!PUT_LINE END *>:*;
(AZUDA DEL PAQUETE ARITM\TICA) ; () ; (P*;:$+$ &" 8*%'*# @:"&'"$# *%'+7+'&*#!) ; (L*# @:"&'"$# '#"'=$# #" #+*#) ; (- #:7* (":7, ":72), *%* #:7*% 2 "7$%#) ; (- %$#+* (":7, ":72), *%* %$#+*%) ; (- 7:+''&*(":7, ":72), *%* 7:+''&*%) ; (- '8'$ (":7, ":72), *%* '8''%) ; (- %$#+ (":7, ":72), *%* %$#+ '8'#'") ; (A$7#, $'#+$" 2 %&$'7'$"+#); (- 7#+%*%_'"@, *%* 8$% 8$%#'") ; (- *>:*, *%* 7#+%*% $#+* *>:*) ;
FUNCTION #:7* BEGIN RETURN (*X=); END #:7*;
(* NUMBER, = NUMBER) RETURN NUMBER IS
FUNCTION %$#+* BEGIN RETURN (*-=); END %$#+*;
(* NUMBER, = NUMBER) RETURN NUMBER IS
FUNCTION 7:+''&* (* NUMBER, = NUMBER) RETURN NUMBER IS BEGIN RETURN (*=); END 7:+''&*; FUNCTION '8'$ BEGIN RETURN (*4=); END '8'$;
(* NUMBER, = NUMBER) RETURN NUMBER IS
FUNCTION %$#+ (* NUMBER, = NUMBER) RETURN NUMBER IS BEGIN RETURN MOD(*,=); END %$#+; END *%'+7$+'&*;
4
-- P%:$=*# BEGIN
ARITMETICA!MOSTRAR_INFO; ARITMETICA!AZUDA; END;
4
Página "6 de 35
SELECT SELECT SELECT SELECT SELECT
ARITMETICA!SUMA (.,3) FROM DUAL; ARITMETICA!RESTA (.,3) FROM DUAL; ARITMETICA!MULTIPLICA(.,3) FROM DUAL; ARITMETICA!DI?IDE (.,3) FROM DUAL; ARITMETICA!RESTO (.,3) FROM DUAL;
)! Aesarrolla el paquete IE?=(! En un principio tendremos los procedimientos para gestionar los departamentos! Aado el arc;i%o de especi4cacin mostrado más a+aBo crea el arc;i%o para el cuerpo! #ealiza %arias prue+as para compro+ar que las llamadas a funciones y procedimientos funcionan correctamente! -- PAQUETE GESTION [ E#$&'@'&*&'" -- PACKAGE_GESTION.SL CREATE OR REPLACE PACKAGE GESTION AS PROCEDURE CREAR_DEP FUNCTION NUM_DEP PROCEDURE MOSTRAR_DEP PROCEDURE BORRAR_DEP PROCEDURE MODIFICAR_DEP END GESTION;
("7=%$ VARCHAR2, %$#::$#+ NUMBER); ("7=%$ VARCHAR2) RETURN NUMBER; (":7$% NUMBER); (":7$% NUMBER); (":7$% NUMBER, %$#::$#+ NUMBER);
4
-- PAQUETE GESTION [ C:$% -- PACKAGE_BODY_GESTION.SL CREATE OR REPLACE PACKAGE BODY GESTION AS PROCEDURE CREAR_DEP ("7=%$ VARCHAR2, %$#::$#+ NUMBER) AS ":7_$ NUMBER(3); BEGIN SELECT NUMDE INTO ":7_$ FROM DEPARTAMENTOS WHERE NOMDE"7=%$;
-- S' $'#+$, " #$ %:&$ $&$&'"! M#+%*7# $"+"&$# $ -- #':'$"+$ 7$"#*$! DBMS_OUTPUT!PUT_LINE (D$*%+*7$"+ || "7=%$ || " &%$*!); DBMS_OUTPUT!PUT_LINE (Z* $'#+$ :" $*%+*7$"+ &" '&< "7=%$!); -- S' " $'#+$ $ "7=%$ $ $*%+*7$"+ #$ %:&$ :"* $&$&'" -- * &:* *%8$&<*7# *%* '"+%:&'% # *+#! EXCEPTION WHEN NO_DATA_FOUND THEN SELECT MAJ(NUMDE)X0 INTO ":7_$ FROM DEPARTAMENTOS; INSERT INTO DEPARTAMENTOS (":7$, "7$, %$#:) VALUES (":7_$, "7=%$, %$#::$#+) ; END CREAR_DEP;
Página "9 de 35
FUNCTION NUM_DEP ("7=%$ VARCHAR2) RETURN NUMBER AS ":7_$ NUMBER; BEGIN SELECT NUMDE INTO ":7_$ FROM DEPARTAMENTOS WHERE NOMDE"7=%$; RETURN ":7_$; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN -; END NUM_DEP;
PROCEDURE MOSTRAR_DEP (":7$% NUMBER) AS $7$* NUMBER(3); "7=%$ DEPARTAMENTOS!NOMDE%TYPE; %$#: DEPARTAMENTOS!PRESU%TYPE; BEGIN SELECT NOMDE, PRESU INTO "7=%$, %$#: FROM DEPARTAMENTOS WHERE NUMDE ":7$% ; DBMS_OUTPUT!PUT_LINE(N:7! D+ || ":7$%|| - N7=%$ D+ || "7=%$ || - P%$#::$#+ || %$#: ); EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT!PUT_LINE (N $'#+$ $#+$ $*%+*7$"+!); END MOSTRAR_DEP;
PROCEDURE BORRAR_DEP (":7$% NUMBER) AS $7$* NUMBER(3); BEGIN UPDATE EMPLEADOS SET NUMDE NULL WHERE NUMDE ":7$%;
DBMS_OUTPUT!PUT_LINE(TO_CHAR (SQLROCOUNT)|| $7$*# *@$&+*#!); DELETE DEPARTAMENTOS WHERE NUMDE ":7$%; DBMS_OUTPUT!PUT_LINE(TO_CHAR (SQLROCOUNT)|| $*%+*7$"+# =%%*#!); END BORRAR_DEP; PROCEDURE MODIFICAR_DEP (":7$% NUMBER, %$#::$#+ NUMBER) AS BEGIN UPDATE DEPARTAMENTOS SET PRESU %$#::$#+ WHERE NUMDE ":7$%; CASE SQLROCOUNT WHEN 0 THEN DBMS_OUTPUT!PUT_LINE(N $'#+$ $ $*%+*7$"+ || ":7$%); WHEN THEN DBMS_OUTPUT!PUT_LINE(A&+:*'*&'" %$*'**!) ; DBMS_OUTPUT!PUT_LINE(A %*% &:%%'!); ELSE END CASE;
Página "7 de 35
END MODIFICAR_DEP; END GESTION;
4
-- P%:$=*# DECLARE ":7 NUMBER; BEGIN
GESTION!CREAR_DEP (MARETING, 0) ; GESTION!CREAR_DEP (IXD, 20); ":7 GESTION!NUM_DEP (MARETING); GESTION!MOSTRAR_DEP ( ":7 ); ":7 GESTION!NUM_DEP (IXD); GESTION!MOSTRAR_DEP ( ":7 ); GESTION!MODIFICAR_DEP (GESTION!NUM_DEP (MARETING), 2) ; GESTION!MODIFICAR_DEP (GESTION!NUM_DEP (IXD), 22) ; GESTION!MOSTRAR_DEP ( GESTION!NUM_DEP (MARETING)); GESTION!MOSTRAR_DEP ( GESTION!NUM_DEP (IXD)) ; GESTION!BORRAR_DEP (GESTION!NUM_DEP (MARETING)); GESTION!BORRAR_DEP (GESTION!NUM_DEP (IXD)) ; END;
4
Página 30 de 35
Práctica " #L"S*L% Tri&&ers , E2epiones
Pre%iamente de+eremos crear una ta+la :DA(=#(:LE2PE:A? para registrar los e%entos a auditar que ocurran so+re la ta+la E2PE:A?! CREATE TABLE AUDITORIA_EMPLEADOS ($#&%'&'" VARCHAR2(200));
tam+in crearemos una %ista ?EAELAEP:#=:2E=? acerca de los departamentos y su localizacin! CREATE VIEW SEDE_DEPARTAMENTOS AS SELECT C!NUMCE, C!NOMCE, C!DIRCE,
D!NUMDE, D!NOMDE, D!PRESU, D!DIREC, D!TIDIR, D!DEPDE FROM CENTROS C "OIN DEPARTAMENTOS D ON C!NUMCED!NUMCE;
=am+in insertaremos en la ta+la AEP:#=:2E=? uno llamado =E2P donde serán mo%idos los empleados cuyo departamento desaparezca! INSERT INTO DEPARTAMENTOS VALUES (0, 0,
260, F, 0, 00, TEMP) ;
1! *rea un trigger que, cada %ez que se inserte o elimine un empleado, registre una entrada en la ta+la :DA(=#(:LE2PE:A? con la fec;a del suceso, nmero y nom+re de empleado, así como el tipo de operacin realizada $(?E#*(Q o E(2(:*(Q&! CREATE OR REPLACE TRIGGER I"#$%&'"_$'7'"*&'"_$7$* AFTER INSERT OR DELETE ON EMPLEADOS FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO AUDITORIA_EMPLEADOS VALUES(TO_CHAR(SZSDATE,DD4MM4ZZZZ HHMISS) || - INSERCI]N - || "$^!NUMEM || || "$^!NOMEM ); ELSIF DELETING THEN INSERT INTO AUDITORIA_EMPLEADOS VALUES(TO_CHAR(SZSDATE,DD4MM4ZZZZ HHMISS) || - ELIMINACI]N - || !NUMEM || || !NOMEM );
Página 31 de 35
END IF; END I"#$%&'"_$'7'"*&'"_$7$*;
"! *rea un trigger que, cada %ez que se modi4quen datos de un empleado, registre una entrada en la ta+la :DA(=#(:LE2PE:A? con la fec;a del suceso, %alor antiguo y %alor nue%o de cada campo, así como el tipo de operacin realizada $en este caso 2A(-(*:*(Q&! CREATE OR REPLACE TRIGGER M'@'&*&'"_$7$* AFTER UPDATE ON EMPLEADOS FOR EACH ROW DECLARE
&*$"* VARCHAR2(200); BEGIN
&*$"* TO_CHAR(SZSDATE,DD4MM4ZZZZ HHMISS) || - MODIFICACI]N - || "$^!NUMEM || || "$^!NOMEM || - ; IF UPDATING(NUMEM) THEN &*$"* &*$"* || N:7! $7$* || !NUMEM || --K || "$^!NUMEM; END IF; IF UPDATING(NOMEM) THEN &*$"* &*$"* || , N7=%$ || !NOMEM || --K || "$^!NOMEM || , ; END IF; IF UPDATING(SALAR) THEN &*$"* &*$"* || , S**%' || !SALAR || --K || "$^!SALAR || , ; END IF; IF UPDATING(COMIS) THEN &*$"* &*$"* || , C7'#'" || !COMIS || --K || "$^!COMIS || , ; END IF; IF UPDATING(NUMHI) THEN &*$"* &*$"* || , H'# || !NUMHI || --K || "$^!NUMHI || , ; END IF; IF UPDATING(EJTEL) THEN &*$"* &*$"* || , E+$"#'" || !EJTEL || --K || "$^!EJTEL || , ;
Página 3" de 35
END IF; IF UPDATING(NUMDE) THEN &*$"* &*$"* || , N:7! D$*%+*7$"+ || !NUMDE || --K || "$^!NUMDE || , ; END IF; INSERT INTO AUDITORIA_EMPLEADOS VALUES(&*$"*); END M'@'&*&'"_$7$*;
3! *rea un trigger para que registre en la ta+la :DA(=#(:LE2PE:A? las su+idas de salarios superiores al 5N! CREATE OR REPLACE TRIGGER S:='*_#**%' AFTER UPDATE OF SALAR ON EMPLEADOS FOR EACH ROW BEGIN IF ("$^!SALAR - !SALAR) K (!SALAR 0!05) THEN INSERT INTO AUDITORIA_EMPLEADOS VALUES(TO_CHAR(SZSDATE,DD4MM4ZZZZ HHMISS) || - MODIFICACI]N SALARIO - || !NUMEM || || !NOMEM || - || !SALAR || --K || "$^!SALAR ) ; END IF; END S:='*_#**%';
)! Aeseamos operar so+re los datos de los departamentos y los centros donde se ;allan! Para ello ;aremos uso de la %ista ?EAELAEP:#=:2E=? creada anteriormente! :l tratarse de una %ista que in%olucra más de una ta+la, necesitaremos crear un trigger de sustitucin para gestionar las operaciones de actualizacin de la informacin! *rea el trigger necesario para realizar inserciones, eliminaciones y modi4caciones en la %ista anterior! CREATE OR REPLACE TRIGGER A&+:*'*&'"_$*%+*7$"+ INSTEAD OF DELETE OR INSERT OR UPDATE ON SEDE_DEPARTAMENTOS FOR EACH ROW DECLARE
&*"+'*
NUMBER(3);
BEGIN
-- M'@'&*7# *+#
Página 33 de 35
IF UPDATING THEN UPDATE CENTROS SET NOMCE "$^!NOMCE, DIRCE "$^!DIRCE WHERE NUMCE !NUMCE; UPDATE DEPARTAMENTOS SET NUMCE "$^!NUMCE, NOMDE "$^!NOMDE, DIREC "$^!DIREC,
TIDIR "$^!TIDIR, PRESU "$^!PRESU, DEPDE "$^!DEPDE WHERE NUMCE !NUMCE AND NUMDE !NUMDE ; -- B%%*7# *+# ELSIF DELETING THEN
-- S' $ $*%+*7$"+ +'$"$ $7$*# -- # 78$7# * $*%+*7$"+ TEMP, :$ =%%*7# $ *%+*7$"+ -- S' $ &$"+% +'$"$ $*%+*7$"+#, " =%%*7# $ &$"+%! SELECT COUNT(NUMDE) INTO &*"+'* FROM EMPLEADOS WHERE NUMDE !NUMDE ; IF &*"+'* K 0 THEN UPDATE EMPLEADOS SET NUMDE 0 WHERE NUMDE !NUMDE; END IF; DELETE DEPARTAMENTOS WHERE NUMDE !NUMDE; SELECT COUNT(NUMCE) INTO &*"+'* FROM DEPARTAMENTOS WHERE NUMCE !NUMCE ; IF &*"+'* 0 THEN DELETE CENTROS WHERE NUMCE !NUMCE; END IF;
-- I"#$%+*7# *+# ELSIF INSERTING THEN
-- S' $ &$"+% $ $*%+*7$"+ " $'#+$ *7# $ *+*, -- $" +% &*# *&+:*'*7# # *+# SELECT COUNT(NUMCE) INTO &*"+'* FROM CENTROS WHERE NUMCE "$^!NUMCE ; IF &*"+'* 0 THEN INSERT INTO CENTROS VALUES("$^!NUMCE, "$^!NOMCE, "$^!DIRCE) ; ELSE UPDATE CENTROS SET NOMCE "$^!NOMCE, DIRCE "$^!DIRCE WHERE NUMCE "$^!NUMCE; END IF; SELECT COUNT(NUMDE) INTO &*"+'* FROM DEPARTAMENTOS WHERE NUMDE "$^!NUMDE ; IF &*"+'* 0 THEN INSERT INTO DEPARTAMENTOS VALUES("$^!NUMDE, "$^!NUMCE, "$^!DIREC, "$^!TIDIR, "$^!PRESU, "$^!DEPDE, "$^!NOMDE) ; ELSE UPDATE DEPARTAMENTOS SET NUMCE "$^!NUMCE, DIREC "$^!DIREC, TIDIR "$^!TIDIR,
PRESU "$^!PRESU, DEPDE "$^!DEPDE, NOMDE "$^!NOMDE WHERE NUMCE "$^!NUMCE;
Página 3) de 35