Читать «Язык PL/SQL» онлайн - страница 90

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

PL/SQL procedure successfully completed.

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

30

PL/SQL procedure successfully completed.

Как видно, использование NDS позволяет писать очень компактный код.

Пакет DBMS_SQL

Использование встроенного пакета DBMS_SQL для выполнения динамического SQL предусматривает в общем случае последовательность из 8 этапов.