create or replace package TestBD AS
    procedure displayAuthor (id_of_author AUTHOR.AUTHORID%TYPE);
end TestBD;
/

create or replace package body TestBD AS
    cursor c_books (id_of_author AUTHOR.AUTHORID%TYPE) IS
    (
        SELECT TITLE FROM BOOKAUTHOR
        INNER JOIN BOOKS
        ON BOOKAUTHOR.ISBN = BOOKS.ISBN
        WHERE AUTHORID = id_of_author
    );

    book_title varchar2(50);

    function countOrders (title_of_book varchar2) return number is total_orders number;
    begin
        SELECT COUNT(*) INTO total_orders
        FROM BOOKORDERS
        INNER JOIN BOOKORDERITEMS
        ON BOOKORDERS.ORDER# = BOOKORDERITEMS.ORDER#
        INNER JOIN BOOKS
        ON BOOKORDERITEMS.ISBN = BOOKS.ISBN
        WHERE TITLE = title_of_book;

        return title_of_book;
    end;

    procedure displayAuthor (id_of_author AUTHOR.AUTHORID%TYPE) IS
    begin
        OPEN c_books(id_of_author);
        LOOP
            exit when c_books%notfound;
            FETCH c_books into book_title;
            dbms_output.put_line(book_title);
        END LOOP;
    end;
END TestBD;
/

declare

begin
    TestBD.displayAuthor('autor');
    dbms_output.put_line('boss');
end;


------------_--

--------------------------------- cursor
--cursor clasic

declare
  cursor C is (select * from employees);
  angajati employees%rowtype;

begin
  open C;
    loop
    fetch C into angajati;
    exit when C%notfound;
    dbms_output.put_line(angajati.employee_id||' '||angajati.first_name);

    end loop;
  close C;
end;
/




--cursor clasic cu parametru
declare
  cursor C(eid employees.employee_id%type) is
          (select * from employees
           where employee_id = eid);
  angajati employees%rowtype;

begin
  open C(100);
    loop
    fetch C into angajati;
    exit when C%notfound;
    dbms_output.put_line(angajati.employee_id||' '||angajati.first_name);

    end loop;
  close C;
end;
/





--cursor ciclu
declare
  cursor C is (select * from employees );
  angajati employees%rowtype;

begin
  for i in C loop
    --if C%isopen then
    -- dbms_output.put_line(i.employee_id||' este deschis');
    dbms_output.put_line(i.employee_id||' '||i.salary);
   -- end if;
  end loop;

end;
/



--cursor ciclu cu subcereri

begin
  for i in (select * from employees) loop
    dbms_output.put_line(i.employee_id||' '||i.salary);
  end loop;

end;
/

---------------------------------------- functii

DECLARE
   num number;
   factorial number;

FUNCTION fact(x number)
RETURN number
IS
   f number;
BEGIN
   IF x=0 THEN
      f := 1;
   ELSE
      f := x * fact(x-1);
   END IF;
RETURN f;
END;

BEGIN
   num:= 6;
   factorial := fact(num);
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;


 -----------------------------------

l_first  VARCHAR2 (10) := 'Steven';


type grades IS VARRAY(5) OF INTEGER;
marks grades;




__________________________________

DECLARE
cont NUMBER := 1;
val NUMBER := 0;
numarAng NUMBER := 0;
ValoareTol NUMBER := 0;

BEGIN
  FOR jb IN (SELECT job_id,job_title FROM jobs ) LOOP
    DBMS_OUTPUT.new_line;
    DBMS_OUTPUT.put_line(jb.job_title);
    FOR anga IN (SELECT first_name,last_name,salary,commission_pct FROM employees WHERE job_id = jb.job_id) LOOP
      DBMS_OUTPUT.put_line(cont ||' '|| anga.first_name || ' ' || anga.last_name);
      cont := cont + 1;
      val := val + anga.salary;
    END LOOP;
    IF cont > 1 THEN
      cont := cont - 1;
    END IF;
    numarAng := numarAng + cont;
    ValoareTol := ValoareTol + val;
    DBMS_OUTPUT.put_line('Numar de angajati = ' || cont );
    DBMS_OUTPUT.put_line('Valoare Lunara = ' || val);
    DBMS_OUTPUT.put_line('Valoare medie = ' || val/cont);
    DBMS_OUTPUT.new_line;
    cont:=1;
    val := 0;
  END LOOP;
  DBMS_OUTPUT.put_line('Numar total de angajati = ' || numarAng );
  DBMS_OUTPUT.put_line('Valoare Lunara toti angajati = ' || ValoareTol );
  DBMS_OUTPUT.put_line('Valoare Lunara medie = ' || ValoareTol/numarAng );
END;