CODEWITHSUNDEEP
sqloracle-databaseplsql
Bek Zhak
Bek Zhak

Reputation: 27

PL/SQL - The function deosn't return multiple rows

I wanted to select 10 highly paid employees from the "employees" table, but the function returnes only 1 row. How to get multiple rows in this case? My subquery for selecting employees works well, but when I call function it returns 1 row.

This is my code:

CREATE OR REPLACE FUNCTION f_sal
RETURN Varchar2

IS cursor c_emp is (select first_name, last_name from (select first_name, last_name, row_number() 
over(order by salary desc) as ranking from employees) where ranking <= 10);

v_first employees.first_name%type;
v_last employees.last_name%type;

begin
open c_emp;
fetch c_emp into v_first, v_last;
close c_emp;
return v_first || ' ' || v_last;

EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
     dbms_output.put_line('Error');
     when others then dbms_output.put_line('Other Error');
END;
  
select f_sal from dual;

Upvotes: 1

Views: 224

Answers (1)

MT0
MT0

Reputation: 167867

Option 1: Use a collection

CREATE FUNCTION f_sal
RETURN SYS.ODCIVARCHAR2LIST
IS 
  v_names SYS.ODCIVARCHAR2LIST;
BEGIN
  SELECT first_name || ' ' || last_name
  BULK COLLECT INTO v_names
  FROM   employees
  ORDER BY salary DESC
  FETCH FIRST 10 ROWS ONLY;

  return v_names;
END;
/

Then:

SELECT * FROM TABLE(f_sal);

Option 2: Use a pipelined function and iterate over a cursor into a collection

CREATE OR REPLACE FUNCTION f_sal
RETURN SYS.ODCIVARCHAR2LIST PIPELINED
IS
BEGIN
  FOR n IN (
    SELECT first_name || ' ' || last_name AS name
    FROM   employees
    ORDER BY salary DESC
    FETCH FIRST 10 ROWS ONLY
  )
  LOOP
    PIPE ROW (n.name);
  END LOOP;
END;
/

Then:

SELECT * FROM TABLE(f_sal);

Option 3: Return a cursor

CREATE FUNCTION f_sal
RETURN SYS_REFCURSOR
IS 
  v_names SYS_REFCURSOR;
BEGIN
  OPEN v_names FOR
    SELECT first_name || ' ' || last_name AS name
    FROM   employees
    ORDER BY salary DESC
    FETCH FIRST 10 ROWS ONLY;

  return v_names;
END;
/

Then:

DECLARE
  v_names SYS_REFCURSOR := f_sal();
  v_name VARCHAR2(100);
BEGIN
  LOOP
    FETCH v_names INTO v_name;
    EXIT WHEN v_names%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( v_name );
  END LOOP;
END;
/

db<>fiddle here

Upvotes: 2

Related Questions