Anonymous Functions in an Inner PLSQL block

October 13, 2010 § Leave a comment

Typically PL/SQL functions are written as a packaged functions like

CREATE OR REPLACE PACKAGE BODY some_package IS

  FUNCTION MY_FUNCTION  (p_param1    IN table1.column1%TYPE)
    RETURN  VARCHAR2
  IS
  BEGIN
  --...
  --...
  END MY_FUNCTION

END some_package;

Other PL/SQL procedures use the function by referring it with a package name. This approach is fine when PL/SQL code and related functions are a part of an application and need to be present all the time. However there are couple of compelling reasons some may not want to do this -

  1. The client to this function may be in a very specific need and want it’s own variety of it.
  2. The client may be targeted for some maintenance work and client might need to be defined as an anonymous block (run only once and discard)
  3. Because client is an anonymous block, it is preferable to keep the functions anonymous too so that those get discarded after the run

An example PL/SQL block below throws light on how to create inner function in an anonymous PLSQL block.

DECLARE

  --------------------------------------------------
  -- Variables to use throughout the SQL
  --------------------------------------------------
  t_temp INTEGER := -1;

  /**
  * This function is to create a mapping entry in the x_code_map table
  */
  FUNCTION MY_FUNCTION_ON_THE_FLY(p_param1 IN table1.column1%TYPE,
        ) RETURN NUMBER IS 
  BEGIN

    -- do your stuff...
    -- This return statement has no meaning. It is kept only for syntactical purpose.
    -- Return a 
    RETURN 0;

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RAISE;
      
  END MY_FUNCTION_ON_THE_FLY;

BEGIN
  

  -- Start doing your regular SQL... 
  -- Line 1
  t_temp := MY_FUNCTION_ON_THE_FLY('1000');
  -- Line 2
  t_temp := MY_FUNCTION_ON_THE_FLY('2000');
  
  -- Further SQL...
  
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

Function MY_FUNCTION_ON_THE_FLY is defined after the DECLARE of the main PL/SQL block and before the BEGIN. The declared function is used at Line 1 and Line 2 just like using regular functions. However no package references are made because the function is local.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

What’s this?

You are currently reading Anonymous Functions in an Inner PLSQL block at Sunil writes....

meta

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: