Хорошие привычки в PL/SQL

Далее будет говориться об Oracle Database 11g, о тех привычках что мы выработали со временем.

Стандарт

Внутри команды нужно разработать свои стандарты (и естественно учитывать стандарты клиента).
Минимум который надо включить в стандарт:

• Именование объектов базы данных (таблицы, представления, типы, коллекции, пакеты и т.д.);
• Именование переменных (область видимости, локальность, константность, тип переменной, курсор, переменные курсора)
• Комментирование кода
• Некоторые правила написания кода

По желанию сюда можно включать все что угодно. Важно чтобы все согласились со стандартом и подписались (под страхом отрубания пальцев).

Управление кодом

Было несколько статей на тему миграции баз и версионность. Статьи бесспорно хороши, но только когда у вас несколько десятков табличек. У нас же 200к строк PL/SQL кода (число других объектов сопоставимо с этим) и команда из 10 человек.
Сначала пытались использовать для контроля всего связку RequesitePro + ClearQuest + ClearCase, но после непродолжительного времени от СС отказались и заменили на git с локальным хранилищем на gitorious.
Разные части приложения разделили на разные репозитории. Создали отдельные репозитории для модификаций для отдельных клиентов.
Подход к файлам следующий: есть baseline и есть патчи к нему, со временем и то и другое обновляется.

Написание кода

Рассмотрим простую процедуру (это только пример):

1. CREATE OR REPLACE PROCEDURE upd_for_dept (
2. dept_in IN employee.department_id%TYPE
3. ,newsal_in IN employee.salary%TYPE)
4. IS
5. CURSOR emp_cur IS
6. SELECT employee_id,salary,hire_date
7. FROM employee WHERE department_id = dept_in;
8. BEGIN
9. FOR rec IN emp_cur LOOP
10. UPDATE employee SET salary = NVL(newsal_in, 1000)
11. WHERE employee_id = rec.employee_id;
12. END LOOP;
13. END upd_for_dept;

* This source code was highlighted with Source Code Highlighter.

Здесь стоит обратить еще внимание на %TYPE. Всегда когда тип переменной это тип какой то колонки или поля, то следует использовать %TYPE.
В цикле FOR выполняется множество операторов UPDATE (и да всю процедуру можно запихать в один UPDATE). В Oracle DB pl/sql и sql код выполняют разные движки, и соответственно будет множество переключений контекстов между этими движками, что занимает какое то время.
Поэтому перепишем нашу процедуру следующим образом:

1. CREATE OR REPLACE PROCEDURE upd_for_dept (
2. dept_in IN employee.department_id%TYPE
3. ,newsal_in IN employee.salary%TYPE)
4. IS
5. deptlist id_list;
6. BEGIN
7. SELECT employee_id
8. BULK COLLECT INTO deptlist
9. FROM employee WHERE department_id = dept_in;
10.  
11. FORALL indx IN deptlist.FIRST..deptlist.LAST
12. UPDATE employee
13. SET salary = NVL(newsal_in, 1000)
14. WHERE employee_id = deptlist(indx);
15. END upd_for_dept;

* This source code was highlighted with Source Code Highlighter.

Оператор FORALL не вызовет переключений контекстов. Но что здесь еще плохо. Правильно это NVL. NVL — это функция PL/SQL, следовательно снова будет переключение кон текстов. Поэтому ее следует заменить на COALESCE или оператор CASE.
Тут естественным образом вырисовывается правило, об использовании функций встроенных в SQL движок.
При использовании коллекций естественным образом меняется дизайн процедур и функций. Конечным бы вариантом стала бы такая процедура:

1. CREATE OR REPLACE PROCEDURE upd_for_dept (
2. deptlist IN id_list,
3. ,newsal_in IN employee.salary%TYPE)
4. IS
5.  
6. BEGIN
7. FORALL indx IN deptlist.FIRST..deptlist.LAST
8. UPDATE employee
9. SET salary = COALESCE(newsal_in, 1000)
10. WHERE employee_id = deptlist(indx);
11. END upd_for_dept;

* This source code was highlighted with Source Code Highlighter.

Коллекция создается следующим образом:

1. CREATE TYPE id_list IS TABLE OF INTEGER;

* This source code was highlighted with Source Code Highlighter.

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

• Вы обязаны понимать и уметь использовать различные типы коллекций
• SQL%BULK_ROWCOUNT поможет узнать количество затронутых строк
• SAVE EXCEPTIONS в FORALL поможет отловить ошибки
• В FORALL может быть только один SQL оператор

Практически всегда (я ни разу не видел чтобы это было не верно) скорость выполнения кода использующего коллекции в 6 раз выше по сравнению с обычным способом.
На этом я хочу закончить.
Намеренно не освещены такие темы как обработка ошибок и логирование, CBO оптимизация запросов. Если будет интересно освещу эти моменты.