4.3.2　ストアドファンクションの定義

ストアドファンクションは，SQLで記述した一連のデータベース操作を，ユーザ定義関数としてデータベースに登録しておく機能です。

〈この項の構成〉

(1)　SQLストアドファンクションの定義と実行
(2)　SQLストアドファンクションの例
(3)　呼び出す関数の決定規則と結果のデータ型

(1)　SQLストアドファンクションの定義と実行

SQLストアドファンクションは，CREATE FUNCTION又はCREATE TYPE実行時にユーザ定義関数がデータベースに登録され，DROP FUNCTION実行時に削除されます。

登録したユーザ定義関数は，SQL文中に関数呼出しを指定すれば，その関数を呼び出して実行します。

SQLオブジェクトが無効になっている関数がある場合には，ALTER ROUTINEで関数を再作成することもできます。

SQLストアドファンクションの定義と実行を次の図に示します。

図4‒13　SQLストアドファンクションの定義と実行

パブリックファンクション: 他ユーザが定義したストアドファンクションを使用する場合は，UAP中からストアドファンクションを呼び出すときに，所有者の認可識別子とルーチン識別子を指定する必要があります。しかし，CREATE PUBLIC FUNCTIONを実行してパブリックファンクションとして定義すると，他ユーザが定義したストアドファンクションを使用する場合でも，UAP中からストアドファンクションを呼び出すときに，所有者の認可識別子を指定する必要がなくなります（ルーチン識別子だけ指定します）。

ページの先頭へ

(2)　SQLストアドファンクションの例

SQLストアドファンクションの例として，ルーチン制御SQLを組み合わせてユーザ定義関数（ファンクション）として定義する例とそれを呼び出して実行する例を次の図に示します。

図4‒14　SQLストアドファンクションの例

[説明]

ユーザ定義関数の名前とSQLパラメタの定義
関数の戻り値の指定
複合文の開始
SQL変数の宣言
値の代入の指定
関数の戻り値を返却する指定
複合文の終了
関数呼出しを使用した検索

注

各SQL文については，マニュアル「HiRDB SQLリファレンス」を参照してください。

関数の定義例：

指定した日付の月末の日付を求める関数

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

指定した日付の曜日を0（日）〜6（土）の整数で求める関数

CREATE FUNCTION DNOFWEEK(INDATE DATE) RETURNS INTEGER
  BEGIN
    RETURN MOD(DAYS(INDATE),7);
  END

指定した日付の曜日を日本語で求める関数

CREATE FUNCTION 曜日(INDATE DATE) RETURNS NCHAR
  BEGIN
    RETURN (CASE MOD(DAYS(INDATE),7) WHEN 0 THEN N'日'
      WHEN 1 THEN N'月'
      WHEN 2 THEN N'火'
      WHEN 3 THEN N'水'
      WHEN 4 THEN N'木'
      WHEN 5 THEN N'金'
      ELSE N'土'  END);
  END

指定した日付の曜日を英語で求める関数

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

指定した日付の直後の，指定した曜日の日付を求める関数

（引数の曜日が日本語（N'日'〜N'土'）の場合）

CREATE FUNCTION NEXT_DAY(INDATE DATE,曜日 NCHAR)
  RETURNS DATE
  BEGIN
    DECLARE SDOW,TDOW INTEGER;
    SET TDOW=(CASE 曜日 WHEN N'日' THEN 0
      WHEN N'月' THEN 1
      WHEN N'火' THEN 2
      WHEN N'水' THEN 3
      WHEN N'木' THEN 4
      WHEN N'金' 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

（引数の曜日が英語（'SUN'〜'SAT'）の場合）

CREATE FUNCTION NEXT_DAY(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

（引数の曜日が整数（0〜6）の場合）

CREATE FUNCTION NEXT_DAY(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

20日締めで指定した日付の年月（'yyyy-mm'）を求める関数

CREATE FUNCTION YYYYMM20(INDATE DATE) RETURNS CHAR(7)
  BEGIN
    RETURN SUBSTR(CHAR(INDATE+1 MONTH -20 DAYS),1,7);
  END

3月20日締めで指定した日付の年度（'yyyy'）を求める関数

CREATE FUNCTION YYYY0320(INDATE DATE) RETURNS CHAR(4)
  BEGIN
    RETURN SUBSTR(CHAR(INDATE-2 MONTHS -20 DAYS),1,4);
  END

3月20日締めで指定した日付の年度と四半期（'yyyy-nQ'）を求める関数

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

3月20日締めで指定した日付の年度と期（'yyyy-nH'）を求める関数

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

日付間（第一引数−第二引数）の月数を整数で求める関数（日数は切り捨て）

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

日付間（第一引数−第二引数）の月数を小数点以下まで求める関数（ただし，1日の月数は小さい方の日付の日を1か月の起点とし，大きい方の日付を含む1か月の日数分の1とします）

CREATE FUNCTION 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

日付間（第一引数−第二引数）の年数を小数点以下まで求める関数（ただし，1日の年数は小さい方の日付の月日を1か年の起点とし，大きい方の日付を含む1か年の日数分の1とします）

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

ページの先頭へ

(3)　呼び出す関数の決定規則と結果のデータ型

認可識別子，ルーチン識別子，引数の数が一致し，引数のデータ型に抽象データ型を含まないで，かつ引数の順序に対応してパラメタのデータ型が完全一致する場合は，この関数を呼び出します。また，この場合の関数の結果のデータ型は，呼び出す関数のRETURNS句のデータ型になります。
認可識別子，ルーチン識別子，引数の数のどれかが一致しない関数の場合は，この関数は呼び出しの対象とはなりません。

認可識別子，ルーチン識別子，引数の数は一致しているが，引数のデータ型に抽象データ型を含む場合，又は引数の順序に対応したパラメタのデータ型が完全に一致しない場合は，呼び出す関数は次のように決定します。

抽象データ型を含まない場合

左側の引数から順番に各引数の既定義型を基準として，基準と優先度が同じか又はより優先度が低いデータ型の中で最も優先度の高い既定義型をパラメタに持つ関数を呼び出します。既定義型の優先度を次の表に示します。また，この場合，呼び出す関数がSQL解析時に一意に決まるので，関数の結果のデータ型は呼び出す関数のRETURNS句のデータ型となります。

表4‒3　既定義型の優先度
各引数のデータ型	優先度
数データ	SMALLINT→INTEGER→DECIMAL→SMALLFLT→FLOAT
文字データ	CHAR→VARCHAR
各国文字データ	NCHAR→NVARCHAR
混在文字データ	MCHAR→MVARCHAR

（凡例）　A→B：AがBより優先度が高いことを示します。

抽象データ型を含む場合

抽象データ型を含む場合，次の順番で呼び出す関数を決定します。

基本となる関数の決定

基本となる関数の決定方法は，左側の引数から順番に各引数のデータ型を基準として，基準と優先度が同じか又はより優先度が低いデータ型の中で最も優先度の高いデータ型をパラメタに持つ関数を，基本となる関数とします。データ型が既定義型の場合は，表「既定義型の優先度」の優先度に準じます。データ型が抽象データ型の場合は，次の表に示す優先度に準じます。

表4‒4　抽象データ型の優先度
各引数のデータ型	優先度
抽象データ型	同じデータ型→スーパタイプ^※

（凡例）A→B：AがBより優先度が高いことを示します。

注※　抽象データ型定義中のUNDER句で直接指定するスーパタイプの方が，そのほかのスーパタイプよりも優先度が高くなります。

候補となる関数の決定

引数が抽象データ型の場合，引数のデータとして取り得る実際の値のデータ型は，引数の定義の抽象データ型と同じデータ型又はサブタイプとなります。そのため，基本となる関数のほかに，引数の抽象データ型と同じデータ型又はサブタイプの抽象データ型を対応するパラメタに持つすべての関数が候補となる関数となります。

候補となる関数が，基本となる関数一つだけの場合，その関数が呼び出す関数となります。関数の結果のデータ型は，呼び出す関数のRETURNS句のデータ型になります。
RETURNS句のデータ型を用いた候補となる関数の絞り込み

基本となる関数のRETURNS句のデータ型と，基本となる関数以外の候補となる関数のRETURNS句のデータ型の互換性のチェックをします。RETURNS句のデータ型が互換性のない関数の場合，候補となる関数ではなくなります。また，互換性のチェックの後，残った候補となる関数のRETURNS句のデータ型を基に，関数の結果のデータ型を決定します。結果のデータ型及びデータ長は，集合演算（UNION 〔ALL〕，又はEXCEPT 〔ALL〕）の結果のデータ型及びデータ長と同じになります。詳細（問合せ式）については，マニュアル「HiRDB SQLリファレンス」を参照してください。

ただし，抽象データ型の場合は，基本となる関数のRETURNS句の抽象データ型になります。
SQL文実行時の関数の決定

2及び3で関数が一意に決まらない場合，SQL文実行時に，抽象データ型の引数の実際のデータ型によって，候補となる関数の中から呼び出す関数を一つに決定します。左側の引数より順番に，各引数の実際の値がナル値以外の場合はその値のデータ型を基準として，ナル値の場合はその引数のデータ型を基準とし，その基準のデータ型と同じか又はより優先度が低いデータ型の中で最も優先度の高いデータ型をパラメタとして持つ関数を候補となる関数の中から一つ決定し，呼び出す関数とします。

HiRDBでは，関数を多重定義できるため，呼び出す関数の候補が複数ある場合があります。関数の呼び出しの記述と，関数の定義がどのように一致するかによって，呼び出す関数が決定されます。抽象データ型がある表と呼び出す関数の対応を次の図に示します。

図4‒15　抽象データ型がある表と呼び出す関数の対応

[説明]

例えば，社員表に対して抽象データ型関数「報酬」を使用して検索する，次のようなSQL文があるとします。

　SELECT 社員番号 FROM 社員表 WHERE 報酬(従業員)>=200000

この場合，パラメタの値のデータ型がt_従業員かt_営業部員かによって，それぞれのデータ型に対応した関数が決定され，呼び出されます。

なお，この社員表の定義内容については，マニュアル「HiRDB システム導入・設計ガイド」を参照してください。

注※1　報酬＝基本給×報酬率()

注※2　報酬＝顧客総数()×1000＋基本給×報酬率()

（例）　抽象データ型を含む場合の呼び出し関数の決定: A，B，Cを抽象データ型とし，CをＢのスーパタイプ，BをAのスーパタイプとします（抽象データ型の優先度：A→B→C）。

（例1）

＜前提条件＞

表定義: CREATE TABLE T1(C1 C)
関数定義: f(A)，f(B)，f(C)
SQL文: SELECT f(C1) FROM T1

＜結果＞

基本となる関数

f(C)

関数呼出しがf(C1)の場合の候補となる関数

f(A),f(B),f(C)

呼び出し関数

SQL文実行時に呼び出す関数を次に示します。

T1.C1の実際の値	呼び出し関数
A型	f(A)
B型	f(B)
C型	f(C)
NULL値	f(C)

（例2）

＜前提条件＞

表定義: CREATE TABLE T1(C1 C,C2 B)
関数定義: 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文: SELECT f(C1,C2) FROM T1

＜結果＞

基本となる関数

f(C,B)

関数呼出しがf(C1,C2)の場合の候補となる関数

f(A,A),f(A,B),f(A,C),f(B,A),f(B,C),f(C,A),f(C,B)

呼び出し関数

SQL文実行時に呼び出す関数を次に示します。

T1.C1の実際の値	T1.C2の実際の値	呼び出し関数
A型	A型	f(A,A)
	B型	f(A,B)
	NULL値	f(A,B)
B型	A型	f(B,A)
	B型	f(B,C)
	NULL値	f(B,C)
C型	A型	f(C,A)
	B型	f(C,B)
	NULL値	f(C,B)
NULL値	A型	f(C,A)
	B型	f(C,B)
	NULL値	f(C,B)

ページの先頭へ

4.3.2 ストアドファンクションの定義

(1) SQLストアドファンクションの定義と実行

(2) SQLストアドファンクションの例

(3) 呼び出す関数の決定規則と結果のデータ型

4.3.2　ストアドファンクションの定義

(1)　SQLストアドファンクションの定義と実行

(2)　SQLストアドファンクションの例

(3)　呼び出す関数の決定規則と結果のデータ型