11g リリース1（11.1）
E05694-03
 目次 |
 索引 |
| Oracle Database 2日で開発者ガイド 11g リリース1（11.1） E05694-03 |
|
この章では、データベースを参照し、情報を引き出し、既存表の情報を変更し、トランザクション処理を制御する方法を示します。
この章の内容は次のとおりです。
表以外にも、Oracle Databaseには様々なオブジェクト型が用意されています。拡張性の高い管理オプションが多く含まれているオブジェクトもあり、それらには類似したプロパティが含まれます。たとえば、データベースの各オブジェクトは、1つのスキーマのみに所属しており、そのスキーマを使用した一意の名前が付けられています。このため、オブジェクトのネーミング規則および方法は、適切なスキーマを使用して新規のオブジェクトおよびオブジェクト型の識別を明確にすることをお薦めします。ここでユーザーが使用するオブジェクトは、すべて同一のhrスキーマに属しています。一般的に、アプリケーションは同一スキーマ内のオブジェクトを使用して動作します。
独自のオブジェクトを作成する場合は、オブジェクト名は30文字を超えることはできず、文字で始まる必要があります。
この項では、hrサンプルおよびその属性、またはデータベース・オブジェクトに関する理解をさらに深めることができます。Oracle SQL Developerで参照してこれらのオブジェクトを表示する方法について紹介します。
まず、各スキーマに含まれるオブジェクトのタイプを調べます。
hr_connの横にあるプラス記号をクリックします。
hrスキーマへの接続を認証します。「OK」をクリックします。 
hr_connの横にあるプラス記号をクリックし、hrスキーマ・データベース・オブジェクトのビューを拡張表示します。 表、ビュー、索引、パッケージ、プロシージャ、ファンクション、トリガー、タイプ、順序などの多くのオブジェクトがスキーマに含まれています。ここでは、使用する頻度の高いデータベース・オブジェクトの各タイプに関する定義を簡単に説明します。
この項では、データベース表のプロパティの検索方法および、表に含まれるデータの表示方法について紹介します。
Oracle Database表は、基本的なデータ・コンテナです。ユーザーがアクセスできるすべてのデータは、データベース・スキーマの表に含まれています。各表は個々のレコードである行、および各レコードの様々なフィールドを表す列を持つ2次元オブジェクトです。
hrスキーマ内の表のリストを拡張表示します。 表の拡張リストには、表countries、departments、employees、job_history、jobs、locationsおよびregionsが含まれます。
employees表をクリックします。「Oracle SQL Developer」ウィンドウの右側にある「Columns」タブに、この表のすべての列（EMPLOYEE_ID、FIRST_NAME、LAST_NAME、EMAIL、PHONE_NUMBER、HIRE_DATE、JOB_ID、SALARY、COMMISSION_PCT、MANAGER_IDおよびDEPARTMENT_ID）のリストが表示されます。表の各列には関連付けられたデータ型が含まれていて、それぞれ文字データ、整数、浮動小数点数、データまたは時間に関する情報として定義されます。列のすべてのプロパティを表示するには、水平スクロール・バーを右に動かします。
この表で使用されるすべての制約が表示されます。制約のタイプおよび制約の参照表や、制約が有効かどうか、その他のプロパティなどが含まれます。
hr）、名前、パーティション化情報などの表のその他の詳細がリストされています。
employeesの定義の前述の情報が要約されています。表には、列定義、索引などが含まれます。
「Oracle SQL Developer」ウィンドウの右側にある「Data」タブをクリックします。
この表のすべてのレコードのリストが表示されます。表の各列には文字データ、整数、浮動小数点、日付、または時間情報として定義する関連付けされたデータ型が含まれます。列のすべてのプロパティを表示するには、横軸のスクロール・バーを右へ動かします。
問合せとは、データを1つ以上の表またはビューから取得する操作のことです。最上位のSELECT文により問合せの結果が戻され、別のSQL文内にネストされた問合せは副問合せと呼ばれます。
この項では、問合せおよび副問合せのいくつかの型について紹介します。
次は、単純な問合せ形式の一例です。
SELECT select_list FROM source_list
この例では、select_listはデータが取得される列を指定し、source_listはこれらの列を含む表またはビューを指定しています。列数、各列のデータ型および長さは選択リストの要素で決定されます。選択リストはSQLファンクションを使用できることに注意してください。
表にあるすべての列を表示するには、select_listに対して*を使用します。
例2-1では、「Data」ウィンドウのemployees表の表示によって、以前に表示した情報を戻すためにSELECT文を使用します。
SELECT * FROM employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME ... ---------------------- -------------------- ------------------------- ... 100 Steven King ... 101 Neena Kochhar ... 102 Lex De Haan ... ... 107 rows selected
異なる問合せの実行間に、ツールバーの消しゴムのアイコンをクリックすると「SQL Worksheet」ペインおよび「Script Output」ペインの両方をクリアできます。
例2-2は、問合せで要求した列、FIRST_NAME、LAST_NAMEおよびHIRE_DATEのみを戻すためのSELECT文の使用方法を示しています。
SELECT first_name, last_name, hire_date FROM employees;
問合せの結果があります。
FIRST_NAME LAST_NAME HIRE_DATE ---------------------- ------------------- ------------------------- Steven King 17-JUN-87 Neena Kochhar 21-SEP-89 Lex De Haan 13-JAN-93 ... 107 rows selected
新規のヘッダーを含む列を表示するには、列の正しい名前の直後に別名を使用して、レポート内の列の名前を変更できます。この別名により、問合せの期間に対する項目名が効果的に変更されます。
例2-3では、SELECT文は問合せで要求した列を戻しますが、別名name1、name2およびhiredとして指定した列のヘッダーを含みます。
SELECT first_name name1, last_name name2, hire_date hired FROM employees;
問合せの結果は次のとおりです。
NAME1 NAME2 HIRED --------------------- -------------------- ------------------------- Steven King 17-JUN-87 Neena Kochhar 21-SEP-89 Lex De Haan 13-JAN-93 ... 107 rows selected
使用する別名に大文字、小文字、スペースまたは組合せが含まれる場合、二重引用符（"）を使用する必要があります。
例2-4では、SELECT文を使用して指定した別名の列のヘッダー、First、LastおよびDate Startedを含む列を戻します。
SELECT first_name "First", last_name "Last", hire_date "Date Started" FROM employees;
問合せの結果は次のとおりです。
First Last Date Started ---------------------- -------------------- ------------------------- Steven King 17-JUN-87 Neena Kochhar 21-SEP-89 Lex De Haan 13-JAN-93 ... 107 rows selected
SELECTおよびFROMキーワード以外に、その他の一般的な句も問合せで使用できます。WHERE句を比較演算子と一緒に使用すると、表内のすべての行を戻すかわりに取得する行を選択できます。
この表にWHERE句で使用される比較演算子をリストします。
WHERE句を使用すると単一条件をテストでき、AND句を使用すると複数のテストを結合できます。
例2-5は、department_idに90が含まれる単一の部門を制限する列の値を戻すWHERE句の使用方法を示しています。
SELECT first_name "First", last_name "Last" FROM employees WHERE department_id=90;
問合せの結果が表示されます。
First Last -------------------- ------------------------- Steven King Neena Kochhar Lex De Haan 3 rows selected
例2-6は、11,000と同一またはそれ以上の給与および割り当てられた（NULLではない）手数料率を一致させるために2つの異なる条件を制限する行を戻すWHERE ... AND句の使用方法を示しています。
SELECT first_name "First", last_name "Last", SALARY "Salary", COMMISSION_PCT "%" FROM employees WHERE salary >=11000 AND commission_pct IS NOT NULL;
問合せの結果が表示されます。
First Last Salary % -------------------- --------------------- -------------------- ----- John Russell 14000 0.4 Karen Partners 13500 0.3 Alberto Errazuriz 12000 0.3 ... 6 rows selected
例2-7では、WHERE句を使用して姓がMaで始まるMallin、Markle、Marlow、Marvins、MatosおよびMavrisの6行が戻されます。一致式%ma%（テキストmaが列のどこにある場合も可という意味）を使用した場合には、結果にはKumar、UrmanおよびVollmanの3行のみが含まれます。
SELECT first_name "First", last_name "Last" FROM employees WHERE last_name LIKE 'Ma%';
問合せの結果が表示されます。
First Last -------------------- ------------------------- Jason Mallin Steven Markle James Marlow ... 6 rows selected
例2-8は、100、110、120の値のリストとdepartment_idが一致する複数の異なる部門で勤務する従業員を検索するためのWHERE ... IN句の使用方法を示しています。結果は8行を含み、リストの最初の値と一致した4行、リストの2番目の値と一致した2行が含まれます。120と一致した値はありません。
SELECT first_name "First", last_name "Last", department_id "Department" FROM employees WHERE department_id IN (100, 110, 120);
問合せの結果が表示されます。
First Last Department -------------------- ------------------------- ---------------------- John Chen 100 Daniel Faviet 100 William Gietz 110 ... 8 rows selected
対応するdepartment_id値のわからない、特定の部門で働く従業員を検索する場合、employeesおよびdepartments表の両方を検索する必要があります。JOIN操作で、2つの表を結合して確認できます。
employees.employee_idのような完全修飾された列名はオプションです。ただし、同一の列名を持つ2つ以上の表を問合せに使用する場合、表に対してこれらの列を識別する必要があります。たとえば、employees.department_idおよびdepartments.department_idを同時に使用すると、従業員が働く部門の名前を決定できます。
完全修飾された列名の使用時には、表の名称departmentsにdなどの別名を使用した場合、問合せがさらに読みやすくなります。列departments.department_idはd.department_idに、employees.department_idはe.department_idになります。問合せのFROM句でこれらの表の別名を作成する必要があります。
例2-9では、この結果セットに2つの異なる表の列が含まれます。レポートの列名は一意であるため、表名で修飾する必要はありません。ただし、WHERE句では、2つの異なる表から同じ列名を使用したため、修飾が必要になります。
SELECT e.first_name "First", e.last_name "Last", d.department_name "Department" FROM employees e, departments d WHERE e.department_id = d.department_id;
問合せの結果が表示されます。
First Last Department -------------------- ------------------------- ------------------------------ Jennifer Whalen Administration Michael Hartstein Marketing Pat Fay Marketing ... 106 rows selected
正規表現を使用することにより、一般的な構文規則を使用して文字順序における複雑なパターンを検索できます。正規表現は、検索アルゴリズムを指定するメタ文字および文字を指定するリテラルを使用して検索パターンを定義します。
正規表現ファンクションには、REGEXP_INSTR、REGEXP_LIKE、REGEXP_REPLACEおよびREGEXP_SUBSTRが含まれます。
例2-10は、すべてのマネージャの検出方法を示しています。メタ文字|はOR条件を表し、マネージャの位置が部門に応じて%_MGRまたは%_MANのどちらか一方に指定されるために使用する必要があります。オプションiは大/小文字を区別しない一致を指定します。
SELECT first_name "First", last_name "Last", job_id "Job" FROM employees WHERE REGEXP_LIKE (job_id, '(_m[an|gr])', 'i');
問合せの結果が表示されます。
First Last Job -------------------- ------------------------- ---------- Nancy Greenberg FI_MGR Den Raphaely PU_MAN Matthew Weiss ST_MAN ... 14 rows selected
例2-11は、REGEXPR_LIKE式でlast_nameに二重母音（a、e、i、oまたはuのいずれか2つが隣り合って発生する場合）が含まれている行を選択する方法を示しています。REGEXP_LIKE条件の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT first_name "First", last_name "Last" FROM employees WHERE REGEXP_LIKE (last_name, '([aeiou])¥1', 'i');
問合せの結果が表示されます。
First Last -------------------- ------------------------- Harrison Bloom Lex De Haan Kevin Feeney ... 8 rows selected
データ・パターンを検出して別のデータ・パターンに置き換えるには、REGEXPR_REPLACEを使用します。例2-12では、電話番号の形式'nnn.nnn.nnnn'を'(nnn) nnn-nnnn'に置き換えます。数字はメタ文字 [:digit] と一致し、メタ文字{n}は発生数を表します。メタ文字'.'は通常、式のあらゆる文字を示します。そのため、メタ文字¥はエスケープ文字として使用され、次のパターンの文字はリテラルになります。この結果セットには新しい形式で電話番号が表示されます。REGEXP_REPLACEファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT first_name "First", last_name "Last", phone_number "Old Number", REGEXP_REPLACE(phone_number, '([[:digit:]]{3})¥.([[:digit:]]{3})¥.([[:digit:]]{4})', '(¥1) ¥2-¥3') "New Number" FROM employees WHERE department_id = 90;
問合せの結果が表示されます。
First Last Old Number New Number -------------------- ---------------------- -------------------- -------------- Steven King 515.123.4567 (515) 123-4567 Neena Kochhar 515.123.4568 (515) 123-4568 Lex De Haan 515.123.4569 (515) 123-4569 3 rows selected
例2-13は、REGEXPR_SUBSTRファンクションを使用してパターンと一致する最初の部分文字列を検索する方法を示します。メタ文字+はパターンの複数の発生を示しています。この結果セットは数字およびダッシュ記号をstreet_address列から抽出します。REGEXP_SUBSTR式の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT street_address, REGEXP_SUBSTR(street_address, '[[:digit:]-]+', 1, 1) "Street Numbers" FROM locations;
問合せの結果が表示されます。
STREET_ADDRESS Street Numbers ---------------------------------------- ---------------------- 1297 Via Cola di Rie 1297 93091 Calle della Testa 93091 2017 Shinjuku-ku 2017 ... 23 rows selected
REGEXPR_INSTRファンクションを使用すると、パターンと一致する最初の部分文字列の位置を検索できます。例2-14では、空白文字「 」を検索するためにREGEXPR_INSTRを使用します。メタ文字+'はパターンの複数の発生を示しています。この結果セットは各アドレスの最初の空白を示します。REGEXP_INSTR式の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT street_address, REGEXP_INSTR(street_address, '[ ]+', 1, 1) "Position" FROM locations;
問合せの結果が表示されます。
STREET_ADDRESS Position ---------------------------------------- ---------------------- 1297 Via Cola di Rie 5 93091 Calle della Testa 6 2017 Shinjuku-ku 5 ... 23 rows selected
ファンクションREGEXPR_COUNTは文字列で指定した文字パターンの繰返しの回数を決定します。例2-15では、REGEXPR_COUNTは表locationsのstreet_address列で発生した空白文字の回数を戻します。REGEXP_COUNT式の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT street_address, REGEXP_COUNT(street_address, ' ', 1) "Number of Spaces" FROM locations;
問合せの結果が表示されます。
STREET_ADDRESS Number of Spaces ---------------------------------------- ---------------------- 1297 Via Cola di Rie 4 93091 Calle della Testa 3 2017 Shinjuku-ku 1 ... 23 rows selected
この結果セットに、各住所の空白の数が表示されます。
SQLでは、ORDER BY句は、結果データのソートに使用される列の識別に使用します。ソート条件は結果セットに含まれる必要はなく、式、列名、計算操作、ユーザー定義ファンクションなどが含まれます。
例2-16は、昇順でlast_name順にソートされた結果セットを戻すORDER BY句を示しています。
SELECT first_name "First", last_name "Last", hire_date "Date Started" FROM employees ORDER BY last_name;
問合せの結果が表示されます。
First Last Date Started -------------------- ------------------------- ------------------------- Ellen Abel 11-MAY-96 Sundar Ande 24-MAR-00 Mozhe Atkinson 30-OCT-97 ... 107 rows selected
SQL算術演算子およびその他のビルトイン・ファンクションを使用することで、表内でソートされたデータを直接計算できます。
Oracle Database SQLでは、加算に使用するプラス記号（+）、減算に使用するマイナス記号（-）、乗算に使用するアスタリスク（*）、除算に使用するフォワードスラッシュ（/）などの基本的な算術演算子がサポートされています。これらは、評価順の標準的な算術ルールに従って評価されます。
例2-17では、歩合制に適格歩合による賃金の受取りに見合った従業員によって稼がれた給与が雇用日の順序で結果セットに表示されます。
SELECT first_name "First", last_name "Last", salary * 12 "Annual Compensation" FROM employees WHERE commission_pct IS NOT NULL ORDER BY hire_date;
問合せの結果が表示されます。
First Last Annual Compensation -------------------- ------------------------- ---------------------- Janette King 120000 Patrick Sully 114000 Ellen Abel 132000 ... 35 rows selected
Oracle Databaseには、数値操作のための数値ファンクションが数多く用意されています。たとえば、指定した小数点以下で切り下げるROUND、指定した小数点で切り捨てるTRUNCなどです。これらのすべてのファンクションにより、評価された各行に対する単一の値が戻されます。
例2-18は、セントの位までの概数にした日給の決定方法を示しています。
SELECT first_name "First", last_name "Last", ROUND(salary/30, 2) "Daily Compensation" FROM employees;
問合せの結果が表示されます。
First Last Daily Compensation -------------------- ------------------------- ---------------------- Steven King 800 Neena Kochhar 566.67 Lex De Haan 566.67 ... 107 rows selected
例2-19は、ドルの単位まで切り捨てた日給の決定方法を示しています。TRUNCファンクションは値を繰り上げしないことに注意してください。
SELECT first_name "First", last_name "Last", TRUNC(salary/30, 0) "Daily Compensation" FROM employees;
問合せの結果が表示されます。
First Last Daily Compensation -------------------- ------------------------- ---------------------- Steven King 800 Neena Kochhar 566 Lex De Haan 566 ... 107 rows selected
Oracle Databaseには、文字の値をカスタマイズするために、拡張性の高い文字ファンクションのリストが用意されています。
これらのファンクションは、文字式を大文字または小文字に変更し、空白を削除し、文字列を連結し、部分文字列を抽出または削除できます。
例2-20は、式の大/小文字の変更方法を示しています。結果セットにはUPPER、LOWERおよびINITCAPファンクションの結果が表示されます。
SELECT UPPER(first_name) "First upper", LOWER(last_name) "Last lower", INITCAP(email) "E-Mail" FROM employees;
問合せの結果が表示されます。
First upper Last lower E-Mail -------------------- ------------------------- ------------------------- STEVEN king Sking NEENA kochhar Nkochhar LEX de haan Ldehaan
レポートの同一の列で2つの個別の列または式から情報を生成するには、連結演算子||を使用して個別の結果を連結できます。例2-21では、4つの連結演算子が実行されていることにも注意してください。この結果セットには、first_name値の直後にlast_name値がリストされた列Nameに簡単な連結ファンクションが表示され、列Locationでネストした連結ファンクションは、cityおよびcountry_name値に区別されます。
SELECT e.first_name || ' ' || e.last_name "Name", l.city || ', ' || c.country_name "Location" FROM employees e, departments d, locations l, countries c WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND l.country_id=c.country_id ORDER BY last_name;
問合せの結果が表示されます。
Name Location ----------------------------- -------------------------------------------- Ellen Abel Oxford, United Kingdom Sundar Ande Oxford, United Kingdom Mozhe Atkinson South San Francisco, United States of America ... 106 rows selected
RTRIMおよびLTRIMファンクションを使用して、文字データの先頭または末尾から文字（デフォルトでは空白）を削除できます。TRIMファンクションは最初の文字およびそれに続く文字の両方を削除できます。例2-22では、タイプ変換ファンクションTO_CHARを使用します。この結果セットには、Mで始まらないlast_name値を持ち、job_id値の末尾にMANを含まず、date_hired値が0で始まらないすべての従業員が表示されます。
SELECT LTRIM(last_name, 'M') "Last Name", RTRIM(job_id, 'MAN') "Job", TO_CHAR(TRIM(LEADING 0 FROM hire_date)) "Hired" FROM employees WHERE department_id=50;
問合せの結果が表示されます。
Last Name Job Hired ------------------------- ---------- --------- Weiss ST_ 18-JUL-96 Fripp ST_ 10-APR-97 Kaufling ST_ 1-MAY-95 Vollman ST_ 10-OCT-97 ourgos ST_ 16-NOV-99 ... ikkilineni ST_CLERK 28-SEP-98 Landry ST_CLERK 14-JAN-99 arkle ST_CLERK 8-MAR-00 ... arlow ST_CLERK 16-FEB-97 ... allin ST_CLERK 14-JUN-96 ... Philtanker ST_CLERK 6-FEB-00 ... Patel ST_CLERK 6-APR-98 ... atos ST_CLERK 15-MAR-98 Vargas ST_CLERK 9-JUL-98 Taylor SH_CLERK 24-JAN-98 ... Geoni SH_CLERK 3-FEB-00 ... Cabrio SH_CLERK 7-FEB-99 ... Bell SH_CLERK 4-FEB-96 Everett SH_CLERK 3-MAR-97 cCain SH_CLERK 1-JUL-98 ... 45 rows selected
RPADを使用して文字を追加できます。デフォルトでは、文字データの末尾に空白が追加されます。LPADファンクションを使用すると、文字データの先頭に文字が追加されます。
例2-23では、この結果セットに、給与の相対値の単純なヒストグラムが表示されます。
SELECT first_name || ' ' || last_name "Name", RPAD(' ', salary/1000, '$') "Salary" FROM employees;
問合せの結果が表示されます。
Name Salary -------------------------------------- ---------------- Steven King $$$$$$$$$$$$$$$$$$$$$$$ Neena Kochhar $$$$$$$$$$$$$$$$ Lex De Haan $$$$$$$$$$$$$$$$ ... 107 rows selected
SUBSTRを使用して、開始文字位置および文字の総数により指定されたデータの部分文字列のみを抽出できます。
例2-24では、SUBSTRを使用してfirst_name値を頭文字に短縮し、phone_number値から地域コードを抜き出します。
SELECT SUBSTR(first_name, 1, 1) || '. ' || last_name "Name", SUBSTR(phone_number, 5, 8) "Phone" FROM employees;
問合せの結果が表示されます。
Name Phone ---------------------------- -------- S. King 123.4567 N. Kochhar 123.4568 L. De Haan 123.4569 ... 107 rows selected
この結果セットに、頭文字に短縮されたfirst_name値、および語頭の地域コード・コンポーネントを含まないphone_number値が表示されます。
文字データの関連する位置が判明している場合、REPLACEをSUBSTRと組み合せて使用して、特定の部分文字列を置換できます。
例2-25では、WHERE句のSUBSTRを使用して、ジョブ・コードを略称に置換します。
SELECT SUBSTR(first_name, 1, 1) || '. ' || last_name "Name", REPLACE(job_id, 'SH', 'SHIPPING') "Job" FROM employees WHERE SUBSTR(job_id, 1, 2) = 'SH';
問合せの結果が表示されます。
Name Job ----------------------- ------------------------ W. Taylor SHIPPING CLERK J. Fleaur SHIPPING_CLERK M. Sullivan SHIPPING_CLERK ... 20 rows selected
この結果セットに、頭文字に短縮されたfirst_name値、および置換されたjob_id値が表示されます。
Oracle Databaseには、間隔ファンクションなどのデータおよび日付データを操作および計算できるデータ・ファンクションが含まれています。
例2-26では、異なる役職に就いた従業員の特定の役職での雇用期間を決定します。従業員は一定期間に2つ以上の異なる役職に就いていた場合があるため、名称は一意ではないことに注意してください。MONTHS_BETWEENファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT e.first_name || ' ' || e.last_name "Name", TRUNC(MONTHS_BETWEEN(j.end_date, j.start_date)) "Months Worked" FROM employees e, job_history j WHERE e.employee_id = j.employee_id ORDER BY "Months Worked";
問合せの結果が表示されます。
Name Months Worked ---------------------------------------------- ------------- Jonathon Taylor 9 Payam Kaufling 11 Jonathon Taylor 11 ... 10 rows selected
この結果に、退社した従業員の雇用期間が最短で9か月、最長で69か月であると表示されます。
例2-27では、EXTRACTファンクションを使用して、従業員が、連続する雇用において暦年の年目であるかどうかを決定します。EXTRACTファンクションは、MONTHやDATEなどと組み合せても使用できます。
SYSDATEファンクションを使用するとシステム時計の現在の日付がわかります。SYSDATEファンクションの詳細は、『Oracle Database SQL言語リファレンス』 を参照してください。
SELECT first_name || ' ' || last_name "Name", (EXTRACT(YEAR from SYSDATE) - EXTRACT(YEAR FROM hire_date)) "Years Employed" FROM employees;
問合せの結果が表示されます。
Name Years Employed ---------------------------------------------- -------------- Steven King 20 Neena Kochhar 18 Lex De Haan 14 ... 107 rows selected
従業員'Steven King'の雇用年数（20年）が最も長いことが結果に表示されます。
例2-28では、last_dayファンクションを使用して、従業員が雇用された月の末日を決定します。
SELECT first_name || ' ' || last_name "Name", hire_date "Date Started", LAST_DAY(hire_date) "End of Month" FROM employees;
問合せの結果が表示されます。
Name Date Started End of Month ------------------------------ ------------------ ------------------------- Steven King 17-JUN-87 30-JUN-87 Neena Kochhar 21-SEP-89 30-SEP-89 Lex De Haan 13-JAN-93 31-JAN-93 ... 107 rows selected
各hire_date値に対する月の正確な末日が結果に表示されます。
例2-29では、ADD_MONTHSファンクションを使用して、従業員が雇用された日付に6か月を追加します。ADD_MONTHファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT first_name || ' ' || last_name "Name", hire_date "Date Started", ADD_MONTHS(hire_date, 6) "New Date" FROM employees;
問合せの結果が表示されます。
Name Date Started New Date ------------------------ ------------------------- ------------------------- Steven King 17-JUN-87 17-DEC-87 Neena Kochhar 21-SEP-89 21-MAR-90 Lex De Haan 13-JAN-93 13-JUL-93 ... 107 rows selected
例2-30では、SYSTIMESTAMPファンクションを使用して現在のシステム時間および日付を決定します。SYSTIMESTAMPはSYSDATEと類似していますが、タイムゾーンおよび秒数を含んだ日の情報の時間を含みます。SYSTIMESTAMPファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
hrスキーマ表のかわりに、既知の結果を保証するために参照できる、データ・ディクショナリの小表DUALを使用することに注意してください。DUAL表の詳細は、『Oracle Database概要』を、DUAL表からの選択の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) || ':' || EXTRACT(MINUTE FROM SYSTIMESTAMP) || ':' || ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP), 0) || ', ' || EXTRACT(MONTH FROM SYSTIMESTAMP) || '/' || EXTRACT(DAY FROM SYSTIMESTAMP) || '/' || EXTRACT(YEAR FROM SYSTIMESTAMP) "System Time and Date" FROM DUAL;
問合せの結果が表示されます。
System Time and Date ------------------------------------------------------------ 18:25:56, 4/5/2007
現在のSYSTIMESTAMP値によっては結果が変更する可能性もあります。
Oracle Databaseには、異なるデータ型間で変換できるデータ・ファンクションが含まれています。これは、同一列内にある異なるデータ型のデータを表示する必要がある場合に特に有効です。
一般的に3つの変換ファンクションがあります。文字に対してはTO_CHARファンクション、数値に対してはTO_NUMBERファンクション、日付に対してはTO_DATEファンクション、そしてタイムスタンプに対してはTO_TIMESTAMPファンクションです。
TO_CHARファンクションを使用して目的の書式にデータを変換します。例2-31では、HIRE_DATE値を'FMMonth DD YYYY'書式に変換します。FMオプションを使用すると月名から先頭および末尾の空白をすべて削除でき、その他のオプションでは、'DD-MON-YYYY AD'や'MM-DD-YYYY HH24:MI:SS'などを含めて使用できます。
SELECT first_name || ' ' || last_name "Name", TO_CHAR(hire_date, 'FMMonth DD YYYY') "Date Started" FROM employees;
問合せの結果が表示されます。
Name Date Started ---------------------------------------------- ----------------- Steven King June 17 1987 Neena Kochhar September 21 1989 Lex De Haan January 13 1993 ... 107 rows selected
新しい書式ですべてのhire_date値が結果セットにリストされます。
例2-32は、短い日付（DS）および長い日付（DL）という2つの標準書式タグを使用して、日付の書式を設定する方法を示しています。
SELECT first_name || ' ' || last_name "Name", TO_CHAR(hire_date, 'DS') "Short Date", TO_CHAR(hire_date, 'DL') "Long Date" FROM employees;
問合せの結果が表示されます。
Name Short Date Long Date --------------------------- ---------- ------------------------- Steven King 6/17/1987 Wednesday, June 17, 1987 Neera Kochhar 9/21/19889 Thursday, September 21, 1989 Lex De Haen 1/13/1993 Wednesday, January 13, 1993 ... 107 rows selected
TO_CHARファンクションを使用して目的の通貨書式に数字を変換できます。例2-33では、SALARY値を'$99,999.99'書式に変換します。TO_CHARの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT first_name || ' ' || last_name "Name", TO_CHAR(salary, '$99,999.99') "Salary" FROM employees;
問合せの結果が表示されます。
Name Salary ---------------------------------------------- ----------- Steven King $24,000.00 Neena Kochhar $17,000.00 Lex De Haan $17,000.00 ... 107 rows selected
例2-34は、TO_NUMBERファンクションを使用して、その後の計算に使用できるように文字を数字に変換する方法を示しています。TO_NUMBERの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT first_name || ' ' || last_name "Name", TO_NUMBER('300') + salary "Proposed Salary" FROM employees WHERE SUBSTR(job_id, 4, 5) = 'CLERK';
問合せの結果が表示されます。
Name Proposed Salary ---------------------------------------------- --------------- Alexander Khoo 3400 Shelli Baida 3200 Sigal Tobias 3100 ... 45 rows selected
選択した従業員のサブセットに対して提案されたすべてのsalary値が結果セットにリストされます。
TO_DATEファンクションを使用して、指定された書式モードを含む文字データを日付データに変換できます。例2-35で使用する書式モデルは、'Month dd, YYYY, HH:MI A.M. '、'DD-MON-RR'、'FF-Mon-YY HH24:MI:SI'などです。
SELECT TO_DATE('January 5, 2007, 8:43 A.M.', 'Month dd, YYYY, HH:MI A.M.') "Date" FROM DUAL;
問合せの結果が表示されます。
Date --------- 05-JAN-07
指定した書式文字列によって解釈され、文字データがDATE型に変換されます。
例2-36は、'DD-Mon-RR HH24:MI:SS.FF'などの書式モデルを含む'TO_TIMESTAMP'メソッドを使用する方法を示しています。TO_DATEの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT TO_TIMESTAMP('May 5, 2007, 8:43 A.M.', 'Month dd, YYYY, HH:MI A.M.') "Timestamp" FROM DUAL;
問合せの結果が表示されます。
Timestamp --------------------------------------------- 05-MAY-07 08.43.00.000000000 AM
指定した書式文字列によって解釈され、文字データがTIMESTAMP型に変換されます。
集計ファンクションは行のグループまたは表またはビュー全体で操作されます。本来、これらのファンクションは、セットに対する統計結果を提供し、平均（AVG）、集計（COUNT）、最大（MAX）、最小（MIN）、標準偏差（STDEV）、合計（SUM）などが含まれます。
GROUP BY句と組み合せて使用すると、集計ファンクションは特に有効です。この句を使用することで、問合せでは各グループに明確な結果を持つ1つ以上の列でグループ化されたリストが戻されます。
指定した条件に一致する集計値を含む行のみが問合せで戻されるよう指定するHAVING句も使用できます。
例2-37は、COUNTファンクションおよびGROUP BY句を使用して、マネージャにレポートする人数を決定する方法を示しています。レコード全体の数を示すためにワイルドカード*が使用されています。
SELECT manager_id "Manager", COUNT(*) "Number of Reports" FROM employees GROUP BY manager_id;
問合せの結果が表示されます。
Manager Number of Reports --------- ----------------- 1 100 14 123 8 ... 19 rows selected
結果として各マネージャにレポートする人数が表示されます。ここで、誰にもレポートしない人がいることに注意してください。データを調査すると、Steven Kingにはスーパーバイザがいないことがわかります。
例2-38は、COUNTファンクションをDISTINCTオプションと一緒に使用して、データ・セット内にある個別値の数を決定する方法を示しています。この例では、従業員を抱える部門の数を数えます。
SELECT COUNT(DISTINCT department_id) "Number of Departments" FROM employees;
問合せの結果が表示されます。
Number of Departments --------------------- 11
11部門に従業員が所属しているという結果が表示されます。departments表を確認すると、27部門がリストされているのがわかります。
MIN、MAX、MEDIAN、AVGなどの基本的な統計ファンクションを使用して、セット間の給与の範囲を決定できます。例2-39では、job_idでグループ化した給与を調査でき、同様の問合せを使用して、部門、事業所などの間での給与調査も可能です。
SELECT job_id "Job", COUNT(*) "#", MIN(salary) "Minimum", ROUND(AVG(salary), 0) "Average", MEDIAN(salary) "Median", MAX(salary) "Maximum", ROUND(STDDEV(salary)) "Std Dev" FROM employees GROUP BY job_id ORDER BY job_id;
問合せの結果が表示されます。
Job # Minimum Average Median Maximum Std Dev ---------- ---------- ---------- ---------- ---------- ---------- ---------- AC_ACCOUNT 1 8300 8300 8300 8300 0 AC_MGR 1 12000 12000 12000 12000 0 AD_ASST 1 4400 4400 4400 4400 0 AD_PRES 1 24000 24000 24000 24000 0 AD_VP 2 17000 17000 17000 17000 0 FI_ACCOUNT 5 6900 7920 7800 9000 766 FI_MGR 1 12000 12000 12000 12000 0 HR_REP 1 6500 6500 6500 6500 0 IT_PROG 5 4200 5760 4800 9000 1926 MK_MAN 1 13000 13000 13000 13000 0 MK_REP 1 6000 6000 6000 6000 0 ... 19 rows selected
結果として19の異なるジョブに対する統計が表示されます。
HAVING句を使用すると、結果セットを目的の値のみに限定できます。例2-40では、給与合計が年間で$1,000,000を超える部門に対する給与予算を表示します。
SELECT Department_id "Department", SUM(salary*12) "All Salaries" FROM employees HAVING SUM(salary * 12) >= 1000000 GROUP BY department_id;
問合せの結果が表示されます。
Department All Salaries ---------- ------------ 50 1876800 80 3654000
結果として給与予算が$1,000,000を超過する2部門が表示されます。
RANKファンクションを使用すると相対的な順序付けランクを決定でき、PERCENT_RANKファンクションを使用するとその百分率を決定できます。例2-41では、job_idにCLERK指定のあるすべての従業員のサブセット内で給与$3,000に対するこれらの値を決定できます。
WITHIN GROUPファンクションを使用するとグループを調査することもできます。
SELECT RANK(3000) WITHIN GROUP (ORDER BY salary DESC) "Rank", ROUND(100 * (PERCENT_RANK(3000) WITHIN GROUP (ORDER BY salary DESC)), 0) "Percentile" FROM employees WHERE job_id LIKE '%CLERK';
問合せの結果が表示されます。
Rank Percentile ---------- ---------- 20 42
CLERK指定のあるすべての従業員の間で、給与$3,000は20番目に高く、百分率では42%であることが結果に表示されます。
DENSE_RANKファンクションは、RANKファンクションとほぼ同様の働きをしますが、同じ値が同一ランクを受け取り、ランキングに差分が発生しません。例2-42では、job_idにCLERK指定のあるすべての従業員のサブセット内で$3,000のDENSE_RANKを決定します。
SELECT DENSE_RANK(3000) WITHIN GROUP (ORDER BY salary DESC) "Rank" FROM employees WHERE job_id LIKE '%CLERK';
問合せの結果が表示されます。
Rank ---------- 12
DESNE_RANKファンクションを使用した結果、給与$3,000が12番目であることが表示されます。RANKファンクションを使用した前の例で取得した20番目のランクと対比します。
NULL値を処理できるように、Oracle Databaseには2つのファンクションが用意されています。NULLがあった場合、NVLファンクションは指定した値に置き換え、NVL2ファンクションは考えられる2つの評価可能な式（コンポーネント変数のいずれもNULLでない場合と、少なくとも1つの変数がNULLである場合）を指定します。
例2-43では、NVLおよびNVL2ファンクションを使用して、$300,000の売上に関係している従業員の場合の各従業員に対する全体の年間報酬を決定します。コミッション率は売上量の乗算であり、基本給与の乗算ではないことに注意してください。また、WHERE句はマネージャに対して結果セットを制限することに注意してください。
SELECT first_name || ' ' || last_name "Name", NVL((commission_pct * 100), 0) "Comm Rate", NVL2(commission_pct, ROUND(salary * 12 + commission_pct * 300000, 2), salary * 12) "With $300K Sales" FROM employees WHERE job_id LIKE '%_M%' AND department_id = 80;
問合せの結果が表示されます。
Name Comm Rate With $300K Sales ---------------------------------- ---------------------- ----------------- John Russell 40 288000 Karen Partners 30 252000 Alberto Errazuriz 30 234000 Gerald Cambrault 30 222000 Eleni Zlotkey 20 186000 5 rows selected
Comm Rate列で、NVLファンクションによりNULL値が0に置き換えられます。With $300K Sales列には、NVL2ファンクションにより、COMMISSION_PCT値の値によって2つの異なる式から値が生成されます。
Oracle Databaseには、複数の条件値に基づいた値を戻すことができる2つのファンクションが用意されています。
CASEファンクションは、値、式または検索条件を対比し、一致を検出した場合に結果を戻すという点で、ネストされたIF ... THEN ... ELSE文と同等です。
例2-44では、CASEファンクションを使用して、勤続年数に応じて支払われる予想給与増加を表示します。
SELECT first_name || ' ' || last_name "Name", hire_date "Date Started", salary "Current Pay", CASE WHEN hire_date < TO_DATE('01-Jan-90') THEN TRUNC(salary*1.15, 0) WHEN hire_date < TO_DATE('01-Jan-95') THEN TRUNC(salary*1.10, 0) WHEN hire_date < TO_DATE('01-Jan-00') THEN TRUNC(salary*1.05, 0) ELSE salary END "Proposed Salary" FROM employees;
問合せの結果が表示されます。
Name Date Started Current Pay Proposed Salary -------------------------- ---------------- --------------- ------------------- Steven King 17-JUN-87 24000 27600 Neena Kochhar 21-SEP-89 17000 19550 Lex De Haen 13-JAN-93 17000 18700 ... 107 rows selected
Proposed Salary列の値が、Date Started値に基づいて調整されていることが結果に表示されます。
DECODEファンクションは、値または式を検索値と対比し、一致を検出した場合に結果を戻します。一致が検出されない場合、DECODEファンクションはデフォルト値、またはデフォルト値が指定されていない場合はNULLに戻します。
例2-45では、DECODEファンクションを使用してjob_id値に基づいて考えられる給与増加を割り当てます。
SELECT first_name || ' ' || last_name "Name", job_id "Job", salary "Current Pay", DECODE(job_id, 'PU_CLERK', salary * 1.10, 'SH_CLERK', salary * 1.15, 'ST_CLERK', salary * 1.20, salary) "Proposed Salary" FROM employees;
問合せの結果が表示されます。
Name Job Current Pay Proposed Salary -------------------------- ---------------- --------------- ------------------- ... Alexander Khoo PU-CLERK 3100 3410 ... Julia Nayer ST_CLERK 3200 3840 ... Winston Taylor SH_CLERK 3200 3680 ... 107 rows selected
Proposed Salary列の値が、job_id値に基づいて調整されていることが結果に表示されます。
データベースにおける追加、変更および削除操作は、一般的にデータ操作言語（DML）文と呼ばれます。
これらの文は表内のデータを変更するため、トランザクション管理を使用してすべての依存するDML文をグループ化することをお薦めします。
INSERT文を使用して表にデータの行を追加する場合、挿入されたデータは表の各列のデータ型およびサイズに対して有効である必要があります。
INSERTコマンドの一般構文は次のとおりです。値リストは表の列と同じ順序である必要があることに注意してください。
INSERT INTO table_name VALUES (list_of_values_for_new_row);
例2-46では、INSERTファンクションを使用してemployees表に新規の行を追加します。
INSERT INTO employees VALUES (10, 'George', 'Gordon', 'GGORDON', '650.506.2222', '01-JAN-07', 'SA_REP', 9000, .1, 148, 80);
問合せの結果が表示されます。
1 row created.
新規の行がemployees表に正常に追加されたことが結果に表示されます。
例2-47は、新しいレコードがデータベースに追加される時点ですべての情報が使用不可の場合、特定の既知の表の列に値を挿入し、残っている列をNULLに設定する方法を示しています。
NULLに設定された列がNOT NULL制約に指定される場合、エラーが生成されます。
INSERT INTO employees VALUES (20, 'John', 'Keats', 'JKEATS', '650.506.3333', '01-JAN-07', 'SA_REP', NULL, .1, 148, 80);
問合せの結果が表示されます。
1 row created.
新規の行がemployees表に正常に追加されたことが結果に表示されます。
UPDATE文を使用して表の行を更新する場合、新規のデータは表の各列のデータ型およびサイズに対して有効である必要があります。
UPDATEコマンドの一般構文は次のとおりです。変更された列は識別される必要があり、一致条件に一致している必要があります。
UPDATE table_name SET column_name = value; WHERE condition;
データが欠落している行の情報を更新するには、欠落しているデータの列を指定する必要があります。例2-48では以前挿入したレコードに対してsalary列を更新します。
UPDATE employees SET salary = 8500 WHERE last_name = 'Keats';
問合せの結果が表示されます。
1 row updated.
結果として一致行が更新されたことが表示されます。
例2-49は、UPDATE文を使用して複数行を更新する方法を示しています。
UPDATE employees SET commission_pct=commission_pct + 0.05 WHERE department_id = 80;
問合せの結果が表示されます。
36 rows updated.
結果として指定した行が更新されたことが表示されます。
DELETE文を使用して、表の特定の行を削除できます。表のすべての行を削除する場合は、空の表を使用します。データベースから表全体を削除する場合は、DROP TABLE文を使用します。
行を誤って削除した場合は、ROLLBACK文を使用して行をリストアできます。
例2-50は、DELETE文を使用して、以前追加したデータを削除する方法を示しています。
WHERE句を使用しないとすべての行が削除されるので注意してください。
DELETE FROM employees WHERE hire_date = '1-Jan-2007';
問合せの結果が表示されます。
2 rows deleted.
結果として指定した行が削除されたことが表示されます。
ビジネス・プロセスをモデル化する多くのアプリケーションでは、複数の異なる操作が同時に実行される必要があります。たとえば、マネージャが退職する場合、job_history表に行が挿入され、退社日が表示され、このマネージャにレポートする全従業員はemployees表内で再度割り当てられる必要があります。この一連の操作は、単一の単位またはトランザクションとして扱われる必要があります。
次のようなトランザクションを制御する文は、DML文による変更を管理し、トランザクションにグループ化します。
COMMIT文は現在のトランザクションを終了し、トランザクション内のすべての変更を永続的にします。COMMIT文は、トランザクション内のすべてのセーブポイントを消去し、トランザクションのロックを解除します。
ROLLBACK文は、現在のトランザクションで実行された作業を戻します。つまり、最後のCOMMITまたはROLLBACKからのすべてのデータ変更が破棄されます。データの状態は、要求された変更前の状態にロールバックされます。
SAVEPOINT文は、後でロールバックする際のトランザクションのポイントを識別します。
COMMITまたはROLLBACK文のいずれかを使用してトランザクションを明示的に終了することをお薦めします。明示的にトランザクションをコミットせずプログラムが異常終了した場合、Oracle Databaseはコミットされていないトランザクションを自動的にロールバックします。
明示的なCOMMIT文はトランザクションを終了し、データベース内のすべての変更を永続的にします。トランザクションをコミットするまでは、データベースに対するすべての変更を表示できますが、これらの変更は完了していないか、またはデータベース・インスタンスの他のユーザーに対して参照可能ではありません。トランザクションをコミットすると、すべての変更は他のユーザーに対して参照可能になり、他のユーザーの文はトランザクション終了後に実行されます。
明示的なCOMMITまたはROLLBACK文より前の変更は取り消すことが可能です。
例2-51は、regions表に新規の行を追加した後に、COMMIT文を使用する方法を示しています。
INSERT INTO regions VALUES (5, 'Africa'); COMMIT;
問合せの結果およびCOMMIT文が表示されます。
Commit complete.
手動でregions表の内容を確認する際に、新規の行が含まれていることを確認します。
ROLLBACK文は、最後のCOMMIT文以降のトランザクションのすべてをロールバックします。前のCOMMIT文がプログラムに残っていない場合は、すべての操作がロールバックされます。
例2-52および例2-53は、ROLLBACK文を使用してregions表への変更を取り消す方法を示しています。
UPDATE regions SET region_name = 'Just Middle East' WHERE region_name = 'Middle East and Africa';
問合せの結果が表示されます。
1 row updated.
手動でregions表の内容を確認します。
region_name値が更新されていることを確認します。
ROLLBACK;
regions表の内容を「Refresh」アイコンをクリックして手動でチェックします。region_name値が元の値に戻っていることを確認します。
SAVEPOINT文を使用して後でロールバックできるトランザクションのポイントを識別できます。アプリケーションに必要なだけセーブポイントを使用できるため、アプリケーションにより多くのトランザクション制御を実装できます。
例2-54では、regions表に新規の行を追加した後に、ROLLBACK文を使用します。
UPDATE regions SET region_name = 'Middle East' WHERE region_name = 'Middle East and Africa'; SAVEPOINT reg_rename; UPDATE countries SET region_id = 5 WHERE country_id = 'ZM'; SAVEPOINT zambia; UPDATE countries SET region_id = 5 WHERE country_id = 'NG'; SAVEPOINT nigeria; UPDATE countries SET region_id = 5 WHERE country_id = 'ZW'; SAVEPOINT zimbabwe; UPDATE countries SET region_id = 5 WHERE country_id = 'EG'; SAVEPOINT egypt; ROLLBACK TO SAVEPOINT nigeria; COMMIT;
各UPDATEおよびSAVEPOINT文の結果は次のとおりです。
1 row updated. Savepoint created.
regions表の内容を手動でチェックします。「Refresh」アイコンをクリックする必要がある場合があります。更新されたregion_name値を確認します。
次に、countries表の内容を手動でチェックします。「Refresh」アイコンをクリックする必要がある場合があります。ZambiaおよびNigeriaの更新されたregion_name値を含んでいること、ZimbabweおよびEgyptの値は更新されていないことを確認します。
データの変更がセーブポイントnigeriaに対するROLLBACK文によって戻されているかどうか確認できます。
|
 Copyright © 2005, 2008, Oracle Corporation. All Rights Reserved. |
|