Информационное обеспечение
жүктеу/скачать 256,49 Kb.
|
СУБДOracle
| Использование BULK COLLECT. Использование оператора BULK COLLECT может значительно ускорить выборку данных из таблицы в коллек- цию, особенно при большом количестве строк. Включив его в явный или неяв- ный курсор, программист указывает ядру SQL на необходимость перед переда- чей управления PL/SQL выполнить пакетное связывание данных из множества строк с заданными в запросе коллекциями, что уменьшает переключения между ядром SQL и PL/SQL. Так, например, если коллекция имеет 100 элементов, ис- пользование BULK COLLECT позволит выполнить операцию, функционально эквивалентную выполнению 100 операторов SELECT, но при этом исключит 99 лишних переключений. Синтаксис этого предложения следующий:
BULK COLLECT INTO имя_коллекции [, имя_коллекции] … Пример использования: DECLARE TYPE empl_inf_type is TABLE of EMPLOYEES%ROWTYPE; cEmp empl_inf_type; BEGIN SELECT * BULK COLLECT into cEmp FROM EMPLOYEES; DBMS_OUTPUT.PUT_LINE ('Список сотрудников : '); FOR i IN cEmp.FIRST .. cEmp.LAST LOOP DBMS_OUTPUT.PUT_LINE (cEmp(i).Employee_id || '.' || RPAD(cEmp(i).first_name,20,' ') || ':' || cEmp(i).Salary); END LOOP; END; / Использование FORALL. Предложение FORALL предназначено для вы- полнения операторов обновления базы данных: INSERT, UPDATE, DELETE. Синтаксис оператора: FORALL индекс_записи IN начальное_значение ... конечное значение Sql_инструкция Рассмотрим пример использования оператора FORALL. Сначала созда- дим таблицу на основе справочника ALL_TAB_COLUMNS: CREATE TABLE tmp_forall as SELECT OWNER, table_name, column_name, data_type FROM ALL_TAB_COLUMNS WHERE rownum=0; Количество записей в ALL_TAB_COLUMNS довольно большое. Сравним производительность СУБД при использовании FORALL и без него: DECLARE
ctable_name LstString_type; ccolumn_name LstString_type; cdata_type LstString_type; t1 Number(15); t2 Number(15); t3 Number(15); BEGIN SELECT OWNER, table_name, column_name, data_type BULK COLLECT INTO cOwner, ctable_name, ccolumn_name, cdata_type FROM ALL_TAB_COLUMNS; t1 := dbms_utility.get_time; DBMS_OUTPUT.PUT_LINE ('количество записей ='||cowner.Count); FOR i IN cowner.FIRST .. cowner.LAST LOOP INSERT INTO tmp_forall VALUES (cOWNER(i), ctable_name(i), ccolumn_name(i), cdata_type(i)); END LOOP; t2 := dbms_utility.get_time; FORALL i IN cowner.FIRST .. cowner.LAST INSERT INTO tmp_forall VALUES (cOWNER(i), ctable_name(i), ccolumn_name(i), cdata_type(i)); t3 := dbms_utility.get_time; DBMS_OUTPUT.PUT_LINE ('time FOR = '||to_char(t2-t1)); DBMS_OUTPUT.PUT_LINE ('time FORALL = '||to_char(t3-t2)); END; / Получили результат: количество записей =9382 time FOR =174 time FORALL =5 Понятие динамического SQL. Все программы, рассматриваемые ранее, являются статическими. Это означает, что структура SQL-операторов известна уже во время компиляции программы (механизм раннего связывания). Динами- ческий SQL позволяет выполнять SQL-операторы, создавая их динамически, во время выполнения программы (механизм позднего связывания), а потом произ- водить их грамматический разбор и обработку. Возможно, программа генери- рует запросы по ходу работы на основе введенных пользователем условий; возможно, это специализированная программа загрузки данных. Утилита SQL*Plus это пример рода программы, схожего с любым другим средством выполнения произвольных запросов или генерации отчетов. Утилита SQL*Plus позволяет выполнить любой SQL-оператор и показать результаты его выполне- ния, хотя при ее компиляции операторы, которые пишет пользователь, опреде- ленно не были известны. В обычной практике разработки приложений на PL/SQL встречаются си- туации, когда необходимо выполнить в модуле PL/SQL запрос, текст которого будет известен только к моменту его выполнения, не определен на стадии раз- работки программы и будет меняться во время выполнения. Например, проце- дура, реализующая выборку данных из таблицы, имя которой задается в каче- стве параметра процедуры, или процедура, запускающая на выполнение другую процедуру, имя которой храниться в таблице базы данных. Динамический SQL позволяет сконструировать и хранить код в прило- жении в виде символьной строки, выполнить команды с различными столбца- ми или условиями, содержащие и не содержащие связанные переменные, написать и выполнить в коде PL/SQL команды DDL, DCL и команды управле- ния сеансом. Для обработки таких ситуаций существует несколько механизмов: пакет DBMS_SQL; внутренний динамический SQL (пакет NDS), основой которого является оператор EXECUTE IMMEDIATE. И пакет DBMS_SQL, и оператор EXECUTE IMMEDIATE позволяют вы- полнять не только запросы SELECT, но и анонимные блоки PL/SQL, процеду- ры, а также операторы DDL, такие как создание, удаление таблиц БД, триггеров и других объектов, что невозможно сделать напрямую, написав команду в бло- ке PL/SQL. Пакет DBMS_SQL. Алгоритм выполнения операторов с помощью DBMS_SQL следующий: Преобразование SQL-оператора в строку символов. Грамматический разбор строки символов с помощью DBMS_SQL.PARSE. Привязка всех входных переменных с помощью DBMS_SQL.BIND_VARIABLE. Если выполняемый оператор – это оператор DML (UPDATE, DELETE, INSERT), выполнение его с помощью DBMS_SQL.EXECUTE с последующим считыванием выходных переменных привязки с помощью DBMS_SQL.VARIABLE_VALUE (если нужно). Если оператор является оператором выборки (select) – описание вы- ходных переменных с помощью DBMS_SQL.DEFINE_COLUMN. Выполнение запроса на выборку с помощью DBMS_SQL.EXECUTE и выборка результатов при помощи DBMS_SQL.FETCH_ROWS и DBMS_SQL.COLUMN_VALUE. Обработка операторов DML посредством DBMS_SQL. Для обработки операторов UPDATE, DELETE, INSERT средствами модуля DBMS_SQL необ- ходимо последовательно выполнить следующие действия: Открыть курсор. Осуществляется посредством вызова процедуры OPEN_CURSOR, описание которой в модуле выглядит следующим образом: OPEN_CURSOR return INTEGER; Параметры в данной процедуре отсутствуют. Каждый вызов возвращает целое число, представляющее собой идентифи- кационный номер курсора. Этот номер используется в последующих вызовах курсора. В границах одного курсора можно по очереди обрабатывать несколько SQL-операторов или выполнять один и тот же оператор несколько раз. Выполнить грамматический разбор оператора. При выполнении грам- матического разбора оператор направляется на сервер БД. Сервер проверяет его синтаксис и семантику и возвращает ошибку (устанавливая исключительную ситуацию), если нарушены требования грамматики. Кроме того, во время раз- бора определяется план выполнения оператора. Осуществляется грамматиче- ский разбор посредством вызова процедуры DBMS_SQL.PARSE, описание ко- торой в модуле имеет следующий вид: PROCEDURE PARSE (c in INTEGER, statement in VARCHAR2, language_flag in INTEGER); где с – идентификационный номер курсора, который предварительно должен быть открыт посредством OPEN_CURSOR; statement – оператор, грамматический разбор которого выполняется; language_flag – указывает, как трактовать оператор, при этом значение NA- TIVE – это режим, установленный для той базы данных, с которой выполнено соединение. Выполнить привязку входных переменных. При выполнении этой опе- рации заполнители, указанные в операторе, связываются с фактическими пере- менными. Имена заполнителей обычно предваряют символом двоеточия. Про- цедура BIND_VARIABLE выполняет привязку и объявление имен заполните- лей. Размер и тип данных фактических переменных также устанавливается BIND_VARIABLE посредством набора переопределенных вызовов: PROCEDURE BIND_VARIABLE (c in INTEGER, name in VARCHAR2, value in number); PROCEDURE BIND_VARIABLE (c in INTEGER, name in VARCHAR2, value in VARCHAR2); PROCEDURE BIND_VARIABLE (c in INTEGER, namein VARCHAR2, value in VARCHAR2, out_value_size in INTEGER); где name – это имя заполнителя, с которым будет связана переменная; value – реальные данные, которые будут привязываться (тип и размер этой переменной также считываются); при необходимости данные, содержащиеся в этой переменной, будут преобразованы; out_value_size – параметр, задаваемый при привязке переменных VARCHAR2 и CHAR; если он указан, то это максимальный ожидаемый размер значения в бай- тах, если не указан, то используется размер указанный в параметре value. Выполнить оператор посредством функции EXECUTE. Описание ее в модуле выглядит следующим образом: FUNCTION EXECUTE (c in INTEGER) return INTEGER; где с – идентификатор предварительно открытого курсора. Функция EXECUTE возвращает число отработанных строк (в этом смыс- ле возвращаемое значение аналогично курсорному атрибуту %ROWCOUNT). Следует учесть, что возвращаемое значение не определено для операторов вы- борки, а также и то, что EXECUTE вызывается из выражений программ. Закрыть курсор. Закрытие курсора осуществляется посредством вызо- ва процедуры CLOSE_CURSOR, описание которой выглядит следующим обра- зом: PROCEDURE CLOSE_CURSOR (c in out INTEGER); Передаваемое процедуре значение должно быть достоверным идентифи- катором курсора. После вызова фактический параметр устанавливается в NULL, что свидетельствует о закрытии курсора. Приведем пример динамического использования операторов DML: CREATE OR REPLACE PROCEDURE update_address (p_lname IN staff.lname%TYPE, p_fname IN staff.fname%TYPE, p_newaddress IN staff.address%TYPE, p_rowsupdated OUT INTEGER) IS v_cursor_id INTEGER; v_updatestmt VARCHAR2(100); BEGIN v_cursor_id := DBMS_SQL.OPEN_CURSOR; v_updatestmt := 'UPDATE staff SET address=:addr WHERE fname =: fname AND lname= :lname'; DBMS_SQL.PARSE (v_cursor_id, v_updatestmt, DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE (v_cursor_id, ':addr', p_newaddress); DBMS_SQL.BIND_VARIABLE (v_cursor_id, ':fname', p_fname); DBMS_SQL.BIND_VARIABLE (v_cursor_id, ':lname', p_lname); p_rowsupdated := DBMS_SQL.EXECUTE (v_cursor_id); DBMS_SQL.CLOSE_CURSOR (v_cursor_id); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(v_cursor_id); RAISE; END update_address; / Обработка запросов на извлечение информации производится путем по- следовательного выполнения всех перечисленных далее действий: Открытие курсора (OPEN_CURSOR). Выполнение грамматического разбора (PARSE). Выполнение привязки всех входных переменных (BIND_VARIABLE). Описание элементов списка выбора (DEFINE_COLUMN). Исполнение запроса (EXECUTE). Считывание строк (FETCH). Запись результатов в переменные (COLUMN_VALUE). Закрытие курсора (CLOSE_CURSOR). В отличие от динамической обработки DML-операторов, обработка ин- струкций SELECT включает дополнительно описание элементов списка выбо- ра, считывание строк и запись результатов в переменные PL/SQL. Процесс определения элементов списка выбора напоминает привязку вход- ных переменных, за исключением того, что элементы списка выбора должны быть не привязаны, а только определены. В процедуре DEFINE_COLUMN указывают- ся типы и размер переменных, в которые считываются элементы списка выбора. Каждый элемент при этом преобразуется в тип соответствующей переменной. Описание элементов списка выбора производится посредством процедур DEFINE_COLUMN модуля DBMS_SQL: PROCEDURE DEFINE_COLUMN (c in INTEGER, position in INTEGER, column in number); PROCEDURE DEFINE_COLUMN (c in INTEGER, position in INTEGER, column in VARCHAR2, column_size in INTEGER); Для переменных VARCHAR2 нужно обязательно указывать параметр col- umn_size, поскольку система поддержки PL/SQL должна знать максимальный размер этих переменных во время выполнения программы, так как в отличие от типов number, date данные этих типов не имеют фиксированной длины, заранее известной компилятору. Описание параметров процедуры приведено в табл. 6. Таблица 6 Параметры процедуры DEFINE_COLUMN
Окончание табл. 6
После выполнения запроса строки набора необходимо считать в буфер посредством вызова функции FETCH_ROWS. Эта функция описана в модуле DBMS_SQL следующим образом: FUNCTION FETCH_ROWS (cin INTEGER) return INTEGER; FETCH_ROWS возвращает число считываемых строк. FETCH_ROWS и COLUMN_VALUE вызываются в цикле несколько раз до тех пор, пока FETCH_ROWS не возвратит 0. После успешно выполненного считывания строк производится запись ре- зультатов в переменные PL/SQL посредством процедуры COLUMN_VALUE. Если в выборке не были возвращены строки (что указывается возвратом 0), COLUMN_VALUE устанавливает для выходной переменной NULL-значение. Ниже приведено описание этой процедуры в модуле DBMS_SQL, а описание ее параметров – в табл. 7: PROCEDURE COLUMN_VALUE (c in INTEGER, position in INTEGER, value out number); PROCEDURE COLUMN_VALUE (c in INTEGER, position in INTEGER, value out number, column_error out number, actual_length out number); PROCEDURE COLUMN_VALUE (c in INTEGER, position in INTEGER, value out VARCHAR2); PROCEDURE COLUMN_VALUE (c in INTEGER, positionin INTEGER, value out VARCHAR2, column_error out number, actual_length out number);
Процедура создания таблицы с помощью пакета DBMS_SQL. CREATE OR REPLACE PROCEDURE create_temp_dept (tname IN OUT VARCHAR2) AS cur INTEGER; --хранит идентификатор (ID) курсора ret INTEGER; -- хранит возвращаемое по вызову значение str VARCHAR2(250); -- хранит команды BEGIN -- генерация временной таблицы по имени с использованием заранее -- заданного имени и возврат значения функции -- DBMS_SESSION.UNIQUE_SESSION_ID tname:= 'dept ' || dbms_session.unique_session_id; -- генерация команды CREATE TABLE по заранее заданному тексту -- и переменной tname str := 'CREATE TABLE'||' '||tname || ' (deptno INTEGER,' || 'dname VARCHAR2(14),' || 'loc VARCHAR2(13))'; -- Динамически формируемое DDL-предложение cur:= DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cur, str, dbms_sql.v7); ret:= DBMS_SQL.EXECUTE(cur); DBMS_SQL.CLOSE_CURSOR(cur); END; / Выполним анонимный блок: DECLARE f varchar2(20); BEGIN f:='name_tab'; create_temp_dept (f); END; / В результате появится таблица, например, с именем DEPT10145ACDA0002, где следующие за DEPT символы представляют собой уникальный номер сессии пользователя. Чтобы создать таблицу со своим име- нем ('name_tab'), нужно закомментировать строку tname := 'dept1' || dbms_session.unique_session_id и снова выполнить анонимный блок. Создадим процедуру print_any_table, которая выводит в виде отчета со- держимое таблицы, имя которой передается в качестве параметра. CREATE OR REPLACE PROCEDURE print_any_table ( vowner IN VARCHAR2, vtable_name IN VARCHAR2) IS sqltext VARCHAR2 (2000) := 'select '; x CHAR (1):= ' '; table_cursor NUMBER; TYPE col_inf_type IS RECORD (name VARCHAR2 (30), data_type VARCHAR2 (30),value VARCHAR2 (1000)); TYPE list_col_type IS TABLE OF col_inf_type; s VARCHAR2 (250); list_col list_col_type := list_col_type (); cnt INTEGER; BEGIN DBMS_OUTPUT.ENABLE (100000); FOR c IN (SELECT column_name, data_type FROM all_tab_columns WHERE owner = UPPER (vowner) AND table_name = UPPER (vtable_name)) LOOP sqltext:= sqltext || x || 'to_char(' || c.column_name || ') AS ' || c.column_name; x:= ','; list_col.EXTEND; list_col (list_col.LAST).NAME := c.column_name; list_col (list_col.LAST).NAME := c.data_type; END LOOP; sqltext := sqltext || ' from ' || vowner || '.' || vtable_name; table_cursor := DBMS_SQL.open_cursor; DBMS_SQL.parse (table_cursor, sqltext, DBMS_SQL.native); FOR i IN list_col.FIRST ..list_col.LAST LOOP DBMS_SQL.DEFINE_COLUMN (table_cursor, i, list_col (i).VALUE, 1000); END LOOP; cnt := DBMS_SQL.EXECUTE (table_cursor); DBMS_OUTPUT.put_line ('Таблица' || vtable_name || ' содержит ' || cnt || ' строк.'); cnt := 0; LOOP IF DBMS_SQL.fetch_rows (table_cursor) = 0THEN EXIT; END IF; cnt := cnt + 1; s := cnt || '.'; FOR i IN list_col.FIRST ..list_col.LAST LOOP DBMS_SQL.column_value (table_cursor, i, list_col (i).VALUE); s := s || ' ' || list_col (i).VALUE; END LOOP; DBMS_OUTPUT.put_line (SUBSTR (s, 1, 255)); END LOOP; DBMS_SQL.close_cursor (table_cursor); END; / Встроенный SQL (Native dynamic SQL, NDS). Встроенный динамический SQL впервые появился в Oracle 8i и является составной частью самого языка. Вследствие этого он значительно проще в применении и быстрее, чем модуль DBMS_SQL. Он позволяет декларативно выполнять динамический SQL в среде PL/SQL при условии, что структура команды неизвестна только до момента выполнения. Большинство действий можно выполнить с помощью одного оператора – EXECUTE IMMEDIATE, а остальные – с помощью операторов OPEN FOR, FETCH, CLOSE. При работе со встроенным динамическим SQL для выполнения предложений используется следующая конструкция: EXECUTE IMMEDIATE оператор_SQL [ INTO{ переменная1 [, переменная2 ] ... | запись } ] [ USING [ IN | OUT | IN OUT ] связанный_аргумент1 [, [ IN | OUT | IN OUT ] связанный_аргумент2 ] ... ]; [{RETURNING | RETURN} INTO результат1, . . . ,результатN] где INTO – определяет приемник результата; USING – определяет типы и порядок аргументов, используемых для передачи значений в запрос и приема данных результата. Замена аргументов на их значения происходит позиционно; жүктеу/скачать 256,49 Kb. Достарыңызбен бөлісу:
| |||||||||||||||||||||||||||||||||||||||||||||