|
|
A Brief Intro to PL/SQL |   | 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. |
|
