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
SQL> 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> 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 PROD_PKG_V1.migrateCustInDb ( ' || 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>
