Oracle Flash back and built-in objects

Oracle built-in objects:

Sequence sequence: generates an integer sequence that can often be used to populate a primary key column.

To create a sequence:

CREATE SEQUENCE sequence name

[Start with start value ]

[INCREMENT by growth value ]

When used: Two "pseudo-column" nextval: Let the sequence produce the next numeric content

Currval: Take the current value of the sequence

Note: After the sequence is generated, the first time you use it, you must first call Nextval to initialize the data, and you cannot call currvaldirectly.

View information for all sequences:

SELECT * from User_sequences;

Parameters of the sequence

[Start with start value ] : Specify the initial value

[INCREMENT by growth value ] : Specify the increment value step value

Note: When theINCREMENT by growth value is negative, the sequence value is generated in descending order, to consider The value of MAXVALUE

[{MAXVALUE max | Nomaxvalue}]: Specify the maximum value,nomaxvalue is the default value, The second party, descending, is -1

[{MINVALUE min value | Nominvalue}] Specifies the minimum value,nominvalue is the default value, and whenthe order is added,1, descending, is negative of the - Sub-party

[{CYCLE | Nocycle}]: Cycle means that when the maximum or minimum is reached, the loop starts from scratch,andnocycle indicates that the number is no longer generated when the maximum or minimum is reached,nocycle to the default value

[{cache Cached quantity | NOCACHE}]: cache can improve access efficiency, but when the database shuts down, the cached data is lost, resulting in data discontinuity, if you want to avoid this problem, you should use NOCACHE. The default value is CACHE

[order| Noorder]:

Use sequence:

You can usually use it to populate the primary key columns, as follows:

INSERT into student VALUES (Seq2.nextval, ' JavaScript ', one, ' 9-4 month -2012', null);

To delete a sequence:

DROP SEQUENCE sequence name ;

Indexed Index

Objective: To optimize the time efficiency of the query,

Timing: You can index columns when you need to retrieve a few rows from a table that contains many rows

Syntax:CREATE [UNIQUE] index name on table name ( column name [, column name ...]) [Tablespace table Space ]

Example:CREATE INDEX sname_index on student (sname);

CREATE INDEX sname_index2 on student (sname,age); -- composite Index

CREATE INDEX sname_index3 on student (LOWER (sname)); -- Function Index

Btree Index bit Index

Query index:

SELECT * from User_indexes;

To delete an index:

DROP index name;

Example:DROP INDEX sname_index;

Views View

1. Create a View

CREATE View name as subquery

This cop query is a very complex statement.

sql> CREATE OR REPLACE VIEW empv20 as

SELECT empno,ename,job,hiredate

From EMP

WHERE deptno=20;

Note: The CREATE view needs to give create VIEW permission to execute grant CREATE view to Scott under the sys account ;

2. Query view

Sql> SELECT * from EMPV20;

3. Delete View

sql> DROP VIEW empv20;

If you want to modify the view, delete the view before Oracle provides a replacement command for the user to modify the view

CREATE OR REPLACE view name as subquery

Views can encapsulate complex queries, such as querying department names, department numbers, average wages, and minimum wage employees.

sql> CREATE OR REPLACE VIEW empv20 as

SELECT D.dname,ed.c,ed.a,e.ename from Dept D, (

SELECT Deptno,count (empno) C, AVG (SAL) a,min (SAL) MIN

From EMP

GROUP by Deptno) Ed,emp E

WHERE D.deptno=ed.deptno and E.sal=e.min;

It is inconvenient to write such a long SQL statement every time you open a hair, you can set it up as a view, the red department above

If you are updating the view, you should not include the actual data in the views and follow the commands below.

sql> UPDATE empv20 SET deptno=30 WHERE empno=7369;

The discovery view has been updated normally because the 7369 number in the EMP table has been modified to be the same , so the view is created conditionally

SQL provides two important parameters

With CHECK OPTION: Cannot update the view's creation criteria

sql> CREATE OR REPLACE VIEW empv20 as

SELECT * from EMP WHERE deptno=20

With CHECK OPTION;

The Create condition cannot be updated, but the other fields can still be updated

sql> UPDATE empv20 SET ename= ' Wilson ' WHERE empno=7369;

So you can use the first 2 conditions of the view : Create a read-only view

sql> CREATE OR REPLACE VIEW empv20 as

SELECT * from EMP WHERE deptno=20

with READ only;

Synonyms

Synonyms are public and private, and creating the appropriate permissions is required

Grant Create [public] synonym to user name

Sql> SELECT sysdate from dual;

Dual is a virtual table that is defined under the SYS user and can be queried using the following statement

Sql> SELECT * from tab WHERE tname= ' DUAL ';

This table is under SYS , but the SCOTT user can access it directly from the table name, which normally we need to use " username . " Table name "that is the role of synonyms

To create a synonym:

CREATE [public] synonym synonym name for user name . Table name ';

For example, the scott.emp defines the synonyms for the EMP

sql> CREATE synonym emp for scott.emp;

Delete synonyms

Sql> DROP [public] synonym EMP;

Synonyms This feature is only suitable for Oracle Database

Constraint usage explained in Oracle database

* If a constraint is only used for a separate field, you can define constraints at the field level, or you can define constraints at the table level, but if a constraint works on more than one field,
Constraints must be defined at the table level
* When defining constraints, you can name the constraint with the constraint keyword, and if not specified, Oracle will automatically establish a default name for the constraint

Define PRIMARY KEY constraint (single field)
CREATE TABLE Employees (empno number (5) primary key,...)

Specify constraint name
CREATE TABLE Employees (empno number (5) Constraint EMP_PK primary key,...)

Defining PRIMARY KEY constraints (multiple fields, defining constraints at the table level)
CREATE TABLE Employees
(empno number (5),
Deptno Number (3) is not NULL,
Constraint EMP_PK primary KEY (Empno,deptno)
Using index tablespace indx
Storage (initial 64K
Next 64K
)
)

Oracle automatically establishes a unique index and a NOT NULL constraint for a field with the primary key constraint, which can be indexed when defining the PRIMARY KEY constraint
Specify storage location and storage parameters

ALTER TABLE employees add primary key (EMPNO)
ALTER TABLE employees ADD constraint EMP_PK primary key (EMPNO)
ALTER TABLE employees ADD constraint EMP_PK primary key (Empno,deptno)

NOT NULL constraint (only a NOT NULL constraint is defined at the field level and multiple NOT NULL constraints can be defined in the same table)
ALTER TABLE employees modify DEPTNO not Null/null

Unique constraint
CREATE TABLE Employees
(empno number (5),
ename VARCHAR2 (15),
Phone VARCHAR2 (15),
Email varchar2 (+) Unique,
Deptno Number (3) is not NULL,
Constraint Emp_ename_phone_uk unique (ename,phone)
)

ALTER TABLE Employees
Add constraint Emp_uk unique (ename,phone)
Using index tablespace indx

A field that defines a unique constraint cannot contain duplicate values, and you can define a unique constraint for one or more fields, so unique can be defined at the field level as well as at the table level.
You can include a null value on a field that is uniqued constrained.

FOREIGN KEY constraint

* The field defined as FOREIGN KEY constraint can contain only the value of the Reference Code field in the corresponding other table or the null value
* FOREIGN KEY constraints can be defined for the combination of one or more fields
* The external Code field that defines the foreign key constraint and the corresponding reference Code field can exist in the same table, which is referred to as a "self-reference"
* FOREIGN KEY constraints and NOT NULL constraints can be defined for the same field

The field that defines the foreign key constraint is called the "External Code field", the field referenced by the Forgien key constraint is called the "Reference Code field", the reference code must be a master or a unique code, and the table containing the external code is called the child table.
The table that contains the reference code is called the parent table.

A:
CREATE TABLE Employees
(.....,
Deptno Number (3) is not NULL,
Constraint EMP_DEPTNO_FK foreign key (DEPTNO)
References Dept (DEPTNO)
)

If the external code in the child table has the same name as the reference code in the primary table, it can be written as:
B:
CREATE TABLE Employees
(.....,
Deptno Number (3) not NULL
Constraint EMP_DEPTNO_FK References Dept
)

Attention:
The above example (B) is not null followed by a comma, because the contraint of this sentence is followed by the column deptno, which is a column definition, so there is no need to specify the column. In the case of a table definition, you need to specify that column, so add a comma, you cannot define it after the column, and you can write:

CREATE TABLE Employees
(Empno char (4),
Deptno char (2) NOT NULL constraint EMP_DEPTNO_FK references dept,
ename VARCHAR2 (10)
)
Table Definition contraint can only be written at the end, see two more examples:

CREATE TABLE Employees
(empno number (5),
ename VARCHAR2 (10),
Deptno char (2) NOT NULL constraint EMP_DEPTNO_FK references dept,
Constraint EMP_PK primary KEY (Empno,ename)
)

CREATE TABLE Employees
(empno number (5),
ename VARCHAR2 (15),
Phone VARCHAR2 (15),
Email varchar2 (+) Unique,
Deptno Number (3) is not NULL,
Constraint EMP_PK primary KEY (Empno,ename),
Constraint Emp_phone_uk unique (phone)
)

Add FOREIGN KEY constraint (multi-field/table-level)
ALTER TABLE Employees
Add constraint EMP_JOBS_FK foreign key (JOB,DEPTNO)
References Jobs (JOBID,DEPTNO)
ON DELETE Cascade

Change the reference behavior of the FOREIGN KEY constraint definition (delete cascade/delete set Null/delete no action), the default is delete on action

Referential behavior (determines how to handle external code fields in a Word table when a record in the primary table is deleted):
Delete Cascade: Deletes all related records in the child table
Delete Set NULL: Sets the external Code field value of all related records to NULL
Delete No action: do nothing

Delete the original foreign KEY constraint before adding the constraint
ALTER TABLE Employees DROP CONSTRAINT EMP_DEPTNO_FK;
ALTER TABLE Employees ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (deptno) REFERENCES Dept (DEPTNO) on DELETE CASCADE;

Check Constraint
* One or more fields in the table must be referenced in the expression in the check constraint, and the expression must evaluate to a Boolean value
* Can be defined at the table level or at the field level
* Multiple CHECK constraints can be defined on the same field, and NOT NULL constraints can also be defined

CREATE TABLE Employees
(Sal number (7,2)
Constraint emp_sal_ck1 Check (sal > 0)
)

ALTER TABLE Employees
Add constraint emp_sal_ck2 Check (Sal < 20000)

Delete Constraint

ALTER TABLE Dept Drop Unique (dname,loc)--Specify the definition of the constraint
ALTER TABLE DEPT DROP constraint Dept_dname_loc_uk--Specify constraint name

When you delete a constraint, the index corresponding to the constraint is deleted by default, and if you want to keep the index, use the Keep index keyword
ALTER TABLE employees drop PRIMARY key Keep index

If the constraint to be removed is being referenced by another constraint, alter TABLE: The CASCADE keyword specified in the drop statement can delete the constraint that references it at the same time

The following statement removes the FOREIGN key constraint that references this constraint in the other tables when the primary KEY constraint in the Dept table is deleted:
ALTER TABLE Dept DROP PRIMARY KEY Cascade

disabling/activating constraints (disabling/activating constraints can cause operations to delete and rebuild indexes)
ALTER TABLE employees disable/enable unique Email
ALTER TABLE employees disable/enable constraint EMP_ENAME_PK
Alter Tabel employees modify constraint emp_pk disable/enable
Alter Tabel employees modify constraint Emp_ename_phone_uk disable/enable

If a foreign key constraint is referencing a unique or PRIMARY KEY constraint, these unique or primary key constraints cannot be disabled.
You can then disable the FOREIGN KEY constraint before disabling the unique or PRIMARY KEY constraint, or you can do so at the alter TABLE ... DISABLE
The Cascade keyword is specified in the statement, which disables the foreign key constraints that reference them while disabling the unique or PRIMARY key constraint, such as:
ALTER TABLE employees DISABLE PRIMARY KEY cascade

Constrained data dictionary
All_constraints/dba_constraints/user_constraints basic information about constraints, including the name, type, and state of the constraint
(Constraint type: C (check constraint), P (main code constraint), R (external code constraint), U (unique code constraint))
All_cons_columns/dba/user constraint corresponding field information

Oracle Flash back and built-in objects