CREATE FUNCTION

Purpose

Use this statement to create a user-defined function.

Prerequisites

  • You need to have the system privilege CREATE FUNCTION if the function is created in your own schema or in that of an assigned role or CREATE ANY FUNCTION.
  • If the option OR REPLACE is specified and the function already exists, then you also need to have the rights for DROP FUNCTION.
  • To use a function, you need to have the system privilege EXECUTE ANY FUNCTION, the object privilege EXECUTE on the function or its schema, or you are the current owner.

Syntax

create_function::=

CREATE FUNCTION Statement

CREATE FUNCTION Statement

CREATE FUNCTION Statement

CREATE FUNCTION Statement

CREATE FUNCTION Statement

function_statement::=

Function Statement

assignment::=

Assignment

if_branch::=

If Branch

If Branch

If Branch

for_loop::=

For Loop

For Loop

for_loop_2::=

while_loop::=

While Loop

Usage Notes

  • The option OR REPLACE can be used to replace an existing function without having to explicitly delete it with DROP FUNCTION.
  • For variable declarations, the normal SQL data types are valid. Refer to Data Types for more information.
  • Variable assignments cannot be performed in the variable declaration, but only in the function body.
  • Any scalar SQL expressions can be used for expressions (expr). For example, all built-in and previously defined scalar functions are available. For more information, refer to the Scalar Functions section.
  • Similar to normal SQL statements any number of comments can be inserted within a function definition. For more information, refer to the Comments in SQL section.
  • The counter variable of the FOR statement iterates over all integers which are defined by the border parameters. The number of iterations is evaluated as a constant before entering the FOR body, and it is never re-evaluated.
  • The definition text of a user-defined function can be found in the EXA_ALL_FUNCTIONS system table.
  • Scalar subqueries within functions cannot be parametrized. That means they cannot contain variables or parameters.

Examples

CREATE OR REPLACE FUNCTION percentage (fraction DECIMAL,
                                       entirety DECIMAL)
   RETURN VARCHAR(10)
   IS
     res DECIMAL;
   BEGIN
     IF entirety = 0
       THEN res := NULL;
     ELSE
       res := (100*fraction)/entirety;
     END IF;
     RETURN res || ' %';
   END percentage;
/

SELECT fraction, entirety, percentage(fraction,entirety) AS PERCENTAGE
FROM my_table;

FRACTION  ENTIRETY  PERCENTAGE
--------  --------  ----------
       1  2         50 %
       1  3         33 %
       3  24        12 %
-- Examples for function statements
-- assignment
    res := CASE WHEN input_variable < 0 THEN 0 ELSE input_variable END;

-- if-branch
    IF input_variable = 0 THEN
      res := NULL;
    ELSE
      res := input_variable;
    END IF;

-- for loop
    FOR cnt := 1 TO input_variable
    DO
        res := res*2;
    END FOR;

-- while loop
    WHILE cnt <= input_variable
    DO
         res := res*2;
         cnt := cnt+1;
    END WHILE;