В этом учебном материале вы узнаете, как использовать динамический SQL в Oracle/PLSQL с синтаксисом и примерами.
Описание
- Динамический SQL делает ваши программы более гибкими, создавая и обрабатывая SQL предложения во время выполнения программ.
- С динамическим SQL вы можете напрямую выполнять большинство типов операторов SQL, включая определение данных и операторы управления данными.
- Вы можете строить запросы, в которых вы заранее не знаете имен таблиц, предложений WHERE и другой информации.
Оператор EXECUTE IMMEDIATE
Oracle/PLSQL оператор EXECUTE IMMEDIATE подготавливает (анализирует) и немедленно выполняет динамический SQL-запрос или анонимный PL/SQL блок.
Основным аргументом EXECUTE IMMEDIATE является строка, содержащая SQL-запрос для выполнения. Вы можете создать строку, используя конкатенацию, или использовать предопределенную строку.
Динамическая строка может содержать любой оператор SQL (без последней точки с запятой), за исключением многострочных запросов или любой PL/SQL блок (с последней точкой с запятой).
Строка dynamic_string также может содержать заполнители, произвольные имена, которым предшествует двоеточие, для аргументов связывания bind_argument. В этом случае вы указываете, какие переменные PL/SQL соответствуют заполнителям, с помощью операторов INTO, USING и RETURNING INTO. Во время выполнения аргументы связывания заменяют соответствующие заполнители в динамической строке. Каждый заполнитель должен быть связан с аргументом связывания в предложении USING и/или предложении RETURNING INTO.
Синтаксис
Синтаксис Oracle/PLSQL оператора EXECUTE IMMEDIATE для передачи значения в переменную или строку:
[ INTO {[define_variable[, define_variable] ... | record_name}]
[USING [IN | OUT | IN OUT] bind_argument ]
returning_clause;
или синтаксис Oracle/PLSQL оператора EXECUTE IMMEDIATE для передачи значения в коллекцию
[[ BULK COLLECT] INTO {host_array_name | collection_name}]
[USING [IN | OUT | IN OUT] bind_argument]
returning_clause;
Параметры или аргументы
- dynamic_string
- Строковый литерал, переменная или выражение, представляющее один оператор SQL или блок PL/SQL. Он должен иметь тип CHAR или VARCHAR2, а не NCHAR или NVARCHAR2.
- BULK COLLECT
- Сохраняет значения результатов в одной или нескольких коллекциях для более быстрых запросов, чем циклы с операторами FETCH.
- INTO
- Используется только для однострочных запросов, в этом разделе указываются переменные или записи, в которые извлекаются значения столбцов. Для каждого значения, полученного запросом, в предложении INTO должна быть соответствующая тип-совместимая переменная или поле.
- define_variable
- Переменная, в которой сохраняется значение выбранного столбца.
- record_name
- Пользовательская запись или запись %ROWTYPE, в которой сохраняется выбранная строка.
- bind_argument
- Выражение, значение которого передается в динамический оператор SQL, или переменная, в которой сохраняется значение, возвращаемое динамическим оператором SQL.
- collection_name
- Объявленная коллекция, в которую извлекаются значения select_item из dynamic_string. Для каждого select_item должна быть соответствующая, совместимая с типом коллекция в списке.
- host_array_name
- Массив (объявленный в хост-среде PL/SQL и переданный PL/SQL как переменная связывания), в который извлекаются значения select_item. Для каждого select_item должен быть соответствующий, совместимый с типом массив в списке. Массивы хоста должны начинаться с двоеточия.
- USING
- По умолчанию - IN. Определяет список входных и/или выходных аргументов привязки.
- returning_clause
- Возвращает значения из вставленных строк, устраняя необходимость SELECT строки после. Вы можете извлечь значения столбца в переменные или в коллекции. Вы не можете использовать предложение RETURNING для удаленной или параллельной вставки. Если инструкция не влияет ни на какие строки, значения переменных, указанных в предложении RETURNING, не определены.
Примеры:
- Некоторые примеры динамического SQL
- Пример процедуры динамического SQL, которая принимает имя таблицы и предложение WHERE
- Указание режимов параметров для переменных связывания в строках динамического SQL
- Построение динамического запроса с помощью динамического SQL
- Примеры динамического SQL для типов объектов и коллекций
- Использование Bulk (множественного) SQL в динамическом SQL
- Использование динамического SQL с Bulk SQL
- Примеры динамический SQL с предложением BULK COLLECT INTO
- Пример динамический SQL с предложением RETURNING BULK COLLECT INTO
- Пример динамический SQL внутри оператора FORALL
- Рекомендации по динамическому SQL
- Когда использовать или пропустить точку с запятой с помощью динамического SQL
- Повышение производительности динамического SQL с помощью переменных связывания
- Передача имен объектов схемы в качестве параметров
- Использование дублирующих заполнителей с динамическим SQL
- Использование атрибутов курсора с динамическим SQL
- Передача NULL в динамический SQL
- Использование связей базы данных с динамическим SQL
- Использование прав Invoker с динамическим SQL
- Как избежать тупиков с помощью динамического SQL
Некоторые примеры динамического SQL
Рассмотрим несколько примеров использования Oracle/PLSQL оператора EXECUTE IMMEDIATE, чтобы понять как использовать EXECUTE IMMEDIATE в Oracle/PLSQL.
Описание команд в комментариях (--).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
DECLARE sql_stmt VARCHAR2(200); plsql_block VARCHAR2(500); emp_id NUMBER(4) := 7566; salary NUMBER(7,2); dept_id NUMBER(2) := 50; dept_name VARCHAR2(14) := 'PERSONNEL'; location VARCHAR2(13) := 'DALLAS'; emp_rec emp%ROWTYPE; BEGIN --EXECUTE IMMEDIATE c SQL предложением EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; --присвоим sql_stmt строковое SQL предложение с заполнителями :1, :2, :3 sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; --запустим EXECUTE IMMEDIATE с sql_stmt используя аргументы связывания dept_id, dept_name, location EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; --присвоим sql_stmt SQL предложение с заполнителем :id sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; --запустим EXECUTE IMMEDIATE с sql_stmt используя аргумент связывания emp_id и сохраним результат в emp_rec EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; --присвоим plsql_block запуск анонимного блока с подпрограммой raise_salary пакета emp_pkg с заполнителями :id, :amt plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;'; --запустим EXECUTE IMMEDIATE с plsql_block используя аргументы связывания :id, :amt EXECUTE IMMEDIATE plsql_block USING 7788, 500; --присвоим sql_stmt SQL предложение с заполнителем :1, :2 sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2'; --запустим EXECUTE IMMEDIATE с sql_stmt используя аргументы связывания emp_id, salary EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; --EXECUTE IMMEDIATE c SQL предложение с заполнителем :num и аргументом связывания dept_id EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id; --EXECUTE IMMEDIATE c SQL предложением EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE'; END; |
Пример процедуры динамического SQL, которая принимает имя таблицы и предложение WHERE
В этом примере автономная процедура принимает имя таблицы базы данных и необязательное условие предложения WHERE. Если вы пропустите условие, процедура удалит все строки из таблицы. В противном случае процедура удаляет только те строки, которые соответствуют условию.
|
1 2 3 4 5 6 7 8 |
CREATE OR REPLACE PROCEDURE delete_rows ( table_name IN VARCHAR2, condition IN VARCHAR2 DEFAULT NULL) AS where_clause VARCHAR2(100) := ' WHERE ' || condition; BEGIN IF condition IS NULL THEN where_clause := NULL; END IF; EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || where_clause; END; |
Указание режимов параметров для переменных связывания в строках динамического SQL
С предложением USING режимом по умолчанию является IN, поэтому вам не нужно указывать режим параметров для аргументов связывания ввода.
С предложением RETURNING INTO режим имеет значение OUT, поэтому вы не можете указать режим параметров для выходных аргументов связывания.
Вы должны указать режим параметров в более сложных случаях, таких как этот, где вы вызываете процедуру из динамического блока PL/SQL:
Пример
|
1 2 3 4 5 6 7 8 |
CREATE PROCEDURE create_dept ( deptno IN OUT NUMBER, dname IN VARCHAR2, loc IN VARCHAR2) AS BEGIN SELECT deptno_seq.NEXTVAL INTO deptno FROM dual; INSERT INTO dept VALUES (deptno, dname, loc); END; |
Чтобы вызвать процедуру из динамического блока PL/SQL, необходимо указать режим IN OUT для аргумента связывания, связанного с формальным параметром deptno, следующим образом:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE plsql_block VARCHAR2(500); new_deptno NUMBER(2); new_dname VARCHAR2(14) := 'ADVERTISING'; new_loc VARCHAR2(13) := 'NEW YORK'; BEGIN plsql_block := 'BEGIN create_dept(:a, :b, :c); END;'; EXECUTE IMMEDIATE plsql_block USING IN OUT new_deptno, new_dname, new_loc; IF new_deptno > 90 THEN ... END; |
Построение динамического запроса с помощью динамического SQL
Для обработки динамического многострочного запроса вы используете три оператора: OPEN-FOR, FETCH и CLOSE.
Сначала вы открываете переменную курсора для многострочного запроса. Затем вы выбираете строки из набора результатов по одной за раз.
Когда все строки обработаны, вы закрываете (CLOSE) курсорную переменную.
В следующем примере показано, как вы можете извлечь строки из результирующего набора динамического многострочного запроса в запись:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; emp_rec emp%ROWTYPE; sql_stmt VARCHAR2(200); my_job VARCHAR2(15) := 'CLERK'; BEGIN sql_stmt := 'SELECT * FROM emp WHERE job = :j'; OPEN emp_cv FOR sql_stmt USING my_job; LOOP FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; -- запись процесса END LOOP; CLOSE emp_cv; END; |
Примеры динамического SQL для типов объектов и коллекций
Следующий пример иллюстрирует использование объектов и коллекций. Предположим, вы определили тип объекта Person и VARRAY тип Hobbies следующим образом:
|
1 2 |
CREATE TYPE Person AS OBJECT (name VARCHAR2(25), age NUMBER); CREATE TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25); |
Используя динамический SQL, вы можете создать пакет, который использует эти типы:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
--создать спецификацию пакета CREATE OR REPLACE PACKAGE teams AS PROCEDURE create_table (tab_name VARCHAR2); PROCEDURE insert_row (tab_name VARCHAR2, p Person, h Hobbies); PROCEDURE print_table (tab_name VARCHAR2); END; --создать тело пакета CREATE OR REPLACE PACKAGE BODY teams AS PROCEDURE create_table (tab_name VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name || ' (pers Person, hobbs Hobbies)'; END; PROCEDURE insert_row ( tab_name VARCHAR2, p Person, h Hobbies) IS BEGIN EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name || ' VALUES (:1, :2)' USING p, h; END; PROCEDURE print_table (tab_name VARCHAR2) IS TYPE RefCurTyp IS REF CURSOR; cv RefCurTyp; p Person; h Hobbies; BEGIN OPEN cv FOR 'SELECT pers, hobbs FROM ' || tab_name; LOOP FETCH cv INTO p, h; EXIT WHEN cv%NOTFOUND; -- print attributes of 'p' and elements of 'h' END LOOP; CLOSE cv; END; END; |
Из анонимного блока вы можете вызвать процедуры из пакета TEAMS:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE team_name VARCHAR2(15); BEGIN team_name := 'Notables'; teams.create_table(team_name); teams.insert_row(team_name, Person('John', 31), Hobbies('skiing', 'coin collecting', 'tennis')); teams.insert_row(team_name, Person('Mary', 28), Hobbies('golf', 'quilting', 'rock climbing')); teams.print_table(team_name); END; |
Использование Bulk (множественного) SQL в динамическом SQL
SQL Bulk связывает целые коллекции, а не только отдельные элементы. Этот метод повышает производительность за счет минимизации количества переключений контекста между механизмами PL/SQL и SQL. Вы можете использовать один оператор вместо цикла, который выдает оператор SQL на каждой итерации.
Используя следующие команды, предложения и атрибут курсора, ваши приложения могут создавать объемные операторы SQL, а затем выполнять их динамически во время выполнения:
- BULK FETCH предложение
- BULK EXECUTE IMMEDIATE предложение
- FORALL предложение
- COLLECT INTO выражение
- RETURNING INTO выражение
- %BULK_ROWCOUNT атрибут курсора
Использование динамического SQL с Bulk SQL
Массовое (множественное) связывание позволяет Oracle связать переменную в операторе SQL с коллекцией значений.
Тип коллекции может быть любым типом коллекции Oracle/PLSQL (index-by table, nested table или varray).
Элементы коллекции должны иметь тип данных SQL, такой как CHAR, DATE или NUMBER.
Три оператора поддерживают динамическое массовое связывание: EXECUTE IMMEDIATE, FETCH и FORALL.
EXECUTE IMMEDIATE
- Вы можете использовать предложение BULK COLLECT INTO с оператором EXECUTE IMMEDIATE, чтобы хранить значения из каждого столбца набора результатов запроса в отдельной коллекции.
- Вы можете использовать предложение RETURNING BULK COLLECT INTO с оператором EXECUTE IMMEDIATE, чтобы сохранить результаты оператора INSERT, UPDATE или DELETE в наборе коллекций.
FETCH
- Вы можете использовать предложение BULK COLLECT INTO с оператором FETCH для хранения значений из каждого столбца курсора в отдельной коллекции.
FORALL
- Вы можете совместить EXECUTE IMMEDIATE инструкцию с RETURNING BULK COLLECT INTO внутри инструкции FORALL. Вы можете хранить результаты всех операторов INSERT, UPDATE или DELETE в наборе коллекций.
- Вы можете передать индексированные элементы коллекции в инструкцию EXECUTE IMMEDIATE через предложение USING.
- Вы не можете объединить индексированные элементы непосредственно в строковый аргумент для EXECUTE IMMEDIATE; например, вы не можете создать коллекцию имен таблиц и написать оператор FORALL, где каждая итерация применяется к другой таблице.
Примеры динамический SQL с предложением BULK COLLECT INTO
Вы можете связать определенные переменные в динамическом запросе, используя предложение BULK COLLECT INTO. Как показано в следующем примере, вы можете использовать это предложение в массовом выражении FETCH или в массовом выражении EXECUTE IMMEDIATE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE TYPE EmpCurTyp IS REF CURSOR; TYPE NumList IS TABLE OF NUMBER; TYPE NameList IS TABLE OF VARCHAR2(15); emp_cv EmpCurTyp; empnos NumList; enames NameList; sals NumList; BEGIN OPEN emp_cv FOR 'SELECT empno, ename FROM emp'; FETCH emp_cv BULK COLLECT INTO empnos, enames; CLOSE emp_cv; EXECUTE IMMEDIATE 'SELECT sal FROM emp' BULK COLLECT INTO sals; END; |
Пример динамический SQL с предложением RETURNING BULK COLLECT INTO
Только операторы INSERT, UPDATE и DELETE могут иметь выходные переменные связывания. Вы массово связываете их в EXECUTE IMMEDIATE с предложением RETURNING BULK COLLECT INTO.
Например:
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE TYPE NameList IS TABLE OF VARCHAR2(15); enames NameList; bonus_amt NUMBER := 500; sql_stmt VARCHAR(200); BEGIN sql_stmt := 'UPDATE emp SET bonus = :1 RETURNING ename INTO :2'; EXECUTE IMMEDIATE sql_stmt USING bonus_amt RETURNING BULK COLLECT INTO enames; END; |
Пример динамический SQL внутри оператора FORALL
Чтобы связать входные переменные в операторе SQL, вы можете использовать оператор FORALL и предложение USING, как показано ниже. Оператор SQL не может быть запросом.
Например:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE TYPE NumList IS TABLE OF NUMBER; TYPE NameList IS TABLE OF VARCHAR2(15); empnos NumList; enames NameList; BEGIN empnos := NumList(1,2,3,4,5); FORALL i IN 1..5 EXECUTE IMMEDIATE 'UPDATE emp SET sal = sal * 1.1 WHERE empno = :1 RETURNING ename INTO :2' USING empnos(i) RETURNING BULK COLLECT INTO enames; ... END; |
Рекомендации по динамическому SQL
В этом разделе показано, как в полной мере использовать преимущества динамического SQL и как избежать некоторых распространенных ошибок.
Когда использовать или пропустить точку с запятой с помощью динамического SQL
При построении одного оператора SQL в строке не ставьте точку с запятой в конце.
При создании анонимного блока PL/SQL добавьте точку с запятой в конце каждого оператора PL/SQL и в конце анонимного блока.
Например:
|
1 2 3 4 |
BEGIN EXECUTE IMMEDIATE 'dbms_output.put_line(''Нет точки с запятой'')'; EXECUTE IMMEDIATE 'BEGIN dbms_output.put_line(''точка с запятой''); END;'; END; |
Повышение производительности динамического SQL с помощью переменных связывания
Когда код ваших операторов INSERT, UPDATE, DELETE, и SELECT находится непосредственно в PL/SQL, PL/SQL превращает переменные в переменные связывания автоматически, чтобы операторы эффективно работали с SQL. Когда вы создаете такие операторы в динамическом SQL, вам нужно указать переменные связывания самостоятельно, чтобы получить ту же производительность.
В приведенном ниже примере Oracle открывает отдельный курсор для каждого отдельного значения emp_id. Это может привести к конфликту ресурсов и снижению производительности, поскольку каждый оператор анализируется и кэшируется.
|
1 2 3 4 5 |
CREATE PROCEDURE fire_employee (emp_id NUMBER) AS BEGIN EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = ' || TO_CHAR(emp_id); END; |
Вы можете улучшить производительность, используя переменную связывания, которая позволяет Oracle повторно использовать один и тот же курсор для разных значений emp_id:
|
1 2 3 4 5 |
CREATE PROCEDURE fire_employee (emp_id NUMBER) AS BEGIN EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :num' USING emp_id; END; |
Передача имен объектов схемы в качестве параметров
Предположим, вам нужна процедура, которая принимает имя любой таблицы базы данных, а затем удаляет эту таблицу из вашей схемы. Вы должны создать строку с оператором, который включает имена объектов, а затем использовать EXECUTE IMMEDIATE для выполнения оператора:
|
1 2 3 4 |
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS BEGIN EXECUTE IMMEDIATE 'DROP TABLE ' || table_name; END; |
Используйте конкатенацию для построения строки, а не пытайтесь передать имя таблицы как переменную связывания через предложение USING.
Использование дублирующих заполнителей с динамическим SQL
Заполнители в динамическом операторе SQL связаны с аргументами связывания в предложении USING по позиции, а не по имени. Если вы укажете последовательность заполнителей, например :a, :a, :b, :b, вы должны включить в предложение USING четыре элемента. Например, учитывая динамическую строку:
|
1 |
sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)'; |
тот факт, что имя x повторяется, не имеет значения. Вы можете кодировать соответствующее предложение USING с четырьмя различными переменными связывания:
|
1 |
EXECUTE IMMEDIATE sql_stmt USING a, a, b, a; |
Если динамический оператор представляет PL/SQL блок, то правила для дублирующих заполнителей различны. Каждый уникальный заполнитель отображается на один элемент в предложении USING. Если один и тот же заполнитель появляется два или более раз, все ссылки на это имя соответствуют одному аргументу связывания в предложении USING.
В следующем примере все ссылки на заполнитель x связаны с первым аргументом связывания a, а второй уникальный заполнитель y связан со вторым аргументом связывания b.
Например:
|
1 2 3 4 5 6 7 |
DECLARE a NUMBER := 4; b NUMBER := 7; BEGIN plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;' EXECUTE IMMEDIATE plsql_block USING a, b; END; |
Использование атрибутов курсора с динамическим SQL
SQL атрибуты курсора %FOUND, %ISOPEN, %NOTFOUND и %ROWCOUNT работают в динамическом SQL при выдаче INSERT, UPDATE, DELETE или однорядного предложения SELECT:
|
1 2 |
EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id > 1000'; rows_deleted := SQL%ROWCOUNT; |
Аналогично, при добавлении к имени переменной курсора, атрибуты курсора возвращают информацию о выполнении многострочного запроса:
|
1 2 3 |
OPEN c1 FOR 'SELECT * FROM employees'; FETCH c1 BULK COLLECT INTO rec_tab; rows_fetched := c1%ROWCOUNT; |
Передача NULL в динамический SQL
Литерал NULL не допускается в предложении USING. Чтобы обойти это ограничение, замените ключевое слово NULL неинициализированной переменной:
|
1 2 3 4 5 |
DECLARE a_null CHAR(1); -- установится в NULL автоматически во время выполнения BEGIN EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null; END; |
Использование связей базы данных с динамическим SQL
Подпрограммы PL/SQL могут выполнять динамические операторы SQL, которые используют ссылки на базы данных для ссылки на объекты в удаленных базах данных:
|
1 2 3 4 5 |
PROCEDURE delete_dept (db_link VARCHAR2, dept_id INTEGER) IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM departments@' || db_link || ' WHERE deptno = :num' USING dept_id; END; |
Цели удаленных вызовов процедур (RPC) могут содержать динамические операторы SQL. Например, предположим, что следующая автономная функция, которая возвращает количество строк в таблице, находится в базе данных Чикаго:
|
1 2 3 4 5 6 |
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; |
Из анонимного блока вы можете вызвать функцию удаленно, как показано ниже:
|
1 2 3 4 5 |
DECLARE emp_count INTEGER; BEGIN emp_count := row_count@chicago('employees'); END; |
Использование прав Invoker с динамическим SQL
Динамический SQL позволяет писать процедуры управления схемами, которые можно централизовать в одной схеме, а также вызывать из других схем и работать с объектами в этих схемах.
Например, эта процедура может удалить любой объект базы данных:
|
1 2 3 4 5 6 7 |
CREATE OR REPLACE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2) AUTHID CURRENT_USER AS BEGIN EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name; END; |
Допустим, эта процедура является частью схемы HR. Без этого условия AUTHID процедура всегда удаляла бы объекты в схеме HR, независимо от того, кто ее вызывает. Даже если вы передадите полное имя объекта, эта процедура не будет иметь прав для внесения изменений в другие схемы.
AUTHID оператор поднимает оба этих ограничения. Он позволяет процедуре запускаться с привилегиями пользователя, который ее вызывает, и делает неквалифицированные ссылки на объекты в схеме этого пользователя.
Как избежать тупиков с помощью динамического SQL
В некоторых ситуациях выполнение оператора определения данных SQL приводит к тупику. Например, приведенная ниже процедура вызывает взаимоблокировку, поскольку она пытается удалить саму себя. Для того чтобы избежать тупиков, никогда не пытайтесь выполнить команды ALTER или DROP подпрограммы или пакета в то время как вы все еще используете его.
|
1 2 3 4 |
CREATE OR REPLACE PROCEDURE calc_bonus (emp_id NUMBER) AS BEGIN EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus'; -- тупик! END; |