PL/SQL
Η PL/SQL (Procedural Language/SQL) επεκτείνει την SQL
προσθέτοντας δομές που βρίσκονται συνήθως σε διαδικαστικές γλώσσες
προγραμματισμού (Pascal, PL/I). Το αποτέλεσμα είναι μια δομημένη γλώσσα με
περισσότερες δυνατότητες από την SQL. Η βασική μονάδα της PL/SQL είναι το
block. Όλα τα προγράμματα σε PL/SQL αποτελούνται από blocks τα οποία
μπορούν να είναι εμφωλιασμένα. Κάθε block εκτελεί μια λογική πράξη μέσα
στο πρόγραμμα. Ένα block έχει την εξής δομή:
DECLARE
/* Declarative section: Μεταβλητές, τύποι και τοπικά υποπρογράμματα.
*/
BEGIN
/* Executable section: διαδικασίες και οι εντολές PL/SQL. */
/* Είναι το μόνο τμήμα που απαιτείται. */
EXCEPTION
/* Exception handling section:εντολές χειρισμούλαθών*/
END;
To μόνο τμήμα που απαιτείται είναι το executable. Τα
άλλα είναι προαιρετικά. Οι μόνες εντολές της SQL που επιτρέπονται σε ένα
πρόγραμμα PL/SQL είναι οι : SELECT, INSERT, UPDATE, DELETE καθώς
και κάποιες άλλες εντολές χειρισμού δεδομένων. Η εντολή SELECT έχει μια
ειδική μορφή στην οποία μια ν-άδα αποθηκεύεται σε μεταβλητές. Στην PL/SQL δεν
επιτρέπονται εντολές ορισμού δεδομένων (data definition satements), όπως οι
CREATE, DROP και ALTER. To executable section περιέχει
επίσης δομές όπως αναθέσεις, διακλαδώσεις (if-statements), βρόγχους(loops),
κλήσεις διαδικασιών και μια είδκή δομή η trigger. Η PL/SQL δεν κάνει διαχωρισμό
κεφαλαίων-μικρών. Τα σχόλια είναι όπως στη C (/* ..... */).
Για να εκτελέσουμε ένα πρόγραμμα PL/SQL πρέπει να γράψουμε μετά
τον κώδικα του προγράμματος:
- Μια γραμμή που να περιέχει μόνο μια τελεία (".") και μετά
- Μια γραμμή με την λέξη run;
Μπορούμε να εκτελέσουμε προγράμματα PL/SQL μέσα από την
sqlplus είτε γράφοντας τις εντολές στην γραμή διαταγών (SQL>), είτε γράφοντας
τον κώδικα σε ένα αρχείο και καλώντας το από την sqlplus.
Οι πληροφορίες μεταξύ της βάσης δεδομένων και ενός προγράμματος
PL/SQL μεταδίδονται μέσω μεταβλητών η κάθεμια από τις οποίες έχει και έναν
ξεχωριστό τύπο. Αυτός ο τύπος μπορεί να είναι :
- Ένας από τους τύπους που χρησιμοποιεί η SQL για να προσδιορίσει τις
στήλες ενός πίνακα.
- Ένας γενικός τύπος που χρησιμοποιείται από την PL/SQL και λέγεται
NUMBER.
- Ίδιος με τον τύπο μιας στήλης ενός πίνακα
Ο πιο πολυχρησιμοποιημένος γενικός τύπος είναι ο NUMBER. Μεταβλητές αυτού του τύπου μπορούν να είναι είτε ακέραιοι είτε
πραγματικοί αριθμοί. Ο πιο συνηθισμένος τύπος δήλωσης συμβολοσειρών είναι ο
VARCHAR(n) όπου n είναι το μέγιστο μήκος της
συμβολοσειράς σε bytes. Το μήκος είναι απαραίτητο να δηλωθεί και δεν υπάρχει
default τιμή. Για παράδειγμα μπορούμε να δηλώσουμε :
DECLARE
price NUMBER;
myBeer VARCHAR(20);
Οι τύποι της PL/SQL είναι περίεργοι. Κάποιοι συγκεκριμένοι
τύποι της SQL δεν μπορούν να παραμετροποιηθούν στην PL/SQL. Για παράδειγμα η
δήλωση CHAR(50) δεν λειτουργεί
σωστά. Επίσης σημειώστε ότι η PL/SQL επιτρέπει τη χρήση BOOLEAN μεταβλητών, αν και η
Oracle δεν υποστηρίζει BOOLEAN ως τύπο για τις στήλες
ενός πίνακα.
Σε πολλές περιπτώσεις μια PL/SQL μεταβλητή θα χρησιμοποιείται
για τον χειρισμό αποθηκευμένων δεδομένων σε μια υπάρχουσα σχέση. Σε αυτή την
περίπτωση είναι ιδανικό οι μεταβλητές να έχουν τον ίδιο τύπο με την αντίστοιχη
στήλη της σχέσης. Εάν οι τύποι των μεταβλητών δεν συμφωνούν, οι αναθέσεις και οι
συγκρίσεις μεταβλητών δεν θα λειτουργήσουν όπως περιμένετε. Για να είστε
σίγουροι, θα πρέπει να χρησιμοποιείται τον τελεστή %TYPE.
Για
παράδειγμα :
DECLARE
myBeer Beers.name%TYPE;
δίνει στην PL/SQL μεταβλητή myBeer τον τύπο που είχε δηλωθεί η name
στήλη στη σχέση Beers.
Μια μεταβλητή επίσης μπορεί να έχει τύπο που είναι μια εγγραφή
(record) με πολλά πεδία (fields). Ο πιο απλός τρόπος να δηλώσετε μια τέτοια
μεταβλητή είναι να χρησιμοποιήσετε τον τελεστή %ROWTYPE στο σχεσιακό όνομα. Το αποτέλεσμα
είναι ένας τύπος εγγραφής όπου τα πεδία έχουν τα ίδια ονόματα και τους ίδιους
τύπους όπως οι μεταβλητές της σχέσης. Για παράδειγμα :
DECLARE
beerTuple Beers%ROWTYPE;
κάνει τη μεταβλητή beerTuple να είναι μια εγγραφή με πεδία name
και manufacture, υποθέτοντας ότι η σχέση έχει την μορφή Beers(name,
manufacture).
Η αρχική τιμή κάθε μεταβλητής άσχετα από τον τύπο της είναι
NULL . Μπορούμε να αναθέσουμε
τιμές σε μεταβλητές, χρησιμοποιώντας τον τελεστή ":=". Η ανάθεση μπορεί να γίνει
είτε αμέσως με την δήλωση του τύπου της μεςταβλητής, ή οπουδήποτε μέσα στο τμήμα
εκτέλεσης του προγράμματος. Ένα παράδειγμα :
DECLARE
a NUMBER := 3;
BEGIN
a := a + 1;
END;
.
run;
Αυτό το πρόγραμμα δεν έχει κανένα αποτέλεσμα όταν τρέχει,
επειδή δεν γίνονται αλλαγές στην βάση.
Η πιο απλή μορφή προγράμματος έχει κάποιους ορισμούς οι οποίοι
ακολουθούνται από ένα εκτελέσιμο τμήμα, που αποτελείται από μια ή περισσότερες
SQL εντολές με τις οποίες έχετε εξοικειωθεί. Η σύνταξη της εντολής SELECT είναι
διαφορετική από αυτή της SQL. Μετά τη δεσμευμένη λέξη SELECT πρέπει οπωσδήποτε να βάλουμε την δεσμευμένη λέξη INTO με μια λίστα από
μεταβλητές, μια για κάθε στήλη που θέλουμε να προβάλουμε. Σε αυτές τις
μεταβλητές θα αποθηκευτεί το αποτέλεσμα της ερώτησης (ν-άδα). Το αποτέλεσμα της
SELECT σε αυτήν την περίπτωση πρέπει να είναι
μια ν-άδα και όχι περισσότερες. Σε περίπτωση που η εντολή επιστρέφει
περισσότερες από μια ν-άδες, χρειαζόμαστε μια άλλη δομή για την αποθήκευση τους
(cursors).
Για παράδειγμα :
CREATE TABLE T1(
e INTEGER,
f INTEGER
);
DELETE FROM T1;
INSERT INTO T1 VALUES(1, 3);
INSERT INTO T1 VALUES(2, 4);
/* Το παραπάνω είναι απλή SQL. Αυτό που ακολουθεί είναι PL/SQL.*/
DECLARE
a NUMBER;
b NUMBER;
BEGIN
SELECT e,f INTO a,b FROM T1 WHERE e>1;
INSERT INTO T1 VALUES(b,a);
END;
.
run;
Στο παραπάνω παράδειγμα υπήρχε μόνο μια δυάδα που επέστρεφε η
SELECT η (2, 4). Η εντολή INSERT εισάγει μια νέα
γραμμή στον πίνακα Τ1, την (4, 2).
Η PL/SQL
επιτρέπει στον χρήστη να ελέγχει τη ροή του προγράμματος, με τρόπο παρόμοιο με
τις κλασσικές διαδικαστικές γλώσσες προγραμματισμού.
Μια εντολή IF συντάσσεται ως
εξής:
IF
<condition> THEN <statement_list> ELSE <statement_list>
END IF;
Το τμήμα ELSE είναι προαιρετικό. Εάν θέλουμε μια
πολλαπλή διακλάδωση της ροής χρησιμοποιούμε και το ELSIF.
IF <condition_1> THEN ...
ELSIF <condition_2> THEN ...
... ...
ELSIF <condition_n> THEN ...
ELSE ...
END IF;
To παράδειγμα που
ακολουθεί, είναι το προηγούμενο λίγο διαφοροποιημένο. Αυτό που κάνουμε είναι να
εκτελούμε την εισαγωγή της δυάδας στον πίνακα, μόνο εάν το δεύτερο στοιχείο της
είναι μεγαλύτερο του 1. Αλλιώς πρώτα προσθέτουμε 10 σε κάθε στοιχείο της δυάδας
και μετά την εισάγουμε στον πίνακα:
DECLARE
a NUMBER;
b NUMBER;
BEGIN
SELECT e,f INTO a,b FROM T1 WHERE e>1;
IF b=1 THEN
INSERT INTO T1 VALUES(b,a);
ELSE
INSERT INTO T1 VALUES(b+10,a+10);
END IF;
END;
.
run;
Οι επαναλήψεις (loops) γίνονται με
διάφορους τρόπους:
LOOP
<loop_body> /*λίστααπό PL/SQL εντολές. */
END LOOP;
Τουλάχιστον μια από τις εντολές μέσα στο βρόγχο επανάληψης,
πρέπει να είναι της μορφής:
EXIT
WHEN <condition>;
Η επανάληψη σταματάει εάν η συνθήκη
<condition> είναι αληθής. Για παράδειγμα ακολουθεί
ένας τρόπος για να εισάγουμε όλες τις γραμμές από (1, 1) μέχρι την (100, 100)
στον πίνακα Τ1.
DECLARE
i NUMBER := 1;
BEGIN
LOOP
INSERT INTO T1 VALUES(i,i);
i := i+1;
EXIT WHEN i>100;
END LOOP;
END;
.
run;
Μερικές από τις χρήσιμες εντολές που χρησιμοποιούνται για την
δημιουργία δομών επανάληψης στην PL/SQL είναι οι
ακόλουθες:
- ΕΧΙΤ από μόνη της προκαλεί την έξοδο από το loop, χωρίς καμία
συνθήκη.
- Η εντολή
WHILE, η οποία συντάσσεται ως εξής:
WHILE
<condition>
LOOP
<loop_body>
ÈND
LOOP;
- Η εντολή
FOR, η οποία συντάσσεται ως εξής:
FOR
<var>
IN
<start>..<finish>
LOOP
<loop_body>
END
LOOP;
Η
μεταβλητή <var>
μπορεί να είναι οποιαδήποτε μεταβλητή. Είναι τοπική στο σώμα του βρόγχου και
δεν χρειάζεται να δηλωθεί. Επίσης τα <start> και
<finish>
είναι σταθερές.
Ο cursor είναι μια
μεταβλητή με την οποία μπορούμε να διατρέχουμε ν-άδες κάποιας σχέσης. Η σχέση
αυτή μπορεί να είναι ένας πίνακας, ή το αποτέλεσμα μιας ερώτησης. Με το να
αποθηκεύουμε ν-άδες στην δομή cursor, είναι δυνατό να
φτιάχνουμε προγράμματα που να διαβάζουν και να επεξεργάζονται τις τιμές κάθε
στοιχείου αυτών των ν-άδων. Εάν η σχέση είναι αποθηκευμένη (πίνακας), μπορούμε
να αλλάξουμε τις τιμές αυτές ή να διαγράφουμε τελείως τις ν-άδες.
Το παράδειγμα που ακολουθεί παρουσιάζει μια επανάληψη
χρησιμοποιώντας cursor. Χρησιμοποιεί τη σχέση Τ1(e, f), της οποίας οι ν-άδες
είναι ζεύγη ακεραίων. Το πρόγραμμα αυτό διαγράφει κάθε δυάδα που το πρώτο της
στοιχείο είναι μικρότερο από το δεύτερο και εισάγει στον Τ1 την ανάποδη
δυάδα:
1) DECLARE
/* Μεταβλητές που θα αποθηκεύσουν το αποτέλεσμα της ερώτησης:*/
2) a T1.e%TYPE;
3) b T1.f%TYPE;
/* Δήλωση του cursor: */
4) CURSOR T1Cursor IS
5) SELECT e, f
6) FROM T1
7) WHERE e < f
8) FOR UPDATE;
9) BEGIN
10) OPEN T1Cursor;
11) LOOP
/* Τοποθέτησε κάθε δυάδα του cursor σε PL/SQL μεταβλητές: */
12) FETCH T1Cursor INTO a, b;
/* Εαν δεν υπάρχουν άλλες δυάδες στον cursor σταμάτα το loop:*/
13) EXIT WHEN T1Cursor%NOTFOUND;
/* Διέγραψε την τρέχουσα δυάδα: */
14) DELETE FROM T1 WHERE CURRENT OF T1Cursor;
/* Εισήγαγε την αντίστροφη της στον πίνακα : */
15) INSERT INTO T1 VALUES(b, a);
16) END LOOP;
/* Ελευθέρωσε τον cursor.*/
17) CLOSE T1Cursor;
18) END;
19) .
20) run;
Ας περιγράψουμε λίγο το παραπάνω πρόγραμμα :
·
Η γραμμή (1) σηματοδοτεί την περιοχή των
δηλώσεων.
·
Οι γραμμές (2) και (3) δηλώνουν τις μεταβλητές
a και b με τύπους ίδιους με αυτούς των στηλών του πίνακα Τ1(e, f). Αν και ξέρουμε ότι ο
τύπος των στηλών του πίνακα Τ1 είναι INTEGER, δηλώνουμε τις μεταβλητές a και b έτσι ώστε να έχουν τον ίδιο τύπο με τις στήλες του Τ1.
·
Στις γραμμές από (4) έως και (8) ορίζουμε τον
cursor T1cursor. O
ορισμός του γίνεται με μια ερώτηση SELECT-FROM-WHERE, η οποία επιλέγει από τις δυάδες του πίνακα, αυτές που το πρώτο τους
όρισμα είναι μικρότερο από το δεύτερο. Στη γραμμή (8) δηλώνουμε ότι ο
cursor είναι
FOR
UPDATE, εφόσον τροποποιούμε τον Τ1 χρησιμοποιώντας δυάδες
του. Η δήλωση αυτή δε χρειάζεται αν ο cursor δεν πρόκειται να
επηρεάσει ν-άδες του πίνακα.
·
Η γραμμή (9) ξεκινά το εκτελέσιμο τμήμα του
προγράμματος.
·
Η γραμμή (10) ανοίγει τον cursor και αποθηκεύει σ’ αυτόν τις δυάδες του
πίνακα Τ1 που επιλέχθηκαν από την ερώτηση SELECT-FROM-WHERE.
·
Οι γραμμές (11) έως (16) αποτελούν ένα PL/SQL loop, που γίνεται ανάμεσα
στις εντολές LOOP και END
LOOP. Μέσα σε αυτό το loop γίνονται τα
εξής:
1. Μια εντολή FETCH, που μεταφέρει μια δυάδα του cursor σε δυο PL/SQL μεταβλητές. Γενικά η
εντολή FETCH πρέπει να έχει μεταβλητές όσα και τα στοιχεία των ν-άδων του
cursor και με
τους ίδιους τύπους.
2. Στη γραμμή (13) γίνεται
ένας έλεγχος για την έξοδο από το loop. Αυτός ο έλεγχος, το
όνομα του cursor ακολουθούμενο από
%NOTFOUND, είναι αληθής έχουμε προσπελάσει όλες τις ν-άδες του cursor.
3. Στη γραμμή (14) διαγράφω
την τρέχουσα δυάδα του cursor από τον πίνακα Τ1.
Αυτό γίνεται με μια ειδική συνθήκη στην εντολή DELETE, την WHERE
CURRENT OF T1Cursor.
4. Στην γραμμή (15)
εισάγουμε στον πίνακα την αντίστροφη ν-άδα που μόλις έχουμε διαβάσει από τον
cursor.
·
H
γραμμή (17) κλείνει τον cursor.
·
H
γραμμή (18) τερματίζει το PL/SQL πρόγραμμα.
·
Οι γραμμές (19) έως (20) προκαλούν την εκτέλεση του
προγράμματος.
Οι διαδικασίες της PL/SQL συμπεριφέρονται
παρόμοια με τις διαδικασίες σε άλλες γλώσσες προγραμματισμού. Για παράδειγμα μια
συνάρτηση addtuple1 που δοθέντος ενός ακέραιου i εισάγει την δυάδα (i,
‘xxx’) σε έναν πίνακα Τ2 :
CREATE TABLE T2 (
a INTEGER,
b CHAR(10)
);
CREATE PROCEDURE addtuple1(i IN NUMBER) AS
BEGIN
INSERT INTO T2 VALUES(i, 'xxx');
END addtuple1;
.
run;
H διαδικασία
ορίζεται χρησιμοποιώντας τις λέξεις CREATE
PROCEDURE ακολουθούμενες από το όνομα της διαδικασίας και
τις παραμέτρους τις.
Σε μια διαδικασία υπάρχει ένας αριθμός παραμέτρων που
ακολουθείτε από ένα mode και τον τύπο της
παραμέτρου. Οι δυνατοί modes είναι ΙΝ (μόνο ανάγνωση), OUT (μόνο εγγραφή), και INOUT (ανάγνωση και εγγραφή). Οι τύποι των μεταβλητών που είναι παράμετροι
μιας διαδικασίας δεν πρέπει να έχουν όρια. Για παράδειγμα CHAR(10) και VARCHAR(20) δεν επιτρέπονται να περνάνε σαν παράμετροι. Πρέπει να χρησιμοποιήσουμε
CHAR και VARCHAR αντίστοιχα.
Τον ορισμό μιας διαδικασίας ακολουθεί η λέξη AS (ή ΙS) και μετά ακολουθεί το σώμα της διαδικασίας που είναι ένα PL/SQL block. Η επανάληψη του
ονόματος της διεργασίας στο END είναι προαιρετική. Η διαφορά από ένα block είναι ότι δεν
χρειάζεται η λέξη DECLARE
αλλά οι
μεταβλητές ορίζονται αμέσως μετά το AS.
... AS
<local_var_declarations>
BEGIN
<procedure_body>
END;
.
run;
H εντολή
run
στο
τέλος απλώς δημιουργεί τη διαδικασία, δεν την εκτελεί. Η εκτέλεση γίνεται σε ένα
άλλο PL/SQL
block
ως εξής:
BEGIN addtuple1(99); END;
.
run;
Η διαδικασία που ακολουθεί εισάγει μια δυάδα στον πίνακα
Τ2.
CREATE PROCEDURE addtuple2(
x T2.a%TYPE,
y T2.b%TYPE)
AS
BEGIN
INSERT INTO T2(a, b)
VALUES(x, y);
END addtuple2;
.
run;
Τώρα με αυτή τη διαδικασία θα εισάγουμε την δυάδα
(10,
'abc').
BEGIN
addtuple2(10, 'abc');
END;
.
run;
Το ακόλουθο παράδειγμα δείχνει τη χρήση μια OUT παραμέτρου:
CREATE TABLE T3 (
a INTEGER,
b INTEGER
);
CREATE PROCEDURE addtuple3(a NUMBER, b OUT NUMBER)
AS
BEGIN
b := 4;
INSERT INTO T3 VALUES(a, b);
END;
.
run;
DECLARE
v NUMBER;
BEGIN
addtuple3(10, v);
END;
.
run;
Όπως φαίνεται στο παραπάνω παράδειγμα η παράμετρος b χρησιμοποιείται μόνο για εγγραφή. Κατά την κλήση της διαδικασίας
χρησιμοποιούμε μια μεταβλητή v για να αποθηκεύσουμε την παράμετρο b. Μετά την κλήση της διαδικασίας η μεταβλητή v
έχει την τιμή 4.
Στην PL/SQL μπορούμε να γράφουμε
συναρτήσεις αντί για διαδικασίες. Στον ορισμό της συνάρτησης πρέπει να
ακολουθήσουμε τη λίστα με παραμέτρους της συνάρτησης, με RETURN και με τον τύπο που
επιστρέφει η συνάρτηση:
CREATE FUNCTION <func_name>(<param_list>) RETURN <return_type> AS ...
Στο σώμα της συνάρτησης η εντολή RETURN
<expression>; τερματίζει τη συνάρτηση και
επιστρέφει μια τιμή.
Για να βρείτε ποιες διαδικασίες ή συναρτήσεις έχετε φτιάξει
μέχρι τώρα πρέπει να γράψετε τα εξής:
select object_type, object_name
from user_objects
where object_type = 'PROCEDURE'
or object_type = 'FUNCTION';
Για να διαγράψετε μια διαδικασία ή συνάρτηση χρησιμοποιήστε την
εντολή DROP.
drop procedure <procedure_name>;
drop function <function_name>;
Το trigger είναι ειδική δομή της PL/SQL
παρόμοιο με τις procedures. Όμως, μια procedure καλείται ρητά από κάποιο block
του προγράμματος μέσω καλεσμάτων, ενώ ένα trigger εκτελείται αυτόματα όποτε
συμβαίνει κάποιο triggering event. Triggering event μπορεί να είναι κάποια από
τις εντολές INSERT, DELETE ή UPDATE. Το timing μπορεί να είναι είτε BEFORE είτε
AFTER. To trigger μπορεί να είναι είτε row-level είτε statement-level, όπου το
πρώτο εκτελείται μια φορά για κάθε γραμμή που επηρεάζεται από την triggering
δήλωση και το δεύτερο μία φορά για όλη τη δήλωση.
Παρακάτω δίνεται η σύνταξη για τη δημιουργία
ενός trigger στην Oracle (η σύνταξη έχει απλοποιηθεί και για την πλήρη έκδοση
δοκιμάστε HELP CREATE TRIGGER στην sqlplus)
CREATE [OR REPLACE] TRIGGER <trigger_name>
{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name> [FOR EACH ROW [WHEN (<trigger_condition>)]]
<trigger_body>
Μερικές σημαντικές σημειώσεις
·
Μπορείται να δημιουργήσετε μόνο BEFORE ή AFTER
triggers για τα tables. (Οι INSTEAD OF triggers είναι διαθέσιμες μόνο για
προβολές και τυπικά χρησιμοποιούνται μόνο για να δημιουργήσουν προβολές
ενημερώσεων)
·
Μπορείται να ορίσετε μέχρι τρία triggering events
χρησιμοποιώντας την δεσμευμένη λέξη OR. Επίσης το UPDATE μπορεί προαιρετικά να
ακολουθηθεί από τη δεσμευμένη λέξη OF και μια λίστα γνωρισμάτων στο
<table_name>. Αν υπάρχει, ο όρος OF ορίζει το event να είναι μόνο
ενημέρωση των γνωρισμάτων που υπάρχουν μετά το OF.
Εδώ υπάρχουν μερικά παραδείγματα
... INSERT ON R ...
... INSERT OR DELETE OR UPDATE ON R ...
... UPDATE OF A, B OR INSERT ON R ...
·
Εάν έχει οριστεί η επιλογή
FOR EACH ROW το trigger είναι row-level αλλιώς
είναι
statement-level.
·
Για κάθε row-level trigger, περιορισμός
στα trigger
μπορεί να οριστεί στον όρο WHEN , ανάμεσα σε παρενθέσεις. Ο περιορισμός trigger είναι μια κατάσταση της SQL που πρέπει να
ικανοποιηθεί ώστε η Oracle να μπορέσει να ενεργοποιήσει τα triggers. Αυτή η
κατάσταση δεν μπορεί να περιέχει υποερωτήσεις. Χωρίς τον όρο WHEN ένα
trigger
ενεργοποιείται από κάθε triggering event
·
<trigger_body> είναι ένα block της PL/SQL αντί
για τα statements της SQL. Η Oracle έχει τοποθετήσει συγκεκριμένους περιορισμούς
στο τι μπορείς να κάνεις μέσα στο <trigger_body> με σκοπό να αποφύγει
περιπτώσεις όπου ένα trigger εκτελεί μια λειτουργία που ενεργοποιεί ένα άλλο
trigger το οποίο ενεργοποιεί ένα τρίτο και ούτω καθ’ εξής πράγμα που θα
δημιουργήσει ένα άπειρο loop. Οι περιορισμοί στο <trigger_body>
περιλαμβάνουν
o
Δεν μπορείς να αλλάξεις την ίδια σχέση της οποίας η
μετατροπή θα ενεργοποιήσει το trigger.
o
Δεν μπορείς να τροποποιήσεις μια σχέση που συνδέεται
με την triggering σχέση
Διευκρινίζουμε την σύνταξη της Oracle μέσα από ένα παράδειγμα
βασισμένο στους δυο παρακάτω πίνακες
CREATE TABLE T4 (a INTEGER, b CHAR(10));
CREATE TABLE T5 (c CHAR(10), d INTEGER);
Δημιουργούμε ένα trigger που μπορεί να εισάγει ένα tuple στο T5
όταν ένα tuple έχει εισαχθεί στο T4. Συγκεκριμένα το trigger εξετάζει αν το
καινούριο tuple έχει πρώτο όρο 10 ή λιγότερο και αν έχει τότε εισάγει το
αντίστροφο tuple στο Τ5.
CREATE TRIGGER trig1
AFTER INSERT ON T4
FOR EACH ROW
WHEN (NEW.a <= 10)
BEGIN
INSERT INTO T5 VALUES(:NEW.b, :NEW.a);
END trig1;
.
run;
Οι μεταβλητές NEW και OLD είναι διαθέσιμες να αναφερθούν σε νέα ή παλιά
tuples σχετικά. Σημείωση στο σώμα του trigger τα NEW και OLD πρέπει να έχουν
πρόθεμα τον χαρακτήρα (:) εκτός και αν βρίσκονται μέσα στον όρο WHEN.
Ξανά σημειώστε ότι τελειώνουμε την δήλωση CREATE TRIGGER με . και run
όπως με όλες της δηλώσεις της PL/SQL γενικά. Τρέχοντας την δήλωση CREATE TRIGGER
κατασκευάζει μόνο ένα trigger και δεν το εκτελεί. Μόνο ένα trigger event όπως η
εισαγωγή στο Τ4 στο παράδειγμα, μπορεί να ενεργοποιήσει την εκτέλεση.
Για να δείτε πληροφορίες για τα triggers χρησιμοποιείστε τα
παρακάτω
select trigger_name from user_triggers;
select trigger_type, table_name, triggering_event
from user_triggers
where trigger_name = '<trigger_name>';
Για να απορρίψετε ένα trigger
drop trigger <trigger_name>;
Για να απενεργοποιήσετε ή να ενεργοποιήσετε ένα
trigger
alter trigger <trigger_name> {disable|enable};
Η PL/SQL δεν σας λέει πάντα για τα λάθη της μεταγλώτισης.
Αντιθέτως, σας δίνει ένα απλό μήνυμα
όπως
“procedure created with compilation errors”. Αν δεν
προσέξετε αμέσως το λάθος σας προσπαθείστε να δώσετε την εντολή
show errors procedure <procedure_name>;
Ομοίως μπορείτε να δείτε τα λάθη που σχετίζονται με την
δημιουργία του trigger με
show errors trigger <trigger_name>;
Τέλος το “SHO ERR” είναι μια συντομογραφία του ”SHOW ERRORS” και
μπορείτε να προσπαθήσετε το
“PROCEDURE….” ή το “TRIGGER …” αν θέλετε μόνο να δείτε το πιο πρόσφατο
λάθος μεταγλώτισης.
Μερικές φορές θέλουμε να εκτυπώσουμε την τιμή μιας τοπικής μεταβλητής
της PL/SQL. Ένας γρήγορος τρόπος είναι να την αποθηκεύσουμε σαν sole νιάδα
κάποιας σχέσης και μετά την PL/SQL δήλωση να εκτυπώσουμε τη σχέση με μια δήλωση
SELECT. Ένας άλλος τρόπος είναι να ορίσουμε μια bind μεταβλητή η οποία είναι ο
μόνος τύπος που μπορεί να εκτυπωθεί με την εντολή print. Οι μεταβλητές bind είναι ο τύπος που πρέπει να έχει ως πρόθεμα το (:)
στις δηλώσεις PL/SQL όπως :new που συζητήθηκε στην ενότητα
Triggers.
Τα βήματα είναι τα εξής:
1.
Ορίζουμε μια bind μεταβλητή ως ακολούθως
VARIABLE <name> <type>
όπου το τμήμα <type>
μπορεί να είναι ένα από τα τρία:NUMBER,CHAR ή CHAR(n).
2.
Μπορούμε να αναθέσουμε στην μεταβλητή μια τιμή σε μια ακόλουθη PL/SQL
δήλωση αλλά πρέπει να βάλουμε πρόθεμα το (:) έξω από τη δήλωση
3.
Μπορούμε να εκτελέσουμε μια δήλωση
PRINT :<name>;
έξω από την PL/SQL
δήλωση
4. Εδώ
είναι ένα απλό παράδειγμα το οποίο τυπώνει την τιμή 1.
VARIABLE x NUMBER
BEGIN
:x := 1;
END;
.
run;
PRINT :x;