Dynamic Statements

Executing Dynamic Query Statements

You can perform dynamic queries MogDB 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](#EXECUTE IMMEDIATE) 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 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.

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 use of cursors, see Cursors.

Executing Dynamic Non-query Statements

Syntax

Figure 4 shows the syntax diagram.

Figure 4 noselect::=

Figure 5 shows the syntax diagram for using_clause.

Figure 5 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:
mogdb=# CREATE TABLE sections_t1
(
   section       NUMBER(4) ,
   section_name  VARCHAR2(30),
   manager_id    NUMBER(6),
   place_id      NUMBER(4)
);

-- Declare a variable:
mogdb=# 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:
mogdb=# SELECT * FROM sections_t1;

--Delete the table.
mogdb=# DROP TABLE sections_t1;

Dynamically Calling Stored Procedures

This section describes how to dynamically call store procedures. You must use anonymous statement blocks to package stored procedures or statement blocks and append IN and OUT behind the EXECUTE IMMEDIATE…USING statement to input and output parameters.

Syntax

Figure 6 shows the syntax diagram.

Figure 6 call_procedure::=

[Figure 7](#Figure 2) shows the syntax diagram for using_clause.

Figure 7 using_clause::=

The above syntax diagram is explained as follows:

• CALL procedure_name: calls the stored procedure.
• [:placeholder1,:placeholder2,…]: specifies the placeholder list of the stored procedure parameters. The numbers of the placeholders and parameters are the same.
• USING [IN|OUT|IN OUT]bind_argument: specifies where the variable passed to the stored procedure parameter value is stored. The modifiers in front of bind_argument and of the corresponding parameter are the same.

Dynamically Calling Anonymous Blocks

This section describes how to execute anonymous blocks in dynamic statements. Append IN and OUT behind the EXECUTE IMMEDIATE…USING statement to input and output parameters.

Syntax

Figure 8 shows the syntax diagram.

Figure 8 call_anonymous_block::=

[Figure 9](#Figure 2using_clause) shows the syntax diagram for using_clause.

Figure 9 using_clause::=

The above syntax diagram is explained as follows:

• The execute part of an anonymous block starts with a BEGIN statement, has a break with an END statement, and ends with a semicolon (;).
• USING [IN|OUT|IN OUT]bind_argument: specifies where the variable passed to the stored procedure parameter value is stored. The modifiers in front of bind_argument and of the corresponding parameter are the same.
• The input and output parameters in the middle of an anonymous block are designated by placeholders. The numbers of the placeholders and parameters are the same. The sequences of the parameters corresponding to the placeholders and the USING parameters are the same.
• Currently in MogDB, when dynamic statements call anonymous blocks, placeholders cannot be used to pass input and output parameters in an EXCEPTION statement.