Nonstop Database, HiRDB Version 9 UAP Development Guide
![[Contents]](FIGURE/CONTENT.GIF)
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
CREATE FUNCTION or CREATE TYPE registers a user-defined function in a database as an SQL stored function. DROP FUNCTION deletes an SQL stored function from the database. Once registered in a database, a user-defined function can be executed by calling it in an SQL statement. If a function has an SQL object that has been invalidated, the ALTER ROUTINE statement can be used to re-create that function. The following figure shows the definition and execution of an SQL stored function.
Figure 4-13 Defining and executing an SQL stored function
![[Figure]](FIGURE/ZU040304.GIF)
- Public function
- If you use a stored function defined by another user, you must specify the owner's authorization identifier and routine identifier when you call the stored function from within a UAP. However, if CREATE PUBLIC FUNCTION is executed to define the stored function as a public function, there is no need to specify the owner's authorization identifier when the stored function is called from a UAP (only the routine identifier must be specified).
(2) Example of an SQL stored function
The following shows an example of the definition and execution of an SQL stored function that combines multiple routine control SQL statements and defines them as a user-defined function (function).
Figure 4-14 SQL stored function example
![[Figure]](FIGURE/ZU040305.GIF)
- Explanation
- Defines the user-defined function name and the SQL parameters.
- Specifies the function return value.
- Begins the compound statements.
- Declares SQL variables.
- Specifies value assignments.
- Specifies return of the function return value.
- Ends the compound statements.
- Retrieves the SQL stored function with a function call.
- Note
- For details about the individual SQL statements, see the HiRDB Version 9 SQL Reference manual.
Defining the following functions is helpful.
- Function that calculates the last date of a month containing the specified date
CREATE FUNCTION LASTDAY(INDATE DATE) RETURNS DATE
BEGIN
DECLARE MM1 INTEGER;
SET MM1=MONTH(INDATE)-1;
RETURN (INDATE-MM1 MONTHS+(31-DAY(INDATE))
DAYS+MM1 MONTHS);
END
- Function that calculates the day of a specified date with an integer from 0 (Sunday) through 6 (Saturday)
CREATE FUNCTION DNOFWEEK(INDATE DATE) RETURNS INTEGER
BEGIN
RETURN MOD(DAYS(INDATE),7);
END
- Function that calculates the day of a specified date in English
CREATE FUNCTION DAYOFWEEK(INDATE DATE) RETURNS CHAR(3)
BEGIN
RETURN (CASE MOD(DAYS(INDATE),7) WHEN 0 THEN 'SUN'
WHEN 1 THEN 'MON'
WHEN 2 THEN 'TUE'
WHEN 3 THEN 'WED'
WHEN 4 THEN 'THU'
WHEN 5 THEN 'FRI'
ELSE 'SAT' END);
END
- Function that calculates the date of the specified day that immediately follows a specified date
CREATE FUNCTION NEXTDAY(INDATE DATE, DAYOFWEEK CHAR(3))
RETURNS DATE
BEGIN
DECLARE SDOW, TDOW INTEGER;
SET TDOW=(CASE, DAYOFWEEK WHEN 'SUN' THEN 0
WHEN 'MON' THEN 1
WHEN 'TUE' THEN 2
WHEN 'WED' THEN 3
WHEN 'THU' THEN 4
WHEN 'FRI' THEN 5
ELSE 6 END);
SET SDOW=MOD(DAYS(INDATE),7);
RETURN (INDATE + (CASE WHEN TDOW>SDOW THEN TDOW-SDOW
ELSE 7+TDOW-SDOW END) DAYS);
END
(When the day argument is an integer [0 to 6])
CREATE FUNCTION NEXTDAY(INDATE DATE, DNOFWEEK INTEGER)
RETURNS DATE
BEGIN
DECLARE SDOW, TDOW INTEGER;
SET TDOW=DNOFWEEK;
SET SDOW=MOD(DAYS(INDATE),7);
RETURN (INDATE + (CASE WHEN TDOW>SDOW THEN TDOW-SDOW
ELSE 7+TDOW-SDOW END) DAYS);
END
- Function that calculates the year and month (yyyy-mm) of a specified date when each month ends on the 20th
CREATE FUNCTION YYYYMM20(INDATE DATE) RETURNS CHAR(7)
BEGIN
RETURN SUBSTR(CHAR(INDATE+1 MONTH -20 DAYS),1,7);
END
- Function that calculates the year (yyyy) of the specified date when each fiscal year ends on March 20
CREATE FUNCTION YYYY0320(INDATE DATE) RETURNS CHAR(4)
BEGIN
RETURN SUBSTR(CHAR(INDATE-2 MONTHS -20 DAYS)1,4);
END
- Function that calculates the year and quarter (yyyy-nQ) of the specified date when each fiscal year ends on March 20
CREATE FUNCTION YYYYNQ0320(INDATE DATE) RETURNS CHAR(7)
BEGIN
DECLARE WORKDATE DATE;
SET WORKDATE=(INDATE -2 MONTHS -20 DAYS);
RETURN (SUBSTR(CHAR(WORKDATE),1,5)||
SUBSTR(DIGITS((MONTH(WORKDATE)+2)/3),10,1)| |'Q');
END
- Function that calculates the year and half (yyyy-nH) of the specified date when each fiscal year ends on March 20
CREATE FUNCTION YYYYNH0320(INDATE DATE) RETURNS CHAR(7)
BEGIN
DECLARE WORKDATE DATE;
SET WORKDATE=(INDATE -2 MONTHS -20 DAYS);
RETURN (SUBSTR(CHAR(WORKDATE),1,5) ||
SUBSTR(DIGITS((MONTH(WORKDATE)+5)/6),10,1)| |'H');
END
- Function that calculates the number of months between dates (argument 1 - argument 2)(extra days are discarded)
CREATE FUNCTION MONTHBETWEEN0(INDATE1 DATE, INDATE2 DATE)
RETURNS INTEGER
BEGIN
DECLARE YMINTERDATE INTERVAL YEAR TO DAY;
SET YMINTERDATE=INDATE1-INDATE2;
RETURN (YEAR(YMINTERDATE)*12+MONTH(YMINTERDATE));
END
- Function that calculates the number of months between two dates (argument 1 - argument 2) to several decimal places. (The number-of-months value for one day is calculated by setting the day of the earlier date as the starting point of each month and then dividing 1 by the number of days in the month with the later date.)
CREATE FUCNTION MONTHBETWEEN(INDATE1 DATE,INDATE2 DATE)
RETURNS DECIMAL(29,19)
BEGIN
DECLARE INTERDATE INTERVAL YEAR TO DAY;
DECLARE DMONTHS DEC(29,19);
DECLARE YYI,MMI INTEGER;
DECLARE WDATE DATE;
DECLARE SIGNFLAG DEC(1);
IF INDATE1>INDATE2 THEN
SET INTERDATE=INDATE1-INDATE2;
SET WDATE=INDATE2;
SET SIGNFLAG=1;
ELSEIF INDATE1<INDATE2 THEN
SET INTERDATE=INDATE2-INDATE1;
SET WDATE=INDATE1;
SET SIGNFLAG=-1;
ELSE RETURN 0;
END IF;
SET YYI=YEAR(INTERDATE);
SET MMI=MONTH(INTERDATE);
SET WDATE=WDATE+YYI YEARS+MMI MONTHS;
SET DMONTHS=YYI*12+MMI
+DEC(DAY(INTERDATE),2)/(DAYS(WDATE+1 MONTH)- DAYS(WDATE));
IF SIGNFLAG=1 THEN RETURN DMONTHS;
ELSE RETURN -DMONTHS;
END IF;
END
- Function that calculates the number of years between two dates (argument 1 - argument 2) to several decimal places (the number-of-years value for one day is calculated by setting the month and day of the earlier date as the starting point of each year and then dividing 1 by the number of days in the year with the later date).
CREATE FUNCTION YEARBETWEEN(INDATE1 DATE,INDATE2 DATE)
RETURNS DECIMAL(29,19)
BEGIN
DECLARE INTERDATE INTERVAL YEAR TO DAY;
DECLARE DYEARS DEC(29,19);
DECLARE YYI,MMI INTEGER;
DECLARE WDATE1, WDATE2 DATE;
DECLARE SIGNFLAG DEC(1);
IF INDATE1>INDATE2 THEN
SET INTERDATE=INDATE1-INDATE2;
SET WDATE1=INDATE1;
SET WDATE2=INDATE2;
SET SIGNFLAG=1;
ELSEIF INDATE1<INDATE2 THEN
SET INTERDATE-INDATE2-INDATE1;
SET WDATE1=INDATE2;
SET WDATE2=INDATE1;
SET SIGNFLAG=-1;
ELSE RETURN 0;
END IF;
SET YYI=YEAR(INTERDATE);
SET WDATE2=WDATE2+YYI YEARS;
SET DYEARS=YYI
+DEC(DAYS(WDATE1)-DAYS(WDATE2),3)
/(DAYS(WDATE2+1 YEAR)-DAYS(WDATE2));
IF SIGNFLAG=1 THEN RETURN DYEARS;
ELSE RETURN -DYEARS;
END IF;
END
- A function is called if the counts for authorization identifiers, routine identifiers, and arguments all match, if the argument data types do not include abstract data types, and if the parameter data types perfectly match the argument order. In this case, the data type of the function result is the RETURNS clause data type of the called function.
- A function is not called if any of the counts for authorization identifiers, routine identifiers, or arguments do not match.
- If the counts for authorization identifiers, routine identifiers, and arguments all match, but the argument data types include an abstract data type or the parameter data types do not perfectly match the argument order, the called function is determined as follows:
- A
B: Indicates that A has a higher priority than B.
- If an abstract data type is included in the arguments
If an abstract data type is included in the arguments, the function to be called is determined according to the sequence described as follows:
- Determining the basic function
The HiRDB system checks the arguments sequentially from the leftmost argument and sets the data types of the individual arguments as references. The system then selects the function whose parameters have data types with priorities that are equal to those of the references and sets that function as the basic function. If it does not find such a function, the system looks at the functions whose parameters have pre-defined data types with priorities that are less than those of the references and selects the function with the highest data type priorities. If a data type is a pre-defined data type, the priority is determined according to Table 4-3. If a data type is an abstract data type, the priority is determined according to the following table.
Table 4-4 Priorities of abstract data types
Argument data type |
Priority |
Abstract data type |
Same data type super type# |
- #: The super type that is specified directly by the UNDER clause in an abstract type definition has a higher priority than other super types.
- A
B: Indicates that A has a higher priority than B.
- Determining other candidate functions
If an argument has an abstract data type, the data types of values that can actually be used as data for that argument are the same as the abstract data type in the argument definition and the subtypes of that data type. In addition to the basic function, all functions that have parameters corresponding to the same data type as the abstract data type of the argument, or to the abstract data type of a subtype, become candidates for the called function.
If the basic function is the only candidate function, it becomes the called function. The data type of the function result becomes the RETURNS clause data type of the called function.
- Limiting the candidate functions based on the data type in the RETURNS clause
For each candidate function other than the basic function, the HiRDB system checks whether the RETURNS clause data type is compatible with the RETURNS clause data type for the basic function. If the data type is not compatible, the function is dropped from the candidate functions. After checking this compatibility for all candidate functions, the HiRDB system determines the data type of the function result based on the RETURNS clause data types for the remaining candidate functions. The system performs a set operation (UNION[ALL] or EXCEPT[ALL]) on the remaining candidates. The resulting data type and data length become the data type and data length of the function result. For details, see the HiRDB Version 9 SQL Reference manual.
However, if the data type of the function result is an abstract data type, the abstract data type of the RETURNS clause for the basic function is used.
- Determining the called function when an SQL statement is executed
If there are two or three functions that cannot be determined uniquely, the HiRDB system determines which one of these candidate functions to call based on the actual data type used for each abstract data type argument when the SQL statement is executed. The system checks the arguments sequentially from the leftmost argument. If the actual value of an argument is a non-null value, the data type of that value is used as a reference. If the actual value is a null value, the data type of that argument is used as reference. From the candidate functions, the HiRDB system selects the function whose parameters have data types with priorities that are equal to those of the references and sets that function as the called function. If it cannot find such a function, the system looks at the functions whose parameters have pre-defined data types with priorities that are less than those of the references and selects the function with the highest data type priorities.
Because HiRDB allows a function to be defined more than once, there may be several candidates for a called function. The called function is determined by how the function call specification and the function definition match. The following figure shows the correspondences between a table with abstract data types and the called function.
Figure 4-15 Correspondences between a table with abstract data types and the called function
![[Figure]](FIGURE/ZU040306.GIF)
- Explanation
- Suppose that the following SQL statement uses the abstract data type function REMUNERATION to retrieve data from the staff table:
SELECT STAFF_NUMBER FROM STAFF_TABLE WHERE
REMUNERATION(EMPLOYEE)>=2000.00
- In this case, the function for each data type is determined and called according to whether the data for the parameter value is t_EMPLOYEE or t_SALESPERSON.
- For details about the definitions of this staff table, see the HiRDB Version 9 Installation and Design Guide.
- #1: REMUNERATION = SALARY x REMUNERATION_RATE()
- #2: REMUNERATION = TOTAL_NUMBER_OF_CLIENTS x 1,000 + SALARY x REMUNERATION_RATE()
- Examples of determining the called function when abstract data types are included
- In the examples below, A, B, and C are abstract data types, C is the super type of B, and B is the super type of A (priority of abstract data type: A
B
C).
- Example 1
- Prerequisite conditions
- Table definition
- CREATE TABLE T1(C1 C)
- Function definitions
- f(A), f(B), f(C)
- SQL statement
- SELECT f(C1) FROM T1
- Results
- Basic function
- f(C)
- Candidate functions when function call is f(C1)
- f(A), f(B), f(C)
- Called function
- The following table shows which function is called when the SQL statement is executed.
Actual value of T1.C1 |
Called function |
Type A |
f(A) |
Type B |
f(B) |
Type C |
f(C) |
Null value |
f(C) |
- Example 2
- Prerequisite conditions
- Table definition
- CREATE TABLE T1(C1 C,C2 B)
- Function definitions
- f(A,A), f(A,B), f(A,C), f(B,A), f(B,C), f(C,A),
f(C,B), f(C,C)
- SQL statement
- SELECT f(C1,C2) from T1
- Results
- Basic function
- f(C,B)
- Candidate functions when function call is f(C1,C2)
- f(A,A), f(A,B), f(A,C), f(B,A), f(B,C), f(C,A), f(C,B)
- Called function
- The following table shows which function is called when the SQL statement is executed.
Actual value of T1.C1 |
Actual value of T1.C2 |
Called function |
Type A |
Type A |
f(A,A) |
Type B |
f(A,B) |
Null value |
f(A,B) |
Type B |
Type A |
f(B,A) |
Type B |
f(B,C) |
Null value |
f(B,C) |
Type C |
Type A |
f(C,A) |
Type B |
f(C,B) |
Null value |
f(C,B) |
Null value |
Type A |
f(C,A) |
Type B |
f(C,B) |
Null value |
f(C,B) |
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.