Saturday, April 21, 2007

Perl vs. PL/SQL

When I started learning PL/SQL, I was gratified to find that my whole Perl way of working translated easily into doing stored procedures.

Most small Perl scripts that do transformations are built around a loop like this:

@lines = ; # read entire contents of file
for $line (@lines)
{
# do some search/replace on each line
# print the result, or write to another file
}

PL/SQL has similar compact notation for doing a query and looping through the results:

for item in (select * from some_table)
loop
# the name "item" is arbitrary; that variable doesn't need to be declared
# refer to columns using the notation .
end loop;

As the stored procedures get more elaborate, you can get into different syntax like the FORALL statement for issuing lots of INSERT/UPDATE/DELETE statements, or doing BULK COLLECT to pull the full results of a query into a PL/SQL collection variable to inspect the whole thing. But the construct above is fine for most typical query / reporting scenarios.

The similarity between the 2 languages is the reason that I've never used the Perl DBI modules for database work. It's too easy to write out real SQL statements or PL/SQL blocks using a HERE document, invoke SQL*Plus using backticks, do one-liners in Perl to just call PL/SQL stored procedures... just generally hand off the database logic. I even see parallels in the way object-oriented constructs were added to both languages in ways that don't especially appeal to me. (If I want to do object-oriented programming, Ruby is the most appealing choice for me.)

2 comments:

michel said...

Hi John,

* Interesting thoughts on the Perl and PL/SQL similarities and even potential synergy without DBI.

* question : Would you know if it is possible to declare multi-line strings (i.e. HERE document equivalents) using PL/SQL ?

* Coming from a strong Perl and Python scripting background, I am new to PL/SQL and find having too many DBMS_OUTPUT.put_line() or HTP.print() calls is just killing me.

Regards,
Michel

John Russell said...

HERE documents in PL/SQL is the subject of a different post on this blog. To make a long story short, you use syntax like this (I'll show 3 slight variations):

htp.p('
Line 1
Line 2
Line 3
');

htp.p('[
Lines that have ' in them
etc.
etc.
]'); -- Can use other characters besides [ and ] as delimiters too.

htp.p('#
@somefile.htm;
#'); -- Only works in SQL*Plus because of the way @ is parsed.