A View is a shortcut similar to a table but not a physical object. A view can contain data from a table or multiple tables, or a query result. All operations carried out on tables can also be applied to views. Views can be updated, deleted or inserted into. Views can be used to simple table, reducing a query’s complexity.
The data dictionary is a good example of a collection of complex joins appearing as more simple tables. The ALL_CONSTRAINTS view, for example, is a complex nine-table join that also includes two subqueries. Trying to navigate the base tables used in the dictionary views is a bit daunting, but the dictionary views are much more usable because they hide a great deal of the underlying complexity.
Likewise, some data is deemed too sensitive for most access, such as the passwords associated with a database link. The data is stored in the underlying base table link$, but not revealed in the dictionary views DBA_DB_LINKS or ALL_DB_LINKS. Because access to the base table is restrictive, but the dictionary views are widely accessed, the database can hide the sensitive data in the base tables.
To create a view named empv that is based on a combination of data in both the EMPLOYEES and DEPARTMENTS tables, you can execute the following:
CREATE OR REPLACE VIEW empv
(employee_name, department, manager, hire_date)
AS SELECT E.first_name||' '||E.last_name, D.dept_name,
M.first_name||' '||M.last_name, E.hire_date
FROM employees E,departments D, employees M
WHERE E.dept_nbr = D.dept_nbr
AND D.manager_id = M.employee_id;
The OR REPLACE keywords tell the database to replace the view definition if it already exists. If the OR REPLACE keyword is not included, the statement fails if the view already exists. The column list in the parentheses is the list of column names as they appear in the view and correspond positionally with the column expressions in the query—the first expression the query maps to the first column name for the view, the second expression in the query, to the second column name, and so on. You can choose not to include salary or commission in the view definition and only grant privileges on the view, thus restricting access to sensitive data.
To remove a view from the database, use a DROP VIEW statement, like this:
DROP VIEW empv;
Sequences are schema objects that generate unique integers. They are frequently used to generate primary key values.
The CREATE SEQUENCE statement creates new sequences. At a minimum, the sequence name must be given. Other attributes that can be assigned to the sequence in the CREATE statement are:
(CEIL (MAXVALUE - MINVALUE)) / ABS(INCREMENT BY)
To create a sequence called employees_seq that generates unique integers, starting with 100500 and incrementing by 1, use the following statement:
CREATE SEQUENCE employees_seq START WITH 100500 NOMAXVALUE NOMINVALUE;
To request the next integer from the sequence, reference the pseudocolumn NEXTVAL in a SQL statement, like this:
SELECT employees_seq.nextval FROM dual;
These values can be used in INSERT statements or in UPDATE statements, like this:
INSERT INTO employees (employee_id, hire_date) SELECT employees_seq.NEXTVAL, hired_date FROM merged_employees;
You can use the ALTER SEQUENCE statement to modify the INCREMENT BY, MAXVALUE, MINVALUE, CYCLE, and CACHE attributes, but not the START WITH attribute, like this: ALTER SEQUENCE employees_seq INCREMENT BY -1;
If you need to reset the next value that a sequence will generate, drop it, re-create it, and then re-grant any privileges on it. To drop a sequence, use the DROP SEQUENCE statement, like this:
DROP SEQUENCE employee_seq;