Οι αποθηκευμένες διαδικασίες MySQL αντιμετωπίζουν αποτελεσματικά την υπνηλία. Εισαγωγή στις αποθηκευμένες διαδικασίες MySQL

Σας παρουσιάζουμε μια νέα πορεία από την ομάδα Το Codeby- "Δοκιμή διείσδυσης εφαρμογών Ιστού από την αρχή." Γενική θεωρία, προετοιμασία εργασιακού περιβάλλοντος, παθητικό fuzzing και fingerprinting, Active fuzzing, Vulnerabilities, Post-exploitation, Tools, Social Engineering και πολλά άλλα.

Αποθηκευμένες διαδικασίες MySQL (Μέρος 1)

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

Δημιουργία Αποθηκευμένης Διαδικασίας

Ανοίξτε το phpmyadmin. Επιλέξτε τη βάση δεδομένων mytest και κάντε κλικ στον τίτλο της ή στο εικονίδιο Αναζήτηση. Στη συνέχεια, μεταβείτε στην καρτέλα Ρουτίνες και δημιουργήστε μια νέα διαδικασία κάνοντας κλικ στο Προσθήκη ρουτίνας.

Θα εμφανιστεί μια φόρμα που πρέπει να συμπληρώσετε.

Όνομα ρουτίνας (όνομα διαδικασίας/λειτουργίας) ReplyToComment.

Τύπος (τύπος) - διαδικασία. Η διαφορά μεταξύ μιας διαδικασίας και μιας συνάρτησης είναι ότι μια συνάρτηση επιστρέφει πάντα κάποια τιμή και περιέχει μια δήλωση επιστροφής.

Παράμετροι (παράμετροι) Η διαδικασία μας θα έχει δύο παραμέτρους: το κείμενο της απάντησης και το αναγνωριστικό του σχολίου στο οποίο απαντάμε. Και οι δύο παράμετροι θα περάσουν από την εφαρμογή πελάτη μας.

Δημιουργία παραμέτρων διαδικασίας

Ας δημιουργήσουμε την πρώτη παράμετρο

Κατεύθυνσηυποδεικνύουν την κατεύθυνση της παραμέτρου (IN, OUT, INOUT). Στη διαδικασία μας, και οι δύο παράμετροι που θα περάσουν θα είναι εισερχόμενες (IN).

Όνομα (όνομα παραμέτρου) Περιεχόμενο.

Πληκτρολογήστε INT, VARCHAR, DATETIME και ούτω καθεξής. Η παράμετρος Content περιέχει το κείμενο της απάντησης, το οποίο θα αποθηκευτεί στη στήλη comment_content. Αυτή η στήλη έχει συγκεκριμένο τύπο, για να τον προσδιορίσετε, ανοίξτε τον πίνακα wp_comments και μεταβείτε στην καρτέλα Δομή, βρείτε το όνομα της στήλης που χρειαζόμαστε και δείτε τον τύπο της στη στήλη Τύπος. Σε αυτό το παράδειγμα, η στήλη έχει έναν τύπο κειμένου, ο ίδιος τύπος πρέπει να καθοριστεί για την παράμετρό μας.

Μήκος/Τιμές (μήκος ή τιμή) για τον τύπο κειμένου δεν μπορεί να οριστεί αυτό το πεδίο, αλλά συνήθως το μήκος υποδεικνύεται εδώ, για παράδειγμα VARCHAR(20), INT(10) ή κάποια προεπιλεγμένη τιμή.

Επιλογές ως πρόσθετες επιλογές, μπορείτε να καθορίσετε την τρέχουσα κωδικοποίηση στήλης μπορεί επίσης να προβληθεί στην καρτέλα Δομή, στήλη Συλλογή. Ας ορίσουμε την τιμή σε utf8.

αποτέλεσμα

Ας προσθέσουμε μια δεύτερη παράμετρο κάνοντας κλικ στο κουμπί Προσθήκη παραμέτρου.

Κατεύθυνση - IN Name - ComID Type - BIGINT Length/Values ​​- 20 Options - Unsigned

Και οι δύο παράμετροι έχουν δημιουργηθεί, συνεχίζουμε να συμπληρώνουμε τη φόρμα.

Ορισμόςεδώ περιγράφουμε το σώμα της διαδικασίας. Το σώμα είναι ένα μπλοκ που ξεκινά με τη λέξη-κλειδί BEGIN και τελειώνει με τη λέξη-κλειδί ΤΕΛΟΣ. Μέσα στο σώμα της διαδικασίας, μπορείτε να τοποθετήσετε το κείμενο του ερωτήματος, να δηλώσετε μεταβλητές, να χρησιμοποιήσετε κατασκευές διακλάδωσης, βρόχους και πολλά άλλα, όπως σε οποιαδήποτε γλώσσα προγραμματισμού.

όργανο της διαδικασίας

Αρχικά, ας δημιουργήσουμε ένα μπλοκ για την αρχή και το τέλος του σώματος της διαδικασίας μας.

ΑΡΧΗ ΤΕΛΟΣ?

Τώρα ας προσθέσουμε ένα κείμενο ερωτήματος που θα συμπληρώνει τα πεδία στον πίνακα wp-comments όταν προσθέτουμε ένα νέο σχόλιο (απάντηση).

ΑΡΧΙΣΤΕ ΕΙΣΑΓΩΓΗ ΣΤΑ wp_comments (comment_author, comment_author_email, comment_content, comment_date, comment_date_gmt, comment_post_id, comment_parent, comment_approved, user_id) ΤΙΜΕΣ; ΤΕΛΟΣ;

Θα αποθηκεύσουμε τις αντικατεστημένες τιμές σε μεταβλητές. Για να δημιουργήσετε μια μεταβλητή, χρησιμοποιήστε τη λέξη-κλειδί DECLARE, στη συνέχεια καθορίστε το όνομα, τον τύπο και το μήκος της μεταβλητής και μπορείτε επίσης να καθορίσετε την προεπιλεγμένη τιμή. Εάν μια μεταβλητή έχει μια παράμετρο DEFAULT, τότε η μεταβλητή αρχικοποιείται.

ΔΗΛΩΣΗ τύπου ονόματος (μήκος) ΠΡΟΕΠΙΛΟΓΗ προεπιλεγμένη τιμή.

Μπορείτε επίσης να ορίσετε μια τιμή για οποιαδήποτε μεταβλητή χρησιμοποιώντας τον τελεστή SET.

SET όνομα μεταβλητής = τιμή;

Και έτσι θα δημιουργήσουμε τρεις μεταβλητές: Author, Email, UsedID, οι οποίες θα αποθηκεύουν τιμές για τις στήλες: comment_author, comment_author_email, user_id.

ΑΡΧΙΣΤΕ ΔΗΛΩΣΤΕ Συγγραφέας tinytext ΠΡΟΕΠΙΛΟΓΗ "διαχειριστής"; DECLARE UserID bigint(20) DEFAULT 1; -- Δηλώθηκε η μεταβλητή Email DECLARE Email varchar(100); -- Ορίστε την τιμή της μεταβλητής Email SET Email = " [email προστατευμένο]"; ΤΕΛΟΣ;

σχόλιο_περιεχόμενοΑυτή η στήλη αποθηκεύει το κείμενο του σχολίου, το οποίο μεταβιβάζεται στη διαδικασία ως παράμετρος εισαγωγής περιεχομένου. Δεν θα δημιουργήσουμε μια ξεχωριστή μεταβλητή, αλλά απλώς θα αντικαταστήσουμε την τιμή της παραμέτρου εισόδου σε VALUES.

σχόλιο_ημερομηνίαΚαι comment_date_gmtΚαι οι δύο στήλες θα έχουν τις ίδιες τιμές κατά την πρώτη συμπλήρωση. Ας δημιουργήσουμε μια μεταβλητή Date και ας της εκχωρήσουμε ως τιμή το αποτέλεσμα που θα επιστρέψει η ενσωματωμένη συνάρτηση NOW. Αυτή η λειτουργία επιστρέφει την τρέχουσα ημερομηνία και ώρα σε μορφή DATETIME.

ΔΗΛΩΣΗ ΗΜΕΡΟΜΗΝΙΑ ΩΡΑΣ ΠΡΟΕΠΙΛΟΓΗΣ ΤΩΡΑ ();

ΔΗΛΩΣΤΕ ΗΜΕΡΟΜΗΝΙΑ ΩΡΑ. SET MyCurDate = NOW();

σχόλιο_εγκρίθηκεΈχει εγκριθεί το σχόλιο, 1 (ναι) διαφορετικά 0. Ας δημιουργήσουμε τη μεταβλητή Εγκρίθηκε, αλλά πριν ορίσουμε την τιμή θα κάνουμε λίγο έλεγχο.

ΔΗΛΩΣΤΕ Εγκεκριμένο varchar(20); ΕΑΝ Συντάκτης = "διαχειριστής" ΤΟΤΕ ΟΡΙΣΤΕ Εγκρίθηκε = 1; ΑΛΛΟ ΣΕΤ Εγκρίθηκε = 0; ΤΕΛΟΣ ΕΑΝ;

σχόλιο_γονέαςΕδώ πρέπει να καθορίσετε ως τιμή το αναγνωριστικό του σχολίου στο οποίο απαντάμε. Το ID μεταβιβάζεται στη διαδικασία ως δεύτερη παράμετρος εισόδου. Ας δημιουργήσουμε μια μεταβλητή ParentCom και ας της εκχωρήσουμε την τιμή της παραμέτρου που πέρασε.

ΔΗΛΩΣΤΕ ParentCom varchar(20); SET ParentCom = ComID ;

Απομένει τελευταία παράμετρος comment_post_idΕδώ πρέπει να δηλώσετε το αναγνωριστικό της ανάρτησης στην οποία θα αναρτηθεί το σχόλιό μας. Ας δηλώσουμε μια μεταβλητή που ονομάζεται PostID.

DECLARE PostID BIGINT(20);

σε αυτό το σημείο το σώμα της διαδικασίας θα πρέπει να μοιάζει με αυτό

BEGIN -- μπλοκ δήλωσης μεταβλητής ΔΗΛΩΣΗ Συντάκτης tinytext ΠΡΟΕΠΙΛΟΓΗ "διαχειριστής"; DECLARE UserID bigint(20) DEFAULT 1; ΔΗΛΩΣΤΕ Email varchar(100); ΔΗΛΩΣΤΕ ΗΜΕΡΟΜΗΝΙΑ DATETIME ΠΡΟΕΠΙΛΟΓΗ ΤΩΡΑ(); ΔΗΛΩΣΤΕ ParentCom varchar(20); ΔΗΛΩΣΤΕ Εγκεκριμένο varchar(20); DECLARE PostID BIGINT(20); -- Ρύθμιση τιμών μεταβλητών IF Author = "admin" THON SET Approved = 1; ΑΛΛΟ ΣΕΤ Εγκρίθηκε = 0; ΤΕΛΟΣ ΕΑΝ; SET Email = " [email προστατευμένο]"; SET ParentCom = ComID ; -- αίτημα INSERT INTO wp_comments (comment_author, comment_author_email, comment_content, comment_date, comment_date_gmt, comment_post_id, comment_parent, comment_approved, user_id) VALUES (Συντάκτης, Συντάκτης, Date, Email, Παράρτημα_Συγγραφέας, Δηλ. Ταυτότητα χρήστη);

Ορισμός 1

Αποθηκευμένη διαδικασίαείναι ένα ανεξάρτητο κομμάτι κώδικα λογισμικού που δημιουργείται και αποθηκεύεται σε μια βάση δεδομένων MySQL.

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

Δημιουργία Αποθηκευμένης Διαδικασίας

Ας δημιουργήσουμε μια διαδικασία που ονομάζεται GetAllProducts() για να λάβουμε μια λίστα με όλα τα προϊόντα από τον πίνακα.

Για να το κάνετε αυτό, πρέπει να κάνετε λήψη του προγράμματος-πελάτη mysql και να εκτελέσετε τις ακόλουθες εντολές:

Ομάδα DELIMITER //δεν περιλαμβάνονται στις αποθηκευμένες διαδικασίες. Το DELIMITER είναι μια ειδική εντολή που αλλάζει τον τυπικό διαχωριστή ερωτήματος (προεπιλογή ";") σε αυτόν που καθορίζεται μετά από αυτόν. Με αυτή την εντολή θα την αλλάξουμε σε 2 κάθετες (//).

Εάν δεν αλλάξετε τον οριοθέτη, η mysql θα παρερμηνεύσει τη διαδικασία και θα δημιουργήσει ένα σφάλμα. Μετά το ΤΕΛΟΣ, χρησιμοποιείται ένας // οριοθέτης και η εντολή DELIMITER επιστρέφει την τιμή του οριοθέτη ";".

Οι δεσμευμένες λέξεις CREATE PROCEDURE λένε στη mysql να δημιουργήσει μια διαδικασία. Μετά από αυτές τις λέξεις πρέπει να υποδείξετε το όνομα της αποθηκευμένης διαδικασίας (στο παράδειγμα GetAllProducts). Η κενή παρένθεση "()" μετά το όνομα της διαδικασίας σημαίνει ότι η διαδικασία δεν δέχεται μεταβλητές.

Οι εντολές BEGIN και END ανοίγουν και κλείνουν αντίστοιχα ένα μπλοκ κώδικα SQL.

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

ΣΕ Πάγκος εργασίας MySQLδημιουργείται μια διαδικασία κάνοντας δεξί κλικ στις Ρουτίνες και επιλέγοντας το στοιχείο από το αναπτυσσόμενο μενού Διαδικασία δημιουργίας…

Μπορείτε να δείτε τον πλήρη κώδικα που θα σταλεί στη MySQL πριν εγγραφεί στη βάση δεδομένων. Εάν δεν υπάρχουν σφάλματα, κάντε κλικ Ισχύουν.

Μετά τη μεταγλώττιση, η MySQL εγγράφει τη διαδικασία σε έναν κατάλογο. Αφού ολοκληρώσετε την εγγραφή, πατήστε το κουμπί Φινίρισμα.

Μπορείτε να δείτε τη δημιουργημένη αποθηκευμένη διαδικασία στη λίστα Ρουτίνες.

Κλήση αποθηκευμένης διαδικασίας

Για να καλέσετε μια αποθηκευμένη διαδικασία, χρησιμοποιήστε την ενσωματωμένη εντολή SQL ΚΛΗΣΗ(ΚΛΗΣΗ):

Αντί STORED_PROCEDURE_NAMEΥποδεικνύεται το όνομα της αποθηκευμένης διαδικασίας και μια λίστα μεταβλητών υποδεικνύεται σε παρένθεση. Για να καλέσετε τη διαδικασία που δημιουργήθηκε χωρίς μεταβλητές, θα χρησιμοποιηθεί ο κώδικας:

Επειδή η διαδικασία θα πρέπει να εκτελέσει την εντολή * ΕΠΙΛΕΓΩΑΠΟ προϊόντα;**, δηλ. ΕΠΙΛΕΞΤΕ όλα ΑΠΟ τον πίνακα ΠΡΟΪΟΝΤΑ και, στη συνέχεια, ένα παράδειγμα του πώς θα λειτουργούσε θα ήταν το ακόλουθο αποτέλεσμα:

Μεταβλητές σε αποθηκευμένες διαδικασίες

Ορισμός 2

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

Χρησιμοποιούνται για την αποθήκευση οποιουδήποτε αποτελέσματος ενός ερωτήματος ή μιας έκφρασης. Οι μεταβλητές στις αποθηκευμένες διαδικασίες είναι τοπικές, π.χ. δεν μπορούν να καλούνται από έξω.

Δήλωση μεταβλητών

Ορισμός 3

Η διαδικασία που λέει η mysql ότι πρέπει να χρησιμοποιηθεί μια μεταβλητή ονομάζεται δήλωση.

Για να δηλώσετε μεταβλητές, χρησιμοποιήστε τον τελεστή ΔΗΛΩΝΩ:

ΔΗΛΩΝΩ– δεσμευμένος χειριστής.

variable_name– το όνομα της μεταβλητής, για την οποία ισχύουν οι ίδιοι κανόνες με το όνομα μιας στήλης δεδομένων στη MySQL.

τύπος δεδομένων (μέγεθος)– τον ​​τύπο της μεταβλητής που χρησιμοποιείται και το μέγεθός της. Οι μεταβλητές στη MySQL μπορούν να λάβουν τιμές οποιουδήποτε τύπου δεδομένων, όπως DATETIME, VARCHAR, INT κ.λπ.

DEFAULT default_value– σας επιτρέπει να ορίσετε την αρχική τιμή της μεταβλητής. Εάν δεν έχει καθοριστεί, η προεπιλεγμένη τιμή θα είναι NULL.

Για να δημιουργήσετε μια μεταβλητή total_sale, η οποία θα αποθηκεύει μια λίστα αγορών τύπου INT και η οποία θα είναι ίση με 0 από προεπιλογή, γράψτε τον κωδικό:

Για να δηλώσετε πολλές μεταβλητές του ίδιου τύπου, μπορείτε να γράψετε τον κώδικα:

Εκχώρηση τιμών σε μια μεταβλητή

Για να αντιστοιχίσετε μια τιμή σε μια μεταβλητή, χρησιμοποιήστε τον τελεστή ΣΕΙΡΑ.

Για παράδειγμα, δηλώνουμε τη μεταβλητή total_count και στη συνέχεια της εκχωρούμε την τιμή 10:

Εκτός από τον τελεστή SET, ο τελεστής SELECT INTO μπορεί να χρησιμοποιηθεί για τη μεταφορά του αποτελέσματος ενός ερωτήματος σε μια μεταβλητή. Λάβετε υπόψη ότι το ερώτημα πρέπει να επιστρέψει μια κλιμακωτή τιμή (δηλαδή μία).

Η πρώτη γραμμή κώδικα δηλώνει τη μεταβλητή total_products και ορίζει την τιμή της σε 0.

Η δεύτερη και η τρίτη γραμμή χρησιμοποιούν τη δήλωση SELECT INTO για να γράψουν το αποτέλεσμα του ερωτήματος SELECT COUNT(*) FROM products στη μεταβλητή που δημιουργήθηκε.

Μεταβλητό Πεδίο

Κάθε μεταβλητή έχει το δικό της εύρος, το οποίο καθορίζει τη διάρκεια ζωής της.

Εάν δημιουργήσετε μια μεταβλητή μέσα σε μια αποθηκευμένη διαδικασία, θα υπάρχει όσο χρησιμοποιείται.

Εάν δηλώσετε μια μεταβλητή μέσα σε ένα μπλοκ BEGIN END, τότε θα υπάρχει μόνο σε αυτό το μπλοκ.

Οι μεταβλητές με @ στην αρχή των ονομάτων τους είναι παγκόσμια, δηλ. είναι διαθέσιμα καθ' όλη τη διάρκεια της συνεδρίας.

Η πιο κοινή διαφορά μεταξύ διαδικασιών και συναρτήσεων είναι ότι καλούνται διαφορετικά και για διαφορετικούς σκοπούς:

• Η διαδικασία δεν επιστρέφει τιμή. Αντίθετα, καλείται με μια δήλωση CALL για να εκτελέσει μια λειτουργία όπως η τροποποίηση ενός πίνακα ή η επεξεργασία των ανακτημένων εγγραφών.
• Η συνάρτηση καλείται μέσα σε μια έκφραση και επιστρέφει μια τιμή στον άμεσο καλούντα, η οποία πρέπει να χρησιμοποιηθεί στην έκφραση.
• Δεν μπορείτε να καλέσετε μια συνάρτηση με μια πρόταση CALL και δεν μπορείτε να καλέσετε μια διαδικασία σε μια έκφραση.

Η σύνταξη για τη δημιουργία ρουτίνας είναι ελαφρώς διαφορετική για διαδικασίες και συναρτήσεις:

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

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

• Για να καλέσετε μια αποθηκευμένη διαδικασία, χρησιμοποιήστε μια δήλωση CALL. Για να καλέσετε μια αποθηκευμένη συνάρτηση, ανατρέξτε σε αυτήν σε μια έκφραση. Η συνάρτηση επιστρέφει μια τιμή κατά την αξιολόγηση της έκφρασης.

  Μια διαδικασία καλείται χρησιμοποιώντας μια δήλωση CALL και μπορεί να επιστρέψει τιμές μόνο χρησιμοποιώντας μεταβλητές εξόδου. Μια συνάρτηση μπορεί να κληθεί μέσα από μια δήλωση όπως και κάθε άλλη συνάρτηση (δηλαδή καλώντας το όνομα της συνάρτησης) και μπορεί να επιστρέψει μια βαθμωτή τιμή.

  Η ρύθμιση IN, OUT ή INOUT ισχύει μόνο για τη ΔΙΑΔΙΚΑΣΙΑ. Για τη συνάρτηση FUNCTION, οι παράμετροι θεωρούνται πάντα ως παράμετροι IN.

Εάν μια λέξη-κλειδί δεν έχει καθοριστεί πριν από ένα όνομα παραμέτρου, ορίζεται από προεπιλογή μια παράμετρος IN. Οι παράμετροι για τις αποθηκευμένες λειτουργίες δεν προηγούνται από IN, OUT ή INOUT.Όλες οι παράμετροι συνάρτησης αντιμετωπίζονται ως παράμετροι IN.

Για να ορίσετε μια αποθηκευμένη διαδικασία ή συνάρτηση, χρησιμοποιήστε CREATE PROCEDURE ή CREATE FUNCTION αντίστοιχα:

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑ proc_name () routine_body ΔΗΜΙΟΥΡΓΙΑ ΣΥΝΑΡΤΗΣΗ func_name () RETURNS data_type // diffrent routine_body

Μια επέκταση MySQL σε μια αποθηκευμένη διαδικασία (όχι συναρτήσεις) είναι ότι μια διαδικασία μπορεί να δημιουργήσει ένα σύνολο αποτελεσμάτων, ή ακόμα και πολλαπλά σύνολα αποτελεσμάτων, τα οποία ο καλών εκτελεί με τον ίδιο τρόπο όπως το αποτέλεσμα μιας δήλωσης SELECT. Ωστόσο, τα περιεχόμενα τέτοιων συνόλων αποτελεσμάτων δεν μπορούν να χρησιμοποιηθούν απευθείας σε μια έκφραση.

Αποθηκευμένες Διαδικασίες(αναφορά τόσο στις αποθηκευμένες διαδικασίες όσο και στις αποθηκευμένες λειτουργίες) συσχετίζονται με μια συγκεκριμένη βάση δεδομένων, όπως πίνακες ή προβολές.Όταν απορρίπτετε μια βάση δεδομένων, τυχόν αποθηκευμένες διαδικασίες στη βάση δεδομένων απορρίπτονται επίσης.

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

Μπορείτε να χρησιμοποιήσετε τη δυναμική SQL σε αποθηκευμένες διαδικασίες, αλλά όχι σε συναρτήσεις ή κανόνες ετικέτας.

Οι κονσερβοποιημένες δηλώσεις SQL (PREPARE, EXECUTE, DEALLOCATE PREPARE) μπορούν να χρησιμοποιηθούν σε αποθηκευμένες διαδικασίες, αλλά όχι σε αποθηκευμένες συναρτήσεις ή ενεργοποιητές. Επομένως, οι αποθηκευμένες συναρτήσεις και οι κανόνες ετικέτας δεν μπορούν να χρησιμοποιήσουν τη Dynamic SQL (όπου δημιουργείτε εντολές ως συμβολοσειρές και στη συνέχεια τις εκτελείτε). (Δυναμική SQL σε αποθηκευμένες διαδικασίες MySQL)

Μερικές ακόμα ενδιαφέρουσες διαφορές μεταξύ FUNCTION και STORED PROCEDURE:

Οι συναρτήσεις χρησιμοποιούνται συνήθως για υπολογισμούς, όπου ως διαδικασίες χρησιμοποιούνται συνήθως για την εκτέλεση επιχειρηματικής λογικής.

Οι συναρτήσεις δεν μπορούν να επηρεάσουν την κατάσταση της βάσης δεδομένων (Οι δηλώσεις που κάνουν ρητή ή σιωπηρή δέσμευση ή επαναφορά απαγορεύονται σε μια συνάρτηση) Ενώ οι αποθηκευμένες διαδικασίες μπορούν να επηρεάσουν την κατάσταση της βάσης δεδομένων χρησιμοποιώντας δέσμευση κ.λπ.
παραπομπή: J.1. Περιορισμοί στις αποθηκευμένες διαδικασίες και ενεργοποιητές

Οι λειτουργίες δεν μπορούν να χρησιμοποιούν FLUSH, ενώ οι αποθηκευμένες διαδικασίες μπορούν.

Μέσα σε μια αποθηκευμένη συνάρτηση ή έναυσμα, δεν επιτρέπεται να τροποποιήσετε έναν πίνακα που χρησιμοποιείται ήδη (για ανάγνωση ή εγγραφή) με τη δήλωση που καλεί τη συνάρτηση ή το έναυσμα. Καλό παράδειγμα: Πώς να ενημερώσετε τον ίδιο πίνακα όταν διαγράφεται στη MYSQL;

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

Από τον συγγραφέα:Γιατί κοιμάσαι στη δουλειά! Είστε ξύπνιοι και περιμένετε το DBMS να εκτελέσει το ερώτημα; Πρέπει λοιπόν να επιταχυνθεί. Έχετε χρησιμοποιήσει αποθηκευμένες διαδικασίες MySQL; Δεν ξέρω πώς; Λοιπόν, τότε ξυπνήστε, γιατί τώρα θα εξετάσουμε ακριβώς αυτό το θέμα.

Ποιες άλλες διαδικασίες υπάρχουν;

Εάν έχετε φοβία σχετικά με τις ιατρικές διαδικασίες, τότε αυτές οι δομές «δεν είναι το σωστό θέμα». Οπότε δεν χρειάζεται να φοβάσαι. Αλλά σοβαρά, οι αποθηκευμένες διαδικασίες είναι ένα βολικό και χρήσιμο πράγμα για την «υγεία» ενός DBMS. Ονομάζονται επίσης "αποθηκευμένες λειτουργίες MySQL", αλλά αυτός δεν είναι ένας απολύτως ακριβής ορισμός. Αν και ας τα αντιμετωπίσουμε όλα με τη σειρά.

Οι αποθηκευμένες διαδικασίες μπορούν να βελτιστοποιήσουν σημαντικά την απόδοση του διακομιστή και να αυξήσουν την ταχύτητά του, αφού ο κώδικάς τους αποθηκεύεται στην κρυφή μνήμη RAM μετά την πρώτη εκτέλεση. Για όλες τις επόμενες κλήσεις, η διαδικασία θα ανακτηθεί από την κρυφή μνήμη αντί να σταλεί ξανά για εκτέλεση.

Στη MySQL, η κλήση μιας αποθηκευμένης διαδικασίας σημαίνει ότι τα ερωτήματα που είναι γραμμένα στον κώδικά της θα υποβληθούν σε επεξεργασία μόνο στα μισά. Και μόνο εάν αλλάξουν οι τιμές των παραμέτρων τους. Δεν είναι όμως όλα τόσο τέλεια. Ας περιγράψουμε πρώτα τις θετικές πτυχές της χρήσης διαδικασιών:

Ενθυλάκωση λειτουργικότητας - όλος ο κώδικας αποθηκεύεται σε ένα μέρος, διευκολύνοντας την πρόσβαση άλλων εφαρμογών. Με αυτόν τον τρόπο, οι αποθηκευμένες διαδικασίες είναι παρόμοιες με τις λειτουργίες του προγράμματος.

Απομόνωση πρόσβασης δεδομένων – όλοι οι χρήστες δεν έχουν πρόσβαση σε σειρές πίνακα, αλλά μόνο σε αποθηκευμένες διαδικασίες. Αυτό με τη σειρά του αυξάνει το επίπεδο ασφάλειας όλων των δεδομένων.

Αύξηση της ταχύτητας διακομιστή με προσωρινή αποθήκευση και συγχώνευση αιτημάτων.

Στη MySQL, οι αποθηκευμένες διαδικασίες, θεωρητικά, είναι δομές που σχετίζονται με «υψηλότερα θέματα» - προγραμματισμός DBMS. Εσύ κι εγώ (ως επαγγελματίες) λοιπόν, τουλάχιστον αργά, αλλά... Ας επιστρέψουμε όμως στις διαδικασίες και ας περιγράψουμε τις αρνητικές πτυχές της χρήσης τους:

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

Ο χειρισμός δεδομένων γίνεται πιο περίπλοκος - κατά την ανάπτυξη εφαρμογών, θα πρέπει να γράψετε μέρος του κώδικα στην πλευρά του πελάτη.

Η διαδικασία μεταφοράς βάσεων δεδομένων σε άλλες ράγες (DBMS) γίνεται πιο περίπλοκη.

Διαδικασίες στο phpMyAdmin

Αρχικά, ας δούμε τη χρήση αποθηκευμένων διαδικασιών στη MySQL χρησιμοποιώντας το phpMyAdmin ως παράδειγμα. Αυτό θα μας διευκολύνει να κατανοήσουμε αυτόν τον τύπο δομής. Ας αρχίσουμε!

Εκκινούμε το κέλυφος λογισμικού, επιλέγουμε τη δοκιμαστική βάση δεδομένων στα δεξιά. Η βάση δεδομένων μου είναι κόσμος. Στη συνέχεια, στο κύριο μενού στην κορυφή, μεταβείτε στην καρτέλα "Διαδικασίες". Εδώ κάνουμε κλικ στο «Προσθήκη διαδικασίας».

Μετά από αυτό, θα εμφανιστεί το πλαίσιο διαλόγου "Προσθήκη Διαδικασίας". Συμπληρώνουμε όλα τα πεδία που φαίνονται στην εικόνα. Καθορίστε το όνομα και τον τύπο της διαδικασίας. Στη στήλη "Ορισμός", εισαγάγετε τον λογαριασμό χρήστη και στα σχόλια (προαιρετικά) υποδείξτε στον εαυτό σας ότι αυτό είναι απλώς ένα παράδειγμα αποθηκευμένης διαδικασίας.

Ήδη σε αυτό το στάδιο εξοικειωνόμαστε με τις ιδιαιτερότητες της σύνταξης για τη δημιουργία αποθηκευμένων διαδικασιών MySQL. Στο πεδίο «Ορισμός» γράφουμε το σώμα της δομής. Σημειώστε ότι το ερώτημα που εκτελείται είναι μεταξύ των λέξεων-κλειδιών ΑΡΧΗ και ΤΕΛΟΣ:

ΑΡΧΙΣΤΕ ΕΠΙΛΟΓΗ "HELLO, WORD!"; ΤΕΛΟΣ

ΑΡΧΙΖΟΥΝ ΕΠΙΛΕΞΤΕ "HELLO, WORD!" ;

Αυτό το αίτημα δεν εκτελεί καμία ενέργεια με τη βάση δεδομένων, αλλά εμφανίζει μόνο μια επιγραφή. Αυτό το υποδείξαμε στο πεδίο "Πρόσβαση σε δεδομένα SQL".

Για να ολοκληρώσετε τη δημιουργία της πρώτης μας διαδικασίας, κάντε κλικ στο "OK" στο κάτω μέρος. Μετά από αυτό, το πρόγραμμα εμφανίζει ένα «πράσινο» μήνυμα που υποδεικνύει ότι το αίτημα ολοκληρώθηκε με επιτυχία. Ο κωδικός του παρουσιάζεται παρακάτω. Στη MySQL, οι αποθηκευμένες διαδικασίες και οι συναρτήσεις δημιουργούνται χρησιμοποιώντας την ειδική εντολή CREATE PROCEDURE. Αλλά περισσότερα για αυτό αργότερα.

Τώρα ας εκτελέσουμε τη δομή που δημιουργήθηκε για εκτέλεση. Για να το κάνετε αυτό, στην ενότητα "Διαδικασίες", κάντε κλικ στο σύνδεσμο "Εκτέλεση". Μα τι ντροπή είναι αυτό! Πού πήγε ο αγαπημένος μας «πράσινος»; Γιατί το πρόγραμμα «ορκίζεται» και «ουρλιάζει» ότι δεν έχει αρκετή εκχωρημένη μνήμη;

Πού έψαχνε ο συγγραφέας αυτής της δημοσίευσης...! Συγγνώμη, μπερδεύτηκα λίγο. Άλλωστε ο συγγραφέας είμαι εγώ. Ηρέμησε, θα τα φτιάξουμε όλα τώρα! Αυτό το σφάλμα παρουσιάζεται επειδή η τιμή της παραμέτρου thread_stack στο κύριο αρχείο ρυθμίσεων παραμένει αμετάβλητη. Από προεπιλογή, εκχωρούνται 128 Kb για κάθε ροή. Το εκχωρημένο όριο μνήμης RAM είναι αρκετό για την εκτέλεση απλών ερωτημάτων, αλλά όχι αρκετό για διαδικασίες.

Αυτό αποδεικνύει για άλλη μια φορά ότι περισσότεροι πόροι δαπανώνται για την εκτέλεση κανόνων και αποθηκευμένων διαδικασιών στη MySQL.

Μεταβείτε στο αρχείο διαμόρφωσης my.ini και αυξήστε το όριο RAM που έχει οριστεί για κάθε νήμα στα 256 kb. Τώρα εκτελέστε ξανά τη διαδικασία που δημιουργήθηκε. Αυτή τη φορά όλα πήγαν όπως αναμενόταν και το πρόγραμμα επέστρεψε το αποτέλεσμα χωρίς σφάλμα.

Σημειώστε ότι η κλήση πραγματοποιείται χρησιμοποιώντας την εντολή CALL, υποδεικνύοντας το όνομα της διαδικασίας και τις παραμέτρους που γίνονται αποδεκτές (σε παρένθεση).

Πιο περίπλοκο παράδειγμα

Ωστόσο, οι δυνατότητες του phpMyAdmin είναι πιο κατάλληλες για τη γρήγορη δημιουργία διαδικασιών. Και για να αναπτύξετε μια αποθηκευμένη διαδικασία στη MySQL με δυναμικό αριθμό ορισμάτων (για παράδειγμα), θα χρειαστείτε πιο βολικό λογισμικό. Γιατί:

Το phpMyAdmin δεν θέλει να «κατανοήσει» σωστά τις διαδικασίες που δεν δημιουργήθηκαν μέσω ειδικού κατασκευαστή.

Το πρόγραμμα δεν εκτελεί δομές που εκτελούνται υπό root και με κενό κωδικό πρόσβασης, και στο Ντένβερ η δημιουργία ενός νέου χρήστη και η σύνδεση στο phpMyAdmin κάτω από αυτό είναι ένα πραγματικό πρόβλημα.

Εάν παρακολουθείτε προσεκτικά τις δημοσιεύσεις μου και εκπληρώνετε όλες τις «επιθυμίες» που αναφέρονται σε αυτές, τότε θα πρέπει να έχετε ήδη εγκαταστήσει το MySQL Administrator. Σε σχέση με αυτό, πρέπει απλώς να κάνετε λήψη του MySQL Query Browser από αυτόν τον σύνδεσμο. Είναι καλύτερα να χρησιμοποιείτε αυτά τα δύο προγράμματα μαζί: δημιουργήστε διαδικασίες στο πρώτο και δοκιμάστε τα στο άλλο. Πηγαίνω:

Επάνω αριστερά, μεταβείτε στην καρτέλα "Κατάλογος".

Επιλέξτε την επιθυμητή βάση δεδομένων και στο επάνω μενού κάντε κλικ στο "Αποθηκευμένες διαδικασίες" και στο κάτω μέρος στο "Δημιουργία Αποθηκευμένου Proc"

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

ΔΗΜΙΟΥΡΓΙΑ ΟΡΙΣΤΟΥ=`roman`@`localhost` ΔΙΑΔΙΚΑΣΙΑ `proc5`() BEGIN δηλώνω int; set a="SELECT COUNT(*) FROM city as a"; if(a > 1000) THEN SELECT "<1000"; ELSE SELECT ">1000"; ΤΕΛΟΣ ΑΝ; ΤΕΛΟΣ

CREATE DEFINER = ` roman ` @ ` localhost ` PROCEDURE ` proc5 ` () ΑΡΧΙΖΟΥΝ δηλώσει ένα int ; ορίστε ένα = "ΕΠΙΛΟΓΗ COUNT(*) FROM city as a"; αν (α > 1000) ΤΟΤΕ ΕΠΙΛΟΓΗ"<1000" ; ΑΛΛΟΥ ΕΠΙΛΟΓΗ ">1000" ; ΤΕΛΟΣ ΕΑΝ ;

Ίσως έχετε παρατηρήσει ότι δεν μεταβιβάζουμε τιμές μέσα στη διαδικασία. Επιπλέον, η MySQL μπορεί να έχει έναν άγνωστο αριθμό παραμέτρων σε μια αποθηκευμένη διαδικασία, οι οποίες στη συνέχεια μπορούν να περάσουν μέσω νέων δηλώσεων μεταβλητών που βρίσκονται μέσα σε βρόχους.

Για να ξεκινήσετε τη διαδικασία, μεταβείτε στο MySQL Query Browser. Πρώτα, πληκτρολογήστε τον λογαριασμό και τον κωδικό πρόσβασής σας και, στη συνέχεια, στα αριστερά στον "Object Explorer" βρίσκουμε τον φάκελο με την απαιτούμενη βάση δεδομένων. Η υπόλοιπη ακολουθία ενεργειών φαίνεται στην επόμενη εικόνα.

Εκτέλεση μιας διαδικασίας στην PHP

Τώρα ας δούμε πώς ονομάζεται μια αποθηκευμένη διαδικασία MySQL στην PHP. Για να γίνει αυτό, θα πρέπει να «ξανασχεδιάζουμε» ελαφρώς τον κώδικα του προηγούμενου παραδείγματός μας. Θα προσθέσουμε μια παράμετρο εξόδου στη διαδικασία και θα αλλάξουμε επίσης τον κωδικό αιτήματος:

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc6`(εκτός στήλης δεκαδικού) BEGIN SELECT COUNT(*) into col FROM city; ΤΕΛΟΣ

ΔΗΜΙΟΥΡΓΙΑ ΟΡΙΣΜΟΥ = ` root ` @ ` localhost ` PROCEDURE ` proc6 ` (εκτός διαδοχικής δεκαδικής θέσης) ΑΡΧΙΖΟΥΝ SELECT COUNT (*) σε διάσελο FROM city.

Για να καλέσουμε μια διαδικασία από ένα αρχείο PHP και να εξάγουμε το αποτέλεσμα, θα χρησιμοποιήσουμε τις δυνατότητες της κλάσης PDOStatement, που δημιουργήθηκε ειδικά για εργασία με τη βάση δεδομένων μέσω SQL

Αυτή η κλάση έχει εφαρμοστεί σχετικά πρόσφατα και υποστηρίζεται από την PHP από την έκδοση 5.1.0. Πριν από τη χρήση, σας συμβουλεύω να ελέγξετε την έκδοση της γλώσσας που χρησιμοποιείτε χρησιμοποιώντας την ενσωματωμένη συνάρτηση phpversion().

Εργαστηριακή εργασία Νο 1 «Αποθηκευμένες διαδικασίες στο Μ

ySQL"

3.1.Σκοπός εργασίας

Μελετήστε τους τύπους που χρησιμοποιούνται σε MySQL αποθηκευμένες διαδικασίες.

Αποκτήστε δεξιότητες στην εργασία με αποθηκευμένες διαδικασίες χρησιμοποιώντας εντολές SQL και χρησιμοποιώντας ένα βοηθητικό πρόγραμμα dbForge για MySQL.

3.2. Εισαγωγή

Οι αποθηκευμένες διαδικασίες είναι ένα από τα πιο ισχυρά εργαλεία που προσφέρονται στους προγραμματιστές εφαρμογών βάσεων δεδομένων MySQL για την εφαρμογή της επιχειρηματικής λογικής. Οι αποθηκευμένες διαδικασίες (Αγγλικά, stoied proceduies) σας επιτρέπουν να εφαρμόσετε ένα σημαντικό μέρος της λογικής της εφαρμογής σε επίπεδο βάσης δεδομένων και έτσι να αυξήσετε την απόδοση ολόκληρης της εφαρμογής, να συγκεντρώσετε την επεξεργασία δεδομένων και να μειώσετε την ποσότητα του κώδικα που απαιτείται για την ολοκλήρωση των εργασιών που έχουν ανατεθεί. Σχεδόν κάθε αρκετά πολύπλοκη εφαρμογή βάσης δεδομένων δεν μπορεί να κάνει χωρίς τη χρήση αποθηκευμένων διαδικασιών.

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

3.3. Τρόπος εκτέλεσης της εργασίας

2. Αναπτύξτε το κείμενο των αποθηκευμένων διαδικασιών για τρία ερωτήματα σύμφωνα με την παραλλαγή της εργασίας για εργαστηριακή εργασία Νο. 4.

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

3.4. Επιλογές εργασιών

Τα αρχικά δεδομένα για εργαστηριακές εργασίες είναι οι παραλλαγές των εργασιών και τα αποτελέσματα προηγούμενων εργαστηριακών εργασιών.

3.5. Περιεχόμενα της έκθεσης

Η έκθεση πρέπει να περιέχει περιγραφή και αποτελέσματα της εργασίας, που παρουσιάζονται με την ακόλουθη σειρά:

1. Περιγραφή επιχειρηματικών κανόνων που εφαρμόζονται με χρήση αποθηκευμένων διαδικασιών.

2. Εκτύπωση του σεναρίου για τη δημιουργία αποθηκευμένων διαδικασιών με σχόλια για τις δημιουργημένες αποθηκευμένες διαδικασίες.

3. 6. Ερωτήσεις τεστ

1. Καθορίστε μια αποθηκευμένη διαδικασία.

2. Πώς δημιουργείται μια αποθηκευμένη διαδικασία;

3. Πώς να ορίσετε τις παραμέτρους εξόδου μιας αποθηκευμένης διαδικασίας;

4. Πώς αποδίδονται οι τιμές στις μεταβλητές;

5. Πώς να οργανώσετε έναν βρόχο σε μια αποθηκευμένη διαδικασία;

6. Πώς να εφαρμόσετε ένα άλμα υπό όρους σε μια αποθηκευμένη διαδικασία;

7. Πώς να χειριστείτε τα σφάλματα εκτέλεσης μιας αποθηκευμένης διαδικασίας;

3.7.1. Η έννοια της αποθηκευμένης διαδικασίας

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

Ο λόγος δημιουργίας τους είναι ξεκάθαρος και επιβεβαιώνεται από τη συχνή χρήση.

Πίσω

• Κοινή χρήση λογικής με άλλες εφαρμογές. Οι αποθηκευμένες διαδικασίες ενσωματώνουν τη λειτουργικότητα. Αυτό παρέχει συνδεσιμότητα για πρόσβαση και διαχείριση δεδομένων σε διαφορετικές εφαρμογές.
• Απομόνωση χρηστών από πίνακες βάσεων δεδομένων. Αυτό σας επιτρέπει να δίνετε πρόσβαση σε αποθηκευμένες διαδικασίες, αλλά όχι στα ίδια τα δεδομένα του πίνακα.
• Παρέχει μηχανισμό προστασίας. Σύμφωνα με το προηγούμενο σημείο, εάν έχετε πρόσβαση σε δεδομένα μόνο μέσω αποθηκευμένων διαδικασιών, κανένας άλλος δεν μπορεί να διαγράψει τα δεδομένα σας μέσω της εντολής SQL DELETE.
• Βελτιωμένη εκτέλεση ως αποτέλεσμα της μειωμένης κίνησης δικτύου. Χρησιμοποιώντας αποθηκευμένες διαδικασίες, μπορούν να συνδυαστούν πολλαπλά ερωτήματα.

Κατά

• Αυξημένο φόρτο στον διακομιστή βάσης δεδομένων λόγω του γεγονότος ότι το μεγαλύτερο μέρος της εργασίας εκτελείται από την πλευρά του διακομιστή και λιγότερο από την πλευρά του πελάτη.
• Θα πρέπει να μάθετε πολλά. Θα χρειαστεί να μάθετε τη σύνταξη έκφρασης MySQL για να γράψετε τις αποθηκευμένες διαδικασίες σας.
• Αντιγράφετε τη λογική της εφαρμογής σας σε δύο σημεία: κώδικα διακομιστή και κώδικα για αποθηκευμένες διαδικασίες, περιπλέκοντας έτσι τη διαδικασία χειρισμού δεδομένων.
• Η μετεγκατάσταση από ένα DBMS σε άλλο (DB2, SQL Server, κ.λπ.) μπορεί να οδηγήσει σε προβλήματα.

Πώς να εργαστείτε με αποθηκευμένες διαδικασίες

Δημιουργία Αποθηκευμένης Διαδικασίας

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ p2()

SQL SECURITY INVOKER

ΣΧΟΛΙΟ «Αυτή είναι η πρώτη μου διαδικασία»

ΑΡΧΙΖΟΥΝ

ΕΠΙΛΕΞΤΕ "Γεια";

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

Τα ονόματα των αποθηκευμένων διαδικασιών κάνουν διάκριση πεζών-κεφαλαίων. Δεν μπορείτε επίσης να δημιουργήσετε πολλές διαδικασίες με το ίδιο όνομα. Δεν μπορούν να υπάρχουν εκφράσεις μέσα σε μια αποθηκευμένη διαδικασία που να τροποποιούν την ίδια τη βάση δεδομένων.

4 χαρακτηριστικά μιας αποθηκευμένης διαδικασίας:

• Γλώσσα: Για λόγους φορητότητας, η προεπιλογή είναι SQL.
• Deterministic: εάν η διαδικασία επιστρέφει πάντα το ίδιο αποτέλεσμα και παίρνει τις ίδιες παραμέτρους εισαγωγής. Αυτό είναι για τη διαδικασία αναπαραγωγής και εγγραφής. Η προεπιλεγμένη τιμή ΔΕΝ είναι ΑΠΟΔΟΤΙΚΗ.
• SQL Security: τα δικαιώματα χρήστη ελέγχονται κατά τη διάρκεια της κλήσης. Ο INVOKER είναι ο χρήστης που καλεί την αποθηκευμένη διαδικασία. Ο DEFINER είναι ο «δημιουργός» της διαδικασίας. Η προεπιλεγμένη τιμή είναι DEFINER.
• Σχόλιο: Για λόγους τεκμηρίωσης, η προεπιλεγμένη τιμή είναι

Κλήση αποθηκευμένης διαδικασίας

Για να καλέσετε μια αποθηκευμένη διαδικασία, πρέπει να πληκτρολογήσετε τη λέξη-κλειδί CALL, ακολουθούμενη από το όνομα της διαδικασίας, ακολουθούμενη από τις παραμέτρους (μεταβλητές ή τιμές) σε παρένθεση. Απαιτούνται παρενθέσεις.

CALL p2();

ΚΛΗΣΗ stored_procedure_name (param1, param2, ....)

CALL procedure1(10 , "string parameter" , @parameter_var);

Τροποποίηση αποθηκευμένης διαδικασίας

Η MySQL έχει μια δήλωση ALTER PRO CEDURE για αλλαγή διαδικασιών, αλλά είναι κατάλληλο για αλλαγή μόνο ορισμένων χαρακτηριστικών. Εάν πρέπει να αλλάξετε τις παραμέτρους ή το σώμα μιας διαδικασίας, θα πρέπει να το διαγράψετε και να το δημιουργήσετε ξανά.

Αφαίρεση αποθηκευμένης διαδικασίας

ΔΙΑΔΙΚΑΣΙΑ ΑΠΟΡΡΙΨΗΣ ΑΝ ΥΠΑΡΧΕΙ p2;

Αυτή είναι μια απλή εντολή. Η πρόταση IF EXISTS πιάνει σφάλμα εάν δεν υπάρχει τέτοια διαδικασία.

Επιλογές

Ας δούμε πώς μπορούμε να περάσουμε παραμέτρους σε μια αποθηκευμένη διαδικασία.

• CREATE PROCEDURE proc1(): κενή λίστα παραμέτρων
• ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ proc1 (IN varname DATA-TYPE):μία παράμετρος εισόδου. Η λέξη IN είναι προαιρετική επειδή οι προεπιλεγμένες παράμετροι είναι IN (in).
• ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ proc1 (ΕΞΩ ΔΕΔΟΜΕΝΩΝ ονομάτων παραλλαγής):μία παράμετρος επιστροφής.
• CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE):μία παράμετρος, που συμπεριλαμβάνεται και επιστρέφεται.

Φυσικά, μπορείτε να καθορίσετε πολλές παραμέτρους διαφορετικών τύπων.

Παράδειγμα παραμέτρου IN

CREATE PROCEDURE proc_IN (IN var1 INT)

ΑΡΧΙΖΟΥΝ

SELECT var1 + 2 AS αποτέλεσμα.

ΤΕΛΟΣ

Παράδειγμα παραμέτρου OUT

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ proc_OUT (OUT var1 VARCHAR(100))

ΑΡΧΙΖΟΥΝ

SET var1 = "Αυτή είναι δοκιμή";

ΤΕΛΟΣ

Παράδειγμα παραμέτρου INOUT

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ proc_INOUT (ΕΞΟΔΟΣ var1 INT)

ΑΡΧΙΖΟΥΝ

SET var1 = var1 * 2;

ΤΕΛΟΣ

Μεταβλητές

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

Η σύνταξη της δήλωσης μεταβλητής μοιάζει με αυτό:

ΔΗΛΩΣΤΕ το όνομα της παραλλαγής DATA-TYPE DEFAULT προεπιλεγμένη τιμή ;

Ας δηλώσουμε μερικές μεταβλητές:

ΔΗΛΩΣΤΕ a, b ΕΙΣ ΠΡΟΕΠΙΛΟΓΗ 5;

DECLARE str VARCHAR(50);

ΔΗΛΩΣΤΕ v1, v2, v3 TINYINT;

Εργασία με μεταβλητές

Αφού δηλώσετε μια μεταβλητή, μπορείτε να ορίσετε την τιμή της χρησιμοποιώντας τις εντολές:

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ var_proc (ΣΕ παραμέτρους VARCHAR(20))

ΑΡΧΙΖΟΥΝ

ΔΗΛΩΣΤΕ a, b ΕΙΣ ΠΡΟΕΠΙΛΟΓΗ 5;

DECLARE str VARCHAR(50);

ΔΗΛΩΣΤΕ σήμερα TIMESTAMP ΠΡΟΕΠΙΛΟΓΗ CURRENT_DATE.

ΔΗΛΩΣΤΕ v1, v2, v3 TINYINT;

ΕΙΣΑΓΩΓΗ ΣΤΟ Πίνακα 1 ΤΙΜΕΣ (a);

SET str = "Είμαι μια συμβολοσειρά";

SELECT CONCAT(str,paramstr), σήμερα ΑΠΟ τον πίνακα2 WHERE b >=5;

Δομές ελέγχου νήματος

Η MySQL υποστηρίζει κατασκευές IF, CASE, ITERATE, LEAVE LOOP, WHILE και REPEAT για τον έλεγχο των νημάτων σε μια αποθηκευμένη διαδικασία. Θα εξετάσουμε πώς να χρησιμοποιήσετε τα IF, CASE και WHILE, καθώς είναι τα πιο συχνά χρησιμοποιούμενα.

Σχεδιασμός IF

Χρησιμοποιώντας την κατασκευή IF, μπορούμε να εκτελέσουμε εργασίες που περιέχουν συνθήκες:

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ proc_IF (ΣΤΗΝ παράγραφο 1 INT)

ΑΡΧΙΖΟΥΝ

DECLARE variable1 INT;

Μεταβλητή SET1 = param1 + 1;

ΑΝ μεταβλητή1 = 0 ΤΟΤΕ

SELECT μεταβλητή1;

ΤΕΛΟΣ ΕΑΝ;

ΑΝ param1 = 0 ΤΟΤΕ

ΕΠΙΛΟΓΗ "Τιμή παραμέτρου = 0";

ΑΛΛΟΥ

ΕΠΙΛΟΓΗ "Τιμή παραμέτρου<> 0";

ΤΕΛΟΣ ΕΑΝ;

ΤΕΛΟΣ

Σχέδιο θήκης

Το CASE είναι μια άλλη μέθοδος δοκιμής συνθηκών και επιλογής κατάλληλης λύσης. Αυτός είναι ένας πολύ καλός τρόπος για να αντικαταστήσετε πολλές κατασκευές IF. Η κατασκευή μπορεί να περιγραφεί με δύο τρόπους, παρέχοντας ευελιξία στη διαχείριση πολλαπλών παραστάσεων υπό όρους.

ΑΡΧΙΖΟΥΝ

DECLARE variable1 INT;

Μεταβλητή SET1 = param1 + 1;

CASE μεταβλητή1

ΟΤΑΝ 0 ΤΟΤΕ

ΟΤΑΝ 1 ΤΟΤΕ

ΑΛΛΟΥ

ΤΕΛΙΚΗ ΠΕΡΙΠΤΩΣΗ?

ή :

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ proc_CASE (ΣΤΗΝ παράγραφο 1 INT)

ΑΡΧΙΖΟΥΝ

DECLARE variable1 INT;

Μεταβλητή SET1 = param1 + 1;

ΥΠΟΘΕΣΗ

ΟΤΑΝ μεταβλητή1 = 0 ΤΟΤΕ

INSERT INTO TABLE 1 ΤΙΜΕΣ (param1);

ΟΤΑΝ μεταβλητή1 = 1 ΤΟΤΕ

INSERT INTO TABLE 1 ΤΙΜΕΣ (μεταβλητή1).

ΑΛΛΟΥ

INSERT INTO TABLE 1 ΤΙΜΕΣ (99);

ΤΕΛΙΚΗ ΠΕΡΙΠΤΩΣΗ?

ΤΕΛΟΣ

ΕΝΩ σχεδίαση

Τεχνικά, υπάρχουν τρεις τύποι βρόχων: ο βρόχος WHILE, ο βρόχος LOOP και ο βρόχος REPEAT. Μπορείτε επίσης να κάνετε βρόχο χρησιμοποιώντας την τεχνική προγραμματισμού Darth Vader: δηλώσεις GOTO. Εδώπαράδειγμα βρόχου:

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ proc_WHILE (ΣΤΗΝ παράγραφο 1 INT)

ΑΡΧΙΖΟΥΝ

DECLARE variable1, variable2 INT;

Μεταβλητή SET1 = 0;

μεταβλητή WHILE1< param1 DO

INSERT INTO TABLE 1 ΤΙΜΕΣ (param1);

SELECT COUNT(*) INTO variable2 FROM table1;

SET variable1 = variable1 + 1;

ΤΕΛΟΣ ΕΝΩ?

ΤΕΛΟΣ

Δρομείς

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

Η MySQL υποστηρίζει δρομείς σε αποθηκευμένες διαδικασίες. Ακολουθεί μια σύντομη σύνταξη για τη δημιουργία και τη χρήση δρομέα.

/*Δήλωση δρομέα και συμπλήρωσή του */

ΔΗΛΩΣΗ δρομέα - όνομα ΔΡΟΜΕΑΣ ΓΙΑ ΕΠΙΛΟΓΗ ...;

/*Τι να κάνετε όταν δεν υπάρχουν άλλες συμμετοχές*/

ΔΗΛΩΣΤΕ ΣΥΝΕΧΕΙΑ ΧΕΙΡΙΣΤΗΣ ΓΙΑ ΔΕΝ ΒΡΕΘΗΚΕ……

/*Άνοιγμα κέρσορα*/

OPEN δρομέα-όνομα.

/*Ορίστε μια τιμή σε μια μεταβλητή ίση με την τρέχουσα τιμή της στήλης*/

FETCH δρομέα - όνομα INTO μεταβλητή [, μεταβλητή ];

/* Κλείσιμο κέρσορα */

ΚΛΕΙΣΙΜΟ όνομα κέρσορα.

Σε αυτό το παράδειγμα θα εκτελέσουμε μερικές απλές λειτουργίες χρησιμοποιώντας έναν δρομέα:

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ proc_CURSOR (OUT param1 INT)

ΑΡΧΙΖΟΥΝ

ΔΗΛΩΣΤΕ a, b, c INT;

ΔΗΛΩΣΗ ΚΕΡΣΟΡΑ cur1 ΓΙΑ ΕΠΙΛΟΓΗ col1 ΑΠΟ τον πίνακα1.

ΔΗΛΩΣΤΕ ΣΥΝΕΧΕΙΑ ΧΕΙΡΙΣΤΗΣ ΓΙΑ ΔΕΝ ΒΡΕΘΗΚΕ ΣΕΤ b = 1;

ΑΝΟΙΧΤΟ cur1;

ΣΕΤ b = 0;

ΣΕΤ c = 0;

ΕΝΩ b = 0 DO

ΑΝΑΓΚΗ κουρ1 σε α;

ΑΝ b = 0 ΤΟΤΕ

ΣΕΤ c = c + a;

ΤΕΛΟΣ ΕΑΝ;

ΤΕΛΟΣ ΕΝΩ?

ΚΛΕΙΣΙΜΟ cur1;

SET param1 = c;

Οι δρομείς έχουν τρεις ιδιότητες που πρέπει να κατανοήσετε για να αποφύγετε απροσδόκητα αποτελέσματα:

• Μη ευαίσθητο: ένας δρομέας που ανοίγει μία φορά δεν θα αντικατοπτρίζει τις αλλαγές στον πίνακα που προκύπτουν αργότερα. Στην πραγματικότητα, η MySQL δεν εγγυάται ότι ο δρομέας θα ενημερωθεί, επομένως μην βασίζεστε σε αυτόν.
• Μόνο για ανάγνωση: Οι δρομείς δεν μπορούν να τροποποιηθούν.
• Χωρίς επαναφορά: ο κέρσορας μπορεί να κινηθεί μόνο προς μία κατεύθυνση - προς τα εμπρός, δεν θα μπορείτε να παραλείψετε γραμμές χωρίς να τις επιλέξετε.

συμπέρασμα

Θα πρέπει να υπολογίσετε τα οφέλη από τη χρήση αποθηκευμένων διαδικασιών στη συγκεκριμένη εφαρμογή σας και, στη συνέχεια, να δημιουργήσετε μόνο τις απαραίτητες διαδικασίες. Γενικά χρησιμοποιώ διαδικασίες. Κατά τη γνώμη μου, αξίζει να εφαρμοστούν σε έργα λόγω της ασφάλειας, της συντήρησης κώδικα και του συνολικού σχεδιασμού τους. Επίσης, λάβετε υπόψη ότι οι διαδικασίες MySQL είναι ακόμη σε εξέλιξη. Αναμένετε βελτιώσεις όσον αφορά τη λειτουργικότητα και βελτιώσεις. Μη διστάσετε να μοιραστείτε τις απόψεις σας.

Παραδείγματα:

CREATE PROCEDURE test.p2(IN P_bdate DATE)

SQL SECURITY INVOKER

ΣΧΟΛΙΟ "Αυτή είναι η πρώτη μου διαδικασία"

ΑΡΧΙΖΟΥΝ

SELECT * FROM person WHERE bdate<=P_bdate;

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ test.p1(ΣΕ ταξινόμησηΠεδίο VARCHAR(255), ΣΕ ταξινόμησηΣειρά VARCHAR(255), ΣΤΗΝ πρώτηΣειράΕυρετήριο SMALLINT, ΣΤΙΣ σειρέςΑνάΣελίδα SMALLINT)

SQL SECURITY INVOKER

ΣΧΟΛΙΟ "Αυτή είναι η πρώτη μου διαδικασία"

ΑΡΧΙΖΟΥΝ

SET @var = concat("SELECT fio, Count(*) AS count FROM persona,pnumber WHERE persona.id_person=pnumber.id_person GROUP BY fio",

" ORDER BY ", sortingField, " ", sortingOrder, " LIMIT ", firstRowIndex, ",", rowsPerPage);

ΠΡΟΕΤΟΙΜΑΣΤΕ zxc ΑΠΟ @var;

ΕΚΤΕΛΕΣΤΕ zxc;