IN 子句的函数或过程

Question

我想编写一个可以在另一个过程的 IN 子句中使用的函数或过程。 该函数或过程将返回 ID 号。

主过程会说类似

SELECT *
FROM EMPLOYEES
WHERE OFFICE_ID IN (GET_OFFICE_IDS);  -- GET_OFFICE_IDS requires no parameters

GET_OFFICE_IDS 返回一个 VARCHAR2，其中 ID 用逗号分隔。 当我运行主程序时，我收到“ORA-01722：无效数字”错误，这是有道理的，但我不知道我需要从这里去哪里。

我是否需要 GET_OFFICE_IDS 来创建主过程使用的临时表？ 如果是这样，是否会有性能损失？

Answer 1

以下是使用 EMP 表的嵌套表解决方案的工作示例：

create type t_ids is table of integer
/

create or replace function get_office_ids return t_ids
is
   l_ids t_ids := t_ids();
   l_idx integer := 0;
begin
   for r in (select empno from emp where deptno=10)
   loop
      l_ids.extend;
      l_idx := l_idx+1;
      l_ids(l_idx) := r.empno;
   end loop;
   return l_ids;
end;
/

select ename from emp where empno in (select * from table(get_office_ids));


ENAME
----------
CLARK
KING
TEST
MILLER
BINNSY
FARMER

Answer 2

简单的强力方法：

WHERE ','||GET_OFFICE_IDS||',' LIKE '%,'||OFFICE_ID||',%'

最好更改 GET_OFFICE_IDS 以返回嵌套表并使用类似以下内容的内容：

OFFICE_ID IN (SELECT * FROM TABLE(GET_OFFICE_IDS))

Answer 3

我不熟悉 oracle SQL，但是您不能简单地在 IN 子句中放置另一个 select 语句来返回 ID 吗？

SELECT * FROM EMPLOYEES WHERE OFFICE_ID IN (SELECT ID FROM tbl_X WHERE x=y);

...或者您希望做一些更复杂的事情？

Answer 4

您可能可以使用 ref_cursor 来完成此操作（refcursor c := 'select '||.... ），

但是管道函数效果很好。
像这样使用它：

create or replace type type_varchar2 as table of varchar2(100);

create or replace function GET_OFFICE_IDS return TYPE_varchar2 PIPELINED
is
  retval VARCHAR2(100);
begin
  -- put some sql here which results in statements as below
 retval := '135';
 PIPE ROW (retval);
 retval := '110';
 PIPE ROW (retval);
end GET_OFFICE_IDS;


select *
from entries
where id in (SELECT COLUMN_VALUE FROM TABLE(GET_OFFICE_IDS));

通常管道函数执行得很好。
然而，具有大量条目的子查询的性能并不总是很好。

Answer 5

编辑：我违反了SO的基本规则，我没有回答OP。 由于已经有一个可接受的答案，因此我认为警告是明智的。

通常，混合 SQL 和 PL/SQL 是一个非常糟糕的主意。 有 2 个独立的代码引擎。 有一个 SQL 引擎和一个 PL/SQL 引擎。 强制来回进行数千次切换绝对会降低性能。

我明白为什么程序员要这样做。 我得到它。 这一切都是封装的、温暖的、模糊的，但它会让你精疲力竭。 就像大自然一样，它会用它的景象和声音引诱你，然后它会折断你的脚踝。

即使是这样愚蠢的事情。

create or replace function my_Date (p_Date in date)
return varchar
as
begin

    return to_char(p_Date, 'yyyy/mm/dd');

end;

将杀死你的执行时间。

打开打开自动跟踪，

然后运行它们。

select to_char(created, 'yyyy/mm/dd'), to_char(last_ddl_time, 'yyyy/mm/dd')  from all_objects


select my_date(created), my_Date(last_DDL_TIME) From all_objects

第二个需要两倍的时间来运行。 我在 1 秒内得到查询 1 的答案，在 2 秒内得到查询 2 的答案。

这是一个极其简单的案例...我所做的只是转换值。 想象一下，如果您必须按照自己的意愿加入其中。 这确实是最坏的情况。

现在想想当您将某些内容隐藏在函数中时优化器完全无法执行哪些操作。

当您执行 IN 时，有时执行 join 的速度要快得多。 如果某些条件成立，优化器将为您执行此操作。 它将把 IN 转换为 JOIN。 但由于您在函数内隐藏了选择，因此它无法再确定条件是否成熟。 您强制优化器做了一些次优的事情。

优化器依赖的一项关键统计数据是行计数。 是一行还是10亿。 它从表和索引的统计数据中得知。 您的函数没有统计数据。

你可以把它们放在那里，可以暗示基数，我并不是说你不能，但为什么呢？ 你为什么想要这么做？ 看来您正在使用该函数，因为您是一位勤奋的程序员，一生都被告知要将冗余代码分解到函数中。

你脑子里的那些规则几乎没有一个适用于 SQL。 优化器不是编译器。 它不能内联你的函数。 只有您才能帮助您的优化器获得最佳计划。