Oracle Database（5） Schema对象

• DDL - Data Definition Language Statements
• Tables
• Views
• Sequences
• Synonyms

About Data Definition Language Statements

The statements that create, change, and drop schema objects are data definition language statements. Before and after a DDL statement, Oracle Database issues an implicit COMMIT statement; therefore, you can't roll back a DDL statement.
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.

Creating and Managing Tables

Tables are the basic units of data storage in Oracle Database. Table hold all users-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:

• About SQL Data Types
• Creating Tables
• Ensuring Data Integrity in Tables
• Managing Indexes
• Dropping Tables

About SQL Data Types

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. SQL data types fall into two categories: built-in and user-defined.

Creating Tables

To create tables, use DDL CREATE TABLE.

create table EVALUATIONS
(
  evaluation_id   NUMBER(8),
  employee_id     NUMBER(6),
  evaluation_date DATE,
  job_id          VARCHAR2(10),
  manager_id      NUMBER(6),
  department_id   NUMBER(4),
  total_score     NUMBER(3)
)

Ensuring Data Integrity in Tables

To ensure that the data in your tables satisfies the business rules that your application models, you can use constrains, application logic, or both.
Constrains 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.

About Constraint Types

• Not Null, which prevents a value from being null
• 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
• Primary Key, which is a combination of Not Null and UNIQUE
• Foreign Key, which requires values in one table to match values in another table
• Check, which requires that a value satisfy a specified condition
• REF, which further describes the relationship between a REF column and the object that it references.

ALTER TABLE table_name
MODIFY table_column [Not Null];

Managing Indexes

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:

• If an existing index starts with the primary key columns, then Oracle Database 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 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 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 creates a non-Unique index on the primary key.

Dropping Tables

DROP TABLE table_name;

Creating and Managing Views

A view presents a query result as a table. In the 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.

Creating and Managing Sequences

Sequences are schema objects from which you can generate unique sequential values, which are very useful when you need unique primary keys.
Sequences are used through the pseudo columns 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.