Updated on 2025-07-22 GMT+08:00

Executing Dynamic Query Statements

You can perform dynamic queries GaussDB provides two modes: EXECUTE IMMEDIATE and OPEN FOR. The former dynamically executes SELECT statements while the latter combines the use of cursors. If you need to store query results in a dataset, use OPEN FOR.

EXECUTE IMMEDIATE

Figure 1 shows the syntax diagram.

Figure 1 EXECUTE IMMEDIATE dynamic_select_clause::=

Figure 2 shows the syntax diagram for using_clause.

Figure 2 using_clause::=

The above syntax diagram is explained as follows:

  • define_variable: specifies a variable to store single-row query results.
  • USING IN bind_argument: specifies the variable whose value is passed to the dynamic SQL statement. The variable is used when a dynamic placeholder exists in dynamic_select_string.
  • USING OUT bind_argument: specifies the variable that stores a value returned by the dynamic SQL statement.
    • In query statements, INTO and OUT cannot coexist.
    • A placeholder name starts with a colon (:) followed by digits, characters, or strings, corresponding to bind_argument in the USING clause.
    • bind_argument can only be a value, variable, or expression. It cannot be a database object such as a table name, column name, and data type. That is, bind_argument cannot be used to transfer schema objects for dynamic SQL statements. If a stored procedure needs to transfer database objects by declaring the parameter 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.
    • A dynamic PL/SQL block allows duplicate placeholders. That is, a placeholder can correspond to only one bind_argument in the USING clause.

OPEN FOR

Dynamic query statements can be executed by using OPEN FOR to open dynamic cursors.

Figure 3 shows the syntax diagram.

Figure 3 open_for::=

Parameter description:

  • cursor_name: specifies the name of the cursor to be opened.
  • dynamic_string: specifies the dynamic query statement.
  • USING value: applies when a placeholder exists in dynamic_string.

For the use of cursors, see Cursors.