LANG LEBE EXTRACT

--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;