PLSQL Lab 6

-- Ex. 1.b
DECLARE
  v_hasEmps BOOLEAN := FALSE;
  v_numarAngajati INTEGER;
  v_venitLunar employees.salary%TYPE;

  v_numarTotalAngajati INTEGER := 0;
  v_venitTotalLunarFaraComision employees.salary%TYPE := 0;

  v_venitTotalLunar employees.salary%TYPE := 0;

  CURSOR c_job IS
    SELECT job_id, job_title FROM jobs;

  CURSOR c_emps (p_job_id jobs.job_id%TYPE) IS
    SELECT (last_name || ' ' || first_name) AS Name, salary, commission_pct, employee_id FROM employees
    WHERE job_id = p_job_id;
BEGIN
  FOR v_job IN c_job LOOP
     FOR v_emp IN c_emps(v_job.job_id) LOOP
      v_venitTotalLunar := v_venitTotalLunar + v_emp.salary + (v_emp.salary * NVL(v_emp.commission_pct, 0));
     END LOOP;
  END LOOP;

  FOR v_job IN c_job LOOP
    IF (c_job%ROWCOUNT > 1) THEN
      DBMS_OUTPUT.PUT_LINE('');
    END IF;
    DBMS_OUTPUT.PUT_LINE (c_job%ROWCOUNT || '. '||v_job.job_title);
    DBMS_OUTPUT.PUT_LINE('-------------------------------------');

    v_hasEmps := FALSE;
    v_numarAngajati := 0;
    v_venitLunar := 0;

    FOR v_emp IN c_emps(v_job.job_id) LOOP
      v_hasEmps := TRUE;
      v_numarAngajati := v_numarAngajati + 1;
      v_venitLunar := v_venitLunar + NVL(v_emp.salary, 0);
      DBMS_OUTPUT.PUT_LINE (c_emps%ROWCOUNT || '. ' || v_emp.Name || ' - ' || (v_emp.salary + (v_emp.salary * NVL(v_emp.commission_pct, 0))) ||
      ' reprezentand ' || ROUND(((v_emp.salary + v_emp.salary * NVL(v_emp.commission_pct, 0)) / v_venitTotalLunar) * 100, 2) ||
      '% din suma total');
    END LOOP;

    IF (v_hasEmps = FALSE) THEN
      DBMS_OUTPUT.PUT_LINE('No employee found!');
    ELSE
      v_numarTotalAngajati := v_numarTotalAngajati + v_numarAngajati;
      v_venitTotalLunarFaraComision := v_venitTotalLunarFaraComision + v_venitLunar;
      DBMS_OUTPUT.PUT_LINE('');
      DBMS_OUTPUT.PUT_LINE('Numarul de angajati: ' || v_numarAngajati);
      DBMS_OUTPUT.PUT_LINE('Valoarea lunara a veniturilor angajatilor: ' || v_venitLunar);
      DBMS_OUTPUT.PUT_LINE('Valoarea medie a veniturilor angajatilor: ' || ROUND(v_venitLunar/v_numarAngajati, 2));
    END IF;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('========================================================');
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('Numarul total de angajati: ' || v_numarTotalAngajati);
  DBMS_OUTPUT.PUT_LINE('Valoarea totala lunara a veniturilor angajatilor: ' || v_venitTotalLunarFaraComision);
  DBMS_OUTPUT.PUT_LINE('Valoarea medie a veniturilor angajatilor: ' || ROUND(v_venitTotalLunarFaraComision/v_numarTotalAngajati, 2));
  DBMS_OUTPUT.PUT_LINE('Suma totala a veniturilor si comisioanelor angajatilor: ' || ROUND(v_venitTotalLunar, 2));
END;