HPL/SQL allows you to defined user-defined functions and stored procedures using CREATE FUNCTION and CREATE PROCEDURE statements, respectively.

The easiest way to use HPL/SQL functions and procedures is to define them in the current script before their actual use.

For example:

CREATE FUNCTION hello(text STRING)
 RETURNS STRING
BEGIN
 RETURN 'Hello, ' || text || '!';
END;
 
CREATE PROCEDURE set_message(IN name STRING, OUT result STRING)
BEGIN
 SET result = 'Hello, ' || name || '!';
END;
 
-- Invoke the function
PRINT hello('world');
 
-- Call the procedure and print the results
DECLARE str STRING;
CALL set_message('world', str);
PRINT str;

Once defined the function can be used in any HPL/SQL and HQL expression as a built-in function. You can invoke a procedure using the CALL statement.

HPL/SQL allows you to share functions and procedures between HPL/SQL scripts so you do not need to put their content to every script.

Unlike databases that typically store functions and stored procedures in the database catalog, HPL/SQL stores them in local files. There are several options how to you can include functions and procedures:

• Use .hplsqlrc file

You can put your functions and procedures to .hplsqlrc file. The content of this file is automatically executed when you start hplsql tool. For more information, see .hplsqlrc file.

• INCLUDE statement

Using INCLUDE statement you can load function and procedures definitions from any file. Note that you can also use INCLUDE statements in .hplsqlrc file.

HPL/SQL allows you to invoke user-defined functions written in HPL/SQL language from Hive queries the same way as you use HQL built-in functions.

HPL/SQL CLI tool automatically puts referenced HPL/SQL functions and procedures to Distributed Cache, registers the Hive UDF and modifies the function call in the SQL statements. For more information, see Hive UDF to Run HPL/HQL Scripts.

Version: HPL/SQL 0.3.1

See also:

• CALL
• CREATE FUNCTION
• CREATE PROCEDURE
• INCLUDE