Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*==============================================================*/
- /* DBMS name: ORACLE Version 11g */
- /* Created on: 4/7/2017 12:38:49 */
- /*==============================================================*/
- ALTER TABLE ELENCO
- DROP CONSTRAINT FK_ELENCO_FK_ELENCO_ACTOR;
- ALTER TABLE ELENCO
- DROP CONSTRAINT FK_ELENCO_FK_ELENCO_PELICULA;
- DROP TABLE ACTOR CASCADE CONSTRAINTS;
- DROP INDEX FK_ELENCO_ACTOR_FK;
- DROP INDEX FK_ELENCO_PELICULA_FK;
- DROP TABLE ELENCO CASCADE CONSTRAINTS;
- DROP TABLE PELICULA CASCADE CONSTRAINTS;
- /*==============================================================*/
- /* Table: ACTOR */
- /*==============================================================*/
- CREATE TABLE ACTOR
- (
- ID_ACTOR VARCHAR2(10) NOT NULL,
- NOMBRE_ACTOR VARCHAR2(40),
- GENERO_ACTOR VARCHAR2(20),
- ANO_NACIMIENTO INTEGER,
- TOTAL_PELICULAS_FILMADAS INTEGER,
- CONSTRAINT PK_ACTOR PRIMARY KEY (ID_ACTOR)
- );
- /*==============================================================*/
- /* Table: ELENCO */
- /*==============================================================*/
- CREATE TABLE ELENCO
- (
- ID_ACTOR VARCHAR2(10),
- ID_PELICULA VARCHAR2(10),
- EDADACTOR_CUANDOFILMAPELICULA INTEGER,
- PAGOALACTOR_PORLAPELICULA NUMBER(6,2)
- );
- /*==============================================================*/
- /* Index: FK_ELENCO_PELICULA_FK */
- /*==============================================================*/
- CREATE INDEX FK_ELENCO_PELICULA_FK ON ELENCO (
- ID_PELICULA ASC
- );
- /*==============================================================*/
- /* Index: FK_ELENCO_ACTOR_FK */
- /*==============================================================*/
- CREATE INDEX FK_ELENCO_ACTOR_FK ON ELENCO (
- ID_ACTOR ASC
- );
- /*==============================================================*/
- /* Table: PELICULA */
- /*==============================================================*/
- CREATE TABLE PELICULA
- (
- ID_PELICULA VARCHAR2(10) NOT NULL,
- NOMBRE_PELICULA VARCHAR2(40),
- DURACION_PELICULAS_MINUTOS INTEGER,
- TOTAL_ACTORES INTEGER,
- ANO_PELICULA INTEGER,
- LUGAR_FLMACION VARCHAR2(40),
- TOTAL_HOMBRES_PELICULA SMALLINT,
- TOTAL_MUJERES_PELICULA SMALLINT,
- CONSTRAINT PK_PELICULA PRIMARY KEY (ID_PELICULA)
- );
- ALTER TABLE ELENCO
- ADD CONSTRAINT FK_ELENCO_FK_ELENCO_ACTOR foreign KEY (ID_ACTOR)
- references ACTOR (ID_ACTOR);
- ALTER TABLE ELENCO
- ADD CONSTRAINT FK_ELENCO_FK_ELENCO_PELICULA foreign KEY (ID_PELICULA)
- references PELICULA (ID_PELICULA);
- CREATE OR REPLACE TRIGGER TR_B_IU_GENERO
- BEFORE
- INSERT OR UPDATE OF GENERO_ACTOR ON ACTOR
- FOR EACH ROW
- DECLARE
- BEGIN
- IF NOT UPPER(:NEW.genero_actor) = 'MASCULINO' AND NOT UPPER(:NEW.genero_actor)='FEMENINO' THEN
- RAISE_APPLICATION_ERROR(-20001,'El campo generoactor solo admite "MASCULINO" o "FEMENINO" en mayusculas o minusculas ');
- ELSE
- :NEW.genero_actor := UPPER(:NEW.genero_actor);
- END IF;
- END;
- CREATE OR REPLACE TRIGGER TR_B_IU_EDAD
- before
- INSERT OR UPDATE ON ELENCO
- FOR EACH ROW
- DECLARE
- ano_na actor.ano_nacimiento%TYPE;
- ano_peli pelicula.ano_pelicula%TYPE;
- BEGIN
- SELECT ano_nacimiento INTO ano_na FROM actor WHERE ID_ACTOR = :NEW.id_actor;
- SELECT ano_pelicula INTO ano_peli FROM pelicula WHERE ID_PELICULA = :NEW.id_pelicula;
- :NEW.EDADACTOR_CUANDOFILMAPELICULA := ano_peli-ano_na;
- END;
- CREATE OR REPLACE TRIGGER TR_B_IU_H_M
- before
- INSERT OR UPDATE ON ELENCO
- FOR EACH ROW
- DECLARE
- nmujeres SMALLINT :=0;
- nhombres SMALLINT :=0;
- genero VARCHAR2(20);
- BEGIN
- SELECT GENERO_ACTOR INTO genero FROM ACTOR WHERE ID_ACTOR = :NEW.id_actor;
- IF genero = 'MASCULINO' THEN
- nhombres := nhombres+1;
- ELSIF genero = 'FEMENINO' THEN
- nmujeres := nmujeres+1;
- END IF;
- FOR i IN (SELECT * FROM ELENCO )
- LOOP
- IF i.id_pelicula = :NEW.id_pelicula THEN
- SELECT GENERO_ACTOR INTO genero FROM ACTOR WHERE ID_ACTOR = i.id_actor AND NOT ID_ACTOR=:NEW.id_actor;
- IF genero = 'MASCULINO' THEN
- nhombres := nhombres+1;
- ELSIF genero = 'FEMENINO' THEN
- nmujeres := nmujeres+1;
- END IF;
- END IF;
- END LOOP;
- UPDATE PELICULA SET TOTAL_HOMBRES_PELICULA = nhombres, TOTAL_MUJERES_PELICULA=nmujeres, TOTAL_ACTORES = nhombres+nmujeres WHERE ID_PELICULA = :NEW.id_pelicula;
- END;
- INSERT INTO actor VALUES(1,'AA','MASCULINO',1990,0);
- INSERT INTO actor VALUES(2,'BB','MASCULINO',1940,0);
- INSERT INTO actor VALUES(3,'CC','femenino',1930,0);
- INSERT INTO actor VALUES(4,'DD','femenino',1935,0);
- SELECT*FROM actor;
- INSERT INTO pelicula VALUES('1','lolo',130,3,2015,'manta',NULL,NULL);
- INSERT INTO pelicula VALUES('2','lalo',130,NULL,2014,'manta',NULL,NULL);
- SELECT * FROM pelicula;
- INSERT INTO elenco VALUES(1,1,NULL,10);
- INSERT INTO elenco VALUES(2,1,NULL,10);
- INSERT INTO elenco VALUES(3,1,NULL,10);
- INSERT INTO elenco VALUES(4,1,NULL,10);
- INSERT INTO elenco VALUES(3,2,NULL,10);
- INSERT INTO elenco VALUES(4,2,NULL,10);
- SELECT * FROM elenco;
- SELECT * FROM pelicula;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement