Executing Dynamic Query Statements

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-1

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.
  

  • 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.

Example

 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

--Retrieve values from dynamic statements (INTO clause).
DECLARE
   staff_count  VARCHAR2(20);
BEGIN
   EXECUTE IMMEDIATE 'select count(*) from staffs'
      INTO staff_count;
   dbms_output.put_line(staff_count);
END;
/

--Pass and retrieve values (the INTO clause is used before the USING clause).
CREATE OR REPLACE PROCEDURE dynamic_proc
AS
   staff_id     NUMBER(6) := 200;
   first_name   VARCHAR2(20);
   salary       NUMBER(8,2);
BEGIN
   EXECUTE IMMEDIATE 'select first_name, salary from staffs where staff_id = :1'
       INTO first_name, salary
       USING IN staff_id;
   dbms_output.put_line(first_name || ' ' || salary);
END;
/

-- Invoke the stored procedure.
CALL dynamic_proc();

-- Delete the stored procedure.
DROP PROCEDURE dynamic_proc;

OPEN FOR

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

For details about the syntax, see Figure 3.

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.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

DECLARE
    name          VARCHAR2(20);
    phone_number  VARCHAR2(20);
    salary        NUMBER(8,2);
    sqlstr        VARCHAR2(1024);

    TYPE app_ref_cur_type IS REF CURSOR; -- Define the cursor type.
    my_cur app_ref_cur_type; -- Define the cursor variable.
    
BEGIN
    sqlstr := 'select first_name,phone_number,salary from staffs
         where section_id = :1';
    OPEN my_cur FOR sqlstr USING '30'; -- Open the cursor. using is optional.
    FETCH my_cur INTO name, phone_number, salary; -- Retrieve the data.
    WHILE my_cur%FOUND LOOP
          dbms_output.put_line(name||'#'||phone_number||'#'||salary);
          FETCH my_cur INTO name, phone_number, salary;
    END LOOP;
    CLOSE my_cur; -- Close the cursor.
END;
/