Views, synonyms, and sequences of Oracle

L management view

 

Create View

Use the create view statement to CREATE a VIEW. Each VIEW is materialized by the query of the reference table or defined by other views, just as all the subquery definition views cannot contain the for update clause.

 

Create view sales_staff

SELECT empno, ename, deptno FROM emp WHERE deptno = 10

With check option constraint sales_staff_cnst;

 

For a query that defines the check option view, only the rows with the department number are referenced. In addition, the INSERT and UPDATE statements published for the view with the constraint sales_staff_cnst cannot generate rows that cannot be selected by the view.

 

Insert into sales_staff VALUES (7584, 'ster', 10); -- successful Insert

Insert into sales_staff VALUES (7591, 'williams ', 30 );

-- Failed. Only records with sales_staff view where deptno = 10 can be used for Insert or update.

 

Optional, you can create a view by specifying the with read only clause. This prevents any changes to the view from being inserted or deleted. If the WITH clause is not specified, a view WITH certain restrictions is naturally updatable.

 

Join View

You can specify multiple basic tables or views in the FROM clause to create a view. This view is called a join view.

 

Create view division=staff as select ename, empno, job, dname FROM emp, dept

WHERE emp. deptno IN (10, 30) AND emp. deptno = dept. deptno;

-- Create the division1_staff view of data in the join emp table and dept table

 

Create an error View

To CREATE a VIEW with errors, the FORCE option must be included in the create view statement.

Create force view as select * from employee

-- The table employee does not exist at this time. You can use the FORCE keyword to create a view and then create a table.

 

Update join View

If the primary key and foreign key are clearly defined in the table or the unique index is defined, the following example can work.

The following table definitions are suitable but restricted for emp and dept tables.

 

Create table dept (

Deptno NUMBER (4) primary key, dname VARCHAR2 (14), loc VARCHAR2 (13 ));

 

Create table emp (

Empno NUMBER (4) primary key, ename VARCHAR2 (10 ),

Job VARCHAR2 (9), mgr NUMBER (4 ),

Sal NUMBER (7,2), comm NUMBER (7,2 ),

Deptno NUMBER (2), foreign key (DEPTNO) references dept (DEPTNO ));

 

The following statement creates the emp_dept join view referenced in the example.

 

Create view emp_dept

SELECT emp. empno, emp. ename, emp. deptno, emp. sal, dept. dname, dept. loc FROM emp, dept

WHERE emp. deptno = dept. deptno

AND dept. loc IN ('Dallas ', 'New YORK', 'boston ');

 

The concept of a key reserved table is the basis for understanding the restrictions on modifying the join view. If each key in the table is also the result key of the join, the table is reserved by the key, therefore, a key reserved table reserves its own key through the join operation.

Note that you do not need to select one or more keys in the table as the reserved keys. You can select them as the keys for the join result.

The reserved attribute of the key in the table does not depend on the actual data in the table. It is also a schema attribute. For example, if each department in the emp table has at most one employee, deptno is in the join results of emp and dept. unique but dept is not a key reserved table

 

If you SELECT all rows from emp_dept, the result is as follows:

 

EMPNO ENAME DEPTNO DNAME LOC

-----------------------------------------

7782 CLARK 10 ACCOUNTING NEW YORK

7839 KING 10 ACCOUNTING NEW YORK

7934 MILLER 10 ACCOUNTING NEW YORK

7369 SMITH 20 RESEARCH DALLAS

7876 ADAMS 20 RESEARCH DALLAS

7902 FORD 20 RESEARCH DALLAS

7788 SCOTT 20 RESEARCH DALLAS

7566 JONES 20 RESEARCH DALLAS

8 rows selected.

 

In this view, emp is a key reserved table because empno is a key of the emp table and also a join result, the key dept is not a key reserved table. Although deptno is a key of the dept table, it is not a join key.

 

DML statements and join views

The general rule is to join any UPDATE, DELETE, or INSERT statements on the view to modify only one underlying table. The following example shows the rules for UPDATE, DELETE, and INSERT statements.

 

UPDATE emp_dept SET sal = sal * 1.10 WHERE deptno = 10;

-- Modify the UPDATE statement of the emp_dept view correctly

 

UPDATE emp_dept SET loc = 'boston 'WHERE ename = 'Smith ';

-- This statement fails to generate an error ORA-01779 cannot modify a column which maps to a non key-preserved table cannot modify columns mapped to a non-key reserved table, this statement attempts to modify the basic dept table, but the dept table is not a key reserved table in the emp_dept view.

All updatable columns in the join view must be mapped to the columns in the key reserved table. If the view is defined by the with check option clause, therefore, all joined columns and all columns retrieved from the table referenced multiple times in the view cannot be modified.

For example, if the emp_dept view is defined using the with check option clause, the following UPDATE statement fails.

 

UPDATE emp_dept SET deptno = 10 WHERE ename = 'Smith '-- failed because an attempt to UPDATE a joined Column

 

The DELETE statement can be used to DELETE a join view that has only one key reserved table in the join operation.

The following DELETE statement works on the emp_dept view.

 

Delete from emp_dept WHERE ename = 'Smith ';

-- The DELETE statement is valid because it can be converted to the DELETE operation on the basic emp table, and the emp table is the only key reserved table in the join.

If the following view is created, the operation cannot be performed on the view, because both of them are key-reserved tables.

 

Create view emp_emp

SELECT e1.ename, e2.empno, deptno FROM emp e1, emp e2 WHERE e1.empno = e2.empno

 

If the view is defined by the with check option clause and the key reserved table is duplicate, You cannot delete rows in this view.

 

Create view emp_mgr

SELECT e1.ename, e2.ename mname FROM emp e1, emp e2 WHERE e1.mgr = e2.empno

With check option;

-- Deletion cannot be performed on the view because the view includes the join of the key reserved table.

 

Insert into emp_dept (ename, empno, deptno) VALUES ('kuroda', 9010, 40 );

-- This statement can be run because only one key reserved base table emp 40 is valid in the dept table deptno.

(That is, meet the foreign key integrity constraints on the emp table)

Insert into emp_dept (ename, empno, deptno) VALUES ('kuroda', 9010, 77 );

-- The cause of INSERT statement failure is the same as that of UPDATE failure in the basic emp table. It violates the foreign key Integrity Constraint in the emp table because no deptno 77

 

-

Insert into emp_dept (empno, ename, loc) VALUES (9010, 'kuroda', 'boston ');

-- The following INSTER statement will also fail to generate an error ORA-01776 cannot modify more than one base table through a view cannot modify multiple base tables through the view

 

The INSERT statement cannot implicitly or explicitly involve columns in non-key reserved tables. If the join view is defined by the with check option clause, the INSERT operation cannot be performed on The View.

 

Use UPDATABLE_COLUMNS View

When you want to modify the join view, the view described in the UPDATABLE_COLUMNS view is helpful to you.

View name
Description
 
DBA_UPDATABLE_COLUMNS
Display All modifiable columns in all tables and views
 
ALL_UPDATABLE_COLUMNS
Display All Tables accessible to users and all modifiable columns in the view
 
USER_UPDATABLE_COLUMNS
Display All modifiable columns in all tables and views in user mode
 

Delete View

You can delete any view contained in your mode. to delete a VIEW in another user mode, you must have the permission to drop any view. Use the drop view statement to delete the VIEW.

The following statement deletes the emp_dept View

Drop view emp_dept;

 

Replace View

To replace a view, you must have all the permissions required to delete and create a view. If you must modify the view definition, you must replace the view without modifying the view definition. The following methods can be used to replace the view.

Delete and recreate view warning: When a view is deleted, all permissions for the corresponding object permission are revoked and re-authorized from the role and user.

After creating a view, you must re-grant permissions.

You can use the create view statement containing the or replace option to redefine the view or replace option to REPLACE the current definition of the VIEW and retain the current security authorization. For example, if you have created the sales_staff VIEW as described earlier roles and other users have granted several object permissions

However, you need to redefine the sales_staff view to change the Department number specified in the WHERE clause. Replace the current sales_staff view with the following statement.

 

Create or replace view sales_staff

SELECT empno, ename, deptno FROM emp WHERE deptno = 30

With check option constraint sales_staff_cnst;

 

Before replacing a view, consider the following:

 

Replacing the view replaces the definition of the view in the data dictionary and does not affect all the lower-level objects involved in the view.

If the constraints in check option have been defined earlier and the new view definition does not contain such constraints, the constraints will be deleted, all views dependent on the replaced view and PL/SQL program units become invalid and cannot be used.

 

L Synonym

Synonyms are the aliases of pattern objects. By shielding the names and owners of objects and providing location transparency for remote objects in distributed databases, synonyms at the security layer are easy to use, reducing SQL statements for database users. complexity

Synonyms allow renaming or moving lower-level objects where only synonyms need to be redefined, while synonym-based applications can continue to work without any modifications, that is, you can create PUBLIC and private synonyms. PUBLIC synonyms are owned by special user groups named PUBLIC, in addition, each user in the database can access the private synonym which is included in the special user mode and can be used only by the user and the user's transferee.

 

Create Synonym

To CREATE a private SYNONYM in your own mode, you must have the create synonym permission, to CREATE a private SYNONYM in another user's mode, you must have the create any synonym permission. To CREATE a public synonym, you must have the create public synonym system permission.

CREATE a SYNONYM using the create synonym statement. The lower-level mode object does not exist when creating a SYNONYM.

You do not need the permission to create a public synonym named public_emp on the emp table contained in jward mode with the statement below the access object.

 

Create public synonym public_emp FOR jward. emp;

A001 user access Scott. emp

Select * from scott. emp

Demonstrate creating synonym scemp;

Create synonym scemp for scott. emp

Demonstrate access to emp in a001: select * from scemp;

 

Delete Synonym

Use the drop synonym statement to delete unnecessary synonyms. If you want to delete a private SYNONYM, the PUBLIC keyword is omitted. If you want to delete a public synonym, the PUBLIC keyword must be included.

 

Drop synonym emp; -- delete a private SYNONYM named emp

Drop public synonym public_emp; -- delete a public synonym named public_emp.

 

L sequence

 

Create Sequence

To CREATE a SEQUENCE in your mode, you must have the create sequence system permission. To CREATE a SEQUENCE in another user's mode, you must have the create any sequence permission.

Use the create sequence statement to CREATE a SEQUENCE. For example, the following statement creates a SEQUENCE for emp. The empno column in the table generates employee numbers.

 

Create sequence emp_sequence

Increment by 1 -- step size is 1

Start with 1 -- START is 1

NOMAXVALUE -- no maximum value [or Maxvalue 100, Minvalue 1]

NOCYCLE -- indicates non-circular [or CYCLE indicates a Maxvalue loop, starting from minvalue]

CACHE 10; -- pre-allocate a group of 10 serial numbers. The default value is 20.

Example:

Insert into table name value (emp_sequence.Nextval, 'ddd ')

-- Auto-increment column. emp_sequence.Nextval indicates that the number of the current auto-increment column is returned.

The CACHE option pre-allocates a set of serial numbers and stores them in the memory, this allows you to access the serial number more quickly. When the last serial number in the cache is used by Oracle, you can read another serial number and put it into the cache.

If you choose to store a set of serial numbers in the cache, Oracle may skip the serial number. For example, when the instance is shut down improperly, for example, when the instance fails or the shutdown abort statement is published, it is already placed in the cache but not the serial numbers used will be lost, which are also used but not saved, and Oracle may also skip the serial numbers in the cache after the import and export.

 

Change Sequence

To change the SEQUENCE, your mode must contain the SEQUENCE or you must have the alter any sequence system permission to change the SEQUENCE to modify ANY parameter that defines how it generates the SEQUENCE number except the SEQUENCE start number. you can delete the sequence before re-creating the sequence.

DDL will lose the value in the cache.

Use the alter sequence statement to change the SEQUENCE. For example, use the following statement to change the emp_sequence SEQUENCE.

 

Alter sequence emp_sequence

Increment by 10

Max value 10000

CYCLE

CACHE 20;

 

Delete Sequence

You can delete any sequence in your mode. to delete a SEQUENCE in another mode, you must have the permission to drop any sequence. If you no longer need a SEQUENCE, you can use the drop sequence statement to delete it.

For example, the following statement deletes the order _ seq sequence.

 

Drop sequence order_seq;

 

When a sequence is deleted, its definition will delete all synonyms of the sequence from the data dictionary.

An error is returned when you use these synonyms.

 

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/blackstreet/archive/2006/10/25/1351071.aspx