Nested practices of Oracle multi-layer cursors

The following articles mainly introduce the nesting of Oracle multi-layer cursors and the specific concepts of Oracle cursors, the following articles mainly describe the actual operation steps for nesting Oracle multi-layer cursors, hoping to help you in this regard.

This is not the case. A cursor is not required in the stored procedure. It has input and output parameters. If the corresponding processing is performed in the process, the output parameters are returned. The cursor mainly serves to extract data cyclically. The cursor is divided into implicit cursor and explicit cursor.
For example, explicit cursor ):

CURSOR name is select statement;
.....
WHILE cursor name % found LOOP
.....
End loop;

The SELECT statement extracts a column of values, and then obtains a record each time for the following loop.

Implicit cursor ):

For cursor name in SELECT statement) loop
.....

.....
End loop;

The SELECT statement extracts a column of values, and then retrieves a record for the following loop each time.

Oracle multi-layer cursor nesting: Generally, multi-layer nesting can be replaced by combining several tables for query, but sometimes it cannot be replaced. For example, the second value to be queried is the first value to be queried before performing the like operation.

 
 

  
  
declare  
  
  
v_0 number;  
  
  
v_1 number;  
  
  
cursor c1 is select productordernumber from his_productorder@pro_crm where productid in (9000045516);  
  
  
cursor c2 is select cust_order_id from suf_cust_order_q_his where cust_order_num like v_0||'%';  
  
  
cursor c3 is select * from suf_work_order_q_his where cust_order_id=v_1;  
  
  
begin  
  
  
for i in c1 loop  
  
  
v_0:=i.productordernumber;  
  
  
for j in c2 loop  
  
  
v_1:=j.cust_order_id;  
  
  
for k in c3 loop  
  
  
dbms_output.put_line(k.work_order_id||' '||k.status);  
  
  
end loop;  
  
  
end loop;  
  
  
end loop;   
 
 

The above content is an introduction to Oracle multi-layer cursor nesting. I hope you will get some benefits.