Browse DevX
Sign up for e-mail newsletters from DevX


Oracle Has SQL Server Beat in Stored Procedure Programming : Page 3

Oracle9i and SQL Server 2000 add enhancements to the standard SQL language that enable you to use the database to implement your business rules and processes—SQL Server with Transact-SQL and Oracle with PL/SQL. Find out why the Oracle database engine is better suited for this role.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 goto 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 @@error 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:

Insert . If @@error <> 0 Begin Set some error message Goto failed: End Update .. Begin Set some error message Goto failed: End

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.

EXCEPTION WHEN NO_DATA_FOUND system exception THEN .. WHEN invalid_emp_id user-defined exception THEN .

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date