twBazy

DROP TABLE klienci CASCADE CONSTRAINTS;
DROP TABLE narzedzia CASCADE CONSTRAINTS;
DROP TABLE zamowienia CASCADE CONSTRAINTS;
DROP TABLE promocje CASCADE CONSTRAINTS;
DROP TABLE rachunki CASCADE CONSTRAINTS;
DROP TABLE messages;
DROP VIEW lista_dostepnych CASCADE CONSTRAINTS;
DROP VIEW przedawnione_rachunki CASCADE CONSTRAINTS;
DROP SEQUENCE id_klienta_seq;
DROP SEQUENCE id_narzedzia_seq;
DROP SEQUENCE id_rachunku_seq;
DROP SEQUENCE id_promocji_seq;
DROP SEQUENCE mess_id;

CREATE TABLE klienci (
id_klienta NUMBER(11) PRIMARY KEY NOT NULL,
rodzaj_dokumentu VARCHAR2(30) NOT NULL,
numer_dokumentu VARCHAR2(30) UNIQUE NOT NULL,
nazwisko VARCHAR2(30) NOT NULL,
imie VARCHAR2(30) NOT NULL,
numer_telefonu NUMBER(20) NOT NULL,
uwagi VARCHAR2(250) NOT NULL
);

CREATE TABLE narzedzia (
id_narzedzia NUMBER(11) PRIMARY KEY NOT NULL,
rodzaj_narzedzia VARCHAR2(20) NOT NULL,
ilosc NUMBER(11) NOT NULL,
cena_dzien NUMBER(11) NOT NULL,
opis VARCHAR2(250) NOT NULL
);

CREATE TABLE promocje (
id_promocji NUMBER(11) PRIMARY KEY NOT NULL,
rodzaj_narzedzia VARCHAR2(20) NOT NULL,
procent_znizki NUMBER(3) NOT NULL,
termin_od DATE NOT NULL,
termin_do DATE NOT NULL
);

CREATE TABLE rachunki (
id_rachunku NUMBER(11) PRIMARY KEY NOT NULL,
id_klienta NUMBER(11) REFERENCES klienci(id_klienta) NOT NULL,
data_wypozyczenia DATE NOT NULL,
kaucja NUMBER(11) NOT NULL,
kwota NUMBER(11) NOT NULL,
termin_wplaty DATE UNIQUE NOT NULL
);

CREATE TABLE zamowienia (
id_rachunku NUMBER(11) REFERENCES rachunki(id_rachunku) NOT NULL,
id_narzedzia NUMBER(11) REFERENCES narzedzia(id_narzedzia) NOT NULL
);

create table messages(
    id_mess NUMBER(5,0) constraint mess_pk PRIMARY KEY,
    m_when TIMESTAMP NOT NULL,
    table_name VARCHAR2(30) NOT NULL,
    oper_type VARCHAR2(30) NOT NULL,
    comm VARCHAR2(100)
);

CREATE SEQUENCE id_klienta_seq;
CREATE SEQUENCE id_narzedzia_seq;
CREATE SEQUENCE id_rachunku_seq;
CREATE SEQUENCE id_promocji_seq;
CREATE SEQUENCE mess_id;

CREATE INDEX rach_klienta ON rachunki(id_klienta);
CREATE INDEX zam_rachunki ON zamowienia(id_rachunku);
CREATE INDEX zam_narzedzia ON zamowienia(id_narzedzia);
CREATE INDEX promo_narzedzia ON promocje(rodzaj_narzedzia);

CREATE OR REPLACE VIEW lista_dostepnych AS
SELECT * FROM narzedzia
WHERE ilosc >0;

CREATE OR REPLACE VIEW przedawnione_rachunki AS
SELECT * FROM rachunki
WHERE termin_wplaty > SYSDATE;

CREATE OR REPLACE VIEW niezwrocone_narzedzia AS
SELECT * FROM rachunki
WHERE data_wypozyczenia > SYSDATE +30;

CREATE OR REPLACE VIEW lista_uszkodzonych AS
SELECT * FROM narzedzia
WHERE opis LIKE 'uszkodzone';

INSERT INTO klienci VALUES
        ( ID_KLIENTA_SEQ.NEXTVAL
        , 'DOWOD OSOBISTY'
        , 'ANY12345'
        , 'KOWALSKI'
        , 'ADAM'
        , 123456789
        , 'BRAK'
        );

INSERT INTO klienci VALUES
        ( ID_KLIENTA_SEQ.NEXTVAL
        , 'DOWOD OSOBISTY'
        , 'ANY45645'
        , 'NIEPRZECKI'
        , 'ZENEK'
        , 365478965
        , 'BRAK'
        );

INSERT INTO klienci VALUES
        ( ID_KLIENTA_SEQ.NEXTVAL
        , 'DOWOD OSOBISTY'
        , 'ANY85345'
        , 'LEWANDOWSKI'
        , 'TADEUSZ'
        , 486254862
        , 'BRAK'
        );

INSERT INTO klienci VALUES
        ( ID_KLIENTA_SEQ.NEXTVAL
        , 'DOWOD OSOBISTY'
        , 'ANY12987'
        , 'NIEMIEC'
        , 'WOJCIECH'
        , '123456789'
        , 'BRAK'
        );

INSERT INTO klienci VALUES
        ( ID_KLIENTA_SEQ.NEXTVAL
        , 'PESEL'
        , '7812245324660'
        , 'NUKEM'
        , 'DUKE'
        , 894885248
        , 'BRAK'
        );

INSERT INTO klienci VALUES
        ( ID_KLIENTA_SEQ.NEXTVAL
        , 'PESEL'
        , '55082042668'
        , 'POTOCKI'
        , 'ZBIGNIEW'
        , 365896589
        , 'BRAK'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'WIERTARKA'
        , 2
        , 30
        , 'MAKITA, MOC 220W, NAPIECIE 230V, 1000 RPM, CZARNA W CENTKI'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'WIERTARKA'
        , 3
        , 20
        , 'JBC, MOC 420W, NAPIECIE 230V, 2000 RPM, ZIELONA'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'SZLIFIERKA KATOWA'
        , 4
        , 15
        , 'BOSH, MOC 330W, NAPIECIE 230V, 3000 RPM, ROZOWA'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'SZLIFIERKA KATOWA'
        , 1
        , 40
        , 'EINHELL, MOC 320W, NAPIECIE 230V, 2500 RPM, BIALAI'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'SZLIFIERKA KATOWA'
        , 2
        , 35
        , 'BOSH, MOC 220W, NAPIECIE 230V, 1000 RPM, ZIELONA'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'WKRETARKA'
        , 1
        , 20
        , 'VERTO, MOC 120W, NAPIECIE 230V, 500 RPM, CZERWONA'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'WKRETARKA'
        , 2
        , 25
        , 'HITACHI, MOC 150W, NAPIECIE 230V, 1000 RPM, ZIELONA'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'WKRETARKA'
        , 2
        , 20
        , 'BOSH, MOC 220W, NAPIECIE 230V, 2500 RPM, CZARNA'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'WKRETARKA'
        , 2
        , 25
        , 'DEWALT, MOC 220W, NAPIECIE 230V, 1000 RPM, CZARNA W CENTKI'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'SRUBOKRET'
        , 1
        , 200000000
        , 'KRZYZAKOWY, ZLOTY, WYSADZANY RUBINAMI'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'SRUBOKRET'
        , 1000
        , 1
        , 'PLASKI, ZIELONY, ZWYKLY'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'AGREGAT'
        , 2
        , 90
        , 'MATRIX, MOC 1500W, BENZYNA, 2L/H'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'LAMPA HALOGENOWA'
        , 2
        , 30
        , 'PHILIPS, MOC 2200W, NAPIECIE 230V'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'MYJKA CISNIENIOWA'
        , 4
        , 50
        , 'KARCHER, MOC 2000W, NAPIECIE 230V, ZOLTA'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'POZIOMICA'
        , 2
        , 3
        , 'VALEO, CZERWONA'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'PILA SPALINOWA'
        , 2
        , 30
        , 'STIHL, MOC 5KM, POMARANCZOWA'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'SPAWARKA MIG-MAG'
        , 1
        , 80
        , 'BESTER, MOC 3200W, NAPIECIE 400V, CZERWONA'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'SPAWARKA PLASTIKU'
        , 4
        , 50
        , 'KRAFTDELE, MOC 1700W, NAPIECIE 230V, CZARNA'
        );

INSERT INTO narzedzia VALUES
        ( ID_NARZEDZIA_SEQ.NEXTVAL
        , 'LUTOWNICA'
        , 3
        , 40
        , 'BOSH, MOC 500W, NAPIECIE 230V, ZOLTA'
        );

INSERT INTO promocje VALUES
        (ID_PROMOCJI_SEQ.NEXTVAL
        , 'WKRETARKA'
        , 10
        , TO_DATE('17-11-2017', 'dd-mm-yyyy')
        , TO_DATE('27-12-2017', 'dd-mm-yyyy')
        );

INSERT INTO promocje VALUES
        (ID_PROMOCJI_SEQ.NEXTVAL
        , 'WKRETARKA'
        , 10
        , TO_DATE('17-11-2017', 'dd-mm-yyyy')
        , TO_DATE('27-12-2017', 'dd-mm-yyyy')
        );

INSERT INTO promocje VALUES
        (ID_PROMOCJI_SEQ.NEXTVAL
        , 'SRUBOKRET'
        , 50
        , TO_DATE('15-11-2017', 'dd-mm-yyyy')
        , TO_DATE('22-12-2017', 'dd-mm-yyyy')
        );

COMMIT;

SELECT * FROM klienci;
SELECT * FROM narzedzia;
SELECT * FROM promocje;
SELECT ID_NARZEDZIA FROM NARZEDZIA WHERE ILOSC <2;
SELECT ID_NARZEDZIA FROM NARZEDZIA WHERE CENA_DZIEN = (SELECT Min(CENA_DZIEN) FROM NARZEDZIA);
SELECT RODZAJ_NARZEDZIA FROM NARZEDZIA ORDER BY CENA_DZIEN;
SELECT NAZWISKO, IMIE FROM KLIENCI WHERE RODZAJ_DOKUMENTU = 'PESEL';


--------------------------------zad2

--Pkt 1

DECLARE
    v_rodzaj_narzedzia narzedzia.rodzaj_narzedzia%TYPE;
    v_cena_dzien narzedzia.cena_dzien%TYPE;
    vr_klient klienci%ROWTYPE;
BEGIN
    SELECT rodzaj_narzedzia INTO v_rodzaj_narzedzia FROM narzedzia WHERE id_narzedzia = 5;
    SELECT cena_dzien INTO v_cena_dzien FROM narzedzia WHERE id_narzedzia =5;
    SELECT * INTO vr_klient FROM klienci WHERE numer_dokumentu = 'ANY12345';
    dbms_output.put_line(v_rodzaj_narzedzia);
    dbms_output.put_line(v_cena_dzien);
    dbms_output.put_line('Imie: '||vr_klient.imie||' Nazwisko: '||vr_klient.nazwisko);
END;
/

--Pkt 2

DECLARE
    vr_klient klienci%ROWTYPE;
    v_changed NUMBER;
BEGIN
    SELECT * INTO vr_klient FROM klienci WHERE id_klienta = 3;
    dbms_output.put_line('Numer: '||vr_klient.numer_telefonu);
    UPDATE klienci SET numer_telefonu = 1234567890;
    v_changed := SQL%ROWCOUNT;
    COMMIT;
    dbms_output.put_line('Zmieniono: '||v_changed||' wierszy');
END;
/

-- Pkt 3

DECLARE
    vr_klient klienci%ROWTYPE;
    CURSOR c_klienci IS
        SELECT * FROM klienci WHERE numer_dokumentu = '55082042668';
BEGIN
    OPEN c_klienci;
    LOOP
        FETCH c_klienci INTO vr_klient;
        EXIT WHEN c_klienci%NOTFOUND OR c_klienci%NOTFOUND IS NULL;
        dbms_output.put_line(vr_klient.nazwisko||' nr.tel: '||vr_klient.numer_telefonu);
    END LOOP;
    CLOSE c_klienci;
END;
/


-----------------zad3


set serveroutput on;

CREATE OR REPLACE PROCEDURE get_klient(id_kli klienci.id_klienta%TYPE)
IS
 vr_klient klienci%ROWTYPE;
BEGIN
 SELECT * INTO vr_klient FROM klienci WHERE id_klienta = id_kli;
 dbms_output.put_line('Imie: ' || vr_klient.imie || ' Nazwisko: ' || vr_klient.nazwisko);
END get_klient;
/

CREATE OR REPLACE PROCEDURE ins_klient(klient_dokument klienci.rodzaj_dokumentu%TYPE, klient_nrdokumentu klienci.numer_dokumentu%TYPE, klient_nazwisko klienci.nazwisko%TYPE, klient_imie klienci.imie%TYPE, klient_telefon klienci.numer_telefonu%TYPE, klient_uwagi klienci.uwagi%TYPE)
IS
BEGIN
 INSERT INTO klienci VALUES(id_klienta_seq.NEXTVAL,klient_dokument,klient_nrdokumentu,klient_nazwisko,klient_imie,klient_telefon,klient_uwagi);
 COMMIT;
EXCEPTION
 WHEN DUP_VAL_ON_INDEX THEN
    INSERT INTO messages VALUES (mess_id.nextval,current_timestamp,'klienci','INSERT','Powtorzony numer dokumentu');
    dbms_output.put_line('Powtorzony numer dokumentu, klient juz istnieje w bazie');
    COMMIT;
 WHEN others THEN
    INSERT INTO messages VALUES (mess_id.nextval,current_timestamp,'klienci','INSERT','OTHERS');
    dbms_output.put_line('Nieznany blad');
    COMMIT;
END ins_klient;
/

CREATE OR REPLACE PROCEDURE upd_klient(id_kli klienci.id_klienta%TYPE, nowy_numer klienci.numer_telefonu%TYPE)
IS
BEGIN
 UPDATE klienci SET numer_telefonu=nowy_numer WHERE id_klienta=id_kli;
 COMMIT;
 dbms_output.put_line('Zaaktualizowano numer telefonu');
END upd_klient;
/

CREATE OR REPLACE PROCEDURE del_klient(id_kli klienci.id_klienta%TYPE)
IS
BEGIN
 DELETE FROM klienci WHERE id_klienta=id_kli;
 COMMIT;
 dbms_output.put_line('Usunieto klienta');
END del_klient;
/


EXECUTE get_klient(3);
EXECUTE ins_klient('DOWOD OSOBISTY', 'ANY52698', 'Duda', 'Andrzej', 700800900, 'Adrian');
EXECUTE upd_klient(3,123654789);
EXECUTE del_klient(3);


----------------------różnetakie

create or replace function srednia_pensji
return number
as
v_sr number := 0;
v_sum  number := 0;
v_ct number :=0;
begin
select sum(pensja) into v_sum from nauczyciel;
select count(pensja) into v_ct from nauczyciel;
v_sr := v_sum/v_ct;
return v_sr;
end;
/


CREATE OR REPLACE function srednia
return number
as
  v_sr number := 0;
  v_sum number := 0;
  v_ct number := 0;
BEGIN
  select sum(Pensja) into v_sum from Pracownicy;
  select count(Idpracownicy) into v_ct from Pracownicy;
  v_sr := v_sum / v_ct;
  return v_sr;

END;
/
DECLARE
  sr number;
BEGIN
  sr := srednia();
  Dbms_Output.Put_Line(sr);
END;
/