11g PL/SQL Subprogram Inlining and Compound Triggers Oracle 11g New Features Tips by Donald BurlesonJuly 13, 2015

Oracle 11g New Features Tips

Subprogram inlining has been around in optimizing compilers for a long time.  With the 11g database, Oracle has included this feature in PL/SQL.  Basically subprogram inlining is where the program calls a subprogram (procedure or function) and the compiler places the actual subprogram code in place of the call to that code.  Since the subprogram code is in the main code (INLINE), the overhead of calling that subprogram is saved.  This allows you to program using a modular method, producing readable, maintainable source code, while the compiler optimizes the code to make it more efficient.

If the PLSQL_OPTIMIZER_LEVEL is set to 3 the compiler will automatically try and inline subprograms.  If the PLSQL_OPTIMIZER_LEVEL is set to 2 (the default) it will not automatically try and inline subprograms.  So the PRAGMA INLINE is used to tell the optimizer to inline or not to inline depending on the setting of the optimizer level.

The PRAGMA INLINE statement is used to tell the optimizer if the following statements subprograms should be placed inline or not.

In the example below, the PARGMA INLINE is used to inline the first function call but not the second.  The PARGMA INLINE statement only effects the statement following PARGMA INLINE statement.

create or replace package inline_demo is
  procedure runtest;
end;
/

create or replace package body inline_demo as 

function newadd(n1 number) return number
as
 n11 number;
begin
  n11:= n1+n1;
  return n11;
end;

procedure runtest as 
  n2 number := 0;
begin
  n2 := 3;
  dbms_output.put('Num: '||n2);
  PRAGMA INLINE (newadd,'YES');
  n2 := newadd(n2);
  dbms_output.put('  Num2: '||n2);
  PRAGMA INLINE (newadd,'NO');
  n2 := newadd(n2);
  dbms_output.put_line('  Num3: '||n2);
end;

end inline_demo;
/

SQL> exec inline_demo.runtest;
Num: 3  Num2: 6  Num3: 12

PL/SQL procedure successfully completed.

The PARGMA INLINE impacts all calls in the statement that follows it. If the statement is a loop, then all calls to the subprogram in the loop is placed inline.

Inlining subprogram code is a common and powerful code optimization capability that should be used when possible.  Placing the PLSQL_OPTIMIZER_LEVEL parameter to 3 will automate the code inline process.

PL/SQL Compound Triggers

A compound trigger is a PL/SQL trigger that contains multiple actions that take place at different timing points.  The timing points for a compound trigger on a table are:

BEFORE STATEMENT  - before trigger statement executes
AFTER STATEMENT     - after the trigger statement executes
BEFORE EACH ROW    - before each row the statement impacts
AFTER EACH ROW       - after each row the statement impacts

If the compound trigger is on a view, the only timing point is:

INSTEAD OF EACH ROW

The real advantage of the compound trigger is that the trigger definition has a declaration section and variable state is maintained through each timing section from the firing of the trigger to completion.  The statements at each timing point have some restrictions.  The statement must be DML and can contain Inserting, Updating, Deleting and Applying.  If the statement at the timing point does not impact any rows, the before statement and after statement do not fire.  The compound trigger body does not have an initialization block and thus no exception block.  All exceptions must be handled in the section that raised the exception.  You cannot pass execution from one section to another using an exception handler or a GOTO statement.  The references to :OLD, :NEW or :PARENT cannot be used in the declaration section, BEFORE STATEMENT or AFTER STATEMENT.  Only the BEFORE EACH ROW section can change the :NEW value.

The order of firing within a compound trigger is defined, however like simple triggers, the order of firing of multiple triggers (compound or simple) is not guaranteed. The order of firing multiple triggers can be defined using the FOLLOWS statement.  The FOLLOWS option is explained in the next section.

Let's look at a compound trigger. The following code creates a test table and a compound trigger that fires for each timing point associated with insert, update and delete statements. The triggering actions are logged in a PL/SQL table defined in the global declaration section. The final timing point for each statement prints out the content of the PL/SQL table to show that the variable state has been maintained throughout the lifetime of the statement.

The example courtesy of Dr. Tim Hall (www.oracle-base.com)

CREATE TABLE compound_trigger_test (

  id           NUMBER,

  description  VARCHAR2(50)

);

CREATE OR REPLACE TRIGGER compound_trigger_test_trg
  FOR INSERT OR UPDATE OR DELETE ON compound_trigger_test
    COMPOUND TRIGGER 

  -- Global declaration.

  TYPE t_tab IS TABLE OF VARCHAR2(50);
  l_tab t_tab := t_tab(); 

  BEFORE STATEMENT IS

  BEGIN
    l_tab.extend;
    CASE
      WHEN INSERTING THEN
        l_tab(l_tab.last) := 'BEFORE STATEMENT - INSERT';
      WHEN UPDATING THEN
        l_tab(l_tab.last) := 'BEFORE STATEMENT - UPDATE';
      WHEN DELETING THEN
        l_tab(l_tab.last) := 'BEFORE STATEMENT - DELETE';
    END CASE;
  END BEFORE STATEMENT; 

  BEFORE EACH ROW IS
  BEGIN
    l_tab.extend;
    CASE
      WHEN INSERTING THEN
        l_tab(l_tab.last) := 'BEFORE EACH ROW - INSERT (new.id=' || :new.id ||
')';
      WHEN UPDATING THEN
        l_tab(l_tab.last) := 'BEFORE EACH ROW - UPDATE (new.id=' || :new.id ||
' old.id=' || :old.id || ')';
      WHEN DELETING THEN
        l_tab(l_tab.last) := 'BEFORE EACH ROW - DELETE (old.id=' || :old.id ||
')';
    END CASE;
  END BEFORE EACH ROW; 

  AFTER EACH ROW IS
  BEGIN
    l_tab.extend;
    CASE
      WHEN INSERTING THEN
        l_tab(l_tab.last) := 'AFTER EACH ROW - INSERT (new.id=' || :new.id ||
')';
      WHEN UPDATING THEN
        l_tab(l_tab.last) := 'AFTER EACH ROW - UPDATE (new.id=' || :new.id ||
' old.id=' || :old.id || ')';
      WHEN DELETING THEN
        l_tab(l_tab.last) := 'AFTER EACH ROW - DELETE (old.id=' || :old.id ||
')';
    END CASE;
  END AFTER EACH ROW; 

  AFTER STATEMENT IS
  BEGIN
    l_tab.extend;
    CASE
      WHEN INSERTING THEN
        l_tab(l_tab.last) := 'AFTER STATEMENT - INSERT';
      WHEN UPDATING THEN
        l_tab(l_tab.last) := 'AFTER STATEMENT - UPDATE';
      WHEN DELETING THEN
        l_tab(l_tab.last) := 'AFTER STATEMENT - DELETE';
    END CASE;   

    FOR i IN l_tab.first .. l_tab.last LOOP
      DBMS_OUTPUT.put_line(l_tab(i));
    END LOOP;
    l_tab.delete;
  END AFTER STATEMENT; 

END compound_trigger_test_trg;
/

By issuing several insert, update and delete statements against the test table we can see that the compound trigger is working as expected.

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO compound_trigger_test VALUES (1, 'ONE');
BEFORE STATEMENT - INSERT
BEFORE EACH ROW - INSERT (new.id=1)
AFTER EACH ROW - INSERT (new.id=1)
AFTER STATEMENT - INSERT 

1 row created.

SQL> INSERT INTO compound_trigger_test VALUES (2, 'TWO');
BEFORE STATEMENT - INSERT
BEFORE EACH ROW - INSERT (new.id=2)
AFTER EACH ROW - INSERT (new.id=2)
AFTER STATEMENT - INSERT 

1 row created.

SQL> UPDATE compound_trigger_test SET id = id;
BEFORE STATEMENT - UPDATE
BEFORE EACH ROW - UPDATE (new.id=2 old.id=2)
AFTER EACH ROW - UPDATE (new.id=2 old.id=2)
BEFORE EACH ROW - UPDATE (new.id=1 old.id=1)
AFTER EACH ROW - UPDATE (new.id=1 old.id=1)
AFTER STATEMENT - UPDATE

2 rows updated.

SQL> DELETE FROM compound_trigger_test;
BEFORE STATEMENT - DELETE
BEFORE EACH ROW - DELETE (old.id=2)
AFTER EACH ROW - DELETE (old.id=2)
BEFORE EACH ROW - DELETE (old.id=1)
AFTER EACH ROW - DELETE (old.id=1)
AFTER STATEMENT - DELETE

2 rows deleted. 

SQL>

One of the real advantages of a compound trigger is to use the FOR EACH ROW statement to capture changes and load them into a PL/SQL table, then use the AFTER STATEMENT statement to bulk load the changes into another table.  Using simple triggers, you would have to reference a package to load the data and then execute the bulk load.  With a compound trigger these operations are internal to the trigger.