--Oracle 11g Express Edition
--please drop objects you've created at the end of the script
--or check for their existance before creating
--'\\' is a delimiter
DECLARE
sqlquery VARCHAR2(128);
tabName VARCHAR2(30) := 'tabela';
VNAZWISKO VARCHAR2(30) := 'nazwisko';
BEGIN
    --sqlquery := 'UPDATE ' || tabName || ' SET NAZWISKO = ''KOWALSKA_NOWY'' WHERE NAZWISKO = ''KOWALSKA''';
    sqlquery := 'UPDATE ' || tabName || ' SET NAZWISKO = ''' || VNAZWISKO || ''' WHERE NAZWISKO = ''KOWALSKA''';
    dbms_output.put_line(sqlquery);
END;


CREATE OR REPLACE PROCEDURE updatedbms (tabela VARCHAR2, iimie VARCHAR2) IS
    curs INTEGER(10);
    ret INTEGER(10);
    sqlquery VARCHAR2(128);
BEGIN
    sqlquery := 'insert into ' || tabela || '(imie) values(:im)';
    curs := DBMS_SQL.OPEN_CUROSR;
    DMBS_SQL.PARSE(curs, sqlquery, DMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(curs,':im',iimie);
    ret := DBMS_SQL.EXECUTE(curs);
    DBMS_SQL.CLOSE_CURSOR(curs);
    DBMS_OUTPUT.PUT_LINE(ret);
END;

DECLARE
    obj telefon := NEW telefon(...);
BEGIN


--3--
CREATE TYPE TELEFON AS OBJECT(
NAZWA_SIECI VARCHAR2(30),
NUMER NUMBER,
DARMOWE_MINUTY NUMBER
);

CREATE TABLE TELEFONY OF TELEFON;

INSERT INTO TELEFONY VALUES(NEW TELEFON('TMOBILE', 666925358, 12000));
INSERT INTO TELEFONY VALUES(NEW TELEFON('ORANGE', 666222333, 1200));

SELECT * FROM TELEFONY;

SELECT VALUE(A).NAZWA_SIECI, VALUE(A).NUMER, VALUE(A).DARMOWE_MINUTY FROM TELEFONY A;

--4--
CREATE TYPE WLASCICIEL OBJECT(
IMIE VARCHAR2(150000),
NAZWISKO VARCHAR(120000),
TEL REF TELEFON
    );

CREATE TABLE WLASCICIELE OF WLASCICIEL;

ALTER TABLE WLASCICIELE ADD SCOPE FOR(TEL) IS TELEFONY;

ALTER TYPE wlasciciel REPLACE AS object(
    IMIE VARCHAR2(150000),
    NAZWISKO VARCHAR(120000),
    TEL REF TELEFON,
    member FUNCTION mintosek(minu NUMBER) RETURN NUMBER,
    member PROCEDURE darmowemin(wykorzystane NUMBER),
    constructor FUNCTION wlasciciel(imie VARCHAR2, nazwisko VARCHAR2)
        RETURN self AS result
    );



CREATE OR REPLACE TYPE BODY wlascicel AS
    member FUNCTION mintosek(minu NUMBER) RETURN NUMBER IS
        BEGIN
            RETURN minu*60;
    END mintosek;
    member PROCEDURE darmowemin(wykorzystane NUMBER) IS
        BEGIN
            self.tel.DARMOWE_MINUTY := self.tel.DARMOWE_MINUTY - wykorzystane;
    END darmowemin;
    constructor FUNCTION wlasciciel(imie VARCHAR2, nazwisko VARCHAR2) RETURN self AS result IS
        BEGIN
        self.IMIE:=imie;
        self.NAZWISKO:=nazwisko;
        self.TEL:=NULL;
        RETURN;
    END;
END;

constructor FUNCTION ksiazka(tytul VARCHAR2, autor VARCHAR2, licz_str NUMBER,
                                    cena FLOAT, data_wyd NUMBER) RETURN self AS result IS
        BEGIN
            self.tytul := tytul;
            self.autor := autor;
            self. licz_str := licz_str;
            self.cena := cena;
            self.data_wyd := data_wyd;
            self.czytelnikk := NULL;
            liczwartosc();
            RETURN;
        END;




CREATE OR REPLACE TYPE ceny AS TABLE OF character varying(50);

DECLARE
    cceny ceny := ceny(1,2,3,4,5,6);
    x numeric;
BEGIN
    FOR x IN cceny.FIRST()..cceny.LAST() LOOP
        dbms_output.put_line(cceny(x));
    END LOOP;
    cceny.extend(2);
    cceny.DELETE(3);
    cceny.DELETE(4);
    FOR x IN cceny.FIRST()..cceny.LAST() LOOP
        IF cceny.EXISTS(x) THEN
            dbms_output.put_line(cceny(x));
        END IF;
    END LOOP;
    dbms_output.put_line(cceny(5));
    dbms_output.put_line(cceny(6));
END;

CREATE TYPE ksiazka AS object (
    tytul VARCHAR2(30),
    autor VARCHAR2(30),
    licz_str NUMBER(4,0),
    cena FLOAT(5),
    data_wyd NUMBER(4,0)
);

CREATE TABLE Ksiazki OF Ksiazka;
INSERT INTO Ksiazki VALUES(NEW Ksiazka('Tytul1', 'Autor1', 300, 19.99, 1997));
INSERT INTO Ksiazki VALUES('Tytul2', 'Autor2', 301, 20, 1999);
SELECT * FROM ksiazki;
SELECT VALUE(p) FROM ksiazki p;

CREATE TABLE biblioteka (
    nazwa VARCHAR2(30),
    adres VARCHAR2(30),
    ksiazkaa ksiazka);

INSERT INTO BIBLIOTEKA VALUES('bibl1', 'adres1', NEW Ksiazka('Tytul1', 'Autor1', 300, 19.99, 1997));

SELECT * FROM BIBLIOTEKA;

INSERT INTO BIBLIOTEKA VALUES('bibl1', 'adres1', NEW Ksiazka('Tytul2', 'Autor2', 313, 1.33, 1994));
INSERT INTO BIBLIOTEKA VALUES('bibl1', 'adres1', NEW Ksiazka('Tytul3', 'Autor3', 355, 19, 1977));
INSERT INTO BIBLIOTEKA VALUES('bibl2', 'adres2', NEW Ksiazka('Tytul2', 'Autor2', 313, 1.33, 1994));
INSERT INTO BIBLIOTEKA VALUES('bibl3', 'adres3', NEW Ksiazka('Tytul3', 'Autor3', 355, 19, 1977));

SELECT p.ksiazkaa.tytul FROM biblioteka p;

CREATE TYPE ksiazka AS object (
    tytul VARCHAR2(30),
    autor VARCHAR2(30),
    licz_str NUMBER(5,0),
    cena FLOAT(10),
    data_wyd NUMBER(5,0)
);

ALTER TYPE ksiazka REPLACE AS object (
    tytul VARCHAR2(30),
    autor VARCHAR2(30),
    licz_str NUMBER(5,0),
    cena FLOAT(10),
    data_wyd NUMBER(5,0),
    member PROCEDURE liczWartosc,
    constructor FUNCTION ksiazka(
        tytul VARCHAR2,
        autor VARCHAR2,
        licz_str NUMBER,
        cena FLOAT,
        data_wyd NUMBER) RETURN self AS result,
    map member FUNCTION odwzoruj RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY ksiazka AS
    member PROCEDURE liczWartosc IS
        BEGIN
            FOR licz IN 1..(EXTRACT(YEAR FROM CURRENT_TIMESTAMP) - data_wyd) LOOP
                cena := cena * 0.95;
            END LOOP;
        END liczWartosc;
    constructor FUNCTION ksiazka(tytul VARCHAR2, autor VARCHAR2, licz_str NUMBER,
                                    cena FLOAT, data_wyd NUMBER) RETURN self AS result IS
        BEGIN
            self.tytul := tytul;
            self.autor := autor;
            self. licz_str := licz_str;
            self.cena := cena;
            self.data_wyd := data_wyd;
            liczwartosc();
            RETURN;
        END;
    map member FUNCTION odwzoruj RETURN NUMBER IS
        BEGIN
            RETURN data_wyd;
        END;
END;

CREATE TABLE ksiazki OF ksiazka;

INSERT INTO ksiazki VALUES(NEW Ksiazka('Tytul1', 'Autor1', 313, 19.33, 1994));
INSERT INTO ksiazki VALUES(NEW Ksiazka('Tytul2', 'Autor2', 33, 12.33, 2000));

SELECT VALUE(k) FROM ksiazki k

ALTER TYPE ksiazka ADD map member FUNCTION odwzoruj RETURN NUMBER CASCADE including TABLE data;

SET serveroutput ON;

DECLARE
    obj1 ksiazka := NEW Ksiazka('Tytul1', 'Autor1', 313, 19, 1994);
    obj2 ksiazka := NEW Ksiazka('Tytul2', 'Autor2', 33, 22, 1994);
BEGIN
    IF obj1 = obj2 THEN
        dbms_output.put_line('equals');
    ELSE
        dbms_output.put_line('not equals');
    END IF;
END;

CREATE TYPE czytelnik AS object (
    imie VARCHAR2(30),
    nazwisko VARCHAR2(30)
);

CREATE TYPE ksiazka AS object (
    tytul VARCHAR2(30),
    autor VARCHAR2(30),
    licz_str NUMBER(5,0),
    cena FLOAT(10),
    data_wyd NUMBER(5,0),
    czytelnikk REF czytelnik
);

ALTER TYPE ksiazka REPLACE AS object (
    tytul VARCHAR2(30),
    autor VARCHAR2(30),
    licz_str NUMBER(5,0),
    cena FLOAT(10),
    data_wyd NUMBER(5,0),
    czytelnikk REF czytelnik,
    member PROCEDURE liczWartosc,
    constructor FUNCTION ksiazka(
        tytul VARCHAR2,
        autor VARCHAR2,
        licz_str NUMBER,
        cena FLOAT,
        data_wyd NUMBER) RETURN self AS result,
    map member FUNCTION odwzoruj RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY ksiazka AS
    member PROCEDURE liczWartosc IS
        BEGIN
            FOR licz IN 1..(EXTRACT(YEAR FROM CURRENT_TIMESTAMP) - data_wyd) LOOP
                cena := cena * 0.95;
            END LOOP;
        END liczWartosc;
    constructor FUNCTION ksiazka(tytul VARCHAR2, autor VARCHAR2, licz_str NUMBER,
                                    cena FLOAT, data_wyd NUMBER) RETURN self AS result IS
        BEGIN
            self.tytul := tytul;
            self.autor := autor;
            self. licz_str := licz_str;
            self.cena := cena;
            self.data_wyd := data_wyd;
            self.czytelnikk := NULL;
            liczwartosc();
            RETURN;
        END;
    map member FUNCTION odwzoruj RETURN NUMBER IS
        BEGIN
            RETURN data_wyd;
        END;
END;

CREATE TABLE czytelnicy OF czytelnik;

INSERT INTO czytelnicy VALUES(NEW czytelnik('aaa', 'aaa'));
INSERT INTO czytelnicy VALUES(NEW czytelnik('bbb', 'bbb'));
INSERT INTO czytelnicy VALUES(NEW czytelnik('ccc', 'ccc'));

CREATE TABLE ksiazki OF ksiazka;
ALTER TABLE ksiazki ADD scope FOR(czytelnikk) IS czytelnicy;

--insert into ksiazki values(new Ksiazka('Tytul1', 'Autor1', 313, 19, 1994, new czytelnik('ddd', 'ddd')));
INSERT INTO ksiazki VALUES(NEW Ksiazka('Tytul1', 'Autor1', 313, 19, 1994, NULL));
INSERT INTO ksiazki VALUES(NEW Ksiazka('Tytul1', 'Autor1', 313, 19, 1994, (SELECT REF(c) FROM czytelnicy c WHERE imie LIKE 'aaa')));
INSERT INTO ksiazki VALUES(NEW Ksiazka('Tytul1', 'Autor1', 313, 19, 1994, (SELECT REF(c) FROM czytelnicy c WHERE imie LIKE 'bbb')));

SELECT * FROM ksiazki;
SELECT DISTINCT * FROM ksiazki;
SELECT k.czytelnikk.imie, k.czytelnikk.nazwisko FROM ksiazki k;
SELECT k.tytul, k.autor, DEREF(k.czytelnikk) FROM ksiazki k;

DELETE FROM czytelnicy WHERE imie LIKE 'bbb';
UPDATE ksiazki SET czytelnikk = NULL WHERE czytelnikk IS dangling;



--GRUPA 2


--ZAD 1
--1.    Korzystając z pakietu DBMS_SQL napisz procedurę uaktualniając rekord z nazwiskiem KOWALSKA
--KOWALSKA_NOWY z tabeli o podanej nazwie będącej
--parametrem procedury.

CREATE OR REPLACE PROCEDURE grupa2zad1(tabName VARCHAR2, vnazwisko VARCHAR2) IS
sqlquery VARCHAR2(128);
curs BINARY_INTEGER;
retval BINARY_INTEGER;
BEGIN
    --sqlquery := 'UPDATE ' || tabName || ' SET NAZWISKO = ''KOWALSKA_NOWY'' WHERE NAZWISKO = ''KOWALSKA''';
    sqlquery := 'UPDATE ' || tabName || ' SET NAZWISKO = ''' || VNAZWISKO || ''' WHERE NAZWISKO = ''KOWALSKA''';
    dbms_output.put_line(sqlquery);
    curs := DBMS_SQL.OPEN_CURSOR;
    dbms_output.put_line(curs);
    DBMS_SQL.PARSE(curs, sqlquery, DBMS_SQL.NATIVE);
    retval := DBMS_SQL.EXECUTE(curs);
    DBMS_SQL.CLOSE_CURSOR(curs);
END;

CREATE TABLE GRUPA2ZAD1TAB(IMIE VARCHAR2(128), NAZWISKO VARCHAR2(128));

INSERT INTO GRUPA2ZAD1TAB VALUES('SIEMA', 'ENIU');
INSERT INTO GRUPA2ZAD1TAB VALUES('WIESLAAWA', 'KOWALSKA');

SET SERVEROUTPUT ON
DECLARE
BEGIN
    grupa2zad1('GRUPA2ZAD1TAB', 'xd');
END;





--ZAD2
--2. Utworzyć procedurę przyjmującą jako parametry nazwę tabeli i nazwę kolumny typu NUMBER.
--Korzystając z dynamicznego SQL policz sumę wartości z kolumny będącej
--parametrem wywołania oraz liczbę różnych wartości tej kolumny
--Wynik zapisz w tabeli jednokolumnowej w postaci np.: SUMA = 34, liczba różnych = 5

CREATE TABLE RESULTSZAD2(data_ VARCHAR2(128));
CREATE OR REPLACE PROCEDURE grupa2zad2(tabName VARCHAR2, kolName VARCHAR2)
IS
sqlquery VARCHAR2(128);
sumval INT(32);
distinctval INT(32);
BEGIN
    sqlquery := 'SELECT SUM(' || kolName || ') FROM ' || tabName;
    EXECUTE IMMEDIATE(sqlquery) INTO sumval;
    sqlquery := 'SELECT COUNT(*) FROM (SELECT DISTINCT ' || kolName || ' FROM ' || tabName || ')';
    EXECUTE IMMEDIATE(sqlquery) INTO distinctval;
    INSERT INTO RESULTSZAD2 VALUES('SUMA = ' || sumval || ', rozne = ' || distinctval);
END;

SET SERVEROUTPUT ON
DECLARE
BEGIN
    grupa2zad2('STUDENT', 'ID_STUDENT');
END;





--ZAD3
--3.    Zdefiniuj typ obiektowy reprezentujący STUDENTA.
--Każdy STUDENT powinien mieć nazwisko i imię, datę urodzenia oraz nr indeksu.
--Utwórz tablicę obiektową i wprowadź dwa przykładowe obiekty.
--Wypisz zawartość tablicy w postaci obiektowej i relacyjnej.

CREATE OR REPLACE TYPE STUDENTZAD3GRP2 AS OBJECT(
    imie VARCHAR2(128),
    nazwisko VARCHAR2(128),
    data_ur DATE,
    nr_indx INT(16)
);

CREATE TABLE STUDENCIZAD3GRP2 OF STUDENTZAD3GRP2;

INSERT INTO STUDENCIZAD3GRP2 VALUES(NEW STUDENTZAD3GRP2('Abdul', 'Wisimulacha', TO_DATE('01/01/1990', 'dd/mm/yyyy'), 101));
INSERT INTO STUDENCIZAD3GRP2 VALUES(NEW STUDENTZAD3GRP2('Wiesiek', 'Wisimupala', TO_DATE('01/05/1993', 'dd/mm/yyyy'), 111));

SELECT * FROM STUDENCIZAD3GRP2;

SELECT VALUE(B).IMIE, VALUE(B).NAZWISKO, VALUE(B).DATA_UR, VALUE(B).NR_INDX FROM STUDENCIZAD3GRP2 B;





--ZAD 4
--4.    Utwórz tablicę ADRESY zawierającą adres zamieszkania studenta (miejscowość i ulica)
--a następnie powiąż te obiekty za pomocą referencji.
--Wstaw obiekty do tabeli STUDENT oraz ADRESY.
--Wykonaj zapytania zwracające nazwisko i adres studenta - wykorzystaj nawigację
--przez referencje.

--tworze typ bo jak kurwa referencja do tablicy adresow?
CREATE OR REPLACE TYPE ADRESZAD4GRP2 AS OBJECT(
    miejsc VARCHAR2(128),
    ulica VARCHAR2(128)
);

CREATE OR REPLACE TYPE STUDENTZAD4GRP2 AS OBJECT(
    imie VARCHAR2(128),
    nazwisko VARCHAR2(128),
    data_ur DATE,
    nr_indx INT(16),
    adres REF ADRESZAD4GRP2
);

CREATE TABLE ADRESYZAD4 OF ADRESZAD4GRP2;
CREATE TABLE STUDENTYZAD4 OF STUDENTZAD4GRP2;

ALTER TABLE STUDENTYZAD4 ADD SCOPE FOR(adres) IS ADRESYZAD4;

INSERT INTO STUDENTYZAD4 VALUES
(NEW STUDENTZAD4GRP2('Abdul','Wisimutromba',TO_DATE('01/05/1993', 'dd/mm/yyyy'), 101, NULL));

INSERT INTO ADRESYZAD4 VALUES('krakow', 'bronowicka');

UPDATE STUDENTYZAD4 SET STUDENTYZAD4.adres = (SELECT REF(C) FROM ADRESYZAD4 C WHERE C.miejsc = 'krakow' FETCH FIRST ROW ONLY);

SELECT * FROM STUDENTYZAD4;

SELECT B.imie, B.nazwisko, B.adres.miejsc FROM STUDENTYZAD4 B;

CREATE OR REPLACE PROCEDURE updatedbms (tabela VARCHAR2, iimie VARCHAR2) IS
    curs INTEGER(10);
    ret INTEGER(10);
    sqlquery VARCHAR2(128);
BEGIN
    sqlquery := 'insert into ' || tabela || ' values(:im) where imie = ''Kamil''';
    curs := DBMS_SQL.OPEN_CUROSR;
    DMBS_SQL.PARSE(curs, sqlquery, DMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(curs,':im',iimie);
    ret := DBMS_SQL.EXECUTE(curs);
    DBMS_SQL.CLOSE_CURSOR(curs);
    DBMS_OUTPUT.PUT_LINE(ret);
END;

DECLARE
    wart BOOLEAN;
BEGIN
    wart := (EXTRACT(YEAR FROM SYSDATE) = '2017');
    IF wart = TRUE THEN
        DBMS_OUTPUT.PUT_LINE('true');
    ELSE
        DBMS_OUTPUT.PUT_LINE('false');
    END IF;
END;

CREATE TYPE TELEFON AS OBJECT(
NAZWA_SIECI VARCHAR2(30),
NUMER NUMBER,
DARMOWE_MINUTY NUMBER
);

CREATE TABLE TELEFONY OF TELEFON;

INSERT INTO TELEFONY VALUES(NEW TELEFON('TMOBILE', 666925358, 12000));
INSERT INTO TELEFONY VALUES(NEW TELEFON('ORANGE', 666222333, 1200));

SELECT * FROM TELEFONY;

SELECT VALUE(A).NAZWA_SIECI, VALUE(A).NUMER, VALUE(A).DARMOWE_MINUTY FROM TELEFONY A;

--4--
CREATE TYPE WLASCICIEL AS OBJECT(
IMIE VARCHAR2(150),
NAZWISKO VARCHAR(120),
TEL REF TELEFON
    );

CREATE TABLE WLASCICIELE OF WLASCICIEL;

ALTER TABLE WLASCICIELE ADD SCOPE FOR(TEL) IS TELEFONY;

ALTER TYPE wlasciciel REPLACE AS object(
    IMIE VARCHAR2(150),
    NAZWISKO VARCHAR(120),
    TEL REF TELEFON,
    member FUNCTION mintosek(minu NUMBER) RETURN NUMBER,
    member PROCEDURE darmowemin(wykorzystane NUMBER)
    );



CREATE OR REPLACE TYPE BODY wlasciciel AS
    member FUNCTION mintosek(minu NUMBER) RETURN NUMBER IS
        BEGIN
            RETURN minu*60;
    END mintosek;
    member PROCEDURE darmowemin(wykorzystane NUMBER) IS
        BEGIN
            self.tel.DARMOWE_MINUTY := self.tel.DARMOWE_MINUTY - wykorzystane;
    END darmowemin;
END;

CREATE OR REPLACE TYPE ceny AS TABLE OF character varying(50);

SET serveroutput ON
DECLARE
    cceny ceny := ceny(1,2,3,4,5,6);
    x numeric;
BEGIN
    FOR x IN cceny.FIRST()..cceny.LAST() LOOP
        dbms_output.put_line(cceny(x));
    END LOOP;
    cceny.extend(2);
    cceny.DELETE(3);
    cceny.DELETE(4);
    FOR x IN cceny.FIRST()..cceny.LAST() LOOP
        IF cceny.EXISTS(x) THEN
            dbms_output.put_line(cceny(x));
        END IF;
    END LOOP;
    dbms_output.put_line(cceny(5));
    dbms_output.put_line(cceny(6));
END;