スケーラブルデータベースサーバ HiRDB Version 8 UAP開発ガイド
ストアドファンクションは,SQLで記述した一連のデータベース操作を,ユーザ定義関数としてデータベースに登録しておく機能です。
SQLストアドファンクションは,CREATE FUNCTION又はCREATE TYPE実行時にユーザ定義関数がデータベースに登録され,DROP FUNCTION実行時に削除されます。
登録したユーザ定義関数は,SQL文中に関数呼出しを指定すれば,その関数を呼び出して実行します。
SQLオブジェクトが無効になっている関数がある場合には,ALTER ROUTINEで関数を再作成することもできます。
SQLストアドファンクションの定義と実行を次の図に示します。
図4-13 SQLストアドファンクションの定義と実行
SQLストアドファンクションの例として,ルーチン制御SQLを組み合わせてユーザ定義関数(ファンクション)として定義する例とそれを呼び出して実行する例を次の図に示します。
図4-14 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
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
CREATE FUNCTION NEXTDAY(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
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
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
CREATE FUNCTION YYYYMM20(INDATE DATE) RETURNS CHAR(7) BEGIN RETURN SUBSTR(CHAR(INDATE+1 MONTH -20 DAYS),1,7); END
CREATE FUNCTION YYYY0320(INDATE DATE) RETURNS CHAR(4) BEGIN RETURN SUBSTR(CHAR(INDATE-2 MONTHS -20 DAYS),1,4); END
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
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
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
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
表4-3 既定義型の優先度
各引数のデータ型 | 優先度 |
---|---|
数データ | SMALLINT→INTEGER→DECIMAL→SMALLFLT→FLOAT |
文字データ | CHAR→VARCHAR |
各国文字データ | NCHAR→NVARCHAR |
混在文字データ | MCHAR→MVARCHAR |
(凡例) A→B:AがBより優先度が高いことを示します。
表4-4 抽象データ型の優先度
各引数のデータ型 | 優先度 |
---|---|
抽象データ型 | 同じデータ型→スーパタイプ※ |
(凡例)A→B:AがBより優先度が高いことを示します。
注※ 抽象データ型定義中のUNDER句で直接指定するスーパタイプの方が,そのほかのスーパタイプよりも優先度が高くなります。
HiRDBでは,関数を多重定義できるため,呼び出す関数の候補が複数ある場合があります。関数の呼び出しの記述と,関数の定義がどのように一致するかによって,呼び出す関数が決定されます。抽象データ型がある表と呼び出す関数の対応を次の図に示します。
図4-15 抽象データ型がある表と呼び出す関数の対応
SELECT 社員番号 FROM 社員表 WHERE 報酬(従業員)>=200000
T1.C1の実際の値 | 呼び出し関数 |
---|---|
A型 | f(A) |
B型 | f(B) |
C型 | f(C) |
NULL値 | f(C) |
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) |
All Rights Reserved. Copyright (C) 2006, 2016, Hitachi, Ltd.