| Oracle Database SQL Developerユーザーズ・ガイド リリース1.2 E06017-01 |
|
 戻る |
 次へ |
このチュートリアルでは、SQL Developerを使用して、簡略化した図書館データベースのオブジェクトを作成します。このデータベースには、本、利用者（図書館カードの所有者）および処理（本の貸出し、本の返却など）に関する表が含まれます。
これらの表は、チュートリアル用に意図的に簡略化されています。公共または組織内の実際の図書館での利用には適していません。たとえば、ここに示す図書館には書籍（雑誌、機関誌、その他の形式の文書を除く）のみが所蔵されており、同じ本を複数冊所蔵することはできません。
実行する主要な手順は次のとおりです。
PL/SQLプロシージャのデバッグ（オプション）
問合せのためのSQLワークシートの使用（オプション）
関連項目
3.9「図書館のチュートリアル用オブジェクトを作成および使用するためのスクリプト」
1.2「SQL Developerユーザー・インタフェース」
BOOKS表には、図書館の各本の行が含まれます。また、文字型および数値型の列、主キー、一意制約およびチェック制約が含まれます。「Create Table」ダイアログ・ボックスを使用して、表を宣言的に作成します。作成する表は、SQLワークシートを使用して次の文を入力した場合と実質的に同じ表になります。
CREATE TABLE books (
book_id VARCHAR2(20),
title VARCHAR2(50)
CONSTRAINT title_not_null NOT NULL,
author_last_name VARCHAR2(30)
CONSTRAINT last_name_not_null NOT NULL,
author_first_name VARCHAR2(30),
rating NUMBER,
CONSTRAINT books_pk PRIMARY KEY (book_id),
CONSTRAINT rating_1_to_10 CHECK (rating IS NULL OR
(rating >= 1 and rating <= 10)),
CONSTRAINT author_title_unique UNIQUE (author_last_name, title));
BOOKS表を作成するには、このチュートリアルに使用するスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「Tables」ノードを右クリックし、「New Table」を選択して、次の情報を入力します。（特に説明しないタブまたはフィールドには、何も入力しないでください。表の作成の開始時に、「Advanced」ボックスが選択されていないことを確認してください。）
表に関するダイアログ・ボックスおよびそのタブの詳細は、4.25「Create Table（クイック作成）」および4.26「Create/Edit Table（拡張オプション付き）」を参照してください。
Schema: 表を作成するスキーマとして現行のスキーマを指定します。
Name: BOOKS
次の情報を使用して、表の列を作成します。最後の列（rating）以外の各列を作成したら、「Add Column」をクリックし、次の列を追加します。（誤って「Add Column」ではなく「OK」をクリックした場合は、「Connections」ナビゲータ表示でBOOKS表を右クリックして「Edit」を選択すると、列の追加を続行できます。）
| 列名 | 型 | サイズ | その他の情報および注意事項 |
|---|---|---|---|
| book_id | VARCHAR2 | 20 | 主キー（NOT NULLかどうかを自動的にチェックします。主キー列には索引も作成されます。これは、デューイ・コードまたはその他の書籍識別子です） |
| title | VARCHAR2 | 50 | NOT NULL |
| author_last_name | VARCHAR2 | 30 | NOT NULL |
| author_first_name | VARCHAR2 | 30 | |
| rating | NUMBER | （図書館職員による個人的な本の評価で、1（つまらない）から10（すばらしい）で示されます） |
最後の列（rating）の入力後、「Schema」の横の「Advanced」を選択します。これによって、より多くの表オプションが含まれるペインが表示されます。この表では、「Unique Constraints」ペインと「Check Constraints」ペインを使用します。
「Unique Constraints」ペイン
「Add」をクリックして、author_last_nameとtitleの組合せは表内で一意でなければならないという一意制約を表に追加します。（大規模な図書館では同じ本を複数冊所蔵している場合が多いため、ここでは意図的に簡略化しています。また、姓とタイトルの組合せで一意性を確実にチェックできるとはかぎりませんが、ここに示す簡単なシナリオでは問題ありません。）
Name: author_title_unique
「Available Columns」で、TITLE、AUTHOR_LAST_NAMEの順にダブルクリックして、これらを「Selected Columns」に移動します。
「Check Constraints」ペイン
「Add」をクリックして、rating列の値はオプション（NULLを指定可）であるが、値を指定する場合は1から10の範囲の数字でなければならないというチェック制約を表に追加します。CHECK句で有効なSQL構文を使用して条件を入力する必要があります（ただし、CHECKキーワードは不要です。CHECK句のテキスト全体を囲むカッコも含めないでください）。
Name: rating_1_to_10
Condition: rating is null or (rating >= 1 and rating <= 10)
「OK」をクリックして、表の作成を終了します。
3.2「表の作成（PATRONS）」に進み、次の表を作成します。
PATRONS表には、図書館から本を借りることができる各利用者（図書館カードの所有者）の行が含まれます。また、オブジェクト型（MDSYS.SDO_GEOMETRY）列が含まれます。「Create Table」ダイアログ・ボックスを使用して、表を宣言的に作成します。作成する表は、SQLワークシートを使用して次の文を入力した場合と実質的に同じ表になります。
CREATE TABLE patrons (
patron_id NUMBER,
last_name VARCHAR2(30)
CONSTRAINT patron_last_not_null NOT NULL,
first_name VARCHAR2(30),
street_address VARCHAR2(50),
city_state_zip VARCHAR2(50),
location MDSYS.SDO_GEOMETRY,
CONSTRAINT patrons_pk PRIMARY KEY (patron_id));
データベースの設計としては、すべての情報に単一のcity_state_zip列を使用することはお薦めしません。ここでは単に、チュートリアルでの作業を簡略化するために、このように設定しています。
location列（ジオコーディングされた利用者の住所を表すOracle Spatialジオメトリ）は、単に、複合（オブジェクト）型を使用することを示す列です。
PATRONS表を作成するには、（まだ接続していない場合は）このチュートリアルに使用しているスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「Tables」ノードを右クリックし、「New Table」を選択して、次の情報を入力します。（特に説明しないタブまたはフィールドには、何も入力しないでください。表の作成の開始時に、「Advanced」ボックスが選択されていないことを確認してください。）
Schema: 表を作成するスキーマとして現行のスキーマを指定します。
Name: PATRONS
次の情報を使用して、表のほとんどの列を作成します。city_state_zip列以外の各列を作成したら、「Add Column」をクリックし、次の列を追加します。（誤って「Add Column」ではなく「OK」をクリックした場合は、「Connections」ナビゲータ表示でPATRONS表を右クリックして「Edit」を選択すると、列の追加を続行できます。）
| 列名 | 型 | サイズ | その他の情報および注意事項 |
|---|---|---|---|
| patron_id | NUMBER | 主キー（一意の利用者ID番号。値は、ユーザーが作成する順序を使用して作成されます） | |
| last_name | VARCHAR2 | 30 | NOT NULL |
| first_name | VARCHAR2 | 30 | |
| street_address | VARCHAR2 | 30 | |
| city_state_zip | VARCHAR2 | 30 |
表の最後の列（location）には、複合データ型が必要です。この列には、「Columns」タブで拡張オプションを使用する必要があります。「Schema」の横の「Advanced」を選択します。これによって、より多くの表オプションを選択するためのペインが表示されます。
「Columns」ペインで、city_state_zipという列名をクリックし、「Add Column」（+）アイコンをクリックして、表の最後の列に次の情報を追加します。
| 列名 | 型 | その他の情報および注意事項 |
|---|---|---|
| location | 複合型
スキーマ: MDSYS 型: SDO_GEOMETRY |
（ジオコーディングされた利用者の住所を表すOracle Spatialジオメトリ・オブジェクト） |
最後の列（location）の入力後、「OK」をクリックして表の作成を終了します。
3.3「表の作成（TRANSACTIONS）」に進み、次の表を作成します。
TRANSACTIONS表には、利用者と本に関連する各処理（利用者への本の貸出し、返却など）の行が含まれます。また、2つの外部キー列が含まれます。「Create Table」ダイアログ・ボックスを使用して、表を宣言的に作成します。作成する表は、SQLワークシートを使用して次の文を入力した場合と実質的に同じ表になります。
CREATE TABLE transactions (
transaction_id NUMBER,
patron_id CONSTRAINT for_key_patron_id
REFERENCES patrons(patron_id),
book_id CONSTRAINT for_key_book_id
REFERENCES books(book_id),
transaction_date DATE
CONSTRAINT tran_date_not_null NOT NULL,
transaction_type NUMBER
CONSTRAINT tran_type_not_null NOT NULL,
CONSTRAINT transactions_pk PRIMARY KEY (transaction_id));
TRANSACTIONS表を作成するには、（まだ接続していない場合は）このチュートリアルに使用しているスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「Tables」ノードを右クリックし、「New Table」を選択して、次の情報を入力します。（特に説明しないタブまたはフィールドには、何も入力しないでください。表の作成の開始時に、「Advanced」ボックスが選択されていないことを確認してください。）
Schema: 表を作成するスキーマとして現行のスキーマを指定します。
Name: TRANSACTIONS
次の情報を使用して、表の列を作成します。最後の列（transaction_type）以外の各列を作成したら、「Add Column」をクリックし、次の列を追加します。（誤って「Add Column」ではなく「OK」をクリックした場合は、「Connections」ナビゲータ表示でTRANSACTIONS表を右クリックして「Edit」を選択すると、列の追加を続行できます。）
| 列名 | 型 | サイズ | その他の情報および注意事項 |
|---|---|---|---|
| transaction_id | NUMBER | 主キー（一意の処理ID番号。値は、自動的に作成されるトリガーおよび順序を使用して作成されます） | |
| patron_id | NUMBER | （外部キー。PATRONS表のpatron_idの値と一致する必要があります） | |
| book_id | VARCHAR2 | 20 | （外部キー。BOOKS表のbook_idの値と一致する必要があります） |
| transaction_date | DATE | （処理の日時） | |
| transaction_type | NUMBER | （処理の種類を示す数値コード。たとえば、1は本の貸出しを示します） |
最後の列（transaction_type）の入力後、「Schema」の横の「Advanced」を選択します。これによって、より多くの表オプションを選択するためのペインが表示されます。この表では、「Column Sequences」ペインと「Foreign Keys」ペインを使用します。
「Column Sequences」ペイン
すでにTRANSACTION_IDを主キーに指定しているため、このペインでは、主キー列の値を自動的に移入することのみを指定します。この便利な方法では、（SQL Developerによって自動的に作成される）トリガーおよび順序を使用して、各処理のID値を一意にすることができます。
Column: TRANSACTION_ID
Sequence: 新しい順序
Trigger: TRANSACTIONS_TRG（デフォルト。BEFORE INSERTトリガーはこの名前で自動的に作成されます。）
「Foreign Keys」タブ
1. 「Add」をクリックして、TRANSACTIONS表の2つの外部キーの最初のキーを作成します。
Name: for_key_patron_id
Referenced Schema: この外部キーで参照する主キーまたは一意制約を持つ表が含まれているスキーマの名前。このチュートリアルですでに使用しているスキーマを使用します。
Referenced Table: PATRONS
Referenced Constraint: PATRONS_PK（PATRONS表の主キー制約の名前。「Referenced Column on PATRONS」に表示されている値がPATRON_IDになっていることを確認します。）
Associations: Local Column: PATRON_ID
Associations: Referenced Column on PATRONS: PATRON_ID
2. 「Add」をクリックして、TRANSACTIONS表の2つの外部キーの2番目のキーを作成します。
Name: for_key_book_id
Referenced Schema: この外部キーで参照する主キーまたは一意制約を持つ表が含まれているスキーマの名前。このチュートリアルですでに使用しているスキーマを使用します。
Referenced Table: BOOKS
Referenced Constraint: BOOKS_PK（BOOKS表の主キー制約の名前。「Referenced Column on BOOKS」に表示されている値がBOOK_IDになっていることを確認します。）
Associations: Local Column: BOOK_ID
Associations: Referenced Column on BOOKS: BOOK_ID
3. 「OK」をクリックして、表の作成を終了します。
これで、すべての表の作成が完了しました。PATRONS表の一意の主キー値の生成に使用する順序を作成するには、3.4「順序の作成」に進みます。
1つの順序オブジェクトを作成します。このオブジェクトは、PATRONS表に一意の主キー値を生成するINSERT文に使用されます。（TRANSACTIONS表の主キー値の自動移入を可能にするSQL Developer機能を使用しているため、この表の主キーには順序を作成する必要はありません。）「Create Sequence」ダイアログ・ボックスを使用して、順序を宣言的に作成します。作成する順序は、SQLワークシートを使用して次の文を入力した場合と実質的に同じ順序になります。
CREATE SEQUENCE patron_id_seq START WITH 100 INCREMENT BY 1;
順序の作成後、INSERT文にこの順序を使用して、一意の数値を生成できます。次の例では、新しい利用者（図書館の利用者）に関する行の作成でpatron_id_seqという順序を使用して、順序patron_id_seqで次に使用可能な値を利用者IDとしてこの利用者に割り当てます。
INSERT INTO patrons VALUES (patron_id_seq.nextval, 'Smith', 'Jane', '123 Main Street', 'Mytown, MA 01234', null);
順序を作成するには、（まだ接続していない場合は）このチュートリアルに使用しているスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「Sequences」ノードを右クリックして、「New Sequence」を選択し、「Create Sequence」ダイアログ・ボックスを使用して次の情報を入力します。
Schema: 順序を作成するスキーマとして現行のスキーマを指定します。
Name: patron_id_seq
Increment: 1
Start with: 100
「OK」をクリックして、順序の作成を終了します。
ビューを作成するには、3.5「ビューの作成」に進みます。
利用者およびその処理に関する情報を戻すビューを作成します。このビューは、PATRONS表およびTRANSACTIONS表を問い合せて、利用者のID、姓、名、処理および処理の種類が含まれる行を戻します。行は、利用者IDによって、利用者ID内では処理の種類によって順序付けされます。
patrons_trans_viewビューを作成するには、（まだ接続していない場合は）このチュートリアルに使用しているスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「Views」ノードを右クリックし、「New View」を選択して、次の情報を入力します。（特に説明しないタブまたはフィールドには、何も入力しないでください。）
Schema: ビューを作成するスキーマとして現行のスキーマを指定します。
Name: patrons_trans_view
「Entire SQL Query」タブ
「Entire SQL Query」ボックスに、次の文を入力（またはコピー・アンド・ペースト）します。
SELECT p.patron_id,
p.last_name,
p.first_name,
t.transaction_type,
t.transaction_date
FROM patrons p, transactions t
WHERE p.patron_id = t.patron_id
ORDER BY p.patron_id, t.transaction_type
次に、「Test Syntax」をクリックして、構文エラーがないことを確認します。エラーがある場合は修正して、再度「Test Syntax」をクリックします。
「DDL」
SQL Developerによってビューの作成に使用されるSQL文を確認します。変更が必要な場合は「Entire SQL Query」タブに戻って変更します。
SQLスクリプト･ファイルにCREATE VIEW文を保存する場合は、「Save」をクリックして、場所とファイル名を指定します。
終了したら、「OK」をクリックします。
これで、ビューの作成が完了しました。ビューによって戻されるデータを確認するには、「Connections」ナビゲータで「Views」を開き、「PATRONS_TRANS_VIEW」を選択して、「Data」タブをクリックします。
指定した評価を受けているすべての本を表示するプロシージャを作成します。入力パラメータ（1から10の範囲の数字）を指定してこのプロシージャをコールすると、指定した評価を受けているすべての本のタイトルが出力されます。
プロシージャを作成するには、（まだ接続していない場合は）このチュートリアルに使用しているスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「Procedures」ノードを右クリックして、「New Procedure」を選択し、「Create PL/SQL Procedure」ダイアログ・ボックスを使用して次の情報を入力します。
Object Name: list_a_rating
「OK」をクリックします。新しいプロシージャの「Source」ウィンドウが開きます。次のプロシージャ・テキストを入力（またはコピー・アンド・ペースト）します。
CREATE OR REPLACE
PROCEDURE list_a_rating(in_rating IN NUMBER) AS
matching_title VARCHAR2(50);
TYPE my_cursor IS REF CURSOR;
the_cursor my_cursor;
BEGIN
OPEN the_cursor
FOR 'SELECT title
FROM books
WHERE rating = :in_rating'
USING in_rating;
DBMS_OUTPUT.PUT_LINE('All books with a rating of ' || in_rating || ':');
LOOP
FETCH the_cursor INTO matching_title;
EXIT WHEN the_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(matching_title);
END LOOP;
CLOSE the_cursor;
END list_a_rating;
このプロシージャは、（the_cursorという）カーソルを使用して、（in_ratingパラメータで）指定した評価を受けている本の行のみを戻し、ループを使用してその評価を受けている各本のタイトルを出力します。
「Save」アイコンをクリックしてプロシージャを保存します。
使用例を次に示します。LIST_A_RATINGというプロシージャを作成し、（たとえば、3.9「図書館のチュートリアル用オブジェクトを作成および使用するためのスクリプト」に示すINSERT文を使用して）BOOKS表にデータを挿入した後、次の文を使用して、10という評価を受けているすべての本を戻すとします。
CALL list_a_rating(10);
3.9「図書館のチュートリアル用オブジェクトを作成および使用するためのスクリプト」に示すBOOKS表の行を挿入した後にSQL Developerでこのプロシージャを実行するには、「Connections」ナビゲータの階層表示でLIST_A_RATINGを右クリックし、「Run」を選択します。「Run PL/SQL」ダイアログ・ボックスで、「IN_RATING => IN_RATING」を「IN_RATING => 10」に変更し、「OK」をクリックします。「Log」ウィンドウに次の出力が表示されます。
All books with a rating of 10: Moby Dick Software Wizardry
SQL Developerを使用してPL/SQLプロシージャのデバッグを実践してみるには、3.6「PL/SQLプロシージャの作成」で作成したlist_a_ratingのようなプロシージャを、論理エラーが発生するように作成します。（コーディングも意図的に非効率的なものとし、評価が変数で表示されるようにします。）
このプロシージャを作成するには、（まだ接続していない場合は）このチュートリアルに使用しているスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「Procedures」ノードを右クリックして、「New Procedure」を選択し、「Create PL/SQL Procedure」ダイアログ・ボックスを使用して次の情報を入力します。
Object Name: list_a_rating2
「OK」をクリックします。新しいプロシージャの「Source」ウィンドウが開きます。次のプロシージャ・テキストを入力（またはコピー・アンド・ペースト）します。
CREATE OR REPLACE
PROCEDURE list_a_rating2(in_rating IN NUMBER) AS
matching_title VARCHAR2(50);
matching_rating NUMBER;
TYPE my_cursor IS REF CURSOR;
the_cursor my_cursor;
rating_cursor my_cursor;
BEGIN
OPEN the_cursor
FOR 'SELECT title
FROM books
WHERE rating <= :in_rating'
USING in_rating;
OPEN rating_cursor FOR 'SELECT rating FROM books WHERE
rating <= :in_rating' USING in_rating;
DBMS_OUTPUT.PUT_LINE('All books with a rating of ' || in_rating || ':');
LOOP
FETCH the_cursor INTO matching_title;
FETCH rating_cursor INTO matching_rating;
EXIT WHEN the_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(matching_title);
END LOOP;
CLOSE the_cursor;
CLOSE rating_cursor;
END list_a_rating2;
このプロシージャには、the_cursorの定義に論理エラーが含まれています。このプロシージャでは、指定した評価を受けている本のタイトルのみを選択する必要がありますが、この定義では、指定した評価以下の評価を受けている本のタイトルが選択されることになります。
「Save」アイコンをクリックしてプロシージャを保存します。
このプロシージャを実行して、10という評価を受けているすべての本を表示する必要があるとします。「Connections」ナビゲータの階層表示でLIST_A_RATING2を右クリックし、「Run」を選択します。「Run PL/SQL」ダイアログ・ボックスで、「IN_RATING => IN_RATING」を「IN_RATING => 10」に変更し、「OK」をクリックします。「Log」ウィンドウには、予期しない出力が表示されます（10以外の評価を受けている本を含む多くの本のタイトルが表示されます）。そのため、このプロシージャをデバッグすることにします。
プロシージャをデバッグするには、次の手順を実行します。
「Connections」ナビゲータでLIST_A_RATING2をクリックし、右クリックして「Compile for Debug」を選択します。
コード・タブで、「Edit」アイコン（鉛筆と紙のアイコン）をクリックします。
「FETCH the_cursor INTO matching_title;」および「FETCH rating_cursor INTO matching_rating;」という2つの行のそれぞれの左側の余白（細い縦線の左側）をクリックして、2つのブレーク・ポイントを設定します。（左側の余白をクリックすると、ブレーク・ポイントの設定と設定解除が切り替わります。）これによって、デバッグ・モードで実行しながら、matching_title変数およびmatching_rating変数の値を確認することができます。
「Connections」ナビゲータの階層表示でLIST_A_RATING2をクリックし、「Debug」メニューで「Debug」を選択します。「IN_RATING => IN_RATING」行が「IN_RATING => 10」に変更されていることを確認し、「OK」をクリックします。
デバッグ・ツールバーで、「Resume」アイコンをクリックして実行を開始し、次のブレーク・ポイントで停止します。ウィンドウの右下の「Smart Data」タブの表示を書き留めます。「Resume」アイコンを繰り返しクリックし、各行が処理されるときの評価変数の値に注目します。Get Rich Really Fast というタイトルのところで最初の不正な結果が見つかります。このタイトルは、評価が1（明らかに10未満）であるにもかかわらず、選択されています。（1.6「ファンクションおよびプロシージャの実行とデバッグ」に示す、デバッグ情報が含まれる画面の図を参照してください。）
問題を修正するための十分な情報が得られたら、デバッグ・ツールバーの「Terminate」アイコンをクリックします。
このデバッグ・セッションから、論理エラーを修正するには、the_cursorの定義で「rating <= :in_rating」を「rating = :in_rating」に変更する必要があることがわかります。
SQLワークシートを使用して、データベース接続を使用するSQL文をテストすることができます。ワークシートを表示するには、「Tools」メニューから「SQL Worksheet」を選択します。「Select Connection」ダイアログ・ボックスで、第3章「チュートリアル: 小規模データベースのオブジェクトの作成」に示すチュートリアルのBOOKS表、PATRONS表およびTRANSACTIONS表を作成するために使用したデータベース接続を選択します。
SQLワークシートには、1.7「SQLワークシートの使用」に示すユーザー・インタフェースがあります。
「Enter SQL Statement」ボックスに次の文を入力します（SQLワークシートではセミコロンはオプションです）。
SELECT author_last_name, title FROM books;
SQLキーワード（この例ではSELECTおよびFROM）が自動的にハイライト表示されていることに注目します。
SQLワークシート・ツールバーで「Execute SQL Statement」アイコンをクリックします。SQL文を入力した領域の下の「Results」タブに、問合せの結果が表示されます。
「Enter SQL Statement」ボックスに次の文を入力（またはコピー・アンド・ペースト）します。この文は、「ビューの作成」で作成したビューのSELECT文と同じです。
SELECT p.patron_id,
p.last_name,
p.first_name,
t.transaction_type,
t.transaction_date
FROM patrons p, transactions t
WHERE p.patron_id = t.patron_id
ORDER BY p.patron_id, t.transaction_type;
SQLワークシート・ツールバーで「Execute SQL Statement」アイコンをクリックし、問合せの結果を表示します。
SQLワークシート・ツールバーで「Execute Explain Plan」アイコンをクリックして、Oracle DatabaseがSQL文の実行に使用する実行計画を確認します（「Explain」タブに表示されます）。この情報には、オプティマイザ方針および文の実行にかかるコストが含まれています。（実行計画の生成および解釈方法の詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。）
次の文は、第3章「チュートリアル: 小規模データベースのオブジェクトの作成」のチュートリアル用に作成した（または作成する）データベース・オブジェクトを作成および使用するためのものです。これらのコマンドを確認しておくと、チュートリアルに示す図書館データベース・オブジェクトについて理解するのに役立ちます。
-- Clean up from any previous tutorial actions.
DROP TABLE transactions;
DROP TABLE books;
DROP TABLE patrons;
DROP SEQUENCE patron_id_seq;
DROP SEQUENCE transactions_seq;
DROP TRIGGER transactions_trg;
DROP VIEW patrons_trans_view;
DROP PROCEDURE list_a_rating;
DROP PROCEDURE list_a_rating2;
set serveroutput on
-- Create objects.
CREATE TABLE books (
book_id VARCHAR2(20),
title VARCHAR2(50)
CONSTRAINT title_not_null NOT NULL,
author_last_name VARCHAR2(30)
CONSTRAINT last_name_not_null NOT NULL,
author_first_name VARCHAR2(30),
rating NUMBER,
CONSTRAINT books_pk PRIMARY KEY (book_id),
CONSTRAINT rating_1_to_10 CHECK (rating IS NULL OR
(rating >= 1 and rating <= 10)),
CONSTRAINT author_title_unique UNIQUE (author_last_name, title));
CREATE TABLE patrons (
patron_id NUMBER,
last_name VARCHAR2(30)
CONSTRAINT patron_last_not_null NOT NULL,
first_name VARCHAR2(30),
street_address VARCHAR2(50),
city_state_zip VARCHAR2(50),
location MDSYS.SDO_GEOMETRY,
CONSTRAINT patrons_pk PRIMARY KEY (patron_id));
CREATE TABLE transactions (
transaction_id NUMBER,
patron_id CONSTRAINT for_key_patron_id
REFERENCES patrons(patron_id),
book_id CONSTRAINT for_key_book_id
REFERENCES books(book_id),
transaction_date DATE
CONSTRAINT tran_date_not_null NOT NULL,
transaction_type NUMBER
CONSTRAINT tran_type_not_null NOT NULL,
CONSTRAINT transactions_pk PRIMARY KEY (transaction_id));
CREATE SEQUENCE patron_id_seq
START WITH 100
INCREMENT BY 1;
-- The sequence for the transaction_id
-- in the tutorial is created automatically,
-- and may have the name TRANSACTIONS_SEQ.
CREATE SEQUENCE transactions_seq
START WITH 1
INCREMENT BY 1;
-- The before-insert trigger for transaction ID values
-- in the tutorial is created automatically,
-- and may have the name TRANSACTIONS_TRG.
CREATE OR REPLACE TRIGGER transactions_trg
BEFORE INSERT ON TRANSACTIONS
FOR EACH ROW
BEGIN
SELECT TRANSACTIONS_SEQ.NEXTVAL INTO :NEW.TRANSACTION_ID FROM DUAL;
END;
/
CREATE VIEW patrons_trans_view AS
SELECT p.patron_id,
p.last_name,
p.first_name,
t.transaction_type,
t.transaction_date
FROM patrons p, transactions t
WHERE p.patron_id = t.patron_id
ORDER BY p.patron_id, t.transaction_type;
-- Procedure: List all books that have a specified rating.
CREATE OR REPLACE PROCEDURE list_a_rating(in_rating IN NUMBER) AS
matching_title VARCHAR2(50);
TYPE my_cursor IS REF CURSOR;
the_cursor my_cursor;
BEGIN
OPEN the_cursor
FOR 'SELECT title
FROM books
WHERE rating = :in_rating'
USING in_rating;
DBMS_OUTPUT.PUT_LINE('All books with a rating of ' || in_rating || ':');
LOOP
FETCH the_cursor INTO matching_title;
EXIT WHEN the_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(matching_title);
END LOOP;
CLOSE the_cursor;
END;
/
show errors;
-- Insert and query data.
INSERT INTO books VALUES ('A1111', 'Moby Dick', 'Melville', 'Herman', 10);
INSERT INTO books VALUES ('A2222', 'Get Rich Really Fast', 'Scammer', 'Ima', 1);
INSERT INTO books VALUES ('A3333', 'Finding Inner Peace', 'Blissford', 'Serenity', null);
INSERT INTO books VALUES ('A4444', 'Great Mystery Stories', 'Whodunit', 'Rodney', 5);
INSERT INTO books VALUES ('A5555', 'Software Wizardry', 'Abugov', 'D.', 10);
INSERT INTO patrons VALUES (patron_id_seq.nextval,
'Smith', 'Jane', '123 Main Street', 'Mytown, MA 01234', null);
INSERT INTO patrons VALUES (patron_id_seq.nextval,
'Chen', 'William', '16 S. Maple Road', 'Mytown, MA 01234', null);
INSERT INTO patrons VALUES (patron_id_seq.nextval,
'Fernandez', 'Maria', '502 Harrison Blvd.', 'Sometown, NH 03078', null);
INSERT INTO patrons VALUES (patron_id_seq.nextval,
'Murphy', 'Sam', '57 Main Street', 'Mytown, MA 01234', null);
INSERT INTO transactions (patron_id, book_id,
transaction_date, transaction_type)
VALUES (100, 'A1111', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id,
transaction_date, transaction_type)
VALUES (100, 'A2222', SYSDATE, 2);
INSERT INTO transactions (patron_id, book_id,
transaction_date, transaction_type)
VALUES (101, 'A3333', SYSDATE, 3);
INSERT INTO transactions (patron_id, book_id,
transaction_date, transaction_type)
VALUES (101, 'A2222', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id,
transaction_date, transaction_type)
VALUES (102, 'A3333', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id,
transaction_date, transaction_type)
VALUES (103, 'A4444', SYSDATE, 2);
INSERT INTO transactions (patron_id, book_id,
transaction_date, transaction_type)
VALUES (100, 'A4444', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id,
transaction_date, transaction_type)
VALUES (102, 'A2222', SYSDATE, 2);
INSERT INTO transactions (patron_id, book_id,
transaction_date, transaction_type)
VALUES (102, 'A5555', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id,
transaction_date, transaction_type)
VALUES (101, 'A2222', SYSDATE, 1);
-- Test the view and the procedure.
SELECT * FROM patrons_trans_view;
CALL list_a_rating(10);
