So I have had to program using quiet a few different databases as a backend for my web projects (Oracle, SqlServer, MySQL, SQLite, SqlAnywhere). I find that quiet often I have to write a script and I need some sort of "for loop" or "for each" loop and I end up using a cursor to get the job done.  Sure it works but I have always found the syntax of a cursor to be laborious and anything but syntactical sugar (a little bit more analogous to lemon juice).  Anyway I recently stumbled across some syntactical sugar when it comes to for loops with Oracle.

Note-  I "grew up" using TSQL and still prefer it to PL/SQL but as PL/SQL can often times be a little more complex and complexity equates to higher salaries.. I find myself using it.

Ok so enough rambling here is a script I wrote using a regular old cursor to drop all of the connections for a user with oracle.

--Kill all the Oracle connections script

DECLARE -- declare variables

  CURSOR c1 IS

  select sid, serial# from v$session

  where username='user';  -- declare cursor

-- declare record variable that represents a row fetched

   kill_it c1%ROWTYPE; -- declare variable with %ROWTYPE attribute

BEGIN

-- open the explicit cursor c1

  OPEN c1;

  LOOP

    FETCH c1 INTO kill_it; -- retrieve record

    EXIT WHEN c1%NOTFOUND;

    BEGIN

      EXECUTE IMMEDIATE

      'alter system kill session '''||kill_it.sid||', '||kill_it.serial#||'''';

    END;

  END LOOP;

  CLOSE c1;

END;

You will notice that with the cursor that you have to declare it and fetch from it with each loop, check to make sure that you actually fetched a value and then close the cursor when you are done.

Oracle Implicit Cursor how to go from ~16 to ~6 lines

BEGIN

  FOR kill_it in (select sid, serial# from v$session where username='user')

  LOOP

      EXECUTE IMMEDIATE

      'alter system kill session '''||kill_it.sid||', '||kill_it.serial#||'''';

  END LOOP;

END;

Ahh that is much better.  Writing statements like that in PL/SQL makes me feel much better.