[Tip] Run 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
Post Reply
User avatar
MuLtAnI
Moderador
Moderador
Posts: 90
Joined: Tue, 01 Jun 2004 9:21 am
Location: Videira - SC
Contact:

SQL Dynamic - Declaration Run Immediate
--------------------------------------- ----

What is Dynamic SQL?
--------------------

- Many PL / SQL programs do a specific job. For example a stored
procedure can accept an employee number and an increase of salary, then
update the salary column in the table. In this case the entire update command
is known in compile time. As they do not change execution for execution,
are called SQL Static.
Meanwhile, some programs can mount and run SQL at run time.
For example, a proposed rlative can construct different SQL queries
for the various generated reports. They are flame of dynamic SQL.
Dynamic SQL are stored in strings constructed by your program in time
.
In this way strings can contain the text of a valid SQL or a PL / SQL block
and may contain spaces reserved for the connection arguments. These spaces are non-declared
identifiers, so their names do not matter. For example
PL / SQL does not distinction between the following strings:

Select all

’DELETE FROM emp WHERE sal > :meu_sal AND com < :minha_comm’ 
’DELETE FROM emp WHERE sal > :s AND comm < :c’
To process many dynamic SQL, the Immediate execute is used. Meanwhile
to process multiple lines queries we use open-for, fetch and close.


using the execute immediate
-------------------------- ----

He prepares and immediately performs a dynamic SQL or an anonymous PL / SQL block.

Syntax:

Select all

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 Dynamic_string is the string of the expression that represents SQL or block
PL / SQL, 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 line, the
input bind_Arguments is the expression that passes the value to the string SQL or block
PL / SQL.
Output bind_Arguments is the value that stores the return of SQL or PL / SQL.

Except for multi-line queries, dynamic string may contain any SQL
SQL statement (without the terminator [.]) Or any PL / SQL with the terminator [.].
String may contain placeholder for the connection arguments. However,
you can not use these arguments to pass names of schemas to
a dynamic SQL.

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

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

Example:

Select all

CREATE 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; 
 
EXCEPTION 
 
... 
 
 
END;

[] 's galera

] :-O
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 13 guests