ORACLE/関数編

オラクルちょこっとリファレンス

HOME > 関数編

関数

関数とはプログラム言語などでおなじみの関数(引数に値を渡すと処理した結果が取得できる)と同じです。SQL文の中に組み込んで使うことができます。

関数を使いこなすと、複雑なSQL文を書くことができます。
関数はなかなか奥が深いです。(^^

使用例(現在日付を「YYYY/MM/DD」型式で取得する)
SELECT TO_CHAR(sysdate, 'YYYY/MM/DD') FROM DUAL;

主要関数一覧

変換関数
TO_CHAR(val[,fm]) valをCHAR型に変換する。
フォーマット形式を指定する場合はfmも指定する。
 (フォーマット形式については→フォーマット形式編をご覧ください。)
TO_NUMBER(str) strをNUMBER型に変換する
TO_DATE(str[,fm]) strをDATE型に変換する。
フォーマット形式を指定する場合はfmも指定する。
 (フォーマット形式については→フォーマット形式編をご覧ください。)
TO_TIMESTAMP(str[,fm]) strをTIMESTAMP型に変換する
フォーマット形式を指定する場合はfmも指定する。
 (フォーマット形式については→フォーマット形式編をご覧ください。)
NVL(a,b) aがnullならbを、nullでなければaを返す
NVL2(a,b,c) aがnullならcを、nullでなければbを返す
CAST(m, n) 値mをデータ型nに変換する
 (例)CAST(1, CHAR(1)) → '1'
文字列操作関数
LENGTH(str) 文字列の長さ(文字数)を返す
(例)LENGTH('東京都') → 3
LENGTHB(str) 文字列の長さ(バイト数)を返す
(例)LENGTHB('東京都') → 6
LOWER(str) 文字列を全て小文字にして返す
(例)LOWER('aBcDe') → 'abcde'
UPPER(str) 文字列を全て大文字にして返す
(例)LOWER('aBcDe') → 'ABCDE'
SUBSTR(str,m[,n]) strのm文字目からn文字分取り出す
(nを省略するとstrのm文字目から最後まで取り出す)
 (例)SUBSTR('YOKOHAMA',5,3) → HAM、
     SUBSTR('YOKOHAMA',5) → HAMA
SUBSTRB(str,m[,n]) strのmバイト目からnバイト分取り出す
(nを省略するとstrのmバイト目から最後まで取り出す)
 (例)SUBSTRB('YOKOHAMA',5,3) → HAM、
   SUBSTRB('富士山',1,4) → '富士'
   SUBSTRB('富士山',1,5) → '富士 '(※2バイト文字の途中だったら  半角スペースになる)
TRIM(str) strの左右の空白を無くす
(例)TRIM(' ABC ') → 'ABC'
RTRIM(str) strの右の空白を無くす
(例)RTRIM(' ABC ') → ' ABC'
LTRIM(str) strの左の空白を無くす
(例)LTRIM(' ABC ') → 'ABC '
INSTR(str1,str2[,m[,n]]) str1のm文字目からstr2を検索してn番目に見つかった
文字位置を返すm,nを省略した場合は1とみなされる
 (例)INSTR('ABCDECD','CD',1,2) → 6
DECODE(v1,v2,v3,v4) v1 = v2 だったらv3を返し、違ったらv4を返す
 (例)DECODE(1, 1, '男', '女') → 男
DECODE(v1,v2,v3,v4,v5,v6 ...) v1 = v2 だったらv3を返し、v1 = v4 だったらv5を返し、
それ以外だったらv6を返す
※パラメータは255個まで
 (例)DECODE(2, 1, '赤', 2, '青', 3,'黄', '黒') → 青
INITCAP(str) 先頭の文字だけ大文字でその他は小文字に変換する
 (例)INITCAP('oracle') → Oracle
LPAD(str1, m, str2) str1の左側にstr2を埋めてm文字で返す
(例)LPAD('234',6,'0') → 000234
RPAD(str1, m, str2) str1の右側にstr2を埋めてm文字で返す
(例)RPAD('234',6,'0') → 234000
REPLACE(str1, str2, str3) str1にあるstr2をstr3へ変換する
 (例)REPLACE('ABCABCABC','C','X') → ABXABXABX
TRANSLATE(str1, str2, str3) 説明が難しいので例を示します。
例1:translate('ABCHIJ', 'ABCDEFGHIJ','1234567890') →123890
例2:translate('ACEGJ', 'ABCDEFGHIJ','1234567890') →13570
ASCII(char) 文字charに対応するASCIIコードを返す
 (例)ASCII('A') →65
CHR(n) ASCIIコードnに対応する文字を返す
 (例)CHR(65) →A
 (例)CHR(33440) →あ ※10進:33440=16進:82A0
TO_MULTI_BYTE(str) strを2バイト文字に変換する
 (例)TO_MULTI_BYTE('1Aa') → 1Aa
TO_SINGLE_BYTE(str) strを1バイト文字に変換する
 (例)TO_SINGLE_BYTE('¥aA') → \aA
GREATEST(str1[,strn..]) 引数の中で一番大きい数を返す
 (例)GREATEST(1,4,6,3,32,9) → 32
LEAST(str1[,strn..]) 引数の中で一番小さい数を返す
 (例)LEAST(1,4,6,3,32,9) → 1
REVERSE 文字列を逆にする
 (例)REVERSE('ABCDE') → 'EDCBA'
計算関数
MOD(m,n) mをnで割った余りを返す
 (例)MOD(6,4) → 2
TRUNC(m[,n]) mを小数点以下n桁で切り捨てる
(nを省略すると小数点以下全て切り捨てられる)
 (例)TRUNC(12.987,2) → 12.98、TRUNC(12.987) → 12
ROUND(m[,n]) 小数mを小数点以下n桁目で四捨五入する
 (例)ROUND(12.987,2) → 12.99、ROUND(1234.56,-2) → 1200
CEIL(m) 小数mを切り上げた整数を返す
 (例)CEIL(1.732) → 2、CEIL(-2.3) → -2
FLOOR(m) 小数mを切り下げた整数を返す
 (例)FLOOR(42.195) → 42、FLOOR(-42.195) → -43
SIGN(m) 数値mがマイナスなら-1、プラスなら1、ゼロなら0を返す
 (例)SIGN(-9) → -1、 SIGN(3) → 1、 SIGN(0) → 0
ABS(m) mの絶対値を返す
 (例)ABS(-8) → 8、ABS(8) → 8
POWER(m, n) mのn乗を返す
 (例)POWER(2,3) → 8
SQRT(m) mの平方根を返す
 (例)SQRT(3) → 1.73205081
集合関数
SUM(<カラム名>) 指定したカラムの合計値を求める
COUNT(<カラム名>) 指定したカラムの件数を求める
MAX(<カラム名>) 指定したカラムの最大値を返す
MIN(<カラム名>) 指定したカラムの最小値を返す
AVG(<カラム名>) 指定したカラムの平均値を返す
日付関数
ADD_MONTHS(d,m) 日付dにmヶ月足した日付を返す
 (例)ADD_MONTHS(TO_DATE('05-11-8'),4) → 06-03-08
LAST_DAY(d) 日付dの月の最終日を返す
 (例)LAST_DAY(TO_DATE('05-11-8')) → 05-11-30
NEXT_DAY(d,m) 日付d以降で最も近い曜日mの日付を返す
(曜日は1~7が日~土に対応)
 (例)NEXT_DAY(TO_DATE('06-03-03', 2)) → 06-03-06
EXTRACT
 ([year|month|day|hour|
 minute|second] from d)
TIMESTAMP型dの年月日時分秒のみを取得する
※年月日のみDATE型でもOK
 (例)EXTRACT(year from TO_DATE('2005/11/08')) → 2005
MONTHS_BETWEEN(d1,d2) 日付型d1と日付型d2の間の月数を返す
 (例)MONTHS_BETWEEN(TO_DATE('05-11-08'), TO_DATE('06-05-8')) → -6
ROUND(d[,fm]) 日付dを指定したフォーマットfmで四捨五入する
(フォーマット形式については→フォーマット形式編をご覧ください。)
 (例)ROUND(TO_DATE('06-03-03'),'YY') → 06-01-01、
     ROUND(TO_DATE('06-03-03'),'MM') → 06-03-01
SYSDATE 現在日時(年月日時分秒)を返す
(例)SELECT SYSDATE FROM DUAL;
SYSTIMESTAMP 現在日時(年月日時分秒ミリ秒)を返す
(例)SELECT SYSTIMESTAMP FROM DUAL;
その他
USERENV('isdba') 接続ユーザがDBAロールをもっているかどうかを返す
(持っていればTRUE、無ければFALSE)
USERENV('language') 使用している言語を返す
(JAPANESE_JAPAN.JA16SJIS などが返る)
USERENV('sessionid') セッション識別子を返す
USERENV('terminal') 端末名を返す
USERENV('instance') インスタンス識別番号を返す
ToTop