Язык PL/SQL

Удаляется из очереди задание следующим образом:

SQL> BEGIN

2 DBMS_JOB.remove(23);

3 COMMIT;

4 END;

5 /

PL/SQL procedure successfully completed.

Файловый ввод-вывод

Встроенный пакет UTL_FILE предоставляет программам PL/SQL возможность работать с файлами на сервере. С помощью этого пакета можно решать следующие задачи:

формирование на сервере небольших текстовых отчетов;

загрузка в базу данных строк небольших текстовых файлов;

чтение log-файлов сервера для мониторинга ошибок;

формирование log-файлов программ PL/SQL.

Для загрузки/выгрузки данных значительных (мегабайты, гигабайты) объемов следует использовать не пакет UTL_FILE, а использовать клиентские приложения – SQL*Loader для загрузки данных и SQL*Plus для их выгрузки. Если запускать эти утилиты на сервере баз данных, то их использование – самый быстрый способ загрузить и выгрузить данные из базы в файлы. В то же время при разработке серверной бизнес-логики встречаются ситуации, когда читать и писать в файлы хотелось бы именно в хранимых программах на PL/SQL без усложнений, вызванных обращениями к другим средствам. Чтобы для каждого поступившего платежа выгружать на сервере XML-файл размером меньше килобайта пакет UTL_FILE вполне подходит.

Настройка ограничений доступа к каталогам

Считается, что UTL_FILE потенциально может представлять большую угрозу безопасности баз данных Oracle. Этот пакет дает возможность доступа к файлам от имени такого пользователя операционной системы, который в ней имеет доступ ко всем файлам данных, журнала базы данных, управляющим файлам, файлам паролей и параметров. Получив неправомерный доступ к пакету UTL_FILE, при определенных навыках можно довольно быстро грамотно вывести базу данных из строя или реализовать угрозу нарушения конфиденциальности информации (например, прочитать файлы данных).

По этой причине перед использованием пакета UTL_FILE требуется настроить ограничения доступа к каталогам, с файлами из которых сможет осуществлять операции пакет. Настроить ограничения доступа к каталогам можно сделать двумя способами:

использовать параметр базы данных utl_file_dir;

создать специальные объекты баз данных – директории (DIRECTORY) и предоставить пользователям привилегии доступа к ним.

В параметре utl_file_dir задается список каталогов, с файлами из которых может осуществлять операции UTL_FILE. Для просмотра текущего значения параметра utl_file_dir можно использовать представление словаря данных V$PARAMETER или команду SHOW PARAMETER утилиты SQL*Plus:

SQL> SELECT value dir FROM V$PARAMETER

2 WHERE name='utl_file_dir';

DIR

–

C:\Dir1

SQL> SHOW PARAMETER utl_file_dir

NAME TYPE VALUE

– – –

utl_file_dir string C:\Dir1

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

Директория (directory) – объект баз данных Oracle, являющийся псевдонимом каталога в файловой системе сервера. Директории создаются DDL-командами следующего вида:

SQL> CREATE OR REPLACE DIRECTORY dir1 AS 'C:\Dir1';

Directory created.

По сравнению с использованием параметра utl_file_dir, директории дают более гибкие возможности по управлению доступом к файлам. Так, после того как директория создана, администратор базы данных может предоставить конкретным пользователям привилегии только на чтение из нее:

SQL> GRANT READ ON DIRECTORY dir1 TO user1;

Grant succeeded.

Параметр utl_file_dir задает список каталогов сразу для всех пользователей, которые имеют привилегии на выполнение программ пакета UTL_FILE, причем настроить конкретные действия (чтение или запись) с файлами из этих каталогов с его помощью нельзя.

Использование UTL_FILE

В пакете UTL_FILE используется следующая последовательность действий с файлами – сначала файл открывается в заданном режиме, затем производятся действия с его содержимым, по окончании которых файл закрывается. После открытия файла во все процедуры и функции пакета UTL_FILE передается его идентификатор, который представляет собой переменную-запись PL/SQL объявленного в пакете UTL_FILE типа.

Таблица 9. Программы пакета UTL_FILE.

Программа

Описание программы

FOPEN (функция)

открывает файл для чтения/записи

IS_OPEN (функция)

проверяет, открыт ли файл

FCLOSE (процедура)

закрывает открытый файл

FCLOSE_A (процедура)

закрывает все открытые файлы (a – all)

GET_LINE (процедура)

считывает строку из файла

PUT (процедура)

записывает в файл строку без символа конца строки

PUT_LINE (процедура)

записывает в файл строку с символом конца строки

PUTF (процедура)

записывает в файл форматированный текст

FFLUSH (процедура)

вызывает физическую запись буферизированных данных

Файл может быть открыт процедурой UTL_FILE.FOPEN в одном из трех режимов:

для чтения (read) – содержимое файла не будет изменяться;

для записи (write) – содержимое файла будет перезаписано;

для добавления данных (append) – запись будет осуществляться в конец файла без перезаписи имевшегося содержимого.

В качестве примера работы с пакетом UTL_FILE приведем код процедуры table_copy, которая построчно сохраняет выборку SQL-запроса к таблице tab1 в файл. У table_copy имеется параметр p_mode, определяющий режим открытия файла.

SQL> CREATE OR REPLACE PROCEDURE table_copy(p_mode IN VARCHAR2) IS

2 fid UTL_FILE.FILE_TYPE;

3 BEGIN

4 fid := UTL_FILE.FOPEN (location=> 'C:\Dir1',

filename => 'f-name.txt',

open_mode=> p_mode);

5 FOR rec IN (SELECT at1,at2 FROM tab1) LOOP

6 UTL_FILE.PUT_LINE (fid, rec.at1||' '||rec.at2);

7 END LOOP;

8 UTL_FILE.FCLOSE (fid);

9 EXCEPTION

10 WHEN UTL_FILE.INVALID_PATH

11 THEN DBMS_OUTPUT.PUT_LINE('Неверный каталог');

12 WHEN UTL_FILE.INVALID_MODE

13 THEN DBMS_OUTPUT.PUT_LINE('Неверный режим работы с файлом');

14 WHEN UTL_FILE.INVALID_FILEHANDLE

15 THEN DBMS_OUTPUT.PUT_LINE('Ошибочный дескриптор файла');

16 WHEN UTL_FILE.READ_ERROR

17 THEN DBMS_OUTPUT.PUT_LINE('Ошибка при чтении файла');

18 WHEN UTL_FILE.WRITE_ERROR

19 THEN DBMS_OUTPUT.PUT_LINE('Ошибка при записи в файл');

20 WHEN UTL_FILE.INTERNAL_ERROR

21 THEN DBMS_OUTPUT.PUT_LINE('Произошла внутренняя ошибка');

22 WHEN OTHERS

25 THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);

26 END;

27 /

Procedure created.

SQL> BEGIN

2 table_copy('A');

3 END;

4 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM tab1;

AT1 A

– -

1 A

2 B

3 C

– команда HOST утилиты SQL*Plus позволяет выполнять команды ОС

– выполняем прямо из SQL*Plus команду type ОС Windows

SQL> HOST type C:\Dir1\f-name.txt

1 A

2 B

3 C

В ходе выполнения процедуры table_copy с параметром 'A' (append) в конец файла fname.txt, находящийся в каталоге C:\Dir1, будут записаны все строки таблицы tab1. Обратите внимание – если вызвать процедуру table_copy с параметром 'W' (write), то существующее содержимое файла будет перезаписано на содержимое таблицы.

В старших версиях сервера Oracle с помощью пакета UTL_FILE можно копировать и удалять файлы. Также для работы с файлами на сервере можно использовать хранимые программы на Java.

Работа с большими объектами

В Oracle имеются специальные типы данных для хранения больших объектов (Large Objects, LOB), размеры которых могут измеряться в террабайтах:

BLOB – тип для представления бинарных данных (значение содержит локатор на большой бинарный объект, хранящийся в базе данных);

CLOB – тип для представления символьных данных (значение содержит локатор на большой символьный объект, хранящийся в базе данных);

BFILE – тип данных для описания файлов (значение содержит указатель на файл, который находится вне базы данных Oracle).

Локатором называется хранящийся в базе данных указатель на данные большого объекта. Значение типа BLOB или CLOB может характеризоваться одним из трех состояний:

содержит NULL (не содержит локатор);

содержит локатор, указывающий данные большого объекта;

содержит локатор, не указывающий ни на какие данные.

Про последнее состояние говорят, что это «пустой» (empty) LOB-объект. «Пустые» LOB-объекты инициализируются встроенными функциями EMPTY_BLOB() и EMPTY_CLOB(). Для определения текущего состояния значения из трех возможных используется следующая логика:

IF some_clob IS NULL THEN

– нет ни данных, ни локатора

ELSIF DBMS_LOB.GETLENGTH(some_CLOB)=0 THEN

– пустой (empty) LOB-объект

ELSE

– данные в LOB-объекте есть

END IF

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

Для работы с данными типа LOB нужно сначала извлечь локатор, а затем с помощью процедур и функций встроенного пакета DBMS_LOB прочитать или записать собственно данные.

Таблица 10. Программы пакета DBMS_LOB.

Программа

Описание программы

APPEND (процедура)

записывает данные в конец LOB-объекта

WRITE (процедура)

записывает данные в LOB-объект по смещению

COMPARE (функция)

сравнивает два LOB-объекта одного типа

GETLENGTH (функция)

возвращает длину LOB-объекта

INSTR (функция)

возвращает позицию вхождения строки в объект

READ (процедура)

считывает часть LOB-объекта

SUBSTR (функция)

возвращает часть LOB-объекта по смещению

FILECLOSE (процедура)

закрывает файл по указателю-значению BFILE

FILEEXISTS (функция)

проверяет наличие файла по указателю

FILEOPEN (процедура)

открывает файл для значения BFILE

COPY (процедура)

копирует LOB-объекты

ERASE (процедура)

удаляет LOB-объект полностью или частично

Работа с файлами с помощью пакета DBMS_LOB

В качестве примера использования пакета DBMS_LOB приведем процедуру f_compare, которая сравнивает файлы в каталоге dir1. Имена файлов передаются как параметры:

SQL> CREATE DIRECTORY dir1 AS 'C:\WORK';

Directory created.

SQL> CREATE OR REPLACE PROCEDURE f_compare

2 (fname1 IN VARCHAR2, fname2 IN VARCHAR2) IS

3 file_1 BFILE;

4 file_2 BFILE;

5 result INTEGER;

6 BEGIN

7 file_1 := BFILENAME('DIR1',fname1);

8 file_2 := BFILENAME('DIR1',fname2);

9 DBMS_LOB.FILEOPEN(file_1);

10 DBMS_LOB.FILEOPEN(file_2);

11 result := DBMS_LOB.COMPARE(file_1,file_2,

12 DBMS_LOB.LOBMAXSIZE,1,1);

13 IF (result != 0) THEN

14 DBMS_OUTPUT.PUT_LINE('Файлы различные');

15 ELSE

16 DBMS_OUTPUT.PUT_LINE('Файлы одинаковые');

17 END IF;

18 DBMS_LOB.FILECLOSE(file_1);

19 DBMS_LOB.FILECLOSE(file_2);

20 END;

21 /

Procedure created.

SQL> BEGIN

2 f_compare('fname.txt','fname.txt');

3 END;

4 /

Файлы одинаковые

SQL> BEGIN

2 f_compare('fname.txt','fname2.txt');

3 END;

4 /

Файлы различные

SQL> BEGIN

2 f_compare('fname.txt','fname3.txt');

3 END;

4 /

BEGIN

*

ERROR at line 1:

ORA-22288: file or LOB operation FILEOPEN failed

The system cannot find the path specified

ORA-06512: at "SYS.DBMS_LOB", line 475

ORA-06512: at "SYSTEM.F_COMPARE", line 9

ORA-06512: at line 2

При последнем вызове процедуры f_compare не удалось открыть указанный файл. Обратите внимание, ошибка произошла при попытке открыть файл, установка указателя BFILE произошла нормально.

Для загрузки файлов в базу данных как LOB-объектов предназначена пакетная процедура DBMS_LOB.LOADFROMFILE, которой в качестве параметров передается переменная типа BFILE, связанная с загружаемым файлом, количество байт, считываемое из файла, и указатель на объект-приемник.

SQL> CREATE TABLE tab1 (at1 NUMBER, at2 BLOB, at3 BFILE);

Table created.

SQL> INSERT INTO tab1 VALUES (2,EMPTY_BLOB(),NULL);

1 row created.

SQL> DECLARE

2 l_BLOB BLOB;

3 file_1 BFILE;

4 BEGIN

5 SELECT at2 INTO l_BLOB FROM tab1

6 WHERE at1=2 FOR UPDATE;

7 file_1 := BFILENAME('DIR1','fname.txt');

8 DBMS_LOB.FILEOPEN(file_1);

10 DBMS_LOB.LOADFROMFILE(l_BLOB,file_1,

11 DBMS_LOB.GETLENGTH(file_1));

12 COMMIT;

13 END;

14 /

PL/SQL procedure successfully completed.

В данном случае сначала строка таблицы с пустым LOB-объектом блокируется с помощью команды SELECT FOR UPDATE, а затем пакетная процедура DBMS_LOB.LOADFROMFILE осуществляет в него загрузку из файла.

Семантика SQL для LOB-объектов

Начиная с версии Oracle 9i, реализована поддержка семантики SQL для LOB-объектов. Это означает, что с BLOB и CLOB могут работать обычные встроенные функции как со значениями типов VARCHAR2 и CHAR (используются перегруженные версии встроенных функций):

SQL> CREATE TABLE clob_table (at1 CLOB);

Table created.

SQL> INSERT INTO clob_table VALUES ('I say :');

1 row created.

SQL> UPDATE clob_table SET at1 = 'Hello, world'||rpad(at1, 1000000, '!');

1 row updated.

SQL> SELECT LENGTH (at1) AS len, TO_CHAR (SUBSTR (at1, 1, 12)) AS words

2 FROM clob_table;

LEN WORDS

– –

1000012 Hello, world

Столбец at1 типа CLOB при выполнении предложений UPDATE и SELECT передавался как параметр встроенным функциям определения длины строки LENGTH, выделения подстроки в строке SUBSTR и дополнения строки до заданной длинны RPAD.

Динамический SQL

Предложения SQL, которые не изменяются с момента компиляции программы PL/SQL, называются статическими. Статические предложения SQL формируются компилятором PL/SQL по объявлениям явных курсоров, по командам SELECT INTO и остальным DML-командам языка PL/SQL. После формирования они сохраняются в байт-коде хранимых программ PL/SQL и больше не изменяются.

Термином «динамический SQL» (dynamic SQL) называются предложения SQL, которые динамически формируются как символьные строки непосредственно во время выполнения программ PL/SQL. Эти предложения SQL в байт-коде отсутствуют, поэтому для из выполнения используются специальные механизмы PL/SQL, рассматриваемые далее.

Динамический SQL в PL/SQL в основном применяется для решения следующих задач:

выполнение DDL-команд (CREATE, ALTER, DROP);

поддержка нерегламентированных SQL-запросов (SQL ad hoc queries).

«Создать табличку» в программе PL/SQL нельзя:

SQL> BEGIN

2 CREATE TABLE tab1(at1 INTEGER);

3 END;

4 /

CREATE TABLE tab1(at1 INTEGER);

*

ERROR at line 2:

ORA-06550: line 2, column 3:

PLS-00103: Encountered the symbol "CREATE" when expecting

one of the following: (begin case declare exit for goto if loop pragma …

По префиксу PLS видно, что ошибку выдал компилятор PL/SQL, а из текста сообщения следует, что она произошла на этапе синтаксического анализа кода программы. Даже в грамматике языка PL/SQL не предусмотрено наличие в коде PL/SQL команд, похожих на DDL-команды CREATE.

«…, но если очень хочется, то можно» – для это следует использовать динамический SQL, передавая DDL-команду как символьную строку:

SQL> BEGIN

2 EXECUTE IMMEDIATE 'CREATE TABLE tab1(at1 INTEGER)';

3 END;

4 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM tab1;

no rows selected

Выборка из tab1 проходит без ошибок, значит, таблица существует.

Нерегламентированные запросы SQL

Нерегламентированным является SQL-запрос, у которого до этапа выполнения могут быть не определены следующие три составляющие:

текст SQL-запроса, включая список таблиц во фразе FROM, критерий отбора данных во фразе WHERE, фразы группировки и сортировки;

перечень возвращаемых столбцов;

список параметров.

Примером выполнения нерегламентированных SQL-запросов может быть подбор моделей телефонов по параметрам, похожий на соответствующий сервис на «Яндекс.Маркете».

Пусть таблица моделей телефонов имеет следующий вид:

CREATE TABLE phone_models (model VARCHAR2(100),

LTE INTEGER,

dual_sim INTEGER,

price INTEGER,

color VARCHAR2(100));

INSERT INTO phone_models VALUES('Xiaomi Redmi Note 2',1,1,12500,'black');

INSERT INTO phone_models VALUES('Meizu M2 mini',1,1,11400,'white');

…

Один человек может подбирать себе телефон по двум параметрам «вид – смартфон, цена – в пределах 10 000 – 15 000 рублей», другой человек может подбирать модель не по двум, а по трем параметрам «LTE – да, две SIM-карты – да, цвет – черный». Приложению потребуется сформировать и выполнить в базе данных два разных SQL-запроса. Для первого поиска это будет SQL-запрос с тремя связываемыми переменными:

SELECT * FROM phone_models

WHERE type=:p_1

AND price BETWEEN :p2 AND :p3

со значениями переменных :p1='smartphone', :p2=10000, :p3=15000.

Для второго поиска это будет SQL-запрос тоже с тремя связываемыми переменными, но для других ограничений:

SELECT * FROM phone_models

WHERE LTE=:p_1

AND dual_sim=:p2 AND color=:p3

со значениями переменных :p1=1, :p2=1, :p3='black'

Механизмы выполнения динамического SQL в PL/SQL

Для выполнения динамического SQL в PL/SQL есть два механизма:

встроенный динамический SQL (Native Dynamic SQL, NDS);

встроенный пакет DBMS_SQL.

Динамический SQL в Oracle принято делить на четыре категории.

Таблица 11. Категории динамического SQL в Oracle.

Категория

Описание категории

Категория 1

DDL-команды и предложения UPDATE, INSERT и DELETE без параметров

Категория 2

DDL-команды и предложения UPDATE, INSERT и DELETE с фиксированным количеством параметров

Категория 3

предложения SELECT с фиксированным количеством столбцов и параметров

Категория 4

DML-предложения, в которых количество выбранных столбцов (для запросов) или количество параметров (для всех предложений) неизвестно до стадии выполнения

С помощью встроенного пакета DBMS_SQL можно выполнить динамический SQL всех четырех категорий, с помощью NDS – первых трех категорий, на которые приходится, по некоторым оценкам, до 90% всего динамического SQL.

Встроенный динамический SQL

Главным достоинством NDS является его простота. Для выполнения динамического SQL в пакете DBMS_SQL в общем случае требуется 8 этапов, при этом код PL/SQL выглядит довольно громоздко и далее будет возможность в этом убедиться. С NDS обходятся вызовом одной команды EXECUTE IMMEDIATE («выполнить немедленно»), которая имеет следующий синтаксис:

EXECUTE IMMEDIATE предложение SQL

[ [ BULK COLLECT] INTO {переменная[, переменная]… | запись PL/SQL}]

[USING аргумент[,аргумент]…];

Сразу после ключевых слов EXECUTE IMMEDIATE в одинарных кавычках указывается текст предложения SQL, также в этом месте можно указать символьную переменную с текстом предложения SQL, причем эта переменная может иметь тип данных как VARCHAR2, так и CLOB.

Конструкция INTO со списком переменных предназначена для получения значений столбцов результирующей выборки и используется в том случае, если выполняется предложение SELECT. Число переменных и число столбцов должно совпадать. Переменные в конструкции INTO должны быть скалярных типов данных, соответствующих типам столбцов, или одной записью PL/SQL.

Конструкция USING со списком переменных и констант используется для передачи значений, которые должны быть связаны с имеющимися в тексте предложения SQL связываемыми переменными. Связывание значений в NDS осуществляется по позициям связываемых переменных. Количество передаваемых значений, естественно, должно совпадать c количеством связываемых переменных.

Для SQL-запросов команда EXECUTE IMMEDIATE фактически является аналогом команды SELECT INTO с таким же ограничением на результирующую выборку: запросом должна отбираться ровно одна строка, в противном случае инициируются предопределенные исключения.

Рассмотрим пример использования команды EXECUTE IMMEDIATE:

CREATE TABLE tab1 (at1 INT, at2 VARCHAR2(1));

INSERT INTO tab1 VALUES(1,'A');

INSERT INTO tab1 VALUES(2,'B');

SQL> DECLARE

2 l_tab1 tab1%ROWTYPE;

3 l_sql_text VARCHAR2(100) := 'SELECT * FROM tab1 WHERE at1>=:p_at1';

4 BEGIN

5

6 EXECUTE IMMEDIATE l_sql_text INTO l_tab1_rec USING 2;

7 DBMS_OUTPUT.PUT_LINE('Отобранная строка: '||l_tab1.at1||l_tab1.at2);

8

9 BEGIN

10 EXECUTE IMMEDIATE l_sql_text INTO l_tab1 USING 1;

11 EXCEPTION

12 WHEN OTHERS THEN

13 DBMS_OUTPUT.PUT_LINE(SQLERRM);

14 END;

15

16 BEGIN

17 EXECUTE IMMEDIATE l_sql_text INTO l_tab1 USING 3;

18 EXCEPTION

19 WHEN OTHERS THEN

20 DBMS_OUTPUT.PUT_LINE(SQLERRM);

21 END;

22

23 END;

24 /

Отобранная строка: 2B

ORA-01422: exact fetch returns more than requested number of rows

ORA-01403: no data found

PL/SQL procedure successfully completed.

Рассмотрим еще два примера использования NDS.

Пусть база данных спроектирована таким образом, что у каждой таблицы столбец первичного ключа называется id и имеет тип INTEGER.

Напишем процедуру, которая удаляет строку в любой таблице по значению ее первичного ключа. Параметрами процедуры будут имя таблицы и значение столбца id удаляемой строки.

CREATE TABLE tab1(id INTEGER PRIMARY KEY,at1 CHAR(1));

INSERT INTO tab1 VALUES(1,'a');

INSERT INTO tab1 VALUES(2,'b');

CREATE TABLE tab2(id INTEGER PRIMARY KEY,at1 CHAR(1));

INSERT INTO tab2 VALUES(20,'x');

INSERT INTO tab2 VALUES(30,'y');

SQL> CREATE OR REPLACE PROCEDURE delete_by_id (p_table_name IN VARCHAR2,

2 p_id IN INTEGER) IS

3 BEGIN

4 EXECUTE IMMEDIATE 'DELETE FROM '||p_table_name||' WHERE id=:p_id'

5 USING p_id;

6 DBMS_OUTPUT.PUT_LINE('In table '||p_table_name||' '

7 ||SQL%ROWCOUNT||' rows deleted');

8 END;

9 /

Procedure created.

SQL> set serveroutput on

SQL> EXECUTE delete_by_id('tab1',1);

In table tab1 1 rows deleted

PL/SQL procedure successfully completed.

SQL> EXECUTE delete_by_id('tab1',-1);

In table tab1 0 rows deleted

PL/SQL procedure successfully completed.

SQL> EXECUTE delete_by_id('tab2',20);

In table tab2 1 rows deleted

PL/SQL procedure successfully completed.

Для NDS в PL/SQL поддерживаются средства массовой обработки данных (bulk processing). Конструкция BULK COLLECT указывается в том случае, когда известно, что SQL-запрос может иметь в результирующей выборке не одну, а несколько строк. Тогда переменной, в которую помещается результирующая выборка, должна быть коллекция, то есть и здесь прослеживается аналогия с командой SELECT INTO для статических предложений SQL. Также команда EXECUTE IMMEDIATE может использоваться совместно с рассматриваемой ранее командой FORALL.

Вернем содержимое таблиц tab1, tab2 в исходное состояние и создадим теперь процедуру print_id_list со считыванием в коллекцию всех строк результирующей выборки с помощью конструкции BULK COLLECT.

SQL> CREATE OR REPLACE PROCEDURE print_id_list(p_table_name IN VARCHAR2,

2 p_id IN INTEGER) IS

3 TYPE t_table IS TABLE OF INTEGER;

4 l_table t_table;

5 BEGIN

6 EXECUTE IMMEDIATE 'SELECT id FROM '||p_table_name||' WHERE id>:p_id'

7 BULK COLLECT INTO l_table

8 USING p_id;

9 FOR i IN 1..l_table.COUNT LOOP

10 DBMS_OUTPUT.PUT_LINE(l_table(i));

11 END LOOP;

12 END;

13 /

Procedure created.

SQL> EXECUTE print_id_list('tab1',0);

1

2