pl/sql excersises

---------------506--------------
DECLARE
CURSOR c_nyelv IS SELECT DISTINCT language_id FROM oe.product_descriptions;
        CURSOR c_leiras(v_lang_id oe.product_descriptions.language_id%TYPE)
                IS SELECT * FROM oe.product_descriptions WHERE language_id=v_lang_id;

        r_nyelv oe.product_descriptions.language_id%TYPE;
        r_leiras oe.product_descriptions%ROWTYPE;
    BEGIN

        OPEN c_nyelv;
        LOOP
            FETCH c_nyelv INTO r_nyelv;
                EXIT WHEN c_nyelv%NOTFOUND;
                dopl(r_nyelv);
                OPEN c_leiras(r_nyelv);
                LOOP
                    FETCH c_leiras INTO r_leiras;
                    EXIT WHEN c_leiras%NOTFOUND;
                    dopl(CHR(9)|| r_leiras.product_id || ' ' || r_leiras.translated_description);
                END LOOP;
                CLOSE c_leiras;
        END LOOP;
        CLOSE c_nyelv;
    END;

-------701-----------
DROP TABLE sajat_oe_product_information;
CREATE TABLE sajat_oe_product_information AS SELECT * FROM oe.product_information;
CREATE OR REPLACE PROCEDURE increasePruductPrice(
        p_warehouse_name oe.warehouses.warehouse_name%TYPE
    ) IS
        CURSOR c1 IS
            SELECT *
            FROM sajat_oe_product_information
            WHERE product_id IN (SELECT D2.product_id
                                 FROM sajat_oe_INVENTORIES D2
                                 JOIN sajat_oe_WAREHOUSES D3
                                 ON D2.warehouse_id = D3.warehouse_id
                                 WHERE D3.WAREHOUSE_NAME=p_warehouse_name
                                            AND D2.product_id NOT IN (SELECT D2.product_id
                                                                     FROM sajat_oe_INVENTORIES D2
                                                                     JOIN sajat_oe_WAREHOUSES D3
                                                                     ON D2.warehouse_id = D3.warehouse_id
                                                                     WHERE D3.WAREHOUSE_NAME != p_warehouse_name))
            FOR UPDATE;
        v_c1 c1%ROWTYPE;
    BEGIN
        OPEN c1;
        LOOP
            FETCH c1 INTO v_c1;
            EXIT WHEN c1%notfound;
            UPDATE sajat_oe_PRODUCT_INFORMATION
            SET list_price = list_price + list_price * 0.1
            WHERE CURRENT OF c1;
        END LOOP;
        CLOSE c1;
    END;



------702-----
BEGIN
        increasePruductPrice('Sydney');
        COMMIT;
    END;



-----------706-----------
CREATE OR REPLACE PROCEDURE feladat_706 (c_gyenge SYS_REFCURSOR,p_szam OUT NUMBER,p_szoveg OUT VARCHAR) IS
        TYPE t_record IS RECORD (
        id NUMBER,
        leiras VARCHAR2(100));
    BEGIN
        IF (NOT(c_gyenge%ISOPEN)) THEN
            dopl('Nincs nyitva a kurzováltozóó');
        ELSE
            FETCH c_gyenge INTO p_szam, p_szoveg;
        END IF;
        IF c_gyenge%NOTFOUND THEN
                DBMS_OUTPUT.put_line('nincs több sor');
        END IF;
    END;
-----------707-----------

    DECLARE
        TYPE t_egyed IS RECORD (
                id NUMBER,
                leiras VARCHAR2(100));
        TYPE t_eros_dept IS REF CURSOR
            RETURN t_egyed;
        RETURN t_egyed;
        v_eros t_eros_dept;
        v_Egyedek1 t_egyed;
    BEGIN
        OPEN v_eros FOR SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM HR.DEPARTMENTS;
        FOR i IN 1..3
        LOOP
            FETCH v_eros INTO v_Egyedek1;
            feladat_706(v_eros, v_Egyedek1.id, v_Egyedek1.leiras);
            DBMS_OUTPUT.PUT_LINE(v_Egyedek1.id || ' ' || v_Egyedek1.leiras);
        END LOOP;

        OPEN v_eros FOR SELECT EMPLOYEE_ID, (FIRST_NAME || ' ' || LAST_NAME) FROM HR.EMPLOYEES;
        FETCH v_eros INTO v_Egyedek1;
        feladat_706(v_eros, v_Egyedek1.id, v_Egyedek1.leiras);
        DBMS_OUTPUT.PUT_LINE(v_Egyedek1.id || ' ' || v_Egyedek1.leiras);

        EXCEPTION WHEN INVALID_CURSOR THEN
            DBMS_OUTPUT.PUT_LINE('Nincs nyitva a kurzorvaltozo.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Nincs tobb sor');
    END;
-----------803-----------
BEGIN
HDBMS19.megoldas_feltolt(803,'DECLARE
        c VARCHAR2(1 CHAR);
        TYPE t_gyum IS TABLE OF NUMBER INDEX BY c%TYPE;
        gyakorisag t_gyum;

        CURSOR c_gyumolcs IS SELECT NEV FROM GYUMOLCSOK;
        szoveg gyumolcsok.nev%type;
    BEGIN
        OPEN c_gyumolcs;
        LOOP
            FETCH c_gyumolcs INTO szoveg;
            EXIT WHEN c_gyumolcs%NOTFOUND;
                FOR i IN 1..LENGTH(szoveg)
                LOOP
                c := LOWER(SUBSTR(szoveg, i, 1));
                    IF gyakorisag.EXISTS(c) THEN
                        gyakorisag(c) := gyakorisag(c)+1;
                    ELSE
                        gyakorisag(c) := 1;
                    END IF;
                END LOOP;
        END LOOP;
        c := gyakorisag.FIRST;
        WHILE c IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('' " '' || c || '' " '' || gyakorisag(c));
        c := gyakorisag.NEXT(c);
        END LOOP;
    END;/');
END;



----804-----
CREATE OR REPLACE PACKAGE feladat_804
    AS
    letezo_elem EXCEPTION;
    nem_letezo_elem EXCEPTION;
    PROCEDURE add_element(indexe VARCHAR2, elem NUMBER);
    PROCEDURE modify_element(indexe VARCHAR2, elem NUMBER);
    PROCEDURE delete_element(indexe VARCHAR2);
    PROCEDURE delete_between(indextol VARCHAR2, indexig VARCHAR2);
    PROCEDURE read_elements;
    FUNCTION count_indexes RETURN NUMBER;
    FUNCTION read_element(indexe VARCHAR2) RETURN NUMBER;
END feladat_804;
/
CREATE OR REPLACE PACKAGE BODY feladat_804
AS
    TYPE assoc_array
    IS TABLE OF NUMBER(5)
    INDEX BY VARCHAR2(50);
    array_one assoc_array;

    PROCEDURE add_element(indexe VARCHAR2, elem NUMBER)
        IS
        BEGIN
            IF array_one.EXISTS(indexe) THEN
                RAISE letezo_elem;
            ELSE
                array_one(indexe):=elem;
            END IF;
    END add_element;

    PROCEDURE modify_element(indexe VARCHAR2, elem NUMBER)
        IS
        BEGIN
            IF (NOT(array_one.EXISTS(indexe))) THEN
                RAISE nem_letezo_elem;
            ELSE
                array_one(indexe):=elem;
            END IF;
    END modify_element;

    PROCEDURE delete_element(indexe VARCHAR2)
    IS
    BEGIN
    array_one.DELETE(indexe);
    END delete_element;

    PROCEDURE delete_between(indextol VARCHAR2, indexig VARCHAR2)IS
    BEGIN
    array_one.DELETE(indextol,indexig);
    END delete_between;

    PROCEDURE read_elements IS
    indexe VARCHAR2(50);
    BEGIN
    indexe := array_one.FIRST;
        WHILE indexe IS NOT NULL
        LOOP
        dopl(indexe || ' = ' || array_one(indexe));
        indexe := array_one.NEXT(indexe);
        END LOOP;
    END read_elements;

    FUNCTION count_indexes RETURN NUMBER
    IS
    BEGIN
        RETURN array_one.COUNT;
    END count_indexes;

    FUNCTION read_element(indexe VARCHAR2) RETURN NUMBER
    IS
    elem NUMBER(5);
    BEGIN
        IF(array_one.EXISTS(indexe)) THEN
            elem := array_one(indexe);
            RETURN elem;
        ELSE
            RETURN NULL;
        END IF;
    END;
END feladat_804;


------805 Írjunk blokkot, amely kipróbálja az előző feladat összes eszközét.-----
DECLARE
ertek NUMBER;
BEGIN
        feladat_804.add_element('macska',1);
        feladat_804.add_element('kutya',3);
        feladat_804.modify_element('kutya', 10); --Modosítja az erteket 10-re
        feladat_804.delete_element('macsaka');
        ertek := feladat_804.count_indexes;
        DBMS_OUTPUT.PUT_LINE(ertek);
        feladat_804.read_elements;
        feladat_804.delete_between('kutya','macska');
        EXCEPTION
        WHEN OTHERS THEN
        dopl(SQLERRM);
    END;


--------901-------

CREATE OR REPLACE PACKAGE feladat_901
AS
TYPE v_search IS varray (10) OF HR.employees%ROWTYPE;
employees v_search := v_search();

PROCEDURE open_cursor(wage NUMBER);
END feladat_901;
/
CREATE OR REPLACE PACKAGE BODY feladat_901
AS
CURSOR emp_less (payment NUMBER)
IS
SELECT hre.employee_id, hre.first_name, hre.last_name FROM hr.employees hre WHERE hre.salary < payment ;

PROCEDURE open_cursor(wage NUMBER) IS
emp_id hr.employees.employee_id%TYPE;
firstname hr.employees.first_name%TYPE;
lastname hr.employees.last_name%TYPE;
BEGIN
    employees.DELETE;
    IF(NOT(emp_less%isopen))
        THEN
        OPEN emp_less(wage);
        END IF;
    IF(emp_less%isopen)
    THEN
        FOR n IN 1..10
            LOOP
                employees.extend;
                FETCH emp_less INTO emp_id, firstname, lastname;
                IF(emp_less%notfound)
                    THEN
                        CLOSE emp_less;
                        OPEN emp_less(wage);
                        EXIT;
                END IF;
                employees(n).employee_id :=emp_id;
                employees(n).first_name := firstname;
                employees(n).last_name := lastname;

        END LOOP;
    END IF;
    CLOSE emp_less;
END open_cursor;

END feladat_901;
-------902--------
BEGIN
feladat_901.open_cursor(8000);
FOR i IN 1..feladat_901.employees.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(feladat_901.employees(i).employee_id || ' ' || feladat_901.employees(i).first_name || ' ' || feladat_901.employees(i).last_name );
END LOOP;
END;

-----------------903----------------
DECLARE
TYPE jobTitle IS TABLE OF sajat_hr_jobs.job_title%TYPE;
TYPE minSalary IS TABLE OF sajat_hr_jobs.min_salary%TYPE;
TYPE maxSalary IS TABLE OF sajat_hr_jobs.max_salary%TYPE;
beagyazott_jobTitle jobTitle;
beagyazott_minSalary minSalary;
beagyazott_maxSalary maxSalary;
seged VARCHAR2(100);
BEGIN
    SELECT job_title, min_salary, max_salary
    BULK COLLECT INTO beagyazott_jobTitle, beagyazott_minSalary, beagyazott_maxSalary FROM sajat_hr_jobs;

    FOR i IN beagyazott_jobTitle.FIRST..beagyazott_jobTitle.LAST
    LOOP
        IF beagyazott_minSalary(i) > beagyazott_maxSalary(i)/2 THEN
            beagyazott_jobTitle.DELETE(i);
        END IF;
    END LOOP;

    seged:=beagyazott_jobTitle.FIRST;
    WHILE seged IS NOT NULL
        LOOP
        DBMS_OUTPUT.PUT_LINE(beagyazott_jobTitle(seged));
        seged:=beagyazott_jobtitle.next(seged);
        END LOOP;

    FORALL i IN INDICES OF beagyazott_jobTitle
        UPDATE (SELECT sajat_hr_employees.salary AS hre, sajat_hr_jobs.max_salary AS hrj
        FROM sajat_hr_employees inner join sajat_hr_jobs ON sajat_hr_employees.job_id=sajat_hr_jobs.job_id
        WHERE sajat_hr_jobs.job_title=beagyazott_jobTitle(i)) newsalary
        SET newsalary.hre = newsalary.hre + newsalary.hrj * 0.1;
COMMIT;
END;
-------------------904------------------------
CREATE OR REPLACE TYPE keresztnev IS TABLE OF VARCHAR(30);