-
Notifications
You must be signed in to change notification settings - Fork 0
/
lista4_setup.sql
673 lines (576 loc) · 35.4 KB
/
lista4_setup.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
-- ctrl + f8 = enable dbms output
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'ENGLISH';
-- zad. 47 Założyć, że w stadzie kotów pojawił się podział na elitę i na plebs.
-- -- Członek elity posiadał prawo do jednego sługi wybranego spośród plebsu.
-- -- Dodatkowo mógł gromadzić myszy na dostępnym dla każdego członka elity koncie.
-- -- Konto ma zawierać dane o dacie wprowadzenia na nie pojedynczej myszy i o dacie jej usunięcia.
-- -- O tym, do kogo należy mysz ma mówić odniesienie do jej właściciela z elity.
-- -- Przyjmując te dodatkowe założenia zdefiniować schemat bazy danych kotów (bez odpowiedników relacji Funkcje, Bandy, Wrogowie)
-- -- w postaci relacyjno-obiektowej, gdzie dane dotyczące kotów, elity, plebsu. kont, incydentów będą określane przez odpowiednie typy obiektowe.
-- -- Dla każdego z typów zaproponować i zdefiniować przykładowe metody.
-- -- Powiązania referencyjne należy zdefiniować za pomocą typów odniesienia.
-- -- Tak przygotowany schemat wypełnić danymi z rzeczywistości kotów (dane do opisu elit, plebsu i kont zaproponować samodzielnie)
-- -- a następnie wykonać przykładowe zapytania SQL, operujące na rozszerzonym schemacie bazy,
-- -- wykorzystujące referencje (jako realizacje złączeń), podzapytania, grupowanie oraz metody zdefiniowane w ramach typów.
-- -- Dla każdego z mechanizmów (referencja, podzapytanie, grupowanie) należy przedstawić jeden taki przykład.
-- -- Zrealizować dodatkowo, w ramach nowego, relacyjno-obiektowego schematu, po dwa wybrane zadania z list nr 2 i 3.
DROP TABLE KOCUR_OBJ;
DROP TABLE ELITA_OBJ;
DROP TABLE PLEBS_OBJ;
DROP TABLE KONTO_OBJ;
DROP TABLE INCYDENT_OBJ;
DROP TYPE KONTO_TYPE;
DROP TYPE INCYDENT_TYPE;
DROP TYPE ELITA_TYPE;
DROP TYPE PLEBS_TYPE;
DROP TYPE KOCUR_TYPE;
CREATE OR REPLACE TYPE KOCUR_TYPE AS OBJECT (
imie VARCHAR2(15),
plec VARCHAR2(1),
pseudo VARCHAR2(15),
funkcja VARCHAR2(10),
szef REF KOCUR_TYPE,
w_stadku_od DATE,
przydzial_myszy NUMBER(3),
myszy_extra NUMBER(3),
nr_bandy NUMBER(2),
MEMBER FUNCTION suma_myszy RETURN NUMBER,
MEMBER FUNCTION pseudo_szefa RETURN NUMBER
);
CREATE OR REPLACE TYPE PLEBS_TYPE AS OBJECT (
nr_plebsu NUMBER,
KOCUR REF KOCUR_TYPE,
MEMBER FUNCTION ilu_elitarnym_sluzy RETURN NUMBER
);
CREATE OR REPLACE TYPE ELITA_TYPE AS OBJECT (
nr_elity NUMBER,
KOCUR REF KOCUR_TYPE,
sluga REF PLEBS_TYPE,
MEMBER FUNCTION get_KOCUR RETURN KOCUR_TYPE,
MEMBER FUNCTION imie_kota RETURN VARCHAR2
);
CREATE OR REPLACE TYPE KONTO_TYPE AS OBJECT (
nr_konta NUMBER,
wlasciciel REF ELITA_TYPE,
data_wprowadzenia DATE,
data_usuniecia DATE,
MEMBER FUNCTION pseudo_wlasciciela RETURN VARCHAR2
);
CREATE OR REPLACE TYPE INCYDENT_TYPE AS OBJECT (
nr_incydentu NUMBER(3),
KOCUR REF KOCUR_TYPE,
imie_wroga VARCHAR2(25),
data_incydentu DATE,
opis_incydentu VARCHAR2(50),
MEMBER FUNCTION ile_lat_od_incydentu RETURN NUMBER
);
CREATE TABLE KOCUR_OBJ OF KOCUR_TYPE (
CONSTRAINT obj_req_kocury CHECK(imie IS NOT NULL),
CONSTRAINT obj_check_kocury CHECK (plec IN ('M', 'D')),
CONSTRAINT obj_pk_kocury PRIMARY KEY(pseudo),
CONSTRAINT obj_fk_szef szef SCOPE IS KOCUR_OBJ
);
CREATE TABLE PLEBS_OBJ OF PLEBS_TYPE (
CONSTRAINT obj_pk_plebs PRIMARY KEY (nr_plebsu),
CONSTRAINT obj_fk_plebs_KOCUR KOCUR SCOPE IS KOCUR_OBJ
);
CREATE TABLE ELITA_OBJ OF ELITA_TYPE (
CONSTRAINT obj_pk_elita PRIMARY KEY (nr_elity),
CONSTRAINT obj_fk_elita_KOCUR KOCUR SCOPE IS KOCUR_OBJ,
CONSTRAINT obj_fk_sluga sluga SCOPE IS PLEBS_OBJ
);
CREATE TABLE KONTO_OBJ OF KONTO_TYPE (
CONSTRAINT obj_pk_konto PRIMARY KEY (nr_konta),
CONSTRAINT obj_fk_konto_elita wlasciciel SCOPE IS ELITA_OBJ,
CONSTRAINT obj_reg_konto CHECK(data_wprowadzenia IS NOT NULL),
CONSTRAINT obj_reg_konto2 CHECK(data_wprowadzenia <= data_usuniecia)
);
CREATE TABLE INCYDENT_OBJ OF INCYDENT_TYPE (
CONSTRAINT obj_fk_incydent_kocury KOCUR SCOPE IS KOCUR_OBJ,
CONSTRAINT obj_req_incdyent CHECK(data_incydentu IS NOT NULL),
CONSTRAINT obj_pk_incydent PRIMARY KEY (nr_incydentu)
);
CREATE OR REPLACE TYPE BODY KOCUR_TYPE IS
MEMBER FUNCTION suma_myszy RETURN NUMBER IS
BEGIN
RETURN NVL(przydzial_myszy, 0) + NVL(myszy_extra, 0);
END;
MEMBER FUNCTION pseudo_szefa RETURN NUMBER IS
tmp VARCHAR2(255);
BEGIN
SELECT DEREF(szef).pseudo into tmp from dual;
RETURN tmp;
END;
END;
CREATE OR REPLACE TYPE BODY ELITA_TYPE IS
MEMBER FUNCTION get_KOCUR RETURN KOCUR_TYPE IS
tmp KOCUR_TYPE;
BEGIN
SELECT DEREF(KOCUR) INTO tmp FROM DUAL;
RETURN tmp;
END;
MEMBER FUNCTION imie_kota RETURN VARCHAR2 IS BEGIN
RETURN SELF.get_KOCUR().imie;
END;
END;
CREATE OR REPLACE TYPE BODY KONTO_TYPE IS
MEMBER FUNCTION pseudo_wlasciciela RETURN VARCHAR2 IS
tmp VARCHAR2(255);
BEGIN
SELECT DEREF(DEREF(wlasciciel).KOCUR).pseudo INTO tmp FROM DUAL;
RETURN tmp;
END;
END;
CREATE OR REPLACE TYPE BODY INCYDENT_TYPE IS
MEMBER FUNCTION ile_lat_od_incydentu RETURN NUMBER IS
BEGIN
RETURN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM data_incydentu);
END;
END;
CREATE OR REPLACE TYPE BODY PLEBS_TYPE IS
MEMBER FUNCTION ilu_elitarnym_sluzy RETURN NUMBER IS
suma NUMBER;
BEGIN
SELECT COUNT(*) into suma FROM ELITA_OBJ elita WHERE elita.sluga.KOCUR.PSEUDO = (SELECT DEREF(SELF.KOCUR).PSEUDO FROM dual);
return suma;
END;
END;
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('MRUCZEK', 'M', 'TYGRYS', 'SZEFUNIO', NULL, '2002-01-01', 103, 33, 1));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('MICKA', 'D', 'LOLA', 'MILUSIA', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='TYGRYS'), '2009-10-14', 25, 47, 1));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('CHYTRY', 'M', 'BOLEK', 'DZIELCZY', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='TYGRYS'), '2002-05-05', 50, NULL, 1));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('KOREK', 'M', 'ZOMBI', 'BANDZIOR', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='TYGRYS'), '2004-03-16', 75, 13, 3));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('BOLEK', 'M', 'LYSY', 'BANDZIOR', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='TYGRYS'), '2006-08-15', 72, 21, 2));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('RUDA', 'D', 'MALA', 'MILUSIA', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='TYGRYS'), '2006-09-17', 22, 42, 1));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('PUCEK', 'M', 'RAFA', 'LOWCZY', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='TYGRYS'), '2006-10-15', 65, NULL, 4));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('SONIA', 'D', 'PUSZYSTA', 'MILUSIA', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='ZOMBI'), '2010-11-18', 20, 35, 3));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('PUNIA', 'D', 'KURKA', 'LOWCZY', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='ZOMBI'), '2008-01-01', 61, NULL, 3));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('JACEK', 'M', 'PLACEK', 'LOWCZY', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='LYSY'), '2008-12-01', 67, NULL, 2));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('BARI', 'M', 'RURA', 'LAPACZ', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='LYSY'), '2009-09-01', 56, NULL, 2));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('ZUZIA', 'D', 'SZYBKA', 'LOWCZY', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='LYSY'), '2006-07-21', 65, NULL, 2));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('BELA', 'D', 'LASKA', 'MILUSIA', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='LYSY'), '2008-02-01', 24, 28, 2));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('LATKA', 'D', 'UCHO', 'KOT', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='RAFA'), '2011-01-01', 40, NULL, 4));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('DUDEK', 'M', 'MALY', 'KOT', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='RAFA'), '2011-05-15', 40, NULL, 4));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('KSAWERY', 'M', 'MAN', 'LAPACZ', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='RAFA'), '2008-07-12', 51, NULL, 4));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('MELA', 'D', 'DAMA', 'LAPACZ', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='RAFA'), '2008-11-01', 51, NULL, 4));
INSERT INTO KOCUR_OBJ VALUES (KOCUR_TYPE('LUCEK', 'M', 'ZERO', 'KOT', (SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='KURKA'), '2010-03-01', 43, NULL, 3));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(1,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='TYGRYS'), 'KAZIO', '2004-10-13', 'USILOWAL NABIC NA WIDLY'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(2,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='ZOMBI'), 'SWAWOLNY DYZIO', '2005-03-07', 'WYBIL OKO Z PROCY'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(3,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='BOLEK'), 'KAZIO', '2005-03-29', 'POSZCZUL BURKIEM'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(4,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='SZYBKA'), 'GLUPIA ZOSKA', '2006-09-12', 'UZYLA KOTA JAKO SCIERKI'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(5,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='MALA'), 'CHYTRUSEK', '2007-03-07', 'ZALECAL SIE'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(6,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='TYGRYS'), 'DZIKI BILL', '2007-06-12', 'USILOWAL POZBAWIC ZYCIA'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(7,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='BOLEK'), 'DZIKI BILL', '2007-11-10', 'ODGRYZL UCHO'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(8,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='LASKA'), 'DZIKI BILL', '2008-12-12', 'POGRYZL ZE LEDWO SIE WYLIZALA'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(9,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='LASKA'), 'KAZIO', '2009-01-07', 'ZLAPAL ZA OGON I ZROBIL WIATRAK'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(10,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='DAMA'), 'KAZIO', '2009-02-07', 'CHCIAL OBEDRZEC ZE SKORY'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(11,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='MAN'), 'REKSIO', '2009-04-14', 'WYJATKOWO NIEGRZECZNIE OBSZCZEKAL'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(12,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='LYSY'), 'BETHOVEN', '2009-05-11', 'NIE PODZIELIL SIE SWOJA KASZA'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(13,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='RURA'), 'DZIKI BILL', '2009-09-03', 'ODGRYZL OGON'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(14,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='PLACEK'), 'BAZYLI', '2010-07-12', 'DZIOBIAC UNIEMOZLIWIL PODEBRANIE KURCZAKA'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(15,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='PUSZYSTA'), 'SMUKLA', '2010-11-19', 'OBRZUCILA SZYSZKAMI'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(16,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='KURKA'), 'BUREK', '2010-12-14', 'POGONIL'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(17,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='MALY'), 'CHYTRUSEK', '2011-07-13', 'PODEBRAL PODEBRANE JAJKA'));
INSERT INTO INCYDENT_OBJ VALUES (INCYDENT_TYPE(18,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='UCHO'), 'SWAWOLNY DYZIO', '2011-07-14', 'OBRZUCIL KAMIENIAMI'));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(1,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='LOLA')));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(2,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='BOLEK')));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(3,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='MALA')));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(4,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='PUSZYSTA')));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(5,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='PLACEK')));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(6,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='RURA')));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(7,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='SZYBKA')));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(8,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='LASKA')));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(9,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='UCHO')));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(10,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='MALY')));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(11,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='MAN')));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(12,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='DAMA')));
INSERT INTO PLEBS_OBJ VALUES (PLEBS_TYPE(13,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='ZERO')));
INSERT INTO ELITA_OBJ VALUES (ELITA_TYPE(1,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='TYGRYS'),(SELECT REF(plebs) FROM PLEBS_OBJ plebs WHERE plebs.KOCUR.PSEUDO='LOLA')));
INSERT INTO ELITA_OBJ VALUES (ELITA_TYPE(2,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='LYSY'),(SELECT REF(plebs) FROM PLEBS_OBJ plebs WHERE plebs.KOCUR.PSEUDO='LASKA')));
INSERT INTO ELITA_OBJ VALUES (ELITA_TYPE(3,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='ZOMBI'),(SELECT REF(plebs) FROM PLEBS_OBJ plebs WHERE plebs.KOCUR.PSEUDO='PUSZYSTA')));
INSERT INTO ELITA_OBJ VALUES (ELITA_TYPE(4,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='RAFA'),(SELECT REF(plebs) FROM PLEBS_OBJ plebs WHERE plebs.KOCUR.PSEUDO='UCHO')));
INSERT INTO ELITA_OBJ VALUES (ELITA_TYPE(5,(SELECT REF(KOCUR) FROM KOCUR_OBJ KOCUR WHERE KOCUR.pseudo='KURKA'),(SELECT REF(plebs) FROM PLEBS_OBJ plebs WHERE plebs.KOCUR.PSEUDO='ZERO')));
INSERT INTO KONTO_OBJ VALUES (KONTO_TYPE(1,(SELECT REF(elita) FROM ELITA_OBJ elita WHERE elita.KOCUR.pseudo='TYGRYS'),'2017-05-11','2017-06-12'));
INSERT INTO KONTO_OBJ VALUES (KONTO_TYPE(2,(SELECT REF(elita) FROM ELITA_OBJ elita WHERE elita.KOCUR.pseudo='LYSY'),'2017-05-11','2017-06-12'));
INSERT INTO KONTO_OBJ VALUES (KONTO_TYPE(3,(SELECT REF(elita) FROM ELITA_OBJ elita WHERE elita.KOCUR.pseudo='ZOMBI'),'2017-05-11','2017-06-12'));
INSERT INTO KONTO_OBJ VALUES (KONTO_TYPE(4,(SELECT REF(elita) FROM ELITA_OBJ elita WHERE elita.KOCUR.pseudo='RAFA'),'2017-05-11','2017-06-12'));
INSERT INTO KONTO_OBJ VALUES (KONTO_TYPE(5,(SELECT REF(elita) FROM ELITA_OBJ elita WHERE elita.KOCUR.pseudo='KURKA'),'2017-05-11','2017-06-12'));
INSERT INTO KONTO_OBJ VALUES (KONTO_TYPE(6,(SELECT REF(elita) FROM ELITA_OBJ elita WHERE elita.KOCUR.pseudo='TYGRYS'),'2017-05-12',null));
INSERT INTO KONTO_OBJ VALUES (KONTO_TYPE(7,(SELECT REF(elita) FROM ELITA_OBJ elita WHERE elita.KOCUR.pseudo='LYSY'),'2017-05-12',null));
INSERT INTO KONTO_OBJ VALUES (KONTO_TYPE(8,(SELECT REF(elita) FROM ELITA_OBJ elita WHERE elita.KOCUR.pseudo='ZOMBI'),'2017-05-12',null));
-- Zad. 48 Rozszerzyć relacyjną bazę danych kotów o dodatkowe relacje opisujące elitę, plebs i konta elity (patrz opis z zad. 47)
-- -- a następnie zdefiniować "nakładkę", w postaci perspektyw obiektowych (bez odpowiedników relacji Funkcje, Bandy, Wrogowie),
-- -- na tak zmodyfikowaną bazę. Odpowiadające relacjom typy obiektowe mają zawierać przykładowe metody (mogą to być metody z zad. 47).
-- -- Zamodelować wszystkie powiązania referencyjne z wykorzystaniem identyfikatorów OID i funkcji MAKE_REF.
-- -- Relacje wypełnić przykładowymi danymi (mogą to być dane z zad. 47).
-- -- Dla tak przygotowanej bazy wykonać wszystkie zapytania SQL i bloki PL/SQL zrealizowane w ramach zad. 47.
DROP TABLE ELITY CASCADE CONSTRAINTS;
DROP TABLE PLEBSY CASCADE CONSTRAINTS;
DROP TABLE KONTA CASCADE CONSTRAINTS;
CREATE TABLE PLEBSY
(
nr_plebsu NUMBER CONSTRAINT pk_plebsu PRIMARY KEY,
kocur VARCHAR2(15) CONSTRAINT fk_plebsy_kocury REFERENCES KOCURY(pseudo)
);
CREATE TABLE ELITY
(
nr_elity NUMBER CONSTRAINT pk_elity PRIMARY KEY,
kocur VARCHAR2(15) CONSTRAINT fk_elity_kocury REFERENCES KOCURY(pseudo),
sluga NUMBER CONSTRAINT fk_elity_plebsy REFERENCES PLEBSY(nr_plebsu)
);
CREATE TABLE KONTA
(
nr_konta NUMBER CONSTRAINT pk_konta PRIMARY KEY,
wlasciciel NUMBER CONSTRAINT fk_konta_elity REFERENCES ELITY(nr_elity),
data_wprowadzenia DATE CONSTRAINT req_konta NOT NULL,
data_usuniecia DATE,
CONSTRAINT check_konta_1 CHECK(data_wprowadzenia <= data_usuniecia)
);
INSERT ALL
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (1,'LOLA')
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (2,'BOLEK')
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (3,'MALA')
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (4,'PUSZYSTA')
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (5,'PLACEK')
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (6,'RURA')
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (7,'SZYBKA')
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (8,'LASKA')
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (9,'UCHO')
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (10,'MALY')
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (11,'MAN')
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (12,'DAMA')
INTO PLEBSY(NR_PLEBSU,KOCUR) VALUES (13,'ZERO')
INTO ELITY(nr_elity,kocur,sluga) VALUES(1,'TYGRYS',1)
INTO ELITY(nr_elity,kocur,sluga) VALUES(2,'LYSY',8)
INTO ELITY(nr_elity,kocur,sluga) VALUES(3,'ZOMBI',4)
INTO ELITY(nr_elity,kocur,sluga) VALUES(4,'RAFA',9)
INTO ELITY(nr_elity,kocur,sluga) VALUES(5,'KURKA',13)
INTO KONTA(nr_konta,wlasciciel,data_wprowadzenia,data_usuniecia) VALUES (1,1,'2017-05-11','2017-06-12')
INTO KONTA(nr_konta,wlasciciel,data_wprowadzenia,data_usuniecia) VALUES (2,2,'2017-05-11','2017-06-12')
INTO KONTA(nr_konta,wlasciciel,data_wprowadzenia,data_usuniecia) VALUES (3,3,'2017-05-11','2017-06-12')
INTO KONTA(nr_konta,wlasciciel,data_wprowadzenia,data_usuniecia) VALUES (4,4,'2017-05-11','2017-06-12')
INTO KONTA(nr_konta,wlasciciel,data_wprowadzenia,data_usuniecia) VALUES (5,5,'2017-05-11','2017-06-12')
INTO KONTA(nr_konta,wlasciciel,data_wprowadzenia,data_usuniecia) VALUES (6,1,'2017-05-12',null)
INTO KONTA(nr_konta,wlasciciel,data_wprowadzenia,data_usuniecia) VALUES (7,2,'2017-05-12',null)
INTO KONTA(nr_konta,wlasciciel,data_wprowadzenia,data_usuniecia) VALUES (8,3,'2017-05-12',null)
SELECT * FROM dual;
CREATE OR REPLACE FORCE VIEW KOCURY_NAKLADKA OF KOCUR_TYPE
WITH OBJECT IDENTIFIER (pseudo) AS
SELECT imie,
plec,
pseudo,
funkcja,
MAKE_REF(KOCURY_NAKLADKA, szef),
w_stadku_od,
przydzial_myszy,
myszy_extra,
nr_bandy
FROM KOCURY;
CREATE OR REPLACE VIEW INCYDENTY_NAKLADKA OF INCYDENT_TYPE
WITH OBJECT IDENTIFIER (nr_incydentu) AS
SELECT row_number() over (order by data_incydentu),
MAKE_REF(KOCURY_NAKLADKA, pseudo) pseudo,
imie_wroga,
data_incydentu,
opis_incydentu
FROM WROGOWIE_KOCUROW;
CREATE OR REPLACE VIEW PLEBS_NAKLADKA OF PLEBS_TYPE
WITH OBJECT IDENTIFIER (nr_plebsu) AS
SELECT nr_plebsu,
MAKE_REF(KOCURY_NAKLADKA, kocur) kocur
FROM PLEBSY;
CREATE OR REPLACE VIEW ELITA_NAKLADKA OF ELITA_TYPE
WITH OBJECT IDENTIFIER (nr_elity) AS
SELECT nr_elity,
MAKE_REF(KOCURY_NAKLADKA, kocur) kocur,
MAKE_REF(PLEBS_NAKLADKA, sluga) sluga
FROM ELITY;
CREATE OR REPLACE VIEW KONTA_NAKLADKA OF KONTO_TYPE
WITH OBJECT IDENTIFIER (nr_konta) AS
SELECT nr_konta,
MAKE_REF(ELITA_NAKlADKA, wlasciciel) wlasciciel,
data_wprowadzenia,
data_usuniecia
FROM KONTA;
-- zad. 49 W związku z wejściem do Unii Europejskiej konieczna stała się szczegółowa ewidencja myszy upolowanych i spożywanych.
-- -- Należało więc odnotowywać zarówno kota, który mysz upolował (wraz z datą upolowania) jak i kota,
-- -- który mysz zjadł (wraz z datą „wypłaty”). Dodatkowo istotna stała się waga myszy (waga ta musi spełniać Unijną normę (normę tę proszę ustalić)). C
-- -- o najgorsze, jednak, dane należało uzupełnić w tył zaczynając od 1 stycznia 2004.
-- -- Niestety, jak to czasami bywa, nastąpiło „niewielkie” opóźnienie w realizacji programu ewidencjonującego upolowane i zjedzone myszy.
-- -- Dziwnym zbiegiem okoliczności ewidencja ta stała się możliwa dopiero na dzień przed terminem oddawania bieżącej listy.
-- -- Napisać blok (bloki), który zrealizuje ewidencję, a więc:
-- -- a. zmodyfikuje schemat bazy danych o nową relację Myszy z atrybutami:
-- -- nr_myszy (klucz główny), lowca (klucz obcy), zjadacz (klucz obcy), waga_myszy, data_zlowienia, data_wydania (zawsze ostatnia środa miesiąca),
-- -- b. wypełni relację Myszy sztucznie wygenerowanymi danymi, od 1 stycznia 2004 począwszy,
-- -- na dniu poprzednim w stosunku do terminu oddania bieżącej listy skończywszy.
-- -- Liczba wpisanych myszy, upolowanych w konkretnym miesiącu, ma być zgodna z liczbą myszy,
-- -- które koty otrzymały w ramach „wypłaty” w tym miesiącu (z uwzględnieniem myszy extra).
-- -- W trakcie uzupełniania danych należy przyjąć założenie, że każdy kot jest w stanie upolować w ciągu miesiąca
-- -- liczbę myszy równą liczbie myszy spożywanych średnio w ciągu miesiąca przez każdego kota
-- -- („zagospodarować” ewentualne nadwyżki związane z zaokrągleniami).
-- -- Daty złowienia myszy mają być ustawione „w miarę” równomiernie w ciągu całego miesiąca. Datą wydania ma być ostatnia środa każdego miesiąca.
-- -- W rozwiązaniu należy wykorzystać pierwotny dynamiczny SQL (tworzenie nowej relacji) oraz pierwotne wiązanie masowe (wypełnianie relacji wygenerowanymi danymi).
--
-- -- Od daty bieżącej począwszy mają być już wpisywane rzeczywiste dane dotyczące upolowanych myszy.
-- -- Należy więc przygotować procedurę, która umożliwi przyjęcie na stan myszy upolowanych w ciągu dnia przez konkretnego kota
-- -- (założyć, że dane o upolowanych w ciągu dnia myszach dostępne są w, indywidualnej dla każdego kota, zewnętrznej relacji)
-- -- oraz procedurę, która umożliwi co miesięczną wypłatę (myszy mają być przydzielane po jednej kolejnym kotom
-- -- w kolejności zgodnej z pozycją kota w hierarchii stada aż do uzyskania przysługującego przydziału
-- -- lub do momentu wyczerpania się zapasów). W obu procedurach należy wykorzystać pierwotne wiązanie masowe.
declare
CURSOR kursor is select table_name
from user_tables
where table_name like 'KONTO\_%' ESCAPE '\';
begin
for rekord in kursor
loop
execute immediate 'drop table ' || rekord.table_name;
end loop;
end;
-- CREATE OR REPLACE PROCEDURE stworz_tabele_myszy AUTHID CURRENT_USER IS
-- czy_tabela_myszy_istnieje NUMBER := 0;
-- BEGIN
-- select count(*) into czy_tabela_myszy_istnieje from user_tables where table_name = 'MYSZY';
-- IF czy_tabela_myszy_istnieje <> 0 then
-- EXECUTE IMMEDIATE 'drop table myszy cascade constraints';
-- end if;
--
-- EXECUTE IMMEDIATE 'CREATE TABLE MYSZY(
-- nr_myszy NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) CONSTRAINT pk_myszy PRIMARY KEY,
-- lowca VARCHAR2(15) CONSTRAINT fk_myszy_kocury_1 REFERENCES KOCURY(pseudo),
-- zjadacz VARCHAR2(15) CONSTRAINT fk_myszy_kocury_2 REFERENCES KOCURY(pseudo),
-- waga_myszy NUMBER(3) CONSTRAINT check_myszy_1 CHECK (waga_myszy BETWEEN 10 AND 100),
-- data_zlowienia DATE CONSTRAINT req_myszy_1 NOT NULL,
-- data_wydania DATE,
-- CONSTRAINT check_myszy_2 CHECK(data_zlowienia <= data_wydania)
-- )';
--
-- EXCEPTION
-- WHEN OTHERS
-- THEN
-- DBMS_OUTPUT.PUT_LINE(SQLERRM);
-- END;
-- wypełnij danymi archiwalnymi
-- 2019-01-17 - czwartek
-- 2004-01-01 - czwartek
DECLARE
TYPE wiersz_kota IS RECORD (pseudo kocury.pseudo%TYPE, myszy NUMBER(3));
TYPE tab_kotow IS TABLE OF wiersz_kota INDEX BY BINARY_INTEGER;
TYPE wiersz_myszy IS RECORD (lowca kocury.pseudo%type, zjadacz kocury.pseudo%TYPE, waga_myszy number(3),
data_zlowienia date, data_wydania date);
TYPE tab_myszy IS TABLE OF wiersz_myszy INDEX BY BINARY_INTEGER;
koty tab_kotow;
dane_myszy tab_myszy;
pierwszy_dzien_lowienia DATE := '2004-01-01'; --pierwszy dzień łowienia po ostatniej wypłacie
dzien_wyplaty DATE := (next_day(last_day('2004-01-01') - 7, 'WEDNESDAY')); -- pierwsza ostatnia środa miesiąca po pierwszym dniu łowienia
liczba_myszy_w_miesiacu NUMBER; --suma myszy którą powinny dostać koty w miesiącu, uwzględnia tylko koty które od bieżącego pierwszego dnia łowienia były w stadzie
myszy_do_rozdania BINARY_INTEGER := 0;
rozdane_myszy BINARY_INTEGER := 0;
ostatni_dzien_ewidencji DATE := '2019-01-16'; -- dzien przed oddaniem listy na zajęciach
czy_tabela_myszy_istnieje NUMBER := 0;
BEGIN
select count(*) into czy_tabela_myszy_istnieje from user_tables where table_name = 'MYSZY';
IF czy_tabela_myszy_istnieje <> 0 then
EXECUTE IMMEDIATE 'drop table myszy cascade constraints';
end if;
EXECUTE IMMEDIATE 'CREATE TABLE MYSZY(
nr_myszy NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) CONSTRAINT pk_myszy PRIMARY KEY,
lowca VARCHAR2(15) CONSTRAINT fk_myszy_kocury_1 REFERENCES KOCURY(pseudo),
zjadacz VARCHAR2(15) CONSTRAINT fk_myszy_kocury_2 REFERENCES KOCURY(pseudo),
waga_myszy NUMBER(3) CONSTRAINT check_myszy_1 CHECK (waga_myszy BETWEEN 10 AND 100),
data_zlowienia DATE CONSTRAINT req_myszy_1 NOT NULL,
data_wydania DATE,
CONSTRAINT check_myszy_2 CHECK(data_zlowienia <= data_wydania)
)';
-- powtarzaj do ostatnie wypłaty przed ostatnim dniem ewidencji
WHILE dzien_wyplaty <= (next_day(last_day(ostatni_dzien_ewidencji) - 7, 'WEDNESDAY'))
LOOP
-- wybierz koty które od bieżącego pierwszego dnia łowienia były w stadzie
SELECT pseudo,
nvl(przydzial_myszy, 0) + nvl(myszy_extra, 0)
BULK COLLECT INTO koty
FROM kocury
WHERE w_stadku_od <= pierwszy_dzien_lowienia;
-- policz średnią liczbę myszy którą powinien złowić każdy kot
SELECT CEIL(AVG(nvl(przydzial_myszy, 0) + nvl(myszy_extra, 0)))
INTO liczba_myszy_w_miesiacu
FROM kocury
WHERE w_stadku_od <= pierwszy_dzien_lowienia;
-- jeśli dzień wypłaty później niż ostatni dzień ewidencji to odpowiednio pomniejsz liczbę myszy do zaewidencjonowania
IF dzien_wyplaty > ostatni_dzien_ewidencji then
liczba_myszy_w_miesiacu :=
ROUND(liczba_myszy_w_miesiacu * (ostatni_dzien_ewidencji - pierwszy_dzien_lowienia) / 30);
end if;
--dla każdego kota przydziel tyle myszy ile powinien dostać w miesiącu
FOR j IN 1..koty.COUNT
LOOP
FOR k IN 1..liczba_myszy_w_miesiacu
LOOP
dane_myszy(myszy_do_rozdania).lowca := koty(j).pseudo;
dane_myszy(myszy_do_rozdania).waga_myszy := dbms_random.value(10, 100);
-- date zlowienia ustaw w dopuszczalnym przedziale
IF dzien_wyplaty > ostatni_dzien_ewidencji then
dane_myszy(myszy_do_rozdania).data_zlowienia := TRUNC(pierwszy_dzien_lowienia + dbms_random.value(0,
ostatni_dzien_ewidencji - pierwszy_dzien_lowienia));
else
dane_myszy(myszy_do_rozdania).data_zlowienia :=
TRUNC(pierwszy_dzien_lowienia + dbms_random.value(0, dzien_wyplaty - pierwszy_dzien_lowienia));
end if;
-- po każdej utworzonej myszy zwiększ licznik myszy do rozdania
myszy_do_rozdania := myszy_do_rozdania + 1;
END LOOP;
END LOOP;
IF dzien_wyplaty <= ostatni_dzien_ewidencji then
-- każdemu kotu przydziel tyle myszy ile powinien dostć w danym miesiącu
FOR j IN 1..koty.COUNT
LOOP
FOR k IN 1..koty(j).myszy
LOOP
dane_myszy(rozdane_myszy).data_wydania := dzien_wyplaty;
dane_myszy(rozdane_myszy).zjadacz := koty(j).pseudo;
rozdane_myszy := rozdane_myszy + 1;
END LOOP;
END LOOP;
-- wszystkie nadmiarowe myszy (CEIL) daj Tygrysowi
WHILE rozdane_myszy < myszy_do_rozdania
LOOP
dane_myszy(rozdane_myszy).data_wydania := dzien_wyplaty;
dane_myszy(rozdane_myszy).zjadacz := 'TYGRYS';
rozdane_myszy := rozdane_myszy + 1;
END LOOP;
END IF;
-- ustaw nowy pierwszy dzień łowienia i wypłaty
pierwszy_dzien_lowienia := dzien_wyplaty + 1;
dzien_wyplaty := (next_day(last_day(add_months(dzien_wyplaty, 1)) - 7, 'WEDNESDAY'));
END LOOP;
-- wstaw naraz wszystko do bazy (ok 160k rekordów)
FORALL j IN 0..(dane_myszy.count - 1) SAVE EXCEPTIONS
INSERT INTO myszy(lowca, zjadacz, waga_myszy, data_zlowienia, data_wydania)
VALUES (dane_myszy(j).lowca, dane_myszy(j).zjadacz, dane_myszy(j).waga_myszy, dane_myszy(j).data_zlowienia,
dane_myszy(j).data_wydania);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
CREATE OR REPLACE PROCEDURE przechowaj_na_prywatnym_koncie(ps KOCURY.PSEUDO%TYPE, waga_myszy MYSZY.WAGA_MYSZY%TYPE,
data_zlowienia MYSZY.DATA_ZLOWIENIA%TYPE) AUTHID CURRENT_USER IS
nazwa_konta VARCHAR2(255) := 'KONTO_' || ps;
czy_konto_istnieje NUMBER := 0;
czy_kot_istnieje NUMBER := 0;
BEGIN
select count(*) into czy_kot_istnieje from KOCURY where pseudo = ps;
if czy_kot_istnieje = 0 then
DBMS_OUTPUT.PUT_LINE('Kot o pseudo ' || ps || 'nie istnieje.');
else
select count(*) into czy_konto_istnieje from user_tables where table_name = nazwa_konta;
if czy_konto_istnieje = 0 then
EXECUTE IMMEDIATE 'CREATE TABLE ' || nazwa_konta || '(
nr_myszy NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) CONSTRAINT pk_' || nazwa_konta || ' PRIMARY KEY,
waga_myszy NUMBER(3) CONSTRAINT check_' || nazwa_konta || ' CHECK (waga_myszy BETWEEN 10 AND 100),
data_zlowienia DATE CONSTRAINT req_' || nazwa_konta || ' NOT NULL)';
end if;
EXECUTE IMMEDIATE 'INSERT INTO ' || nazwa_konta || '(waga_myszy, data_zlowienia) VALUES (' || waga_myszy ||
', ''' ||
data_zlowienia || ''' )';
DBMS_OUTPUT.PUT_LINE('Dodano mysz na konto kota ' || ps);
end if;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;
CREATE OR REPLACE PROCEDURE przyjmij_myszy_na_stan(pseudo KOCURY.PSEUDO%TYPE) IS
TYPE wiersz_konta_kota IS RECORD (waga_myszy MYSZY.WAGA_MYSZY%TYPE, data_zlowienia MYSZY.DATA_ZLOWIENIA%TYPE);
TYPE tab_konta_kota IS TABLE OF wiersz_konta_kota INDEX BY BINARY_INTEGER;
zapasy_kota tab_konta_kota;
nazwa_konta VARCHAR2(255) := 'KONTO_' || pseudo;
czy_konto_istnieje NUMBER := 0;
BEGIN
select count(*) into czy_konto_istnieje from user_tables where table_name = nazwa_konta;
if czy_konto_istnieje > 0 then
-- EXECUTE IMMEDIATE 'insert into MYSZY(LOWCA, WAGA_MYSZY, DATA_ZLOWIENIA) select ''' || pseudo ||
-- ''', waga_myszy, data_zlowienia from ' || nazwa_konta;
EXECUTE IMMEDIATE 'select waga_myszy, data_zlowienia from ' || nazwa_konta bulk collect into zapasy_kota;
FORALL j IN 1..(zapasy_kota.count) SAVE EXCEPTIONS
INSERT INTO myszy(lowca, waga_myszy, data_zlowienia)
VALUES (pseudo, zapasy_kota(j).waga_myszy, zapasy_kota(j).data_zlowienia);
EXECUTE IMMEDIATE 'delete from ' || nazwa_konta;
DBMS_OUTPUT.PUT_LINE('Myszy przyjęto na stan');
else
DBMS_OUTPUT.PUT_LINE('Kot o wybranym pseudo nie posiada konta');
end if;
-- EXCEPTION
-- WHEN OTHERS
-- THEN
-- DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
CREATE OR REPLACE PROCEDURE wyplac_myszy(dzien_wyplaty DATE) IS
TYPE wiersz_kota IS RECORD (pseudo kocury.pseudo%TYPE, myszy NUMBER(3));
TYPE tab_kotow IS TABLE OF wiersz_kota INDEX BY BINARY_INTEGER;
TYPE wiersz_myszy IS RECORD (nr_myszy myszy.NR_MYSZY%TYPE);
TYPE tab_myszy IS TABLE OF wiersz_myszy INDEX BY BINARY_INTEGER;
TYPE wiersz_kolejka IS RECORD (nr_myszy myszy.NR_MYSZY%TYPE, pseudo kocury.pseudo%TYPE);
TYPE tab_kolejka IS TABLE OF wiersz_kolejka INDEX BY BINARY_INTEGER;
koty tab_kotow;
dane_myszy tab_myszy;
kolejka tab_kolejka;
dzien_poprzedniej_wyplaty DATE := (next_day(last_day(add_months(dzien_wyplaty, -1)) - 7, 'WEDNESDAY'));
ile_myszy_potrzeba NUMBER := 0;
wskaznik_kota NUMBER := 1;
bledna_data EXCEPTION;
BEGIN
if dzien_wyplaty <> (next_day(last_day(dzien_wyplaty) - 7, 'WEDNESDAY')) then
raise bledna_data;
end if;
SELECT NR_MYSZY bulk collect into dane_myszy
from myszy
where ZJADACZ is null
and DATA_WYDANIA is null
and DATA_ZLOWIENIA > dzien_poprzedniej_wyplaty
and DATA_ZLOWIENIA <= dzien_wyplaty
order by DATA_ZLOWIENIA, NR_MYSZY;
SELECT pseudo,
nvl(przydzial_myszy, 0) + nvl(myszy_extra, 0) "myszy"
BULK COLLECT INTO koty
FROM kocury
WHERE w_stadku_od <= dzien_wyplaty
START WITH szef IS NULL
CONNECT BY PRIOR pseudo = szef
ORDER BY LEVEL ASC, "myszy" DESC;
for i in 1..koty.COUNT
loop
ile_myszy_potrzeba := ile_myszy_potrzeba + koty(i).myszy;
end loop;
for i in 1..dane_myszy.COUNT
loop
if ile_myszy_potrzeba = 0 then
exit;
end if;
while koty(wskaznik_kota).myszy = 0
loop
wskaznik_kota := wskaznik_kota + 1;
if wskaznik_kota > koty.COUNT then
wskaznik_kota := 1;
end if;
end loop;
kolejka(i).pseudo := koty(wskaznik_kota).pseudo;
kolejka(i).nr_myszy := dane_myszy(i).nr_myszy;
koty(wskaznik_kota).myszy := koty(wskaznik_kota).myszy - 1;
ile_myszy_potrzeba := ile_myszy_potrzeba - 1;
wskaznik_kota := wskaznik_kota + 1;
if wskaznik_kota > koty.COUNT then
wskaznik_kota := 1;
end if;
end loop;
while kolejka.COUNT < dane_myszy.count
loop
kolejka(kolejka.COUNT + 1).pseudo := 'TYGRYS';
kolejka(kolejka.COUNT).nr_myszy := dane_myszy(kolejka.COUNT).nr_myszy;
end loop;
forall i in 1..kolejka.count SAVE EXCEPTIONS
UPDATE MYSZY m
set m.ZJADACZ = kolejka(i).pseudo,
m.DATA_WYDANIA = dzien_wyplaty
where NR_MYSZY = kolejka(i).nr_myszy;
END;