· 7 years ago · Dec 06, 2018, 06:38 PM
1-- IDS Projekt: Zadani c. 20 – Zkousky (IUS)
2-- Authors: Petr Buchal(xbucha02), Tomas Holik (xholik13)
3-- Date: 03/2017
4
5-- ORACLE
6-- generator posloupnosti cisel "1."=[klic] [r_cislo]
7-- "2."=[klic] [r_cislo]
8-- create sequence [atribut] START WITH [pocatecni velikost] INCREMENT BY [pri kazdem vytvoreni nove polozky o kolik zvetsit]
9
10-- MySQL
11-- u atributu AUTO_INCREMENT, pri kazdem vytvoreni polozky se zvedne o jedno
12
13
14--Delete tables
15--BEGIN
16 -- EXECUTE IMMEDIATE 'DROP TABLE Osoba, Student, Vyucujici, Predmet, Termin, Prihlaseni_na_termin, Termin_Zkousky, Otazka, Hodnoceni, Otazka_Hodnoceni, Termin_Otazka CASCADE CONSTRAINTS';
17 -- EXCEPTION
18 -- WHEN OTHERS THEN
19 -- IF SQLCODE != -942 THEN
20 -- RAISE;
21 -- END IF;
22--END;
23
24--drop and create sequences
25--BEGIN
26 -- EXECUTE IMMEDIATE 'DROP SEQUENCE id_osoba, id_kod_terminu, id_kod_predmetu, id_kod_zkousky, id_kod_hodnoceni, id_cislo_otazky';
27 -- EXCEPTION
28 -- WHEN OTHERS THEN
29 -- IF SQLCODE != -2289 THEN
30 -- RAISE;
31 -- END IF;
32
33 --EXECUTE IMMEDIATE 'CREATE SEQUENCE id_osoba, id_kod_terminu, id_kod_predmetu, id_kod_zkousky, id_cislo_otazky, id_kod_hodnoceni START WITH 1 INCREMENT BY 1 NO CYCLE';
34--END;
35
36--CREATE SEQUENCE id_osoba,id_kod_terminu, id_kod_predmetu, id_kod_zkousky, id_cislo_otazky, id_kod_hodnoceni START WITH 1 INCREMENT BY 1 NO CYCLE;
37-- prvni normalni forma - atomicke hodnoty
38-- druha normalni forma - kazdy neklicovy je plne funkce zavisly
39-- treti normalnio forma - neexistuje zadny neklicovy atribut, ktery je tranzitivne zavisly
40-- BCNF - pro kazdou netrivialni funkcni zavislost X->Y je X superklicem, to znamena, ze dva klicove pokud maji navzajem vztah vytvori se nova tabulka, aby se odstranila redundance
41
42
43DROP TABLE Osoba CASCADE constraints;
44DROP TABLE Student CASCADE constraints;
45DROP TABLE Osoba_Titul CASCADE constraints;
46DROP TABLE Vyucujici CASCADE constraints;
47DROP TABLE Predmet CASCADE constraints;
48DROP TABLE Zkouska CASCADE constraints;
49DROP TABLE Termin CASCADE constraints;
50DROP TABLE Prihlaseni_na_termin CASCADE constraints;
51DROP TABLE Otazka CASCADE constraints;
52DROP TABLE Hodnoceni CASCADE constraints;
53DROP TABLE Otazka_Hodnoceni CASCADE constraints;
54DROP TABLE Termin_Otazka CASCADE constraints;
55DROP TABLE Vyucujici_Predmetu CASCADE constraints;
56DROP TABLE Student_Predmet CASCADE constraints;
57
58
59DROP SEQUENCE id_osoba_seq;
60DROP SEQUENCE id_kod_terminu_seq;
61DROP SEQUENCE id_kod_zkousky_seq;
62DROP SEQUENCE id_kod_hodnoceni_seq;
63DROP SEQUENCE id_cislo_otazky_seq;
64
65CREATE SEQUENCE id_osoba_seq START WITH 1 INCREMENT BY 1 NOCYCLE;
66CREATE SEQUENCE id_kod_terminu_seq START WITH 1 INCREMENT BY 1 NOCYCLE;
67CREATE SEQUENCE id_kod_zkousky_seq START WITH 1 INCREMENT BY 1 NOCYCLE;
68CREATE SEQUENCE id_cislo_otazky_seq START WITH 1 INCREMENT BY 1 NOCYCLE;
69CREATE SEQUENCE id_kod_hodnoceni_seq START WITH 1 INCREMENT BY 1 NOCYCLE;
70
71--Create new tables
72CREATE TABLE Osoba (
73 id_osoba INT PRIMARY KEY,
74 jmeno varchar(120) NOT NULL,
75 prijmeni varchar(120) NOT NULL,
76 datum_narozeni varchar(80),
77 rodne_cislo varchar(60) NOT NULL
78);
79
80CREATE TABLE Osoba_Titul (
81 id_osoba_s_titulem INT NOT NULL PRIMARY KEY,
82 id_titul_pred varchar(50),
83 id_titul_za varchar(50),
84 CONSTRAINT con_osoba_s_titulem FOREIGN KEY(id_osoba_s_titulem) REFERENCES Osoba(id_osoba)
85);
86
87CREATE TABLE Student (
88 id_osoba INT NOT NULL PRIMARY KEY,
89 CONSTRAINT con_osoba_student FOREIGN KEY(id_osoba) REFERENCES Osoba(id_osoba)
90 ON DELETE CASCADE
91);
92
93CREATE TABLE Vyucujici (
94 id_osoba INT NOT NULL PRIMARY KEY,
95 CONSTRAINT con_osoba_vyucujici FOREIGN KEY(id_osoba) REFERENCES Osoba(id_osoba)
96 ON DELETE CASCADE
97);
98
99CREATE TABLE Predmet (
100 id_kod_predmetu varchar(20) NOT NULL PRIMARY KEY,
101 pocet_kreditu INT NOT NULL,
102 id_garant INT NOT NULL,
103 CONSTRAINT con_garant FOREIGN KEY(id_garant) REFERENCES Vyucujici(id_osoba)
104 ON DELETE CASCADE
105);
106
107CREATE TABLE Vyucujici_Predmetu (
108 id_vyucujici INT NOT NULL,
109 id_kod_predmetu varchar(20) NOT NULL,
110 CONSTRAINT con_vyucujici FOREIGN KEY(id_vyucujici) REFERENCES Vyucujici(id_osoba)
111 ON DELETE CASCADE,
112 CONSTRAINT con_predmet FOREIGN KEY(id_kod_predmetu) REFERENCES Predmet(id_kod_predmetu)
113 ON DELETE CASCADE
114);
115
116CREATE TABLE Zkouska (
117 id_kod_zkousky INT NOT NULL PRIMARY KEY,
118 typ varchar(64),
119 max_pocet_bodu float,
120 min_pocet_bodu_pro_slozeni float,
121 pocet_terminu INT,
122 max_pocet_terminu_pro_studenta INT,
123 id_kod_predmetu varchar(20) NOT NULL,
124 CONSTRAINT con_zkousky_predmet FOREIGN KEY(id_kod_predmetu) REFERENCES Predmet(id_kod_predmetu)
125 ON DELETE CASCADE
126);
127
128CREATE TABLE Termin (
129 id_kod_terminu INT NOT NULL PRIMARY KEY,
130 datum_cas varchar(64),
131 misto varchar(90),
132 zacatek_prihlasovani varchar(64),
133 konec_prihlasovani varchar(64),
134 max_pocet_studentu INT,
135 cislo_terminu INT NOT NULL,
136 id_kod_zkousky INT NOT NULL,
137 CONSTRAINT con_kod_zkousky FOREIGN KEY(id_kod_zkousky) REFERENCES Zkouska(id_kod_zkousky)
138 ON DELETE CASCADE
139);
140
141CREATE TABLE Prihlaseni_na_termin (
142 id_osoba INT,
143 id_kod_terminu INT NOT NULL,
144 CONSTRAINT con_prihlaseni_studenti FOREIGN KEY(id_osoba) REFERENCES Student(id_osoba),
145 CONSTRAINT con_na_termin FOREIGN KEY(id_kod_terminu) REFERENCES Termin(id_kod_terminu)
146);
147
148CREATE TABLE Otazka (
149 id_cislo_otazky INT NOT NULL PRIMARY KEY,
150 typ_otazky INT,
151 zadani_otazky varchar(1024),
152 max_pocet_bodu float,
153 min_pocet_bodu_pro_slozeni float
154);
155
156CREATE TABLE Hodnoceni (
157 id_kod_hodnoceni INT NOT NULL PRIMARY KEY,
158 pocet_bodu float NOT NULL,
159 procentualni_uspesnost float,
160 percentilova_uspesnost float,
161 komentar varchar(1024),
162 id_vyucujici INT NOT NULL,
163 id_student INT NOT NULL,
164 CONSTRAINT con_udeluje FOREIGN KEY(id_vyucujici) REFERENCES Vyucujici(id_osoba)
165 ON DELETE CASCADE,
166 CONSTRAINT con_jehodnocen FOREIGN KEY(id_student) REFERENCES Student(id_osoba)
167 ON DELETE CASCADE
168);
169
170CREATE TABLE Student_Predmet (
171id_student INT NOT NULL,
172id_predmet varchar(20) NOT NULL,
173CONSTRAINT con_student_v_predmetu FOREIGN KEY(id_student) REFERENCES Student(id_osoba),
174CONSTRAINT con_studovany_predmet FOREIGN KEY(id_predmet) REFERENCES Predmet(id_kod_predmetu)
175);
176
177CREATE TABLE Otazka_Hodnoceni (
178 id_cislo_otazky INT NOT NULL,
179 CONSTRAINT con_otazky FOREIGN KEY(id_cislo_otazky) REFERENCES Otazka(id_cislo_otazky),
180 id_kod_hodnoceni INT NOT NULL,
181 CONSTRAINT con_kod_hodnoceni FOREIGN KEY(id_kod_hodnoceni) REFERENCES Hodnoceni(id_kod_hodnoceni)
182);
183
184CREATE TABLE Termin_Otazka (
185 id_cislo_otazky INT NOT NULL,
186 CONSTRAINT con_cislo_otazky FOREIGN KEY(id_cislo_otazky) REFERENCES Otazka(id_cislo_otazky),
187 id_kod_terminu INT NOT NULL,
188 CONSTRAINT con_kod_terminu FOREIGN KEY(id_kod_terminu) REFERENCES Termin(id_kod_terminu)
189);
190
191CREATE OR REPLACE TRIGGER auto_increment_osoba BEFORE
192INSERT ON Osoba
193FOR EACH ROW
194BEGIN
195 SELECT id_osoba_seq.nextval
196 INTO :new.id_osoba
197 from dual;
198END;
199/
200
201CREATE OR REPLACE TRIGGER rodne_cislo_kontrola BEFORE INSERT OR UPDATE ON Osoba
202FOR EACH ROW
203DECLARE
204 den INTEGER;
205 r_cislo INTEGER;
206 mesic INTEGER;
207 rok INTEGER;
208 datum DATE;
209BEGIN
210 IF REGEXP_LIKE(:new.rodne_cislo, '^[0-9]{9,10}$') THEN
211 den := CAST(SUBSTR(:new.rodne_cislo, 5, 2) AS INTEGER);
212 mesic := CAST(SUBSTR(:new.rodne_cislo, 3, 2) AS INTEGER);
213 rok := CAST(SUBSTR(:new.rodne_cislo, 1, 2) AS INTEGER);
214
215 IF REGEXP_LIKE(:new.rodne_cislo, '^[0-9]{10}$') THEN
216 IF NOT ((MOD(:new.rodne_cislo,11)) = 0) THEN
217 RAISE_APPLICATION_ERROR(-20005, 'Rodne cislo '||:new.rodne_cislo||' je neplatne.');
218 END IF;
219 END IF;
220
221 IF (((mesic BETWEEN 1 AND 12) OR (mesic BETWEEN 51 AND 62) OR (mesic BETWEEN 21 AND 32) OR (mesic BETWEEN 71 AND 82)) AND (den BETWEEN 1 AND 31)) THEN
222 IF (LENGTH(:new.rodne_cislo) = 9 AND SUBSTR(:new.rodne_cislo, 7, 3) = '000') THEN
223 RAISE_APPLICATION_ERROR(-20001, 'Rodne cislo '||:new.rodne_cislo||' je neplatne.');
224 END IF;
225 IF (mesic BETWEEN 51 AND 62) THEN
226 mesic := mesic - 50;
227 END IF;
228 IF (mesic BETWEEN 21 AND 32) THEN
229 mesic := mesic - 20;
230 END IF;
231 IF (mesic BETWEEN 71 AND 82) THEN
232 mesic := mesic - 70;
233 END IF;
234
235 BEGIN
236 datum := den||'.'||mesic||'.'||rok;
237 EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20000, 'Rodne cislo '||:new.rodne_cislo||' je neplatne.');
238 END;
239 ELSE
240 RAISE_APPLICATION_ERROR(-20003, 'Rodne cislo '||:new.rodne_cislo||' je neplatne.');
241 END IF;
242 ELSE
243 RAISE_APPLICATION_ERROR(-20002, 'Rodne cislo '||:new.rodne_cislo||' je neplatne.');
244 END IF;
245END;
246/
247
248--Aktualizuje Otazku na zaklade id_cislo_otazky . Pokud zadany id_cislo_otazky neexistuje zapise na output, ze nebylo nic pridane. Pokud je NULL, hodi vyjimku, jestlize byla nalezena jina chyba naprikald ve formatu vyhodi obecnou vyjimku. Pokud uspesne vykona proceduru vrati na output, uspesne vykonanani
249CREATE OR REPLACE PROCEDURE update_otazka (id Otazka.id_cislo_otazky%type, typ Otazka.typ_otazky%type, zadani Otazka.zadani_otazky%type, max Otazka.max_pocet_bodu%type, min Otazka.min_pocet_bodu_pro_slozeni%type) AS
250BEGIN
251 DECLARE
252 test_na_id INT;
253 exception_not_exists EXCEPTION;
254 exception_null EXCEPTION;
255 BEGIN
256 IF(update_otazka.id IS NULL) THEN
257 RAISE exception_null;
258 END IF;
259
260 SELECT COUNT(*) INTO test_na_id FROM Otazka WHERE Otazka.id_cislo_otazky = update_otazka.id;
261
262 IF(test_na_id = 0) THEN
263 RAISE exception_not_exists;
264 END IF;
265
266 UPDATE Otazka
267 SET Otazka.typ_otazky = update_otazka.typ, Otazka.zadani_otazky = update_otazka.zadani, Otazka.max_pocet_bodu = update_otazka.max, Otazka.min_pocet_bodu_pro_slozeni = update_otazka.min
268 WHERE Otazka.id_cislo_otazky = update_otazka.id;
269 EXCEPTION
270 WHEN exception_not_exists THEN
271 dbms_output.put_line('Kvuli chybnemu zadani id_cislo_otazky nebyla otazka aktualizovana - otazka s danym id neexistuje.');
272 WHEN exception_null THEN
273 RAISE_APPLICATION_ERROR(-20030, 'Na miste id_cislo_otazky je NULL!');
274 WHEN OTHERS THEN
275 RAISE_APPLICATION_ERROR(-20040, 'Doslo k chybe pri chodu procedury. Spatny format?');
276 END;
277 dbms_output.put_line('Otazka byla aktualizovana');
278
279 IF(update_otazka.typ = 1) THEN
280 dbms_output.put_line('Typ otazky je nyni fulltext');
281 END IF;
282 IF(update_otazka.typ = 2) THEN
283 dbms_output.put_line('Typ otazky je nyni testove otazky');
284 END IF;
285 IF(update_otazka.typ = 3) THEN
286 dbms_output.put_line('Typ otazky je nyni pocetni priklad');
287 END IF;
288
289END;
290/
291
292--Procedura vyuzivajici kurzor
293--Vklada osobu s danym rodnym cislem, pokud se osoba jiz vyskytuje v databazi aktualizuje informace.
294--Nasledne vypise zda-li doslo k aktualizaci nebo ke vlozeni nove osoby.
295--Zaroven vlozi/aktualizuje osobu do tabulky studentu nebo ucitelu podle argumentu a do tabulky osoba_titul.
296CREATE OR REPLACE PROCEDURE insert_or_actualize_person(rodne_cislo Osoba.rodne_cislo%TYPE, jmeno Osoba.jmeno%TYPE, prijmeni Osoba.prijmeni%TYPE, datum Osoba.datum_narozeni%TYPE, typ VARCHAR, titul_pred Osoba_titul.id_titul_pred%TYPE, titul_za Osoba_titul.id_titul_za%TYPE) AS
297BEGIN
298 DECLARE
299 CURSOR osoba_seq IS SELECT last_number FROM all_sequences WHERE sequence_name = 'id_osoba_seq';
300 CURSOR person_exist_test IS
301 SELECT id_osoba, jmeno, prijmeni, datum_narozeni, rodne_cislo
302 FROM Osoba;
303 CURSOR student_exist_test IS
304 SELECT id_osoba
305 FROM student;
306 CURSOR vyucujici_exist_test IS
307 SELECT id_osoba
308 FROM vyucujici;
309 id_osoba_test INT;
310 id_osoba_st_vy INT;
311 id_osoba_exist INT;
312 jmeno VARCHAR(120);
313 prijmeni VARCHAR(120);
314 datum_narozeni VARCHAR(80);
315 rodne_cislo VARCHAR(60);
316 BEGIN
317 id_osoba_test := 0;
318 OPEN person_exist_test;
319 LOOP
320 FETCH person_exist_test INTO id_osoba_exist, jmeno, prijmeni, datum_narozeni, rodne_cislo;
321 EXIT WHEN person_exist_test%NOTFOUND;
322 IF(rodne_cislo = insert_or_actualize_person.rodne_cislo) THEN
323 id_osoba_test := 1;
324 UPDATE Osoba
325 SET Osoba.rodne_cislo = insert_or_actualize_person.rodne_cislo, Osoba.jmeno = insert_or_actualize_person.jmeno, Osoba.prijmeni = insert_or_actualize_person.prijmeni, Osoba.datum_narozeni = insert_or_actualize_person.datum
326 WHERE Osoba.id_osoba = id_osoba_exist;
327
328 UPDATE Osoba_titul
329 SET Osoba_titul.id_titul_pred = insert_or_actualize_person.titul_pred, Osoba_titul.id_titul_za = insert_or_actualize_person.titul_za
330 WHERE Osoba_titul.id_osoba_s_titulem = id_osoba_exist;
331
332 IF(insert_or_actualize_person.typ = 'Student') THEN
333 OPEN student_exist_test;
334 LOOP
335 FETCH student_exist_test INTO id_osoba_st_vy;
336 EXIT WHEN student_exist_test%NOTFOUND;
337 IF(id_osoba_exist = id_osoba_st_vy) THEN
338 id_osoba_test := 2;
339 END IF;
340 END LOOP;
341 CLOSE student_exist_test;
342 IF NOT(id_osoba_test = 2) THEN
343 INSERT INTO student(id_osoba) VALUES (id_osoba_exist);
344 END IF;
345 ELSE
346 OPEN vyucujici_exist_test;
347 LOOP
348 FETCH vyucujici_exist_test INTO id_osoba_st_vy;
349 EXIT WHEN vyucujici_exist_test%NOTFOUND;
350 IF(id_osoba_exist = id_osoba_st_vy) THEN
351 id_osoba_test := 2;
352 END IF;
353 END LOOP;
354 CLOSE vyucujici_exist_test;
355 IF NOT(id_osoba_test = 2) THEN
356 INSERT INTO vyucujici(id_osoba) VALUES (id_osoba_exist);
357 END IF;
358 END IF;
359 dbms_output.put_line('Doslo k aktualizaci osoby');
360 END IF;
361 END LOOP;
362 CLOSE person_exist_test;
363
364 IF(id_osoba_test = 0) THEN
365 INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,insert_or_actualize_person.datum,insert_or_actualize_person.jmeno,insert_or_actualize_person.prijmeni,insert_or_actualize_person.rodne_cislo);
366 OPEN osoba_seq;
367 FETCH osoba_seq INTO id_osoba_exist;
368 INSERT INTO Osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (id_osoba_exist+1,insert_or_actualize_person.titul_pred, insert_or_actualize_person.titul_za);
369 IF(insert_or_actualize_person.typ = 'Student') THEN
370 INSERT INTO student(id_osoba) VALUES (id_osoba_exist+1);
371 ELSE
372 INSERT INTO vyucujici(id_osoba) VALUES (id_osoba_exist+1);
373 END IF;
374 CLOSE osoba_seq;
375 dbms_output.put_line('Doslo k vlozeni osoby');
376 END IF;
377 END;
378END;
379/
380
381
382
383
384
385
386INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,'1950-07-08','Petr','Dvorak','500708019');
387INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,'1996-08-08','Jiri','Novak','9608080020');
388INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,'1942-04-10','Ondra','Valach','420410005');
389INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,'1997-10-21','Michal','Hodes','9709210104');
390INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,'1995-09-21','Petr','Stastny','9509210106');
391INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,'1995-09-28','Petr','Rohit','9509284928');
392INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,'1991-08-07','Domingo','Coenraad','9108070202');
393INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,'1990-09-15','Olufunmilola','Siemen','9009150106');
394INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,'1953-05-05','Petr','Prabhakara','530505005');
395INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,'1952-03-24','Zdenka','Premek','520324099');
396INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,'1992-04-15','Vladimira','Marketa','9204151000');
397INSERT INTO Osoba(id_osoba,datum_narozeni,jmeno,prijmeni,rodne_cislo) VALUES (NULL,'1996-12-24','Lubos','Katka','9612241001');
398
399INSERT INTO osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (12,NULL,NULL);
400INSERT INTO osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (11,NULL,NULL);
401INSERT INTO osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (10,'Ing.','Ph.D');
402INSERT INTO osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (9,'Ing.','Ph.D, CSc.');
403INSERT INTO osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (8,NULL,NULL);
404INSERT INTO osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (7,NULL,NULL);
405INSERT INTO osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (6,NULL,NULL);
406INSERT INTO osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (5,NULL,NULL);
407INSERT INTO osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (4,NULL,NULL);
408INSERT INTO osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (3,'Ing.','Ph.D, CSc.');
409INSERT INTO osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (2,NULL,NULL);
410INSERT INTO osoba_titul(id_osoba_s_titulem, id_titul_pred, id_titul_za) VALUES (1,'Ing.',NULL);
411
412INSERT INTO student(id_osoba) VALUES (12);
413INSERT INTO student(id_osoba) VALUES (11);
414INSERT INTO vyucujici(id_osoba) VALUES (10);
415INSERT INTO vyucujici(id_osoba) VALUES (9);
416INSERT INTO Student(id_osoba) VALUES (8);
417INSERT INTO Student(id_osoba) VALUES (7);
418INSERT INTO Student(id_osoba) VALUES (6);
419INSERT INTO Student(id_osoba) VALUES (5);
420INSERT INTO Student(id_osoba) VALUES (4);
421INSERT INTO Vyucujici(id_osoba) VALUES (3);
422INSERT INTO Student(id_osoba) VALUES (2);
423INSERT INTO Vyucujici(id_osoba) VALUES (1);
424
425INSERT INTO Predmet(id_kod_predmetu,pocet_kreditu,id_garant) VALUES ('IDS',5,3);
426INSERT INTO Predmet(id_kod_predmetu,pocet_kreditu,id_garant) VALUES ('IZP',7,1);
427
428INSERT INTO vyucujici_predmetu(id_vyucujici, id_kod_predmetu) VALUES (3, 'IDS');
429INSERT INTO vyucujici_predmetu(id_vyucujici, id_kod_predmetu) VALUES (9, 'IDS');
430INSERT INTO vyucujici_predmetu(id_vyucujici, id_kod_predmetu) VALUES (1, 'IZP');
431INSERT INTO vyucujici_predmetu(id_vyucujici, id_kod_predmetu) VALUES (10, 'IZP');
432
433
434INSERT INTO Zkouska(id_kod_zkousky,typ,max_pocet_bodu,min_pocet_bodu_pro_slozeni,pocet_terminu,max_pocet_terminu_pro_studenta,id_kod_predmetu) VALUES (id_kod_zkousky_seq.nextval,'Pulsemestralni zkouska',10,5,1,1,'IDS');
435INSERT INTO Zkouska(id_kod_zkousky,typ,max_pocet_bodu,min_pocet_bodu_pro_slozeni,pocet_terminu,max_pocet_terminu_pro_studenta,id_kod_predmetu) VALUES (id_kod_zkousky_seq.nextval,'Pulsemestralni zkouska',10,5,1,1,'IZP');
436
437INSERT INTO Termin(id_kod_terminu,datum_cas,misto,zacatek_prihlasovani,konec_prihlasovani,max_pocet_studentu,cislo_terminu,id_kod_zkousky) VALUES (id_kod_terminu_seq.nextval,'1996-07-08 22:00','E112','21.4.','28.4.',10,1,1);
438INSERT INTO Termin(id_kod_terminu,datum_cas,misto,zacatek_prihlasovani,konec_prihlasovani,max_pocet_studentu,cislo_terminu,id_kod_zkousky) VALUES (id_kod_terminu_seq.nextval,'1996-07-09 22:00','E112','22.4.','29.4.',10,1,2);
439
440INSERT INTO Otazka(id_cislo_otazky,max_pocet_bodu,min_pocet_bodu_pro_slozeni,typ_otazky,zadani_otazky) VALUES (id_cislo_otazky_seq.nextval,5,0,1,'Spocitej 1+18');
441INSERT INTO Otazka(id_cislo_otazky,max_pocet_bodu,min_pocet_bodu_pro_slozeni,typ_otazky,zadani_otazky) VALUES (id_cislo_otazky_seq.nextval,5,0,1,'Spocitej 18/2');
442INSERT INTO Otazka(id_cislo_otazky,max_pocet_bodu,min_pocet_bodu_pro_slozeni,typ_otazky,zadani_otazky) VALUES (id_cislo_otazky_seq.nextval,5,0,1,'Spocitej 18/9');
443INSERT INTO Otazka(id_cislo_otazky,max_pocet_bodu,min_pocet_bodu_pro_slozeni,typ_otazky,zadani_otazky) VALUES (id_cislo_otazky_seq.nextval,5,0,1,'Spocitej 18/3');
444
445INSERT INTO Student_Predmet(id_student,id_predmet) VALUES (2,'IDS');
446INSERT INTO Student_Predmet(id_student,id_predmet) VALUES (4,'IDS');
447INSERT INTO Student_Predmet(id_student,id_predmet) VALUES (5,'IDS');
448INSERT INTO Student_Predmet(id_student,id_predmet) VALUES (6,'IDS');
449INSERT INTO Student_Predmet(id_student,id_predmet) VALUES (7,'IZP');
450INSERT INTO Student_Predmet(id_student,id_predmet) VALUES (8,'IZP');
451INSERT INTO Student_Predmet(id_student,id_predmet) VALUES (8,'IDS');
452INSERT INTO Student_Predmet(id_student,id_predmet) VALUES (11,'IZP');
453INSERT INTO Student_Predmet(id_student,id_predmet) VALUES (12,'IZP');
454
455INSERT INTO hodnoceni(id_kod_hodnoceni,pocet_bodu,procentualni_uspesnost,percentilova_uspesnost,komentar,id_vyucujici,id_student) VALUES (id_kod_hodnoceni_seq.nextval,5,100,100,' ',1,7);
456INSERT INTO hodnoceni(id_kod_hodnoceni,pocet_bodu,procentualni_uspesnost,percentilova_uspesnost,komentar,id_vyucujici,id_student) VALUES (id_kod_hodnoceni_seq.nextval,5,100,100,' ',1,8);
457INSERT INTO hodnoceni(id_kod_hodnoceni,pocet_bodu,procentualni_uspesnost,percentilova_uspesnost,komentar,id_vyucujici,id_student) VALUES (id_kod_hodnoceni_seq.nextval,0,0,0,' ',1,11);
458INSERT INTO hodnoceni(id_kod_hodnoceni,pocet_bodu,procentualni_uspesnost,percentilova_uspesnost,komentar,id_vyucujici,id_student) VALUES (id_kod_hodnoceni_seq.nextval,5,100,100,' ',1,12);
459
460INSERT INTO hodnoceni(id_kod_hodnoceni,pocet_bodu,procentualni_uspesnost,percentilova_uspesnost,komentar,id_vyucujici,id_student) VALUES (id_kod_hodnoceni_seq.nextval,5,100,100,'',1,7);
461INSERT INTO hodnoceni(id_kod_hodnoceni,pocet_bodu,procentualni_uspesnost,percentilova_uspesnost,komentar,id_vyucujici,id_student) VALUES (id_kod_hodnoceni_seq.nextval,3,66.6,66.6,'',1,8);
462INSERT INTO hodnoceni(id_kod_hodnoceni,pocet_bodu,procentualni_uspesnost,percentilova_uspesnost,komentar,id_vyucujici,id_student) VALUES (id_kod_hodnoceni_seq.nextval,0,0,0,'',1,11);
463INSERT INTO hodnoceni(id_kod_hodnoceni,pocet_bodu,procentualni_uspesnost,percentilova_uspesnost,komentar,id_vyucujici,id_student) VALUES (id_kod_hodnoceni_seq.nextval,0,0,0,'',1,12);
464
465INSERT INTO otazka_hodnoceni(id_cislo_otazky,id_kod_hodnoceni) VALUES (1,1);
466INSERT INTO otazka_hodnoceni(id_cislo_otazky,id_kod_hodnoceni) VALUES (1,2);
467INSERT INTO otazka_hodnoceni(id_cislo_otazky,id_kod_hodnoceni) VALUES (1,3);
468INSERT INTO otazka_hodnoceni(id_cislo_otazky,id_kod_hodnoceni) VALUES (1,4);
469
470INSERT INTO otazka_hodnoceni(id_cislo_otazky,id_kod_hodnoceni) VALUES (2,5);
471INSERT INTO otazka_hodnoceni(id_cislo_otazky,id_kod_hodnoceni) VALUES (2,6);
472INSERT INTO otazka_hodnoceni(id_cislo_otazky,id_kod_hodnoceni) VALUES (2,7);
473INSERT INTO otazka_hodnoceni(id_cislo_otazky,id_kod_hodnoceni) VALUES (2,8);
474
475INSERT INTO prihlaseni_na_termin(id_kod_terminu,id_osoba) VALUES (1,7);
476INSERT INTO prihlaseni_na_termin(id_kod_terminu,id_osoba) VALUES (1,8);
477INSERT INTO prihlaseni_na_termin(id_kod_terminu,id_osoba) VALUES (1,11);
478INSERT INTO prihlaseni_na_termin(id_kod_terminu,id_osoba) VALUES (1,12);
479
480CALL update_otazka(1, 1, 'Co je treba splnit pri BNFS', 6, 1);
481CALL update_otazka(2, 2, 'Co je treba splnit pri BNFS'||chr(10)||'a) Druhou normalni formu'||chr(10)||'b) Treti normalni formu'||chr(10)||'c) Vse vyse', 6, 1);
482CALL update_otazka(3, 3, 'Normalizujte tabulky [c_stud, jmeno_s, zkratka, nazev, kredity]'||chr(10)||'[body, os_c_gar, jmeno_g, ak_rok]', 6, 1);
483CALL update_otazka(NULL, 1, 'Co je treba splnit pri BNFS', 6, 1);
484CALL update_otazka(100, 1, 'Co je treba splnit pri BNFS', 6, 1);
485
486
487CALL insert_or_actualize_person('9509284928', 'Tomas', 'Holik', '1995-09-28', 'Student', NULL, NULL);
488CALL insert_or_actualize_person('9004020201', 'Marek', 'Ciliak', '1990-04-02', 'Student', NULL, NULL);
489CALL insert_or_actualize_person('8001010204', 'Jozef', 'Kovacik', '1980-01-01', 'Vyucujici', 'Ing.', NULL);
490
491--poradi select
492--SELECT column(s)
493--FROM table_name(s)
494--WHERE condition
495--GROUP BY column(s)
496--HAVING condition
497--ORDER BY column(s);
498
499--vybere vsechny osoby s titulem
500SELECT osoba_titul.id_osoba_s_titulem, osoba_titul.id_titul_pred, osoba_titul.id_titul_za, Osoba.id_osoba, Osoba.jmeno, Osoba.prijmeni
501FROM osoba
502INNER JOIN Osoba_titul ON id_osoba_s_titulem = id_osoba AND (Osoba_titul.id_titul_za IS NOT NULL OR Osoba_titul.id_titul_pred IS NOT NULL);
503
504--vybere korespondujici predmet k danemu terminu
505SELECT t.id_kod_terminu, p.id_kod_predmetu
506FROM termin t
507INNER JOIN zkouska z ON t.id_kod_zkousky = z.id_kod_zkousky
508INNER JOIN predmet p ON z.id_kod_predmetu = p.id_kod_predmetu;
509
510--vybere vsechny Petry z osob, kteri studuji IDS
511SELECT osoba.*
512FROM Osoba, student_predmet
513WHERE jmeno IN('Petr') and osoba.id_osoba = student_predmet.id_student and student_predmet.id_predmet = 'IDS';
514
515--vybere vsechny studenty, kteri studuji jen IZP
516
517SELECT DISTINCT osoba.*
518FROM student, student_predmet, hodnoceni, osoba
519WHERE student.id_osoba = osoba.id_osoba AND student.id_osoba = student_predmet.id_student AND student_predmet.id_predmet = 'IZP' AND NOT EXISTS
520 (SELECT *
521 FROM student_predmet
522 WHERE student.id_osoba = student_predmet.id_student AND student_predmet.id_predmet<>'IZP');
523
524
525--pocet vyucujicich v jednotlivych predmetech, ktere maji vice jak jednoho vyucujiciho
526SELECT id_kod_predmetu, COUNT(id_vyucujici)
527FROM Vyucujici_Predmetu
528GROUP BY id_kod_predmetu
529HAVING COUNT(id_vyucujici) > 1
530ORDER BY COUNT(id_vyucujici) DESC;
531
532--pocet studentu prihlasenych na jednotlive terminy zkousek
533SELECT COUNT(p.id_osoba), p.id_kod_terminu
534FROM prihlaseni_na_termin p
535GROUP BY p.id_kod_terminu
536ORDER BY COUNT(p.id_osoba);
537
538--vybere vsechny vyucujici s titulem Ph.D a bez titulu CSc..
539SELECT osoba_titul.id_osoba_s_titulem, osoba_titul.id_titul_za, vyucujici.id_osoba
540FROM vyucujici, osoba_titul
541WHERE id_osoba = id_osoba_s_titulem AND id_titul_za like '%Ph.D%' AND id_titul_za not like '%CSc.%';
542
543--vybere vsechny studenty, kteri studuji IZP nebo IDS
544SELECT Osoba.id_osoba, Osoba.jmeno, Osoba.prijmeni
545FROM osoba
546WHERE osoba.id_osoba IN (SELECT student.id_osoba
547 FROM Student
548 INTERSECT
549 SELECT student_predmet.id_student
550 FROM Student_Predmet
551 WHERE id_predmet IN('IZP') OR id_predmet IN('IDS'));
552
553--vybere vsechny studenty, prihlasene na termin pulsemestralky z IDS, kteri jsou narozeni v roce 1996
554SELECT Osoba.id_osoba, Osoba.jmeno, Osoba.prijmeni
555FROM osoba
556WHERE to_char(osoba.datum_narozeni) LIKE '%96' AND osoba.id_osoba IN (SELECT id_osoba
557 FROM Prihlaseni_na_termin
558 WHERE id_kod_terminu IN (SELECT id_kod_terminu
559 FROM termin
560 WHERE id_kod_zkousky IN (SELECT id_kod_zkousky
561 FROM Zkouska
562 WHERE Zkouska.id_kod_predmetu in ('IDS') and zkouska.typ IN ('Pulsemestralni zkouska'))));
563
564
565EXPLAIN PLAN FOR
566SELECT O.jmeno, COUNT(*) pocet
567FROM Osoba O
568NATURAL JOIN Student S
569GROUP BY O.jmeno;
570
571SELECT PLAN_TABLE_OUTPUT FROM TABLE(dbms_xplan.display);
572
573CREATE INDEX PersonIndex ON Osoba (jmeno, prijmeni, rodne_cislo);
574
575EXPLAIN PLAN FOR
576SELECT O.jmeno, COUNT(*) pocet
577FROM Osoba O
578NATURAL JOIN Student S
579GROUP BY O.jmeno;
580
581SELECT PLAN_TABLE_OUTPUT FROM TABLE(dbms_xplan.display);
582
583DROP INDEX PersonIndex;
584
585-- Pristupy pro vyucujiciho
586GRANT SELECT ON Student TO XHOLIK13;
587GRANT SELECT ON Vyucujici TO XHOLIK13;
588GRANT SELECT ON Predmet TO XHOLIK13;
589GRANT SELECT ON Vyucujici_Predmetu TO XHOLIK13;
590GRANT SELECT,INSERT,DELETE ON Prihlaseni_na_termin TO XHOLIK13;
591GRANT SELECT,INSERT,DELETE ON Student_Predmet TO XHOLIK13;
592
593GRANT ALL ON Zkouska TO XHOLIK13;
594GRANT ALL ON Termin TO XHOLIK13;
595GRANT ALL ON Otazka TO XHOLIK13;
596GRANT ALL ON Hodnoceni TO XHOLIK13;
597GRANT ALL ON Otazka_Hodnoceni TO XHOLIK13;
598GRANT ALL ON Termin_Otazka TO XHOLIK13;
599
600-- Nutny View log pro fast refresh - vytvari snapshot, ktery refreshne materialized view
601DROP MATERIALIZED VIEW Count_Student_IDS;
602
603CREATE MATERIALIZED VIEW LOG ON Student_Predmet WITH ROWID(id_student,id_predmet) INCLUDING NEW VALUES;
604CREATE MATERIALIZED VIEW Count_Student_IDS
605 REFRESH FAST ON COMMIT
606 AS
607 SELECT Count(P.id_student) as "Pocet studentu", P.id_predmet as "Predmet" FROM student_predmet P GROUP BY id_predmet;
608
609GRANT ALL ON Count_Student_IDS TO XHOLIK13;
610
611SELECT * FROM Count_Student_IDS;
612INSERT INTO Student_Predmet(id_student,id_predmet) VALUES (12,'IZP');
613COMMIT;
614SELECT * FROM Count_Student_IDS;
615
616
617
618CREATE OR REPLACE FUNCTION GET_PK (TABLE_NAME VARCHAR) RETURN VARCHAR AS
619 PK VARCHAR(100);
620 BEGIN
621 SELECT column_name INTO PK FROM all_cons_columns WHERE constraint_name = (
622 SELECT constraint_name FROM user_constraints
623 WHERE table_name = GET_PK.TABLE_NAME AND CONSTRAINT_TYPE = 'P'
624 );
625 RETURN PK;
626 END;
627/
628
629CREATE or replace PROCEDURE UPDATE_TEMPORAL (TABLE_ VARCHAR, UPDATE_SET VARCHAR, WHERE_ VARCHAR) AS
630 NEW_ID INT;
631 OLD_ID INT;
632 PK VARCHAR(100);
633 QUERY_ VARCHAR(300);
634 BEGIN
635 PK := GET_PK(UPDATE_TEMPORAL.TABLE_);
636 QUERY_ := 'SELECT ID_TEMPORAL FROM ' || UPDATE_TEMPORAL.TABLE_ || ' AS OF PERIOD FOR ' || UPDATE_TEMPORAL.TABLE_ || '_VALID_TIME SYSTIMESTAMP WHERE ' || UPDATE_TEMPORAL.WHERE_;
637 EXECUTE IMMEDIATE QUERY_ INTO OLD_ID;
638 QUERY_ := 'INSERT INTO ' || UPDATE_TEMPORAL.TABLE_ || ' SELECT * FROM (SELECT * FROM ' || UPDATE_TEMPORAL.TABLE_ || ' WHERE ' || UPDATE_TEMPORAL.WHERE_ || ' ORDER BY ID_TEMPORAL ASC) WHERE ROWNUM=1';
639 dbms_output.put_line(QUERY_);
640 EXECUTE IMMEDIATE QUERY_;
641 QUERY_ := 'SELECT ' || UPDATE_TEMPORAL.TABLE_ || '_SEQUENCE.CURRVAL FROM DUAL';
642 EXECUTE IMMEDIATE QUERY_ INTO NEW_ID;
643 QUERY_ := 'UPDATE ' || UPDATE_TEMPORAL.TABLE_ || ' SET ' || UPDATE_SET || ', START_DATE=SYSTIMESTAMP, END_DATE=NULL WHERE ' || UPDATE_TEMPORAL.WHERE_ || ' AND ' || PK || ' = (SELECT MAX('|| PK || ') FROM ' || UPDATE_TEMPORAL.TABLE_ || ' WHERE ID_TEMPORAL=' || NEW_ID || ')'; -- ORDER BY ' || PK || ' DESC LIMIT 1)';
644 EXECUTE IMMEDIATE QUERY_;
645 dbms_output.put_line(QUERY_);
646-- UPDATE TABLE_ SET UPDATE_SET, START_DATE=SYSTIMESTAMP, END_DATE=NULL WHERE WHERE_ AND PK=( SELECT PK FORM TABLE_ WHERE WHERE_ ORDER BY PK DESC LIMIT 1;
647 QUERY_ := 'UPDATE ' || UPDATE_TEMPORAL.TABLE_ || ' SET END_DATE=SYSTIMESTAMP WHERE ' || UPDATE_TEMPORAL.WHERE_ || ' AND ' || PK || '=(SELECT ID_TEMPORAL FROM ( SELECT ID_TEMPORAL, ROW_NUMBER() OVER (ORDER BY ID_TEMPORAL DESC) ROW_NUM FROM ' || UPDATE_TEMPORAL.TABLE_|| ' WHERE ' || UPDATE_TEMPORAL.WHERE_ || ') t WHERE ROW_NUM=2)';
648 EXECUTE IMMEDIATE QUERY_;
649 dbms_output.put_line(QUERY_);
650 IF UPDATE_TEMPORAL.TABLE_ = 'PRODUCT' THEN
651 UPDATE INVENTORY SET ID_PRODUCT=NEW_ID WHERE ID_PRODUCT=OLD_ID AND END_DATE IS NULL;
652 UPDATE PRODUCT_IN_ROOM SET ID_PRODUCT=NEW_ID WHERE ID_PRODUCT=OLD_ID;
653 END IF;
654 COMMIT;
655 END;
656/
657
658CREATE OR REPLACE PROCEDURE INSERT_TEMPORAL (INSERT_TABLE VARCHAR, INSERT_CLAUSE VARCHAR) AS
659 QUERY_ VARCHAR(300);
660 PK VARCHAR(100);
661 TEMPORAL_ID_VALUE INTEGER;
662 BEGIN
663 dbms_output.put_line('QUERY: ');
664 PK := GET_PK(INSERT_TEMPORAL.INSERT_TABLE);
665 EXECUTE IMMEDIATE INSERT_TEMPORAL.INSERT_CLAUSE;
666 QUERY_ := 'SELECT ' || INSERT_TEMPORAL.INSERT_TABLE || '_SEQUENCE.CURRVAL FROM DUAL';
667 EXECUTE IMMEDIATE QUERY_ INTO temporal_id_value;
668 QUERY_ := 'UPDATE ' || INSERT_TEMPORAL.INSERT_TABLE || ' SET ID_' || INSERT_TEMPORAL.INSERT_TABLE || '=' || INSERT_TEMPORAL.INSERT_TABLE || '_TEMPORAL_ID_SEQUENCE.NEXTVAL, START_DATE=SYSTIMESTAMP, END_DATE=NULL WHERE ' || PK || '=' || TEMPORAL_ID_VALUE;
669 dbms_output.put_line('QUERY: ' || QUERY_);
670 EXECUTE IMMEDIATE QUERY_;
671 COMMIT;
672 END;
673/
674
675CREATE OR REPLACE PROCEDURE TEMPORAL_DELETE (TABLE_NAME VARCHAR, ID_TO_DELETE NUMBER) AS
676 QUERY_ VARCHAR(300);
677 BEGIN
678 QUERY_:= 'UPDATE ' || TEMPORAL_DELETE.TABLE_NAME || ' SET END_DATE=SYSTIMESTAMP WHERE ID_TEMPORAL=' || TEMPORAL_DELETE.ID_TO_DELETE;
679 EXECUTE IMMEDIATE QUERY_;
680 COMMIT;
681 END;
682/
683
684CREATE OR REPLACE FUNCTION TEMPORAL_SELECT_PERIOD (SELECT_COLUMN VARCHAR, TABLE_NAME VARCHAR, DATE_FROM VARCHAR, DATE_TO VARCHAR, TEMPORAL_JOIN_TYPE VARCHAR, TEMPORAL_JOIN_TABLE VARCHAR, TEMPORAL_JOIN_CONDITION VARCHAR, AFTER_TEMPORAL_JOIN VARCHAR) RETURN SYS_REFCURSOR AS
685 LC SYS_REFCURSOR;
686 QUERY_ VARCHAR(1024);
687 BEGIN
688 IF TEMPORAL_SELECT_PERIOD.TEMPORAL_JOIN_TABLE IS NULL THEN
689 QUERY_ := 'SELECT ' || TEMPORAL_SELECT_PERIOD.SELECT_COLUMN || ' FROM ' || TEMPORAL_SELECT_PERIOD.TABLE_NAME || ' VERSIONS PERIOD FOR ' || TEMPORAL_SELECT_PERIOD.TABLE_NAME || '_VALID_TIME BETWEEN TO_DATE(''' || TEMPORAL_SELECT_PERIOD.DATE_FROM || ''', ''YYYY-MM-DD'') AND TO_DATE(''' || TEMPORAL_SELECT_PERIOD.DATE_TO || ''', ''YYYY-MM-DD'') + INTERVAL ''1'' DAY ' || TEMPORAL_SELECT_PERIOD.AFTER_TEMPORAL_JOIN;
690 ELSE
691 QUERY_ := 'SELECT ' || TEMPORAL_SELECT_PERIOD.SELECT_COLUMN || ' FROM ' || TEMPORAL_SELECT_PERIOD.TABLE_NAME || ' VERSIONS PERIOD FOR ' || TEMPORAL_SELECT_PERIOD.TABLE_NAME || '_VALID_TIME BETWEEN DATE ''' || TEMPORAL_SELECT_PERIOD.DATE_FROM || ''' AND DATE ''' || TEMPORAL_SELECT_PERIOD.DATE_TO || ''' + INTERVAL ''1'' DAY ' || TEMPORAL_SELECT_PERIOD.TEMPORAL_JOIN_TYPE || ' ' || TEMPORAL_SELECT_PERIOD.TEMPORAL_JOIN_TABLE ||' VERSIONS PERIOD FOR ' || TEMPORAL_SELECT_PERIOD.TEMPORAL_JOIN_TABLE || '_VALID_TIME BETWEEN DATE ''' || TEMPORAL_SELECT_PERIOD.DATE_FROM || ''' AND DATE ''' || TEMPORAL_SELECT_PERIOD.DATE_TO || ''' ' || TEMPORAL_SELECT_PERIOD.TEMPORAL_JOIN_CONDITION || ' ' || TEMPORAL_SELECT_PERIOD.AFTER_TEMPORAL_JOIN;
692 END IF;
693 dbms_output.put_line('QUERY: ' || QUERY_);
694 OPEN LC FOR QUERY_;
695 RETURN LC;
696 END;
697/
698
699CREATE OR REPLACE FUNCTION TEMPORAL_SELECT_ACTIVE (SELECT_COLUMN VARCHAR, TABLE_NAME VARCHAR, TEMPORAL_JOIN_TYPE VARCHAR, TEMPORAL_JOIN_TABLE VARCHAR, TEMPORAL_JOIN_CONDITION VARCHAR, AFTER_TEMPORAL_JOIN VARCHAR) RETURN SYS_REFCURSOR AS
700 LC SYS_REFCURSOR;
701 QUERY_ VARCHAR(1024);
702 BEGIN
703 IF TEMPORAL_SELECT_ACTIVE.TEMPORAL_JOIN_TABLE IS NULL THEN
704 QUERY_ := 'SELECT ' || TEMPORAL_SELECT_ACTIVE.SELECT_COLUMN || ' FROM ' || TEMPORAL_SELECT_ACTIVE.TABLE_NAME || ' AS OF PERIOD FOR ' || TEMPORAL_SELECT_ACTIVE.TABLE_NAME || '_VALID_TIME SYSTIMESTAMP ' || TEMPORAL_SELECT_ACTIVE.AFTER_TEMPORAL_JOIN;
705 ELSE
706 QUERY_ := 'SELECT ' || TEMPORAL_SELECT_ACTIVE.SELECT_COLUMN || ' FROM ' || TEMPORAL_SELECT_ACTIVE.TABLE_NAME || ' AS OF PERIOD FOR ' || TEMPORAL_SELECT_ACTIVE.TABLE_NAME || '_VALID_TIME SYSTIMESTAMP ' || TEMPORAL_SELECT_ACTIVE.TEMPORAL_JOIN_TYPE || ' ' || TEMPORAL_SELECT_ACTIVE.TEMPORAL_JOIN_TABLE ||' AS OF PERIOD FOR ' || TEMPORAL_SELECT_ACTIVE.TEMPORAL_JOIN_TABLE || '_VALID_TIME SYSTIMESTAMP ' || TEMPORAL_SELECT_ACTIVE.TEMPORAL_JOIN_CONDITION || ' ' || TEMPORAL_SELECT_ACTIVE.AFTER_TEMPORAL_JOIN;
707 END IF;
708 dbms_output.put_line(QUERY_);
709 OPEN LC FOR QUERY_;
710 RETURN LC;
711 END;
712/
713
714COMMIT;