Usage of ORACLE view and sequence

The use of views and sequences in Oracle:

View

Views are obtained by querying one or more table definitions, and the table on which the view is defined is referred to as the base table, and the simple point is that the view unites one or more tables and a table is created to perform search and DML operations on the table.

Build a View

Create View Terminal_view as (select A.terminal_name, B.pdline_name,c.process_name,d.stage_name,e.emp_name,a.update_ Time from  sys_terminal A, sys_pdline B, sys_process C, Sys_stage D, sys_emp e where  a.pdline_id = b.pdline_id and a.process_id = c.process_id and a.stage_id = d.stage_id and A.update_userid = e.emp_id);

Notice that the as is actually a SELECT statement, the data that the statement queries to form a view, the change of the relevant base table data will immediately reflect the view above.

The view is used the same way as normal table:

Attention:

In effect, DML is performed on the view, which is the base table. For a view of a multi-table join, you must ensure that the following conditions are required for DML operations:

(1) In the SQL statement that creates the view, you cannot have a function of group by, Compute,order by, or set operation.

(2) in the created view, each basic primary key must be included.

(3) In the SQL statement that creates the view, you cannot have a subquery statement, that is, the SQL statement that creates the view must be a simple SQL statement

Sequence

Oracle provides a sequence object that provides self-growing serial numbers from the system, typically used to generate the self-growing primary key or ordinal of a database data record.

Sequence is a sequence of numbers automatically added by a database system in accordance with certain rules. This sequence is generally used as the surrogate primary key (because it is not duplicated) and has no other meaning.

Create

Create Sequence
You first have to have create sequence or create any sequence permissions,
CREATE SEQUENCE emp_sequence
INCREMENT by 1-add a few each time
Start with 1-counting starting from 1
nomaxvalue-do not set the maximum value
Nocycle-always accumulates, does not circulate
CACHE 10;
Once you've defined emp_sequence, you can use Currval,nextval
Currval= returns the current value of the sequence
Nextval= increases the value of sequence and then returns the sequence value

As below, the starting point is 3, each growth 2, the maximum 999, can be recycled:

CREATE SEQUENCE seq_lc_test INCREMENT by 2 START with 3 MAXVALUE 999 MINVALUE 1 CYCLE CACHE 3 ORDER;

How to use:

SELECT Seq_lc_test. Nextval from dual;--each use nextval the corresponding sequence will increment the select Seq_lc_test one time. Currval from dual;--returns the current value, not self-increment

INSERT into EMP VALUES (<span style= "font-family:arial, Helvetica, Sans-serif;" >seq_lc_test</span><span style= "font-family:arial, Helvetica, Sans-serif;" >.nextval, ' LEWIS ', ' clerk ', 7902, Sysdate, $, NULL, 20);--typically placed in INSERT statements </span>

Attention:

The first nextval returns the initial value, and the subsequent nextval automatically increases the increment by value that you define, and then returns the incremented value. Currval always returns the value of the current sequence, but the currval is not used until the first nextval is initialized, otherwise an error occurs. Once nextval will increase the value of sequence once, so if you use Nextval in a different SQL statement, the value is not the same.

Usage of ORACLE view and sequence