式は、次の目的に使用します。
INSERT...SELECT
文の選択リスト
WHERE
句およびHAVING
句の条件
GROUP BY
句およびORDER BY
句
次の項では、TimesTenの式について説明します。
式には、SQL処理で使用する値を指定します。
式は、算術演算子、比較演算子、文字列またはバイナリ演算子、ビット演算子、あるいは第4章「機能」で説明する任意の関数で連結された1つまたは複数のプライマリで構成できます。プライマリは、SQL構文に示された項目から算出される、符号付きまたは符号なしの値です。
{ColumnName
| ROWID | {? | :DynamicParameter
} |Function
|Constant
| (Expression
)}
または
[[+ |-] {ColumnName
| SYSDATE | TT_SYSDATE|GETDATE() | {? | :DynamicParameter
} |Function
|Constant
| {~ | + | -}Expression
}] [...]
または
Expression1
[& | | | ^ | + | / | * | - ]Expression2
または
Expression1
| |Expression2
または
Expression
構成要素 | 説明 |
---|---|
+, – |
単項プラスと単項マイナス
単項マイナスはプライマリの符号を変更します。デフォルトの設定では、符号は変更しません。 |
ColumnName |
値を取得する列の名前
列名の詳細は、第2章「名前、ネームスペースおよびパラメータ」を参照してください。 |
ROWID |
表に保存される各行の一意のID
ROWID値は |
?
|
動的パラメータのプレースホルダ
動的パラメータの値は、実行時に設定されます。 |
Function |
計算された値
第4章「関数」を参照してください。 |
Constant |
特定の値
「定数」を参照してください。 |
( Expression ) |
カッコで囲まれた任意の式 |
Expression1
|
指定された式
|
* |
2つのプライマリを乗算します。 |
/ |
2つのプライマリを除算します。 |
+ |
2つのプライマリを加算します。 |
– |
2つのプライマリを減算します。 |
& |
2つのオペランドのビット単位のAND
|
| |
2つのオペランドのビット単位のOR
|
~ |
オペランドのビット単位のNOT
|
^ |
2つのオペランドの排他OR
|
|| |
連結演算子
両方の式が文字列の場合に、 |
説明
数値間の計算には、算術演算子を使用できます。「数値データ型」を参照してください。
日時値および時間隔型でも、算術演算子を使用できます。日時式の結果は、日時データ型または時間隔データ型です。
文字列値に算術演算子は使用できません。
式の各要素は、次の順序で評価されます。
カッコ内の関数および式。
単項プラスと単項マイナス。
*
および/
処理。
+
および–
処理。
優先順位が同じ要素については、左から右の順に評価されます。
式をカッコで囲み、評価の順序を制御できます。次に例を示します。
10 * 2 – 1 = 19 but 10 * (2 – 1) = 10
評価される式によっては、データ型の変換、切捨て、アンダーフロー、オーバーフローが発生する場合があります。第1章「データ型」を参照してください。
数値式のいずれかのオペランドがNULL
の場合、結果はNULL
になります。
NVL
は、2つのパラメータ(両方とも式で指定)をとるため、TimesTenではどちらの位置にもNULL
は使用できません。式にNULL
値が含まれる場合、比較演算子や他の条件はNULL
と評価されます。NULL値を含む比較演算子や条件の評価の詳細は、第5章「検索条件」
を参照してください。TimesTenではNULL
を挿入することはできますが、通常、INSERT
には特定の値のみが使用され、一般的な式は使用されません。
条件に、別々のROWID等式を指定するか、またはIN
を使用すると、問合せオプティマイザと実行エンジンで複数のROWID検索が可能です。たとえば、複数の高速ROWID検索は、次のように実行します。
WHERE ROWID = :v1 OR ROWID = :v2
あるいは、次のように指定します。
WHERE ROWID IN (:v1, :v2)
?
または:
DynamicParameter
は、式の中で動的パラメータとして使用できます。
例
この例では、任意のSELECT
文のWHERE
句における動的パラメータを示します。
SELECT * FROM purchasing.orders WHERE partnumber = ? AND ordernumber > ? ORDER BY ordernumber;
この例では、UPDATE
文のWHERE
句およびSET
句における動的パラメータを示します。
UPDATE purchasing.parts SET salesprice = :dynamicparameter1 WHERE partnumber = :dynamicparameter2;
この例では、DELETE
文のWHERE
句における動的パラメータを示します。
DELETE FROM purchasing.orderitems WHERE itemnumber BETWEEN ? AND ?;
この例では、INSERT
文のVALUES
句における動的パラメータを示します。この例では、?
と:
dynamicparameter
の両方をpurchasing.orderitems
表の2番目と4番目の列の両方に対応する:dynamicparameter1
で使用します。そのため、この式には、2番目と4番目の列の両方に使用する2番目のパラメータを使用して、4つの動的パラメータのみを渡す必要があります。
INSERT INTO purchasing.orderitems VALUES (?,:dynamicparameter1, :dynamicparameter2, :dynamicparameter1,?);
この例には、?
および:dynamicparameter
の両方を同じSQL文で使用できることと、両方のタイプの動的パラメータを繰り返すことの意味上の違いが示されています。
ビット単位演算子の例を次に示します。
Command> SELECT 0x183D & 0x00FF FROM dual; < 003D > 1 row found. Command> SELECT ~255 FROM dual; < -256 > 1 row found. Command> SELECT 0x08 | 0x0F FROM dual; < 0F > 1 row found.
TimesTenでは、検索条件のINSERT...SELECT
文、CREATE VIEW
文またはUPDATE
文、およびUPDATE
文のSET
句で、導出された表として副問合せがサポートされます。サポートされるのは、表の副問合せおよびスカラー副問合せです。TimesTenでは、行の副問合せはサポートされません。副問合せでは、HAVING
句または結合表を使用して集計を指定できます。副問合せは相関も可能です。
SQL構文
[NOT] EXISTS | [NOT] IN (Subquery
)Expression
{= | <> | > | >= | < | <= } [ANY | ALL] (Subquery
)Expression
[NOT] IN (ValueList
|Subquery
)
ValueList
は定数式のリストです。各定数式は、定数値または定数値になる式(数字、文字列、日付など)を指定します。バインドされた値(?
または:DynamicParameter
)、NULL
、および定数値を返す関数のコールのサポートが含まれます。
説明
TimesTenでは、各項で説明した特性を持つ問合せがサポートされます。
表の副問合せ
副問合せは、マテリアライズド・ビューを作成する文を除き、任意の文のWHERE
またはHAVING
句に指定できます。表の副問合せは、1つの条件に1つのみ指定できます。これらの条件は、WHERE
句やHAVING
句、WHERE
句やHAVING
句内のOR
式または結合された表のON
句に指定できます。CASE
式、マテリアライズド・ビューまたは外部結合のために+
演算子を使用するHAVING
句には指定できません。
副問合せは、EXISTS
またはNOT EXISTS
条件、ANY
またはALL
を使用する限定条件または比較条件に指定できます。比較条件および限定条件の両方に使用できる演算子は、=
、<
、>
、<=
、>=
、<>
です。UNIQUE
またはNOT UNIQUE
演算子を使用して、副問合せを外部問合せに連結することはできません。
限定条件または比較条件に指定できる副問合せは1つのみです。副問合せは、条件の右側のオペランドまたは左側のオペランドとして指定します。両方には指定しません。
FIRST
NumRows
は、副問合せの文ではサポートされていません。
限定条件または比較条件に指定された問合せでは、基礎となるSELECT
の選択リストは単一の式にする必要があります。比較条件で指定される問合せの場合に、基礎となるSELECTが単一の行を戻すときは、その戻り値が選択結果になります。基礎となるSELECTが行を戻さない場合、戻り値はNULL
です。副問合せが複数の行を戻す場合はエラーになります。
スカラー副問合せ
スカラー副問合せは単一の値を返します。
外部問合せの各行に1行のみを戻すことを、コンパイル時にオプティマイザが検出できないような条件を含むスカラー副問合せは、検証できません。OR
式にこのような副問合せを指定することはできません。
例
次に、1件以上の未発送の発注を含む顧客リストの副問合せの例を示します。
SELECT customers.name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customers.id = orders.custid AND orders.status = 'unshipped'); SELECT customers.name FROM customers WHERE customers.id = ANY (SELECT orders.custid FROM orders WHERE orders.status = 'unshipped'); SELECT customers.name FROM customers WHERE customers.id IN (SELECT orders.custid FROM orders WHERE orders.status = 'unshipped');
この例では、発注日と同じ日付に発送されたアイテムをリストします。
SELECT line_items.id FROM line_items WHERE line_items.ship_date = (SELECT orders.order_date FROM orders WHERE orders.id = line_items.order_id);
定数はリテラル値です。
{IntegerValue
|FloatValue
|FloatingPointLiteral
|FixedPointValue
| 'CharacterString
'| 'NationalCharacterString
' |HexadecimalLiteral
| 'DateString
' |DateLiteral
|'TimeString
' |TimeLiteral
| 'TimestampString
' | TimestampLiteral |IntervalLiteral
| BINARY_FLOAT_INFINITY | BINARY_DOUBLE_INFINITY | -BINARY_FLOAT_INFINITY | -BINARY_DOUBLE_INFINITY | BINARY_FLOAT_NAN | BINARY_DOUBLE_NAN }
書式モデルは、文字列に格納された日時および数値データの書式を示す文字リテラルです。文字列を日付または数値に変換する場合は、書式モデルによって、TimesTenでの文字列の解釈方法が決まります。
この項では、次の書式モデルについて説明します。
数値書式モデルは、次の関数で使用します。
NUMBER
、BINARY_FLOAT
またはBINARY_DOUBLE
データ型の値をVARCHAR2
データ型に変換するTO_CHAR
関数。
CHAR
またはVARCHAR2
データ型の値をNUMBER
データ型に変換するTO_NUMBER
関数。
デフォルトのamerican_america
NLS言語および地域の設定が使用されます。
数値書式モデルは、1つ以上の数値書式要素で構成されます。次の表に、数値書式モデルの要素を示します。数値書式モデルに書式要素MI
、S
またはPR
が指定されないかぎり、負の戻り値の先頭には自動的に負の符号が付けられ、正の戻り値の先頭には空白が付けられます。
表3-1 数値書式要素
要素 | 例 | 説明 |
---|---|---|
|
|
指定された位置にカンマを戻します。1つの数値書式モデルに複数のカンマを指定できます。 制限:
|
|
|
指定された位置に小数点(ピリオド(.))を戻します。 制限: 1つの書式モデルで指定できるピリオドは1つのみです。 |
$ |
|
先行ドル記号を含む値を戻します。 |
|
|
先行0(ゼロ)を戻します。 後続0(ゼロ)を戻します。 |
|
|
指定された桁数の値を戻します。正の場合は先行空白が含まれ、負の場合は先行マイナスが含まれます。 固定小数点数の整数部分の場合、先行0(ゼロ)に対して空白を戻します。ただし、値0(ゼロ)に対しては0(ゼロ)を戻します。 |
|
|
固定小数点数の整数部分が0(ゼロ)の場合、空白を戻します(書式モデル内の0(ゼロ)には関係ありません)。 |
|
|
指定された位置にISO通貨記号を戻します。 |
|
|
指定された位置に小数点文字を戻します。デフォルトはピリオド( 制限: 1つの数値書式モデルで指定できる小数点文字は1つのみです。 |
|
|
科学表記法で値を戻します。 |
|
|
指定された位置にグループ・セパレータを戻します。1つの数値書式モデルに複数のグループ・セパレータを指定できます。 制限: 数値書式モデルでは、小数点文字(ピリオド)の右側にグループ・セパレータは使用できません。 |
|
|
指定した位置にローカル通貨記号を戻します。 |
|
|
後続のマイナス符号( 後続の空白を含む正の値を戻します。 制限:
|
|
|
山カッコ( 先行および後続の空白を含む正の値を戻します。 制限:
|
|
|
値を大文字のローマ数字として戻します。 |
|
|
値を小文字のローマ数字として戻します。 1から3999までの整数を値として指定できます。 |
|
|
先行のマイナス符号( 先行のプラス符号( |
|
|
後続のマイナス符号( 後続のプラス符号( 制限:
|
|
|
テキストの最小数値書式モデルは、文字の最小数を(10進出力で)戻します。この要素の大文字と小文字は区別されません。 デフォルトは 制限:
|
|
|
指定された位置にユーロまたは他の二重通貨記号を戻します。 |
|
|
10nを乗算した値を戻します(この値は必要に応じて丸められます)。 |
|
|
指定された桁数の16進値を戻します。指定された数値が整数でない場合は、TimesTenによって整数に丸められます。 制限:
|
日時書式モデルは、次の関数で使用します。
日時値のデフォルト書式以外の文字値を変換するTO_CHAR
関数またはTO_DATE
関数。
デフォルト書式以外の日時値を文字列に変換するTO_CHAR
関数。
日時書式モデルの合計の長さは、22文字を超えることができません。
デフォルトのamerican_america
NLS言語および地域の設定が使用されます。
日時書式モデルは、表3-2に示す、1つ以上の日時書式要素で構成されます。
表3-2 日時書式要素
要素 | 説明 |
---|---|
|
句読点および引用符付きテキストは、結果で複製されます。 |
|
ピリオド付きまたはピリオドなしで西暦を示します。 |
|
ピリオド付きまたはピリオドなしで午前を示します。 |
|
ピリオド付きまたはピリオドなしで紀元前を示します。 |
|
曜日(1から7) |
|
曜日。最大幅の名前を表示するために空白で埋められます。 |
|
日付(1から31) |
|
通日 |
|
長い日付書式 デフォルトの 制限: この書式は、空白で区切られた |
|
短い日付書式 デフォルトの 制限: この書式は、空白で区切られた |
|
曜日の省略名 |
|
先行または後続の空白はありません。 |
|
文字データと書式モデルが完全に一致します。 |
|
時間(1から12) |
|
時間(0から23) |
|
ユリウス日 紀元前4712年1月1日からの日数です。 |
|
分(0から59) |
|
月(01から12。1月=01) |
|
月の省略名 |
|
月の名前。最大幅の名前を表示するために空白で埋められます。 |
|
ローマ数字での月(IからXII。1月=I) |
|
20世紀の年(21世紀に2桁のみを使用) |
|
年を丸めます。 4桁または2桁で入力できます。2桁の場合、 |
|
秒(0から59) |
|
午前0時以降の秒数(0から86399) |
|
短い時間書式 制限: この書式は、空白で区切られた |
|
ローカル基数文字 次に例を示します。 'HH:MI:SSXFF' |
|
指定の位置にカンマを付けた年 |
|
年(4桁)
|
|
年の下3桁、下2桁または下1桁 |
次の表に、ROUND
およびTRUNC
日付関数に使用可能な書式モデルと、日付が丸められる場合や切り捨てられる場合の単位を示します。デフォルトのモデルDD
では、時刻部分が午前0時に丸められるか切り捨てられた日付が戻されます。
書式モデル | 丸め単位または切捨て単位 |
---|---|
CC
|
世紀
4桁の年の下2桁が01から99(01および99を含む)の場合、世紀はその年の上2桁に1を加えた値になります。 4桁の年の下2桁が00の場合、世紀はその年の上2桁と等しい値になります。たとえば、2002は21を返し、2000は20を返す。 |
SYYYY
|
年(7月1日で切上げ) |
IYYY
|
ISO年 |
Q |
四半期(四半期の2番目の月の16日で切上げ) |
MONTH
|
月の名前(16日で切上げ) |
WW |
対象となる年の最初の日と同じ曜日 |
IW |
ISO週の最初の日と同じ曜日(月曜日) |
W |
対象となる月の最初の日と同じ曜日 |
DDD
|
通日 |
DAY
|
開始曜日 |
HH
|
時刻 |
MI |
分 |
この書式モデルは、TO_CHAR
関数を呼び出してTT_TIMESTAMP
またはTT_DATE
の日時値を変換する場合に使用します。また、TO_CHAR
関数を呼び出してNUMBER
またはORA_FLOAT
以外の任意の数値を変換する場合にも使用します。
指定した書式に数値が適合しない場合、値は切り捨てられます。
書式文字列は、50文字以内で指定します。
D
の結果は、常に小数点になります。NLSパラメータを使用して、その値を変更することはできません。
1e-126
未満または1e126
より大きい絶対値を含む浮動小数点を、TO_CHAR
関数への入力として指定した場合、エラーが発生します。
書式 | 説明 |
---|---|
DD |
日付(1から31) |
MM |
月(1から12) |
MON |
月(3文字の接頭辞) |
MONTH |
月(空白が埋め込まれた9文字の正式名) |
YYYY |
年(4桁) |
Y , YYY |
年(カンマ付き) |
YYY |
年(末尾3桁) |
YY |
年(末尾2桁) |
Y |
年(末尾1桁) |
Q |
四半期 |
HH |
時刻(1から12) |
HH12 |
時刻(1から12) |
HH24 |
時刻(0から23) |
MI |
分(0から59) |
SS |
秒(0から59) |
FF |
6桁の精度の秒の小数部 |
FFn |
n で指定される精度の秒の小数部 |
AM |
正午標識 |
A.M. |
正午標識 |
PM |
正午標識 |
P.M. |
正午標識 |
- / , . ; : |
出力される記号 |
"text" | 出力されるテキスト |
9 |
桁 |
0 |
先行または後続0(ゼロ) |
. |
小数点 |
, | カンマ |
EEEE |
科学表記法 |
S |
符号モード |
B |
空白モード
数字がない場合、文字列は空白で埋められます。 |
FM |
非空白モード(埋込みモード)
この要素が使用される場合、後続または先行の空白は削除されます。 |
$ |
先行ドル記号 |
条件付きの値を指定します。単純なCASE式と検索対象のCASE式がサポートされています。CASE
式は、式を任意の場所で指定でき、必要に応じて何度でも使用できます。
一連のIF文を使用するかわりに、CASE
式を使用すると、条件が満たされた場合に適切な値を返す一連の条件を使用できます。CASE
式を使用すると、問合せを簡略化し、効率のよいコードを記述できます。
SQL構文
検索対象のCASE
式の構文は、次のとおりです。
CASE {WHENSearchCondition
THENExpression1
}[…] [ELSEExpression2
] END
単純なCASE
式の構文は、次のとおりです。
CASEExpression
{WHENCompExpression
THENExpression1
}[…] [ELSEExpression2
] END
TimesTenでは、表に保存される各行にrowidと呼ばれる一意のIDが割り当てられます。このROWIDのデータ型はROWID
です。ROWIDは、ROWID
擬似列を問い合せることで確認できます。
ROWID
擬似列は実際の列ではないため、データベース領域は不要です。また、更新したり、索引を作成したり、削除することはできません。
ROWID値は表に行が存在する間は使用されますが、元の行が削除されると、そのROWIDはシステムによって違う行に割り当てられる場合があります。0(ゼロ)はROWIDに使用できません。
ROWIDはリカバリ、バックアップおよびリストア処理を行っても使用できます。ただし、レプリケーション、ttMigrate
またはttBulkCp
の処理を行うと使用できなくなります。
ROWIDの詳細は、「式の指定」を参照してください。ROWIDデータ型の詳細は、「ROWIDデータ型」
を参照してください。
問合せによって戻される行ごとに、ROWNUM
擬似列によって、行が選択された順序を示す数値が戻されます。最初に選択された行にはROWNUM
として1が、2番目に選択された行にはROWNUM
として2が割り当てられ、以降も同様の割当てが行われます。
次の例に示すようにROWNUM
を使用して、問合せによって戻される行数を制限します。
SELECT * FROM employees WHERE ROWNUM < 10;
行が選択された順序は、使用している索引および結合順序によって異なります。ORDER BY
句を指定すると、ソートが行われる前にROWNUM
が割り当てられます。ただし、ORDER BY
句を指定すると、使用している索引および結合順序が変更される可能性があります。選択された行の順序が変更されると、選択された各行に関連付けられているROWNUM
値も変更される可能性があります。
たとえば、別の索引が使用された場合、次の問合せは、以前に実行した問合せとは異なる従業員を戻す可能性があります。
SELECT * FROM employees WHERE ROWNUM < 10 ORDER BY last_name;
比較条件「ROWNUM
値>正の整数」は、常に偽となるため注意してください。たとえば、次の問合せでは行が戻されません。
SELECT * FROM employees WHERE ROWNUM > 1;
ROWNUM
を使用して一意の値を表の各行に割り当てます。次に例を示します。
UPDATE my_table SET column1 = ROWNUM;
問合せにFIRST
NumRows
またはROWS
m
TO
n
が含まれている場合は、ROWNUM
を使用して戻される行の数を制限しないでください。たとえば、次の問合せでは、エラー・メッセージが戻されます。
SELECT FIRST 2 * FROM employees WHERE ROWNUM <1 ORDER BY employee_id; 2974: Using rownum to restrict number of rows returned cannot be combined with first N or rows M to N