| Oracle® Database 2 Day Developer's Guide 11g Release 2 (11.2) E10766-06 |
|
|
PDF · Mobi · ePub |
| Oracle® Database 2 Day Developer's Guide 11g Release 2 (11.2) E10766-06 |
|
|
PDF · Mobi · ePub |
This chapter contains the following topics:
The statements that create, change, and drop schema objects are data definition language (DDL) statements. Before and after a DDL statement, Oracle Database issues an implicit COMMIT statement; therefore, you cannot roll back a DDL statement.
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 SQL 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 click the Refresh icon.
See Also:
Oracle Database SQL Language Reference for more information about DDL statements
When creating schema objects, you must observe the schema object naming rules in Oracle Database SQL Language Reference.
Tip:
Use the same prefix for names of objects of the same type. For example,
t_ for tables, v_ for views, seq_ for sequences, and syn_ for synonyms. This practice makes your objects easy to identify, and groups them in the SQL Developer Connections navigator display, SQL Developer reports, and queries whose results are ordered by object name.Tables are the basic units of data storage in Oracle Database. 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.
Topics:
Note:
To do the tutorials in this document, you must be connected to Oracle Database as the userHR from SQL Developer. For instructions, see "Connecting to Oracle Database as User HR from SQL Developer".See Also:
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 topic 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 are part of the sample application that the tutorials and examples in this document show how to develop and deploy.
Topics:
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:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Right-click Tables.
A list of choices appears.
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 Column Name, enter PERFORMANCE_ID.
For Type, accept the default value, VARCHAR2.
For Size, enter 2.
For Not Null and Primary Key, accept the default values, deselected.
Click Add Column.
For Column Name, enter NAME.
For Type, enter VARCHAR2.
For Size, enter 80.
Click Add Column.
For Column Name, enter WEIGHT.
For Type, enter NUMBER.
Click OK.
The table PERFORMANCE_PARTS is created. To see it, expand Tables in the navigation frame.
See Also:
Oracle Database SQL Developer User's Guide for more information about using SQL Developer to create tablesThis topic shows how to use the CREATE TABLE statement to create the EVALUATIONS and SCORES tables.
The CREATE TABLE statement in Example 6-1 creates the EVALUATIONS table.
Example 6-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 6-2 creates the SCORES table.
Example 6-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 navigation frame, if you expand Tables, you can see the tables EVALUATIONS and SCORES.
If you select a table in the navigation frame, and then click the tab SQL in the right frame, the SQL pane shows the SQL statement that created the table.
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 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.
Topics:
See Also:
Oracle Database Concepts for additional general information about constraints
Oracle Database SQL Language Reference for syntactic information about 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 the column and the object that it references
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 topic 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 (immediately after this paragraph) 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 navigation 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".
To add a Not Null constraint using the Edit Table tool:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Tables.
A list of tables appears.
Right-click PERFORMANCE_PARTS.
A list of choices appears.
Click Edit.
The Edit Table window opens. By default, Columns is selected, the columns of the PERFORMANCE_PARTS table are listed, the column PERFORMANCE_ID is selected, and its properties are listed.
Click the column NAME.
The properties of the column NAME appear. The property "Cannot be NULL" is deselected.
Select Cannot be 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:
Click the icon SQL Worksheet.
The SQL Worksheet pane appears.
In the SQL Worksheet pane, type this statement:
ALTER TABLE PERFORMANCE_PARTS MODIFY WEIGHT NOT NULL;
Click the icon Execute 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:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Tables.
A list of tables appears.
Right-click SCORES.
A list of choices appears.
Click Constraint.
A list of choices appears.
Click Add Unique.
The Add Unique window opens.
For Constraint Name, enter SCORES_EVAL_PERF_UNIQUE.
For Column 1, select EVALUATION_ID from the drop-down menu.
For Column 2, select PERFORMANCE_ID from the drop-down menu.
Click Apply.
The Confirmation window opens.
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:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Tables.
A list of tables appears.
Right-click PERFORMANCE_PARTS.
A list of choices appears.
Click Constraint.
A list of choices appears.
Click Add Primary Key.
The Add Primary Key window opens.
For Primary Key Name, enter PERF_PERF_ID_PK.
For Column 1, select PERFORMANCE_ID from the drop-down menu.
Click Apply.
The Confirmation window opens.
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:
Click the icon SQL Worksheet.
The SQL Worksheet pane appears. Under "Enter SQL Statement:" is a field where you can enter a SQL statement.
In the SQL Worksheet pane, type this statement:
ALTER TABLE EVALUATIONS ADD CONSTRAINT EVAL_EVAL_ID_PK PRIMARY KEY (EVALUATION_ID);
Click the icon Execute 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:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Tables.
A list of tables appears.
Right-click SCORES.
A list of choices appears.
Click Constraint.
A list of choices appears.
Click Add Foreign Key.
The Add Foreign Key window opens.
For Foreign Key Name, enter SCORES_EVAL_FK.
For Column Name, select EVALUATION_ID from the drop-down menu.
For Reference Table Name, select EVALUATIONS from the drop-down menu.
For Referencing Column, select EVALUATION_ID from the drop-down menu.
Click Apply.
The Confirmation window opens.
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.
Right-click SCORES.
A list of tables appears.
Click Constraint.
A list of choices appears.
Click Add Foreign Key.
The Add Foreign Key window opens.
For Foreign Key Name, enter SCORES_PERF_FK.
For Column Name, select PERFORMANCE_ID from the drop-down menu.
For Reference Table Name, select PERFORMANCE_PARTS from the drop-down menu.
For Referencing Column, select PERFORMANCE_ID from the drop-down menu.
Click Apply.
The Confirmation window opens.
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:
Click the icon SQL Worksheet.
The SQL Worksheet pane appears. Under "Enter SQL Statement:" is a field where you can enter a SQL statement.
In the SQL 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 Execute 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:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Tables.
A list of tables appears.
Right-click SCORES.
A list of choices appears.
Click Constraint.
A list of choices appears.
Click Add Check.
The Add Check window opens.
For Constraint Name, enter SCORE_VALID.
For Check Condition, enter score >= 0 and score <= 9.
For Status, accept the default, ENABLE.
Click Apply.
The Confirmation window opens.
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 Database 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 (created in "Tutorial: Creating a Table with the Create Table Tool").
To add rows to the PERFORMANCE_PARTS table using the Insert Row tool:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Tables.
A list of tables appears.
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.
Click the cell under the column heading PERFORMANCE_ID.
Type WM.
The value of PERFORMANCE_ID is now WM.
Either press the key Tab or click the cell under the column heading NAME.
Type Workload Management.
The value of NAME is now Workload Management.
Either press the key Tab or click the cell under the column heading WEIGHT.
Type 0.2.
Press the key Enter.
The value of WEIGHT is now 0.2.
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.
Under the Data pane is the Data Editor Log pane.
Check the Data Editor Log pane for the message "Commit Successful".
Check the new rows in the Data Pane.
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:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Tables.
A list of tables appears.
Select PERFORMANCE_PARTS.
In the right frame, click the tab Data.
The Data pane appears, showing the rows of the PERFORMANCE_PARTS table.
In the row where NAME is Workload Management:
Click the WEIGHT value.
Enter the value 0.3.
Press the key Enter.
In the row where NAME is Building Relationships:
Click the WEIGHT value.
Enter the value 0.15.
Press the key Enter.
In the row where NAME is Customer Focus:
Click the WEIGHT value.
Enter the value 0.15.
Press the key Enter.
Click the icon Commit Changes.
This icon is a picture of a data drum with a green check mark in the lower right corner.
Under the Data pane is the Data Editor Log pane.
Check the Data Editor Log pane for the message "Commit Successful".
Check the new data in the Data Pane.
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:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Tables.
A list of tables appears.
Select PERFORMANCE_PARTS.
In the right frame, click the tab Data.
The Data pane appears, showing the rows of the PERFORMANCE_PARTS table.
Click the row where NAME is Results Orientation.
Click the icon Delete Selected Row(s).
Click the icon Commit Changes.
The row is deleted.
Under the Data pane is the Data Editor - Log pane.
Check the Data Editor 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 I/O.
When you define a primary key on a table, Oracle Database creates a 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 navigation frame and click the Indexes tab, the Indexes pane shows a Unique index on the EVALUATION_ID column.
Topics:
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 "Creating Tables with the CREATE TABLE Statement"). 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:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Tables.
A list of tables appears, including EVALUATIONS.
Right-click EVALUATIONS.
A list of choices appears.
Select Index.
A list of choices appears.
Select Create Index.
The Create Index window opens.
For Schema, accept the default, HR.
For Name, type EVAL_JOB_IX.
Select the tab Definition.
The Definition pane shows the default values for index properties.
In the field labeled "Column Name or Expression:", type JOB_ID.
(For all other properties, accept the default values.)
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 (created in "Tutorial: Adding an Index with the Create Index Tool"). 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:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Indexes.
A list of indexes appears, including EVAL_JOB_IX.
Right-click EVAL_JOB_IX.
A list of choices appears.
Click Edit.
A list of choices appears.
Click Edit Index.
The Edit Index window opens.
In the Edit Index window, change Order to DESC.
Click OK.
To drop an index, use either the SQL Developer tool Drop or the DDL statement DROP INDEX.
This tutorial shows how to use the navigation frame and Drop tool to drop the index EVAL_JOB_IX (created in "Tutorial: Adding an Index with the Create Index Tool"). The equivalent DDL statement is:
DROP INDEX EVAL_JOB_ID;
To drop the index EVAL_JOB_IX:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Indexes.
A list of indexes appears, including EVAL_JOB_IX.
Right-click EVAL_JOB_IX.
A list of choices appears.
Click Drop.
The Drop window opens.
Click Apply.
The Confirmation window opens.
Click OK.
To drop a table, use either the SQL Developer navigation frame and Drop tool, or the DDL statement DROP TABLE.
Caution:
Do not drop any of the 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:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Tables.
A list of tables appears.
Right-click the name of the table to drop.
A list of choices appears.
Click Table.
A list of choices appears.
Click Drop.
The Drop window opens.
Click Apply.
The Confirmation window opens.
Click OK.
A view presents the output of a query 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.
Topics:
See Also:
Chapter 4, "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 topic 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
These view are part of the sample application that the tutorials and examples in this document show how to develop and deploy.
Topics:
See Also:
Oracle Database 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:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Right-click Views.
A list of choices appears.
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.
Click the tab SQL Query.
The SQL Query pane appears.
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 Test Syntax.
A message appears in the field SQL Parse Results.
If the message is not "No errors found in SQL", 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 navigation frame.
See Also:
Oracle Database SQL Developer User's Guide for more information about using SQL Developer to create viewsThe CREATE VIEW statement in Example 6-3 creates the EMP_LOCATIONS view, which joins four tables. (For information about joins, see Selecting Data from Multiple Tables.)
Example 6-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 created.
To change the query in a view, use either the SQL Developer tool Edit View or the DDL statement CREATE VIEW with the OR REPLACE clause. To change the name of a view, use either the Rename tool or the RENAME statement.
This tutorial shows how to use the Edit View tool to add the employees of the Marketing department to the SALESFORCE view (created in "Tutorial: Creating a View with the Create View Tool") and the and Rename tool to change its name to SALES_MARKETING. The equivalent DDL statements are:
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; RENAME SALESFORCE to SALES_MARKETING;
To change the SALESFORCE view using the Edit View and Rename tools:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Views.
A list of views appears.
Right-click SALESFORCE.
A list of choices appears.
Select Edit.
The Edit View window opens. Its SQL Query field contains the query to be changed.
Add this to the end of the query:
OR DEPARTMENT_ID = 20
Click Test Syntax.
A message appears in the field SQL Parse Results.
If the message is not "No errors found in SQL", return to step 5 and correct the syntax errors in the query.
Click OK.
The employees of the Marketing department are added to the SALESFORCE view. Now change the name of the view to SALES_MARKETING.
Right-click SALESFORCE.
A list of choices appears.
Select Rename.
The Rename window opens. It has a New View Name field.
In the New View Name field, type SALES_MARKETING.
Click Apply.
The Confirmation window opens.
Click OK.
See Also:
Oracle Database SQL Language Reference for information about the CREATE VIEW statement with the OR REPLACE clause
Oracle Database SQL Language Reference for information about the RENAME statement
To drop a view, use either the SQL Developer navigation frame and Drop tool or the DDL statement DROP VIEW.
This tutorial shows how to use the navigation frame and Drop tool to drop the view SALES_MARKETING (changed in "Tutorial: Changing Views with the Edit View and Rename Tools"). The equivalent DDL statement is:
DROP VIEW SALES_MARKETING;
To drop the view SALES_MARKETING using the Drop tool:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Views.
A list of views appears.
Right-click SALES_MARKETING.
A list of choices appears.
Click Drop.
The Drop window opens.
Click Apply.
The Confirmation window opens.
Click OK.
See Also:
Oracle Database SQL Language Reference for information about the DROP VIEW statement
Sequences are schema objects that generate unique sequential values, which are very useful when you need unique primary keys. The HR schema has three sequences: DEPARTMENTS_SEQ, EMPLOYEES_SEQ, and LOCATIONS_SEQ.
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 the sequence is initialized 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 topic uses the naming conventiontable_name_SEQ.)Topics:
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
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 "Creating Tables with the CREATE TABLE Statement"). The equivalent DDL statement is:
CREATE SEQUENCE EVALUATIONS_SEQ INCREMENT BY 1 START WITH 1 ORDER;
This sequence is part of the sample application that the tutorials and examples in this document show how to develop and deploy.
To create EVALUATIONS_SEQ using the Create Database Sequence tool:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Right-click Sequences.
A list of choices appears.
Click New Sequence.
The Create Database Sequence window opens. The field Schema has the value HR, and the field Name has the default value, SEQUENCE1.
In the Name field, type EVALUATIONS_SEQ over the default value.
Click the tab Properties.
The Properties pane appears.
In the field Increment, type 1.
In the field Start with, type 1.
Deselect the check box Cycle.
Select the check box Order.
Click OK.
The sequence EVALUATIONS_SEQ is created. To see it, expand Sequences in the navigation frame.
See Also:
Oracle Database 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_seq into the EVALUATIONS table
To drop a sequence, use either the SQL Developer navigation frame and Drop tool, or the DDL statement DROP SEQUENCE.
Caution:
Do not drop the sequence that you created in "Tutorial: Creating a Sequence"—you need it for later tutorials. If you want to practice dropping sequences, create new ones and then drop them.To drop a sequence using the Drop tool:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Sequences.
A list of sequences appears.
Right-click the name of the sequence to drop.
A list of choices appears.
Click Drop.
The Drop window opens.
Click Apply.
The Confirmation window opens.
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.
Topics:
See Also:
Oracle Database Concepts for additional general information about synonyms
To 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 EMP FOR EMPLOYEES;
This synonym is part of the sample application that the tutorials and examples in this document show how to develop and deploy.
To create a synonym using the Create Databse Synonym tool:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Right-click Synonyms.
A list of choices appears.
Click New Synonym.
The Create Database Synonym window opens. The check box Public is deselected, the field Schema has the value HR, and the field Name has the default value SYNONYM1.
In the Name field, type EMP over the default value.
Click the tab Properties.
The Properties pane appears. The Referenced Schema field has the value HR, the option Object based is deselected, and the option Name based is selected.
Select Object Based.
The option Name based is now deselected.
In the field next to the Object based option, select EMPLOYEES from the drop-down list.
Object based means that the synonym refers to a specific schema object; in this case, the table EMPLOYEES.
Click OK.
The synonym EMP is created. To see it, expand Synonyms in the navigation frame. You can now use EMP instead of EMPLOYEES.
To drop a synonym, use either the SQL Developer navigation frame and Drop tool, or the DDL statement DROP SYNONYM.
Caution:
Do not drop the synonym that you created in "Creating Synonyms"—you need it for later tutorials. If you want to practice dropping synonyms, create new ones and then drop them.To drop a synonym using the Drop tool:
On the Connections tab, expand hr_conn.
Under the hr_conn icon, a list of schema object types appears.
Expand Synonyms.
A list of synonyms appears.
Right-click the name of the synonym to drop.
A list of choices appears.
Click Drop.
The Drop window opens.
Click Apply.
The Confirmation window opens.
Click OK.
|
 Copyright © 2008, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|