This chapter contains:
The statements that create, change, and drop schema objects are data definition language (DDL) statements. Before and after a DDL statement, Oracle Database Express Edition issues an implicit COMMIT
statement; therefore, you cannot roll back a DDL statement.
Note:
When creating schema objects, you must observe the schema object naming rules in Oracle Database SQL Language Reference.In the SQL*Plus environment, you can enter a DDL statement after the SQL>
prompt.
In the SQL Developer environment, you can enter a DDL statement in the Worksheet. Alternatively, you can use SQL Developer tools to create, change, and drop objects.
Some DDL statements that create schema objects have an optional OR
REPLACE
clause, which allows a statement to replace an existing schema object with another that has the same name and type. When SQL Developer generates code for one of these statements, it always includes the OR
REPLACE
clause.
To see the effect of a DDL statement in SQL Developer, you might have to select the schema object type of the newly created object in the Connections frame and then click the Refresh icon.
See Also:
Oracle Database SQL Language Reference for more information about DDL statements
Tables are the basic units of data storage in Oracle Database Express Edition. Tables hold all user-accessible data. Each table contains rows that represent individual data records. Rows are composed of columns that represent the fields of the records.
This section contains:
Note:
To do the tutorials in this document, you must be connected to Oracle Database Express Edition as the userHR
from SQL Developer.See Also:
"Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer"
Oracle SQL Developer User's Guide for a SQL Developer tutorial that includes creating and populating tables
Oracle Database Concepts for general information about tables
When you create a table, you must specify the SQL data type for each column. The data type of a column determines what values the column can contain. For example, a column of type DATE
can contain the value '01-MAY-05'
, but it cannot contain the numeric value 2 or the character value 'shoe'
. SQL data types fall into two categories: built-in and user-defined. (PL/SQL has additional data types—see "About PL/SQL Data Types".)
See Also:
Oracle Database SQL Language Reference for a summary of built-in SQL data types
Oracle Database Concepts for introductions to each of the built-in SQL data types
Oracle Database SQL Language Reference for more information about user-defined data types
To create tables, use either the SQL Developer tool Create Table or the DDL statement CREATE
TABLE
. This section shows how to use both of these ways to create these tables, which will contain data about employee evaluations:
PERFORMANCE_PARTS
, which contains the categories of employee performance that are evaluated and their relative weights
EVALUATIONS
, which contains employee information, evaluation date, job, manager, and department
SCORES
, which contains the scores assigned to each performance category for each evaluation
These tables appear in many tutorials and examples in this document.
This section contains:
This tutorial shows how to create the PERFORMANCE_PARTS
table using the Create Table tool.
To create the PERFORMANCE_PARTS table using the Create Table tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, right-click Tables.
In the list of choices, click New Table.
The Create Table window opens, with default values for a new table, which has only one row.
For Schema, accept the default value, HR
.
For Name, enter PERFORMANCE_PARTS
.
In the default row:
For PK (primary key), accept the default option, deselected.
For Column Name, enter PERFORMANCE_ID
.
For Type, accept the default value, VARCHAR2
.
For Size, enter 2
.
For Not Null, accept the default option, deselected.
Click Add Column.
For Column Name, enter NAME
.
For Type, accept the default value, VARCHAR2
.
For Size, enter 80
.
Click Add Column.
For Column Name, enter WEIGHT
.
For Type, select NUMBER
from the menu.
Click OK.
The table PERFORMANCE_PARTS
is created. Its name appears under Tables in the Connections frame.
To see the CREATE
TABLE
statement for creating this table, select PERFORMANCE_PARTS
and click the tab SQL.
See Also:
Oracle SQL Developer User's Guide for more information about using SQL Developer to create tablesThis section shows how to use the CREATE
TABLE
statement to create the EVALUATIONS
and SCORES
tables.
The CREATE
TABLE
statement in Example 4-1 creates the EVALUATIONS
table.
Example 4-1 Creating the EVALUATIONS Table with CREATE TABLE
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) );
Result:
Table created.
The CREATE
TABLE
statement in Example 4-2 creates the SCORES
table.
Example 4-2 Creating the SCORES Table with CREATE TABLE
CREATE TABLE SCORES ( EVALUATION_ID NUMBER(8,0), PERFORMANCE_ID VARCHAR2(2), SCORE NUMBER(1,0) );
Result:
Table created.
In SQL Developer, in the Connections frame, if you expand Tables, you can see the tables EVALUATIONS
and SCORES
.
To ensure that the data in your tables satisfies the business rules that your application models, you can use constraints, application logic, or both.
Constraints restrict the values that columns can have. Trying to change the data in a way that violates a constraint causes an error and rolls back the change. Trying to add a constraint to a populated table causes an error if existing data violates the constraint.
Tip:
Wherever possible, use constraints instead of application logic. Oracle Database Express Edition checks that all data obeys constraints much faster than application logic can.Constraints can be enabled and disabled. By default, they are created in the enabled state.
This section contains:
See Also:
Oracle Database Concepts for additional general information about constraints
Oracle Database SQL Language Reference for syntactic information about constraints
Oracle Database Advanced Application Developer's Guide for information about enabling and disabling constraints
Not Null, which prevents a value from being null
In the EMPLOYEES
table, the column LAST_NAME
has the NOT
NULL
constraint, which enforces the business rule that every employee must have a last name.
Unique, which prevents multiple rows from having the same value in the same column or combination of columns, but allows some values to be null
In the EMPLOYEES
table, the column EMAIL
has the UNIQUE
constraint, which enforces the business rule that an employee can have no email address, but cannot have the same email address as another employee.
Primary Key, which is a combination of NOT
NULL
and UNIQUE
In the EMPLOYEES
table, the column EMPLOYEE_ID
has the PRIMARY
KEY
constraint, which enforces the business rule that every employee must have a unique employee identification number.
Foreign Key, which requires values in one table to match values in another table
In the EMPLOYEES
table, the column JOB_ID
has a FOREIGN
KEY
constraint that references the JOBS
table, which enforces the business rule that an employee cannot have a JOB_ID
that is not in the JOBS
table.
Check, which requires that a value satisfy a specified condition
The EMPLOYEES
table does not have CHECK
constraints. However, suppose that EMPLOYEES
needs a new column, EMPLOYEE_AGE
, and that every employee must be at least 18. The constraint CHECK
(EMPLOYEE_AGE
>=
18)
enforces the business rule.
Tip:
Use check constraints only when other constraint types cannot provide the necessary checking.REF, which further describes the relationship between a REF
column and the object that it references
A REF
column references an object in another object type or in a relational table.
For information about REF constraints, see Oracle Database Concepts.
See Also:
Oracle Database Concepts for additional general information about constraint types
Oracle Database SQL Language Reference for syntactic information about constraints
To add constraints to existing tables, use either SQL Developer tools or the DDL statement ALTER
TABLE
. This section shows how to use both of these ways to add constraints to the tables created in "Creating Tables".
This tutorial has several procedures. The first procedure uses the Edit Table tool to add a Not Null constraint to the NAMES
column of the PERFORMANCE_PARTS
table. The remaining procedures show how to use other tools to add constraints; however, you could add the same constraints using the Edit Table tool.
Note:
After any step of the tutorial, you can view the constraints that a table has:In the Connections frame, select the name of the table.
In the right frame, click the tab Constraints.
For more information about viewing table properties and data, see "Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer".
To add a Not Null constraint using the Edit Table tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Tables.
In the list of tables, right-click PERFORMANCE_PARTS.
In the list of choices, click Edit.
In the Edit Table window, click the column NAME.
Select the property Not Null.
Click OK.
The Not Null constraint is added to the NAME
column of the PERFORMANCE_PARTS
table.
The following procedure uses the ALTER
TABLE
statement to add a Not Null constraint to the WEIGHT
column of the PERFORMANCE_PARTS
table.
To add a Not Null constraint using the ALTER TABLE statement:
If a pane with the tab hr_conn
is there, select it. Otherwise, click the icon SQL Worksheet, as in "Running Queries in SQL Developer".
In the Worksheet pane, type this statement:
ALTER TABLE PERFORMANCE_PARTS MODIFY WEIGHT NOT NULL;
Click the icon Run Statement.
The statement runs, adding the Not Null constraint to the WEIGHT
column of the PERFORMANCE_PARTS
table.
The following procedure uses the Add Unique tool to add a Unique constraint to the SCORES
table.
To add a Unique constraint using the Add Unique tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Tables.
In the list of tables, right-click SCORES.
In the list of choices, select Constraint.
In the list of choices, click Add Unique.
In the Add Unique window:
For Constraint Name, enter SCORES_EVAL_PERF_UNIQUE
.
For Column 1, select EVALUATION_ID
from the menu.
For Column 2, select PERFORMANCE_ID
from the menu.
Click Apply.
In the Confirmation window, click OK.
A unique constraint named SCORES_EVAL_PERF_UNIQUE
is added to the SCORES
table.
The following procedure uses the Add Primary Key tool to add a Primary Key constraint to the PERFORMANCE_ID
column of the PERFORMANCE_PARTS
table.
To add a Primary Key constraint using the Add Primary Key tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Tables.
In the list of tables, right-click PERFORMANCE_PARTS.
In the list of choices, select Constraint.
In the list of choices, click Add Primary Key.
In the Add Primary Key window:
For Primary Key Name, enter PERF_PERF_ID_PK
.
For Column 1, select PERFORMANCE_ID
from the menu.
Click Apply.
In the Confirmation window, click OK.
A primary key constraint named PERF_PERF_ID_PK
is added to the PERFORMANCE_ID
column of the PERFORMANCE_PARTS
table.
The following procedure uses the ALTER
TABLE
statement to add a Primary Key constraint to the EVALUATION_ID
column of the EVALUATIONS
table.
To add a Primary Key constraint using the ALTER TABLE statement:
If a pane with the tab hr_conn
is there, select it. Otherwise, click the icon SQL Worksheet, as in "Running Queries in SQL Developer".
In the Worksheet pane, type this statement:
ALTER TABLE EVALUATIONS ADD CONSTRAINT EVAL_EVAL_ID_PK PRIMARY KEY (EVALUATION_ID);
Click the icon Run Statement.
The statement runs, adding the Primary Key constraint to the EVALUATION_ID
column of the EVALUATIONS
table.
The following procedure uses the Add Foreign Key tool to add two Foreign Key constraints to the SCORES
table.
To add two Foreign Key constraints using the Add Foreign Key tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Tables.
In the list of tables, right-click SCORES.
In the list of choices, select Constraint.
In the list of choices, click Add Foreign Key.
In the Add Foreign Key window:
For Constraint Name, enter SCORES_EVAL_FK
.
For Column Name, select EVALUATION_ID
from the menu.
For References Table Name, select EVALUATIONS
from the menu.
For Referencing Column, select EVALUATION_ID
from the menu.
Click Apply.
In the Confirmation window, click OK.
A foreign key constraint named SCORES_EVAL_FK
is added to the EVALUTION_ID
column of the SCORES
table, referencing the EVALUTION_ID
column of the EVALUATIONS
table.
The following steps add another foreign key constraint to the SCORES
table.
In the list of tables, right-click SCORES.
In the list of tables, select Constraint.
In the list of choices, click Add Foreign Key.
The Add Foreign Key window opens.
In the Add Foreign Key window:
For Constraint Name, enter SCORES_PERF_FK
.
For Column Name, select PERFORMANCE_ID
from the menu.
For Reference Table Name, select PERFORMANCE_PARTS
from the menu.
For Referencing Column, select PERFORMANCE_ID
from the menu.
Click Apply.
In the Confirmation window, click OK.
A foreign key constraint named SCORES_PERF_FK
is added to the EVALUTION_ID
column of the SCORES
table, referencing the EVALUTION_ID
column of the EVALUATIONS
table.
The following procedure uses the ALTER
TABLE
statement to add a Foreign Key constraint to the EMPLOYEE_ID
column of the EVALUATIONS
table, referencing the EMPLOYEE_ID
column of the EMPLOYEES
table.
To add a Foreign Key constraint using the ALTER TABLE statement:
If a pane with the tab hr_conn
is there, select it. Otherwise, click the icon SQL Worksheet, as in "Running Queries in SQL Developer".
In the Worksheet pane, type this statement:
ALTER TABLE EVALUATIONS ADD CONSTRAINT EVAL_EMP_ID_FK FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES (EMPLOYEE_ID);
Click the icon Run Statement.
The statement runs, adding the Foreign Key constraint to the EMPLOYEE_ID
column of the EVALUATIONS
table, referencing the EMPLOYEE_ID
column of the EMPLOYEES
table.
The following procedure uses the Add Check tool to add a Check constraint to the SCORES
table.
To add a Check constraint using the Add Check tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Tables.
In the list of tables, right-click SCORES.
In the list of choices, select Constraint.
In the list of choices, click Add Check.
In the Add Check window:
For Constraint Name, enter SCORE_VALID
.
For Check Condition, enter score
>=
0
and
score
<=
9
.
For Status, accept the default, ENABLE
.
Click Apply.
In the Confirmation window, click OK.
A Check constraint named SCORE_VALID
is added to the SCORES
table.
See Also:
Oracle Database SQL Language Reference for more information about the ALTER
TABLE
statement
Oracle SQL Developer User's Guide for information about adding constraints to a table when you create it with SQL Developer
Oracle Database SQL Language Reference for information about adding constraints to a table when you create it with the CREATE
TABLE
statement
This tutorial shows how to use the Insert Row tool to add six populated rows to the PERFORMANCE_PARTS
table.
To add rows to the PERFORMANCE_PARTS table using the Insert Row tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Tables.
In the list of tables, select PERFORMANCE_PARTS.
In the right frame, click the tab Data.
The Data pane appears, showing the names of the columns of the PERFORMANCE_PARTS
table and no rows.
In the Data pane, click the icon Insert Row.
A new row appears, with empty columns. A green border around the row number indicates that the insertion has not been committed.
Click the cell under the column heading PERFORMANCE_ID
.
Type the value of PERFORMANCE_ID
: WM
Either press the key Tab or click the cell under the column heading NAME
.
Type the value of NAME
: Workload
Management
Either press the key Tab or click the cell under the column heading WEIGHT
.
Type the value of WEIGHT
: 0.2
Press the key Enter.
Add and populate a second row by repeating steps 5 through 12 with these values:
For PERFORMANCE_ID
, type BR
.
For NAME
, type Building Relationships
.
For WEIGHT
, type 0.2
.
Add and populate a third row by repeating steps 5 through 12 with these values:
For PERFORMANCE_ID
, type CF
.
For NAME
, type Customer Focus
.
For WEIGHT
, type 0.2
.
Add and populate a fourth row by repeating steps 5 through 12 with these values:
For PERFORMANCE_ID
, type CM
.
For NAME
, type Communication
.
For WEIGHT
, type 0.2
.
Add and populate a fifth row by repeating steps 5 through 12 with these values:
For PERFORMANCE_ID
, type TW
.
For NAME
, type Teamwork
.
For WEIGHT
, type 0.2
.
Add and populate a sixth row by repeating steps 5 through 12, using these values:
For PERFORMANCE_ID
, type RO
.
For NAME
, type Results Orientation
.
For WEIGHT
, type 0.2
.
Click the icon Commit Changes.
The green borders around the row numbers disappear.
Under the Data pane is the label Messages - Log.
Check the Messages - Log pane for the message "Commit Successful".
In the Data Pane, check the new rows.
See Also:
"About the INSERT Statement"This tutorial shows how to change three of the WEIGHT
values in the PERFORMANCE_PARTS
table (populated in "Tutorial: Adding Rows to Tables with the Insert Row Tool") in the Data pane.
To change data in the PERFORMANCE_PARTS table using the Data pane:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Tables.
In the list of tables, select PERFORMANCE_PARTS.
In the right frame, click the tab Data.
In the Data Pane, in the row where NAME
is Workload
Management
:
Click the WEIGHT
value.
Enter the value 0.3
.
Press the key Enter.
An asterisk appears to the left of the row number to indicate that the change has not been committed.
In the row where NAME
is Building
Relationships
:
Click the WEIGHT
value.
Enter the value 0.15
.
Press the key Enter.
An asterisk appears to the left of the row number to indicate that the change has not been committed.
In the row where NAME
is Customer
Focus
:
Click the WEIGHT
value.
Enter the value 0.15
.
Press the key Enter.
An asterisk appears to the left of the row number to indicate that the change has not been committed.
Click the icon Commit Changes.
The asterisks to the left of the row numbers disappear.
Under the Data pane, check the Messages - Log pane for the message "Commit Successful".
In the Data Pane, check the new data.
See Also:
"About the UPDATE Statement"This tutorial shows how to use the Delete Selected Row(s) tool to delete a row from the PERFORMANCE_PARTS
table (populated in "Tutorial: Adding Rows to Tables with the Insert Row Tool").
To delete row from PERFORMANCE_PARTS using Delete Selected Row(s) tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Tables.
In the list of tables, select PERFORMANCE_PARTS.
In the right frame, click the tab Data.
In the Data pane, click the row where NAME
is Results
Orientation
.
Click the icon Delete Selected Row(s).
A red border appears around the row number to indicate that the deletion has not been committed.
Click the icon Commit Changes.
The row is deleted.
Under the Data pane, check the Messages - Log pane for the message "Commit Successful".
Note:
If you delete every row of a table, the empty table still exists. To delete a table, see "Dropping Tables".See Also:
"About the DELETE Statement"You can create indexes on one or more columns of a table to speed SQL statement execution on that table. When properly used, indexes are the primary means of reducing disk input/output (I/O).
When you define a primary key on a table:
If an existing index starts with the primary key columns, then Oracle Database Express Edition uses that existing index for the primary key. The existing index need not be Unique.
For example, if you define the primary key (A, B), Oracle Database Express Edition uses the existing index (A, B, C).
If no existing index starts with the primary key columns and the constraint is immediate, then Oracle Database Express Edition creates a Unique index on the primary key.
If no existing index starts with the primary key columns and the constraint is deferrable, then Oracle Database Express Edition creates a non-Unique index on the primary key.
For example, in "Tutorial: Adding Constraints to Existing Tables", you added a Primary Key constraint to the EVALUATION_ID
column of the EVALUATIONS
table. Therefore, if you select the EVALUATIONS
table in the SQL Developer Connections frame and click the Indexes tab, the Indexes pane shows a Unique index on the EVALUATION_ID
column.
This section contains:
See Also:
For more information about indexes:To create an index, use either the SQL Developer tool Create Index or the DDL statement CREATE
INDEX
.
This tutorial shows how to use the Create Index tool to add an index to the EVALUATIONS
table created in Example 4-1. The equivalent DDL statement is:
CREATE INDEX EVAL_JOB_IX ON EVALUATIONS (JOB_ID ASC) NOPARALLEL;
To add an index to the EVALUATIONS table using the Create Index tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Tables.
In the list of tables, right-click EVALUATIONS.
In the list of choices, select Index.
In the list of choices, select Create Index.
In the Create Index window:
For Schema, accept the default, HR
.
For Name, type EVAL_JOB_IX
.
If the Definition pane does not show, select the tab Definition.
In the Definition pane, for Index Type, select Unique
from the menu.
Click the icon Add Expression.
The Expression EMPLOYEE_ID
with Order <Not Specified>
appears.
Over EMPLOYEE_ID
, type JOB_ID
.
For Order, select ASC
(ascending) from the menu.
Click OK.
Now the EVALUATIONS
table has an index named EVAL_JOB_IX
on the column JOB_ID
.
To change an index, use either the SQL Developer tool Edit Index or the DDL statements DROP
INDEX
and CREATE
INDEX
.
This tutorial shows how to use the Edit Index tool to reverse the sort order of the index EVAL_JOB_IX
. The equivalent DDL statements are:
DROP INDEX EVAL_JOB_ID; CREATE INDEX EVAL_JOB_IX ON EVALUATIONS (JOB_ID DESC) NOPARALLEL;
To reverse the sort order of the index EVAL_JOB_IX using the Edit Index tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Indexes.
In the list of indexes, right-click EVAL_JOB_IX.
In the list of choices, click Edit.
In the Edit Index window, change Order to DESC
.
Click OK.
In the Confirm Replace window, click either Yes or No.
To drop an index, use either the SQL Developer Connections frame and Drop tool or the DDL statement DROP
INDEX
.
This tutorial shows how to use the Connections frame and Drop tool to drop the index EVAL_JOB_IX
. The equivalent DDL statement is:
DROP INDEX EVAL_JOB_ID;
To drop the index EVAL_JOB_IX:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Indexes.
In the list of indexes, right-click EVAL_JOB_IX.
In the list of choices, click Drop.
In the Drop window, click Apply.
In the Confirmation window, click OK.
To drop a table, use either the SQL Developer Connections frame and Drop tool, or the DDL statement DROP
TABLE
.
Caution:
Do not drop any tables that you created in "Creating Tables"—you need them for later tutorials. If you want to practice dropping tables, create simple ones and then drop them.To drop a table using the Drop tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Tables.
In the list of tables, right-click the name of the table to drop.
In the list of choices, select Table.
In the list of choices, click Drop.
In the Drop window, click Apply.
In the Confirmation window, click OK.
A view presents a query result as a table. In most places that you can use a table, you can use a view. Views are useful when you need frequent access to information that is stored in several different tables.
This section contains:
See Also:
"Selecting Table Data" for information about queries
Oracle Database Concepts for additional general information about views
To create views, use either the SQL Developer tool Create View or the DDL statement CREATE
VIEW
. This section shows how to use both of these ways to create these views:
SALESFORCE
, which contains the names and salaries of the employees in the Sales department
EMP_LOCATIONS
, which contains the names and locations of all employees
This view is used in "Creating an INSTEAD OF Trigger".
This section contains:
See Also:
Oracle SQL Developer User's Guide for more information about using SQL Developer to create a view
Oracle Database SQL Language Reference for more information about the CREATE
VIEW
statement
This tutorial shows how to create the SALESFORCE
view using the Create View tool.
To create the SALESFORCE view using the Create View tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, right-click Views.
In the list of choices, click New View.
The Create View window opens, with default values for a new view.
For Schema, accept the default value, HR
.
For Name, enter SALESFORCE
.
If the SQL Query pane does not show, click the tab SQL Query.
In the SQL Query pane, in the SQL Query field:
After SELECT
, type:
FIRST_NAME || ' ' || LAST_NAME "Name", SALARY*12 "Annual Salary"
After FROM
, type:
EMPLOYEES WHERE DEPARTMENT_ID = 80
Click Check Syntax.
Under "Syntax Results," if the message is not "No errors found in SQL", then return to step 7 and correct the syntax errors in the query.
Click OK.
The view SALESFORCE
is created. To see it, expand Views in the Connections frame.
To see the CREATE
VIEW
statement for creating this view, select its name and click the tab SQL.
See Also:
Oracle SQL Developer User's Guide for more information about using SQL Developer to create viewsThe CREATE
VIEW
statement in Example 4-3 creates the EMP_LOCATIONS
view, which joins four tables. (For information about joins, see "Selecting Data from Multiple Tables".)
Example 4-3 Creating the EMP_LOCATIONS View with CREATE VIEW
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;
Result:
View EMP_LOCATIONS created.
To change the query in a view, use the DDL statement CREATE
VIEW
with the OR
REPLACE
clause.
The CREATE
OR
REPLACE
VIEW
statement in Example 4-4 changes the query in the SALESFORCE
view.
Example 4-4 Changing the Query in the SALESFORCE View
CREATE OR REPLACE VIEW SALESFORCE AS
SELECT FIRST_NAME || ' ' || LAST_NAME "Name",
SALARY*12 "Annual Salary"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80 OR DEPARTMENT_ID = 20;
Result:
View SALESFORCE created.
See Also:
Oracle Database SQL Language Reference for information about theCREATE
VIEW
statement with the OR
REPLACE
clauseTo change the name of a view, use either the SQL Developer tool Rename or the RENAME
statement.
This tutorial shows how to use the Rename tool to change the name of the SALESFORCE
view. The equivalent DDL statement is:
RENAME SALESFORCE to SALES_MARKETING;
To change the SALESFORCE view using the Rename tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Views.
In the list of views, right-click SALESFORCE.
In the list of choices, select Rename.
In the Rename window, in the New View Name field, type SALES_MARKETING
.
Click Apply.
In the Confirmation window, click OK.
To drop a view, use either the SQL Developer Connections frame and Drop tool or the DDL statement DROP
VIEW
.
This tutorial shows how to use the Connections frame and Drop tool to drop the view SALES_MARKETING
(changed in "Tutorial: Changing View Names with the Rename Tool"). The equivalent DDL statement is:
DROP VIEW SALES_MARKETING;
To drop the view SALES_MARKETING using the Drop tool:
In the Connections frame, expand hr_conn.
In the a list of schema object types, expand Views.
In the a list of views, right-click SALES_MARKETING.
In the a list of choices, click Drop.
In the Drop window, click Apply.
In the Confirmation window, click OK.
Sequences are schema objects from which you can generate unique sequential values, which are very useful when you need unique primary keys. The HR
schema has three sequences: DEPARTMENTS_SEQUENCE
, EMPLOYEES_SEQUENCE
, and LOCATIONS_SEQUENCE
.
Sequences are used through the pseudocolumns CURRVAL
and NEXTVAL
, which return the current and next values of the sequence, respectively. After creating a sequence, you must initialize it by using NEXTVAL
to get its first value. Only after you initialize a sequence does CURRVAL
return its current value.
Tip:
When you plan to use a sequence to populate the primary key of a table, give the sequence a name that reflects this purpose. (This section uses the naming conventionTABLE_NAME
_SEQUENCE
.)This section contains:
See Also:
Oracle Database Concepts for information about the sequence generator
Oracle Database SQL Language Reference for more information about the CURRVAL
and NEXTVAL
pseudocolumns
Oracle Database Administrator's Guide for information about managing sequences
To create a sequence, use either the SQL Developer tool Create Sequence or the DDL statement CREATE
SEQUENCE
.
This tutorial shows how to use the Create Database Sequence tool to create a sequence to use to generate primary keys for the EVALUATIONS
table created in Example 4-1. The equivalent DDL statement is:
CREATE SEQUENCE evaluations_sequence INCREMENT BY 1 START WITH 1 ORDER;
To create EVALUATIONS_SEQUENCE using the Create Database Sequence tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, right-click Sequences.
In the list of choices, click New Sequence.
In the Create Sequence window, in the Name field, type EVALUATIONS_SEQUENCE
over the default value SEQUENCE1
.
If the Properties pane does not show, click the tab Properties.
In the Properties pane:
In the field Increment, type 1.
In the field Start with, type 1.
For the remaining fields, accept the default values.
Click OK.
The sequence EVALUATIONS_SEQUENCE
is created. Its name appears under Sequences in the Connections frame.
See Also:
Oracle SQL Developer User's Guide for more information about using SQL Developer to create a sequence
Oracle Database SQL Language Reference for information about the CREATE
SEQUENCE
statement
"Tutorial: Creating a Trigger that Generates a Primary Key for a Row Before It Is Inserted" to learn how to create a trigger that inserts the primary keys created by EVALUATIONS_SEQUENCE
into the EVALUATIONS
table
To drop a sequence, use either the SQL Developer Connections frame and Drop tool, or the DDL statement DROP
SEQUENCE
.
This statement drops the sequence EVALUATIONS_SEQUENCE
:
DROP SEQUENCE EVALUATIONS_SEQUENCE;
Caution:
Do not drop the sequenceEVALUATIONS_SEQUENCE
—you need them for Example 5-3. If you want to practice dropping sequences, create others and then drop them.To drop a sequence using the Drop tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Sequences.
In the list of sequences, right-click the name of the sequence to drop.
In the list of choices, click Drop.
In the Drop window, click Apply.
In the Confirmation window, click OK.
A synonym is an alias for another schema object. Some reasons to use synonyms are security (for example, to hide the owner and location of an object) and convenience. Examples of convenience are:
Using a short synonym, such as SALES
, for a long object name, such as ACME_CO
.SALES_DATA
Using a synonym for a renamed object, instead of changing that object name throughout the applications that use it
For example, if your application uses a table named DEPARTMENTS
, and its name changes to DIVISIONS
, you can create a DEPARTMENTS
synonym for that table and continue to reference it by its original name.
This section contains:
See Also:
Oracle Database Concepts for additional general information about synonymsTo create a synonym, use either the SQL Developer tool Create Database Synonym or the DDL statement CREATE
SYNONYM
.
This tutorial shows how to use the Create Database Synonym tool to create the synonym EMP
for the EMPLOYEES
table. The equivalent DDL statement is:
CREATE SYNONYM EMPL FOR EMPLOYEES;
To create the synonym EMP using the Create Database Synonym tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, right-click Synonyms.
In the list of choices, click New Synonym.
In the New Synonym window:
In the Synonym Name field, type EMPL
.
In the Object Owner field, select HR
from the menu.
In the Object Name field, select EMPLOYEES
from the menu.
The synonym refers to a specific schema object; in this case, the table EMPLOYEES
.
Click Apply.
In the Confirmation window, click OK.
The synonym EMPL
is created. To see it, expand Synonyms in the Connections frame. You can now use EMPL
instead of EMPLOYEES
.
To drop a synonym, use either the SQL Developer Connections frame and Drop tool, or the DDL statement DROP
SYNONYM
.
This statement drops the synonym EMP
:
DROP SYNONYM EMP;
To drop a synonym using the Drop tool:
In the Connections frame, expand hr_conn.
In the list of schema object types, expand Synonyms.
In the list of synonyms, right-click the name of the synonym to drop.
In the list of choices, click Drop.
In the Drop window, click Apply.
In the Confirmation window, click OK.