The power of ORACLE’s FOR IN logic


Oracle’s for in loop can be effectively used in simple SQL queries instead of writing cursors for SELECT queries.

Today I coded such a FOR loop for DROPing around 70 tables generated with a BUILD tool by a mistake (table names starting with BS_) .

Now I want to explain it with a basic example;

Here is the syntax
FOR reference IN ( SQL resultset ) –Here we can reference any rows inside Resultset
LOOP
END LOOP;

First example will show you increasing the salary  (+500) of all employees working as an IT programmer except

Alexander Hunold

SELECT employee_id , first_name, last_name , job_id , salary
FROMemployees
WHERE job_id = ‘IT_PROG’

EMPLOYEE_ID FIRST_NAME   LAST_NAME    JOB_ID   SALARY
———————- ——————– ————————- ———- ———————-
103                     Alexander          Hunold                 IT_PROG  9000
104                    Bruce                    Ernst                    IT_PROG  6000
105                    David                   Austin                  IT_PROG  4800
106                    Valli                    Pataballa             IT_PROG  4800
107                   Diana                  Lorentz                 IT_PROG   4200

5 rows selected

–after this we wrote a FOR IN LOOP like below

BEGIN
FOR IT_PROGRAMMERS IN
(
SELECT employee_id , first_name, last_name , job_id , salary
FROMemployees
WHERE job_id = ‘IT_PROG’
— here we should take a resultset and reference all columns with IT_PROGRAMMERS reference
)
LOOP
IF NOT (IT_PROGRAMMERS.employee_id = 103) THEN –I mean the employee is not Alexander Hunold
UPDATE employees
SET employees.salary = employees.salary + 500 –increase it 500 $ good money
WHERE employees.employee_id = IT_PROGRAMMERS.employee_id; –reference from the original resultset

END IF;
END LOOP;
COMMIT;
END;

SELECT employee_id , first_name, last_name , job_id , salary
FROMemployees
WHERE job_id = ‘IT_PROG’;

EMPLOYEE_ID   FIRST_NAME  LAST_NAME       JOB_ID  SALARY
———————- ——————– ————————- ———- ———————-
103                       Alexander            Hunold                IT_PROG     9000
104                       Bruce                     Ernst                    IT_PROG     6500
105                       David                    Austin                  IT_PROG     5300
106                       Valli                     Pataballa             IT_PROG      5300
107                       Diana                    Lorentz                IT_PROG     4700

5 rows selected

–yes it works

Here is an another handy example could be used it for dropping around 70 tables in the database

DECLARE
countval NUMBER:=0; –use it know how many drop operation would be perform
sqlString VARCHAR(100); — dynamic sqlString to produce sqlString on the fly
BEGIN

FOR alltables IN
( SELECT table_name FROM user_tables — I got all user table names from this resultset
)
LOOP
———————–LOOPING BETWEEN THESE LINES—->LOOP STARTS

IF(alltables.table_name LIKE ‘BS_%’) THEN

— reference it with all tables’ name and trying to learn if there is a BS_ in front of table names
BEGIN

countval := countval + 1;
sqlString := ‘DROP TABLE ‘ || alltables.table_name || ‘ CASCADE CONSTRAINTS’ ;

— using cascade contraints to escape from contraints exceptions
EXECUTE IMMEDIATE ”|| sqlString || ”; — executing dynamic sql here

END;
END IF;
———————–LOOPING BETWEEN THESE LINES—->LOOP ENDS

END LOOP;

dbms_output.put_line( countval || ‘ TABLE(S) IS DROPED!’); — how many tables were dropped!
END;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s