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