(오라클) view/뷰

"view"

view isn't a physical table, it is virtual (default) table that doesn't actually store the data.

DB developer should know how to use view in DB oracle, as the DB developer able to restrict the table that can be viewed by selected individuals/groups.

We gonna enter to 'hr' account to create a view.


The formula for view:

create view view_name
as
select first_name, last_name, email, hire_date
from employees;

The detailed formula for the view

create (or replace) [{force | no force }] view view_name
as
[with check option]
[with read only]; 

1. create or replace
actually, you can just use create only.
but if you use replace with the create, you can create&edit the view at the same time.

2. force | no force
you can force to create view even if you don't have default table.

3. with check optino
you can use check option to use update and insert in the selected area

4. with read only
you cannot edit the view with CRUD operation.


"Why do you use the view?"

Let's say we want to print out the employee's content with the department_id= 30;
so you have to write sth like

select empno, empname, depno from employees where depno = 30;

instead we can create a view to reduce the length of typing.

create view emp_view_depno_30
as
select empno, empname, sal, depno
from employees
where depno = 30;

It is really easy to create the view as you just paste 'create view view_name as' at the front of select ~ sentence.

now if you want to call the table again, you can just type this,

select * from emp_view_depno_30;


if we say view_name is sihyun_table,
the lucky table only consists of 4 components from employees table.
now you can see that we have restricted a user from reading all the data by creating view table.

sihyun_table ( view from employees table)

but actually, employees table contains lots of components.

employees table

To remove the object- > drop
To remove the data -> delete

since the view is the object, we use drop command.

drop view sihyun_table;


-

example:

View from the employees where department_id are 20 and 30.

CREATE OR REPLACE VIEW view_test_emp AS
SELECT employee_id, first_name, last_name, email, hire_date
FROM employees
WHERE department_id  IN (20,30);

So, we will make another view for 1~7 without Guy.

CREATE OR REPLACE VIEW v_emp_from_table AS
SELECT employee_id, first_name, last_name, email, hire_date
FROM view_test_emp

WHERE hire_date < TO_DATE('2007-01-01');

CREATE OR REPLACE VIEW v_emp_from_table AS
SELECT employee_id, first_name, last_name, email, hire_date
FROM view_test_emp
WHERE hire_date < TO_DATE('2007-01-01')

with read only;

with read only -> cannot change the data (CRUD cannot be performed.)

Let's try it with update example:
update v_emp_from_table
set last_name='easy'
where first_name = 'Den'

;

error-> you cannot perform a DML operation hahahaha