ヘッダーをスキップ

Oracle Database SQLリファレンス
10g リリース2(10.2)

B19201-02
目次
目次
索引
索引

戻る 次へ

6 式

この章では、値、演算子およびファンクションを式の中で組み合せて使用する方法について説明します。

この章では、次の内容を説明します。

SQL式

は、1つ以上の値、演算子、および値に評価されるSQLファンクションの組合せです。一般に、式のデータ型は、そのコンポーネントのデータ型になります。


注意:

NLS_COMPNLS_SORTの設定を組み合せた値によって、文字をソートおよび比較するルールが決まります。ご使用のデータベースのNLS_COMPLINGUISTICが設定されている場合、この章のエンティティはすべてNLS_SORTパラメータによって指定されるルールに従って解釈されます。NLS_COMPLINGUISTICに設定されていない場合、ファンクションはNLS_SORTの設定に関係なく解釈されます。NLS_SORTは、明示的に設定できます。明示的に設定されていない場合は、NLS_LANGUAGEから導出されます。これらの設定の詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。 


次の単純式は、値が4になり、データ型はNUMBER(コンポーネントと同じデータ型)になります。

2*2 

次の例は、ファンクションと演算子を使用した複雑な式です。この式は、現在の日付に7日を加算し、その合計から時間コンポーネントを削除し、結果をCHARデータ型に変換します。

TO_CHAR(TRUNC(SYSDATE+7)) 

次の場所で式を使用できます。

たとえば、次のUPDATE文のSET句で、引用符で囲まれた文字列'Smith'のかわりに式を使用することもできます。

SET last_name = 'Smith'; 

このSET句では、引用符で囲まれた文字列'Smith'のかわりに、INITCAP(last_name)を使用しています。

SET last_name = INITCAP(last_name);

次の構文に示すとおり、式にはいくつかの書式があります。

expr::=

画像の説明

Oracleデータベースは、すべてのSQL文のすべての部分で、式のすべての書式を受け入れるわけではありません。文に指定する式の制限については、第10章第19章の個々のSQL文の説明を参照してください。

このマニュアルの他の箇所で、条件、SQLファンクションまたはSQL文にexprが示されている場合は、必ず適切な式の表記法を使用してください。次の項では、いくつかの例をあげて、様々な式の書式を説明します。

単純式

単純式は、列、疑似列、定数、順序番号またはNULLを指定します。

simple_expression::=

画像の説明

スキーマは各ユーザー用の他に、"PUBLIC"(二重引用符が必要)にもなり得ます。その場合、スキーマは表、ビューまたはマテリアライズド・ビューのパブリック・シノニムを修飾する必要があります。"PUBLIC"でのパブリック・シノニムの修飾は、データ操作言語(DML)文でのみサポートされています。データ定義言語(DDL)文ではサポートされていません。

ROWIDは、表でのみ使用でき、ビューまたはマテリアライズド・ビューでは使用できません。NCHARおよびNVARCHAR2は、有効な疑似列データ型ではありません。

参照:

疑似列の詳細は、第3章「疑似列」を参照してください。query_nameの詳細は、「subquery_factoring_clause」を参照してください。 

有効な単純式の例を次に示します。

employees.last_name 
'this is a text string'
10 
N'this is an NCHAR string'

複合式

複合式は、その他の式の組合せを指定します。

compound_expression::=

画像の説明

組込みファンクションを式として使用できます(「ファンクション式」を参照してください)。ただし、複合式では、ファンクションの組合せによっては、適切でないものや拒否されるものもあります。たとえば、LENGTHファンクションは集計ファンクション内では使用できません。

PRIOR演算子は、階層問合せのCONNECT BY句で使用されます。

参照:

「演算子の優先順位」および「階層問合せ」を参照してください。 

有効な複合式の例を次に示します。

('CLARK' || 'SMITH') 
LENGTH('MOOSE') * 57 
SQRT(144) + 72 
my_fun(TO_CHAR(sysdate,'DD-MMM-YY'))

CASE式

CASE式を使用すると、プロシージャを起動せずに、SQL文でIF ... THEN ... ELSE論理を使用できます。構文は次のとおりです。


画像の説明

simple_case_expression::=

画像の説明

searched_case_expression::=

画像の説明

else_clause::=

画像の説明

単純CASE式では、Oracleデータベースは、exprcomparison_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データベースでは、短絡評価を使用します。つまり単純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値は、同じデータ型(CHARVARCHAR2NCHARNVARCHAR2NUMBERBINARY_FLOATまたはBINARY_DOUBLE)であるか、またはすべて数値データ型である必要があります。すべての式が数値データ型の場合、Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。

単純および検索CASE式では、すべてのreturn_exprは、同じデータ型(CHARVARCHAR2NCHARNVARCHAR2NUMBERBINARY_FLOATまたはBINARY_DOUBLE)であるか、またはすべて数値データ型である必要があります。すべての戻り式が数値データ型の場合、Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換して、そのデータ型を戻します。

1つのCASE式では、引数の最大数は255です。単純CASE式の初期式やオプションのELSE式を含むすべての式が、この上限の対象となります。WHEN ... THENの各組は、2つの引数としてカウントします。この上限を超えないように、CASE式をネストし、return_expr自体がCASE式となるようにすることができます。

参照:

 

単純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
   FROM customers;

CUST_LAST_NAME       CASECR
-------------------- ------
...
Bogart               Medium
Nolte                Medium
Loren                Medium
Gueney               Medium
検索CASE式の例

次の文は、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

CURSOR式

CURSOR式は、ネステッド・カーソルを戻します。この式の書式は、PL/SQLのREF CURSORと同じで、REF CURSOR引数としてファンクションに渡すことができます。


画像の説明

カーソル式が評価されるときに、ネステッド・カーソルは暗黙的にオープンされます。たとえば、カーソル式がSELECT構文のリストにある場合、問合せによってフェッチされた各行に対して、ネステッド・カーソルがオープンされます。ネステッド・カーソルは、次の場合にのみクローズされます。

CURSOR式の制限事項:

CURSOR式には、次の制限事項があります。

問合せのSELECT構文のリストでのCURSOR式の使用方法について、次に例を示します。

SELECT department_name, CURSOR(SELECT salary, commission_pct 
   FROM employees e
   WHERE e.department_id = d.department_id)
   FROM departments d;

ファンクションの引数としてのCURSORの使用方法について、次に例を示します。例では、まず、サンプル・スキーマOEREF CURSOR引数を受け入れるファンクションを作成します。

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;

LAST_NAME
-------------------------
De Haan
Mourgos
Cambrault
Zlotkey
Higgens

日時式

日時式は、日時データ型の値を戻します。

datetime_expression::=

画像の説明

datetime_value_expressionは、日時の値を戻す日時列または複合式です。表2-5で定義される規則に従って、日時および期間を組み合せることができます。日時の値を戻す3つの組合せは、日時式で有効です。

AT LOCALを指定すると、Oracleは現行のセッションのタイムゾーンを使用します。

AT TIME ZONEの設定は、次のように解析されます。

次の例は、タイムゾーンの日時の値を別のタイムゾーンに変換します。

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 

参照:

組込みファンクションの詳細は、「SQLファンクション」および「集計ファンクション」を参照してください。 

ユーザー定義ファンクション式は、次のものへのコールを指定します。

有効なユーザー定義ファンクション式の例を次に示します。

circle_area(radius)
payroll.tax_rate(empno)
hr.employees.comm_pct(dependents, empno)@remote
DBMS_LOB.getlength(column_name)
my_function(DISTINCT a_column)
ユーザー定義ファンクション式の制限事項:

オブジェクト型またはXMLTypeの引数をリモート・ファンクションおよびリモート・プロシージャに渡すことはできません。

期間式

期間式は、INTERVAL YEAR TO MONTHまたはINTERVAL DAY TO SECONDの値を戻します。

interval_expression::=

画像の説明

interval_value_expressionは、期間の値を戻すINTERVAL列または複合式の値です。表2-5で定義される規則に従って、日時および期間を組み合せることができます。期間の値を戻す6つの組合せは、期間式で有効です。

leading_field_precisionおよびfractional_second_precisionは、0〜9の任意の整数です。DAYまたはYEARのいずれかでleading_field_precisionを省略すると、Oracleデータベースはデフォルト値である2を使用します。秒でfractional_second_precisionを省略すると、データベースはデフォルト値である6を使用します。問合せで戻された値にデフォルトの精度を超える桁数が含まれる場合、Oracleデータベースはエラーを戻します。したがって、問合せで戻されるとわかっている値以上の精度を指定することをお薦めします。

たとえば、次の文は、システム・タイムスタンプ(日時の値)からサンプル表ordersorder_date列の値(別の日時の値)を減算して、期間値の式を戻します。一番古い注文が発注されたのが何日前かわからないため、DAYの先行フィールド精度に最大値である9を指定します。

SELECT (SYSTIMESTAMP - order_date) DAY(9) TO SECOND FROM orders
   WHERE order_id = 2458;

オブジェクト・アクセス式

オブジェクト・アクセス式は、属性の参照およびメソッドの起動を指定します。

object_access_expression::=

画像の説明

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;

スカラー副問合せ式

スカラー副問合せ式は、1つの行から1つの列値のみを戻す副問合せです。スカラー副問合せ式の値は、副問合せのSELECT構文リスト項目の値です。副問合せが0行を戻す場合、スカラー副問合せ式の値はNULLです。副問合せが2つ以上の行を戻す場合、Oracleはエラーを戻します。

スカラー副問合せ式は、式(expr)をコールするほとんどの構文で使用できます。ただし、次の場所では、スカラー副問合せは無効です。

モデル式

モデル式は、SELECT文のmodel_clauseでのみ、さらにモデル・ルールの右側でのみ使用されます。モデル式は、model_clauseで事前定義されたメジャー列のセルに値を戻します。詳細は、「model_clause」を参照してください。

model_expression::=

画像の説明

モデル式でメジャー列を指定する場合、指定する任意の条件と式は単一の値に変換される必要があります。

モデル式で集計ファンクションを指定する場合、ファンクションの引数はmodel_clauseで事前定義したメジャー列です。集計ファンクションは、モデル・ルールの右側でのみ使用できます。

モデル・ルールの右側で分析ファンクションを指定すると、model_clauseに複雑な計算を直接表記することができます。モデル式で分析ファンクションを使用するときには、次の制限事項が適用されます。

expr自体がモデル式である場合、ネストしたセル参照と呼ばれます。ネストしたセル参照には、次の制限事項が適用されます。

後述するモデル式は、次に示す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]

参照:

「CV」および「model_clause」を参照してください。 

型コンストラクタ式

型コンストラクタ式は、コンストラクタ・メソッドへのコールを指定します。型コンストラクタの引数は、任意の式です。型コンストラクタは、ファンクションが起動されるすべての場所で起動できます。

type_constructor_expression::=

画像の説明

NEWキーワードによって、コレクション型ではなくオブジェクトに、コンストラクタが適用されます。これによって、適切なコンストラクタを起動して新しいオブジェクトを作成するようにOracleに指示します。NEWキーワードはオプションですが、指定することをお薦めします。

type_nameオブジェクト型の場合、最初の引数の値の型がオブジェクト型の最初の属性と一致する値を取り、2番目の引数の値の型がオブジェクト型の2番目の属性と一致するというように、式は順序付けられたリストになっている必要があります。コンストラクタの引数の合計数は、オブジェクト型の属性の合計数と一致する必要があります。

type_nameVARRAY型またはネストした表型の場合、式のリストには0個以上の引数を含めることができます。引数が0個の場合は、空コレクションの構造であることを示します。それ以外の場合は、各引数が、型がコレクション型の要素型である要素値に対応します。

型コンストラクタの起動の制限事項

型コンストラクタ・メソッドの起動では、オブジェクト型に1000以上の属性がある場合でも、指定できるパラメータの数(expr)は最大で999です。この制限は、コンストラクタがSQLからコールされる場合にのみ適用されます。PL/SQLからコールされる場合には、PL/SQLの制限が適用されます。

参照:

コンストラクタ・メソッドの詳細は、『Oracle Databaseアプリケーション開発者ガイド-オブジェクト・リレーショナル機能』を参照してください。型コンストラクタへのコールに関するPL/SQLの制限の詳細は、『Oracle Database PL/SQLユーザーズ・ガイドおよびリファレンス』を参照してください。 

式の例

次の例では、サンプル・スキーマoecust_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; 
/
副問合せ例

次の例では、サンプル・スキーマoewarehouse_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)

変数式

変数式は、オプションの標識変数を持つホスト変数を指定します。この書式の式は、埋込みSQL文またはOracle Call Interface(OCI)プログラムで処理されるSQL文のみで指定できます。

variable_expression::=

画像の説明

有効な変数式の例を次に示します。

:employee_name INDICATOR :employee_name_indicator_var
:department_location 

式のリスト

式のリストは、その他の式の組合せです。

expression_list::=

画像の説明

式のリストは、比較条件、メンバーシップ条件、および問合せと副問合せの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')) 

参照:

「比較条件」および「IN条件」条件を参照してください。 

単純なGROUP BY句では、式のリストの書式として、次のいずれかを使用できます。

SELECT department_id, MIN(salary), MAX(salary) FROM employees
   GROUP BY department_id, salary;

SELECT department_id, MIN(salary), MAX(salary) FROM employees
   GROUP BY (department_id, salary);

ROLLUPCUBE、および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
  );

参照:

「SELECT」 


戻る 次へ
Oracle
Copyright © 2006 Oracle Corporation.

All Rights Reserved.
目次
目次
索引
索引