:
Новые возможности в PL/SQLИгорь Мельников Консультант по базам данных
Новый тип данных SIMPLE_INTEGER
В 11g: новый тип для целых чисел
- Является подтипом типа PLS_INTEGER
- Но в отличие от PLS_INTEGER, для переменных типа SIMPLE_INTEGER НЕ генерируется код проверки на переполнение (overflow checking), и код проверки на NULL
- Повышение быстродействия в среднем 20-30% по сравнению с PLS_INTEGER
- Области применения: операции с целочисленной арифметикой, где заранее известен диапазон (напр: счетчики циклов)
- Переменные типа SIMPLE_INTEGER имеют неявное ограничение NOT NULL !
declare
xCount simple_integer;
begin
null;
end;
/
xCount simple_integer;
*
ERROR at line 2:
ORA-06550: line 2, column 10:
PLS-00218: a variable declared NOT NULL must have an
initialization assignment
Последовательности в PL/SQL
До PL/SQL 11g
create or replace trigger Trg
before insert on My_Table for each row
declare
s number;
begin
-- Автоматическаягенерацияпервичногоключаselect My_Seq.Nextval into s from dual;
:New.Id := s;
end;
В PL/SQL 11g
create or replace trigger Trg
before insert on My_Table for each row
begin
:New.Id := My_Seq.Nextval;
end;
- Обращение к последовательности прямо в выражении - проще и короче код
- Повышение производительности (не нужно обрабатывать курсор), используется прямой и более
- быстрый механизм доступа –НЕ препроцессинг в select from dual !
- Переключение контекста как таковое все равно нужно: последовательность лежит в SGA
Виды динамического SQL в PL/SQL
До PL/SQL 11g: виды динамического SQL в PL/SQL
- Пакет DBMS_SQL
- Используется, если на этапе компиляции неизвестна структура курсора, число и типы переменных привязки
- Позволяет выяснить структуру курсора в run-time
- Сложный синтаксис !
- Native Dynamic SQL, NDS (операторы EXECUTE IMMEDIATE и OPEN FOR)
- Число и типы переменных привязки должны быть известны во время компиляции
- Структура курсора должна быть известна !
- Простой и короткий синтаксис !
Выбор между видами dynamic-SQL
До 11g: Преимущества NDS по сравнению с DBMS_SQL
procedure insert_into_table(pTableName varchar2,
pDeptName varchar2) is
stmt_str varchar2(200);
begin
stmt_str := 'insert into '|| pTableName ||
' values (:dname);';
cur_hdl := DBMS_SQL.OPEN_CURSOR;
end;
EXECUTE IMMEDIATE stmt_str USING pDeptName
Переключение между видами dynamic SQL
В PL/SQL 11g: Динамическое переключение
Новое в динамическом SQL
PL/SQL 11g: Переключение из DBMS_SQL в NDS
declare
cur_mum number := DBMS_SQL.OPEN_CURSOR();
cur_ref sys_refcursor;
type emps_t is table of employees%rowtype;
emps emps_t;
begin
DBMS_SQL.PARSE(c => cur_num,
Language_Flag => DBMS_SQL.Native,
Statement => ‘select * from Employees
where Department_ID = :d and ...');
...
dummy := DBMS_SQL.EXECUTE(cur_num);
-- Переключение в ref cursor и NDS
cur_ref := DBMS_SQL.TO_REFCURSOR(cur_num);
fetch cur_ref bulk collect into emps;
close cur_ref;
PL/SQL 11g: Переключение из NDS в DBMS_SQL
declare
cur_num number;
cur_ref sys_refcursor;
type emps_t is table of employees%rowtype;
emps emps_t;
begin
open cur_ref for ‘select * from employees
where Department_ID = :d and ...‘;
-- Переключение из NDS в DBMS_SQL
cur_num := DBMS_SQL.TO_CURSOR_NUMBER(cur_ref);
-- Получаем структуру курсора
DBMS_SQL.DESCRIBE_COLUMNS2(cur_num, col_cnt, col_desc);
...
DBMS_SQL.CLOSE_CURSOR(cur_num);
end;
PL/SQL 11g: Замечания по переключению
- Переключение однонаправлено (нельзя возвратиться в исходный вид):
- DBMS_SQL -> NDS
- NDS -> DBMS_SQL
- Курсор закрывается только в том виде dynamic-SQL, куда произошло переключение
- Оператор CLOSE в NDS
- Процедура CLOSE_CURSOR в пакете DBMS_SQL
- Кэширование курсоров в DBMS_SQL продолжает выполняться ("cost saving paradigm”)
PL/SQL 11g: Другие новшества в динамическом SQL
•DBMS_SQL.PARSE может принимать на вход строки типа CLOB (> 32Kb)
•Теперь нет необходимости в использовании VARCHAR2S
•EXECUTE IMMEDIATE поддерживает CLOB-строки
•Переменные привязки могут быть объектного типа, в том числе и коллекции
•Раньше приходилось использовать для этого NDS
•Пакетное связывание (bulk binding) поддерживается для коллекций объектных типов
Работа с циклами в PL/SQL
До 11g: Переход на следующий шаг цикла
begin
for f in (select * from employees)
loop
if f.Salary < 1000 then
goto next_iteration;
end if;
...
<<next_iteration>>
null;
end loop;
end;
PL/SQL 11g: Переход на следующий шаг цикла
begin
for f in (select * from employees)
loop
if f.Salary < 1000 then
continue;
end if;
...
end loop;
end;
Новый оператор “ontinue”в PL/SQL
PL/SQL 11g: Переход на следующий шаг внешнего цикла
<<outer>>for i in 1..10 loop
...
<<inner>>for j in 1..Data.Count() loop
if Data(j).Uninteresting then
continue outer;
end if
...
end loop;
end loop;
Новый оператор “ontinue”в PL/SQL
PL/SQL 11g: Переход на следующий шаг внешнего
цикла по условию
<<outer>>for i in 1..10 loop
...
<<inner>>for j in 1..Data.Count() loop
continue outer when Data(j).Uninteresting;
...
end loop;
end loop;
Вызов метода super-класса в PL/SQL
До 11g: Вызов перекрытого метода в классе-потомке
create or replace type TObject as object
(
...
member function getName return varchar2,
...
)
not final;
-- Перегружаем метод getName в типе- потомке
create or replace type TMyObject under TObject
(
...
overriding member function getName return varchar2,
...
);
Вызов метода super-класса в PL/SQL
До 11g: Невозможно напрямую вызвать перекрытый метод в классе-потомке
create or replace type body TMyObject is
...
overriding member function getName return varchar2 is
begin
return ‘_’|| self.getName; //Каквызватьметодпредка???
end; //Super –вJava
//Inherited вPascal
...
end;
•Приходилось использовать для этого различные
workarounds
•Например: http://www.citforum.ru/database/oracle/oo_pl_sql/
Вызов метода super-класса в PL/SQL
PL/SQL 11g: Вызов переопределенного метода в классе-потомке
overriding member function getName return varchar2 is
begin
return ‘_’|| (self as TObject).getName;
end;
•Выполняется явное преобразование к нужному типу-предку с помощью оператора AS
Inline-подстановка в PL/SQL
PL/SQL 11g: Подстановка тела функции вместо вызова
begin
PRAGMA INLINE(my_func, 'YES'); -- включаем подстановку
for f in (select * from employees)
loop
x:= my_func(f.Name, f.amount) + 17; -- невызов, атело-- функции!
end loop;
PRAGMA INLINE(my_func, 'NO'); -- выключаем подстановку
…end;
•Увеличение скорости выполнения: вместо передачи параметров, возврата управления и результатов
•Включение/выключение подстановки в коде
Новый уровень оптимизации (level 3)
alter session set plsql_optimize_level=3;
•Оптимизатор PL/SQL сам делает подстановку (inline) исходя из:
•Размера процедуры/функции
•Предполагаемой частоты вызова (вызов в цикле)
•Числа и типов параметров
Кэширование результатов в PL/SQL
В 11g: Подстановка значения из кэша вместо вычисления функции
•Новый кэш в SGA –Result Cache
•Хранит результаты вычисления PL/SQL-функций
•Если функция уже выполнялась с теми же самыми параметрами, то вместо выполнения функции PVM может брать значение из этого кэша !
•Разделяется между сессиями
•Может применяться ТОЛЬКО для детерминированных функций, и функций не имеющих побочных эффектов (определяет разработчик) !
В 11g: Ручное управление кэшем результатов
create function getAmount(pAccountId in number,
pCurrency in varchar2) return number
result_cache relies_on (accounts, accounts_amounts); is
begin
………return xRes;
end;
•При создании функции указывается:
•Ключевое слово RESULT_CACHE
•Фраза RELIES_ON –перечень таблиц от которых зависит результат функции
•При изменении таблиц указанных в RELIES_ON кэш для этой функции автоматически очищается
Фраза RELIES_ON может быть опущена
•При этом задача актуальности кэша возлагается на программиста
•Разработчик определяет события по которым кэш очищается (например: триггер, API - пакет таблицы)
•Для этого служит пакет DBMS_RESULT_CACHE и его метод INVALIDATE
begin
………
if xHasChanged then
DBMS_RESULT_CACHE.INVALIDATE(USER,’getAmount’);
end if;
………
end;
В 11g: API для управления кэшем результатов
•Встроенный пакет DBMS_RESULT_CACHE
•INVALIDATE –очистка кэша для функции
•FLUSH –очистка всего кэша
•BYPASS –включение/выключение кэша на уровне экзмепляра
•STATUS –проверка статуса кэша
•MEMORY_REPORT –вывод отчета об использовании кэша
•Динамические представления
•V$RESULT_CACHE_MEMORY
•V$RESULT_CACHE_OBJECTS
•V$RESULT_CACHE_DEPENDENCY
•Параметр экземпляра БД
•RESULT_CACHE_SIZE –задает размер кэша в SGA
Вызов функции в SQL - выражениях
До 11g: возможна только позиционная нотация
S
QL> select
2 get_amount(v_pAccId => a.Id,
3 v_pCurrency => 'USD')as Amount
4 from
5 accounts a
6 /
get_amount(v_pAccId => a.Id,
*
ERROR at line 2:
ORA-00907: missing right parenthesis
•Это создавало проблемы для перегруженных (overload) функций
В 11g: возможна нотация по имени, а также смешанная
SQL>select
2 get_amount(a.Id,
3 v_pCurrency => 'USD')as Amount
4 from
5 accounts a
6 /
Amount
-------------
538
537
536
535
4 rows selected.
Анализ и оптимизация производительности в PL/SQL
В 11g: новый профайлер
•Встроенный пакет DBMS_HPROF
•На выходе формируется trc-файл (текстовый)
•Вывод дополнительной информации: dynamic-sql, static-sql, init-секция и т.д.
•Показ иерархия вызовов
•рlshprof –утилита для преобразования trc-файла в html-отчет
•Не требуется перекомпиляция (DEBUG) и изменение кода
В 11g: пример работы профайлера
Включить профилирование для процедуры pkg.myproc
VARIABLE runid NUMBER;
BEGIN
:runid := DBMS_HRPROF.analyze(
'PLSHPROF_DIR',
'test.trc',
trace => '"HR"."PKG"."MYPROC"');
END;
Получить отчет
•% plshprof -output report test.trc
В 11g: пример отчета профайлера
Анализ исходного кода PL/SQL
В 11g: новый инструмент - PL/Scope
•Сбор информации об всех идентификаторах исходного кода
•Аналог Cscope для языка C (http://cscope.sourceforge.net)
•Параметр PLSCOPE_SETTINGS –для определения типа собираемых идентификаторов
•Представление USER_IDENTIFIERS –для просмотра
•Предварительно нужно перекомпиляция
•Для wrapped-кода сбор информации невозможен !
•Интеграция с SQL Developer
В 11g: PL/Scope - пример
SQL> alter session set plscope_settings='identifiers:all';
SQL> alter type TObject compile;
SQL> select name, type, usage from USER_IDENTIFIERS;
NAME TYPE USAGE
----------- ------------------ -----------
V_PSTATE FORMAL IN DECLARATION
SELF FORMAL IN OUT DECLARATION
SETSTATE PROCEDURE DECLARATION
SETSTATE PROCEDURE DEFINITION
GETSTATE FUNCTION DECLARATION
GETSTATE FUNCTION DEFINITION
GETNEWID FUNCTION DECLARATION
GETNEWID FUNCTION DEFINITION
GETID FUNCTION DECLARATIO
| < Предыдущая | Следующая > |
|---|
