Oracle SQL 関数その１
このページはページの再構築に伴い移転しました。3秒後にジャンプします。
ジャンプしない場合は、以下のURLをクリックしてください。
- 翌月日計算
- コードデータ置換え
- 指定月の最終日計算
- 日時フィールドを返す
- 月数の計算
- 文字列置換
- 文字列置換
- 数値の丸め処理
- 日付の丸め処理
- 符号チェック
- 文字列切出し
- 文字列検索
- NULLデータ置換え
ADD_MONTHS ： 翌月日計算
dateRet := ADD_MONTHS( dateSrc , N ); ADD_MONTHSは日付dataSrcにNヵ月後の結果を返します。dataSrcの月数にNを加算した結果、日付が その月の最終日を越える場合は、日の部分はその月の最終日を返します。
SQL> SELECT SYSDATE,ADD_MONTHS(SYSDATE,1) AS ADD_1,
2 ADD_MONTHS(SYSDATE,13) AS ADD_13,
3 ADD_MONTHS(TO_DATE('2002/01/31'),1) AS ADD_N FROM DUAL;
SYSDATE ADD_1 ADD_13 ADD_N
-------- -------- -------- --------
02-11-07 02-12-07 03-12-07 02-02-28
ADD_MONTHSの例としてADD_MONTHS(SYSDATE,13)はシステム日付の1年と1ヵ月後の日付を返します。
また、最後のADD_MONTHS(TO_DATE('2002/01/31'),1)は1ヶ月後の2月には31日は存在しない為、2月の最終日が結果として帰ります。
DECODE ： コードデータ置換え
DECODE( <式1>,<判定値1>,<結果値1>[,<判定値2>,<結果値2>, ... ] [ ,<デフォルト値> ] ) DECODEは<式1>を<判定値1>から判定し等しい場合はその<結果値1>を返します。等しくない場合は順次 <判定値2>,<判定値3>...と処理を進めます。全て等しくない場合はデフォルト値が設定されている場合 そのデフォルト値を返します。全て一致しない場合はNULLを返します。 このDECODEを利用することで1個のSQL文の中でIF～THEN～ELSEの処理を記述することができます。
SQL> SELECT DECODE(1,1,'CODE1',2,'CODE2',3,'CODE3','OTHER') AS DEC1, 2 DECODE(3,1,'CODE1',2,'CODE2',3,'CODE3','OTHER') AS DEC3, 3 DECODE(0,1,'CODE1',2,'CODE2',3,'CODE3','OTHER') AS DEC0, 4 DECODE(0,1,'CODE1',2,'CODE2',3,'CODE3') AS DECN 5 FROM DUAL; DEC1 DEC3 DEC0 D ----- ----- ----- - CODE1 CODE3 OTHER
4番目のDECODEは結果をNULLを返していますが、以下のSQLであればNULLであることがはっきりします。
SQL> SELECT NVL(DECODE(0,1,'CODE1',2,'CODE2',3,'CODE3'),'This is NULL.') FROM DUAL; NVL(DECODE(0, ------------- This is NULL.
その他のDECODEの便利な使い方としては、DECODEの結果とCOUNT,SUM等の関数を組み合わせるといろいろな
応用が利くと思います。以下に、EMP表を用いてDEPTNO毎の件数とSALの合計を計算しています。
SQL> SELECT SUM(DECODE(DEPTNO,10,1,0)),SUM(DECODE(DEPTNO,20,1,0)),SUM(DECODE(DEPTNO,30,1,0)),
2 SUM(DECODE(DEPTNO,10,SAL,0)),SUM(DECODE(DEPTNO,20,SAL,0)),SUM(DECODE(DEPTNO,30,SAL,0))
3 FROM EMP;
SUM(DECODE(DEPTNO,10,1,0)) SUM(DECODE(DEPTNO,20,1,0)) SUM(DECODE(DEPTNO,30,1,0))
-------------------------- -------------------------- --------------------------
SUM(DECODE(DEPTNO,10,SAL,0)) SUM(DECODE(DEPTNO,20,SAL,0)) SUM(DECODE(DEPTNO,30,SAL,0))
---------------------------- ---------------------------- ----------------------------
3 5 6
8750 10875 9400
上のSELECT文の件数を取得する部分を以下のSELECT文の様にCOUNT()関数を用いても可能です。
COUNTはNULL値を計数しないのでDECODEのデフォルト値をNULLにしています。
(COUNT,SUM,AVGのグループ関数はNULLを処理の対象としないことを利用しています)
SQL> SELECT COUNT(DECODE(DEPTNO,10,1,NULL)),COUNT(DECODE(DEPTNO,20,1,NULL)),COUNT(DECODE(DEPTNO,30,1,NULL))
2 FROM EMP;
COUNT(DECODE(DEPTNO,10,1,NULL)) COUNT(DECODE(DEPTNO,20,1,NULL)) COUNT(DECODE(DEPTNO,30,1,NULL))
------------------------------- ------------------------------- -------------------------------
3 5 6
LAST_DAY ： 指定月の最終日計算
dateRet := LAST_DAY( dateSrc ); LAST_DAYは日付dataSrcの末日の日付を返します。この関数を使えば簡単に該当月の最終日を計算してくれる ので一般の言語の様に別に関数を設ける必要がありません。
SQL> SELECT LAST_DAY(TO_DATE('2000/02/01')),LAST_DAY(TO_DATE('2001/02/01')),
2 LAST_DAY(TO_DATE('2002/02/01')),LAST_DAY(TO_DATE('2003/02/01'))
3 FROM DUAL;
LAST_DAY LAST_DAY LAST_DAY LAST_DAY
-------- -------- -------- --------
00-02-29 01-02-28 02-02-28 03-02-28
この例は2000年から2003年の2月の末日を求めています。閏年の計算をしなくても簡単に行えます。
SQL> SELECT
2 LAST_DAY(SYSDATE) AS SYSDATE_LAST
3 ,TO_CHAR(LAST_DAY(SYSDATE),'YYYY/MM/DD HH24:MI:SS') AS 日付文字列
4 ,TO_CHAR(TRUNC(LAST_DAY(SYSDATE),'DD'),'YYYY/MM/DD HH24:MI:SS') AS 日付文字列2
5 ,LAST_DAY(TO_DATE('2004/02/01')) FROM DUAL;
SYSDATE_ 日付文字列 日付文字列2 LAST_DAY
-------- ------------------- ------------------- --------
04-10-31 2004/10/31 17:53:25 2004/10/31 00:00:00 04-02-29
LAST_DAYは日付部分は月末を返すが、時刻部分は指定日付型の時刻をそのまま引き継ぐ様です。上のSQLを見ると良くわかるとは思いますが、もし時刻が00:00:00が必要であればTRUNC関数で切り捨てる必要があります。
EXTRACT ： 日時フィールドを返す
datetimeRet := EXTRACT( [YEAR,MONTH,DAY,HOUR,MINUTE,SECOND] FROM exp1 ); EXTRACTはexp1の指定された日時フィールドを返します。
SQL> SELECT
2 EXTRACT(YEAR FROM TO_DATE('2004/10/20')) AS YEAR
3 ,EXTRACT(MONTH FROM TO_DATE('2004/10/20')) AS MONTH
4 ,EXTRACT(DAY FROM TO_DATE('2004/10/20')) AS DAY
5 ,TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') AS SYSTEM_DATE
6 ,EXTRACT(HOUR FROM TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'))) AS HOUR
7 ,EXTRACT(MINUTE FROM TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'))) AS MINUTE
8 ,EXTRACT(SECOND FROM TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'))) AS SECOND
9 FROM DUAL;
YEAR MONTH DAY SYSTEM_DATE HOUR MINUTE SECOND
---------- ---------- ---------- ------------------- ---------- ---------- ----------
2004 10 20 2004/10/20 11:59:07 11 59 7
上記のSQLでは２～４行では年,月,日の値をそれぞれ返し、６～８行では時,分,秒を返します。６～８のTIMESTAP型データをDATE型に変更するとエラーが発生します。時,分,秒を取得するにはTIMESTAP型データでなければならない様です。
■MONTHS_BETWEEN ： 月数の計算
dateRet := MONTHS_BETWEEN( date1 , date2 ); MONTHS_BETWEENはdate1とdate2の間の月数を返します。date1がdate2より大きい場合は正の値を、また date2がdate1より大きい場合は負の値を返します。同一日での比較の場合、及び月末同士の比較の場合は 結果は整数の値になります。その他の場合は、1ヶ月を３１日とした換算値が返されます。
SQL> SELECT
2 MONTHS_BETWEEN(TO_DATE('2004/02/14'),TO_DATE('2004/01/14')) AS 月違いの同一日
3 ,MONTHS_BETWEEN(TO_DATE('2004/01/14'),TO_DATE('2004/02/14')) AS 同上負値
4 ,MONTHS_BETWEEN(TO_DATE('2004/01/14'),TO_DATE('2004/01/13')) AS 一日違い
5 ,MONTHS_BETWEEN(TO_DATE('2004/02/29'),TO_DATE('2004/01/27')) AS 閏年1
6 ,MONTHS_BETWEEN(TO_DATE('2004/02/29'),TO_DATE('2004/01/28')) AS 閏年2
7 ,MONTHS_BETWEEN(TO_DATE('2004/02/29'),TO_DATE('2004/01/29')) AS 閏年3
8 ,MONTHS_BETWEEN(TO_DATE('2004/02/29'),TO_DATE('2004/01/30')) AS 閏年4
9 ,MONTHS_BETWEEN(TO_DATE('2004/02/29'),TO_DATE('2004/01/31')) AS 閏年5
10 ,MONTHS_BETWEEN(TO_DATE('2004/11/30'),TO_DATE('2004/10/29')) AS T29
11 ,MONTHS_BETWEEN(TO_DATE('2004/11/30'),TO_DATE('2004/10/30')) AS T30
12 ,MONTHS_BETWEEN(TO_DATE('2004/11/30'),TO_DATE('2004/10/31')) AS T31
13 FROM DUAL;
月違い同一日 同上負値 一日違い 閏年1 閏年2 閏年3 閏年4 閏年5 T29 T30 T31
------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 -1 .032258065 1.06451613 1.03225806 1 .967741935 1 1.03225806 1 1
上記に、いろいろな場合のMONTHS_BETWEENの結果を示します。少し問題があるとすれば、閏年の場合には２９日を1ヶ月として計算しているようである。但し、1月31日には月末の処理がなされて結果は1ヶ月の差として返されている。このあたりは、月数を処理する場合には注意が必要かもしれません。
尚、MONTHS_BETWEENの応用として、年齢の計算について以下に示します。
SQL> SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,TO_DATE('1964/10/12'))/12) AS AGE FROM DUAL;
AGE
----------
40
REPLACE ： 文字列置換
charRet := REPLACE( charSrc , strSearch [ , strReplace ] ); REPLACEは文字データcharSrcを先頭からサーチし、文字列strSearchと一致する部分を文字列strReplaceに 置き換えます。strReplaceが指定されていない場合は置換えではなく一致する部分を削除します。
SQL> SELECT REPLACE('AABBBCCCCDDDDD','AA','XXXX'),REPLACE('AABBBCCCCDDDDD','AA') FROM DUAL;
REPLACE('AABBBCC REPLACE('AAB
---------------- ------------
XXXXBBBCCCCDDDDD BBBCCCCDDDDD
この例では最初は置換え処理を、2番目には削除処理を示しています。
TRANSLATE： 文字列変換
charRet := TRANSLATE( charSrc , strSearch , strTranslate ); TRANSLATEは文字データcharSrcを先頭からサーチし、文字列strSearchと一致する文字があった場合に 変換文字列strTranslateの対応する文字に置換します。 strTranslateがstrSearchより短い場合は、strSearchに対応する文字を削除します。
SQL> SELECT
2 TRANSLATE('1234321', '123', 'ABC') AS TEST1 /* 1⇒A, 2⇒B, 3⇒C */
3 ,TRANSLATE('1234321', '123', 'AB') AS TEST2 /* 1⇒A, 2⇒B, 3⇒削除 */
4 ,TRANSLATE('1234321', '12', 'ABC') AS TEST3 /* 1⇒A, 2⇒B*/
5 ,TRANSLATE('あいうえお', 'あいう', 'AB') AS TEST4 /* あ⇒A, い⇒B, う⇒削除 */
6 ,TRANSLATE('ABCDE', 'ABC', 'あい') AS TEST5 /* A⇒あ, B⇒い, C⇒削除 */
7 FROM DUAL;
TEST1 TEST2 TEST3 TEST4 TEST5
------- ----- ------- ------ ------
ABC4CBA AB4BA AB343BA ABえお あいDE
TRANSLATEの注意点は、取扱が文字単位でありバイト単位ではないことです。上の例からも分かる様に、
シングルバイト文字とマルチバイト文字との変換が可能です。
但し、１文字単位での処理のため、「ﾀﾞﾁﾞﾂﾞﾃﾞﾄﾞ」などの半角の濁音文字を「ダヂヅデド」全角文字に変換するには
１回のTRANSLATEでは不可能ですので、PL/SQLで自分で関数を作るしかないかもしれません。
ROUND(数値) ： 丸め処理
numberRet := ROUND( numberSrc [ , numberUnit ] ); ROUNDは数値データnumberSrcを小数点以下numberUnit桁までに丸めた数値を返します。 numberUnitが省略されると０(ゼロ)と指定したことと同様で、小数点以下を丸めます。 numberUnitが負の場合は、小数点から見てnumberUnit*(-1)の位置の桁を丸めます。
| numberUnit値 | 丸める桁 |
|---|---|
| 3 | 小数点以下４桁目 |
| 2 | 小数点以下３桁目 |
| 1 | 小数点以下２桁目 |
| 0 | 小数点以下１桁目 |
| -1 | 一の位の桁 |
| -2 | 十の位の桁 |
| -3 | 百の位の桁 |
SQL> SELECT ROUND(1234.5678, 3) DP4 ,ROUND(1234.5678, 2) DP3 ,
2 ROUND(1234.5678, 1) DP2 ,ROUND(1234.5678, 0) DP1 ,
3 ROUND(1234.5678,-1) DPL1,ROUND(1234.5678,-2) DPL2,ROUND(1234.5678,-3) DPL3
4 FROM DUAL;
DP4 DP3 DP2 DP1 DPL1 DPL2 DPL3
---------- ---------- ---------- ---------- ---------- ---------- ----------
1234.568 1234.57 1234.6 1235 1230 1200 1000
ROUND(日付) ： 丸め処理
dateRet := ROUND( dateSrc [ , dateFormat ] ); ROUNDは日付データdateSrcをdateFormatに従って丸めた日付を返します。 dateFormatが省略されると最も近い日に丸められます。
| dateFormat | 丸め処理 |
|---|---|
| YYYY,YEAR | 年(7月1日で切り上げ) |
| Q | 四半期 |
| MONTH,MM | 月(16日で切り上げ) |
| DD | 日 |
| DAY | 週の開始日 |
| HH,HH12,HH24 | 時 |
| MI | 分 |
SQL> SELECT ROUND(TO_DATE('2002/06/30'),'YEAR') YEAR0630,
2 ROUND(TO_DATE('2002/07/01'),'YEAR') YEAR0701,
3 ROUND(TO_DATE('2002/06/30'),'Q') Q,
4 ROUND(TO_DATE('2002/06/15'),'MM') MM0615 ,
5 ROUND(TO_DATE('2002/06/16'),'MM') MM0616,
6 ROUND(TO_DATE('2002/06/15 23:50:00','YYYY/MM/DD HH24:MI:SS'),'DD') DD,
7 TO_CHAR(ROUND(TO_DATE('2002/06/15 23:29:00','YYYY/MM/DD HH24:MI:SS'),'HH24'),
8 'YYYY/MM/DD HH24:MI:SS') HH24
9 FROM DUAL;
YEAR0630 YEAR0701 Q MM0615 MM0616 DD HH24
-------- -------- -------- -------- -------- -------- -------------------
02-01-01 03-01-01 02-07-01 02-06-01 02-07-01 02-06-16 2002/06/15 23:00:00
SIGN ： 符号チェック
numberRet := SIGN( numberSrc ); SIGNは数値データnumberSrcが0(ゼロ)より小さい場合は-1を返し、numberSrcが0(ゼロ)の 場合は0(ゼロ)を返し、numberSrcが0(ゼロ)より大きい場合は1を返します。 numberSrcは数値を返す式を指定できます。
SQL> SELECT SIGN(-12),SIGN(0),SIGN(10) FROM DUAL;
SIGN(-12) SIGN(0) SIGN(10)
---------- ---------- ----------
-1 0 1
このSIGN関数と前述のDECODE関数を組み合わせることで、割と複雑なことが可能になります。
以下に簡単な例を示します。
SQL> SELECT EMPNO,SAL,DECODE(SIGN(SAL - 1500),-1,'1500未満',0,'1500同じ',1,'1500より大きい') FROM EMP;
EMPNO SAL DECODE(SIGN(SA
---------- ---------- --------------
7369 800 1500未満
7499 1600 1500より大きい
7521 1250 1500未満
7566 2975 1500より大きい
7654 1250 1500未満
7698 2850 1500より大きい
7782 2450 1500より大きい
7788 3000 1500より大きい
7839 5000 1500より大きい
7844 1500 1500同じ
7876 1100 1500未満
EMPNO SAL DECODE(SIGN(SA
---------- ---------- --------------
7900 950 1500未満
7902 3000 1500より大きい
7934 1300 1500未満
14行が選択されました。
転職を本気で考えている方向けのプログラミングスクール！【WebCampPRO】
SUBSTR ： 文字列切出し
stringRet := SUBSTR( stringSrc , numberPos [ , numberLen ] ); SUBSTRは文字列stringSrcのnumberPos番目からnumberLen文字分の文字列を返します。 (stringSrcの文字列の数え方は先頭が１として処理します。) numberPosが0(ゼロ)の場合は１として処理します。 numberPosが正の数の場合はstringSrcの先頭から数え，numberPosが負の数の場合はstringSrcの 最後から数えます。 numberLenを指定しない場合は、stringSrcのnumberPos番目から最後までの文字列を返します。
SQL> SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',10,5),
2 SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',10),
3 SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',0,5),
4 SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',-5,4)
5 FROM DUAL;
SUBST SUBSTR('ABCDEFGHI SUBST SUBS
----- ----------------- ----- ----
JKLMN JKLMNOPQRSTUVWXYZ ABCDE VWXY
INSTR ： 文字列検索
numberRet := INSTR( stringSrc , stringChk , numberPos [ , numberOcr ] ); INSTRは文字列stringSrcのnumberPos番目からstringChk文字列を検索します。 numberOcr回数の検索ができた位置を結果として返します。 (stringSrcの文字列の数え方は先頭が１として処理します。) numberPos及びnumberOcrのデフォルトは１として処理します。 numberPosが正の数の場合はstringSrcの先頭から数え，numberPosが負の数の場合はstringSrcの 最後から数えます。
SQL> SELECT INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ','Z',1) AS 回数指定無し
2 ,INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ','A',-1) AS 回数指定無しで逆方向
3 ,INSTR('ABCDEFGHIABCDEFGHIABCDEFGHI','A',1,3) AS 回数指定有り
4 ,INSTR('ABCDEFGHIABCDEFGHIABCDEFGHI','A',2,2) AS 回数指定有りで途中から検索
5 FROM DUAL;
回数指定無し 回数指定無しで逆方向 回数指定有り 回数指定有りで途中から検索
------------ -------------------- ------------ --------------------------
26 1 19 19
実際のテーブルを使用した例を以下に示します。商品マスタを例に取りますが、商品名の中に”－”が
含まれていますが、それを検索して商品名の前半と後半に分ける処理をさせます。最初に商品マスタの一覧を示します。
SQL> select * from TM_商品;
商品コード 商品名 商品区分 仕入単価 売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
1 PC-9001 デスクトップパソコン 65000 98000
2 PC-9002 デスクトップパソコン 95000 120000
3 PC-9003 デスクトップパソコン 150000 190000
4 NOTE-1010 ノートパソコン 125000 188000
5 NOTE-1020 ノートパソコン 145000 200000
6 NOTE-1030 ノートパソコン 155000 220000
7 PRT-3001 プリンタ 45000 88000
8 PRT-4001 プリンタ 115000 180000
9 CRT-1001 ディスプレイ 45000 78000
10 CRT-2001 ディスプレイ 55000 98000
11 HUB-A001 ネットワーク 20000 40000
12 HUB-B001 ネットワーク 40000 60000
100 TEST1000 デスクトップパソコン 100000 120000
13行が選択されました。
SUBSTR関数の引数の中に、INSTRの結果を渡しています。文字列の中を検索して、文字列の抽出や置換えにはこの様な方法が使えると思います。
SQL> SELECT '#' || SUBSTR(商品名,INSTR(商品名,'-') + 1) || '#' AS 商品名のハイフン以降 2 ,'#' || SUBSTR(商品名,1,INSTR(商品名,'-')) || '#' AS 商品名のハイフン以前 3 FROM TM_商品; 商品名のハイフン以降 商品名のハイフン以前 ---------------------------------------- ---------------------------------------- #9001# #PC-# #9002# #PC-# #9003# #PC-# #1010# #NOTE-# #1020# #NOTE-# #1030# #NOTE-# #3001# #PRT-# #4001# #PRT-# #1001# #CRT-# #2001# #CRT-# #A001# #HUB-# #B001# #HUB-# #TEST1000# ## 13行が選択されました。
実際のSQL/PLUSでこのSQLを実行すると、各カラムの長さが非常に長くなり各行が1行では表示できなくなります。この場合には、PLUSのコマンドのCOLUMNを使用して表示カラムの、表示形式を設定すれば1行で表示可能になります。
SQL> COLUMN 商品名のハイフン以降 FORMAT A40 SQL> COLUMN 商品名のハイフン以前 FORMAT A40
NVL , NVL2 ： NULL文字列置換
dataRet := NVL( dataSrc , dataChg ); NVLはデータdataSrcがNULLの場合、dataChgを結果として返し、NULLで無い場合は dataSrcそのものを返します。 戻り値のデータ型はdataSrcのデータ型と同じになります。
SQL> SELECT NVL(NULL,'IS NULL') , NVL('123','IS NULL') FROM DUAL;
NVL(NUL NVL
------- ---
IS NULL 123
dataRet := NVL2( dataSrc , dataNotNull, dataNull ); NVL2はデータdataSrcがNULLの場合dataNullを結果として返し、NULL以外の場合dataNotNullを返します。 dataNotNullと dataNullのデータ型が異なりdataNullがNULLで無い場合にはdataNullをdataNotNullの データ型に変換します。 戻り値のデータ型はdataNotNullのデータ型と同じになります。 dataNotNullが文字列型の場合はVRACHAR2型になります。
SQL> SELECT NVL2(NULL,'IS NOT NULL','IS NULL') , NVL2('123','IS NOT NULL','IS NULL') FROM DUAL;
NVL2(NU NVL2('123',
------- -----------
IS NULL IS NOT NULL
