Querying Data with PL/SQL: Implicit Cursor FOR Loop

Looping through result sets from SELECT statement using PL/SQL

One day, I was looking to update a bunch of records with a stored procedure via a for loop using PL/SQL. In order to accomplish this, I used an Implicit Cursor FOR Loop. Here is my example:

BEGIN  
FOR item IN  
 (select cust_id from products where server_id = 1542 and cust_status in (10,11))  
LOOP  
 PROD_PKG_V1.migrateCustInDb ( item.cust_id , 2783 );  
END LOOP;  
END;  

PL/SQL procedure successfully completed.

If the desire is to display the output before committing execution of the stored procedure, DBMS_OUTPUT.PUT_LINE can be used. You may have to SET SERVEROUTPUT ON FORMAT WRAPPED from the SQL prompt for DBMS_OUTPUT.PUT_LINE to actually display output to the console. In the example below, I used ed to edit the previous example. In order for ed to work, ensure that export EDITOR=vi is set it your oracle profile

ed  
~  
~  
"afiedt.buf" 8L, 189C written  
  1  BEGIN  
  2  FOR item IN  
  3  (select cust_id from products where server_id = 2783 and cust_status in (10,11))  
  4  LOOP  
  5  DBMS_OUTPUT.PUT_LINE('cust_id = ' || item.cust_id);  
  6  END LOOP;  
  7* END;  
SQL></span> <span style="font-family: Courier New;"><span style="font-family: Courier New;">SET SERVEROUTPUT ON FORMAT WRAPPED  
SQL> /  

cust_id = WN.HP.251736914  
cust_id = WN.HP.252888120  
cust_id = WN.HP.255425860  
cust_id = WN.HP.255721892  
cust_id = WN.HP.255919690  

PL/SQL procedure successfully completed.  

SQL>

Or to output the SQL / stored procedure statement for copy/paste into a shell script or what-have-you:

DECLARE  
 updpkgsql VARCHAR(1000);  
BEGIN  
FOR item IN  
(select cust_id from products where server_id = 1542 and cust_status in (10,11))  
LOOP  
updpkgsql:='exec</span> <span style="font-family: Courier New;"><span style="font-family: Courier New;">PROD_PKG_V1.migrateCustInDb</span> ( ' || item.cust_id || ', 2783 );';  
DBMS_OUTPUT.PUT_LINE('sql = ' || updpkgsql);  
END LOOP;  
END;  

sql = exec PROD_PKG_V1.migrateCustInDb ( WN.HP.251736914, 2783 );  
sql = exec PROD_PKG_V1.migrateCustInDb ( WN.HP.252888120, 2783 );  
sql = exec PROD_PKG_V1.migrateCustInDb ( WN.HP.255425860, 2783 );  
sql = exec PROD_PKG_V1.migrateCustInDb ( WN.HP.255721892, 2783 );  
sql = exec PROD_PKG_V1.migrateCustInDb ( WN.HP.255919690, 2783 ); 

PL/SQL procedure successfully completed.  

SQL>