Function that would return the data retrieved from a select query - Oracle
I am trying to write a function that would return the result of a select query. I have worked开发者_如何学Python with very basic functions that would return a number and a varchar2(string). But now I want to return the result of a select, which would be like 10 rows and their corresponding columns.
How would I write the function and what would the return type be?
An example function that I have written is:
create or replace function func1 return varchar2 as begin return('hello from func1'); end func1;
I am still at a basic level, so can anybody help me out with returning the result of a select query? I believe cursors are to be used, as there would be more than one row.
Normally, a function returns a single "thing". Normally, that is a scalar (a number, a varchar2, a record, etc) though you can return a collection. So, for example, you could return a collection (in this case a nested table) with all the EMPNO values from the EMP table
CREATE TYPE empno_tbl
IS TABLE OF NUMBER;
CREATE OR REPLACE FUNCTION get_empnos
RETURN empno_tbl
IS
l_empnos empno_tbl;
BEGIN
SELECT empno
BULK COLLECT INTO l_empnos
FROM emp;
RETURN l_empnos;
END;
But this isn't a particularly common thing to do in a function. It would be a bit more common to have the function return a cursor rather than returning values and to let the caller handle fetching the data, i.e.
CREATE OR REPLACE FUNCTION get_empnos2
RETURN SYS_REFCURSOR
IS
l_rc SYS_REFCURSOR;
BEGIN
OPEN l_rc
FOR SELECT empno
FROM emp;
RETURN l_rc;
END;
But even that isn't particularly common in Oracle. Depending on what you're trying to accomplish, it would generally be more common to simply create a view that selected the data you were interested in and to query that view rather than calling a function or procedure.
Well, if you're just learning, you should know about pipelined functions. A pipelined function lets you return dynamically generated tables within PLSQL.
For example...
create function
gen_numbers(n in number default null)
return array
PIPELINED
as
begin
for i in 1 .. nvl(n,999999999)
loop
pipe row(i);
end loop;
return;
end;
Which I borrowed from http://www.akadia.com/services/ora_pipe_functions.html :-)
Without context of how you would be calling this function, I'm a little lost on exactly how to help you.
Are you sure you wouldn't be better off with a subselect, join, or view instead?
精彩评论