10 create view of Oracle9i learning notes

1. View
-Restrict access to the database
-Easy to implement complex queries
-Different Views can be generated for the same data

2. Create a view
1) embed a subquery in the create view statement.
Create [or replace] [force | noforce] view
[(Alias [, alias]...)]
As subquery
[With check option [constraint]
[With read only]

For example, create a view of empv, which contains only the details of employees in 10 departments.
Create view empv
As select empno, ename, job
From EMP
Where deptno = 10;

View: DESC empv

2) alias usage
Example 1: Create or replace view salv
As select empno employee_id, ename name, Sal salary
From EMP
Where deptno = 30;

Example 2: Create or replace view salv
(Employee_id, name, salary)
As select empno, ename, Sal
From EMP
Where deptno = 30;

3. Create a complex view
For example, create a complex view that is based on two tables and contains group functions.
Create or replace view dept_sum_v (name, MINSAL, maxsal, avgsal)
As select D. dname, min (E. Sal), max (E. Sal), AVG (E. Sal)
From emp e, DEPT d
Where E. deptno = D. deptno
Group by D. dname;

4. view the view
Data Dictionary user_views

Select view_name, text
From user_views;
Result:
View_name text
Salv select empno employee_id, ename name, Sal salary from EMP wh

5. retrieve data from the View
Select * From salv;

6. Execute DML operations on the View
1) if the view contains the following content, you cannot modify the data:
-Group function, group by clause, distinct keyword
-Columns defined using expressions
-Rownum pseudo Column

2) If the view contains the following content, the data row cannot be deleted.
-Group Function
-Group by clause
-Distinct keyword
-Rownum pseudo Column

7. With check option clause
-To ensure that the insert and update operations on the view are limited to a certain extent, you can use the with check option clause.
Example:
Create or replace view empv
As select *
From EMP
Where deptno = 20
With check option constraint empv_ck;

Test 1: Update empv set deptno = 10 where empno = 7369
Result:
Error is located in row 1st:
ORA-01402: view with check optidn violation where clause

Test 2: Update empv set sal = 2000 where empno = 7369
Result: 1 row has been updated.

8. Reject DML operations
-When defining a view, use the with read only option to ensure that DML operations cannot be performed on The View.
Example:
Create or replace view empv (employee_id, employ_name, job_title)
As select empno, ename, job
From EMP
Where deptno = 10
With read only;

9. delete a view
-Deleting a view does not delete data because the view is based on the base table in the database.
Drop view;

Example: Drop view empv;

10. Summary
A view is a logical table based on one or more tables or views. It does not contain data and has the following advantages:
-Restrict access to the database
-Simplified Query
-Maintain data independence
-Different Views can be created for the same data.
-Views can be deleted without affecting data.

Exercise
1. Create a view empv Based on the EMP table, select the ename, job, Sal, and deptno columns, and display the employee name, job type, salary, and department number respectively.
Requirement: only information about employees with salaries less than 1500 is displayed.

Create or replace view empv
As select ename "employee name", job "job type", Sal "salary", deptno "department no"
From EMP
Where Sal <1500;
Or:
Create or replace view empv ("employee name", "job type", "salary", "department no ")
As select ename, job, Sal, deptno
From EMP
Where Sal <1500;

2. Create the view dept_sum_v Based on the EMP and dept tables, and display the empno, ename, job, Sal, deptno, and dname columns.

Create or replace view dept_sum_v
As select empno, ename, job, Sal, D. deptno, dname
From emp e, DEPT d
Where E. deptno = D. deptno;

3. Create an empv view that displays the Department number, employee number, employee name, and salary.
Insert data through the view empv (2000, 'wang)

Create or replace view empv ("department no.", "employee No.", "employee name", "salary ")
As select deptno, empno, ename, Sal
From EMP;

Insert into empv
Values (2000, 'wang );

4. Create view empv1, which displays the Department ID, employee ID, employee name, salary, and annual salary.
Insert data (10, 'wang ',) through the empv1 view)

Create or replace view empv1
("Department no.", "employee No.", "employee name", "salary", "annual salary ")
As select deptno, empno, ename, Sal, Sal * 12
From EMP;

Insert into empv1
Values (, 'wang );

Result:
Insert into empv1
*
Error is located in row 1st:
ORA-01733: Virtual columns are not allowed here
 

5. Create view empv2, which displays the Department ID, employee name, salary, and employment date.
Insert data through the view empv2 (10, 'wang ', 2000, '2017-8-1 ')

Create or replace view empv2 ("department no.", "employee name", "salary", "employment date ")
As select deptno, ename, Sal, hiredate
From EMP;

Insert into empv2
Values (10, 'wang ', 2000, '2017-8-1 ');
Result:
Values (10, 'wang ', 2000, '2017-8-1 ')
*
Error is located in row 2nd:
ORA-01861: Text and format strings do not match

6. Create the empv3 view and display empno, ename, deptno, dname, Sal
Insert data through the view empv3 (1001, 'wang ', 10, 'date', 2000)

Create or replace view empv3
As select empno, ename, D. deptno, dname, Sal
From emp e, DEPT d
Where E. deptno = D. deptno;

Insert into empv3
Values (1001, 'wang ', 10, 'dashboard', 2000 );
Result:
Insert into empv3
*
Error is located in row 1st:
ORA-01776: Unable to modify multiple base tables through connection View

7. Update the employee ID of 1000 through the view empv, with a salary of 2500

Update empv
Set sal = 2500
Where e-mapreduce = 1000;

8. Create the view empv3, and display the Department ID, maximum salary, and minimum salary according to the department group.
Update a department numbered 10 to increase the minimum salary by 500

Create or replace view empv3
As select deptno, max (SAL) maxsal, min (SAL) MINSAL
From EMP
Group by deptno;

Update empv3
Set MINSAL = MINSAL + 500
Where deptno = 10;
Result:
Update empv3
*
Error is located in row 1st:
ORA-01732: The data manipulation operation for this view is invalid

9. view the created View

Select view_name, text
From user_views;

9. Delete all views created above

Drop view dept_sum_v;
Drop view empv;
Drop view empv1;
Drop view empv2;
Drop view empv3;