Creating Functions in SAP HANA

Ratings:
(4.2)
Views:1893
Banner-Img
  • Share this blog:

CREATE FUNCTIONS (deprecated syntax)

Syntax

Name data – type

REPLACE FUNCTION [SCHEMA] NAME ([IN|OUT|INOUT}

PARM-NAME DATA – TYUPE (,---])

BEGIN

----

END

Descriptions

Create table functions – say the function is SQL script can have a list of input and act put parameters click each parameter is marked using the keywords (IN/OUT/INOUT).  Input and output parameters must be explicitly typed (i.e. no untyped tables are supported)

INOUT parameters can only be of salon type one can use the deprecated replace function to modify a function definition that maintains the function signature and the DROP function to resolve it. This statement is reserved for potential later use to define user-defined functions.

Create procedure (new syntax)

Syntax

CREATE PROCEDURE [schema.] name [({In/OUT/IN out}

Faram – name data – type C, …]}]

[LANGUAGE <LANG>] [SQL SECURITY <MODE>]

[READS SQL DATA [WITH RESULT VIEW <VIEW – NAME>]]

AS BEGIN ……. end
Inclined to build a profession as SAP HANA Developer? Then here is the blog post on, explore"SAP HANA Training"

Description

CREATE PROCEDURE replaces CREATE FUNCTION (Without Return clause). In the Interface of the procedure, only SQL types and table types are supported.

The implementation language is by default SQL Script. It is good practice to define the language in all procedure definitions. Other implementation languages are supported but are not covered in this guide.

One can specify the security mode. Privileges are always checked with the privileges of the definer of a procedure when the procedure is created. With the security mode “definer”, which is the default, execution of the procedure is there performed with privileges of the definition of the procedure? The other alternative is mode “ in worker”. In this case, privileges are checked at runtime with the privileges of the caller of the function please note that analyze teal privileges are checked regardless of the security model. See the SAP HANA security guide referenced at the beginning of the document for details.

Optionally, a procedure can be tagged as a read-only procedure using READS SQL DATA. This marks a procedure as being from the side – effects. One factor to be considered here is that neither DDL our DML statecrafts are allowed its body, and only other read-only procedures can be called by the procedure. The advantage of this definition is that certain optimizations are only available for read-only procedures.

If a read-only procedure box has exactly one table output parameter, a result view can be specified as it was previously available for table functions. The name of the result view is no longer bound to a static name scheme but can be any valid SQL identifier. For back word compatibility reasons, the old static name scheme will be only supported for procedures that are generated with the deprecated

CREATE FUNCTION SYNTAX

CREATE PROCEDURE Proe with result view (in id int,, out OI customer)

LANGUAGE SQL SCRIPT READS SQL DATA WITH RESULT VIEW PROVIDES AS BEGIN

01= SELECT * FROM CUSTOMER WHERE CUST_id

= : ID :

END:

Normally, procedures can only be executed via the call statement. Using with result mew, it is possible to query the result of a procedure as part of a SQL statement.

Example

SELECT * FROM PROVIEW WITH PARAMETERS

(‘place holder’ = (‘$$ id$$, ‘s’))

You have to use DROP and create to modify the definition of a procedure.

Replace Function (Deprecated Syntax)

Syntax

REPLACE FUNCTION [SCHEMA] NAME ([IN|OUT|INOUT}

PARM-NAME DATA – TYUPE (,---])

BEGIN

----

END

Description

This statement is used to modify a function definition while maintaining the signature.

Please migrate to a procedure lusted for using this statement. This statement is reserved for potential later use with user-defined functions.

Drop function (deprecated syntax)

Syntax

Drop function [schema.] name [cascade]

Description

Delete a function definition from the database catalog. If the cascading drop is defined dependent objects will also be dropped; otherwise, they are invalidated. This statement is reserved for potential later use to drop a user-defined function.

For in-depth knowledge of SAP HANA, click on below

You liked the article?

Like : 0

Vote for difficulty

Current difficulty (Avg): Medium

Recommended Courses

1/15

About Author
Authorlogo
Name
TekSlate
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.