Executing Dynamic Query Statements
You can perform dynamic queries openGauss 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 data set, 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.
The above syntax diagram is explained as follows:
define_variable: specifies variables to store single-line query results.
USING IN bind_argument: specifies where the variable passed to the dynamic SQL value is stored, that is, in the dynamic placeholder of dynamic_select_string.
USING OUT bind_argument: specifies where the dynamic SQL returns the value of the variable.
NOTICE:- 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.
OPEN FOR
Dynamic query statements can be executed by using OPEN FOR to open dynamic cursors.
Figure 3 shows the syntax diagram.
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 use of cursors, see Cursors.