[Hint] Execute Immediate-Dynamic SQL

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc

Poston Fri, 25 Feb 2005 2:23 pm

Dynamic SQL-Execute Immediate Statement-------------------------------------------What is dynamic SQL?
---------------------Many PL/SQL programs do a specific job. For example, a Stored Procedure can accept a number of employee and a salary increase, then update the salary column in the table. In this case all the UPDATE command is known at compile time. As they do not change from execution to execution, SQL are called Stático.
However, some programs may install and execute SQL in runtime.
For example, a rlatório of proposals can build different SQL queries for the various reports generated. They are come from Dynamic SQL.
Dynamic SQl are stored in strings built by your program at run time.
This way strings can contain the text of a valid SQl or PL/SQL block may contain placeholders for bind arguments. These spaces are undeclared identifiers, so their names don't matter. For example the PL/SQL does not distinguish between the following strings: ' DELETE FROM emp WHERE sal >: meu_sal AND with <: minha_comm '' DELETE FROM emp WHERE sal >: s AND < comm: c ' to render many Dynamic SQL, use the EXECUTE IMMEDIATE. However to render múltipolas rows queries use OPEN-is, FETCH and CLOSE.

USING the EXECUTE IMMEDIATE------------------------------He prepares and immediately executes a dynamic SQL or PL/SQl anonymous block.

Syntax: EXECUTE IMMEDIATE dynamic_string [INTO {define_variable [, define_variable] ... | record}] [USING [IN | OUT | IN OUT bind_argument [], [IN | OUT | IN OUT bind_argument]] ...]
[{RETURNING | RETURN} INTO bind_argument [, bind_argument] ...];

where is the string of dynamic_string expression that represents the SQL or PL/SQL block define_variable, is the variable that stores the value of the selected column, and record refers to the registry or% ROWTYPE that stores the selected row, the INPUT bind_arguments is the expression that passes the value to the string of SQL or PL/SQL block.
The bind_arguments OUTPUT is the value that stores the return of SQL or PL/SQL.

Except for multiline queries, dynamic string can contain any SQL statement (without the terminator [.]) or any PL/SQL with the terminator [.].
The string can contain placeholder for bind arguments. However, you cannot use these arguments to pass names of objects of schemas for a dynamic SQL.

Used only for single line queries, the INTO clause specifies that variables or records returned by the query must have a type compatible in the INTO clause.

Used only for DML commands that have the RETURNING clause, the RETURNING INTO specifies variables the values of the columns returned.
For each value returned by DML command, we must have a corresponding type in the RETURNING INTO clause.

Example: CREATE PROCEDURE delete_rows (table_name IN VARCHAR2, VARCHAR2 DEFAULT NULL condition IN) the where_clause VARCHAR2 (100): = ' WHERE ' || condition;

BEGIN IF condition IS NULL THEN where_clause: = NULL;

EXECUTE IMMEDIATE ' DELETE FROM ' || table_name || where_clause;



[] 's guys:-
Location: Videira - SC

  • See also
    Last Post