Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ Stored Procedures/ Dynamic Statements/ Executing Dynamic Non-Query Statements
Updated on 2024-05-07 GMT+08:00
View PDF
Share

Executing Dynamic Non-Query Statements

Syntax

Figure 1 shows the syntax diagram.

Figure 1 noselect::=

Figure 2 shows the syntax diagram for using_clause.

Figure 2 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. When the GUC parameter behavior_compat_options is set to dynamic_sql_compat, the bind arguments in the USING clause are matched in sequence based on the placeholder sequence. Duplicate placeholders will not be identified as the same placeholder.

Examples

 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 a table.
gaussdb=# CREATE TABLE sections_t1
(
   section       NUMBER(4) ,
   section_name  VARCHAR2(30),
   manager_id    NUMBER(6),
   place_id      NUMBER(4) 
)
DISTRIBUTE BY hash(manager_id);

-- Declare a variable.
gaussdb=# 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.
gaussdb=# SELECT * FROM sections_t1;
 section |   sec_name   | manager_id | place_id 
---------+--------------+------------+----------
     280 | Info support |        103 |     1400
     280 | Info support |        103 |      280
(2 rows)

-- Delete the table.
gaussdb=# DROP TABLE sections_t1;
Parent topic: Dynamic Statements