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.