Use this statement to create a user-defined function.
- 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.
- 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.
CREATE OR REPLACE FUNCTION percentage (fraction DECIMAL,
IF entirety = 0
THEN res := NULL;
res := (100*fraction)/entirety;
RETURN res || ' %';
SELECT fraction, entirety, percentage(fraction,entirety) AS PERCENTAGE
-- Examples for function statements
res := CASE WHEN input_variable < 0 THEN 0 ELSE input_variable END;