Функция 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 тип данных, тип встроенной коллекции Oracle.

Обратите внимание на использование BULK COLLECT для заполнения коллекции несколькими строками.

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_TABLES, который возвращает одну строку на таблицу, а не несколько попаданий, которые вы получите от USER_TAB_COULMNS.

Чтобы использовать этот запрос, достаточно просто развернуть функцию TABLE() следующим образом:

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

---

Очевидно, что это тривиальное использование функции SQL, которая полностью перегружена для сценария, в котором достаточно прямого запроса SQL.