 |
|
EnterpriseDB: Function Syntax
Oracle Tips by
Burleson
|
CREATE OR REPLACE FUNCTION <function name>[(parameter
list)]
RETURNS <data type> AS $$
<program body in language of choice as a
string>
$$ LANGAUGE <language name> [STRICT];
I will
not be providing many examples or an in depth treatise on the
languages below. There are many references on the Internet and
there are some very good books that cover this subject in great
detail. I will provide additional examples of these languages in
Chapter 8, Oracle Compatibility.
PL/pgSQL
PL/pgSQL
looks an amazing amount like Oracle's PL/SQL but is not nearly as
robust. PL/pgSQL is also a block oriented language like
SPL. The structure of a PL/pgSQL block is: optional declaration
section, executable section, and optional exception section.
The
language also accepts named and typed parameters. Parameters can
be input or output. Parameters can be any data type including
composites and arrays.
PL/pgSQL
fully supports all of the expected conditional and flow control
structures. Variable declaration is straight forward.
For the
most part, you will not program in PL/pgSQL but you may find useful,
pre-written functionality available that is written in PL/pgSQL.
If you can write it in PL/pgSQL, you can write it in SPL and, to
maintain Oracle compatibility, you should concentrate your code in the
SPL language.
Below is
a simple PL/pgSQL function that returns an employee name based on a
passed in number:
CREATE OR REPLACE FUNCTION get_emp_names(p_empno
integer) RETURNS text AS $$
DECLARE
v_ename TEXT;
BEGIN
SELECT ename
INTO v_ename
FROM emp
WHERE empno = p_empno;
IF NOT
FOUND
THEN
RAISE EXCEPTION 'EMP NOT FOUND';
END IF;
RETURN v_ename;
END;
$$ LANGUAGE plpgsql;
I can
call this function from another PL/pgSQL function, from SPL or from
SQL:
SELECT get_emp_names(1) FROM DUAL;
ERROR: EMP NOT FOUND
SELECT get_emp_names(7369) FROM DUAL;
get_emp_names
---------------
SMITH
(1 row)
To call
it from an SPL function might look like this:
CREATE OR REPLACE FUNCTION get_emp_names3(p_empno
number) RETURN varchar2 AS
v_ename TEXT;
BEGIN
v_ename := get_emp_names(cast(p_empno as
integer));
RETURN v_ename;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('No
Employee Found for EMPNO: ' || to_char(p_empno) );
RETURN NULL;
END;
Two
things to notice here is that I used CAST(p_empno as integer) to
ensure that the function signature of the PL/pgSQL function matched
the SPL call. I will cover this function in more detail in
Chapter 8.
The
other thing to notice is that I used an SPL exception handler to catch
and handle the PL/pgSQL exception that was raised. When calling
this function, an error is displayed instead of having an exception
being called.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
