Executing Dynamic Non-query Statements
Syntax
Figure 1 shows the syntax diagram.
Figure 2 shows the syntax diagram for using_clause.
The above syntax diagram is explained as follows:
**USING IN **bind_argument is used to specify the variable whose value is passed to the dynamic SQL statement. The variable is used when a placeholder exists in dynamic_noselect_string. That is, a placeholder is replaced by the corresponding bind_argument when a dynamic SQL statement is executed. Note that bind_argument can only be a value, variable, or expression, and cannot be a database object such as a table name, column name, and data type. If a stored procedure needs to transfer database objects through bind_argument to construct dynamic SQL statements (generally, DDL statements), you are advised to use double vertical bars (||) to concatenate dynamic_select_clause with a database object. In addition, a dynamic PL/SQL block allows duplicate placeholders. That is, a placeholder can correspond to only one bind_argument.
Example
-- Create a table:
postgres=# CREATE TABLE sections_t1
(
section NUMBER(4) ,
section_name VARCHAR2(30),
manager_id NUMBER(6),
place_id NUMBER(4)
);
-- Declare a variable:
postgres=# DECLARE
section NUMBER(4) := 280;
section_name VARCHAR2(30) := 'Info support';
manager_id NUMBER(6) := 103;
place_id NUMBER(4) := 1400;
new_colname VARCHAR2(10) := 'sec_name';
BEGIN
-- Execute the query:
EXECUTE IMMEDIATE 'insert into sections_t1 values(:1, :2, :3, :4)'
USING section, section_name, manager_id,place_id;
-- Execute the query (duplicate placeholders):
EXECUTE IMMEDIATE 'insert into sections_t1 values(:1, :2, :3, :1)'
USING section, section_name, manager_id;
-- Run the ALTER statement. (You are advised to use double vertical bars (||) to concatenate the dynamic DDL statement with a database object.)
EXECUTE IMMEDIATE 'alter table sections_t1 rename section_name to ' || new_colname;
END;
/
-- Query data:
postgres=# SELECT * FROM sections_t1;
--Delete the table.
postgres=# DROP TABLE sections_t1;