【豆知識】疑似列はどうやって使うの？ダミーテーブルとは？

頼れる人

テーブルにはないけど、通常のカラムのように使うことができる疑似列というものがあります
疑似列をうまく使うことで、よりデータ抽出が便利になります
この疑似列というのはどういうものなのか？どうやって使うのかを教えるよ

もうすでに、疑似列についてどういったものかは知ってるよ！という人は、ここは読まなくていいですよ

こけさん

疑似列ってなに？ダミーテーブルってなに？

疑似列

　実際のテーブルには存在しない、テーブルには格納されていないけれども、テーブルのカラムのように利用することができる疑似列というものがあります。
　疑似列の値を挿入・更新・削除することはできず、参照しかできません。
　関数と同じように使いますが、この疑似列には引数はありません。

　疑似列には次のようなものがあります。

ROWID

　１レコードごとに割り振られているIDで、一意となっています。
　レコードを作成したときに、自動的にROWIDが割り振られます。
　このROWIDをキーにして、データを取得することができます。ROWIDを指定した検索がレコードを最速で取得することができる方法となります。

ROWNUM

　WHERE句での条件絞り込みされた結果に対して、先頭より１から番号付けされた値です。
　WHERE句での条件指定がないときには、取得したレコードに対して、先頭より１から番号付けされます。

CURRVAL

　一意で連番を返してくれるSEQUENCEオブジェクトに対して、現在の値を取得できます。

NEXTVAL

　一意で連番を返してくれるSEQUENCEオブジェクトに対して、現在の値に＋１した値で更新し、その値を取得できます。
　＋１された値は元に戻すことはできません。

　・・・
　厳密に言えば、SEQUENCEの値を元に戻したり、任意の値にすることはできます。
　SEQUECEをDROP文で削除し、CREATE SEQUENCEで開始する値を指定して作成すれば可能です。

LEVEL

　親子関係がある階層となっているレコードの階層レベルを取得することができます。
　親レコードはレベル１、親レコードに紐づいている子レコードはレベル２、子レコードに紐づいている孫レコードはレベル３といったように、階層レベルを取得することができます。

SYSDATE・SYSTIMESTAMP・GETDATE・CURRENT_DATE

　現在の日付と時刻を返してくれます。
　SYSDATEのほうは、現時点の年月日と時分秒までを返してくれます。
　SYSTIMESTAMPのほうも、現在の日時を返してくれますが、秒の小数部（ミリ秒～ナノ秒）までを返してくれます。
　これはシステムOSによって時刻の精度が異なってきます。

　SELECT句だけでなく、WHERE句やGROUP BY句・HAVING句でも使うことができます。
　またSYSDATEやSYSTIMESTAMPで取得した値を、カラムにセットすることもできます。
　日付型として扱えるので、日付型で可能な日付や時刻の演算や比較などができます。
　型変換の関数を用いることで、文字型に変換して利用できます。

ダミーテーブル

　テーブルとして実際には存在しないけれど、SYSDATEやCURRNT疑似列の値を取得するときや、関数を実行するときなどに用います。
　取得するテーブルがあるときは、そのテーブルを参照するときに合わせて疑似列の値を取得しますが、検索するテーブルが特にないときに使います。

　ダミーテーブルには、DUAL（Oracle）やSYSDUMMY1（DB2）があります。
　その他のデータベースにはダミーテーブルがないので、FROM句なしで値が取得できます。
　ダミーテーブルに対して、レコードを挿入・更新・削除することはできず、参照しかできません。

// 現在日時を取得
SELECT SYSDATE
FROM DUAL;

// 現在日時を取得
SELECT SYSDATE
FROM SYSIBM.SYSDUMMY1;

// 現在日時を取得
SELECT GETDATE();

それぞれの疑似列はどのようにして使うの？

ROWID

　レコードに一意に割り振られているROWIDを取得できます。
　ROWIDには「AAAMAVAAWAAKe2EAAA」このような値が返ってきます。

// テーブルのカラムと一緒にROWIDを取得
SELECT ROWID
      ,pl.name
      ,pl.age
FROM players pl;

ROWID,name,age
---------------------------
AAAMAVAAWAAKe2EAAA,中島　康太,12
AAAMAVAAWAAKe2EAAB,山本　茂樹,10
AAAMAVAAWAAKe2EAAC,海籐　一之介,9
AAAMAVAAWAAKe2EAAD,熊田　文人,11

　この取得したROWIDをキーにして、レコードを参照・更新・削除することもできます。

// 取得したROWIDをキーにUPDATE
UPDATE players
SET name = ‘高橋徹’
ROWID = [ 取得したROWIDの値 ];

ROWNUM

　指定された条件で絞り込まれた結果に対して、先頭より１から番号が割り振られます。
　この値は条件によって変わりますので、同じレコードであっても異なる番号が振られることがあります。
　またこの番号を使って、指定したレコード数のみを取得することもできます。

// ROWNUMにて取得する２レコードのみ取得
SELECT ROWNUM
      ,pl.name
      ,pl.age
FROM players pl;
WHERE age >= 10
AND ROWNUM <= 2;

ROWNUM,name,age
---------------------------
1,中島　康太,12
2,山本　茂樹,10

// ROWNUMにて11レコード目から20レコード目までを取得
SELECT ROWNUM
　    ,pl.name
　    ,pl.age
FROM players pl;
WHERE age >= 10
AND ROWNUM >=11 AND ROWNUM <= 20;

結果なし

　ここであれ？っと思ったはず…

　一見、10歳以上のレコードに対して１から順番にROWNUMが割り振られるので、11番目から20番目のレコードを取得できるように見えますが、結果はゼロとなってしまいました。
　「指定された条件で絞り込まれた結果に対して、先頭より１から番号が割り振られます」
　ROWNUM >= 10の条件があるので、最初のレコードで1 >= 10が不成立となってしまうので、このレコードは除外されます。
　条件に一致するレコードではなかったためROWNUMは１のままなので、次のレコードも同様に不成立となります。

　このように全てのレコードが条件不成立となってしまうため、結果がゼロなのです。

CURRVAL

　一意で連番を返してくれるSEQUENCEオブジェクトに対して、現在の値を取得できます。

// 現在の連番を取得
// 現在10024まで番号が振られているとき
SELECT [ SEQUENCE名 ].CURRVAL
FROM DUAL;

CURRVAL
----------------
          10024

NEXTVAL

　一意で連番を返してくれるSEQUENCEオブジェクトに対して、現在の値に＋１した値で更新し、その値を取得できます。

// 現在の連番の次を取得
// 現在10024まで番号が振られているとき
SELECT [ SEQUENCE名 ].NEXTVAL
FROM DUAL;

CURRVAL
----------------
          10025

LEVEL

　このような社員コードに対して、直属の上司が設定されているテーブルがあるとしましょう。

id     name　    manager
------ --------- -------
101    JONES     102
102    TOM       null
103    MIKE      102
104    HOWARD    103
105    DAVID     102

// 社員の階層レベルを取得
SELECT id
      ,name
      ,level
FROM emp
START WITH name = ‘TOM’
CONNECT BY PRIOR id = manager;

id,name,level
-------------------------
102,TOM,1
101,JONES,2
105,DAVID,3
103,MIKE,2
104,HOWARD,3

　トップのTOMを先頭に、それぞれの上司に属する社員が階層で取得できます。

　TOMを上司としているのは、JONESとMIKEです。そのJONESを上司としているのはHOWARDで、MIKEを上司としているのはDAVIDということになります。

SYSDATE・SYSTIMESTAMP・GETDATE・CURRENT_DATE

　現在の日付と時刻を返してくれます。

// 現在日時を取得
SELECT SYSDATE
      ,SYSTIMESTAMP
FROM DUAL;

SYSDATE,SYSTIMESTAMP
---------------------
2021-02-14 16:22:08,2021-02-14 16:22:08.828965

まとめ

　実際のテーブルには存在しないけれど、データベースで使える疑似列というものがあります。
　最初は普通のカラムとの違いに戸惑うかもしれませんが、他のカラムと同じような用法で使うことができますし、疑似列は使うことが多いですので、この機会に覚えておくと損はないでしょう。
　疑似列の値を取得するときには、ダミーテーブルを使います。

▶　一覧に戻る