SQL Statements

I think I have finally recovered from studying and taking the two exams required to get Oracle certified. Now I am slowly putting away all my training materials. I still have not done anything with the sorry self study software I bought from Oracle. Here are some things I learned from instructor led training for the exams.

You would be surprised where you can stick a SQL statement in Oracle PL/SQL. When you do a FOR loop with a cursor, you have usually defined the cursor already. However you can put the SQL statement right in the FOR statement. This is creating an anonymous explicit cursor on the fly.

You can also put a SELECT statement right in a part of the WHERE clause. The result of that SELECT statement is an inline view that Oracle uses to limit the rows of your main query.

I could write a whole book chapter on the subject of locking, and how it works in Oracle. However I will just mention it briefly here. There are two ends of the spectrum with regards to how you handle locking. Optimistic locking is where you lock only 1 row at a time, and assume all your locks shall work. On the other hand there is pessimistic locking where you lock all the rows you need to update up front. Then if this lock succeeds, you know you will be able to update all the rows without contention.