Oracle learning notes View

1. What is a view? A view is also called a virtual table. A logical expression VIEW of a group of data corresponds to a SELECT statement.

1. What is a view? A view is also called a virtual table. A logical expression VIEW of a group of data corresponds to a SELECT statement.

1. What is a view?

A view is also called a virtual table. It is a logical expression of a group of data.

A view corresponds to a SELECT statement, and the result set is assigned a name, that is, the view name.

The view itself does not contain any data. It is only a query statement mapped to the base table. When the base table data changes, the view data also changes.

Role: reuse code to protect data

2. You must have the permission grant create view to user scott to create a VIEW;

Create view view_name alias

A function is called a simple view.

Complex view containing function expressions

Connecting views based on multiple tables

Or replace to update a view if it exists

Syntax

Create or replace view name SELECT statement

Hide column names by adding aliases in SELECT statements

Create or replace view v_emp_10 as select empno id, ename name, sal salary, deptno FROM emp WHERE deptno = 10;

View view structure DESC view name

After adding an alias, you can only use the alias to query the view's SELECT name, salary, and work FROM v_emp_10;

Add with check option to prevent dirty data from being inserted to the base table in simple view mode.

Create or replace view v_emp_10 as select empno id,

Ename name, sal salary, deptno FROM emp WHERE deptno = 10 with check option;

Complex view Creation

Create view v_emp_salary

AS

SELECT d. dname,

AVG (e. sal) avg_sal,

SUM (e. sal) sum_sal,

MAX (e. sal) max_sal,

MIN (e. sal) min_sal

FROM emp e JOIN dept d ON e. deptno = d. deptno group by d. dname;

Query complex views

SELECT * FROM v_emp_salary;

Complex views do not allow DML operations

This article permanently updates the link address: