Starting Oracle 9.0 – according to SQL 1999 standard – Oracle introduced the WITH option which can be considered
as the extension of the inline views.
Let’s see an “old” example:
WITH dept_costs AS (SELECT department_name, SUM(salary) as dept_total FROM employees, departments WHERE employees.department_id = departments.department_id GROUP BY department_name), avg_cost AS (SELECT SUM(dept_total)/COUNT(*) as dept_avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT dept_avg FROM avg_cost) ORDER BY department_name;
(The origin of this statement is Oracle course,titled:
Oracle Database: SQL Fundamentals I
Volume II • Student Guide).
In Oracle 12c we have new enhancements for this kind of SQL statement:
We can define “in-line” functions or procedures after the WITH clause.
In the following example we are looking for those departments, where
total salary of the department is greater than the maximum of the average of the total salaries:
WITH FUNCTION tax(p_amount IN NUMBER) RETURN NUMBER IS m NUMBER; BEGIN IF p_amount <8000 THEN m:=0.08; ELSIF p_amount <18000 THEN m:=0.25; ELSE m:=0.3; END IF; RETURN p_amount * m; END; emp_costs AS ( SELECT d.department_name dept_name,e.last_name, e.salary AS salary, tax(e.salary) AS tax_amount FROM employees e JOIN departments d ON e.department_id = d.department_id), dept_costs AS ( SELECT dept_name, SUM(salary) AS dept_sal,SUM(tax_amount) tax_sum, AVG(salary) avg_sal FROM emp_costs GROUP BY dept_name) SELECT * FROM dept_costs WHERE dept_sal > (SELECT MAX(avg_sal) FROM dept_costs) ORDER BY dept_name; /
The result is:
DEPT_NAME DEPT_SAL TAX_SUM AVG_SAL ------------------------------ ---------- ---------- ---------- Accounting 20308 5077 10154 Executive 58000 15700 19333.3333 Finance 51608 9094 8601.33333 IT 28800 3834 5760 Purchasing 24900 3862 4150 Sales 304500 62083 8955.88235 Shipping 156400 15266 3475.55556 7 rows selected.
The execution plan is the following ( not very simple!):
Plan hash value: 38700341
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | HASH GROUP BY | | 27 | 621 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 106 | 2438 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |
| 8 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 0 (0)| |
| 9 | SORT ORDER BY | | 27 | 1512 | 4 (0)| 00:00:01 |
|* 10 | VIEW | | 27 | 1512 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6694_1CABAF | 27 | 621 | 2 (0)| 00:00:01 |
| 12 | SORT AGGREGATE | | 1 | 13 | | |
| 13 | VIEW | | 27 | 351 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6694_1CABAF | 27 | 621 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
10 - filter("DEPT_SAL">)
====================================================================================================</pre>
We can use the WITH option with plsql_declarations clause
in DML statements as well.
Let’s consider the following example where we want to compute the
tax amount of the salaries and modify the tax_amount column,
but we DON’T WANT TO USE the stored tax function if exists at all!
First we create a copy of the employees table and modify the
structure with a new column
CREATE TABLE newmp AS SELECT * FROM employees; ALTER TABLE newemp ADD tax_amount NUMBER(10,2);
Now we modify the content of the tax_amount column
(which is originally empty) with each employee’s tax amount:
UPDATE /*+ WITH_PLSQL */ newemp E SET tax_amount=(WITH FUNCTION TAX(P_AMOUNT IN NUMBER) RETURN NUMBER IS M NUMBER; BEGIN IF P_AMOUNT <8000 THEN M:=0.08; ELSIF P_AMOUNT <18000 THEN M:=0.25; ELSE M:=0.3; END IF; RETURN P_AMOUNT*M; END; SELECT tax(salary) FROM employees m WHERE m.employee_id=e.employee_id) /
Observe that the WITH statement has a special hint.
The WITH_PLSQL hint only enables you to specify
the WITH plsql_declarations clause within the statement.
It is not an optimizer hint.(see Oracle 12c documentation)
Let’s check the result (only the first 10 rows are displayed):
SELECT * FROM (SELECT last_name,salary,tax_amount from newemp ORDER BY salary DESC) WHERE ROWNUM<=10; LAST_NAME SALARY TAX_AMOUNT ------------------------- ---------- ---------- King 24000 7200 Kochhar 17000 4250 De Haan 17000 4250 Russell 14000 3500 Partners 13500 3375 Hartstein 13000 3250 Greenberg 12008 3002 Higgins 12008 3002 Errazuriz 12000 3000 Ozer 11500 2875 10 rows selected.
The execution plan for the UPDATE is the following(simpler!)
Executions:1 | is_bind_sensitive:N | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:505
SQL_ID 6s126hzr93g2d, child number 0
-------------------------------------
UPDATE /*+ WITH_PLSQL */ newemp E SET tax_amount=(WITH FUNCTION
TAX(P_AMOUNT IN NUMBER) RETURN NUMBER IS M NUMBER; BEGIN IF P_AMOUNT
<8000 THEN M:=0.08; ELSIF P_AMOUNT <18000 THEN M:=0.25; ELSE M:=0.3;
END IF; RETURN P_AMOUNT*M; END; SELECT tax(salary) FROM employees m
WHERE m.employee_id=e.employee_id)
Plan hash value: 759157450
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 217 (100)| |
| 1 | UPDATE | NEWEMP | | | | |
| 2 | TABLE ACCESS FULL | NEWEMP | 107 | 1819 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 8 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("M"."EMPLOYEE_ID"=:B1)
====================================================================================================
The greatest benefit of using PL/SQL functions or procedures that we
can use our (not stored) subprograms in queries without writing
procedural code.
Lczinkoc's Blog 
Leave a Reply