| Oracle® Database開発ガイド 12c リリース1 (12.1) B71295-06 |
|
 前 |
 次 |
この章では、データベース・アプリケーション用として作成するデータベース列に対して正しいSQLデータ型を選択する方法について説明します。SQLデータ型に関する構文およびセマンティクスの情報については、『Oracle Database SQL言語リファレンス』を参照してください。
内容は次のとおりです。
|
注意: Oracleプリコンパイラでは、埋込みSQLプログラム内のSQLおよびPL/SQLデータ型以外のデータ型が認識されます。これらの外部データ型は、ホスト変数に関連付けられています。Oracleプリコンパイラの詳細は、17.6項を参照してください。 |
データベース・アプリケーション用として作成するデータベース列ごとに正確かつ最も固有のデータ型を使用することにより、データの整合性が増し、記憶域要件が減り、パフォーマンスが向上します。
内容は次のとおりです。
データ型が正しければ、このデータ型が制約として機能するため、データの整合性が増します。たとえば、日付列に対して日付時間データ型を使用すると、この列に格納できるのは日付のみになります。ただし、この列に対して文字または数値データ型を使用すると、結果として、日付を表さない文字または数値がいずれかのユーザーによって格納されます。この問題を阻止するコードを作成することもできますが、正しいデータ型を使用する方が効率的です。このため、文字は文字データ型、数値は数値データ型、日付および時間は日付時間データ型に格納します。
正しいデータ型を使用する以外にも、最も固有の長さまたは精度を使用します。次はその例です。
最大n文字の文字列を目的としたVARCHAR2列を作成する場合、VARCHAR2(n)を指定します。
整数を目的とした列を作成する場合、NUMBERではなくデータ型NUMBER(38)を使用します。
制約として機能することによってデータの整合性を増すのみでなく、長さおよび精度は記憶域要件にも影響します。
すべての列でそのデータ型に対して最大の長さまたは精度を指定すると、数MBのRAMが不必要に割り当てられます。たとえば、問合せによってVARCHAR2(4000)が10列が選択され、一括フェッチ操作によって100行が戻されるとします。割当てが必要なRAMは、10 x 4,000 x 100で、約4MBになります。対照的に、列の長さが80である場合、割当てが必要なRAMは、10 x 80 x 100で、約78KBになります。この違いは単一の問合せにとって重要であり、アプリケーションは多くの問合せを同時に処理します。このため、アプリケーションは接続ごとに4MBまたは78KBのRAMを割り当てる必要があります。
したがって、プロパティの増加が後で必要になる可能性があるという理由のみで、列のデータ型に対して最大の長さまたは精度を指定しないようにしてください。列を作成した後に変更する必要がある場合、ALTER TABLE文を使用します(『Oracle Database SQL言語リファレンス』を参照)。たとえば、列を長くする場合は、次を使用します。
ALTER TABLE table_name MODIFY column_name VARCHAR2(larger_number)
|
注意: MAX_STRING_SIZE初期化パラメータがEXTENDEDである場合、VARCHAR2、NVARCHAR2およびRAWデータ型の最大の長さは32,767バイトです。拡張データ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
データ型が正しいと、パフォーマンスが向上します。データ型が正しくない場合、実行計画が正しくなくなる可能性があるためです。(実行計画の詳細は、2.2.3項を参照してください。)
例7-1では、同じ概念の操作(データ型が異なる3つの列に対して、日付が2000年12月31日と2001年1月1日の間にある行を選択)を実行し、問合せごとに実行計画を表示しています。これら3つの実行計画で、行(カーディナリティ)、コストおよび操作を比較します。
例7-1 3つのデータ型のパフォーマンス比較
3つの列に同じ日付を格納する表を作成します(データ型VARCHAR2のstr_date、データ型DATEのdate_date、およびデータ型NUMBERのnumber_date)。
CREATE TABLE t (str_date, date_date, number_date, data) AS SELECT TO_CHAR(dt+rownum,'yyyymmdd') str_date, -- VARCHAR2 dt+rownum date_date, -- DATE TO_NUMBER(TO_CHAR(dt+rownum,'yyyymmdd')) number_date, -- NUMBER RPAD('*',45,'*') data FROM (SELECT TO_DATE('01-jan-1995', 'dd-mm-yyyy') dt FROM all_objects) ORDER BY DBMS_RANDOM.VALUE /
列ごとに索引を作成します。
CREATE INDEX t_str_date_idx ON t(str_date); CREATE INDEX t_date_date_idx ON t(date_date); CREATE INDEX t_number_date_idx ON t(number_date);
表の統計を収集します。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
'HR',
'T',
method_opt => 'for all indexed columns size 254',
cascade => TRUE
);
END;
/
後続のSQL文(SQL*Plusコマンド)の実行計画を表示します。
SET AUTOTRACE ON EXPLAIN
str_dateの日付が2000年12月31日と2001年1月1日の間にある行を選択します。
SELECT * FROM t WHERE str_date BETWEEN '20001231' AND '20010101' ORDER BY str_date;
結果および実行計画は、次のとおりです。
STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00 20001231 *********************************************
20010101 01-JAN-01 20010101 *********************************************
2 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 948745535
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 236 | 11092 | 216 (8)| 00:00:01 |
| 1 | SORT ORDER BY | | 236 | 11092 | 216 (8)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T | 236 | 11092 | 215 (8)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STR_DATE"<='20010101' AND "STR_DATE">='20001231')
number_dateの日付が2000年12月31日と2001年1月1日の間にある行を選択します。
SELECT * FROM t WHERE number_date BETWEEN 20001231 AND 20010101; ORDER BY str_date;
結果および実行計画は、次のとおりです。
STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00 20001231 *********************************************
20010101 01-JAN-01 20010101 *********************************************
2 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 948745535
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 234 | 10998 | 219 (10)| 00:00:01 |
| 1 | SORT ORDER BY | | 234 | 10998 | 219 (10)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T | 234 | 10998 | 218 (9)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NUMBER_DATE"<=20010101 AND "NUMBER_DATE">=20001231)
date_dateの日付が2000年12月31日と2001年1月1日の間にある行を選択します。
SELECT * FROM t WHERE date_date BETWEEN TO_DATE('20001231','yyyymmdd') AND TO_DATE('20010101','yyyymmdd'); ORDER BY str_date;
結果と実行計画は次のとおりです(ページにあわせてフォーマットを変更しています)。
STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00 20001231 *********************************************
20010101 01-JAN-01 20010101 *********************************************
2 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2411593187
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
| 1 | SORT ORDER BY | | 1 | 47 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 47 |
|* 3 | INDEX RANGE SCAN | T_DATE_DATE_IDX | 1 | |
| 0 | SELECT STATEMENT | | 1 | 47 |
------------------------
Cost (%CPU)| Time |
4 (25)| 00:00:01 |
4 (25)| 00:00:01 |
3 (0)| 00:00:01 |
2 (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DATE_DATE">=TO_DATE(' 2000-12-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND
"DATE_DATE"<=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
最後の問合せのパフォーマンスが向上しました。これは、DATEデータ型の場合、2000年12月31日と2001年1月1日の間に1日のみが存在したことを確認できたためです。このため、索引範囲スキャンが実行されましたが、これは全表スキャンより高速です。(全表スキャンの詳細は、Oracle Database SQLチューニング・ガイドを参照してください。索引範囲スキャンの詳細は、Oracle Database SQLチューニング・ガイドを参照してください。)
表7-1に、文字データを格納するSQLデータ型の概要を示します。
表7-1 SQL文字データ型
| データ型 | 格納される値 |
|---|---|
|
固定長文字リテラル |
|
|
可変長文字リテラル |
|
|
可変長Unicode文字リテラル |
|
|
最大(4 GB - 1) * ( |
|
|
最大(4 GB - 1) *( |
|
|
最大2 GB - 1の可変長文字データ。下位互換性のためだけに提供されています。 |
CHARとVARCHAR2を選択する場合、次を考慮してください。
使用領域
Oracle Databaseでは、CHAR列に格納されている値には空白を埋めますが、VARCHAR2列に格納された値にはこれを行いません。そのため、VARCHAR2列の方がCHAR列よりもより効率的に領域を使用します。
パフォーマンス
空白を埋める違いのため、VARCHAR2列のある大規模な表の全表スキャンでは、CHAR列に同じデータが格納されている表の全表スキャンより、読み込むデータ・ブロックが少なくなる可能性があります。アプリケーションが文字データを含む大規模な表に対して全表スキャンを行う場合、CHAR列よりVARCHAR2列にデータを格納することによってパフォーマンスを改善できる可能性が高くなります。
比較セマンティクス
比較セマンティクスでANSI互換性が必要な場合は、CHARデータ型を使用します。文字列の比較において後続空白が重要である場合は、VARCHAR2データ型を使用します。
|
参照: これらのデータ型の比較セマンティクスの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
クライアント/サーバー・アプリケーションで、クライアント側のキャラクタ・セットがサーバー側と異なる場合、Oracle DatabaseはCHAR、VARCHAR2およびLONGデータをデータベース・キャラクタ・セット(NLS_LANGUAGEパラメータで決定)からユーザー・セッション用に定義したキャラクタ・セットに変換します。
|
参照:
|
数値データを格納するSQLデータ型は、NUMBER、BINARY_FLOATおよびBINARY_DOUBLEです。
NUMBERデータ型は、実数を固定小数点形式または浮動小数点形式で格納します。NUMBERは、最大で10進数38桁の精度を提供します。NUMBER列には、1 x 10-130から9.99 x10125の正と負の数値および0(ゼロ)を格納できます。すべてのOracle DatabaseプラットフォームはNUMBER値をサポートします。
BINARY_FLOATデータ型およびBINARY_DOUBLEデータ型は、それぞれ単精度(32ビット)IEEE 754形式、および倍精度(64ビット)IEEE 754形式で、浮動小数点データを格納します。高精度の値は、BINARY_FLOATおよびBINARY_DOUBLEとして格納するほうが、NUMBERとして格納するよりも必要とする領域は少なくなります。浮動小数点データの算術演算は通常、BINARY_FLOATおよびBINARY_DOUBLE値のほうが、NUMBER値よりも高速です。
Oracle Databaseでサポートされているクライアント・インタフェースでは、BINARY_FLOATデータ型およびBINARY_DOUBLE値の算術演算は、ハードウェアのベンダーが提供する固有の命令セットによって実行されます。ネイティブ浮動小数点データ型という用語は、データ型BINARY_FLOATおよびBINARY_DOUBLE、およびサポートされているクライアント・インタフェースで実装されているこれらの型すべてを指します。
ネイティブ浮動小数点データ型は、電気電子技術者協会(IEEE)の2進浮動小数点演算についての規格であるIEEE規格754-1985(IEEE754)に実質的に準拠しています。詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
内容は次のとおりです。
|
参照:
|
(-1)sign.significand.baseexponent
たとえば、4.31という値は次のように表されます。
(-1)0.431.10-2
この表現の構成要素は、次のとおりです。
| 構成要素の名前 | 構成要素の値 |
|---|---|
| 符号 | 0 |
| 仮数 | 431 |
| 基数 | 10 |
| 指数 | -2 |
浮動小数点数形式は、浮動小数点数の構成要素の表記方法を指定し、それにより、形式が表す値の範囲および精度が決定されます。範囲とは最小値から最大値までの間隔であり、精度とは仮数の桁数です。範囲と精度のどちらも有限です。指定された形式よりも高い精度の浮動小数点数は丸められます。
数値を丸める方法は、10進または2進のいずれかが可能な、形式の基数によって決まります。10進の形式に格納された数値は、最も近い10進の桁に丸められます(例: 1000、10または0.01)。2進の形式に格納された数値は、最も近い2進の桁に丸められます(例: 1024、512または1/64)。
NUMBERの値は、10進の形式で格納されます。10進の桁への丸めを必要とする計算の場合は、NUMBERデータ型を使用してください。
ネイティブ浮動小数点の値は、2進の形式で格納されます。
表7-2に、IEEE 754の単精度と倍精度形式およびOracle Database NUMBERの範囲と精度を示します。範囲の制限は、正数として表しますが、この制限は負数の絶対値にも適用されます(ここで使用される「数値e指数」という表記法は、数値*10指数という意味です)。
表7-2 浮動小数点データ型の範囲および精度
| 範囲および精度 | 単精度32ビット脚注1 | 倍精度64ビット1 | Oracle Database NUMBERデータ型 |
|---|---|---|---|
|
正の最大正規数 |
3.40282347e+38 |
1.7976931348623157e+308 |
< 1.0e126 |
|
正の最小正規数 |
1.17549435e-38 |
2.2250738585072014e-308 |
1.0e-130 |
|
正の最大非正規数 |
1.17549421e-38 |
2.2250738585072009e-308 |
適用なし |
|
正の最小非正規数 |
1.40129846e-45 |
4.9406564584124654e-324 |
適用なし |
|
精度(10進数) |
6 - 9 |
15 - 17 |
38 - 40 |
脚注1これらの数値は『IEEE Numerical Computation Guide』から引用されています。
2進形式を使用する浮動小数点数の値は、次に示す算式によって決まります。
(-1)sign 2E (bit0 bit1 bit2 ... bitp-1)
表7-3に、算式の構成要素を示します。
表7-3 浮動小数点2進形式の構成要素
| 構成要素 | 構成要素の値 |
|---|---|
|
|
0または1 |
|
|
単精度(32ビット)データ型の場合、-126以上127以下の整数です。 倍精度(64ビット)データ型の場合、-1022以上1023以下の整数です。 |
|
|
0または1(ビットの連続は基数2の数値を表します)。 |
|
|
単精度データ型の場合、24。 倍精度データ型の場合、53。 |
仮数の先頭のビットb0は、非正規の数値(後述)の場合を除いて、(1)に設定する必要があります。したがって、先頭のビットは格納されないため、2進形式では精度としてnビットを指定しますが、格納されるのはn-1ビットになります。IEEE 754標準は、単精度および倍精度データ型のインメモリー形式を定義します。表7-4に概要を示します。
表7-4 2進形式の記憶域パラメータの概要
| データ型 | 符号ビット | 指数ビット | 仮数ビット | ビット数合計 |
|---|---|---|---|---|
|
単精度 |
1 |
8 |
24(23が格納されます) |
32 |
|
倍精度 |
1 |
11 |
53(52が格納されます) |
64 |
|
注意: IEEE 754標準で定義されている拡張単精度および拡張倍精度形式は、Oracle Databaseではサポートされていません。 |
仮数の先頭ビットが設定されている場合、正規化されていると呼ばれます。IEEE 754標準では非正規化数(subnormal numbers、denormal numbers)という、小さすぎて正規化された仮数で表せない数値を定義します。非正規化数の仮数を正規化すると、指数が大きくなりすぎます。非正規数では、条件がx-y==0.0(浮動小数点減算を使用)、結果がx==y、というプロパティが保持されます。
表7-5に、IEEE 754標準がサポートする特殊な数値を示します。
表7-5に示す各値は、NaNを除き、特殊なビット・パターンで表現されます。NaNは未定義の演算の結果であり、多数のビット・パターンで表されます。ビット・パターンには、符号ビット・セットを含むものも含まないものもありますが、符号ビットに意味はありません。
IEEE 754標準では、quiet NaN(ほとんどの演算への伝播時に追加の例外が発生しない)とsignalling NaN(発生する)が区別されます。IEEE 754標準では、例外が有効および無効の場合の動作が指定されています。
Oracle Databaseでは、例外の有効化は許可されません。Oracle Databaseの動作は、IEEE 754標準で指定されている、例外が無効化されている場合の動作になります。特に、Oracle Databaseではquietとsignalling NaNは区別されません。Oracle Call Interface(OCI)を使用して、Oracle DatabaseからNaN値を取得できますが、取得されるNaN値がsignallingかquietかは、クライアント・プラットフォームによって決まり、Oracle Databaseの制御外です。
IEEE 754標準は次の特殊な値のクラスを定義します。
ゼロ
非正規
正規
無限大
NaN
NaNを除き、このリストの各クラスの値は、小さい方から順に示されています(符号は考慮していません)。IEEE 754では、NaNは、他の特殊な値のクラスともNaN自体とも順序付けることはできません。
Oracle Databaseの場合
NaNはすべてquietです。
すべての非NaN value < NaN
任意のNaN == 他のNaN
NaNはすべて同じビット・パターンに変換されます。
-0は+0に変換されます。
|
参照: 浮動小数点条件の詳細は、『Oracle Database SQL言語リファレンス』を参照してください(浮動小数点条件では、式が無限か、または演算の未定義の結果(非数値(NaN))かを判断します)。 |
数式を比較する場合、Oracle Databaseは数値の優先順位を使用して、その条件がNUMBER、BINARY_FLOATまたはBINARY_DOUBLEのうちどの値を比較するかを判断します。数値の優先度の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。数値式の比較の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
比較では、ゼロの符号は無視されます(-0は、+0と等しいとみなされます)。
IEEE 754では、浮動小数点算術に厳密な再現性は求められません。このため、演算の結果を、演算のオペランドが使用するよりも大きい範囲を使用する格納先に格納できます。
倍精度乗算の結果は、拡張倍精度格納先での計算に使用できます。しかし、計算結果は、格納先が単精度または倍精度であるかのように丸められます。結果の範囲(指数のビット数)としては、より高精度(拡張倍精度)の格納先でサポートされている範囲が使用できます。ただし、二重丸め誤差(結果の最下位ビットが不正)が発生する可能性があります。
この問題は、IA-32およびIA-64命令セット・アーキテクチャを実装するハードウェアで、倍精度乗算および除算を実行した場合のみ発生します。したがって、この例を除き、これらのデータ型に対する算術をプラットフォームを渡って再現できます。計算の結果がNaNの場合、すべてのプラットフォームでIS NANがTRUEの値が作成されます。ただし、すべてのプラットフォームで同じビット・パターンを使用する必要はありません。
|
参照: 算術演算の詳細は、『Oracle Database SQL言語リファレンス』を参照してください |
Oracle Databaseでは、浮動小数点と他のデータ型(10進数精度を使用する文字列形式など)の間で変換を行う関数が定義されています(変換中に精度が失われることがあります)。次に例を示します。
TO_BINARY_DOUBLE(『Oracle Database SQL言語リファレンス』を参照)
TO_BINARY_FLOAT(『Oracle Database SQL言語リファレンス』を参照)
TO_CHAR(『Oracle Database SQL言語リファレンス』を参照)
TO_NUMBER(『Oracle Database SQL言語リファレンス』を参照)
Oracle Databaseでは、変換時に例外が発生する可能性があります。IEEE 754標準では、次の例外が定義されています。
無効
不正確
ゼロ除算
アンダーフロー
オーバーフロー
ただし、Oracle Databaseでは、ネイティブ浮動小数点データ型に対して、これらの例外は発生しません。例外が発生する操作では、通常、表7-6に示す値が生成されます。
Oracle Databaseは、ネイティブ浮動小数点データ型を次のクライアント・インタフェースでサポートします。
SQLおよびPL/SQL
BINARY_FLOATおよびBINARY_DOUBLEのサポートには、SQL文CREATE TYPEを使用して作成する抽象データ型(ADT)の属性としての使用が含まれます(『Oracle Database PL/SQL言語リファレンス』を参照)。
OCIを使用したBINARY_FLOATおよびBINARY_DOUBLEの使用方法の詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。
Oracle C++ Call Interface(OCCI)
OCCIを使用したBINARY_FLOATの使用方法の詳細は、『Oracle C++ Call Interfaceプログラマーズ・ガイド』を参照してください。
OCCIを使用したBINARY_DOUBLEの使用方法の詳細は、『Oracle C++ Call Interfaceプログラマーズ・ガイド』を参照してください。
BINARY_FLOATおよびBINARY_DOUBLEを使用するには、アプリケーションのコンパイル時に、Pro*C/C++プリコンパイラ・コマンドライン・オプションNATIVE_TYPESをYESに設定します。NATIVE_TYPESオプションの詳細は、『Pro*C/C++プログラマーズ・ガイド』を参照してください。
Oracle JDBCを使用したBINARY_FLOATおよびBINARY_DOUBLEの使用方法の詳細は、『Oracle Database JDBC開発者ガイド』を参照してください。
Oracle Databaseは、世紀、年、月、日、時、分、秒、および必要に応じて小数秒とタイムゾーンを表すバイナリ形式でDATEおよびTIMESTAMP (日時)データを格納します。
表7-7に、SQL日時データ型の概要を示します。日時データ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
表7-7 SQL日時データ型
| 日付タイプ | 使用方法 |
|---|---|
|
|
日時の値を表に格納します。たとえば、ジョブの日付に使用します。 |
|
|
小数秒を含む正確な日付の値を格納します。たとえば、発生順序を決定するために比較する必要があるイベントの時間に使用します。 |
|
|
複数の地域にまたがって収集または調整する必要がある日時の値を格納します。 |
|
|
タイムゾーンが重要でない場合に日時の値を格納します。たとえば、テレビ会議のスケジュールを立てるアプリケーションでは、参加者は自分のタイムゾーンで開始時間および終了時間を確認します。 クライアント・システムのタイムゾーンを使用して日時を表示する必要がある2層アプリケーションに適しています。通常、3層アプリケーションでこのデータ型を使用するのは不適切です。これは、Webブラウザに表示されるデータがブラウザのタイムゾーンではなくWebサーバーのタイムゾーンによって書式化されるためです。Webサーバーはデータベース・クライアントであるため、ローカル時間が使用されます。 |
|
|
年および月のみが重要な場合に、2つの日時の値の違いを格納します。たとえば、アラームを18か月後の日付に設定したり、特定の日付から6か月経過したことを確認できます。 |
|
|
2つの日時の値の正確な違いを格納します。たとえば、アラームを36時間後に設定したり、レースの開始から終了までの時間を記録できます。長い時間を高精度で表すには、日の部分に大きな値を使用します。 |
内容は次のとおりです。
|
参照: Oracle Database内部日時タイプの詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。 |
現在の日付および時刻を表示する最も簡単な方法は次のとおりです。
SELECT TO_CHAR(SYSDATE, format_model) FROM DUAL
デフォルトの書式モデルは、初期化パラメータNLS_DATE_FORMATに依存します。
Oracle Databaseのデフォルトの標準日付書式はDD-MON-RRです。RR日時書式要素を使用すると、年の末尾2桁のみを指定して20世紀の日付を21世紀に格納できます。たとえば、DD-MON-YY日付書式の13-NOV-54は、1950年から2049年に発行された問合せでは1954年を指しますが、2050から2149年に発行される問合せでは2054年を指します。
|
注意: プログラムの正確さを維持し、SQLインジェクションおよび動的SQLに関する問題を回避するために、すべての日時値に対して書式モデルを指定することをお薦めします。 |
書式モデルを使用して現在の日付および時刻を表示する最も簡単な方法は次のとおりです。
SELECT TO_CHAR(SYSDATE, format_model) FROM DUAL
例7-2では、TO_CHARを書式モデルを指定して使用して、SYSDATEをBCまたはAD修飾子付きの書式で表示します。(デフォルトではSYSDATEは修飾子なしで表示されます)。
例7-2 現在の日付および時刻の表示
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY BC') NOW FROM DUAL;
結果:
NOW ----------------------- 18-MAR-2009 AD 1 row selected.
|
参照:
|
日付を表示および挿入する場合、日時書式モデルとともにTO_CHARおよびTO_DATEファンクションをそれぞれ使用することをお薦めします。
例7-3では、DATE列を含む表を作成し、書式モデルを指定して日付を挿入します。その後、書式モデルを指定する場合と指定しない場合で日付を表示します。
例7-3 日付の挿入および表示
表を作成します。
DROP TABLE dates; CREATE TABLE dates (d DATE);
書式モデルを指定して、指定した日付を表に挿入します。
INSERT INTO dates VALUES (TO_DATE('OCT 27, 1998', 'MON DD, YYYY'));
書式モデルを指定しないで日付を表示します。
SELECT d FROM dates;
結果:
D
---------
27-OCT-98
1 row selected.
書式モデルを指定して日付を表示します。
SELECT TO_CHAR(d, 'YYYY-MON-DD') D FROM dates;
結果:
D
--------------------
1998-OCT-27
1 row selected.
|
参照:
|
時刻を表示および挿入する場合、日時書式モデルとともにTO_CHARおよびTO_DATEファンクションをそれぞれ使用することをお薦めします。
DATE列では
デフォルトの時刻は12:00:00 A.M.(真夜中)です。
デフォルトの時刻は、時刻部分に何も指定されなかった、または値が切捨てられたという理由のどちらの場合でも、時刻部分のない列の値に適用されます。
デフォルトの日付は、日付部分に何も指定されなかった場合、列の値に適用されます。
例7-4では、DATE列を含む表を作成し、日付ごとに異なる書式モデルを指定して3つの日付を挿入します。最初の書式モデルには日付および時刻部分の両方があり、2番目には時刻部分がなく、3番目には日付部分がありません。次に、日付および時刻部分の両方が含まれる書式モデルを指定して3つの日付を表示します。
例7-4 日付と時刻の挿入および表示
表を作成します。
DROP TABLE birthdays; CREATE TABLE birthdays (name VARCHAR2(20), day DATE);
日付ごとに異なる書式モデルを指定して3つの日付を挿入します。
INSERT INTO birthdays (name, day)
VALUES ('Annie',
TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-RR HH:MI A.M.')
);
INSERT INTO birthdays (name, day)
VALUES ('Bobby',
TO_DATE('5-APR-02','DD-MON-RR')
);
INSERT INTO birthdays (name, day)
VALUES ('Cindy',
TO_DATE('8:25 P.M.','HH:MI A.M.')
);
格納された日時の値の日付および時刻部分の両方を表示します。
SELECT name,
TO_CHAR(day, 'Mon DD, RRRR') DAY,
TO_CHAR(day, 'HH:MI A.M.') TIME
FROM birthdays;
結果:
NAME DAY TIME -------------------- --------------------- ---------- Annie Nov 13, 1992 10:56 A.M. Bobby Apr 05, 2002 12:00 A.M. Cindy Nov 01, 2010 08:25 P.M. 3 rows selected.
日時値に対する算術演算の結果は、『Oracle Database SQL言語リファレンス』に記載のルールによって決定されます。
SQLには、日時の式に使用できる多くの日時ファンクションがあります。たとえば、ファンクションADD_MONTHSは、指定した日付から指定した月数である日付を戻します。日時ファンクションのすべてのリストは、『Oracle Database SQL言語リファレンス』を参照してください。
表7-8に、日時データ型間で変換を行うSQLファンクションの概要を示します。これらのファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
表7-8 日時データ型のSQL変換ファンクション
| 関数 | 変換元 | 変換先 |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
内容は次のとおりです。
空間データは、位置情報アプリケーション、地理情報システム(GIS)・アプリケーションおよびジオイメージング・アプリケーションによって使用されます。Oracle Databaseでの空間データの表現の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
Oracle Multimediaを使用すると、Oracle Databaseでイメージ、オーディオ、ビデオまたは他の異機種メディア・データを格納、管理および取得できます。Oracle Databaseでのマルチメディア・データの表現の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
大量のデータを表現するために、Oracle Databaseでは次のものが提供されています。
LONGおよびLONG RAWデータ型(下位互換性のため)
ラージ・オブジェクト(LOB)は、アプリケーションが効率的にアクセスおよび操作できるような方法で大量のデータを格納することを目的としたデータ型です。
表7-9に、LOBの概要を示します。これらのファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
表7-9 ラージ・オブジェクト(LOB)
| データ型 | 説明 |
|---|---|
|
様々な種類のバイナリ形式のデータを格納します。 通常、イメージ、オーディソフトウェアおよび音声などのマルチメディア・データに使用されます。 |
|
|
文字列データをデータベース・キャラクタ・セット形式で格納します。 データベース・キャラクタ・セットのみを使用する大量の文字列またはドキュメントに使用されます。 |
|
|
各国語キャラクタ・セット形式で文字列データを格納します。 各国語キャラクタ・セットの大量の文字列またはドキュメントに使用されます。 |
|
|
バイナリ・ファイルをホストのオペレーティング・システム・ファイル・システム内のデータベース外に格納します。アプリケーションは イメージ・データなど、アプリケーションが操作しない静的データに使用されます。 あらゆる種類のデータ(つまりオペレーティング・システム・ファイル)は |
BLOB、CLOBまたはNCLOB型のインスタンスは一時的(アプリケーションのスコープ内で宣言される)か、または永続的(データベースで作成および格納される)のいずれも可能です。
|
参照: アプリケーション開発でのLOBの使用の詳細は、『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』を参照してください。 |
LONG列には最大2 GB - 1バイトの可変長文字列が格納されます。LONGデータ型の制限を含む詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
LONG RAWデータ型(およびRAWデータ型)は、Oracle Databaseによって、異なるシステム間でデータを移動するときに明示的に変換されないデータを格納します。これらのデータ型は、2進データおよびバイト列のために用意されています。RAWデータ型およびLONG RAWデータ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
全文検索を行うには、低レベルのコードを作成するのではなく、Oracle Textを使用します。Oracle Textは、問合せアプリケーションおよびドキュメント分類アプリケーションのテキスト用の索引付け、語とテーマの検索および表示機能を提供します。また、Oracle Textを使用してXMLデータを検索することもできます。
|
参照: Oracle Textの詳細は、『Oracle Textアプリケーション開発者ガイド』を参照してください。 |
XML形式のファイルとして格納されている情報がある場合、またはADTをXML形式で格納する場合、『Oracle Database SQL言語リファレンス』で説明されている、Oracle提供のXMLType型を使用できます。
XMLType値を使用して、次を使用できます。
XMLTypeメンバー・ファンクション(『Oracle XML DB開発者ガイド』を参照)。
SQL XMLファンクション(『Oracle Database SQL言語リファレンス』を参照)
PL/SQL DBMS_XMLパッケージ(『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照)
|
参照:
|
いくつかの言語では、実行時にデータ型を変更したり、プログラムに変数の型を確認させることができます。たとえば、C言語にはunionキーワードおよびvoid*ポインタがあり、Javaにはtypeof演算子およびNumberなどのラッパー型があります。
Oracle Databaseでは、すべての型のデータを保持できる変数および列を作成することができ、その値をテストして基礎となる表現を判断できます。たとえば、表の単一の列で、数値、文字列およびオブジェクトをそれぞれ別の行に表現できます。
Oracleが提供するSYS.ANYDATA ADTを使用すると、すべてのスカラー型またはADTの値を表現できます。SYS.ANYDATAには、任意の型のスカラー値を受け取り、スカラーまたはオブジェクトに戻すメソッドがあります。同様に、Oracleが提供するSYS.ANYDATASET ADTを使用すると、すべてのコレクション型の値を表現できます。これらのADTの詳細は、『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照してください。
型情報を確認および操作するには、例7-5のように、DBMS_TYPESパッケージ(『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照)を使用します。
OCIを使用するときは、OCIAnyDataおよびOCIAnyDataSetインタフェースを使用します。『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。
例7-5 SYS.ANYDATA列の情報へのアクセス
CREATE OR REPLACE TYPE employee_type AS OBJECT (empno NUMBER, ename VARCHAR2(10)); / DROP TABLE mytab; CREATE TABLE mytab (id NUMBER, data SYS.ANYDATA); INSERT INTO mytab (id, data) VALUES (1, SYS.ANYDATA.ConvertNumber(5)); INSERT INTO mytab (id, data) VALUES (2, SYS.ANYDATA.ConvertObject(Employee_type(5555, 'john'))); CREATE OR REPLACE PROCEDURE p IS CURSOR cur IS SELECT id, data FROM mytab; v_id mytab.id%TYPE; v_data mytab.data%TYPE; v_type SYS.ANYTYPE; v_typecode PLS_INTEGER; v_typename VARCHAR2(60); v_dummy PLS_INTEGER; v_n NUMBER; v_employee employee_type; non_null_anytype_for_NUMBER exception; unknown_typename exception; BEGIN FOR x IN cur LOOP FETCH cur INTO v_id, v_data; EXIT WHEN cur%NOTFOUND; /* typecode signifies type represented by v_data. GetType also produces a value of type SYS.ANYTYPE with methods you can call to find precision and scale of a number, length of a string, and so on. */ v_typecode := v_data.GetType (v_type /* OUT */); /* Compare typecode to DBMS_TYPES constants to determine type of data and decide how to display it. */ CASE v_typecode WHEN DBMS_TYPES.TYPECODE_NUMBER THEN IF v_type IS NOT NULL THEN -- This condition should never happen. RAISE non_null_anytype_for_NUMBER; END IF; -- For each type, there is a Get method. v_dummy := v_data.GetNUMBER (v_n /* OUT */); DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_id) || ': NUMBER = ' || TO_CHAR(v_n) ); WHEN DBMS_TYPES.TYPECODE_OBJECT THEN v_typename := v_data.GetTypeName(); IF v_typename NOT IN ('HR.EMPLOYEE_TYPE') THEN RAISE unknown_typename; END IF; v_dummy := v_data.GetObject (v_employee /* OUT */); DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_id) || ': user-defined type = ' || v_typename || ' ( ' || v_employee.empno || ', ' || v_employee.ename || ' )' ); END CASE; END LOOP; EXCEPTION WHEN non_null_anytype_for_NUMBER THEN RAISE_Application_Error (-20000, 'Paradox: the return AnyType instance FROM GetType ' || 'should be NULL for all but user-defined types'); WHEN unknown_typename THEN RAISE_Application_Error( -20000, 'Unknown user-defined type ' || v_typename || ' - program written to handle only HR.EMPLOYEE_TYPE'); END; / SELECT t.data.gettypename() AS "Type Name" FROM mytab t;
結果:
Type Name -------------------------------------------------------------------------------- SYS.NUMBER HR.EMPLOYEE_TYPE 2 rows selected.
表およびクラスタを作成するSQL文は、ANSIデータ型と、IBM社の製品SQL/DSおよびDB2のデータ型を使用できます(次の段落の注意を除く)。Oracle DatabaseはANSIまたはIBMデータ型を対応するOracleデータ型に変換し、Oracleデータ型を列データ型の名前として記録し、列データをOracleデータ型で格納します。変換の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
|
注意: SQL文ではSQL/DSおよびDB2のTIME、GRAPHIC、VARGRAPHICおよびLONG VARGRAPHICデータ型を使用できません。対応するOracleデータ型がないからです。 |
データベース表の行にアクセスするには、そのアドレス、または行を一意に識別するROWIDを使用するのが最も高速な方法です。同じデータ・ブロック内の異なる行は、これらが異なるクラスタ化表にある場合のみ、同じROWIDを持つことができます。行が1データ・ブロックよりも長い場合、ROWIDによって最初の行断片が識別されます。
ROWIDを表示するには、ROWID疑似列を問い合せます。疑似列ROWIDの各値は、行のアドレスを表す文字列です。文字列のデータ型はROWIDまたはUROWIDです。
|
参照:
|
|
注意: ハイブリッド列圧縮(HCC)で圧縮された表の行を更新すると、行のROWIDが変更されます。特定のOracleストレージ・システムの機能であるHCCの詳細は、『Oracle Database概要』を参照してください。 |
例7-6では、ROWIDデータ型の列を持つ表を作成し、INSERT文でROWID擬似列を問い合せることでROWIDを移入し、表示します。表のROWID列により、格納方法が表示されます。
例7-6 ROWID擬似列の問合せ
DROP TABLE t_tab; -- in case it exists CREATE TABLE t_tab (col1 ROWID); INSERT INTO t_tab (col1) SELECT ROWID FROM employees WHERE employee_id > 199;
問合せ:
SELECT employee_id, rowid
FROM employees
WHERE employee_id > 199;
ROWIDは異なりますが、次に類似した結果が得られます。
EMPLOYEE_ID ROWID
----------- ------------------
200 AAAPeSAAFAAAABTAAC
201 AAAPeSAAFAAAABTAAD
202 AAAPeSAAFAAAABTAAE
203 AAAPeSAAFAAAABTAAF
204 AAAPeSAAFAAAABTAAG
205 AAAPeSAAFAAAABTAAH
206 AAAPeSAAFAAAABTAAI
7 rows selected.
問合せ:
SELECT * FROM t_tab;
COL1は異なりますが、次に類似した結果が得られます。
COL1 ------------------ AAAPeSAAFAAAABTAAC AAAPeSAAFAAAABTAAD AAAPeSAAFAAAABTAAE AAAPeSAAFAAAABTAAF AAAPeSAAFAAAABTAAG AAAPeSAAFAAAABTAAH AAAPeSAAFAAAABTAAI 7 rows selected.
動的パフォーマンス・ビューV$SQLFN_METADATAには、SQL演算子とファンクションのメタデータが表示されます。V$SQLFN_METADATAによって表示されるすべてのファンクションに対し、動的パフォーマンス・ビューV$SQLFN_ARG_METADATAには、それぞれのファンクションの引数に関するメタデータの1行が含まれます。関数の引数が反復可能な場合(関数LEASTおよびGREATEST内など)、V$SQLFN_ARG_METADATAには、それぞれの反復引数向けに1行のみが含まれます。列FUNC_IDでビューV$SQLFN_METADATAおよびV$SQLFN_ARG_METADATAを結合できます。
これらのビューによって、サード・パーティ・ツールは、アプリケーション・レイヤーでメタデータをメンテナンスすることなくSQLファンクションを活用できます。
内容は次のとおりです。
|
参照:
|
ビューV$SQLFN_METADATAの中で列DATATYPEは、関数のデータ型(つまり、関数が戻すデータ型)です。このデータ型は、Oracleデータ型、データ型ファミリ(7.7.3項を参照)、またはARGnのいずれかです。ARGnは、関数のn番目の引数のデータ型です。次に例を示します。
MAX関数(『Oracle Database SQL言語リファレンス』を参照)は、その最初の引数のデータ型を持つ値を戻すため、MAX関数はデータ型ARG1を戻します。
DECODE関数(『Oracle Database SQL言語リファレンス』を参照)は、その3番目の引数のデータ型を持つ値を戻すため、DECODE関数のデータ型は、ARG3です。
ビューV$SQLFN_METADATAの中では、列DISP_TYPEが任意の式を持つ引数のデータ型です。式は、単一の値、または値と単一の値を持つSQLファンクションの組合せから構成されています。
通常、SQLファンクションの引数には、データ型ファミリの任意のデータ型を含めることができます。表7-11に、SQLデータ型ファミリとそのメンバーのデータ型を示します。
