Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PROCEDURE generuj_dane
- AUTHID CURRENT_USER AS
- data_start DATE := TO_DATE('2004-01-01');
- data_stop DATE := TO_DATE('2020-01-20');
- data_temp DATE := nast_sroda(start_date);
- insert_date DATE;
- average_mouses NUMBER;
- myszy_created NUMBER := 1;
- myszy_assigned NUMBER := 1;
- TYPE myszy_table IS TABLE OF myszy%rowtype INDEX BY BINARY_INTEGER;
- myszy_dodane myszy_table;
- TYPE kot_myszy IS RECORD (pseudo Kocury.pseudo%TYPE, myszy NUMBER);
- TYPE koty_table IS TABLE OF kot_myszy INDEX BY BINARY_INTEGER;
- koty koty_table;
- BEGIN
- WHILE start_date < ending_date LOOP
- SELECT AVG(przydzial_myszy + NVL(myszy_extra,0)) INTO average_mouses FROM kocury WHERE w_stadku_od <= start_date;
- SELECT pseudo, przydzial_myszy + NVL(myszy_extra,0) BULK COLLECT INTO koty FROM kocury WHERE w_stadku_od <= start_date;
- -- PRZYDZIAL MYSZY DO TABELI - kazdy kot upolowal srednia
- FOR i IN 1..koty.count LOOP
- FOR j IN 1..average_mouses LOOP
- insert_date := start_date + DBMS_RANDOM.VALUE(0, TRUNC(last_wed) - TRUNC(start_date));
- IF (insert_date < ending_date) THEN
- myszy_dodane(myszy_created).nr_myszy := myszy_created;
- myszy_dodane(myszy_created).lowca := koty(i).pseudo;
- myszy_dodane(myszy_created).waga_myszy := DBMS_RANDOM.VALUE(2,10);
- myszy_dodane(myszy_created).data_zlowienia := insert_date;
- myszy_created := myszy_created + 1;
- END IF;
- END LOOP;
- END LOOP;
- -- wydajemy myszy, kazdy kocur tyle ile ma przydzielone
- IF (last_wed < ending_date) THEN
- FOR i IN 1..koty.COUNT LOOP
- FOR j IN 1..koty(i).myszy LOOP
- IF (myszy_created > myszy_assigned) THEN
- myszy_dodane(myszy_assigned).zjadacz := koty(i).pseudo;
- myszy_dodane(myszy_assigned).data_wydania := last_wed;
- myszy_assigned := myszy_assigned + 1;
- END IF;
- END LOOP;
- END LOOP;
- WHILE myszy_assigned < myszy_created LOOP
- myszy_dodane(myszy_assigned).zjadacz := 'TYGRYS';
- myszy_dodane(myszy_assigned).data_wydania := last_wed;
- myszy_assigned := myszy_assigned + 1;
- END LOOP;
- END IF;
- -- CHECK: sprawdzenie czy wydane zostały wszystkie myszy.
- -- DBMS_OUTPUT.PUT_LINE('Nowych myszy: ' || myszy_created || ' Myszy wydanych: ' || myszy_assigned);
- -- ustalamy nowy przedzial
- start_date := last_wed + 1;
- IF(EXTRACT(MONTH FROM start_date) = EXTRACT(MONTH FROM last_wed)) THEN
- last_wed := get_last_wed(ADD_MONTHS(start_date,1));
- ELSE
- last_wed := get_last_wed(start_date);
- END IF;
- END LOOP;
- FORALL i IN 1..myszy_dodane.COUNT SAVE EXCEPTIONS
- INSERT INTO myszy VALUES (myszy_dodane(i).nr_myszy,myszy_dodane(i).lowca,myszy_dodane(i).zjadacz,myszy_dodane(i).waga_myszy,
- myszy_dodane(i).data_zlowienia,myszy_dodane(i).data_wydania);
- EXCEPTION
- WHEN others THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
- myszy_created:=SQL%BULK_EXCEPTIONS.COUNT;
- FOR i IN 1..myszy_created
- LOOP
- DBMS_OUTPUT.PUT_LINE('Blad '||i||': myszka '||
- SQL%BULK_EXCEPTIONS(i).error_index||' - '||
- SQLERRM(-SQL%BULK_EXCEPTIONS(i).error_code));
- END LOOP;
- END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement