PL/SQL Versus T-SQL
As you'd expect in a full-fledged programming language, PL/SQL also provides a full set of loop constructs including a for loop, a while loop, repeat until, and of course the simple
statement. It also includes advanced features such as arrays and collections. I also particularly like its error-handling mechanism in comparison to T-SQL's. In T-SQL, you have to test a global variable called
explicitly after every SQL statement that might cause an error. It also doesn't provide a way of declaring a common body of code that should handle any errors found. So a typical procedure has numerous lines of code that look like this:
If @@error <> 0
Set some error message
Set some error message
The repeated explicit error handling becomes tedious and clutters up the code. What's worse is that because of the burden of coding the error-handling logic, many times it is simply left out. In PL/SQL, however, you simply declare an EXCEPTION section in your procedure. When an error occurs, Oracle will transfer control to that section of the code and continue from there. You can also declare your own exceptions based on your business logic. So a typical exception section might look like this.
WHEN NO_DATA_FOUND system exception
WHEN invalid_emp_id user-defined exception
I hope by now you agree that PL/SQL provides a much better environment for implementing your business rules using stored procedures than T-SQL does. If you plan to build a system that relies heavily on large amounts of stored procedures, you definitely should consider Oracle over SQL Server.
However, all this additional sophistication comes at a cost. PL/SQL definitely has a longer learning curve than T-SQL. Luckily, many good books are available on the subject. I personally have found Learning Oracle PL/SQL by Bill Pribyl with Steven Feuerstein a great introductory book, and Oracle PL/SQL Programming by the same authors offers some more advanced study.
Additionally, if you are serious about PL/SQL you should obtain a better editor than SQL*Plus (the one that comes with Oracle). Many editors are on the market. I personally use OraEdit Pro from DKG Advanced Solutions. It's relatively cheap (only $99) and it includes Intellisense, which not even Query Analyzer (Microsoft's rather superb built-in editor) has yet.