I'm trying to find a simple and easy-to-maintain way to return a result set via a function that can be referenced in a SELECT statement like this:
SELECT u.UserId, u.UserName, up.ProfileName
FROM GetUser(1) u
INNER JOIN user_profile up ON u.user_id = up.user_id;
Here's what I have in Postgres:
CREATE OR REPLACE FUNCTION GetUser(
pUserId INTEGER
)
RETURNS TABLE (UserId INTEGER, UserClass CHAR(2), UserName VARCHAR(100)) AS $$
BEGIN
RETURN QUERY
SELECT UserId, UserClass, UserName
FROM Users
WHERE (UserId = pUserId OR pUserId IS NULL)
;
END;
$$ LANGUAGE 'plpgsql';
SELECT * FROM GetUser(1);
Here's an example of where I've gotten to in Oracle:
CREATE OR REPLACE TYPE appuser AS OBJECT (UserName VARCHAR(255)); -- user type
CREATE OR REPLACE TYPE appuser_table AS TABLE OF appuser; -- user table type
CREATE OR REPLACE FUNCTION GetUser (
pUserId IN VARCHAR2 DEFAULT NULL
) RETURN appuser_table PIPELINED AS
BEGIN
FOR v_Rec IN (
SELECT UserName
FROM Users
WHERE (UserId = pUserId OR pUserId IS NULL)
)
LOOP
PIPE ROW (appuser(v_Rec.UserName));
END LOOP;
RETURN;
END;
SELECT * FROM TABLE(GetUser(NULL));
It works, but it's cumbersome and requires multiple DDLs. In Postgres, I can do all this easily within the function:
RETURNS TABLE (ObjectId INTEGER, ObjectClass CHAR(2), ObjectName VARCHAR(100))
Is there a cleaner way to do this in Oracle?
Related posts
SELECT in Functions / SPs
DBMS_SQL.return_result
Pipelined Functions
Basically, the way you put it, all you need to create is a function - use Oracle's built-in type. Here's an example based on Scott's EMP table (as I don't have yours):
SQL> select deptno, ename from emp where deptno = 10;
DEPTNO ENAME
---------- ----------
10 CLARK
10 KING
10 MILLER
Function:
SQL> create or replace function getuser (puserid in number default null)
2 return sys.odcivarchar2list
3 is
4 retval sys.odcivarchar2list;
5 begin
6 select ename
7 bulk collect into retval
8 from emp
9 where deptno = puserid or puserid is null;
10
11 return retval;
12 end;
13 /
Function created.
Testing:
SQL> select * From table(getuser(10));
COLUMN_VALUE
----------------------------------------------------------------------------
CLARK
KING
MILLER
SQL>
If you'd want to get something "smarter" as a column name instead of COLUMN_VALUE, then you'd have to create your own type. Something like this:
SQL> create or replace type t_tf_row as object (ename varchar2(20));
2 /
Type created.
SQL> create or replace type t_tf_tab is table of t_tf_row;
2 /
Type created.
SQL> create or replace function getuser (puserid in number default null)
2 return t_tf_tab
3 is
4 retval t_tf_tab;
5 begin
6 select t_tf_row(ename)
7 bulk collect into retval
8 from emp
9 where deptno = puserid or puserid is null;
10 return retval;
11 end;
12 /
Function created.
SQL> select * from table(getuser(10));
ENAME
--------------------
CLARK
KING
MILLER
SQL>