PL/SQL函数:从函数中提取多行

Question

我的数据库中有多个表。我必须找出这些表的名称。所以我写了以下程序：

CREATE OR REPLACE FUNCTION fun_tablefinder( keyword VARCHAR2 )
RETURN NUMBER
IS
 v_query VARCHAR2(200);tablename VARCHAR2(20);
 tablename NUMBER;
 BEGIN
v_query:='SELECT count(TABLE_NAME) FROM USER_TAB_COLUMNS WHERE TABLE_NAME LIKE ''%'||upper(keyword)||'%''';
EXECUTE IMMEDIATE v_query INTO tablename;
RETURN tablename;
END;

但是我的查询中存在错误，我无法修复：

tablename:= fun_tablefinder('ubl'); is not working

，我对如何在调用此功能时如何提取多行感到困惑。如何使用循环处理这些结果？

谢谢。

Answer 1

您需要一个返回嵌套表的函数。在您的功能版本中，我使用dbms_debug.vc2coll DataType，一种Oracle内置集合类型。

请注意，使用散装收集来填充带有多行的集合。

create or replace function     fun_tablefinder( keyword VARCHAR2 )
    RETURN dbms_debug.vc2coll
IS
   tablenames dbms_debug.vc2coll;
BEGIN
    SELECT TABLE_NAME
    bulk collect into tablenames
    FROM USER_TABLES 
    WHERE TABLE_NAME LIKE upper(keyword)||'%';
    RETURN tablenames;
END;

不确定为什么要使用动态SQL，这是完全不必要的。另外，您可能需要查询每个表返回一行的User_table，而不是您从User_tab_coulmns获得的多个命中。

使用此查询是部署Table（）函数的简单问题：

select * 
from table ( fun_tablefinder('UBL') );

---

显然，这是SQL函数的微不足道使用，对于直率SQL查询就足够的情况，它是完全工程的。