Calling a UDF
Go Up to Working with UDFs and Blob Filters
Contents
After a UDF is created and declared to a database, it can be used in SQL statements wherever a built-in function is permitted. To use a UDF, insert its name in a SQL statement at an appropriate location, and enclose its input arguments in parentheses.
For example, the following DELETE statement calls the ABS() UDF as part of a search condition that restricts which rows are deleted:
DELETE FROM CITIES
WHERE ABS (POPULATION - 100000) > 50000;
UDFs can also be called in stored procedures and triggers. For more information, see “Working with Stored Procedures” and “Working with Triggers” in the Data Definition Guide.
Calling a UDF with SELECT
In SELECT statements, a UDF can be used in a select list to specify data retrieval, or in the WHERE clause search condition.
The following statement uses ABS() to guarantee that a returned column value is positive:
SELECT ABS (JOB_GRADE) FROM PROJECTS;
The next statement uses DATEDIFF() in a search condition to restrict rows retrieved:
SELECT START_DATE FROM PROJECTS
WHERE DATEDIFF (:today, START_DATE) > 10;
Calling a UDF with INSERT
In INSERT statements, a UDF can be used in the comma-delimited list in the VALUES clause.
The following statement uses TRIM()to remove leading blanks from firstname and trailing blanks from lastname before inserting the values of these host variables into the EMPLOYEE table:
INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, EMP_NO, DEPT_NO, SALARY)
VALUES (TRIM (0, ' ',:firstname), TRIM (1, ' ', :lastname),
:empno, :deptno, greater(30000, :est_salary));
Calling a UDF with UPDATE
In UPDATE statements, a UDF can be used in the SET clause as part of the expression assigning column values. For example, the following statement uses TRIM() to ensure that update values do not contain leading or trailing blanks:
UPDATE COUNTRIES
SET COUNTRY = TRIM (2, ' ', COUNTRY);
Calling a UDF with DELETE
In DELETE statements, a UDF can be used in a WHERE clause search condition:
DELETE FROM COUNTRIES
WHERE ABS (POPULATION - 100000) < 50000;