Αποθηκευμένη διαδικασίααποθηκευμένη διαδικασία) είναι ένα αντικείμενο προγράμματος βάσης δεδομένων με όνομα. Ο 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 καθορίζει ότι το σύστημα θα μεταγλωττίζει εκ νέου το κείμενο κάθε φορά που καλείται η διαδικασία. Στην κανονική περίπτωση, η διαδικασία που μεταγλωττίστηκε κατά την πρώτη εκτέλεση αποθηκεύεται στην κρυφή μνήμη, η οποία επιτρέπει αυξημένη απόδοση.

Το 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 Server Management Studio, η δημιουργημένη αποθηκευμένη διαδικασία βρίσκεται στην ενότητα προγραμματιζόμενων αντικειμένων βάσης δεδομένων (Αγγλικά)Προγραμματισμός) στην υποενότητα για αποθηκευμένες διαδικασίες (Εικ. 10.2).

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

Ρύζι. 10.2.

ΔΗΛΩΣΤΕ @с int;

EXEC summa 10.5,@c OUTPUT;

PRINT 0c; – Θα εμφανιστεί το 15

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

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

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

PRINT 0c; – Θα εμφανιστεί το 5

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

ΔΗΜΙΟΥΡΓΙΑ PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) ΩΣ

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

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

ΠΟΥ ΜΕΤΑΞΥ ΤΟΥ 0ΠΡΩΤΟΣ ΚΑΙ 0ΠΕΡΣΥΜΕΝΟΥ)

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

DECLARE 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

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

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

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

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

Το ακόλουθο παράδειγμα επεξηγεί τόσο αυτές τις δυνατότητες όσο και ζητήματα που σχετίζονται με το εύρος των προσωρινών αντικειμένων. Η ακόλουθη αποθηκευμένη διαδικασία ελέγχει την ύπαρξη προσωρινού πίνακα #TaL2. αν δεν υπάρχει αυτός ο πίνακας, τον δημιουργεί. Μετά από αυτό, οι τιμές δύο στηλών εισάγονται στον πίνακα #TaL2 και τα περιεχόμενα του πίνακα εμφανίζονται χρησιμοποιώντας τη δήλωση 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. # Tab2 –№1

Πριν καλέσουμε την αποθηκευμένη διαδικασία για πρώτη φορά, θα δημιουργήσουμε τον προσωρινό πίνακα #TaL2 που χρησιμοποιείται σε αυτόν. Δώστε προσοχή στον χειριστή 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”).

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

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

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

SELECT * FROM dbo.#Tab2; –№2

Σε αυτήν την περίπτωση, μόνο η δήλωση SELECT που βρίσκεται μέσα στη διαδικασία (με το σχόλιο "Xa 1") θα εμφανίζει δεδομένα. Η εκτέλεση του SELECT "No. 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 είναι ίσο με την τιμή που καθορίζεται από τη μεταβλητή:

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

EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

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

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

ΔΗΛΩΣΤΕ 0у varchar(100);

SET 0у="2ООО"; – το λάβαμε από τον χρήστη

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

ΔΗΛΩΣΤΕ 0у varchar(100);

SET 0у="2000; ΔΙΑΓΡΑΦΗ ΑΠΟ dbo.Book2"; - ένεση

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

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

EXECUTE sp_executesql

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

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

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

Το θέμα αυτού του κεφαλαίου είναι ένα από τα πιο ισχυρά εργαλεία που προσφέρονται στους προγραμματιστές εφαρμογών βάσης δεδομένων InterBase για την εφαρμογή της επιχειρηματικής λογικής Οι αποθηκευμένες διαδικασίες (αγγλικά, stoied proceduies) σας επιτρέπουν να εφαρμόσετε ένα σημαντικό μέρος της λογικής της εφαρμογής σε επίπεδο βάσης δεδομένων και έτσι να αυξήσετε. την απόδοση ολόκληρης της εφαρμογής, συγκεντρώνουν την επεξεργασία δεδομένων και μειώνουν την ποσότητα του κώδικα που απαιτείται για την ολοκλήρωση των εργασιών που έχουν ανατεθεί.
Εκτός από αυτά τα γνωστά πλεονεκτήματα της χρήσης αποθηκευμένων διαδικασιών, κοινά στα περισσότερα σχεσιακά DBMS, οι αποθηκευμένες διαδικασίες InterBase μπορούν να λειτουργήσουν ως σχεδόν πλήρη σύνολα δεδομένων, επιτρέποντας στα αποτελέσματα που επιστρέφουν να χρησιμοποιηθούν σε συνηθισμένα ερωτήματα SQL.
Συχνά, οι αρχάριοι προγραμματιστές φαντάζονται τις αποθηκευμένες διαδικασίες απλώς ως ένα σύνολο συγκεκριμένων ερωτημάτων SQL που κάνουν κάτι μέσα στη βάση δεδομένων, και υπάρχει η άποψη ότι η εργασία με αποθηκευμένες διαδικασίες είναι πολύ πιο δύσκολη από την εφαρμογή της ίδιας λειτουργικότητας σε μια εφαρμογή πελάτη, σε μια εφαρμογή υψηλού επιπέδου γλώσσας επιπέδου
Ποιες είναι λοιπόν οι αποθηκευμένες διαδικασίες στο InterBase;
Μια αποθηκευμένη διαδικασία (SP) είναι ένα μέρος των μεταδεδομένων της βάσης δεδομένων, το οποίο είναι μια υπορουτίνα μεταγλωττισμένη στην εσωτερική αναπαράσταση της InterBase, γραμμένη σε μια ειδική γλώσσα, ο μεταγλωττιστής της οποίας είναι ενσωματωμένος στον πυρήνα του διακομιστή InteiBase
Μια αποθηκευμένη διαδικασία μπορεί να κληθεί από εφαρμογές πελάτη, από ενεργοποιητές και από άλλες αποθηκευμένες διαδικασίες. Η αποθηκευμένη διαδικασία εκτελείται εντός της διαδικασίας διακομιστή και μπορεί να χειριστεί δεδομένα στη βάση δεδομένων, καθώς και να επιστρέψει τα αποτελέσματα της εκτέλεσής της στον πελάτη που την κάλεσε (δηλαδή έναυσμα, HP, εφαρμογή)
Η βάση των ισχυρών δυνατοτήτων που είναι εγγενείς στην HP είναι μια διαδικαστική γλώσσα προγραμματισμού, η οποία περιλαμβάνει τόσο τροποποιημένες δηλώσεις της κανονικής SQL, όπως INSERT, UPDATE και SELECT, καθώς και εργαλεία για την οργάνωση διακλαδώσεων και βρόχων (IF, WHILE), καθώς και εργαλεία χειρισμού σφαλμάτων και εξαιρετικές καταστάσεις Η γλώσσα των αποθηκευμένων διαδικασιών σάς επιτρέπει να εφαρμόζετε πολύπλοκους αλγόριθμους για την εργασία με δεδομένα και λόγω της εστίασης στην εργασία με σχεσιακά δεδομένα, η HP είναι πολύ πιο συμπαγής από παρόμοιες διαδικασίες σε παραδοσιακές γλώσσες.
Θα πρέπει να σημειωθεί ότι η ίδια γλώσσα προγραμματισμού χρησιμοποιείται για ενεργοποιητές, με εξαίρεση μια σειρά από χαρακτηριστικά και περιορισμούς. Οι διαφορές μεταξύ του υποσυνόλου της γλώσσας που χρησιμοποιείται στους κανόνες ενεργοποίησης και της γλώσσας HP αναλύονται λεπτομερώς στο κεφάλαιο «Ενεργοποιήσεις» (μέρος 1).

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

Ήρθε η ώρα να δημιουργήσετε την πρώτη σας αποθηκευμένη διαδικασία και να τη χρησιμοποιήσετε ως παράδειγμα για να μάθετε τη διαδικασία δημιουργίας αποθηκευμένων διαδικασιών. Πρώτα, όμως, θα πρέπει να πούμε λίγα λόγια για τον τρόπο εργασίας με αποθηκευμένες διαδικασίες Το γεγονός είναι ότι η HP οφείλει τη φήμη της ως ασαφούς και άβολου εργαλείου σε εξαιρετικά κακά τυπικά εργαλεία για την ανάπτυξη και τον εντοπισμό σφαλμάτων αποθηκευμένων διαδικασιών. Η τεκμηρίωση InterBase συνιστά τη δημιουργία διαδικασιών χρησιμοποιώντας αρχεία δέσμης ενεργειών SQL που περιέχουν κείμενο HP, τα οποία παρέχονται ως είσοδος στον διερμηνέα isql, και επομένως τη δημιουργία και τροποποίηση του HP If σε αυτό το σενάριο SQL, στο στάδιο της μεταγλώττισης του κειμένου της διαδικασίας στο BLR (σχετικά BLR, ανατρέξτε στο Κεφάλαιο "Δομή βάσης δεδομένων InterBase" (Μέρος 4)) εάν παρουσιαστεί σφάλμα, το isql θα εμφανίσει ένα μήνυμα σχετικά με τη γραμμή του αρχείου δέσμης ενεργειών SQL που παρουσιάστηκε αυτό το σφάλμα. Διορθώστε το λάθος και κάντε το ξανά από την αρχή. Δεν γίνεται καθόλου λόγος για αποσφαλμάτωση με τη σύγχρονη έννοια της λέξης, δηλαδή για ιχνηλάτηση εκτέλεσης, με δυνατότητα προβολής ενδιάμεσων τιμών μεταβλητών. Προφανώς, αυτή η προσέγγιση δεν συμβάλλει στην αύξηση της ελκυστικότητας των αποθηκευμένων διαδικασιών στα μάτια του προγραμματιστή
Ωστόσο, εκτός από την τυπική μινιμαλιστική προσέγγιση για την ανάπτυξη της HP<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Η σύνταξη των αποθηκευμένων διαδικασιών περιγράφεται ως εξής:

Όνομα ΔΗΜΙΟΥΡΓΙΑΣ ΔΙΑΔΙΚΑΣΙΑΣ
[ (τύπος δεδομένων param [, τύπος δεδομένων param ...]) ]
)]
ΟΠΩΣ ΚΑΙ
;
< procedure_body> = []
< block>
< vanable_declaration_list> =
ΔΗΛΩΣΗ VARIABLE var τύπο δεδομένων.

=
ΑΡΧΙΖΟΥΝ
< compound_statement>
[< compound_statement> ...]
ΤΕΛΟΣ
< compound_statement> = (δήλωση;)

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

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ SP_Add(first_arg DOUBLE PRECISION,
second_arg ΔΙΠΛΗ ΑΚΡΙΒΕΙΑ)
ΕΠΙΣΤΡΟΦΕΣ (Αποτέλεσμα ΔΙΠΛΗ ΑΚΡΙΒΕΙΑ)
ΟΠΩΣ ΚΑΙ
ΑΡΧΙΖΟΥΝ
Αποτέλεσμα=first_arg+second_arg;
ΑΝΑΣΤΕΛΛΩ;
ΤΕΛΟΣ

Όπως μπορείτε να δείτε, όλα είναι απλά: μετά την εντολή CREATE PROCEDURE, υποδεικνύεται το όνομα της διαδικασίας που δημιουργήθηκε πρόσφατα (η οποία πρέπει να είναι μοναδική στη βάση δεδομένων) - σε αυτήν την περίπτωση SP_Add, τότε οι παράμετροι εισόδου HP - first_arg και second_arg - είναι παρατίθενται σε παρένθεση, χωρίζονται με κόμμα, υποδεικνύοντας τους τύπους τους.
Η λίστα των παραμέτρων εισαγωγής είναι προαιρετικό μέρος της δήλωσης CREATE PROCEDURE - υπάρχουν περιπτώσεις που μια διαδικασία λαμβάνει όλα τα δεδομένα για την εργασία της μέσω ερωτημάτων σε πίνακες μέσα στο σώμα της διαδικασίας.

Οι αποθηκευμένες διαδικασίες χρησιμοποιούν βαθμωτούς τύπους δεδομένων InteiBase Δεν επιτρέπει τη χρήση πινάκων και τύπων - τομέων που ορίζονται από το χρήστη

Στη συνέχεια ακολουθεί η λέξη-κλειδί RETURNS, μετά την οποία οι επιστρεφόμενες παράμετροι παρατίθενται σε παρένθεση, υποδεικνύοντας τους τύπους τους - σε αυτήν την περίπτωση, μόνο ένα - Αποτέλεσμα.
Εάν η διαδικασία δεν πρέπει να επιστρέψει παραμέτρους, τότε λείπουν η λέξη RETURNS και η λίστα με τις επιστρεφόμενες παραμέτρους.
Μετά την RETURNSQ καθορίζεται η λέξη-κλειδί AS. Πριν πάει η λέξη-κλειδί AS τίτλος,και μετά από αυτό - techoδιαδικασίες.
Το σώμα μιας αποθηκευμένης διαδικασίας είναι μια λίστα περιγραφών των εσωτερικών (τοπικών) μεταβλητών της (εάν υπάρχουν, θα τις εξετάσουμε λεπτομερέστερα παρακάτω), που χωρίζονται με ένα ερωτηματικό (;) και ένα μπλοκ εντολών που περικλείονται σε αγκύλες τελεστή ΑΡΧΗ ΤΕΛΟΣ. Σε αυτήν την περίπτωση, το σώμα του HP είναι πολύ απλό - ζητάμε να προσθέσουμε δύο ορίσματα εισόδου και να αντιστοιχίσουμε το αποτέλεσμά τους στο εξόδου και, στη συνέχεια, να καλέσουμε την εντολή SUSPEND. Λίγο αργότερα θα εξηγήσουμε την ουσία της δράσης αυτής της εντολής, αλλά προς το παρόν θα σημειώσουμε μόνο ότι χρειάζεται να μεταφέρουμε τις παραμέτρους επιστροφής από όπου κλήθηκε η αποθηκευμένη διαδικασία.

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

Σημειώστε ότι μια δήλωση σε μια διαδικασία τελειώνει με ένα ερωτηματικό (;). Όπως γνωρίζετε, το ερωτηματικό είναι ένα τυπικό διαχωριστικό εντολών στην SQL - είναι ένα σήμα προς τον διερμηνέα SQL ότι το κείμενο της εντολής έχει εισαχθεί πλήρως και πρέπει να ξεκινήσει η επεξεργασία του. Δεν θα αποδειχτεί ότι εάν ο διερμηνέας SQL βρει ένα ερωτηματικό στη μέση της HP, θα υποθέσει ότι η εντολή έχει εισαχθεί πλήρως και θα προσπαθήσει να εκτελέσει μέρος της αποθηκευμένης διαδικασίας; Αυτή η υπόθεση δεν είναι αβάσιμη. Πράγματι, εάν δημιουργήσετε ένα αρχείο στο οποίο θα γράψετε το παραπάνω παράδειγμα, προσθέσετε μια εντολή σύνδεσης από τη βάση δεδομένων και προσπαθήσετε να εκτελέσετε αυτό το σενάριο SQL χρησιμοποιώντας τον διερμηνέα isql, θα επιστραφεί ένα σφάλμα λόγω του απροσδόκητου, κατά τη γνώμη του διερμηνέα, λήξης της εντολής δημιουργίας αποθηκευμένης διαδικασίας. Εάν δημιουργείτε αποθηκευμένες διαδικασίες χρησιμοποιώντας αρχεία δέσμης ενεργειών SQL, χωρίς τη χρήση εξειδικευμένων εργαλείων προγραμματιστή InterBase, τότε πριν από κάθε εντολή δημιουργίας HP (το ίδιο ισχύει για τους κανόνες ενεργοποίησης) πρέπει να αλλάξετε το διαχωριστικό εντολών σεναρίου σε άλλο χαρακτήρα εκτός από ερωτηματικό και μετά το κείμενο HP για να το επαναφέρετε. Η εντολή isql που αλλάζει το διαχωριστικό όρων SQL μοιάζει με αυτό:

ΟΡΙΣΜΟΣ ΟΡΟΥ

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

ΣΕΤ ΟΡΟΥ^;
ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ some_procedure
... . .
ΤΕΛΟΣ
^
ΟΡΙΣΜΟΣ ΟΡΟΥ ;^

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

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

ΕΠΙΛΟΓΗ *
FROM Sp_add(181.35, 23.09)

Αυτό το ερώτημα θα μας επιστρέψει μια γραμμή που περιέχει μόνο ένα πεδίο Αποτέλεσμα, το οποίο θα περιέχει το άθροισμα των αριθμών 181.35 και 23.09, δηλαδή 204.44.
Έτσι, η διαδικασία μας μπορεί να χρησιμοποιηθεί σε συνηθισμένα ερωτήματα SQL που εκτελούνται τόσο σε προγράμματα-πελάτες όσο και σε άλλα HP ή triggers. Αυτή η χρήση της διαδικασίας μας γίνεται δυνατή χρησιμοποιώντας την εντολή SUSPEND στο τέλος της αποθηκευμένης διαδικασίας.
Το γεγονός είναι ότι στο InterBase (και σε όλους τους κλώνους του) υπάρχουν δύο τύποι αποθηκευμένων διαδικασιών: επιλέξιμες διαδικασίες και εκτελέσιμες διαδικασίες. Η διαφορά στη λειτουργία αυτών των δύο τύπων HP είναι ότι οι διαδικασίες δειγματοληψίας συνήθως επιστρέφουν πολλά σύνολα παραμέτρων εξόδου, ομαδοποιημένα γραμμή προς γραμμή, τα οποία μοιάζουν με ένα σύνολο δεδομένων, ενώ οι εκτελέσιμες διαδικασίες θα μπορούσαν είτε να μην επιστρέψουν καθόλου παραμέτρους είτε να επιστρέψουν μόνο ένα σύνολο παραμέτρων εξόδου , που παρατίθεται στις Επιστροφές, όπου μία γραμμή παραμέτρων. Οι διαδικασίες επιλογής καλούνται σε ερωτήματα SELECT και οι εκτελέσιμες διαδικασίες καλούνται χρησιμοποιώντας την εντολή EXECUTE PROCEDURE.
Και οι δύο τύποι αποθηκευμένων διαδικασιών έχουν την ίδια σύνταξη δημιουργίας και τυπικά δεν διαφέρουν, επομένως οποιαδήποτε εκτελέσιμη διαδικασία μπορεί να κληθεί σε ένα ερώτημα SELECT και οποιαδήποτε διαδικασία επιλογής μπορεί να κληθεί χρησιμοποιώντας τη ΔΙΑΔΙΚΑΣΙΑ EXECUTE. Το ερώτημα είναι πώς θα συμπεριφερθεί η HP σε διαφορετικούς τύπους κλήσεων. Με άλλα λόγια, η διαφορά έγκειται στον σχεδιασμό της διαδικασίας για έναν συγκεκριμένο τύπο κλήσης. Δηλαδή, η διαδικασία επιλογής δημιουργείται ειδικά για να κληθεί από ένα ερώτημα SELECT και η εκτελέσιμη διαδικασία δημιουργείται ειδικά για να καλείται χρησιμοποιώντας τη ΔΙΑΔΙΚΑΣΙΑ ΕΚΤΕΛΕΣΗΣ. Ας δούμε ποιες είναι οι διαφορές στη σχεδίαση αυτών των δύο τύπων HP.
Για να κατανοήσετε πώς λειτουργεί η διαδικασία δειγματοληψίας, θα πρέπει να εμβαθύνετε λίγο στη θεωρία. Ας φανταστούμε ένα κανονικό ερώτημα SQL όπως το SELECT ID, NAME FROM Table_example. Ως αποτέλεσμα της εκτέλεσής του, παίρνουμε έναν πίνακα που αποτελείται από δύο στήλες (ID και NAME) και έναν ορισμένο αριθμό σειρών (ίσο με τον αριθμό των γραμμών στον πίνακα Table_example). Ο πίνακας που επιστράφηκε ως αποτέλεσμα αυτού του ερωτήματος ονομάζεται επίσης σύνολο δεδομένων SQL Ας σκεφτούμε πώς σχηματίζεται το σύνολο δεδομένων κατά την εκτέλεση αυτού του ερωτήματος Ο διακομιστής, έχοντας λάβει το ερώτημα, καθορίζει σε ποιους πίνακες αναφέρεται ποιο υποσύνολο εγγραφών από αυτούς τους πίνακες πρέπει να συμπεριληφθεί στο αποτέλεσμα του ερωτήματος . Στη συνέχεια, ο διακομιστής διαβάζει κάθε εγγραφή που ικανοποιεί τα αποτελέσματα του ερωτήματος, επιλέγει τα απαιτούμενα πεδία από αυτήν (στην περίπτωσή μας, ID και NAME) και τα στέλνει στον πελάτη. Στη συνέχεια, η διαδικασία επαναλαμβάνεται ξανά - και ούτω καθεξής για κάθε επιλεγμένη εγγραφή.
Όλη αυτή η παρέκκλιση είναι απαραίτητη ώστε ο αγαπητός αναγνώστης να καταλάβει ότι όλα τα σύνολα δεδομένων SQL δημιουργούνται σειρά προς σειρά, συμπεριλαμβανομένων των αποθηκευμένων διαδικασιών! Και η κύρια διαφορά μεταξύ των διαδικασιών ανάκτησης και των εκτελέσιμων διαδικασιών είναι ότι οι πρώτες έχουν σχεδιαστεί για να επιστρέφουν πολλές σειρές, ενώ οι δεύτερες έχουν σχεδιαστεί για να επιστρέφουν μόνο μία. Γι' αυτό χρησιμοποιούνται διαφορετικά: η διαδικασία Select καλείται χρησιμοποιώντας την εντολή SELECT, η οποία "απαιτεί" η διαδικασία να εγκαταλείψει όλες τις εγγραφές που μπορεί να επιστρέψει. Η εκτελέσιμη διαδικασία καλείται χρησιμοποιώντας EXECUTE PROCEDURE, η οποία «βγάζει» μόνο μία γραμμή από την HP και αγνοεί τις υπόλοιπες (ακόμα και αν υπάρχουν!).
Ας δούμε ένα παράδειγμα διαδικασίας δειγματοληψίας για να γίνει πιο σαφές. Για > συγχώρεση, ας δημιουργήσουμε μια αποθηκευμένη διαδικασία που λειτουργεί ακριβώς όπως ένα ερώτημα SELECT ID, NAME FROM Table_Example, δηλαδή απλώς επιλέγει τα πεδία ID και NAME από ολόκληρο τον πίνακα. Εδώ είναι αυτό το παράδειγμα:

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ Simple_Select_SP
ΕΠΙΣΤΡΟΦΕΣ (
procID ΑΚΕΡΑΙΟΣ,
procNAME VARCHAR(80))
ΟΠΩΣ ΚΑΙ
ΑΡΧΙΖΟΥΝ
ΓΙΑ
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
ΚΑΝΩ
ΑΡΧΙΖΟΥΝ
ΑΝΑΣΤΕΛΛΩ;
ΤΕΛΟΣ
ΤΕΛΟΣ

Ας δούμε τα βήματα αυτής της διαδικασίας, που ονομάζεται Simple_Select_SP. Όπως μπορείτε να δείτε, δεν έχει παραμέτρους εισόδου και έχει δύο παραμέτρους εξόδου - ID και NAME. Το πιο ενδιαφέρον πράγμα, φυσικά, βρίσκεται στο σώμα της διαδικασίας. Η κατασκευή FOR SELECT χρησιμοποιείται εδώ:

ΓΙΑ
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
ΚΑΝΩ
ΑΡΧΙΖΟΥΝ

/*κάντε κάτι με τις μεταβλητές procID και procName*/

ΤΕΛΟΣ

Αυτό το κομμάτι κώδικα σημαίνει τα εξής: για κάθε σειρά που επιλέγεται από τον πίνακα Table_example, βάλτε τις επιλεγμένες τιμές στις μεταβλητές procID και procName και μετά κάντε κάτι με αυτές τις μεταβλητές.
Μπορείτε να κάνετε μια έκπληξη και να ρωτήσετε, "Μεταβλητές; Ποιες άλλες μεταβλητές; 9" Είναι κάπως έκπληξη αυτού του κεφαλαίου ότι μπορούμε να χρησιμοποιήσουμε μεταβλητές σε αποθηκευμένες διαδικασίες. Στη γλώσσα HP, μπορείτε να δηλώσετε και τις δύο δικές σας τοπικές μεταβλητές μέσα σε μια διαδικασία και να χρησιμοποιήσετε παραμέτρους εισόδου και εξόδου ως μεταβλητές.
Για να δηλώσετε μια τοπική μεταβλητή σε μια αποθηκευμένη διαδικασία, πρέπει να τοποθετήσετε την περιγραφή της μετά τη λέξη-κλειδί AS και πριν από την πρώτη λέξη BEGIN Η περιγραφή μιας τοπικής μεταβλητής μοιάζει με αυτό:

ΔΗΛΩΣΤΕ ΜΕΤΑΒΛΗΤΟ ;

Για παράδειγμα, για να δηλώσετε μια ακέραια τοπική μεταβλητή Mylnt, θα εισαγάγετε την ακόλουθη δήλωση μεταξύ AS και BEGIN

ΔΗΛΩΣΗ ΜΕΤΑΒΛΗΤΗΣ Mylnt ΑΚΕΡΑΙΟΣ;

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

procName="Κάποιο όνομα";

Ας επιστρέψουμε όμως στο σώμα της διαδικασίας μας. Ο όρος FOR SELECT επιστρέφει δεδομένα όχι με τη μορφή πίνακα - ένα σύνολο δεδομένων, αλλά μία σειρά τη φορά. Κάθε πεδίο που επιστρέφεται πρέπει να τοποθετηθεί στη δική του μεταβλητή: ID => procID, NAME => procName. Στο τμήμα DO, αυτές οι μεταβλητές αποστέλλονται στον πελάτη που κάλεσε τη διαδικασία χρησιμοποιώντας την εντολή SUSPEND
Έτσι, η εντολή FOR SELECT...DO κάνει βρόχο μέσα από τις εγγραφές που έχουν επιλεγεί στο τμήμα SELECT της εντολής. Στο σώμα του βρόχου που σχηματίζεται από το τμήμα DO, η επόμενη παραγόμενη εγγραφή μεταφέρεται στον πελάτη χρησιμοποιώντας την εντολή SUSPEND.
Έτσι, η διαδικασία επιλογής έχει σχεδιαστεί για να επιστρέφει μία ή περισσότερες σειρές, για τις οποίες οργανώνεται ένας βρόχος μέσα στο σώμα της HP που συμπληρώνει τις παραμέτρους της μεταβλητής που προκύπτουν. Και στο τέλος του σώματος αυτού του βρόχου υπάρχει πάντα μια εντολή SUSPEND, η οποία θα επιστρέψει την επόμενη σειρά δεδομένων στον πελάτη.

Βρόχοι και δηλώσεις διακλάδωσης

Εκτός από την εντολή FOR SELECT...DO, η οποία οργανώνει έναν βρόχο μέσω των εγγραφών μιας επιλογής, υπάρχει ένας άλλος τύπος βρόχου - WHILE...DO, που σας επιτρέπει να οργανώσετε έναν βρόχο με βάση τον έλεγχο τυχόν συνθηκών. Ακολουθεί ένα παράδειγμα HP που χρησιμοποιεί τον βρόχο WHILE..DO. Αυτή η διαδικασία επιστρέφει τα τετράγωνα των ακεραίων από το 0 στο 99:

ΔΗΜΙΟΥΡΓΗΣΤΕ PROCEDJRE QUAD
ΕΠΙΣΤΡΟΦΕΣ (ΑΚΕΡΑΙΟΣ ΤΕΤΑΡΧΕΙΟ)
ΟΠΩΣ ΚΑΙ
ΔΗΛΩΣΤΕ ΜΕΤΑΒΛΗΤΗ I ΑΚΕΡΑΙΟΣ;
ΑΡΧΙΖΟΥΝ
I = 1;
Καθώς εγώ<100) DO
ΑΡΧΙΖΟΥΝ
QUADRAT= I*I;
I=I+1;
ΑΝΑΣΤΕΛΛΩ;
ΤΕΛΟΣ
ΤΕΛΟΣ

Ως αποτέλεσμα της εκτέλεσης του ερωτήματος SELECT FROM QUAD, θα λάβουμε έναν πίνακα που περιέχει μια στήλη QUADRAT, η οποία θα περιέχει τα τετράγωνα των ακεραίων από το 1 έως το 99
Εκτός από την επανάληψη των αποτελεσμάτων ενός δείγματος SQL και ενός κλασικού βρόχου, η αποθηκευμένη γλώσσα διαδικασίας χρησιμοποιεί τον τελεστή IF...THEN..ELSE, ο οποίος σας επιτρέπει να οργανώσετε τη διακλάδωση ανάλογα με την εκτέλεση οποιωνδήποτε συνθηκών στους περισσότερους τελεστές διακλάδωσης σε γλώσσες προγραμματισμού υψηλού επιπέδου, όπως οι Pascal και C.
Ας δούμε ένα πιο περίπλοκο παράδειγμα αποθηκευμένης διαδικασίας που κάνει τα εξής.

1. Υπολογίζει τη μέση τιμή στον πίνακα Table_example (βλ. κεφάλαιο "Πίνακες Πρωτεύοντα κλειδιά και γεννήτριες")
2. Στη συνέχεια, για κάθε καταχώρηση στον πίνακα, κάνει τον ακόλουθο έλεγχο: εάν η υπάρχουσα τιμή (PRICE) είναι μεγαλύτερη από τη μέση τιμή, τότε ορίζει μια τιμή ίση με τη μέση τιμή, συν ένα καθορισμένο σταθερό ποσοστό
3. Εάν η υπάρχουσα τιμή είναι μικρότερη ή ίση με τη μέση τιμή, τότε ορίζεται μια τιμή ίση με την προηγούμενη τιμή, συν τη μισή διαφορά μεταξύ της προηγούμενης και της μέσης τιμής.
4. Επιστρέφει όλες τις τροποποιημένες σειρές στον πίνακα.

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

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ Αύξηση Τιμών (
Ποσοστό 2lnαύξηση ΔΙΠΛΗΣ ΑΚΡΙΒΕΙΑΣ)
ΕΠΙΣΤΡΟΦΕΣ (ID INTEGER, ΟΝΟΜΑ VARCHAR(SO), new_price DOUBLE
ΑΚΡΙΒΕΙΑ ΩΣ

Η διαδικασία θα ονομάζεται IncreasePrices, έχει μία παράμετρο εισόδου Peiceni21nciease τύπου DOUBLE PRECISION και 3 παραμέτρους εξόδου - ID, NAME και new_pnce. Σημειώστε ότι οι δύο πρώτες παράμετροι εξόδου έχουν τα ίδια ονόματα με τα πεδία στον πίνακα Table_example με τον οποίο πρόκειται να εργαστούμε Αυτό επιτρέπεται από τους κανόνες της αποθηκευμένης γλώσσας διαδικασίας.
Τώρα πρέπει να δηλώσουμε μια τοπική μεταβλητή που θα χρησιμοποιηθεί για την αποθήκευση της μέσης τιμής Η δήλωση θα μοιάζει με αυτό:

ΔΗΛΩΣΗ ΜΕΤΑΒΛΗΤΟΣ μέση_τιμή ΔΙΠΛΗ ΑΚΡΙΒΕΙΑ;

Τώρα ας προχωρήσουμε στο σώμα της αποθηκευμένης διαδικασίας Ανοίξτε το σώμα του HP με τη λέξη-κλειδί ΑΡΧΗ.
Πρώτα πρέπει να εκτελέσουμε το πρώτο βήμα του αλγορίθμου μας - να υπολογίσουμε τη μέση τιμή. Για να γίνει αυτό, θα χρησιμοποιήσουμε τον ακόλουθο τύπο ερωτήματος:

ΕΠΙΛΟΓΗ AVG (Τιμή_l)
ΑΠΟ Πίνακας_Παράδειγμα
INTO:avg_price,-

Αυτό το ερώτημα χρησιμοποιεί τη συνάρτηση συγκεντρωτικών AVG, η οποία επιστρέφει τον μέσο όρο του πεδίου PRICE_1 μεταξύ των επιλεγμένων σειρών ερωτήματος—στην περίπτωσή μας, τον μέσο όρο του PRICE_1 σε ολόκληρο τον πίνακα Table_example. Η τιμή που επιστρέφεται από το αίτημα τοποθετείται στη μεταβλητή avg_price. Σημειώστε ότι πριν από τη μεταβλητή avg_pnce υπάρχει άνω και κάτω τελεία για να τη διακρίνει από τα πεδία που χρησιμοποιούνται στο αίτημα.
Η ιδιαιτερότητα αυτού του ερωτήματος είναι ότι πάντα επιστρέφει ακριβώς μία μόνο εγγραφή. Τέτοια ερωτήματα ονομάζονται ερωτήματα singleton Και μόνο τέτοιες επιλογές μπορούν να χρησιμοποιηθούν σε αποθηκευμένες διαδικασίες. Εάν ένα ερώτημα επιστρέφει περισσότερες από μία σειρές, τότε πρέπει να μορφοποιηθεί ως κατασκευή FOR SELECT...DO, η οποία οργανώνει έναν βρόχο για την επεξεργασία κάθε επιστρεφόμενης σειράς
Έτσι, πήραμε τη μέση τιμή. Τώρα πρέπει να διαβάσετε ολόκληρο τον πίνακα, να συγκρίνετε την τιμή της τιμής σε κάθε καταχώριση με τη μέση τιμή και να προβείτε στις κατάλληλες ενέργειες
Από την αρχή, οργανώνουμε την αναζήτηση για κάθε εγγραφή από τον πίνακα Table_example

ΓΙΑ
SELECT ID, NAME, PRICE_1
ΑΠΟ Πίνακας_Παράδειγμα
INTO:ID, :NAME, :new_price
ΚΑΝΩ
ΑΡΧΙΖΟΥΝ
/*_εδώ περιγράφουμε κάθε καταχώρηση*/
ΤΕΛΟΣ

Όταν εκτελεστεί αυτή η κατασκευή, τα δεδομένα θα εξαχθούν από τον πίνακα Table_example σειρά προς σειρά και οι τιμές των πεδίων σε κάθε σειρά θα αντιστοιχιστούν στις μεταβλητές ID, NAME και new_pnce. Φυσικά, θυμάστε ότι αυτές οι μεταβλητές δηλώνονται ως παράμετροι εξόδου, αλλά δεν χρειάζεται να ανησυχείτε ότι τα επιλεγμένα δεδομένα θα επιστραφούν ως αποτελέσματα: το γεγονός ότι οι παράμετροι εξόδου έχουν εκχωρηθεί κάτι δεν σημαίνει ότι ο πελάτης που καλεί την HP θα λάβει αμέσως αυτές τις τιμές! Οι παράμετροι περνούν μόνο όταν εκτελείται η εντολή SUSPEND και πριν από αυτό μπορούμε να χρησιμοποιήσουμε τις παραμέτρους εξόδου ως συνηθισμένες μεταβλητές - στο παράδειγμά μας κάνουμε ακριβώς αυτό με την παράμετρο new_price.
Έτσι, μέσα στο σώμα του βρόχου BEGIN... END μπορούμε να επεξεργαστούμε τις τιμές κάθε σειράς. Όπως θυμάστε, πρέπει να καταλάβουμε πώς συγκρίνεται η υπάρχουσα τιμή με τον μέσο όρο και να λάβουμε τα κατάλληλα μέτρα. Υλοποιούμε αυτήν τη διαδικασία σύγκρισης χρησιμοποιώντας τη δήλωση IF:

ΑΝ (new_price > avg_price) ΤΟΤΕ /*αν η υπάρχουσα τιμή είναι μεγαλύτερη από τη μέση τιμή*/
ΑΡΧΙΖΟΥΝ
/*τότε θα ορίσουμε μια νέα τιμή ίση με τη μέση τιμή, συν ένα σταθερό ποσοστό */
new_price = (μέση_τιμή + μέση_τιμή*(Percent2Increase/100));
ΕΝΗΜΕΡΩΣΗ Πίνακα_παράδειγμα
SET PRICE_1 = :new_price
WHERE ID = :ID;
ΤΕΛΟΣ
ΑΛΛΟΥ
ΑΡΧΙΖΟΥΝ
/* Εάν η υπάρχουσα τιμή είναι μικρότερη ή ίση με τη μέση τιμή, τότε ορίστε μια τιμή ίση με την προηγούμενη τιμή, συν τη μισή διαφορά μεταξύ της προηγούμενης και της μέσης τιμής */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
ΕΝΗΜΕΡΩΣΗ Πίνακα_παράδειγμα
SET PRICE_1 = :new_price
WHERE ID = .ID;
ΤΕΛΟΣ

Όπως μπορείτε να δείτε, το αποτέλεσμα είναι μια αρκετά μεγάλη κατασκευή IF, η οποία θα ήταν δύσκολο να κατανοηθεί αν δεν υπήρχαν τα σχόλια που περικλείονται στα σύμβολα /**/.
Για να αλλάξουμε την τιμή σύμφωνα με την υπολογισμένη διαφορά, θα χρησιμοποιήσουμε τη δήλωση UPDATE, η οποία μας επιτρέπει να τροποποιήσουμε τις υπάρχουσες εγγραφές - μία ή περισσότερες. Για να υποδείξουμε με σαφήνεια σε ποια εγγραφή πρέπει να αλλάξει η τιμή, χρησιμοποιούμε το πεδίο πρωτεύοντος κλειδιού στη συνθήκη WHERE, συγκρίνοντάς το με την τιμή της μεταβλητής που αποθηκεύει την τιμή ID για την τρέχουσα εγγραφή: ID=:ID. Σημειώστε ότι πριν από τη μεταβλητή ID υπάρχει άνω και κάτω τελεία.
Μετά την εκτέλεση της κατασκευής IF...THEN...ELSE, οι μεταβλητές ID, NAME και new_price περιέχουν δεδομένα που πρέπει να επιστρέψουμε στον πελάτη που κάλεσε τη διαδικασία. Για να το κάνετε αυτό, μετά το IF, θα πρέπει να εισαγάγετε την εντολή SUSPEND, η οποία θα στείλει τα δεδομένα από όπου κλήθηκε η HP Κατά τη μεταφορά, η διαδικασία θα ανασταλεί και όταν απαιτείται νέα εγγραφή από την HP θα συνεχιστεί ξανά - και αυτό θα συνεχιστεί έως ότου το FOR SELECT...DO δεν θα επαναλάβει όλες τις εγγραφές στο ερώτημά του.
Θα πρέπει να σημειωθεί ότι εκτός από την εντολή SUSPEND, η οποία αναστέλλει μόνο την αποθηκευμένη διαδικασία, υπάρχει μια εντολή EXIT που τερματίζει την αποθηκευμένη διαδικασία αφού περάσει τη συμβολοσειρά. Ωστόσο, η εντολή EXIT χρησιμοποιείται αρκετά σπάνια, καθώς απαιτείται κυρίως για τη διακοπή του βρόχου όταν επιτευχθεί μια συνθήκη
Ωστόσο, στην περίπτωση που η διαδικασία κλήθηκε με δήλωση SELECT και ολοκληρώθηκε με EXIT, η τελευταία σειρά που ανακτήθηκε δεν θα επιστραφεί. Δηλαδή, εάν πρέπει να διακόψετε τη διαδικασία και να λάβετε αυτή τη συμβολοσειρά, πρέπει να χρησιμοποιήσετε την ακολουθία

ΑΝΑΣΤΕΛΛΩ;
ΕΞΟΔΟΣ;

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

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ Αύξηση Τιμών (
Ποσοστό 2 Αύξηση ΔΙΠΛΗΣ ΑΚΡΙΒΕΙΑΣ)
ΕΠΙΣΤΡΟΦΕΣ (ID INTEGER, ΟΝΟΜΑ VARCHAR(80),
new_price ΔΙΠΛΗ ΑΚΡΙΒΕΙΑ) ΩΣ
ΔΗΛΩΣΗ ΜΕΤΑΒΛΗΤΟΣ μέση_τιμή ΔΙΠΛΗ ΑΚΡΙΒΕΙΑ;
ΑΡΧΙΖΟΥΝ
ΕΠΙΛΟΓΗ AVG (Τιμή_l)
ΑΠΟ Πίνακας_Παράδειγμα
INTO:avg_price;
ΓΙΑ
SELECT ID, NAME, PRICE_1
ΑΠΟ Πίνακας_Παράδειγμα
INTO:ID, :NAME, :new_price
ΚΑΝΩ
ΑΡΧΙΖΟΥΝ
/*επεξεργαστείτε κάθε εγγραφή εδώ*/
ΑΝ (new_pnce > avg_price) ΤΟΤΕ /*αν η υπάρχουσα τιμή είναι μεγαλύτερη από τη μέση τιμή*/
ΑΡΧΙΖΟΥΝ
/*ορίστε μια νέα τιμή ίση με τη μέση τιμή συν ένα σταθερό ποσοστό */
new_price = (μέση_τιμή + μέση_τιμή*(Percent2lncrease/100));
ΕΝΗΜΕΡΩΣΗ Πίνακα_παράδειγμα
SET PRICE_1 = :new_price
WHERE ID = :ID;
ΤΕΛΟΣ
ΑΛΛΟΥ
ΑΡΧΙΖΟΥΝ
/* Εάν η υπάρχουσα τιμή είναι μικρότερη ή ίση με τη μέση τιμή, τότε ορίζει μια τιμή ίση με την προηγούμενη τιμή συν το μισό της διαφοράς μεταξύ της προηγούμενης και της μέσης τιμής */
new_price = (new_price + ((μέση_τιμή - νέα_τιμή)/2));
ΕΝΗΜΕΡΩΣΗ Πίνακα_παράδειγμα
SET PRICE_1 = :new_price
WHERE ID = :ID;
ΤΕΛΟΣ
ΑΝΑΣΤΕΛΛΩ;
ΤΕΛΟΣ
ΤΕΛΟΣ

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

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

Οι αποθηκευμένες διαδικασίες InterBase μπορεί να είναι αναδρομικές. Αυτό σημαίνει ότι μια αποθηκευμένη διαδικασία μπορεί να καλέσει τον εαυτό της. Επιτρέπονται έως και 1000 επίπεδα ένθεσης αποθηκευμένων διαδικασιών, αλλά πρέπει να θυμόμαστε ότι οι δωρεάν πόροι στον διακομιστή ενδέχεται να εξαντληθούν πριν επιτευχθεί η μέγιστη ένθεση της HP.
Μια κοινή χρήση των αποθηκευμένων διαδικασιών είναι η επεξεργασία δομών δέντρων που είναι αποθηκευμένες σε μια βάση δεδομένων. Τα δέντρα χρησιμοποιούνται συχνά στη σύνθεση προϊόντων, στην αποθήκη, στο προσωπικό και σε άλλες κοινές εφαρμογές.
Ας δούμε ένα παράδειγμα αποθηκευμένης διαδικασίας που επιλέγει όλα τα προϊόντα ενός συγκεκριμένου τύπου, ξεκινώντας από ένα συγκεκριμένο επίπεδο ένθεσης.
Ας έχουμε την ακόλουθη διατύπωση του προβλήματος: έχουμε έναν κατάλογο αγαθών με μια ιεραρχική δομή αυτού του τύπου:

Εμπορεύματα
- Συσκευές
- Ψυγεία
- Τριθάλαμος
- Διπλός θάλαμος
- Μονοθάλαμος
- Πλυντήρια
- Κάθετη
- Μπροστά
- Κλασικό
- Στενό
- Τεχνολογία υπολογιστών
....

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

Πλυντήρια - Κάθετα
Πλυντήρια ρούχων - Μπροστά Classic
Πλυντήρια ρούχων - Μπροστά Στενό

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

ΔΗΜΙΟΥΡΓΙΑ ΕΠΙΤΡΑΠΕΖΙΩΝ GoodsTree
(ID_GOOD ΑΚΕΡΑΙΟΣ ΟΧΙ NULL,
ID_PARENT_GOOD ΑΚΕΡΑΙΟΣ,
GOOD_NAME VARCHAR(80),
περιορισμός pkGooci πρωτεύον κλειδί (ID_GOOD));

Δημιουργούμε έναν πίνακα GoodsTree, στον οποίο υπάρχουν μόνο 3 πεδία: ID_GOOD - το έξυπνο αναγνωριστικό της κατηγορίας, ID_PARENT_GOOD - το αναγνωριστικό της μητρικής εταιρείας για αυτήν την κατηγορία και GOOD_NAME - το όνομα της κατηγορίας. Για να διασφαλίσουμε την ακεραιότητα των δεδομένων σε αυτόν τον πίνακα, θα επιβάλουμε έναν περιορισμό ξένου κλειδιού σε αυτόν τον πίνακα:

ALTER TABLE GoodsTree
ΠΡΟΣΘΗΚΗ ΠΕΡΙΟΡΙΣΜΟΥ FK_goodstree
ΞΕΝΟ ΚΛΕΙΔΙ (ID_PARENT_GOOD)
ΑΝΑΦΟΡΕΣ GOODSTPEE (ID__GOOD)

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

ID_GOOD 1 2 3 4 5 6 7 8 9 10 11 12

ID_PARENT_GOOD 0 1 1 2 2 4 4 4 5 5 10 10

ΚΑΛΟ ΟΝΟΜΑ ΕΜΠΟΡΕΥΜΑΤΑ Συσκευές Υπολογιστές και εξαρτήματα Ψυγεία Πλυντήρια Τριθάλαμος Διπλός θάλαμος Ενιαίος θάλαμος Κατακόρυφος Μετωπικός Στενός Κλασσικός

Τώρα που έχουμε ένα μέρος για να αποθηκεύσουμε τα δεδομένα, μπορούμε να αρχίσουμε να δημιουργούμε μια αποθηκευμένη διαδικασία που θα παράγει όλες τις "τελικές" κατηγορίες προϊόντων σε μια "διευρυμένη" μορφή - για παράδειγμα, για την κατηγορία "Three-Chamber", την πλήρη κατηγορία Το όνομα θα ήταν "Οικιακές Συσκευές Ψυγεία" Τριών θαλάμων".
Οι αποθηκευμένες διαδικασίες που επεξεργάζονται δομές δέντρων έχουν τη δική τους ορολογία. Κάθε στοιχείο του δέντρου ονομάζεται κόμβος. και η σχέση μεταξύ κόμβων που αναφέρονται μεταξύ τους ονομάζεται σχέση γονέα-παιδιού. Οι κόμβοι που βρίσκονται στην άκρη του δέντρου και δεν έχουν παιδιά ονομάζονται "φύλλα".
Για αυτήν την αποθηκευμένη διαδικασία, η παράμετρος εισαγωγής θα είναι το αναγνωριστικό κατηγορίας, από το οποίο θα πρέπει να ξεκινήσουμε την ανάλυση. Η αποθηκευμένη διαδικασία θα μοιάζει με αυτό:

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ GETFULLNAME (ID_GOOD2ΕΜΦΑΝΙΣΗ ΑΚΕΡΑΙΟΥ)
RETURNS (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD ΑΚΕΡΑΙΟΣ)
ΟΠΩΣ ΚΑΙ
ΔΗΛΩΣΗ VARIABLE CURR_CHILD_NAME VARCHAR(80);
ΑΡΧΙΖΟΥΝ
/*0οργανώστε τον εξωτερικό βρόχο FOR SELECT σύμφωνα με τους άμεσους απογόνους του προϊόντος με ID_GOOD=ID_GOOD2SHOW */
ΓΙΑ ΕΠΙΛΟΓΗ gtl.id_good, gtl.good_name
ΑΠΟ GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
ΚΑΝΩ
ΑΡΧΙΖΟΥΝ
/"Ελέγξτε χρησιμοποιώντας τη συνάρτηση EXISTS, η οποία επιστρέφει TRUE εάν το ερώτημα σε αγκύλες επιστρέφει τουλάχιστον μία σειρά. Εάν ο κόμβος που βρέθηκε με ID_PARENT_GOOD = ID_CHILD_GOOD δεν έχει παιδιά, τότε είναι ένα "φύλλο" του δέντρου και περιλαμβάνεται στα αποτελέσματα */
ΑΝ (ΔΕΝ ΥΠΑΡΧΕΙ(
ΕΠΙΛΟΓΗ * ΑΠΟ GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
ΕΠΕΙΤΑ
ΑΡΧΙΖΟΥΝ
/* Περάστε το "φύλλο" του δέντρου στα αποτελέσματα */
ΑΝΑΣΤΕΛΛΩ;
ΤΕΛΟΣ
ΑΛΛΟΥ
/* Για κόμβους που έχουν παιδιά*/
ΑΡΧΙΖΟΥΝ
/*αποθηκεύστε το όνομα του γονικού κόμβου σε μια προσωρινή μεταβλητή */
CURR_CHILD_NAME=full_goods_name;
/* εκτελέστε αυτήν τη διαδικασία αναδρομικά */
ΓΙΑ
SELECT ID_CHILD_GOOD, full_goods_name
FROM GETFULLNAME (:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
ΞΕΚΙΝΗΣΤΕ
/*προσθέστε το όνομα του γονικού κόμβου στο όνομα θυγατρικού που βρέθηκε χρησιμοποιώντας τη λειτουργία συνένωσης συμβολοσειρών || */
full_goods_name=CURR_CHILD_NAME| "" | f ull_goods_name,-
ΑΝΑΣΤΕΛΛΩ; /* επιστρέψτε το πλήρες όνομα του προϊόντος*/
ΤΕΛΟΣ
ΤΕΛΟΣ
ΤΕΛΟΣ
ΤΕΛΟΣ

Εάν εκτελέσουμε αυτή τη διαδικασία με την παράμετρο εισόδου ID_GOOD2SHOW= 1, θα λάβουμε τα εξής:

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

συμπέρασμα

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

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

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

Σημείωση! Για αρχάριους προγραμματιστές, προτείνω τα ακόλουθα χρήσιμα υλικά για την T-SQL:

• Για μια πιο λεπτομερή μελέτη της γλώσσας T-SQL, προτείνω επίσης να διαβάσετε το βιβλίο - The T-SQL Programmer's Path. Εκμάθηση για τη γλώσσα Transact-SQL.
• Επαγγελματικά διαδικτυακά μαθήματα για την T-SQL

Τι είναι οι αποθηκευμένες διαδικασίες στην T-SQL;

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

Για να εκτελέσετε μια αποθηκευμένη διαδικασία στον SQL Server, πρέπει να γράψετε την εντολή EXECUTE πριν από το όνομά της. Η κλήση μιας αποθηκευμένης διαδικασίας σε μια πρόταση SELECT, για παράδειγμα, ως συνάρτηση δεν θα λειτουργεί πλέον, π.χ. οι διαδικασίες ξεκινούν χωριστά.

Σε αποθηκευμένες διαδικασίες, σε αντίθεση με τις συναρτήσεις, είναι ήδη δυνατή η εκτέλεση λειτουργιών τροποποίησης δεδομένων όπως: UNSERT, UPDATE, DELETE. Μπορείτε επίσης να χρησιμοποιήσετε εντολές SQL σχεδόν οποιουδήποτε τύπου σε διαδικασίες, για παράδειγμα, CREATE TABLE για τη δημιουργία πινάκων ή EXECUTE, π.χ. καλώντας άλλες διαδικασίες. Η εξαίρεση είναι διάφοροι τύποι εντολών, όπως: δημιουργία ή αλλαγή συναρτήσεων, προβολών, ενεργοποιήσεων, δημιουργία σχημάτων και πολλές άλλες παρόμοιες οδηγίες, για παράδειγμα, δεν μπορείτε επίσης να αλλάξετε το περιβάλλον σύνδεσης της βάσης δεδομένων (ΧΡΗΣΗ) σε μια αποθηκευμένη διαδικασία.

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

Οι αποθηκευμένες διαδικασίες είναι πολύ χρήσιμες, μας βοηθούν να αυτοματοποιήσουμε ή να απλοποιήσουμε πολλές λειτουργίες, για παράδειγμα, χρειάζεται συνεχώς να δημιουργείτε διάφορες σύνθετες αναλυτικές αναφορές χρησιμοποιώντας συγκεντρωτικούς πίνακες, π.χ. Χειριστής PIVOT. Για να διευκολυνθεί η διατύπωση ερωτημάτων με αυτόν τον τελεστή ( όπως γνωρίζετε, η σύνταξη του PIVOT είναι αρκετά περίπλοκη), Μπορείτε να γράψετε μια διαδικασία που θα δημιουργεί δυναμικά συνοπτικές αναφορές για εσάς, για παράδειγμα, το υλικό "Dynamic PIVOT in T-SQL" παρέχει ένα παράδειγμα εφαρμογής αυτής της δυνατότητας με τη μορφή αποθηκευμένης διαδικασίας.

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

Πηγή δεδομένων για παραδείγματα

Όλα τα παρακάτω παραδείγματα θα εκτελούνται στον Microsoft SQL Server 2016 Express. Για να δείξουμε πώς λειτουργούν οι αποθηκευμένες διαδικασίες με πραγματικά δεδομένα, χρειαζόμαστε αυτά τα δεδομένα, ας τα δημιουργήσουμε. Για παράδειγμα, ας δημιουργήσουμε έναν πίνακα δοκιμών και ας προσθέσουμε μερικές εγγραφές σε αυτόν, ας πούμε ότι θα είναι ένας πίνακας που θα περιέχει μια λίστα προϊόντων με τις τιμές τους.

Οδηγίες για τη δημιουργία πίνακα CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Οδηγίες για την προσθήκη δεδομένων INSERT INTO TestTable(CategoryId, ProductName, ΤΙΜΕΣ (1 , "Ποντίκι", 100), (1, "Πληκτρολόγιο", 200), (2, "Τηλέφωνο", 400) GO --Επιλέξτε ερώτημα ΕΠΙΛΟΓΗ * ΑΠΟ Τεστ Πίνακας

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

Δημιουργία μιας αποθηκευμένης διαδικασίας στην T-SQL - η δήλωση CREATE PROCEDURE

Οι αποθηκευμένες διαδικασίες δημιουργούνται χρησιμοποιώντας μια δήλωση ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ, μετά από αυτήν την οδηγία πρέπει να γράψετε το όνομα της διαδικασίας σας και, αν χρειάζεται, να ορίσετε τις παραμέτρους εισόδου και εξόδου σε παρένθεση. Μετά από αυτό, γράφετε τη λέξη-κλειδί AS και ανοίγετε το μπλοκ οδηγιών με τη λέξη-κλειδί BEGIN, κλείνετε αυτό το μπλοκ με τη λέξη END. Μέσα σε αυτό το μπλοκ, γράφετε όλες τις οδηγίες που υλοποιούν τον αλγόριθμό σας ή κάποιο είδος διαδοχικού υπολογισμού, με άλλα λόγια, προγραμματίζετε σε T-SQL.

Για παράδειγμα, ας γράψουμε μια αποθηκευμένη διαδικασία που θα προσθέσει μια νέα εγγραφή, π.χ. νέο προϊόν στο τραπέζι δοκιμών μας. Για να γίνει αυτό, θα ορίσουμε τρεις παραμέτρους εισόδου: @CategoryId – αναγνωριστικό κατηγορίας προϊόντος, @ProductName – όνομα προϊόντος και @Τιμή – τιμή προϊόντος αυτή η παράμετρος θα είναι προαιρετική, π.χ. δεν θα χρειαστεί να περάσει στη διαδικασία ( για παράδειγμα, δεν γνωρίζουμε ακόμη την τιμή), για το σκοπό αυτό θα ορίσουμε μια προεπιλεγμένη τιμή στον ορισμό του. Αυτές οι παράμετροι βρίσκονται στο σώμα της διαδικασίας, δηλ. στο μπλοκ BEGIN...END μπορεί να χρησιμοποιηθεί, όπως και οι κανονικές μεταβλητές ( Όπως γνωρίζετε, οι μεταβλητές συμβολίζονται με το σύμβολο @). Εάν πρέπει να καθορίσετε παραμέτρους εξόδου, τότε μετά το όνομα της παραμέτρου υποδείξτε τη λέξη-κλειδί OUTPUT ( ή OUT για συντομία).

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

Εδώ είναι ο κωδικός αυτής της διαδικασίας ( Το σχολίασα και εγώ).

Δημιουργία διαδικασίας ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ TestProcedure (--Εισαγωγή παραμέτρων @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) ΩΣ ΑΡΧΗ --Οδηγίες που εφαρμόζουν τον αλγόριθμό σας --Επεξεργασία εισερχόμενων παραμέτρων --Αφαίρεση επιπλέον διαστημάτων στην αρχή και στο τέλος της γραμμής κειμένου SET @ProductName = LTRIM(RTRIM(@ProductName)); --Προσθήκη νέας εγγραφής INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Επιστροφή των δεδομένων SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

Εκτέλεση μιας αποθηκευμένης διαδικασίας στην εντολή T-SQL - EXECUTE

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

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

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

1. Καλέστε τη διαδικασία χωρίς να καθορίσετε την τιμή EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" --2. Καλέστε τη διαδικασία υποδεικνύοντας την τιμή EXEC TestProcedure @CategoryId = 1, @ProductName = "Test product 2", @Price = 300 --3. Καλέστε τη διαδικασία χωρίς να καθορίσετε το όνομα των παραμέτρων EXEC TestProcedure 1, "Test product 3", 400

Αλλαγή μιας αποθηκευμένης διαδικασίας σε δήλωση T-SQL - ALTER PROCEDURE

Μπορείτε να κάνετε αλλαγές στον αλγόριθμο της διαδικασίας χρησιμοποιώντας τις οδηγίες ΑΛΛΑΓΗ ΔΙΑΔΙΚΑΣΙΑΣ. Με άλλα λόγια, για να αλλάξετε μια ήδη υπάρχουσα διαδικασία, πρέπει απλώς να γράψετε ALTER PROCEDURE αντί για CREATE PROCEDURE και να αλλάξετε όλα τα άλλα όπως χρειάζεται.

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

Αλλάζουμε τη διαδικασία ΑΛΛΑΓΗ ΔΙΑΔΙΚΑΣΙΑΣ TestProcedure (--Εισερχόμενες παράμετροι @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) ΩΣ ΑΡΧΗ --Οδηγίες που εφαρμόζουν τον αλγόριθμό σας --Επεξεργασία εισερχόμενων παραμέτρων --Αφαίρεση επιπλέον διαστημάτων στην αρχή και τέλος των γραμμών κειμένου SET @ProductName = LTRIM(RTRIM(@ProductName)); --Προσθήκη νέας εγγραφής INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

Διαγραφή μιας αποθηκευμένης διαδικασίας στην πρόταση T-SQL - DROP PROCEDURE

Εάν είναι απαραίτητο, μπορείτε να διαγράψετε την αποθηκευμένη διαδικασία, αυτό γίνεται χρησιμοποιώντας τις οδηγίες ΔΙΑΔΙΚΑΣΙΑ ΑΠΟΡΡΙΨΗΣ.

Για παράδειγμα, ας διαγράψουμε τη διαδικασία δοκιμής που δημιουργήσαμε.

ΔΙΑΔΙΚΑΣΙΑ ΑΠΟΡΡΙΨΗΣ TestProcedure

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

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

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

1. Επεξεργαστείτε όλα τα παραδείγματα, αναλύστε τα αποτελέσματα της εκτέλεσής τους στο βοηθητικό πρόγραμμα SQL Server Management Studio. Έλεγχος παρουσίας δημιουργημένων διαδικασιών στην τρέχουσα βάση δεδομένων.

2. Ολοκλήρωση όλων των παραδειγμάτων και εργασιών κατά την εργαστηριακή εργασία.

3. Ολοκλήρωση μεμονωμένων εργασιών σύμφωνα με τις επιλογές.

Επεξηγήσεις για την εκτέλεση της εργασίας

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

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

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

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

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

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

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

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

Η σύνταξη του τελεστή για τη δημιουργία μιας νέας ή την αλλαγή μιας υπάρχουσας αποθηκευμένης διαδικασίας στον συμβολισμό του MS SQL Server:

( ΔΗΜΙΟΥΡΓΙΑ | ΑΛΛΑΓΗ ) PROC[ EDURE] όνομα_διαδικασίας [ ;αριθμός] [ ( @parameter_name data_type ) [ ΠΑΡΑΛΛΟΝ ] [ = ΠΡΟΕΠΙΛΟΓΗ ] [ ΕΞΟΔΟΣ ] ] [ ,... n] [ ΜΕ ( ΕΠΑΝΑΣΥΝΘΕΣΗ | ΚΡΥΠΤΟΠΟΙΗΣΗ | ΕΠΑΝΑΣΥΝΘΕΣΗ, ENCRY)] [ ΓΙΑ ΑΝΑΠΑΡΑΓΩΓΗ] AS sql_statement [ ... n]

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

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

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

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

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

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

Η παράμετρος ΓΙΑ ΑΝΑΠΑΡΑΓΩΓΗ απαιτείται κατά την αναπαραγωγή δεδομένων και τη συμπερίληψη της αποθηκευμένης διαδικασίας που δημιουργήθηκε ως άρθρο σε μια δημοσίευση. Η λέξη-κλειδί ENCRYPTION δίνει εντολή στον διακομιστή να κρυπτογραφήσει τον αποθηκευμένο κώδικα διαδικασίας, ο οποίος μπορεί να παρέχει προστασία από τη χρήση ιδιόκτητων αλγορίθμων που υλοποιούν την αποθηκευμένη διαδικασία. Η λέξη-κλειδί AS τοποθετείται στην αρχή του ίδιου του αποθηκευμένου σώματος διαδικασίας. Το σώμα της διαδικασίας μπορεί να χρησιμοποιήσει σχεδόν όλες τις εντολές SQL, να δηλώσει συναλλαγές, να ορίσει κλειδώματα και να καλέσει άλλες αποθηκευμένες διαδικασίες. Μπορείτε να βγείτε από μια αποθηκευμένη διαδικασία χρησιμοποιώντας την εντολή RETURN.

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

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

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

Για να εκτελέσετε μια αποθηκευμένη διαδικασία, χρησιμοποιήστε την εντολή: [ [ EXEC [ UTE] procedure_name [ ;number] [ [ @parameter_name= ] (τιμή | @variable_name) [ OUTPUT ] | [ ΠΡΟΕΠΙΛΟΓΗ ] ] [ ,... n]

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

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

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

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

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

Χρήση RETURN σε αποθηκευμένη διαδικασία

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

CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

Ασκηση 1.

EXEC Count_Books

Ελέγξτε το αποτέλεσμα.

Ένα παράδειγμα δημιουργίας μιας διαδικασίας με μια παράμετρο εισόδου:

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GO

Εργασία 2. Δημιουργήστε αυτήν τη διαδικασία στην ενότητα Αποθηκευμένες διαδικασίες της βάσης δεδομένων DB_Books χρησιμοποιώντας το βοηθητικό πρόγραμμα SQL server Management Studio. Εκτελέστε το χρησιμοποιώντας την εντολή

EXEC Count_Books_Pages 100

Ελέγξτε το αποτέλεσμα.

Ένα παράδειγμα δημιουργίας μιας διαδικασίας με παραμέτρους εισόδου:

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages ΚΑΙ Title_book ΚΑΝΤΕ LIKE @Title GO

Εργασία 3.Δημιουργήστε αυτήν τη διαδικασία στην ενότητα Αποθηκευμένες διαδικασίες της βάσης δεδομένων DB_Books χρησιμοποιώντας το βοηθητικό πρόγραμμα SQL server Management Studio. Εκτελέστε το χρησιμοποιώντας την εντολή

EXEC Count_Books_Title 100 , "P%"

Ελέγξτε το αποτέλεσμα.

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

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_itle LIKE

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

Sql> Δήλωση @q Ως int EXEC Count_Books_Itogo 100, "P%", @q έξοδος επιλέξτε @q

Ελέγξτε το αποτέλεσμα.

Ένα παράδειγμα δημιουργίας μιας διαδικασίας με παραμέτρους εισαγωγής και RETURN:

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ όνομα ελέγχου @param INT AS IF (ΕΠΙΛΕΞΤΕ Όνομα_συγγραφέα ΑΠΟ συντάκτες WHERE Code_author = @param) = "Pushkin A.S." ΕΠΙΣΤΡΟΦΗ 1 ΑΛΛΟ ΕΠΙΣΤΡΟΦΗ 2

Εργασία 5.Δημιουργήστε αυτήν τη διαδικασία στην ενότητα Αποθηκευμένες διαδικασίες της βάσης δεδομένων DB_Books χρησιμοποιώντας το βοηθητικό πρόγραμμα SQL server Management Studio. Εκτελέστε το χρησιμοποιώντας τις εντολές:

ΔΗΛΩΣΤΕ @return_status INT EXEC @return_status = όνομα ελέγχου 1 ΕΠΙΛΟΓΗ "Κατάσταση επιστροφής" = @return_status

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

ΔΗΜΙΟΥΡΓΙΑ PROC update_proc ΩΣ ΕΝΗΜΕΡΩΣΗ Purchases SET Code_purchase = Code_purchase* 2

Εργασία 6.Δημιουργήστε αυτήν τη διαδικασία στην ενότητα Αποθηκευμένες διαδικασίες της βάσης δεδομένων DB_Books χρησιμοποιώντας το βοηθητικό πρόγραμμα SQL server Management Studio. Εκτελέστε το χρησιμοποιώντας την εντολή

EXEC update_proc

Ένα παράδειγμα διαδικασίας με παράμετρο εισόδου για τη λήψη όλων των πληροφοριών σχετικά με έναν συγκεκριμένο συγγραφέα:

CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Συγγραφείς WHERE name_author= @k

Εργασία 7.

EXEC select_author "Pushkin A.S." ή select_author @k= "Pushkin A.S." ή EXEC select_author @k= "Pushkin A.S."

Ένα παράδειγμα δημιουργίας μιας διαδικασίας με μια παράμετρο εισόδου και μια προεπιλεγμένη τιμή για την αύξηση της τιμής ενός πεδίου κλειδιού στον πίνακα Αγορές κατά έναν καθορισμένο αριθμό φορών (2 φορές από προεπιλογή):

CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @p

Η διαδικασία δεν επιστρέφει δεδομένα.

Εργασία 8.Δημιουργήστε αυτήν τη διαδικασία στην ενότητα Αποθηκευμένες διαδικασίες της βάσης δεδομένων DB_Books χρησιμοποιώντας το βοηθητικό πρόγραμμα SQL server Management Studio. Εκτελέστε το χρησιμοποιώντας τις εντολές:

EXEC update_proc 4 ή EXEC update_proc @p = 4 ή EXEC update_proc -- θα χρησιμοποιηθεί η προεπιλεγμένη τιμή.

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

CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @d1 ΚΑΙ @d2 SET @c = ISNULL(@c,

Εργασία 9.Δημιουργήστε αυτήν τη διαδικασία στην ενότητα Αποθηκευμένες διαδικασίες της βάσης δεδομένων DB_Books χρησιμοποιώντας το βοηθητικό πρόγραμμα SQL server Management Studio. Εκτελέστε το χρησιμοποιώντας τις εντολές:

ΔΗΛΩΣΤΕ @c2 INT EXEC count_purchases '01-jun-2006', '01-jul-2006', @c2 OUTPUT SELECT @c2

Επιλογές για εργασίες για εργαστηριακές εργασίες Νο. 4

Γενικές προμήθειες.Στο βοηθητικό πρόγραμμα SQL Server Management Studio, δημιουργήστε μια νέα σελίδα για τον κώδικα (το κουμπί "Δημιουργία ερωτήματος"). Ενεργοποιήστε μέσω προγραμματισμού τη δημιουργημένη βάση δεδομένων DB_Books χρησιμοποιώντας τη δήλωση Χρήση. Δημιουργήστε αποθηκευμένες διαδικασίες χρησιμοποιώντας δηλώσεις διαδικασίας Δημιουργία και ορίστε μόνοι σας τα ονόματα των διαδικασιών. Κάθε διαδικασία θα εκτελεί ένα ερώτημα SQL που εκτελέστηκε στο δεύτερο εργαστήριο. Επιπλέον, ο κώδικας SQL των ερωτημάτων πρέπει να αλλάξει, ώστε να μπορούν να μεταδίδουν τις τιμές των πεδίων που χρησιμοποιούνται για την αναζήτηση.

Για παράδειγμα, η αρχική εργασία και αίτημα στην εργαστηριακή εργασία Νο. 2:

/*Επιλέξτε από τον κατάλογο προμηθευτών (πίνακας παράδοσης) τα ονόματα των εταιρειών, τους αριθμούς τηλεφώνου και το INN (πεδία Όνομα_εταιρία, Τηλέφωνο και INN), των οποίων το όνομα εταιρείας (πεδίο Όνομα_εταιρία) είναι "OJSC MIR".

SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = "OJSC MIR"

*/ –Σε αυτήν την εργασία θα δημιουργηθεί η ακόλουθη διαδικασία:

CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = @comp

– Για να ξεκινήσετε τη διαδικασία, χρησιμοποιήστε την εντολή:

EXEC select_name_company "JSC MIR"

Λίστα εργασιών

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

Επιλογή 1

1. Εμφανίστε μια λίστα εργαζομένων που έχουν τουλάχιστον ένα παιδί.

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

3. Εμφάνιση λίστας γονέων που έχουν ανήλικα παιδιά.

4. Εμφάνιση πληροφοριών για δώρα με τιμή μεγαλύτερη από τον καθορισμένο αριθμό, ταξινομημένα κατά ημερομηνία.

Επιλογή 2

1. Εμφανίστε μια λίστα συσκευών με τον καθορισμένο τύπο.

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

3. Εμφανίστε μια λίστα με τους κατόχους συσκευών και τον αριθμό των αιτημάτων τους, ταξινομημένα κατά τον αριθμό των αιτημάτων με φθίνουσα σειρά.

4. Εμφάνιση πληροφοριών για τεχνίτες με βαθμό μεγαλύτερο από τον καθορισμένο αριθμό ή με ημερομηνία πρόσληψης μικρότερη από την καθορισμένη ημερομηνία.

Επιλογή 3

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

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

4. Εμφανίστε μια λίστα λουλουδιών και την ποικιλία για λουλούδια με ύψος μεγαλύτερο από τον καθορισμένο αριθμό ή ανθισμένα.

Επιλογή 4

1. Εμφανίστε μια λίστα φαρμάκων με την καθορισμένη ένδειξη χρήσης.

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

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

Επιλογή 5

2. Εμφανίστε μια λίστα παροπλισμένου εξοπλισμού για τον καθορισμένο λόγο.

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

4. Εμφανίστε μια λίστα εξοπλισμού με καθορισμένο τύπο ή με ημερομηνία παραλαβής μεγαλύτερη από μια συγκεκριμένη τιμή

Επιλογή 6

1. Εμφανίστε μια λίστα με πιάτα με βάρος μεγαλύτερο από τον καθορισμένο αριθμό.

2. Εμφανίστε μια λίστα προϊόντων των οποίων τα ονόματα περιέχουν το καθορισμένο τμήμα λέξης.

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

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

Επιλογή 7

1. Εμφανίστε μια λίστα υπαλλήλων με την καθορισμένη θέση.

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

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

Επιλογή 8

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

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

Επιλογή 9

1. Εμφανίστε μια λίστα υπαλλήλων που πήραν άδεια του καθορισμένου τύπου.

2. Εμφανίστε μια λίστα εγγράφων με ημερομηνία εγγραφής στην καθορισμένη περίοδο.

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

4. Εμφανίστε μια λίστα καταχωρισμένων εγγράφων με κωδικό εγγράφου στο καθορισμένο εύρος.

Επιλογή 10

1. Εμφανίστε μια λίστα υπαλλήλων με την καθορισμένη θέση.

2. Εμφανίστε μια λίστα εγγράφων των οποίων το περιεχόμενο περιέχει το καθορισμένο τμήμα λέξης.

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

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

Επιλογή 11

1. Εμφανίστε μια λίστα υπαλλήλων που έχουν ανατεθεί στην καθορισμένη θέση.

2. Εμφανίστε μια λίστα εγγράφων με ημερομηνία εγγραφής στην καθορισμένη περίοδο.

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

4. Εμφανίστε μια λίστα καταχωρισμένων εγγράφων με κωδικό εγγράφου στο καθορισμένο εύρος.

Επιλογή 12

3. Εμφανίστε μια λίστα με άτομα που ενοικίασαν εξοπλισμό και τον αριθμό των αιτημάτων τους, ταξινομημένα κατά τον αριθμό των αιτημάτων με φθίνουσα σειρά.

Επιλογή 13

1. Εμφανίστε μια λίστα εξοπλισμού με τον καθορισμένο τύπο. 2. Εμφανίστε μια λίστα εξοπλισμού που έχει διαγραφεί από συγκεκριμένο υπάλληλο.

3. Εμφανίστε την ποσότητα του παροπλισμένου εξοπλισμού, ομαδοποιημένη ανά τύπο εξοπλισμού.

4. Εμφάνιση πληροφοριών σχετικά με υπαλλήλους με ημερομηνία πρόσληψης μεγαλύτερη από μια συγκεκριμένη ημερομηνία.

Επιλογή 14

1. Εκτυπώστε μια λίστα λουλουδιών με τον καθορισμένο τύπο φύλλου.

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

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

4. Εμφανίστε μια λίστα λουλουδιών και την ποικιλία για λουλούδια με ύψος μεγαλύτερο από έναν ορισμένο αριθμό ή ανθισμένα.

Επιλογή 15

1. Εμφανίστε μια λίστα πελατών που έκαναν check in στα δωμάτια κατά τη διάρκεια της καθορισμένης περιόδου.

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

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

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

Επιλογή 16

1. Εμφανίστε μια λίστα εξοπλισμού με τον καθορισμένο τύπο.

2. Εμφανίστε μια λίστα εξοπλισμού που νοικιάστηκε από συγκεκριμένο πελάτη.

3. Εμφανίστε μια λίστα με άτομα που ενοικίασαν εξοπλισμό και τον αριθμό των αιτημάτων τους, ταξινομημένα κατά τον αριθμό των αιτημάτων με φθίνουσα σειρά.

4. Εμφάνιση πληροφοριών σχετικά με πελάτες ταξινομημένες κατά διεύθυνση.

Επιλογή 17

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

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

3. Εμφανίστε το άθροισμα της τιμής των τιμών με έναν κωδικό στο καθορισμένο εύρος.

4. Εμφανίστε μια λίστα με οικονομικά υπεύθυνα άτομα με την ημερομηνία απασχόλησης στο καθορισμένο εύρος.

Επιλογή 18

1. Εμφανίστε μια λίστα με τις επισκευές που πραγματοποιήθηκαν από συγκεκριμένο τεχνικό.

2. Εμφανίστε μια λίστα με τα στάδια εργασίας που περιλαμβάνονται στο έργο του οποίου ο τίτλος περιέχει την καθορισμένη λέξη.

3. Εμφανίστε το άθροισμα του κόστους των σταδίων εργασιών επισκευής για εργασία με έναν κωδικό στο καθορισμένο εύρος.

4. Εμφανίστε μια λίστα εργοδηγών με την ημερομηνία πρόσληψης στο καθορισμένο εύρος.

Επιλογή 19

1. Εμφάνιση λίστας φαρμάκων με συγκεκριμένη ένδειξη.

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

3. Εμφανίστε την ημερομηνία πώλησης, το ποσό, το όνομα του ταμείου και το φάρμακο στην απόδειξη με τον καθορισμένο αριθμό.

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

Επιλογή 20

1. Εμφανίστε μια λίστα υπαλλήλων με την καθορισμένη θέση.

2. Εμφανίστε μια λίστα εγγράφων των οποίων το περιεχόμενο περιέχει το καθορισμένο τμήμα λέξης.

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

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

SQL - Μάθημα 15. Αποθηκευμένες διαδικασίες. Μέρος 1.

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

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ Όνομα_όνομα διαδικασίας (παράμετροι) έναρξη δηλώσεων τέλος

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

INSERT INTO πελάτες (όνομα, email) VALUE ("Ivanov Sergey", " [email προστατευμένο]");

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

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ ins_cust(n CHAR(50), e CHAR(50)) ξεκινάει εισαγωγή στους πελάτες (όνομα, email) τιμή (n, e); τέλος

Δώστε προσοχή στον τρόπο καθορισμού των παραμέτρων: πρέπει να δώσετε ένα όνομα στην παράμετρο και να υποδείξετε τον τύπο της και στο σώμα της διαδικασίας χρησιμοποιούμε ήδη ονόματα παραμέτρων. Μία επιφύλαξη. Όπως θυμάστε, ένα ερωτηματικό σημαίνει το τέλος του αιτήματος και το στέλνει για εκτέλεση, κάτι που είναι απαράδεκτο σε αυτήν την περίπτωση. Επομένως, πριν γράψετε μια διαδικασία, πρέπει να επαναπροσδιορίσετε το διαχωριστικό c. στο "//" ώστε το αίτημα να μην αποσταλεί νωρίτερα. Αυτό γίνεται χρησιμοποιώντας τον τελεστή DELIMITER //:

Έτσι, υποδείξαμε στο DBMS ότι οι εντολές θα πρέπει τώρα να εκτελούνται μετά το //. Θα πρέπει να θυμόμαστε ότι ο επαναπροσδιορισμός του διαχωριστή πραγματοποιείται μόνο για μία συνεδρία, δηλ. την επόμενη φορά που θα δουλέψετε με τη MySql, το διαχωριστικό θα γίνει ξανά ερωτηματικό και, αν χρειαστεί, θα πρέπει να επαναπροσδιοριστεί ξανά. Τώρα μπορείτε να τοποθετήσετε τη διαδικασία:

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ ins_cust(n CHAR(50), e CHAR(50)) ξεκινάει εισαγωγή στους πελάτες (όνομα, email) τιμή (n, e); τέλος //

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

call ins_cust("Sychov Valery", " [email προστατευμένο]")//

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

Εμφανίστηκε, η διαδικασία λειτουργεί και θα λειτουργεί πάντα μέχρι να τη διαγράψουμε χρησιμοποιώντας τον τελεστή DOP PROCEDURE procedure_name.

Όπως αναφέρθηκε στην αρχή του μαθήματος, οι διαδικασίες σάς επιτρέπουν να συνδυάσετε μια σειρά ερωτημάτων. Ας δούμε πώς γίνεται. Θυμάστε στο Μάθημα 11 θέλαμε να μάθουμε πόσο μας έφερε αγαθά ο προμηθευτής "House of Printing"; Για να γίνει αυτό, έπρεπε να χρησιμοποιήσουμε δευτερεύοντα ερωτήματα, συνδέσεις, υπολογιζόμενες στήλες και προβολές. Τι γίνεται αν θέλουμε να μάθουμε πόσο μας έφερε αγαθά ο άλλος προμηθευτής; Θα πρέπει να δημιουργήσετε νέα ερωτήματα, να εγγραφείτε κ.λπ. Είναι πιο εύκολο να γράψετε μια αποθηκευμένη διαδικασία για αυτήν την ενέργεια μία φορά.

Φαίνεται ότι ο ευκολότερος τρόπος είναι να λάβετε την προβολή και το ερώτημα για αυτό που είναι ήδη γραμμένο στο Μάθημα 11, να το συνδυάσετε σε μια αποθηκευμένη διαδικασία και να κάνετε το αναγνωριστικό προμηθευτή (id_vendor) μια παράμετρο εισόδου, όπως αυτό:

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ sum_vendor(i INT) start ΔΗΜΙΟΥΡΓΙΑ ΠΡΟΒΟΛΗ report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, Prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming. εισερχόμενη = ( SELECT id_incoming FROM incoming WHERE id_vendor=i); SELECT SUM(summa) FROM report_vendor; τέλος //

Αλλά η διαδικασία δεν θα λειτουργήσει έτσι. Το όλο θέμα είναι αυτό οι προβολές δεν μπορούν να χρησιμοποιήσουν παραμέτρους. Επομένως, θα πρέπει να αλλάξουμε ελαφρώς τη σειρά των αιτημάτων. Αρχικά, θα δημιουργήσουμε μια προβολή που θα εμφανίζει το αναγνωριστικό προμηθευτή (id_vendor), το αναγνωριστικό προϊόντος (id_product), την ποσότητα (ποσότητα), την τιμή (τιμή) και το ποσό (άθροισμα) από τους τρεις πίνακες Προμήθειες (εισερχόμενα), Magazine (magazine_incoming) , Τιμές (τιμές):

ΔΗΜΙΟΥΡΓΙΑ ΠΡΟΒΟΛΗ report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, Prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming.incoming .id_incoming= εισερχόμενη .id_incoming;

Στη συνέχεια, θα δημιουργήσουμε ένα ερώτημα που θα συνοψίζει τα ποσά προμήθειας του προμηθευτή που μας ενδιαφέρει, για παράδειγμα, με id_vendor=2:

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

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑ sum_vendor(i INT) start ΔΗΜΙΟΥΡΓΙΑ ΠΡΟΒΟΛΗ report_vendor AS ΕΠΙΛΟΓΗ incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, magazine_incoming.quantity*prices.price AS summa_incominneazi _προϊόν= τιμές .id_product AND magazine_incoming.id_incoming= incoming.id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; τέλος //

Ας ελέγξουμε τη λειτουργία της διαδικασίας με διαφορετικές παραμέτρους εισαγωγής:

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

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

ΑΠΟΣΤΟΛΗ ΔΙΑΔΙΚΑΣΙΑ sum_vendor// DROP VIEW report_vendor// CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, Prices.price, magazine_incoming.quantityAssuming_prices.comazine ne_incoming.id_product = Prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming // ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ sum_vendor(i INT) start SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; τέλος //

Έλεγχος της εργασίας:

call sum_vendor(1)// call sum_vendor(2)// call sum_vendor(3)//

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

ΔΗΜΙΟΥΡΓΙΑ ΔΙΑΔΙΚΑΣΙΑΣ sum_vendor(i INT) start DROP VIEW ΑΝ ΥΠΑΡΧΕΙ report_vendor; ΔΗΜΙΟΥΡΓΙΑ ΠΡΟΒΟΛΗ report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, Prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming.incoming .id_incoming= εισερχόμενη .id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; τέλος //

Πριν χρησιμοποιήσετε αυτήν την επιλογή, φροντίστε να καταργήσετε τη διαδικασία sum_vendor και στη συνέχεια να δοκιμάσετε την εργασία:

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