User Defined Functions

Create Function: compile, link and add a new function to a database a function.Create Function privilege- enables user to Create a function.This privilege is not granted automatically and must be granted explicitly.


Syntax: GRANT CREATE FUNCTION ON Developer1 TO Developer1; Allows ‘Developer1’ to create functions in their own database.

Drop Function: removes the function by not linking from its shared library.
Drop Function privilege - enables user to DROP or REPLACE a previously created function.Granted automatically WITH GRANT OPTION to the creator of a database or function.Granted automatically to a database or user when it is created.


Syntax: GRANT DROP ON SPECIFIC FUNCTION Parse_Text TO develop1.
Allows ‘develop1’ to drop or replace the Parse_Text function..


Alter Function - performs either or both of the following:
 - Controls whether an existing function can run in protected mode as a separate process or in non-protected mode as part of the database.
  -  Re-compiles or relinks the function and redistributes it.
ALTER FUNCTION privilege - enables user to ALTER a function.This privilege is not granted automatically and must be explicitly granted.Alter Function privilege should be held only by the DBA.


Syntax: ALTER FUNCTION Parse_Text EXECUTE NOT PROTECTED;
Change the function from protected to non-protected mode.
ALTER FUNCTION Parse_Text COMPILE; Recompile the function without changing the protection mode.


Execute Function privilege: Enables user to evoke a function in an SQL statement. It enables a user to execute either specific functions, or all functions in a specified database. There is no EXECUTE FUNCTION command.
This privilege is not granted automatically to the creator of a database or user and must be explicitly granted. A user can execute any function they create, in protected mode.
GRANT EXECUTE FUNCTION On SYSLIB TO Usera;
Allows “Usera” to execute all function in database SYSLIB.
GRANT EXECUTE FUNCTION ON SPECIFIC FUNCTION Develop.Parse_Text TO Userc;
Allows “Userc” to execute the one function called ‘Parse_Text’ located in database “Develop”


Rename Function: This function permits the renaming either of a function name or a specific function name.
Syntax: RENAME SPECIFIC FUNCTION Match_Text TO Scan_Text;
RENAME FUNCTION Imagine_numbers(FLOAT, FLOAT) TO Imaginary_numbers;
COMMENT ON
COMMENT [ON] [ FUNCTION ] <object_name> [AS] ‘<comment>’
To create comments on a function use FUNCTION keyword and specify function name.
COMMENT ON FUNCTION Parse_Text ‘Scans for text using a pattern’;
To comment on function parameter then use the COLUMN keyword option.
COMMENT ON COLUMN Parse_Text.source_text AS ‘The text to be scanned’;
Limitations of User Defined Functions:
 - UDFs cannot execute any SQL.
 - UDFs cannot execute any Standard I/O functions.
 - UDFs cannot be used to calculate the value of a Primary Index column for an INSERT statement.
 - UDFs cannot be used to calculate the value of a Primary Index column in a Where clause to Select a Row.

Tweet