oracle 函数体中的逗号分隔值

Question

我有以下 oracle 函数，但它不起作用并且出错。我用了询问汤姆 转换逗号分隔值以在

包头中声明的 select * from table1 where col1 in <> 中使用的方法：

TYPE myTableType IS table of varchar2 (255);

包体的一部分：

l_string        long default iv_value_with_comma_separated|| ',';
l_data          myTableType := myTableType();
n               NUMBER;

begin
  begin
LOOP
    EXIT when l_string is null;
    n := instr( l_string, ',' );
     l_data.extend;
     l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
     l_string := substr( l_string, n+1 );
END LOOP;
end;

OPEN my_cursor FOR
  select * from table_a where column_a in (select * from table (l_data));
CLOSE my_cursor
END;

上面失败，但当我时它工作正常删除

select * from table (l_data)

有人可以告诉我我在这里可能做错了什么吗？

Answer 1

您没有向我们提供实际的错误，这使得我们更难诊断您的问题。然而，值得一试：ORA-00902: invalid datatype

您还没有完全按照 Tom 给出的方式实现他的解决方案。具体来说，他将 myTableType 创建为 SQL 类型，而您已在包规范中声明了它。这不是一个微不足道的细节：我们不能在 SQL 语句中使用 PL/SQL 类型。因此例外。

因此，从包中删除 MyTableType 的声明并在 SQL 中创建它......

create or replace type mytabletype as table of varchar2(255);
/

您的 SELECT 语句现在应该可以工作。如果没有，请编辑您的问题以向我们提供确切的错误消息。

编辑

“我希望一切都在里面
包裹。我必须改变什么
实现这个目标吗？”

正如您所看到的，PKG1 在规范中声明了 PL/SQL 类型：

SQL> create or replace package pkg1 as
  2      TYPE myTableType IS table of varchar2 (255);
  3      function split (p_string  in    long )
  4          return          myTableType ;
  5      function get_resultset (p_tab in myTableType)
  6          return sys_refcursor;
  7      function get_resultset_for_str (p_string  in    long)
  8          return sys_refcursor;
  9  end pkg1;
 10  /

Package created.

SQL>

在包主体中，您将把 SPLIT() 识别为 Tom Kyte 的解决方案。 GET_RESULTSET() 循环遍历传递的集合并进行 动态 SQL 语句。 GET_RESULTSET_FOR_STR() 是一个调用其他函数的辅助函数，

SQL> create or replace package body pkg1 as
  2      function split (p_string   in     long )
  3      return          myTableType
  4      is
  5              l_string        long default p_string || ',';
  6              l_data          myTableType := myTableType();
  7              n               number;
  8          begin
  9            loop
 10                exit when l_string is null;
 11                n := instr( l_string, ',' );
 12               l_data.extend;
 13               l_data(l_data.count) :=
 14                       ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
 15               l_string := substr( l_string, n+1 );
 16          end loop;
 17          return l_data;
 18      end split;
 19
 20      function get_resultset (p_tab in myTableType)
 21          return sys_refcursor
 22      is
 23          return_value sys_refcursor;
 24          stmt varchar2(32767);
 25          i pls_integer := 1;
 26      begin
 27          stmt := 'select '''||p_tab(1)||''' from dual';
 28          while i < p_tab.count()
 29          loop
 30              i := i+1;
 31              stmt := stmt||' union all select '''||p_tab(i)||''' from dual';
 32          end loop;
 33          open return_value for stmt;
 34          return return_value;
 35      end get_resultset;
 36
 37      function get_resultset_for_str (p_string  in    long)
 38          return sys_refcursor
 39      is
 40          l_tab myTableType;
 41          return_value sys_refcursor;
 42      begin
 43          l_tab := split(p_string);
 44          return_value :=  get_resultset (l_tab);
 45          return return_value;
 46      end get_resultset_for_str;
 47
 48  end pkg1;
 49  /

Package body created.

SQL>

它在 SQL*Plus 中的工作原理如下：

SQL> var rc refcursor
SQL> exec :rc := pkg1.get_resultset_for_str('ABC,DEF,XYZ')

PL/SQL procedure successfully completed.

SQL> print rc

'AB
---
ABC
DEF
XYZ

SQL>