Reese Knowledgebase

Querying Data with PL/SQL: Implicit Cursor FOR Loop

View Kristian Reese's profile on LinkedIn


If you like this article, please +1 or Recommend via FB with the provided buttons above:

Article ID: 112
by: Reese K.
Posted: 28 Jun, 2013
Last updated: 01 Jul, 2013
Views: 1289

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>

External links
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqloperations.htm
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1968769482859

Prev   Next
VMware     MySQL

RSS