SQL PROGRAMMING STYLE- P18 pot
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (154.4 KB, 5 trang )
CHAPTER
7
How to Use VIEWS
The Blind Men and the Elephant
By John Godfrey Saxe (1816–1887)
It was six men of Indostan
To learning much inclined,
Who went to see the Elephant
(Though all of them were blind),
That each by observation
Might satisfy his mind.
The First approached the Elephant,
And happening to fall
Against his broad and sturdy side,
At once began to bawl:
“God bless me! but the Elephant
Is very like a wall!”
The Second, feeling of the tusk,
Cried, “Ho! what have we here
So very round and smooth and sharp?
To me ‘tis mighty clear
This wonder of an Elephant
134 CHAPTER 7: HOW TO USE VIEWS
Is very like a spear!”
The Third approached the animal,
And happening to take
The squirming trunk within his hands,
Thus boldly up and spake:
“I see,” quoth he, “the Elephant
Is very like a snake!”
The Fourth reached out an eager hand,
And felt about the knee.
“What most this wondrous beast is like
Is mighty plain,” quoth he;
“Tis clear enough the Elephant
Is very like a tree!”
The Fifth, who chanced to touch the ear,
Said: “E’en the blindest man
Can tell what this resembles most;
Deny the fact who can
This marvel of an Elephant
Is very like a fan!”
The Sixth no sooner had begun
About the beast to grope,
Than, seizing on the swinging tail
That fell within his scope,
“I see,” quoth he, “the Elephant
Is very like a rope!”
And so these men of Indostan
Disputed loud and long,
Each in his own opinion
Exceeding stiff and strong,
Though each was partly in the right,
And all were in the wrong!
Moral:
So oft in theologic wars,
The disputants, I ween,
Rail on in utter ignorance
7.1 VIEW Naming Conventions Are the Same as Tables 135
Of what each other mean,
And prate about an Elephant
Not one of them has seen!
VIEWs are virtual tables, defined by SELECT statements stored in the
database. The SQL statement that defines the VIEW is executed only
when the VIEW is invoked in another statement. The standard says that
VIEWs are to act as if they are materialized, but in practice the optimizer
will decide to materialize them as physical tables or to insert the SELECT
statement in the definition into the query, invoking it and then
compiling it like a derived table. There are six basic uses for VIEWs that
we will discuss.
7.1 VIEW Naming Conventions Are the Same as Tables
Rationale:
A VIEW is a logical table. It consists of rows and columns, exactly the
same as a base table. A VIEW can be used in SELECT, UPDATE,
DELETE, and INSERT statements in the same way that a base table can.
Therefore, it stands to reason that VIEWs should utilize the same naming
conventions as are used for tables. As an aside, the same can be said for
aliases, synonyms, derived tables,
table-valued functions, or anything
that returns a table.
In particular, there is an absurd naming convention of putting a “v”
or “vw” in the first or last position of a VIEW name. My guess is that it
comes from programmers either who are used to weakly typed languages
that use Hungarian notation or who worked with file systems that had to
have prefixes to locate the physical drive for the file. In the ISO-11179,
the “vw” implies that the VIEW is a table dealing with Volkswagens.
Individuals who have a need to differentiate between tables and
VIEWs can utilize the schema information tables to determine which
objects are VIEWs and which objects are tables. They should be at the
system administration level or higher.
INSERT, UPDATE, and DELETE are operations that cannot be
performed on certain types of VIEWs. Users who need to do these
privileges can be given INSTEAD OF triggers and never know if they are
dealing with a VIEW or a base table.
Exceptions:
None
136 CHAPTER 7: HOW TO USE VIEWS
7.1.1 Always Specify Column Names
Rationale:
When creating VIEWs, SQL provides the option of specifying new
column names for the VIEW clause or defaulting to the same column
names as the defining SELECT statement. It is always advisable to
explicitly specify VIEW column names instead of allowing them to
default, even if using the same names as the underlying base tables. This
will provide for more accurate documentation.
Exceptions:
Make sure that the VIEW clause names are correct. If you misspell them,
that is what the user sees.
7.2 VIEWs Provide Row- and Column-Level Security
One of the most beneficial purposes served by VIEWs is to extend the
data security features of SQL. VIEWs can be created that provide a
subset of rows, a subset of columns, or a subset of both rows and
columns from the base table.
How do VIEWs help provide row- and column-level security?
Consider a “Personnel” table that contains all of the pertinent
information regarding an enterprise’s employees. Typically, name,
address, position, birthdate, and salary information would be contained
in such a table. However, not every user will require access to all of this
information. Specifically, it may become necessary to shield the salary
information from most users. You can accomplish this by creating a
VIEW that does not contain the salary column and then granting most
users the ability to access the VIEW, instead of the base table. The salary
column will not be visible to users of the VIEW.
Or perhaps you need to implement security at the row level. Consider
a table that contains project information. Typically, this would include
project name, purpose, start date, and who is responsible for the project.
Assume that the security requirements for projects within your
organization deem that only the employee who is responsible for the
project can access the project data. By storing the authorization ID of the
responsible employee in the “projects” table, a VIEW can be created
using the CURRENT_USER value.
CREATE VIEW MyProjects ( )
AS
SELECT
