Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter