CREATE FUNCTION calc(inval SMALLINT) RETURNS INT RETURN inval * 10;
CREATE FUNCTION calc(inval INTEGER) RETURNS INT RETURN inval * 5;
SELECT id AS id ANSWER ,calc(SMALLINT(id)) AS c1 ========== ,calc(INTEGER (id)) AS C2 ID C1 C2 FROM staff -- --- --- WHERE id < 30 10 100 50 ORDER BY id; 20 200 100
DROP FUNCTION calc(SMALLINT);
DROP FUNCTION calc(INTEGER);
Figure 515, Two functions with same name
Below is an example of a function that is not deterministic, which means that the function
result can not be determined based on the input:
CREATE FUNCTION rnd(inval INT)RETURNS SMALLINT
NOT DETERMINISTIC
RETURN RAND() * 50; ANSWER ====== SELECT id AS id ID RND ,rnd(1) AS RND -- --- FROM staff 10 37 WHERE id < 40 20 8 ORDER BY id; 30 42
Figure 516, Not deterministic function
The next function uses a query to return a single row/column value:
CREATE FUNCTION get_sal(inval SMALLINT)RETURNS DECIMAL(7,2)
RETURN SELECT salary
FROM staff
WHERE id = inval; ANSWER
=========== SELECT id AS id ID SALARY ,get_sal(id) AS salary -- --- FROM staff 10 98357.50 WHERE id < 40 20 78171.25 ORDER BY id; 30 77506.75
Figure 517, Function using query
More complex SQL statements are also allowed - as long as the result (in a scalar function) is
just one row/column value. In the next example, the either the maximum salary in the same
department is obtained, or the maximum salary for the same year - whatever is higher:
190 Scalar Functions
CREATE FUNCTION max_sal(inval SMALLINT)
RETURNS DECIMAL(7,2) RETURN WITH ddd (max_sal) AS (SELECT MAX(S2.salary) FROM staff S1 ,staff S2
WHERE S1.id = inval
AND S1.dept = s2.dept)
,yyy (max_sal) AS
(SELECT MAX(S2.salary)
FROM staff S1
,staff S2
WHERE S1.id = inval
AND S1.years = s2.years)
SELECT CASE
WHEN ddd.max_sal > yyy.max_sal
THEN ddd.max_sal ELSE yyy.max_sal END FROM ddd, yyy; ANSWER SELECT id AS id ====================
,salary AS SAL1 ID SAL1 SAL2
,max_sal(id) AS SAL2 -- --- ---
FROM staff 10 98357.50 98357.50 WHERE id < 40 20 78171.25 98357.50 ORDER BY id; 30 77506.75 79260.25
Figure 518, Function using common table expression
A scalar or table function cannot change any data, but it can be used in a DML statement. In
the next example, a function is used to remove all "e" characters from the name column:
CREATE FUNCTION remove_e(instr VARCHAR(50))RETURNS VARCHAR(50)
RETURN replace(instr,'e','');
UPDATE staff
SET name = remove_e(name)
WHERE id < 40;
Figure 519, Function used in update
Compound SQL Usage
A function can use compound SQL, with the following limitations:
The statement delimiter, if needed, cannot be a semi-colon.
No DML statements are allowed.
Below is an example of a scalar function that uses compound SQL to reverse the contents of a
text string:
--#SET DELIMITER ! IMPORTANT
============
CREATE FUNCTION reverse(instr VARCHAR(50)) This example RETURNS VARCHAR(50) uses an "!" BEGIN ATOMIC as the stmt DECLARE outstr VARCHAR(50) DEFAULT ''; delimiter. DECLARE curbyte SMALLINT DEFAULT 0;
SET curbyte = LENGTH(RTRIM(instr));
WHILE curbyte >= 1 DO
SET outstr = outstr || SUBSTR(instr,curbyte,1);
SET curbyte = curbyte - 1;
END WHILE;
RETURN outstr;
END!
ANSWER
SELECT id AS id ====================
,name AS name1 ID NAME1 NAME2
,reverse(name) AS name2 -- --- ---
FROM staff 10 Sanders srednaS WHERE id < 40 20 Pernal lanreP ORDER BY id! 30 Marenghi ihgneraM
Figure 520, Function using compound SQL
Because compound SQL is a language with basic logical constructs, one can add code that
does different things, depending on what input is provided. To illustrate, in the next example
the possible output values are as follows:
If the input is null, the output is set to null.
If the length of the input string is less than 6, an error is flagged.
If the length of the input string is less than 7, the result is set to -1.
Otherwise, the result is the length of the input string.
Now for the code:
--#SET DELIMITER ! IMPORTANT============
CREATE FUNCTION check_len(instr VARCHAR(50)) This example RETURNS SMALLINT uses an "!" BEGIN ATOMIC as the stmt IF instr IS NULL THEN delimiter. RETURN NULL;
END IF;
IF length(instr) < 6 THEN
SIGNAL SQLSTATE '75001'
SET MESSAGE_TEXT = 'Input string is < 6';
ELSEIF length(instr) < 7 THEN
RETURN -1;
END IF;
RETURN length(instr); ANSWER
END! =================
ID NAME1 NAME2 SELECT id AS id -- --- ---
,name AS name1 10 Sanders 7
,check_len(name) AS name2 20 Pernal -1
FROM staff 30 Marenghi 8
WHERE id < 60 40 O'Brien 7
ORDER BY id! <error>
Figure 521, Function with error checking logic
192 Table Functions
Table Functions
A table function is very similar to a scalar function, except that it returns a set of rows and
columns, rather than a single value. Here is an example:
CREATE FUNCTION get_staff()
RETURNS TABLE (ID SMALLINT
,name VARCHAR(9) ,YR SMALLINT) RETURN SELECT id ,name ,years ANSWER FROM staff; ============== ID NAME YR SELECT * -- --- --
FROM TABLE(get_staff()) AS s 10 Sanders 7 WHERE id < 40 20 Pernal 8
ORDER BY id; 30 Marenghi 5
Figure 522, Simple table function
NOTE: See page 187 for the create table function syntax diagram.Description
The basic syntax for selecting from a table function goes as follows:
) FROM TABLE ( function-name input-parmeter , AS correlation-name ( ) ) column-name , (Figure 523, Table function usage - syntax
Note the following:
The TABLE keyword, the function name (obviously), the two sets of parenthesis , and a
correlation name, are all required.
If the function has input parameters, they are all required, and their type must match.
Optionally, one can list all of the columns that are returned by the function, giving each
an assigned name
Below is an example of a function that uses all of the above features:
CREATE FUNCTION get_st(inval INTEGER)RETURNS TABLE (id SMALLINT
,name VARCHAR(9)
,yr SMALLINT)
RETURN SELECT id
,name
,years
FROM staff ANSWER
WHERE id = inval; ==============
ID NNN YY SELECT * -- --- -- FROM TABLE(get_st(30)) AS sss (id, nnn, yy); 30 Marenghi 5
Examples
A table function returns a table, but it doesn't have to touch a table. To illustrate, the follow-
ing function creates the data on the fly:
CREATE FUNCTION make_data()
RETURNS TABLE (KY SMALLINT
,DAT CHAR(5))
RETURN WITH temp1 (k#) AS (VALUES (1),(2),(3)) ANSWER SELECT k# ======== ,DIGITS(SMALLINT(k#)) KY DAT FROM temp1; -- --- 1 00001 SELECT * 2 00002 FROM TABLE(make_data()) AS ttt; 3 00003
Figure 525, Table function that creates data
The next example uses compound SQL to first flag an error if one of the input values is too
low, then find the maximum salary and related ID in the matching set of rows, then fetch the
same rows - returning the two previously found values at the same time:
CREATE FUNCTION staff_list(lo_key INTEGER IMPORTANT,lo_sal INTEGER) ============
RETURNS TABLE (id SMALLINT This example ,salary DECIMAL(7,2) uses an "!" ,max_sal DECIMAL(7,2) as the stmt ,id_max SMALLINT) delimiter. LANGUAGE SQL
READS SQL DATA
EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC
DECLARE hold_sal DECIMAL(7,2) DEFAULT 0;
DECLARE hold_key SMALLINT;
IF lo_sal < 0 THEN
SIGNAL SQLSTATE '75001'
SET MESSAGE_TEXT = 'Salary too low';
END IF; FOR get_max AS SELECT id AS in_key ,salary As in_sal FROM staff WHERE id >= lo_key DO
IF in_sal > hold_sal THEN
SET hold_sal = in_sal;
SET hold_key = in_key;
END IF; END FOR; RETURN SELECT id ,salary ,hold_sal ,hold_key ANSWER FROM staff ============================
WHERE id >= lo_key; ID SALARY MAX_SAL ID_MAX END! --- --- --- --- 70 76502.83 91150.00 140 SELECT * 80 43504.60 91150.00 140 FROM TABLE(staff_list(66,1)) AS ttt 90 38001.75 91150.00 140 WHERE id < 111 100 78352.80 91150.00 140 ORDER BY id! 110 42508.20 91150.00 140
194 Useful User-Defined Functions
Useful User-Defined Functions
In this section we will describe some simple functions that are generally useful, and that peo-
ple have asked for over the years. In addition to the functions listed here, there are also the
following elsewhere in this book:
Check character input is a numeric value - page 399
Convert numeric data to character (right justified) - page 401.
Like-column predicate evaluation - page 43.
Locate string in input, a block at a time - page 322.
Pause SQL statement (by looping) for "n" seconds - page 419.
Sort character field contents - page 418.
Julian Date Functions
The function below converts a DB2 date into a Julian date (format) value:
CREATE FUNCTION julian_out(inval DATE)
RETURNS CHAR(7)
RETURN RTRIM(CHAR(YEAR(inval)))
|| SUBSTR(DIGITS(DAYOFYEAR(inval)),8);
ANSWER SELECT empno =========================
,CHAR(hiredate,ISO) AS h_date EMPNO H_DATE J_DATE ,JULIAN_OUT(hiredate) AS j_date --- --- ---
FROM employee 000010 1995-01-01 1995001 WHERE empno < '000050' 000020 2003-10-10 2003283 ORDER BY empno; 000030 2005-04-05 2005095
Figure 527, Convert Date into Julian Date
The next function does the opposite:
CREATE FUNCTION julian_in(inval CHAR(7))RETURNS DATE
RETURN DATE('0001-01-01')
+ (INT(SUBSTR(inval,1,4)) - 1) YEARS
+ (INT(SUBSTR(inval,5,3)) - 1) DAYS;
Figure 528, Convert Julian Date into Date
Get Prior Date
Imagine that one wanted to get all rows where some date is for the prior year - relative to the
current year. This is easy to code:
SELECT empno,hiredate
FROM employee
WHERE YEAR(hiredate) = YEAR(CURRENT DATE) - 1;
Figure 529, Select rows where hire-date = prior year
Get Prior Month
One can use the DAYS function to get the same data for the prior day. But one cannot use the
MONTH function to do the equivalent for the prior month because at the first of the year the
month number goes back to one.
One can address this issue by writing a simple function that multiplies the year-number by 12,
and then adds the month-number:
CREATE FUNCTION year_month(inval DATE)
RETURNS INTEGER
RETURN (YEAR(inval) * 12) + MONTH(inval);
Figure 530, Create year-month function
We can use this function thus:
SELECT empno,hiredate
FROM employee
WHERE YEAR_MONTH(hiredate) = YEAR_MONTH(CURRENT DATE) - 1;
Figure 531, Select rows where hire-date = prior month
Get Prior WeekSelecting rows for the prior week is complicated by the fact that both the US and ISO defini-
tions of a week begin at one at the start of the year (see page 434). If however we choose to
define a week as a set of seven contiguous days, regardless of the date, we can create a func-
tion to do the job. In the example below we shall assume that a week begins on a Sunday:
CREATE FUNCTION sunday_week(inval DATE)RETURNS INTEGER
RETURN DAYS(inval) / 7;
Figure 532, Create week-number function
The next function assumes that a week begins on a Monday:
CREATE FUNCTION monday_week(inval DATE)RETURNS INTEGER
RETURN (DAYS(inval) - 1) / 7;
Figure 533, Create week-number function
Both the above functions convert the input date into a day-number value, then subtract (if
needed) to get to the right day of the week, then divide by seven to get a week-number. The
result is the number of weeks since the beginning of the current era.
The next query shows the two functions in action:
WITH ANSWER temp1 (num,dt) AS ==================================(VALUES (1 DATE DAY WK IS SUN_WK MON_WK ,DATE('2004-12-29')) --- --- -- -- --- ---
UNION ALL 2004-12-29 Wed 53 53 104563 104563 SELECT num + 1 2004-12-30 Thu 53 53 104563 104563 ,dt + 1 DAY 2004-12-31 Fri 53 53 104563 104563 FROM temp1 2005-01-01 Sat 1 53 104563 104563 WHERE num < 15 2005-01-02 Sun 2 53 104564 104563 ), 2005-01-03 Mon 2 1 104564 104564 temp2 (dt,dy) AS 2005-01-04 Tue 2 1 104564 104564 (SELECT dt 2005-01-05 Wed 2 1 104564 104564 ,SUBSTR(DAYNAME(dt),1,3) 2005-01-06 Thu 2 1 104564 104564 FROM temp1 2005-01-07 Fri 2 1 104564 104564 ) 2005-01-08 Sat 2 1 104564 104564 SELECT CHAR(dt,ISO) AS date 2005-01-09 Sun 3 1 104565 104564 ,dy AS day 2005-01-10 Mon 3 2 104565 104565 ,WEEK(dt) AS wk 2005-01-11 Tue 3 2 104565 104565 ,WEEK_ISO(dt) AS is 2005-01-12 Wed 3 2 104565 104565 ,sunday_week(dt) AS sun_wk ,monday_week(dt) AS mon_wk FROM temp2 ORDER BY 1;
196 Useful User-Defined Functions
Generating Numbers
The next function returns a table of rows. Each row consists of a single integer value , starting
at zero, and going up to the number given in the input. At least one row is always returned. If
the input value is greater than zero, the number of rows returned equals the input value plus
one:
CREATE FUNCTION NumList(max_num INTEGER)
RETURNS TABLE(num INTEGER)
LANGUAGE SQL
RETURN
WITH temp1 (num) AS
(VALUES (0)
UNION ALL
SELECT num + 1
FROM temp1
WHERE num < max_num
)
SELECT num
FROM temp1;
Figure 535, Create num-list function
Below are some queries that use the above function:
ANSWERS SELECT * ======= FROM TABLE(NumList(-1)) AS xxx; 0 SELECT * FROM TABLE(NumList(+0)) AS xxx; 0 SELECT * FROM TABLE(NumList(+3)) AS xxx; 0 1 2 3 SELECT *FROM TABLE(NumList(CAST(NULL AS INTEGER))) AS xxx; 0
Figure 536, Using num-list function
NOTE: If this function did not always return one row, we might have to use a left-outer-join when joining to it. Otherwise the calling row might disappear from the answer-set because no row was returned.To illustrate the function's usefulness, consider the following query, which returns the start
and end date for a given set of activities:
SELECT actno ANSWER,emstdate =================================
,emendate ACTNO EMSTDATE EMENDATE #DAYS ,DAYS(emendate) - --- --- --- ---
DAYS(emstdate) AS #days 70 2002-06-15 2002-07-01 16
FROM emp_act act 80 2002-03-01 2002-04-15 45
WHERE empno = '000260'
AND projno = 'AD3113'
AND actno < 100
AND emptime = 0.5
ORDER BY actno;
Figure 537, Select activity start & end date
Imagine that we wanted take the above output, and generate a row for each day between the
start and end dates. To do this we first have to calculate the number of days between a given
start and end, and then join to the function using that value:
SELECT actno ANSWER
,#days ==========================
,num ACTNO #DAYS NUM NEW_DATE ,emstdate + num DAYS AS new_date --- --- --- ---
FROM (SELECT actno 70 16 0 2002-06-15 ,emstdate 70 16 1 2002-06-16 ,emendate 70 16 2 2002-06-17 ,DAYS(emendate) - 70 16 3 2002-06-18 DAYS(emstdate) AS #days 70 16 4 2002-06-19 FROM emp_act act 70 16 5 2002-06-20 WHERE empno = '000260' 70 16 6 2002-06-21 AND projno = 'AD3113' 70 16 7 2002-06-22 AND actno < 100 70 16 8 2002-06-23 AND emptime = 0.5 70 16 9 2002-06-24 )AS aaa 70 16 10 2002-06-25 ,TABLE(NumList(#days)) AS ttt etc... ORDER BY actno ,num;
Figure 538, Generate one row per date between start & end dates (1 of 2)
In the above query the #days value equals the number of days between the start and end dates.
If the two dates equal, the #days value will be zero. In this case we will still get a row because
the function will return a single zero value. If this were not the case (i.e. the function returned
no rows if the input value was less than one), we would have to code a left-outer-join with a
fake ON statement:
SELECT actno,#days
,num ACTNO #DAYS NUM NEW_DATE ,emstdate + num DAYS AS new_date --- --- --- ---
FROM (SELECT actno 70 16 0 2002-06-15 ,emstdate 70 16 1 2002-06-16 ,emendate 70 16 2 2002-06-17 ,DAYS(emendate) - 70 16 3 2002-06-18 DAYS(emstdate) AS #days 70 16 4 2002-06-19 FROM emp_act act 70 16 5 2002-06-20 WHERE empno = '000260' 70 16 6 2002-06-21 AND projno = 'AD3113' 70 16 7 2002-06-22 AND actno < 100 70 16 8 2002-06-23 AND emptime = 0.5 70 16 9 2002-06-24 )AS aaa 70 16 10 2002-06-25 LEFT OUTER JOIN etc...
TABLE(NumList(#days)) AS ttt
ON 1 = 1
ORDER BY actno
,num;
Figure 539, Generate one row per date between start & end dates (2 of 2)
Check Data Value Type
The following function checks to see if an input value is character, where character is defined
as meaning that all bytes are "A" through "Z" or blank. It converts (if possible) all bytes to
blank using the TRANSLATE function, and then checks to see if the result is blank:
CREATE FUNCTION ISCHAR (inval VARCHAR(250))RETURNS SMALLINT
LANGUAGE SQL
RETURN
CASE
WHEN TRANSLATE(UPPER(inval),' ','ABCDEFGHIJKLMNOPQRSTUVWXYZ') = ' ' THEN 1
ELSE 0
END;
198 Useful User-Defined Functions
The next function is similar to the prior, except that it looks to see if all bytes in the input are
in the range of "0" through "9", or blank:
CREATE FUNCTION ISNUM (inval VARCHAR(250))
RETURNS SMALLINT LANGUAGE SQL RETURN CASE WHEN TRANSLATE(inval,' ','01234567890') = ' ' THEN 1 ELSE 0 END;
Figure 541, Check if input value is numeric
Below is an example of the above two functions in action:
WITH temp (indata) AS ANSWER (VALUES ('ABC'),('123'),('3.4') ==========,('-44'),('A1 '),(' ')) INDATA C N SELECT indata AS indata --- - -
,ISCHAR(indata) AS c ABC 1 0 ,ISNUM(indata) AS n 123 0 1 FROM temp; 3.4 0 0 -44 0 0 A1 0 0 1 1
Figure 542, Example of functions in use
The above ISNUM function is a little simplistic. It doesn't check for all-blanks, or embedded
blanks, decimal input, or sign indicators. The next function does all of this, and also indicates
what type of number was found:
CREATE FUNCTION ISNUM2 (inval VARCHAR(255))RETURNS CHAR(4) LANGUAGE SQL RETURN CASE WHEN inval = ' ' THEN ' '
WHEN LOCATE(' ',RTRIM(LTRIM(inval))) > 0
THEN ' '
WHEN TRANSLATE(inval,' ','01234567890') = inval
THEN ' '
WHEN TRANSLATE(inval,' ','01234567890') = ' '
THEN 'INT '
WHEN TRANSLATE(inval,' ','+01234567890') = ' '
AND LOCATE('+',LTRIM(inval)) = 1
AND LENGTH(REPLACE(inval,'+','')) = LENGTH(inval) - 1
THEN 'INT+'
WHEN TRANSLATE(inval,' ','-01234567890') = ' '
AND LOCATE('-',LTRIM(inval)) = 1
AND LENGTH(REPLACE(inval,'-','')) = LENGTH(inval) - 1
THEN 'INT-'
WHEN TRANSLATE(inval,' ','.01234567890') = ' '
AND LENGTH(REPLACE(inval,'.','')) = LENGTH(inval) - 1
THEN 'DEC '
WHEN TRANSLATE(inval,' ','+.01234567890') = ' '
AND LOCATE('+',LTRIM(inval)) = 1
AND LENGTH(REPLACE(inval,'+','')) = LENGTH(inval) - 1
AND LENGTH(REPLACE(inval,'.','')) = LENGTH(inval) - 1
THEN 'DEC+'
WHEN TRANSLATE(inval,' ','-.01234567890') = ' ' AND LOCATE('-',LTRIM(inval)) = 1 AND LENGTH(REPLACE(inval,'-','')) = LENGTH(inval) - 1 AND LENGTH(REPLACE(inval,'.','')) = LENGTH(inval) - 1 THEN 'DEC-' ELSE ' ' END;