SQL DML:データ型

一覧

タイプ データ型 最大 桁未指定時 備考
文字型 CHAR 2,000byte 1byte 固定長 'AAA '
VARCHAR2 4,000byte エラー 可変長 'AAA'
LONG 2G 可変長 'AAA'
CLOB 4G 可変長 'AAA'
数値型 NUMBER 38桁 整数38桁 111.11
日付型 DATE 年月日 内部で時分秒を保持している '2016-01-01'
TIMESTAMP DATE + 秒以下 DATE + 秒以下(6桁) '2016-01-01 10:00:00.333000'
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
バイナリ型 RAW 2,000byte
LONG RAW 2G
BLOB 4G
BFILE 4G
ROWID型 ROWID 一位のBASE64文字列 AAASRSAAEAAAASOAAA

型の種類

CHAR

文字列型(固定長)
n:1~2000
省略可。省略した場合n:1
指定した桁未満の値を格納した場合、末尾にスペースが自動で割り当てられる。

VARCHAR2

文字列型(可変長)
n:1~2000
省略不可
指定した桁未満の値を格納した場合でも、その値で格納される。
末尾にスペースは割り当てられない

LONG/CLOB

Long型
文字列型(可変長)
最大2GB
表につき1列のみ使用可
CLOB型で代用可
Where/OrderBy/GroupBy/サブクエリー適用不可

CLOB型
LONG型の改良型:文字列型(可変長)
表につき1列のみ使用可→制限無し

NUMBER/NUMERIC

Number(p,s)/Number(p)
数値(小数点を含めた桁数)、小数点(桁指定)
p:1~38
s:-84~127

Number(3,1)
1234.5→エラー
123.45→134.5

Numeric(p,s)
数値(小数点を含めた桁数)、小数点(桁指定)

999.99
-99.99

日付/日時型

'で囲む必要あり。'2014-04-01'

DATE型
内部で時刻を持っているが、デフォルトでは表示されない。
デフォルト書式:"RR-MM-DD"

TIMESTAMP型
内部で時刻を持っており、デフォルトで表示される。
デフォルト書式:"RR-MM-DD HH24:MI:SS"

INTERVAL YEAR TO MONTH型
年と月の期間

INTERVAL DAY TO SECOND型
日、時、分、秒の期間

Date型
YYYY-MM-DD

Time型
hh:mm:ss[. nnnnnnn]型

DateTime型
YYYY-MM-DD hh:mm:ss[. nnn]

RR年書式(2000年問題対応)

【2050年~2099年】
99/01/01(1999/01/01のつもり)
select to_date('99-01-01','YY/MM/DD') from mytable;
→99-01-01

99/01/01(1999/01/01のつもり)→4桁へ
select to_char(to_date('99-01-01','YY-MM-DD'),'YYYY/MM/DD') from mytable;
2099/01/01 ※不足分はシステム日付(2015/~/~)の西暦(2000)から補われる
select to_char(to_date('99-01-01','RR-MM-DD'),'YYYY/MM/DD') from mytable;
1999/01/01 ※不足分はシステム日付(2015/~/~)の西暦-1(1999)から補われる

【2000年~2049年】
00/01/01(2000/01/01のつもり)
select to_date('00-01-01','YY/MM/DD') from mytable;
→00-01-01

00/01/01(2000/01/01のつもり)→4桁へ
select to_char(to_date('00-01-01','YY-MM-DD'),'YYYY/MM/DD') from mytable;
→2000/01/01 ※不足分はシステム日付(2015/~/~)の西暦(2000)から補われる
select to_char(to_date('00-01-01','RR-MM-DD'),'YYYY/MM/DD') from mytable;
→2000/01/01 ※不足分はシステム日付(2015/~/~)の西暦(2015)から補われる

指定日と現在の年数の下二桁によって上二桁が変化する。

指定された年が 0~49、現在が 0~49 → 現在の年の上二桁
指定された年が50~99、現在が 0~49 → 現在の年の上二桁 - 1 (1900年代)
指定された年が 0~49、現在が50~99 → 現在の年の上二桁 + 1 (2100年代)
指定された年が50~99、現在が50~99 → 現在の年の上二桁
※現在:2000年代の場合

OBJECT型

大容量のオブジェクトを格納できるデータ型
表とは別場所にデータを保存、表にはポインタ情報のみを格納する事も可能
1つの表に複数のObject型を定義可能

・CLOB Char Large Object
文字型データを格納

・BLOB Binary Large Object
バイナリ型データを格納

・BFILE Binary File
OS上のファイルへのポインタ情報を格納

TYPE・ROWTYPE型

【TYPE型】
=指定表指定列と同じ型

DECLARE
 value1 emp.ename1%TYPE;
 emp表ename1列と同じ型に定義される
 value2 emp.ename2%TYPE;
 emp表ename2列と同じ型に定義される
BEGIN
 SELECT結果を変数へ格納
 SELECT ename1, ename2INTO value1, value2 FROM emp WHERE ~ = 1;
 型が同じなので格納できる
 value1 := ~
 value2 := ~
END;

【ROWTYPE型】
=指定表内の列と同じ型(複数列)
=構造体

DECLARE
 tmpTable emp%ROWTYPE;
 emp表内の列と同じ列を持つ表を定義
BEGIN
 tmpTable.Col1 := 10;
 emp.Col1と同じ型
 tmpTable.Col2 := 'XXXXXXX';
 tmpTable.~ := ~;
 empが持つ列と同じ列を操作できる
END;

ユーザ定義型

TYPE TESTRECORD IS RECORD (
 COL1 NUMBER,
 COL2 VARCHAR(8)
 COL3 emp.COL3%TYPE
);
TEST TESTRECORD;

TEST.COL1:=1
TEST.COL2:='XXXXX'

SELECT結果の格納
SELECT * INTO TEST FROM (表名) WHERE ~
SELECT結果(複数列)をまとめて格納できる

NULL値

NULL:「どこのリテラル領域も参照していない」の意味。
0でも空文字('')でもない。
NULL値の計算、連結
SQL DML:演算子」参照
WHERE句におけるNULL値の抽出方法
SELECT ~ FROM ~ WHERE 列名 IS NULL
SELECT ~ FROM ~ WHERE 列名 IS NOT NULL
SELECT ~ FROM ~ WHERE 列名 = NULL は×
SELECT ~ FROM ~ WHERE 列名 <> NULL は×
NULLの表示順については「SQL DML:基本操作(CRUD)」参照
NULLの集計については「SQL グループ関数/NULLの集計」参照
NULL値の判定・変換については「SQL 変換関数/NULL値の判定・変換」参照

固定長と可変長の違い

固定長
Char(8):’a_______’
可変長
Varhar2(8):’a’

SQLにおける文字表現

日付型、文字、文字列型は'で囲む
'01-JAN-15'、'AAA'

文字としての'を表現する場合は''と続ける。
'Aを含む文字列を持つレコードを選択する場合
WHERE 列名 LIKE '%''A%'

代用引用符
代用引用符で囲まれた文字列は''もスペースもそのまま文字列として解釈される。
代用引用符に使用する文字列は何でも良い
Q'文字文字'
q'文字 ~ 文字'
Q'AB'B''B'''A'
→B'B''B'''
Q'('' '')'
→'' ''
Q'Aa'
→エラー

文字連結
SQL DML:演算子」参照

WHERE句における文字指定

任意の文字列

SELECT ~ FROM ~
任意の文字列(空文字''含む)
WHERE ~ LIKE '検索文字%'
⇒検索文字AAA、検索文字AA、検索文字A、検索文字
WHERE ~ LIKE '%検索文字'
⇒AAA検索文字、AA検索文字、A検索文字、検索文字
WHERE ~ LIKE '%検索文字%'
⇒AAA検索文字、AA検索文字、A検索文字、検索文字、検索文字A、検索文字AA、検索文字AAA

任意の一文字

任意の一文字(空文字''含まない)
WHERE ~ LIKE '検索文字_'
⇒検索文字A
WHERE ~ LIKE '_検索文字'
⇒A検索文字
WHERE ~ LIKE '_検索文字_'
⇒A検索文字A

特殊文字のエスケープ

特殊文字を含む文字列を指定する場合に利用
例 '_A' という文字列を含むデータを選択する場合
(この場合の'_'は任意の一文字ではなく、文字としての'_')
WHERE ~ LIKE '%\_A%' ESCAPE '\'
⇒AAA_A、AA_A、AA_A、_A、_AA、_AAA、_AAAA
※\の後の文字は特殊文字(ワイルドカード)では無く、文字であるという宣言
※ESCAPE に続く一文字は何でも良い( _ % 以外)

'100%_'
で始まる文字列を検索する場合
WHERE 列 LIKE '100\%\_%' ESCAPE '\';
\%\_%
||
\% \_ %
||
%(文字) _(文字) %(ワイルドカード)

暗黙の型変換

列(型:数値)←’数値’:OK
※数値に変換される
列(型:文字列型)←数値:OK
※文字列に変換される