How to write a stored procedure to display the information of employees from a particular department in PL/SQL?

All we need is an easy explanation of the problem, so here it is.

These are the list of tables I have created and inserted values for the created tables:

CREATE TABLE DEPARTMENT
(DEPARTMENT_ID NUMBER PRIMARY KEY,
 DEPARTMENT_NAME VARCHAR(30) NOT NULL
 );

 CREATE TABLE JOBS
 (JOB_ID NUMBER PRIMARY KEY,
  JOB_TITLE VARCHAR(35) NOT NULL,
  MIN_SALARY DECIMAL NOT NULL,
  MAX_SALARY DECIMAL NOT NULL
  );

 CREATE TABLE EMPLOYEES
(EMPLOYEE_ID NUMBER PRIMARY KEY,
 FIRST_NAME VARCHAR(20) NOT NULL,
 LAST_NAME VARCHAR(25) NOT NULL,
 EMAIL VARCHAR(25) NOT NULL,
 PHONE_NUMBER VARCHAR(20) NOT NULL,
 HIRE_DATE DATE NOT NULL,
 JOB_ID NUMBER NOT NULL,
 SALARY DECIMAL NOT NULL,
 DEPARTMENT_ID NUMBER NOT NULL,
 CONSTRAINT emp_job_fk FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
 CONSTRAINT emp_department_fk FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID)
 );

INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(1,'IT');
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(2,'Sales');

INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES (1,'IT Administrator',250000.00,50000.00);
INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES (2,'Salesman',200000.00,40000.00);

INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (1,'Tony','Starc','[email protected]','0123456789',TO_DATE('15/1/2008','DD/MM/YYYY'),1,45000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (2,'Bruce','Wayne','[email protected]','0123456788',TO_DATE('15/1/2009','DD/MM/YYYY'),1,40000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (3,'Larry','Ellison','[email protected]','0123456787',TO_DATE('15/1/2010','DD/MM/YYYY'),1,30000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (4,'Steve','Jobs','[email protected]','0123456786',TO_DATE('15/1/2011','DD/MM/YYYY'),2,35000.00,2);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (5,'Remy','Lebeau','[email protected]','0123456785',TO_DATE('15/1/2012','DD/MM/YYYY'),2,30000.00,2);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (6,'Clark','Kent','[email protected]','0123456784',TO_DATE('15/1/2013','DD/MM/YYYY'),2,20000.00,2);

In order to display the information of employees from a particular department, I have written the following procedure:

CREATE OR REPLACE PROCEDURE pr_emps_per_dept_jc450912(p_dept_id NUMBER)
AS
BEGIN
DECLARE
emp_id EMPLOYEES.EMPLOYEE_ID%TYPE;
f_name EMPLOYEES.FIRST_NAME%TYPE;
l_name EMPLOYEES.LAST_NAME%TYPE;
job_name JOBS.JOB_TITLE%TYPE;
BEGIN
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_TITLE
INTO emp_id,f_name,l_name,job_name
FROM EMPLOYEES,JOBS,DEPARTMENT
WHERE DEPARTMENT.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
AND JOBS.JOB_ID = EMPLOYEES.JOB_ID
AND EMPLOYEES.DEPARTMENT_ID  = p_dept_id;
END;
END;

The above stored procedure has been compiled successfully.
However, while I’m trying to execute this procedure [by using EXECUTE statement], I’m getting the following error message:

EXECUTE  pr_emps_per_dept_jc450912(1);

Error report -
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.PR_EMPS_PER_DEPT_JC450912", line 10
ORA-06512: at line 1
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The error message is self-explanatory. You can not store multiple values in a single primitive variable.

Also this is not MS SQL where you can just simply put a SELECT in a procedure then call it to display the result. PL/SQL code runs on the server side. It does not display results on the client.
You can use PL/SQL to collect and return the results to the client. Then you can somehow display that on the client.

Below is an example:

create or replace function pr_emps_per_dept_jc450912 ( p_dept_id number )
  return sys_refcursor as
  l_cursor sys_refcursor;
begin
  open l_cursor for 
    select employee_id, first_name, last_name, job_title
    from employees, jobs, department
    where department.department_id = employees.department_id
    and jobs.job_id = employees.job_id
    and employees.department_id = p_dept_id;
  return l_cursor;
end;
/

Then use it:

SQL> variable c refcursor
SQL> exec :c := pr_emps_per_dept_jc450912(1);

PL/SQL procedure successfully completed.

SQL> print :c

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_TITLE
----------- -------------------- ------------------------- ----------------------------
          1 Tony                 Starc                     IT Administrator
          2 Bruce                Wayne                     IT Administrator
          3 Larry                Ellison                   IT Administrator

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply