SQL データディクショナリ・ビュー

データディクショナリ・ビューとは?

ユーザー、権限、領域、制約などの 根幹的な情報を保持する読み取り専用の表とビュー
データディクショナリ表は、DDLなどが実行されスキーマ構造が変更されるなどの行為によって自動的に更新される。
静的ディクショナリともよばれる ⇔ 動的パフォーマンス・ビュー
ディクショナリ表へは、直接アクセスできない。以下のビューを経由してアクセスを行う。
ALL_~
ユーザーがアクセスできる情報(他のスキーマ含む)
USER_~
ユーザーのスキーマ(ユーザー)の情報
DBA_~
データベース全体に関連する情報(SELECT ANY TABLE、SELECT ANY DICTIONARY権限が必要:DBAロール)
SYSTEM表領域に格納されている。
一般ユーザーはSYSTEM表領域がOPENされるまでデータベースにアクセスできない。
管理ユーザー(SYSDBA、SYSOPER保持ユーザー)は○

一覧

select * from dictionary;

テーブル情報

USER_COL_COMMENTS

SELECT
 TABLE_NAME テーブル名
 COLUMN_NAME 列名
 COMMENTS 列コメント
FROM
 USER_COL_COMMENTS;

USER_TAB_COLUMNS

SELECT
 TABLE_NAME テーブル名
 COLUMN_NAME 列名
 DATA_TYPE 項目の型。VARCHAR2やNUMBERなど
 DATA_LENGTH 項目長。文字項目の場合想定した値がそのまま。NUMBER型の22固定。
 DATA_PRECISION 数値の精度(NUMBER(8,3)とかの8の部分)
 DATA_SCALE 数値の小数部(NUMBER(8,3)とかの3の部分)
 NULLABLE NULL値を許可するかどうか。NULLを許容する場合、Y
 COLUMN_ID テーブル内での項目の位置
FROM
 USER_TAB_COLUMNS

ビュー情報

SELECT
 VIEW_NAME VIEW名
 TEXT VIEWの定義に使用したSQL
FROM
 USER_VIEWS

インデックス情報

SELECT * FROM USER_IND_COLUMNS WHERE TABLENAME LIKE = '%~%'

シーケンス情報

一覧
SELECT * FROM USER_SEQUENCES;
現在値
SELECT LAST_NUMBER FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = 'シーケンス名';

表領域情報

SELECT * FROM USER_TABLES ORDER BY TABLESPACE_NAME;

ユーザー情報

SELECT * FROM DBA_USERS;

所有者情報

ALL_COL_COMMENTS

USER_COL_COMMENTSの各列

所有者
OWNER

SELECT
 OWNER
 TABLE_NAME
 COLUMN_NAME
 COMMENTS
FROM
 ALL_COL_COMMENTS;

ALL_DIRECTORIES

OWNER
DIRECTORY_NAME
DIRECTORY_PATH
ORIGIN_CON_ID

インスタンス情報

インスタンス

インスタンスの状態
SELECT status FROM v$instance;

ステータス コマンド
OPEN STARTUP または ALTER DATABASE OPEN後
OPEN MIGRATE ALTER DATABASE OPEN { UPGRADE | DOWNGRADE }後
MOUNTED STARTUP MOUNT または ALTER DATABASE CLOSE後
STARTED STARTUP NOMOUNT後

セッション情報

SELECT SID, SERIAL# FROM v$session WHERE USERNAME = ~;
※結果
100 200
101 201

セッションを削除
ALTER SYSTEM KILL SESSION '100, 200'

キャッシュ

共有プールクリア
※ディクショナリのキャッシュ、SQL文、パッケージ、実行計画
ALTER SYSTEM FLUSH BUFFER_CACHE;

データベース・バッファ・キャッシュクリア
※データブロック
ALTER SYSTEM FLUSH BUFFER_CACHE;

文字コード

SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';
SELECT character_set_name FROM information_schema.character_sets;

DUAL表

全ユーザが参照できるシステム表
1列(DUMMY列)、1行(値='X')で構成される
SELECT * FROM DUAL;
テストに便利
SELECT INITCAP('AAA') FROM DUAL;
→Aaa
※DUAL表に格納されている値と関係ない列を自分で指定して出力する

SQL 関数:グループ関数

概要

グループ化した複数行のデータに対して単一の値を返す関数
※単一行関数=1行のデータに対して単一の値を返す
グループ化については「SQL DML:集計」参照

※以下のグループ関数でグループ化を行う表

ID NAME DATE
1 GROUP-1 99-01-01
2 GROUP-1 99-01-01
3 GROUP-1 99-01-02
4 GROUP-1 99-01-03
5 GROUP-1 99-01-04
6 GROUP-1 99-01-05
7 GROUP-2 99-02-01
8 GROUP-2 99-02-02
9 GROUP-2 99-02-03
10 GROUP-2 99-02-04
11 GROUP-2 99-02-05
12 GROUP-2 99-02-06
一覧

関数 取得 * 数値 文字 日付
COUNT データ件数
MIN 最小
MAX 最大
AVG 平均
SUM 合計
STDDEV 標準偏差
VARANCE 母集団標準偏差

COUNT

SELECT
  COUNT(ID) AS COUNT,
  TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);
COUNT YYYY/MM
-----------
6 1999/02
6 1999/01

SUM

SELECT
  SUM(ID),
  TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);
SUM YYYY/MM
-----------
57 1999/02
21 1999/01

MAX/MIN

SELECT
  MAX(ID),
  MIN(ID),
  TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);

MAX  MIN YYYY/MM
-----------
12  7 1999/02
66  1 1999/01

AVG

平均
SELECT
  AVG(ID) AS AVG,
  TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);

AVG YYYY/MM
-----------
~ 1999/01

STDDEV

標準偏差
SELECT
  STDDEV(ID) AS STDDEV,
  TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);

STDDEV YYYY/MM
-----------
~ 1999/01

全列指定(*)

全ての列で使用可能
SELECT COUNT(*)
SELECT COUNT('X')
FROM 表名
WHERE ~;

NULLの集計

データ型 NULL
MIN (列名) 数値、文字、日付 無視
MAX (列名) 数値、文字、日付 無視
COUNT (列名) 数値、文字、日付 無視 COUNT(*) :NULLが集計される
AVG (列名) 数値 無視
SUM (列名) 数値 無視

ID列:1,1,(NULL),2,2 の場合
COUNT(ALL ID) = 4
※(NULL)は集計されない
COUNT(ID) = 4
※ALLと同じ
COUNT(DISTINCT ID) = 2
COUNT(*) = 5
※(NULL)も集計する

NULLの判定/置換は「SQL 変換関数NULL値の判定・変換」参照

グループ関数が利用可能な句

・SELECT句
SELECT
 COUNT(~)
FROM ~

・HAVING句
SELECT ~ FROM ~ GROUP BY ~
HAVING COUNT(~) = ~

・ORDER BY句
SELECT ~ FROM ~
ORDER BY COUNT(~)

SQL 関数:変換関数

型変換

TO_CHAR( 値, 書式)
TO_NUMBER( 値 )
TO_DATE( 値, 書式 )

TO_CHAR( 値, 書式)
TO_DATE( 値, 書式 )
TO_TIMESTAMP( 値, 書式 )

( 値 )::TEXT
( 値 )::INT
( 値 )::JSONB

CAST( 値 AS INT)
CAST( 値 AS VARCHAR)
CAST( 値 AS DATE)

CONVERT(INTEGER , 値)
CONVERT(CHAR , 値)
CONVERT(VARCHAR , 値)
CONVERT(DATE, 値)

文字列系

カンマ区切り(通貨)

L:通貨記号
0:0埋めする/9:0埋めしない

SELECT TO_CHAR('1000', 'L9,999,999') FROM MYTABLE;
— ¥1,000
SELECT TO_CHAR('1000', '$9,999,999') FROM MYTABLE;
— $1,000
SELECT TO_CHAR('1000', '9,999,999') FROM MYTABLE;
— 1,000
SELECT TO_CHAR('1000', 'L0,000,000') FROM MYTABLE;
— ¥0,001,000
SELECT TO_CHAR('1000', '$0,000,000') FROM MYTABLE;
— $0,001,000
SELECT TO_CHAR('1000', '0,000,000') FROM MYTABLE;
— 0,001,000
SELECT TO_CHAR('1000', '0G000G000') FROM MYTABLE;
— 0,001,000

通貨型⇒文字列型(カンマ区切り)
10000→10,000

'通貨型の場合
DECLARE @TestValue MONEY
SET @TestValue = 10000

CONVERT(VARCHAR, @TestValue, 1 )
⇒結果:10,000

CONVERT(VARCHAR, '10000', 1)
⇒結果:10000 '文字列型の直接変換は不可

'文字列型の場合は通貨型に変換してから
CONVERT(VARCHAR, CONVERT(MONEY, '10000'), 1)
⇒結果:10,000

小数点表示

SELECT TO_CHAR(0.25, '0,000.000') FROM MYTABLE;
→ 0,000.250 ※整数部:0埋め 少数部:0埋め

SELECT TO_CHAR(0.25, '9,999.999') FROM MYTABLE;
→  .250 ※整数部:スペース埋め 少数部:0埋め

SELECT TO_CHAR(10.25, '0,000.000') FROM MYTABLE;
→ 0,010.250 ※整数部:0埋め 少数部:0埋め

SELECT TO_CHAR(10.25, '9,999.999') FROM MYTABLE;
→  10.250 ※整数部:スペース埋め 少数部:0埋め

SELECT TO_CHAR(10.25, '9G999D999') FROM MYTABLE;
→  10.250

色々な日付表示

TO_CHARは日付型データを指定した書式の文字列に変換する。

UPDATE MYTABLE SET DATE1 = '2015-06-18';
SELECT * FROM MYTABLE;

SELECT TO_CHAR(DATE1,'RR-MM-DD') FROM MYTABLE;
→'15-06-18'

SELECT TO_CHAR(DATE1,'YY-MM-DD') FROM MYTABLE;
→'15-06-18'

SELECT TO_CHAR(DATE1,'YY-MON-DD') FROM MYTABLE;
→'15-6月 -18'

SELECT TO_CHAR(DATE1,'RR-MM-DD(D)') FROM MYTABLE;
→'15-06-18(7)'
日:1 月:2 火:3 水:4 木:5 金:6 土:7

SELECT ~ ORDER BY TO_CHAR(DATE1,'D')
曜日順(日曜先頭)
SELECT ~ ORDER BY TO_CHAR(DATE1 – 1,'D')
曜日順(曜先頭)

SELECT TO_CHAR(DATE1,'RR-MM-DD(DD)') FROM MYTABLE;
→'15-06-18(土)'

SELECT TO_CHAR(DATE1,'RR-MM-DD(DAY)') FROM MYTABLE;
→'15-06-18(土曜日)'

SELECT TO_CHAR(DATE1,'YY-MON-DD', 'NLS_DATE_LANGUAGE=ENGLISH') FROM MYTABLE;
→'15-JUN-18'

SELECT TO_CHAR(DATE1,'YY-MON-DD', 'NLS_DATE_LANGUAGE=JAPANESE') FROM MYTABLE;
→'15-6月 -18'

SELECT TO_CHAR(DATE1,'RR-MM-DD HH24:MI:SS') FROM MYTABLE;
→'15-06-18 00:00:00'

SELECT TO_CHAR(DATE1,'YY-MM-DD HH24:MI:SS') FROM MYTABLE;
→'15-06-18 00:00:00'

SELECT TO_CHAR(DATE1,'RR-MM-DD HH12:MI:SS') FROM MYTABLE;
→'15-06-18 12:00:00'

SELECT TO_CHAR(DATE1,'YY-MM-DD HH12:MI:SS') FROM MYTABLE;
→'15-06-18 12:00:00'

SELECT TO_CHAR(DATE1,'RR-MM-DD HH:MI:SS') FROM MYTABLE;
→'15-06-18 12:00:00'

SELECT TO_CHAR(DATE1,'YY-MM-DD HH:MI:SS') FROM MYTABLE;
→'15-06-18 12:00:00'

SELECT TO_CHAR(DATE1,'RRRR-MM-DD HH24:MI:SS') FROM MYTABLE;
→'2015-06-18 00:00:00'

SELECT TO_CHAR(DATE1,'YYYY-MM-DD HH24:MI:SS') FROM MYTABLE;
→'2015-06-18 00:00:00'

SELECT TO_CHAR(DATE1,'AM HH24:MI:SS') FROM MYTABLE;
SELECT TO_CHAR(DATE1,'PM HH24:MI:SS') FROM MYTABLE;
→'午前 00:00:00'
※AM/PMを指定すると常に正しい午前/午後が表示される'

SELECT TO_CHAR(DATE1,'YY-MON-DD HH24:MI:SS', 'NLS_DATE_LANGUAGE=JAPANESE') FROM MYTABLE;
→'15-6月 -18 09:09:09'
SELECT TO_CHAR(DATE1,'fmYY-MON-DD HH24:MI:SS', 'NLS_DATE_LANGUAGE=JAPANESE') FROM MYTABLE;
→'15-6月-18 9:9:9' ※スペース、0削除
SELECT TO_CHAR(DATE1,'fmYY-MON-DD HH24:fmMI:SS',
'NLS_DATE_LANGUAGE=JAPANESE') FROM MYTABLE;
→'15-6月-18 9:09:09' ※複数回のfmで更に切替

SELECT TO_CHAR(SYSDATE,'YY-MM-DDTH') FROM MYTABLE;
→'15-06-08TH' ※日付の文字表記

SELECT TO_CHAR(SYSDATE,'YY-MM-DDSP') FROM MYTABLE;
→'15-06-EIGHTEEN' ※日付の文字表記

SELECT TO_CHAR(SYSDATE,'YY-MM-DDSPTH') FROM MYTABLE;
→'15-06-EIGHTEENTH' ※日付の文字表記

ミリ秒の変換
SELECT TO_CHAR(SYSTIMESTAMP,'YY-MM-DD HH24:MI:SS.FF3') FROM MYTABLE;

WHERE句で日付型を指定する時の注意点
DATE1:'15-06-18 10:30:00'の場合、
SELECT * FROM MYTABLE WHERE DATE1 = '15-06-18'
では抽出できない。

SELECT * FROM MYTABLE WHERE TRUNC(DATE1) = '15-06-18'
※時間部分の切り捨て(='15-06-18 00:00:00')
SELECT * FROM MYTABLE WHERE TO_CHAR(DATE1,'YY-MM-DD') = '15-06-18'
※'YY-MM-DD'書式で文字列化(='15-06-18')

to_char(now(), ‘YYYY/MM/DD’)
to_char(now(), ‘YYYY/MM/DD HH24:MI:SS’)

('2018-01/01')::TEXT

CAST('2018-01/01' AS VARCHAR)

SELECT TO_CHAR(9999.99, '9G99B9D999') FROM MYTABLE;
→  9,99 9.990

数値系

SELECT TO_NUMBER('3') FROM MYTABLE;
→3

('2018')::INT

CAST('2018' AS INT)

数値判定

※非数値=0/数値=1

DECLARE @TestValue VARCHAR(2)
SET @TestValue = 'A'

CASE ISNUMERIC( @TestValue )
WHEN 0 THEN '0'
WHEN 1 THEN @TestValue
END AS STAN
※数値以外の時は0に変換

日付系

SELECT TO_DATE('日付を表す文字列', 日付書式) FROM MYTABLE;
SELECT TO_DATE('2015/01/01 10:10:10', 'YYYY/MM/DD HH:MI:SS') FROM MYTABLE;
SELECT TO_DATE('15/01/01 10:10:10', 'YY/MM/DD HH:MI:SS') FROM MYTABLE;
SELECT TO_DATE('150101 101010', 'YYMMDD HHMISS') FROM MYTABLE;

【変換時:書式を省略した場合】
SELECT TO_DATE("02:00:00","HH:MI:SS") FROM MYTABLE;
SELECT TO_CHAR(TO_DATE("02:00:00","HH:MI:SS"),"YYYY/MM/DD HH24:MI:SS") FROM MYTABLE;
– – 2015/07/01 02:00:00 ※年月日を省略した場合、年月はシステム日付が適用される。日は強制01日。

SELECT TO_DATE("000101","RRMMDD") FROM MYTABLE;
SELECT TO_CHAR(TO_DATE("000101","RRMMDD"),"YYYY/MM/DD HH24:MI:SS") FROM MYTABLE;
– – 2000/01/01 00:00:00 時刻を省略した場合、00:00:00が適用される。

【TIMESTAMP型】
TO_TIMESTAMP('2016-04-01 00:00:00' , 'YYYY-MM-DD HH24:MI:SS')

to_date( '2018-01-01' , 'YYYY-MM-DD' )
to_timestamp( '2018-01-01 10:00:00' , 'YYYY-MM-DD HH24:MI:SS' )

CAST('2018-01-01' AS DATE)

DECLARE @dteFuriDay DATE;
SET @dteFuriDay = CONVERT(DATE,'20130101')
@dteFuriDay:2013-01-01

DECLARE @chrFuriDay VARCHAR(8);
SET @chrFuriDay = CONVERT(VARCHAR,@dteFuriDay,111)
@chrFuriDay:2013/01/01

SET @chrFuriDay = CONVERT(VARCHAR,@dteFuriDay,112)
@chrFuriDay:20130101

NULL

NULL値の判定・変換

SELECT NVL((NULL),0) FROM MYTABLE;
→0

NVL2(~1, ~2, ~3)
~1がNULLでない時⇒~2を、NULLの時⇒~3を返す。

SELECT NVL2('NULL',1, 0) FROM MYTABLE;
→1
SELECT NVL2((NULL),1, 0) FROM MYTABLE;
→0

戻り値:第二引数
第二引数の型 != 第三引数の型 の場合 → 第二引数の型へ暗黙変換

NULLIF(~1, ~2)
~1 = ~2の時⇒NULLを、~1 ≠ ~2の時⇒~1を返す

COALESCE(~1, ~2, ~3)
リスト内からNULLで無い値を返す(コウアレス)
リスト:NULL, NULL, 5の時⇒5
リスト:NULL, NULL, NULLの時⇒NULL
リスト内は全て同型の必要あり

DECODE関数を用いたNULL変換も可能

NULLの変換
TO_CHAR( (NULL) )
→(NULL)
TO_NUMBER( (NULL) )
→(null)
TO_DATE( (NULL) )
→(null)

NULLの集計は「SQL グループ関数/NULLの集計」参照

SELECT ISNULL( 列名, 0 ) FROM ~
Nullの場合、0を取得

SELECT ISNULL( 列名, '' ) FROM ~
Nullの場合、''を取得

SQL 関数:システム関数

存在チェック


IF OBJECT_ID( 'dbo.TestTable', 'U') IS NOT NULL
  DROP TABLE TestTable;
CREATE TABLE TestTable
(
  ~
);

更新された行数を取得

IF @@ROWCOUNT = 0
PRINT 'エラーメッセージ';

エラー時の値

BEGIN TRY
  ~
END TRY

BEGIN CATCH
  SELECT
    エラー内容を表す番号
    ERROR_NUMBER() AS ErrorNumber,
    
    エラーが発生したストアド名/トリガー名等
    ERROR_PROCEDURE() AS ErrorProcedure,
    
    エラーが発生した行
    ERROR_LINE() AS ErrorLine,
    
    エラーメッセージ
    ERROR_MESSAGE() AS ErrorMessage;
    
END CATCH;

SQL 関数:数値関数

一覧

関数 取得 第二引数 結果
ROUND 四捨五入 小数点以下の桁数 ROUND(5.55 , 1) 5.6
TRUNC 切り捨て 小数点以下の桁数 TRUNC(5.55 , 1) 5.5
MOD 余り MOD(10, 3) 1
GREATEST 最大 GREATEST(1, 3, 5) 5
LEAST 最小 LEAST(1, 3, 5) 1

四捨五入

ROUND(対象、実行桁数)
※実行桁数 = 四捨五入後の小数点以下の表示数
⇒実行桁数の更に下の桁の四捨五入を行う
ROUND(555.555, 1) ⇒555.6
ROUND(555.555, 2) ⇒555.56
ROUND(555.554, 2) ⇒555.55

ROUND(対象)
※ROUND(~,0)と同じ
ROUND(555) ⇒555
ROUND(555.55) ⇒556
ROUND(555.40) ⇒555

日時の四捨五入
※例:ROUND('15-06-22 17:30:30', '~')

四捨五入ルール
時間:~11時→当日/12時~→翌日
日:~15日→当月/16日~→翌月
月:~6月→当年/7月~→翌年

ROUND(日時) ※引数省略時
=ROUND(日時, 'DD')
ROUND(日時, 'YYYY')
→15-01-01 00:00:00
ROUND(日時, 'MM')
→15-07-01 00:00:00
ROUND(日時, 'DD')
→15-06-23 00:00:00
ROUND(日時, 'HH')
→15-06-22 17:00:00

ROUND(対象、実行桁数)
※実行桁数 = 四捨五入後の小数点以下の表示数

ROUND(145.50 , 0 ) ⇒ 146.00(145.50)
ROUND(145.45 , 0) ⇒ 145.00(145.45)
ROUND(-145.50 , 0) ⇒ -146.00(-145.50)
ROUND(-145.45 , 0) ⇒ -145.00(-145.45)

ROUND(145.45 , 1) ⇒ 145.50(145.45)
ROUND(145.45 , 2) ⇒ 145.45(145.45[])
ROUND(145.45 , -1) ⇒ 150.00(145.45)
ROUND(145.45 , -2) ⇒ 100.00(145.45)

切り捨て

※Truncate(切り取り)の略

TRUNC(対象、実行桁数)
※実行桁数 = 切り捨て後の小数点以下の表示数
⇒実行桁数の更に下の桁の切り捨てを行う
TRUNC(555.555, 1) ⇒555.5
TRUNC(555.555, 2) ⇒555.55
TRUNC(555.559, 2) ⇒555.55

TRUNC(対象)
※TRUNC(~,0)と同じ
TRUNC(555) ⇒555
TRUNC(555.55) ⇒555
TRUNC(555.99) ⇒555

日時の切り捨て
※例:TRUNC('15-06-22 17:30:30', '~')

TRUNC(日時) ※引数省略時
=TRUNC(日時, 'DD')
TRUNC(日時, 'YYYY')
→15-01-01 00:00:00
TRUNC(日時, 'MM')
→15-06-01 00:00:00
TRUNC(日時, 'DD')
→15-06-22 00:00:00
TRUNC(日時, 'HH')
→15-06-22 17:00:00
TRUNC(日時, 'MI')
→15-06-22 17:30:00
TRUNC(日時, 'SS')
→エラー

余り

MOD(10, 3)
⇒ 1

SQL 関数:日付関数

加減

年の加算
ADD_MONTHS(日付型列名 , 1 * 12)
ADD_MONTHS( TO_DATE(列名,'YYYY/MM/DD'),1 * 12)

月の加算
ADD_MONTHS(日付型列名,1)
ADD_MONTHS( TO_DATE(列名,'YYYY/MM/DD') ,1)

日の加減算
日付型列名 + 1
日付型列名 – 1
TO_DATE('2005/03/29','YYYY/MM/DD') + 1
TO_DATE('2005/03/29','YYYY/MM/DD') – 1

時間の加減算
日付型列名 + 1/24
日付型列名 – 1/24
TO_DATE('2005/03/29','YYYY/MM/DD') + 1/24
TO_DATE('2005/03/29','YYYY/MM/DD') – 1/24

分の加減算
日付型列名 + 1/24/60
日付型列名 – 1/24/60
TO_DATE('2005/03/29','YYYY/MM/DD') + 1/24/60
TO_DATE('2005/03/29','YYYY/MM/DD') – 1/24/60

秒の加減算
日付型列名 + 1/24/60/60
日付型列名 – 1/24/60/60
TO_DATE('2005/03/29','YYYY/MM/DD') + 1/24/60/60
TO_DATE('2005/03/29','YYYY/MM/DD') – 1/24/60/60

日付型変数 + 1
to_date('2018/01/01', 'YYYY/MM/DD') + 1
1日後

日付型変数 + cast('1 days' AS INTERVAL)
1日後
日付型変数 + cast('1 months' AS INTERVAL)
1カ月後
日付型変数 + cast('1 years' AS INTERVAL)
1年後

DECLARE @dteYMD DATETIME

'3年減算
SET @dteYMD= DATEADD(yy, -3, @dteNIPYMD)

取得

月末
LAST_DAY(日付型列名)
LAST_DAY(TO_DATE('2005/03/29','YYYY/MM/DD'))

次の指定曜日の日
NEXT_DAY(日付型列名, 曜日)
NEXT_DAY('2005/03/29' ,1)
NEXT_DAY('2005/03/29' ,'MONDAY')

年/月/日/時/分/秒
EXTRACT(FORMAT FROM 日付)

EXTRACT(YEAR FROM 日付)
EXTRACT(MONTH FROM 日付)
EXTRACT(DAY FROM 日付)
EXTRACT(HOUR FROM 日付)
EXTRACT(MINUTE FROM 日付)
EXTRACT(SECOND FROM 日付)

年の取得
DECLARE @dteYMD DATETIME
DECLARE @dteYEAR DATETIME

SET @dteYEAR= YEAR(@dteYMD)

月の取得
DECLARE @dteYMD DATETIME
DECLARE @dteMONTH DATETIME

SET @dteMONTH= MONTH(@dteYMD)

日の取得
DECLARE @dteYMD DATETIME
DECLARE @dteDAY DATETIME

SET @dteDAY= DAY(@dteYMD)

DATEPART関数
DATEPART( yyyy, TestDate )
⇒ 2000
DATEPART( d, TestDate )
⇒ 1
DATEPART( m, TestDate )
⇒ 4
DATEPART( w, TestDate )
⇒ 1~7(日、月…金、土を表す数字)

時間差

時間差(日)
'2000-08-25' '2000-08-24'
→1
'2000-08-25 12:00:00' '2000-08-25 10:00:00'
→0.083333(2時間差=2/24)
'2000-08-25' + '2000-08-24'
エラー

時間差(月)
MONTHS_BETWEEN(日付型列名, 日付型列名)

MONTHS_BETWEEN('2000-08-25', '2015-06-25')
→2
MONTHS_BETWEEN('2000-08-25', '2015-05-10')
→3.48387097
※1ヶ月未満の差がある場合は31日の日割り計算される。
3ヶ月15日=3+15/31=0.48387097

MONTHS_BETWEEN('2000-04-31', '2015-02-28')
→2
※引数が両方月末の場合は月数のみの差が算出される

時間差(年)
TRUNC(MONTHS_BETWEEN(日付型列名, 日付型列名) /12)
※TRUNC:切り捨て

年齢
TRUNC(MONTHS_BETWEEN(SYSDATE, 誕生日) /12)
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('2005/03/29','YYYY/MM/DD')) /12)
※SYSDATE:システム日付

切り捨て

date_trunc( 'second', now() )
秒未満を切り捨て

SQL 関数:文字関数

一覧

関数 取得 結果
UPPER 大文字変換 UPPER( 'aaa' ) 'AAA'
LOWER 小文字変換 LOWER( 'AAA' ) 'aaa'
INITCAP 先頭大文字変換 INITCAP( 'aaa' ) 'Aaa'
LPAD 文字埋め(左側) LPAD( 'AAA' , 5 , '0' ) '00AAA'
RPAD 文字埋め(右側) RPAD( 'AAA' , 5 , '0' ) 'AAA00'
SUBSTR 切り出し SUBSTR( 'ABC' , 2 , 2 ) 'BC'
TRIM 切り捨て TRIM( ' AA BB ' ) 'AA BB'
REPLACE 置換 REPLACE( 'ABC', 'B' , 'X' ) 'AXC'
CONCAT 接続 CONCAT( 'AA' , 'BB' ) 'AABB'
LENGTH 文字数 LENGTH( 'AAA' ) 3
INSTR 文字位置 INSTR( 'ABC' , 'A' ) 1
大文字/小文字

小文字変換
LOWER('ABC DEF')
⇒'abc def'

大文字変換
UPPER('abc def')
⇒'ABC DEF'

先頭文字のみ大文字変換
INITCAP('ABC DEF')
⇒'Abc Def'
INITCAP('abc def')
⇒'Abc Def'

0埋め

指定文字埋め(右寄せ)
LPAD('ABC', 5, '*')
⇒'**ABC'

指定文字埋め(左寄せ)
RPAD('ABC', 5, '*')
⇒'ABC**'

列名に4桁の時刻がある場合(例:0545)
これがINTEGER型だと、545となる。
この場合、0埋めは上手く行かない。 

SELECT RIGHT('0000'+ 列名, 2) FROM [TABLE]

正しくは、
SELECT RIGHT('0000'+ CONVERT(VARCHAR,列名), 2) FROM [TABLE]

切り出し

※VBで言うMid()関数

切り出し
SUBSTR('ABCDEF' ,1 ,3)
⇒'ABC'

SUBSTR('ABCDEF' ,-3)
⇒'DEF'

select
 col1,
 col1列の3文字目から1文字切り取り
 substring(col1 from 3 for 1)
 col1が数値型の場合
 substring('' || col1 from 3 for 1)
from
 ~

SUBSTRING
SUBSTRING( expression, start, length )

SELECT SUBSTRING( 列名, 1, 2) FROM [TABLE]

LEFT
SELECT LEFT(列名,2) FROM [TABLE]

※VBと同じ使い方

RIGHT
SELECT RIGHT(列名,2) FROM [TABLE]

※VBと同じ使い方
DBの種類によっては使えない(Oracle等)。

切り捨て

前後の半角スペース削除
TRIM(' abc def ')
⇒'abc def'
※真ん中の半角スペースは削除されない。全角スペースは前後にあっても無視。

前後の指定文字削除
TRIM('G' FROM 'GoodMorning')
⇒'oodMorning'

TRIM(' ' FROM ' abc def ')
⇒'abc def' ※TRIM(' abc def ')と同じ

BOTH/LEADING/TRAINLING ※省略可(BOTHがデフォルト)

TRIM(BOTH ' ' FROM ' abc def ')
⇒'abc def' ※TRIM(' abc def ')と同じ

TRIM(LEADING ' ' FROM ' abc def ')
⇒'abc def '

TRIM(TRAILING ' ' FROM ' abc def ')
⇒' abc def'

trim(' aa a ')
→aa a
トリムは前後のみ
途中をトリムする場合は置換を使用
置換

REPLACE('Good', 'G', 'W')
⇒'Wood'
正規表現による置換
regexp_replace( 対象, 検索, 置換 [,繰り返し])
regexp_replace( ' a a a ', '(\s| )', '', 'g')
→aaa
接続

CONCAT('ABC' , 'DEF')
⇒'ABCDEF'

↓ も可
'ABC' || 'DEF'
⇒'ABCDEF'

文字数

LENGTH('ABC')
⇒3

LENGTH('ABC')
⇒3

CHAR_LENGTH('ABC')
CHARACTER_LENGTH('ABC')
も可(同機能)

文字位置

INSTR('ABC', 'A')
⇒1
※1から

INSTR('ABC', 'Z')
⇒0
※該当無し

SQL 関数:関数一覧

単一行関数

・問い合わせ結果である各行を操作する。
・引数(定数、列、式)を渡す。
・SELECT句、WHERE句、ORDER BY句で使用可能。
・制限無くネスト可能。

変換関数

型変換

文字列系

カンマ区切り(通貨)
小数点表示
色々な日付表示
RR年書式(2000年問題対応)

数値系

数値判定

日付系

書式指定による変換

NULL値

NULL値の判定/変換

文字関数

0埋め
切り出し
切り捨て
置換
大文字/小文字
接続
文字数
文字位置

日付関数

加減
取得
時間差

数値関数

四捨五入
切り捨て
余り

グループ関数

・行のグループに対して1つの結果を返す。
・以下の(1)~(3)のグループに対して計算を行う。
 (1)表全体
 (2)WHERE句で選択した複数の行
 (3)GROUP BY句でグループ分けした複数の行
・SELECT句、ORDER BY句、HAVING句で使用可能。
 WHERE句、GROUP BY句では不可
・ネストは2回まで

COUNT
SUM
MAX/MIN
AVG
STDDEV
NULLの集計

システム関数

存在チェック
更新された行を取得
エラー時の値

SQL DDL:プロファイル

プロファイルとは?

データベース・リソースへの制限
ユーザにプロファイルを割り当てる事で、ユーザの動作を制限できる
・ リソース制限
・ パスワード制限
・ ログイン制限
等がある

リソース制限

ユーザーが使用できるシステム・リソースを制限
使用可能なCPU等の上限値を設定
ユーザーが誤った無限ループ処理を行い、CPUが無制限に浪費される等の事態を防止
設定したリソース制限を超えると現行の処理はロールバックされる

ログイン制限

・ パスワードの定期変更
・ パスワードの有効期限
・ 誤ったパスワードの連続入力制限等
アカウントのロック
設定回数以上ログインを失敗した場合に自動的にそのアカウントをロック

定義

CREATE PROFILE (プロファイル名)
LIMIT
FAILED_LOGIN_ATTEMPTS 5
指定回数ログイン失敗でアカウントをロック

PASSWORD_LIFE_TIME 60
パスワード設定時からの有効日数

PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 5
(1)同一パスワードが使用可能になるまでの日数
(2)同一パスワードが使用可能になるまでの変更回数
(1)(2)はどちらか一方のみ設定可

PASSWORD_VERIFY_FUNCTION (ファンクション名)
指定のパスワードチェック関数を実行して使用可能なパスワード文字をルール化

PASSWORD_LOCK_TIME 1/24
指定回数ログイン失敗時のアカウントロック日数
規定:1日

PASSWORD_GRACE_TIME 10
ログイン猶予日数の警告表示を行う日数
規定:7日


SESSIONS_PER_USER UNLIMITED
同時セッション数

CPU_PER_SESSION UNLIMITED
1セッション当たりのCPU制限時間(1/100秒単位)

CPU_PER_CALL 3000
1命令当たりのCPU制限時間(1/100秒単位)
CONNECT_TIME 45
1セッション当たりの接続制限時間(分単位)

LOGICAL_READS_PER_SESSION DEFAULT
1セッション当たりの読み込みデータブロック数上限

LOGICAL_READS_PER_CALL 1000
1命令当たりの読み込みデータブロック数上限

PRIVATE_SGA 15K
1セッション当たりのSGA内共有プールに割り当て可能な容量上限
COMPOSITE_LIMIT 5000000;
1セッション当たりのリソース使用容量(サービス単位)

SQL DDL:表領域

表領域とは?

=テーブルスペース
スキーマオブジェクトやディスクソートに使用するための一時的な領域を格納するためのストレージ領域
基本は表を表領域に所属させると、データファイルが特定の表領域に保存される。
(場合によっては論理的に複数の表領域に所属させたりできる)
・表領域単位でのアクセス制限
・表領域単位でのバックアップ/リカバリ
・表領域単位で保存ディスクを分ける事でのI/O速度向上
CREATE TABLESPACE ~ で作成し、容量を指定するので、
データベースのデータ蓄積に伴いHDがパンクする事は無い。

SYSTEM表領域

・ディクショナリ
・ストアドプロシージャ
・SYSTEMロールバックセグメント
データベース作成時に自動的に作成される
この領域にユーザオブジェクトを作成しない

ユーザ表領域

テーブル等のスキーマオブジェクトを作成
表、索引、順序、ビュー毎に表領域を作成する事をOracleは推奨している

ロールバック表領域

トランザクションデータを一時的に保持する領域

一時表領域

DISTINCT句、GROUP BY句、ORDER BY句を含むSQL実行時に利用される領域

定義方法

作成

CREATE TABLESPACE (表領域名)
 DATAFILE (データファイル名.dbf) SIZE 100M;
表領域を作成(オプション)
CREATE TABLESPACE (表領域名)
 DATAFILE (データファイル名.dbf) SIZE 100M;
 AUTOEXTEND ON NEXT 500K MAXSIZE 1024M;
 自動拡張500Kで最大1024M
 AUTOEXTEND ON NEXT 500K MAXSIZE UNLIMITED
 自動拡張500Kで最大サイズ無制限
 AUTOEXTEND OFF NEXT 500K MAXSIZE 1024M;
 自動拡張無しで最大1024M
 BLOCKSIZE ブロックサイズ
複数のデータファイルにまたがった表領域を作成
CREATE TABLESPACE (表領域名)
 DATAFILE (データファイル名1.dbf) SIZE 100M;
 DATAFILE (データファイル名2.dbf) SIZE 100M;
 AUTOEXTEND ON NEXT 500K MAXSIZE 1024M;

拡張

ALTER TABLESPACE (表領域名)
ADD DATAFILE '~/(データファイル名).dbf' SIZE 100M;
ALTER DATABASE DATAFILE '~/(データファイル名).dbf ';
RESIZE 150M
AUTOEXTEND ON ※自動拡張
NEXT 10M
MAXSIZE UNLIMITED;

削除

DROP TABLESPACE 表領域名;
(表領域にオブジェクトがある場合はエラー)
DROP TABLESPACE 表領域名 INCLUDING CONTENTS AND DATAFILES;
(表領域にオブジェクトがある場合も可。データファイルも同時削除)
EMにおける表領域削除時の挙動と同じ

移動

ALTER TABLESPACE 旧表領域名 RENAME TO 新表領域名;

表作成時の表領域指定

SQL DDL:テーブル定義」参照