Skip Headers
Oracle® Database 2 Day Developer's Guide
11g Release 1 (11.1)

B28843-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

3 Creating and Using Database Objects

In this chapter, you will create and use the types of database objects that were discussed in "Querying and Manipulating Data".

Note that the statements CREATE TABLE, ALTER TABLE, DROP TABLE, and so on, use an implicit commit, and cannot be rolled back.

This chapter contains the following sections:

Using Data Types

Data types associate a set of properties with values so you can use these values in the database. Depending on the data type, Oracle Database can perform different kinds of operations on the information in the database. For example, it is possible to calculate a sum of numeric values but not characters.

Oracle Database supports many kinds of data types, including the most common VARCHAR2(length), NUMBER(precision, scale), DATE, and also CHAR(length), CLOB, TIMESTAMP, and others. As you create a table, you must specify data types for each of its columns and (optionally) indicate the longest value that can be placed in the column.

Some of the data types and their properties you will use here include the following:

Creating and Using Tables

Tables are the basic unit of data storage in an Oracle database, and hold all user-accessible data. Tables are two-dimensional objects made up of vertical columns that represent the fields of the table and horizontal rows that represent the values for each record in the table.

In this section, you will create all the necessary tables and other schema objects to implement an employee performance evaluation process for the existing hr schema.

Creating a Table

To implement the employee evaluation process, you will need to establish three tables, performance_parts, evaluations, and scores.

  • The performance_parts table lists the categories of performance measurements, and the relative weight for each item.

  • The evaluations table will contain the employee's information, evaluation date, and the job, manager and department at the time of evaluation. You must preserve this information in this table because at any point in the future, the employee may change job designation, manager, or department.

  • The scores table contains the scores assigned to each performance category for each evaluation.

To create a table using SQL Developer interface:

You will create the performance_parts table using the SQL Developer graphical interface.

  1. In the Connections navigation hierarchy, click the plus sign (+) next to hr_conn to expand the list of schema objects.

  2. Right-click Tables.

  3. Select New Table.

    Description of table_create_1.gif follows
    Description of the illustration table_create_1.gif

  4. In the Create Table window, enter the following information:

    • For Schema, select HR.

    • For Name, enter PERFORMANCE_PARTS.

    Description of table_create_2.gif follows
    Description of the illustration table_create_2.gif

  5. Click the default column that was created with the table.

  6. Enter the information for the first column in the table as follows:

    • For Column Name, enter PERFORMANCE_ID.

    • For Type, enter VARCHAR2.

    • For Size, enter 2.

    Leave the value of Not Null and Primary Key properties. You will come back to this later, in "Ensuring Data Integrity".

    Description of table_create_3.gif follows
    Description of the illustration table_create_3.gif

  7. Enter information for the second column as follows:

    Click Add Column.

    • For Column Name, enter NAME.

    • For Type, enter VARCHAR2.

    • For Size, enter 80.

  8. Enter information for the third column as follows:

    Click Add Column.

    • For Column Name, enter WEIGHT.

    • For Type, enter NUMBER.

  9. Click OK.

    SQL Developer generates the new table, performance_parts.

  10. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.

    performance_parts is a new table in the hr schema, listed between locations and regions.

You just created a new table, performance_parts. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table.

In Example 3-1, you will create the evaluations table by entering the information directly in the SQL Worksheet pane.

Example 3-1 Creating a Table in SQL Script

CREATE TABLE evaluations (
  evaluation_id    NUMBER(8,0), 
  employee_id      NUMBER(6,0), 
  evaluation_date  DATE, 
  job_id           VARCHAR2(10), 
  manager_id       NUMBER(6,0), 
  department_id    NUMBER(4,0),
  total_score      NUMBER(3,0)
)

The results of the script follow.

CREATE TABLE succeeded.

You created a new table, evaluations. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table. You may need to click the Refresh icon.

In Example 3-2, you will create another table, scores, by entering the information in the SQL Worksheet pane.

Example 3-2 Creating the SCORES Table

CREATE TABLE scores (
  evaluation_id   NUMBER(8,0), 
  performance_id  VARCHAR2(2), 
  score           NUMBER(1,0)
);

The results of the statement follow.

CREATE TABLE succeed.

You created a new table, scores. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table. You may need to click the Refresh icon.

See Also:

Ensuring Data Integrity

The data in the table must satisfy the business rules that are modeled in the application. Many of these rules can be implemented through integrity constraints that use the SQL language to explicitly state what type of data values are valid for each column.

When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule, so when your application includes a SQL statement that inserts or modifies data in the table, Oracle Database automatically ensures that the constraint is satisfied. If you attempt to insert, update, or remove a row that violates a constraint, the system generates an error, and the statement is rolled back. If you attempt to apply a new constraint to a populated table, the system may generate an error if any existing row violates the new constraint.

Because Oracle Database checks that all the data in a table obeys an integrity constraint much faster than an application can, you can enforce the business rules defined by integrity constraints more reliably than by including this type of checking in your application logic.

See Also:

Understanding Types of Data Integrity Constraints

There are five basic types of integrity constraints:

  • A NOT NULL constraint ensures that the column contains data (it is not null).

  • A unique constraint ensures that multiple rows do not have the same value in the same column. This type of constraint can also be used on combination of columns, as a composite unique constraint. This constraint ignores null values.

  • A primary key constraint combines NOT NULL and UNIQUE constraints in a single declaration; it prevents multiple rows from having the same value in the same column or combination of columns, and prevents null values.

  • A foreign key constraint requires that for each value in the column on which the constraint is defined, there must be a matching value in a specified other table and column.

  • A check constraint ensures that a value satisfies a specified condition. Use check constraints when you need to enforce integrity rules based on logical expressions, such as comparisons. Oracle recommends that you never use check constraints when other types of constraints can provide the necessary checking.

Adding Integrity Constraints

You will now add different types of constraints to the tables you created in "Creating a Table".

To Add a NOT NULL Constraint Using the SQL Developer Interface:

You will add a NOT NULL constraint to the table using the SQL Developer graphical interface.

  1. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.

  2. Right-click the performance_parts table.

  3. Select Edit.

    Description of constraint_create_5.gif follows
    Description of the illustration constraint_create_5.gif

  4. In the Edit Table window, follow these steps:

  5. In the Confirmation window, click OK.

    You have now created a NOT NULL constraint for the name column of the performance_parts table.

The definition of the name column in the performance_parts table is changed to the following; note that the constraint is automatically enabled.

"NAME" VARCHAR2(80) NOT NULL ENABLE

Example 3-3 shows how you can add another NOT NULL constraint to the performance_parts table by entering the required information directly in the SQL Statement window.

Example 3-3 Adding a NOT NULL Constraint in SQL Script

ALTER TABLE performance_parts
MODIFY weight NOT NULL;

The results of the script follow.

ALTER TABLE performance_parts succeeded. 

You just created a NOT NULL constraint for column weight of the performance_parts table. If you click the SQL tab, you will see that the definition of the weight column changed. You may need to click the Refresh icon.

"WEIGHT" NUMBER NOT NULL ENABLE

To add a unique constraint using the SQL Developer interface:

You will add a unique constraint to the scores table using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.

  1. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.

  2. Right-click the scores table.

  3. Select Constraint, and then select Add Unique.

    Description of constraint_create_3.gif follows
    Description of the illustration constraint_create_3.gif

  4. In the Add Unique window, enter the following information:

    • Set the constraint name to SCORES_EVAL_PERF_UNIQUE.

    • Set Column 1 to EVALUATION_ID.

    • Set Column 2 to PERFORMANCE _ID.

    Click Apply.

    Description of constraint_create_4.gif follows
    Description of the illustration constraint_create_4.gif

  5. In the Confirmation window, click OK.

    You have now created a unique constraint for the scores table.

    The following SQL statement was added to your table definition:

    CONSTRAINT "SCORES_EVAL_PERF_UNIQUE" UNIQUE ("EVALUATION_ID", "PERFORMANCE_ID")
    

To add a primary key constraint using the SQL Developer interface:

You will add a primary key constraint to the performance_parts table using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.

  1. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.

  2. Right-click the performance_parts table.

  3. Select Constraint, and then select Add Primary Key.

    Description of constraint_create_1.gif follows
    Description of the illustration constraint_create_1.gif

  4. In the Add Primary Key window, enter the following information:

    • Set the primary key name to PERF_PERF_ID_PK.

    • Set Column 1 to PERFORMANCE_ID.

    Click Apply.

    Description of constraint_create_2.gif follows
    Description of the illustration constraint_create_2.gif

  5. In the Confirmation window, click OK.

    You have now created a primary key constraint for the performance_parts table.

The following SQL statement was added to your table definition:

CONSTRAINT "PERF_PERF_ID_PK" PRIMARY KEY ("PERFORMANCE_ID")

In Example 3-4, you will create a primary key constraint on the evaluations table by entering the required information directly in the SQL Statement window.

Example 3-4 Adding a Primary Key Constraint in SQL Script

ALTER TABLE evaluations
ADD CONSTRAINT eval_eval_id_pk PRIMARY KEY (evaluation_id);

The results of the script follow.

ALTER TABLE evaluations succeeded.

You just created a primary key eval_eval_id_pk on the evaluations table. If you click the SQL tab, you will see the following SQL statement was added to your table definition. You may need to click the Refresh icon.

CONSTRAINT "EVAL_EVAL_ID_PK" PRIMARY KEY ("EVALUATION_ID")

To add a foreign key constraint using the SQL Developer interface:

You will add two foreign key constraints to the scores table using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.

  1. In the Connections navigation hierarchy, the plus sign (+) next to Tables to expand the list of tables.

  2. Right-click the scores table.

  3. Select Constraint, and then select Add Foreign Key.

    Description of constraint_create_7.gif follows
    Description of the illustration constraint_create_7.gif

  4. In the Add Foreign Key window, enter the following information:

    • Set the foreign key name to SCORES_EVAL_FK.

    • Set Column Name to EVALUATION_ID.

    • Set Reference Table Name to EVALUATIONS.

    • Set Referencing Column to EVALUATION_ID.

    Click Apply.

    Description of constraint_create_8.gif follows
    Description of the illustration constraint_create_8.gif

  5. In the Confirmation window, click OK.

    You have now created a foreign key constraint on the evalution_id column from the evaluations table.

  6. Add another foreign key constraint by repeating steps 2 through 5, with the following parameters:

    • Set the foreign key name to SCORES_PERF_FK.

    • Set Column Name to PERFORMANCE_ID.

    • Set Reference Table Name to PERFORMANCE_PARTS.

    • Set Referencing Column to PERFORMANCE_ID.

    Click Apply.

The following SQL statements were added to your table definition:

CONSTRAINT "SCORES_EVAL_FK" FOREIGN KEY ("EVALUATION_ID")
 REFERENCES "HR"."EVALUATIONS" ("EVALUATION_ID") ENABLE
CONSTRAINT "SCORES_PERF_FK" FOREIGN KEY ("PERFORMANCE_ID")
 REFERENCES "HR"."PERFORMANCE_PARTS" ("PERFORMANCE_ID") ENABLE

In Example 3-5, you will create a foreign key constraint on the evaluations table by entering the required information directly in the SQL Statement window.

Example 3-5 Adding a Foreign Key Constraint in SQL Script

ALTER TABLE evaluations
    ADD CONSTRAINT eval_emp_id_fk FOREIGN KEY (employee_id) 
    REFERENCES employees(employee_id);

The results of the script follow.

ALTER TABLE evaluations succeeded

You have now created a foreign key constraint on the employee_id column from the employees table. If you click the SQL tab, you will see the following SQL statement was added to your table definition. You may need to click the Refresh icon.

CONSTRAINT "EVAL_EMP_ID_FK" FOREIGN KEY ("EMPLOYEE_ID")
 REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE

To add a check constraint using the SQL Developer interface:

You will add a check constraint to the scores table using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.

  1. In the Connections navigation hierarchy, the plus sign (+) next to Tables to expand the list of tables.

  2. Right-click the scores table.

  3. Select Constraint, and then select Add Check.

    Description of constraint_create_9.gif follows
    Description of the illustration constraint_create_9.gif

  4. In the Add Check window, enter the following information:

    • Set the Constraint Name to SCORE_VALID.

    • Set Check Condition to score >=0 and score <=9.

    • Set Status to ENABLE.

    Click Apply.

    Description of constraint_create_10.gif follows
    Description of the illustration constraint_create_10.gif

  5. In the Confirmation window, click OK.

    You have now created a check constraint on the score column of the scores table.

The following SQL statement was added to your table definition:

CONSTRAINT "SCORE_VALID" CHECK (score >=0 and score <=9) ENABLE

Adding Data to a Table, Modifying, and Deleting

You can use SQL Developer to enter data into tables, to edit, and to delete existing data. The following tasks will show these processes for the performance_parts table.

To add data to a table using the SQL Developer interface:

Follow these steps to add rows of data to the performance_parts table

  1. In the Connections navigation hierarchy, double-click the performance_parts table.

  2. Click the Data tab in the performance_parts table display.

  3. In the Data pane, click the New Record icon.

    Description of data_add_1.gif follows
    Description of the illustration data_add_1.gif

  4. In the new row, add the following information; you can click directly into the column, or tab between columns:

    • Set PERFORMANCE_ID to 'WM'

    • Set NAME to 'Workload Management'

    • Set WEIGHT to 0.2

    Press the Enter key.

    Description of data_add_2.gif follows
    Description of the illustration data_add_2.gif

  5. Add a second row with the following information: set PERFORMANCE_ID to 'BR, set NAME to 'Building Relationships, and set WEIGHT to 0.2.

    Press the Enter key.

  6. Add a third row with the following information: set PERFORMANCE_ID to 'CF', set NAME to 'Customer Focus', and set WEIGHT to 0.2.

    Press the Enter key.

  7. Add a fourth row with the following information: set PERFORMANCE_ID to 'CM', set NAME to 'Communication', and set WEIGHT to 0.2.

    Press the Enter key.

  8. Add a fifth row with the following information: set PERFORMANCE_ID to 'TW', set NAME to 'Teamwork', and set WEIGHT to 0.2.

    Press the Enter key.

  9. Add a sixth row with the following information: set PERFORMANCE_ID to 'RD', set NAME to 'Results Orientation', and set WEIGHT to 0.2.

    Press the Enter key.

  10. Click the Commit Changes icon.

  11. Review and close the Data Editor Log window.

    Description of data_add_3.gif follows
    Description of the illustration data_add_3.gif

  12. Review the new data in the table performance_parts.

    Description of data_add_4.gif follows
    Description of the illustration data_add_4.gif

You have added 6 rows to the performance_parts table.

To modify table data using the SQL Developer interface:

Follow these steps to change data to the performance_parts table.

  1. In the Connections navigation hierarchy, double-click the performance_parts table.

  2. Click the Data tab in the performance_parts table display.

  3. In the Data pane, in the 'Workload Management' row, click the weight value, and enter a new value for '0.3'.

    In the 'Building Relationships' row, click the weight value, and enter a new value for '0.15'.

    In the 'Customer Focus' row, click the weight value, and enter a new value for '0.15'.

    Description of data_add_5.gif follows
    Description of the illustration data_add_5.gif

  4. Press the Enter key.

  5. Click the Commit Changes icon.

  6. Review and close the Data Editor Log window.

    Description of data_add_6.gif follows
    Description of the illustration data_add_6.gif

You have now changed values in three rows of the performance_parts table.

To delete table data using the SQL Developer interface:

Imagine that in the company modeled by the hr schema, management decided that the categories Workload Management and Results Orientation had too much overlap. You will now remove the row 'Results Orientation' from the performance_parts table.

  1. In the Connections navigation hierarchy, double-click the performance_parts table.

  2. Click the Data tab in the performance_parts table display.

  3. In the Data pane, click the 'Results Orientation' row.

  4. Click the Delete Selected Row(s) icon.

    Description of data_add_7.gif follows
    Description of the illustration data_add_7.gif

  5. Click the Commit Changes icon.

  6. Review and close the Data Editor Log window.

    Description of data_add_8.gif follows
    Description of the illustration data_add_8.gif

You have now removed a row from the performance_parts table.

Indexing Tables

When you define a primary key on a table, Oracle Database implicitly creates an index on the column that contains the primary key. For example, you can confirm that an index was created for the evaluations table on its primary key, by looking at its Indexes pane.

Description of index_1.gif follows
Description of the illustration index_1.gif

In this section, you will learn how to add different types of indexes to the tables you created earlier.

To create an index using the SQL Developer interface:

Follow these steps to create a new index for the evaluations table.

  1. In the Connections navigation hierarchy, right-click the evaluations table.

  2. Select Index and then select Create Index.

    Alternatively, in the Connections navigation hierarchy, you can right-click Indexes and select New Index.

    Description of index_2.gif follows
    Description of the illustration index_2.gif

  3. In the Create Index window, enter the following parameters:

    • Ensure that the Schema is set to HR.

    • Set the Name to EVAL_JOB_IX.

    Click the Add Column Expression icon, which looks like a 'plus' sign.

    • Set the Column Name or Expression to JOB_ID.

    • Set the Order to ASC.

    Click OK.

    Description of index_3.gif follows
    Description of the illustration index_3.gif

You have now created a new index EVAL_JOB_IX on the column JOB_ID in the evaluations table. You can see this index by finding it in the list of Indexes in the Connections navigation hierarchy, or by opening the evaluations table and browsing to the Indexes tab. The following script is the equivalent SQL statement for creating this index.

CREATE INDEX eval_job_ix ON evaluations (job_id ASC) NOPARALLEL;

To modify an index using SQL Developer interface:

Follow these steps to reverse the sort order of the EVAL_JOB_IX index.

  1. In the Connections navigation hierarchy, the plus sign (+) to expand Indexes.

  2. Right-click EVAL_JOB_IX, and select Edit.

    Description of index_4.gif follows
    Description of the illustration index_4.gif

  3. In the Edit Index window, change Order to DESC.

    Click OK.

You changed the index. The following script is the equivalent SQL statement for creating this index:

DROP INDEX eval_job_id;
CREATE INDEX eval_job_ix ON evaluations (job_id DESC) NOPARALLEL;

To delete an index using SQL Developer interface:

Following steps to delete the EVAL_JOB_IX index.

  1. In the Connections navigation hierarchy, the plus sign (+) to expand Indexes.

  2. Right-click EVAL_JOB_IX, and select Drop.

    Description of index_5.gif follows
    Description of the illustration index_5.gif

  3. In the Drop window, click Apply.

  4. In the Confirmation window, click OK.

You deleted the index EVAL_JOB_IX. The following script is the equivalent SQL statement for dropping this index.

DROP INDEX "HR"."EVAL_JOB_ID";

See Also:

Dropping Tables

Sometimes it becomes necessary to delete a table and all its contents from your schema. To accomplish this, you must use the SQL statement DROP TABLE. You will use the tables that you already created to learn other concepts, so create a simple table that you can subsequently delete by running the following script in the SQL Statement window:

CREATE TABLE temp_table(
  id NUMBER(1,0),
  name VARCHAR2(10)
);

To delete a table using the SQL Developer interface:

Follow these steps to delete TEMP_TABLE from the hr schema.

  1. In the Connections navigation hierarchy, right-click TEMP_TABLE.

  2. Select Table, and then select Drop.

    Description of index_6.gif follows
    Description of the illustration index_6.gif

  3. In the Drop window, click Apply.

  4. In the Confirmation window, click OK.

You deleted the table TEMP_TABLE. The following script is the equivalent SQL statement for dropping this table.

DROP TABLE "HR"."TEMP_TABLE";

See Also:

Using Views

Views are logical tables based on one or more tables or views. Views are particularly useful if your business needs include frequent access to information that is stored in several different tables.

Creating a View

The standard syntax for creating a view follows:

CREATE VIEW view_name AS query;

To create a view using the SQL Developer interface:

Follow these steps to delete create a new view from the hr schema.

  1. In the Connections navigation hierarchy, right-click Views.

  2. Select New View.

    Description of view_1.gif follows
    Description of the illustration view_1.gif

  3. In the Create View window, enter the following parameters:

    • Ensure that Schema is set to HR.

    • Set Name to SALESFORCE.

    • Set the SQL Query to the following:

      SELECT first_name || ' ' || last_name "Name", salary*12 "Annual Salary"
      FROM employees
      WHERE department_id = 80
      
  4. In SQL Parse Results, click Test Syntax.

    Description of view_2.gif follows
    Description of the illustration view_2.gif

  5. Click OK.

You created a new view. The equivalent SQL statement for creating this view follows:

CREATE VIEW salesforce AS 
SELECT first_name || ' ' || last_name "Name", 
 salary*12 "Annual Salary"
FROM employees
WHERE department_id = 80;

In Example 3-6, you will create a view of all employees in the company and their work location, similar to the query you used in "Using Character Functions".

Example 3-6 Creating a View in SQL Script

CREATE VIEW emp_locations AS
SELECT e.employee_id,
  e.last_name || ', ' || e.first_name name,
  d.department_name department,
  l.city city, 
  c.country_name country
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;

The results of the script follow.

CREATE VIEW succeeded.

You have now created new view that relies on information in 4 separate tables, or a 4-way JOIN. In the Connections navigation hierarchy, if you click the 'plus' sign next to Views, you will see emp_locations.

Updating a View

To change the properties of a view in SQL Developer interface:

You will change the salesforce view by adding to it the employees in the Marketing department, and then rename the view to sales_marketing.

  1. In the Connections navigation hierarchy, right-click the salesforce view.

  2. Select Edit.

    Description of view_3.gif follows
    Description of the illustration view_3.gif

  3. In the Edit View window, change the SQL Query by adding the following to the last line: 'OR department_id = 20'.

    Click Test Syntax.

    Click OK.

    Description of view_4.gif follows
    Description of the illustration view_4.gif

  4. To rename the view, right-click salesforce and select Rename.

    Description of view_5.gif follows
    Description of the illustration view_5.gif

  5. In the Rename window, set New View Name to sales_marketing.

    Click Apply.

    Description of view_6.gif follows
    Description of the illustration view_6.gif

  6. In the Confirmation window, click OK.

You changed the view. The equivalent SQL statements for changing and renaming the view are:

CREATE OR REPLACE VIEW salesforce AS query;
RENAME "SALESFORCE" to SALES_MARKETING;

Dropping a View

To drop a view using the SQL Developer interface:

You will use the DROP VIEW statement to delete the sales_marketing view.

  1. In the Connections navigation hierarchy, right-click the sales_marketing view.

  2. Select Drop.

    Description of view_7.gif follows
    Description of the illustration view_7.gif

  3. In the Drop window, click Apply.

  4. In the Confirmation window, click OK.

You deleted the view. The equivalent SQL statement for dropping the view is:

DROP VIEW sales_marketing;

See Also:

Using Sequences

Sequences are database objects that generate unique sequential values, which are very useful when you need unique primary keys. The hr schema already has three such sequences: departments_seq, employees_seq, and locations_seq.

The sequences are used through these pseudocolumns:

Note that a sequence is not connected to any other object, except for conventions of use. When you plan to use a sequence to populate the primary key of a table, Oracle recommends that you use a naming convention to link the sequence to that table. Throughout this discussion, the naming convention for such sequences is table_name_seq.

Creating a Sequence

You can create a sequence in the SQL Developer Interface, or using the SQL Statement window.

To create a sequence using the SQL Developer interface:

The following steps will create a sequence, evaluations_seq, that you can use for the primary key of the evaluations table.

  1. In the Connections navigation hierarchy, right-click Sequences.

  2. Select New Sequence.

    Description of sequence_1.gif follows
    Description of the illustration sequence_1.gif

  3. In the New Sequence window, enter the following parameters:

    • Ensure that Schema is set to HR.

    • Set Name to EVALUATIONS_SEQ.

    In the Properties tab:

    • Set Increment to 1.

    • Set Start with to 1.

    • Check Order.

    Click OK.

    Description of sequence_2.gif follows
    Description of the illustration sequence_2.gif

You have now created a sequence that can be used for the primary key of the evaluations table. If you click '+' to expand the Sequence tree, you can see new sequence. The equivalent SQL statement is:

CREATE SEQUENCE evaluations_seq INCREMENT BY 1 START WITH 1 ORDER;

In Example 3-7, you will create another sequence by entering the required information directly in the SQL Statement window.

Example 3-7 Creating a Sequence Using SQL Script

CREATE SEQUENCE test_seq INCREMENT BY 5 START WITH 5 ORDER;

The results of the script follow.

CREATE SEQUENCE succeeded.

See Also:

Dropping a Sequence

To delete a sequence, you must use the SQL statement DROP SEQUENCE. To see how a sequence can be deleted in SQL Developer, you can use the test_seq sequence you created earlier. If the new sequence does not appear in the Connections hierarchy navigator, click the refresh icon.

To drop a sequence:

Follow these steps to drop a sequence.

  1. In the Connections navigator, right-click the test_seq sequence.

    Description of sequence_3.gif follows
    Description of the illustration sequence_3.gif

  2. In the Drop window, click Apply.

    Description of sequence_4.gif follows
    Description of the illustration sequence_4.gif

  3. In the Confirmation window, click OK.

You have now deleted the sequence test_seq. The equivalent SQL statement follows:

DROP SEQUENCE "HR"."TEST_SEQ";

See Also:

Using Synonyms

A synonym is an alias for any schema object and can be used to simplify SQL statements or even obscure the names of actual database objects for security purposes. Additionally, if a table is renamed in the database (departments to divisions), you could create a departments synonym and continue using your application code as before.

To create a synonym using the SQL Developer interface:

The following steps will create a synonym, positions, that you can use in place of the jobs schema object.

  1. In the Connections navigation hierarchy, right-click Synonyms.

  2. Select New Synonym.

    Description of synonym_1.gif follows
    Description of the illustration synonym_1.gif

  3. In the New Synonym window, set the following parameters:

    • Ensure that Schema is set to HR.

    • Set Name to POSITIONS.

    In the Properties tab:

    • Select Object Based. This means that the synonym refers to a specific schema object, such as a table, a view, a sequence, and so on.

    • Set Object Based to JOBS.

    Click OK.

    Description of synonym_2.gif follows
    Description of the illustration synonym_2.gif

You created a synonym positions for the jobs table. The equivalent SQL statement follows:

CREATE SYNONYM positions FOR jobs;

In Example 3-8, you use the new positions synonym in place of the jobs table name.

Example 3-8 Using a Synonym

SELECT first_name || ' ' || last_name "Name", p.job_title "Position"
FROM employees e, positions p
WHERE e.job_id = p.job_id
ORDER BY last_name;

The results of the query appear.

Name                   Position
---------------------  -------------------------
Ellen Abel             Sales Representative
Sundar Ande            Sales Representative
Mozhe Atkinson         Stock Clerk
David Austin           Programmer
...
197 rows selected

To drop a synonym:

Follow these steps to drop the positions synonym.

  1. In the Connections navigator, right-click the positions synonym.

  2. Select Drop.

    Description of synonym_4.gif follows
    Description of the illustration synonym_4.gif

  3. In the Drop window, click Apply.

    Description of synonym_5.gif follows
    Description of the illustration synonym_5.gif

  4. In the Confirmation window, click OK.

You deleted synonym positions. The equivalent SQL statement follows:

DROP SYNONYM positions;

See Also: