Skip to Content
Author's profile photo Raj Kumar S

Let’s go for Function

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).
  F_1.jpg
.
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;
     F_2.jpg
.
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();
   F_3.jpg
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();
  F_04.jpg
————————————————————————————————
—  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();
  F_5.jpg
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();
   F_6.jpg
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’);
   F_7.jpg
————————————————————————————————
—  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’);
  F_08.jpg   
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.
    F_9.jpg
.
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;
  F_10.jpg.
.
Instead of RESTRICT, lets use CASCADE.
DROP FUNCTION RAJ.F_SERVICE_MEMORY CASCADE;
  .F_11.jpg
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. 
   F13.jpg
ℹ 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 🙂

Assigned Tags

      27 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo John K
      John K

      Hi Raj,

       

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

       

      Regards,

      John.

      Author's profile photo Rama Shankar
      Rama Shankar

      Good stuff Raj - Thanks!

      Author's profile photo Raj Kumar Salla
      Raj Kumar Salla
      Blog Post Author

      John, your comment made me smile.

      Thanks John and Rama.

      Author's profile photo Former Member
      Former Member

      Hi Rajkumar,

      Its really helpful, Tahnks For sharing.

       

       

      Regards,
      Giri

      Author's profile photo Raj Kumar Salla
      Raj Kumar Salla
      Blog Post Author

      Thanks Giri.

      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Raj Kumar Salla
      Raj Kumar Salla
      Blog Post Author

      Thanks Nageshwar

      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Kumar Mayuresh
      Kumar Mayuresh

      Thanks Raj  for great share .

       

      Regards

      Kumar

      Author's profile photo Nikhil Joy
      Nikhil Joy

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

       

      Regards,

      Nikhil Joy

      Author's profile photo Raj Kumar Salla
      Raj Kumar Salla
      Blog Post Author

      Thanks Ramesh, Mayuresh and Nikhil.

      Author's profile photo SIVA PIDUGU
      SIVA PIDUGU

      Good one

      Author's profile photo Raj Kumar Salla
      Raj Kumar Salla
      Blog Post Author

      Thanks Siva

      Author's profile photo prabhith prabhakaran
      prabhith prabhakaran

      Good one, Raj...

       

      Really appreciate your efforts.

       

      BR

      Prabhith

      Author's profile photo Azeem Quadri Mohammed Abdul
      Azeem Quadri Mohammed Abdul

      Nice informative article.

      Author's profile photo CH Raman
      CH Raman

      Really useful article. Thanks for sharing raj

      Author's profile photo Raj Kumar Salla
      Raj Kumar Salla
      Blog Post Author

      Thanks Prabhith, Azeem and Ram

      Author's profile photo rajesh bethamcharla
      rajesh bethamcharla

      Good one... Thanks for sharing...

      Author's profile photo Former Member
      Former Member

      Thanks for sharing.

      Author's profile photo Raj Kumar Salla
      Raj Kumar Salla
      Blog Post Author

      Thanks Rajesh, Victor.

      Author's profile photo Krishna Chaitanya
      Krishna Chaitanya

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

       

      Regards,

      Krishna Chaitanya.

      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Former Member
      Former Member

      Nice post

      Author's profile photo shishupalreddy ramreddy
      shishupalreddy ramreddy

      Hello Raj,

       

      Nice post , Thank you for sharing ...

       

      Regards

      Shishupal

      Author's profile photo Former Member
      Former Member

      Nice one. Thanks.

      David.

      Author's profile photo Former Member
      Former Member

      Good document !

       

      Regards,

      Satya

      Author's profile photo Former Member
      Former Member

      Great!