Yet another OSS DB：Firebird（4）

Firebirdの標準関数を極める

Firebird日本ユーザー会
アナハイムテクノロジー
はやしつとむ
2009/4/20
Firebird日本ユーザー会の林です。前回まで2回に渡ってFirebirdのデータベースファイルの内部構造を詳しく見てきました。1つのファイルで複雑なRDBMSの機能を実現しているFirebirdですが、中身はこんなふうになっているのかと興味を持っていただければ幸いです。

　さて、今回は打って変わって、Firebirdの標準関数とUDF（ユーザー定義関数）についてのお話をしたいと思います。

　Firebirdは、オリジナルを書いたJim Starkeyの設計思想によるのだと思いますが、データベースエンジンに取り込んでいる標準関数が極端に少ないことが特徴でした。「でした」と書いたのは現在の最新バージョンであるバージョン2.1からそれが大きく変わったためなのですが、僕が2005年に「Firebird RDBMS傾向と対策〈4〉UDF活用編」を書いたときには、内部関数はわずか10個しかありませんでした。

　そして、この少ししかない内部関数を補完するモノとして、ib_udfやfbudfという「標準UDF」というそれ自体矛盾した名前のライブラリが付属していたのです（それはいまも付属しているのですが）。それでは、詳しく見ていきましょう。

拡張された内部関数

　Firebird 2.1では、これまでib_udfやfbudfで提供されてきた「標準UDF」のほとんどが内部関数として取り込まれました。このため、以前から存在した10種類の関数（CAST、EXTRACT、SUBSTRING、UPPER、GEN_ID、AVG、COUNT、MAX、MIN、SUM）に加えて、新たに53種類の内部関数が利用可能となっています。

　一覧を以下に示します。OracleでおなじみのDECODE関数などもこのバージョンから追加されています。

内部関数の一覧

Firebird2.1内部関数一覧
凡例
関数名 フォーマット
使用例
詳細
内部関数一覧
abs abs(<数値>)
select abs(amount) from transactions;
数値の絶対値を返します
acos acos(<数値>)
select acos(x) from y;
数値のアークコサインを返します。引数の範囲は-1から1まででなくてはなりません。戻り値の範囲は0からπ（円周率）までとなります
ascii_char ascii_char(<数値>)
select ascii_char(x) from y;
指定されたコードのアスキーキャラクタを返します。引数の範囲は0から255まででなくてはなりません。戻り値のキャラクタセットはNONEで返されます
ascii_val ascii_val(<文字列>)
select ascii_val(x) from y;
指定された文字列の1文字目のASCIIコードを返します
1. 空文字列が渡された場合、0が返ります
2. 1文字目がマルチバイト文字の場合、エラーが返されます
asin asin(<数値>)
select a= sin(x) from y;
数値のアークサインを返します。引数の範囲は-1から1まででなくてはなりません。戻り値の範囲は-π/2からπ/2までとなります
atan atan(<数値>)
select a= tan(x) from y;
数値のアークタンジェントを返します。戻り値の範囲は-π/2からπ/2までとなります
atan2 atan2(<数値1>,<数値2>)
select a= tan2(x,y) from z;
数値1/数値2のアークタンジェントを返します。戻り値の範囲は-πからπまでとなります
bin_and bin_and(<数値> [,<数値>…])
select bin_and(flags, 1) from x;
すべての引数をバイナリレベルでAND演算して返します
bin_or bin_or(<数値> [ ,<数値>…])
select bin_or(flags1, flags2) from x;
すべての引数をバイナリレベルOR演算して返します
bin_shl bin_shl(<数値1>, <数値2>)
select bin_shl(flags1, 1) from x;
数値1を数値2だけ左へビットシフトして返します （数値1 << 数値2）
bin_shr bin_shr(<数値1>, <数値2>)
select bin_shr(flags1, 1) from x;
数値1を数値2だけ右へビットシフトして返します （数値1 << 数値2）
bin_xor bin_xor(<数値> [,<数値>…])
select bin_xor(flags1, flags2) from x;
すべての引数をバイナリレベルでXOR演算 して返します
bit_length bit_leng= th(<文字列> | <文字列表現>) 
select
  rdb$relation_name, 
  bit_length(rdb$relation_name),
  bit_length(trim(rdb$relation_name))
from rdb$relations;
文字列の長さをビット長で返します
ceil/ceiling {ceil | ceiling}(<数値>)
  1. select ceil(val) from x;
  2. select ceil(2.1), ceil(-2.1) from rdb$database;
    -- returns 3, -2
数値の小数点以下を切り上げて返します
char_length/
character_length		 char_length(<文字列> | <文字列表現>) 
select
  rdb$relation_name,
  char_length(rdb$relation_name),
  char_length(trim(rdb$relation_name))
from rdb$relations;
文字列または文字列表現の文字数を返します
cos cos(<数値>)
select cos(x) from y;
数値のコサインを返します。数値はラディアンで指定し、戻り値の範囲は-1から1までとなります
cosh cosh(<数値>)
select cosh(x) from y;
数値のハイパボリックコサインを返します
cot cot(<数値>)
select cot(x) from y;
数値のタンジェントで1を割って返します。1/tan(<数値>)
dateadd

1. dateadd( <数値> <timestamp指定子> TO <日付時刻型> )
2. dateadd( <timestamp指定子>, <数値>, <日付時刻型> )
select dateadd(day, -1, current_date)
as yesterday from rdb$database;

/* or (expanded syntax) */
select dateadd(-1 day to current_date) as yesterday from rdb$database;

日付時刻型（data/time/timestamp）の値を、指定された数値で加算したり減算したりして返します。timestamp指定子は、以下のルールに従います

  1. YEAR, MONTH、DAYはtime型には使用できません
  2. HOUR, MINUTE, SECOND, MILLISECOND はdate型には使用できません
  3. timestamp型には全てのtimestamp指定子を使用できます
timestamp指定子 ::= { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND }
datediff

1.datediff(<timestamp指定子> from <日付時刻型> to <日付時刻型>)
2.datediff(<timestamp指定子>, <日付時刻型>, <日付時刻型>)
select datediff( DAY, (cast('TOMORROW' as date) -10), current_date) as datediffresult from rdb$database;

2つの日付時刻型（data/time/timestamp）の値を比較し両者の差を返します。2つめの引数が大きければ正の数を、1つめが大きければ負の数を返します。date型とtime型の比較はできませんが、date型同士、time型同士、date型とtimestamp型、time型とtimestamp型の比較が可能です。timestamp指定子は、以下のルールに従います

  1. YEAR, MONTH, DAY はtime型には使用できません
  2. HOUR, MINUTE, SECOND, MILLISECOND はdate型には使用できません
  3. timestamp型には全てのtimestamp指定子を使用できます
timestamp指定子 ::= { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND }
decode DECODE( <SQL表現>, <検索値>, <戻り値>
[ , <検索値>, <戻り値> ... ] [, <規定値> ]
select decode(state, 0, 'deleted', 1, 'active', 'unknown') from things;
decodeは、CASE…WHEN…ELESE 表現へのショートカットです。SQL表現と一致する検索値に対応した戻り値を返します。一致するものがなければ、規定値を返します
exp exp(<数値>)
select exp(1) from rdb$database;
ネイピア数eを指定された数でべき乗して返します
floor floor(<数値>)
  1. select floor(val) from x;
  2. select floor(2.1) , floor(-2.1) from rdb$database;
       -- returns 2, -3
指定された数の小数点以下を切り捨てます
gen_uuid gen_uuid( ) 　　
insert into records (id) vlaue (gen_uuid());
UUID識別子を返します。引数は取りません
hash hash(<文字列>)
select hash(x) from y;
文字列をhashして返します
left left(<文字列>, <数値>) 
select left(name, char_length(name) - 10)
from people
where name like '% FERNANDES';

select left(rdb$field_name, 2.5) from rdb$relation_fields where rdb$system_
flag=0;

文字列の左から指定された文字数分だけを返します

  1. 文字列の1文字目は1です。0ではありません
  2. 数値が整数でない場合、四捨五入されます
ln ln(<数値>)
  1. select ln(exp(1)) from rdb$database;
      -- returns 1.000000000000000
  2. select ln(2.718281828459045) from rdb$database;
      -- returns 1.000000000000000
  3. select ln(2.71828182845904) from rdb$database;
      -- returns 0.9999999999999981
数値の自然対数を返します
log log(<数値>, <数値>)
select log(2, 8) from rdb$database;
  -- returns 3
log(x, y)は、xを底とするyの対数を返します
log10 log10(<数値>)
select log10(100) from rdb$database;
  -- returns 2
10を底とする対数を返します
lower lower(<文字列>) 
isql -q -ch dos850
create database 'test.fdb';
create table t (c char(1) character set dos850);
insert into t values ('A');
insert into t values ('E');
insert into t values ('A');;
insert into t values ('E');
select c, lower(c) from t;

C LOWER
====== ======
A a
E e
A a
E e
Windowsのコマンドプロンプトで実行する場合には、先にコードページを変更してください（>chcp 850;）。
その後、コマンドプロンプトのプロパティからフォントをLucida Consoleに変更します
文字列をすべて小文字にして返します
lpad lpad(<文字列>, <数値>, [ , <文字列>])

elect lpad(x, 10) from y;

lpad(文字列1, 長さ, 文字列2) と指定すると、文字列1の先頭に指定された長さまで文字列2が埋められます。

  1. 文字列2が省略された時は半角空白が使用されます
  2. 結果の文字列があふれる場合は、文字列2を繰り返した結果が切り捨てられてから、文字列1に接続されます
LPAD/RPADの戻り値は、Varchar(32767)と文字列の最大幅で返ってきます。そのため、明示的にCASTしない場合には、連続した呼び出しがレコードの最大幅を超えてしまいエラーとなってしまいます。CAST(LPAD(...) AS Varchar(40)) などとします。この問題は、Firebird2.5で解消する予定です。
maxvalue maxvalue(<数値>, [<数値>…])
select maxvalue(v1, v2, 10) from x;
与えられた数値のうち最大のものを返します
minvalue minvalue(<数値>, [<数値>…])
select minvalue(v1, v2, 10) from x;
与えられた数値のうち最小のものを返します
mod mod(<数値>, <数値>)
select mod(x, 10) from y;
mod(x, y) は、xをyで割った剰余を返します
octet_length octed_length(<文字列> | <文字列表現>) 
select
rdb$relation_name,
octet_length(rdb$relation_name),
octet_length(trim(rdb$relation_name))
from rdb$relations;
文字列または文字列表現の長さをバイト数で返します
overray overray(<文字列1> PLACING <文字列2> FROM <開始位置> [ FOR <長さ>])
select overlay('rdb$database' PLACING 'hoge' from 2 for 4) from rdb$relations;
文字列1を、開始位置から文字列2で長さ分だけ置換して返します。長さが文字列2の長さより短くても最低文字列2の長さだけ確保され、文字列2の長さより長い場合には文字列1が切り捨てられます
pi pi( )　　
select pi( ) from rdb$database;
π（円周率）を返します。引数はとりません
position 1.position(<文字列1> IN <文字列2>)
2.position(<文字列1>, <文字列2> [, <オフセット>])
select position('_', rdb$relation_name) from rdb$relations;</td>
文字列2の中で、文字列1が現れる位置を返します。2番目のフォーマットの場合には、オフセットから検索して、"先頭から"の位置を返します
power power(<数値>, <数値>)
select power(2, 8) from rdb$database;
power(x, y) は、xのy乗を返します
rand rand( )
select rand() from rdb$database;
0〜1の間の乱数を返します(両端を含む)。引数はとりません
replace replace(<対象文字列>, <検索文字列>, <置換文字列>)
select replace(x, ' ', ',') from y;
<対象文字列>中の<検索文字列>を<置換文字列>で置き換えて返します
reverse reverse(<文字列>)
create index people_email on people computed by (reverse(email));
select * from people where reverse(email) startting with reverse('.br');
文字列を逆転してから返します。右から左への文字列のインデックスから、expression indexを作成すると便利です
right right(<文字列>, <数値>) 
select right(rdb$relation_name, char_length(rdb$relation_name) - 4) 
  from rdb$relations
  where rdb$relation_name like 'RDB$%';
文字列の右側から指定された長さの文字列を返します
round round(<数値>, [<数値>])
select round(salary * 1.1, 0) from people;
指定された桁数（2番目の引数)に四捨五入した値を返します。桁数が負の数の場合または省略された場合、整数部分のみが対象となります。round(123.456, -1) は、120.000
rpad rpad(<文字列1>, <長さ> [,<文字列2>])

elect rpad(x, 10) from y ;

文字列1の最後に、文字列2を付加します

  1. 文字列2が省略された場合、半角空白が使用されます
  2. 結果文字列が長さに満たない場合、文字列2が切り捨てられます
LPAD/RPADの戻り値は、Varchar(32767)と文字列の最大幅で返ってきます。そのため、明示的にCASTしない場合には、連続した呼び出しがレコードの最大幅を超えてしまいエラーとなってしまいます。CAST(LPAD(...) AS Varchar(40)) などとします。この問題は、Firebird2.5で解消する予定です。
sign sign(<数値>)
selectg sign(x) from y;
引数の符号を、1, 0, -1で返します
sin sin(<数値>)
select sin(x) from y;
ラディアンで指定された引数のサインを返します
sinh sinh(<数値>)
select sinh(x) from y;
引数のハイパボリックサインを返します
sqrt sqrt(<数値>)
select sqrt(x) from y;
引数のルートを返します
tan tan(<数値>)
select tan(x) from y;
引数のタンジェントを返します
tanh tanh(<数値>)
select tanh(x) from y;
引数のハイパボリックタンジェントを返します
trim 
trim ([ [ <trim指定子> ] [ <trim文字> ]
from ] <value expression>)
<trim指定子> ::= LEADING | TRAILING | BOTH
<trim文字> ::= <value expression>
 
  1. select
rdb$relation_name,
trim(leading 'RDB$' from rdb$relation_name)
from rdb$relations
where rdb$relation_name starting with 'RDB$';
    2. 

  2. select
    
trim(rdb$relation_name) || ' is a system table'
from rdb$relations
where rdb$system_flag = 1;

文字列から指定された文字、先頭と最後（どちらかまたは両方）から削除します

  1. トリム指定子が省略された場合、BOTHとなります
  2. トリム文字が省略された場合、半角空白となります
  3. トリム指定子とトリム文字（どちらかまたは両方）を指定する場合、FROMは必須です
  4. トリム指定子とトリム文字を両方とも省略する場合、FROMは付けてはいけません
trunc trcun(<数値>, [<数値>]) 
  1. select trunc(x) from y;

    2. 

  2. select trunc(-2.8), trunc(2.8)
from rdb$database; --- returns -2, 2

    3. 

  3. select trunc(987.65, 1), trunc(987.65, -1)
from rdb$database; -- returns 987.60, 980.00
　引数を指定された桁数まで切り捨てた値を返します

1/2 次のページへ

Index
Firebirdの標準関数を極める
→ Page 1
拡張された内部関数
内部関数の一覧

Page 2
標準UDF関数
Yet another OSS DB：Firebird


Database Expert フォーラム 新着記事
＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）

注目のテーマ

Database Expert 記事ランキング

本日月間
＠IT