Sql-certificate-creating-other-schema-objects

他のスキーマオブジェクトの作成

テーブルとは別に、他の重要なスキーマオブジェクトはビュー、シーケンス、インデックス、シノニムです。ビューは論理テーブルまたは仮想テーブルです。 シノニムは、単にデータベースオブジェクトのエイリアス名です。シノニムは、クエリの記述を簡素化し、データベースオブジェクトの実際の名前を隠すことでシステムセキュリティの要素を提供します。シーケンスは、整数値の自動生成をサポートする特別なデータベースオブジェクトであり、テーブルの主キー値を生成します。テーブルから情報を迅速に取得できるように、テーブルの列にインデックスが作成されます。

ビュー

データベースビューは、クエリに基づく論理テーブルまたは仮想テーブルです。ビューはテーブルと同様にクエリされます。つまり、開発者としての観点から、またはデータベースシステムユーザーの観点から、ビューはテーブルのように見えます。オブジェクトがデータベースのデータディクショナリ内に格納されるため。ただし、ビューにはデータ自体は格納されません。データベースには、ビューを作成するための実行プランも格納されます。これは、ビューのSELECTクエリによって提示される実際のデータが格納されなくても、ビューを使用してデータを迅速に取得できることを意味しますビューの一部として。むしろ、ビューが定義されているデータベーステーブルからビューがクエリされるたびにデータが「収集」されます。これらはベーステーブルと呼ばれます。

一般的な構文は次のとおりです。

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [ViewName]
[(Column Alias Name...)]
AS [Query]
[WITH [CHECK OPTION] [READ ONLY] [CONSTRAINT]];

構文から、

FORCEオプションを使用すると、ビューが参照するベーステーブルが存在しない場合でも、ビューを作成できます。このオプションを使用して、ベーステーブルと付随データを実際に作成する前にビューを作成します。

NOFORCEオプションはFORCEの反対であり、ビューを作成するために必要な特権があり、ビューの作成元のテーブルが既に存在する場合、システムユーザーはビューを作成できます。 これはデフォルトのオプションです。

WITH READ ONLYオプションを使用すると、読み取り専用のビューを作成できます。DELETE、INSERT、またはUPDATEコマンドを使用して、読み取り専用ビューのデータを変更することはできません。

WITH CHECK OPTION句を使用すると、ビューで選択できる行を更新できます。また、値の制約を指定することもできます。CONSTRAINT句は、WITH CHECK OPTION句と連動して、データベース管理者が一意の名前を割り当てることができるようにします。データベース管理者がCONSTRAINT句を省略した場合、Oracleはシステム生成の名前にあまり意味のない名前を自動的に割り当てます。

ビューの種類

シンプルビューは1つのテーブルの上にのみ作成されます。これは、関数またはグループ句のない単純なSELECTクエリですが、変換なしでテーブルから列を選択するだけです。ビューでDMLを実行すると、すぐに反映されます。ベーステーブルに。

複合ビューは、結合を使用して複数のテーブルに作成されます。SQL関数、関数ごとのグループを含めることができます。ただし、ビューは複数のデータにあり、列の選択も単純ではないため、DML操作は許可されません。

図

• シンプルビュー：*以下のシンプルビューでは、DEVとしてジョブIDを持つ従業員の従業員名、部門ID、および給与を選択します。

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary
FROM employees
WHERE job_id = 'DEV';

• 複雑なビュー：*以下の例は、部門名、部門での平均給与、およびそこで働いている従業員の数を示しています。

CREATE OR REPLACE VIEW EMP_VU
AS
SELECT department_name, AVG (salary) avg_sal, COUNT (first_name) count
FROM employees E, departments D
WHERE E.department_id = D.department_id
GROUP BY department_name;

*DESCRIBE [ビュー名]* はビュー構造を説明します。 列は、ビュー定義と同じ順序でリストされます。

ビューでのDML操作

DML操作は、単純なビューで簡単に実行できます。前述のように、挿入、更新、削除の操作は実際にはベーステーブルで行われます。

ビューでUPDATE、DELETE、またはINSERT DMLステートメントを実行すると、実際にはビューが定義されている1つまたは複数のベーステーブルのデータ行を操作します。UPDATE、DELETE、およびINSERTステートメントの使用には制限があります。ビューでUPDATE、DELETE、またはINSERTステートメントを使用するには、ビューが更新可能である必要があります。SELECT句でSELECTリストの集計関数が指定されていない場合、ビューは更新可能である必要があります。 GROUP BY、DISTINCT、またはUNION句を使用して作成されている。FROM句のSELECTサブクエリで集約関数を使用できます。 また、ビューのSELECTリストに派生列を含めることはできません。 次に、JOIN操作（結合ビュー）の結果としてビューが作成された場合、UPDATEおよびINSERTステートメントは一度に1つのベーステーブルの行のみを変更または挿入できます。 1つのデータ操作言語（DML）ステートメントで2つ以上のテーブルの行を変更することはできません。最後に、DELETEステートメントは、テーブルがFROM句で参照されている場合にのみビューに対して実行できます。 これは単に、指定されていないテーブルから行を削除できないことを意味します。

WITH CHECK OPTION句

WITH CHECK OPTIONは、ビューを介してデータを挿入または更新するときに実行するチェックのレベルを指定するオプションの句です。WITHCHECK OPTION句を使用してビューを作成すると、ビューを介してベーステーブルに挿入または更新されるすべての行ビューの定義に準拠する必要があります。 ビューが読み取り専用として作成されている場合、オプションを指定できないことに注意してください。

たとえば、開発者である従業員（JOB_ID = DEV）に対してビューV_EMP_DEVが作成されます。

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary,
FROM employees
WHERE job_id = 'DEV'
WITH CHECK OPTION empvu_dev;

ユーザーがビューを介してHR従業員の給与を更新しようとしましたが、例外が発生しました。 ビューがWITH CHECK OPTIONで作成されたためです。

UPDATE v_emp_dev
SET salary = salary+500
WHERE JOB_ID = 'HR';
ORA-01402: view WITH CHECK OPTION where-clause violation

単純なビューであれば、UPDATEステートメントは例外を発生させませんでした。

ビューをドロップする

データベース管理者（DBA）またはビュー所有者は、DROP VIEWステートメントを使用してビューをドロップできます。ビューに制約が定義されている場合、ビューをドロップするときにCASCADE CONSTRAINTS句を指定する必要があります。そうでない場合、DROP VIEWステートメントは処理に失敗します。別のビューまたはシノニムビューやマテリアライズドビューなどの他のデータベースオブジェクト（これらのオブジェクトについては、この章で後述します）がドロップされたビューを参照する場合、Oracleはこれらのデータベースオブジェクトをドロップしません。むしろ、Oracleはそれらを無効としてマークします。これらの無効なオブジェクトを削除するか、再定義して再度有効にすることができます。

以下のDROP VIEWコマンドは、データベースからビューEMP_VUを削除します。

DROP VIEW EMP_VU;

シーケンス

Oracleは、このタイプの使用のために一意の番号のシーケンスを生成する機能を提供します。これらはシーケンスと呼ばれます。一般に、シーケンスはデータベース表の主キー値として使用される一意の連続整数値を生成するために使用されます。シーケンスによって一度生成された数値はロールバックできないことに注意してください。

構文

CREATE SEQUENCE <sequence name>
[INCREMENT BY < number >]
[START WITH < start value number>]
[MAXVALUE < MAXIMUM VLAUE NUMBER>]
[NOMAXVALUE]
[MINVALUE < minimum value number>]
[CYCLE | NOCYCLE]
[CACHE < number of sequence value to cache> | NOCACHE]
[ORDER | NOORDER];

構文から、

CREATE SEQUENCEステートメントでは、一意のシーケンス名を指定する必要があります。 これは、ステートメントで必要な唯一の句です。 他の句を指定しない場合、生成されるすべての順序番号はOracleのデフォルト設定に従います。

INCREMENT BY句は、各番号が生成されるときにシーケンスがどのように増加するかを決定します。 デフォルトの増分は1です。ただし、シーケンスが番号をスキップする正当な理由がある場合は、異なる増分を指定できます。正の数値増分は、選択した間隔に等しい間隔を持つ昇順のシーケンス番号を生成します。負の数値増分は、降順のシーケンス番号を生成します。

START WITH句は、シーケンスの開始数値を指定します。デフォルトの開始番号は1です。さらに、現在シーケンス値を格納する列にデータがある行がある場合は、開始値を指定する必要があります。

MAXVALUE句は、シーケンスをインクリメントできる最大値を指定します。 MAXVALUEがない場合、シーケンスに対して生成できる最大許容値は非常に大きく、10の27乗-1です。 デフォルトはNOMAXVALUEです。

MINVALUE句は、減少シーケンス（降順で数値を生成するシーケンス）のシーケンスの最小値を指定します。 デフォルトはNOMINVALUEです。

CYCLE句は、シーケンスが指定されたMAXVALUEに達した場合にシーケンス値を再利用できることを指定します。 シーケンスが循環する場合、START WITH値から再び番号が生成されます。

CACHE句は、Oracleがキャッシュメモリに格納されるシーケンス番号の指定されたバッチを生成できるようにすることで、システムのパフォーマンスを向上させることができます。

番号を指定せずにCACHEを指定した場合、デフォルトのキャッシュサイズは20のシーケンス番号です。オプションで、NOCACHEを指定して、シーケンス番号のキャッシュを防ぐことができます。

ORDER句は、シーケンス番号が要求された正確な時系列順に割り当てられることを指定します。

NEXTVALおよびCURRVAL

シーケンス値は、currvalとnextvalという名前の2つの疑似列を使用して生成されます。疑似列はテーブル列のように動作しますが、疑似列は実際にはテーブルに格納されません。nextval疑似列を初めて選択すると、 nextval疑似列の後続の選択により、INCREMENT BY句で指定されたとおりにシーケンスがインクリメントされ、新しく生成されたシーケンス値が返されます。currval疑似列は、シーケンスの現在の値を返します。 nextvalへの最後の参照。

セッションでは、CURRVALではなくNEXTVALがシーケンスの最初のアクションである必要があります。 これは、セッションでは、NEXTVALがシーケンスからセッションの最初の番号を生成するときに、OracleがCURRVALに現在の値を保持するためです。

構文：

Sequence.NEXTVAL
Sequence.CURRVAL

注意点-

• CURRVALおよびNEXTVALは、selectステートメントの外部SQLでのみ使用できます。
• CURRVALおよびNEXTVALは、INSERTステートメントで使用して列の主キーを置き換えることができます。サブクエリ句としても、VALUES句でも使用できます。
• CURRVALおよびNEXTVALを使用して、テーブルの値を更新できます。 *CURRVALおよびNEXTVALは、CREATE TABLEまたはALTER TABLEステートメントのGROUP BY、HAVING、またはORDER BY節、およびDEFAULT式を使用して、DISTINCTキーワードを使用してVIEW選択リストに含めることはできません。

シーケンスの変更

シーケンスの所有者は、シーケンスを変更して、INCREMENT BY value、MINVALUE、MAXVALUE、CYCLE、またはCACHE句などの属性のみを変更できます。 行われた変更は、今後の数値に反映されることに注意してください。

構文：

ALTER SEQUENCE [sequence name]
INCREMENT BY n
MAXVALUE n
NOCACHE
NOCYCLE

シーケンスのドロップ

DROP SEQUENCEコマンドは、再作成が必要なシーケンスまたは不要になったシーケンスをドロップします。

DROP SEQUENCE [sequence name]

索引

インデックスは、SELECTクエリのパフォーマンスをチューニングするために使用されるデータベースオブジェクトです。主キー制約、一意のインデックス、非一意のインデックス、および連結インデックスなどを実施するために使用されるインデックスなど、さまざまなタイプのインデックスがあります。クエリでは、Oracleは結果テーブルに必要な行を返すためにテーブル内のすべての行をスキャンする必要があります。インデックスはテーブルの列に作成され、インデックスセグメントの下の列のすべての値を格納します。特定：テーブルが削除されると、自動的に削除されます。

インデックスは自動または手動で作成できます。PRIMARYKEY制約またはUNIQUE制約を指定すると、Oracleは自動的に一意のインデックスを作成して、指定されたテーブルの高速データ取得をサポートします。

または、ユーザーは手動でインデックスを作成してクエリのパフォーマンスを最適化できます。手動で作成されたインデックスは一意または非一意にできます。非一意のインデックスはBツリー、ビットマップ、または関数ベースのインデックスです。デフォルトでは、Oracleは列にBツリーインデックスを作成します。ここに構文があります

構文

CREATE [UNIQUE][BITMAP]INDEX index
ON table (column [, column]...);

UNIQUEおよびBITMAPは、一意およびビットマップ索引に対してのみ指定する必要があることに注意してください。デフォルトでは、Oracleは通常の索引用にBツリー索引を作成します。

複合インデックス（連結インデックスとも呼ばれます）は、テーブルの複数の列に作成されるインデックスです。 複合インデックスの列は任意の順序で表示でき、テーブル内の隣接する列である必要はありません。 複合インデックスは、WHERE句が複合インデックス内の列のすべてまたは先頭部分を参照するクエリの行取得速度を向上させます。 インデックスには最大32列を含めることができます。

たとえば、ユーザーはEMPLOYEESテーブルのHIRE_DATE列にインデックスIDX_EMPを作成します。インデックスの使用により、インデックス付きパススキャンを走査することでディスクI/Oが削減され、HIRE_DATE列でフィルタリングされたデータが見つかります。

CREATE INDEX IDX_EMP ON employees(hire_date);

インデックスの削除

インデックスは変更できませんが、分析、再構築、または統計計算のために変更できます。インデックス定義を変更する必要がある場合は、削除して再作成する必要があります。DROPINDEXコマンドの構文は単純です。

DROP INDEX index_name;

同義語

シノニムはエイリアス、つまり、データベースオブジェクトを参照するタスクを簡素化するために使用される短縮形の形式です。概念は、友人や知人のニックネームの使用に似ています。別のユーザーが所有するオブジェクトを参照するには、スキーマ名が必要です。接頭辞が付いています。 シノニムを使用すると、スキーマ名とともにオブジェクトを参照する労力を削減できます。このように、シノニム名は実際のオブジェクト名とその所有者を隠すため、シノニムは場所の透過性を提供します。

シノニムにはパブリックとプライベートの2つのカテゴリがあります。パブリックシノニムを使用すると、すべてのシステムユーザーがオブジェクトに簡単にアクセスできます。 実際、パブリックシノニムを作成する個人はシノニムではなく、Oracle内に存在するPUBLICユーザーグループに属します。一方、プライベートシノニムは、そのシノニムを作成して常駐するシステムユーザーに属します。ユーザーのスキーマ。

構文

CREATE [PUBLIC] SYNONYM [synonym name]
FOR OBJECT;

システムユーザーは、所有するプライベートシノニムを使用する特権を他のシステムユーザーに付与できます。シノニムを作成するには、CREATE SYNONYM特権が必要です。さらに、パブリックシノニムを作成するには、CREATE PUBLIC SYNONYM特権が必要です。シノニムがパブリックとして宣言されている場合、シノニム名をパブリックシノニムとして使用することはできません。すでに存在するパブリックシノニムを作成しようとすると、CREATE PUBLIC SYNONYMコマンドが失敗し、OracleはORA-00955を返します。 nameは既存のオブジェクトエラーメッセージで既に使用されています。

図

2人のユーザーU1とU2.U1がEMPLOYEESテーブルにアクセスできるとします。 したがって、EMPLOYEESテーブルからU2へのアクセスを有効にするために、U2スキーマでシノニムを作成できます。 U1からU2へのアクセスを許可する必要があります。

CONN U2/U2
SQL> CREATE SYNONYM EMP_SYN  FOR U1.employees;

CONN U1/U1
SQL> GRANT ALL ON EMP_SYN TO U2;

CONN U2/U2
SQL> SELECT* FROM EMP_SYN;

シノニムの削除

ユーザーは、所有する同義語を削除できます。 パブリックシノニムを削除するには、DROP PUBLIC SYNONYM特権が必要です。

DROP SYNONYM EMP_SYN;