Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use the database object view in Oracle, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

-Common database objects:

An object description table is a basic collection of data stores, a sequence of logically related data sets extracted from the table by rows and columns provides a regular numerical index to improve the efficiency of the query synonym to the object 1, Oracle view introduction and creation a, what is a view?

A view is a virtual table, which is based on existing tables, and the tables on which the view is built are called base tables.

Statements that provide data content to the view are called SELECT statements.

B. Why use views?

There are two main reasons:

1, security reasons, restrict data access; 2, views can make complex queries easy to understand and use.

C. Classification of views

Views are divided into simple views, complex views, connected views, and read-only views:

1. The simple view only gets data from a single table; it does not contain functions and data sets.

2. Complex views get data from multiple tables, including functions and data sets.

3. Join view refers to the view based on multiple tables, which can simplify the join query.

4. Read-only view only allows the use of select statements, not other DML statements.

Additional note: DML (data manipulation language): they are SELECT, UPDATE, INSERT, DELETE, just like its name, these four commands are used to manipulate the data in the database.

D. How to create a view?

Syntax format for creating views: CREATE [OR REPLACE] VIEW view [(alias [, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY]

It is important to note that the Oracle database user must have CREATRE VIEW privileges to create views.

An example of creating a simple view:

Create a view to query the employee number, name, and salary.

SQL > conn sys/02000059 as sysdba; is connected. SQL > grant create view to scott; authorization succeeded. SQL > conn scott/02000059 is connected. SQL > create VIEW emp_view AS select empno, ename, sal FROM emp; view has been created.

A view is a virtual table that, like a table, can be queried through a SELECT statement.

SQL > select * from emp_view; EMPNO ENAME SAL 7951 EASON 3000 7369 G_EASON 7499 ALLEN 1600. 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 15 rows selected.

You can change the name of the column when you create the view.

SQL > create VIEW emp_view2 (employee number, employee name, salary) AS select empno, ename, sal FROM emp; view has been created. SQL > select * from emp_view2; employee name salary 7951 EASON 3000 7369 G_EASON 7499 ALLEN 1600 7521 WARD 1250. 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 15 rows have been selected.

Example of creating a connection view:

Set up to obtain the department number, department name, and employee information for department number 10.

SQL > CREATE VIEW dept_emp_view AS SELECT d.deptno, d.dname, e.empno, e.ename, e.job FROM dept d, emp e WHERE d.deptno = e.deptno AND d.deptno = 10; View has been created. SQL > select * from dept_emp_view; DEPTNO DNAME EMPNO ENAME JOB 10 ACCOUNTING 7782 CLARK MANAGER 10 ACCOUNTING 7839 KING PRESIDENT 10 ACCOUNTING 7934 MILLER CLERK

Read-only view example: create a view to view employee information in department 10.

SQL > CREATE VIEW emp_view3 AS select * from emp where deptno = 10 with read only; view has been created. SQL > update emp_view3 set sal = sal + 50th update emp_view3 set sal = sal + 50 there is an error in line 1: ORA-42399: unable to perform DML operation on read-only view 2, query modification and deletion of Oracle view a, DML operation on view

Query view-select, such as: select * from emp_view

Add data-insert, such as: insert into empnew_view (empno, ename, sal) values (8888, 'LAYNA', 6666)

Modify data-update, for example: update emp_view set sal = sal + 100 where empno = 8888

Delete data-delete, for example: delete from emp_view where empno = 8888

Note: the update operations for the view (insert, update, delete) actually change the data in the base table.

B. Define check constraints when creating views

The WITH CHECK OPTION option is used to define CHECK constraints on the view

Example: CHREATE VIEW emp_view4 AS SELECT * FROM emp_view WHERE deptno = 20 WITH CHECK OPTION chk_view

SQL > create view dept_emp_view2 as select * from dept_emp_view where deptno = 10 with check option constraint ck_view; view has been created. SQL > select * from dept_emp_view2; DEPTNO DNAME EMPNO ENAME JOB 10 ACCOUNTING 7782 CLARK MANAGER 10 ACCOUNTING 7839 KING PRESIDENT 10 ACCOUNTING 7934 MILLER CLERKSQL > update dept_emp_view set deptno = 20 where empno = 7782 Update dept_emp_view set deptno = 20 where empno = 7782 error occurred in line 1: ORA-01779: cannot modify the column corresponding to the non-key value save table

The role of the check constraint is that when adding or modifying data, the data must meet the conditions of the where clause before with check option. As in the above example: the value of deptno must be 10 when modifying and adding data.

C. Modify the view

Use the CREATE OR REPLACE VIEW clause to modify the view.

Example: CREATE OR REPLACE VIEW emp_view AS SELECT * FROM emp WHERE job = 'SALESMAN'

SQL > CREATE OR REPLACE VIEW emp_view AS SELECT * FROM emp WHERE job = 'SALESMAN'; view has been created. D. Delete the view

When the view is no longer needed, the user can execute the DROP view statement to delete the view. (base table will not be deleted)

The SQL > DROP VIEW emp_view; view has been deleted. There is an error in line 1 of SQL > select * from emp_view;select * from emp_ view: ORA-00942: table or view does not exist 3, Oracle's complex view a, what is a complex view?

A complex view is a view that contains functions, expressions, or grouped data, which is mainly used to perform query operations.

Note: when defining a complex view, you must define a column alias for a function or expression.

B. Create a complex view

Example: create a view to get the average salary, the sum of wages, the maximum wage, and the minimum wage for each job.

SQL > create VIEW job_view (job, avgsal, sumsal, maxsal, minsal) AS SELECT job, avg (sal), sum (sal), max (sal), min (sal) FROM emp GROUP BY job; views have been created. SQL > select * from job_view;JOB AVGSAL SUMSAL MAXSAL MINSALCLERK 1037.5 4150 1300 800SALESMAN 1400 5600 1600 1250PRESIDENT 5000 5000 5000 5000MANAGER 2758.33333 8275 2975 2450ANALYST 3000 9000 3000 3000c principles for performing DML operations on complex views

DELETE operation principle, the view contains one of the following elements that cannot perform delete operations:

Group by clause, grouping function, distinct keyword, rownum pseudo column.

UPDATE operation principle, the view contains one of the following elements that cannot perform update operations:

Group by clauses, grouping functions, dinstinct keywords, rownum pseudo columns, columns defined by expressions.

INSERT operation principle, the view contains one of the following elements that cannot perform insert operations:

Group by clauses, grouping functions, dinstinct keywords, rownum pseudo columns, columns defined using expressions, NOT NULL columns that do not contain the base table on the view.

Thank you for reading this article carefully. I hope the article "how to use Database object View in Oracle" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.