How to use dynamic query statements in Oracle

Oracle dynamic query statements are a special type of query statements. The following describes the syntax of Oracle dynamic query statements in detail. If you are interested in Oracle dynamic query, take a look.

1. When you use the execute immediate statement to process a single row query, you must use the INTO clause to accept the returned data:

 
 

  
  
DECLARE
  
  
 
  
  
Query_stat VARCHAR2 (100): = 'select * FROM emp' | 'where empno =: eno ';
  
  
 
  
  
Emp_record emp % ROWTYPE;
  
  
 
  
  
BEGIN
  
  
 
  
  
Execute immediate query_stat INTO emp_record USING & eno;
  
  
 
  
  
Dbms_output.put_line ('name: '| emp_record.ename |', position: '| emp_record.job );
  
  
 
  
  
END;
  
  
 
 
 

2. execute immediate can only be used to process single-row queries. to dynamically process multiple rows of data returned by the SELECT statement, you need to define the cursor variable and use the OPEN-FOR, FETCH, and CLOSE statements to complete it together. The procedure is as follows:

#1 Define the cursor variable: Because you need to use the cursor variable to dynamically process multi-row queries, You need to define the cursor variable in the definition section. The syntax is as follows:

 
 

  
  
TYPE cursortype IS REF CURSOR;  
  
  
 
  
  
                cursor_variable cursortype;  
 
 

#2 open the cursor variable: When the cursor variable is opened, the dynamic SELECT statement corresponding to the cursor variable is executed, and the query results are stored in the cursor result set. Syntax:

 
 

  
  
OPEN cursor_variable FOR dynamic_string  
  
  
 
  
  
                             [USING bind_argument];  
 
 

#3 extract data: extract data to store row data in the result set to PL/SQL variables. The syntax is as follows:

 
 

  
  
FETCH cursor_variable INTO {VAR1}; 
 
 

#4 close the cursor variable: Close the cursor variable to release the cursor result set. The syntax is as follows:

 
 

  
  
CLOSE cursor_variable; 
 
 

Example:

 
 

  
  
DECLARE
  
  
 
  
  
TYPE empcurtyp is ref cursor;
  
  
 
  
  
Emp_cv empcurtyp;
  
  
 
  
  
Emp_record emp % ROWTYPE;
  
  
 
  
  
SQL _stat VARCHAR2 (100 );
  
  
 
  
  
BEGIN
  
  
 
  
  
SQL _stat: = 'select * FROM emp WHERE deptno =: dno ';
  
  
 
  
  
OPEN emp_cv FOR SQL _stat USING & dno;
  
  
 
  
  
LOOP
  
  
 
  
  
FETCH emp_cv INTO emp_record;
  
  
 
  
  
Exit when emp_cv % NOTFOUND;
  
  
 
  
  
Dbms_output.put_line ('employee name: '| emp_record.ename |', salary: '| emp_record.sal );
  
  
 
  
  
End loop;
  
  
 
  
  
CLOSE emp_cv;
  
  
 
  
  
END;
  
  
 
 
 

The preceding section describes the usage of Oracle dynamic query statements.

Implementation of oracle query current time

Learn more about Oracle hierarchical Query

Provides you with an in-depth understanding of Oracle temporary tables

Oracle with statement usage

Common ORACLE Data Types