PL/SQL

A PL/SQL (Procedural Language/Structured Query Language) az Oracle által az SQL kiterjesztéseként kifejlesztett procedurális programozási nyelv. Az Oracle adatbáziskezelőt használva ezen a nyelven írhatunk triggereket és más tárolt eljárásokat. A nyelv alapja az Ada programozási nyelv; természetesen a PL/SQL magában foglalja az SQL nyelvet, pontosabban annak SELECT, INSERT, DELETE, UPDATE illetve OPEN, FETCH, CLOSE utasításait.

Példaprogramok

• MERGE utasítás PL SQL nyelven, amely magában foglalja az UPDATE, DELETE és INSERT utasításokat

A következő példában adott egy fizetesek tábla (benne a nevekkel és fizetésekkel) és egy fiz_modosit tábla (benne nevekkel és az új fizetésekkel). A merge paranccsal a következőket tudjuk megtenni:

- ha van egyezés a név alapján, akkor az új fizetésre módosítjuk a fizetést (update)

- ha van egyezés a név alapján és a fizetés 13000 vagy kevesebb, akkor töröljük a sort (delete)

- ha nincs egyezés a név alapján akkor új sort szúrunk be name és new_sal értékkel (insert)

Alaptáblák:

select * from fizetesek;

NAME       SAL
---------- ----------
ADAM       12000
BOB        13000
BOBEK      14000

select * from fiz_modosit;

NAME       NEW_SAL
---------- ----------
ADAM       13000
BOBEK      15000
MARRY      16000

Ha a fiztesek táblát merge paranccsal változtatjuk a fiz_modosit tábla használatával, akkor a következőket várjuk:

- Bobek fizetése 15000 legyen (update)

- Adam sora törlődjön (kevesebb a fizetése, mint 13000) (delete)

- Marry kerüljön bele a fizetések táblába (insert)

merge into fizetesek
  using fiz_modosit
  on (fizetesek.name = fiz_modosit.name)
when matched then
  update set sal = new_sal
  delete where sal <= 13000
when not matched then
  insert (fizetesek.name, fizetesek.sal)
  values (fiz_modosit.name, fiz_modosit.new_sal);
-- 3 rows merged.

select * from fizetesek;
NAME       SAL
---------- ----------
BOB        13000
BOBEK      15000
MARRY      16000

A három változás megtörtént. De felmerül kérdésként, hogy miért nem kerül törlésre Bob fizetése, hiszen megfelel a delete feltételnek (kisebb vagy egyenlő mint 13000). Azért nem kerül törlésre, mert a merge során csak azok a sorokkal történik változás, amelyek a merge során érintettek, azaz benne vannak a using után álló módosítótáblában. Például: Adam szerepelt ebben a táblában, ezért törlődött is a sor, de Bob nem (ezért nem történt vele semmi)

• Egy 'my_table_before' nevű triggert definiálunk, amely a 'my_table' tábla 'statusz' mezője alapján tartja karban a 'my_stat_table' nevű statisztikai segédtáblát:

 create or replace trigger my_table_before
 before insert or update or delete on my_table
 for each row
 begin
     if inserting or
        (updating and :new.statusz <> :old.statusz) then
         update my_stat_table st set st.count = st.count+1
         where st.statusz = :new.statusz;
         if sql%notfound then
             insert into my_stat_table values (:new.statusz, 1);
         end if;
     end if;
     if deleting or
        (updating and :new.statusz <> :old.statusz) then
         update my_stat_table st set st.count = st.count-1
         where st.statusz = :old.statusz;
     end if;
 end;
 /

Megj.: a program végén lévő perjel akkor kell, ha az eljárást az Sql*Plus használatával definiáljuk, ezzel jelezzük a PL/SQL blokk végét.

• Úgynevezett 'névtelen PL/SQL blokk' hívása Pro*C-ből. A PL/SQL blokk egy cursort nyit meg, amelyet egy 'SQL_CURSOR' típusú host-változóban tárolunk.

    SQL_CURSOR crsr;
    varchar ename [32];
    int ideptno, empno, deptno;

    EXEC SQL ALLOCATE :crsr;

    ideptno= 30;
    EXEC SQL EXECUTE
        BEGIN
           OPEN :crsr FOR
           SELECT ename, empno, deptno
           FROM emp
           WHERE deptno = :ideptno;
        END;
    END-EXEC;

    EXEC SQL FETCH :crsr INTO :ename, :empno, :deptno;

Források

• Database PL/SQL Language Reference (Oracle Help Center)