 |
|
EnterpriseDB: PL/PERL
Oracle Tips by
Burleson
|
Perl is
a C-like language developed by Larry Wall. It is used in
utilities and applications in every operating system available.
It is arguably one of the most popular languages even if it is less
visible than some others are. Most flavors of Linux come with
Perl pre-installed. For Windows, you can download ActivePERL
from ActiveState.com.
In the
Chapter on Oracle Compatibility (Chapter 8), I will show you how to
use Perl to expand your options for using file input and output in
EnterpriseDB. In this chapter, I will introduce you to the
EnterpriseDB implementation of Perl.
Below is
a function that provides the same basic functionality as the example
that I have been using.
CREATE OR REPLACE FUNCTION get_emp_names_perl(integer)
RETURNS TEXT AS $$
$query = "SELECT ename FROM emp WHERE empno = $_[0]";
$cursor = spi_exec_query($query, 1);
$foo = $cursor->{rows}[0]->{ename};
return $foo;
$$ LANGUAGE plperlu;
The
spi_exec_query call accepts a string parameter that defines a valid
query. The second parameter is a count of the max number of rows
to return. In this case I wanted only a single row.
In
PL/Perl, spi_exec_query returns a zero-based array of array row values
(that's the $cursor variable). I retrieved the single ename
value from row 1 by accessing the array at offset 0. I pointed
(->) to the array, which pointed to row array ({row}) at offset 0 and
then pointed to the ename value.
When
run, this function works exactly like the examples above:
SELECT get_emp_names_perl(7788);
get_emp_names_perl
-------------------
SCOTT
(1 row)
SELECT get_emp_names_perl(1);
get_emp_names_perl
-------------------
(1 row)
Conclusion
This
chapter covered a lot of territory. It began with an
introduction describing how EnterpriseDB's SPL is compatible with
Oracle's PL/SQL.
I
covered the block structure of the code, comments, and variables, both
simple and complex. There was detail on scalar variables and
composite variables and detailed examples showing how to declare and
use those.
The
control structures that were covered include the conditionals, IF and
CASE and the flow control examples include LOOP, WHILE and FOR.
A
critical piece to understanding and using SPL includes the ability to
effectively use SQL embedded in your code. To assist with that,
this chapter also included many examples of static and dynamic SQL.
EnterpriseDB SPL provides full cursor support and provides Oracle
compatible ref cursors. Ref cursors can be used to pass a
pointer to a query from one procedure to another.
While
not as robust as PL/SQL exception handling, SPL does provide Oracle
compatibility with the procedure raise_application_error. SPL
also provides a range of named exceptions.
Programming SPL is at its finest when properly utilizing packages.
In this chapter I showed how to use procedures and functions within,
and external to, packages. I recommend that all code be wrapped
in a package.
Triggers
in EnterpriseDB can be written in SPL and I recommend that you do so.
SPL triggers are very easy to read and maintain. This chapter
showed you how to define triggers using SPL.
I closed
the section on SPL by showing the built-in XML support via
xpath_string, xpath_number, xpath_nodeset, xpath_table and
xslt_process. I provide examples using all five of these
functions. In EnterpriseDB, XML support is not as complete as it
is in Oracle but it has the basics that are needed.
I closed
the chapter by showing you a few of the available languages that are
available to developers of EnterpriseDB applications (PL/pgSQL,
PL/Perl and PL/TCL). I also showed you where to get these
languages and how to install them. I finished with a consistent
example in their usage.
There is
no way a single chapter, or a single book for that matter, can cover
every possible combination of features and languages in EnterpriseDB.
This chapter was intended to give you a place to start and I hope it
did that. The remainder of the book will use the things you
learned in this chapter.
The next
chapter, Using Developer Studio, will show you how to apply the things
you learned in Chapter 3 and Chapter 4. You will learn how to
write and tune your code and access other databases.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
