Oracle PL/SQL Αποθηκευμένη Διαδικασία & Συναρτήσεις με Παραδείγματα

Τι είναι τα υποπρογράμματα PL/SQL;

Σε αυτό το σεμινάριο, θα δείτε μια λεπτομερή περιγραφή του τρόπου δημιουργίας και εκτέλεσης των επώνυμων μπλοκ, διαδικασιών και συναρτήσεων.

Οι διαδικασίες και οι συναρτήσεις είναι υποπρογράμματα που μπορούν να δημιουργηθούν και να αποθηκευτούν στη βάση δεδομένων ως αντικείμενα βάσης δεδομένων. Μπορούν να κληθούν ή να αναφερθούν και μέσα σε άλλα μπλοκ.

Καλύπτουμε επίσης τις κύριες διαφορές μεταξύ αυτών των δύο υποπρογραμμάτων και συζητάμε τα Oracle ενσωματωμένες λειτουργίες.

Ορολογίες σε υποπρογράμματα PL/SQL

Πριν μάθουμε για τα υποπρογράμματα PL/SQL, συζητάμε τις διάφορες ορολογίες που αποτελούν μέρος αυτών των υποπρογραμμάτων.

Παράμετρος

Μια παράμετρος είναι μια μεταβλητή ή σύμβολο κράτησης θέσης οποιουδήποτε έγκυρου Τύπος δεδομένων PL/SQL μέσω του οποίου το υποπρόγραμμα PL/SQL ανταλλάσσει τιμές με τον κύριο κώδικα. Αυτή η παράμετρος επιτρέπει την εισαγωγή δεδομένων στα υποπρογράμματα και π.χ.tracαξιών από αυτά.

• Αυτές οι παράμετροι θα πρέπει να ορίζονται μαζί με τα υποπρογράμματα τη στιγμή της δημιουργίας.
• Περιλαμβάνονται στην εντολή κλήσης για να αλληλεπιδρούν με τα υποπρογράμματα.
• Ο τύπος δεδομένων της παραμέτρου στο υποπρόγραμμα και η εντολή κλήσης θα πρέπει να είναι οι ίδιοι.
• Το μέγεθος του τύπου δεδομένων δεν πρέπει να αναφέρεται κατά τη δήλωση της παραμέτρου, καθώς το μέγεθος είναι δυναμικό.

Ανάλογα με τον σκοπό τους, οι παράμετροι ταξινομούνται ως εξής:

1. Παράμετρος IN
2. Παράμετρος OUT
3. Παράμετρος IN OUT

Παράμετρος IN

• Χρησιμοποιείται για την παροχή δεδομένων εισόδου στα υποπρογράμματα.
• Είναι μια μεταβλητή μόνο για ανάγνωση μέσα στα υποπρογράμματα· η τιμή της δεν μπορεί να αλλάξει μέσα στο υποπρόγραμμα.
• Στην εντολή κλήσης, μπορεί να είναι μια μεταβλητή, μια κυριολεκτική τιμή ή μια έκφραση, όπως '5*8' ή 'a/b'.
• Από προεπιλογή, οι παράμετροι είναι τύπου IN.

Παράμετρος OUT

• Χρησιμοποιείται για την λήψη δεδομένων εξόδου από τα υποπρογράμματα.
• Είναι μια μεταβλητή ανάγνωσης-εγγραφής μέσα στα υποπρογράμματα· η τιμή της μπορεί να αλλάξει μέσα σε αυτά.
• Στην εντολή κλήσης, θα πρέπει πάντα να υπάρχει μια μεταβλητή που να διατηρεί την τιμή από το υποπρόγραμμα.

Παράμετρος IN OUT

• Χρησιμοποιείται τόσο για την παροχή εισόδου όσο και για την λήψη εξόδου από τα υποπρογράμματα.
• Είναι μια μεταβλητή ανάγνωσης-εγγραφής μέσα στα υποπρογράμματα· η τιμή της μπορεί να αλλάξει μέσα σε αυτά.
• Στην εντολή κλήσης, θα πρέπει πάντα να υπάρχει μια μεταβλητή που να διατηρεί την τιμή από το υποπρόγραμμα.

Ο τύπος της παραμέτρου θα πρέπει να αναφέρεται κατά τη δημιουργία των υποπρογραμμάτων.

ΑΠΌΔΟΣΗ

Η λέξη-κλειδί RETURN δίνει εντολή στον μεταγλωττιστή να αλλάξει τον έλεγχο από το υποπρόγραμμα στην εντολή κλήσης. Σε ένα υποπρόγραμμα, η εντολή RETURN σημαίνει απλώς ότι ο έλεγχος πρέπει να τερματίσει το υποπρόγραμμα. Μόλις ο ελεγκτής βρει την εντολή RETURN, ο κώδικας αφού την παραλείψει, παραλείπεται.

Κανονικά, το γονικό ή κύριο μπλοκ καλεί τα υποπρογράμματα και ο έλεγχος μετατοπίζεται από το γονικό μπλοκ στο καλούμενο υποπρόγραμμα. Η εντολή RETURN στο υποπρόγραμμα επιστρέφει τον έλεγχο πίσω στο γονικό μπλοκ. Στην περίπτωση των συναρτήσεων, η εντολή RETURN επιστρέφει επίσης μια τιμή, ο τύπος δεδομένων της οποίας αναφέρεται κατά τη στιγμή της δήλωσης της συνάρτησης.

Τι είναι μια διαδικασία σε PL/SQL;

A Διαδικασία στην PL/SQL είναι μια μονάδα υποπρογράμματος που αποτελείται από μια ομάδα εντολών PL/SQL που μπορούν να κληθούν με όνομα. Κάθε διαδικασία έχει το δικό της μοναδικό όνομα και αποθηκεύεται στο Oracle βάση δεδομένων ως αντικείμενο βάσης δεδομένων.

Σημείωση: Ένα υποπρόγραμμα δεν είναι τίποτα άλλο παρά μια διαδικασία και πρέπει να δημιουργηθεί χειροκίνητα σύμφωνα με τις απαιτήσεις. Μόλις δημιουργηθεί, αποθηκεύεται ως αντικείμενο βάσης δεδομένων.

Τα χαρακτηριστικά μιας μονάδας υποπρογράμματος διαδικασίας σε PL/SQL είναι:

• Οι διαδικασίες είναι αυτόνομα μπλοκ που μπορούν να αποθηκευτούν στο βάσεις δεδομένων.
• Μπορούν να κληθούν με το όνομά τους για να εκτελέσουν τις εντολές PL/SQL.
• Χρησιμοποιούνται κυρίως για την εκτέλεση μιας διαδικασίας.
• Μπορούν να έχουν ένθετα μπλοκ ή να είναι ένθετα μέσα σε άλλα μπλοκ ή πακέτα.
• Περιέχουν ένα μέρος δήλωσης (προαιρετικό), ένα μέρος εκτέλεσης και ένα μέρος χειρισμού εξαιρέσεων (προαιρετικό).
• Οι τιμές μπορούν να μεταβιβαστούν ή να ανακτηθούν από μια διαδικασία μέσω παραμέτρων.
• Αυτές οι παράμετροι πρέπει να περιλαμβάνονται στη δήλωση κλήσης.
• Μια διαδικασία μπορεί να έχει μια εντολή RETURN για να επιστρέψει τον έλεγχο στο μπλοκ κλήσης, αλλά δεν μπορεί να επιστρέψει καμία τιμή μέσω της εντολής RETURN.
• Οι διαδικασίες δεν μπορούν να κληθούν απευθείας από τις εντολές SELECT. Μπορούν να κληθούν από ένα άλλο μπλοκ ή μέσω της λέξης-κλειδιού EXEC.

Σύνταξη

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• Η συνάρτηση CREATE PROCEDURE δίνει εντολή στον μεταγλωττιστή να δημιουργήσει μια νέα διαδικασία. Η λέξη-κλειδί 'OR REPLACE' του δίνει εντολή να αντικαταστήσει την υπάρχουσα διαδικασία (εάν υπάρχει) με την τρέχουσα.
• Το όνομα της διαδικασίας πρέπει να είναι μοναδικό.
• Η λέξη-κλειδί «IS» χρησιμοποιείται όταν η αποθηκευμένη διαδικασία είναι ένθετη μέσα σε κάποιο άλλο μπλοκ. Εάν η διαδικασία είναι αυτόνομη, χρησιμοποιείται η λέξη «AS». Εκτός από αυτό το πρότυπο κωδικοποίησης, και οι δύο έχουν την ίδια σημασία.

Παράδειγμα 1: Δημιουργία μιας διαδικασίας και κλήση της χρησιμοποιώντας EXEC. Σε αυτό το παράδειγμα, δημιουργούμε ένα Oracle Μια διαδικασία που δέχεται ένα όνομα ως είσοδο και εκτυπώνει ένα μήνυμα καλωσορίσματος ως έξοδο, χρησιμοποιώντας την εντολή EXEC για να την καλέσει.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)
IS
BEGIN
dbms_output.put_line ('Welcome '|| p_name);
END;
/
EXEC welcome_msg ('Guru99');

Code Επεξήγηση:

• Code γραμμή 1: Δημιουργία της διαδικασίας με όνομα 'welcome_msg' και μία παράμετρο 'p_name' τύπου 'IN'.
• Code γραμμή 4: Εκτύπωση του μηνύματος καλωσορίσματος συνενώνοντας το όνομα εισόδου.
• Η διαδικασία μεταγλωττίζεται με επιτυχία.
• Code γραμμή 7: Κλήση της διαδικασίας χρησιμοποιώντας EXEC με την παράμετρο 'Guru99'. Η διαδικασία εκτελείται και εκτυπώνει το μήνυμα "Καλώς ορίσατε Guru99. "

Τι είναι μια Συνάρτηση;

Μια συνάρτηση είναι ένα αυτόνομο υποπρόγραμμα PL/SQL. Όπως μια διαδικασία, μια συνάρτηση έχει ένα μοναδικό όνομα και αποθηκεύεται ως αντικείμενο βάσης δεδομένων PL/SQL. Τα χαρακτηριστικά της είναι:

• Οι συναρτήσεις είναι αυτόνομα μπλοκ που χρησιμοποιούνται κυρίως για υπολογισμούς.
• Μια συνάρτηση χρησιμοποιεί τη λέξη-κλειδί RETURN για να επιστρέψει μια τιμή, της οποίας ο τύπος δεδομένων ορίζεται κατά τη στιγμή της δημιουργίας.
• Μια συνάρτηση θα πρέπει είτε να επιστρέφει μια τιμή είτε να δημιουργεί μια εξαίρεση. Η εντολή return είναι υποχρεωτική στις συναρτήσεις.
• Μια συνάρτηση χωρίς εντολές DML μπορεί να κληθεί απευθείας σε ένα ερώτημα SELECT, ενώ μια συνάρτηση με DML μπορεί να κληθεί μόνο από άλλα μπλοκ PL/SQL.
• Μπορεί να έχει ένθετα μπλοκ ή να είναι ένθετο μέσα σε άλλα μπλοκ ή πακέτα.
• Περιέχει ένα μέρος δήλωσης (προαιρετικό), ένα μέρος εκτέλεσης και ένα μέρος χειρισμού εξαιρέσεων (προαιρετικό).
• Οι τιμές μπορούν να μεταβιβαστούν ή να ανακτηθούν από τη συνάρτηση μέσω παραμέτρων.
• Αυτές οι παράμετροι πρέπει να περιλαμβάνονται στη δήλωση κλήσης.
• Μια συνάρτηση μπορεί επίσης να επιστρέψει μια τιμή μέσω παραμέτρων OUT εκτός από τη χρήση της συνάρτησης RETURN.
• Δεδομένου ότι επιστρέφει πάντα μια τιμή, η εντολή κλήσης χρησιμοποιεί πάντα έναν τελεστή ανάθεσης για να συμπληρώσει μια μεταβλητή.

Σύνταξη

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• Η εντολή CREATE FUNCTION δίνει εντολή στον μεταγλωττιστή να δημιουργήσει μια νέα συνάρτηση. Η εντολή 'OR REPLACE' του δίνει εντολή να αντικαταστήσει την υπάρχουσα συνάρτηση (εάν υπάρχει) με την τρέχουσα.
• Το όνομα της συνάρτησης πρέπει να είναι μοναδικό.
• Θα πρέπει να αναφέρεται ο τύπος δεδομένων RETURN.
• Η λέξη-κλειδί «IS» χρησιμοποιείται όταν η συνάρτηση είναι ένθετη μέσα σε κάποιο άλλο μπλοκ. Εάν η συνάρτηση είναι αυτόνομη, χρησιμοποιείται η λέξη-κλειδί «AS».

Παράδειγμα 1: Δημιουργία μιας συνάρτησης και κλήση της χρησιμοποιώντας ένα ανώνυμο μπλοκ. Σε αυτό το πρόγραμμα, δημιουργούμε μια συνάρτηση που δέχεται ένα όνομα ως είσοδο και επιστρέφει ένα μήνυμα καλωσορίσματος, χρησιμοποιώντας ένα ανώνυμο μπλοκ και μια εντολή SELECT για να την καλέσει.

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN ('Welcome '|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func ('Guru99');
dbms_output.put_line(lv_msg);
END;
/
SELECT welcome_msg_func('Guru99') FROM DUAL;

Code Επεξήγηση:

• Code γραμμή 1: Δημιουργία της συνάρτησης με όνομα 'welcome_msg_func' και μία παράμετρο 'p_name' τύπου 'IN'.
• Code γραμμή 2: Δηλώνοντας τον τύπο επιστροφής ως VARCHAR2.
• Code γραμμή 5: Επιστρέφει την ενωμένη τιμή 'Καλώς ορίσατε' και την τιμή της παραμέτρου.
• Code γραμμή 8: Ανώνυμο μπλοκ για την κλήση της παραπάνω συνάρτησης.
• Code γραμμή 9: Δηλώνοντας τη μεταβλητή με τον ίδιο τύπο δεδομένων με τον τύπο επιστροφής της συνάρτησης.
• Code γραμμή 11: Κλήση της συνάρτησης και συμπλήρωση της τιμής επιστροφής στη μεταβλητή 'lv_msg'.
• Code γραμμή 12: Εκτύπωση της τιμής της μεταβλητής. Η έξοδος είναι "Καλώς ορίσατε" Guru99. "
• Code γραμμή 14: Κλήση της ίδιας συνάρτησης μέσω μιας εντολής SELECT. Η τιμή που επιστρέφεται κατευθύνεται στην τυπική έξοδο.

Ομοιότητες μεταξύ μιας διαδικασίας και μιας συνάρτησης

• Και τα δύο μπορούν να κληθούν από άλλα μπλοκ PL/SQL.
• Εάν μια εξαίρεση που προκύπτει στο υποπρόγραμμα δεν αντιμετωπιστεί στο δικό της χειρισμός εξαιρέσεων τμήμα, διαδίδεται στο μπλοκ κλήσης.
• Και οι δύο μπορούν να έχουν όσες παραμέτρους απαιτούνται.
• Και τα δύο αντιμετωπίζονται ως αντικείμενα βάσης δεδομένων στο PL/SQL.

Διαδικασία έναντι Λειτουργίας: Βασικές Διαφορές

Ενσωματωμένες λειτουργίες σε PL/SQL

PL / SQL Περιέχει διάφορες ενσωματωμένες συναρτήσεις για εργασία με τύπους δεδομένων συμβολοσειράς και ημερομηνίας. Εδώ βλέπουμε τις συναρτήσεις που χρησιμοποιούνται συνήθως και τη χρήση τους.

Λειτουργίες Μετατροπής

Αυτές οι ενσωματωμένες συναρτήσεις μετατρέπουν έναν τύπο δεδομένων σε έναν άλλο.

Λειτουργίες συμβολοσειράς

Αυτές οι συναρτήσεις χρησιμοποιούνται στον τύπο δεδομένων χαρακτήρων.

Λειτουργίες ημερομηνίας

Αυτές οι συναρτήσεις χρησιμοποιούνται για τον χειρισμό ημερομηνιών.

Συχνές Ερωτήσεις