11g リリース1（11.1）
E05750-03
 目次 |
 索引 |
| Oracle Database SQL言語リファレンス 11g リリース1（11.1） E05750-03 |
|
この章では、値、演算子およびファンクションを式の中で組み合せて使用する方法について説明します。
この章では、次の内容を説明します。
式は、1つ以上の値、演算子、および値に評価されるSQLファンクションの組合せです。一般に、式のデータ型は、そのコンポーネントのデータ型になります。
次の単純式は、値が4になり、データ型はNUMBER（コンポーネントと同じデータ型）になります。
2*2
次の例は、ファンクションと演算子を使用した複雑な式です。この式は、現在の日付に7日を加算し、その合計から時間コンポーネントを削除し、結果をCHARデータ型に変換します。
TO_CHAR(TRUNC(SYSDATE+7))
次の場所で式を使用できます。
SELECT文のSELECT構文のリスト
WHERE句およびHAVING句の条件
CONNECT BY句、START WITH句およびORDER BY句
INSERT文のVALUES句
UPDATE文のSET句
たとえば、次のUPDATE文のSET句で、引用符で囲まれた文字列'Smith'のかわりに式を使用することもできます。
SET last_name = 'Smith';
このSET句では、引用符で囲まれた文字列'Smith'のかわりに、INITCAP(last_name)を使用しています。
SET last_name = INITCAP(last_name);
次の構文に示すとおり、式にはいくつかの書式があります。
Oracle Databaseは、すべてのSQL文のすべての部分で、式のすべての書式を受け入れるわけではありません。文に指定する式の制限については、第10章〜第19章の個々のSQL文の説明を参照してください。
このマニュアルの他の箇所で、条件、SQLファンクションまたはSQL文にexprが示されている場合は、必ず適切な式の表記法を使用してください。次の項では、いくつかの例をあげて、様々な式の書式を説明します。
単純式は、列、疑似列、定数、順序番号またはNULLを指定します。
スキーマは各ユーザー用の他に、"PUBLIC"（二重引用符が必要）にもなり得ます。その場合、スキーマは表、ビューまたはマテリアライズド・ビューのパブリック・シノニムを修飾する必要があります。"PUBLIC"でのパブリック・シノニムの修飾は、データ操作言語（DML）文でのみサポートされています。データ定義言語（DDL）文ではサポートされていません。
列の後ろに続くオプションのPRECEDINGキーワードおよびINITIALキーワードは、バージョン問合せを発行する場合にのみ有効です。
ROWIDは、表でのみ使用でき、ビューまたはマテリアライズド・ビューでは使用できません。NCHARおよびNVARCHAR2は、有効な疑似列データ型ではありません。
有効な単純式の例を次に示します。
employees.last_name 'this is a text string' 10 N'this is an NCHAR string'
複合式は、その他の式の組合せを指定します。
組込みファンクションを式として使用できます（「ファンクション式」を参照してください）。ただし、複合式では、ファンクションの組合せによっては、適切でないものや拒否されるものもあります。たとえば、LENGTHファンクションは集計ファンクション内では使用できません。
PRIOR演算子は、階層問合せのCONNECT BY句で使用されます。
有効な複合式の例を次に示します。
('CLARK' || 'SMITH') LENGTH('MOOSE') * 57 SQRT(144) + 72 my_fun(TO_CHAR(sysdate,'DD-MMM-YY'))
CASE式を使用すると、プロシージャを起動せずに、SQL文でIF ... THEN ... ELSE論理を使用できます。構文は次のとおりです。
単純CASE式では、Oracle Databaseは、exprとcomparison_exprが一致する最初のWHEN ... THENの組合せを検索し、return_exprを戻します。WHEN ... THENの組合せが条件に一致せず、ELSE句が存在する場合、Oracleはelse_exprを戻します。それ以外の場合、OracleはNULLを戻します。return_exprおよびelse_exprには、リテラルNULLを指定できません。
検索CASE式では、Oracleは、conditionが真である項目を左から右へ検索し、return_exprを戻します。真であるconditionがなく、ELSE句が存在する場合、Oracleはelse_exprを戻します。それ以外の場合、OracleはNULLを戻します。
Oracle Databaseでは、短絡評価を使用します。単純CASE式の場合、データベースは、comparison_expr値のいずれかとexprを比較する前にすべてのcomparison_expr値を評価するのではなく、各comparison_expr値とexprを比較する前にのみ、各comparison_expr値を評価します。その結果、exprと等しいcomparison_exprが見つかると、Oracleはその後のcomparison_exprを評価しません。検索CASE式の場合、データベースは各conditionを評価してこれが真であるかどうかを判断します。ただし、あるconditionが真の場合は、次のconditionを評価しません。
単純CASE式では、exprおよびすべてのcomparison_expr値は、同じデータ型（CHAR、VARCHAR2、NCHAR、NVARCHAR2、NUMBER、BINARY_FLOATまたはBINARY_DOUBLE）であるか、またはすべて数値データ型である必要があります。すべての式が数値データ型の場合、Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。
単純および検索CASE式では、すべてのreturn_exprは、同じデータ型（CHAR、VARCHAR2、NCHAR、NVARCHAR2、NUMBER、BINARY_FLOATまたはBINARY_DOUBLE）であるか、またはすべて数値データ型である必要があります。すべての戻り式が数値データ型の場合、Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。
1つのCASE式では、引数の最大数は255です。単純CASE式の初期式やオプションのELSE式を含むすべての式が、この上限の対象となります。WHEN ... THENの各組は、2つの引数としてカウントします。この上限を超えないように、CASE式をネストし、return_expr自体がCASE式となるようにすることができます。
|
参照:
|
次の文は、サンプル表oe.customersのそれぞれの顧客について、クレジット利用限度額を、100ドルの場合は「Low」、5,000ドルの場合は「High」、それ以外の場合は「Medium」で表示します。
SELECT cust_last_name, CASE credit_limit WHEN 100 THEN 'Low' WHEN 5000 THEN 'High' ELSE 'Medium' END AS credit FROM customers ORDER BY cust_last_name, credit; CUST_LAST_NAME CREDIT -------------------- ------ Adjani Medium Adjani Medium Alexander Medium Alexander Medium Altman High Altman Medium . . .
次の文は、2,000ドルを最少額の給与として、サンプル表oe.employeesの従業員の給与の平均を検出します。
SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary ELSE 2000 END) "Average Salary" FROM employees e; Average Salary -------------- 6461.68224
後述の構文図でcolumn_exprで示される列式は、exprの書式の一部です。列式は単純式、複合式、ファンクション式または式リストにできますが、列式に含めることができるのは、次の書式の式にかぎられます。
これ以外に、この章で説明されている書式で使用できるものはありません。また、PRIORキーワードを使用した複合式および集計ファンクションはサポートされません。
列式は、次の目的で使用できます。
column_exprの定義では、現行の文または以前の文ですでに定義されている対象の表の列のみを参照する必要があります。
列式を組み合せた要素は、決定的である必要があります。つまり、同じ入力値のセットによって、同じ出力値のセットが戻される必要があります。
CURSOR式は、ネステッド・カーソルを戻します。この式の書式は、PL/SQLのREF CURSORと同じで、REF CURSOR引数としてファンクションに渡すことができます。
カーソル式が評価されるときに、ネステッド・カーソルは暗黙的にオープンされます。たとえば、カーソル式がSELECT構文のリストにある場合、問合せによってフェッチされた各行に対して、ネステッド・カーソルがオープンされます。ネステッド・カーソルは、次の場合にのみクローズされます。
CURSOR式には、次の制限事項があります。
SELECT文以外の文である場合、ネステッド・カーソルは、プロシージャのREF CURSOR引数としてのみ表示されます。
SELECT文である場合、ネステッド・カーソルは、問合せ指定の一番外側のSELECT構文のリスト、または別のネステッド・カーソルの一番外側のSELECT構文のリストに表示されます。
BIND操作およびEXECUTE操作は実行できません。
問合せのSELECT構文のリストでのCURSOR式の使用方法について、次に例を示します。
SELECT department_name, CURSOR(SELECT salary, commission_pct FROM employees e WHERE e.department_id = d.department_id) FROM departments d ORDER BY department_name;
ファンクションの引数としてのCURSORの使用方法について、次に例を示します。例では、まず、サンプル・スキーマOEにREF CURSOR引数を受け入れるファンクションを作成します。（イタリック体は、PL/SQLファンクションの本体です。）
CREATE FUNCTION f(cur SYS_REFCURSOR, mgr_hiredate DATE) RETURN NUMBER IS emp_hiredate DATE; before number :=0; after number:=0; begin loop fetch cur into emp_hiredate; exit when cur%NOTFOUND; if emp_hiredate > mgr_hiredate then after:=after+1; else before:=before+1; end if; end loop; close cur; if before > after then return 1; else return 0; end if; end; /
ファンクションには、カーソルおよび日付を指定できます。ファンクションは、カーソルが日付セットを戻す問合せであることを想定します。次の問合せでは、ファンクションを使用して、サンプル表employeesから、ほとんどの従業員がマネージャよりも前に雇用されているマネージャを検索します。
SELECT e1.last_name FROM employees e1 WHERE f( CURSOR(SELECT e2.hire_date FROM employees e2 WHERE e1.employee_id = e2.manager_id), e1.hire_date) = 1 ORDER BY last_name; LAST_NAME ------------------------- Cambrault De Haan Higgins Mourgos Zlotkey
日時式は、日時データ型の値を戻します。
初期のexprは、データ型TIMESTAMP、TIMESTAMP WITH TIME ZONEまたはTIMESTAMP WITH LOCAL TIME ZONEの値に評価される任意の式（スカラー副問合せ式を除く）です。DATEデータ型はサポートされません。このexpr自体がdatetime_expressionである場合は、カッコで囲む必要があります。
表2-5で定義される規則に従って、日時および期間を組み合せることができます。日時の値を戻す3つの組合せは、日時式で有効です。
AT LOCALを指定すると、Oracleは現行のセッションのタイムゾーンを使用します。
AT TIME ZONEの設定は、次のように解析されます。
'(+|-)HH:MM': UTCのオフセットとしてタイムゾーンを指定します。
DBTIMEZONE: Oracleは、データベースの作成中に（明示的またはデフォルトで）構築されたデータベース・タイムゾーンを使用します。
SESSIONTIMEZONE: Oracleは、デフォルトで構築されたセッション・タイムゾーン、または最新のALTER SESSION文で構築されたセッション・タイムゾーンを使用します。
time_zone_name: Oracleは、time_zone_nameで指定されたタイムゾーンのdatetime_value_exprを戻します。有効なタイムゾーン名を表示するには、V$TIMEZONE_NAMES動的パフォーマンス・ビューに問合せを実行してください。expr: exprが有効なタイムゾーン書式で文字列を戻す場合、Oracleは、そのタイムゾーンで入力を戻します。そうでない場合は、エラーが戻ります。
次の例は、タイムゾーンの日時の値を別のタイムゾーンに変換します。
SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York') AT TIME ZONE 'America/Los_Angeles' "West Coast Time" FROM DUAL; West Coast Time ------------------------------------------------ 01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES
組込みSQLファンクションまたはユーザー定義ファンクションを式として使用できます。有効な組込みファンクション式の例を次に示します。
LENGTH('BLAKE') ROUND(1234.567*43) SYSDATE
ユーザー定義ファンクション式は、次のものへのコールを指定します。
有効なユーザー定義ファンクション式の例を次に示します。
circle_area(radius) payroll.tax_rate(empno) hr.employees.comm_pct@remote(dependents, empno) DBMS_LOB.getlength(column_name) my_function(a_column)
式として使用されるユーザー定義ファンクションでは、位置表記法、名前付き表記法および複合表記法がサポートされています。たとえば、次の表記はすべて正しい表記です。
CALL my_function(arg1 => 3, arg2 => 4) ... CALL my_function(3, 4) ... CALL my_function(3, arg2 => 4) ...
オブジェクト型またはXMLTypeの引数をリモート・ファンクションおよびリモート・プロシージャに渡すことはできません。
期間式は、INTERVAL YEAR TO MONTHまたはINTERVAL DAY TO SECONDの値を戻します。
式expr1およびexpr2は、データ型DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONEまたはTIMESTAMP WITH LOCAL TIME ZONEの値に評価される任意の式にすることができます。
表2-5で定義される規則に従って、日時および期間を組み合せることができます。期間の値を戻す6つの組合せは、期間式で有効です。
leading_field_precisionおよびfractional_second_precisionは、0〜9の任意の整数です。DAYまたはYEARのいずれかでleading_field_precisionを省略すると、Oracle Databaseはデフォルト値である2を使用します。秒でfractional_second_precisionを省略すると、データベースはデフォルト値である6を使用します。問合せで戻された値にデフォルトの精度を超える桁数が含まれる場合、Oracle Databaseはエラーを戻します。したがって、問合せで戻されるとわかっている値以上の精度を指定することをお薦めします。
たとえば、次の文は、システム・タイムスタンプ（日時の値）からサンプル表ordersのorder_date列の値（別の日時の値）を減算して、期間値の式を戻します。一番古い注文が発注されたのが何日前かわからないため、DAYの先行フィールド精度に最大値である9を指定します。
SELECT (SYSTIMESTAMP - order_date) DAY(9) TO SECOND FROM orders WHERE order_id = 2458;
モデル式は、SELECT文のmodel_clauseでのみ、さらにモデル・ルールの右側でのみ使用されます。モデル式は、model_clauseで事前定義されたメジャー列のセルに値を戻します。詳細は、「model_clause」を参照してください。
モデル式でメジャー列を指定する場合、指定する任意の条件と式は単一の値に変換される必要があります。
モデル式で集計ファンクションを指定する場合、ファンクションの引数はmodel_clauseで事前定義したメジャー列です。集計ファンクションは、モデル・ルールの右側でのみ使用できます。
モデル・ルールの右側で分析ファンクションを指定すると、model_clauseに複雑な計算を直接表記することができます。モデル式で分析ファンクションを使用するときには、次の制限事項が適用されます。
UPDATEルールのみで使用できます。
FORループまたはORDER BY句が含まれている場合は、モデル・ルールの右側で分析ファンクションを指定できません。
OVER句の引数に集計を含めることはできません。
OVER句の前の引数にセル参照を含めることはできません。expr自体がモデル式である場合、ネストしたセル参照と呼ばれます。ネストしたセル参照には、次の制限事項が適用されます。
model_rules_clauseにAUTOMATIC ORDERが指定されているとき、ネストしたセル参照で使用されるメジャーがスプレッドシート句内のいずれのセルについても更新されない場合のみ、ネストしたセル参照をモデル・ルールの左側で使用できます。
後述するモデル式は、次に示すSELECT文のmodel_clauseに基づいています。
SELECT country,prod,year,s FROM sales_view_ref MODEL PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale s) IGNORE NAV UNIQUE DIMENSION RULES UPSERT SEQUENTIAL ORDER ( s[prod='Mouse Pad', year=2000] = s['Mouse Pad', 1998] + s['Mouse Pad', 1999], s['Standard Mouse', 2001] = s['Standard Mouse', 2000] ) ORDER BY country, prod, year;
次のモデル式は、記号表記法を使用して単一セル参照を表しています。この式は、2000年のマウス・パッドの売上を示します。
s[prod='Mouse Pad',year=2000]
次のモデル式は、CVファンクションを使用して、位置表記法を使用する複数セル参照を表しています。この式は、prodディメンション列の現在値の2001年における売上を示します。
s[CV(prod), 2001]
次のモデル式は、集計ファンクションを表しています。この式は、yearディメンション列の現在の値-2からyearディメンション列の現在の値-1までの年のマウス・パッドの売上合計を示します。
SUM(s)['Mouse Pad',year BETWEEN CV()-2 AND CV()-1]
オブジェクト・アクセス式は、属性の参照およびメソッドの起動を指定します。
columnパラメータはオブジェクトまたはREF列です。exprを指定する場合、オブジェクト型に変換する必要があります。
ある型のメンバー・ファンクションがSQL文のコンテキストでコールされると、SELF引数がNULLの場合に、OracleはNULLを戻し、ファンクションは起動されません。
次の例では、サンプル・オブジェクト型oe.order_item_typに基づく表を作成し、オブジェクト列属性から更新および検索する方法を示します。
CREATE TABLE short_orders ( sales_rep VARCHAR2(25), item order_item_typ); UPDATE short_orders s SET sales_rep = 'Unassigned'; SELECT o.item.line_item_id, o.item.quantity FROM short_orders o;
プレースホルダ式は、SQL文における位置を指定します。この位置に対して、第三世代言語のバインド変数によって値が指定されます。プレースホルダ式には、オプションで標識変数を指定できます。この書式の式は、埋込みSQL文またはOracle Call Interface（OCI）プログラムで処理されるSQL文のみで指定できます。
有効なプレースホルダ式の例を次に示します。
:employee_name INDICATOR :employee_name_indicator_var :department_location
スカラー副問合せ式は、1つの行から1つの列値のみを戻す副問合せです。スカラー副問合せ式の値は、副問合せのSELECT構文リスト項目の値です。副問合せが0行を戻す場合、スカラー副問合せ式の値はNULLです。副問合せが2つ以上の行を戻す場合、Oracleはエラーを戻します。
スカラー副問合せ式は、式（expr）をコールするほとんどの構文で使用できます。すべての場合、スカラー副問合せは、その構文の場所がすでにカッコ内であっても（組込みファンクションの引数として使用されている場合など）、独自のカッコで囲む必要があります。
スカラー副問合せは、次の場所では無効です。
型コンストラクタ式は、コンストラクタ・メソッドへのコールを指定します。型コンストラクタの引数は、任意の式です。型コンストラクタは、ファンクションが起動されるすべての場所で起動できます。
NEWキーワードによって、コレクション型ではなくオブジェクトに、コンストラクタが適用されます。これによって、適切なコンストラクタを起動して新しいオブジェクトを作成するようにOracleに指示します。NEWキーワードはオプションですが、指定することをお薦めします。
type_nameがオブジェクト型の場合、最初の引数の値の型がオブジェクト型の最初の属性と一致する値を取り、2番目の引数の値の型がオブジェクト型の2番目の属性と一致するというように、式は順序付けられたリストになっている必要があります。コンストラクタの引数の合計数は、オブジェクト型の属性の合計数と一致する必要があります。
type_nameがVARRAY型またはネストした表型の場合、式のリストには0個以上の引数を含めることができます。引数が0個の場合は、空コレクションの構造であることを示します。それ以外の場合は、各引数が、型がコレクション型の要素型である要素値に対応します。
型コンストラクタ・メソッドの起動では、オブジェクト型に1000以上の属性がある場合でも、指定できるパラメータの数（expr）は最大で999です。この制限は、コンストラクタがSQLからコールされる場合にのみ適用されます。PL/SQLからコールされる場合には、PL/SQLの制限が適用されます。
次の例では、サンプル・スキーマoeのcust_address_typ型を使用して、コンストラクタ・メソッドへのコールに含まれる式の使用方法を示します（PL/SQLはイタリック体で示しています）。
CREATE TYPE address_book_t AS TABLE OF cust_address_typ; DECLARE myaddr cust_address_typ := cust_address_typ( '500 Oracle Parkway', 94065, 'Redwood Shores', 'CA','USA'); alladdr address_book_t := address_book_t(); BEGIN INSERT INTO customers VALUES ( 666999, 'Joe', 'Smith', myaddr, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); END; /
次の例では、サンプル・スキーマoeのwarehouse_typ型を使用して、コンストラクタ・メソッドへのコールに含まれる副問合せの使用方法を示します。
CREATE TABLE warehouse_tab OF warehouse_typ; INSERT INTO warehouse_tab VALUES (warehouse_typ(101, 'new_wh', 201)); CREATE TYPE facility_typ AS OBJECT ( facility_id NUMBER, warehouse_ref REF warehouse_typ); CREATE TABLE buildings (b_id NUMBER, building facility_typ); INSERT INTO buildings VALUES (10, facility_typ(102, (SELECT REF(w) FROM warehouse_tab w WHERE warehouse_name = 'new_wh'))); SELECT b.b_id, b.building.facility_id "FAC_ID", DEREF(b.building.warehouse_ref) "WH" FROM buildings b; B_ID FAC_ID WH(WAREHOUSE_ID, WAREHOUSE_NAME, LOCATION_ID) ---------- ---------- --------------------------------------------- 10 102 WAREHOUSE_TYP(101, 'new_wh', 201)
式のリストは、その他の式の組合せです。
式のリストは、比較条件、メンバーシップ条件、および問合せと副問合せのGROUP BY句に指定できます。比較条件またはメンバーシップ条件内の式のリストは、行値コンストラクタまたは行コンストラクタと呼ばれる場合があります。
比較条件およびメンバーシップ条件は、WHERE句の条件として指定します。これらの条件には、カンマで区切られた1つ以上の式、または1つ以上の式の集合（各集合には、カンマで区切られた式が1つ以上含まれる）を含めることができます。式の集合が複数存在する場合は、次のように指定します。
カンマで区切られた式のリストには、最大1000個の式を指定できます。カンマで区切られた式の集合のリストには、任意数の式の集合を含めることができますが、各集合に指定できる式は最大1000個です。
次に、条件に含める有効な式のリストを示します。
(10, 20, 40) ('SCOTT', 'BLAKE', 'TAYLOR') ( ('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA') )
3番目の例の場合、各集合の式の数は、条件の最初の部分の式の数と同じである必要があります。たとえば、次のようになります。
SELECT * FROM employees WHERE (first_name, last_name, email) IN (('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA'))
単純なGROUP BY句では、式のリストの書式として、次のいずれかを使用できます。
SELECT department_id, MIN(salary) min, MAX(salary) max FROM employees GROUP BY department_id, salary ORDER BY department_id, min, max; SELECT department_id, MIN(salary) min, MAX(salary) max FROM employees GROUP BY (department_id, salary) ORDER BY department_id, min, max;
ROLLUP、CUBE、およびGROUP BY句のGROUPING SETS句では、個々の式と同じリストにある式の集合を組み合せることができます。次に、1つのSQL文に含まれている有効なグルーピング・セットと式のリストを示します。
SELECT prod_category, prod_subcategory, country_id, cust_city, count(*) FROM products, sales, customers WHERE sales.prod_id = products.prod_id AND sales.cust_id=customers.cust_id AND sales.time_id = '01-oct-00' AND customers.cust_year_of_birth BETWEEN 1960 and 1970 GROUP BY GROUPING SETS ( (prod_category, prod_subcategory, country_id, cust_city), (prod_category, prod_subcategory, country_id), (prod_category, prod_subcategory), country_id ) ORDER BY prod_category, prod_subcategory, country_id, cust_city;
|
 Copyright © 1996, 2008, Oracle Corporation. All Rights Reserved. |
|