All language subtitles for [SubtitleTools.com] Views - Learning Oracle 12c [Video]

In this lesson, we're
looking at views.

So the queries that we do here
are fairly simple, but queries

can be very, very complex.

I myself have seen queries
that were pages and pages long,

just of a single SELECT
statement, that's

doing lots of joins and
subqueries and various things

so that it makes it
very, very complex.

So using a view allows us
to encapsulate a SQL query

as a database object.

So views are queries that are
stored as database objects.

So what do we use views for?

Well, mainly to reduce
query complexity.

So rather than having someone
that runs this enormous SQL

statement, this
SELECT statement,

on a number of different times,
maybe having to type it in,

maybe having to store
it in a file somewhere

and then look for
it and go get it,

we can actually
encapsulate that query--

that SELECT statement--
into a view that's actually

stored in the database.

And then other people can access
it, and the user can access it.

The other things that
we use views to do

is what's called data hiding.

So let's say that we wanted a
certain user to be able to see

certain things in a table--

certain columns, but
maybe not other columns.

So maybe it's an
employee table, and we

want them to be
able to see things

like the first
name and last name,

but maybe not their salary.

Then we can use a
view to mask that,

and we let the user look at the
view and not the table itself.

And it's also
important to understand

that views can be updatable
in certain situations,

although that's not
always advisable,

because it comes with a
certain amount of complexity.

So let's go ahead,
connect to Scott.

Now, just to be a
little different,

it's important,
in this situation,

that the Scott user
has the permissions

to create a view on his tables.

Just to show you a little bit
different way to do it here,

let's log in with SQL*Plus
rather than SQL Developer,

as a privileged user to
grant Scott that privilege.

So we're going to
do sqlplus system

and grant create view to Scott.

All right, so let's
begin by looking

at a little more complex query.

This is going to be
a join from the emp

table and the dept table.

So let's put this together.

So so far I have the empno,
ename, and job columns

from the employee table, and
the dname and loc columns

from the dept table.

Aliased as e, dep aliased as d,
and join on the deptno column.

All right, so it's
joining data from the emp

table and the dept table.

So rather than having to type
this query in anytime we need

to do this particular join,
we can essentially just

grab the SELECT statement
itself and create a view.

So create view,
and then the name,

as, and then the
SELECT statement.

So remembering what
this data looks like,

we'll create the view, and then
simply select * from emp_view.

When we execute this
statement, it's basically

going to come back to
the emp_view and it says,

oh, that's defined
as this query.

And it's going to
return the same data--

data from emp and dept.
We're using a view

to simplify query complexity.

And the other type of view
we do is the selective view

for data hiding.

So let's look at the emp table.

So let's say that
I want a user to be

able to see ename
and job, manager,

and hire date, but not
these other columns.

Then I can employ a
view for data hiding.

So I'll do create view
emp_sel_view as select ename,

job, mgr-- for manager--

and hire date-- so
the columns that I do

want the user to be
able to see from emp.

And now the user would
simply use this query.

And we can see that only those
columns that we've specified

in the selective view are
available to that user,

and thereby we're
accomplishing data hiding.

So those are just two ways
that we can use views.