LABORATOR BD

--------------------------------------------------
DROP TABLE CADOU CASCADE CONSTRAINTS;
CREATE TABLE CADOU (
    idCadou NUMBER(5) PRIMARY KEY,
    denumire VARCHAR2(15) NOT NULL,
    descriere VARCHAR2(50) NOT NULL,
    tip VARCHAR2(1)
)

DROP SEQUENCE cadou_seq;
CREATE SEQUENCE cadou_seq START WITH 1;

CREATE OR REPLACE TRIGGER cadou_id_trigger
BEFORE INSERT ON CADOU
FOR EACH ROW
  BEGIN
    IF :NEW.idCadou IS NULL THEN
      :NEW.idCadou := cadou_seq.NEXTVAL;
    END IF;
  END;
/

INSERT INTO CADOU (denumire, descriere, tip) VALUES('Masinuta', 'Masinuta este albastra', 'M');
INSERT INTO CADOU (denumire, descriere, tip) VALUES('Masinuta', 'Masinuta este rosie', 'F');
INSERT INTO CADOU (denumire, descriere, tip) VALUES('Urs', 'Ursul este mov', 'M');
INSERT INTO CADOU (denumire, descriere, tip) VALUES('Urs', 'Ursul este rosu', 'F');
INSERT INTO CADOU (idCadou, denumire, descriere, tip) VALUES(1,'Urs', 'Ursul este rosu', 'F');
INSERT INTO CADOU (idCadou, denumire, descriere, tip) VALUES(5,'Urs', 'Ursul este galben', 'F');
SELECT * FROM CADOU;

--------------------------------------------------
CREATE TABLE COPIL (
    idCopil NUMBER(5) PRIMARY KEY,
    nume VARCHAR2(30),
    prenume VARCHAR2(30),
    judet VARCHAR(2)
)

DROP SEQUENCE copil_seq;
CREATE SEQUENCE copil_seq START WITH 1;

CREATE OR REPLACE TRIGGER copil_id_trigger
BEFORE INSERT ON COPIL
FOR EACH ROW
  BEGIN
    IF :NEW.idCopil IS NULL THEN
      :NEW.idCopil := copil_seq.NEXTVAL;
    END IF;
  END;
/

INSERT INTO COPIL (nume, prenume, judet) VALUES ('Vrabie', 'Cosmin', 'IS');
INSERT INTO COPIL (nume, prenume, judet) VALUES ('Vrabie2', 'Cosmin2', 'BT');
INSERT INTO COPIL (nume, prenume, judet) VALUES ('Vrabie3', 'Cosmin3', 'IS');
INSERT INTO COPIL (nume, prenume, judet) VALUES ('Vrabie4', 'Cosmin4', 'GL');
INSERT INTO COPIL (idCopil, nume, prenume, judet) VALUES (4, 'Vrabie5', 'Cosmin5', 'IS');
INSERT INTO COPIL (idCopil, nume, prenume, judet) VALUES (5, 'Vrabie5', 'Cosmin5', 'IS');


SELECT * FROM COPIL;

--------------------------------------------------
CREATE TABLE CORESPONDENTA_CADOU (
    idCorespondenta NUMBER(5) PRIMARY KEY,
    idCadou NUMBER(5) NOT NULL,
    idCopil NUMBER(5) NOT NULL,
    CONSTRAINT fk_cadou
      FOREIGN KEY (idCadou)
      REFERENCES CADOU(idCadou),
    CONSTRAINT fk_copil
      FOREIGN KEY (idCopil)
      REFERENCES COPIL(idCopil)
)


DROP SEQUENCE corespondenta_cadou_seq;
CREATE SEQUENCE corespondenta_cadou_seq START WITH 1;

CREATE OR REPLACE TRIGGER corespondenta_cadou_id_trigger
BEFORE INSERT ON CORESPONDENTA_CADOU
FOR EACH ROW
  BEGIN
    IF :NEW.idCorespondenta IS NULL THEN
      :NEW.idCorespondenta := corespondenta_cadou_seq.NEXTVAL;
    END IF;
  END;
/

--------------------------------------------------

CREATE OR REPLACE PROCEDURE alocare_cadou IS
idCopil NUMBER(5);
BEGIN
  FOR cadou IN (SELECT * FROM cadou)
  LOOP
    SELECT idCopil INTO idCopil FROM (SELECT idCopil FROM COPIL ORDER BY DBMS_RANDOM.VALUE)
          WHERE ROWNUM = 1;
    INSERT INTO CORESPONDENTA_CADOU (idCadou, idCopil)
      VALUES (cadou.idCadou, idCopil);
  END LOOP;
END;
/

SELECT * FROM CORESPONDENTA_CADOU;

---------------------------------------
BEGIN
ALOCARE_CADOU();
END;
/