Common database objects in Oracle-View

View

Database Object-View:

I. view definition: Logical Data Set extracted from the table

A) a view is a virtual table.

B) The view is built on an existing table. The view depends on the created tables, which are called base tables.

C) the statement that provides data content to the view is a SELECT statement, which can be understood as a stored SELECT statement.

D) The view provides users with another representation of the base table data.

Ii. Why use a view?

A) Data Access Control: provides a security mechanism to block data that users should not know when displaying data in a view.

B) Simplified query: The SELECT statement constructs a complex query statement, which must be called each time it is used.

C) avoid repeated access to the same data

3. Create a view:

A) embed a subquery in the create view statement.

I. Create [or replace] [force | noforce] view [(alias [, alias]…)]

As subquerytion

[With check option [constraint]

[With read only [constraint]

B) subqueries can be complex select statements.

I. Create view empvu

As select employee_id, last_name, salary

From employees

Where department_ed = 80;

C) Description view structure:

I. Describe empvu;

D. Define the column alias in the subquery when creating a view.

I. SQL> Create view usersview as select username name, password pass from users;

Ii. // query view

SQL> select * From usersview;

E) Use the create or replace view clause to modify the view

I. SQL> Create or replace view usersview as (select * from users );

F) Create a view with a function and a groupby clause

I. SQL> Create or replace view GDE as (select deptno, count (*) cou from EMP group by deptno );

G) create a connection view with the order by clause of the Group by clause

I. SQL> Create view gdeo as select e. deptno, count (*) cou from EMP e inner join dept D on E. deptno = D. deptno group by E. deptno order by E. deptno;

Iv. DML usage in views

A) DML operations can be performed in a simple view.

B) One of the following elements in the graph definition at that time cannot use delete:

I. Group functions

Ii. Group by clause

Iii. Distinct keywords

Iv. rownum pseudo Column

C. Update is unavailable when the view definition contains the following elements:

I. Group functions

Ii. Group by clause

Iii. Distinct keywords

Iv. rownum pseudo Column

V. The column is defined as an expression.

D) You can use the with read only option to block DML operations on The View.

D) Any DML operation will return an ORACLE Server Error

5. delete a view

A) Drop view name;