SET SERVEROUTPUT ON

/* 1. feladat */

BEGIN
        FOR sor IN (SELECT nev, szuletes
                                FROM ugyfel)
        LOOP
                DBMS_OUTPUT.PUT_LINE('Nev: ' || sor.nev || ', szuletes: ' || sor.szuletes);
        END LOOP;
END;

/* 2. feladat */

DECLARE
        CURSOR cur_ugyfel IS
                SELECT *
                FROM ugyfel;
        sor            ugyfel%ROWTYPE;

BEGIN
        OPEN cur_ugyfel;
                LOOP
                        FETCH cur_ugyfel INTO sor;
                        EXIT WHEN cur_ugyfel%NOTFOUND;
                        CASE cur_ugyfel%ROWCOUNT    
                                WHEN 1 THEN
                                        DBMS_OUTPUT.PUT_LINE('Nev: ' || sor.nev || ', szuletes: ' || sor.szuletes);
                                WHEN 3 THEN
                                        DBMS_OUTPUT.PUT_LINE('Nev: ' || sor.nev || ', szuletes: ' || sor.szuletes);
                                WHEN 6 THEN
                                        DBMS_OUTPUT.PUT_LINE('Nev: ' || sor.nev || ', szuletes: ' || sor.szuletes);
                                ELSE NULL;
                        END CASE;
                END LOOP;
END;

-- Egy egyszerűbb megoldás.

DECLARE
        CURSOR cur_ugyfel IS
                SELECT *
                FROM ugyfel;
        sor            ugyfel%ROWTYPE;

BEGIN
        OPEN cur_ugyfel;
                LOOP
                        FETCH cur_ugyfel INTO sor;
                        EXIT WHEN cur_ugyfel%NOTFOUND;
                        IF cur_ugyfel%ROWCOUNT IN (1, 3, 6) THEN    
                                DBMS_OUTPUT.PUT_LINE('Nev: ' || sor.nev || ', szuletes: ' || sor.szuletes);
                        END IF;
                END LOOP;
END;

/* 3. feladat */

DECLARE
        CURSOR osszeg (dat tranzakcio.datum%TYPE) IS
                SELECT ugyf_azon, SUM(osszeg) ossz
                FROM tranzakcio
                WHERE dat = datum
                GROUP BY ugyf_azon;
BEGIN
        FOR sor IN osszeg(DATE '2007-04-23')
                LOOP
                        DBMS_OUTPUT.PUT_LINE('Azonosito: ' || sor.ugyf_azon || ', osszeg: ' || ABS(sor.ossz));
                END LOOP;
END;

/* 4. feladat */

CREATE VIEW  seged AS (
        SELECT ugyf_azon, SUM(osszeg) ossz, datum
        FROM tranzakcio
        WHERE osszeg < 0
        GROUP BY ugyf_azon);

DECLARE
        CURSOR cur_legn_tranz (dat DATE) IS
                
                SELECT u.azon, u.nev, s.ossz
                FROM (SELECT s1.ugyf_azon, s1.ossz
                        FROM seged s1, seged s2
                        WHERE s1.ossz > s2.ossz and s1.datum = dat and s2.datum = dat
/* Ne felejtsük, negatív számokról van szó, ezért szerepel s1.ossz > s2.ossz, azaz minden ugyfel mellett felsoroljuk
majd azokat, akik nála több pénzt vettek fel az adott napon. */
                        GROUP BY s1.ugyf_azon, s1.ossz
                        HAVING COUNT(DISTINCT s2.ossz) IN (1, 3)) s, ugyfel u
                WHERE s.ugyf_azon = u.azon;

BEGIN
        FOR sor IN cur_legn_tranz ( DATE '2007-04-23')
                LOOP
                        DBMS_OUTPUT.PUT_LINE('Azon: ' || sor.azon || ', nev: ' || sor.nev || ', levett osszeg: '|| 
                                                ABS(sor.ossz));
                END LOOP;
END;

drop view seged;

-- Egy nem pontos, az előzőnél viszont jóval egyszerűbb megoldás.

DECLARE
        TYPE cur_sor IS RECORD (
                ugyf_id               ugyfel.azon%TYPE,
                ugyf_name     ugyfel.nev%TYPE,
                ossz          NUMBER);
        sor            cur_sor;
        CURSOR cur_ugyf_tranz (dat DATE) RETURN cur_sor IS
                SELECT u.azon, u.nev, s.ossz
                FROM (SELECT ugyf_azon, SUM(osszeg) ossz
                                FROM tranzakcio
                                WHERE osszeg < 0 and datum = dat
                                GROUP BY ugyf_azon) s, ugyfel u
                WHERE u.azon = s.ugyf_azon;

BEGIN
        OPEN cur_ugyf_tranz(DATE '2007-04-23');
        LOOP
                FETCH cur_ugyf_tranz INTO sor;
                EXIT WHEN cur_ugyf_tranz%NOTFOUND;
                IF cur_ugyf_tranz%ROWCOUNT IN (2, 4) THEN
                        DBMS_OUTPUT.PUT_LINE('Azon: ' || sor.ugyf_id || ', nev: ' || sor.ugyf_name || 
                                                ', levett osszeg: '|| ABS(sor.ossz));
                END IF;
        END LOOP;
END;

/* 5. feladat */

DECLARE
        db             NUMBER:=0;
BEGIN
        FOR sor IN (SELECT ugyf_azon, SUM(osszeg) ossz
                                FROM szamla
                                GROUP BY ugyf_azon)
                LOOP
                        IF sor.ossz > 50000 THEN
                                UPDATE ugyfel
                                SET keret = 200000
                                WHERE azon = sor.ugyf_azon;
                                db := db+1;
                        END IF;
                END LOOP;
        DBMS_OUTPUT.PUT_LINE(db || ' db sor valtozott meg az ugyfel tablaban.');
END;

-- Egyszerűbb és gyorsabb.

BEGIN
        UPDATE ugyfel
        SET keret = 200000
        WHERE azon IN (SELECT ugyf_azon
                                        FROM szamla
                                        GROUP BY ugyf_azon
                                        HAVING SUM(osszeg) > 50000);
        DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' db sor valtozott meg az ugyfel tablaban.');
END;

/* 6. feladat */

CREATE OR REPLACE TABLE mai_csod AS (
                azon            varchar2(3),
        tartalek        number(9));

DECLARE
        CURSOR csod (dat tranzakcio.datum%TYPE) IS
                        SELECT b.azon, b.tartalek
                        FROM tranzakcio t, reszleg b, szamla sz
                        WHERE t.datum = dat AND t.szaml_azon = sz.azon AND sz.reszl_azon = b.azon AND
                                t.osszeg < 0
                        GROUP BY b.azon, b.tartalek
                        HAVING ABS(SUM(t.osszeg)) > b.tartalek;
BEGIN
        FOR sor IN csod(DATE '2007-04-21')
                LOOP
                        INSERT INTO mai_csod VALUES (sor.azon, sor.tartalek);
                END LOOP;
END;

/* Egyszerűbb megoldás. */

CREATE TABLE mai_csod AS (
        SELECT b.azon, b.tartalek
        FROM tranzakcio t, reszleg b, szamla sz
        WHERE t.datum = DATE '2007-04-21' AND t.szaml_azon = sz.azon AND sz.reszl_azon = b.azon AND
                t.osszeg < 0
        GROUP BY b.azon, b.tartalek
        HAVING ABS(SUM(t.osszeg)) > b.tartalek);
        
/* 7. feladat */

DECLARE
        db             NUMBER(6);
BEGIN
        SELECT COUNT(t.azon)
        INTO db
        FROM tranzakcio t, ugyfel u
        WHERE t.ugyf_azon = u.azon AND ABS(t.osszeg) >= keret * 0.9;
DBMS_OUTPUT.PUT_LINE ('2007. április 21-én ' || db || 'történt, ahol legalább a keret 90%-t felhasználták.');
END;