1. ユーザーズ・ガイド
  2. SQL Developerチュートリアル: 小規模データベースのオブジェクトの作成

5 SQL Developerチュートリアル: 小規模データベースのオブジェクトの作成

このチュートリアルでは、SQL Developerを使用して、簡略化した図書館データベースのオブジェクトを作成します。このデータベースには、本、利用者(図書館カードの所有者)および処理(本の貸出し、本の返却など)に関する表が含まれます。

ノート:

他にも多くのSQL Developerチュートリアルが利用できます。詳細は、開始ページ、特に「チュートリアルとオンライン・デモンストレーション」を参照してください(「ヘルプ」、「開始ページ」の順にクリックして「はじめに」タブが開いていない場合はクリック)。

これらの表は、チュートリアル用に意図的に簡略化されています。公共または組織内の実際の図書館での利用には適していません。たとえば、ここに示す図書館には書籍(雑誌、機関誌、その他の形式の文書を除く)のみが所蔵されており、同じ本を複数冊所蔵することはできません。

実行する主要なステップは次のとおりです。

  1. 表の作成(BOOKS)

  2. 表の作成(PATRONS)

  3. 表の作成(TRANSACTIONS)

  4. 順序の作成

  5. 表へのデータの挿入

  6. ビューの作成

  7. PL/SQLプロシージャの作成

  8. PL/SQLプロシージャのデバッグ(オプション)

  9. 問合せのためのSQLワークシートの使用(オプション)

ノート:

「図書館のチュートリアル用オブジェクトを作成および使用するためのスクリプト」に示すスクリプトで、先頭にDROP文を使用すると、このチュートリアル用に作成したオブジェクトを削除できます。

このチュートリアルでは、作成した表およびその他のオブジェクトを所有するデータベース・ユーザーに対してデータベース接続があるものと想定します。

  • そのようなデータベース・ユーザーの作成が必要な場合は、先に作成してください(「ユーザー(他のユーザー)」を参照)。

  • そのユーザーに対する接続の作成が必要な場合は、接続を作成(または、ローカル接続を自動的に生成)します。「データベース接続」を参照してください。

このチュートリアルをオンライン・ヘルプで表示するには、「ヘルプの目次」表示で本のアイコンを右クリックし、「トピックのサブツリーを印刷」を選択してください。

関連項目

5.1 表の作成(BOOKS)

BOOKS表には、図書館の各本の行が含まれます。文字型および数値型の列、主キー、一意制約およびチェック制約が含まれます。「表の作成」ダイアログ・ボックスを使用して表を宣言的に作成しますが、作成される表は、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表を作成するには、このチュートリアルに使用するスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「表」ノードを右クリックし、「新規の表」を選択して、次の情報を入力します。(特に説明しないタブまたはフィールドには、何も入力しないでください。表の作成の開始時に、「拡張」ボックスが選択されていないことを確認してください。)

スキーマ: 表を作成するスキーマとして現行のスキーマを指定します。

名前: BOOKS

次の情報を使用して、表の列を作成します。最後の列(rating)以外の各列を作成したら、「列の追加」をクリックし、次の列を追加します。(誤って「列の追加」ではなく「OK」をクリックした場合は、「接続」ナビゲータ表示でBOOKS表を右クリックして「編集」を選択すると、列の追加を続行できます。)

列名 型 サイズ その他の情報およびノート

book_id

VARCHAR2

20

主キー(NOT NULLかどうかを自動的にチェックします。主キー列には索引も作成されます。これは、デューイ・コードまたはその他の書籍識別子です)

title

VARCHAR2

50

Null以外

author_last_name

VARCHAR2

30

Null以外

author_first_name

VARCHAR2

30

rating

NUMBER

(図書館職員による個人的な本の評価で、1(つまらない)から10(すばらしい)で示されます)

最後の列(rating)の入力後、「スキーマ」の横の「拡張」を選択します。これによって、より多くの表オプションが含まれるペインが表示されます。この表では、「一意制約」ペインと「CHECK制約」ペインを使用します。

「一意制約」ペイン

「追加」をクリックして、author_last_nameとtitleの組合せは表内で一意でなければならないという一意制約を表に追加します。(大規模な図書館では同じ本を複数冊所蔵している場合が多いため、ここでは意図的に簡略化しています。また、姓とタイトルの組合せで一意性を確実にチェックできるとはかぎりませんが、ここに示す簡単なシナリオでは問題ありません。)

名前: author_title_unique

「使用可能な列」で、TITLE、AUTHOR_LAST_NAMEの順にダブルクリックして、これらを「選択済の列」に移動します。

「CHECK制約」ペイン

「追加」をクリックして、rating列の値はオプション(NULLを指定可)であるが、値を指定する場合は1から10の範囲の数字でなければならないというチェック制約を表に追加します。CHECK句で有効なSQL構文を使用して条件を入力する必要があります(ただし、CHECKキーワードは不要です。CHECK句のテキスト全体を囲むカッコも含めないでください)。

名前: rating_1_to_10

条件: rating is null or (rating >= 1 and rating <= 10)

「OK」をクリックして、表の作成を終了します。

次の表を作成するには、「表の作成(PATRONS)」に進みます。

関連項目:

表に関するダイアログ・ボックスおよびそのタブの詳細は、「表の作成(クイック作成)」および「表の作成/編集(拡張オプション付き)」を参照してください

5.2 表の作成(PATRONS)

PATRONS表には、図書館から本を借りることができる各利用者(図書館カードの所有者)の行が含まれます。また、オブジェクト・タイプ(MDSYS.SDO_GEOMETRY)列が含まれます。「表の作成」ダイアログ・ボックスを使用して表を宣言的に作成しますが、作成される表は、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 and Graph ジオメトリ)は、単に、複合(オブジェクト)型を使用することを示す列です。

PATRONS表を作成するには、(まだ接続していない場合は)このチュートリアルに使用しているスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「表」ノードを右クリックし、「新規の表」を選択して、次の情報を入力します。(特に説明しないタブまたはフィールドには、何も入力しないでください。表の作成の開始時に、「拡張」ボックスが選択されていないことを確認してください。)

スキーマ: 表を作成するスキーマとして現行のスキーマを指定します。

名前: PATRONS

次の情報を使用して、表のほとんどの列を作成します。city_state_zip列以外の各列を作成したら、「列の追加」をクリックし、次の列を追加します。(誤って「列の追加」ではなく「OK」をクリックした場合は、「接続」ナビゲータ表示でPATRONS表を右クリックして「編集」を選択すると、列の追加を続行できます。)

列名 型 サイズ その他の情報およびノート

patron_id

NUMBER

主キー。(一意の利用者ID番号。値は、ユーザーが作成する順序を使用して作成されます)

last_name

VARCHAR2

30

Null以外

first_name

VARCHAR2

30

street_address

VARCHAR2

30

city_state_zip

VARCHAR2

30

表の最後の列(location)には、複合データ型が必要です。この列には、「列」タブで拡張オプションを使用する必要があります。「スキーマ」の横の「拡張」を選択します。これによって、より多くの表オプションを選択するためのペインが表示されます。

「列」ペインで、city_state_zipという列名をクリックし、「列の追加」(+)アイコンをクリックして、表の最後の列に次の情報を追加します。

列名 型 その他の情報およびノート

location

複合型

スキーマ: MDSYS

型: SDO_GEOMETRY

(ジオコーディングされた利用者の住所を表すOracle Spatial and Graph ジオメトリ・オブジェクト)

最後の列(location)の入力後、「OK」をクリックして表の作成を終了します。

次の表を作成するには、「表の作成(TRANSACTIONS)」に進みます。

5.3 表の作成(TRANSACTIONS)

TRANSACTIONS表には、利用者と本に関連する各処理(利用者への本の貸出し、返却など)の行が含まれます。また、2つの外部キー列が含まれます。「表の作成」ダイアログ・ボックスを使用して表を宣言的に作成しますが、作成される表は、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表を作成するには、(まだ接続していない場合は)このチュートリアルに使用しているスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「表」ノードを右クリックし、「新規の表」を選択して、次の情報を入力します。(特に説明しないタブまたはフィールドには、何も入力しないでください。表の作成の開始時に、「拡張」ボックスが選択されていないことを確認してください。)

スキーマ: 表を作成するスキーマとして現行のスキーマを指定します。

名前: TRANSACTIONS

次の情報を使用して、表の列を作成します。最後の列(transaction_type)以外の各列を作成したら、「列の追加」をクリックし、次の列を追加します。(誤って「列の追加」ではなく「OK」をクリックした場合は、「接続」ナビゲータ表示でTRANSACTIONS表を右クリックして「編集」を選択すると、列の追加を続行できます。)

列名 型 サイズ その他の情報およびノート

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)の入力後、「スキーマ」の横の「拡張」を選択します。これによって、より多くの表オプションを選択するためのペインが表示されます。この表では、「列順序」ペインと「外部キー」ペインを使用します。

「列順序」ペイン

すでにTRANSACTION_IDを主キーに指定しているため、このペインでは、主キー列の値を自動的に移入することのみを指定します。この便利な方法では、(SQL Developerによって自動的に作成される)トリガーおよび順序を使用して、各処理のID値を一意にすることができます。

列: TRANSACTION_ID

順序: 新しい順序

トリガー: TRANSACTIONS_TRG(デフォルト。BEFORE INSERTトリガーはこの名前で自動的に作成されます。)

「外部キー」タブ

1. 「追加」をクリックして、TRANSACTIONS表の2つの外部キーの最初のキーを作成します。

名前: for_key_patron_id

参照スキーマ: この外部キーで参照する主キーまたは一意制約を持つ表が含まれているスキーマの名前。このチュートリアルですでに使用しているスキーマを使用します。

参照先の表: PATRONS

参照先の制約: PATRONS_PK(PATRONS表の主キー制約の名前。「PATRONSの参照列」に表示されている値がPATRON_IDになっていることを確認します。)

アソシエーション: ローカル列: PATRON_ID

アソシエーション: PATRONSの参照列: PATRON_ID

2. 「追加」をクリックして、TRANSACTIONS表の2つの外部キーの2番目のキーを作成します。

名前: for_key_book_id

参照スキーマ: この外部キーで参照する主キーまたは一意制約を持つ表が含まれているスキーマの名前。このチュートリアルですでに使用しているスキーマを使用します。

参照先の表: BOOKS

参照先の制約: BOOKS_PK(BOOKS表の主キー制約の名前)。「BOOKSの参照列」に表示されている値がBOOK_IDになっていることを確認します。

アソシエーション: ローカル列: BOOK_ID

アソシエーション: BOOKSの参照列: BOOK_ID

3. 「OK」をクリックして、表の作成を終了します。

これで、すべての表の作成が完了しました。PATRONS表の一意の主キー値の生成に使用する順序を作成するには、「順序の作成」に進みます。

5.4 順序の作成

1つの順序オブジェクトを作成します。このオブジェクトは、PATRONS表に一意の主キー値を生成するINSERT文に使用されます。(TRANSACTIONS表の主キー値の自動移入を可能にするSQL Developer機能を使用しているため、この表の主キーには順序を作成する必要はありません。)「順序の作成」ダイアログ・ボックスを使用して、順序を宣言的に作成します。作成する順序は、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);

順序を作成するには、(まだ接続していない場合は)このチュートリアルに使用しているスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「順序」ノードを右クリックして、「新規順序」を選択し、「順序の作成」ダイアログ・ボックスを使用して次の情報を入力します。

スキーマ: 順序を作成するスキーマとして現行のスキーマを指定します。

名前: patron_id_seq

増分: 1

開始: 100

最小値: 100

「OK」をクリックして、順序の作成を終了します。

サンプル・データを表に挿入するには、「表へのデータの挿入」に進みます。

5.5 表へのデータの挿入

作成するビューおよびPL/SQLプロシージャの使用時に便利なように、BOOKS、PATRONSおよびTRANSACTIONS表にサンプル・データを追加します。(サンプル・データを追加しなくても、このチュートリアルの残りのオブジェクトは作成できますが、ビューおよびプロシージャは結果を戻しません。)

これまでに使用しているデータベース接続に関連付けられたSQLワークシート・ウィンドウに移動します。次のINSERT文を「SQL文の入力」ボックスにコピーして貼り付けます。 

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);

「スクリプトの実行」アイコンをクリックするか、または[F5]キーを押します。

ビューを作成するには、「ビューの作成」に進みます。

関連項目

5.6 ビューの作成

利用者およびその処理に関する情報を戻すビューを作成します。このビューは、PATRONS表およびTRANSACTIONS表を問い合せて、利用者のID、姓、名、処理および処理の種類が含まれる行を戻します。行は、利用者IDによって、利用者ID内では処理の種類によって順序付けされます。

patrons_trans_viewビューを作成するには、(まだ接続していない場合は)このチュートリアルに使用しているスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「ビュー」ノードを右クリックし、「新規ビュー」を選択して、次の情報を入力します。(特に説明しないタブまたはフィールドには、何も入力しないでください。)

スキーマ: ビューを作成するスキーマとして現行のスキーマを指定します。

名前: patrons_trans_view

「SQL問合せ」タブ

「SQL問合せ全体」ボックスに、次の文を入力(またはコピー・アンド・ペースト)します。

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

次に、「構文のテスト」をクリックして、構文エラーがないことを確認します。エラーがある場合は修正して、再度「構文のテスト」をクリックします。

DDL

SQL Developerによってビューの作成に使用されるSQL文を確認します。変更が必要な場合は「SQL問合せ全体」タブに戻って変更します。

SQLスクリプト・ファイルにCREATE VIEW文を保存する場合は、「保存」をクリックして、場所とファイル名を指定します。

終了したら、「OK」をクリックします。

これで、ビューの作成が完了しました。基礎となる表にデータを挿入した場合、次のようにして、このビューから戻されたデータを確認できます。「接続」ナビゲータで「ビュー」を展開し、PATRONS_TRANS_VIEWを選択してから、「データ」タブをクリックします。

指定した評価を受けているすべての本を表示するプロシージャを作成するには、「PL/SQLプロシージャの作成」に進みます。

関連項目

5.7 PL/SQLプロシージャの作成

指定した評価を受けているすべての本を表示するプロシージャを作成します。入力パラメータ(1から10の範囲の数字)を指定してこのプロシージャをコールすると、指定した評価を受けているすべての本のタイトルが出力されます。

プロシージャを作成するには、(まだ接続していない場合は)このチュートリアルに使用しているスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「プロシージャ」ノードを右クリックして、「新規プロシージャ」を選択し、「PL/SQLプロシージャ作成」ダイアログ・ボックスを使用して次の情報を入力します。

オブジェクト名: list_a_rating

「OK」をクリックします。新しいプロシージャの「ソース」ウィンドウが開きます。次のプロシージャ・テキストを入力(またはコピー・アンド・ペースト)して、既存のテキストを置き換えます。 

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パラメータで)指定した評価を受けている本の行のみを戻し、ループを使用してその評価を受けている各本のタイトルを出力します。

「保存」アイコンをクリックしてプロシージャを保存します。

使用例を次に示します。LIST_A_RATINGというプロシージャを作成し、(たとえば、「表へのデータの挿入」に示すINSERT文を使用して)BOOKS表にデータを挿入した場合、次の文を使用して、10という評価を受けているすべての本を戻すとします。 

CALL list_a_rating(10);

SQL Developerでこのプロシージャを実行するには、「接続」ナビゲータ階層表示のLIST_A_RATINGを右クリックし、「実行」を選択します。「PL/SQLの実行」ダイアログ・ボックスの「PL/SQLブロック」の下で、「IN_RATING => IN_RATING」を「IN_RATING => 10」に変更し、「OK」をクリックします。「ログ」ウィンドウに次の出力が表示されます。 

All books with a rating of 10:                                                  
Moby Dick                                                                       
Software Wizardry

5.8 PL/SQLプロシージャのデバッグ

SQL Developerを使用してPL/SQLプロシージャのデバッグを実践してみるには、「PL/SQLプロシージャの作成」で作成したlist_a_ratingのようなプロシージャを、論理エラーが発生するように作成します。(コーディングも意図的に非効率的なものとし、評価が変数で表示されるようにします。)

プロシージャをデバッグするには、データベース接続に関連付けられたユーザーがDEBUG CONNECT SESSION権限とDEBUG ANY PROCEDURE権限を持っていることを確認する必要があります。

このプロシージャを作成するには、(まだ接続していない場合は)このチュートリアルに使用しているスキーマのユーザーでデータベースに接続します。左側のスキーマ階層で「プロシージャ」ノードを右クリックして、「新規プロシージャ」を選択し、「PL/SQLプロシージャ作成」ダイアログ・ボックスを使用して次の情報を入力します。

オブジェクト名: list_a_rating2

「OK」をクリックします。新しいプロシージャの「ソース」ウィンドウが開きます。次のプロシージャ・テキストを入力(またはコピー・アンド・ペースト)して、既存のテキストを置き換えます。 

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の定義に論理エラーが含まれています。このプロシージャでは、指定した評価を受けている本のタイトルのみを選択する必要がありますが、この定義では、指定した評価以下の評価を受けている本のタイトルが選択されることになります。

「保存」アイコンをクリックしてプロシージャを保存します。

このプロシージャを実行して、10という評価を受けているすべての本を表示する必要があるとします。「接続」ナビゲータの階層表示でLIST_A_RATING2を右クリックし、「実行」を選択します。「PL/SQLの実行」ダイアログ・ボックスの「PL/SQLブロック」の下で、「IN_RATING => IN_RATING」を「IN_RATING => 10」に変更し、「OK」をクリックします。「ログ」ウィンドウには、予期しない出力が表示されます(10以外の評価を受けている本を含む多くの本のタイトルが表示されます)。そのため、このプロシージャをデバッグすることにします。

プロシージャをデバッグするには、次のステップを実行します。

  1. 「LIST_A_RATING2」タブで、ツールバーの「デバッグ用にコンパイル」アイコンをクリックします。

  2. 次の2つの行のそれぞれの左側の余白(細い縦線の左側)をクリックして、2つのブレーク・ポイントを設定します。

    FETCH the_cursor INTO matching_title;
FETCH rating_cursor INTO matching_rating;

    左側の余白をクリックすると、ブレーク・ポイントの設定と設定解除が切り替わります。これらの2つの行の横をクリックすると、デバッグ・モードで実行しながら、matching_title変数およびmatching_rating変数の値を確認することができます。

  3. 「デバッグ」アイコンをクリックし、「PL/SQLの実行」ダイアログ・ボックスで「IN_RATING => IN_RATING」を「IN_RATING => 10」に変更し、「OK」をクリックします。

  4. 「表示」→「デバッガ」→「データ」をクリックし、「データ」ペインを表示します。(ヒント: MATCHING_RATINGを確認できるように「名前」列の幅を拡大します。)

  5. [F9]キーを押して(または「デバッグ」→「再開」をクリックして)実行を開始し、次のブレーク・ポイントで停止します。

  6. [F9]キーを繰り返しクリックして(または「デバッグ」→「再開」をクリックして)、各行が処理されるときのMATCHING_RATINGの値に注目します。Get Rich Really Fast というタイトルのところで最初の不正な結果が見つかります。このタイトルは、評価が1(明らかに10未満)であるにもかかわらず、選択されています。

  7. 問題を修正するための十分な情報が得られたら、デバッグ・ツールバーの「終了」アイコンをクリックします。

このデバッグ・セッションから、論理エラーを修正するには、the_cursorの定義で「rating <= :in_rating」を「rating = :in_rating」に変更する必要があることがわかります。

関連項目

5.9 問合せのためのSQLワークシートの使用

SQLワークシートを使用して、データベース接続を使用するSQL文をテストすることができます。ワークシートを表示するには、「ツール」メニューから「SQLワークシート」を選択します。「接続の選択」ダイアログ・ボックスで、チュートリアルのBOOKS、PATRONSおよびTRANSACTIONS表の作成に使用したデータベース接続を選択します。

「Enter SQL Statement」ボックスに次の文を入力します(SQLワークシートではセミコロンはオプションです)。 

SELECT author_last_name, title FROM books;

SQLキーワード(この例ではSELECTおよびFROM)が自動的にハイライト表示されていることに注目します。

SQLワークシート・ツールバーで「SQL文の実行」アイコンをクリックします。SQL文を入力した領域の下の「結果」タブに、問合せの結果が表示されます。

「SQL文の入力」ボックスに次の文を入力(またはコピー・アンド・ペースト)します。この文は、「ビューの作成」で作成したビューの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ワークシート・ツールバーで「SQL文の実行」アイコンをクリックし、問合せの結果を表示します。

SQLワークシート・ツールバーで「実行計画の実行」アイコンをクリックして、Oracle DatabaseがSQL文の実行に使用する実行計画を確認します(「説明」タブに表示されます)。この情報には、オプティマイザ方針および文の実行にかかるコストが含まれています。

関連項目:

SQLワークシートのユーザー・インタフェースの詳細は、「SQLワークシートの使用」を参照してください。

実行計画の生成および解釈方法の詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください

5.10 図書館のチュートリアル用オブジェクトを作成および使用するためのスクリプト

次の文は、「SQL Developerチュートリアル: 小規模データベースのオブジェクトの作成」のチュートリアル用に作成した(または作成する)データベース・オブジェクトを作成および使用するためのものです。これらのコマンドを確認しておくと、チュートリアルに示す図書館データベース・オブジェクトについて理解するのに役立ちます。 

-- 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);