Вернуть строки данных из блока pl/sql

Question

Я хочу написать код pl/sql, который использует Cursor и Bulk Collect для получения моих данных. В моей базе данных есть строки порядка миллионов, и иногда мне приходится запрашивать ее, чтобы получить почти все записи по запросу клиента. Я делаю запросы и последующую обработку в пакетах, чтобы не перегружать сервер и показывать постепенный прогресс клиенту. Я видел, что копание для последующих партий занимает значительно больше времени, поэтому я пытаюсь сделать это с помощью курсора.

Вот что должно быть простым pl/sql вокруг моего основного SQL-запроса:

declare
  cursor device_row_cur
    is
      select /my_query_details/;

  type  l_device_rows is table of device_row_cur%rowtype;
  out_entries l_device_rows := l_device_rows();

begin
    open device_row_cur;    
      fetch device_row_cur
      bulk collect into out_entries
      limit 100;        
  close device_row_cur; 
end;

Я делаю партии по 100 и доставляю их в out_entries, Проблема в том, что этот блок компилируется и выполняется просто отлично, но не возвращает выбранные строки данных. Я хотел бы, чтобы он возвращал эти строки точно так же, как и выбор. Как этого достичь? Есть идеи?

Answer 1

Я изучил эту превосходную статью по оптимизации пагинации: http://www.inf.unideb.hu/~gabora/pagination/article/Gabor_Andras_pagination_article.pdf

Я использовал технику 6 в основном. Описывает, как ограничить запрос для выборки страницы x и далее. Для дополнительного улучшения вы можете ограничить его выборкой только страницы x. При правильном использовании он может повысить производительность в 1000 раз.

Вместо того, чтобы возвращать пользовательские строки таблицы (что очень сложно, если не невозможно, для интерфейса с Java), я решил открыть sys_refcursor в моем pl/sql, который может быть связан с такими интерфейсами, как:

OracleCallableStatement stmt = (OracleCallableStatement) connection.prepareCall(sql); stmt.registerOutParameter(someIndex, OracleTypes.CURSOR); stmt.execute(); resultSet = stmt.getCursor(idx);

Answer 2

Анонимный блок не может ничего вернуть. Вы можете присвоить значения переменной связывания, включая тип коллекции или курсор ref внутри блока. Но коллекция должна быть определена, а также объявлена ​​вне блока. То есть это должен быть тип, который вы можете использовать в простом SQL, а не тот, который определен в PL/SQL. В данный момент вы используете тип PL / SQL, который определен в блоке, и переменную, которая также объявлена ​​в блоке - так что это выходит за рамки видимости для клиента и также не будет допустимым типом вне его., (Его также не нужно инициализировать, но это незначительная проблема).

В зависимости от того, как он будет в действительности использоваться, одним из вариантов является использование курсора ref, и вы можете объявить и отобразить его через SQL*Plus или SQL Developer с variable а также print команды. Например:

variable rc sys_refcursor

begin
  open :rc for ( select ... /* your cursor statement */ );
end;
/

print rc

Вы можете сделать нечто подобное из клиентского приложения, например, иметь функцию, возвращающую ref-курсор или процедуру с выходным параметром, который является ref-курсором, и связывать это из приложения. Затем переберите курсор ref в качестве результирующего набора. Но детали зависят от языка вашего приложения.

Другой вариант - иметь конвейерную функцию, которая возвращает тип таблицы - снова определенную на уровне SQL (с create type) не в PL/SQL - который может потреблять меньше ресурсов, чем коллекция, возвращаемая за один раз.

Но я должен спросить, зачем ты это делаешь. Вы сказали, что "поиск более поздних пакетов занимает значительно больше времени", что звучит так, как будто вы используете механизм подкачки в своем запросе, генерируете номер строки и затем выбираете диапазон 100 в этом запросе. Если ваш клиент / приложение хочет получить все строки, то было бы проще выполнить один запрос, но получить набор результатов в пакетном режиме.

К сожалению, без какой-либо информации о приложении это просто предположение...