L17GP2tD

1DROP TABLE elevi1 CASCADE CONSTRAINTS
2/
3DROP TABLE parinti1 CASCADE CONSTRAINTS
4/
5DROP TABLE relatii1 CASCADE CONSTRAINTS
6/
7DROP TABLE profesori1 CASCADE CONSTRAINTS
8/
9DROP TABLE accounts CASCADE CONSTRAINTS
10/
11DROP TABLE materii1 CASCADE CONSTRAINTS
12/
13DROP TABLE teze1 CASCADE CONSTRAINTS
14/
15DROP TABLE activitate1 CASCADE CONSTRAINTS
16/
17
18CREATE TABLE elevi1 (
19  id INT NOT NULL PRIMARY KEY,
20  nume VARCHAR2(20) NOT NULL,
21  prenume VARCHAR2(40) NOT NULL, 
22  nr_matricol VARCHAR2(14) NOT NULL,
23  profil VARCHAR2(25),
24  clasa VARCHAR2(3) )
25/
26
27CREATE TABLE parinti1 ( 
28  id INT NOT NULL PRIMARY KEY,
29  id_elev INT NOT NULL,
30  nume VARCHAR2(20) NOT NULL,
31  prenume VARCHAR2(40) NOT NULL,
32  
33  CONSTRAINT fk_parinti1_id_student FOREIGN KEY (id_elev) REFERENCES elevi1(id) )
34/
35
36CREATE TABLE relatii1 ( 
37  id INT NOT NULL PRIMARY KEY,
38  id_elev INT NOT NULL,
39  id_parinte INT NOT NULL,
40  
41  CONSTRAINT fk_relatie_id_elev FOREIGN KEY (id_elev) REFERENCES elevi1(id),
42  CONSTRAINT fk_relatie_id_parinte FOREIGN KEY (id_parinte) REFERENCES parinti1(id) )
43/
44
45CREATE TABLE profesori1 (
46  id INT NOT NULL PRIMARY KEY,
47  nume VARCHAR2(20) NOT NULL,
48  prenume VARCHAR2(40) NOT NULL,
49  email VARCHAR2(60) NOT NULL )
50/
51
52CREATE TABLE materii1 ( 
53  id INT NOT NULL PRIMARY KEY,
54  nume_materie VARCHAR2(40) NOT NULL,
55  clasa INT NOT NULL,
56  id_profesor INT NOT NULL,
57  profil VARCHAR2(4) NOT NULL,
58  
59  CONSTRAINT fk_materii_id_profesor FOREIGN KEY (id_profesor) REFERENCES profesori1(id) )
60/
61
62CREATE TABLE accounts (
63  nume_cont VARCHAR2(65) NOT NULL,
64  parola VARCHAR2(10) NOT NULL )
65/
66
67CREATE TABLE teze1 ( 
68  id INT NOT NULL PRIMARY KEY,
69  id_elev INT NOT NULL,
70  id_profesor INT NOT NULL,
71  id_materie INT NOT NULL,
72  nota_teza INT NOT NULL,
73  data_notare DATE NOT NULL,
74  
75  CONSTRAINT fk_teze1_ide_profesor FOREIGN KEY (id_elev) REFERENCES elevi1(id),
76  CONSTRAINT fk_teze1_idp_profesor FOREIGN KEY (id_profesor) REFERENCES profesori1(id),
77  CONSTRAINT fk_teze1_idm_profesor FOREIGN KEY (id_materie) REFERENCES materii1(id) )
78/  
79
80CREATE TABLE activitate1 (
81  id_elev INT NOT NULL,
82  id_materie INT NOT NULL,
83  id_profesor INT NOT NULL,
84  data_notare DATE NOT NULL,
85  nota INT,
86  
87  CONSTRAINT fk_activitate_id_profesor FOREIGN KEY (id_profesor) REFERENCES profesori1(id),
88  CONSTRAINT fk_activitate_id_elev FOREIGN KEY (id_elev) REFERENCES elevi1(id),
89  CONSTRAINT fk_activitate_id_materie FOREIGN KEY (id_materie) REFERENCES materii1(id) )
90/
91
92DECLARE 
93  TYPE myString IS VARRAY(5000) OF varchar2(255);
94  nume myString := myString('Pirlog','Dinescu','Puiu','Poenaru','Ionesco','Stoenescu','Boroi','Cinca','Bratianu','Florea','Olaru'
95              ,'Pavel','Neagoe','Nica','Tilea','Banciu','Hila','Cristea','Dobre','Vladimirescu','Cojocaru','Gaina','Stolojan'
96              ,'Dumitrescu','Martinecu','Mocanu','Nistor','Manole','Dumitru','Pop','Minea','Avramescu','Dimir','Munteanu'
97              ,'Lazarescu','Babes','Goga','Dragan','Ilionescu','Antonescu','Mircea','Mihnea','Albu','Mitu','Lupei','Ghita','Ungur'
98              ,'Ungureanu','Enescu','Moisuc','Lascar');
99  
100  prenume_baieti myString := myString('Marcel','Alexandru','Andrei','Anghel','Avram','Silviu','Eduard','Calin','Raul','Stefan','Traian'
101              ,'Ioan','Ionut','Denis','Sandu','Beniamin','Petre','Ovidiu','Flavius','Ion','Mihai','Vasile','Robert','Corneliu'
102              ,'Gabriel','Horatiu','Valeriu','Iuliu','Alin','Neculai','Horia','Teo','Artur','Gavril','Luca','Cristian','David'
103              ,'Costi','Constantin','Marku','Laurentiu','Cosmin','Mihail','Dumitru','Ivan','Carol','Octavian','Bogdan','Martin','Ghorghe');
104  
105  prenume_fete myString := myString('Ana','Alexandra','Marta','Madalina','Ileana','Narcisa','Dorina','Sorana','Magdalena','Sorina'
106              ,'Corina','Voileta', 'Elisabeta','Aurica','Denisa','Luminita','Florina','Tatiana','Adelina','Ioana','Mihaela'
107              ,'Cami','Ecaterina','Marcela','Oana','Sanda','Ligia','Claudia','Lavinia','Victoria','Voctorita','Antanasia'
108              ,'Cornelia','Valerica','Carla','Rozalia','Veronica','Marilena','Bianca','Ioanela','Magda','Georgeta','Andreea'
109              ,'Cecilia','Georgiana','Sabina','Nicoleta','Delia','Marioara','Stefania');
110  
111  lista_materii_clasa_9_real myString := myString('Logica', 'Matematica', 'Limba_Romana','Fizica','Istorie','Geografie','Informatica','Educatie Fizica','Desen','Educatie Muzicala','Chimie','Religie','Limba Engleza','Limba Franceza','TIC','Biologie');
112  lista_materii_clasa_10_real myString := myString('Psihologie', 'Matematica', 'Limba Romana','Fizica','Istorie','Geografie','Informatica','Educatie Fizica','Desen','Educatie Muzicala','Chimie','Religie','Limba Engleza','Limba Franceza','TIC','Biologie');
113  lista_materii_clasa_11_real myString := myString('Filozofie', 'Matematica', 'Limba Romana','Fizica','Istorie','Geografie','Informatica','Educatie Fizica','Chimie','Religie','Limba Engleza','Limba Franceza','Biologie');
114  lista_materii_clasa_12_real myString := myString('Economie', 'Matematica', 'Limba Romana','Fizica','Istorie','Geografie','Informatica','Educatie Fizica','Chimie','Religie','Limba Engleza','Limba Franceza','Biologie');
115  lista_materii_clasa_9_uman myString := myString('Logica', 'Matematica', 'Limba Romana','Fizica','Istorie','Geografie','Educatie Fizica','Desen','Educatie Muzicala','Chimie','Religie','Limba Engleza','Limba Franceza','TIC','Biologie');
116  lista_materii_clasa_10_uman myString := myString('Psihologie', 'Matematica', 'Limba Romana','Fizica','Istorie','Geografie','Educatie Fizica','Desen','Educatie Muzicala','Chimie','Religie','Limba Engleza','Limba Franceza','TIC','Biologie');
117  lista_materii_clasa_11_uman myString := myString('Filozofie', 'Matematica', 'Limba Romana','Istorie','Geografie','Educatie Fizica','Religie','Limba Engleza','Limba Franceza','TIC','Literatura Universala');
118  lista_materii_clasa_12_uman myString := myString('Economie', 'Matematica', 'Limba Romana','Istorie','Geografie','Educatie Fizica','Religie','Limba Engleza','Limba Franceza','TIC','Literatura Universala');
119
120--DATE DESPRE ELEVI
121  v_nume_elev VARCHAR2(255);
122  v_prenume_elev VARCHAR2(255);
123  v_prenume1_elev VARCHAR2(255);
124  v_prenume2_elev VARCHAR2(255);
125  v_numar_matricol VARCHAR2(14);
126  v_clasa VARCHAR2(4);
127  v_profil VARCHAR2(25);
128  
129--DATE DESPRE PARINTI
130  v_nume_parinte VARCHAR2(255);
131  v_prenume_parinte VARCHAR2(255);
132  v_prenume1_parinte VARCHAR2(255);
133  v_prenume2_parinte VARCHAR2(255);
134
135--DATE DESPRE MATERII
136  v_nume_materie VARCHAR2(255);
137  v_clasa_materie INT;
138
139--DATE DESPRE PROFESORI
140  v_nume_profesor VARCHAR2(255);
141  v_prenume_profesor VARCHAR2(255);
142  v_prenume1_profesor VARCHAR2(255);
143  v_prenume2_profesor VARCHAR2(255);
144  v_email_profesor VARCHAR2(255);
145
146--DATE DESPRE NOTE/ABSENTE  
147  v_data_nastere DATE;
148
149--DATEDESPRE CONTURI
150  v_user VARCHAR2(255);
151  v_parola VARCHAR2(255);
152
153--DATE AUXILIARE
154  v_auxiliar INT;
155  v_auxiliar1 INT;
156  v_auxiliar2 NUMBER;
157  v_auxiliar3 INT;
158  v_auxiliar4 INT;
159  v_aux_clasa VARCHAR2(1);
160  v_nr_matricol_auxiliar VARCHAR2(14);
161  v_temporar INT;
162  contor INT;
163  v_contor_note INT;
164  v_randomizare INT;
165BEGIN
166  contor := 1;
167--  CLASELE 9-10-11-12
168--  CLASE: A-B-C-D-E-F-G-H
169--  30ELEVI/CLASA
170--  30 x 8 = 240 x 4 = 960 elevi de inserat
171  v_contor_note := 1;
172  v_auxiliar := 1;
173  v_auxiliar3 := 1;
174 DBMS_OUTPUT.PUT_LINE('Incepem crearea profesorilor.');
175  DBMS_OUTPUT.PUT_LINE('<------START------>');
176  
177  FOR v_index IN 1..110 LOOP
178    v_nume_profesor := nume(TRUNC(DBMS_RANDOM.VALUE(0,nume.COUNT))+1);
179    IF (SYS.DBMS_RANDOM.VALUE(0,100) < 50) THEN
180      v_prenume1_profesor := prenume_baieti(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_baieti.COUNT)) + 1);
181      IF ((SYS.DBMS_RANDOM.VALUE(0,100) < 20 )) THEN
182        v_prenume2_profesor := prenume_baieti(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_baieti.COUNT)) + 1);
183        WHILE ( v_prenume1_profesor = v_prenume2_profesor) LOOP
184          v_prenume2_profesor := prenume_baieti(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_baieti.COUNT)) + 1);
185        END LOOP;
186      END IF;
187      v_prenume_profesor := v_prenume1_profesor || ' ' || v_prenume2_profesor;
188    ELSE 
189      v_prenume1_profesor := prenume_fete(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_fete.COUNT)) + 1);
190      IF ((SYS.DBMS_RANDOM.VALUE(0,100) < 20 )) THEN
191        v_prenume2_profesor := prenume_fete(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_fete.COUNT)) + 1);
192        WHILE ( v_prenume1_profesor = v_prenume2_profesor) LOOP
193          v_prenume2_profesor := prenume_fete(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_fete.COUNT)) + 1);
194        END LOOP;
195      END IF;
196      v_prenume_profesor := v_prenume1_profesor || ' ' || v_prenume2_profesor;
197    END IF;
198    
199    v_email_profesor := LOWER(v_nume_profesor) || '.' || LOWER(v_prenume1_profesor);
200    IF (length(v_prenume2_profesor) > 0) THEN
201      v_email_profesor := v_email_profesor || '.' || LOWER(v_prenume2_profesor);
202    END IF;
203    v_email_profesor := v_email_profesor || '@gmail.com';
204    
205    
206--    DBMS_OUTPUT.PUT_LINE(v_nume_profesor || ' ' ||  v_prenume1_profesor || ' ' || v_prenume2_profesor);
207--    DBMS_OUTPUT.PUT_LINE(v_email_profesor);
208    
209    insert into profesori1 values(v_index,v_nume_profesor,v_prenume_profesor,v_email_profesor);
210    
211    v_user := 'P.' || LOWER(v_nume_profesor) || '.' || LOWER(v_prenume1_profesor);
212  
213    IF(LENGTH(v_prenume2_profesor) > 0) THEN
214      v_user := v_user || '.' || LOWER(v_prenume2_profesor);
215    END IF;
216  
217    v_parola := TRUNC(DBMS_RANDOM.VALUE(100,999)) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90))) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90))) || TRUNC(DBMS_RANDOM.VALUE(100,999)) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90))) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90)));
218  
219    insert into accounts values(v_user,v_parola);
220    v_parola := '';
221    v_user := '';
222    v_nume_profesor := '';
223    v_prenume_profesor :='';
224    v_prenume1_profesor := '';
225    v_prenume2_profesor := '';
226    v_email_profesor := '';
227
228  END LOOP;
229  
230  v_auxiliar := 1;
231  
232  FOR v_index IN 1..lista_materii_clasa_9_real.COUNT LOOP
233    
234    insert into materii1 values(v_auxiliar,lista_materii_clasa_9_real(v_index),9,v_auxiliar,'real');
235    
236    v_auxiliar := v_auxiliar + 1;
237    
238  END LOOP;
239  FOR v_index IN 1..lista_materii_clasa_10_real.COUNT LOOP
240    
241    insert into materii1 values(v_auxiliar,lista_materii_clasa_10_real(v_index),10,v_auxiliar,'real');
242    
243    v_auxiliar := v_auxiliar + 1;
244    
245  END LOOP;
246  FOR v_index IN 1..lista_materii_clasa_11_real.COUNT LOOP
247    
248    insert into materii1 values(v_auxiliar,lista_materii_clasa_11_real(v_index),11,v_auxiliar,'real');
249    
250    v_auxiliar := v_auxiliar + 1;
251    
252  END LOOP;
253  FOR v_index IN 1..lista_materii_clasa_12_real.COUNT LOOP
254    
255    insert into materii1 values(v_auxiliar,lista_materii_clasa_12_real(v_index),12,v_auxiliar,'real');
256    
257    v_auxiliar := v_auxiliar + 1;
258    
259  END LOOP;
260  FOR v_index IN 1..lista_materii_clasa_9_uman.COUNT LOOP
261    
262    insert into materii1 values(v_auxiliar,lista_materii_clasa_9_uman(v_index),9,v_auxiliar,'uman');
263    
264    v_auxiliar := v_auxiliar + 1;
265    
266  END LOOP;
267  FOR v_index IN 1..lista_materii_clasa_10_uman.COUNT LOOP
268    
269    insert into materii1 values(v_auxiliar,lista_materii_clasa_10_uman(v_index),10,v_auxiliar,'uman');
270    
271    v_auxiliar := v_auxiliar + 1;
272    
273  END LOOP;
274  FOR v_index IN 1..lista_materii_clasa_11_uman.COUNT LOOP
275    
276    insert into materii1 values(v_auxiliar,lista_materii_clasa_11_uman(v_index),11,v_auxiliar,'uman');
277    
278    v_auxiliar := v_auxiliar + 1;
279    
280  END LOOP;
281  FOR v_index IN 1..lista_materii_clasa_12_uman.COUNT LOOP
282    
283    insert into materii1 values(v_auxiliar,lista_materii_clasa_12_uman(v_index),12,v_auxiliar,'uman');
284    
285    v_auxiliar := v_auxiliar + 1;
286    
287  END LOOP;
288  DBMS_OUTPUT.PUT_LINE('<------GATA------>');
289  DBMS_OUTPUT.PUT_LINE('Incercam inserarea a 960 de elevi.');
290  DBMS_OUTPUT.PUT_LINE('<------START------>');
291  FOR v_index IN 1..960 LOOP
292    v_nr_matricol_auxiliar := 'AB20182019';
293    v_nume_elev := nume(TRUNC(DBMS_RANDOM.VALUE(0,nume.COUNT))+1);
294    IF (SYS.DBMS_RANDOM.VALUE(0,100) < 50) THEN
295      v_prenume1_elev := prenume_baieti(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_baieti.COUNT)) + 1);
296      IF ((SYS.DBMS_RANDOM.VALUE(0,100) < 20 )) THEN
297        v_prenume2_elev := prenume_baieti(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_baieti.COUNT)) + 1);
298        WHILE ( v_prenume1_elev = v_prenume2_elev) LOOP
299          v_prenume2_elev := prenume_baieti(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_baieti.COUNT)) + 1);
300        END LOOP;
301      END IF;
302      v_prenume_elev := v_prenume1_elev || ' ' || v_prenume2_elev;
303    ELSE 
304      v_prenume1_elev := prenume_fete(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_fete.COUNT)) + 1);
305      IF ((SYS.DBMS_RANDOM.VALUE(0,100) < 20 )) THEN
306        v_prenume2_elev := prenume_fete(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_fete.COUNT)) + 1);
307        WHILE ( v_prenume1_elev = v_prenume2_elev) LOOP
308          v_prenume2_elev := prenume_fete(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_fete.COUNT)) + 1);
309        END LOOP;
310      END IF;
311      v_prenume_elev := v_prenume1_elev || ' ' || v_prenume2_elev;
312    END IF;
313  
314  IF (v_auxiliar < 10 ) THEN 
315    v_nr_matricol_auxiliar := v_nr_matricol_auxiliar || '00' || v_auxiliar;
316  ELSIF ( v_auxiliar < 100 AND v_auxiliar >= 10 ) THEN
317    v_nr_matricol_auxiliar := v_nr_matricol_auxiliar || '0' || v_auxiliar;
318  ELSE 
319    v_nr_matricol_auxiliar := v_nr_matricol_auxiliar || v_auxiliar;
320  END IF;
321
322    IF ( v_auxiliar <= 480 ) THEN
323      v_profil := 'real';
324      IF ( v_auxiliar <= 40 ) THEN 
325        v_clasa := '12A';
326        v_temporar := 12;
327      ELSIF(v_auxiliar <= 80) THEN
328        v_clasa := '12B';
329        v_temporar := 12;
330      ELSIF(v_auxiliar <= 120) THEN
331        v_clasa := '12C';
332        v_temporar := 12;
333      ELSIF(v_auxiliar <= 160) THEN
334        v_clasa := '12D';
335        v_temporar := 12;
336      ELSIF(v_auxiliar <= 200) THEN
337        v_clasa := '11A';
338        v_temporar := 11;
339      ELSIF(v_auxiliar <= 240) THEN
340        v_clasa := '11B';
341        v_temporar := 11;
342      ELSIF(v_auxiliar <= 280) THEN
343        v_clasa := '11C';
344        v_temporar := 11;
345      ELSIF(v_auxiliar <= 320) THEN
346        v_clasa := '11D';
347        v_temporar := 11;
348      ELSIF(v_auxiliar <= 360) THEN
349        v_clasa := '10A';
350        v_temporar := 10;
351      ELSIF(v_auxiliar <= 400) THEN
352        v_clasa := '10B';
353        v_temporar := 10;
354      ELSIF(v_auxiliar <= 440) THEN
355        v_clasa := '10C';
356        v_temporar := 10;
357      ELSIF(v_auxiliar <= 480) THEN
358        v_clasa := '10D';
359        v_temporar := 10;
360      ELSIF(v_auxiliar <= 520) THEN
361        v_clasa := '9A';
362        v_temporar := 9;
363      ELSIF(v_auxiliar <= 560) THEN
364        v_clasa := '9B';
365        v_temporar := 9;
366      ELSIF(v_auxiliar <= 600) THEN
367        v_clasa := '9C';
368        v_temporar := 9;
369      ELSIF(v_auxiliar <= 640) THEN
370        v_clasa := '9D';
371        v_temporar := 9;
372      END IF;
373    ELSE
374      v_profil := 'uman';
375      IF ( v_auxiliar <= 680 ) THEN 
376        v_clasa := '12E';
377        v_temporar := 12;
378      ELSIF(v_auxiliar <= 720) THEN
379        v_temporar := 12;
380        v_clasa := '12F';
381      ELSIF(v_auxiliar <= 760) THEN
382        v_clasa := '12G';
383        v_temporar := 12;
384      ELSIF(v_auxiliar <= 800) THEN
385        v_clasa := '12H';
386        v_temporar := 12;
387      ELSIF(v_auxiliar <= 840) THEN
388        v_clasa := '11E';
389        v_temporar := 11;
390      ELSIF(v_auxiliar <= 880) THEN
391        v_clasa := '11F';
392        v_temporar := 11;
393      ELSIF(v_auxiliar <= 920) THEN
394        v_clasa := '11G';
395        v_temporar := 11;
396      ELSIF(v_auxiliar <= 960) THEN
397        v_clasa := '11H';
398        v_temporar := 11;
399      ELSIF(v_auxiliar <= 1000) THEN
400        v_clasa := '10E';
401        v_temporar := 10;
402      ELSIF(v_auxiliar <= 1040) THEN
403        v_clasa := '10F';
404        v_temporar := 10;
405      ELSIF(v_auxiliar <= 1080) THEN
406        v_clasa := '10G';
407        v_temporar := 10;
408      ELSIF(v_auxiliar <= 1120) THEN
409        v_clasa := '10H';
410        v_temporar := 10;
411      ELSIF(v_auxiliar <= 1160) THEN
412        v_clasa := '9E';
413        v_temporar := 9;
414      ELSIF(v_auxiliar <= 1200) THEN
415        v_clasa := '9F';
416        v_temporar := 9;
417      ELSIF(v_auxiliar <= 1240) THEN
418        v_clasa := '9G';
419        v_temporar := 9;
420      ELSIF(v_auxiliar <= 1280) THEN
421        v_clasa := '9H';
422        v_temporar := 9;
423      END IF;
424    END IF;
425
426  v_numar_matricol := v_nr_matricol_auxiliar; 
427  v_nume_parinte := v_nume_elev;
428  
429--  DBMS_OUTPUT.PUT_LINE(v_index||' '|| v_nume_elev||' '||v_prenume_elev ||' '||v_numar_matricol||' ' || v_profil || ' ' ||v_clasa);  
430  
431  insert into elevi1 values(v_index,v_nume_elev,v_prenume_elev,v_numar_matricol,v_profil,v_clasa );
432  
433  IF (v_auxiliar <= 120) THEN
434    FOR v_index1 IN 1..lista_materii_clasa_12_real.COUNT LOOP
435      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(6,12));
436      FOR v_index2 IN 1..v_randomizare LOOP
437        insert into activitate1 values(v_index,v_index1,v_index1,sysdate,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)));
438      END LOOP;   
439      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(0,8))-1;
440      IF(v_randomizare > 0) THEN
441        FOR v_index2 IN 1..v_randomizare LOOP
442          insert into activitate1 values(v_index,v_index1,v_index1,sysdate,null);
443        END LOOP;
444      END IF;
445    END LOOP;
446    
447  ELSIF (v_auxiliar <= 240 ) THEN
448    FOR v_index1 IN 1..lista_materii_clasa_12_uman.COUNT LOOP
449      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(6,12));
450      FOR v_index2 IN 1..v_randomizare LOOP
451        insert into activitate1 values(v_index,v_index1,v_index1,sysdate,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)));
452      END LOOP;   
453      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(0,8))-1;
454      IF(v_randomizare > 0) THEN
455        FOR v_index2 IN 1..v_randomizare LOOP
456          insert into activitate1 values(v_index,v_index1,v_index1,sysdate,null);
457        END LOOP;
458      END IF;   
459    END LOOP;
460  ELSIF (v_auxiliar <= 360) THEN
461    FOR v_index1 IN 1..lista_materii_clasa_11_real.COUNT LOOP
462      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(6,12));
463      FOR v_index2 IN 1..v_randomizare LOOP
464        insert into activitate1 values(v_index,v_index1,v_index1,sysdate,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)));
465      END LOOP;    
466      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(0,8))-1;
467      IF(v_randomizare > 0) THEN
468        FOR v_index2 IN 1..v_randomizare LOOP
469          insert into activitate1 values(v_index,v_index1,v_index1,sysdate,null);
470        END LOOP;
471      END IF;  
472    END LOOP;
473  ELSIF (v_auxiliar <= 480) THEN
474    FOR v_index1 IN 1..lista_materii_clasa_11_uman.COUNT LOOP
475      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(6,12));
476      FOR v_index2 IN 1..v_randomizare LOOP
477        insert into activitate1 values(v_index,v_index1,v_index1,sysdate,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)));
478      END LOOP;   
479      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(0,8))-1;
480      IF(v_randomizare > 0) THEN
481        FOR v_index2 IN 1..v_randomizare LOOP
482          insert into activitate1 values(v_index,v_index1,v_index1,sysdate,null);
483        END LOOP;
484      END IF;   
485    END LOOP;
486  ELSIF (v_auxiliar <= 600) THEN
487    FOR v_index1 IN 1..lista_materii_clasa_10_real.COUNT LOOP
488      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(6,12));
489      FOR v_index2 IN 1..v_randomizare LOOP
490        insert into activitate1 values(v_index,v_index1,v_index1,sysdate,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)));
491      END LOOP;   
492    END LOOP;
493  ELSIF (v_auxiliar <= 720) THEN
494    FOR v_index1 IN 1..lista_materii_clasa_10_uman.COUNT LOOP
495      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(6,12));
496      FOR v_index2 IN 1..v_randomizare LOOP
497        insert into activitate1 values(v_index,v_index1,v_index1,sysdate,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)));
498      END LOOP;   
499      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(0,8))-1;
500      IF(v_randomizare > 0) THEN
501        FOR v_index2 IN 1..v_randomizare LOOP
502          insert into activitate1 values(v_index,v_index1,v_index1,sysdate,null);
503        END LOOP;
504      END IF;   
505    END LOOP;
506  ELSIF ( v_auxiliar <= 840 ) THEN
507    FOR v_index1 IN 1..lista_materii_clasa_9_real.COUNT LOOP
508      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(6,12));
509      FOR v_index2 IN 1..v_randomizare LOOP
510        insert into activitate1 values(v_index,v_index1,v_index1,sysdate,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)));
511      END LOOP;   
512      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(0,8))-1;
513      IF(v_randomizare > 0) THEN
514        FOR v_index2 IN 1..v_randomizare LOOP
515          insert into activitate1 values(v_index,v_index1,v_index1,sysdate,null);
516        END LOOP;
517      END IF;   
518    END LOOP;
519  ELSE
520    FOR v_index1 IN 1..lista_materii_clasa_9_uman.COUNT LOOP
521      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(6,12));
522      FOR v_index2 IN 1..v_randomizare LOOP
523        insert into activitate1 values(v_index,v_index1,v_index1,sysdate,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)));
524      END LOOP;   
525      v_randomizare := CEIL(SYS.DBMS_RANDOM.VALUE(0,8))-1;
526      IF(v_randomizare > 0) THEN
527        FOR v_index2 IN 1..v_randomizare LOOP
528          insert into activitate1 values(v_index,v_index1,v_index1,sysdate,null);
529        END LOOP;
530      END IF;   
531    END LOOP;
532  END IF;
533  v_auxiliar2 := SYS.DBMS_RANDOM.VALUE(0,1);
534  IF ( v_profil = 'uman' ) THEN
535    IF ( v_temporar = 9 ) THEN
536      insert into teze1 values(contor,v_index,61,61,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
537      insert into teze1 values(contor,v_index,59,59,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
538      insert into teze1 values(contor,v_index,63,63,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
539      IF (SYS.DBMS_RANDOM.VALUE(0,1) >= 0.5) THEN
540        insert into teze1 values(contor,v_index,70,70,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
541      ELSE
542        insert into teze1 values(contor,v_index,71,71,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
543      END IF;
544    END IF;
545    IF ( v_temporar = 10 ) THEN
546      insert into teze1 values(contor,v_index,76,76,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
547      insert into teze1 values(contor,v_index,74,74,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
548      insert into teze1 values(contor,v_index,78,78,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
549      IF (SYS.DBMS_RANDOM.VALUE(0,1) >= 0.5) THEN
550        insert into teze1 values(contor,v_index,85,85,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
551      ELSE
552        insert into teze1 values(contor,v_index,86,86,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
553      END IF;
554    END IF;
555    IF ( v_temporar = 11 ) THEN
556      insert into teze1 values(contor,v_index,91,91,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
557      insert into teze1 values(contor,v_index,89,89,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
558      insert into teze1 values(contor,v_index,92,92,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
559      IF (SYS.DBMS_RANDOM.VALUE(0,1) >= 0.5) THEN
560        insert into teze1 values(contor,v_index,96,96,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
561      ELSE
562        insert into teze1 values(contor,v_index,97,97,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
563      END IF;
564    END IF;
565    IF ( v_temporar = 12 ) THEN
566      insert into teze1 values(contor,v_index,102,102,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
567      insert into teze1 values(contor,v_index,100,100,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
568      insert into teze1 values(contor,v_index,103,103,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
569      IF (SYS.DBMS_RANDOM.VALUE(0,1) >= 0.5) THEN
570        insert into teze1 values(contor,v_index,107,107,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
571      ELSE
572        insert into teze1 values(contor,v_index,108,108,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
573      END IF;
574    END IF;
575  ELSE
576    IF ( v_temporar = 9 ) THEN
577      insert into teze1 values(contor,v_index,3,3,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
578      insert into teze1 values(contor,v_index,2,2,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
579      insert into teze1 values(contor,v_index,7,7,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
580      IF (v_auxiliar2 <= 0.334) THEN
581        insert into teze1 values(contor,v_index,4,4,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
582      ELSIF (v_auxiliar2 > 0.334 AND v_auxiliar2 <= 0.667) THEN
583        insert into teze1 values(contor,v_index,11,11,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);contor := contor + 1;
584      ELSE
585        insert into teze1 values(contor,v_index,16,16,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
586        contor := contor + 1;
587      END IF;
588    END IF;
589    IF ( v_temporar = 10 ) THEN
590      insert into teze1 values(contor,v_index,19,19,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
591      contor := contor + 1;
592      insert into teze1 values(contor,v_index,18,18,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
593      contor := contor + 1;
594      insert into teze1 values(contor,v_index,23,23,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
595      contor := contor + 1;
596      IF (v_auxiliar2 <= 0.334) THEN
597        insert into teze1 values(contor,v_index,20,20,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
598        contor := contor + 1;
599      ELSIF (v_auxiliar2 > 0.334 AND v_auxiliar2 <= 0.667) THEN
600        insert into teze1 values(contor,v_index,27,27,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
601        contor := contor + 1;
602      ELSE
603        insert into teze1 values(contor,v_index,32,32,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
604        contor := contor + 1;
605      END IF;
606    END IF;
607    IF ( v_temporar = 11 ) THEN
608      insert into teze1 values(contor,v_index,35,35,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
609      contor := contor + 1;
610      insert into teze1 values(contor,v_index,34,34,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
611      contor := contor + 1;
612      insert into teze1 values(contor,v_index,39,39,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
613      contor := contor + 1;
614      IF (v_auxiliar2 <= 0.334) THEN
615        insert into teze1 values(contor,v_index,36,36,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
616        contor := contor + 1;
617      ELSIF (v_auxiliar2 > 0.334 AND v_auxiliar2 <= 0.667) THEN
618        insert into teze1 values(contor,v_index,41,41,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
619        contor := contor + 1;
620      ELSE
621        insert into teze1 values(contor,v_index,41,41,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
622        contor := contor + 1;
623      END IF;
624    END IF;
625    IF ( v_temporar = 12 ) THEN
626      insert into teze1 values(contor,v_index,48,48,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
627      contor := contor + 1;
628      insert into teze1 values(contor,v_index,47,47,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
629      contor := contor + 1;
630      insert into teze1 values(contor,v_index,52,52,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
631      contor := contor + 1;
632      IF (v_auxiliar2 <= 0.334) THEN
633        insert into teze1 values(contor,v_index,49,49,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
634        contor := contor + 1;
635      ELSIF (v_auxiliar2 > 0.334 AND v_auxiliar2 <= 0.667) THEN
636        insert into teze1 values(contor,v_index,54,54,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
637        contor := contor + 1;
638      ELSE
639        insert into teze1 values(contor,v_index,58,58,CEIL(SYS.DBMS_RANDOM.VALUE(4,10)),sysdate);
640        contor := contor + 1;
641      END IF;
642    END IF;
643  END IF;
644  v_user := 'E.' || LOWER(v_nume_elev) || '.' || LOWER(v_prenume1_elev);
645  
646  IF(LENGTH(v_prenume2_elev) > 0) THEN
647    v_user := v_user || '.' || LOWER(v_prenume2_elev);
648  END IF;
649  
650  v_parola := TRUNC(DBMS_RANDOM.VALUE(100,999)) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90))) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90))) || TRUNC(DBMS_RANDOM.VALUE(100,999)) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90))) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90)));
651  
652  insert into accounts values(v_user,v_parola);
653  v_parola := '';
654  v_user := '';
655  
656-- INTEPEM INSERAREA PARINTILOR SI A RELATIILOR DE FAMILIE
657    
658    v_prenume1_parinte := prenume_baieti(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_baieti.COUNT)) + 1);
659    IF ((SYS.DBMS_RANDOM.VALUE(0,100) < 20 )) THEN
660      v_prenume2_parinte := prenume_baieti(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_baieti.COUNT)) + 1);
661      WHILE ( v_prenume1_parinte = v_prenume2_parinte) LOOP
662        v_prenume2_parinte := prenume_baieti(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_baieti.COUNT)) + 1);
663      END LOOP;
664    END IF;
665    v_prenume_parinte := v_prenume1_parinte || ' ' || v_prenume2_parinte;
666    
667    
668    insert into parinti1 values(2*v_index-1,v_index,v_nume_parinte,v_prenume_parinte);
669    v_user := 'Pa.' || LOWER(v_nume_parinte) || '.' || LOWER(v_prenume1_parinte);
670  
671    IF(LENGTH(v_prenume2_parinte) > 0) THEN
672      v_user := v_user || '.' || LOWER(v_prenume2_parinte);
673    END IF;
674  
675    v_parola := TRUNC(DBMS_RANDOM.VALUE(100,999)) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90))) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90))) || TRUNC(DBMS_RANDOM.VALUE(100,999)) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90))) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90)));
676  
677    insert into accounts values(v_user,v_parola);
678    v_parola := '';
679    v_user := '';
680  
681    v_prenume1_parinte := prenume_fete(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_fete.COUNT)) + 1);
682    IF ((SYS.DBMS_RANDOM.VALUE(0,100) < 20 )) THEN
683      v_prenume2_parinte := prenume_fete(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_fete.COUNT)) + 1);
684      WHILE ( v_prenume1_parinte = v_prenume2_parinte) LOOP
685        v_prenume2_parinte := prenume_fete(TRUNC(SYS.DBMS_RANDOM.VALUE(0,prenume_fete.COUNT)) + 1);
686      END LOOP;
687    END IF;
688    v_prenume_parinte := v_prenume1_parinte || ' ' || v_prenume2_parinte;
689    
690    insert into parinti1 values(2*v_index,v_index,v_nume_parinte,v_prenume_parinte);
691    
692    v_user := 'Pa.' || LOWER(v_nume_parinte) || '.' || LOWER(v_prenume1_parinte);
693  
694    IF(LENGTH(v_prenume2_parinte) > 0) THEN
695      v_user := v_user || '.' || LOWER(v_prenume2_parinte);
696    END IF;
697  
698    v_parola := TRUNC(DBMS_RANDOM.VALUE(100,999)) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90))) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90))) || TRUNC(DBMS_RANDOM.VALUE(100,999)) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90))) || CHR(CEIL(SYS.DBMS_RANDOM.VALUE(64,90)));
699  
700    insert into accounts values(v_user,v_parola);
701    v_parola := '';
702    v_user := '';
703    
704    insert into RELATII1 VALUES (2*v_index-1,v_index,2*v_index-1);
705    insert into RELATII1 VALUES (2*v_index,v_index,2*v_index);
706  v_auxiliar3 := v_auxiliar3 + 1;
707  v_nr_matricol_auxiliar := '';
708  v_auxiliar := v_auxiliar + 1;
709  v_prenume_elev := '';
710  v_prenume1_elev := '';
711  v_prenume2_elev := '';
712  v_nume_elev := '';
713  v_profil := '';
714  v_clasa := '';
715  v_numar_matricol := '';
716  
717  END LOOP;
718  DBMS_OUTPUT.PUT_LINE('<------GATA------>');
719END;
720/
721clear screen;
722/
723select * from elevi1;
724/
725select * from relatii1;
726/
727select * from parinti1;
728/
729select * from profesori1;
730/
731select * from accounts;
732/
733select * from materii1;
734/
735select * from teze1;
736/
737select * from activitate1;
738/
739select count(*) from activitate1;
740/
741select count(*) from elevi1;
742/
743select count(*) from relatii1;
744/
745select count(*) from parinti1;
746/