CODEWITHSUNDEEP
sqloracle-databaseplsql
sqlovers
sqlovers

Reputation: 67

Cursor and procedure/function pl sql to display employees name

I want to create a procedure/function, when user input a year and a department name, i need to display the employees' name (first and last name). This is my code so far. Any idea? Thanks!

DECLARE
      CURSOR emp_cur (my_date int,my_dept departments.department_name%type) IS
        SELECT e.first_name,e.last_name
        FROM employees e
        join departments d
        on e.department_id=d.department_id 
        where d.department_name=my_dept and my_date=extract(year from e.hire_date);
    
    PROCEDURE emp_excesspaid  IS
        v_first  employees.first_name%TYPE;
        v_last  employees.last_name%TYPE;
        
      BEGIN
        LOOP
          FETCH emp_cur INTO v_first, v_last;
          EXIT WHEN emp_cur%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE(v_first||' '||v_last);
        END LOOP;
      END;
      end;

Upvotes: 0

Views: 968

Answers (1)

Popeye
Popeye

Reputation: 35930

You can make it simple as follows:

CREATE OR REPLACE PROCEDURE PROC_NAME(
      P_IN_DEPT_NAME IN VARCHAR2,
      P_IN_YEAR IN NUMBER
) AS
BEGIN
   For D in (SELECT e.first_name, e.last_name
               FROM employees e
               join departments d
                 on e.department_id = d.department_id 
              where d.department_name = P_IN_DEPT_NAME 
                and extract(year from e.hire_date) = P_IN_YEAR) 
   LOOP     
        DBMS_OUTPUT.PUT_LINE(D.FIRST_NAME || ' ' || D.LAST_NAME);
        END LOOP;
END;
/

Upvotes: 1

Related Questions