Exercise 4
Create a PL/SQL block that will prompt the user to input a particular job
description and then displays the information below. Make use of UPPER function
to avoid data mismatches due to case sensitivity
example output:
The job code CLERK has 3 employees with total salary of R4150.00
DECLARE
-- Déclaration des variables
v_job emp.job%TYPE;
v_employee_count NUMBER;
v_total_salary NUMBER;
-- Curseur dynamique pour récupérer les infos selon le job entré
CURSOR c_employees IS
SELECT job, COUNT(empno) AS employee_count, SUM(sal) AS total_salary
FROM emp
WHERE UPPER(job) = v_job
GROUP BY job;
BEGIN
-- Activer l'affichage des résultats dans la console
DBMS_OUTPUT.ENABLE;
-- Demander à l'utilisateur d'entrer la description du métier
v_job := UPPER('&job_description');
-- Parcourir les résultats du curseur
OPEN c_employees;
FETCH c_employees INTO v_job, v_employee_count, v_total_salary;
CLOSE c_employees;
-- Vérification et affichage du résultat
IF v_employee_count IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('The job ' || v_job || ' has ' || v_employee_count ||
' employees with total salary of R' || TO_CHAR(v_total_salary, '9999.99'));
ELSE
DBMS_OUTPUT.PUT_LINE('No employees found for the job: ' || v_job);
END IF;
END;
/
After running this code this is the iutput, need some help.
