Firebirdの標準関数を極める
アナハイムテクノロジー
はやしつとむ
2009/4/20
さて、今回は打って変わって、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関数などもこのバージョンから追加されています。
内部関数の一覧
凡例 | |||||||
---|---|---|---|---|---|---|---|
| |||||||
内部関数一覧 | |||||||
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}(<数値>) | ||||||
|
|||||||
数値の小数点以下を切り上げて返します | |||||||
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 <日付時刻型> ) |
||||||
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指定子は、以下のルールに従います
timestamp指定子 ::= { YEAR | MONTH | DAY |
HOUR | MINUTE | SECOND | MILLISECOND } |
|||||||
datediff | 1.datediff(<timestamp指定子> from <日付時刻型> to <日付時刻型>) |
||||||
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指定子は、以下のルールに従います
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(<数値>) | ||||||
|
|||||||
指定された数の小数点以下を切り捨てます | |||||||
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; |
|||||||
文字列の左から指定された文字数分だけを返します
|
|||||||
ln | ln(<数値>) | ||||||
|
|||||||
数値の自然対数を返します | |||||||
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が埋められます。
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を付加します
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> |
||||||
|
|||||||
文字列から指定された文字、先頭と最後(どちらかまたは両方)から削除します
|
|||||||
trunc | trcun(<数値>, [<数値>]) | ||||||
|
|||||||
引数を指定された桁数まで切り捨てた値を返します |
1/2 |
Index | |
Firebirdの標準関数を極める | |
Page 1 拡張された内部関数 内部関数の一覧 |
|
Page 2 標準UDF関数 |
Yet another OSS DB:Firebird |
- Oracleライセンス「SE2」検証 CPUスレッド数制限はどんな仕組みで制御されるのか (2017/7/26)
データベース管理システムの運用でトラブルが発生したらどうするか。DBサポートスペシャリストが現場目線の解決Tipsをお届けします。今回は、Oracle SE2の「CPUスレッド数制限」がどんな仕組みで行われるのかを検証します - ドメイン参加後、SQL Serverが起動しなくなった (2017/7/24)
本連載では、「SQL Server」で発生するトラブルを「どんな方法で」「どのように」解決していくか、正しい対処のためのノウハウを紹介します。今回は、「ドメイン参加後にSQL Serverが起動しなくなった場合の対処方法」を解説します - さらに高度なSQL実行計画の取得」のために理解しておくべきこと (2017/7/21)
日本オラクルのデータベーススペシャリストが「DBAがすぐ実践できる即効テクニック」を紹介する本連載。今回は「より高度なSQL実行計画を取得するために、理解しておいてほしいこと」を解説します - データベースセキュリティが「各種ガイドライン」に記載され始めている事実 (2017/7/20)
本連載では、「データベースセキュリティに必要な対策」を学び、DBMSでの「具体的な実装方法」や「Tips」などを紹介していきます。今回は、「各種ガイドラインが示すコンプライアンス要件に、データベースのセキュリティはどのように記載されているのか」を解説します
|
|