SPL Functions

SPL functions are UDRs written in SPL that return one or more values. To write and register an SPL function, use a CREATE FUNCTION statement. Embed appropriate SQL and SPL statements between the CREATE FUNCTION and END FUNCTION keywords. You can also follow the function with the DOCUMENT and WITH FILE IN options.

SPL functions are parsed, optimized (as far as possible), and stored in the system catalog tables in executable format. The body of an SPL function is stored in the sysprocbody system catalog table. Other information about the function is stored in other system catalog tables, including sysprocedures, sysprocplan, and sysprocauth. For more information about these system catalog tables, see the HCL OneDB™ Guide to SQL: Reference.

The END FUNCTION keywords are required in every SPL function, and a semicolon ( ; ) must follow the clause that immediately precedes the statement block. The following code example creates an SPL function:

CREATE FUNCTION update_by_pct ( pct INT, pid CHAR(10))
   RETURNING INT;
   UPDATE inventory SET price = price + price * (pct/100)
      WHERE part_id = pid;
   return (select price from inventory where part_id = pid);
END FUNCTION
   DOCUMENT "USAGE: Update a price by a percentage",
         "Enter an integer percentage from 1 - 100",
         "and a part id number"
   WITH LISTING IN '/tmp/warn_file';

For more information on how to write SPL functions, see the chapter about SPL routines in HCL OneDB Guide to SQL: Tutorial.

See also the section Transactions in SPL Routines.

You can include valid SQL or SPL language statements in SPL functions. See, however, the following sections in Other syntax segments that describe restrictions on SQL and SPL statements within SPL routines: Subset of SPL Statements Valid in the Statement Block; SQL Statements Valid in SPL Statement Blocks; and Restrictions on SPL Routines in Data-Manipulation Statements.