Home » SQL & PL/SQL » SQL & PL/SQL » Using %ROWTYPE in a Function with a cursor
Using %ROWTYPE in a Function with a cursor [message #186098] Sat, 05 August 2006 16:21 Go to next message
bstockto
Messages: 1
Registered: August 2006
Junior Member
I am trying to create a function that will return all of the rows from a table that meet a user inputted value (department id).

How can I use the %ROWTYPE attribute to return all of the records that meet the criteria.

create or replace function department_by_number
(p_deptid in departments.department_id%TYPE) return varchar2
is l_value varchar2(100);
CURSOR P1
RETURN DEPARTMENTS%ROWTYPE
IS
SELECT *
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = P_deptid;
BEGIN
OPEN P1;
FETCH P1 INTO L_VALUE;
IF P1%NOTFOUND THEN
L_VALUE := 'NO DEPARTMENT NUMBER FOUND';
END IF;
CLOSE P1;
RETURN L_VALUE;
END;

Thanks for your help. I'm a newbie when it comes to Oracle programming.
Re: Using %ROWTYPE in a Function with a cursor [message #186099 is a reply to message #186098] Sat, 05 August 2006 16:25 Go to previous message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
If you need to return multiple rows from a single call to the function, you could return a ref cursor or use a pipelined function. Information on both is available in the online documentation.
Previous Topic: Queries and tables
Next Topic: Identifying Trends via a SELECT query
Goto Forum:
  


Current Time: Tue Dec 06 12:01:23 CST 2016

Total time taken to generate the page: 0.15031 seconds