xtender 8 авг 2011 в 18:23

Некоторые советы при работе с PLSQL

6 мин

65K

Я решил написать цикл небольших заметок благодаря посту Хорошие привычки в PL/SQL, советы которого и рассмотрю.

В этой части обсудим:

Нюансы %TYPE/%ROWTYPE
Select for update
Работа с коллекциями

1. Нюансы %TYPE/%ROWTYPE

Cделаем тест-кейс — создадим табличку с полем типа Integer и внесем в нее тестовое значение:

create table test_integer(a integer); create type integer_table as table of integer; / insert into test_integer values (1E125); commit; select * from test_integer;

Как видите, значение прекрасно вставилось. А теперь давайте попробуем его получить в pl/sql блоке:

declare l_a test_integer.a%type; begin select a into l_a from test_integer; dbms_output.put_line('Все ок, значение=' || l_a); exception when others then dbms_output.put_line('Все плохо! '||sqlerrm); end; /

Все плохо! ORA-06502: PL/SQL: numeric or value error: number precision too large
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02

Причина ошибки кроется в том, что INTEGER в SQL это number(*,0) — floating-point number, у которого scale = 0, а INTEGER в PL/SQL это «SUBTYPE INTEGER IS NUMBER(38,0);».

Что же насчет типа из той статьи "CREATE TYPE id_list IS TABLE OF INTEGER;"?
Естественно, это коллекция pl/sql integer'ов:

DB1> declare
  2    l_int_tab integer_table;
  3  begin
  4    select a
  5           bulk collect into l_int_tab
  6    from test_integer;
  7    dbms_output.put_line('Все ок!');
  8  exception when others then
  9    dbms_output.put_line('Все плохо! Exception:'||sqlerrm);
 10  end;
 11  /
Все плохо! Exception:ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

Тип id_list я создал как integer_table — это просто моя прихоть создавать типы коллекций с постфиксом _TABLE. Кроме того, я обычно не создаю по типу на каждую таблицу table of table_name%rowtype, а только на часто используемые или если эти типы используются в параметрах процедур и функций. Кстати говоря, если у вас oracle < 11g и вы создаете тип в пакете и затем используете его в параметрах или полях объектов уровня схемы, то это автоматически создает и тип на уровне схемы с «многозначными» названиями типа: SYS_PLSQL_2906526_17_1, в 11g эти типы просто скрыли.

2. Select for update

Не секрет, что если мы хотим что-то проапдейтить в выборке, то надо использовать select for update для блокировки необходимых строк целиком или полей в них. Например, в той процедуре из обсуждаемой статьи нет кляузы for update и результаты могут быть фееричными(изменение salary не у тех работников, если в параллельной сессии изменялся отдел, потеря изменений salary в случае параллельного изменения salary другой сессией в зависимости от очередности коммита в 1-й или 2-й сессии), поэтому select следовало бы переписать так:

SELECT e.employee_id,e.salary,e.hire_date FROM employee e WHERE department_id = dept_in FOR UPDATE OF e.salary;

Учтите, что «OF e.salary» не означает, что заблокировано будет изменение только поля e.salary(такое невозможно), будут заблокированы все строки таблицы с алиасом e:

The columns in the OF clause only indicate which table or view rows are locked.

Указание здесь e.salary означает лишь к кому дополнительно применять механизм statement restart, и, поэтому в данном случае, как и во всех случаях когда вам нужно заблокировать строки всех таблиц из FROM, а изменяемые поля перечислены в списке select, можно не указывать «OF ...»
Кстати, с for update можно нарваться на ORA-00060: deadlock detected.
Рассмотрим пример:

create table test_integer pctfree 99 pctused 1 as select level id, trunc(10*dbms_random.value) val, rpad('x',100) padding from dual connect by level <=100 / create index test_indeger_idx on test_integer(id) / exec dbms_stats.gather_table_stats(ownname => user,tabname => 'TEST_INTEGER',cascade => true);

Эмулировать задержку будем функцией:

create or replace function integer_delay(p_i in integer,p_interval in integer) return integer is begin dbms_lock.sleep(seconds => p_interval); return p_i; end; 

И запустим в параллельных сессиях:
1:

begin for rec in ( select --+ index(test_integer_idx) id,val from test_integer where integer_delay(id,1) in (100,30,1) order by id desc for update ) loop dbms_output.put_line(rec.id); end loop; commit; end;

Иногда когда важна скорость можно воспользоваться параметрами к for update: nowait или skip locked. Кстати, следует учесть, что в случае использования skip locked оконные функции и rownum будут отрабатывать на целом наборе не исключая блокированных. Пример:
1-я сессия:

select * from test_integer where id in (1,3,4,8) for update;

2-я сессия:

select id ,row_number()over (order by id) rn ,rownum from test_integer t where id < 10 order by id for update skip locked

3. Работа с коллекциями

Весьма забавно было прочитать в том посте о том, что всегда надо работать с коллекциями, да и еще и обрабатывать их целиком. Про «FOR LOOP» и оптимальное количество для bulk операций Томом освещается здесь и выяснять кол-во вам нужно непосредственно в конкретной вашей ситуации.
В случае же с FORALL надо уточнить сразу несколько моментов:

FORALL не атомарен — то есть, если данные которые он будет видеть будут не на момент начала выполнения FORALL, а на момент выполнения конкретного его цикла получения набора, а обновление он будет проводить потом, когда получит готовый измененный набор(это для read committed, для serializable получим ora-08177).

Проведем пример снова с test_ab, только сначала снова установите b=1 для всех столбцов.
- Запускаем первую сессию:
 
 declare type number_table is table of number; procedure bulk_update(p_nt in number_table) is begin forall n in p_nt.first..p_nt.last update test_ab set b=b*10 where a=p_nt(n) and a=integer_delay(a,10); end bulk_update; begin bulk_update(number_table(1,3,7,10)); end;
- А потом спустя пару секунд запускаем вторую сессию и преспокойно меняем любое b из того же набора:
 
 update test_ab set b=10 where a=7;
 
 Как видите, пока мы не завершим вторую транзакцию, первая будет заблокирована, и при этом в первой транзакции будут видны значения второй, хотя мы ее запустили последней. Можно, конечно, сначала заблокировать таблицу test_ab, например, через тот же select for update, но так ли это удобно и всегда ли необходимо?
Управление планами выполнения в FORALL усложняется.
В случае использования различных SQL-вариантов мы можем относительно легко подстраивать план под массовые update, что в случае с forall затруднительно, с другой стороны — возможно, что улучшать ничего и не надо(например, в случае index range scan).

В общем, это момент неоднозначный и категорически нельзя советовать во всех случаях что-то одно.

PS. Продолжение следует…

Код в статье оформлен с помощью Source Code Highlighter.

Теги:

Хабы:

Oracle

Некоторые советы при работе с PLSQL

1. Нюансы %TYPE/%ROWTYPE

2. Select for update

3. Работа с коллекциями

Публикации

Истории

Работа

Ближайшие события