Let's go for Function

[rindia]

This document is prepared based on HANA SP6 revision 60.

Function is very much alike to a procedure except that Function must return a value.
Whenever we define a function, it is called as User defined Function (UDF).
UDF are of two types: Table function and Scalar function.

Syntax:

CREATE FUNCTION <func_name> [(<parameter_clause>)] RETURNS <return_type>
LANGUAGE <lang>] [SQL SECURITY <mode>] AS
<local_scalar_variables>
BEGIN

    <function_code>
END;

.

1. We can create read-only functions. Insert, Update and Delete statements are not allowed in the function body.
2. Also other functions or procedures selected/called from the body of the function must be read-only.
3. Only SQLSCRIPT language is supported as of now.
4. SQL SECURITY can be either DEFINER or INVOKER.

.

Many of us might created or used various functions in traditional databases such as SQL Server, Oracle, DB2, etc..
Now lets start with creating very very basic function in HANA studio.
------------------------------------------------------------------------------------------------
--  Function which calculates sum of two numbers 10 and 20
------------------------------------------------------------------------------------------------
CREATE FUNCTION RAJ.F_CALC_SUM_CONSTANT()
RETURNS RESULT_SUM INTEGER
READS SQL DATA AS
BEGIN
  RESULT_SUM := 10 + 20;
END;

.

After executing the above SQL, the function F_CALC_SUM_CONSTANT will be created in schema RAJ under folder FUNCTIONS.
This function is of type Scalar function as it returns the scalar value.

This function does not have any input parameters and every time this function is called, it always returns a constant value of 30(10+20).
 

.

But how to call this function? As function is similar to procedure, it has to be something like CALL function_name......
.

No. Functions are called based on type of UDF. Scalar functions are called in SELECT and WHERE clause of SQL statements.

.

The above function is called in SELECT clause as

SELECT RAJ.F_CALC_SUM_CONSTANT() AS CALC_SUM FROM DUMMY;

.

Now lets create function to include input parameters.
------------------------------------------------------------------------------------------------
--  Function which calculates sum of two numbers of given input parameters
------------------------------------------------------------------------------------------------

CREATE FUNCTION RAJ.F_CALC_SUM_INPUT(IN VALUE1 INTEGER, IN VALUE2 INTEGER)
RETURNS RESULT_SUM INTEGER
READS SQL DATA AS
BEGIN
  RESULT_SUM := VALUE1 + VALUE2;
END;

.

Lets call the function passing input parameters:
SELECT RAJ.F_CALC_SUM_INPUT(10,20) AS CALC_SUM FROM DUMMY;

    

.

So far we seen how to use scalar functions. When to use table function?

.
Now lets create function based on system views to find maximum peak used memory using table function.

------------------------------------------------------------------------------------------------
--  Function which calculates maximum peak memory
------------------------------------------------------------------------------------------------

CREATE FUNCTION RAJ.F_MAX_PEAK_MEM()
RETURNS TABLE (M BIGINT ) LANGUAGE SQLSCRIPT AS -- SLIGHTLY DIFFERENT FROM SCALAR
BEGIN
   RETURN 
   SELECT ROUND(SUM("M")/1024/1024/1024,2) as M FROM
      (SELECT SUM(CODE_SIZE+SHARED_MEMORY_ALLOCATED_SIZE) AS "M"

         FROM SYS.M_SERVICE_MEMORY
       UNION
       SELECT SUM(INCLUSIVE_PEAK_ALLOCATION_SIZE) AS "M"

         FROM M_HEAP_MEMORY WHERE DEPTH = 0);
END;

.

The table function is called in FROM clause as
SELECT * FROM RAJ.F_MAX_PEAK_MEM();

  

In order to demonstrate a function calling some other functions, let us take the example of above function

and split into two.

One function will calculate the sum of code size and shared memory and

other function calculates the sum of inclusive peak allocation.

.

------------------------------------------------------------------------------------------------
--  Function which calculates CODE_SIZE and SHARED_MEMORY
------------------------------------------------------------------------------------------------

CREATE FUNCTION RAJ.F_SERVICE_MEMORY()
RETURNS  TABLE(M BIGINT ) LANGUAGE SQLSCRIPT AS
BEGIN
  RETURN   
  SELECT SUM(CODE_SIZE+SHARED_MEMORY_ALLOCATED_SIZE) AS "M"
    FROM SYS.M_SERVICE_MEMORY;
END;

SELECT * FROM RAJ.F_SERVICE_MEMORY();

 

------------------------------------------------------------------------------------------------
--  Function which calculates INCLUSIVE_PEAK_ALLOCATION
------------------------------------------------------------------------------------------------

CREATE FUNCTION RAJ.F_HEAP_MEMORY()
RETURNS  TABLE(M BIGINT ) LANGUAGE SQLSCRIPT AS
BEGIN
  RETURN   
  SELECT SUM(INCLUSIVE_PEAK_ALLOCATION_SIZE) AS "M"
    FROM M_HEAP_MEMORY WHERE DEPTH = 0 ;
END;

SELECT * FROM RAJ.F_HEAP_MEMORY();

 

Now let us call the above two functions in a function which calculates the maximum peak memory.
------------------------------------------------------------------------------------------------
--  Function which calculates Maximum Peak Memory

------------------------------------------------------------------------------------------------

CREATE FUNCTION RAJ.F_MAX_PEAK_MEMORY()
RETURNS  TABLE(M BIGINT) LANGUAGE SQLSCRIPT AS
BEGIN
  RETURN SELECT ROUND(SUM("M")/1024/1024/1024,2) as M 
           FROM (
                SELECT * FROM RAJ.F_SERVICE_MEMORY()
                UNION
                SELECT * FROM RAJ.F_HEAP_MEMORY()
                );
END;

SELECT * FROM RAJ.F_MAX_PEAK_MEMORY();

  

Now let us create function based on ECC tables where table function is called in WHERE clause.

.

I would like to get all Sales document and item, Material number and Short text of sales order item for a given Sales Group.

.

For this, I will create a function to get the list of  all Sales document based on table VBAK based on a given sales group.

And then create another function to get the list of Document item, Material number and Short text of sales order item by calling the above function which is used as criteria for other table VBAP.

.

------------------------------------------------------------------------------------------------
--  Function which gets the list of Sales document based on given sales group

------------------------------------------------------------------------------------------------

CREATE FUNCTION RAJ.F_GET_SALES_DOC(IN I_VKGRP NVARCHAR(3))
RETURNS TABLE(VBELN NVARCHAR (10)) LANGUAGE SQLSCRIPT AS
BEGIN
  RETURN
  SELECT ECC2HANA.VBAK.VBELN
    FROM ECC2HANA.VBAK
   WHERE ECC2HANA.VBAK.VKGRP = :I_VKGRP;
END;

SELECT * FROM RAJ.F_GET_SALES_DOC('100');

  

------------------------------------------------------------------------------------------------
--  Function calling another function whose result set is used as criteria

------------------------------------------------------------------------------------------------

CREATE FUNCTION RAJ.F_GET_SALES_ORDER_ITEM(IN I_VKGRP NVARCHAR(3))
RETURNS TABLE(POSNR SMALLINT

            , MATNR NVARCHAR (18)

            , ARKTX NVARCHAR (40)) LANGUAGE SQLSCRIPT AS
BEGIN
  RETURN
  SELECT DISTINCT

         ECC2HANA.VBAP.POSNR,
         ECC2HANA.VBAP.MATNR,
         ECC2HANA.VBAP.ARKTX
    FROM ECC2HANA.VBAP
   WHERE ECC2HANA.VBAP.VBELN IN (SELECT * FROM RAJ.F_GET_SALES_DOC(:I_VKGRP));
END;

.

SELECT * FROM RAJ.F_GET_SALES_ORDER_ITEM('100');

     

It's working......... :razz: What we did so far for table and scalar functions........

The difference between Table and Scalar functions are:

.

Table Functions	Scalar Functions
1. Can have a list of input parameters and must return a table whose type is defined in <return type>	1. Can have a list of input parameters and must return scalar values specified in <return parameter list>
2. Input parameters must be explicitly typed and can have any of the primitive SQL type or a table type.	2. Input parameters must be explicitly typed and can have any of the primitive SQL type.
	3. Using a table as input is not allowed.

.

After we create the function, we can check the same in system column view 'FUNCTIONS' which shows all the list of functions created.

   

.

The functions seen in the system views vary according to privileges that a user has been granted.

.

User with Privilege CATALOG READ or DATA ADMIN can see all the functions.

User with Privilege SCHEMA OWNER or EXECUTE can see only specific functions where the user is the owner or they have execute privileges.

.

The function can be deleted using syntax:    DROP FUNCTION function_name drop_option;

drop_option is optional in the syntax and can be either CASCADE or RESTRICT.

.

Let us drop the first function we created:

DROP FUNCTION RAJ.F_CALC_SUM_CONSTANT;

This will delete the function from the schema as well from system view FUNCTIONS.

.

If my function is called in some other function and does not want to delete in such cases then use drop option RESTRICT.

Function F_SERVICE_MEMORY is used in function F_MAX_PEAK_MEMORY and lets try this:

.

DROP FUNCTION RAJ.F_SERVICE_MEMORY RESTRICT;

  .

.

Instead of RESTRICT, lets use CASCADE.

DROP FUNCTION RAJ.F_SERVICE_MEMORY CASCADE;

  .

If we use the drop option CASCADE then the function will be deleted though it is called by other functions. Then what about the calling function? It just becomes invalid. 

  

ℹ The purpose of this document is to just demonstrate about using Functions and there are many several ways to achieve the same functionality by other means with better performance though :wink:

.

Function is over and Thank You for your visit :smile: