Use this statement to create a user-defined function.
You need either the system privilege USE ANY SCHEMA or the object privilege USAGE on the target schema, or the schema must be owned by you or one of your assigned roles.
- You need the system privilegeCREATE ANY FUNCTION, or CREATE FUNCTION if the function is created in your own schema or in that of an assigned role.
- 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 be 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;