set serveroutput on size 20000 word_wrapped;

create or replace procedure rez(CodC in number, Nume in varchar2, CNP in number, Adresa in varchar2, Pret in number)
as
begin
    insert into pasager values (CodC, Nume, CNP, Adresa, Pret);
    update curse set nrlib =
        case when (nrlib - 1) > 0
        then
            nrlib - 1
        end;
end;
/

declare
    idx number;
    totalSum number;

procedure afis(CodC in number) as

cursor c_afis is select c.*, t.nume from curse c join tehnic t on c.codp = t.cod where c.codc = CodC;
r c_afis%rowtype;

cursor c_pasageri is select * from pasager p where p.codc = CodC;
r2 c_pasageri%rowtype;

begin
    open c_afis;
    fetch c_afis into r;

    if c_afis%notfound
    then
        dbms_output.put_line('Nu a putut fi gasita acea cursa.');
    else
        dbms_output.put_line('Cursa: ' || r.codc || ', Pilot: ' || r.nume || ', Nr. loc: ' || r.nrloc || ', Nr. Locuri Libere: ' || r.nrlib);
        dbms_output.put_line('Decolare: ' || r.oras_dec || ', Ora: ' || r.ora_dec || ', Aterizare: ' || r.oras_ater || ', Ora: ' || r.ora_ater);
    end if;
    close c_afis;

    open c_pasageri;
    idx := 0;
    totalSum := 0;

    loop
        fetch c_pasageri into r2;
        exit when c_pasageri%notfound;

        dbms_output.put_line('Nr. Crt: ' || idx || ', CNP: ' || r2.cnp || ', Nume: ' || r2.nume || ', Adresa: ' || r2.adresa || ', Pret: ' || r2.pret);

        idx := idx + 1;
        totalSum := totalSum + r2.pret;
    end loop;

    dbms_output.put_line('Suma: ' || totalSum);
    close c_pasageri;
end;
/

begin
    afis(5);
end;
/