returning two varchar in a function pl sql oracle

0
votes

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;
/
3
sqloracleplsql
Returning first and last names separately is not too complicated (for example you can create your own "record type" to hold two strings, first_name and last_name, and write the function to return this data type). More interesting question - what if there are multiple employees hired in the same department in the same year? What should the function return then? One might argue, "a table of such records". That can be done too. Is that what you need though? Only you know how you plan to use this function. - mathguy

3 Answers

3
votes

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.

1
votes

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

0
votes

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.