User Tools

Site Tools


Sidebar

HPL/SQL is included to Apache Hive since version 2.0

udf-sproc

This is an old revision of the document!


A PCRE internal error occured. This might be caused by a faulty plugin

====== User-Defined Functions and Stored Procedures ======= PL/HQL allows you to defined user-defined functions and stored procedures using [[create-function|CREATE FUNCTION]] and [[create-procedure|CREATE PROCEDURE]] statements, respectively. ===== Define Functions and Procedures in the Current Script ===== The easiest way to use PL/HQL functions and procedures is to define them in the current script before their actual use. For example: <code language=sql> 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; </code> Once defined the function can be used in any PL/HQL and HQL expression as a built-in function. You can invoke a procedure using the [[call|CALL]] statement. ===== Permanent Functions and Stored Procedures ===== PL/HQL allows you to share functions and procedures between PL/HQL 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, PL/HQL stores them in local files. There are several options how to you can include functions and procedures: * Use //.plhqlrc// file You can put your functions and procedures to //.plhqlrc// file. The content of this file is automatically executed when you start //plhql// tool. For more information, see [[configuration#plhqlrc-file|.plhqlrc]] file. * INCLUDE statement Using [[include|INCLUDE]] statement you can load function and procedures definitions from any file. Note that you can also use [[include|INCLUDE]] statements in [[configuration#plhqlrc-file|.plhqlrc]] file. ===== Using PL/HQL User-Defined Functions in Hive Queries ===== PL/HQL allows you to invoke user-defined functions written in PL/HQL language from Hive queries the same way as you use HQL built-in functions. PL/HQL CLI tool automatically puts referenced PL/HQL functions and procedures to Distributed Cache, registers the Hive UDF and modifies the function call in the SQL statements. For more information, see [[udf|Hive UDF to Run PL/HQL Scripts]]. **Version**: PL/HQL 0.3.1 See also: * [[call|CALL]] * [[create-function|CREATE FUNCTION]] * [[create-procedure|CREATE PROCEDURE]] * [[include|INCLUDE]] ~~NOTOC~~