5.4 Inline Views
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.
|
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 6In 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 6If 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 6The 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 6Finally, the dept_orders set is joined to the department table, and the final result set is:
DEPT_ID DEPT_NAME TOT_ORDERS
---------- -------------------- ----------
30 SALES 6After 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 396One 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 396Now 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
399Since 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-01Since 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 11Each 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 1The 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 590Even 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 180Since 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 198Since 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 identifierOf 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 590Here'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 590Note 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 590Earlier 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.
