Scalar User Defined Functions in SAP HANA

[RichHeilman]

Back in December, I introduced you Table UDFs in HANA 1.0 SPS5.  At that time, I also mentioned that we are working on implementing Scalar UDFs as well.   Today, I am very happy to announce that as of HANA 1.0 SPS6(Rev 60), we now support Scalar UDFs as well.  Scalar UDFs are user-defined functions which accept multiple input parameters and result exactly one scalar value.  These functions allow the developer to encapsulate complex algorithms into manageable, reusable code which can then be nested within the field list of a SELECT statement.  If you have worked with scalar UDFs with other databases, you know how powerful they can be.  Below is an example showing how to create two scalar UDFs, and then leveraging both within the field list of a SELECT statement.  This is a very simplistic example, and of course the logic can be done by other means, I just wanted to remove any complexity of logic and focus purely on the syntax.

CREATE FUNCTION add_surcharge(im_var1 decimal(15,2), im_var2 decimal(15,2))

RETURNS result decimal(15,2)

LANGUAGE SQLSCRIPT  

SQL SECURITY INVOKER AS

BEGIN

result := :im_var1 + :im_var2;

END; 

CREATE FUNCTION apply_discount(im_var1 decimal(15,2), im_var2 decimal(15,2)) 

RETURNS result decimal(15,2)

LANGUAGE SQLSCRIPT  

SQL SECURITY INVOKER AS

BEGIN

result := :im_var1 - ( :im_var1 * :im_var2 );

END;

Once you execute the CREATE statements in the SQL Console, the new objects will show up on the catalog in the “Functions” folder.

 

As shown below, you can now use the functions in the field list of your SELECT statements.

 

Again, this is a pretty simple example, but I think you can see how powerful a tool scalar UDFs could be to a developer.   Currently, both table and scalar UDFs can only be created via the SQL Console, but rest assured we are working to allow the creation of these artifacts in the HANA repository via an XS Project.