Most texts
covering SQL define the FROM clause of a
SELECT statement as containing a list of tables and/or views. Please
abandon this definition and replace it with the following:
The FROM clause contains a list of data sets.
In this light, it is easy to see how the FROM clause can contain
tables (permanent data sets), views (virtual data sets), and SELECT
statements (temporary data sets). SELECT statements, or inline views
as mentioned earlier, are one of the most powerful, yet underutilized
features of Oracle SQL.
 |
In our opinion, the name "inline
view" is confusing and tends to intimidate people.
Since it is a subquery that executes prior to the containing query, a
more palatable name might have been
"pre-query."
|
|
Here's a simple example of an inline view:
SELECT d.dept_id, d.name, emp_cnt.tot
FROM department d INNER JOIN
(SELECT dept_id, COUNT(*) tot
FROM employee
GROUP BY dept_id) emp_cnt
ON d.dept_id = emp_cnt.dept_id;
DEPT_ID NAME TOT
---------- -------------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
In this example, the FROM clause references the
department table and an inline view called
emp_cnt, which calculates the number of employees
in each department. The two sets are joined using
dept_id and the ID, name, and employee count are
returned for each department. While this example is fairly simple,
inline views allow you to do things in a single query that might
otherwise require multiple select statements or a procedural language
to accomplish.
5.4.1 Inline View Basics
Because the
result set from an inline view is
referenced by other elements of the containing query, you must give
your inline view a name and provide
aliases for all ambiguous columns. In
the previous example, the inline view was given the name
"emp_cnt", and the alias
"tot" was assigned to the
COUNT(*) column. Similar to other types of
subqueries, inline views may join multiple tables, call built-in and
user-defined functions, specify optimizer hints, and include GROUP
BY, HAVING, and CONNECT BY clauses. Unlike other types of subqueries,
an inline view may also contain an ORDER BY clause, which opens
several interesting possibilities (see Section 5.5 later in the chapter for an
example using ORDER BY in a subquery).
Inline views are particularly useful when you need to combine data at
different levels of aggregation. In the previous example, we needed
to retrieve all rows from the department table and
include aggregated data from the employee table,
so we chose to do the aggregation within an inline view and join the
results to the department table. Anyone involved
in report generation or data warehouse extraction, transformation,
and load (ETL) applications has doubtless encountered situations
where data from various levels of aggregation needs to be combined;
with inline views, you should be able to produce the desired results
in a single SQL statement rather than having to break the logic into
multiple pieces or write code in a procedural language.
When considering using an inline view, ask yourself the following
questions:
What value does the inline view add to the readability and, more
importantly, the performance of the containing query?
How large will the result set generated by the inline view be?
How often, if ever, will I have need of this particular data set?
Generally, using an inline view should enhance the readability and
performance of the query, and it should generate a manageable data
set that is of no value to other statements or sessions; otherwise,
you may want to consider building a permanent or temporary table so
that you can share the data between sessions and build additional
indexes as needed.
5.4.2 Query Execution
Inline views
are always
executed prior to the containing query and, thus, may not reference
columns from other tables or inline views from the same query. After
execution, the containing query interacts with an inline view as if
it were an unindexed, in-memory table. If inline views are nested,
the innermost inline view is executed first, followed by the
next-innermost inline view, and so on. Consider the following query:
SELECT d.dept_id dept_id, d.name dept_name,
dept_orders.tot_orders tot_orders
FROM department d INNER JOIN
(SELECT e.dept_id dept_id, SUM(emp_orders.tot_orders) tot_orders
FROM employee e INNER JOIN
(SELECT sales_emp_id, COUNT(*) tot_orders
FROM cust_order
WHERE order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
AND cancelled_dt IS NULL
GROUP BY sales_emp_id
) emp_orders
ON e.emp_id = emp_orders.sales_emp_id
GROUP BY e.dept_id
) dept_orders
ON d.dept_id = dept_orders.dept_id;
DEPT_ID DEPT_NAME TOT_ORDERS
---------- -------------------- ----------
30 SALES 6
If you're new to inline views, this query might be
intimidating. Start with the innermost query, understand the result
set generated by that query, and move outward to the next level.
Since inline views must be noncorrelated, you can run each inline
view's SELECT statement individually and look at the
results. (From the standpoint of the inline view, this would
constitute an "out-of-query
experience.") For the previous query, executing the
emp_orders inline view generates the following
result set:
SELECT sales_emp_id, COUNT(*) tot_orders
FROM cust_order
WHERE order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
AND cancelled_dt IS NULL
GROUP BY sales_emp_id;
SALES_EMP_ID TOT_ORDERS
------------ ----------
7354 4
7368 4
7654 6
The emp_orders set contains all salespeople who
booked orders since 2001, along with the total number of orders
booked. The next level up is the dept_orders
inline view, which joins the emp_orders data set
to the employee table and aggregates the number of
orders up to the department level. The resulting data set looks as
follows:
SELECT e.dept_id dept_id, SUM(emp_orders.tot_orders) tot_orders
FROM employee e INNER JOIN
(SELECT sales_emp_id, COUNT(*) tot_orders
FROM cust_order
WHERE order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
AND cancelled_dt IS NULL
GROUP BY sales_emp_id
) emp_orders
ON e.emp_id = emp_orders.sales_emp_id
GROUP BY e.dept_id;
DEPT_ID TOT_ORDERS
---------- ----------
30 6
Finally, the dept_orders set is joined to the
department table, and the final result set is:
DEPT_ID DEPT_NAME TOT_ORDERS
---------- -------------------- ----------
30 SALES 6
After query execution completes, the emp_orders
and dept_orders result sets are discarded.
5.4.3 Data Set Fabrication
Along
with
querying existing tables, inline views may be used to fabricate
special-purpose data sets that don't exist in the
database. For example, you might want to aggregate orders over the
past year by small, medium, and large orders, but the concept of
order sizes may not have been defined in your database. You could
build a table with three records to define the different sizes and
their boundaries, but you only need this information for a single
query, and you don't want to clutter the database
with dozens of small, special-purpose tables. One solution is to use
the UNION set operator to combine individual
sets of data into a single set. (Set operators will be covered in
detail in Chapter 7.) For example:
SELECT 'SMALL' name, 0 lower_bound, 29 upper_bound from dual
UNION ALL
SELECT 'MEDIUM' name, 30 lower_bound, 79 upper_bound from dual
UNION ALL
SELECT 'LARGE' name, 80 lower_bound, 9999999 upper_bound from dual;
NAME LOWER_BOUND UPPER_BOUND
------ ----------- -----------
SMALL 0 29
MEDIUM 30 79
LARGE 80 9999999
You can then wrap this query in an inline view and use it to do your
aggregations:
SELECT sizes.name order_size, SUM(co.sale_price) tot_dollars
FROM cust_order co INNER JOIN
(SELECT 'SMALL' name, 0 lower_bound, 29 upper_bound from dual
UNION ALL
SELECT 'MEDIUM' name, 30 lower_bound, 79 upper_bound from dual
UNION ALL
SELECT 'LARGE' name, 80 lower_bound, 9999999 upper_bound from dual
) sizes
ON co.sale_price BETWEEN sizes.lower_bound AND sizes.upper_bound
WHERE co.cancelled_dt IS NULL
AND co.order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
AND co.order_dt < TO_DATE('01-JAN-2002','DD-MON-YYYY')
GROUP BY sizes.name
ORDER BY sizes.name DESC;
ORDER_ TOT_DOLLARS
------ -----------
SMALL 100
MEDIUM 292
LARGE 396
One word of caution: when constructing a set of
ranges, make sure there are no gaps
through which data may slip. For example, an order totaling $29.50
would not appear in either the small or medium categories, since
$29.50 is neither between $0 and $29 nor between $30 and $79. One
solution is to overlap the region boundaries so that there is no gap
through which data can slip. Note that you can no longer use BETWEEN
with this approach:
SELECT sizes.name order_size, SUM(co.sale_price) tot_dollars
FROM cust_order co INNER JOIN
(SELECT 'SMALL' name, 0 lower_bound, 30 upper_bound from dual
UNION ALL
SELECT 'MEDIUM' name, 30 lower_bound, 80 upper_bound from dual
UNION ALL
SELECT 'LARGE' name, 80 lower_bound, 9999999 upper_bound from dual
) sizes
ON co.sale_price >= sizes.lower_bound
AND co.sale_price < sizes.upper_bound
WHERE co.cancelled_dt IS NULL
AND co.order_dt >= TO_DATE('01-JAN-2001', 'DD-MON-YYYY')
AND co.order_dt < TO_DATE('01-JAN-2002', 'DD-MON-YYYY')
GROUP BY sizes.name
ORDER BY sizes.name DESC;
ORDER_ TOT_DOLLARS
------ -----------
SMALL 100
MEDIUM 292
LARGE 396
Now that you have neither an overlap nor a gap between the buckets,
you can be sure that no data will be left out of the aggregations.
Fabricated data sets can also be useful for
determining what data is not stored in a
database. For example, your manager might ask for a report listing
the aggregate sales for each day of the year 2001, including days
with no sales. Although the cust_order table
contains records for every day that had orders, there is no table in
the database containing a record for every day of the year. To
provide your manager with an answer, you will need to fabricate a
driving table containing a record for every day in 2001, and then
outer join it to the set of aggregated sales for the same period.
Since a year contains either 365 or 366 days, we will build the set
{0, 1, 2, ..., 399}, add each member of the set to the start date of
01-JAN-2001, and let Oracle throw away the rows that
don't belong in 2001. To build the set {0, 1, 2,
..., 399}, we will create the sets {0, 1, 2, ..., 10}, {0, 10, 20,
30, ..., 90}, and {0, 100, 200, 300} and add members of the three
sets across the Cartesian product:
SELECT ones.x + tens.x + hundreds.x tot
FROM
(SELECT 0 x FROM dual UNION ALL
SELECT 1 x FROM dual UNION ALL
SELECT 2 x FROM dual UNION ALL
SELECT 3 x FROM dual UNION ALL
SELECT 4 x FROM dual UNION ALL
SELECT 5 x FROM dual UNION ALL
SELECT 6 x FROM dual UNION ALL
SELECT 7 x FROM dual UNION ALL
SELECT 8 x FROM dual UNION ALL
SELECT 9 x FROM dual) ones
CROSS JOIN
(SELECT 0 x FROM dual UNION ALL
SELECT 10 x FROM dual UNION ALL
SELECT 20 x FROM dual UNION ALL
SELECT 30 x FROM dual UNION ALL
SELECT 40 x FROM dual UNION ALL
SELECT 50 x FROM dual UNION ALL
SELECT 60 x FROM dual UNION ALL
SELECT 70 x FROM dual UNION ALL
SELECT 80 x FROM dual UNION ALL
SELECT 90 x FROM dual) tens
CROSS JOIN
(SELECT 0 x FROM dual UNION ALL
SELECT 100 x FROM dual UNION ALL
SELECT 200 x FROM dual UNION ALL
SELECT 300 x FROM dual) hundreds;
TOT
----------
0
1
2
3
4
5
6
7
8
9
10
. . .
390
391
392
393
394
395
396
397
398
399
Since this query has no join conditions, every combination of the
rows in the ones, tens, and
hundreds sets will be generated, and the sum of
the three numbers in each row will produce the set {0, 1, 2, ...,
399}. The next query generates the set of days in 2001 by adding each
number in the set to the base date and then discarding days that fall
in 2002:
SELECT days.dt
FROM
(SELECT TO_DATE('01-JAN-2001', 'DD-MON-YYYY') +
ones.x + tens.x + hundreds.x dt
FROM
(SELECT 0 x FROM dual UNION ALL
SELECT 1 x FROM dual UNION ALL
SELECT 2 x FROM dual UNION ALL
SELECT 3 x FROM dual UNION ALL
SELECT 4 x FROM dual UNION ALL
SELECT 5 x FROM dual UNION ALL
SELECT 6 x FROM dual UNION ALL
SELECT 7 x FROM dual UNION ALL
SELECT 8 x FROM dual UNION ALL
SELECT 9 x FROM dual) ones
CROSS JOIN
(SELECT 0 x FROM dual UNION ALL
SELECT 10 x FROM dual UNION ALL
SELECT 20 x FROM dual UNION ALL
SELECT 30 x FROM dual UNION ALL
SELECT 40 x FROM dual UNION ALL
SELECT 50 x FROM dual UNION ALL
SELECT 60 x FROM dual UNION ALL
SELECT 70 x FROM dual UNION ALL
SELECT 80 x FROM dual UNION ALL
SELECT 90 x FROM dual) tens
CROSS JOIN
(SELECT 0 x FROM dual UNION ALL
SELECT 100 x FROM dual UNION ALL
SELECT 200 x FROM dual UNION ALL
SELECT 300 x FROM dual) hundreds) days
WHERE days.dt < TO_DATE('01-JAN-2002', 'DD-MON-YYYY');
DT
---------
01-JAN-01
02-JAN-01
03-JAN-01
04-JAN-01
05-JAN-01
06-JAN-01
07-JAN-01
08-JAN-01
09-JAN-01
10-JAN-01
. . .
20-DEC-01
21-DEC-01
22-DEC-01
23-DEC-01
24-DEC-01
25-DEC-01
26-DEC-01
27-DEC-01
28-DEC-01
29-DEC-01
30-DEC-01
31-DEC-01
Since 2001 is not a leap year, the result set will contain 365 rows,
one for each day of 2001. This query can then be wrapped in another
inline view and used as the driving table for generating the report.
Whether you would actually want to use such a strategy in your code
is up to you;
the main purpose of this example is
to help get the creative juices
flowing.
5.4.4 Overcoming SQL Restrictions
The use of certain features of Oracle SQL can
impose restrictions on our SQL statements. When these features are
isolated from the rest of the query inside an inline view, however,
these restrictions can be sidestepped. This section explores how
inline views can overcome limitations with hierarchical and
aggregation queries.
5.4.4.1 Hierarchical queries
Hierarchical
queries
allow recursive
relationships
to be traversed. As an
example of a recursive relationship, consider a table called
region that holds data about sales territories.
Regions are arranged in a hierarchy, and each record in the
region table references the region in which it is
contained, as illustrated by the following data:
SELECT * FROM region;
REGION_ID NAME SUPER_REGION_ID
---------- -------------------- ---------------
1 North America
2 Canada 1
3 United States 1
4 Mexico 1
5 New England 3
6 Mid-Atlantic 3
7 Southeast US 3
8 Southwest US 3
9 Northwest US 3
10 Central US 3
11 Europe
12 France 11
13 Germany 11
14 Spain 11
Each record in the customer table references the
smallest of its applicable regions. Given a particular region, it is
possible to construct a query that traverses up or down the hierarchy
by utilizing the START WITH and
CONNECT BY
clauses:
SELECT region_id, name, super_region_id
FROM region
START WITH name = 'North America'
CONNECT BY PRIOR region_id = super_region_id;
REGION_ID NAME SUPER_REGION_ID
---------- -------------------- ---------------
1 North America
2 Canada 1
3 United States 1
5 New England 3
6 Mid-Atlantic 3
7 Southeast US 3
8 Southwest US 3
9 Northwest US 3
10 Central US 3
4 Mexico 1
The query just shown traverses the region hierarchy starting with the
North America region and working down the tree. Looking carefully at
the results, you can see that the Canada, United States, and Mexico
regions all point to the North America region via the
super_region_id field. The remainder of the rows
all point to the United States region. Thus, we have identified a
three-level hierarchy with one node at the top, three nodes in the
second level, and six nodes in the third level underneath the United
States node. For a detailed look at hierarchical queries, see Chapter 8.
Imagine that you have been asked to generate a report showing total
sales in 2001 for each subregion of North America. However,
hierarchical queries have the restriction that the table being
traversed cannot be joined to other tables within the same query, so
it might seem impossible to generate the report from a single query.
Using an inline view, however, you can isolate the hierarchical query
on the region table from the
customer and cust_order tables,
as in:
SELECT na_regions.name region_name,
SUM(co.sale_price) total_sales
FROM cust_order co INNER JOIN customer c
ON co.cust_nbr = c.cust_nbr
INNER JOIN
(SELECT region_id, name
FROM region
START WITH name = 'North America'
CONNECT BY PRIOR region_id = super_region_id) na_regions
ON c.region_id = na_regions.region_id
WHERE co.cancelled_dt IS NULL
AND co.order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
AND co.order_dt < TO_DATE('01-JAN-2002','DD-MON-YYYY')
GROUP BY na_regions.name;
REGION_NAME TOTAL_SALES
-------------------- -----------
Mid-Atlantic 198
New England 590
Even though the na_regions set includes the North
America and United States regions, customer records always point to
the smallest applicable region, which is why these particular regions
do not show up in the final result set.
By placing the hierarchical query within an inline view, you are able
to temporarily flatten the region hierarchy to suit the purposes of
the query, which allows you to bypass the restriction on hierarchical
queries without resorting to splitting the logic into multiple
pieces. The next section will demonstrate a similar strategy for
working with aggregate queries.
5.4.4.2 Aggregate queries
Queries
that
perform aggregations
have the following restriction: all nonaggregate columns in the
SELECT clause must be included in the GROUP BY clause. Consider the
following query, which aggregates sales data by customer and
salesperson, and then adds supporting data from the
customer, region,
employee, and department
tables:
SELECT c.name customer, r.name region,
e.fname || ' ' || e.lname salesperson, d.name department,
SUM(co.sale_price) total_sales
FROM cust_order co INNER JOIN customer c
ON co.cust_nbr = c.cust_nbr
INNER JOIN region r
ON c.region_id = r.region_id
INNER JOIN employee e
ON co.sales_emp_id = e.emp_id
INNER JOIN department d
ON e.dept_id = d.dept_id
WHERE co.cancelled_dt IS NULL
AND co.order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
GROUP BY c.name, r.name, e.fname || ' ' || e.lname, d.name;
CUSTOMER REGION SALESPERSON DEPARTMENT TOTAL_SALES
---------------- ------------- --------------- ------------ -----------
Zantech Inc. Mid-Atlantic KENNETH MARTIN SALES 198
Flowtech Inc. New England KENNETH MARTIN SALES 180
Since every nonaggregate in the SELECT clause must be included in the
GROUP BY clause, you are forced to sort on five columns, since a sort
is needed to generate the groupings. Because every customer is in one
and only one region and every employee is in one and only one
department, you really only need to sort on the
customer and employee columns
to produce the desired results. So the Oracle engine is wasting its
time sorting on the region and department names.
However, by isolating the aggregation from the supporting tables, you
can create a more efficient and more understandable query:
SELECT c.name customer, r.name region,
e.fname || ' ' || e.lname salesperson, d.name department,
cust_emp_orders.total total_sales
FROM
(SELECT cust_nbr, sales_emp_id, SUM(sale_price) total
FROM cust_order
WHERE cancelled_dt IS NULL
AND order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
GROUP BY cust_nbr, sales_emp_id) cust_emp_orders
INNER JOIN customer c
ON cust_emp_orders.cust_nbr = c.cust_nbr
INNER JOIN region r
ON c.region_id = r.region_id
INNER JOIN employee e
ON cust_emp_orders.sales_emp_id = e.emp_id
INNER JOIN department d
ON e.dept_id = d.dept_id;
CUSTOMER REGION SALESPERSON DEPARTMENT TOTAL_SALES
---------------- ------------- --------------- ------------ -----------
Flowtech Inc. New England KENNETH MARTIN SALES 180
Zantech Inc. Mid-Atlantic KENNETH MARTIN SALES 198
Since the cust_order table includes the customer
number and salesperson ID, you can perform the aggregation against
these two columns without the need to include the other four tables.
Not only are you sorting on fewer columns, you are sorting on numeric
fields (customer number and employee ID) rather than potentially
lengthy strings (customer name, region name, employee name, and
department name). The containing query uses the
cust_nbr and sales_emp_id
columns from the inline view to join to the
customer and employee tables,
which in turn are used to join to the region and
department tables.
By performing the aggregation within an inline view, you have
sidestepped the restriction that all nonaggregates be included in the
GROUP BY clause. You have also shortened execution time by
eliminating unnecessary sorts and minimized the number of joins to
the customer, region,
employee, and department
tables. Depending on the amount of data in the tables,
these
improvements could yield significant performance gains.
5.4.5 Inline Views in DML Statements
Now that you are comfortable with inline views,
it's time to add another wrinkle: inline views may
also be used in INSERT, UPDATE, and DELETE statements. In most cases,
using an inline view in a DML statement improves readability but
otherwise adds little value to statement execution. To illustrate,
we'll begin with a fairly simple
UPDATE statement and then show the
equivalent statement using an inline view:
UPDATE cust_order co
SET co.expected_ship_dt = co.expected_ship_dt + 7
WHERE co.cancelled_dt IS NULL AND co.ship_dt IS NULL
AND EXISTS (SELECT 1
FROM line_item li INNER JOIN part p
ON li.part_nbr = p.part_nbr
WHERE li.order_nbr = co.order_nbr
AND p.inventory_qty = 0);
This statement uses an EXISTS condition to locate orders that include
out-of-stock parts. The next version uses an inline view called
suspended_orders to identify the same set of
orders:
UPDATE (SELECT co.expected_ship_dt exp_ship_dt
FROM cust_order co
WHERE co.cancelled_dt IS NULL AND co.ship_dt IS NULL
AND EXISTS (SELECT 1
FROM line_item li INNER JOIN part p
ON li.part_nbr = p.part_nbr
WHERE li.order_nbr = co.order_nbr
AND p.inventory_qty = 0)) suspended_orders
SET suspended_orders.exp_ship_dt = suspended_orders.exp_ship_dt + 7;
In the first statement, the WHERE clause of the UPDATE statement
determines the set of rows to be updated, whereas in the second
statement, the result set returned by the SELECT statement determines
the target rows. Otherwise, the two statements are identical. For the
inline view to add extra value to the statement, it must be able to
do something that the simple update statement cannot do: join
multiple tables. The following version attempts to do just that by
replacing the subquery with a three-table join:
UPDATE (SELECT co.expected_ship_dt exp_ship_dt
FROM cust_order co INNER JOIN line_item li
ON co.order_nbr = li.order_nbr
INNER JOIN part p
ON li.part_nbr = p.part_nbr
WHERE co.cancelled_dt IS NULL AND co.ship_dt IS NULL
AND p.inventory_qty = 0) suspended_orders
SET suspended_orders.exp_ship_dt = suspended_orders.exp_ship_dt + 7;
However, statement execution results in the following
error:
ORA-01779: cannot modify a column which maps to a non key-preserved table
As is often the case in life, we can't get something
for nothing. To take advantage of the ability to join multiple tables
within a DML statement, we must abide by the following rules:
Only one of the joined tables in an inline view may be modified by
the containing DML statement.
To be modifiable, the target table's key must be
preserved in the result set of the inline view.
Although the previous UPDATE statement attempts to modify only one
table (cust_order), that table's
key (order_nbr) is not preserved in the result
set, since an order has multiple line items. In other words, rows in
the result set generated by the three-table join cannot be uniquely
identified using just the order_nbr field, so it
is not possible to update the cust_order table via
this particular three-table join. However, it is possible to update
or delete from the line_item table using the same
join, since the key of the line_item table matches
the key of the result set returned from the inline view
(order_nbr and part_nbr). The
next statement deletes rows from the line_item
table using an inline view nearly identical to the one that failed
for the previous UPDATE attempt:
DELETE FROM (SELECT li.order_nbr order_nbr, li.part_nbr part_nbr
FROM cust_order co INNER JOIN line_item li
ON co.order_nbr = li.order_nbr
INNER JOIN part p
ON li.part_nbr = p.part_nbr
WHERE co.cancelled_dt IS NULL AND co.ship_dt IS NULL
AND p.inventory_qty = 0) suspended_orders;
The column(s) referenced in the SELECT clause of the inline view are
actually irrelevant. Since the line_item table is
the only key-preserved table of the three tables listed in the FROM
clause, this is the table on which the DELETE statement operates.
Although utilizing an inline view in a DELETE statement can be more
efficient, it's somewhat disturbing that it is not
immediately obvious which table is the focus of the DELETE statement.
A reasonable convention when writing such statements would be to
always select the key columns from
the target table.
5.4.6 Restricting Access Using WITH CHECK OPTION
Another way
in which
inline views can add value to DML statements
is by restricting both the rows and columns that may be modified. For
example, most companies only allow members of Human Resources to see
or modify salary information. By restricting the columns visible to a
DML statement, we can effectively hide the salary column:
UPDATE (SELECT emp_id, fname, lname, dept_id, manager_emp_id
FROM employee) emp
SET emp.manager_emp_id = 11
WHERE emp.dept_id = 4;
Although this statement executes cleanly, attempting to add the
salary column to the SET clause would yield
the
following error:
UPDATE (SELECT emp_id, fname, lname, dept_id, manager_emp_id
FROM employee) emp
SET emp.manager_emp_id = 11, emp.salary = 1000000000
WHERE emp.dept_id = 4;
ORA-00904: "EMP"."SALARY": invalid identifier
Of course, the person writing the UPDATE statement has full access to
the table; the intent here is to protect against unauthorized
modifications by the users. This might prove useful in an n-tier
environment, where the interface layer interacts with a
business-logic layer.
Although this mechanism is useful for restricting access to
particular columns, it does not limit access to particular rows in
the target table. To restrict the rows that may be modified using a
DML statement, you can add a WHERE clause to the inline view and
specify WITH CHECK OPTION. For example, you may want to restrict the
users from modifying data for any employee in the Accounting
department:
UPDATE (SELECT emp_id, fname, lname, dept_id, manager_emp_id
FROM employee
WHERE dept_id !=
(SELECT dept_id FROM department WHERE name = 'ACCOUNTING')
WITH CHECK OPTION) emp
SET emp.manager_emp_id = 7698
WHERE emp.dept_id = 30;
The addition of WITH CHECK OPTION to the inline view protects against
any data modifications that would not be visible via the inline view.
For example, attempting to modify an employee's
department assignment from Sales to Accounting would generate an
error, since the data would no longer be visible via the inline view:
UPDATE (SELECT emp_id, fname, lname, dept_id, manager_emp_id
FROM employee
WHERE dept_id !=
(SELECT dept_id FROM department WHERE name = 'ACCOUNTING')
WITH CHECK OPTION) emp
SET dept_id = (SELECT dept_id FROM department WHERE name = 'ACCOUNTING')
WHERE emp_id = 7900;
ORA-01402: view WITH CHECK OPTION where-clause
violation
5.4.7 Global Inline Views
Earlier in the chapter, you saw how the WITH clause can be
used to allow the same subquery to be referenced multiple times
within the same query. Another way to utilize the WITH clause is as
an inline view with global scope. To illustrate, we will rework one
of the previous inline view examples to show how the subquery can be
moved from the FROM clause to the WITH clause.
Here's the original example, which comes from Section 5.4.4.1:
SELECT na_regions.name region_name,
SUM(co.sale_price) total_sales
FROM cust_order co INNER JOIN customer c
ON co.cust_nbr = c.cust_nbr
INNER JOIN
(SELECT region_id, name
FROM region
START WITH name = 'North America'
CONNECT BY PRIOR region_id = super_region_id) na_regions
ON c.region_id = na_regions.region_id
WHERE co.cancelled_dt IS NULL
AND co.order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
AND co.order_dt < TO_DATE('01-JAN-2002','DD-MON-YYYY')
GROUP BY na_regions.name;
REGION_NAME TOTAL_SALES
-------------------- -----------
Mid-Atlantic 198
New England 590
Here's the same query with the
na_regions subquery moved to the WITH clause:
WITH na_regions AS (SELECT region_id, name
FROM region
START WITH name = 'North America'
CONNECT BY PRIOR region_id = super_region_id)
SELECT na_regions.name region_name,
SUM(co.sale_price) total_sales
FROM cust_order co INNER JOIN customer c
ON co.cust_nbr = c.cust_nbr
INNER JOIN na_regions
ON c.region_id = na_regions.region_id
WHERE co.cancelled_dt IS NULL
AND co.order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
AND co.order_dt < TO_DATE('01-JAN-2002','DD-MON-YYYY')
GROUP BY na_regions.name;
REGION_NAME TOTAL_SALES
-------------------- -----------
Mid-Atlantic 198
New England 590
Note that the FROM clause must include the inline view alias for you
to reference the inline view's columns in the
SELECT, WHERE, GROUP BY, or ORDER BY clauses.
To show how the na_regions subquery has global
scope, the join between the na_regions inline view
and the customer table has been moved to another
inline view (called cust) in the FROM clause:
WITH na_regions AS (SELECT region_id, name
FROM region
START WITH name = 'North America'
CONNECT BY PRIOR region_id = super_region_id)
SELECT cust.region_name region_name,
SUM(co.sale_price) total_sales
FROM cust_order co INNER JOIN
(SELECT c.cust_nbr cust_nbr, na_regions.name region_name
FROM customer c INNER JOIN na_regions
ON c.region_id = na_regions.region_id) cust
ON co.cust_nbr = cust.cust_nbr
WHERE co.cancelled_dt IS NULL
AND co.order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
AND co.order_dt < TO_DATE('01-JAN-2002','DD-MON-YYYY')
GROUP BY cust.region_name;
REGION_NAME TOTAL_SALES
-------------------- -----------
Mid-Atlantic 198
New England 590
Earlier in this section, we stated that inline views
"are always executed prior to the containing query
and, thus, may not reference columns from other tables or inline
views from the same query." Using the WITH clause,
however, you are able to break this rule, since the
na_regions inline view is visible everywhere
within the query. This makes the na_regions inline
view act more like
a temporary table than a true
inline view.
