CREATE OR REPLACE PROCEDURE vypis_studentov ( rocnik   IN student.rocnik%TYPE )
AS
    CURSOR cur1 ( p_rocnik  student.rocnik%TYPE )
    IS
        SELECT
             st.os_cislo,
             ou.meno,
             ou.priezvisko,
             st.st_skupina
         FROM
             priklad_db2.student st
             JOIN priklad_db2.os_udaje ou USING ( rod_cislo )
         WHERE
             st.rocnik = p_rocnik
         ORDER BY
             st.st_skupina,
             ou.priezvisko,
             ou.meno;

    TYPE t_st_rec IS RECORD (
        oc         student.os_cislo%TYPE,
        meno       os_udaje.meno%TYPE,
        priezv     os_udaje.priezvisko%TYPE,
        skupina    student.st_skupina%TYPE );
--  st_rec  t_st_rec;
    st_rec  cur1%ROWTYPE;
BEGIN
    OPEN cur1(rocnik);
        LOOP FETCH cur1 INTO st_rec;
            IF cur1%notfound THEN EXIT;
            ELSE DBMS_OUTPUT.put( st_rec.os_cislo/*oc*/ || ' ');
                DBMS_OUTPUT.put(st_rec.meno|| ' ' || st_rec.priezvisko/*priezv*/ || ' ' );
                DBMS_OUTPUT.put_line(st_rec.st_skupina/*skupina*/);
            END IF;
        END LOOP;
    CLOSE cur1;
END;
/

SET serveroutput ON;

exec vypis_studentov(2);

SELECT table_name FROM tabs;

SELECT 'drop table ' || table_name || ';' FROM tabs WHERE table_name LIKE 'P\_%' escape '\';







drop table P_OSOBA;
drop table P_ZTP;
drop table P_TYP_POSTIHNUTIA;
drop table P_ZAMESTNANEC;
drop table P_ZAMESTNAVATEL;
drop table P_MESTO;
drop table P_OKRES;
drop table P_KRAJ;
drop table P_KRAJINA;
drop table P_ODVOD_PLATBA;
drop table P_PRISPEVKY;
drop table P_POBERATEL;
drop table P_POISTENIE;
drop table P_HISTORIA;
drop table P_TYP_PRISPEVKU;
drop table P_PLATITEL;



drop type t_adresa;

CREATE or replace TYPE t_adresa
is object
 (
    mesto VARCHAR2(30),
    ulica VARCHAR2(50),
    psc CHAR(5)
 );
/


select ulica from p_osoba;
desc p_osoba;


drop type t_osoba;
CREATE or replace TYPE t_osoba
is object
 (
    meno varchar2(20),
    priezvisko varchar2(30),
    rod_cislo char(11),
    adresa t_adresa
 );
/

create or replace procedure vypis ( p_adresa in t_adresa )
as
begin
    dbms_output.put_line(p_adresa.mesto || ' ' || p_adresa.ulica || ' ' || p_adresa.psc);
end;
/

set serveroutput on;

declare
adresa t_adresa;
begin
    adresa := t_adresa('Nededza', 'Stredna', 01302);
    vypis(adresa);
end;
/