oth Oracle9i and SQL Server 2000 support the use of the database as more than a simple data store for your information. They enable you to use the database to implement your business rules and processes as well with enhancements to the standard SQL language that support the more complicated SQL necessary for this feature. However, each product does so in a different way. This article compares the two approaches and determines which database engine is more suited for this role.

SQL Server and Transact-SQL
SQL Server’s enhanced version of SQL is called Transact-SQL (T-SQL). T-SQL is very much like SQL with a couple of crucial additions: it supports stored procedures and user-defined functions. These two features, as in any programming language, allow you to package your code into modular sections.

T-SQL also supports simple looping and branching constructs such as the 'goto, while' loop and 'if else' statements. In addition, SQL Server provides a library of useful functions in areas such as date and time, math, and data conversion. T-SQL also allows you to declare variables of the various SQL types and retrieve data from the database directly into these variables. You can create temporary tables (or a table variable) for storing intermediate results too.

Because T-SQL is very similar to SQL, programmers who are comfortable with SQL will find it quite easy to learn. For those who need more assistance, SQL Server comes with a very well indexed and searchable help system called Books Online that can quickly provide examples of the necessary syntax or more detailed explanations.

So how well does T-SQL do when programming systems with stored procedures? In my experience, you can support very complex and large systems this way. I currently work on several systems that consist of close to 1,000 stored procedures. To do so successfully, you need to carefully plan how to structure and modularize your code in a standard way. For example, my organization employs a standard way of writing updates, selects, and any other SQL operation to the systems I support. We also have standards for naming variables and stored procedures. Oracle and PL/SQL
Unlike T-SQL, PL/SQL, Oracle’s primary language for writing stored procedures, is a full-fledged programming language that happens to share some syntax with SQL. But as opposed to other general-purpose languages, PL/SQL is very data aware and has many features designed to ease data handling.

To gain an understanding of how PL/SQL can ease your coding, consider the following?unfortunately, common?scenario. Suppose you have a column in a table that is declared as a char (50). In all your stored procedures that reference this column and store values from it in a variable, you also declare your variables to be char (50). Now, after you’ve finished your umpteenth procedure, you find out that the column needs to be extended to a char (100). In T-SQL, you (or in fact, me during a very long afternoon session several weeks ago) are in for a lot of searching and replacing the char (50) declarations with char (100). But in PL/SQL, you can simply use what’s called an anchored declaration and avoid the problem.

An anchored declaration allows you to declare your variable as being of whatever type the particular column in the table is. For example, emp_id emp.empno%TYPE declares a variable called emp_id, which is the same type as the column empno in the emp table. If I change the column type in the table, all I need to do is recompile the dependant code (Oracle also keeps track of the dependencies for you and marks the code as “invalid”). Oracle makes coding even easier by enabling you to declare in one fell swoop all the variables needed to represent a row in a table. The emprow emp%ROWTYPE command declares a structure emprow that has the same attributes as the columns in the emp table.

The syntax for declaring a cursor to loop through each row of a result set again shows how easy PL/SQL makes working with data. The following statement implicitly declares a cursor and loops through each row of the result set:

FOR emp_cur in (select * from emp where deptid = 5)LOOP	Pay_employee(emp_cur);END LOOP;

PL/SQL also gives you additional tools for organizing large volumes of code. Rather than maintaining hundreds of separate stored procedures, you can organize related stored procedures into packages. So for example, I can have a customer package with procedures called add, delete, and update. To use a particular procedure, I simply write code like customer.add(whatever parameters) or customer.delete. This of course makes the code very readable and understandable. Packages also provide for the all-important concept of abstraction by allowing you to declare the signature of the package (the procedures and parameters) separate from the package body. So I can change the body at will without affecting the users who call those procedures, as long as I don’t change the signature. Additionally, I can declare private procedures in the package that can be called only by other procedures in the same package.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 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 <> 0Begin	Set some error message	Goto failed:EndUpdate ..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.

EXCEPTIONWHEN NO_DATA_FOUND  system exceptionTHEN	..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.Oracle Offers the Better Environment
Of course, technology changes and this applies even to something as seemingly mundane and old hat as writing stored procedures. On Oracle’s side, PL/SQL continues to be enhanced with each version. You can also now write stored procedures in Java. Microsoft has also announced that in a future version of SQL Server you will be able to create stored procedures in your .NET language of choice. So in a year or so, perhaps SQL Server will have the better environment. But for now, Oracle is the clear winner.