User Defined Functions in SAP HANA

User Define Function in SAP HANA:-

User Defined Functions in SAP HANA. User Defined Functions in SAP HANA are the read-only function that means we can not perform any DDL and DML(insert update and delete) operation inside the body of the function.There is two type of user-defined functions: Scalar User Defined Functions and Table User Defined Functions.

There are two types of user define the function in sap Hana.
1-Table User Define Function (Table UDF)
2-Scalar User Define Function(Scalar UDF)
These categories are defined on the basis of input/Output parameter, supported functionality in the body of the function and how they are going to consume in the SQL statement.

1-Table User Define Function:[AdSense-A]
Table User Define Function always returns a table and its parameter may also be off table type.

Syntax:

CREATE FUNCTION FUNCTION_NAME(Parameter1 DATA_TYPE,Parameter2 DATA_TYPE)
RETURNS TABLE( RetruenValue1 DATA_TYPE, ReturnValue2 DATA_TYPE )
BEGIN
RETURN SELECT 1* Parameter1 AS RetruenValue1 ,1*Parameter2 AS ReturnValue2 FROM mytab;
END;

 

Example:

CREATE FUNCTION Tset1(Var1 INT,Var2 INT)
RETURNS TABLE (a INT,b INT)
BEGIN
RETURN SELECT :Var1*a AS A, :Var2 * b AS b FROM mytab;
END;

 

Calling of the function:[AdSense-A]

select * from Tset1(10,20);

 

2-Scalar User Define Function:
Scalar User Define Function does not support table type variable as its input and table operations in the function body.

Syntax:

CREATE FUNCTION func_add_mul(x1 Double, x2 Double)
RETURNS result_add1 Double, result_mul1 Double
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
result_add1 := :x1 + :x2;
result_mul1 := :x1 * :x2;
END;

 


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

error: Content is protected !!