Most small Perl scripts that do transformations are built around a loop like this:
@lines =
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:
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
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.
Post a Comment