Begin
  HDBMS15.MEGOLDAS_FELTOLT(100,
  '/');
End;

Begin 
  megoldasaim;
End;

SELECT fel.AZON, to_char(fel.HATARIDO, 'YYYY.MM.DD. HH24:MI') "Határidő", 
  meg.JO, meg.MEGJEGYZES, meg.MEGOLDAS, fel.FELADAT
FROM HDBMS15.FELADATAIM fel left join HDBMS15.MEGOLDASAIM meg on fel.AZON = meg.FELADAT_AZON
WHERE fel.AZON IN(
  SELECT AZON FROM HDBMS15.FELADATAIM
    MINUS
  SELECT FELADAT_AZON FROM HDBMS15.MEGOLDASAIM WHERE lower(JO)='i'
)
ORDER BY fel.AZON;



CREATE OR REPLACE PROCEDURE megoldasaim
  AUTHID CURRENT_USER
AS
  v_all number;
  v_correct number;
  v_waiting number;
Begin
  EXECUTE IMMEDIATE 'SELECT count(*) FROM HDBMS15.FELADATAIM' INTO v_all;
  EXECUTE IMMEDIATE '
    SELECT count(*) 
    FROM HDBMS15.MEGOLDASAIM 
    WHERE JO = ''i''' 
    INTO v_correct;
  EXECUTE IMMEDIATE '
    SELECT count(*) 
    FROM HDBMS15.MEGOLDASAIM 
    WHERE JO = ''?''' 
    INTO v_waiting;
  dbms_output.put_line('Elfogadva:           ' 
    || lpad(to_char(v_correct / v_all * 100, 'FM00.0') || '%', 6));
  dbms_output.put_line('Várakozókkal együtt: ' 
    || lpad(to_char((v_correct + v_waiting) / v_all * 100, 'FM00.0') || '%', 6));
End;

-- http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#SQLRF01702
-- http://docs.oracle.com/cd/E11882_01/server.112/e10831/diagrams.htm#CIHGFFHI

-- ###########################################################################
-- ###########################################################################
-- ###########################################################################

-- 101
-- Listázzuk ki az ügyfelek (customers) nevét és születési dátumát 
-- születési dátum szerint csökkenően, azon belül név szerint növekvően.
SELECT cust_last_name, cust_first_name, to_char(date_of_birth, 'YYYY.MM.DD.') 
FROM oe.customers 
ORDER BY date_of_birth DESC, cust_last_name ASC, cust_first_name ASC;

-- 102
-- Listázzuk ki, hogy az egyes ügyfelek (customers) mikor rendeltek (orders) utoljára. 
-- A lista legyen dátum szerint növekvően rendezve. 
-- Azokat az ügyfeleket is listázzuk, akik nem rendeltek.
SELECT cus.CUSTOMER_ID, cus.CUST_LAST_NAME, cus.CUST_FIRST_NAME, 
  to_char(max(ord.ORDER_DATE), 'YYYY.MM.DD.') AS last_order_date
FROM OE.CUSTOMERS cus LEFT JOIN OE.ORDERS ord ON cus.CUSTOMER_ID = ord.CUSTOMER_ID
GROUP BY cus.CUSTOMER_ID, cus.CUST_LAST_NAME, cus.CUST_FIRST_NAME
ORDER BY last_order_date;

-- 103
-- Listázzuk ki, hogy a Bombay nevű raktárban (warehouse) 
-- milyen nevű termékekből (product_name) hány darab található. 
-- Azzal a termékkel kezdjük a listát, amelyikből a legtöbb van.
SELECT inf.PRODUCT_NAME, inv.QUANTITY_ON_HAND db
FROM OE.INVENTORIES inv NATURAL JOIN OE.PRODUCT_INFORMATION inf
WHERE inv.WAREHOUSE_ID =
  (SELECT war.WAREHOUSE_ID
  FROM OE.WAREHOUSES war
  WHERE war.WAREHOUSE_NAME='Bombay')
ORDER BY db DESC;

-- 104
-- Minden 20 évnél fiatalabb ügyfél 10% kedvezményt kap a rendelés árából. 
-- Az életkort viszonyítsuk a rendelés dátumához. 
-- Módosítsuk ennek megfelelően a megrendelés (orders) táblát.
/*UPDATE OE_ORDERS
SET ORDER_TOTAL = ORDER_TOTAL * 0.9
WHERE ORDER_ID IN(
  SELECT ord.ORDER_ID
  FROM OE_CUSTOMERS cus JOIN OE_ORDERS ord ON cus.CUSTOMER_ID = ord.CUSTOMER_ID
  WHERE MONTHS_BETWEEN(cus.DATE_OF_BIRTH, ord.ORDER_DATE) / 12 > 20
);*/
/*UPDATE OE_ORDERS ord
SET ORDER_TOTAL = ORDER_TOTAL * 0.9
WHERE (
  (SELECT Count(*)
  FROM OE_CUSTOMERS cus
  WHERE cus.CUSTOMER_ID = ord.CUSTOMER_ID
    AND MONTHS_BETWEEN(cus.DATE_OF_BIRTH, ord.ORDER_DATE) / 12 < 20
  ) > 0
);*/
UPDATE OE_ORDERS ord
SET ORDER_TOTAL = ORDER_TOTAL * 0.9
WHERE ord.CUSTOMER_ID IN(
  SELECT cus.CUSTOMER_ID
  FROM OE_CUSTOMERS cus
  WHERE cus.CUSTOMER_ID = ord.CUSTOMER_ID
    AND MONTHS_BETWEEN(cus.DATE_OF_BIRTH, ord.ORDER_DATE) / 12 < 20
  )
;

-- 105
-- Listázzuk ki azokat a megrendeléseket (az azonosítójukat és a dátumukat 
-- illetve a megrendelő nevét), amelyeken 5-nél több sor szerepel (order_item)
SELECT ord.ORDER_ID, TO_CHAR(ord.ORDER_DATE, 'YYYY.MM.DD.') "date", 
  cus.CUST_LAST_NAME, cus.CUST_FIRST_NAME
FROM OE_ORDERS ord JOIN OE_CUSTOMERS cus ON ord.CUSTOMER_ID = cus.CUSTOMER_ID
WHERE ord.ORDER_ID IN(
  SELECT ORDER_ID
  FROM OE_ORDER_ITEMS
  GROUP BY ORDER_ID
  HAVING count(*) > 5
);

-- 106
-- Melyek azok a termékek, amelyekből még egyáltalán nem rendeltek?
SELECT * 
FROM OE_PRODUCT_INFORMATION
WHERE PRODUCT_ID IN(
  SELECT PRODUCT_ID
  FROM OE_PRODUCT_INFORMATION
    MINUS
  SELECT pro.PRODUCT_ID
  FROM OE_PRODUCT_INFORMATION pro 
  JOIN OE_ORDER_ITEMS ord ON pro.PRODUCT_ID = ord.PRODUCT_ID
);

-- 107
-- Hozzunk létre nézetet, amely listázza, az ügyfelek bevételi szintjeiként 
-- (income level) és havonta a megrendelések összegét.
CREATE VIEW oe_havi_rendelesek AS
SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, MONTH, PRICE
FROM OE_CUSTOMERS
NATURAL JOIN
  (SELECT CUSTOMER_ID, TO_CHAR(ORDER_DATE, 'YYYY.MM.') MONTH, sum(ORDER_TOTAL) PRICE
  FROM OE_ORDERS
  GROUP BY CUSTOMER_ID, TO_CHAR(ORDER_DATE, 'YYYY.MM.'))
;

-- 108
-- Szúrjunk be egy új sort az ORDERS táblába. Minden oszlopot töltünk ki.
INSERT INTO OE_ORDERS (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, 
ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID)
VALUES(100, SYSDATE, 'online', 101, 0, 15000, null, null);

-- 109
-- "Hozzunk létre táblát raktar_reszlegek néven. A tábla oszlopai legyenek:
-- azonosito (szám típusú)
-- raktar_azonosito (külső kulcs, mutat a warehouses táblára),
-- alapitas_datuma (dátum típusú),
-- reszleg_neve (karaktersorozat, max 50 hosszú).
-- A tábla elsődleges kulcsa összetett, az azonosito és a raktar_azonosito együtt."
CREATE TABLE OEHR__raktar_reszlegek(
  azonosito INT NOT NULL,
  raktar_azonosito NUMBER(3,0) NOT NULL,
  alapitas_datuma DATE,
  reszleg_neve VARCHAR2(50),
  CONSTRAINT PK_raktar_reszlegek PRIMARY KEY (azonosito, raktar_azonosito),
  CONSTRAINT FK_raktar_reszlegek__raktar FOREIGN KEY (raktar_azonosito) 
    REFERENCES OEHR_WAREHOUSES(WAREHOUSE_ID)
);

-- 110
-- Listázzuk ki azokat a termékek nevét, amelyeknek a leírásában (description) 
-- szerepel a motherboard szó (mindegy, hogy kis vagy nagybetűkkel), 
-- vagy a card szó, és a listaára  50 és 100 között van vagy nincs kitöltve.
SELECT PRODUCT_NAME
FROM OE_PRODUCT_INFORMATION
WHERE 
      (lower(PRODUCT_DESCRIPTION) LIKE '%motherboard%' 
    OR 
      lower(PRODUCT_DESCRIPTION) LIKE '%card%')
  AND
      (LIST_PRICE is null
    OR 
      LIST_PRICE between 50 and 100)
;

-- ###########################################################################
-- ###########################################################################
-- ###########################################################################

-- 201
-- Írj blokkot, amely kiírja a "képernyőre", hogy "Szép az élet".
Begin
  DBMS_OUTPUT.PUT_LINE('Szép az élet');
End;

-- 202
-- Írj blokkot, amely kiírja a "képernyőre" a rendszeridőt és a saját felhasználói 
-- nevedet. A feladathoz használt a sysdate és a user függvényeket.
Begin
  DBMS_OUTPUT.PUT_LINE(to_char(SYSDATE, 'YYYY.MM.DD. HH24:MI:SS'));
  DBMS_OUTPUT.PUT_LINE(USER);
End;

-- 203
-- Írjunk blokkot, amely kiírja az első 10 négyzetszámot!
Begin
  FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(i*i);
  END LOOP;
End;

-- 204
-- Írjunk blokkot, amely egy tetszőlegesen választott számot megvizsgál, 
-- és kiírja azt a tényt, hogy 6-tal osztható-e. Ha nem osztható 6-tal, 
-- akkor kiírja, hogy 2-vel osztható-e, illetve, hogy 3-mal osztható-e. 
-- A blokkot próbáljuk ki több számmal is helyettesítési változó segítségével 
-- (a helyettesítési változót nem lehet feltölteni, helyette konkrét szám legyen feltöltve). 
Declare
  num NUMBER := to_number('&A');
Begin
  IF num mod 6 = 0 THEN
    DBMS_OUTPUT.put_line('Osztható 6-tal.');
  ELSE
    IF num mod 3 = 0 THEN
      DBMS_OUTPUT.put_line('Osztható 3-mal.');
    END IF;
    IF num mod 2 = 0 THEN
      DBMS_OUTPUT.put_line('Osztható 2-vel.');
    END IF;
  END IF;
End;

-- 205
-- Írjunk blokkot, amely két tetszőlegesen választott egész szám esetén megkeresi 
-- a két szám legnagyobb közös osztóját és legkisebb közös többszörösét. 
-- A blokkot próbáld ki több számra is, helyettesítési változó használatával 
-- (a helyettesítési változót nem lehet feltölteni, helyette konkrét szám legyen feltöltve).
Declare
  szam1 number := to_number('&A');
  szam2 number := to_number('&B');
  sz1 number;
  sz2 number;
  m number;
  lnko number;
  lkkt number;
Begin
  sz1 := szam1;
  sz2 := szam2;
  m := 1;
  WHILE m <> 0 LOOP
    m := sz1 mod sz2;
    sz1 := sz2;
    sz2 := m;
  END LOOP;
  lnko := sz1;
  lkkt := round((szam1*szam2)/lnko);
  DBMS_OUTPUT.PUT_LINE(concat('Legnagyobb közös osztójuk: ', to_char(lnko)));
  DBMS_OUTPUT.PUT_LINE(concat('Legkisebb közös többszörösük: ', to_char(lkkt)));
End;

-- ###########################################################################
-- ###########################################################################
-- ###########################################################################

-- 301
-- Írj blokkot, amelyben deklarálsz egy felhasználói altípust, amelynek 
-- a tartománya 2 jegyű egész számok. Ügyelj rá, hogy a változódeklarációban 
-- (ahol használod az altípust) a tartományt ne lehessen bővíteni. 
-- Deklarálj egy változó ezzel az altípussal. A blokkban adj értéket a változónak. 
-- Próbáld ki több értékre is (tartományon kívüli értékekre is) és nézd meg, hogy mi történik.
Declare
  SUBTYPE t_kicsi_szam IS PLS_INTEGER RANGE 10..99;
  -- SUBTYPE t_kicsi_szam2 IS t_kicsi_szam RANGE 100..999;
  -- v_kicsi_szam t_kicsi_szam2;
  v_kicsi_szam t_kicsi_szam;
Begin
  v_kicsi_szam := 11;
End;

-- 302
-- Deklarálj rekordtípust, amelynek három mezője legyen: 
-- azonosító (number(5)), 
-- név (varchar2(50)), 
-- szul_hely (varchar2(50), nem lehet null, alapértelmezett értéke 'Debrecen'). 
-- Deklarálj egy ilyen típusú változót, a blokk törzsében adj neki értéket. 
-- Írd ki a "képernyőre" a rekord értékeit.
Declare
  TYPE t_szemely IS RECORD (
    azon number(5),
    nev varchar2(50),
    szul_hely varchar2(50) NOT NULL DEFAULT 'Debrecen'
  );
  v_sz t_szemely;
Begin
  v_sz.azon := 10;
  v_sz.nev := 'Teszt Elek';
  DBMS_OUTPUT.PUT_LINE(v_sz.azon || ', ' || v_sz.nev || ', ' || v_sz.szul_hely);
End;

-- 303
-- Írjunk blokkot, amely képernyőre írja a legidősebb ügyfél nevét.
/*Declare
  v_ugyfel OE_CUSTOMERS%RowType;
Begin
  SELECT * INTO v_ugyfel
  FROM OE_CUSTOMERS
  WHERE rownum = 1
  ORDER BY date_of_birth;
  DBMS_OUTPUT.put_line(v_ugyfel.cust_first_name || ' ' || v_ugyfel.cust_last_name);
End;*/
Declare
  v_ugyfel OE_CUSTOMERS%RowType;
Begin
  SELECT * INTO v_ugyfel
  FROM OE_CUSTOMERS
  WHERE date_of_birth = (
    SELECT min(date_of_birth)
    FROM OE_CUSTOMERS
  );
  DBMS_OUTPUT.put_line(v_ugyfel.cust_first_name || ' ' || v_ugyfel.cust_last_name);
End;

-- 304
-- Írjunk blokkot, amely képernyőre írja annak a vevőnek az adatait, 
-- amelyiknek a legtöbb rendelése van.
Declare
  v_ugyfel OE_CUSTOMERS%RowType;
  v_id PLS_INTEGER;
  v_cim OE_CUSTOMERS.CUST_ADDRESS%Type;
Begin
  SELECT CUSTOMER_ID INTO v_id
  FROM (
    SELECT count(*) cnt, CUSTOMER_ID
    FROM OE_ORDERS
    GROUP BY CUSTOMER_ID
  )
  WHERE cnt = (
    SELECT max(cnt)
    FROM (
      SELECT count(*) cnt, CUSTOMER_ID
      FROM OE_ORDERS
      GROUP BY CUSTOMER_ID
    )
  );
  
  SELECT * INTO v_ugyfel
  FROM OE_CUSTOMERS cus
  WHERE CUSTOMER_ID = v_id;
    
  v_cim := v_ugyfel.cust_address;
  
  DBMS_OUTPUT.put_line('ID: ' || v_ugyfel.customer_id);
  DBMS_OUTPUT.put_line('Név: ' || v_ugyfel.cust_first_name || ' ' || v_ugyfel.cust_last_name);
  DBMS_OUTPUT.put_line('Született: ' || to_char(v_ugyfel.date_of_birth, 'YYYY.MM.DD.'));
  DBMS_OUTPUT.put_line('Cím: ' || v_cim.state_province || ', ' 
    || v_cim.postal_code || ' ' || v_cim.city || ', ' || v_cim.street_address);
End;

-- 305
-- Leégett a Beijingben lévő raktár egyrésze. Írjunk blokkot, 
-- amely 10 darabbal csökkent minden ott lévő raktármennyiséget. 
-- Ha nincs 10 darab raktárelem a táblában, akkor töröljük a sort, 
-- egyébként módosítsuk a megfelelő tábla tartalmát.
Declare
  v_warehouse_id OE_WAREHOUSES.WAREHOUSE_ID%TYPE;
Begin
  SELECT WAREHOUSE_ID INTO v_warehouse_id
    FROM OE_WAREHOUSES
    WHERE WAREHOUSE_NAME = 'Beijing';
  UPDATE OE_INVENTORIES 
    SET QUANTITY_ON_HAND = QUANTITY_ON_HAND - 10
    WHERE WAREHOUSE_ID = v_warehouse_id;
  DELETE
    FROM OE_INVENTORIES
    WHERE WAREHOUSE_ID = v_warehouse_id AND QUANTITY_ON_HAND < 0;
End;

-- ###########################################################################
-- ###########################################################################
-- ###########################################################################

-- 401
-- Írjunk blokkot, amelyben deklarálunk egy függvényt, amely paraméterként 
-- egy ügyfél azonosítóját és egy termék azonosítóját kapja, és visszaadja, 
-- hogy az ügyfél az adott termékből hány darabot rendelt összesen. 
-- A blokk minden 'T' beűvel kezdődő nevű ügyfélre és minden 500-nál olcsóbb 
-- (list_price) termékre hívja meg a függvényt, és írja ki az ügyfél nevét, 
-- a termék nevét, a darabszámot a képernyőre.
/*Declare
  CURSOR c_cus_id is 
    SELECT CUSTOMER_ID 
    FROM OE_CUSTOMERS
    WHERE CUST_LAST_NAME like 'T%';
  v_cus_id OE_CUSTOMERS.CUSTOMER_ID%Type;
  CURSOR c_prod_id is
    SELECT PRODUCT_ID
    FROM OE_PRODUCT_INFORMATION
    WHERE LIST_PRICE < 500;
  v_prod_id OE_PRODUCT_INFORMATION.PRODUCT_ID%Type;
  v_ugyfel OE_CUSTOMERS%RowType;
  v_db number;
Begin
  DECLARE
    v_db number;
  FUNCTION func_401 (
    p_ugyf_azon IN OE_CUSTOMERS.CUSTOMER_ID%TYPE, 
    p_termek_azon IN OE_PRODUCT_INFORMATION.PRODUCT_ID%TYPE
  )
  Return PLS_INTEGER AS
  Begin
    SELECT sum(ite.QUANTITY) 
    INTO v_db
    FROM OE_CUSTOMERS cus 
      JOIN OE_ORDERS ord ON cus.CUSTOMER_ID = ord.CUSTOMER_ID
      JOIN OE_ORDER_ITEMS ite ON ord.ORDER_ID = ite.ORDER_ID
    WHERE cus.CUSTOMER_ID = p_ugyf_azon AND ite.PRODUCT_ID = p_termek_azon;
    RETURN v_db;
  End;
  
  OPEN c_cus_id;
  LOOP
    FETCH c_cus_id INTO v_cus_id;
    EXIT WHEN c_cus_id%NOTFOUND;
    SELECT * 
      INTO v_ugyfel
      FROM OE_CUSTOMERS 
      WHERE CUSTOMER_ID = c_cus_id;
    DBMS_OUTPUT(v_ugyfel.cust_first_name || ' ' || v_ugyfel.cust_last_name);
    OPEN c_prod_id;
    LOOP
      FETCH c_prod_id INTO v_prod_id;
      EXIT WHEN c_prod_id%NOTFOUND;
      v_db := func_401(v_prod_id, v_cus_id);
      DBMS_OUTPUT(v_db);
    END LOOP;
    CLOSE c_prod_id;
  END LOOP;
  CLOSE c_cus_id;
End;*/
Declare
  v_db number;
Begin
  DECLARE
    v_db number;
  FUNCTION func_401 (
    p_ugyf_azon IN OE_CUSTOMERS.CUSTOMER_ID%TYPE, 
    p_termek_azon IN OE_PRODUCT_INFORMATION.PRODUCT_ID%TYPE
  )
  Return number AS
  Begin
    SELECT sum(ite.QUANTITY) 
    INTO v_db
    FROM OE_CUSTOMERS cus 
      JOIN OE_ORDERS ord ON cus.CUSTOMER_ID = ord.CUSTOMER_ID
      JOIN OE_ORDER_ITEMS ite ON ord.ORDER_ID = ite.ORDER_ID
    WHERE cus.CUSTOMER_ID = p_ugyf_azon AND ite.PRODUCT_ID = p_termek_azon;
    IF v_db IS NULL THEN
      RETURN 0;
    END IF;
    RETURN v_db;
  End;
  
  Begin
    FOR v_cus IN (SELECT CUSTOMER_ID, cust_first_name, cust_last_name
      FROM OE_CUSTOMERS
      WHERE CUST_LAST_NAME like 'T%')
    LOOP
      Begin
        FOR v_prod IN (SELECT PRODUCT_ID, PRODUCT_NAME
          FROM OE_PRODUCT_INFORMATION
          WHERE LIST_PRICE < 500)
        LOOP
          v_db := func_401(v_cus.CUSTOMER_ID, v_prod.PRODUCT_ID);
          IF v_db = 0 THEN -- Enélkül: buffer owerflow.
            CONTINUE;
          END IF;
          DBMS_OUTPUT.PUT_LINE(v_cus.cust_first_name || ' ' 
            || v_cus.cust_last_name || ', ' || v_prod.PRODUCT_NAME || ': '
            || v_db || ' db');
        END LOOP;
      End;
    END LOOP;
  End;
End;


-- 402
-- Írjunk blokkot, amelyben deklarálunk egy eljárást, amely paraméterként 
-- kap egy számot, és visszaadja annak a szinuszát és koszinuszát. 
-- A blokk meghívja az eljárást 0,15,30,45,60,75,90,...360 fok értékekre 
-- és kiírja a képernyőre a fokot, és annak szinuszát, koszinuszát.
Declare
  v_sin number(38,1);
  v_cos number(38,1);
  procedure sin_cos(p_deg IN number, p_sin OUT number, p_cos OUT number) AS
  Begin
    p_sin := sin(p_deg / 180 * pi());
    p_cos := cos(p_deg / 180 * pi());
  End;
Begin
  FOR i IN 0..(360/15) LOOP
    sin_cos(i*15, v_sin, v_cos);
    DBMS_OUTPUT.put_line(i*15 || '°, sinus: ' || v_sin || ', cosinus: ' || v_cos);
  END LOOP;
End;

-- 403
-- Írjunk tárolt eljárást, amely egy dolgozónak a fizetését emeli (hr schema), 
-- azaz paraméterként egy dolgozó azonosítóját kapja, egy paraméterként kapott 
-- százalékkal megemeli a dolgozó fizetését, majd visszaadja a dolgozó nevét, 
-- és az új fizetésének értékét.
CREATE OR REPLACE PROCEDURE incr_salary(
  p_empl_id     IN  HR_EMPLOYEES.EMPLOYEE_ID%TYPE, 
  p_inc_pct     IN  number,
  p_first_name  OUT HR_EMPLOYEES.FIRST_NAME%Type,
  p_last_name   OUT HR_EMPLOYEES.LAST_NAME%Type,
  p_new_salary  OUT number
) IS 
  TYPE t_ret_info IS RECORD (
    first_name HR_EMPLOYEES.FIRST_NAME%Type,
    last_name HR_EMPLOYEES.LAST_NAME%Type,
    salary number
  );
  v_empl t_ret_info;
Begin
  UPDATE HR_EMPLOYEES
    SET salary = salary * (1 + p_inc_pct / 100)
    WHERE employee_id = p_empl_id
    RETURNING first_name, last_name, salary INTO v_empl;
  p_first_name := v_empl.first_name;
  p_last_name := v_empl.last_name;
  p_new_salary := v_empl.salary;
End;

-- 404
-- Hívjuk meg az előző tárolt eljárást több dolgozóra úgy, hogy a meghívás előtt 
-- keressük ki a dolgozó neve alapján a dolgozó azonosítóját.
-- Próbáljuk ki a fizetés csökkentést is.
Declare
  v_first_name  HR_EMPLOYEES.FIRST_NAME%Type;
  v_last_name   HR_EMPLOYEES.LAST_NAME%Type;
  v_new_salary  number;
Begin
  FOR v_empl IN (
    SELECT EMPLOYEE_ID
    FROM HR_EMPLOYEES
    WHERE FIRST_NAME like 'P%'
  ) LOOP
    incr_salary(v_empl.employee_id, 20, v_first_name, v_last_name, v_new_salary);
  END LOOP;
  incr_salary(100, -5, v_first_name, v_last_name, v_new_salary);
End;

SELECT employee_id, FIRST_NAME, last_name, salary
FROM HR_EMPLOYEES
WHERE FIRST_NAME like 'P%' OR employee_id = 100
order by EMPLOYEE_ID;

-- 405
-- Írjunk tárolt függvényt, amely paraméterként kap egy hónapsorszámot, 
-- és visszaadja, hogy hány olyan ügyfél van, aki az adott hónapban született. 
CREATE OR REPLACE FUNCTION num_of_customers(p_birth_month IN number)
RETURN number IS
  v_count number;
Begin
  SELECT count(*) INTO v_count
  FROM OE_CUSTOMERS
  WHERE to_char(date_of_birth, 'MM') = p_birth_month;
  return v_count;
End;

-- 406
-- Hívjuk meg az előző függvényt lekérdezés segítségével.
SELECT num_of_customers(5)
FROM DUAL;

-- 407
-- Hívjuk meg az előző függvényt egy blokkból. 
Declare
  v_count number;
Begin
  v_count := num_of_customers(5);
  -- DBMS_OUTPUT.put_line(v_count);
End;

-- ###########################################################################
-- ###########################################################################
-- ###########################################################################

-- 501
-- Írjunk blokkot, amelynek a deklarációs részében deklarálunk egy függvény. 
-- A függvény paraméterként kapott ügyfélnévhez megkeresi, hogy az adott ügyfél 
-- hány megrendelést hajtott végre, majd visszaadja ezt a számot. 
-- A blokkból hívjuk meg a függvényt olyan ügyfélnevekkel, amelyekre: 
-- - nincs megrendelés, 
-- - több megrendelés van, 
-- - nem szerepel az ügyfél táblában. Futtassuk a blokkot. Ha kivételt kapunk, 
-- akkor kapjuk el, és írjuk ki a hiba kódját és üzenetét (minden kivétel esetén).
Declare
  v_unused pls_integer;
  FUNCTION sikeres_megrendelesek(
    p_first_name IN OE_CUSTOMERS.cust_first_name%Type,
    p_last_name IN OE_CUSTOMERS.cust_last_name%Type
  ) RETURN pls_integer IS
  v_ret pls_integer;
  Begin
    SELECT count(*) INTO v_ret
    FROM OE_ORDERS
    WHERE order_status != 0 -- csak a sikereseket kell megszámolni
      AND customer_id = (
        SELECT customer_id
        FROM OE_CUSTOMERS
        WHERE cust_first_name = p_first_name
          AND cust_last_name = p_last_name
      );
    return v_ret;
  End;
Begin
  v_unused := sikeres_megrendelesek('Kathleen', 'Walken'); -- Nincs megrendelése.
  v_unused := sikeres_megrendelesek('Sivaji', 'Landis'); -- Több megrendelése is van.
  v_unused := sikeres_megrendelesek('She is', 'Unexists');
EXCEPTION
  WHEN others THEN
    dbms_output.put_line(SQLCODE || SQLERRM);
End;

select * from oe_customers cus left join
(select count(*), customer_id from oe_orders
group by customer_id) ord 
on cus.customer_id = ord.customer_id;

-- 502
-- Írjunk blokkot, amelyben deklarálunk egy függvényt, amely paraméterként egy 
-- dátumot kap (melynek alapértelmezett értéke a rendszeridő), és egy dátumformátumot, 
-- amelynek alapértelmezett értéke alapján a dátumból visszakaphatjuk az órát, 
-- a percet és a másodpercet. A függvény térjen vissza azzal a karaktersorozatból, 
-- amely az adott dátumból nyerünk a dátumformátum segítségével. 
-- 
-- A blokkban 
-- deklaráljunk egy eljárást, amely paraméterként megkapja egy ügyfél nevét, 
-- és visszaadja, hogy az ügyfél az utolsó rendelését melyik időpontban hajtotta 
-- végre (karaktersorozat formátumban), és a megrendelésen hány sor szerepelt. 
-- Az eljárás az időpont konvertálásához használja a függvényt. 
-- 
-- A blokkból hívjuk meg az eljárásunkat több ügyfélre. Ha kivételt dob kajuk el, 
-- és írjuk ki a hiba kódját és üzenetét. A blokk ezen kívül hívja meg a függvényt is,
-- írja ki a futtatáskori időpontot. Az alprogramok meghívásakor próbáljuk ki 
-- a név szerinti kötést és a kevert kötést is. 
Declare
  v_date varchar2(50);
  v_lines pls_integer;
  
  Function date_to_string_502(
    p_date IN date default SYSDATE,
    p_format IN varchar2 default 'HH24:MI:SS'
  ) RETURN varchar2 IS
  Begin
    return to_char(p_date, p_format);
  End;
  
  Procedure last_order_502(
    p_first_name IN OE_CUSTOMERS.cust_first_name%Type,
    p_last_name IN OE_CUSTOMERS.cust_last_name%Type,
    p_last_order OUT varchar2,
    p_last_order_lines OUT pls_integer
  ) IS
  v_last_order_date DATE;
  v_last_order_id OE_ORDERS.order_id%Type;
  v_cust_id OE_CUSTOMERS.customer_id%Type;
  Begin
    SELECT customer_id INTO v_cust_id
    FROM OE_CUSTOMERS
    WHERE cust_first_name = p_first_name
      AND cust_last_name = p_last_name;
    
    SELECT order_date, order_id INTO v_last_order_date, v_last_order_id
    FROM OE_ORDERS
    WHERE customer_id = v_cust_id
      AND order_date = (
        SELECT max(order_date)
        FROM OE_ORDERS
        WHERE customer_id = v_cust_id
      );
    
    p_last_order := date_to_string_502(
      p_format => 'YYYY.MM.DD. HH24:MI:SS',
      p_date => v_last_order_date
    );
    
    SELECT count(*) INTO p_last_order_lines
    FROM OE_ORDER_ITEMS
    WHERE order_id = v_last_order_id;
  End;
Begin
  v_date := date_to_string_502();
  
  last_order_502('Ishwarya', 'Roberts', v_date, v_lines); -- létezik
  dbms_output.put_line(v_lines || ' fajta termek, ekkor: ' || v_date);
  
  last_order_502(
    'Ching Chung',
    'Chang',
    p_last_order_lines => v_lines,
    p_last_order => v_date
  );
  dbms_output.put_line(v_lines || ' fajta termek, ekkor: ' || v_date);
  
Exception
  WHEN others THEN
    dbms_output.put_line(SQLCODE || SQLERRM);
End;

-- 503
-- Írjunk tárolt eljárást, amely paraméterként kapott ügyfél nevéhez visszaadja 
-- az ügyfél születési dátumát és a nemét. (Itt nem kell kivételt kezelni.)
CREATE OR REPLACE PROCEDURE proc_503(
  p_first_name    IN  OE_CUSTOMERS.CUST_FIRST_NAME%Type,
  p_last_name     IN  OE_CUSTOMERS.CUST_LAST_NAME%Type,
  p_gender        OUT OE_CUSTOMERS.GENDER%Type,
  p_date_of_birth OUT OE_CUSTOMERS.DATE_OF_BIRTH%Type
) IS
Begin
  SELECT GENDER, DATE_OF_BIRTH INTO p_gender, p_date_of_birth
  FROM OE_CUSTOMERS
  WHERE CUST_FIRST_NAME = p_first_name AND CUST_LAST_NAME = p_last_name;
End;

-- 504
-- Írjunk blokkot, amely meghívja az előző tárolt eljárást. Hívjuk meg úgy is, 
-- hogy kivételt dob, és vizsgáljuk meg, hogy mi történik. Bővítsük a blokkunkat 
-- úgy, hogy a select into által okozott kivételeket elkapja, majd írja ki, 
-- hogy mi volt a hiba.
Declare
  v_gender        OE_CUSTOMERS.GENDER%Type;
  v_date_of_birth OE_CUSTOMERS.DATE_OF_BIRTH%Type;
  v_customer      OE_CUSTOMERS%RowType;
Begin
  SELECT * INTO v_customer
    FROM OE_CUSTOMERS 
    WHERE customer_id = -1;
  proc_503(v_customer.CUST_FIRST_NAME, v_customer.CUST_LAST_NAME, v_gender, v_date_of_birth);
  DBMS_OUTPUT.PUT_LINE(v_gender || ', ' || to_char(v_date_of_birth, 'YYYY.MM.DD.'));
EXCEPTION
  WHEN NO_DATA_FOUND or TOO_MANY_ROWS THEN
    DBMS_OUTPUT.put_line(SQLCODE || SQLERRM);
End;

-- 505
-- Hozzunk létre táblát csaladtagok néven. A tábla oszlopaiban tároljuk a customers 
-- tábla azonosítóját (külső kulcsként hivatkozzunk rá), és a családtag nevét. 
-- A tábla elsődleges kulcsa legyen a két oszlop együtt.
DROP TABLE csaladtagok;
CREATE TABLE csaladtagok(
  cust_id NUMBER(6,0),
  name varchar2(100),
  CONSTRAINT csaladtagok_PK
    PRIMARY KEY (cust_id, name),
  CONSTRAINT csaladtagok_FK__customers 
    FOREIGN KEY (cust_id) 
    REFERENCES OEHR_CUSTOMERS(CUSTOMER_ID)
);

-- 506
-- Írjunk tárolt függvényt, amely az előző feladat táblájába felvesz egy sort. 
-- A függvény a következő paramétereket kapja: az ügyfél azonosítója és a 
-- családtag neve.Ha a függvény rendben lefutott adja vissza a beszúrt sort. 
-- Ha kivételt kapunk amiatt, hogy egy ügyfélhez két azonos nevű családtagot 
-- veszünk fel, akkor a kapott kivételt kezeljük: írjuk ki a képernyőre, 
-- hogy melyik ügyfél (az ügyfél neve) milyen nevű családtagja már létezik. 
-- A függvény NULL értékekkel térjen vissza.
CREATE OR REPLACE FUNCTION insert_into_csaladtagok(
  p_cust_id IN OE_CUSTOMERS.customer_id%Type,
  p_name IN CSALADTAGOK.name%Type
) RETURN CSALADTAGOK%RowType IS
v_row CSALADTAGOK%RowType;
Begin
  INSERT INTO CSALADTAGOK(cust_id, name)
    VALUES(p_cust_id, p_name)
    RETURNING cust_id, name INTO v_row;
  return v_row;
Exception
  WHEN dup_val_on_index THEN
    Declare
      v_cust OE_CUSTOMERS%RowType;
    Begin
      SELECT * INTO v_cust
      FROM OE_CUSTOMERS
      WHERE customer_id = p_cust_id;
      
      dbms_output.put_line(
        v_cust.cust_first_name 
        || ' ' || v_cust.cust_last_name 
        || ' nevű ügyfél ' || p_name 
        || ' nevű családtagja már szerepel a rendszerben!'
      );
      
      return null;
    End;
End;

-- 507
-- Írjunk tárolt eljárást, amely paraméterként kap egy ügyfélnevet és az ügyfél 
-- egy családtagjának a nevét. Az előző tárolt függvény meghívásával szúrjuk be 
-- a megfelelő sort az első feladat táblájába. Az eljárás írja képernyőre a 
-- függvény által visszaadott értékeket. 
CREATE OR REPLACE PROCEDURE insert_into_csaladtagok_2(
  p_cust_first_name IN OE_CUSTOMERS.cust_first_name%Type,
  p_cust_last_name IN OE_CUSTOMERS.cust_last_name%Type,
  p_name IN CSALADTAGOK.name%Type
) IS
v_row CSALADTAGOK%RowType;
v_cust_id OE_CUSTOMERS.customer_id%Type;
Begin
  SELECT customer_id INTO v_cust_id
  FROM OE_CUSTOMERS
  WHERE cust_first_name = p_cust_first_name
    AND cust_last_name = p_cust_last_name;
  v_row := insert_into_csaladtagok(v_cust_id, p_name);
  IF v_row.name IS NOT NULL THEN
    dbms_output.put_line(p_cust_first_name || ' ' || p_cust_last_name 
      || ' új családtagja felvéve: ' || v_row.name);
  END IF;
End;

-- 508
-- Írjunk blokkot, amely meghívja az előző feladat tárolt eljárását úgy, hogy az 
-- ügyfél családtagjának a neve null érték. A null érték miatt bekövetkező kivételt 
-- kezeljük, írjuk ki a képernyőre, hogy nincs megadva a családtag neve. 
-- Ugyanebben a blokkban hívjuk meg az eljárást úgy is, hogy olyan ügyfelet adunk 
-- meg, amely nem létezik a customer táblában. Az emiatt kapott kivételt kezeljük, 
-- írjuk ki a képernyőre, hogy nincs ilyen ügyfél. Azt is próbáljuk ki, hogy olyan 
-- ügyfélnevet adjunk meg, amelyből kettő van az adatbázisban. Az ennek megfelelő 
-- kivételt hasonló módon kezeljük. A kivétek kezelése mindig csak arra az egy 
-- eljáráshiváshoz tartozzon.
Declare
  CANNOT_INSERT_NULL Exception;
  PRAGMA EXCEPTION_INIT (CANNOT_INSERT_NULL, -1400);
Begin
  Begin
    insert_into_csaladtagok_2('Kathleen', 'Walken', null); -- létező OE_CUSTOMERS név
  Exception
    WHEN CANNOT_INSERT_NULL THEN
      dbms_output.put_line('Hiba: nincs megadva a családtag neve!');
  End;
  
  Begin
    insert_into_csaladtagok_2('She Is', 'Unexists', 'Bla Bla');
  Exception
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('Hiba: az ügyfél nem létezik a OE_CUSTOMERS táblában!');
  End;
  
  Begin
    insert_into_csaladtagok_2('Kathleen', 'Walken',  -- Ide kellene egy kétszer szereplő név.
      'Wolfeschlegelsteinhausenbergerdorff');
  Exception
    WHEN TOO_MANY_ROWS THEN
      dbms_output.put_line('Hiba: Két ugyanilyen nevű ügyfél is létezik');
  End;
End;

select count(*) from oe_customers group by cust_first_name, cust_last_name;

-- 509
-- Az előző blokk kivételei miatt alakítsuk át a 7. feladat eljárását úgy, hogy 
-- a 8. feladatban szereplő kivételeket kapja el, kezelje az ott megadottak szerint. 
-- A kivételkezelő rész csak 8. feladat kivételeivel foglalkozzon.
CREATE OR REPLACE PROCEDURE insert_into_csaladtagok_3(
  p_cust_first_name IN OE_CUSTOMERS.cust_first_name%Type,
  p_cust_last_name IN OE_CUSTOMERS.cust_last_name%Type,
  p_name IN CSALADTAGOK.name%Type
) IS
v_row CSALADTAGOK%RowType;
v_cust_id OE_CUSTOMERS.customer_id%Type;
Begin
  SELECT customer_id INTO v_cust_id
  FROM OE_CUSTOMERS
  WHERE cust_first_name = p_cust_first_name
    AND cust_last_name = p_cust_last_name;
  v_row := insert_into_csaladtagok(v_cust_id, p_name);
  IF v_row.name IS NOT NULL THEN
    dbms_output.put_line(v_row.cust_id || ', ' || v_row.name);
  END IF;
Exception
  WHEN ACCESS_INTO_NULL THEN
    dbms_output.put_line('Hiba: nincs megadva a családtag neve!');
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('Hiba: az ügyfél nem létezik a OE_CUSTOMERS táblában!');
  WHEN TOO_MANY_ROWS THEN
    dbms_output.put_line('Hiba: Két ugyanilyen nevű ügyfél is létezik');
End;

-- ###########################################################################
-- ###########################################################################
-- ###########################################################################

-- 601
-- Írjunk tárolt függvényt, amely paraméterként megkapja egy ügyfél nevét, 
-- és egy termék nevét. A függvény visszaadja, hogy az adott ügyfél az adott 
-- termékből összesen hányszor rendelt. Ha az ügyfél nem létezik az adatbázisban, 
-- akkor dobjunk felhasználói kivételt -20001-es kóddal és "Nincs ilyen ügyfél"
-- hibaüzenettel. Ha a termék nem létezik, akkor -20002-es kódú kivételt dobjunk 
-- "Nincs ilyen termék hibaüzenettel". 
CREATE OR REPLACE FUNCTION order_count_601(
  p_cust_first_name IN OE_CUSTOMERS.cust_first_name%Type,
  p_cust_last_name IN OE_CUSTOMERS.cust_last_name%Type,
  p_product_name IN OE_PRODUCT_INFORMATION.PRODUCT_NAME%Type
) RETURN pls_integer IS
  v_count pls_integer;
  v_cust_id OE_CUSTOMERS.CUSTOMER_ID%Type;
  v_prod_id OE_PRODUCT_INFORMATION.PRODUCT_ID%Type;
Begin

  Begin
    SELECT CUSTOMER_ID INTO v_cust_id
    FROM OE_CUSTOMERS
    WHERE cust_first_name = p_cust_first_name 
      AND cust_last_name = p_cust_last_name;
  /*IF v_cust_id IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'Nincs ilyen ügyfél');
  END IF;*/
  Exception
    When NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20001, 'Nincs ilyen ügyfél');
  End;
  
  Begin
    SELECT PRODUCT_ID INTO v_prod_id
    FROM OE_PRODUCT_INFORMATION
    WHERE PRODUCT_NAME = p_product_name;
 /* IF v_prod_id IS NULL THEN
    RAISE_APPLICATION_ERROR(-20002, 'Nincs ilyen termék');
  END IF;*/
  Exception
    When NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20002, 'Nincs ilyen termék');
  End;
  
  SELECT count(*) INTO v_count
  FROM oe_customers cus
    JOIN oe_orders ord ON cus.CUSTOMER_ID = ord.CUSTOMER_ID
    JOIN oe_order_items ite ON ord.ORDER_ID = ite.ORDER_ID
  WHERE cus.CUSTOMER_ID = v_cust_id
    AND ite.PRODUCT_ID = v_prod_id;
  
  return v_count;
End;

-- 602
-- Írjunk blokkot, amely meghívja az előző függvényt, képernyőre írja a visszaadott 
-- értéket a meghívott paraméterekkel. Ha a függvényt -20001-es vagy -20002-es 
-- kódú kivételt dobja, akkor kezeljük őket (csak ezeket), és írjuk képernyőre 
-- a hiba kódját és üzenetét. 
Declare
  v_cust_first_name OE_CUSTOMERS.cust_first_name%Type     := 'Ishwarya';
  v_cust_last_name OE_CUSTOMERS.cust_last_name%Type       := 'Roberts';
  v_product_name OE_PRODUCT_INFORMATION.PRODUCT_NAME%Type := 'KB 101/FR';
  v_count pls_integer;
  CUSTOMER_UNEXISTS EXCEPTION;
  PRODUCT_UNEXISTS EXCEPTION;
  PRAGMA EXCEPTION_INIT(CUSTOMER_UNEXISTS, -20001);
  PRAGMA EXCEPTION_INIT(PRODUCT_UNEXISTS, -20002);
Begin
  v_count := order_count_601(v_cust_first_name, v_cust_last_name, v_product_name);
  dbms_output.put_line(
    v_cust_first_name || ' ' || v_cust_last_name
    || 'nevű vásárló a ' || v_product_name || ' termékből ' 
    || v_count || ' darabot vásárolt.');
Exception
  WHEN CUSTOMER_UNEXISTS OR PRODUCT_UNEXISTS THEN
    dbms_output.put_line(SQLCODE || SQLERRM);
End;
/*
select * from oe_customers cus
    JOIN oe_orders ord ON cus.CUSTOMER_ID = ord.CUSTOMER_ID
    JOIN oe_order_items ite ON ord.ORDER_ID = ite.ORDER_ID
    where cus.customer_id = 147;
select * from oe_product_information where product_id = 3110;
*/

-- 603
-- Írjunk tárolt eljárást, amely paraméterként megkapja egy felhasználónak a nevét, 
-- és explicit kurzor segítségével a képernyőre írja a felhasználó 
-- tábláinak, nézeteinek, tárolt eljárásainak és függvényeinek, és szekvenciáinak 
-- a nevét (jelöljük, hogy melyik név milyen típusú objektumhoz tartozik.) 
-- Azt is írja ki, hogy a felhasználónak hány ilyen objektuma van. 
-- 
-- Ha az adott felhasználó nem létezik, akkor dobjunk kivételt -20001-es kóddal 
-- és 'Nincs ilyen felhasználó' üzenettel, melyhez fűzzük hozzá a keresett 
-- felhasználónevet. Ha meghívásnál nem adunk meg felhasználói nevet, 
-- akkor a hívó felhasználó nevével hívjuk meg. 
-- A tárolt eljárás a hívó jogosultságaival fusson. 
CREATE OR REPLACE PROCEDURE show_user_tables_603(
  p_user_name Varchar2 := USER
) AUTHID CURRENT_USER
IS
  CURSOR c_objects(
    p_owner VARCHAR2,
    p_object_type VARCHAR2
  ) IS 
  SELECT *
  FROM ALL_OBJECTS
  WHERE owner = p_owner
    AND object_type = p_object_type;
  v_row ALL_OBJECTS%RowType;
  -- v_count pls_integer;
  v_user_exists pls_integer;
  v_object_type ALL_OBJECTS.object_type%Type;
  v_message VARCHAR(100);
Begin
  /*SELECT count(*) INTO v_count
    FROM ALL_USERS
    WHERE USERNAME = p_user_name;
  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Nincs ilyen felhasználó: ' || p_user_name);
  END IF;*/
  SELECT 1 INTO v_user_exists 
    FROM ALL_USERS 
    WHERE USERNAME = p_user_name;
  
  dbms_output.put_line('A(z) ' || p_user_name 
    || ' nevű felhasználónak a következő objektumai vannak: ');
  dbms_output.new_line;
  
  FOR i IN 1..5 LOOP
    CASE i
      WHEN 1 THEN
        v_object_type := 'TABLE';
        v_message := 'Tábla';
      WHEN 2 THEN
        v_object_type := 'VIEW';
        v_message := 'Nézet';
      WHEN 3 THEN
        v_object_type := 'PROCEDURE';
        v_message := 'Tárolt eljárás';
      WHEN 4 THEN
        v_object_type := 'FUNCTION';
        v_message := 'Függvény';
      WHEN 5 THEN
        v_object_type := 'SEQUENCE';
        v_message := 'Szekvencia';
    END CASE;
      /*SELECT count(*) INTO v_count
      FROM ALL_OBJECTS
      WHERE owner = p_user_name
        AND object_type = v_object_type;
      dbms_output.new_line;
      dbms_output.put_line(v_message || ': ' || v_count || ' db');*/
      /*IF v_count > 0 THEN
        dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - -');
      END IF;*/
      OPEN c_objects(p_user_name, v_object_type);
      If c_objects%RowCount > 0 Then
        dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - -');
      End If;
      LOOP
        FETCH c_objects INTO v_row;
        EXIT WHEN c_objects%NOTFOUND;
        dbms_output.put_line(v_row.object_name);
      END LOOP;
      dbms_output.put_line(v_message || ': ' || c_objects%RowCount || ' db');
      dbms_output.new_line;
      CLOSE c_objects;
  END LOOP;
  
Exception
  When NO_DATA_FOUND Then
    RAISE_APPLICATION_ERROR(-20001, 'Nincs ilyen felhasználó: ' || p_user_name);
End;

-- 604
-- Hívjuk meg az előző feladat tárolt eljárását, kapjuk el a lehetséges kivételt, 
-- és írjuk a képernyőre a hiba üzenetét. 
Declare
  NINCS_ILYEN_FELHASZNALO EXCEPTION;
  Pragma EXCEPTION_INIT(NINCS_ILYEN_FELHASZNALO, -20001);
Begin
  show_user_tables_603('HDBMS15');
  dbms_output.put_line('*****************************************************');
  dbms_output.new_line;
  show_user_tables_603();
  dbms_output.put_line('*****************************************************');
  dbms_output.new_line;
  show_user_tables_603('__UNEXISTS__');
Exception
  WHEN NINCS_ILYEN_FELHASZNALO THEN
    dbms_output.put_line('[' || SQLCODE || '] ' || SQLERRM);
End;

-- 605
-- Adjunk jogot egy társunknak, hogy meghívhassa a 603-as feladat tárolt eljárását. 
GRANT EXECUTE ON show_user_tables_603 TO HDBMS15;
REVOKE EXECUTE ON show_user_tables_603 FROM HDBMS15;

-- 606
-- Hívjuk meg az előző feladat tárolt eljárását, azonban most egy társunkét, 
-- kapjuk el a lehetséges kivételt, és írjuk a képernyőre a hiba üzenetét. 
/*ALTER SESSION SET CURRENT_SCHEMA = HDBMS15;
ALTER SESSION SET CURRENT_SCHEMA = H_X5RJ0X;
*/
Declare
  NINCS_ILYEN_FELHASZNALO EXCEPTION;
  Pragma EXCEPTION_INIT(NINCS_ILYEN_FELHASZNALO, -20001);
Begin
  H_M6NQEL.FELHASZNALO_OBJEKTUMAI('__UNEXISTS__');
Exception
  WHEN NINCS_ILYEN_FELHASZNALO THEN
    dbms_output.put_line('[' || SQLCODE || '] ' || SQLERRM);
End;

-- 607
-- Írjunk tárolt függvény, amely visszaadja a PI értékét. 
-- A függvény az első kiszámítás után jegyezze meg az értéket.
CREATE OR REPLACE FUNCTION pi
RETURN number DETERMINISTIC IS 
Begin
  return acos(-1);
End;

-- 608
-- Hívjuk  meg az előző feladat függvényt
Begin
  dbms_output.put_line(pi());
End;

-- ###########################################################################
-- ###########################################################################
-- ###########################################################################

-- 701
-- Írjunk tárolt eljárást, amely paraméterként kapott ügyfél nevéhez képernyőre 
-- listázza az ügyfél által megrendelt termékek nevét (mindegyiket csak egyszer).
-- Az eljárás végén képernyőre írja az összértéket, amelyért az ügyfél eddig
-- összesen rendelt. (A feladat első részét kurzor for ciklussal oldjuk meg.)
CREATE OR REPLACE PROCEDURE megrendelt_termekek(
  p_first_name OE_CUSTOMERS.cust_first_name%Type,
  p_last_name OE_CUSTOMERS.cust_last_name%Type
) IS
  v_money number;
Begin
  FOR i IN (
    SELECT DISTINCT pro.product_name
    FROM OE_CUSTOMERS cus
      JOIN OE_ORDERS ord ON ord.customer_id = cus.customer_id
      JOIN OE_ORDER_ITEMS ite ON ite.order_id = ord.order_id
      JOIN OE_PRODUCT_INFORMATION pro ON pro.product_id = ite.product_id
    WHERE cus.cust_first_name = p_first_name
      AND cus.cust_last_name = p_last_name
  )LOOP
    dbms_output.put_line(i.product_name);
  END LOOP;
  
  SELECT sum(ord.order_total) INTO v_money
  FROM OE_CUSTOMERS cus
    JOIN OE_ORDERS ord ON ord.customer_id = cus.customer_id
  WHERE cus.cust_first_name = p_first_name
    AND cus.cust_last_name = p_last_name;
  dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - -');
  dbms_output.put_line('Eddigi rendelések összértéke: ' || v_money);
End;

CALL megrendelt_termekek('Ishwarya', 'Roberts');

-- 702
-- Írjunk triggert, amely akkor indul el, ha a customer táblába új sor kerül be 
-- vagy a customer tábla marital_status vagy gender oszlop módosul. 
-- A trigger vizsgálja meg, hogy a gender-nek megfelel-e a marital_status. 
-- Nő esetén hajadon, férjes, özvegy lehet a marital_status, férfi esetén 
-- nőtlen, nős, özvegy lehet a marital status. Ha nem felel meg 
-- a két oszlop egymásnak, akkor dobjunk felhasználói kivételt -20010-es kóddal és 
-- "Nem megfelelő nem és/vagy családi állapot" üzenettel.
CREATE OR REPLACE TRIGGER tr_check_marital_status
  BEFORE INSERT OR UPDATE OF marital_status, gender ON OE_CUSTOMERS
  FOR EACH ROW
Begin
  IF (:NEW.gender = 'F' AND lower(:NEW.marital_status) NOT IN('hajadon', 'férjes', 'özvegy'))
      OR (:NEW.gender = 'M' AND lower(:NEW.marital_status) NOT IN('nőtlen', 'nős', 'özvegy')) 
  Then
    RAISE_APPLICATION_ERROR(-20010, 'Nem megfelelő nem és/vagy családi állapot');
  End IF;
End;

-- 703
-- Az előző feladat triggerét próbáljuk ki beszúrással és módosítással is.
-- A kapott kivételt kapjuk el és kezeljük.
Declare
  MARITAL_STATUS_EXCEPTION EXCEPTION;
  PRAGMA EXCEPTION_INIT (MARITAL_STATUS_EXCEPTION, -20010);
Begin
  -- jó insert
  INSERT INTO OE_CUSTOMERS(CUST_FIRST_NAME, CUST_LAST_NAME, MARITAL_STATUS, GENDER)
    VALUES('Ching', 'Chung', 'nős', 'M');
  DBMS_OUTPUT.put_line('insert');
  -- jó update
  UPDATE OE_CUSTOMERS SET MARITAL_STATUS = 'özvegy' 
  WHERE CUST_FIRST_NAME = 'Ching' AND CUST_LAST_NAME = 'Chung';
  DBMS_OUTPUT.put_line('update');
  -- rossz insert
  Begin
    INSERT INTO OE_CUSTOMERS(CUST_FIRST_NAME, CUST_LAST_NAME, MARITAL_STATUS, GENDER)
      VALUES('Ching', 'Chung', 'kínai', 'M');
  Exception
    When MARITAL_STATUS_EXCEPTION Then
      DBMS_OUTPUT.put_line(SQLCODE || SQLERRM);
  End;
  -- rossz update
  Begin
    UPDATE OE_CUSTOMERS SET MARITAL_STATUS = 'kínai' 
    WHERE CUST_FIRST_NAME = 'Ching' AND CUST_LAST_NAME = 'Chung';
  Exception
    When MARITAL_STATUS_EXCEPTION Then
      DBMS_OUTPUT.put_line(SQLCODE || SQLERRM);
  End;
End;

-- 704
-- Írjunk triggert, amely akkor indul el, amikor új ügyfelet vagy terméket
-- veszünk fel vagy ügyfelet vagy terméket törlünk. A trigger egy új, napló nevű
-- táblába írja be, hogy melyik felhasználó, mikor melyik táblábát módosította
-- és milyen műveletet hajtott végre. 
DROP TABLE naplo;
/
CREATE TABLE naplo(
  user_name VARCHAR2(200),
  modification_date DATE,
  table_name VARCHAR2(200),
  query_type VARCHAR2(100)
);
/
CREATE OR REPLACE TRIGGER tr_logging_user_changes
  AFTER INSERT OR DELETE ON OE_CUSTOMERS
Declare
  v_query_type VARCHAR2(100);
Begin
  If INSERTING Then
    v_query_type := 'INSERT';
  ElsIf DELETING Then
    v_query_type := 'DELETE';
  End If;
  INSERT INTO naplo(USER_NAME, MODIFICATION_DATE, TABLE_NAME, QUERY_TYPE)
  VALUES(USER, SYSDATE, 'OE_CUSTOMERS', v_query_type);
End;
/
CREATE OR REPLACE TRIGGER tr_logging_product_changes
  AFTER INSERT OR DELETE ON OE_PRODUCT_INFORMATION
Declare
  v_query_type VARCHAR2(100);
Begin
  If INSERTING Then
    v_query_type := 'INSERT';
  ElsIf DELETING Then
    v_query_type := 'DELETE';
  End If;
  INSERT INTO naplo(USER_NAME, MODIFICATION_DATE, TABLE_NAME, QUERY_TYPE)
  VALUES(USER, SYSDATE, 'OE_PRODUCT_INFORMATION', v_query_type);
End;

-- 705
-- Az előző feladat triggerét próbáljuk ki több művelet segítségével.
INSERT INTO OE_CUSTOMERS(CUST_FIRST_NAME, CUST_LAST_NAME)
  VALUES('Ching', 'Chung');
DELETE FROM OE_CUSTOMERS 
  WHERE CUST_FIRST_NAME = 'Ching' AND CUST_LAST_NAME = 'Chung';
INSERT INTO OE_PRODUCT_INFORMATION(product_id)
  VALUES(991166);
DELETE FROM OE_PRODUCT_INFORMATION
  WHERE product_id = 991166;

-- 706
-- Hozzunk létre táblát hallgatok néven. A táblának két oszlop legyen,
-- az egyikben a hallgató nevét, a másikban a hallgató neptunkódját tároljuk.
-- Ez utóbbi legyen a tábla elsődleges kulcsa. 
DROP TABLE hallgatok;
CREATE TABLE hallgatok(
  nev VARCHAR2(200),
  neptun_kod VARCHAR(200),
  CONSTRAINT hallgatok_PK PRIMARY KEY(neptun_kod)
);

-- 707
-- Írjunk triggereket, amelyek a hallgató táblából való törlésre indul el,
-- rendre utasítás előtt, sor előtt, utasítás után, sor után.
-- A triggerek írják ki a képernyőre,
-- hogy ők éppen melyik triggerek, azaz utasítás előtt/után, sor előtt/után.
CREATE OR REPLACE TRIGGER tr_delete_from_hallgatok
  FOR DELETE ON hallgatok
  COMPOUND TRIGGER
BEFORE STATEMENT IS
  Begin
    DBMS_OUTPUT.put_line('utasítás előtt');
  End BEFORE STATEMENT;
BEFORE EACH ROW IS
  Begin
    DBMS_OUTPUT.put_line('sor előtt');
  End BEFORE EACH ROW;
AFTER EACH ROW IS
  Begin
    DBMS_OUTPUT.put_line('sor után');
  End AFTER EACH ROW;
AFTER STATEMENT IS
  Begin
    DBMS_OUTPUT.put_line('utasítás után');
  End AFTER STATEMENT;
End;

-- 708
-- Töröljünk egyszerre 5 sort a hallgatok táblából, amivel kipróbáljuk az előző
-- triggert. Majd írjunk olyan törlést, amely egyetlen sort sem tötöl.
-- Nézzük meg a triggerek által kiírt eredményt.
TRUNCATE TABLE HALLGATOK;

Begin
  INSERT INTO HALLGATOK(NEV, NEPTUN_KOD) VALUES('Ching', 'AABBCC');
  INSERT INTO HALLGATOK(NEV, NEPTUN_KOD) VALUES('Chung', 'AABBCD');
  INSERT INTO HALLGATOK(NEV, NEPTUN_KOD) VALUES('Chang', 'AABBCE');
  INSERT INTO HALLGATOK(NEV, NEPTUN_KOD) VALUES('Cheng', 'AABBCF');
  INSERT INTO HALLGATOK(NEV, NEPTUN_KOD) VALUES('Chong', 'AABBD0');
  DELETE FROM hallgatok
    WHERE nev LIKE 'Ch_ng';
  DELETE FROM hallgatok
    WHERE nev LIKE 'István';
End;

-- ###########################################################################
-- ###########################################################################
-- ###########################################################################

-- 801
-- Írj blokkot, amely explicit kurzor segítségével sorban kilistázza egy adott 
-- (Te válaszd ki) raktár (warehouse) által tárolt termékek (product) nevét, 
-- és azt, hogy hány darab van a raktáron.
Declare
  CURSOR c IS
    SELECT pro.product_name "name", inv.quantity_on_hand "count"
    FROM OE_WAREHOUSES war
      JOIN oe_inventories inv ON inv.warehouse_id = war.warehouse_id
      JOIN oe_product_information pro ON pro.product_id = inv.product_id
    WHERE war.warehouse_name = 'Sydney'
    ORDER BY "name";
  TYPE t_quantity IS RECORD (
    name oe_product_information.product_name%Type,
    count oe_inventories.quantity_on_hand%Type
  );
  v_quantity t_quantity;
Begin
  OPEN c;
  Loop
    FETCH c INTO v_quantity;
    EXIT WHEN c%NOTFOUND;
    dbms_output.put_line(v_quantity.name || ': ' || v_quantity.count);
  End Loop;
  CLOSE c;
End;


-- 802
-- Írj tárolt eljárást, amely paraméterként kapott raktár minden olyan terméke 
-- esetén, amely csak az adott raktárban van, megemeli a termék árát 10%-kal. 
-- A megoldásban explicit kurzort használj, for update, és current of utasításrészekkel.
CREATE OR REPLACE PROCEDURE veszelyessegi_potlek_802 (
  p_raktar OE_WAREHOUSES.warehouse_name%Type
) 
AS
  CURSOR c IS 
      SELECT *
      FROM oe_product_information
      WHERE product_id IN (
        SELECT pro.product_id
        FROM OE_WAREHOUSES war
          JOIN oe_inventories inv ON inv.warehouse_id = war.warehouse_id
          JOIN oe_product_information pro ON pro.product_id = inv.product_id
        WHERE war.warehouse_name = p_raktar
              MINUS
        SELECT pro.product_id
        FROM OE_WAREHOUSES war
          JOIN oe_inventories inv ON inv.warehouse_id = war.warehouse_id
          JOIN oe_product_information pro ON pro.product_id = inv.product_id
        WHERE war.warehouse_name != p_raktar
      )
      FOR UPDATE;
  v_row oe_product_information%RowType;
Begin
  OPEN c;
  LOOP
    FETCH c INTO v_row;
    EXIT WHEN c%NOTFOUND;
    UPDATE oe_product_information
      SET list_price = list_price * 1.1
      WHERE CURRENT OF c;
  END LOOP;
  CLOSE c;
End;

-- 803
-- Írj blokkot, amely meghívja az előző feladat tárolt eljárását, és ne feledd 
-- lezárni a tranzakciót.
Begin
  veszelyessegi_potlek_802('Sydney');
  COMMIT;
End;

-- 804
-- Hozz létre táblát konyvek néven, amelyben könyveknek az ISBN számát, címét, 
-- és kiadóját és a kiadás évét tároljuk. A tábla elsődleges kulcsa az ISBN legyen. 
-- 
-- Hozzunk létre táblát példány néven, melyben a könyv raktári számát 
-- és az ISBN számát tároljuk. A tábla elsődleges kulcsa a raktári szám legyen, 
-- az ISBN hivatkozzon a konyv tábla elsődleges kulcsára.
DROP TABLE konyvek;
DROP TABLE peldany;

CREATE TABLE konyvek (
  ISBN        VARCHAR2(20),
  cim         VARCHAR2(500),
  kiado       VARCHAR2(500),
  kiadas_eve  DATE,
  CONSTRAINT konyvek_PK PRIMARY KEY (ISBN)
);
/
CREATE TABLE peldany (
  raktari_szam  INTEGER,
  ISBN          VARCHAR2(20),
  CONSTRAINT peldany_PK PRIMARY KEY (raktari_szam),
  CONSTRAINT peldany__konyvek_FK FOREIGN KEY (ISBN) 
    REFERENCES konyvek(ISBN)
);
/

-- 805
DROP PACKAGE package_805;
-- Hozzunk létre csomagot, amelynek a segítségével az előző feladat két tábláját 
-- használni tudjuk. A csomag tartalmazzon egy beszur_konyv nevű publikus eljárást, 
-- egy töröl könyv nevű publikus függvényt, egy listáz nevű publikus eljárást, 
-- és nincs_ilyen_konyv, nem_megfelelo_konyv, letezo_raktari_szam nevű kivételeket. 
CREATE OR REPLACE PACKAGE package_805 
AS
  nincs_ilyen_konyv EXCEPTION;
  nem_megfelelo_konyv EXCEPTION;
  letezo_raktari_szam EXCEPTION;
  
  TYPE t_konyv_info IS RECORD (
    raktari_szam peldany.raktari_szam%Type,
    cim konyvek.cim%Type,
    kiado konyvek.kiado%Type,
    kiadas_eve konyvek.kiadas_eve%Type
  );
  
  PROCEDURE beszur_konyv (
    p_ISBN          KONYVEK.ISBN%Type,
    p_cim           KONYVEK.cim%Type,
    p_kiado         KONYVEK.kiado%Type,
    p_kiadas_eve    KONYVEK.kiadas_eve%Type,
    p_raktari_szam  PELDANY.raktari_szam%Type
  );
  
  FUNCTION torol_konyv (
    p_raktari_szam peldany.raktari_szam%Type
  ) RETURN PLS_INTEGER;
  
  PROCEDURE listaz (
    p_isbn        IN  peldany.isbn%Type,
    p_result_row  OUT t_konyv_info
  );
End package_805; -- /SIGNATURE
/

CREATE OR REPLACE PACKAGE BODY package_805 
AS
  CURSOR c(p_isbn peldany.isbn%Type) 
  IS 
    SELECT pel.raktari_szam, kon.cim, kon.kiado, kon.kiadas_eve
    FROM peldany pel
    JOIN konyvek kon ON kon.isbn = pel.isbn
    WHERE pel.isbn = p_isbn;
  -- A beszur_konyv nevű eljárás paraméterként kapjon 
  -- ISBN, cím, kiadó, kiadás éve, raktári szám értékeket. 
  -- Először a könyv táblába próbáljon beszúrni, ha ott a könyv megtalálható, 
  -- és az értékek megfelelnek, akkor örülünk, ha nem felelnek meg az értékek, 
  -- akkor a nem_megfelelo_konyv kivételt dobjuk, ha nincs ilyen könyv, akkor beszúrjuk. 
  -- Majd a megfelelő értékeket beszúrjuk a példány táblába. Ha az adott raktári 
  -- számon már létezik a könyv, akkor dobjunk letezo_raktari_szam kivetelt. 
  PROCEDURE beszur_konyv (
    p_ISBN          KONYVEK.ISBN%Type,
    p_cim           KONYVEK.cim%Type,
    p_kiado         KONYVEK.kiado%Type,
    p_kiadas_eve    KONYVEK.kiadas_eve%Type,
    p_raktari_szam  PELDANY.raktari_szam%Type) 
  AS
    v_isbn KONYVEK.ISBN%Type;
    v_konyv KONYVEK%RowType;
  Begin
    Begin
      INSERT INTO konyvek(ISBN, cim, kiado, kiadas_eve)
      VALUES (p_ISBN, p_cim, p_kiado, p_kiadas_eve);
    Exception
      -- Szerepel már a könyv.
      When DUP_VAL_ON_INDEX Then
        SELECT * INTO v_konyv
          FROM konyvek
          WHERE isbn = p_isbn;
        IF v_konyv.cim != p_cim OR v_konyv.kiado != p_kiado 
          OR v_konyv.kiadas_eve != p_kiadas_eve 
        THEN
          RAISE nem_megfelelo_konyv;
        END IF;
    End;
    -- Vegyük fel a példányt!
    Begin
      INSERT INTO peldany (raktari_szam, isbn)
      VALUES (p_raktari_szam, p_isbn);
    Exception
      When DUP_VAL_ON_INDEX Then
        RAISE letezo_raktari_szam;
    End;
  End beszur_konyv;
  
  -- A töröl könyv nevű függvény paraméterként egy raktári számot kapjon, 
  -- és törölje ki csak a példány táblából az adott könyvet, majd visszatérési 
  -- értékként adja vissza, hogy hány példány van még a kitörölt könyvből ugyanazzal 
  -- az ISBN számmal. Ha a raktári szám nem létezik, akkor ne történjen semmi. 
  FUNCTION torol_konyv (
    p_raktari_szam peldany.raktari_szam%Type
  ) RETURN PLS_INTEGER
  AS
    v_count PLS_INTEGER;
    v_isbn peldany.isbn%Type;
  Begin
    DELETE FROM peldany
      WHERE raktari_szam = p_raktari_szam
      RETURNING isbn INTO v_isbn;
    SELECT count(*) INTO v_count
      FROM peldany
      WHERE isbn = v_isbn;
    RETURN v_count;
  Exception
    When NO_DATA_FOUND Then
      NULL;
  End torol_konyv;
  
  -- A listáz nevű eljáráshoz definiáljunk egy privát kurzort, 
  -- amely paraméterként kapott ISBN számhoz listázza a raktári számokat, 
  -- a könyv címeket, a könyv kiadókat és a kiadás évét. 
  -- Az eljárás megnyitja a kurzort, ha még nincs nyitva, egy sort felolvas, 
  -- majd paraméterként visszaadja az eredményeket. 
  -- (Ezt egy publikus rekortípusban tegyük meg.) 
  -- Ha a kurzorban nem találnuk több sort, akkor null értéket adjunk vissza. 
  -- Ha nem talál ilyen ISBN számú könyvet, akkor nincs ilyen könyv kivételt dob.
  PROCEDURE listaz (
    p_isbn        IN  peldany.isbn%Type,
    p_result_row  OUT t_konyv_info
  )
  AS
    v_row t_konyv_info;
    v_exists PLS_INTEGER;
  Begin
    Begin
      SELECT 1 INTO v_exists
      FROM konyvek
      WHERE isbn = p_isbn;
    Exception
      When NO_DATA_FOUND Then
        RAISE nincs_ilyen_konyv;
    End;
    IF NOT c%ISOPEN THEN
      OPEN c(p_isbn);
    END IF;
    FETCH c INTO v_row;
    IF c%FOUND THEN
      p_result_row := v_row;
    ELSE
      p_result_row := NULL;
    END IF;
  End listaz;
  
End package_805; -- /BODY
/

-- 806
<<< Nem adtam be! >>>
-- Próbáljuk ki az előző feladat csomagjának az eszözeit. 
-- Vegyünk fel a táblába sorokat, ugyanolyan ISBN számmal rendelkezőeket is, 
-- próbáljuk ki a lehetséges kivételeket. 
-- Töröljünk könyvet, nézzük meg a függvény visszatérési értékét, 
-- próbáljuk ki a lehetséges kivételt, kapjuk el. A listázd próbáljuk ki úgy, 
-- hogy adott ISBN számhoz minden létező példányt listázzunk ki. 
-- Próbáljuk ki itt is a kivételt, kapjuk el.
/*Begin
  INSERT INTO KONYVEK(ISBN, CIM, KIADO, KIADAS_EVE)
  VALUES(01234567890, 'Asd', 'Ching Chang', 2015);
End;*/

-- ###########################################################################
-- ###########################################################################
-- ###########################################################################

-- 901
-- Írj tárolt eljárást, amely paraméterként kap egy sztringet (clob-ot), 
-- majd a képernyőre listázza abc sorrendben, hogy melyik betűből hány darab van. 
-- Csak azokat a betűket listázza, amelyek szerepelnek a szövegben. 
-- A feladat megoldásához asszociatív tömböt használj.
CREATE OR REPLACE PROCEDURE betuk (p_string CLOB) 
AS
  TYPE t_char_hash IS TABLE OF number INDEX BY VARCHAR2(1);
  v_abc t_char_hash;
  v_length PLS_INTEGER;
  v_char VARCHAR2(1);
  v_key VARCHAR2(1);
Begin
  -- számlálás
  v_length := DBMS_LOB.GETLENGTH(p_string);
  IF v_length != 0 THEN
    FOR i IN 1..v_length LOOP
      v_char := substr(p_string, i, 1);
      IF v_abc.EXISTS(v_char) THEN
        v_abc(v_char) := v_abc(v_char) + 1;
      ELSE
        v_abc(v_char) := 1;
      END IF;
    END LOOP;
  END IF;
  -- kiíratás
  dbms_output.put('(');
  v_key := v_abc.FIRST;
  LOOP
    EXIT WHEN v_key IS NULL;
    dbms_output.put(v_key || ':' || v_abc(v_key));
    v_key := v_abc.NEXT(v_key);
    IF v_key IS NOT NULL THEN
      dbms_output.put(', ');
    END IF;
  END LOOP;
  dbms_output.put_line(')');
End;

-- 902
-- Hívd meg az előző tárolt eljárást különböző sztringekkel.
Begin
  betuk('Ching, Cheng, Chong, Chang, Chunk, Chäng');
  betuk('Ching');
  betuk('');
End;

-- 903
-- Írj tárolt eljárást, amely paraméterként egy sztringet kap, 
-- és amelyről feltételezzük, hogy egy lekérdezést tartalmaz. 
-- Az eljárás vizsgálja meg, hogy a sztring első szava select-e, 
-- ha nem, akkor dobjon felhasználói kivételt 'Nem lekérdezés' üzenettel. 
-- Ha igen, akkor feltételezzük a lekérdezésről, hogy egy olyan rekorddal tér vissza, 
-- amelynek két karaktersorozat típusú oszlopa van. 
-- Az eljárás listázza a képernyőre a lekérdezés eredményét. 
-- Ha a lekérdezés nem megfelelő, akkor az eljárás felhasználói kivételt adjon 
-- 'Nem megfelelő lekérdezés' üzenettel kiegészítve 
-- a kiváltó kivétel kódjával és üzenetével. 
CREATE OR REPLACE PROCEDURE sql_query(p_string STRING) 
AS
  TYPE t_row IS RECORD(
    col_1 VARCHAR2(1000),
    col_2 VARCHAR2(1000)
  );
  TYPE t_tbl IS TABLE OF t_row;
  v_tbl t_tbl;
Begin
  IF upper(substr(p_string, 0, 6)) != 'SELECT' THEN
    RAISE_APPLICATION_ERROR(-20001, 'Nem lekérdezés');
  END IF;
  
  Begin
    EXECUTE IMMEDIATE p_string BULK COLLECT INTO v_tbl;
    FOR i IN v_tbl.FIRST..v_tbl.LAST LOOP
      dbms_output.put_line(v_tbl(i).col_1 || '; ' || v_tbl(i).col_2);
    END LOOP;
  Exception
    When others Then
      RAISE_APPLICATION_ERROR(-20002, 'Nem megfelelő lekérdezés', true);
  End;
End;

-- 904
<<< Nem fogadta el Anikó a rownum használata miatt! >>>
-- "Hívd meg az előző feladat eljárását a következő lekérdezésekre:
--    * a hr séma alapán melyek azon a régiónevek, 
--      és hány ország tartozik hozzájuk, amelyekhez legalább 2 ország tartozik
--    * a legfiatalabb menedzserhez tartozó dolgozók neve
--    * select 1,2 from dual
--    * select 1 from dual
--    * update (azaz nem működő lekérdezésre)"
Begin
  sql_query('SELECT reg.region_name, count(*)
    FROM hr_regions reg
    JOIN hr_countries con ON con.region_id = reg.region_id
    GROUP BY reg.region_id, reg.region_name
    HAVING count(*) >= 2');
Exception When others Then dbms_output.put_line(SQLCODE || SQLERRM);
End;
/

Begin
  dbms_output.new_line;
  sql_query('SELECT employee_table.first_name, employee_table.last_name
    FROM (
      SELECT * 
      FROM (
        SELECT his.start_date, emp.employee_id
        FROM hr_employees emp
        JOIN hr_jobs job ON job.job_id = emp.job_id
        JOIN hr_job_history his ON his.job_id = emp.job_id 
          AND his.employee_id = emp.employee_id
        WHERE lower(job.job_title) LIKE ''%manager%''
        ORDER BY START_DATE DESC
      )
      WHERE rownum = 1
    ) boss_table
    JOIN hr_employees employee_table 
      ON boss_table.employee_id = employee_table.manager_id');
Exception When others Then dbms_output.put_line(SQLCODE || SQLERRM);
End;
/

Begin
  dbms_output.new_line;
  sql_query('select 1,2 from dual');
Exception When others Then dbms_output.put_line(SQLCODE || SQLERRM);
End;
/

Begin
  dbms_output.new_line;
  sql_query('select 1 from dual');
Exception When others Then dbms_output.put_line(SQLCODE || SQLERRM);
End;
/

Begin
  dbms_output.new_line;
  sql_query('update');
Exception When others Then dbms_output.put_line(SQLCODE || SQLERRM);
End;
/

-- 905
-- Írjunk tárolt függvényt, amely paraméterként kap egy warehouse nevet, 
-- és visszaad egy beágyazott táblát, amely az adott warehouse-ban lévő összes 
-- termék nevét (product_name a product_descriptionból) tartalmazza 
-- (mindegyiket csak egyszer). A feladatot együttes hozzárendeléssel oldd meg.
CREATE OR REPLACE TYPE t_product_names_tbl IS TABLE OF VARCHAR2(50);
CREATE OR REPLACE FUNCTION termekek_a_raktarban_905 (
  p_warehouse_name OE_WAREHOUSES.warehouse_name%Type) 
RETURN t_product_names_tbl
AS
  v_tbl t_product_names_tbl;
Begin
  SELECT distinct pro.product_name
  BULK COLLECT INTO v_tbl
  FROM OE_WAREHOUSES war
    JOIN OE_INVENTORIES inv ON war.warehouse_id = inv.warehouse_id
    JOIN OE_PRODUCT_INFORMATION pro ON inv.product_id = pro.product_id
  WHERE war.warehouse_name = p_warehouse_name;
  return v_tbl;
End;

-- 906
-- Hívjuk meg az előző tárolt függvényt, és írjuk ki a képernyőre 
-- a kapott kollekció tartalmát.
Declare
  v_tbl t_product_names_tbl;
Begin
  v_tbl := termekek_a_raktarban_905('Sydney');
  FOR i IN 1..v_tbl.COUNT LOOP
    dbms_output.put_line(v_tbl(i));
  END LOOP;
End;

-- 907
-- Írj blokkot, amelyben deklarálsz három beágyazott táblát, amelynek az elemei 
-- rendre job_title-k, min_salary-k és max_salary-k lesznek. Olvasd fel 
-- a kollekciókba a jobs tábla minden sorát. Majd töröld ki azokat a job_title-ket, 
-- amelyek esetén a min_salary több, mint a max_salary fele. Listázd a megmaradt 
-- job_title-ket a képernyőre. Majd minden olyan dolgozónak, akik ebben 
-- a kollekcióban maradt munkakörben dolgozik, emeljük meg a fizetését a 
-- max_salary 10%-ával. A feladatban használd az együttes hozzárendelést 
-- (BULK COLLECT, FORALL). Ne feledd véglegesíteni a tranzakciót.
Declare
  TYPE t_tbl_job_title IS TABLE OF HR_JOBS.job_title%Type;
  TYPE t_tbl_min_salary IS TABLE OF HR_JOBS.min_salary%Type;
  TYPE t_tbl_max_salary IS TABLE OF HR_JOBS.max_salary%Type;
  v_tbl_job_title t_tbl_job_title;
  v_tbl_min_salary t_tbl_min_salary;
  v_tbl_max_salary t_tbl_max_salary;
  v_index PLS_INTEGER;
Begin
  SELECT job_title, min_salary, max_salary 
    BULK COLLECT INTO v_tbl_job_title, v_tbl_min_salary, v_tbl_max_salary
    FROM HR_JOBS;
  FOR i IN 1..v_tbl_job_title.count LOOP
    IF v_tbl_min_salary(i) > v_tbl_max_salary(i) / 2 THEN
      v_tbl_job_title.DELETE(i);
    END IF;
  END LOOP;
  v_index := v_tbl_job_title.FIRST;
  LOOP
    EXIT WHEN v_index IS NULL;
    dbms_output.put_line(v_tbl_job_title(v_index));
    v_index := v_tbl_job_title.NEXT(v_index);
  END LOOP;
  FORALL i IN INDICES OF v_tbl_job_title
    UPDATE HR_EMPLOYEES emp
    SET emp.salary = emp.salary + v_tbl_max_salary(i) * 0.1
    WHERE (SELECT job.job_title FROM hr_jobs job WHERE job.job_id = emp.job_id
      ) = v_tbl_job_title(i);
  COMMIT;
End;

-- ###########################################################################
-- ###########################################################################
-- ###########################################################################

-- 1001
<<< Nem adtam be! >>>
-- Írj csomagot, amely egy sztring elemű maximum 10 elemű dinamikus tömb kezel. 
-- A dinamikus tömb legyen privát. A csomag inicializációjakor inicializáld 
-- a dinamikus tömböt, de legyen üres. A csomag a következő függvényeket 
-- és eljárásokat tartalmazza:
--   * új elem felvétele (eljárás)
--   * az elemek képernyőre listázása (eljárás)
--   * az utolsó elem törlése (eljárás)
--   * az i-edik elem (függvény) 
--     (ha nem létezik az i-edik elem, akkor null értéket adjon vissza)
--   * a teljes kollekció kiürítése
CREATE OR REPLACE PACKAGE 
AS
End; -- /SIGNATURE
/

CREATE OR REPLACE PACKAGE 
AS
End; -- /BODY
/

-- 1002
<<< Nem adtam be! >>>
-- Próbáld ki az előző csomag minden elemét. 

-- 1003
<<< Nem adtam be! >>>
-- Írjunk blokkot, amely minden olyan customerhez, akinek az első neve 
-- B betűvel kezdődik, és csak egy telefonszáma van, felvesz egy új telefonszámot, 
-- amely az első telefonszámától annyival tér el, hogy az utolsó előtti számjegye 
-- eggyel nagyobb (ha 9-es, akkor 0 lesz). 
Declare
  TYPE t_row IS RECORD (
    customer_id     oe_customers.customer_id%Type,
    phone_numbers   oe_customers.phone_numbers%Type
  );
  TYPE t_result IS TABLE OF t_row;
  v_result t_result;
Begin
  SELECT customer_id, phone_numbers
    BULK COLLECT INTO v_result
    FROM oe_customers
    WHERE cust_first_name like 'B%';
  dbms_output.put_line('asasd');
End;