Joomla 3.1 Templates by FatCow Coupon

Динамический SQL в ORACLE (часть 3 - Фишки, секреты и типичные ошибки)

Категория: Блог Опубликовано 24.05.2017
В этом разделе показано, как в использовать динамический SQL и избежать некоторых распространенных ошибок.
Повышение производительности

В приведенном ниже примере, Oracle открывает различные курсоры для каждого отдельного значения emp_id . Это может привести к конфликту ресурсов и резкой потере производительности.

  CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM emp WHERE empno = ' || TO_CHAR(emp_id);
END;


Чтобы избежать данной ошибки можно использовать связанные переменные, как показано ниже. Это позволяет повторно использовать Oracle один и тот же курсор для различных значений emp_id .
CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM emp WHERE empno = :num' USING emp_id;
END;




Работа над произвольными объектами схемы

Предположим, вам нужна процедура, которая принимает имя таблицы базы данных, затем удаляет эту таблицу из схемы. Используя динамический SQL, вы можете написать следующую процедуру:
 CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name;
END;


Тем не менее, во время выполнения этой процедуры возникает ошибка недопустимое имя таблицы. Это потому, что вы не можете использовать привязку аргументов для передачи имен объектов схемы для динамического оператора SQL.

Вместо этого, необходимо сделать параметры в динамическом SQL, а затем задать имена объектов схемы этим параметрам.
В последнем примере, вы должны изменить EXECUTE IMMEDIATE . Вы задаете параметр table_name в динамической строке, следующим образом:
  CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
  НАЧАТЬ
  EXECUTE IMMEDIATE "DROP TABLE '| | table_name;
  END;


Теперь вы можете передать имя любой таблицы базы данных в динамический оператор.

Использование Атрибутов курсоров
Каждый явный курсор имеет четыре атрибута: %FOUND , %ISOPEN , %NOTFOUND и %ROWCOUNT . Они возвращают полезную информацию о выполнении статических и динамических SQL команд.
Для обработки SQL выражений манипуляции данными, Oracle открывает неявный курсор с именем SQL . Его атрибуты - информация о последних выполненных INSERT , UPDATE, DELETE или SELECT . Например, в следующей функции %ROWCOUNT возвращает количество удаленных строк из таблицы базы данных:

CREATE FUNCTION rows_deleted (
   table_name IN VARCHAR2, 
   condition IN VARCHAR2) RETURN INTEGER AS
BEGIN
   EXECUTE IMMEDIATE 
      'DELETE FROM ' || table_name || ' WHERE ' || condition;
   RETURN SQL%ROWCOUNT;  -- return number of rows deleted
END;


Тип Null
Предположим, что вы хотите передать NULL в динамический оператор SQL. Например, вы могли бы написать следующий 

EXECUTE IMMEDIATE :
  EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING NULL;


Однако эта команда вызовет ошибку, поскольку использование NULL не допускается в USING таким образом. Чтобы обойти это ограничение, просто замените ключевое слово NULL с неинициализированную переменную:

DECLARE
   a_null CHAR(1); -- set to NULL automatically at run time
BEGIN
   EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null;
END;


Выполнение удаленных операций через DB LINK
Как показывает следующий пример, PL / SQL подпрограмма может выполнять динамический SQL, который работает с объектами на удаленной базе данных:

PROCEDURE delete_dept (db_link VARCHAR2, dept_id INTEGER) IS
BEGIN
   EXECUTE IMMEDIATE 'DELETE FROM dept@' || db_link ||
      ' WHERE deptno = :num' USING dept_id;
END;


Так же вызовы удаленных процедур (RPC) могут содержать динамические операторы SQL. Например, предположим, следующая функция, которая возвращает количество строк в таблице, находятся в базе данных Чикаго:
  CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN INTEGER AS
   rows INTEGER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;
   RETURN rows;
END;


Код, который можно вызывает функцию удаленно:

DECLARE
   emp_count INTEGER;
BEGIN
   emp_count := row_count@chicago('emp');


Как избежать deadlock

В некоторых ситуациях, выполнения SQL мы создаем deadlock. Например, приведенная ниже процедура вызывает deadlock из-за того , что пытается удалить сама себя. 

Чтобы избежать таких ситуаций, никогда не пытайтесь сделать ALTER или DROP подпрограммы или пакета, в то время. как все еще используете его.
CREATE PROCEDURE calc_bonus (emp_id NUMBER) AS
BEGIN
   ...
   EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus';
 
Просмотров: 131