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

Executing Dynamic Query Statements

This section describes how to execute dynamic query statements. GaussDB provides two methods for dynamic query: EXECUTE IMMEDIATE and OPEN FOR. The former dynamically executes SELECT statements while the latter combines the 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 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.
    • The IMMEDIATE keyword is used only for syntax compatibility and has no actual meaning.
Example: 
gaussdb=# DROP SCHEMA IF EXISTS hr CASCADE;
NOTICE:  drop cascades to table staffs
DROP SCHEMA
gaussdb=# CREATE SCHEMA hr;
CREATE SCHEMA
gaussdb=# SET CURRENT_SCHEMA = hr;
SET
gaussdb=# CREATE TABLE staffs 
(
  staff_id NUMBER, 
  first_name VARCHAR2,
  salary NUMBER
);
CREATE TABLE
gaussdb=# INSERT INTO staffs VALUES (200, 'mike', 5800);
INSERT 0 1
gaussdb=# INSERT INTO staffs VALUES (201, 'lily', 3000);
INSERT 0 1
gaussdb=# INSERT INTO staffs VALUES (202, 'john', 4400);
INSERT 0 1
-- 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
-- Call the stored procedure.
gaussdb=# CALL dynamic_proc();
mike 5800.00
 dynamic_proc 
--------------

(1 row)
-- Delete the stored procedure.
gaussdb=# DROP PROCEDURE dynamic_proc;
DROP PROCEDURE

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.

Example: 
gaussdb=# CREATE SCHEMA hr;
CREATE SCHEMA
gaussdb=# SET CURRENT_SCHEMA = hr;
SET
gaussdb=# CREATE TABLE staffs 
(
  section_id NUMBER,
  first_name VARCHAR2,
  phone_number VARCHAR2, 
  salary NUMBER
  );
CREATE TABLE
gaussdb=# INSERT INTO staffs VALUES (30, 'mike', '13567829252', 5800);
INSERT 0 1
gaussdb=# INSERT INTO staffs VALUES (40, 'john', '17896354637', 4000);
INSERT 0 1
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
ANONYMOUS BLOCK EXECUTE
Parent topic: Dynamic Statements