Hana - (User Defined) Function

Syntax

CREATE FUNCTION <function_name> [(<parameter_clause>)] RETURNS <return_type>
   [LANGUAGE <lang>]
   [SQL SECURITY <mode>] 
   [DEFAULT SCHEMA <default_schema_name>]
   [DETERMINISTIC] 
   AS
      {
      BEGIN
         <function_body>
      END
      | HEADER ONLY
      }
   [WITH [<cache_type>] CACHE RETENTION <minute_value>
   [OF <projection_list>] [FILTER <filter_condition>]]

where

  • DEFAULT_SCHEMA: Specifies the schema for unqualified objects in the function body.

Example

Basic

 CREATE FUNCTION hot_item.func_mul(input1 INT)
 RETURNS output1 INT LANGUAGE SQLSCRIPT AS
 BEGIN
     output1 := :input1 * :input1;
 END;

Decode

CREATE FUNCTION MY_SCHEMA.decode(input1 DOUBLE, input2 DOUBLE, input3 DOUBLE, input4 DOUBLE)  RETURNS output1 DOUBLE DEFAULT SCHEMA MY_SCHEMA
AS
BEGIN
   if :input1 = :input2 then
     output1 := :input3;
     else
     output1 := :input4;
   end if;
END;

Sleep

Burning CPU

DROP FUNCTION sleep;

CREATE FUNCTION sleep(seconds DOUBLE) RETURNS output1 TIMESTAMP DEFAULT SCHEMA HOTITEM AS 
BEGIN 

   DECLARE endSleepTime TIMESTAMP;
   
   endSleepTime := ADD_SECONDS(now(), :seconds);
   
   output1 := endSleepTime;
   
   while (1=1)  Do 
   	IF (now() > endSleepTime) THEN
   		break;
   	END IF;
   end while; 
     
END;

Documentation / Reference


Powered by ComboStrap