|
| |||
Стивен Ферстайн О путанице и перекомпиляции Источник: журнал “Oracle Magazine”, #3, 2007,
Применяйте правильные приемы при именовании программ, перекомпиляции инвалидных программ и их сборке. Вопрос: У меня есть пакет pck_events_1, в котором есть функция fn_1. И есть другая функция уровня схемы ("standalone"), которая также называется fn_1 и определена в этой же схеме. Как вызвать функцию уровня схемы из подпрограммы в пакете? Ответ: Нелепая ситуация, не так ли? Существует три возможных решения:
Последние два варианта продемонстрированы в скрипте name_confusion.sql на Листинге 1. Листинг 1: скрипт name_confusion.sql -- Текст сохранён в name_confusion.sql -- Должен выполняться в схеме SCOTT SET SERVEROUTPUT ON CREATE OR REPLACE PROCEDURE this_one (n in number) IS BEGIN DBMS_OUTPUT.put_line ( 'schema-level' ); END this_one; / CREATE OR REPLACE PACKAGE same_name IS PROCEDURE this_one (n in number); PROCEDURE that_one; END same_name; / CREATE OR REPLACE PACKAGE BODY same_name IS PROCEDURE this_one (n in number) IS BEGIN DBMS_OUTPUT.put_line ( 'package-level' ); END this_one; PROCEDURE that_one IS BEGIN this_one (10); scott.this_one (10); EXECUTE IMMEDIATE 'BEGIN same_name.this_one (:val); END;' USING 10; EXECUTE IMMEDIATE 'BEGIN this_one (:val); END;' USING 10; END that_one; END same_name; / BEGIN same_name.that_one; END; / А теперь выполним скрипт Листинга 1 в SQL*Plus и посмотрим на следующие результаты: SQL> @name_confusion Procedure created. Package created. Package body created. package-level schema-level package-level schema-level В этом примере обе программы this_one (уровня схемы и пакетная) имеют одинаковый список параметров, поэтому код компилируется независимо от того, какая программа будет вызываться. Однако предположим, что списки параметров различаются, как показано на Листинге 2. В нём процедура уровня схемы имеет единственный параметр типа Boolean. В этом случае, как можно увидеть в результатах Листинга 2, пакет даже не будет компилироваться до тех пор, пока не будет указано название программы вместе со схемой. Листинг 2: name_confusion2.sql -- Текст сохранён в name_confusion2.sql
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE this_one (b IN BOOLEAN)
IS
BEGIN
DBMS_OUTPUT.put_line ( 'schema-level' );
END this_one;
/
CREATE OR REPLACE PACKAGE same_name
IS
PROCEDURE this_one (n in number);
PROCEDURE that_one;
END same_name;
/
CREATE OR REPLACE PACKAGE BODY same_name
IS
PROCEDURE this_one (n in number)
IS
BEGIN
DBMS_OUTPUT.put_line ( 'package-level' );
END this_one;
PROCEDURE that_one
IS
BEGIN
this_one ('155');
this_one (TRUE);
END that_one;
END same_name;
/
SQL> @name_confusion2
Procedure created.
Package created.
Warning: Package Body created with compilation errors.
SQL> show errors
Errors for PACKAGE BODY SAME_NAME:
LINE/COL ERROR
---------- ------------------------------------------------------------------
13/7 PL/SQL: Statement ignored
13/7 PLS-00306: wrong number or types of arguments in call to 'THIS_ONE'
Я закончу этим примером, чтобы подвести к сути, заключающейся в том, что для двух процедур this_one нет никакой перегрузки. Они определены в различных областях, поэтому просто наличие различающихся списков параметров недостаточно, чтобы сказать PL/SQL-компилятору, какая программа должна использоваться. Какой тип коллекций лучше использовать? Вопрос: Мне необходимо передать коллекцию из одной PL/SQL-программы в другую. Какой тип коллекции следует использовать и какая между ними разница? Ответ: Oracle PL/SQL предоставляет три типа коллекций (массивоподобных структур - arraylike structures): ассоциативный массив (associative array), вложенная таблица (nested table) или массив переменной длины (VARRAY). Какой тип коллекции подходит вашему приложению? В некоторых случаях выбор очевиден. В других же может быть несколько применимых вариантов. Я предлагаю небольшой экскурс и таблицу, которая описывает основные различия между ассоциативным массивом, вложенной таблицей и VARRAY (массивом переменной длины). Как разработчик на PL/SQL, я в первую очередь интуитивно пробую применить ассоциативные массивы. Имеется несколько причин для этого предпочтения: раньше (Oracle Database 7.3) ассоциативные массивы — названные позднее PL/SQL-таблицами—были единственным типом коллекций в PL/SQL. Поэтому они и использовались. Они также включены во многие участки кода: их не надо инициализировать или расширять. В Oracle9i Database Release 2 и более поздних версиях ассоциативные массивы можно индексировать не только по числам, но и по строкам. Однако для сохранения коллекции в таблице базы данных ассоциативные массивы использоваться не могут. Тогда остается выбори вложенная таблица или массив переменной длины? Следующее рекомендации помогут вам выбрать ассоциативный массив, вложенную таблицу или VARRAY:
Информация в Таблице 1 поможет сделать выбор. Таблица 1: Ассоциативный массив, вложенная таблица или VARRAY-массив переменной длины
Более подробное объяснение этих пунктов, и о коллекциях в общем, можно найти в главе 13 of Oracle PL/SQL Programming, 4th Edition ( www.oreilly.com/catalog/oraclep4) и в документации по Oracle (Oracle Database PL/SQL User's Guide and Reference), download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm. Перекомпиляция инвалидных программ Вопрос: Как перекомпилировать инвалидные программы в моей схеме? Ответ: Oracle предлагает две пакетные утилиты для перекомпиляции инвалидных программ. Кроме того, Соломон Якобсон (Solomon Yakobson), известный технолог Oracle, написал свою собственную утилиту для перекомпиляции, которую сделал доступной всем. И, наконец, многие PL/SQL-редакторы предлагают свои собственные возможности для этого. Сначала я дам краткий обзор двум вариантам Oracle и утилите Якобсона, а затем покажу использование каждого их них в скрипте сравнения производительности. UTL_RECOMP. Новая в Oracle Database 10g Release 2 и очень полезная утилита, разработанная специально для перекомпиляции, UTL_RECOMP предлагает "пакетный интерфейс для перекомпиляции инвалидных PL/SQL-модулей, Java-классов, индексных типов и операторов в базе данных последовательно или параллельно " (из описания в скрипте utlrcmp.sql, который создаёт пакет UTL_RECOMP и несколько связанных с ним таблиц и представлений). Только SYSDBA имеет право выполнять подпрограммы UTL_RECOMP (RECOMP_SERIAL и RECOMP_PARALLEL), если только DBA не предоставит право EXECUTE на этот пакет вашей схеме или роли PUBLIC. Более подробную информацию об этом пакете смотрите в PL/SQL Packages and Types Reference, download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_recomp.htm#ARPLS380. DBMS_UTILITY.COMPILE_SCHEMA. Эта утилита применяется, начиная с Oracle Database Version 7. Её можно выполнить в любой схеме, и она будет перекомпилировать все программы (этот вариант возможен до Oracle Database 10g), или только все инвалидные программы (это возможно с помощью нового параметра в версии пакета COMPILE_SCHEMA для Oracle Database 10g). Более подробную информацию об этом пакете смотрите в PL/SQL Packages and Types Reference, download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9332 Скрипт перекомпиляции Соломона Якобсона. Эту удобную утилиту (доступную по ссылке Downloads на странице с the Best Practice PL/SQL — oracle.com/technology/pub/columns/plsql) можно выполнить из любой схемы, и она будет перекомпилировать все инвалидные программы в порядке зависимостей, поэтому после её завершения все программы без ошибок компиляции будут перекомпилированы и иметь статус VALID. Эта функция возвращает следующие значения: 0 – Успешное завершение. Все требуемые объекты перекомпилированы и имеют статус VALID. Поэтому, как обычно в мире Oracle и PL/SQL, у вас есть альтернативы. Какую утилиту следует использовать? Для ответа на этот вопрос, я помещу их вместе в скрипт — recompile_comparison.sql, показанный на Листинге 3, — чтобы проверить производительность каждого из этих трех методов. Когда вы будете анализировать результаты, примите во внимание, что абсолютные величины не существенны; ключевыми являются различия между значениями. Листинг 3: recompile_comparison.sql -- Текст сохранён в recompile_comparison.sql
SET SERVEROUTPUT ON FORMAT WRAPPED
SPOOL recompile_comparison.log
DECLARE
-- Какую программу вы хотите перекомпилировать,
-- чтобы избежать изменения статуса других объектов?
g_program VARCHAR2 ( 100 ) := 'package qu_all_objects';
--
g_start_time PLS_INTEGER;
--
l_dummy PLS_INTEGER;
PROCEDURE show_invalid ( context_in IN VARCHAR2 )
IS
l_invalid PLS_INTEGER;
BEGIN
SELECT COUNT ( * )
INTO l_invalid
FROM user_objects
WHERE status = 'INVALID';
DBMS_OUTPUT.put_line ( 'Invalid object count '
|| context_in
|| ': '
|| l_invalid
);
END show_invalid;
PROCEDURE before_recompile
IS
BEGIN
EXECUTE IMMEDIATE 'alter ' || g_program || ' compile reuse settings';
show_invalid ( 'before' );
-- Изменяем get_cpu_time на get_time для версий, более ранних, чем 10g
g_start_time := DBMS_UTILITY.get_cpu_time;
END before_recompile;
PROCEDURE after_recompile ( approach_in IN VARCHAR2 )
IS
BEGIN
-- Изменяем get_cpu_time на get_time для версий, более ранних, чем 10g
DBMS_OUTPUT.put_line ( 'Time for "'
|| approach_in
|| '" = '
|| TO_CHAR ( DBMS_UTILITY.get_cpu_time
- g_start_time
)
);
show_invalid ( 'after' );
END after_recompile;
BEGIN
before_recompile;
l_dummy := recompile ( o_owner => USER, display => FALSE );
after_recompile ( 'Yakobson utility' );
--
before_recompile;
DBMS_UTILITY.compile_schema ( USER
-- Закомментируем следующую строку для версий Oracle, более ранних, чем 10g
, compile_all => FALSE, reuse_settings => TRUE
);
after_recompile ( 'dbms_utility.compile_schema' );
--
before_recompile;
SYS.UTL_RECOMP.recomp_serial ( USER );
after_recompile ( 'utl_recomp.serial' );
END;
/
SPOOL OFF
После выполнения скрипт recompile_comparison в Oracle Database 10g Release 2 и получил следующие результаты: SQL> @recompile_comparison Time for "Yakobson utility" = 6003 Time for "dbms_utility.compile_schema" = 5900 Time for "utl_recomp.recomp_serial" = 5936 Я выполнил модифицированную версию recompile_comparison в Oracle9i Database Release 2 (применил использование DBMS_UTILITY.GET_TIME вместо GET_CPU_TIME и не вызывал пакет UTL_RECOMP, так как он не существует в Oracle9i Database) и получил следующие результаты: Time for "Yakobson utility" = 7244 Time for "dbms_utility.compile_schema" = 22309 Вызов DBMS_UTILITY.COMPILE_SCHEMA в Oracle9i Database занимает намного больше времени, потому что эта утилита перекомпилирует все программы схемы, а не только инвалидные. Версия этой программы из Oracle Database 10g позволяет компилировать только инвалидные объекты. По этим результатам сделаем следующие выводы: 1. В Oracle Database 10g я буду использовать DBMS_UTILITY.COMPILE_SCHEMA, которая выполняется эффективнее и лучше других вариантов (когда компилируются только инвалидные объекты) и может запускаться в любой схеме (не требуется специальных привилегий). Получение имен столбцов в динамическом Select Вопрос: Я храню предложения SELECT в реляционной таблице и затем извлекаю их динамически внутри PL/SQL, как это описано пользователем. Мне необходимо получить имена столбцов в запросах, чтобы использовать их для отображения в заголовках. Как получить такую информацию? Ответ: Для решения этой проблемы Oracle предоставляет очень полезную утилиту в пакете DBMS_SQL — процедуру DESCRIBE_COLUMNS. Процедуре DBMS_SQL.DESCRIBE_COLUMNS передается уже разобранный курсор, и она возвращает коллекцию записей, каждой элемент которой дает информацию о столбце или выражении в списке SELECT для запроса. DBMS_SQL.DESCRIBE_COLUMNS позволяет описать столбцы динамического курсора, возвращая информацию о каждом столбце в записи ассоциативного массива. Такая возможность позволяет написать очень общий код обработки курсора; эта процедура станет очень удобной при написании динамического SQL с использованием Метода 4, когда неизвестно заранее, сколько столбцов будет выбрано. Она также позволяет легко получить имена каждого из этих столбцов. Чтобы использовать эту процедуру, необходимо объявить PL/SQL-коллекцию, основанную на типе DBMS_SQL.DESC_TAB (или DESC_TAB2, если запрос может возвращать имена столбцов, которые длиннее 30 символов). Затем можно просканировать таблицу и извлечь необходимую информацию о курсоре. Анонимный блок на Листинге 4 показывает основные шаги, которые выполняются при работе с процедурой DBMS_SQL.DESCRIBE_COLUMNS. (Для упрощения кода предположим, что типы данных для всех столбцов VARCHAR2 или неявно конвертируются в VARCHAR2.) Листинг 4: Анонимный блок, использующий DBMS_SQL.DESCRIBE_COLUMNS DECLARE -- Этот запрос может быть считан из таблицы... l_query VARCHAR2 ( 4000 ) := 'SELECT last_name, salary FROM employees'; l_cursor PLS_INTEGER := DBMS_SQL.open_cursor; l_columns DBMS_SQL.desc_tab2; l_numcols PLS_INTEGER; l_value VARCHAR2 ( 4000 ); l_feedback PLS_INTEGER; BEGIN -- Синтаксический разбор запроса. DBMS_SQL.parse ( l_cursor, l_query, DBMS_SQL.native ); -- Извлечение информации о столбце DBMS_SQL.describe_columns2 ( l_cursor, l_numcols, l_columns ); -- Описание каждого из имен столбцов (и отображение имени столбца) FOR colind IN 1 .. l_numcols LOOP -- Спецификация максимального размера извлекаемой строки. DBMS_SQL.define_column ( l_cursor, colind, l_value, 4000 ); DBMS_OUTPUT.put_line ( l_columns ( colind ).col_name ); END LOOP; --- Выполнение запроса.... l_feedback := DBMS_SQL.EXECUTE ( l_cursor ); LOOP EXIT WHEN DBMS_SQL.fetch_rows ( l_cursor ) = 0; FOR colind IN 1 .. l_numcols LOOP -- Извлечение каждого значения и вывод его на экран. DBMS_SQL.COLUMN_VALUE ( l_cursor, colind, l_value ); DBMS_OUTPUT.put_line ( l_columns ( colind ).col_name || ' = ' || l_value ); END LOOP; END LOOP; -- Закрытие курсора DBMS_SQL.close_cursor ( l_cursor ); END; / Steven Feuerstein (steven@stevenfeuerstein.com) is considered one of the world's leading experts on the Oracle PL/SQL language, having written 10 books on the subject, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media). Feuerstein serves as a senior technology advisor for Quest Software, and his projects include Qnxo, the world's first active mentoring product (www.qnxo.com). | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
