Advertisement
JUNIORCEDE1

Untitled

Jul 4th, 2017
213
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*==============================================================*/
  2. /* DBMS name:      ORACLE Version 11g                           */
  3. /* Created on:     4/7/2017 12:38:49                            */
  4. /*==============================================================*/
  5.  
  6.  
  7. ALTER TABLE ELENCO
  8.    DROP CONSTRAINT FK_ELENCO_FK_ELENCO_ACTOR;
  9.  
  10. ALTER TABLE ELENCO
  11.    DROP CONSTRAINT FK_ELENCO_FK_ELENCO_PELICULA;
  12.  
  13. DROP TABLE ACTOR CASCADE CONSTRAINTS;
  14.  
  15. DROP INDEX FK_ELENCO_ACTOR_FK;
  16.  
  17. DROP INDEX FK_ELENCO_PELICULA_FK;
  18.  
  19. DROP TABLE ELENCO CASCADE CONSTRAINTS;
  20.  
  21. DROP TABLE PELICULA CASCADE CONSTRAINTS;
  22.  
  23. /*==============================================================*/
  24. /* Table: ACTOR                                                 */
  25. /*==============================================================*/
  26.  
  27. CREATE TABLE ACTOR
  28. (
  29.    ID_ACTOR             VARCHAR2(10)         NOT NULL,
  30.    NOMBRE_ACTOR         VARCHAR2(40),
  31.    GENERO_ACTOR         VARCHAR2(20),
  32.    ANO_NACIMIENTO       INTEGER,
  33.    TOTAL_PELICULAS_FILMADAS INTEGER,
  34.    CONSTRAINT PK_ACTOR PRIMARY KEY (ID_ACTOR)
  35. );
  36.  
  37. /*==============================================================*/
  38. /* Table: ELENCO                                                */
  39. /*==============================================================*/
  40.  
  41. CREATE TABLE ELENCO
  42. (
  43.    ID_ACTOR             VARCHAR2(10),
  44.    ID_PELICULA          VARCHAR2(10),
  45.    EDADACTOR_CUANDOFILMAPELICULA INTEGER,
  46.    PAGOALACTOR_PORLAPELICULA NUMBER(6,2)
  47. );
  48.  
  49. /*==============================================================*/
  50. /* Index: FK_ELENCO_PELICULA_FK                                 */
  51. /*==============================================================*/
  52. CREATE INDEX FK_ELENCO_PELICULA_FK ON ELENCO (
  53.    ID_PELICULA ASC
  54. );
  55.  
  56. /*==============================================================*/
  57. /* Index: FK_ELENCO_ACTOR_FK                                    */
  58. /*==============================================================*/
  59. CREATE INDEX FK_ELENCO_ACTOR_FK ON ELENCO (
  60.    ID_ACTOR ASC
  61. );
  62.  
  63. /*==============================================================*/
  64. /* Table: PELICULA                                              */
  65. /*==============================================================*/
  66.  
  67. CREATE TABLE PELICULA
  68. (
  69.    ID_PELICULA          VARCHAR2(10)         NOT NULL,
  70.    NOMBRE_PELICULA      VARCHAR2(40),
  71.    DURACION_PELICULAS_MINUTOS INTEGER,
  72.    TOTAL_ACTORES        INTEGER,
  73.    ANO_PELICULA         INTEGER,
  74.    LUGAR_FLMACION       VARCHAR2(40),
  75.    TOTAL_HOMBRES_PELICULA SMALLINT,
  76.    TOTAL_MUJERES_PELICULA SMALLINT,
  77.    CONSTRAINT PK_PELICULA PRIMARY KEY (ID_PELICULA)
  78. );
  79.  
  80. ALTER TABLE ELENCO
  81.    ADD CONSTRAINT FK_ELENCO_FK_ELENCO_ACTOR foreign KEY (ID_ACTOR)
  82.       references ACTOR (ID_ACTOR);
  83.  
  84. ALTER TABLE ELENCO
  85.    ADD CONSTRAINT FK_ELENCO_FK_ELENCO_PELICULA foreign KEY (ID_PELICULA)
  86.       references PELICULA (ID_PELICULA);
  87.      
  88. CREATE OR REPLACE TRIGGER TR_B_IU_GENERO
  89. BEFORE
  90. INSERT OR UPDATE OF GENERO_ACTOR ON ACTOR
  91. FOR EACH ROW
  92. DECLARE
  93. BEGIN
  94.     IF NOT UPPER(:NEW.genero_actor) = 'MASCULINO' AND NOT UPPER(:NEW.genero_actor)='FEMENINO' THEN
  95.      RAISE_APPLICATION_ERROR(-20001,'El campo generoactor solo admite "MASCULINO" o "FEMENINO" en mayusculas o minusculas ');
  96.     ELSE
  97.       :NEW.genero_actor := UPPER(:NEW.genero_actor);
  98.     END IF;
  99. END;
  100.  
  101. CREATE OR REPLACE TRIGGER TR_B_IU_EDAD
  102. before
  103. INSERT OR UPDATE ON ELENCO
  104. FOR EACH ROW
  105. DECLARE
  106. ano_na actor.ano_nacimiento%TYPE;
  107. ano_peli pelicula.ano_pelicula%TYPE;
  108. BEGIN
  109.     SELECT ano_nacimiento INTO ano_na FROM actor WHERE ID_ACTOR = :NEW.id_actor;
  110.     SELECT ano_pelicula INTO ano_peli FROM pelicula WHERE ID_PELICULA = :NEW.id_pelicula;
  111.     :NEW.EDADACTOR_CUANDOFILMAPELICULA := ano_peli-ano_na;
  112. END;
  113.  
  114. CREATE OR REPLACE TRIGGER TR_B_IU_H_M
  115. before
  116. INSERT OR UPDATE ON ELENCO
  117. FOR EACH ROW
  118. DECLARE
  119. nmujeres SMALLINT :=0;
  120. nhombres SMALLINT :=0;
  121. genero VARCHAR2(20);
  122. BEGIN
  123.     SELECT GENERO_ACTOR INTO genero FROM ACTOR WHERE ID_ACTOR = :NEW.id_actor;
  124.     IF genero = 'MASCULINO'  THEN
  125.      nhombres := nhombres+1;
  126.     ELSIF genero = 'FEMENINO' THEN
  127.      nmujeres := nmujeres+1;
  128.     END IF;
  129.     FOR i IN (SELECT * FROM ELENCO )
  130.     LOOP
  131.         IF i.id_pelicula = :NEW.id_pelicula THEN
  132.             SELECT GENERO_ACTOR INTO genero FROM ACTOR WHERE ID_ACTOR = i.id_actor AND NOT ID_ACTOR=:NEW.id_actor;
  133.             IF genero = 'MASCULINO'  THEN
  134.              nhombres := nhombres+1;
  135.             ELSIF genero = 'FEMENINO' THEN
  136.              nmujeres := nmujeres+1;
  137.             END IF;
  138.         END IF;
  139.     END LOOP;
  140.     UPDATE PELICULA SET TOTAL_HOMBRES_PELICULA = nhombres, TOTAL_MUJERES_PELICULA=nmujeres, TOTAL_ACTORES = nhombres+nmujeres WHERE ID_PELICULA = :NEW.id_pelicula;
  141. END;
  142.  
  143. INSERT INTO actor VALUES(1,'AA','MASCULINO',1990,0);
  144. INSERT INTO actor VALUES(2,'BB','MASCULINO',1940,0);
  145. INSERT INTO actor VALUES(3,'CC','femenino',1930,0);
  146. INSERT INTO actor VALUES(4,'DD','femenino',1935,0);
  147. SELECT*FROM actor;
  148. INSERT INTO pelicula VALUES('1','lolo',130,3,2015,'manta',NULL,NULL);
  149. INSERT INTO pelicula VALUES('2','lalo',130,NULL,2014,'manta',NULL,NULL);
  150. SELECT * FROM pelicula;
  151. INSERT INTO elenco VALUES(1,1,NULL,10);
  152. INSERT INTO elenco VALUES(2,1,NULL,10);
  153. INSERT INTO elenco VALUES(3,1,NULL,10);
  154. INSERT INTO elenco VALUES(4,1,NULL,10);
  155. INSERT INTO elenco VALUES(3,2,NULL,10);
  156. INSERT INTO elenco VALUES(4,2,NULL,10);
  157. SELECT * FROM elenco;
  158. SELECT * FROM pelicula;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement