Samenvatting Databanken 2 - Stored Procedures

DATABANKEN 2: STORED PROCEDURES
H1 : PL/SQL INTRODUCTIE

PL/SQL = Procedural Language / Structured Query Language

MOGELIJKHEDEN VAN SQL

SQL is nodig om een relationele databank te maken en te beheren:
o Objecten creëren en inhouden van tabellen wijzigen + opvragen
o Views, sequences, synoniemen, … creëren
o Database beveiligen

SQL is een taal gebaseerd op de verzamelingenleer:
o Rijen uit tabellen zitten in willekeurige volgorde
o In willekeurige volgorde opgevraagd en gewijzigd

SQL is een declaratieve taal:
o Je geeft WAT je wil bereiken, niet HOE
→ 4de generatietaal: hebben een bepaald doel

ONMOGELIJKHEDEN VAN SQL

o Geen variabelen voor tussenresultaten
o Geen procedurele elementen zoals IF of LOOP structuren
o Je kan geen volgorde afdwingen bij verwerken rijen (sinds willekeurig verwerkt)
o Constraints leggen slechts in beperkte mate business rules op aan tabelinhouden

OPLOSSINGEN VIA PL/SQL

o Variabelen declareren voor tussenresultaten
o Bevat procedurele elementen (IF, LOOP)
o D.m.v. cursors en collections rijen in een bepaalde volgorde laten verwerken
o Triggers bieden heel wat mogelijkheden om business rules op te leggen aan
tabelinhouden en om de toegang tot tabellen verder te beveiligen
o Packages schrijven
(om broncode van functies en procedures af te schermen van de gebruikers)
o Supplied packages beschikbaar
(voorgeprogrammeerd en bruikbaar in zelfgeschreven stored procedures)




1

,H2 : BASIS PL/SQL

PL/SQL BLOCK

Een PL/SQL programma bestaat uit 1 of meerdere PL/SQL blokken
Een PL/SQL blok bestaat (maximum) uit 3 delen:

o Declaratie deel
→ waarin variabelen, constanten, cursors, collections,
functies, procedures, exceptions worden gedeclareerd
o Uitvoerings deel
→ waarin de instructies worden opgegeven
o Exceptions deel
→ waarin foutsituaties worden opgevangen

CREATE OR REPLACE PROCEDURE procedurenaam
(parameterlijst)

IS {Declaratie deel}

BEGIN {Uitvoerings deel}

EXCEPTIONS {Exceptions deel}

END procedurenaam;

DECLARATIE DEEL

→ declaratie van o.a. variabelen, constanten, cursors, collections, procedures, functies

Hoe variabelen en constanten declareren?

variabelenaam [CONSTANT] datatype [NOT NULL] [{:=|DEFAULT} expressie];

Variabele naam:
→ laten beginnen met v_ (afspraak)

Datatype:
o Alle SQL-datatypes
o PLS_INTEGER
→ om gehele getallen binair te stockeren + om snel te rekenen
o BOOLEAN
→ 3-waardige logica: TRUE, FALSE en NULL
o Gebruik van %TYPE
→ voordeel: up-to-date datatype
→ nadeel: netwerkverkeer

DEFAULT of :=
→ om een initiele waarde toe te kennen



2

,CONSTANT:
→ gebruiken voor declaratie van een constante
→ verplicht initiële waarde
→ begint met c_ (afspraak)

NOT NULL:
→ verplicht initiële waarde

Voorbeelden:
v_aantal NUMBER(3) := 0;
c_percent CONSTANT NUMBER(3,2) DEFAULT 0.05;
v_mnr medewerkers.mnr%TYPE;
v_oud_mnr v_mnr%TYPE;

UITVOERINGS DEEL

→ bevat de instructies en begint met het woord BEGIN
→ elke instructie wordt afgesloten met “;”

Welke componenten kunnen we terugvinden in instructies?

Operatoren:
o Rekenkundig * / + - ** (** = exponentieel)
o Alfanumerisch || (|| = concatenatie)
o Vergelijkingsoperatoren < > = <= >= != <>
o IS NULL of IS NOT NULL
o BETWEEN … AND
o LIKE
o IN
o Logische operatoren AND, OR en NOT

Functies:
→ alle functies die we kennen uit SQL
(substr, instr, round, trunc, upper, lower, …)
→ behalve DECODE en GROEPSFUNCTIES
(count, sum, avg, max, min)

FOUT: v_max := MAX(lengte);
CORRECT: SELECT MAX(lengte) INTO v_max FROM cursussen;




3

, Programmabesturing:

De IF structuur:

IF logische expressie1
THEN commandoreeks1;
[
ELSIF logische expressie2
THEN commandoreeks2;
]
[
ELSE commandoreeks3;
]
END IF;

Richtlijnen bij het gebruik van IF statements:
o Let op de spelling van de key words! (Bv. ELSIF, END IF)
o PL/SQL werkt met een driewaardige logica (TRUE, FALSE, NULL)
→ wanneer een conditie NULL oplevert wordt er gehandeld als FALSE

De CASE structuur:

CASE variabele
WHEN expressie1
THEN commandoreeks1;
[
WHEN expressie2
THEN commandoreeks2;
]
[
ELSE commandoreeks3;
END CASE;

Richtlijnen bij het gebruik van CASE statements:
o PL/SQL werkt met een driewaardige logica (TRUE, FALSE, NULL)
→ wanneer een conditie NULL oplevert wordt er gehandeld als FALSE
o Als er geen ELSE tak is opgenomen en geen van de (logische) expressies leidt tot
TRUE, dan stopt het programma met een foutmelding

ORA-06592: CASE not found while executing CASE statement

Bemerkingen rond het gebruik van NULL waarden bij condities:
o Enkelvoudige condities waarbij NULLs betrokken zijn resulteren steeds in NULL
o De logische operator NOT toegepast op NULL geeft steeds NULL terug




4