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

In this lesson,
we'll take a look

at sequences in a database.

So to begin our
discussion of sequences,

let's talk about a
couple of terms here,

natural keys versus
synthetic keys.

So a natural key is a value
in a database or column

value that actually uses
the true data that's

being stored in the database.

So if we have an
employee table, and we

have a column for
social security number,

we could use that
as a primary key.

Because we make the assumption
that value for social security

number would conform to
the rules of a primary key,

mainly, that there are
no duplicates allowed.

So there's no duplicate
social security numbers.

A synthetic key is a key
value that we add, say,

for a primary key that
is completely generated.

So it's synthetic.

It has no relationship to the
data itself in any real way,

except to act as a
primary key value.

So there's pros and cons for
both ways when you do this.

But it's really important, in
my opinion, to choose a method

and stick with it.

So natural keys have the
benefit of using less space,

and they don't require
an additional column.

It's only the space that would
be used in a synthetic key, so

that key value,
or the key values

throughout all the
tables in the database.

But there is some savings
there, and also you

don't have to have
that extra column.

Synthetic keys, they're going
to actually abstract the values

from the actual data.

So to give you an example
of a coding standard I've

seen in the past is you have
a data model with many tables,

and every table has
to have a primary key.

In many cases,
those primary keys

can't be established
from the data that's

in those tables themselves.

There may not be
any column value

that can be used to actually
make a primary key value

or to make a value
that allows that value

to distinctly identify that
row from all of the other rows.

And so, since the rules
were that a table must

have our primary key,
every one of the tables

had a synthetic key
for the primary key.

So a sequential number,
1, 2, 3, on and on,

was generated for that table.

And then that table had
the primary key values

that were actually
separate from the data.

They didn't have
any relationship.

But they could
function in the way

that a relational
table needed to.

In my opinion, synthetic
keys are beneficial,

because there are so many
problems in data models

trying to find a column or
columns that can uniquely

identify every row in a table.

So if we choose to
use synthetic keys,

the sequence can
be very important.

So a sequence is a
database object that

generates sequential numbers.

And that's really all it does.

Now, of course, if we decided
to use synthetic keys,

we could have our
code constructed

to go out and search for
a value, the greatest

value in the table, increment by
one, and then insert that row.

But a sequence
keeps us from having

to do that, because we can just
reference the sequence itself,

and say, give me the
next available number

in the sequence.

And so we use a couple of
modifiers, NEXTVAL and CURRVAL

in order to advance
our sequences.

So this is much easier to
show than it is to explain.

So let's take a look
at what we have here.

Let's connect to our
Scott user, and we're

going to do select star from
salgrade order by grade.

So we have this grade column,
which is incrementing numbers.

So these are salary grades.

So grade 1 has a low sal of
700 and a high sal of 1,200,

and so on, and so forth.

So if we wanted to add
more salary grades,

we need to make
sure that grade--

if we're going to use
grade is the primary key--

is never a duplicate.

So we can't have another grade
5 with different high sal

and low sal.

Now again, we could
go out in our code,

and we could select the max
value for grade, increment

by one, and then do the insert.

Or we could use a sequence.

So let's say, select
max(grade) from salgrade.

Now we can see it.

But just to
establish this, let's

say, OK, that's the
maximum value for the grade

column in salgrade.

Now I'm going to attempt
to create a sequence.

Salgrade_seq, and we
put INCREMENT by 1,

and start with 6.

Why do we start with 6?

Well, because our max
value right now is 5.

So we're going to use a sequence
to insert data into salgrade

from now on.

And so, that's
what we want to do.

We have the sequence name.

Increment by whatever
value we want.

In this case, we'll
increment by 1.

You can increment by 2,
or 5, or 10, or whatever.

And then we have
the starting value.

So let's attempt to create this,
and the sequence is created.

So how do we use a sequence?

How do we reference it?

Well, let's say we have
a new salary grade to put

into the salgrade table.

So we could say, insert
into_salgrade values.

And instead of putting
a literal value here,

we're going to use the
sequencer name, salgrade_seq,

and the modifier nextval.

And then we'll put our high sal
and low sal to be consistent.

The row's inserted.

Now we select, and we see
that we have now grade 6

with the low sal and the
high sal that we requested.

So we didn't put a literal
6 into the insert statement.

We just referenced the sequence.

So wherever the sequence is
increment with the nextval

modifier and go ahead
and give us that value.

Notice that we started
with 6 and used nextval,

and yet that still
gave us the value of 6.

Because the first time
you use a sequence,

it has to be initialized
with that value.

So if we try a
similar statement.

Copy, and then we'll make this.

So again, just
referencing the sequence.

Row is inserted.

And now it's incremented to 7.

We can also use the
CURRVAL modifier

to see what the current
value of a sequence is.

So to do that, I
use the dual table.

And I can do select
salgrade_seq currval--

C-U-R-R-V-A-L-- from dual.

It show us that the
current value is 7.

In a similar way, I can use
NEXTVAL to actually advance

the sequence.

So select nextval for dual,
now it's 8, 9, and 10.

And if we look at
the CURRVAL, it's 10.

So if we were to go
one more, the sequence

has already been advanced.

Now we insert, and select, and
notice that the value for grade

was advanced as we
used NEXTVAL to advance

the sequence a couple
of times, and then

we used NEXTVAL to reference
it, and so it advanced it again.

But again, the most
important thing

here generally in
these columns is

that there are no duplicates.

So if you have a standard in an
organization, where the coding

standards use sequences--

every one, every
user, every developer

can access those
sequences-- and there

may be gaps, which is usually
less problematic than having

duplicate values.

But you can ensure
that primary key,

that synthetic primary key, is
consistent across every table.