The month-long festival of football has finally come to an end.
A tournament that was supposed to be about “No 10s” and the coronation of the host nation has lived up to expectations. Ironically, by defying them.

Where to start ? Well, it seems that goalkeepers had something to say about just who the star players were going to be.
Ochoa, Navas and Neuer were all outstanding, not to mention Tim Howard. I wonder if he could save me money on my car insurance ?
Those number 10s were also in evidence. However, in the end, it wasn’t Neymar, Messi, or even Mueller who shone brightest in the firmament. That honour belonged to one James Rodriguez, scorer of the best goal, winner of the Golden Boot, and inspiration to a thrilling Columbia side that were a bit unlucky to lose out to Brazil in a gripping Quarter Final.
Now, usually a World Cup finals will throw up the odd one-sided game. One of the smaller teams will end up on the wrong end of a good thrashing.
This tournament was no exception…apart from the fact that it was the holders, Spain, who were on the wrong-end of a 5-1 defeat by the Netherlands.
Then things got really surreal.
Brazil were taken apart by a team wearing a kit that bore more than a passing resemblence to the Queens Park Rangers away strip.
The popular terrace chant “It’s just like watching Brazil” may well require a re-think after Germany’s 7-1 win.
So, Germany (disguised as QPR) advanced to the final to play a side managed by a former Sheffield United winger.
Eventually, German style and attacking verve triumphed.
Through the course of the tournament, O Jogo Bonito seems to have metamorphosed into Das Schöne Spiel.
The stylish Germans are what provide the tenuous link to this post. I have once again been reviewing my SQL and PL/SQL coding style.
What follows is a review of some of the coding conventions I (and I’m sure, many others) have used since time immemorial with a view to presenting PL/SQL in all it’s glory – a mature, powerful, yet modern language rather than something that looks like a legacy from the pre-history of computing.

Hopefully, the changes discussed here will help my code to become more readable ( and therefore maintainable) as well as looking a bit nicer.
William Robertson (from whom I plaigarised the title for this post) has some interesting things to say on PL/SQL coding style.

In this post, I’ve attempted to focus on stylistic topics that may affect code readability rather than just well established practices that I find annoying. Believe me, it was a bit of a struggle.

What I will be looking at is :

  • Uppercase Keywords
  • The use of camel case
  • The vexed question of ANSI Join syntax

Inevitably, what follows is likely to be highly subjective so feel free to disagree vehemently.

Uppercase Keywords

I started using SQL back in 1993, when the world was black and white.
My first experiences with Oracle was coding on a terminal emulator using vi.
Sure, you could change the screen colour if you were so minded, but syntax highlighting for PL/SQL was the stuff of science fiction.
The accepted way of distinguishing keywords was therefore, to type them in upper case.

--
-- Old style...
-- Keywords and built in packages are in uppercase.
-- brackets in the cursor for loop are not aligned, but the identation is consistent
--
DECLARE
    l_message VARCHAR2(50) := 'Happy New Millenium!';
BEGIN
    FOR r_emps IN (  
        SELECT first_name,  
        FROM hr.employees) 
    LOOP
        DBMS_OUTPUT.PUT_LINE('Message for '||r_emps.first_name||' - '||l_message);
    END LOOP;
END;
/

Needless to say, things have changed a bit since then. Not only do you have multiple Oracle IDEs to choose from, all but the most basic text editors will have syntax highlighting as standard. Of course many of them will have built-in highlighting for SQL rather than PL/SQL, but many such as Textpad and Gedit can easily be customised to suit.

One additional tweak to the next code example is that I’ve aligned the brackets in the way that you’d expect to see in other 3GLs. Apart from making bracket matching a bit easier, I think it looks a bit nicer…

--
-- All keywords and built-in packages are lowercase.
-- Syntax highlighting means that the keywords are still distinguishable...
--
declare
    l_message varchar2(50) := 'Happy New Millenium!';
begin
    for r_emps in
    (
        select first_name
        from hr.employees
    ) 
    loop
        dbms_output.put_line('Message for '||r_emps.first_name||' - '||l_message);
    end loop;
end;
/

…OK, WordPress doesn’t really do PL/SQL, so I’ll try to illustrate with a screenshot from Gedit :

My code in glorious technicolour

My code in glorious technicolour

We could make this code look even more funky and modern if, for example, we were to consider using…

Camel Case

Camel Case has never really gained wide acceptance in Oracle programming circles. I would suggest that the main reason for this is that Oracle stores object names, column names etc in the data dictionary in UPPERCASE.
Therefore, if you were to create a table with the following script…

Create Table Regions
(
    regionId Number,
    regionName Varchar2(25),
    longRegionDescription Varchar2(4000)
)
/

You may well be a bit surprised by the result of the following query…

select column_name, data_type
from user_tab_columns
where table_name = 'Regions'
/

no rows selected

Yes, in order to find the column details we need to specify the table name in upper case…

select column_name, data_type
from user_tab_columns
where table_name = 'REGIONS'
/

COLUMN_NAME                    DATA_TYPE
------------------------------ ------------------------------
REGIONID                       NUMBER
REGIONNAME                     VARCHAR2
LONGREGIONDESCRIPTION          VARCHAR2

Unless you want to go down the long and winding road of quoted identifiers, the underscore remains – in my opinion at least – the best way of separating words in identifiers.

Hang on a second, that’s probably fair enough as far as object names go, but what about variables ?

At this point, I have a confession to make. If I were to see something like this…

Declare
    lMessage varchar2(50) := 'Happy New Millenium!';
Begin
    For rEmps In
    (
        select first_name
        from hr.employees
    ) 
    Loop
        dbms_output.put_line('Message for '||rEmps.first_name||' - '||lMessage);
    End Loop;
End;
/

…my first reaction would be to assume that it was written by someone who normally codes in a language other than PL/SQL. Of course, this says rather more about me than the author of the code or, indeed, the code itself.

My justification for persisting with underscores in variable names can be reduced to two rather small fig leaves.

First of all, when declaring a variable for use to compare to a column value, I tend to keep to the name of the target column. For example :

create or replace function get_employee_name_fn
( 
    i_employee_id employees.employee_id%type
)
    return varchar2	
as
    l_last_name varchar2(100);
begin
    select last_name
    into l_last_name
    from employees
    where employee_id = i_employee_id;
    return l_last_name;
end;
/

The second is that, If I’m using underscores for some variables, it would look a bit odd if I then used Camel Case for others.

In an attempt to rid you of the unfortunate mental image you may have acquired with the fig leaves reference, I’ll move swiftly on to something that seems to ellicit strong opinions in the Oracle world…

ANSI Join syntax

In the vast majority of cases, if you’re writing PL/SQL then you will, at some point, need to write some SQL.

Back in Oracle 6, there was no Cost Based Optimizer. For the first several years of my using Oracle, tuning of SQL was done by understanding the 14 rules applied by the Rule Base Optimizer.
One key point was that a query was evaluated from the bottom up, so the ordering of the predicate was important.
For this reason, it became common practice to specify join conditions first, ahead of other, more restrictive conditions.
Of course, in those days, ANSI syntax was not available in Oracle, but it didn’t matter too much as you could be reasonably certain of where to look for join conditions in SQL. For example :

select dept.department_name, coun.country_name, 
    emp.last_name as manager
from departments dept, employees emp, locations loc,
    countries coun, regions reg
where dept.manager_id = emp.employee_id
and dept.location_id = loc.location_id
and loc.country_id = coun.country_id
and coun.region_id = reg.region_id
and coun.country_name != 'Switzerland'
and reg.region_name = 'Europe'
order by coun.country_name, dept.department_name
/

In the period of time between the Cost Based Optimizer becoming de rigeur and the bugs in the ANSI syntax being ironed out in Oracle, a less structured coding style seems to have become prevalant.
Join conditions can now appear anywhere in the predicate list without any impact on performance…

select dept.department_name, coun.country_name, 
    emp.last_name as manager
from departments dept, employees emp, locations loc,
    countries coun, regions reg
where dept.manager_id = emp.employee_id
and reg.region_name = 'Europe'
and dept.location_id = loc.location_id
and coun.region_id = reg.region_id
and coun.country_name != 'Switzerland'
and loc.country_id = coun.country_id
order by coun.country_name, dept.department_name
/

Inner Joins – ANSI Style

The ANSI syntax offers a solution to this by enforcing the separation of join conditions into a discreet clause in the query :

select dept.department_name, coun.country_name, 
    emp.last_name as manager
from departments dept
join employees emp
    on dept.manager_id = emp.employee_id
join locations loc
    on dept.location_id = loc.location_id
join countries coun
    on loc.country_id = coun.country_id
join regions reg
    on coun.region_id = reg.region_id
where reg.region_name = 'Europe'
and coun.country_name != 'Switzerland'
order by coun.country_name, dept.department_name
/

In terms of readability, unless you’re an old fogey like me, this would seem to be a significant improvement.

The ANSI syntax offers several variations on the theme of Inner Join. You can explicitly use the INNER keyword with your inner joins, although this would seem to be a bit superfluous as other join types would have to be stated explicitly in the code.

As well as the tradional…

select dept.department_name, emp.last_name
from departments dept
inner join employees emp
    on dept.department_id = emp.department_id
/

…you could specify a join between tables that have the same column name like this :

select dept.department_name, emp.last_name
from departments dept
inner join employees emp
    using(department_id)
/

Slightly less appealing is the NATURAL JOIN syntax, which assumes that you want to join on any and all matching column names between two tables.
If you run either of the previous two queries in the HR schema, they will return 106 rows.
However, the following query returns only 32 rows…

select dept.department_name, emp.last_name
from departments dept
natural join employees emp
/

As well as DEPARTMENT_ID, the tables also both contain a MANAGER_ID column. The above statement is therefore not, as you might think, the equivalent of the previous two, but is rather more …

select dept.department_name, emp.last_name
from departments dept
join employees emp
    using( department_id, manager_id)
/

With this in mind, I think I’ll steer clear of having my joins au naturel.

Outer Joins

This is one area where the ANSI syntax has a distinct advantage over it’s more venerable Oracle counterpart.
I’ve previously explored how it allows you to outer join a table multiple times in the same query.

Apart from this, it’s main distinguishing feature over the more traditional Oracle syntax is it’s sense of direction.

For a left outer join, tables are read left-to-right – i.e. the table you’re outer joining to is specified second :

select dept.department_name, 
    count(emp.employee_id) as "Employee Count"
from departments dept
left outer join employees emp
    using( department_id)
group by dept.department_name
order by 2 desc, dept.department_name
/

For a right outer join, the reverse is true, the table specified first is the one you’re outer joining to :

select dept.department_name, 
    count(emp.employee_id) as "Employee Count"
from employees emp
right outer join departments dept
    using( department_id)
group by dept.department_name
order by 2 desc, dept.department_name
/

Whilst having to know your left from right is something new to contend with, either of the above examples is more readable to someone familiar with SQL ( although not necessarily Oracle) than :

select dept.department_name, 
    count(emp.employee_id) as "Employee Count"
from departments dept, employees emp
where dept.department_id = emp.department_id(+)
group by dept.department_name
order by 2 desc, dept.department_name
/

In the past, there have been one or two issues with Oracle’s implementation of the ANSI join syntax. However, it now seems to be fairly stable and consistent.
Add this to it’s undoubted advantage in terms of readability and I’ve really rather run out of excuses not to use it.


Filed under: Oracle, PL/SQL, SQL Tagged: ansi join syntax, camel case, inner join, join using, LEFT OUTER JOIN, natural join, right outer join, uppercase keywords