Oracle-通过SELECTable函数返回一个结果集，而不使用显式对象/表类型

Question

我正试图找到一种简单易维护的方法，通过一个可以在SELECT语句中引用的函数返回结果集，如下所示：

SELECT u.UserId, u.UserName, up.ProfileName
FROM GetUser(1) u
INNER JOIN user_profile up ON u.user_id = up.user_id; 

以下是我在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);

以下是我在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));

它是可行的，但它很麻烦，需要多个DDL。在Postgres中，我可以在函数中轻松地完成所有这些：

RETURNS TABLE (ObjectId INTEGER, ObjectClass CHAR(2), ObjectName VARCHAR(100))

在Oracle中有更干净的方法吗？

相关帖子
SELECT in Functions/SPs
DBMS_SQL.return_sult
Pipeline Functions

Answer 1

基本上，按照你的说法，你只需要创建一个函数——使用Oracle的内置类型。这里有一个基于Scott的EMP表的例子(因为我没有你的(：

SQL> select deptno, ename from emp where deptno = 10;
DEPTNO ENAME
---------- ----------
10 CLARK
10 KING
10 MILLER

功能：

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.

测试：

SQL> select * From table(getuser(10));
COLUMN_VALUE
----------------------------------------------------------------------------
CLARK
KING
MILLER
SQL>

---

如果你想得到什么"；更聪明"；作为列名而不是COLUMN_VALUE，则必须创建自己的类型。类似这样的东西：

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>

Answer 2

在19.6之前，具有定义的表类型(作为SQL类型或在PL/SQL包中(的流水线函数是实现这一目标的唯一方法。一旦你使用了这个版本，你就可以使用SQL宏了。对于您的特定要求，您可以直接进行替换，如：

create or replace function getUser (pUserId  integer) 
return varchar2 sql_macro 
is
begin
return q'{SELECT UserId, UserClass, UserName
FROM Users 
WHERE (UserId = pUserId OR pUserId IS NULL)}';
end getUser;
/

或者，您可以让优化器更容易地将查询分为两种可能的形式——返回所有内容或只返回与pUserId值匹配的行：

create or replace function getUser (pUserId  integer) 
return varchar2 sql_macro 
is
begin
if pUserId is null then 
return q'{SELECT UserId, UserClass, UserName FROM Users}';
else 
return q'{SELECT UserId, UserClass, UserName FROM  Users WHERE UserId = pUserId}';
end if;
end getUser;
/

我在LiveSQL上做了一个演示。