returning two varchar in a function pl sql oracle

Question

So i want to create a function in oracle. The user will input the department name and the year of hire date. Then i want to return the list of employees' name. But how can i return both first and last name of the employees in function? Here's what I've got so far. I got an error "Query must begin with SELECT or WITH". Any idea? Thanks!

type emp_type is record
(v_name1 employees.first_name%type, v_name2 employees.last_name%type);
create or replace function get_employee
 (P_IN_DEPT_NAME IN departments.department_name%type, P_IN_YEAR IN NUMBER)
return emp_type as emp_record emp_type;
BEGIN
   For D in (SELECT e.first_name, e.last_name into emp_record.v_name1,emp_record.v_name2
               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
        return emp_record;
        end loop;
END;
end;
/  

Answer 1

Do not know where this function will be used but here is another answer of your question:

-- First creating the function. It will return SYS_REFCURSOR.
create or replace function get_employee
 (P_IN_DEPT_NAME IN departments.department_name%type, P_IN_YEAR IN NUMBER)
return sys_refcursor
is
    v_cursor sys_refcursor;
BEGIN
open v_cursor for 
   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;
        return v_cursor;
END;


-- Then main code that will call function and outpur result:

declare
    v_first_name employees.firsT_name%type;
    v_last_name employees.last_name%type;
    v_res sys_refcursor;
begin
    v_res := get_employee('Purchasing', 2005); -- 'Purchasing' and 2005 are just examples
    loop
        fetch v_res into v_first_name, v_last_name;
        exit when v_res%Notfound;
        dbms_output.put_line(v_first_name || ' ' ||v_last_name);
    end loop;
end;

Thanks.

Answer 2

If you have the tables:

CREATE TABLE departments (
  department_id   INT PRIMARY KEY,
  department_name VARCHAR2(20)
);

CREATE TABLE employees (
  first_name    VARCHAR2(20),
  last_name     VARCHAR2(20),
  hire_date     DATE,
  department_id INT REFERENCES departments ( department_id )
);

INSERT ALL
  INTO departments VALUES ( 1, 'Dept. A' )
  INTO employees   VALUES ( 'Alice', 'Adams', DATE '2020-01-01', 1 )
  INTO employees   VALUES ( 'Betty', 'Baron', DATE '2020-02-01', 1 )
  INTO employees   VALUES ( 'Cerys', 'Carol', DATE '2020-03-01', 1 )
SELECT * FROM DUAL;

Then you can create the package:

CREATE PACKAGE employees_pkg
IS
  TYPE emp_type IS RECORD(
    first_name EMPLOYEES.FIRST_NAME%TYPE,
    last_name  EMPLOYEES.LAST_NAME%TYPE
  );
  
  FUNCTION get_employee(
    P_DEPT_NAME IN DEPARTMENTS.DEPARTMENT_NAME%type,
    P_YEAR      IN NUMBER
  ) RETURN emp_type;
END;
/

(Note: the package specification only contains the signatures for the function.)

And its body:

CREATE PACKAGE BODY employees_pkg
IS
  FUNCTION get_employee(
    P_DEPT_NAME IN DEPARTMENTS.DEPARTMENT_NAME%type,
    P_YEAR      IN NUMBER
  ) RETURN emp_type
  IS
    v_year DATE := TRUNC( TO_DATE( p_year, 'YYYY' ), 'YYYY' );
    v_emp  emp_type;
  BEGIN
    SELECT e.first_name, e.last_name
    INTO   v_emp.first_name, v_emp.last_name
    FROM   employees e
           INNER JOIN departments d
           ON e.department_id = d.department_id 
    WHERE  d.department_name = p_dept_name 
    AND    e.hire_date >= v_year
    AND    e.hire_date <  ADD_MONTHS( v_year, 12 )
    ORDER BY e.hire_date
    FETCH FIRST ROW ONLY;
    
    RETURN v_emp;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- your code to handle the exception
      NULL;
  END;
END;
/

Note:

• SELECT ... INTO ... only accepts a single row so you can limit the query to return at most one row using FETCH FIRST ROW ONLY and also handle the NO_DATA_FOUND exception in case there are no employees.
• You also don't want to mix cursors and SELECT ... INTO ..., just use the latter. Then you can call it:

DECLARE
  v_emp employees_pkg.emp_type;
BEGIN
  v_emp := employees_pkg.get_employee(p_dept_name => 'Dept. A', p_year => 2020 );
  DBMS_OUTPUT.PUT_LINE( v_emp.first_name || ' ' || v_emp.last_name );
END;
/

Which outputs:

Alice Adams

db<>fiddle here

Answer 3

You are mixing for loop with select into.

• either remove for loop and keep only select with into
• or keep for loop and remove into clause from select. In loop body type emp_record.v_name1 := D.first_name etc.

Ensure select returns only one row in both cases. Otherwise query fails or rewrites values. Or return collection of emp_type, which is another thing.