Oracle 11g feature van de maand: Efficiëntere PL/SQL code schrijven

December 2007 - Naast verbeteringen in de database, heeft Oracle ook enkele verbeteringen aangebracht in PL/SQL. Sommige zaken zijn geheel nieuw en andere zaken zijn aangepast om makkelijker gebruikt te worden. In dit Whitebook worden een aantal van de belangrijkste aanpassingen besproken.

Op 6 november vond de officiële release plaats van de nieuwste versie van de Oracle database: 11g. In een serie Whitebooks vertellen de techneuten van Whitehorses u alles over de belangrijkste nieuwe features. Deze maand deel 3 over de mogelijkheid tot het schrijven efficiëntere PL/SQL code.

Naast verbeteringen in de database, heeft Oracle ook enkele verbeteringen aangebracht in PL/SQL. Sommige zaken zijn geheel nieuw en andere zaken zijn aangepast om makkelijker gebruikt te worden. In dit Whitebook worden een aantal van de belangrijkste aanpassingen besproken.

Compound Triggers

Drie vernieuwingen hebben te maken triggers. Oracle heeft een nieuw type trigger toegevoegd en twee nieuwe opties toegevoegd aan het bestaande gebruik van triggers.

Het nieuwe type trigger is de Compound Trigger. Een compound trigger is een trigger die bestaat uit een willekeurige combinatie van de bestaande triggers die afgaan op Before Statement, Before Row, After Row en After Statement.

Door het gebruik van een compound trigger zijn er veel meer mogelijkheden en is een ontwikkelaar veel flexibeler bij ingewikkelde transacties. Het is bijvoorbeeld mogelijk om variabelen te gebruiken tussen de verschillende delen van de compound trigger. Iets wat bij de conventionele triggers alleen mogelijk is via constructies met bijvoorbeeld packages en PL/SQL tables. Een eenvoudig voorbeeld kan dit goed illustreren.

We hebben een simpele tabel temp_tabel met 2 kolommen: naam en woonplaats. Volgens de regels van het bedrijf moet iedere update van woonplaats worden vastgelegd in de tabel audit_tabel samen met de username en het tijdstip, bovendien moet in de tabel audit_totals worden vastgelegd hoeveel updates de user heeft vastgelegd.

We doen hierbij voor het gemak wel de aannames dat iedere USER al een record heeft in de audit tabellen en dat er geen nieuwe inserts worden gedaan in de temp_tabel.

create or replace trigger compound_test
   for update of woonplaats
   on temp_tabel
   compound trigger
     pl_totaal number;

   before statement is
   begin
       /* Hier halen we de huidige hoeveelheid inserts op */

     select totaal
       into pl_totaal
       from audit_totaal
      where gebruiker = USER;

   end before statement;
   before each row is
   begin

     /* Hier doen we niks */
     null;

   end before each row;
   after each row is
   begin

     /* Iedere wijziging van de woonplaats moet worden vastgelegd */

     insert into audit_tabel
            (naam, oude_woonplaats, nieuwe_woonplaats, gebruiker, tijdstip)
     values (:old.naam, :old.woonplaats, :new.woonplaats, USER, sysdate);

     pl_totaal := pl_totaal + 1;

   end after each row;
   after statement is
   begin

     /* Het totaal aantal wijzigingen per gebruiker wordt bijgewerkt */

     update audit_totaal
        set totaal = pl_totaal
      where gebruiker = USER;

   end after statement;
 end compound_test;
 

We beginnen met de temp_tabel in de status zoals onderstaande figuur laat zien.

Hierna doen we enkele updates op de tabel. Allereerst verhuist Michel van Nieuwegein naar Utrecht. Daarna verplaatsen we de overige mensen naar Nieuwegein. De eerste actie zal zorgen voor 1 nieuw record in de audit_tabel en een opgehoogd totaal in audit_totaal met 1. De tweede actie moet 3 nieuwe records in de audit_tabel en een ophoging met 3 opleveren tot een totaal van 4.

De eerste update vindt plaats op de temp_tabel:

Dit levert het volgende op in de audit_tabel:

En in audit_totaal:

De 3 updates zorgen voor de onderstaande gevolgen in de volgende figuren:

Nu is dit een heel eenvoudig voorbeeld, maar het toont wel aan welke uitgebreide mogelijkheden de Compound Triggers hebben.

In eerdere versies van PL/SQL zou de variabele pl_totaal opgeslagen worden in een package variabele of tijdelijke tabel tijdens het before statement, om daarna weer opgehaald en bijgewerkt te worden in het after row statement en als laatste weer opgehaald in het after statement moment om te worden vastgelegd. Het is duidelijk dat een Compound Trigger in dit soort voorbeelden veel efficiënter is.

Ordered Execution in triggers

De tweede vernieuwing in het gebruik van triggers is de Ordered Execution. Hiermee is het mogelijk om de volgorde in het geval van meerdere triggers op één timing point zelf aan te geven. In sommige gevallen is het belangrijk om bij een DML actie een aantal triggers in een bepaalde volgorde af te vuren.

De syntax is als volgt:

create or replace trigger trigger_2
 before update
 on temp_tabel
 for each row	
 follows trigger_1
 begin
 ... trigger code ... 

Dit zorgt ervoor dat trigger_2 afgaat in het before update gedeelte nadat trigger_1 klaar is.

Disabled Triggers

Een derde toevoeging bij triggers is het Disabled creëren van een trigger . Dit kan handig zijn als de programmeur een trigger wil toevoegen aan een High Availability omgeving, maar niet het risico wil lopen dat deze niet compileert door een simpele fout en zodoende de werking van een bestaande tabel verstoort, en wellicht nog veel meer.

Als een trigger als disabled wordt gebouwd, kan de programmeur eerst alle mogelijke fouten eruit halen voordat deze live wordt gezet.

create or replace trigger trigger_t
 after insert on temp_tabel
 for each row
 disable
 begin
   /* Hier de trigger code */
 end; 

Ondanks het feit dat de trigger op deze manier niet enabled is, zal hij pas compileren als er geen fouten meer in zitten.

Het CONTINUE statement

Een belangrijk element dat gek genoeg nog altijd miste in PL/SQL was de mogelijkheid om in een LOOP bepaalde situaties over te slaan. Met een if-then-else constructie was wel het een en ander te maken, maar een echt statement bestond nog niet.

Tot 11g het CONTINUE statement introduceerde. Als voorbeeld hiervan een teller van 1 tot 100 die alleen de tientallen toont:

begin
         for teller in 1..100 loop
                 continue when mod(teller,10) != 0;
                 dbms_output.put_line ('teller='||teller);
         end loop;
 end; 

De output hiervan is:

teller=10
 teller=20
 teller=30
 ... 

Loops kunnen ook een naam krijgen, waardoor het bij geneste loops zelfs mogelijk is om selectief uit een binnenste of een buitenste loop te springen.

Sequences

Voorheen was het niet mogelijk om in PL/SQL een variabele direct te vullen met de NEXTVAL van een sequence. Hiervoor moest altijd een constructie gebruikt worden met SELECT INTO FROM DUAL. Dat hoeft vanaf 11g niet meer. Hierdoor wordt de code een stuk simpeler.

Voorheen:

declare
         mijn_id number(10);
 begin
         select mijnseq.nextval
         into mijn_id
         from dual;
 end; 

Nu:

declare
         mijn_id number(10);
 begin
         mijn_id := mijnseq.nextval;
 end; 

WHEN OTHERS

Vaak maken programmeurs geen gebruik van de exception when others om onverwachte fouten afgevangen. Veelal wordt deze exception gevuld met null; en verder niets.

Oracle heeft een nieuwe warning PLW-06009 gemaakt om hiervoor te waarschuwen. De procedure zal met een null; in de when others wel compileren, maar als pl_sql_warnings aanstaat (alter session set plsql_warnings = 'enable:all') dan zal de onderstaande waarschuwing te zien zijn:

LINE/COL ERROR
 -------- -----------------------------------------------------------------
 8/7      PLW-06009: procedure "MIJNPROCEDURE" OTHERS handler does not end in
          RAISE or RAISE_APPLICATION_ERROR 

De code zal hierdoor netjes uitgevoerd worden, maar de programmeur heeft in elk geval de waarschuwing gekregen dat zijn when-others niet zal resulteren in een foutmelding aan de gebruiker.

Named Parameters in Functions

Het gebruik van Named Parameters was niet toegestaan bij Functions.

Bijvoorbeeld:

select mijnfunctie (p_parameter1=<1,p_parameter2=<1) from dual; 

Dit leverde tot en met 10g nog een error op (ORA-00907: missing right parenthesis). Vanaf 11g is het gebruik van Named Parameters bij Functions gewoon toegestaan.

Conclusie

De Oracle 11g database heeft ook in de structuur van PL/SQL een aantal verbeteringen ondergaan. Het zijn geen hele grote zaken die Oracle heeft veranderd, maar een aantal frustraties van ontwikkelaars zijn met deze versie onder handen genomen. Een goed voorbeeld daarvan zijn het toestaan van Named Parameters bij Functions, het Continue statement en het simpeler aanroepen van Sequences. Drie zaken die eigenlijk heel logisch zijn, maar tot 11g nog niet mogelijk waren.

Ook Compound Triggers en Ordered Execution zijn dingen die ik als ontwikkelaar graag zou willen gebruiken. Bij diverse projecten heb ik oplossingen gemaakt die met deze twee nieuwe mogelijkheden veel eenvoudiger en overzichtelijker zouden zijn opgelost.

Concluderend kan ik zeggen dat ik als ontwikkelaar erg blij ben met de nieuwe features in 11g PL/SQL. Het maakt het werk eenvoudiger en efficiënter.

Over de auteur

Michel van Zoest is Oracle consultant bij Whitehorses en heeft ruim 7 jaar ervaring in de IT. Hij houdt zich bezig met PL/SQL en Forms/Designer ontwikkeling en heeft zich ook gericht op integratietrajecten en Javaprojecten met ADF. Michel is tevens één van de Kapow specialisten binnen Whitehorses.

Zijn expertise heeft hij onder andere opgedaan in projecten voor klanten in de automotive industrie, de financiële wereld, het verzekeringswezen, de pensioenenbranche, logistiek, energie, marktonderzoek en bij de overheid.