Stored Procedures A Comparison of PL/SQL and Transact SQL by William Ryan

You have probably heard to old adage "If you know one programming language, you know them all."  While it's certainly an oversimplification, the spirit of it is probably quite true, after all, many langauge features are similar regardless of what you are coding in.  In many regards, the same can be said of using a Relational Database.  This article is the first in a series I'll be writing illustrating how similar SQL Server's Transact SQL (T-SQL) and Oracle's PL/SQL really are.

If are reading this article, you're probably already familiar with Structured Query Language aka SQL.  Since the official SQL Spec is an ANSI standard, a thorough knowledge of SQL ports across any database implementation that supports it (which comprises just about every major database on the market). Vendors may do a little freestyling here and there, but you can take your SQL skillset and work on SQL Server, Oracle, Access, Sybase etc.  However, SQL is based on the premise of What not How.  With traditional programming languages, developers define what is going to happen and how it's going to happen.  If I have a function called Sum for instance, that takes two integers and adds them together, it's incumbent upon me to implement this.  If instead of adding the values, I multiply them, then SUM will return the product of the two values instead of their sum.  SQL on the other hand provides a dialect wherein I tell the databsae what I want, and provided I abide by its syntactial rules, and it's the databases responsibility to figure out how to retrieve the data.  Whereas I can implement my SUM Function however I please, I have no control (well, really limited control) over the implementation of SELECT * FROM SomeTable. This nuance makes SQL much different from just about any other language in modern programming.  And while it provides a very straigthforward mechanism for data retrieval, it also has some limitations.  For instance, just about any programming language these days supports For Loops.  However, there's no such construct in SQL.  The same holds for While Loops and a whole host of other features.  To fill this gap, most database vendors provide their own flavor of a Structured version of SQL, one with Loops, variable declarations, Functions and the like.  Two of the more popular implementations are Microsoft's T-SQL and Oracle's PL/SQL.  In short, SELECT * From SomeTable will work with Oracle and SQL Server alike provided there's a database object named SomeTable.  However, if you want to do anything more advanced than what SQL provides, you'll need to dig into the vendor's personal flavor of programmable SQL.

Let's look at a few examples.  For the sake of this argument, let's assume that we have a table in both Oracle and SQL Server named SomeTable.  SomeTable has three fields in it, KeyField, FirstField and SecondField.  Let's say that we want to create a Stored Procedure in each language that returns all of the values of SomeTable:

T-SQL:

CREATE PROCEDURE usp_GetAllFromSomeTable AS     SELECT * FROM SomeTable GO

PL/SQL:

CREATE PROCEDURE PROC_GetAllFromSomeTable IS BEGIN    SELECT * FROM SomeTable; END /

These two procedures are almost identical.  The first difference is the use of the word "AS" vs. "IS" but this is a trivial distinction because you can actually use "AS" in PL/SQL.  The next difference is the use of BEGIN and END.  A valid block of code in PL/SQL must contain BEGIN and END which isn't necessary in T-SQL (although it's available for any block and necessary in certain code blocks.  PL/SQL has a C like syntax in many regards and lines are ended with the semicolon (;) character.  Finally, the forward slash (/) character ends a block.

Ok, let's delete everything from both tables using the same stored procedures:

T-SQL:

CREATE PROCEDURE usp_DeleteData AS    DELETE FROM SomeTable; GO

PL/SQL:

CREATE PROCEDURE PROC_DeleteData IS BEGIN    DELETE FROM SomeTable;    COMMIT; END /

Again, the differences are pretty simple and the main distinction is the use of COMMIT.  In SQL Server you can wrap blocks in Transactions and use Rollbacks.

The next big difference is in the actual declaration.  Both of the procedures above assume that no database object exists named usp_DeleteData or PROC_DeleteData.  If either procedure existed in the respective database, an exception would be raised.  To safeguard from this problem, you need to check for the existence of the object (which is a procedure in this case) first.  The difference here is pretty much different.  In T-SQL, you need to query the system catalogs using an Exists Statement

T-SQL:

IF EXISTS (SELECT * FROM sysobjects WHERE name='SomeTable' and type='P') DROP Procedure usp_SomeProcedure

PL/SQL:

CREATE OR REPLACE PROCEDURE PROC_SomeProcedure

This is one of the places that I like PL/SQL's syntax a little more (while it's intuitive that a Stored Procedure would have type 'P' in SQL Server, tables have the type 'U' which  isn't exactly intuitive.  Sure you'll remember it after you've done it once or twice, but the PL/SQL syntax seems a little cleaner).

Variable declaration is pretty much similar in both languages, but let's walk through an example because the difference is a little more notable when it comes to default parameters.

T-SQL:

CREATE PROCEDURE usp_SomeProcedure     @MyFirstVariable           DATETIME    ,@MySecondVariable          SMALLDATETIME    ,@MyThirdVariable           VARCHAR (50) AS    SELECT *     FROM SomeTable      WHERE FirstField = @MyFirstVariable GO

Note that to declare a variable in T-SQL, you begin with the Prefix "@" and you specify the datatype afterward.  With some variable types like DATETIME and SMALLDATETIME you can't specify a precision.  On the other hand you can specify a maxmimum size with VARCHAR for instance.  Note that VARCHAR in T-SQL is analogous to VARCHAR2 in PL/SQL.

PL/SQL:

CRATE PROCEDURE PROC_SomeProcedure (      P_MyFirstVariable             IN DATETIME      , p_MySecondVariable          IN DATETIME      , p_MyThirdVariable           IN VARCHAR ) IS   SELECT *     FROM SomeTable        WHERE FirstField = p_MyFirstVariable; /

Notice that the variable declarations are enclosed in parenthesis in PL/SQL which is different from T-SQL.  I'm also specifying that these are Input parameters denoted by the IN keyword.  Similarly, I could use OUT or IN OUT, which I'll address in the next article.  IN or Input Parametes are the default types in both PL/SQL and T-SQL, but I used it to highlight the syntactical differences.

One of the really cool features in PL/SQL is the %Type specification (and it's companion rowtype).  Let's say that in the above block, the Database administrator decided that p_MyThirdVariable needed to be changed to a DATETIME type.  As it stands, the code may break.  However, if we changed the declaration to

...   , p_MyThirdValue            IN SomeTable.ThirdField%Type

p_MyThirdValue will map to whatever type SomeTable's ThirdField column is.  Now, in ADO.NET, where you will probably be specifying your parameter types, you may still have problems, as far as a language feature goes, this is pretty cool and I don't know of an equvialent syntax in T-SQL

Another difference is with Default Parameters. My  Article here shows you how to do this in T-SQL and why it's a good idea to do so.  In PL/SQL, it's essentially the same, the just use the literal 'DEFAULT' and supply the value:

PL/SQL:

CRATE PROCEDURE PROC_SomeProcedure (      p_MyFirstVariable             IN DATE   DEFAULT SYSDATE ) IS

I used SYSDATE as the default value, and SYSDATE roughly corresponds to T-SQL's GETDATE()  function.  That's not really important, you could supply any valid date value  Basically, all you need to do is use the literal DEFAULT and then provide the value (or use a function that returns a value). So if it was a VARCHAR2 value, the following would set the default value of ThirdField to "www.knowdotnet.com"

p_MyThirdVariable     VARCHAR2 DEFAULT 'www.knowdotnet.com';

Both languages also support both positional parameters and named parameters.  In my article above, I discuss how to do this in T-SQL and ADO.NET, so let me show you how to do this in PL/SQL

If I called this line:

PROC_SomeProc ('01/10/2003', '12/12/2003', 'www.devbuzz.com');

The parameters would come in in their respective positions.  Hence, '01/10/2003' would map to p_MyFirstValue and 'www.devbuzz.com' would map to p_MyThirdVariable.

However, I may want to use named parameters because I forgot what position they appeared in (If I remember the variable names it's doubtful I'd forget the positions, but my point is simply to illustrate how this works).

PROC_SomeProc(p_MyThirdVariable => 'www.devbuzz.com' , p_SeconedValue => '10/02/2003', p_FirstValue => SYSDATE);

Notice that I've switched there positions around (except for p_SecondValue) and used the => annotation to indicate that this is a named value.  It's worth noting that you can mix the types, but just like most programming languages that support named parameters, all unnamed parameters must precede the named ones, and must appear in their correct order.  As such, if I used this:

PROC_SomeProc('www.devbuzz.com, p_SecondValue => '10/02/2003', Sysdate);

Oracle would take issue with it.

This should give us a good foundation for my next article which will focus on more advanced scenarios using input and output parameters.  My goal is to walk through the foundation principles in both langauges, and ultimately show you how to implement some rather advanced scenarios juxtaposing the nuances of both languages.