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. Figure 4-5 shows the definition and execution of an SQL stored function.

Figure 4-5 Defining and executing an SQL stored function

Figure 4-6 shows an example of how routine control SQL statements are combined and defined as a user-defined function and how the function is called and executed.

Figure 4-6 SQL stored function example

Explanation

1. Defines the user-defined function name and the SQL parameters.
2. Specifies the function return value.
3. Begins the compound statements.
4. Declares SQL variables.
5. Specifies value assignments.
6. Specifies return of the function return value.
7. Ends the compound statements.
8. Retrieves the SQL stored function with a function call.

Note

For details about the individual SQL statements, see the HiRDB Version 8 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 20^th
  

  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:
  
  • If an abstract data type is not included in the arguments
    The HiRDB system checks the arguments sequentially from the leftmost argument and sets the pre-defined data types of the individual arguments as references. The system then calls the function whose parameters have pre-defined data types with priorities that are equal to those of the references. 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 calls the function with the highest data type priorities. Table 4-1 shows the priorities of pre-defined data types. If an abstract data type is not included in the arguments, the called function is uniquely determined during SQL parsing, and the RETURNS clause data type of the called function becomes the data type of the function result.
    

    Table 4-1 Priorities of pre-defined data types

    Argument data type	Priority
    Numeric data	SMALLINTINTEGERDECIMALSMALLFLTFLOAT
    Character data	CHARVARCHAR
    National character data	NCHARNVARCHAR
    Mixed character string data	MCHARMVARCHAR

AB: 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:
  

1. 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-1. If a data type is an abstract data type, the priority is determined according to Table 4-2 as follows:
   

   Table 4-2 Priorities of abstract data types

   Argument data type	Priority
   Abstract data type	Same data typesuper 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.

AB: Indicates that A has a higher priority than B.

1. 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.
   
2. 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 8 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.
   
3. 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. Figure 4-7 shows the correspondences between a table with abstract data types and the called function.

Figure 4-7 Correspondences between a table with abstract data types and the called function

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 8 Installation and Design Guide.

¹ REMUNERATION = SALARY REMUNERATION_RATE()

² REMUNERATION = TOTAL_NUMBER_OF_CLIENTS 1000 + SALARY 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: ABC).

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)