Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- INSERT INTO osoby (imie, nazwisko, pesel, kontakt)
- VALUES ('Adam', 'Kowalski', '87654321', 'tel: 6623');
- INSERT INTO osoby (imie, nazwisko, pesel, kontakt)
- VALUES ('Jan', 'Nowak', '12345678', 'tel: 2312, dzwonić po 18.00');
- INSERT INTO osoby (imie, nazwisko, pesel, kontakt)
- VALUES ('Piotr', 'Kędra', '13579246', 'tel: 6666');
- INSERT INTO osoby (imie, nazwisko, pesel, kontakt)
- VALUES ('Marcelina', 'Banaś', '99988877', 'tel: 2460');
- INSERT INTO osoby (imie, nazwisko, pesel, kontakt)
- VALUES ('Jan', 'Ilczuk', '11122233', 'tel: 4342');
- INSERT INTO osoby (imie, nazwisko, pesel, kontakt)
- VALUES ('Stacy', 'Noland', '44444444', 'tel: 6677');
- INSERT INTO osoby (imie, nazwisko, pesel, kontakt)
- VALUES ('Rachel', 'McAdams', '98765432', 'tel: 5555');
- INSERT INTO osoby (imie, nazwisko, pesel, kontakt)
- VALUES ('Monica', 'Belucci', '65654543', 'tel: 0976');
- INSERT INTO osoby (imie, nazwisko, pesel, kontakt)
- VALUES ('Rafał', 'Banaś', '65434343', 'tel: 4343');
- INSERT INTO osoby (imie, nazwisko, pesel, kontakt)
- VALUES ('Damian', 'Markowski', '95456569', 'tel: 1342');
- INSERT INTO wycieczki (nazwa, kraj, data, opis, liczba_miejsc)
- VALUES ('Wycieczka do Paryza', 'Francja', TO_DATE('2016-01-01', 'YYYY-MM-DD'), 'Ciekawa wycieczka ...', 3);
- INSERT INTO wycieczki (nazwa, kraj, data, opis, liczba_miejsc)
- VALUES ('Piękny Kraków', 'Polska', TO_DATE('2020-02-03', 'YYYY-MM-DD'), 'Najciekawa wycieczka ...', 2);
- INSERT INTO wycieczki (nazwa, kraj, data, opis, liczba_miejsc)
- VALUES ('Wieliczka', 'Polska', TO_DATE('2020-03-03', 'YYYY-MM-DD'), 'Zadziwiająca kopalnia ...', 2);
- INSERT INTO wycieczki (nazwa, kraj, data, opis, liczba_miejsc)
- VALUES ('Warszawa wyjazd', 'Polska', TO_DATE('2020-04-12', 'YYYY-MM-DD'), 'Miasto prezydentów ...', 4);
- INSERT INTO rezerwacje(id_wycieczki, id_osoby, status)
- VALUES (3, 1, 'N');
- INSERT INTO rezerwacje(id_wycieczki, id_osoby, status)
- VALUES (3, 2, 'P');
- INSERT INTO rezerwacje(id_wycieczki, id_osoby, status)
- VALUES (3, 3, 'A');
- INSERT INTO rezerwacje(id_wycieczki, id_osoby, status)
- VALUES (3, 4, 'Z');
- INSERT INTO rezerwacje(id_wycieczki, id_osoby, status)
- VALUES (4, 3, 'Z');
- INSERT INTO rezerwacje(id_wycieczki, id_osoby, status)
- VALUES (4, 5, 'P');
- INSERT INTO rezerwacje(id_wycieczki, id_osoby, status)
- VALUES (5, 6, 'N');
- INSERT INTO rezerwacje(id_wycieczki, id_osoby, status)
- VALUES (6, 7, 'P');
- INSERT INTO rezerwacje(id_wycieczki, id_osoby, status)
- VALUES (6, 8, 'N');
- INSERT INTO rezerwacje(id_wycieczki, id_osoby, status)
- VALUES (6, 9, 'P');
- CREATE OR REPLACE VIEW wycieczki_osoby
- AS
- SELECT w.ID_WYCIECZKI,
- w.NAZWA,
- w.KRAJ,
- w.DATA,
- o.IMIE,
- o.NAZWISKO,
- r.STATUS
- FROM WYCIECZKI w
- JOIN REZERWACJE r ON w.ID_WYCIECZKI = r.ID_WYCIECZKI
- JOIN OSOBY o ON r.ID_OSOBY = o.ID_OSOBY
- WHERE r.STATUS <> 'A';
- CREATE OR REPLACE VIEW wycieczki_osoby_potwierdzone
- AS
- SELECT w.ID_WYCIECZKI,
- w.NAZWA,
- w.KRAJ,
- w.DATA,
- o.IMIE,
- o.NAZWISKO,
- r.STATUS
- FROM WYCIECZKI w
- JOIN REZERWACJE r ON w.ID_WYCIECZKI = r.ID_WYCIECZKI
- JOIN OSOBY o ON r.ID_OSOBY = o.ID_OSOBY
- WHERE r.STATUS LIKE 'P';
- CREATE OR REPLACE VIEW wycieczki_przyszle
- AS
- SELECT w.ID_WYCIECZKI,
- w.NAZWA,
- w.KRAJ,
- w.DATA,
- o.IMIE,
- o.NAZWISKO,
- r.STATUS
- FROM WYCIECZKI w
- JOIN REZERWACJE r ON w.ID_WYCIECZKI = r.ID_WYCIECZKI
- JOIN OSOBY o ON r.ID_OSOBY = o.ID_OSOBY
- WHERE w.DATA > CURRENT_DATE;
- CREATE OR REPLACE VIEW wycieczki_miejsca
- AS
- SELECT w.ID_WYCIECZKI,
- w.NAZWA,
- w.KRAJ,
- w.DATA,
- w.LICZBA_MIEJSC,
- (w.LICZBA_MIEJSC - COUNT(DISTINCT r.ID_OSOBY)) as LICZBA_WOLNYCH_MIEJSC
- FROM WYCIECZKI w
- LEFT JOIN REZERWACJE r ON r.ID_WYCIECZKI = w.ID_WYCIECZKI
- where r.STATUS <> 'A'
- GROUP BY w.ID_WYCIECZKI, w.NAZWA, w.KRAJ, w.DATA, w.LICZBA_MIEJSC;
- CREATE OR REPLACE VIEW dostepne_wycieczki
- AS
- SELECT w.ID_WYCIECZKI,
- w.NAZWA,
- w.KRAJ,
- w.DATA,
- w.LICZBA_MIEJSC,
- w.LICZBA_WOLNYCH_MIEJSC
- FROM wycieczki_miejsca w
- WHERE w.LICZBA_WOLNYCH_MIEJSC > 0
- AND w.DATA > CURRENT_DATE;
- CREATE OR REPLACE VIEW rezerwacje_do_anulowania
- AS
- SELECT r.NR_REZERWACJI,
- o.IMIE,
- o.NAZWISKO,
- w.NAZWA,
- w.DATA,
- r.STATUS
- FROM OSOBY o
- JOIN REZERWACJE r on o.ID_OSOBY = r.ID_OSOBY
- JOIN WYCIECZKI w on r.ID_WYCIECZKI = w.ID_WYCIECZKI
- WHERE r.STATUS = 'N'
- AND w.DATA < (CURRENT_DATE - 7);
- ----------------------------------------------------------------
- CREATE OR REPLACE TYPE uczestnicy_wycieczki_r AS object
- (
- kraj VARCHAR(50),
- "data" DATE,
- nazwa_wycieczki VARCHAR(100),
- imie VARCHAR2(50),
- nazwisko VARCHAR2(50),
- status CHAR(1)
- );
- CREATE OR REPLACE TYPE uczestnicy_wycieczki_t IS TABLE OF uczestnicy_wycieczki_r;
- CREATE OR REPLACE FUNCTION uczestnicy_wycieczki(id_wycieczki WYCIECZKI.ID_WYCIECZKI%TYPE)
- RETURN uczestnicy_wycieczki_t as
- owt uczestnicy_wycieczki_t;
- exist integer;
- BEGIN
- SELECT COUNT(*) INTO exist FROM WYCIECZKI w WHERE w.ID_WYCIECZKI = uczestnicy_wycieczki.id_wycieczki;
- if exist = 0 THEN
- raise_application_error(-20001, 'Brak wycieczki o danym id');
- end if;
- SELECT uczestnicy_wycieczki_r(w.Kraj, w.DATA, w.NAZWA, o.IMIE, o.NAZWISKO, r.STATUS)
- BULK COLLECT
- INTO owt
- FROM WYCIECZKI w
- JOIN REZERWACJE r ON w.ID_WYCIECZKI = r.ID_WYCIECZKI
- JOIN OSOBY o on r.ID_OSOBY = o.ID_OSOBY
- WHERE w.ID_WYCIECZKI = uczestnicy_wycieczki.id_wycieczki
- AND r.STATUS <> 'A';
- return owt;
- END;
- select *
- from table (uczestnicy_wycieczki(3));
- CREATE OR REPLACE FUNCTION rezerwacje_osoby(id_osoby OSOBY.ID_OSOBY%TYPE)
- return uczestnicy_wycieczki_t as
- owt uczestnicy_wycieczki_t;
- exist integer;
- BEGIN
- SELECT COUNT(*) INTO exist FROM OSOBY o WHERE o.ID_OSOBY = rezerwacje_osoby.id_osoby;
- if exist = 0 THEN
- raise_application_error(-20001, 'Brak osoby o danym id');
- end if;
- SELECT uczestnicy_wycieczki_r(w.Kraj, w.DATA, w.NAZWA, o.IMIE, o.NAZWISKO, r.STATUS)
- BULK COLLECT
- INTO owt
- FROM WYCIECZKI w
- JOIN REZERWACJE r ON w.ID_WYCIECZKI = r.ID_WYCIECZKI
- JOIN OSOBY o on r.ID_OSOBY = o.ID_OSOBY
- WHERE o.ID_OSOBY = rezerwacje_osoby.id_osoby
- AND r.STATUS <> 'A';
- return owt;
- end;
- select *
- from table (rezerwacje_osoby(1));
- CREATE OR REPLACE FUNCTION przyszle_rezerwacje_osoby(id_osoby OSOBY.ID_OSOBY%TYPE)
- return uczestnicy_wycieczki_t as
- owt uczestnicy_wycieczki_t;
- exist integer;
- BEGIN
- SELECT COUNT(*) INTO exist FROM OSOBY o WHERE o.ID_OSOBY = przyszle_rezerwacje_osoby.id_osoby;
- if exist = 0 THEN
- raise_application_error(-20001, 'Brak osoby o danym id');
- end if;
- SELECT uczestnicy_wycieczki_r(w.Kraj, w.DATA, w.NAZWA, o.IMIE, o.NAZWISKO, r.STATUS)
- BULK COLLECT
- INTO owt
- FROM WYCIECZKI w
- JOIN REZERWACJE r ON w.ID_WYCIECZKI = r.ID_WYCIECZKI
- JOIN OSOBY o on r.ID_OSOBY = o.ID_OSOBY
- WHERE o.ID_OSOBY = przyszle_rezerwacje_osoby.id_osoby
- AND r.STATUS <> 'A'
- AND w.DATA > CURRENT_DATE;
- return owt;
- end;
- select *
- from table (przyszle_rezerwacje_osoby(3));
- CREATE OR REPLACE TYPE wycieczki_r AS object
- (
- id_wycieczki NUMBER,
- nazwa VARCHAR2(100),
- kraj VARCHAR2(50),
- "data" DATE,
- liczba_miejsc NUMBER,
- liczba_wolnych_miejsc NUMBER
- );
- CREATE OR REPLACE TYPE wycieczki_t IS TABLE OF wycieczki_r;
- CREATE OR REPLACE FUNCTION dostepne_wycieczki_function(kraj WYCIECZKI.KRAJ%TYPE,
- data_od DATE, data_do DATE)
- return wycieczki_t as
- wt wycieczki_t;
- BEGIN
- SELECT wycieczki_r(w.ID_WYCIECZKI,
- w.NAZWA,
- w.KRAJ,
- w.DATA,
- w.LICZBA_MIEJSC,
- w.LICZBA_WOLNYCH_MIEJSC)
- BULK COLLECT
- INTO wt
- FROM dostepne_wycieczki w
- WHERE w.KRAJ = dostepne_wycieczki_function.kraj
- AND w.DATA >= data_od
- AND w.DATA <= data_do;
- return wt;
- end;
- select *
- from table (dostepne_wycieczki_function('Polska', TO_DATE('2019-03-07', 'YYYY-MM-DD'),
- TO_DATE('2020-03-07', 'YYYY-MM-DD')));
- -------------------------------------------------------------
- CREATE OR REPLACE PROCEDURE dodaj_rezerwacje(id_wycieczki WYCIECZKI.ID_WYCIECZKI%TYPE, id_osoby OSOBY.ID_OSOBY%TYPE) AS
- exist integer;
- BEGIN
- SELECT COUNT(*) INTO exist FROM OSOBY o WHERE o.ID_OSOBY = dodaj_rezerwacje.id_osoby;
- if exist = 0 then
- raise_application_error(-20001, 'Brak osoby o danym id');
- end if;
- SELECT COUNT(*) INTO exist FROM DOSTEPNE_WYCIECZKI w WHERE w.ID_WYCIECZKI = dodaj_rezerwacje.id_wycieczki;
- if exist = 0 then
- raise_application_error(-20001, 'Brak dostepnej wycieczki o danym id');
- end if;
- INSERT INTO REZERWACJE(id_wycieczki, id_osoby, STATUS)
- VALUES (dodaj_rezerwacje.id_wycieczki, dodaj_rezerwacje.id_osoby, 'N');
- END;
- SELECT COUNT(*)
- FROM DOSTEPNE_WYCIECZKI w
- WHERE w.ID_WYCIECZKI = 5;
- begin
- DODAJ_REZERWACJE(
- 6, 1
- );
- end;
- CREATE OR REPLACE PROCEDURE zmien_status_rezerwacji(id_rezerwacji REZERWACJE.NR_REZERWACJI%TYPE,
- status REZERWACJE.STATUS%TYPE) AS
- exist integer;
- old_status REZERWACJE.STATUS%TYPE;
- BEGIN
- SELECT COUNT(*)
- INTO exist
- from wycieczki_przyszle wp
- join REZERWACJE r on r.ID_WYCIECZKI = wp.ID_WYCIECZKI
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- if exist = 0 then
- raise_application_error(-20001, 'Nie ma takiej wycieczki w przyszlosci');
- end if;
- SELECT status
- INTO old_status
- from REZERWACJE r
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- CASE
- WHEN old_status is NULL
- then
- raise_application_error(-20001, 'Nie ma takiej rezerwacji');
- WHEN old_status = 'A' then
- SELECT COUNT(*)
- INTO exist
- FROM dostepne_wycieczki dw
- join REZERWACJE r on r.ID_WYCIECZKI = dw.ID_WYCIECZKI
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- if exist = 0 then
- raise_application_error(-20001, 'Nie ma miejsc na zmiane rezerwacji z anulowanej');
- end if;
- ELSE null;
- END CASE;
- UPDATE REZERWACJE
- SET STATUS = zmien_status_rezerwacji.status
- WHERE NR_REZERWACJI = id_rezerwacji;
- END;
- begin
- zmien_status_rezerwacji(
- 21, 'P'
- );
- end;
- CREATE OR REPLACE PROCEDURE zmien_liczbe_miejsc(id_wycieczki WYCIECZKI.ID_WYCIECZKI%TYPE,
- liczba_miejsc WYCIECZKI.LICZBA_MIEJSC%TYPE) AS
- exist integer;
- BEGIN
- SELECT COUNT(*) INTO exist FROM wycieczki_przyszle w WHERE w.ID_WYCIECZKI = zmien_liczbe_miejsc.id_wycieczki;
- if exist = 0 then
- raise_application_error(-20001, 'Brak dostepnej wycieczki o danym id');
- end if;
- SELECT wm.LICZBA_MIEJSC - wm.LICZBA_WOLNYCH_MIEJSC
- into exist
- from wycieczki_miejsca wm
- where wm.ID_WYCIECZKI = zmien_liczbe_miejsc.id_wycieczki;
- if zmien_liczbe_miejsc.liczba_miejsc < 0 OR exist > zmien_liczbe_miejsc.liczba_miejsc then
- raise_application_error(-20001, 'Liczba miejsc za niska');
- end if;
- UPDATE WYCIECZKI
- SET LICZBA_MIEJSC = zmien_liczbe_miejsc.liczba_miejsc
- Where id_wycieczki = zmien_liczbe_miejsc.id_wycieczki;
- END;
- begin
- ZMIEN_LICZBE_MIEJSC(6, 10);
- end;
- ----------------------------------------------------------
- CREATE TABLE REZERWACJE_LOG
- (
- ID INT GENERATED ALWAYS AS IDENTITY NOT NULL,
- ID_REZERWACJI INT,
- DATA DATE,
- STATUS CHAR(1),
- CONSTRAINT REZERWACJE_LOG_PK PRIMARY KEY (
- ID
- ) ENABLE
- );
- ALTER TABLE REZERWACJE_LOG
- ADD CONSTRAINT REZERWACJE_LOG_FK1 FOREIGN KEY
- (
- ID_REZERWACJI
- )
- REFERENCES REZERWACJE
- (
- NR_REZERWACJI
- )
- ENABLE;
- CREATE OR REPLACE PROCEDURE dodaj_rezerwacje(id_wycieczki WYCIECZKI.ID_WYCIECZKI%TYPE, id_osoby OSOBY.ID_OSOBY%TYPE) AS
- exist integer;
- id_rezerwacji REZERWACJE.nr_rezerwacji%TYPE;
- BEGIN
- SELECT COUNT(*) INTO exist FROM OSOBY o WHERE o.ID_OSOBY = dodaj_rezerwacje.id_osoby;
- if exist = 0 then
- raise_application_error(-20001, 'Brak osoby o danym id');
- end if;
- SELECT COUNT(*) INTO exist FROM DOSTEPNE_WYCIECZKI w WHERE w.ID_WYCIECZKI = dodaj_rezerwacje.id_wycieczki;
- if exist = 0 then
- raise_application_error(-20001, 'Brak dostepnej wycieczki o danym id');
- end if;
- INSERT INTO REZERWACJE(id_wycieczki, id_osoby, STATUS)
- VALUES (dodaj_rezerwacje.id_wycieczki, dodaj_rezerwacje.id_osoby, 'N')
- RETURNING NR_REZERWACJI INTO id_rezerwacji;
- INSERT INTO REZERWACJE_LOG (ID_REZERWACJI, DATA, STATUS)
- VALUES (id_rezerwacji, CURRENT_DATE, 'N');
- END;
- CREATE OR REPLACE PROCEDURE zmien_status_rezerwacji(id_rezerwacji REZERWACJE.NR_REZERWACJI%TYPE,
- status REZERWACJE.STATUS%TYPE) AS
- exist integer;
- old_status REZERWACJE.STATUS%TYPE;
- BEGIN
- SELECT COUNT(*)
- INTO exist
- from wycieczki_przyszle wp
- join REZERWACJE r on r.ID_WYCIECZKI = wp.ID_WYCIECZKI
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- if exist = 0 then
- raise_application_error(-20001, 'Nie ma takiej wycieczki w przyszlosci');
- end if;
- SELECT status
- INTO old_status
- from REZERWACJE r
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- CASE
- WHEN old_status is NULL
- then
- raise_application_error(-20001, 'Nie ma takiej rezerwacji');
- WHEN old_status = 'A' then
- SELECT COUNT(*)
- INTO exist
- FROM dostepne_wycieczki dw
- join REZERWACJE r on r.ID_WYCIECZKI = dw.ID_WYCIECZKI
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- if exist = 0 then
- raise_application_error(-20001, 'Nie ma miejsc na zmiane rezerwacji z anulowanej');
- end if;
- END CASE;
- UPDATE REZERWACJE r
- SET r.STATUS = zmien_status_rezerwacji.status
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- INSERT INTO REZERWACJE_LOG (ID_REZERWACJI, DATA, STATUS)
- VALUES (id_rezerwacji, CURRENT_DATE, zmien_status_rezerwacji.status);
- END;
- -----------------------
- ALTER TABLE WYCIECZKI
- ADD liczba_wolnych_miejsc INT;
- CREATE OR REPLACE PROCEDURE przelicz AS
- BEGIN
- UPDATE WYCIECZKI w
- SET LICZBA_WOLNYCH_MIEJSC = LICZBA_MIEJSC - (SELECT COUNT(*)
- FROM REZERWACJE r
- WHERE r.ID_WYCIECZKI = w.ID_WYCIECZKI
- AND r.STATUS <> 'A');
- END;
- begin
- PRZELICZ;
- end;
- CREATE OR REPLACE VIEW dostepne_wycieczki2
- AS
- SELECT w.id_wycieczki,
- w.kraj,
- w.data,
- w.nazwa,
- w.liczba_miejsc,
- w.liczba_wolnych_miejsc
- FROM WYCIECZKI w
- WHERE liczba_wolnych_miejsc > 0
- AND w.DATA > CURRENT_DATE;
- CREATE OR REPLACE VIEW wycieczki_miejsca2
- AS
- SELECT w.ID_WYCIECZKI,
- w.kraj,
- w.data,
- w.nazwa,
- w.liczba_miejsc,
- w.LICZBA_WOLNYCH_MIEJSC
- FROM wycieczki w;
- CREATE OR REPLACE PROCEDURE dodaj_rezerwacje2(id_wycieczki WYCIECZKI.ID_WYCIECZKI%TYPE, id_osoby OSOBY.ID_OSOBY%TYPE) AS
- exist integer;
- id_rezerwacji REZERWACJE.nr_rezerwacji%TYPE;
- BEGIN
- SELECT COUNT(*) INTO exist FROM OSOBY o WHERE o.ID_OSOBY = dodaj_rezerwacje2.id_osoby;
- if exist = 0 then
- raise_application_error(-20001, 'Brak osoby o danym id');
- end if;
- SELECT COUNT(*) INTO exist FROM DOSTEPNE_WYCIECZKI w WHERE w.ID_WYCIECZKI = dodaj_rezerwacje2.id_wycieczki;
- if exist = 0 then
- raise_application_error(-20001, 'Brak dostepnej wycieczki o danym id');
- end if;
- INSERT INTO REZERWACJE(id_wycieczki, id_osoby, STATUS)
- VALUES (dodaj_rezerwacje2.id_wycieczki, dodaj_rezerwacje2.id_osoby, 'N')
- RETURNING NR_REZERWACJI INTO id_rezerwacji;
- UPDATE WYCIECZKI
- SET LICZBA_WOLNYCH_MIEJSC = LICZBA_WOLNYCH_MIEJSC - 1
- WHERE ID_WYCIECZKI = dodaj_rezerwacje2.id_wycieczki;
- INSERT INTO REZERWACJE_LOG (ID_REZERWACJI, DATA, STATUS)
- VALUES (id_rezerwacji, CURRENT_DATE, 'N');
- END;
- begin
- DODAJ_REZERWACJE2(6, 2);
- end;
- CREATE OR REPLACE PROCEDURE zmien_status_rezerwacji2(id_rezerwacji REZERWACJE.NR_REZERWACJI%TYPE,
- status REZERWACJE.STATUS%TYPE) AS
- exist integer;
- old_status REZERWACJE.STATUS%TYPE;
- wolne_miejsca_roznica integer;
- BEGIN
- SELECT COUNT(*)
- INTO exist
- from wycieczki_przyszle wp
- join REZERWACJE r on r.ID_WYCIECZKI = wp.ID_WYCIECZKI
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- if exist = 0 then
- raise_application_error(-20001, 'Nie ma takiej wycieczki w przyszlosci');
- end if;
- SELECT status
- INTO old_status
- from REZERWACJE r
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- CASE
- WHEN old_status is NULL
- then
- raise_application_error(-20001, 'Nie ma takiej rezerwacji');
- WHEN old_status = 'A' then
- SELECT COUNT(*)
- INTO exist
- FROM dostepne_wycieczki dw
- join REZERWACJE r on r.ID_WYCIECZKI = dw.ID_WYCIECZKI
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- IF exist = 0 THEN
- raise_application_error(-20001, 'Nie ma miejsc na zmiane rezerwacji z anulowanej');
- ELSE
- wolne_miejsca_roznica := -1;
- END IF;
- ELSE
- IF zmien_status_rezerwacji2.status = 'A'
- THEN
- wolne_miejsca_roznica := 1;
- ELSE
- wolne_miejsca_roznica := 0;
- END IF;
- END CASE;
- UPDATE REZERWACJE r
- SET r.STATUS = zmien_status_rezerwacji2.status
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- UPDATE WYCIECZKI w
- SET LICZBA_WOLNYCH_MIEJSC = LICZBA_WOLNYCH_MIEJSC + wolne_miejsca_roznica
- WHERE w.ID_WYCIECZKI = (SELECT ID_WYCIECZKI
- FROM REZERWACJE r
- WHERE r.NR_REZERWACJI = id_rezerwacji);
- INSERT INTO REZERWACJE_LOG (ID_REZERWACJI, DATA, STATUS)
- VALUES (id_rezerwacji, CURRENT_DATE, zmien_status_rezerwacji2.status);
- END;
- CREATE OR REPLACE PROCEDURE zmien_liczbe_miejsc2(id_wycieczki WYCIECZKI.ID_WYCIECZKI%TYPE,
- liczba_miejsc WYCIECZKI.LICZBA_MIEJSC%TYPE) AS
- exist integer;
- BEGIN
- SELECT COUNT(*) INTO exist FROM DOSTEPNE_WYCIECZKI w WHERE w.ID_WYCIECZKI = zmien_liczbe_miejsc2.id_wycieczki;
- if exist = 0 then
- raise_application_error(-20001, 'Brak dostepnej wycieczki o danym id');
- end if;
- SELECT wm.LICZBA_MIEJSC - wm.LICZBA_WOLNYCH_MIEJSC
- into exist
- from wycieczki_miejsca wm
- where wm.ID_WYCIECZKI = zmien_liczbe_miejsc2.id_wycieczki;
- if zmien_liczbe_miejsc2.liczba_miejsc < 0 OR exist > zmien_liczbe_miejsc2.liczba_miejsc then
- raise_application_error(-20001, 'Liczba miejsc za niska');
- end if;
- UPDATE WYCIECZKI w
- SET w.LICZBA_MIEJSC = zmien_liczbe_miejsc2.liczba_miejsc,
- w.LICZBA_WOLNYCH_MIEJSC = LICZBA_WOLNYCH_MIEJSC + zmien_liczbe_miejsc2.id_wycieczki - LICZBA_MIEJSC
- Where w.id_wycieczki = zmien_liczbe_miejsc2.id_wycieczki;
- END;
- CREATE OR REPLACE FUNCTION dostepne_wycieczki_function2(kraj WYCIECZKI.KRAJ%TYPE,
- data_od DATE, data_do DATE)
- return wycieczki_t as
- wt wycieczki_t;
- BEGIN
- SELECT wycieczki_r(w.ID_WYCIECZKI,
- w.NAZWA,
- w.KRAJ,
- w.DATA,
- w.LICZBA_MIEJSC,
- w.LICZBA_WOLNYCH_MIEJSC)
- BULK COLLECT
- INTO wt
- FROM dostepne_wycieczki2 w
- WHERE w.KRAJ = dostepne_wycieczki_function2.kraj
- AND w.DATA >= data_od
- AND w.DATA <= data_do;
- return wt;
- end;
- select *
- from table (dostepne_wycieczki_function2('Polska', TO_DATE('2019-03-07', 'YYYY-MM-DD'),
- TO_DATE('2020-03-07', 'YYYY-MM-DD')));
- ------------------------------------------------
- CREATE OR REPLACE PROCEDURE zmien_status_rezerwacji3(id_rezerwacji REZERWACJE.NR_REZERWACJI%TYPE,
- status REZERWACJE.STATUS%TYPE) AS
- exist integer;
- old_status REZERWACJE.STATUS%TYPE;
- BEGIN
- SELECT COUNT(*)
- INTO exist
- from wycieczki_przyszle wp
- join REZERWACJE r on r.ID_WYCIECZKI = wp.ID_WYCIECZKI
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- if exist = 0 then
- raise_application_error(-20001, 'Nie ma takiej wycieczki w przyszlosci');
- end if;
- SELECT status
- INTO old_status
- from REZERWACJE r
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- CASE
- WHEN old_status is NULL
- then
- raise_application_error(-20001, 'Nie ma takiej rezerwacji');
- WHEN old_status = 'A' then
- SELECT COUNT(*)
- INTO exist
- FROM dostepne_wycieczki dw
- join REZERWACJE r on r.ID_WYCIECZKI = dw.ID_WYCIECZKI
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- if exist = 0 then
- raise_application_error(-20001, 'Nie ma miejsc na zmiane rezerwacji z anulowanej');
- end if;
- ELSE null;
- END CASE;
- UPDATE REZERWACJE r
- SET r.STATUS = zmien_status_rezerwacji3.status
- WHERE r.NR_REZERWACJI = id_rezerwacji;
- END;
- CREATE OR REPLACE PROCEDURE dodaj_rezerwacje3(id_wycieczki WYCIECZKI.ID_WYCIECZKI%TYPE, id_osoby OSOBY.ID_OSOBY%TYPE) AS
- exist integer;
- id_rezerwacji REZERWACJE.nr_rezerwacji%TYPE;
- BEGIN
- SELECT COUNT(*) INTO exist FROM OSOBY o WHERE o.ID_OSOBY = dodaj_rezerwacje3.id_osoby;
- if exist = 0 then
- raise_application_error(-20001, 'Brak osoby o danym id');
- end if;
- SELECT COUNT(*) INTO exist FROM DOSTEPNE_WYCIECZKI w WHERE w.ID_WYCIECZKI = dodaj_rezerwacje3.id_wycieczki;
- if exist = 0 then
- raise_application_error(-20001, 'Brak dostepnej wycieczki o danym id');
- end if;
- INSERT INTO REZERWACJE(id_wycieczki, id_osoby, STATUS)
- VALUES (dodaj_rezerwacje3.id_wycieczki, dodaj_rezerwacje3.id_osoby, 'N');
- END;
- CREATE OR REPLACE PROCEDURE zmien_liczbe_miejsc3(id_wycieczki WYCIECZKI.ID_WYCIECZKI%TYPE,
- liczba_miejsc WYCIECZKI.LICZBA_MIEJSC%TYPE) AS
- exist integer;
- BEGIN
- SELECT COUNT(*) INTO exist FROM DOSTEPNE_WYCIECZKI w WHERE w.ID_WYCIECZKI = zmien_liczbe_miejsc3.id_wycieczki;
- if exist = 0 then
- raise_application_error(-20001, 'Brak dostepnej wycieczki o danym id');
- end if;
- SELECT wm.LICZBA_MIEJSC - wm.LICZBA_WOLNYCH_MIEJSC
- into exist
- from wycieczki_miejsca wm
- where wm.ID_WYCIECZKI = zmien_liczbe_miejsc3.id_wycieczki;
- if zmien_liczbe_miejsc3.liczba_miejsc < 0 OR exist > zmien_liczbe_miejsc3.liczba_miejsc then
- raise_application_error(-20001, 'Liczba miejsc za niska');
- end if;
- UPDATE WYCIECZKI w
- SET w.LICZBA_MIEJSC = zmien_liczbe_miejsc3.liczba_miejsc
- Where w.id_wycieczki = zmien_liczbe_miejsc3.id_wycieczki;
- END;
- begin
- zmien_status_rezerwacji3(43, 'Z');
- end;
- CREATE OR REPLACE TRIGGER dodawanie_rezerwacji_trigger
- AFTER INSERT
- ON REZERWACJE
- FOR EACH ROW
- BEGIN
- INSERT INTO REZERWACJE_LOG (ID_REZERWACJI, DATA, STATUS)
- VALUES (:NEW.NR_REZERWACJI, CURRENT_DATE, :NEW.STATUS);
- UPDATE WYCIECZKI w
- SET LICZBA_WOLNYCH_MIEJSC = LICZBA_WOLNYCH_MIEJSC - 1
- WHERE w.ID_WYCIECZKI = :NEW.ID_WYCIECZKI;
- END;
- CREATE OR REPLACE TRIGGER zmiana_statusu_trigger
- AFTER UPDATE
- ON REZERWACJE
- FOR EACH ROW
- DECLARE
- wolne_miejsca_roznica integer;
- BEGIN
- INSERT INTO REZERWACJE_LOG (ID_REZERWACJI, DATA, STATUS)
- VALUES (:NEW.NR_REZERWACJI, CURRENT_DATE, :NEW.STATUS);
- CASE
- WHEN :OLD.STATUS = 'A' AND :NEW.STATUS <> 'A'
- THEN
- wolne_miejsca_roznica := -1;
- WHEN :OLD.STATUS <> 'A' AND :NEW.STATUS = 'A'
- THEN
- wolne_miejsca_roznica := 1;
- ELSE
- wolne_miejsca_roznica := 0;
- END CASE;
- UPDATE WYCIECZKI w
- SET LICZBA_WOLNYCH_MIEJSC = LICZBA_WOLNYCH_MIEJSC + wolne_miejsca_roznica
- WHERE w.ID_WYCIECZKI = :NEW.ID_WYCIECZKI;
- END;
- CREATE OR REPLACE TRIGGER usuniecie_rezerwacji_trigger
- BEFORE DELETE
- ON REZERWACJE
- FOR EACH ROW
- BEGIN
- raise_application_error(-20002, 'Nie mozna usuwac rezerwacji');
- END;
- CREATE OR REPLACE TRIGGER zmiana_liczby_miejsc_trigger
- BEFORE UPDATE OF liczba_miejsc
- ON wycieczki
- FOR EACH ROW
- BEGIN
- SELECT :OLD.LICZBA_WOLNYCH_MIEJSC +
- :NEW.LICZBA_MIEJSC - :OLD.LICZBA_MIEJSC
- INTO :NEW.LICZBA_WOLNYCH_MIEJSC
- FROM Dual;
- END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement