Examples of views, sequences, and simple paging in oracle

Examples of views, sequences, and simple paging in oracle
 
Use join on to join and group by to query multiple tables
Create View

Description View Structure
DESCRIBE view name

Modify View:
Create or replace view empv
(Id_number, name, sal, department_id)
As select employee_id, salary, department_id
FROM employees
WHERE department_id = 80;

Deleting a view only deletes the view definition and does not delete the base table.
Data
Drop view view; // view indicates the VIEW name.

You can use the with read only option to block DML operations on The View.
An Oracle server error is returned for any DML operation.





Insert is not allowed when the view definition contains the following elements:
Group functions
Group by clause
DISTINCT keyword
ROWNUM pseudo Column
Column definition as an expression
Columns not empty in the table are not included in the view definition.


Delete cannot be used when the view definition contains the following elements:
Group functions
Group by clause
DISTINCT keyword
ROWNUM pseudo Column

Update is unavailable when the view definition contains the following elements:
Group functions
Group by clause
DISTINCT keyword
ROWNUM pseudo Column
Column definition as an expression





Set the null value in sal to 0 and sort it in descending order of sal.


The above situation is equivalent to the above


Add a virtual rownum column,
As shown in
Because he will first sort rownum and then sort it in descending order, it will disrupt the order






To view information about sal's top three employees.
It is hereby emphasized that:
Greater than the number (shown in) is not supported)







The first two figures
This is equivalent to a simple paging operation for oracle Data. query data by Page Based on rownum.









Sequence: a database object that can be used by multiple users to generate unique values.
Unique value automatically provided
Shared object
It is mainly used to provide the primary key value.
Loading Sequence values into memory improves access efficiency


Create a sequence, starting from 1, increasing by 1 each time, the maximum value is 999999, there is no cycle, the cache is 5,


Query Sequence
SELECT dept_seq.CURRVAL FROM dual;

Generally, NEXTVAL returns the next valid value in the sequence, which can be referenced by any user.
Store the current value of the sequence in CURRVAL
NEXTVAL should be specified before CURRVAL, and both should be valid at the same time

Several issues worth noting:
Loading Sequence values into memory improves access efficiency
The sequence has cracks in the following cases:
Rollback
System exception
Multiple tables use the same sequence at the same time
If you do not load the sequence value into the memory (NOCACHE), you can use the USER_SEQUENCES table to view the current valid values of the sequence.