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