Σπίτι Διαδίκτυο Σε τι χρησιμεύουν οι αποθηκευμένες διαδικασίες; αποθηκευμένες διαδικασίες. Ένα παράδειγμα δημιουργίας μιας διαδικασίας με μια παράμετρο εισόδου

Σε τι χρησιμεύουν οι αποθηκευμένες διαδικασίες; αποθηκευμένες διαδικασίες. Ένα παράδειγμα δημιουργίας μιας διαδικασίας με μια παράμετρο εισόδου

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

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

Διαδικασία ΚΛΗΣΗΣ (...)

ΕΚΤΕΛΕΣΗ διαδικασία (…)

Οι αποθηκευμένες διαδικασίες μπορούν να επιστρέψουν σύνολα αποτελεσμάτων, δηλαδή τα αποτελέσματα ενός ερωτήματος SELECT. Τέτοια σύνολα αποτελεσμάτων μπορούν να υποβληθούν σε επεξεργασία χρησιμοποιώντας δρομείς, με άλλες αποθηκευμένες διαδικασίες που επιστρέφουν έναν δείκτη συνόλου αποτελεσμάτων ή από εφαρμογές. Οι αποθηκευμένες διαδικασίες μπορούν επίσης να περιέχουν δηλωμένες μεταβλητές για το χειρισμό δεδομένων και δρομέων, οι οποίες σας επιτρέπουν να κάνετε βρόχο σε πολλές σειρές σε έναν πίνακα. Το πρότυπο SQL παρέχει IF, LOOP, REPEAT, CASE και πολλές ακόμη εκφράσεις για εργασία. Οι αποθηκευμένες διαδικασίες μπορούν να δεχτούν μεταβλητές, να επιστρέψουν αποτελέσματα ή να τροποποιήσουν μεταβλητές και να τις επιστρέψουν, ανάλογα με το πού δηλώνεται η μεταβλητή.

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

Ανά

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

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

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

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

Κατά

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

Πρέπει να μάθεις πολλά. Θα χρειαστεί να μάθετε τη σύνταξη της έκφρασης MySQL για να γράψετε τις αποθηκευμένες διαδικασίες σας.

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

Η μετεγκατάσταση από ένα DBMS σε άλλο (DB2, SQL Server, κ.λπ.) μπορεί να οδηγήσει σε προβλήματα.

Σκοπός και Οφέλη Αποθηκευμένων Διαδικασιών

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

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

Εκτός από την πραγματική εκτέλεση του ερωτήματος, οι αποθηκευμένες διαδικασίες σάς επιτρέπουν επίσης να πραγματοποιείτε υπολογισμούς και να χειρίζεστε δεδομένα - αλλάζετε, διαγράφετε, εκτελείτε δηλώσεις DDL (όχι σε όλα τα DBMS!) και καλείτε άλλες αποθηκευμένες διαδικασίες, εκτελείτε πολύπλοκη λογική συναλλαγών. Μια μεμονωμένη δήλωση σάς επιτρέπει να καλέσετε μια σύνθετη δέσμη ενεργειών που περιέχεται σε μια αποθηκευμένη διαδικασία, η οποία αποφεύγει την αποστολή εκατοντάδων εντολών μέσω του δικτύου και, ειδικότερα, την ανάγκη μεταφοράς μεγάλων ποσοτήτων δεδομένων από τον πελάτη στον διακομιστή.

Στα περισσότερα DBMS, την πρώτη φορά που εκτελείται μια αποθηκευμένη διαδικασία, αυτή μεταγλωττίζεται (αναλύεται και δημιουργείται ένα σχέδιο πρόσβασης δεδομένων). Στο μέλλον, η επεξεργασία του είναι ταχύτερη. Το Oracle DBMS ερμηνεύει τον αποθηκευμένο διαδικαστικό κώδικα που είναι αποθηκευμένος στο λεξικό δεδομένων. Ξεκινώντας με το Oracle 10g, υποστηρίζεται η λεγόμενη εγγενής μεταγλώττιση (εγγενής μεταγλώττιση) του αποθηκευμένου διαδικαστικού κώδικα στο C και στη συνέχεια στον κώδικα μηχανής της μηχανής προορισμού, μετά την οποία, όταν καλείται η αποθηκευμένη διαδικασία, ο μεταγλωττισμένος κώδικας αντικειμένου της γίνεται απευθείας εκτελούνται.

Επιλογές προγραμματισμού

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

Ασφάλεια

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

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

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

Εφαρμογή Αποθηκευμένων Διαδικασιών

Οι αποθηκευμένες διαδικασίες δημιουργούνται συνήθως με Γλώσσα SQLή τη συγκεκριμένη εφαρμογή του στο επιλεγμένο ΣΔΒΔ. Για παράδειγμα, για αυτούς τους σκοπούς, το Microsoft SQL Server DBMS έχει τη γλώσσα Transact-SQL, η Oracle έχει PL/SQL, η InterBase και το Firebird έχουν PSQL, η PostgreSQL έχει PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2 - SQL / PL (Αγγλικά), σε Informix - SPL. Η MySQL ακολουθεί το πρότυπο SQL:2003 αρκετά στενά και η γλώσσα της είναι παρόμοια με την SQL/PL.

Σε ορισμένα DBMS, είναι δυνατό να χρησιμοποιηθούν αποθηκευμένες διαδικασίες γραμμένες σε οποιαδήποτε γλώσσα προγραμματισμού ικανή να δημιουργήσει ανεξάρτητα εκτελέσιμα αρχεία, όπως η C++ ή η Delphi. Στην ορολογία του Microsoft SQL Server, αυτές οι διαδικασίες ονομάζονται εκτεταμένες αποθηκευμένες διαδικασίες και είναι απλώς συναρτήσεις που περιέχονται σε ένα Win32-DLL. Και, για παράδειγμα, στο Interbase και στο Firebird για συναρτήσεις που καλούνται από DLL / SO, ορίζεται ένα άλλο όνομα - UDF (Συνάρτηση Καθορισμένη από τον Χρήστη). Στο MS SQL 2005, κατέστη δυνατή η εγγραφή αποθηκευμένων διαδικασιών σε οποιαδήποτε γλώσσα .NET και οι εκτεταμένες αποθηκευμένες διαδικασίες σχεδιάζεται να εγκαταλειφθούν στο μέλλον. Το Oracle DBMS, με τη σειρά του, επιτρέπει τη σύνταξη αποθηκευμένων διαδικασιών σε Java. Στο IBM DB2, η εγγραφή αποθηκευμένων διαδικασιών και συναρτήσεων σε συμβατικές γλώσσες προγραμματισμού είναι ο παραδοσιακός τρόπος, που υποστηρίζεται από την αρχή και διαδικαστικά επέκταση SQLπροστέθηκε σε αυτό το ΣΔΒΔ μόνο σε σχετικά πρόσφατες εκδόσεις, μετά την συμπερίληψή του στο πρότυπο ANSI. Το Informix υποστηρίζει επίσης διαδικασίες Java και C.

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

Στο IBM DB2, οι αποθηκευμένες διαδικασίες μπορούν να συνδυαστούν σε λειτουργικές μονάδες.

Σύνταξη

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

SQL SECURITY DEFINER

ΣΧΟΛΙΟ "Μια διαδικασία"

ΕΠΙΛΕΞΤΕ "Hello World!";

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

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

Γλώσσα: Για λόγους φορητότητας, η προεπιλογή είναι SQL.

Deterministic: Αν η διαδικασία επιστρέφει το ίδιο αποτέλεσμα συνεχώς και παίρνει τις ίδιες παραμέτρους εισαγωγής. Αυτό είναι για τη διαδικασία αναπαραγωγής και εγγραφής. Η προεπιλεγμένη τιμή ΔΕΝ είναι ΑΠΟΔΟΤΙΚΗ.

SQL Security: κατά τη διάρκεια της κλήσης, ελέγχονται τα δικαιώματα του χρήστη. Ο INVOKER είναι ο χρήστης που καλεί την αποθηκευμένη διαδικασία. Ο DEFINER είναι ο «δημιουργός» της διαδικασίας. Η προεπιλεγμένη τιμή είναι DEFINER.

Σχόλιο: για λόγους τεκμηρίωσης, η προεπιλεγμένη τιμή είναι ""

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

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

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

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

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

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

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

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

Επιλογές

CREATE PROCEDURE proc1(): κενή λίστα παραμέτρων

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ proc1 (IN varname DATA-TYPE): μία παράμετρος εισαγωγής. Η λέξη IN είναι προαιρετική επειδή οι προεπιλεγμένες παράμετροι είναι IN (εισερχόμενη).

CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): μία παράμετρος επιστροφής.

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ proc1 (INOUT varname DATA-TYPE): μία παράμετρος, είσοδος και έξοδος.

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

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

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

Τύποι αποθηκευμένων διαδικασιών

Ο SQL Server έχει διάφορους τύπους αποθηκευμένων διαδικασιών.

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

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

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

εναύσματα

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

Δώσει το έναυσμα γιαείναι μια μεταγλωττισμένη διαδικασία SQL, η εκτέλεση της οποίας καθορίζεται από την εμφάνιση ορισμένων γεγονότων μέσα σε μια σχεσιακή βάση δεδομένων. Η χρήση ενεργοποιητών είναι, ως επί το πλείστον, πολύ βολική για τους χρήστες της βάσης δεδομένων. Ωστόσο, η χρήση τους συνδέεται συχνά με πρόσθετο κόστος πόρων για λειτουργίες I/O. Όταν μπορούν να επιτευχθούν τα ίδια αποτελέσματα (με πολύ μικρότερο κόστος πόρων) χρησιμοποιώντας αποθηκευμένες διαδικασίες ή εφαρμογές, οι ενεργοποιητές είναι ακατάλληλοι.

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

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

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

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

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

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

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

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

υποστήριξη αναπαραγωγής.

Η βασική μορφή της εντολής CREATE TRIGGER φαίνεται παρακάτω:

<Определение_триггера>::=

CREATE TRIGGER trigger_name

ΠΡΙΝ | ΜΕΤΑ<триггерное_событие>

ΕΠΙ<имя_таблицы>

<список_старых_или_новых_псевдонимов>]

<тело_триггера>

Τα συμβάντα ενεργοποίησης αποτελούνται από την εισαγωγή, τη διαγραφή και την ενημέρωση σειρών σε έναν πίνακα. Στην τελευταία περίπτωση, μπορούν να καθοριστούν συγκεκριμένα ονόματα στηλών πίνακα για το συμβάν ενεργοποίησης. Ο χρόνος έναρξης μιας ενεργοποίησης ορίζεται χρησιμοποιώντας τις λέξεις-κλειδιά ΠΡΙΝ ( Δώσει το έναυσμα γιαπυρκαγιές πριν από την εκτέλεση των σχετικών γεγονότων του) ή ΜΕΤΑ (μετά την εκτέλεσή τους).

Οι ενέργειες που εκτελούνται από το έναυσμα ορίζονται για κάθε σειρά (ΓΙΑ ΚΑΘΕ ΣΕΙΡΑ) που καλύπτεται από αυτό το συμβάν ή μόνο μία φορά για κάθε συμβάν (ΓΙΑ ΚΑΘΕ ΔΗΛΩΣΗ).

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

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

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

Διαδικασίες αποθηκευμένες στο σύστημαδιαδικασία αποθήκευσης συστήματος) παρέχονται από προγραμματιστές DBMS και χρησιμοποιούνται για την εκτέλεση ενεργειών στον κατάλογο του συστήματος ή για τη λήψη πληροφοριών συστήματος. Τα ονόματά τους συνήθως ξεκινούν με το πρόθεμα "sp_". Οι αποθηκευμένες διαδικασίες όλων των τύπων εκτελούνται με την εντολή EXECUTE, η οποία μπορεί να συντομευτεί σε EXEC. Για παράδειγμα, η αποθηκευμένη διαδικασία sp_hellogins, που εκτελείται χωρίς παραμέτρους, δημιουργεί δύο αναφορές ονομάτων λογαριασμού (Αγγλικά) logins) και τους αντίστοιχους χρήστες τους σε κάθε βάση δεδομένων (Αγγλικά)χρήστες).

EXEC sp_hellogins;

Για να δώσετε μια ιδέα των ενεργειών που εκτελούνται χρησιμοποιώντας διαδικασίες αποθηκευμένες στο σύστημα, στον πίνακα. Το 10.6 δείχνει μερικά παραδείγματα. Συνολικά, υπάρχουν περισσότερες από χίλιες αποθηκευμένες διαδικασίες συστήματος στον SQL Server.

Πίνακας 10.6

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

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

Τα προγραμματιζόμενα αντικείμενα SQL Server μπορούν να δημιουργηθούν είτε χρησιμοποιώντας εργαλεία Transact-SQL είτε χρησιμοποιώντας συγκροτήματα (Αγγλικά) assembly) στο περιβάλλον CRL (Common Language Runtime) του Microsoft .Net Framework. Σε αυτό το σεμινάριο, θα ληφθεί υπόψη μόνο η πρώτη μέθοδος.

Για να δημιουργήσετε αποθηκευμένες διαδικασίες, χρησιμοποιήστε τη δήλωση CREATE PROCEDURE (μπορεί να συντομευτεί σε PROC), η μορφή της οποίας δίνεται παρακάτω:

CREATE (PROC I PROCEDURE) proc_name [ ; αριθμός]

[(gparameter data_type )

["προεπιλογή] |

[ΜΕ [ ,...n ] ]

[ΓΙΑ ΑΝΑΠΑΡΑΓΩΓΗ]

AS ([ BEGIN ] sql_statement [;] [ ...n ] [ ΤΕΛΟΣ ] )

Εάν δημιουργηθεί μια αποθηκευμένη διαδικασία (ή έναυσμα, συνάρτηση, προβολή) με την επιλογή ΚΡΥΠΤΩΣΗ, ο κώδικάς της μετατρέπεται με τέτοιο τρόπο ώστε το κείμενο να μην είναι αναγνώσιμο. Ταυτόχρονα, όπως σημειώθηκε στο , ο αλγόριθμος που χρησιμοποιείται μεταφέρεται από προηγούμενες εκδόσεις του SQL Server και δεν μπορεί να θεωρηθεί αξιόπιστος αλγόριθμος προστασίας - υπάρχουν βοηθητικά προγράμματα που σας επιτρέπουν να εκτελέσετε γρήγορα την αντίστροφη μετατροπή.

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

Το EXECUTE AS ορίζει το πλαίσιο ασφαλείας στο οποίο πρόκειται να εκτελεστεί η διαδικασία. Στη συνέχεια, μία από τις τιμές f CALLER | ΕΑΥΤΟΣ | ΙΔΙΟΚΤΗΤΗΣ | "όνομα_χρήστη"). CALLER είναι η προεπιλεγμένη τιμή και σημαίνει ότι ο κωδικός θα εκτελεστεί στο πλαίσιο ασφαλείας του χρήστη που καλεί αυτήν την ενότητα. Κατά συνέπεια, ο χρήστης πρέπει να έχει δικαιώματα όχι μόνο για το ίδιο το προγραμματιζόμενο αντικείμενο, αλλά και για άλλα αντικείμενα βάσης δεδομένων που επηρεάζονται από αυτό. EXECUTE AS SELF σημαίνει χρήση του περιβάλλοντος ενός χρήστη που δημιουργεί ή τροποποιεί ένα προγραμματιζόμενο αντικείμενο. Ο OWNER καθορίζει ότι ο κωδικός θα εκτελεστεί στο πλαίσιο του τρέχοντος κατόχου της διαδικασίας. Εάν δεν έχει οριστεί κάτοχος για αυτό, τότε θεωρείται ο κάτοχος του σχήματος στο οποίο ανήκει. EXECUTE AS "user_name" σάς επιτρέπει να προσδιορίσετε ρητά το όνομα χρήστη (σε μονά εισαγωγικά).

Οι παράμετροι μπορούν να καθοριστούν για μια διαδικασία. Αυτές είναι τοπικές μεταβλητές που χρησιμοποιούνται για τη μεταβίβαση τιμών σε μια διαδικασία. Εάν η παράμετρος δηλωθεί με λέξη-κλειδί OUTPUT (ή OUT για συντομία), είναι μια έξοδος: η τιμή που της δίνεται στη διαδικασία μετά την ολοκλήρωσή της μπορεί να χρησιμοποιηθεί από το πρόγραμμα που κάλεσε τη διαδικασία. Η λέξη-κλειδί READONLY σημαίνει ότι η τιμή της παραμέτρου δεν μπορεί να αλλάξει μέσα στην αποθηκευμένη διαδικασία.

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

CREATE PROC surma (@a int, @b int=0,

©αποτέλεσμα int OUTPUT) AS

SET @result=0a+0b

Έχουμε δημιουργήσει μια διαδικασία με τρεις παραμέτρους, όπου η παράμετρος @b έχει προεπιλεγμένη τιμή 0 και η παράμετρος @result είναι μια παράμετρος εξόδου: μέσω αυτής, η τιμή επιστρέφεται στο καλούν πρόγραμμα. Οι ενέργειες που εκτελούνται είναι αρκετά απλές - η παράμετρος εξόδου λαμβάνει την τιμή του αθροίσματος των δύο εισόδων.

Όταν εργάζεστε σε SQL διαχείριση διακομιστήΗ αποθηκευμένη διαδικασία που δημιουργήθηκε στο Studio μπορεί να βρεθεί στην ενότητα προγραμματιζόμενων αντικειμένων DB (Αγγλικά)Προγραμματισμός) στην ενότητα για αποθηκευμένες διαδικασίες (Εικόνα 10.2).

Κατά την κλήση μιας διαδικασίας, τόσο οι μεταβλητές όσο και οι σταθερές μπορούν να χρησιμοποιηθούν ως παράμετροι εισόδου. Ας εξετάσουμε δύο παραδείγματα. Στην πρώτη, οι παράμετροι εισόδου της διαδικασίας ορίζονται ρητά από σταθερές, η λέξη-κλειδί OUTPUT καθορίζεται για την παράμετρο εξόδου στην κλήση. Στη δεύτερη επιλογή, η τιμή της μεταβλητής χρησιμοποιείται ως η πρώτη παράμετρος εισόδου και η δεύτερη παράμετρος καθορίζεται χρησιμοποιώντας τη λέξη-κλειδί ΠΡΟΕΠΙΛΟΓΗ που πρέπει να χρησιμοποιηθεί η προεπιλεγμένη τιμή:

Ρύζι. 10.2.

ΔΗΛΩΣΤΕ @with int;

EXEC summa 10,5,@c OUTPUT;

PRINT0c; - Θα εμφανιστεί το 15

ΔΗΛΩΣΤΕ Gi int = 5;

- κατά την κλήση, χρησιμοποιήστε την προεπιλεγμένη τιμή

EXEC άθροισμα Gi, DEFAULT , 0s OUTPUT;

PRINT0c; - Θα εμφανιστεί το 5

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

CREATE PROC dbo.rownum(0FirsYear int, GLastYear int, 0result int OUTPUT) ΩΣ

ΕΑΝ ΕΠΙΣΤΡΟΦΗ 0Πρώτου Έτους>0Επιστροφής Πέρυσιου 1

SET @result= (ΕΠΙΛΟΓΗ COUNT(*) FROM dbo.Bookl

ΠΟΥ ΜΕΤΑΞΥ 0 ΠΡΩΤΟ ΚΑΙ 0 Πέρυσι) ;

Εξετάστε μια παραλλαγή της κλήσης αυτής της διαδικασίας, στην οποία ο κωδικός επιστροφής αποθηκεύεται στην ακέραια μεταβλητή 0ret, μετά την οποία αναλύεται η τιμή του (στην περίπτωση αυτή θα είναι 1). χρησιμοποιείται στον χειριστή Λειτουργία PRINTΤο CAST χρησιμοποιείται για τη μετατροπή της τιμής μιας ακέραιας μεταβλητής Gres σε τύπο συμβολοσειράς:

DECLARE 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT "Το έτος έναρξης είναι μεγαλύτερο από το έτος λήξης"

PRINT "Αριθμός βιβλίων "+ CAST(Gres as varchar(20))

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

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

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

CREATE PROC My_Procl (@id int, @name varchar(30))

IF OBJECT_ID("tempdb.dbo.#Tab21) ΕΙΝΑΙ NULL

INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)

ΕΠΙΛΟΓΗ * ΑΠΟ dbo. #Καρτέλα 2 -#1

Πριν από την πρώτη κλήση στην αποθηκευμένη διαδικασία, ας δημιουργήσουμε τον προσωρινό πίνακα #Tab2 που χρησιμοποιείται σε αυτόν. Δώστε προσοχή στον χειριστή EXEC. Στα προηγούμενα παραδείγματα, οι παράμετροι μεταβιβάστηκαν στη διαδικασία "κατά θέση", αλλά σε αυτήν την περίπτωση χρησιμοποιείται διαφορετική μορφή για τη μετάδοση παραμέτρων - "κατά όνομα", το όνομα της παραμέτρου και η τιμή της υποδεικνύονται ρητά:

CREATE TABLE dbo.#Tab2 (id int, name varchar(30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo.#Tab2; –#2

Στο παραπάνω παράδειγμα, η πρόταση SELECT θα εκτελεστεί δύο φορές: την πρώτη φορά - εντός της διαδικασίας, τη δεύτερη φορά - από το τμήμα του καλούντος κώδικα (σημειωμένο με το σχόλιο "No. 2").

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

ΑΠΟΡΡΙΨΗ ΠΙΝΑΚΑ dbo.#Tab2;

EXEC My_Procl 0name="Ivan", 0id=2;

SELECT * FROM dbo.#Tab2; –#2

Σε αυτήν την περίπτωση, μόνο η δήλωση SELECT μέσα στη διαδικασία (με το σχόλιο "Xa 1") θα εμφανίσει τα δεδομένα. Η εκτέλεση του SELECT "#2" θα οδηγήσει σε σφάλμα, επειδή ο προσωρινός πίνακας που δημιουργήθηκε στην αποθηκευμένη διαδικασία θα διαγραφεί ήδη από τη βάση δεδομένων tempdb όταν επιστρέψει η διαδικασία.

Μπορείτε να απορρίψετε μια αποθηκευμένη διαδικασία χρησιμοποιώντας τη δήλωση DROP PROCEDURE. Η μορφή του φαίνεται παρακάτω. Με μία πρόταση, μπορείτε να διαγράψετε πολλές αποθηκευμένες διαδικασίες, παραθέτοντας τις διαχωρισμένες με κόμματα:

DROP (ΔΙΑΔΙΚΑΣΙΑ PROC I) ( διαδικασία ) [

Για παράδειγμα, ας αφαιρέσουμε τη διαδικασία άθροισης που δημιουργήθηκε προηγουμένως:

Σύνοψη DROP PROC.

Μπορείτε να κάνετε αλλαγές σε μια υπάρχουσα διαδικασία (στην πραγματικότητα, να την παρακάμψετε) χρησιμοποιώντας τη δήλωση ALTER PROCEDURE (να επιτρέπεται

συντομογραφία PROC). Με εξαίρεση τη λέξη-κλειδί ALTER, η μορφή της δήλωσης είναι σχεδόν ίδια με αυτή της ΔΙΑΔΙΚΑΣΙΑΣ ΔΗΜΙΟΥΡΓΙΑΣ. Για παράδειγμα, ας αλλάξουμε τη διαδικασία dbo. rownum ορίζοντας το να εκτελείται στο πλαίσιο ασφαλείας του κατόχου:

ALTER PROC dbo.rownum(SFirsYear int,

SLastYear int, Sresult int OUTPUT)

ΜΕ ΕΚΤΕΛΕΣΗ ΩΣ Ιδιοκτήτης - ορίστε την επιλογή

ΑΝ 0FirSYear>0LastYear ΕΠΙΣΤΡΟΦΗ 1 ΑΛΛΟ ΞΕΚΙΝΗΣΤΕ

SET 0result= (SELECT COUNT(*) FROM dbo.Bookl

ΠΟΥ ΜΕΤΑΞΥ ΤΟΥ SFirsYear ΚΑΙ SLastYear);

Σε ορισμένες περιπτώσεις, μπορεί να είναι απαραίτητο να δημιουργηθεί δυναμικά μια εντολή και να εκτελεστεί στον διακομιστή της βάσης δεδομένων. Αυτή η εργασία μπορεί επίσης να επιλυθεί χρησιμοποιώντας τον τελεστή EXEC. Το ακόλουθο παράδειγμα επιλέγει εγγραφές από τον πίνακα Bookl με βάση την προϋπόθεση ότι το χαρακτηριστικό Year είναι ίσο με την τιμή που καθορίζεται από τη μεταβλητή:

ΔΗΛΩΣΤΕ 0y int = 2000;

EXEC("SELECT * FROM dbo.Bookl WHERE = " [email προστατευμένο]) ;

Η εκτέλεση δυναμικά δημιουργούμενων εντολών δημιουργεί τις προϋποθέσεις για την υλοποίηση επιθέσεων υπολογιστή όπως "SQL injection" (Αγγλικά) SQL injection). Η ουσία της επίθεσης είναι ότι ο δράστης εισάγει τον δικό του κώδικα SQL σε ένα ερώτημα που δημιουργείται δυναμικά. Αυτό συμβαίνει συνήθως όταν λαμβάνονται ενσωματωμένες παράμετροι από αποτελέσματα εισόδου χρήστη.

Ας αλλάξουμε λίγο το προηγούμενο παράδειγμα:

DECLARE 0y varchar(100);

SET 0y="2OOO"; - αυτό λάβαμε από τον χρήστη

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

DECLARE 0y varchar(100);

SET 0y="2000; DELETE FROM dbo.Book2"; - ένεση

EXEC("SELECT * FROM dbo.Book2 WHERE="+0y);

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

EXECUTE sp_executesql

N"SELECT * FROM dbo.Bookl WHERE =0y",

Αυτό καθορίζει ρητά τον τύπο της παραμέτρου που χρησιμοποιείται στο ερώτημα και ο SQL Server θα την ελέγχει κατά την εκτέλεση. Το γράμμα "N" πριν από τα εισαγωγικά υποδηλώνει ότι πρόκειται για μια κυριολεκτική σταθερά Unicode, όπως απαιτείται από τη διαδικασία. Σε μια παράμετρο μπορεί να εκχωρηθεί όχι μόνο μια σταθερή τιμή, αλλά και η τιμή μιας άλλης μεταβλητής.

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

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

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

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

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

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

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

Αποθηκευμένες διαδικασίες σε περιβάλλον MS SQL Server

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

Τύποι αποθηκευμένων διαδικασιών

Υπάρχουν διάφοροι τύποι στον SQL Server αποθηκευμένες διαδικασίες.

Συστήματος αποθηκευμένες διαδικασίεςσχεδιασμένο για την εκτέλεση διαφόρων διοικητικών ενεργειών. Σχεδόν όλες οι ενέργειες διαχείρισης διακομιστή εκτελούνται με τη βοήθειά τους. Μπορούμε να πούμε ότι το σύστημα αποθηκευμένες διαδικασίεςείναι μια διεπαφή που παρέχει εργασία με πίνακες συστήματος, η οποία, τελικά, καταλήγει στην αλλαγή, προσθήκη, διαγραφή και ανάκτηση δεδομένων από πίνακες συστήματος τόσο των βάσεων δεδομένων χρήστη όσο και του συστήματος. Συστήματος αποθηκευμένες διαδικασίεςέχουν πρόθεμα sp_, αποθηκεύονται στη βάση δεδομένων του συστήματος και μπορούν να κληθούν στο πλαίσιο οποιασδήποτε άλλης βάσης δεδομένων.
Εθιμο αποθηκευμένες διαδικασίεςυλοποιήσει ορισμένες ενέργειες. Αποθηκευμένες διαδικασίες- ένα πλήρες αντικείμενο βάσης δεδομένων. Ως αποτέλεσμα αυτού, το καθένα αποθηκευμένη διαδικασίαβρίσκεται σε μια συγκεκριμένη βάση δεδομένων, όπου και εκτελείται.
Προσωρινός αποθηκευμένες διαδικασίεςυπάρχουν μόνο για μικρό χρονικό διάστημα, μετά το οποίο καταστρέφονται αυτόματα από τον διακομιστή. Χωρίζονται σε τοπικές και παγκόσμιες. Τοπική προσωρινή αποθηκευμένες διαδικασίεςμπορούν να κληθούν μόνο από τη σύνδεση στην οποία έχουν δημιουργηθεί. Κατά τη δημιουργία μιας τέτοιας διαδικασίας, πρέπει να της δοθεί ένα όνομα που ξεκινά με έναν μόνο χαρακτήρα #. Όπως όλα τα προσωρινά αντικείμενα, αποθηκευμένες διαδικασίεςαυτού του τύπου διαγράφονται αυτόματα όταν ο χρήστης αποσυνδέει, επανεκκινεί ή διακόπτει τον διακομιστή. Παγκόσμια προσωρινή αποθηκευμένες διαδικασίεςδιαθέσιμο για οποιεσδήποτε συνδέσεις διακομιστή που έχουν την ίδια διαδικασία. Για να το ορίσετε, αρκεί να του δώσετε ένα όνομα που να ξεκινά με τους χαρακτήρες ## . Αυτές οι διαδικασίες διαγράφονται κατά την επανεκκίνηση ή τη διακοπή του διακομιστή ή όταν η σύνδεση στο πλαίσιο της οποίας δημιουργήθηκαν είναι κλειστή.

Δημιουργία, Τροποποίηση και Διαγραφή Αποθηκευμένων Διαδικασιών

Δημιουργία αποθηκευμένη διαδικασίαπεριλαμβάνει την επίλυση των ακόλουθων εργασιών:

ορίζοντας τον τύπο του αποθηκευμένη διαδικασία: προσωρινό ή έθιμο. Επιπλέον, μπορείτε να δημιουργήσετε το δικό σας σύστημα αποθηκευμένη διαδικασία, δίνοντάς του ένα όνομα με πρόθεμα sp_ και τοποθετώντας το μέσα βάση του συστήματοςδεδομένα. Μια τέτοια διαδικασία θα είναι διαθέσιμη στο πλαίσιο οποιασδήποτε βάσης δεδομένων στον τοπικό διακομιστή.
προγραμματισμός πρόσβασης. Κατά τη δημιουργία αποθηκευμένη διαδικασίαΛάβετε υπόψη ότι θα έχει τα ίδια δικαιώματα πρόσβασης σε αντικείμενα βάσης δεδομένων με τον χρήστη που το δημιούργησε.
ορισμός αποθηκευμένες παραμέτρους διαδικασίας. Όπως οι διαδικασίες που περιλαμβάνονται στις περισσότερες γλώσσες προγραμματισμού, αποθηκευμένες διαδικασίεςμπορεί να έχει παραμέτρους εισόδου και εξόδου.
ανάπτυξη κώδικα αποθηκευμένη διαδικασία. Ο κώδικας διαδικασίας μπορεί να περιέχει μια ακολουθία οποιωνδήποτε εντολών SQL, συμπεριλαμβανομένης της κλήσης άλλων. αποθηκευμένες διαδικασίες.

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

<определение_процедуры>::= (ΔΗΜΙΟΥΡΓΙΑ | ΑΛΛΑΓΗ ) PROC procedure_name [;number] [(@parameter_name type data ) [=default] ][,...n] AS sql_statement [...n]

Εξετάστε τις παραμέτρους αυτής της εντολής.

Χρησιμοποιώντας τα προθέματα sp_ , # , ## , η διαδικασία που δημιουργήθηκε μπορεί να οριστεί ως σύστημα ή προσωρινή διαδικασία. Όπως μπορείτε να δείτε από τη σύνταξη της εντολής, δεν επιτρέπεται να προσδιορίσετε το όνομα του κατόχου στον οποίο θα ανήκει η δημιουργημένη διαδικασία, καθώς και το όνομα της βάσης δεδομένων όπου θα πρέπει να τοποθετηθεί. Έτσι, προκειμένου να φιλοξενήσει το δημιουργημένο αποθηκευμένη διαδικασίασε μια συγκεκριμένη βάση δεδομένων, πρέπει να εκτελέσετε την εντολή CREATE PROCEDURE στο πλαίσιο αυτής της βάσης δεδομένων. Κατά τον χειρισμό από το σώμα αποθηκευμένη διαδικασίαΤα συντομευμένα ονόματα μπορούν να χρησιμοποιηθούν για αντικείμενα στην ίδια βάση δεδομένων, δηλαδή χωρίς να καθοριστεί το όνομα της βάσης δεδομένων. Όταν θέλετε να αναφερθείτε σε αντικείμενα που βρίσκονται σε άλλες βάσεις δεδομένων, απαιτείται ο καθορισμός του ονόματος της βάσης δεδομένων.

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

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

Για να προσδιορίσετε τον τύπο δεδομένων που το αντίστοιχο αποθηκευμένη παράμετρος διαδικασίας, κάθε τύπος είναι κατάλληλος Δεδομένα SQL, συμπεριλαμβανομένων εκείνων που ορίζονται από τον χρήστη. Ωστόσο, ο τύπος δεδομένων CURSOR μπορεί να χρησιμοποιηθεί μόνο ως παράμετρος εξόδου αποθηκευμένη διαδικασία, δηλ. με τη λέξη-κλειδί OUTPUT .

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

Η λέξη-κλειδί VARYING χρησιμοποιείται σε συνδυασμό με την παράμετρο OUTPUT, η οποία είναι του τύπου CURSOR . Το ορίζει αυτό παράμετρος εξόδουθα είναι το σύνολο που προκύπτει.

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

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

Η παράμετρος FOR REPLICATION απαιτείται κατά την αναπαραγωγή δεδομένων και τη συμπερίληψη των δημιουργηθέντων αποθηκευμένη διαδικασίαως άρθρο σε δημοσίευση.

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

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

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

ΔΙΑΔΙΚΑΣΙΑ ΑΠΟΡΡΙΨΗΣ (όνομα_διαδικασίας) [,...n]

Εκτέλεση Αποθηκευμένης Διαδικασίας

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

[[ EXEC [ UTE] όνομα_διαδικασίας [;αριθμός] [[@parameter_name=](τιμή | @variable_name) |][,...n]

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

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

Όταν η λέξη-κλειδί DEFAULT έχει καθοριστεί για μια κλήση διαδικασίας, θα χρησιμοποιηθεί η λέξη-κλειδί ΠΡΟΕΠΙΛΟΓΗ. Προεπιλεγμένη τιμή. Φυσικά, η καθορισμένη λέξη DEFAULT επιτρέπεται μόνο για τις παραμέτρους για τις οποίες έχει οριστεί Προεπιλεγμένη τιμή.

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

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

Παράδειγμα 12.1. Διαδικασία χωρίς παραμέτρους. Αναπτύξτε μια διαδικασία για τη λήψη των ονομάτων και των τιμών των αγαθών που αγόρασε ο Ivanov.

CREATE PROC my_proc1 AS SELECT Item.Name, Item.Price*Trade.Quantity AS Cost, Customer.Last Name FROM Customer INNER JOIN (Item INNER JOIN Transaction ON Item.ItemId=Trade.ItemId) ONCustomerCHEREdeus.C. Πελάτης .Lastname='Ivanov' Παράδειγμα 12.1. Διαδικασία για τη λήψη των ονομάτων και των τιμών των αγαθών που αγόρασε ο Ivanov.