Know Dot Net Article

A Brief Intro to PL/SQL

by William Ryan

My first days in programming started off with Oracle v 7x and I have to admit, it's my first love.  For the last two years I've been either administrating and/or programming SQL Server (which I also love) but I've gotten a little homesick lately.  Let me run through a few of the basics of programming Oracle.

As you may be aware, SQL is a wonderful language but it's , well, not the most modern thing.  The first implementation was created before I was born and pretty much the same as it was then.  If I sound like I'm giving SQL short shrift, I'm not.  On the contrary, many things are classics like SQL, the Porsche 911 and Jaguar X series come to mind.  They look pretty much like they did 30 years ago, and they are still way cool.  The same holds for SQL.  However, SQL lacks many constructs that have become staples of modern programming and every real database implements its own flavor of SQL.  For Oracle, that's PL/SQL (Procedural Language/Structured Query Language).

For most basic queries, you can get away with good ol' SQL, but I'll walk you through a few other examples.

Scenario 1 (Basic Select Statement).

SELECT FirstValue, SecondValue
         FROM myTable
           Where Condition1 = 'Whatever'
              AND Condition2 = 'SomethingElse'
                OR Condition3 = 'SomethingTotallyDifferent'

As you may notice, there's nothing different here and provided that the object names are the same, this query would work with most RDBMS systems.

Now, if you want to declare a Parameterized SQL Statement, the same query would look like this:

DECLARE v_Whatever                NUMBER(4);
DECLARE v_SomethingElse         VARCHAR2(50);

BEGIN
      SELECT FirstValue, SecondValue
         FROM myTable
           Where Condition1 = v_Whatever
             AND Condition2 = v_SomethingElse;
END;

What's the difference?  In the first query, we were looking for the literals 'Whatever', 'SomethingElse', 'SomethingTotallyDifferent'.  In the second query, we were using variables which could have any value that conforms to the data type.

Now, let's say we wanted to load the variables with something from the Database so we could use it.  We'd make the following modifications:

DECLARE v_Whatever                NUMBER(4);
DECLARE v_SomethingElse         VARCHAR2(50);

BEGIN
      SELECT FirstValue, SecondValue
        INTO v_Whatever, v_SomethingElse
         FROM myTable
          WHERE Condition1 = 'Whatever';
END;

Now, let's say we wanted to mix it up:

DECLARE v_Whatever                NUMBER(4);
DECLARE v_SomethingElse         VARCHAR2(50);

BEGIN
      SELECT FirstValue, SecondValue
       INTO v_SomethingElse
         FROM myTable
           Where Condition1 = v_Whatever;
END;

SCENARIO 2:  Basic Insert Statement

Let's say we want to use PL/SQL to insert a few variables into a table.

DECLARE v_Whatever                NUMBER(4);
DECLARE v_SomethingElse         VARCHAR2(50);

BEGIN
     INSERT INTO           myTable(Column1, Column2)
     VALUES                    (v_Whatever, v_SomethingElse);
END;

SCENARIO 3:  Basic Update Statement

DECLARE v_Whatever                NUMBER(4);
DECLARE v_SomethingElse         VARCHAR2(50);
DECLARE v_SomeEvalValue       NUMBER(2) ;

BEGIN
    UPDATE       myTable
    SET               myFirstColumn = v_Whatever
                        , mySecondColumn = v_SomethingElse
     WHERE       someField = v_SomeEvalValue;
END;

SCENARIO 4:  Deleting Data

DECLARE v_SomeEvalValue       NUMBER(2)

BEGIN
    DELETE FROM        myTable
       WHERE       someField = v_SomeEvalValue;
END;

Well, these are the basics of PL/SQL.  In my next article, I'll discuss PL/SQL Packages which are IMHO, the coolest feature Oracle has (well, one of them anyway.