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.

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

LANGUAGE <lang>] [SQL SECURITY <mode>] AS

<local_scalar_variables>

BEGIN

<function_code>

END;

END;

.

- We can create read-only functions. Insert, Update and Delete statements are not allowed in the function body.
- Also other functions or procedures selected/called from the body of the function must be read-only.
- Only SQLSCRIPT language is supported as of now.
- 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;

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 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;

————————————————————————————————

— 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;

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.

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

————————————————————————————————

— Function which calculates maximum peak memory

————————————————————————————————

— 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”

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”

UNION

SELECT SUM(INCLUSIVE_PEAK_ALLOCATION_SIZE) AS “M”

FROM M_HEAP_MEMORY WHERE DEPTH = 0);

END;

END;

.

The table function is called in FROM clause as

SELECT * FROM RAJ.F_MAX_PEAK_MEM();

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

————————————————————————————————

— 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;

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;

— 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

————————————————————————————————

— 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;

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

— 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;

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

— 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

RETURNS TABLE(POSNR SMALLINT

, MATNR NVARCHAR (18)

, ARKTX NVARCHAR (40)) LANGUAGE SQLSCRIPT AS

BEGIN

RETURN

SELECT DISTINCT

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;

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……… ðŸ˜› 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 ðŸ˜‰

.

Function is over and Thank You for your visit ðŸ™‚

Hi Raj,

Raj is here, Don't have fear about HANA. Nice post, Keep on posting.

Regards,

John.

Good stuff Raj - Thanks!

John, your comment made me smile.

Thanks John and Rama.

Hi Rajkumar,

Its really helpful, Tahnks For sharing.

Regards,

Giri

Thanks Giri.

Like you people need to work on R&D of SAP HANA..good stuff..:-)

Thanks Nageshwar

Hi Raj, Good document for all HANA professionals. Thanks Raj

Thanks Raj for great share .

Regards

Kumar

Nice stuff and simple explanation Raj.. thanks for sharing..

Regards,

Nikhil Joy

Thanks Ramesh, Mayuresh and Nikhil.

Good one

Thanks Siva

Good one, Raj...

Really appreciate your efforts.

BR

Prabhith

Nice informative article.

Really useful article. Thanks for sharing raj

Thanks Prabhith, Azeem and Ram

Good one... Thanks for sharing...

Thanks for sharing.

Thanks Rajesh, Victor.

Nicely presented...Good one.. Thanks for sharing

Regards,

Krishna Chaitanya.

Great stuff Raj, Keep up the good work on HANA!!!

Nice post

Hello Raj,

Nice post , Thank you for sharing ...

Regards

Shishupal

Nice one. Thanks.

David.

Good document !

Regards,

Satya

Great!