 |
PLSQL Function By Example
A PLSQL function is one of the 3 types of blocks in
PL/SQL (the others being anonymous blocks and procedures) and is
comprised of
the function specification and the function body.
|
|
The
specification of a function defines the function name and its interface
to the outside world. The function body consists of an optional
declaration section for local types and variables followed by the
executable code and
an optional exception
handing section.
As with procedures, PLSQL functions should be designed to do one thing
only. When the description for your
|
function includes
several "and"s then it may be time to reconsider the design
and split it into
2 or more functions.
A function is probably well designed if its name
(and therefore purpose) can be expressed succinctly as with the
built-in SQL functions upper,
lower, abs, initcaps etc. Also, the name of a function
should be a noun rather than a verb. For example "net_pay" rather than
"calculate_net_pay".
One of the major differences between a PLSQL function and procedure
is that function calls can be embedded directly into SQL statements
either within another PLSQL block or stand alone. However for a
function to be callable from SQL statements, a stored function (and any
procedure or function called by it) must abide by the following rules
controlling purity to ensure there are no side effects:
-
No database tables can be modified by a
function when it's called from a SELECT
statement or a parallelized INSERT, UPDATE,
or DELETE statement
-
When called from an INSERT,
UPDATE, or DELETE
statement, the function cannot query or modify any database tables
modified by that statement.
-
When called from a SELECT,
INSERT, UPDATE,
or DELETE statement, the function cannot
execute SQL transaction control statements (such as COMMIT),
session control statements (such as SET ROLE),
or system control statements (such as ALTER SYSTEM).
This also means that it cannot execute DDL statements (such as CREATE
TABLE etc)
because they perform an implicit commit.
Structure of a PLSQL Function
Having looked at good practice and the formal rule governing functions,
let's look at the basic structure of a PLSQL function.
Oracle
Tips & Tricks to
SKYROCKET Your Career!
If
you're not already a subscriber to Oracle Secrets Revealed,
you're missing out on a myriad of tips and techniques to help
you work better, faster and
smarter. Subscribe now and ignite your
career.
The basic syntax is as follows:
FUNCTION
<function_name> [(<parameters>)]
RETURN <datatype> IS
BEGIN
[<statement(s)>]
RETURN <expression>;
[EXCEPTION <exception handler(s);]
END [<function_name>];
Dissecting this, we see that a PLSQL function must start with the
reserved word FUNCTION,
must have a name, must specify the type of the value that the function
returns and sandwiched between the reserved words BEGIN
and END
must have the reserved word RETURN
followed by the expression to be returned. The function is terminated
by a semi-colon.
The
exception handling section is optional (any unhandled exceptions are
automatically propagated to the calling block by Oracle) as is labeling
the end of the function
with the function name.
One other point to note is that a function need not have any executable
statements other than the RETURN
statement. The reason for this is that the expression in a function
return can do all the work, as we'll see shortly.
Simple PLSQL Function Example
Now let's see some examples. We'll start with a very simple function
which
we'll call "one".
FUNCTION one RETURN
VARCHAR2 IS
BEGIN
RETURN 'one';
END one;
This is a valid PLSQL function, although it doesn't do
anything useful!
A few points of interest to note about this example:
- this function has no parameters
- the size of the return value is not specified -
only its type
- the only line of code is the RETURN statement
- as it is this function would have to be part
of another block
One general point to note is that although PLSQL is free format and not
case sensitive, by
convention reserved words are written in upper case
and the BEGIN
and END
each have a line to themselves. This makes the code easier to read and
therefore cheaper to maintain.
We could write this function all on one line like this:
FUNCTION one
RETURN VARCHAR2 IS BEGIN RETURN 'one'; END one;
For simple functions like this it doesn't matter, but it does make a
difference with
longer or more complex functions. Adding extra
blank lines and
comments in the source code are good ways of ensuring your code is easy
to read and maintain and as they are
stripped out at compile time there's no cost in performance terms. Not
adding them however might cost you or your
successor some headaches.
Another point that worth noting is that a PLSQL
function, procedure or anonymous block can be nested inside
(that is declared
inside, not
just called from) any other PL/SQL procedure, function
or anonymous block. Also, all 3 block types can be
combined or grouped into a PLSQL package (analogous to a program in
other
languages such as C), however only procedures and functions can be
invoked from outside the package.
To have this function stored in our Oracle database as a standalone
entity we need to precede the specification with the clause CREATE
OR REPLACE. This makes it
available for re-use and to other users (depending on
privileges/permissions granted).
Expanded PLSQL Function Example
Now let's see expand on our previous example. This time
we'll add some parameters and an exception handler.
1 CREATE OR
REPLACE
2 FUNCTION deductions (emp_id NUMBER) IS
3
4 total_deductions NUMBER;
5
6 BEGIN
7
8 SELECT tax+insurance+social_club_fees
9 INTO total_deductions
10 FROM emp where
id = emp_id;
11
12 RETURN total_deductions/12;
13
14 END deductions;
Parameters
As you can see, this PLSQL function has just 1 parameter emp_id
which is an input parameter. Although OUT and IN OUT parameters are
allowed with functions, it is not good practice to use them as this
means the function has side effects. Functions should be pure -
returning just one value and not having any side effects.
As with procedures, the IN parameters for a function can be made
optional by providing them with a default
value in the declaration. Oracle also enforces the behaviour of IN
parameters in both procedures and functions - they can't be
written to inside the procedure or function.
Exception Handler
A PLSQL function (or procedure or anonymous block)
can have at most one
exception handling section but this can handle as many different types
exceptions as you like, both user-defined and Oracle provided.
Oracle also provides the OTHERS
exception as a catch all, handling any
exceptions not already handled and so must be the last exception
handler.
Any exceptions not caught by an exception handler are automatically
propagated to the calling block by Oracle as mentioned earlier. The
caller may chose to handle the exception but if it doesn't the
exception will be propagated to the caller of that block and so on
until either it is handled by the calling block or the environment in
which the code is running. Even if the exception is handled it can be
RAISED to be handled (or not) by a calling module.
Other Points To Note
SQL statements can be embedded directly into the code (as on lines 8 to
9) or can be defined in a cursor.
Another Example
Just to demonstrate that the RETURN statement can be as complex as you
like (subject to memory constraints and the code remaining easy to
follow), here's another example.
FUNCTION circumference
(angle NUMBER,
radius NUMBER)
RETURN NUMBER IS
pi CONSTANT NUMBER := 3.1415926;
BEGIN
RETURN ROUND(angle/360*2*pi*radius,2);
END circumference;
More details on PLSQL functions and other aspects of PL/SQL can be
found in the OracleŽ Database PL/SQL User's Guide and
Reference available from the Oracle Technology Network.
Slash Costs, Sky-Rocket
Productivity and Maximise Uptime with Expert Oracle Training from
Smartsoft
Learn from expert consultants with skills honed to
a fine edge over
many years with myriad clients in
a variety of industries including utilities, finance, loacal and
national government and manufacturing.
Use their in-depth knowledge and
wide experience to maximise the benefits and minimise the costs of your
Oracle training.
Contact
us today to discuss your training needs and benefit from increased
productivity and lower costs tomorrow.
|
|
|
