SHARE
TWEET

script creare psgbd

robertzanceanu Apr 2nd, 2019 116 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE useri CASCADE CONSTRAINTS
  2. /
  3. CREATE TABLE useri(
  4.       id INT NOT NULL PRIMARY KEY,
  5.       email VARCHAR2(60) NOT NULL,
  6.       password VARCHAR2(30) NOT NULL,
  7.       doctor NUMBER(1) NOT NULL,
  8.       stapan NUMBER(1) NOT NULL
  9. )
  10. /
  11. DROP TABLE doctori CASCADE CONSTRAINTS
  12. /
  13. CREATE TABLE doctori(
  14.       id INT NOT NULL PRIMARY KEY,
  15.       nume VARCHAR2(15) NOT NULL,
  16.       prenume VARCHAR2(30) NOT NULL,
  17.       varsta NUMBER(2) NOT NULL,
  18.       email VARCHAR2(60) NOT NULL,
  19.       numar_telefon VARCHAR2(10) NOT NULL
  20. )
  21. /
  22. DROP TABLE doctoriuseri CASCADE CONSTRAINTS
  23. /
  24. CREATE TABLE doctoriuseri(
  25.     id INT NOT NULL PRIMARY KEY,
  26.     id_user INT NOT NULL,
  27.     id_doctor INT NOT NULL,
  28.     CONSTRAINT fk_doctoriuseri_id_user FOREIGN KEY (id_user) REFERENCES useri(id),
  29.     CONSTRAINT fk_doctoriuseri_id_doctor FOREIGN KEY (id_doctor) REFERENCES doctori(id)
  30.     )
  31. /
  32. SET SERVEROUTPUT ON;
  33. DECLARE
  34.     TYPE varr IS VARRAY(1000) OF VARCHAR2(255);
  35.     lista_nume varr := varr('Alboaie','Ababei','Adamache','Aioane','Alamaie','Alb','Amarghioalei','Apetrei','Ardel','Avasalcei','Baba','Bac','Bacovia','Bicut','Bej','Bejan','Bomba','Bubuila','Buc','Caba','Ceaca','Cerb','Chirila','Chisalita','Ciuciu','Ciuhu','Cubanit','Cucu','Cucila','Daban','Damb','Deac','Dia','Diaconu','Divan','Dascalu','Divis','Dezi','Devesel','Eacobescu','Ebu','Edelceanu','Efrim','Eftode','Emag','Enache','Epurescu','Eufrosie','Eva','Fabricantu','Fachir','Fagaras','Fier','Filat','Feldes','Fraier','Fuca','Fulger','Frasin','Galati','Geaca','Gheara','Gheata','Geaman','Geamanu','Gheorghita','Gherca','Ghergheloaia','Ghergheluca','Haba','Hada','Habuc','Hedea','Hedeus','Heia','Helici','Hert','Hertug','Hritac','Iacata','Iaciu','Ibanescu','Ichim','Idiceanu','Iefta','Ilian','Inurean','Ioia','Ioje','Jaba','Jac','Jacatoa','Jbanca','Jdeica','Jder','Jdirea','Jian','Jneapan','Joaca-bine','Joaca-rau','Keseru','Kilcos','Kiriac','Kilogram','Kogalniceanu','Komlosi','Kosat','Kovaci','Kretulescu','Kirita','Labici','Laciu','Lacrima','Lae','Lambeanu','Leac','Lia','Lezeu','Lezeriuc','Levoi','Mac','Macafei','Mandra','Meca','Mecher','Marina','Meciu','Meci','Mecu','Mache','Naca','Nace','Nana','Neab','Neag','Neagoie','Nica','Nichie','Neagu','Nicolau','Oaca','Oae','Oaida','Oana','Oala','Oceanu','Oda','Odageriu','Odea','Odgon','Paca','Pacala','Paciu','Pacurari','Peagu','Pechea','Pechi','Pecheanu','Pechis','Pechiu','Rababoc','Rabei','Rac','Raca','Racle','Reaboi','Rebedea','Rebega','Rebej','Rebic','Rotaru','Saba','Sarpe','Sbanza','Sbarn','Scacioc','Sdobis','Sdrobici','Seba','Sebastian','Serban','Tabac','Tabachiu','Tabara','Tcaci','Teaca','Teban','Tecaciuc','Tecar','Tecariu','Tecareu','Uancea','Uca','Ucean','Udangiu','Uhaci','Uglea','Ugrutan','Udila','Udrea','Udovita','Vacea','Vasile','Vac','Vajea','Vlad','Valcu','Valvoi','Vana','Vedea','Valvoi','Zabarceanu','Zabos','Zahan','Zanceanu','Zaiceanu','Zaicean','Zah','Zdruncit','Zara','Zbant');
  36.     lista_prenume_fete varr := varr('Alexandra', 'Andreea', 'Alina', 'Adina', 'Antonela', 'Alexia', 'Ana', 'Anabela', 'Adriana', 'Ariana', 'Anca', 'Anisoara', 'Bianca', 'Beatrice', 'Bogdana', 'Brandusa', 'Betina', 'Cosmina', 'Claudia', 'Crina', 'Camelia', 'Corina', 'Carmen', 'Codruta', 'Codrina', 'Catrina', 'Clara', 'Clarisa', 'Casandra', 'Cecilia', 'Daria', 'Dana', 'Daniela', 'Denisa', 'Diana', 'Daiana', 'Dora', 'Dorina', 'Dalila', 'Daciana', 'Elena', 'Eugenia', 'Ecaterina', 'Elodia', 'Emilia', 'Evelina', 'Eliza', 'Florina', 'Flavia', 'Fiona', 'Florentina', 'Filofteia', 'Francesca', 'Gabriela', 'Georgiana', 'Gina', 'Gratiela', 'Georgia', 'Hortensia', 'Irina', 'Iulia', 'Iolanda', 'Ionela', 'Ivona', 'Iuliana', 'Julieta', 'Julia', 'Janeta', 'Jana', 'Letitia', 'Liliana', 'Laura', 'Luciana', 'Lucretia', 'Manuela', 'Maria', 'Mariuca', 'Maricica', 'Mirela', 'Monica', 'Marta', 'Malina', 'Narcisa', 'Nadia', 'Nicoleta', 'Nora', 'Natalia', 'Otilia', 'Oana', 'Olivia', 'Olga', 'Olimpia', 'Paraschiva', 'Paula', 'Patricia', 'Petronela', 'Pamela', 'Rodica', 'Ramona', 'Raluca', 'Rebeca', 'Roxana', 'Sofia', 'Sabina', 'Simona', 'Sorina', 'Sonia', 'Stela', 'Tamara', 'Teodora', 'Tania', 'Teona', 'Valentina', 'Veronica', 'Vanesa', 'Viviana', 'Valeria');
  37.     lista_prenume_baieti varr := varr('Angel', 'Andrei', 'Augustin', 'Antonio', 'Aurel','Alin', 'Adelin', 'Adelin', 'Bogdan', 'Cornel', 'Catalin', 'Claudiu', 'Cezar', 'Constantin', 'Ciprian', 'Costel', 'Dan', 'Daniel', 'Dorian', 'Dorel', 'Denis', 'Doru', 'Eugen', 'Emil', 'Eric', 'Eusebiu', 'Eduard', 'Fabian', 'Florin', 'Flavius', 'Felix', 'Filip', 'Gabi', 'Gabriel', 'Ghita', 'Giga', 'George', 'Gianu', 'Horia', 'Horatiu', 'Iacob', 'Ionel', 'Ion', 'Iulian', 'Iustin', 'Ivan', 'Irinel', 'Ilie', 'Laur', 'Lucian', 'Lucretiu', 'Licentiu', 'Lazar', 'Laurentiu', 'Marcel', 'Mihai', 'Mitica', 'Marcu', 'Mircea', 'Madalin', 'Maximilian', 'Nae', 'Nelu', 'Nicu', 'Nicolae', 'Narcis', 'Nechifor', 'Nicusor', 'Norman', 'Octavian', 'Octavius', 'Otello', 'Oliviu', 'Ovidiu', 'Octav', 'Paul', 'Petru', 'Petrica', 'Pavel', 'Radu', 'Remus', 'Rares', 'Razvan', 'Raul', 'Relu', 'Sorin', 'Simon', 'Sabin', 'Sandu', 'Sergiu', 'Silviu', 'Teodor', 'Tudor', 'Traian', 'Toma', 'Tiberiu', 'Valeriu', 'Vicentiu', 'Valentin', 'Vlad', 'Viorel', 'Vasile');
  38.    
  39.     v_nume VARCHAR2(255);
  40.     v_prenume VARCHAR2(255);
  41.     v_email VARCHAR2(255);
  42.     v_varsta NUMBER(2);
  43.     v_telefon VARCHAR2(10);
  44.     v_cifra NUMBER(1);
  45.     v_stapan INT;
  46.     v_doctor INT;
  47.     v_password VARCHAR2(255);
  48. BEGIN
  49.     DBMS_OUTPUT.PUT_LINE('Inserarea doctori in tabela useri');
  50.     DBMS_OUTPUT.PUT_LINE('Inserarea doctori in tabela tabela doctori');
  51.     DBMS_OUTPUT.PUT_LINE('Creare legatura doctori-useri in tabela doctoriuseri');
  52.     FOR v_i IN 1..1000000 LOOP
  53.         v_nume := lista_nume(TRUNC(DBMS_RANDOM.VALUE(0,lista_nume.COUNT))+1);
  54.         IF(DBMS_RANDOM.VALUE(0,100)<50) THEN
  55.             v_prenume := lista_prenume_fete(TRUNC(DBMS_RANDOM.VALUE(0,lista_prenume_fete.COUNT))+1);
  56.         ELSE
  57.             v_prenume := lista_prenume_baieti(TRUNC(DBMS_RANDOM.VALUE(0,lista_prenume_baieti.COUNT))+1);
  58.         END IF;
  59.         v_email := LOWER(v_nume || '.' || v_prenume||TO_CHAR(v_i)||'@gmail.com');
  60.         v_varsta := TRUNC(DBMS_RANDOM.VALUE(25,65));
  61.         v_telefon :='07';
  62.         FOR  v_tlf IN 1..8 LOOP
  63.           v_cifra := TRUNC(DBMS_RANDOM.VALUE(0,9));
  64.           v_telefon := CONCAT(v_telefon,TO_CHAR(v_cifra));
  65.         END LOOP;
  66.         v_password := DBMS_RANDOM.STRING('a',10);
  67.         v_doctor := 1;
  68.         v_stapan := 0;
  69.         INSERT INTO useri VALUES(v_i,v_email,v_password,v_doctor,v_stapan);
  70.         INSERT INTO doctori VALUES(v_i,v_nume,v_prenume,v_varsta,v_email,v_telefon);
  71.         INSERT INTO doctoriuseri VALUES (v_i,v_i,v_i);  
  72.     END LOOP;
  73. END;
  74. /
  75. DROP TABLE tip_animal CASCADE CONSTRAINTS
  76. /
  77. CREATE TABLE tip_animal (
  78.   id INT NOT NULL PRIMARY KEY,
  79.   tip_animal VARCHAR2(30)
  80. )
  81. /
  82. SET SERVEROTPUT ON
  83. DECLARE
  84.     TYPE varr IS VARRAY(1000) OF VARCHAR2(255);
  85.     lista_tip_animal varr := varr('Caine', 'Pisica', 'Hamster', 'Perus', 'Papagal', 'Porumbel', 'Soparla');
  86.     v_nume VARCHAR2(255);
  87. BEGIN
  88.     DBMS_OUTPUT.PUT_LINE('Inserare tipuri animale');
  89.     FOR v_i IN 1..lista_tip_animal.COUNT LOOP
  90.       v_nume := lista_tip_animal(v_i);
  91.       INSERT INTO tip_animal VALUES(v_i, v_nume);
  92.     END LOOP;
  93. END;
  94. /
  95. DROP TABLE specializare CASCADE CONSTRAINTS
  96. /
  97. CREATE TABLE specializare (
  98.     id INT NOT NULL PRIMARY KEY,
  99.     id_doctor INT NOT NULL,
  100.     id_animal INT NOT NULL,
  101.     CONSTRAINT fk_specializare_id_doctor FOREIGN KEY (id_doctor) REFERENCES doctori(id),
  102.     CONSTRAINT fk_specializare_id_animal FOREIGN KEY (id_animal) REFERENCES tip_animal(id)
  103. )
  104. /
  105. SET SERVEROUTPUT ON;
  106. DECLARE
  107.     v_animal_tip_count INT;
  108.     v_doctori_count INT;
  109.     v_animal_tip INT;
  110.     v_id_doctor INT;
  111. BEGIN
  112.     DBMS_OUTPUT.PUT_LINE('Inserare specializari pentru fiecare doctor');
  113.     SELECT COUNT(*) INTO v_doctori_count FROM doctori;
  114.     SELECT COUNT(*) INTO v_animal_tip_count FROM tip_animal;
  115.     FOR v_i IN 1..v_doctori_count LOOP
  116.       v_animal_tip := TRUNC(DBMS_RANDOM.VALUE(0,v_animal_tip_count))+1;
  117.       INSERT INTO specializare VALUES (v_i,v_i,v_animal_tip);
  118.     END LOOP;
  119. END;
  120. /
  121. DROP TABLE stapani CASCADE CONSTRAINTS
  122. /
  123. CREATE TABLE stapani(
  124.     id INT NOT NULL PRIMARY KEY,
  125.     nume VARCHAR2(15) NOT NULL,
  126.     prenume VARCHAR2(30) NOT NULL,
  127.     varsta NUMBER(2) NOT NULL,
  128.     email VARCHAR2(60) NOT NULL,
  129.     numar_telefon NUMBER(10) NOT NULL
  130. )
  131. /
  132. DROP TABLE stapaniuseri CASCADE CONSTRAINTS
  133. /
  134. CREATE TABLE stapaniuseri(
  135.     id INT NOT NULL PRIMARY KEY,
  136.     id_user INT NOT NULL,
  137.     id_stapan INT NOT NULL,
  138.     CONSTRAINT fk_stapaniuseri_id_user FOREIGN KEY (id_user) REFERENCES useri(id),
  139.     CONSTRAINT fk_stapaniuseri_id_stapan FOREIGN KEY (id_stapan) REFERENCES stapani(id)
  140. )
  141. /
  142. SET SERVEROUTPUT ON;
  143. DECLARE
  144.     TYPE varr IS VARRAY(1000) OF VARCHAR2(255);
  145.     lista_nume_stapani varr := varr('Albul','Acasandrei','Adamache','Aioane','Alamaie','Albut','Amar','Apetrei','Ardeleanu','Aval','Andrei','Alexandrescu','Barbut','Baciu','Bacovia','Biscuite','Bej','Bejan','Barbu','Bubuila','Bucur','Bulboaca','Caban','Ceacar','Cerbul','Chiriloi','Chisalita','Ciuciu','Ciuhu','Ciuhodaru','Curaj','Cucila','Cristi','Dabani','Damb','Deacu','Dia','Dior','Diac','Divin','Dascalitu','Dir','Dezi','Denoapte','Devesel','Eacobescu','Ebui','Edelcean','Efrim','Eftodi','Emagro','Enachescu','Epurescu','Eufrosie','Eva','Fabricant','Fachirul','Fagarasi','Fierar','Filat','Feldesi','Fraierul','Fucas','Fulger','Frasinaru','Galati','Gecoaica','Gheara','Ghetar','Geamanu','Geamanul','Gheorghita','Gherca','Ghergheloaia','Ghergheluca','Haba','Hada','Habuc','Hedea','Hedeus','Heia','Helici','Hert','Hertug','Hritac','Iacata','Iac','Ibanescu','Ichim','Idiceanu','Iefta','Iulian','In','Ioia','Ioje','Jaba','Jaca','Jacota','Jbanca','Jdeica','Jder','Jdirea','Jianul','Jneapan','Joaca-bine','Joaca-rau','Keseru','Kilcos','Kiriac','Kilogram','Kogalniceanu','Komlosi','Kosat','Kovaci','Kretulescu','Kirita','Kalciu','Labician','Laciur','Lacrima','Laes','Lambeanu','Leacul','Lia','Lezeu','Lerege','Lezeriuc','Leizeriuc','Levoia','Mac','Macdonalds','Macafei','Mandria','Meca','Mecer','Marina','Meciu','Mecib','Melcu','Mache','Mariniuc','Nacal','Nace','Nana','Neabi','Neagoie','Neagoi','Nicar','Nichie','Neagu','Nicolau','Nicoara','Oaca','Oae','Oaida','Oana','Oala','Oceanu','Oda','Odageriu','Odea','Odgon','Paca','Pacanea','Pacala','Paciu','Pacurari','Peagu','Pechea','Pechi','Pecheanu','Pechi','Pechiul','Rababoc','Rabei','Rac','Raca','Racla','Reaboi','Rebedea','Rebega','Rebej','Rebic','Rotaru','Saba','Sarpe','Sbanza','Sbarn','Scacioc','Sdobis','Sdrobici','Seba','Sebastian','Serban','Sanie','Seara','Tabac','Tiganasu','Tabachie','Tabara','Tabaracea','Taci','Teaca','Teban','Tecaciuc','Tecar','Tecariu','Tecaritu','Tecareu','Uancea','Uca','Ucean','Udangiu','Uhaci','Uglea','Ugrutan','Udila','Udrea','Udovita','Vacea','Vasile','Vac','Vajea','Vlad','Valcu','Valvoi','Vana','Vedea','Valvoi','Zabarceanu','Zabos','Zahan','Zanceanu','Zaiceanu','Zaicean','Zah','Zdruncit','Zara','Zbant');
  146.     lista_prenume_fete varr := varr('Alexandra', 'Andreea', 'Alina', 'Adina', 'Antonela', 'Alexia', 'Ana', 'Anabela', 'Adriana', 'Ariana', 'Anca', 'Anisoara', 'Bianca', 'Beatrice', 'Bogdana', 'Brandusa', 'Betina', 'Cosmina', 'Claudia', 'Crina', 'Camelia', 'Corina', 'Carmen', 'Codruta', 'Codrina', 'Catrina', 'Clara', 'Clarisa', 'Casandra', 'Cecilia', 'Daria', 'Dana', 'Daniela', 'Denisa', 'Diana', 'Daiana', 'Dora', 'Dorina', 'Dalila', 'Daciana', 'Elena', 'Eugenia', 'Ecaterina', 'Elodia', 'Emilia', 'Evelina', 'Eliza', 'Florina', 'Flavia', 'Fiona', 'Florentina', 'Filofteia', 'Francesca', 'Gabriela', 'Georgiana', 'Gina', 'Gratiela', 'Georgia', 'Hortensia', 'Irina', 'Iulia', 'Iolanda', 'Ionela', 'Ivona', 'Iuliana', 'Julieta', 'Julia', 'Janeta', 'Jana', 'Letitia', 'Liliana', 'Laura', 'Luciana', 'Lucretia', 'Manuela', 'Maria', 'Mariuca', 'Maricica', 'Mirela', 'Monica', 'Marta', 'Malina', 'Narcisa', 'Nadia', 'Nicoleta', 'Nora', 'Natalia', 'Otilia', 'Oana', 'Olivia', 'Olga', 'Olimpia', 'Paraschiva', 'Paula', 'Patricia', 'Petronela', 'Pamela', 'Rodica', 'Ramona', 'Raluca', 'Rebeca', 'Roxana', 'Sofia', 'Sabina', 'Simona', 'Sorina', 'Sonia', 'Stela', 'Tamara', 'Teodora', 'Tania', 'Teona', 'Valentina', 'Veronica', 'Vanesa', 'Viviana', 'Valeria');
  147.     lista_prenume_baieti varr := varr('Angel', 'Andrei', 'Augustin', 'Antonio', 'Aurel','Alin', 'Adelin', 'Adelin', 'Bogdan', 'Cornel', 'Catalin', 'Claudiu', 'Cezar', 'Constantin', 'Ciprian', 'Costel', 'Dan', 'Daniel', 'Dorian', 'Dorel', 'Denis', 'Doru', 'Eugen', 'Emil', 'Eric', 'Eusebiu', 'Eduard', 'Fabian', 'Florin', 'Flavius', 'Felix', 'Filip', 'Gabi', 'Gabriel', 'Ghita', 'Giga', 'George', 'Gianu', 'Horia', 'Horatiu', 'Iacob', 'Ionel', 'Ion', 'Iulian', 'Iustin', 'Ivan', 'Irinel', 'Ilie', 'Laur', 'Lucian', 'Lucretiu', 'Licentiu', 'Lazar', 'Laurentiu', 'Marcel', 'Mihai', 'Mitica', 'Marcu', 'Mircea', 'Madalin', 'Maximilian', 'Nae', 'Nelu', 'Nicu', 'Nicolae', 'Narcis', 'Nechifor', 'Nicusor', 'Norman', 'Octavian', 'Octavius', 'Otello', 'Oliviu', 'Ovidiu', 'Octav', 'Paul', 'Petru', 'Petrica', 'Pavel', 'Radu', 'Remus', 'Rares', 'Razvan', 'Raul', 'Relu', 'Sorin', 'Simon', 'Sabin', 'Sandu', 'Sergiu', 'Silviu', 'Teodor', 'Tudor', 'Traian', 'Toma', 'Tiberiu', 'Valeriu', 'Vicentiu', 'Valentin', 'Vlad', 'Viorel', 'Vasile');
  148.    
  149.     v_nume VARCHAR2(255);
  150.     v_prenume VARCHAR2(255);
  151.     v_email VARCHAR2(255);
  152.     v_varsta NUMBER(2);
  153.     v_telefon VARCHAR2(10);
  154.     v_cifra NUMBER(1);
  155.     v_stapan INT;
  156.     v_doctor INT;
  157.     v_password VARCHAR2(255);
  158.     v_user_nr INT;
  159.     v_i_user INT;
  160. BEGIN
  161.     DBMS_OUTPUT.PUT_LINE('Inserare stapani');
  162.     SELECT COUNT(*) INTO v_user_nr FROM useri;
  163.     FOR v_i IN 1..1000000 LOOP
  164.         v_nume := lista_nume_stapani(TRUNC(DBMS_RANDOM.VALUE(0,lista_nume_stapani.COUNT))+1);
  165.         IF(DBMS_RANDOM.VALUE(0,100)<50) THEN
  166.             v_prenume := lista_prenume_fete(TRUNC(DBMS_RANDOM.VALUE(0,lista_prenume_fete.COUNT))+1);
  167.         ELSE
  168.             v_prenume := lista_prenume_baieti(TRUNC(DBMS_RANDOM.VALUE(0,lista_prenume_baieti.COUNT))+1);
  169.         END IF;
  170.         v_email := LOWER(v_nume || '.' || v_prenume||TO_CHAR(v_i)||'@gmail.com');
  171.         v_varsta := TRUNC(DBMS_RANDOM.VALUE(18,90));
  172.         v_telefon := '07';
  173.         FOR  v_tlf IN 1..8 LOOP
  174.           v_cifra := TRUNC(DBMS_RANDOM.VALUE(0,9));
  175.           v_telefon := CONCAT(v_telefon,TO_CHAR(v_cifra));
  176.         END LOOP;
  177.         v_password := DBMS_RANDOM.STRING('a',10);
  178.         v_doctor := 0;
  179.         v_stapan := 1;
  180.         v_i_user := v_user_nr+v_i;
  181.         INSERT INTO useri VALUES(v_i_user,v_email,v_password,v_doctor,v_stapan);
  182.         INSERT INTO stapani VALUES(v_i,v_nume,v_prenume,v_varsta,v_email,v_telefon);
  183.         INSERT INTO stapaniuseri VALUES (v_i,v_i_user,v_i);
  184.     END LOOP;
  185. END;
  186. /
  187. DROP TABLE pacienti CASCADE CONSTRAINTS
  188. /
  189. CREATE TABLE pacienti (
  190.     id INT NOT NULL PRIMARY KEY,
  191.     nume VARCHAR2(10),
  192.     varsta VARCHAR2(2),
  193.     tip_animal VARCHAR2(30)
  194. )
  195. /
  196. DROP TABLE pacient_stapan CASCADE CONSTRAINTS
  197. /
  198. CREATE TABLE pacient_stapan (
  199.     id INT NOT NULL PRIMARY KEY,
  200.     id_pacient INT NOT NULL,
  201.     id_stapan INT NOT NULL,
  202.     CONSTRAINT fk_pacient_stapan_id_pacient FOREIGN KEY (id_pacient) REFERENCES pacienti(id),
  203.     CONSTRAINT fk_pacient_stapan_id_stapan FOREIGN KEY (id_stapan) REFERENCES stapani(id)
  204. )
  205. /
  206. SET SERVEROUTPUT ON;
  207. DECLARE
  208.     TYPE varr IS VARRAY(1000) OF VARCHAR2(255);
  209.     lista_tip_animal varr := varr('Caine', 'Pisica', 'Hamster', 'Perus', 'Papagal', 'Porumbel', 'Soparla');
  210.     lista_nume_animale varr := varr('Aricioi', 'Andinel', 'Alintatel', 'Arnor', 'Andy', 'Aricel', 'Biju', 'Birix', 'Boto', 'Botosel', 'Bidu', 'Bulinel', 'Bujorel', 'Batranel', 'Babinco', 'Bobi', 'Bobita', 'Balonel', 'Cocolico', 'Cocorico', 'Celi', 'Ciupitel', 'Cici', 'Ciorchinel', 'Ciripel', 'Delfi', 'Didi', 'Dino', 'Durduliul', 'Delfinel', 'Dulcica', 'Elefantel', 'Ely', 'Electricel', 'Edutu', 'Elinel', 'Fifi', 'Flocosel', 'Floricel', 'Flaffy', 'Fifo', 'Fidi', 'Gargarel', 'Giginel', 'Gogonel', 'Grivei', 'Gridi', 'Golanel', 'Iepurila', 'Irinel', 'Jujulici', 'Jijinel', 'Licurici', 'Lili', 'Lolo', 'Lila', 'Lolita', 'Libi', 'Mititel', 'Miri', 'Micinel', 'Mormaila', 'Nini', 'Nana', 'Nano', 'Norman', 'Odonel', 'Piti', 'Piticu', 'Prichindel', 'Puiutu', 'Petru', 'Pablo', 'Rori', 'Rex', 'Rexo', 'Richi', 'Sisu', 'Sisi', 'Sandi', 'Titi', 'Tomita', 'Tomi', 'Timi', 'Vivi', 'Vini', 'Volvo', 'Zurli', 'Zapacila', 'Zizina', 'Zizinel');
  211.  
  212.     v_nume VARCHAR2(255);
  213.     v_varsta NUMBER(2);
  214.     v_tip_animal VARCHAR2(255);
  215.     v_nr_stapani INT;
  216.     v_id_stapan INT;
  217. BEGIN
  218.     DBMS_OUTPUT.PUT_LINE('Inserare pacienti');
  219.     SELECT COUNT(*) INTO v_nr_stapani FROM stapani;
  220.     FOR v_i IN 1..1000000 LOOP
  221.         v_nume := lista_nume_animale(TRUNC(DBMS_RANDOM.VALUE(0,lista_nume_animale.COUNT))+1);
  222.         v_varsta := TRUNC(DBMS_RANDOM.VALUE(0,20));
  223.         v_id_stapan := TRUNC(DBMS_RANDOM.VALUE(1,v_nr_stapani));
  224.         v_tip_animal := lista_tip_animal(TRUNC(DBMS_RANDOM.VALUE(0,lista_tip_animal.COUNT))+1);
  225.  
  226.         INSERT INTO pacienti VALUES(v_i,v_nume,v_varsta,v_tip_animal);
  227.         INSERT INTO pacient_stapan VALUES(v_i,v_i,v_id_stapan);
  228.     END LOOP;
  229. END;
  230. /
  231. DROP TABLE programari CASCADE CONSTRAINTS
  232. /
  233. CREATE TABLE programari (
  234.     id INT NOT NULL PRIMARY KEY,
  235.     id_doctor INT NOT NULL,
  236.     id_pacient INT NOT NULL,
  237.     data DATE NOT NULL,
  238.     ora VARCHAR2(255) NOT NULL,
  239.     motiv VARCHAR2(255) NOT NULL,
  240.     CONSTRAINT fk_programari_id_doctor FOREIGN KEY (id_doctor) REFERENCES doctori(id),
  241.     CONSTRAINT fk_programari_id_pacient FOREIGN KEY (id_pacient) REFERENCES pacienti(id)
  242. )
  243. /
  244. SET SERVEROUTPUT ON;
  245. DECLARE
  246.     TYPE varr IS VARRAY(1000) OF VARCHAR2(255);
  247.     lista_motiv_programare varr := varr('Consultatie periodica', 'Consultatii dermatologice', 'Consultatii chirurgicale', 'Consiliere nutritionala', 'Microciparea si Carnetul de sanatate', 'Pasaportul', 'Vaccinarea antirabica', 'Vaccinarea polivalenta', 'Vaccinarea Monovalenta', 'Stomatologie', 'Oftalmologie', 'Analize de laborator');
  248.    
  249.     v_nr_doctori INT;
  250.     v_nr_pacienti INT;
  251.     v_id_doctor INT;
  252.     v_id_pacient INT;
  253.     v_data_programare DATE;
  254.     v_ora VARCHAR2(10);
  255.     v_motiv VARCHAR2(255);
  256. BEGIN
  257.     DBMS_OUTPUT.PUT_LINE('Inserare programari');
  258.     SELECT COUNT(*) INTO v_nr_doctori FROM doctori;
  259.     SELECT COUNT(*) INTO v_nr_pacienti FROM pacienti;
  260.     FOR v_i IN 1..1000000 LOOP
  261.         v_id_pacient := TRUNC(DBMS_RANDOM.VALUE(0, v_nr_pacienti))+1;
  262.         v_id_doctor := TRUNC(DBMS_RANDOM.VALUE(0, v_nr_doctori))+1;
  263.         v_data_programare := (SYSDATE + TRUNC(DBMS_RANDOM.VALUE(0,250)));
  264.         v_ora := CONCAT(TRUNC(TO_CHAR(DBMS_RANDOM.VALUE(10,17))),':00');
  265.         v_motiv := lista_motiv_programare(TRUNC(DBMS_RANDOM.VALUE(0,lista_motiv_programare.COUNT))+1);
  266.         INSERT INTO programari VALUES (v_i,v_id_doctor,v_id_pacient,v_data_programare,v_ora,v_motiv);
  267.     END LOOP;
  268. END;
  269. /
  270. DROP TABLE tratamente CASCADE CONSTRAINTS
  271. /
  272. CREATE TABLE tratamente (
  273.     id INT NOT NULL PRIMARY KEY,
  274.     motiv VARCHAR2(255) NOT NULL,
  275.     medicatie VARCHAR2(255) NOT NULL,
  276.     tip_animal VARCHAR2(255) NOT NULL
  277. )
  278. /
  279. DROP SEQUENCE tratamente_sequence;
  280. /
  281. CREATE SEQUENCE tratamente_sequence;
  282. /
  283. CREATE OR REPLACE TRIGGER tratamente_on_insert
  284.   BEFORE INSERT ON tratamente
  285.   FOR EACH ROW
  286. BEGIN
  287.   SELECT tratamente_sequence.NEXTVAL
  288.   INTO :NEW.id
  289.   FROM dual;
  290. END;
  291.  
  292. /
  293. SET SERVEROUTPUT ON;
  294. DECLARE
  295.     TYPE varr IS VARRAY(1000) OF VARCHAR2(255);
  296.     lista_tip_animal varr := varr('Caine', 'Pisica', 'Hamster', 'Perus', 'Papagal', 'Porumbel', 'Soparla');
  297.     lista_motiv_programare varr := varr('Consultatie periodica', 'Consultatii dermatologice', 'Consultatii chirurgicale', 'Consiliere nutritionala', 'Microciparea si Carnetul de sanatate', 'Pasaportul', 'Vaccinarea antirabica', 'Vaccinarea polivalenta', 'Vaccinarea Monovalenta', 'Stomatologie', 'Oftalmologie', 'Analize de laborator');
  298.     lista_medicatie_caini varr := varr('Beaphar Zgarda Bio Caine', 'Vitaderm solutie orala 200 ml', 'Petnil 70 tablete', 'Sampon Pyoderm Glyco, 200 ml', 'Dermoscent Cicafolia Caine/Pisica 30ml', 'Vitavet Complex 60 tablete', 'Sanal Cat-Dog Relax 15 tablete', 'TRICALD 3 x30 Comprimate', 'Solutie injectabil? MULTIVITAROM 50 ml', 'Aptus Reconvalescent Dog Vet Pasta 100 g', 'Dentivet Total 125ml', 'Veggiedent Medium Dog (10 - 30 kg), 15 bucati', 'ARMHAMMER SPRAY DENTAR CAINE, 120 ML', 'Clean Dental 50 g', 'Otoact 100ml', 'Otoprof 100 ml', 'Can Bel 60ml', 'Petkult Clean Drops 40ml', 'CleanAural Dog 50 ml', 'IRYPLUS 50ml');
  299.     lista_medicatie_pisici varr := varr('Sampon Pyoderm Glyco, 200 ml', 'CLOREXYDERM SOLUZIONE 0.5% 250ml', 'CLOREXYDERM SPOT GEL 100ml', 'Veteusan 100 ml', 'ECZEMTRAT 60 Comprimate', 'Supliment nutritiv CAVITROM 150 g', 'Pet Phos Felin 96 tablete', 'VMP Tabs, 50 tablete', 'Rx MegaFlex 600 comprimate', 'CalciTabs 150 tablete', 'Hills Science Plan Adult 1-6 Oral Care Chicken', 'Sanabelle Dental', 'Whiskas Dentabites', 'Malacetic Aural 118 ml', 'IRYPLUS 50ml', 'IRYPLUS WIPES-POCKET, 15 servetele umede', 'Optixcare EYE LUBE, 20 g', 'Optixcare EYE CLEANER, 100ml', 'OTODINE 100ml');
  300.     lista_medicatie_hamsteri varr := varr('TETRACICLINA ATB 250 mg CAPS.', 'Bubbles sampon pentru rozatoare, 250 ml', 'Rodicare Uro, 20 ml', 'RX Amino B-Plex 120 ml', 'Alfa-Vitam Rozatoare- Iepuri 10 ml', 'RX Amino B-Plex 60 ml', 'Chipsi 15 L - Asternut igienic', 'Zolux Asternut Igienic Chinchilla 2 Kg', 'Bio-Lapis, 6 plicuri x 2 g', 'Promedivet Tinctura de iod, 100 ml');
  301.     lista_medicatie_pasari varr := varr('Ectocid Herba Spray, 100 ml', 'Hepavit 1 L', 'Cothivet 30 ml', 'ALAMIN pulbere, 30 g', 'Alfavet Almalyt Energy Porumbei 200 g', 'Multivitamine Vitakraft 10 ml', 'FLAMINGO Os sepie ?i bloc mineral pentru p?s?ri', 'TRIXIE Os de sepie', 'VITAKRAFT Vita Sepia Plus Os de sepie pentru p?s?ri', ' Exo Terra Cricket', 'Tetra Fauna Tortoise 500 ml');
  302.     lista_medicatie_soparle varr := varr('Derma Gel Spray 50 ml', 'Kennel Odor Eliminator, 500 ml', 'Alfa-Vitam Rozatoare- Iepuri 10 ml', 'Tetra ReptiSol 50ml', 'Curatarea cu servetele umede a ochilor de 3 ori pe zi si clatirea cu apa calduta');
  303.  
  304.     v_motiv VARCHAR2(255);
  305.     v_medicatie VARCHAR2(255);
  306.     v_tip_animal VARCHAR2(255);
  307. BEGIN
  308.     DBMS_OUTPUT.PUT_LINE('Inserare tratamente');
  309.     FOR v_i IN 1..lista_motiv_programare.COUNT LOOP
  310.       v_motiv := lista_motiv_programare(v_i);
  311.       FOR v_ii IN 1..lista_tip_animal.COUNT LOOP
  312.         v_tip_animal := lista_tip_animal(v_ii);
  313.         IF(v_tip_animal = lista_tip_animal(1)) THEN
  314.             v_medicatie := lista_medicatie_caini(TRUNC(DBMS_RANDOM.VALUE(0,lista_medicatie_caini.COUNT)) + 1);
  315.         ELSIF(v_tip_animal = lista_tip_animal(2)) THEN
  316.             v_medicatie := lista_medicatie_pisici(TRUNC(DBMS_RANDOM.VALUE(0,lista_medicatie_pisici.COUNT)) +1);
  317.         ELSIF(v_tip_animal = lista_tip_animal(3)) THEN
  318.             v_medicatie := lista_medicatie_hamsteri(TRUNC(DBMS_RANDOM.VALUE(0,lista_medicatie_hamsteri.COUNT)) + 1);
  319.         ELSIF(v_tip_animal = lista_tip_animal(4)) THEN
  320.             v_medicatie := lista_medicatie_pasari(TRUNC(DBMS_RANDOM.VALUE(0,lista_medicatie_pasari.COUNT)) + 1);
  321.         ELSIF(v_tip_animal = lista_tip_animal(5)) THEN
  322.             v_medicatie := lista_medicatie_pasari(TRUNC(DBMS_RANDOM.VALUE(0,lista_medicatie_pasari.COUNT)) + 1);
  323.         ELSIF(v_tip_animal = lista_tip_animal(6)) THEN
  324.             v_medicatie := lista_medicatie_pasari(TRUNC(DBMS_RANDOM.VALUE(0,lista_medicatie_pasari.COUNT)) + 1);
  325.         ELSE
  326.             v_medicatie := lista_medicatie_soparle(TRUNC(DBMS_RANDOM.VALUE(0,lista_medicatie_soparle.COUNT)) + 1);
  327.         END IF;
  328.         INSERT INTO tratamente(motiv,medicatie,tip_animal) VALUES(v_motiv,v_medicatie,v_tip_animal);
  329.       END LOOP;
  330.     END LOOP;
  331. END;
  332. /
  333. DROP TABLE pacient_tratament CASCADE CONSTRAINTS
  334. /
  335. CREATE TABLE pacient_tratament(
  336.     id INT NOT NULL PRIMARY KEY,
  337.     id_pacient NOT NULL,
  338.     id_tratament NOT NULL,
  339.     CONSTRAINT fk_pacient_tratam_id_pacient FOREIGN KEY (id_pacient) REFERENCES pacienti(id),
  340.     CONSTRAINT fk_pacient_tratam_id_tratament FOREIGN KEY (id_tratament) REFERENCES tratamente(id)
  341.  
  342. )
  343. /
  344. SET SERVEROUTPUT ON;
  345. DECLARE
  346.     TYPE varr IS VARRAY(1000) OF VARCHAR2(255);
  347.     lista_tip_animal varr := varr('Caine', 'Pisica', 'Hamster', 'Perus', 'Papagal', 'Porumbel', 'Soparla');
  348.     v_tip_animal VARCHAR2(255);
  349.     v_id_tratament INT;
  350.     v_id_random INT;
  351. BEGIN
  352.     DBMS_OUTPUT.PUT_LINE('Inserare pacient-tratament');
  353.     FOR v_i IN 1..1000000 LOOP
  354.         SELECT tip_animal INTO v_tip_animal FROM pacienti WHERE id=v_i;
  355.         v_id_random := TRUNC(DBMS_RANDOM.VALUE(0,12))+1;
  356.         --select id into v_id_tratament from tratamente where tip_animal=v_tip_animal and rownum<v_id_random+1 minus select id into v_id_tratament from tratamente where tip_animal=v_tip_animal and rownum<v_id_random;
  357.         SELECT id INTO v_id_tratament FROM (SELECT id,ROWNUM AS line_nr FROM tratamente WHERE tip_animal=v_tip_animal) WHERE line_nr=v_id_random;
  358.         INSERT INTO pacient_tratament VALUES (v_i,v_i,v_id_tratament);
  359.     END LOOP;
  360. END;
  361. /
  362. CREATE INDEX exista_cont ON useri(email, password);
  363. /
  364. DROP VIEW find_doctor;
  365. /
  366. CREATE VIEW find_doctor(nume_doctor, prenume_doctor, specializare_animal)
  367.     AS SELECT nume, prenume, tip_animal FROM doctori, tip_animal, specializare
  368.     WHERE specializare.id_doctor=doctori.id AND specializare.id_animal=tip_animal.id;
  369. /
  370. DROP VIEW find_programare_for_stapani;
  371. /
  372. CREATE VIEW find_programare_for_stapani(nume_doctor, prenume_doctor, data_programare, ora_programare)
  373.     AS SELECT nume, prenume, data, ora FROM doctori, programari
  374.     WHERE doctori.id=programari.id_doctor;
  375. /
  376. DROP VIEW find_programare_for_doctori  
  377. /
  378. CREATE VIEW find_programare_for_doctori(nume_pacient, varsta_pacient, data_programare, ora_programare, motiv_programare)
  379.     AS SELECT nume, varsta, data, ora, motiv FROM pacienti, programari WHERE programari.id_pacient=pacienti.id;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top