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

Executing Dynamic Query Statements

You can perform dynamic queries GaussDB provides two modes: EXECUTE IMMEDIATE and OPEN FOR. EXECUTE IMMEDIATE dynamically executes SELECT statements and OPEN FOR combines 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 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.
    • A dynamic PL/SQL block allows duplicate placeholders. That is, a placeholder can correspond to only one bind_argument in the USING clause. 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.

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.

Parent topic: Dynamic Statements