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

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

Εισαγωγή

Πολλοί πιστεύουν ότι είναι παρόμοιοι με τις διαδικασίες διαφόρων γλωσσών προγραμματισμού υψηλού επιπέδου (αντίστοιχα, εκτός από το MS SQL). Ίσως, είναι πραγματικά έτσι. Έχουν παρόμοιες παραμέτρους, μπορούν να δώσουν παρόμοιες τιμές. Επιπλέον, σε πολλές περιπτώσεις βρίσκονται σε επαφή. Για παράδειγμα, συνδυάζονται με βάσεις δεδομένων DDL και DML, καθώς και με λειτουργίες χρηστών (κωδικό όνομα - UDF).

Στην πραγματικότητα, οι αποθηκευμένες διαδικασίες SQL έχουν ένα ευρύ φάσμα πλεονεκτημάτων που τα διαφοροποιούν από παρόμοιες διαδικασίες. Ασφάλεια, μεταβλητότητα προγραμματισμού, παραγωγικότητα - όλα αυτά προσελκύουν χρήστες που εργάζονται με βάσεις δεδομένων, όλο και περισσότερο. Η αιχμή της δημοτικότητας των διαδικασιών συνέβη το 2005-2010, όταν η Microsoft κυκλοφόρησε ένα πρόγραμμα που ονομάζεται SQL Server Management Studio. Με τη βοήθειά του, η εργασία με τις βάσεις δεδομένων έγινε πολύ πιο εύκολη, πιο πρακτική και πιο βολική. Από χρόνο σε χρόνο αυτή η μέθοδος μεταφοράς πληροφοριών κέρδισε δημοτικότητα μεταξύ των προγραμματιστών. Σήμερα, ο MS SQL Server είναι απόλυτα οικείο πρόγραμμα, το οποίο για τους χρήστες που "επικοινωνούν" με βάσεις δεδομένων έχει αυξηθεί μαζί με το "Excel".

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

Για την υλοποίηση αυτής της τεχνολογίας με πληροφορίες, υπάρχουν διάφορες γλώσσες προγραμματισμού. Αυτά περιλαμβάνουν, για παράδειγμα, το PL / SQL από το σύστημα διαχείρισης της βάσης δεδομένων της Oracle, το PSQL στα συστήματα InterBase και Firebird, καθώς και το κλασικό "Microsoft Transact-SQL". Όλα αυτά έχουν σχεδιαστεί για να δημιουργούν και να εκτελούν αποθηκευμένες διαδικασίες, οι οποίες επιτρέπουν τη χρήση μεγάλων αλγορίθμων σε μεγάλους χειριστές βάσεων δεδομένων. Είναι επίσης απαραίτητο για όσους διαχειρίζονται αυτές τις πληροφορίες να προστατεύουν όλα τα αντικείμενα από μη εξουσιοδοτημένη πρόσβαση τρίτων και συνεπώς να δημιουργούν, να αλλάζουν ή να διαγράφουν ορισμένα δεδομένα.

Παραγωγικότητα

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

Ασφάλεια

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

Μεταφορά δεδομένων

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

- μεταφορά δεδομένων με την παράμετρο Output.

- μεταφορά δεδομένων μέσω του χειριστή επιστροφής ·

- μεταφορά δεδομένων μέσω του χειριστή επιλογής.

Τώρα ας δούμε πώς φαίνεται αυτή η διαδικασία από μέσα.

1. Δημιουργήστε μια αποθηκευμένη διαδικασία EXEC σε SQL

Μπορείτε να δημιουργήσετε μια διαδικασία στο MS SQL (Managment Studio). Αφού δημιουργηθεί η διαδικασία, θα μεταφερθεί σε έναν προγραμματιζόμενο κόμβο βάσης δεδομένων στον οποίο η διαδικασία δημιουργίας εκτελείται από τον χειριστή. Για να εκτελέσετε, οι αποθηκευμένες διαδικασίες SQL χρησιμοποιούν μια διεργασία EXEC που περιέχει το όνομα του ίδιου του αντικειμένου.

Κατά τη δημιουργία μιας διαδικασίας, εμφανίζεται πρώτα το όνομά της, μετά από το οποίο παράγονται μία ή περισσότερες παράμετροι που έχουν εκχωρηθεί σε αυτήν. Οι παράμετροι μπορεί να είναι προαιρετικές. Αφού γράψετε την παράμετρο (-ες), δηλαδή το σώμα της διαδικασίας, πρέπει να εκτελέσετε ορισμένες απαραίτητες λειτουργίες.

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

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

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

- το σώμα δεν πρέπει να δημιουργεί άλλη αποθηκευμένη διαδικασία.

- το σώμα δεν πρέπει να δημιουργεί μια ψευδή ιδέα για το αντικείμενο.

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

2. Ορισμός μιας μεταβλητής στο σώμα της διαδικασίας

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

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

Συχνά, οι χρήστες θέτουν την ερώτηση: "Πώς να ορίσετε πολλαπλές τιμές σε έναν φορέα εκμετάλλευσης στο σώμα της διαδικασίας;" Λοιπόν. Το ερώτημα είναι ενδιαφέρον, αλλά είναι πολύ πιο εύκολο να το κάνεις από όσο νομίζεις. Απάντηση: χρησιμοποιώντας ζεύγη όπως "Επιλογή Var = value". Μπορείτε να χρησιμοποιήσετε αυτά τα ζεύγη, χωρίζοντάς τα με κόμμα.

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

Σε μια ποικιλία παραδειγμάτων, οι άνθρωποι δείχνουν πώς να δημιουργήσουν μια απλή αποθηκευμένη διαδικασία και να την εκτελέσουν. Ωστόσο, η διαδικασία μπορεί να λάβει τέτοιες παραμέτρους ώστε η διαδικασία κλήσης να έχει τιμές που είναι κοντά σε αυτήν (αλλά όχι πάντα). Αν συμπίπτουν, τότε οι αντίστοιχες διαδικασίες αρχίζουν μέσα στο σώμα. Για παράδειγμα, αν δημιουργήσετε μια διαδικασία που θα πάρει μια πόλη και μια περιοχή από τον καλούντα και θα επιστρέψει δεδομένα σχετικά με τον αριθμό των συγγραφέων που σχετίζονται με τη σχετική πόλη και περιοχή. Η διαδικασία θα διερευνά τους πίνακες συγγραφέων βάσης δεδομένων, για παράδειγμα, τους Pubs, για να εκτελέσει αυτόν τον αριθμό συγγραφέων. Για να αποκτήσετε αυτές τις βάσεις δεδομένων, για παράδειγμα, το Google φορτώνει τη δέσμη ενεργειών SQL από τη σελίδα SQL2005.

Στο προηγούμενο παράδειγμα, η διαδικασία απαιτεί δύο παραμέτρους, οι οποίες στην αγγλική γλώσσα θα ονομάζονται @State και @City. Ο τύπος δεδομένων αντιστοιχεί στον τύπο που ορίζεται στην εφαρμογή. Το σώμα της διαδικασίας έχει εσωτερικές μεταβλητές @TotalAuthors (όλοι οι συγγραφείς), και αυτή η μεταβλητή χρησιμοποιείται για την εμφάνιση του αριθμού τους. Στη συνέχεια, υπάρχει μια ενότητα για την επιλογή του ερωτήματος, το οποίο μετράει όλα. Τέλος, η υπολογισμένη τιμή εμφανίζεται στο παράθυρο εξόδου χρησιμοποιώντας τον χειριστή εκτύπωσης.

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

Υπάρχουν δύο τρόποι για να εκτελέσετε τη διαδικασία. Η πρώτη διαδρομή εμφανίζει παραμέτρους διέλευσης, καθώς εκτελείται μια λίστα διαχωρισμένη με κόμμα μετά το όνομα της διαδικασίας. Ας υποθέσουμε ότι έχουμε δύο τιμές (όπως στο προηγούμενο παράδειγμα). Αυτές οι τιμές συλλέγονται χρησιμοποιώντας τις μεταβλητές των παραμέτρων διαδικασίας @State και @City. Η παραγγελία είναι σημαντική σε αυτόν τον τρόπο παράδοσης παραμέτρων. Αυτή η μέθοδος ονομάζεται κανονική μεταφορά επιχειρημάτων. Στη δεύτερη μέθοδο, οι παράμετροι έχουν ήδη καθοριστεί άμεσα, και στην περίπτωση αυτή η σειρά δεν είναι σημαντική. Αυτή η δεύτερη μέθοδος είναι γνωστή ως μεταφορά των οριζόμενων επιχειρημάτων.

Η διαδικασία μπορεί να αποκλίνει κάπως από την τυπική. Όλα είναι τα ίδια όπως στο προηγούμενο παράδειγμα, αλλά μόνο εδώ οι παράμετροι μετατοπίζονται. Δηλαδή, η παράμετρος @City αποθηκεύεται πρώτα και το @State αποθηκεύεται δίπλα στην προεπιλεγμένη τιμή. Η προεπιλεγμένη ρύθμιση είναι συνήθως ξεχωριστή. Οι αποθηκευμένες διαδικασίες SQL περνούν ως απλές παράμετροι. Στην περίπτωση αυτή, υπό την προϋπόθεση, η παράμετρος "UT" αντικαθιστά την προεπιλεγμένη τιμή "CA". Στη δεύτερη εκτέλεση, παρέχεται μόνο μία τιμή παραμέτρου για την παράμετρο @City και η παράμετρος @State παίρνει την προεπιλεγμένη τιμή του "CA". Οι έμπειροι προγραμματιστές συμβουλεύουν ότι όλες οι μεταβλητές από προεπιλογή βρίσκονται πιο κοντά στο τέλος της λίστας παραμέτρων. Διαφορετικά, η εκτέλεση δεν είναι δυνατή και, στη συνέχεια, πρέπει να εργαστείτε με τη μεταφορά των ονομάτων, τα οποία είναι μακρύτερα και πιο δύσκολα.

4. Αποθηκευμένες διαδικασίες του SQL Server: Μέθοδοι επιστροφής

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

- να επιστρέψετε την τιμή της αποθηκευμένης διαδικασίας.

- έξοδος της παραμέτρου αποθηκευμένης διαδικασίας,

- Επιλέξτε μία από τις αποθηκευμένες διαδικασίες.

4.1 Επιστροφή τιμών των αποθηκευμένων διαδικασιών SQL

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

Τώρα ας δούμε πώς να εκτελέσουμε τη διαδικασία και να βγάλουμε την τιμή που επέστρεψαν σε αυτήν. Η εκτέλεση της διαδικασίας απαιτεί τη ρύθμιση της μεταβλητής και της εκτύπωσης, η οποία πραγματοποιείται μετά από όλη αυτή τη διαδικασία. Σημειώστε ότι αντί για τη δήλωση εκτύπωσης, μπορείτε να χρησιμοποιήσετε τον χειριστή Select, για παράδειγμα, Select @RetValue, και επίσης OutputValue.

4.2 Έξοδος από την παράμετρο της αποθηκευμένης διαδικασίας SQL

Η τιμή απόκρισης μπορεί να χρησιμοποιηθεί για την επιστροφή μιας μεταβλητής, την οποία είδαμε στο προηγούμενο παράδειγμα. Χρησιμοποιώντας την παράμετρο Έξοδος, η διαδικασία επιτρέπει την αποστολή μιας ή περισσότερων μεταβλητών τιμών στο καλούντα. Η παράμετρος εξόδου ορίζεται ως αυτή τη φορά από αυτή τη λέξη-κλειδί "Έξοδος" κατά τη δημιουργία της διαδικασίας. Εάν η παράμετρος ορίζεται ως παράμετρος εξόδου, τότε το αντικείμενο της διαδικασίας πρέπει να εκχωρήσει μια τιμή σε αυτήν. Οι αποθηκευμένες διαδικασίες SQL, παραδείγματα των οποίων μπορούν να προβληθούν παρακάτω, επιστρέφονται με συνοπτικές πληροφορίες.

Στο παράδειγμά μας, υπάρχουν δύο ονόματα εξόδου: @TotalAuthors και @TotalNoContract. Αυτά ορίζονται στη λίστα παραμέτρων. Αυτές οι μεταβλητές δίνουν τιμές μέσα στο σώμα της διαδικασίας. Όταν χρησιμοποιούμε τις παραμέτρους εξόδου, ο καλών μπορεί να δει την τιμή που έχει οριστεί μέσα στο σώμα της διαδικασίας.

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

4.3 Επιλέξτε μία από τις αποθηκευμένες διαδικασίες SQL

Αυτή η τεχνική χρησιμοποιείται για την επιστροφή ενός συνόλου τιμών ως πίνακα δεδομένων (RecordSet) στην αποθηκευμένη διαδικασία κλήσης. Σε αυτό το παράδειγμα, μια αποθηκευμένη διαδικασία SQL με παραμέτρους @AuthID διερευνά τον πίνακα "Συγγραφείς" φιλτράροντας τις επιστραφείσες εγγραφές χρησιμοποιώντας αυτή την παράμετρο @AuthId. Ο χειριστής επιλογής αποφασίζει τι θα πρέπει να επιστρέψει στην αποθηκευμένη διαδικασία κλήσης. Κατά την εκτέλεση μιας αποθηκευμένης διαδικασίας, το AuthId μεταβιβάζεται πίσω. Μια τέτοια διαδικασία επιστρέφει πάντοτε μόνο ένα αρχείο ή καθόλου. Αλλά η αποθηκευμένη διαδικασία δεν έχει περιορισμούς στην επιστροφή περισσότερων από μία εγγραφών. Είναι συχνά δυνατόν να βρεθούν παραδείγματα στα οποία η επιστροφή δεδομένων χρησιμοποιώντας τις επιλεγμένες παραμέτρους με τη συμμετοχή των υπολογισμένων μεταβλητών συμβαίνει παρέχοντας διάφορες συνοπτικές τιμές.

Συμπερασματικά

Μια αποθηκευμένη διαδικασία είναι μια αρκετά σοβαρή μονάδα λογισμικού που επιστρέφει ή μεταδίδει και επίσης δημιουργεί τις απαραίτητες μεταβλητές μέσω της εφαρμογής πελάτη. Επειδή η αποθηκευμένη διαδικασία εκτελείται στον ίδιο τον διακομιστή, μπορεί να αποφευχθεί η ανταλλαγή δεδομένων σε τεράστιους όγκους μεταξύ του διακομιστή και της εφαρμογής πελάτη (για ορισμένους υπολογισμούς). Αυτό σας επιτρέπει να μειώσετε το φορτίο στον SQL Server, το οποίο, φυσικά, συμβαδίζει με τους κατόχους του. Ένα από τα υποείδη είναι οι αποθηκευμένες διαδικασίες T SQL, ωστόσο, πρέπει να μελετηθούν από εκείνους που δημιουργούν εντυπωσιακές βάσεις δεδομένων. Υπάρχει επίσης ένας μεγάλος, ακόμη και ένας τεράστιος αριθμός αποχρώσεων που μπορεί να είναι χρήσιμος στη μελέτη των αποθηκευμένων διαδικασιών, αλλά αυτό είναι περισσότερο για εκείνους που σκοπεύουν να συμμετάσχουν στον προγραμματισμό, συμπεριλαμβανομένου του επαγγελματικού.