--aufgaben pizza no2

create or replace procedure pppp2(anz_fahrten out number, anz_km_mon out number, anz_km_yea out number, kfz in varchar2, datum in date) as

fahrten number := 0;
kilometer_1 number := 0;
kilometer_2 number := 0;

cursor c1 is SELECT SUM(LIEF.tacho_ab) a_b, SUM(LIEF.tacho_bis) a_a, LIEF.KFZNR, COUNT(LIEF.KFZNR) anz_fahrt from LIEF, FAHRZEUG WHERE LIEF.KFZNR = FAHRZEUG.FNR AND EXTRACT(month FROM LIEF.LIEFDAT) = EXTRACT(month FROM datum) GROUP BY LIEF.KFZNR;
cursor c2 is SELECT SUM(LIEF.tacho_ab) c_b, SUM(LIEF.tacho_bis) c_a from LIEF, FAHRZEUG WHERE LIEF.KFZNR = FAHRZEUG.FNR AND EXTRACT(year FROM LIEF.LIEFDAT) = EXTRACT(year FROM datum);
ca c1%rowtype;
cb c2%rowtype;

begin
OPEN c1;
OPEN c2;
fetch c1 into ca;
fetch c2 into cb;

fahrten := ca.anz_fahrt;
kilometer_1 := (ca.a_a - ca.a_b);
kilometer_2 := (cb.c_a - cb.c_b);

INSERT INTO FAHRZEUG_STAT VALUES(ca.KFZNR, EXTRACT(year FROM datum), EXTRACT(month FROM datum), ca.anz_fahrt, kilometer_1, kilometer_2, t1.nextval );

anz_fahrten := fahrten;
anz_km_mon := kilometer_1;
anz_km_yea := kilometer_2;

CLOSE c2;
CLOSE c1;
end;


declare
datum date := to_date('11-2015', 'MM-YYYY');
kfz number := 1;
km_mon number := 0;
km_yea number := 0;
anz_fahrten number := 0;
begin
    pppp2(anz_fahrten, km_mon, km_yea, kfz, datum);
    dbms_output.put_line(anz_fahrten || ', ' || km_mon || ', ' || km_yea);
end;

create table FAHRZEUG_STAT(
fahrzeug_id number(30) not null,
jahr number(30),
monat number(30),
lieferungen number(30),
km_monat number(30),
km_jahr number(30),

CONSTRAINT pk_1 PRIMARY KEY(fahrzeug_id)
);

create sequence t1
start with 1
increment by 1;