February 27, 2021
Hot Topics:

Sample Chapter: Efficient PL/SQL

  • By Beck, Kallman, Katz, Knox, and McDonald
  • Send Email »
  • More Articles »

Analytic Functions

If you are unfamiliar with the syntax shown in the report_sal_adjustment4 procedure, you have been missing out on one of Oracle's greatest database achievements. These analytic functions, which have been available since version 8.1.6, increase in functionality and power with each release. Unfortunately, they are not widely known because they were initially proposed primarily for data warehousing queries for such requirements as moving averages and regression statistics. As such, they are documented in the Data Warehousing guide (one of the standard manuals in the Oracle documentation set). Unfortunately, this manual isn't typically consulted except for those Oracle customers who are implementing warehouses. That is a great pity—analytic functions can achieve many remarkable things on any database.

Every major release of Oracle adds more diversity and more complexity to the SQL command set. As a consequence, there will (or should be) more tasks that can be achieved with standard SQL and not PL/SQL. One classic case of this that is worth a specific mention is the processing of results in a join (or view containing a join). We have lost count of the number of times we have seen a PL/SQL module built because the module specification ran along the lines of:

"A view V is based on tables, X, Y, and Z. Update the rows in table X for rows in the view V where (some criteria)."

A PL/SQL module is built because the general consensus among developers is that "you cannot update a join view." This was true way, way back in version 7.1, but for many years now, there is an entire class of views in Oracle that are allowed to be directly updated as though they were tables. Oracle even provides a data dictionary object (DBA_UPDATEABLE_COLUMS) that tells you exactly whether a view or part thereof is a candidate for DML.

Consider the following example module specification:

Increase the bonus by 10 percent for all employees who appear in the view YEARLY_BONUS.

Where this view is defined as

create or replace view YEARLY_BONUS as
select emp.empno,emp.ename,dept.dname,emp.bonus
where emp.hiredate <sysdate +1
and emp.deptno =dept.deptno

A PL/SQL solution that queries the view and then updates the underlying table is relatively easy to code.

create or replace
procedure XXX is
for i in (select empno from yearly_bonus )loop
    update emp
  set bonus =bonus *1.1
  where empno =i.empno;

But the best solution is even more unremarkable:

update yearly_bonus set bonus =bonus *1.1;

Which of course you could simply wrap within a PL/SQL procedure if required. That is all you need! In fact, even if there is not a view defined at all, you can still avoid any complex processing. You could simply code

update (select emp.ename,dept.dname,emp.bonus
        from EMP,DEPT
        where emp.hiredate <sysdate +1
        and emp.deptno =dept.deptno)
set bonus =bonus *1.1;

As long as you satisfy the requirements for updating a join view (See "Modifying a Join View" in the Application Developer fundamentals guide), you do not need, and you should not use PL/SQL.

Choose SQL Innovation Over PL/SQL

The hardest thing about using SQL instead of PL/SQL is convincing yourself that something can be coded in SQL when PL/SQL might seem the natural choice. Here are a few examples where SQL can be used but it does not appear obvious to do so. It is of course by no means the definitive set of ways in which SQL can be used instead of PL/SQL, and you will not find a manual anywhere which tells you when a SQL solution can be used instead of PL/SQL. The key is that you need to think outside the box when confronted with a problem that does not appear to be readily solvable with pure SQL.

Displaying a Calendar

PL/SQL combined with DBMS_OUTPUT.PUT_LINE seems to be the only way to display the calendar for the current month (or any month for that matter) because there isn't any obvious function within SQL to generate it. But with the common technique of using decode to pivot rows into columns, we can in fact generate a calendar purely with SQL. First, we will define a substitution variable mdate that holds the value of the date for which the calendar month will be generated.

SQL> col dte new_value mdate
SQL> select '23-JUN-03'dte from dual;


And now a little SQL can be used to generate the month. We retrieve n rows from our src table where n is the number of days in the month, and then use a little date arithmetic to align the results into the appropriate day column.

SQL> select
  2     max(decode(dow,1,d,null))Sun,
  3     max(decode(dow,2,d,null))Mon,
  4     max(decode(dow,3,d,null))Tue,
  5     max(decode(dow,4,d,null))Wed,
  6     max(decode(dow,5,d,null))Thu,
  7     max(decode(dow,6,d,null))Fri,
  8     max(decode(dow,7,d,null))Sat
  9  from
 10  (select rownum d,
 11          rownum-2+to_number(
 12                 to_char(trunc(
 13                   to_date('&mdate'),'MM'),'D'))p,
 14          to_char(trunc(to_date('&mdate'),'MM')
 15             -1+rownum,'D')dow
 16   from SRC
 17   where rownum <=to_number(to_char(
 18         last_day(to_date('&mdate')),'DD')))
 19 group by trunc(p/7)
 20 /

---  ----  ----   ----  ----  ----  -----
  2    3     4      5     6     7     8
  9   10    11     12    13    14    15
 16   17    18     19    20    21    22
 23   24    25     26    27    28    29

6 rows selected.

Such SQL could then easily be folded away within a view to provide a calendar for any desired month.

Calculating the Median Value

The median has always been a classical problem to solve within a relational database because historically, although databases are fine at returning a rowset in order, they have been poor at processing a rowset in order, which is a prerequisite for calculating the median. A typical algorithm for calculating the median might look like:

  • Determine the count of the rows in the rowset and call it n .
  • If n is odd, fetch rows in ascending order until you get to row [n/2+1], this is the median.
  • If n is even, fetch rows in ascending order until you get to row [n/2].
  • The mean of this row and the next are the median.

However, a quick scan of the data-warehousing guide reveals a far easier solution, once again using an analytic function (this one being new to version 9).

SQL> select percentile_cont(0.5)
  2  within group (order by sal desc )median_sal
  3  from   emp;


More importantly, we still can observe the performance benefits of using SQL instead of PL/SQL when we test against a table larger than the standard emp. We've loaded 1,000,000 rows into the emp table, and then created two PL/SQL procedures—one that implements the median using the conventional algorithm listed earlier in this section, and another with the median calculated using the PERCENTILE_CONT function.

SQL> set timing on
SQL> exec median_using_old_method

PL/SQL procedure successfully completed.
SQL> exec median_using_percentile

PL/SQL procedure successfully completed.

On this system, using the percentile function is five times faster when the size of the underlying table is large.

Page 18 of 19

This article was originally published on February 5, 2004

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date