Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ Stored Procedures/ 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 using EXECUTE IMMEDIATE or OPEN FOR in GaussDB. 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.

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
gaussdb=# DROP SCHEMA IF EXISTS hr CASCADE;
gaussdb=# CREATE SCHEMA hr;
gaussdb=# SET CURRENT_SCHEMA = hr;
gaussdb=# CREATE TABLE staffs 
(
  staff_id NUMBER, 
  first_name VARCHAR2,
  salary NUMBER
);
gaussdb=# INSERT INTO staffs VALUES (200, 'mike', 5800);
gaussdb=# INSERT INTO staffs VALUES (201, 'lily', 3000);
gaussdb=# INSERT INTO staffs VALUES (202, 'john', 4400);

-- Retrieve values from dynamic statements (INTO clause).
gaussdb=# DECLARE
   staff_count  VARCHAR2(20);
BEGIN
   EXECUTE IMMEDIATE 'select count(*) from hr.staffs'
      INTO staff_count;
   dbe_output.print_line(staff_count);
END;
/
3
ANONYMOUS BLOCK EXECUTE
-- Pass and retrieve values (the INTO clause is used before the USING clause).
gaussdb=# 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 hr.staffs where staff_id = :1'
       INTO first_name, salary
       USING IN staff_id;
   dbe_output.print_line(first_name || ' ' || salary);
END;
/
CREATE PROCEDURE
-- Invoke a stored procedure.
gaussdb=# CALL dynamic_proc();
mike 5800.00
 dynamic_proc 
--------------

(1 row)

-- Delete the stored procedure.
gaussdb=# DROP PROCEDURE dynamic_proc;

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 details about 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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
gaussdb=# CREATE SCHEMA hr;
gaussdb=# SET CURRENT_SCHEMA = hr;
gaussdb=# CREATE TABLE staffs 
(
  section_id NUMBER,
  first_name VARCHAR2,
  phone_number VARCHAR2, 
  salary NUMBER
  );
gaussdb=# INSERT INTO staffs VALUES (30, 'mike', '13567829252', 5800);
gaussdb=# INSERT INTO staffs VALUES (40, 'john', '17896354637', 4000);

gaussdb=# 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 hr.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
          dbe_output.print_line(name||'#'||phone_number||'#'||salary);
          FETCH my_cur INTO name, phone_number, salary;
    END LOOP;
    CLOSE my_cur;   -- Close the cursor.
END;
/
mike#13567829252#5800.00
mike#13567829252#5800.00
ANONYMOUS BLOCK EXECUTE
Parent topic: Dynamic Statements