|
|
At its simplest, a view is a table specification that stores no data. It acts as a filter on both the columns and the rows of the underlying tables referenced in the view—a “window” through which actual data is exposed. The query that defines the view can be from one or more tables or other views in the current database. It behaves in many ways like a persistent table and encapsulates some special extensions to link it with the underlying tables.
You query a view as if it were an ordinary table and perform joins, order and group output, specify search conditions, subquery it, derive runtime columns from its virtual data, process a named or unnamed cursor selected from it, and so on.
Many views can be “updated,” thereby modifying the state of the underlying persistent tables, or they can be made updatable through triggers. When changes to the tables’ data are committed, the data content of the view changes with them. When a view’s data changes are committed, underlying tables’ data changes accordingly.
Keys and Indexes
Views cannot have keys or indexes. The underlying tables, known as base tables, will be used as the sources of indexes when the optimizer constructs query plans. The topic of query plans for queries involving views is quite complicated. It is discussed later in this chapter, in the section “Using Query Plans for Views.”
Row Ordering and Grouping
A view definition cannot be ordered. An exception is thrown if an ORDER BY clause is included. Consequently, it does not make sense to use the FIRST and/or SKIP quantifiers for SELECT, since they operate on ordered sets. A grouped query specification (using a legal GROUP BY clause) is fine.
Some Simple View Specifications
A view can be created from virtually any SELECT query specification. Examples are presented in the following sections.
A Vertical Subset of Columns froma Single Table
The JOB table in the employee.fdb database has eight columns: JOB _CODE, JOB _GRADE, JOB _COUNTRY, JOB _TITLE, MIN _SALARY, MAX _SALARY, JOB _REQUIREMENT, and LANGUAGE_REQ.
The following view returns a list of salary ranges (subset of columns) for all jobs (all rows) in the JOB table:
CREATE VIEW JOB_SALARY_RANGES AS SELECT JOB_CODE, MIN_SALARY, MAX_SALARY FROM JOB;A Horizontal Subset of Rows from a Single Table
The next view returns all of the columns in the JOB table, but only the subset of rows where the MAX_SALARY is less than $15,000:
CREATE VIEW LOW_PAYING_JOBS AS SELECT * FROM JOB WHERE MAX_SALARY < 15000;A Combined Vertical and Horizontal Subset
This view returns only the JOB_CODE and JOB_TITLE columns and only those jobs where MAX_SALARY is less than $15,000:
CREATE VIEW ENTRY_LEVEL_JOBS AS SELECT JOB_CODE, JOB_TITLE FROM JOB WHERE MAX_SALARY < 15000;A Subset of Rows and Columns from Multiple Tables
The next example shows a view that joins the JOB and EMPLOYEE tables. EMPLOYEE contains 11 columns: EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY, and FULL_NAME. It returns two columns from the JOB table and two columns from EMPLOYEE, filtering so that records for workers whose salary is $15,000 or more are suppressed:
CREATE VIEW ENTRY_LEVEL_WORKERS AS SELECT E.JOB_CODE, J.JOB_TITLE, E.FIRST_NAME, E.LAST_NAME FROM JOB J JOIN EMPLOYEE E ON J.JOB_CODE = E.JOB_CODE WHERE E.SALARY < 15000;Why Views Can Be Useful
The data requirements of an individual user or user group are often quite consistent. Views provide the means to create custom versions of the underlying tables to target clusters of data that are pertinent to specific users and their tasks. The following list summarizes the benefits of views:
Privileges
Because a view is a database object, it requires specific user privileges in order to be accessed. Through granting privileges to a view, it is possible to provide users with very fine-grained access to certain columns and rows from tables, while denying them access to other, more sensitive data stored in the underlying tables. In that case, the view is granted privileges to the tables and the users are granted privileges to the view.
Owner Privileges
The user that creates a view will be its owner. In order to create the view, the user must have the appropriate privileges on the base tables:
Additionally, the owners of the base tables and other views accessed by the view must grant all required privileges for accessing those objects and for modifying them through the view if required, to the view itself. That is, privileges on those base objects must be granted to the view.
The owner of the view has all privileges for it, including the ability to grant privileges on it to other users, to triggers, and to stored procedures.
User Privileges
The creator of the view must grant the appropriate privileges to users, stored procedures, and other views that need to access the view. A user can be granted privileges to a view without having access to its base tables.
In the case of updatable views, INSERT, UPDATE, and DELETE privileges must be assigned to any users who need to perform DML on underlying tables through the view. Conversely, grant the users only SELECT privileges if your intention is to provide a read-only view.
If a user already has the required rights on the view ’s base objects, it will automatically have the same rights on the view.
|
|
Firebird Related Tutorials |
|
---|---|
MySQL Tutorial | Linux Tutorial |
Windows 10 Tutorial | Windows 10 Development Tutorial |
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.