title: Writing a function to display employee information based on a particular department in Oracle?
body: <p>These are the list of tables I have created and inserted values for the created tables:</p>
  
  <pre><code>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','starc@gmail.com','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','bruce@gmail.com','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','larry@gmail.com','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','steve@gmail.com','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','remy@gmail.com','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','clark@gmail.com','0123456784',TO_DATE('15/1/2013','DD/MM/YYYY'),2,20000.00,2);
  </code></pre>
  
  <p>Now in my assignment, I have been asked to solve the following question:</p>
  
  <p><strong>Write a function called fn_emps_per_dept_jc450912 to retrieve EMPLOYEE_ID,FIRST_NAME,LAST_NAME AND JOB_TITLE with a given DEPARTMENT_ID. This function should have DEPARTMENT_ID as input parameter and this function should have EMPLOYEE_ID,FIRST_NAME,LAST_NAME AND JOB_TITLE as output parameters. It should return TRUE if found and FALSE if not found.</strong></p>
  
  <p>In order to display the information of employees from a particular department, I have written the following function:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION fn_emps_per_dept_jc450912 (f_dept_id NUMBER)
  RETURN SYS_REFCURSOR 
  AS
  emp_details SYS_REFCURSOR;
  BEGIN
  OPEN emp_details 
  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 = f_dept_id;
  RETURN emp_details;
  EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
  DBMS_OUTPUT.PUT_LINE('Department not available');
  END fn_emps_per_dept_jc450912;
  
  VARIABLE e REFCURSOR
  EXECUTE :e := fn_emps_per_dept_jc450912(1); 
  
  PRINT e;
  
  EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_TITLE
  ----------- -------------------- ------------------------- ----------------------------
            1 Tony                 Starc                     IT Administrator
            2 Bruce                Wayne                     IT Administrator
            3 Larry                Ellison                   IT Administrator
  </code></pre>
  
  <p>Then I tried inserting an invalid department number:</p>
  
  <pre><code>VARIABLE e REFCURSOR
  EXECUTE :e := fn_emps_per_dept_jc450912(5); 
  
  PRINT e;
  </code></pre>
  
  <p>I have the following 3 questions:</p>
  
  <ol>
  <li>Why is it blank ? Where is the possible mistake ?</li>
  <li>The question demanded that <strong>EMPLOYEE_ID,FIRST_NAME,LAST_NAME AND JOB_TITLE should be the output parameters</strong>. But I'm returning <strong>EMPLOYEE_ID,FIRST_NAME,LAST_NAME AND JOB_TITLE</strong> as <strong>SYS_REFCURSOR</strong>. So is my approach correct based on what the question wanted regarding the output parameters ?</li>
  <li>Which part of the code do I need to fix ? How can this function be re-written ?</li>
  </ol>