Меню Закрыть

Oracle execute immediate using

Содержание

The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance. For more information, see Chapter 7.

Syntax


Description of the illustration execute_immediate_statement.gif

Keyword and Parameter Description

bind_argument

An expression whose value is passed to the dynamic SQL statement, or a variable that stores a value returned by the dynamic SQL statement.

define_variable_name

A variable that stores a selected column value.

dynamic_string

A string literal, variable, or expression that represents a single SQL statement or a PL/SQL block. It must be of type CHAR or VARCHAR2 , not NCHAR or NVARCHAR2 .

Used only for single-row queries, this clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause.

record_name

A user-defined or %ROWTYPE record that stores a selected row.

RETURNING INTO .

Used only for DML statements that have a RETURNING clause (without a BULK COLLECT clause), this clause specifies the bind variables into which column values are returned. For each value returned by the DML statement, there must be a corresponding, type-compatible variable in the RETURNING INTO clause.

USING .

Specifies a list of input and/or output bind arguments. The parameter mode defaults to IN .

Usage Notes

Except for multi-row queries, the dynamic string can contain any SQL statement (without the final semicolon) or any PL/SQL block (with the final semicolon). The string can also contain placeholders for bind arguments. You cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.

You can place all bind arguments in the USING clause. The default parameter mode is IN . For DML statements that have a RETURNING clause, you can place OUT arguments in the RETURNING INTO clause without specifying the parameter mode, which, by definition, is OUT . If you use both the USING clause and the RETURNING INTO clause, the USING clause can contain only IN arguments.

At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals ( TRUE , FALSE , and NULL ). To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".

Dynamic SQL supports all the SQL datatypes. For example, define variables and bind arguments can be collections, LOB s, instances of an object type, and refs. Dynamic SQL does not support PL/SQL-specific types. For example, define variables and bind arguments cannot be Booleans or index-by tables. The only exception is that a PL/SQL record can appear in the INTO clause.

You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. You still incur some overhead, because EXECUTE IMMEDIATE re-prepares the dynamic string before every execution.

The string argument to the EXECUTE IMMEDIATE command cannot be one of the national character types, such as NCHAR or NVARCHAR2 .

Examples

The following PL/SQL block contains several examples of dynamic SQL:

добавлено: 06 сен 13
понравилось:0
просмотров: 105775
комментов: 4

Динамический SQL. Часть первая
В некоторых задачах требуется использование динамических запросов. Это случается , когда мы заранее не знаем какой нам нужен SQL запрос, какие обьекты будут использоваться для получения данных.

Подобные задачи, иногда возникают при построении сложной банковской, бухгалтерской отчетности, а также в моделях данных — обьект, атрибут , связь, при формировании динамических разнородных списков, в задачах миграции и интеграции .

Так же динамический SQL незаменим в случаях ,когда требуется назначить или изменить для определённого пользователя права или привилегии, или роли, или для изменения параметров сессии

Ключевое отличие динамического sql от статического , в том, что команда динамического SQL строится непосредственно во время выполнения процедуры или функции PL/SQL.

Таким образом, команда динамического SQL — это , построенная во время выполнения программы строка SQL запроса , так же такая строка может быть использована во время выполнения анонимного PLSQL блока.

Для выполнения динамических SQL команд , которые возвращают одну строчку рациональнее всего использовать конструкцию EXECUTE IMMEDIATE , так же EXECUTE IMMEDIATE применяется для выполнения динамических команд обновления и изменения данных UPDATE, DELETE, и для выполнения DDL команд.

Использование EXECUTE IMMEDIATE
Оператор EXECUTE IMMEDIATE разбирает и выполняет динамический оператор SQL или анонимный PL / SQL блок. Синтаксис оператора EXECUTE IMMEDIATE следующий

где dynamic_string это строковое выражение, которое представляет SQL запрос или PL / SQL блок,
define_variable это переменные, которая сохраняет выбранное значение столбца, так же можно использовать вместо данных переменных record тип, определенный пользователем, или %ROWTYPE запись, которая совпадает по формату с выбранной строкой.

Входной bind_argument является выражением, значение которого передается в динамический оператор SQL или PL / SQL блок. bind_argument это переменная, в которую будет сохранятся значение, возвращаемое динамическим оператором SQL или PL / SQL блоком.

Следует помнить, что конструкция EXECUTE IMMEDIATE используется только для запросов которые возвращают одну и только одну строчку, конструкция INTO определяет переменные или записи , куда извлекаются значения выражения SELECT. Результат вывода колонки запроса должен быть совместим по типу для переменной в конструкции в INTO .

Так же EXECUTE IMMEDIATE используется для выполнения DML-инструкций
связыващие аргументы задаются в конструкции USING .
Динамический SQL поддерживает все типы данных SQL. Так, например, задаются связанные переменные они могут быть коллекциями, LOB типами, типами объекта, и ссылками.
Примеры динамического SQL с оператором EXECUTE IMMEDIATE

Следующий PL / SQL блок содержит несколько примеров:

В приведенном ниже примере, процедура принимает имя таблицы базы данных (таблица ’emp’ ) и дополнительные параметры для WHERE (такие как ‘sal > 2000’ ). Если опустить условие, процедура удаляет все строки из таблицы. В противном случае, процедура удаляет только те строки, которые удовлетворяют условию.

Использование переменных связки в EXECUTE IMMEDIATE. Конструкция RETURNING
Если динамический INSERT , UPDATE или DELETE возвращают значение ,тогда используйте RETURNING ,вы можете связать выходные данные используя конструкции USING out или RETURNING INTO .

Параметры , специальные типы, RETURNING, EXECUTE IMMEDIATE

В конструкции RETURNING INTO вы не указываете тип параметра для связки выходных аргументов, потому что, по определению, параметр типа OUT . Ниже приведен пример:

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

Читайте также:  Asus xonar dgx драйвер windows 10

Для вызова процедуры из динамического PL / SQL, необходимо обязательно указать IN OUT тип для связывающей переменно, связанной с формальным параметром deptno , вот таким образом:

гуглил EXECUTE IMMEDIATE

спасибо за статью!

хотелось бы обсудить или почитать о достоинствах и недостатках конструкции. есть практический опыт использования? поддерживается ли в современных версиях ORACLE др СУБД. хочу предварительно собрать информацию и потом решить использовать это или нет и если использовать — то где это уместно, а где — не уместно 🙂

пожалуйста , есть конечно опыт , работает во всех современных версиях СУБД
Здесь такая штука — рекомендуется использовать динамический SQL по необходимости , то есть там , где без динамического SQL никак нельзя обойтись.
например динамический поиск , динамический запрос с множеством инвариантных условий. по другим СУБД , скорее нет , я не встречался .

declare
t_obj c_obj%ROWTYPE;
begin
—работает такая конструкция
EXECUTE IMMEDIATE ‘select t.* from c_obj t where t.obj_ > into t_obj using parmOBJ_ID;
—работает такая конструкция
insert into c_obj t values t_obj;
—работает такая конструкция. b_obj и с_obj таблицы одной структуры
execute immediate ‘insert into b_obj
select c.* from c_obj c where c.obj_ > using parmOBJ_ID;
— к сожалению не работает такая конструкция
EXECUTE IMMEDIATE ‘insert into c_obj t values :t_obj’
using t_obj;
—работает такая конструкция
update c_obj t set row=t_obj where t.obj_ > — к сожалению не работает такая конструкция
EXECUTE IMMEDIATE ‘update c_obj t set row=:t_obj where t.obj_ > using t_obj,parmOBJ_ID;

Ошибка: PLS-00457: выражения должны иметь тип SQL Текст: using t_obj;

Ошибка: PLS-00457: выражения должны иметь тип SQL Текст: using t_obj,parmOBJ_ID;

—И пробовали как это можно обойти??

IHmG, понимаю, что много лет прошло, все же отвечу. В T-SQL есть EXEC и можно писать так:

declare @s varchar(max),
@i int
select @i = 1234
select @s = ‘ select * from t1 where >exec (@s)

Ну, т.е. собрать можно какой угодно запрос. Я генерил запрос, который генерит запрос, который генерит запрос (тройная вложенность), была необходимость.

This chapter describes how to use native dynamic SQL (dynamic SQL for short) with PL/SQL to make your programs more flexible, by building and processing SQL statements at run time.

With dynamic SQL, you can directly execute most types of SQL statement, including data definition and data control statements. You can build statements in which you do not know table names, WHERE clauses, and other information in advance.

This chapter contains these topics:

Why Use Dynamic SQL with PL/SQL?

Dynamic SQL enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.

To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query ( SELECT statement), you use the OPEN-FOR , FETCH , and CLOSE statements.

You need dynamic SQL in the following situations:

You want to execute a SQL data definition statement (such as CREATE ), a data control statement (such as GRANT ), or a session control statement (such as ALTER SESSION ). Unlike INSERT , UPDATE , and DELETE statements, these statements cannot be included directly in a PL/SQL program.

You want more flexibility. For example, you might want to pass the name of a schema object as a parameter to a procedure. You might want to build different search conditions for the WHERE clause of a SELECT statement.

You want to issue a query where you do not know the number, names, or datatypes of the columns in advance. In this case, you use the DBMS_SQL package rather than the OPEN-FOR statement.

If you have older code that uses the DBMS_SQL package, the techniques described in this chapter using EXECUTE IMMEDIATE and OPEN-FOR generally provide better performance, more readable code, and extra features such as support for objects and collections.

For a comparison of dynamic SQL with DBMS_SQL , see Oracle Database Application Developer’s Guide — Fundamentals . For information on the DBMS_SQL package, see Oracle Database PL/SQL Packages and Types Reference .

Native dynamic SQL using the EXECUTE IMMEDIATE and OPEN-FOR statements is faster and requires less coding than the DBMS_SQL package. However, the DBMS_SQL package should be used in these situations:

There is an unknown number of input or output variables, such as the number of column values returned by a query, that are used in a dynamic SQL statement (Method 4 for dynamic SQL).

The dynamic code is too large to fit inside a 32K bytes VARCHAR2 variable.

Using the EXECUTE IMMEDIATE Statement in PL/SQL

The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block. The main argument to EXECUTE IMMEDIATE is the string containing the SQL statement to execute. You can build up the string using concatenation, or use a predefined string.

Except for multi-row queries, the dynamic string can contain any SQL statement or any PL/SQL block. The string can also contain placeholders, arbitrary names preceded by a colon, for bind arguments. In this case, you specify which PL/SQL variables correspond to the placeholders with the INTO , USING , and RETURNING INTO clauses.

When constructing a single SQL statement in a dynamic string, do not include a semicolon (;) at the end inside the quotation mark. When constructing a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block; there will be a semicolon immediately before the end of the string literal, and another following the closing single quotation mark.

You can only use placeholders in places where you can substitute variables in the SQL statement, such as conditional tests in WHERE clauses. You cannot use placeholders for the names of schema objects. For the right way, see "Passing Schema Object Names As Parameters".

Used only for single-row queries, the INTO clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause.

Used only for DML statements that have a RETURNING clause (without a BULK COLLECT clause), the RETURNING INTO clause specifies the variables into which column values are returned. For each value returned by the DML statement, there must be a corresponding, type-compatible variable in the RETURNING INTO clause.

You can place all bind arguments in the USING clause. The default parameter mode is IN . For DML statements that have a RETURNING clause, you can place OUT arguments in the RETURNING INTO clause without specifying the parameter mode. If you use both the USING clause and the RETURNING INTO clause, the USING clause can contain only IN arguments.

Читайте также:  Задержка нажатия клавиш на клавиатуре

At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals ( TRUE , FALSE , and NULL ). To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".

Dynamic SQL supports all the SQL datatypes. For example, define variables and bind arguments can be collections, LOB s, instances of an object type, and refs.

As a rule, dynamic SQL does not support PL/SQL-specific types. For example, define variables and bind arguments cannot be Booleans or associative arrays. The only exception is that a PL/SQL record can appear in the INTO clause.

You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. However, you incur some overhead because EXECUTE IMMEDIATE re-prepares the dynamic string before every execution.

For more information on EXECUTE IMMEDIATE , see "EXECUTE IMMEDIATE Statement".

Example 7-1 illustrates several uses of dynamic SQL.

Example 7-1 Examples of Dynamic SQL

In Example 7-2, a standalone procedure accepts the name of a database table and an optional WHERE -clause condition. If you omit the condition, the procedure deletes all rows from the table. Otherwise, the procedure deletes only those rows that meet the condition.

Example 7-2 Dynamic SQL Procedure that Accepts Table Name and WHERE Clause

Specifying Parameter Modes for Bind Variables in Dynamic SQL Strings

With the USING clause, the mode defaults to IN , so you do not need to specify a parameter mode for input bind arguments.

With the RETURNING INTO clause, the mode is OUT , so you cannot specify a parameter mode for output bind arguments.

You must specify the parameter mode in more complicated cases, such as this one where you call a procedure from a dynamic PL/SQL block:

To call the procedure from a dynamic PL/SQL block, you must specify the IN OUT mode for the bind argument associated with formal parameter deptid , as shown in Example 7-3.

Example 7-3 Using IN OUT Bind Arguments to Specify Substitutions

Using Bulk Dynamic SQL in PL/SQL

Bulk SQL passes entire collections back and forth, not just individual elements. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. You can use a single statement instead of a loop that issues a SQL statement in every iteration.

Using the following commands, clauses, and cursor attribute, your applications can construct bulk SQL statements, then execute them dynamically at run time:

BULK FETCH statement
BULK EXECUTE IMMEDIATE statement
FORALL statement
COLLECT INTO clause
RETURNING INTO clause
%BULK_ROWCOUNT cursor attribute

The static versions of these statements, clauses, and cursor attribute are discussed in "Reducing Loop Overhead for DML Statements and Queries with Bulk SQL". Refer to that section for background information.

Using Dynamic SQL with Bulk SQL

Bulk binding lets Oracle bind a variable in a SQL statement to a collection of values. The collection type can be any PL/SQL collection type: index-by table, nested table, or varray. The collection elements must have a SQL datatype such as CHAR , DATE , or NUMBER . Three statements support dynamic bulk binds: EXECUTE IMMEDIATE , FETCH , and FORALL .

You can use the BULK COLLECT INTO clause with the EXECUTE IMMEDIATE statement to store values from each column of a query’s result set in a separate collection.

You can use the RETURNING BULK COLLECT INTO clause with the EXECUTE IMMEDIATE statement to store the results of an INSERT , UPDATE , or DELETE statement in a set of collections.

You can use the BULK COLLECT INTO clause with the FETCH statement to store values from each column of a cursor in a separate collection.

You can put an EXECUTE IMMEDIATE statement with the RETURNING BULK COLLECT INTO inside a FORALL statement. You can store the results of all the INSERT , UPDATE , or DELETE statements in a set of collections.

You can pass subscripted collection elements to the EXECUTE IMMEDIATE statement through the USING clause. You cannot concatenate the subscripted elements directly into the string argument to EXECUTE IMMEDIATE ; for example, you cannot build a collection of table names and write a FORALL statement where each iteration applies to a different table.

Examples of Dynamic Bulk Binds

This sections contains examples of dynamic bulk binds.You can bind define variables in a dynamic query using the BULK COLLECT INTO clause. As shown in Example 7-4, you can use that clause in a bulk FETCH or bulk EXECUTE IMMEDIATE statement.

Example 7-4 Dynamic SQL with BULK COLLECT INTO Clause

Only INSERT , UPDATE , and DELETE statements can have output bind variables. You bulk-bind them with the RETURNING BULK COLLECT INTO clause of EXECUTE IMMEDIATE , as shown in Example 7-5.

Example 7-5 Dynamic SQL with RETURNING BULK COLLECT INTO Clause

To bind the input variables in a SQL statement, you can use the FORALL statement and USING clause, as shown in Example 7-6. The SQL statement cannot be a query.

Example 7-6 Dynamic SQL Inside FORALL Statement

Guidelines for Using Dynamic SQL with PL/SQL

This section shows you how to take full advantage of dynamic SQL and how to avoid some common pitfalls.

When using dynamic SQL with PL/SQL, be aware of the risks of SQL injection, which is a possible security issue. For more information on SQL injection and possible problems, see Oracle Database Application Developer’s Guide — Fundamentals . You can also search for "SQL injection" on the Oracle Technology Network at http://www.oracle.com/technology/

Building a Dynamic Query with Dynamic SQL

You use three statements to process a dynamic multi-row query: OPEN-FOR , FETCH , and CLOSE . First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result set one at a time. When all the rows are processed, you CLOSE the cursor variable. For more information about cursor variables, see "Using Cursor Variables (REF CURSORs)".

When to Use or Omit the Semicolon with Dynamic SQL

When building up a single SQL statement in a string, do not include any semicolon at the end.

Читайте также:  Можно ли найти работу в интернете

When building up a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block. For example:

Improving Performance of Dynamic SQL with Bind Variables

When you code INSERT , UPDATE , DELETE , and SELECT statements directly in PL/SQL, PL/SQL turns the variables into bind variables automatically, to make the statements work efficiently with SQL. When you build up such statements in dynamic SQL, you need to specify the bind variables yourself to get the same performance.

In the following example, Oracle opens a different cursor for each distinct value of emp_id . This can lead to resource contention and poor performance as each statement is parsed and cached.

You can improve performance by using a bind variable, which allows Oracle to reuse the same cursor for different values of emp_id :

Passing Schema Object Names As Parameters

Suppose you need a procedure that accepts the name of any database table, then drops that table from your schema. You must build a string with a statement that includes the object names, then use EXECUTE IMMEDIATE to execute the statement:

Use concatenation to build the string, rather than trying to pass the table name as a bind variable through the USING clause.

In addition, if you need to call a procedure whose name is unknown until runtime, you can pass a parameter identifying the procedure. For example, the following procedure can call another procedure ( drop_table ) by specifying the procedure name when executed.

If you want to drop a table with the drop_table procedure, you can run the procedure as follows. Note that the procedure name is capitalized.

Using Duplicate Placeholders with Dynamic SQL

Placeholders in a dynamic SQL statement are associated with bind arguments in the USING clause by position, not by name. If you specify a sequence of placeholders like :a, :a, :b, :b , you must include four items in the USING clause. For example, given the dynamic string

sql_stmt := ‘INSERT INTO payroll VALUES (:x, :x, :y, :x)’;

the fact that the name X is repeated is not significant. You can code the corresponding USING clause with four different bind variables:

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

If the dynamic statement represents a PL/SQL block, the rules for duplicate placeholders are different. Each unique placeholder maps to a single item in the USING clause. If the same placeholder appears two or more times, all references to that name correspond to one bind argument in the USING clause. In Example 7-7, all references to the placeholder x are associated with the first bind argument a, and the second unique placeholder y is associated with the second bind argument b.

Example 7-7 Using Duplicate Placeholders With Dynamic SQL

Using Cursor Attributes with Dynamic SQL

The SQL cursor attributes %FOUND , %ISOPEN , %NOTFOUND , and %ROWCOUNT work when you issue an INSERT , UPDATE , DELETE , or single-row SELECT statement in dynamic SQL:

Likewise, when appended to a cursor variable name, the cursor attributes return information about the execution of a multi-row query:

Example 7-8 Accessing %ROWCOUNT For an Explicit Cursor

For more information about cursor attributes, see "Managing Cursors in PL/SQL".

Passing Nulls to Dynamic SQL

The literal NULL is not allowed in the USING clause. To work around this restriction, replace the keyword NULL with an uninitialized variable:

Using Database Links with Dynamic SQL

PL/SQL subprograms can execute dynamic SQL statements that use database links to refer to objects on remote databases:

The targets of remote procedure calls (RPCs) can contain dynamic SQL statements. For example, suppose the following standalone function, which returns the number of rows in a table, resides on the hr_db database in London:

Using Invoker Rights with Dynamic SQL

Dynamic SQL lets you write schema-management procedures that can be centralized in one schema, and can be called from other schemas and operate on the objects in those schemas. For example, this procedure can drop any kind of database object:

Let’s say that this procedure is part of the HR schema. Without the AUTHID clause, the procedure would always drop objects in the HR schema, regardless of who calls it. Even if you pass a fully qualified object name, this procedure would not have the privileges to make changes in other schemas.

The AUTHID clause lifts both of these restrictions. It lets the procedure run with the privileges of the user that invokes it, and makes unqualified references refer to objects in that user’s schema.

Using Pragma RESTRICT_REFERENCES with Dynamic SQL

A function called from SQL statements must obey certain rules meant to control side effects. (See "Controlling Side Effects of PL/SQL Subprograms".) To check for violations of the rules, you can use the pragma RESTRICT_REFERENCES . The pragma asserts that a function does not read or write database tables or package variables. (For more information, See Oracle Database Application Developer’s Guide — Fundamentals .)

If the function body contains a dynamic INSERT , UPDATE , or DELETE statement, the function always violates the rules write no database state ( WNDS ) and read no database state ( RNDS ). PL/SQL cannot detect those side-effects automatically, because dynamic SQL statements are checked at run time, not at compile time. In an EXECUTE IMMEDIATE statement, only the INTO clause can be checked at compile time for violations of RNDS .

Avoiding Deadlocks with Dynamic SQL

In a few situations, executing a SQL data definition statement results in a deadlock. For example, the following procedure causes a deadlock because it attempts to drop itself. To avoid deadlocks, never try to ALTER or DROP a subprogram or package while you are still using it.

Backward Compatibility of the USING Clause

When a dynamic INSERT , UPDATE , or DELETE statement has a RETURNING clause, output bind arguments can go in the RETURNING INTO clause or the USING clause. In new applications, use the RETURNING INTO clause. In old applications, you can continue to use the USING clause.

Using Dynamic SQL With PL/SQL Records and Collections

You can use dynamic SQL with records and collections. As shown in Example 7-9, you can fetch rows from the result set of a dynamic multi-row query into a record:

Example 7-9 Dynamic SQL Fetching into a Record

For an example of using dynamic SQL with object types, see "Using Dynamic SQL With Objects".

Рекомендуем к прочтению

Добавить комментарий

Ваш адрес email не будет опубликован.