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, 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;