Learning notes-oracle-pl/sql-Dynamic Cursors

Classification of dynamic cursors:

A strong type: With a return statement, you can only open query statements with the same query result as the Ruturn type.

Two weak types: no Rerurn statements. Can be opened for any query statement.

--Steps to use dynamic cursors

1: Defines the ref dynamic cursor type. 2: Declares a ref dynamic cursor. 3: Open ref dynamic cursor. 4: Close cursor

1: Defining a ref dynamic cursor

Syntax: type〈 type name 〉is REF CURSOR

[Return < return type;]--If the return type is defined, the type must be the record type.

2: Declaring a ref dynamic cursor

Syntax:< cursor name > < cursor type >;--cursor type must be a custom ref type

3: Open ref dynamic cursor

Syntax: Open〈 cursor name 〉for〈 query statement;--the query statement needs to be determined when the cursor is opened.

4: Close cursor

Slose〈 cursor name);

--------example, usage of dynamic cursors

--dynamic cursor, strongly typed cursor, defines the data table to be bound by the cursor
DECLARE
TYPE ref_cur_emp is ref CURSOR RETURN Emp%rowtype; --Declaring a REF CURSOR type
Rc_emp ref_cur_emp; --Declaring a cursor variable
Emp_row Emp%rowtype;
BEGIN
Open Rc_emp for--opens the cursor and binds the statement to be queried
SELECT * from EMP;
LOOP
FETCH rc_emp into Emp_row; --Extracting cursors
EXIT when Rc_emp%notfound;
Dbms_output.put_line (rc_emp%rowcount| | ' ' | | emp_row.empno| | ' ' | | emp_row.ename| | ' ' | | Emp_row.sal);
END LOOP;
CLOSE rc_emp; --Close cursor
END;

--------------------------------------------

--Dynamic cursors, weakly typed cursors
DECLARE
TYPE ref_cur is ref CURSOR; --Declaring a REF CURSOR type
Rc_emp ref_cur; --Declaring a cursor variable
Emp_row Emp%rowtype;
V_no Emp.empno%type;
V_name Emp.ename%type;
V_sal Emp.sal%type;
V_dname Dept.dname%type;
BEGIN
Open Rc_emp for--opens the cursor and binds the statement to be queried
SELECT * from EMP;
Dbms_output.put_line (' Employee Information Form! ');
LOOP
FETCH rc_emp into Emp_row; --Extracting cursors
EXIT when Rc_emp%notfound;
Dbms_output.put_line (rc_emp%rowcount| | ' ' | | emp_row.empno| | ' ' | | emp_row.ename| | ' ' | | Emp_row.sal);
END LOOP;
CLOSE rc_emp; --Close cursor

--corresponding to multiple query statements
Dbms_output.put_line (' Employee Department information Form! ');
OPEN rc_emp for SELECT e.empno,e.ename,e.sal,d.dname
From EMP e,dept d WHERE e.deptno = D.deptno;
LOOP
FETCH rc_emp into V_no,v_name,v_sal,v_dname;
EXIT when Rc_emp%notfound;
Dbms_output.put_line (rc_emp%rowcount| | ' ' | | v_no| | ' ' | | v_name| | ' ' | | v_sal| | ' ' | | V_dname);
END LOOP;
END;

-----1 Display cursors are typically used to loop through the result set, enabling the FOM cursor to use the FOM cursor

2 implicit cursors are generally used to determine whether DML statements are executed successfully

3 dynamic cursors are often used in conjunction with stored procedures, such as queries that require dynamic changes.

Learning notes-oracle-pl/sql-Dynamic Cursors