SQL Developer Tutorial: Creating Objects for a Small Database

In this tutorial, you will use SQL Developer to create objects for a simplified library database, which will include tables for books, patrons (people who have library cards), and transactions (checking a book out, returning a book, and so on).

The tables are deliberately oversimplified for this tutorial. They would not be adequate for any actual public or organizational library. For example, this library contains only books (not magazines, journals, or other document formats), and it can contain no more than one copy of any book.

You will perform the following major steps:

1. Create a Table (BOOKS).

2. Create a Table (PATRONS).

3. Create a Table (TRANSACTIONS).

4. Create a Sequence.

5. Insert Data into the Tables.

6. Create a View.

7. Create a PL/SQL Procedure.

8. Debug a PL/SQL Procedure (optional).

9. Use the SQL Worksheet for Queries (optional).

Related Topics

Section 4.10, "Script for Creating and Using the Library Tutorial Objects"

Chapter 1, "SQL Developer Concepts and Usage"

Note: Other SQL Developer tutorials, including Oracle By Example (OBE) lessons, are available from the Start Page. If the tab for that page is not visible, click Help, then Start Page.

Note: To delete the objects that you create for this tutorial, you can use the DROP statements at the beginning of the script in Section 4.10,

"Script for Creating and Using the Library Tutorial Objects".

Create a Table (BOOKS)

Section 1.3, "Database Objects"

4.1 Create a Table (BOOKS)

The BOOKS table contains a row for each book in the library. It includes columns of character and number types, a primary key, a unique constraint, and a check constraint. You will use the Create Table dialog box to create the table declaratively;

the table that you create will be essentially the same as if you had entered the following statement using the SQL Worksheet:

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

To create the BOOKS table, connect to the database as the user in the schema you want to use for this tutorial. Right-click the Tables node in the schema hierarchy on the left side, select New Table, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it. Be sure that the Advanced box is not checked when you start creating the table.)

For detailed information about the table dialog box and its tabs, see Section 5.52,

"Create Table (quick creation)" and Section 5.53, "Create/Edit Table (with advanced options)".

Schema: Specify your current schema as the schema in which to create the table.

Name: BOOKS

Create the table columns using the following information. After creating each column except the last one (rating), click Add Column to add the next column. (If you

accidentally click OK instead of Add Column, right-click the BOOKS table in the Connections navigator display, select Edit, and continue to add columns.)

After you have entered the last column (rating), check Advanced (next to Schema).

This displays a pane for more table options. For this table, you will use the Unique Constraints and Check Constraints panes.

Column Name Type Size Other Information and Notes

book_id VARCHAR2 20 Primary Key (Automatically checks Not Null;

an index is also created on the primary key column. This is the Dewey code or other book identifier.)

title VARCHAR2 50 Not Null

author_last_name VARCHAR2 30 Not Null author_first_name VARCHAR2 30

rating NUMBER (Librarian’s personal rating of the book, from 1 (poor) to 10 (great))

Create a Table (PATRONS)

Unique Constraints pane

Click Add to add a unique constraint for the table, namely, that the combination of author_last_name and title must be unique within the table. (This is deliberately oversimplified, since most major libraries will have allow more than one copy of a book in their holdings. Also, the combination of last name and title is not always a

"foolproof" check for uniqueness, but it is sufficient for this simple scenario.) Name: author_title_unique

In Available Columns, double-click TITLE and then AUTHOR_LAST_NAME to move them to Selected Columns.

Check Constraints pane

Click Add to add a check constraint for the table, namely, that the rating column value is optional (it can be null), but if a value is specified, it must be a number from 1 through 10. You must enter the condition using SQL syntax that is valid in a CHECK clause (but do not include the CHECK keyword or enclosing parentheses for the entire CHECK clause text).

Name: rating_1_to_10

Condition: rating is null or (rating >= 1 and rating <= 10) Click OK to finish creating the table.

Go to Section 4.2, "Create a Table (PATRONS)" to create the next table.

4.2 Create a Table (PATRONS)

The PATRONS table contains a row for each patron who can check books out of the library (that is, each person who has a library card). It includes an object type (MDSYS.SDO_GEOMETRY) column. You will use the Create Table dialog box to create the table declaratively; the table that you create will be essentially the same as if you had entered the following statement using the SQL Worksheet:

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

The use of single city_state_zip column for all that information is not good database design; it is done here merely to simplify your work in the tutorial.

The location column (Oracle Spatial geometry representing the patron’s geocoded address) is merely to show the use of a complex (object) type.

To create the PATRONS table, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Tables node in the schema hierarchy on the left side, select New Table, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it.

Be sure that the Advanced box is not checked when you start creating the table.) Schema: Specify your current schema as the schema in which to create the table.

Name: PATRONS

Create a Table (TRANSACTIONS)

Create most of the table columns using the following information. After creating each column except the city_state_zip column, click Add Column to add the next column.

(If you accidentally click OK instead of Add Column, right-click the PATRONS table in the Connections navigator display, select Edit, and continue to add columns.)

The last column in the table (location) requires a complex data type, for which you must use the Columns tab with advanced options. Check Advanced (next to Schema).

This displays a pane for selecting more table options.

In the Columns pane, click the city_state_zip column name, and click the Add Column (+) icon to add the following as the last column in the table.

After you have entered the last column (location), click OK to finish creating the table.

Go to Section 4.3, "Create a Table (TRANSACTIONS)" to create the next table.

4.3 Create a Table (TRANSACTIONS)

The TRANSACTIONS table contains a row for each transaction involving a patron and a book (for example, someone checking a book out or returning a book). It includes two foreign key columns. You will use the Create Table dialog box to create the table declaratively; the table that you create will be essentially the same as if you had entered the following statement using the SQL Worksheet:

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

To create the TRANSACTIONS table, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Tables node in the schema hierarchy on the left side, select New Table, and enter the Column Name Type Size Other Information and Notes

patron_id NUMBER Primary Key. (Unique patron ID number, with values to be created using a sequence that you will create)

last_name VARCHAR2 30 Not Null

first_name VARCHAR2 30 street_address VARCHAR2 30 city_state_zip VARCHAR2 30

Column Name Type Other Information and Notes

location Complex type Schema: MDSYS Type: SDO_GEOMETRY

(Oracle Spatial geometry object representing the patron’s geocoded address)

Create a Table (TRANSACTIONS)

following information. (If a tab or field is not mentioned, do not enter anything for it.

Be sure that the Advanced box is not checked when you start creating the table.) Schema: Specify your current schema as the schema in which to create the table.

Name: TRANSACTIONS

Create the table columns using the following information. After creating each column except the last one (transaction_type), click Add Column to add the next column. (If you accidentally click OK instead of Add Column, right-click the TRANSACTIONS table in the Connections navigator display, select Edit, and continue to add columns.)

After you have entered the last column (transaction_type), check Advanced (next to Schema). This displays a pane for selecting more table options. For this table, you will use the Column Sequences and Foreign Keys panes.

Column Sequences pane

You have already specified TRANSACTION_ID as the primary key, and you will use this pane only to specify that the primary key column values are to be populated automatically. This convenient approach uses a trigger and a sequence (both created automatically by SQL Developer), and ensures that each transaction ID value is unique.

Column: TRANSACTION_ID Sequence: New Sequence

Trigger: TRANSACTIONS_TRG (The default; a before-insert trigger with this name will be created automatically.)

Foreign Keys tab