Κεφάλαιο 4. Προχωρημένες λειτουργίες στον SQL Server
4.1. Ερωτήματα ορισμού δεδομένων
4.1.1 Εισαγωγή πολλών γραμμών σε πίνακα
4.1.2 Ενημέρωση τιμής των πεδίων ενός πίνακα
4.1.3 Διαγραφή των γραμμών ενός πίνακα
4.1.4 Μεταβολή της δομής ενός πίνακα
4.1.5 Μετονομασία πίνακα και πεδίου πίνακα
4.1.6 Διαγραφή πίνακα και βάσης δεδομένων
4.2. Αποθηκευμένες Διαδικασίες, Εναύσματα, Συναλλαγές
4.2.1 Αποθηκευμένες διαδικασίες/Stored Procedures
4.2.2 Εναύσματα/Triggers
4.2.3 Συναλλαγές/Transactions
4.3. Βελτιστοποίηση Ερωτημάτων
4.3.1 Παρακολούθηση του πλάνου εκτέλεσης ερωτήματος SQL
4.3.2 Ευρετήρια/Indices
4.3.3 Παράδειγμα βελτίωσης απόδοσης ερωτήματος με την χρήση ευρετηρίου
4.4. Εξαγωγή του κώδικα της βάσης δεδομένων
4.5 Εκχώρηση δικαιωμάτων πρόσβασης χρηστών στη βάση δεδομένων
4.5.1 Εκχώρηση δικαιωμάτων χρήστη
4.5.2 Εκχώρηση δικαιωμάτων στο χρήστη Employee με κώδικα SQL.
4.5.3 Εκχώρηση δικαιωμάτων στο χρήστη Manager με γραφικό τρόπο
4.5.4 Αφαίρεση δικαιωμάτων από τον χρήστη
4.5.5 Άρνηση δικαιωμάτων σε χρήστη
4.6. Ασκήσεις
4.7. Βιβλιογραφία/Αναφορές
Κεφάλαιο 4. Προχωρημένες λειτουργίες στον SQL Server
Σύνοψη
Σ’ αυτό το κεφάλαιο θα παρουσιάσουμε προχωρημένες λειτουργίες που γίνονται στο περιβάλλον του SQL Server. Πιο συγκεκριμένα, αφού μελετηθούν εντολές της SQL που αφορούν τον ορισμό δεδομένων (Data Definition Language), θα παρουσιαστούν κι άλλα προχωρημένα θέματα όπως είναι η βελτιστοποίηση ερωτημάτων, οι αποθηκευμένες διαδικασίες, τα εναύσματα και οι συναλλαγές με την βοήθεια της γλώσσας προγραμματισμού Transact-SQL.
Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.1. Ερωτήματα ορισμού
δεδομένων Οι μέχρι τώρα εντολές της SQL αφορούσαν την αναζήτηση πληροφορίας σε μια βάση δεδομένων. Όμως, η SQL περιέχει εντολές και για την εισαγωγή, τη διαγραφή και τη μεταβολή της πληροφορίας που βρίσκεται στη βάση δεδομένων. Αυτές θα παρουσιαστούν στη συνέχεια. Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.1.1. Εισαγωγή πολλών
γραμμών σε πίνακα Στην SQL, είναι δυνατή η εισαγωγή πολλών γραμμών σε ένα πίνακα με την
βοήθεια ενός ερωτήματος επιλογής από ένα άλλο πίνακα (Hoffer, Venkatarama, & Topi, 2013· Μανωλόπουλος, & Παπαδόπουλος, 2006). Για παράδειγμα έστω
τα παρακάτω δύο ερωτήματα: “Να
δημιουργηθεί ένας νέος πίνακας για ταινίες πριν από το 1960, με όνομα
ΠΑΛΙΑ_ΤΑΙΝΙΑ”. Η εντολή SQL είναι: CREATE TABLE ΠΑΛΙΑ_ΤΑΙΝΙΑ( “Στον
πίνακα ΠΑΛΙΑ_ΤΑΙΝΙΑ να προστεθούν οι γραμμές του πίνακα ΤΑΙΝΙΑ, οι
οποίες αντιστοιχούν σε ταινίες με έτος παραγωγής μικρότερο του 1960”. Η
εντολή SQL είναι: Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.1.2.
Ενημέρωση τιμής των πεδίων ενός πίνακα Με την SQL, είναι δυνατή η μεταβολή της τιμής των πεδίων ορισμένων ή όλων
των γραμμών ενός πίνακα. Η διαδικασία αυτή γίνεται με τους όρους update
και set της SQL. Για παράδειγμα, έστω το ερώτημα: “Να
μειωθεί κατά 1 ευρώ η τιμή του δίσκου με κωδικό 1.”. Η εντολή
SQL είναι: Ο όρος Set περιέχει τα πεδία που θα ενημερωθούν, τα οποία μπορεί να είναι και περισσότερα από ένα. Τονίζεται, επίσης, ότι αν θέλαμε να μη μειώσουμε απλώς την τιμή ενός δίσκου αλλά να τη διαγράψουμε πλήρως, τότε θα αρκούσε να θέσουμε στο όρισμα SET Τιμή = NULL. Μ’ αυτόν τον τρόπο θα πετυχαίναμε την διαγραφή τιμής για ένα ορισμένο πεδίο και όχι τη διαγραφή ολόκληρων εγγραφών, η οποία θα επιδειχθεί στην επόμενη ενότητα. Τέλος, ο όρος where είναι προαιρετικός. Αυτό σημαίνει ότι αν δεν υπάρχει ο όρος WHERE, τότε ενημερώνονται ανάλογα οι τιμές όλων των εγγραφών για το συγκεκριμένο πεδίο. Για παράδειγμα, έστω το ερώτημα: “Έγινε
ανακαίνιση στο DVDclub και πλέον όλοι οι δίσκοι είναι
τύπου BLU-RAY. Να ενημερωθεί ο
πίνακας ΔΙΣΚΟΣ”. Η εντολή SQL είναι: Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου Στην SQL επιτρέπεται η διαγραφή ολόκληρων πλειάδων και όχι η
μερική διαγραφή για ορισμένα μόνο πεδία. Η διαγραφή γίνεται με την εντολή delete from. Για παράδειγμα, έστω το
αίτημα: “Να
διαγραφεί ο πελάτης με κωδικό 4”. Η αντίστοιχη εντολή
είναι: Ο όρος where είναι προαιρετικός. Στην περίπτωση που δεν δίνεται συνθήκη,
τότε διαγράφονται όλες οι πλειάδες της σχέσης. Για παράδειγμα, έστω το
αίτημα: “Διαγράψτε
όλα τα περιεχόμενα του πίνακα ΠΕΛΑΤΗΣ”. Η εντολή SQL είναι: 4.1.4.
Μεταβολή της δομής ενός πίνακα Για την προσθήκη νέων πεδίων σε έναν πίνακα, υπάρχει η εντολή SQL alter table σε συνδυασμό με το όρο add. Έστω για παράδειγμα, το αίτημα: “Να
γίνει προσθήκη ενός πεδίου με όνομα Γλώσσα, τύπου char(30), στον πίνακα
ΤΑΙΝΙΑ”. ΠΡΟΣΟΧΗ! Οι τιμές του πεδίου Γλώσσα είναι αρχικά null για όλες τις γραμμές. Επιπλέον, θα πρέπει να τονιστεί ότι σε κάποιες περιπτώσεις ο SQL Server ενδέχεται για λόγους ασφαλείας να μην εφαρμόσει την αλλαγή σχεδίασης ενός πίνακα. Στη συγκεκριμένη περίπτωση, θα πρέπει, μέσω του μενού, να ακολουθήσουμε τη διαδρομή Tools --> Options --> Designers και, στην καρτέλα που θα εμφανιστεί, να αποεπιλέξουμε την επιλογή “Prevent saving changes that require table re-creation”. Για την διαγραφή πεδίων σε έναν πίνακα, υπάρχει η εντολή SQL alter
table σε συνδυασμό με τον όρο drop column. Έστω για παράδειγμα, το
παρακάτω αίτημα: “Να
διαγραφεί το πεδίο Γλώσσα από τον πίνακα ΤΑΙΝΙΑ”: Επιπλέον, είναι δυνατή και η αλλαγή τύπου δεδομένων για ένα πεδίο ενός
πίνακα. Έστω για παράδειγμα, το παρακάτω αίτημα: Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.1.5.
Μετονομασία πίνακα και πεδίου πίνακα Η μετονομασία πίνακα στον SQL Server γίνεται με τη χρήση της
ενσωματωμένης συνάρτησης sp_rename σε συνδυασμό με τον
όρο exec. Για παράδειγμα, έστω το αίτημα: “Να
μετονομαστεί ο πίνακας ΔΙΣΚΟΣ σε DVD”. Η
εντολή SQL είναι: “Να
μετονομαστεί το πεδίο Έτος του πίνακα ΤΑΙΝΙΑ σε Χρονιά”. Η
εντολή SQL είναι: Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.1.6.
Διαγραφή πίνακα και βάσης δεδομένων Για την κατάργηση ενός πίνακα από τη βάση δεδομένων υπάρχει η εντολή drop table. Έστω για παράδειγμα το αίτημα: “Να
διαγραφεί ο πίνακας ΠΑΛΙΑ_ΤΑΙΝΙΑ”. Η εντολή SQL είναι: Πρέπει απαραίτητα να σημειωθεί ότι η παραπάνω εντολή: (α) διαγράφει όλες τις γραμμές και (β) καταργεί τον ίδιο τον πίνακα. Επομένως, για παράδειγμα, δεν είναι δυνατή η αναφορά για εισαγωγή γραμμών στον πίνακα που διαγράφηκε. Αντιθέτως, η εντολή delete from, που αναφέρθηκε νωρίτερα, θα διαγράψει όλες τις γραμμές αλλά όχι τον πίνακα. Τέλος, σημειώνουμε ότι η διαγραφή ολόκληρης της βάσης δεδομένων γίνεται επίσης με την εντολή drop. Για παράδειγμα, έστω το παρακάτω αίτημα: Να διαγραφεί η βάση δεδομένων database_name”.
Η εντολή SQL είναι: Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.2.
Αποθηκευμένες Διαδικασίες, Εναύσματα, Συναλλαγές
Οι αποθηκευμένες διαδικασίες/ stored procedures είναι τμήματα κώδικα προγράμματος συνδυασμένα με SQL ερωτήματα που αποθηκεύονται στην βάση δεδομένων και ενεργοποιούνται κάθε φορά που εμείς θέλουμε να τα χρησιμοποιήσουμε. Συνήθως αφορούν εργασίες που γίνονται πολύ συχνά και δεν υπάρχει λόγος κάθε φορά να τις φτιάχνουμε από την αρχή. Ο SQL Server έχει ενσωματωμένη την γλώσσα προγραμματισμού Transact-SQL (T-SQL), με την οποία μπορούμε να δημιουργούμε αποθηκευμένες διαδικασίες. Για παράδειγμα, έστω το παρακάτω ερώτημα: “Να
δημιουργηθεί μια διαδικασία (Stored Procedure) που θα εισάγει Ν
εγγραφές πελατών κάθε φορά που την καλούμε.”. Ο κώδικας της
αποθηκευμένης διαδικασίας είναι: use DVDclub Set @counter= @counter +1 Ακολούθως αναλύουμε τις πιο σημαντικές εντολές από το παραπάνω ερώτημα: Μετά την επιτυχή εκτέλεση της αποθηκευμένης διαδικασίας, έχουμε τη δυνατότητα να την εντοπίσουμε στο φάκελο Programmability – Stored Procedures που βρίσκεται μέσα στο φάκελο της βάσης δεδομένων dvdclub, όπως φαίνεται στην Εικόνα 4.1. Αν θέλουμε να αλλάξουμε τον κώδικα της διαδικασίας, επιλέγουμε δεξί κλικ πάνω της και, στη συνέχεια, την εντολή Modify. Για την εκτέλεση του κώδικα της διαδικασίας επιλέγουμε δεξί κλικ και Execute Stored Procedure. Ακολούθως, εμφανίζεται ένα παράθυρο εκτέλεσης, όπως αυτό της Εικόνας 4.2, όπου μπορούμε να εισάγουμε τις παραμέτρους εισόδου. Στο παράδειγμά μας θα εισάγουμε τον αριθμό 5000, γιατί θέλουμε να δημιουργηθούν 5000 νέοι πελάτες. Επιλέγοντας ΟΚ παράγεται αυτόματα ο αντίστοιχος κώδικας που εκτελεί την διαδικασία και, έτσι, ξεκινά η εκτέλεσή της. Εικόνα 4.2 Ανάλογα με τον όγκο της εργασίας της, η αποθηκευμένη διαδικασία εκτελείται στο χρονικό διάστημα δευτερολέπτων ή λεπτών της ώρας. Πρέπει να περιμένουμε μέχρι να δούμε το αντίστοιχο μήνυμα επιτυχούς εκτέλεσης ή όχι. Προκειμένου να επιβεβαιώσουμε ότι τα δεδομένα μας έχουν εισαχθεί στον πίνακα ΠΕΛΑΤΗΣ, αρκεί να τον ανοίξουμε με την επιλογή «Select top 1000 rows». Ένα δεύτερο παράδειγμα μιας stored procedure δίνεται με το παρακάτω ερώτημα: “Να
δημιουργηθεί μια διαδικασία (Stored Procedure) που θα εμφανίζει τα
τηλέφωνα όλων των πελατών κάθε φορά που την καλούμε.”. Ο
κώδικας της αποθηκευμένης διαδικασίας είναι: use DVDclub “Να
δημιουργηθεί μια διαδικασία (Stored Procedure) που θα εμφανίζει το
τηλέφωνο ενός υπό εξέταση πελάτη”. Ο κώδικας της αποθηκευμένης
διαδικασίας είναι: use DVDclub Προκειμένου να εκτελέσουμε την παραπάνω αποθηκευμένη διαδικασία, μπορούμε σε ένα Query Editor να δώσουμε την εντολή EXEC myphone2 ‘Καντακουζηνός’. Τέλος, ως άσκηση, θα μπορούσαμε να προχωρήσουμε στη δημιουργία μιας stored procedure που θα εμφανίζει όλους τους ηθοποιούς. Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου Το εύναυσμα/trigger είναι μια διαδικασία την οποία ορίζει ο διαχειριστής της βάσης δεδομένων. Ενεργοποιείται αυτόματα από τον SQL Server κάθε φορά που συμβαίνουν μεταβολές εισαγωγής, διαγραφής ή ενημέρωσης στα δεδομένα ενός πίνακα. Η βασική (χωρίς τα προαιρετικά ορίσματα) σύνταξη ενός trigger δίνεται παρακάτω: CREATE TRIGGER trigger_name όπου Υποθέτουμε, λοιπόν, ότι θέλουμε να φτιάξουμε ένα trigger το οποίο θα ενημερώνει ένα πεδίο του πίνακα ΔΙΣΚΟΣ κάθε φορά που ένας δίσκος DVD ενοικιάζεται στο πίνακα ΕΝΟΙΚΙΑΣΗ. Για παράδειγμα, έστω τα παρακάτω ερωτήματα: “Δημιουργήστε
ένα νέο πεδίο στον πίνακα ΔΙΣΚΟΣ με όνομα ‘loaned’ και τύπο δεδομένων char(1).
Το πεδίο θα έχει default αρχική τιμή (‘n’)
, που σημαίνει ότι ο δίσκος δεν είναι ενοικιασμένος.” use DVDclub Προσοχή! Σημειώνεται ότι οι ήδη υπάρχουσες εγγραφές του πίνακα ΔΙΣΚΟΣ θα έχουν τιμή NULL στο πεδίο loaned. Μόνο οι νέες εγγραφές θα παίρνουν εξαρχής αυτόματα την τιμή 'n'. Συνεπώς, θα πρέπει να ενημερώσουμε τις παλιές εγγραφές με το παρακάτω ερώτημα: “Ενημερώστε
τον πίνακα ΔΙΣΚΟΣ ώστε το πεδίο loaned στις παλιές
εγγραφές να έχει την τιμή (‘n’) . use DVDclub “Το
πεδίο loaned θα ενημερώνεται με την τιμή true
(‘y’) κάθε φορά που θα ενοικιάζεται ένας ψηφιακός
δίσκος στον πίνακα ΕΝΟΙΚΙΑΣΗ”. Ο κώδικας της αποθηκευμένης
διαδικασίας είναι: use DVDclub Εφόσον εκτελέσουμε επιτυχώς τον παραπάνω κώδικα στον Query Editor, μπορούμε να επιβεβαιώσουμε την δημιουργία του trigger στον πίνακα ENOIKIAΣΗ μέσα στον φάκελο triggers. Το trigger αυτό θα ενεργοποιείται κάθε φορά που θα γίνεται η εισαγωγή μιας νέας εγγραφής στον πίνακα ΕΝΟΙΚΙΑΣΗ. Πιο συγκεκριμένα, μετά την εισαγωγή μιας εγγραφής στο πίνακα ΕΝΟΙΚΙΑΣΗ, η συγκεκριμένη εγγραφή εισάγεται μέσα στο trigger με την βοήθεια ενός virtual πίνακα, ο οποίος έχει το δεσμευμένο (by default) όνομα INSERTED. Ο πίνακας INSERTED περιλαμβάνει την εγγραφή ή τις εγγραφές που κάθε φορά εισάγονται στον πίνακα ΕΝΟΙΚΙΑΣΗ και ενεργοποιούν το trigger μας. Εμείς, για συντομία, μετονομάζουμε το όνομα του πίνακα INSERTED σε i και εφαρμόζουμε την πράξη τη φυσικής σύνδεσης, προκειμένου να γίνει update στην τιμή εκείνης της εγγραφής του πίνακα ΔΙΣΚΟΣ που έχει τιμή ίση με το i.IDΔίσκου. Για να αποφύγουμε την παραπάνω περίπτωση, φτιάχνουμε τον πίνακα ΕΝΟΙΚΙΑΣΗ_DELETED, ο οποίος θα αποθηκεύει τις εγγραφές που διαγράφονται από τον πίνακα ΕΝΟΙΚΙΑΣΗ. Επιπρόσθετα, θα καταγράφει με ακρίβεια την ημερομηνία και την ώρα που πραγματοποιήθηκε η διαγραφή μιας εγγραφής από τον πίνακα ΕΝΟΙΚΙΑΣΗ, ώστε να μπορούμε να ερευνήσουμε από ποιον υπάλληλο έγινε η διαγραφή. Η δομή του πίνακα ΕΝΟΙΚΙΑΣΗ_DELETED φαίνεται στην Πίνακα 4.1. Field Type Null Key ID Int PRI IDΠελάτη int IDΔίσκου int Από date Έως date YES DateTimeOfDeleted datetime Πίνακας 4.1 EΝΟΙΚΙΑΣΗ_DELETED Ο
κώδικας για την δημιουργία του trigger είναι ο παρακάτω: CREATE TRIGGER ΕΝΟΙΚΙΑΣΗ_After_Delete
INSERT INTO ΕΝΟΙΚΙΑΣΗ_DELETED SELECT IDΠελάτη,IDΔίσκου, Από, Έως, GETDATE() FROM Deleted Ο παραπάνω κώδικας φτιάχνει ένα trigger με το όνομα «ΕΝΟΙΚΙΑΣΗ_After_Delete» που θα εφαρμοστεί στον πίνακα ΕΝΟΙΚΙΑΣΗ. Συγκεκριμένα, θα εκτελείται μετά από κάθε γεγονός διαγραφής (AFTER DELETE) μιας ή περισσοτέρων εγγραφών από τον πίνακα ΕΝΟΙΚΙΑΣΗ. Μέσα στο trigger προϋπάρχει εξάλλου, ένας virtual πίνακας με όνομα Deleted, που περιέχει τα πεδία και τις εγγραφές του πίνακα ΕΝΟΙΚΙΑΣΗ που διαγράφηκαν την τελευταία φορά. Με το ερώτημα επιλογής «SELECT IDΠελάτη,IDΔίσκου,
Από, Έως, GETDATE() FROM Deleted»
παίρνουμε όλα τα διεγραφέντα πεδία και την τρέχουσα ημερομηνία και ώρα που
διαγράφτηκαν. Με το ερώτημα επιλογής «Select IDΠελάτη,IDΔίσκου, Από, Έως, getdate() from Deleted» συλλέγουμε όλα τα διαγραφέντα πεδία με την τρέχουσα ημερομηνία και ώρα διαγραφής. Προκειμένου να επιβεβαιώσουμε την ορθή λειτουργία του εναύσματος, ανοίγουμε τον πίνακα ΕΝΟΙΚΙΑΣΗ με δεξί κλικ και επιλέγουμε Edit, για να εμφανιστούν οι τρεις εμπεριεχόμενες εγγραφές. Επιλέγοντας μια εγγραφή και πατώντας το πλήκτρο Delete, θα την διαγράψουμε. Αν τώρα πάμε στον πίνακα ENOIKΙΑΣΗ_DELETED και επιλέξουμε να δούμε τις εγγραφές του, θα βρούμε την εγγραφή που μόλις εισήχθηκε. Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.2.3.
Συναλλαγές/Transactions Συναλλαγή ή δοσοληψία (transaction) καλείται ένα σύνολο λειτουργιών/ ενεργειών ενημέρωσης, διαγραφής ή εισαγωγής γραμμών, το οποίο αποτελεί μια ενιαία λογική λειτουργική μονάδα. Συγκεκριμένα, εντάσσουμε σε μια συναλλαγή όλες εκείνες τις ενέργειες διαγραφής, ενημέρωσης ή εισαγωγής γραμμών που πρέπει να εκτελεστούν μαζί επιτυχώς. Αν τουλάχιστον μία ενέργεια αποτύχει, τότε καμία από τις άλλες επιτυχημένες ενέργειες δεν θα γίνει αποδεκτή από τον SQL Server (δεν θα γίνει Commit). Αντίθετα, ο SQL Server θα επιστρέψει στην αρχική του κατάσταση, σαν να μην είχε συμβεί καμία ενέργεια (Rollback). Ας μελετήσουμε, για παράδειγμα, τα παρακάτω δύο ερωτήματα, εκ των οποίων το πρώτο σκοπίμως είναι εσφαλμένο και δεν θα εκτελεστεί: “Εισάγετε
στον πίνακα ΕΝΟΙΚΙΑΣΗ την ενοικίαση ενός δίσκου με IDΔίσκου
2 και στην συνέχεια ενημερώστε τον πίνακα ΔΙΣΚΟΣ ώστε το πεδίο loaned
για τον δίσκο με κωδικό Id 2 να είναι ‘y’”. use DVDclub
Insert into
ΕΝΟΙΚΙΑΣΗ (IDΠελάτη,IDΔίσκου,Από,Έως) ------------------------------------- Προσοχή! Όπως γίνεται αντιληπτό από τα παραπάνω αποτελέσματα, το ερώτημα εισαγωγής νέας εγγραφής δεν εκτελέστηκε λόγω λάθους ημερομηνίας ('07/24/20133'), ενώ το ερώτημα ενημέρωσης εκτελέστηκε επιτυχώς. Αποτέλεσμα της πρώτης μη επιτυχημένης εκτέλεσης είναι να έχουμε ασυνέπεια δεδομένων στην βάση μας. Δηλαδή, ενώ ένας δίσκος δεν φαίνεται να έχει ενοικιαστεί στο πίνακα ΕΝΟΙΚΙΑΣΗ, φαίνεται νοικιασμένος στον πίνακα ΔΙΣΚΟΣ.
Το πρόβλημα που προέκυψε θα λυνόταν εύκολα με την χρήση ενός transaction
που θα εξασφάλιζε ότι οι δύο εντολές, εφόσον εκτελεστούν ταυτόχρονα
επιτυχώς, θα γίνουν COMMIT από τον SQL Server. To παραπάνω ερώτημα,
γίνεται με την βοήθεια της χρήσης της παρακάτω συναλλαγής: use DVDclub Declare @myerror as int Insert into ΕΝΟΙΚΙΑΣΗ (IDΠελάτη,IDΔίσκου,Από,Έως) Values
(2,2,'07/24/20133',Null) Update ΔΙΣΚΟΣ SET loaned='y' where Id= 2 Select @myerror = @@error Commit Transaction handle_error: Ακολούθως αναλύουμε τις πιο σημαντικές εντολές από το παραπάνω ερώτημα: Declare @myerror as int: Ορίζουμε
μια μεταβλητή, η οποία θα πάρει τιμή διάφορη του μηδενός στην περίπτωση
που υπάρχει σφάλμα σε μία από της δύο εντολές SQL που περιλαμβάνει η
συναλλαγή μας. Select @myerror = @@error: Η μεταβλητή μας
παίρνει τιμή διάφορη του μηδενός στην περίπτωση που υπάρξει σφάλμα
εκτέλεσης σε ένα από τα δύο ερωτήματα της συναλλαγής. Commit Transaction: Στην περίπτωση που δεν υπάρξουν
σφάλματα αποδεχόμαστε τα αποτελέσματα των δύο ερωτημάτων μας και τα
αποθηκεύουμε μόνιμα στην βάση δεδομένων μας. Rollback Transaction: Στην περίπτωση που υπάρξουν
σφάλματα δεν αποδεχόμαστε τα αποτελέσματα των δύο ερωτημάτων μας και
επιστρέφουμε στην προηγούμενη κατάσταση της βάση δεδομένων μας. Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.3. Βελτιστοποίηση Ερωτημάτων Πριν από την εκτέλεση ενός ερωτήματος από τον SQL Server υπάρχει το στάδιο της βελτιστοποίησης (optimization). Το αρχικό ερώτημα που υποβάλλει ο χρήστης μπορεί να βελτιστοποιηθεί από τον SQL Server με ισοδύναμες πράξεις της SQL, έτσι ώστε να μειωθεί ο χρόνος επεξεργασίας του. Στο στάδιο της βελτιστοποίησης προσδιορίζεται, επίσης, το πλάνο εκτέλεσης ερωτήματος (query execution plan), το οποίο περιέχει την σειρά εκτέλεσης των πράξεων SQL, τις μεθόδους που θα χρησιμοποιηθούν για την εκτέλεση κάθε πράξης κτλ. Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.3.1.
Παρακολούθηση του πλάνου εκτέλεσης ερωτήματος SQL Η βελτιστοποίηση ερωτήματος του SQL Server προσπαθεί να βρει το βέλτιστο
πλάνο εκτέλεσης για κάθε ερώτημα SQL. Συγκεκριμένα, o SQL Server αναλύει
κάθε ερώτημα, υπολογίζοντας τον αριθμό των διαφορετικών πλάνων εκτέλεσης,
και το κόστος του κάθε πλάνου, σε σχέση με τους πόρους που είναι
απαραίτητοι και το χρόνο επεξεργασίας που απαιτείται. Τελικά, επιλέγεται το λιγότερο ακριβό πλάνο εκτέλεσης. Αν θέλουμε να δούμε το πλάνο εκτέλεσης που ο SQL Server επέλεξε για το SQL ερώτημά μας , πρέπει να πληκτρολογήσουμε το ερώτημα και να πατήσουμε Ctrl+L ή, διαφορετικά, να επιλέξουμε με δεξί κλικ Display Estimated Execution Plan, όπως φαίνεται στην Εικόνα 4.3. Εικόνα 4.3 Για παράδειγμα, έστω ότι θέλουμε να απεικονισθεί το πλάνο εκτέλεσης για
το παρακάτω ερώτημα: “Προβάλετε
τους κωδικούς των ταινιών στις οποίες συμμετείχε η Grace Kelly ”. Η
εντολή SQL είναι: Select IDΤαινίας Έχοντας πληκτρολογήσει το παραπάνω ερώτημα και πατώντας Ctrl+L,
εμφανίζεται το περιεχόμενο της Εικόνας 4.4. Το εκτιμώμενο πλάνο εκτέλεσης παρέχει πρόσβαση σε επιπρόσθετες πληροφορίες. Αν θέλουμε να τις δούμε, πρέπει να κρατήσουμε το ποντίκι επάνω στο εικονίδιο της λειτουργίας, οπότε εμφανίζεται ένα αναδυόμενο παράθυρο που τις περιέχει, όπως φαίνεται στην Εικόνα 4.5. Οι πιο σημαντικές από τις πληροφορίες που εμπεριέχονται στο αναδυόμενο
παράθυρο της Εικόνας 4.5 είναι οι παρακάτω: Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου Το ευρετήριο είναι μια βοηθητική δομή που μας επιτρέπει να βελτιώσουμε την απόδοση των ερωτημάτων μας, μειώνοντας το μέγεθος της απαιτούμενης Ι/Ο δραστηριότητας για την ανάκτηση των ζητούμενων δεδομένων. Πράγματι, είναι χρήσιμο να ορίζουμε ευρετήρια για τα πεδία ενός πίνακα που χρησιμοποιούνται συχνά μέσα στο πεδίο Where των ερωτημάτων. Μ’ αυτόν τον τρόπο επιταχύνουμε την διαδιακασία επεξεργασίας των ερωτημάτων. Για παράδειγμα, έστω το παρακάτω ερώτημα: “Να
δημιουργηθεί ευρετήριο για το όνομα του συντελεστή”. Η εντολή SQL είναι: Create index IND_ΣΥΝΤΕΛΕΣΤΗΣ on ΣΥΝΤΕΛΕΣΤΗΣ( Όνομα ) Η ύπαρξη ενός ευρετηρίου, π.χ. στο πεδίο Όνομα του πίνακα ΣΥΝΤΕΛΕΣΤΗΣ, βοηθά το πλάνο εκτέλεσης, ώστε να γίνει ταχύτερα πρώτα η επιλογή των γραμμών βάσει της συνθήκης για το όνομα και, στη συνέχεια, η σύνδεση των πινάκων. Αν, για παράδειγμα, θέλουμε να βελτιώσουμε το πλάνο εκτέλεσης του ερωτήματος Q71 της Ενότητας 4.3.1., μπορούμε να εφαρμόσουμε το παρακάτω ερώτημα που κάνει χρήση ευρετηρίου για το όνομα του συντελεστή: “Να προβάλετε
τους κωδικούς των ταινιών στις οποίες συμμετείχε η Grace Kelly ”. Η εντολή
SQL είναι: select IDΤαινίας Τονίζεται ότι στο παραπάνω ερώτημα η χρήση του όρου WITH είναι προαιρετική. Η εφαρμογή του αξιοποιεί τη χρήση του ευρετηρίου (Q72) και, συνεπώς, επιτυγχάνει την επιτάχυνση ανάκτησης της πληροφορίας. Στο νέο πλάνο εκτέλεσης φαίνεται ότι η επιλογή των εγγραφών βάσει του ονόματος συντελεστή γίνεται πλέον μέσω του ευρετηρίου που δημιουργήσαμε. Επισημαίνουμε ότι σ’ αυτό το απλοποιημένο παράδειγμα, με τις λίγες εγγραφές που ανακτώνται από την βάση δεδομένων, δεν υπάρχει εμφανής διαφορά στους χρόνους εκτέλεσης μεταξύ των δύο ερωτημάτων Q71 και Q73. Γι’ αυτόν τον λόγο, στην επόμενη ενότητα θα υλοποιήσουμε ένα παράδειγμα όπου θα γίνεται αισθητή η χρονική μείωση στη εκτέλεση ενός ερωτήματος που επιτυγχάνεται με τη χρήση ευρετηρίων. Το ευρετήριο που κατασκευάσαμε στα προηγούμενα ερωτήματα στηρίχθηκε σε ένα μόνο πεδίο ενός πίνακα. Βέβαια, η δημιουργία σύνθετων ευρετηρίων που στηρίζονται σε περισσότερα του ενός πεδία ενός πίνακα κρίνεται αναγκαία σε πολλές περιπτώσεις, όπως, για παράδειγμα, όταν αυτά εμφανίζονται συχνά μέσα στο Order by ερωτημάτων. Για παράδειγμα, έστω το παρακάτω ερώτημα: “Να
δημιουργηθεί ευρετήριο για τον τύπο και την τιμή ενός δίσκου”. Η εντολή
SQL είναι: Create index IND_ΤΥΠΟΣ_ΤΙΜΗ on ΔΙΣΚΟΣ(Τύπος, Τιμή) Τέλος, στην SQL επιτρέπεται η διαγραφή ενός ευρετηρίου όπως φαίνεται στο παρακάτω
ερώτημα: “Να
διαγραφεί ο ευρετήριο IND_ΣΥΝΤΕΛΕΣΤΗΣ”. Η εντολή SQL είναι: Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.3.3.
Παράδειγμα βελτίωσης απόδοσης ερωτήματος με την χρήση ευρετηρίου Τα Ευρετήρια/ Indexes επιτρέπουν τη σημαντική βελτίωση του χρόνου εκτέλεσης ερωτημάτων. Αυτό, βέβαια, είναι κάτι που γίνεται αντιληπτό όταν έχουμε μεγάλο όγκο δεδομένων και τα ερωτήματα απαιτούν αρκετό χρόνο για να εκτελεστούν. Για παράδειγμα, στην Ενότητα 4.2.1. εισαγάγαμε, με την βοήθεια του ερωτήματος Q62, 5000 εγγραφές στον πίνακα ΠΕΛΑΤΗΣ. Έστω, λοιπόν, ότι θέλουμε να εκτελέσουμε το παρακάτω ερώτημα: “Να
βρεθούν τα ονόματα των πελατών που έχουν ως πρώτο χαρακτήρα το ‘1’ και να
ταξινομηθούν σε φθίνουσα σειρά”. Η εντολή SQL είναι: Select Όνομα Αν κάνουμε δεξί κλικ στον χώρο του ερωτήματος και επιλέξουμε Estimated Execution Plan, ο SQL Server θα μας δείξει ένα διάγραμμα στο οποίο φαίνονται τα στάδια εκτέλεσης του ερωτήματος και το κόστος σε πόρους (CPU & I/O disks) που το καθένα απαιτεί. Πράγματι, στην Εικόνα 4.4 φαίνεται πως το συνολικό κόστος του ερωτήματός μας αφορά τρία στάδια: Στην Εικόνα 4.6 παρατηρούμε την οδηγία που δίνεται γραμμένη με πράσινα γράμματα. Το σύστημα έχει εντοπίσει την απουσία ενός index για το πεδίο Όνομα του πίνακα ΠΕΛΑΤΗΣ, που αποτελεί και κριτήριο αναζήτησης αλλά και πεδίο ταξινόμησης. Μάλιστα, γίνεται εκτίμηση ότι αν υπήρχε ένας τέτοιος index, το impact στην βελτίωση της απόδοσης του ερωτήματός μας θα ήταν 20.8988 επί του συνολικού χρόνου. Προκειμένου, λοιπόν, να βελτιστοποιήσουμε το αρχικό μας ερώτημα, επιλέγουμε τον πίνακα ΠΕΛΑΤΗΣ και κάνουμε δεξί κλικ στο Design, σκοπεύοντας να φτιάξουμε έναν Index στο πεδίο Όνομα. Κάνουμε δεξί κλικ οπουδήποτε και επιλέγουμε Indexes/Keys, προκειμένου να εμφανιστεί το παράθυρο της Εικόνας 4.7. Εικόνα 4.7 Βλέπουμε ότι υπάρχει ήδη ένα ευρετήριο με όνομα PK_ΠΕΛΑΤΗΣ (του κύριου κλειδιού) που δημιουργείται αυτόματα όταν δηλώνουμε μια στήλη ως κύριο κλειδί. Αφού επιλέξουμε Add, στο πεδίο Columns επιλέγουμε το πεδίο Όνομα (με ASC ταξινόμηση), όπως φαίνεται στην Εικόνα 4.8. Αν θέλουμε το πεδίο να είναι μοναδικό στην ρύθμιση ‘Is Unique’, επιλέγουμε ‘Yes’. Εικόνα 4.8 Μετά τη δημιουργία του ευρετηρίου στο πεδίο Όνομα, μπορούμε να κάνουμε δεξί κλικ στον Query Editor και να επιλέξουμε το Estimated Execution Plan. Βλέπουμε τότε, όπως φαίνεται στην Εικόνα 4.9, ότι το πλάνο εκτέλεσης άλλαξε και πλέον ο SQL Server χρησιμοποιεί το index που ορίσαμε για την αναζήτηση και την ταξινόμηση του ερωτήματός μας χωρίς να σπαταλά χρόνο. Βέβαια, δεν μπορούμε να διακρίνουμε εύκολα τις διαφορές στους χρόνους εκτέλεσης ερωτημάτων σε έναν πίνακα με 5000 μόνο εγγραφές. Αντίθετα, οι διαφορές θα ήταν εμφανείς αν πειραματιζόμασταν με τον χρόνο εκτέλεσης του ερωτήματός μας, έχοντας πρώτα εισάγει δύο ή τρία εκατομμύρια πελάτες με την αποθηκευμένη διαδικασία της Ενότητας 4.2.1. Σημειώνουμε ότι στα επόμενα κεφάλαια, όπου διαπραγματευόμαστε τεχνικές αποθηκών και εξόρυξης δεδομένων, θα έχουμε πρόσβαση σε μεγαλύτερες βάσεις δεδομένων. Εικόνα 4.9 Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.4.
Εξαγωγή του κώδικα της βάσης δεδομένων Με τη λειτουργία του Generate Scripts μπορούμε να δημιουργήσουμε ένα αρχείο με εντολές SQL, οι οποίες θα κατασκευάζουν από την αρχή τη βάση δεδομένων μας. Το αρχείο αυτό μπορεί να λειτουργήσει και ως αντίγραφο ασφαλείας για τη βάση δεδομένων, καθώς με την εκτέλεσή του ανακτούμε το σχήμα της βάσης δεδομένων μας. Για να εξάγουμε τον κώδικα SQL, κάνουμε δεξί κλικ στη βάση δεδομένων DVDclub και, όπως φαίνεται στην Εικόνα 4.10, επιλέγουμε τη διαδρομή Tasks και, στη συνέχεια, Generate SQL Script. Εικόνα 4.10 Στο νέο παράθυρο, που φαίνεται στην Εικόνα 4.11, επιλέγουμε είτε «Script entire database and all database objects» είτε «select specific database objects». Για το παράδειγμά μας ζητάμε την δεύτερη επιλογή, επειδή θέλουμε κάποιο υποσύνολο των αντικειμένων της βάσης δεδομένων, και, στη συνέχεια, επιλέγουμε τα αντικείμενα που θέλουμε να εξαχθούν σε εντολές SQL. Εικόνα 4.11 Στη συνέχεια, πρέπει να επιλέξουμε τον τρόπο που θα αποθηκεύσουμε τον κώδικα που θα παραχθεί. Έχουμε τις παρακάτω εναλλακτικές επιλογές: Εμείς επιλέγουμε, για το παράδειγμά μας, την τρίτη επιλογή, όπως φαίνεται στην Εικόνα 4.12: Εικόνα 4.12 Τέλος, αφού πατήσουμε Next, σε ένα νέο παράθυρο ερωτήματος (αυτό που φαίνεται στην Εικόνα 4.13) προβάλλεται ο κώδικας που δημιουργεί όλα τα αντικείμενα της βάσης δεδομένων. Εικόνα 4.13 Προσοχή! Πρέπει πρώτα να έχει δημιουργηθεί η βάση δεδομένων (create), ώστε να μπορούν να φτιαχτούν όλα τα αντικείμενά της με τον κώδικα που μόλις παράχθηκε. Επιπλέον, θα πρέπει να τονιστεί ότι αυτή η διαδικασία δεν εξάγει τα δεδομένα της βάσης, παρά μόνο το σχήμα της. Προκειμένου να εξάγουμε και αυτά, θα πρέπει να πατήσουμε το κουμπί Advanced της Εικόνας 4.12 και, στη συνέχεια, στο option ‘Types of data to script’ να επιλέξουμε Schema and Data. Θυμίζουμε, επιπροσθέτως, ότι μπορούμε ανά πάσα στιγμή να παράξουμε τον κώδικα δημιουργίας της βάσης δεδομένων και από την επιλογή Script Database as, ακολουθώντας τη διαδρομή Create To και, στη συνέχεια, New Query Editor Window, όπως φαίνεται στην Εικόνα 4.14. Εικόνα 4.14 Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.5.
Εκχώρηση δικαιωμάτων πρόσβασης χρηστών στη βάση δεδομένων Στόχος της παρούσας ενότητας είναι η δημιουργία τριών διαβαθμισμένων χρηστών (manager, employee, customer), οι οποίοι θα έχουν διαφορετικά προνόμια πρόσβασης στη βάση DVDclub, σύμφωνα με τον παρακάτω πίνακα: Ενέργειες Πίνακας Select Insert Update Delete ΠΕΛΑΤΗΣ Manager Manager Manager Manager Employee Employee Employee ΕΝΟΙΚΙΑΣΗ Manager Manager Manager Manager Employee Employee Employee Customer ΣΥΝΤΕΛΕΣΤΗΣ Manager Manager Manager Manager Employee Employee Employee Customer ΔΙΣΚΟΣ Manager Manager Manager Manager Employee Employee Employee Customer Τ_Σ_Ρ Manager Manager Manager Manager Employee Employee Employee Customer ΤΑΙΝΙΑ Manager Manager Manager Manager Employee Employee Employee Customer ΡΟΛΟΣ Manager Manager Manager Manager Employee Employee Employee Customer Παρακάτω θα δημιουργήσουμε ένα login με το οποίο θα έχουμε πρόσβαση στον
sql server. Στη συνέχεια θα δημιουργήσουμε ένα χρήστη - employee, ο οποίος
θα αναφέρεται σε αυτό το login. Τονίζεται ότι με τον ίδιο τρόπο μπορούμε
να δημιουργήσουμε και άλλους χρήστες. Το login αφορά όλο τον sql server,
ενώ ο χρήστης – employee αφορά μόνο στη βάση DVDclub. create login employee with password = '123' go use DVDclub 4.5.1.
Εκχώρηση δικαιωμάτων χρήστη Για την εκχώρηση ενός προνομίου ο διαχειριστής της βάσης δεδομένων
πρέπει να χρησιμοποιεί την εντολή grant. Η γενική σύνταξη της εντολής είναι: GRANT privilege_name Οι όροι της παραπάνω εντολής επεξηγούνται ως εξής: • privilege_name
είναι το προνόμιο ή δικαίωμα πρόσβασης το οποίο εκχωρείται στο χρήστη.
Μερικά από τα δικαιώματα πρόσβασης είναι ALL, EXECUTE και SELECT Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.5.2.
Εκχώρηση δικαιωμάτων στο χρήστη Employee με κώδικα SQL. Σ’ αυτήν την ενότητα θα περιγράψουμε την εκχώρηση δικαιωμάτων στον χρήστη employee. Θέλουμε ο employee να έχει πλήρη πρόσβαση στη βάση δεδομένων, ώστε να μπορεί να δημιουργεί καινούριες εγγραφές και να τροποποιεί τις υπάρχουσες. grant select, insert, update on ΠΕΛΑΤΗΣ to employee Πατάμε το execute και δημιουργείται ο χρήστης employee
με τα επιθυμητά δικαιώματα. Για να επαληθεύσουμε τη δημιουργία του χρήστη,
κάνουμε κλικ στο Security και στη συνέχεια, κάνουμε κλικ στο φάκελο Logins
όπου πρέπει να υπάρχει ο νέος χρήστης, όπως φαίνεται στην Εικόνα 4.15. Εικόνα 4.15 Αφού επαληθεύσουμε τη δημιουργία του χρήστη employee, κάνουμε διπλό κλικ στον χρήστη. Στο παράθυρο που εμφανίζεται, επιλέγουμε το Securables, για να επαληθεύσουμε ότι έχει πάρει τα δικαιώματα που θέλουμε, όπως φαίνεται στην Εικόνα 4.16. Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.5.3.
Εκχώρηση δικαιωμάτων στο χρήστη Manager με γραφικό τρόπο Σε αυτή την ενότητα θα δείξουμε πώς γίνεται η εκχώρηση δικαιωμάτων στο χρήστη Manager με γραφικό τρόπο. Η διαδικασία είναι ακριβώς ίδια μ’ αυτήν που περιγράψαμε στην προηγουμένη ενότητα, μόνο που τώρα γίνεται με γραφικό τρόπο. Για να δημιουργήσουμε τον χρήστη Manager ακολουθούμε τα εξής βήματα: Βήμα 1: Καθώς βρισκόμαστε στο κεντρικό περιβάλλον του αριστερού pane στον sql server, επιλέγουμε τη διαδρομή Security -> New -> Login, όπως φαίνεται στην Εικόνα 4.17. Βήμα 2: Εμφανίζεται ένα νέο παράθυρο, το οποίο συμπληρώνουμε όπως φαίνεται στην Εικόνα 4.18. Πιο συγκεκριμένα, στο Login name πληκτρολογούμε manager. Επιλέγουμε με κλικ το Sql Server Authentication και συμπληρώνουμε (πληκτρολογώντας: 123) τα πεδία Password και Confirm password. Αποεπιλέγουμε το User must change password at next login. Συμπληρώνουμε το πεδίο Default database πληκτρολογώντας (επιλέγοντας) το όνομα της βάσης μας, δηλαδή: DVDclub. Πατάμε ΟΚ. Βήμα 3: Κάνουμε δεξί κλικ στο Security και επιλέγουμε τη διαδρομή New à User στο περιβάλλον της βάσης δεδομένων DVDclub, όπως φαίνεται στην Εικόνα 4.19. Βήμα 4: Πληκτρολογούμε την λέξη manager στα πεδία User name και Login name, όπως φαίνεται στην Εικόνα 4.20, και πατάμε ΟΚ. Βήμα 5: Στη συνέχεια κάνουμε δεξί κλικ στην επιλογή Security, επιλέγουμε το Users και, τέλος, στο χρήστη manager που δημιουργήσαμε, κάνουμε διπλό κλικ και επιλέγουμε το Securables (βλέπε Εικόνα 4.21). Εικόνα 4.21 Βήμα 6: Πατάμε το Search και, στο παράθυρο που εμφανίζεται, επιλέγουμε All objects belonging to the schema. Στο schema name επιλέγουμε dbo. Η Εικόνα 4.22 εμφανίζει τις επιλογές αυτού του βήματος. Μ’ αυτόν τον τρόπο εμφανίζονται οι πίνακες της βάσης, ώστε να εκχωρήσουμε τα δικαιώματα στον καθένα χωριστά. Για παράδειγμα, όπως φαίνεται στην Εικόνα 4.23), επιλέγουμε τον πίνακα Ενοικίαση και τσεκάρουμε όλα τα κουτάκια στη στήλη Grant. H ίδια διαδικασία πρέπει να γίνει σε όλους τους πίνακες. Εικόνα 4.23 Προσοχή!: Πρέπει, πριν πατήσουμε ΟΚ, να έχουμε κάνει τη διαδικασία του Βήματος 6 για όλους τους πίνακες. Διαφορετικά, θα πρέπει να ξαναμπούμε στο Securables και να επαναλάβουμε τη διαδικασία από το Βήμα 5 και μετά. Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.5.4.
Αφαίρεση δικαιωμάτων από τον χρήστη Στην ενότητα αυτή θα αναλύσουμε τον τρόπο λειτουργίας της εντολής revoke,
με την οποία αφαιρούμε τα δικαιώματα, που μπορεί να έχει κάποιος χρήστης
σε ένα ή περισσότερους πίνακες. Η σύνταξη της εντολής είναι η ακόλουθη: Ανάλυση της εντολής Revoke. REVOKE privilege_name Για παράδειγμα: Revoke select on employee FROM customer.
Αυτή η εντολή θα ανακαλέσει το δικαίωμα εμφάνισης που έχει ο χρήστης
customer στον πίνακα employee. Όταν γίνει η αναίρεση του δικαιώματος
εμφάνισης σε έναν πίνακα για έναν χρήστη, ο χρήστης δεν θα μπορεί να
εμφανίζει δεδομένα από αυτόν τον πίνακα. Ωστόσο , αν ο χρήστης έχει λάβει
δικαιώματα εμφάνισης για τον συγκεκριμένο πίνακα από περισσοτέρους από
έναν χρήστες, τότε θα μπορεί να εμφανίζει δεδομένα από αυτόν τον πίνακα
μέχρι αυτοί που τα έχουν εκχωρήσει να κάνουν αναίρεση. Δεν μπορεί να γίνει
αναίρεση δικαιωμάτων αν αυτά αρχικά δεν έχουν εκχωρηθεί από εσάς. Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου 4.5.5.
Άρνηση δικαιωμάτων σε χρήστη Σε αυτή την ενότητα θα εξηγήσουμε τον τρόπο λειτουργίας της εντολής
Deny, η οποία κάνει άρνηση δικαιωμάτων σε χρήστη. Η σύνταξη της Deny είναι
η εξής: Deny ALL | permission_name Παράδειγμα: Deny update On employee To customer Αυτή η εντολή απαγορεύει στο χρήστη customer να ενημερώσει τα περιεχόμενα
του πίνακα employee. Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου Hoffer, J. A., Venkatarama, R., & Topi, H. (2013). Modern Database Management, Prentice Hall. Μανωλόπουλος, Ι., & Παπαδόπουλος, Α. Ν. (2006). Συστήματα Βάσεων Δεδομένων: Θεωρία & Πρακτική Εφαρμογή, Αθήνα, Εκδόσεις Νέων Τεχνολογιών. Πίνακας Περιεχομένων /
Περιεχόμενα Κεφαλαίου
Η αντίστοιχη εντολή σε SQL είναι:
+
ID int NOT NULL ,
Τίτλος varchar(100) NOT NULL,
Έτος int NULL,
PRIMARY KEY (ID)) Το αποτέλεσμα του ερωτήματος είναι:
+
Command(s) completed successfully.
Η αντίστοιχη εντολή σε SQL είναι:
+
Select ID, Τίτλος, Έτος
From ΤΑΙΝΙΑ
Where Έτος < 1960 Το αποτέλεσμα του ερωτήματος είναι:
+
(2 row(s) affected)
Η αντίστοιχη εντολή σε SQL είναι:
+
Set Τιμή = Τιμή - 1
Where ID = 1 Το αποτέλεσμα του ερωτήματος είναι:
+
(1 row(s) affected)
Η αντίστοιχη εντολή σε SQL είναι:
+
Set Τύπος = 'BLU-RAY' Το αποτέλεσμα του ερωτήματος είναι:
+
(3 row(s) affected)
Η αντίστοιχη εντολή σε SQL είναι:
+
Where ID = 4<
Η αντίστοιχη εντολή σε SQL είναι:
+
Η αντίστοιχη εντολή σε SQL είναι:
+
add Γλώσσα char(30)
Η αντίστοιχη εντολή σε SQL είναι:
+
“Στον
πίνακα ΡΟΛΟΣ, ο τύπος του πεδίου Περιγραφή να γίνει varchar(100)”:
Η αντίστοιχη εντολή σε SQL είναι:
+
Alter column Περιγραφή varchar(100)
Η αντίστοιχη εντολή σε SQL είναι:
+
Η αντίστοιχη εντολή σε SQL είναι:
+
Η αντίστοιχη εντολή σε SQL είναι:
+
Drop table ΠΑΛΙΑ_ΤΑΙΝΙΑ
Η αντίστοιχη εντολή σε SQL είναι:
+
Drop database database_name
4.2.1.
Αποθηκευμένες διαδικασίες/Stored Procedures
Η αντίστοιχη εντολή σε SQL είναι:
+
go
Create procedure InsertRandomPelates @CustomersTotalNumber as int
as
begin
SET NOCOUNT ON;
Declare @CustomersName as varchar(30)
Declare @counter as int
Set @counter=4
while @counter <= @CustomersTotalNumber
begin
Set @CustomersName = cast(@counter as varchar) + '-Name'
INSERT INTO ΠΕΛΑΤΗΣ (Id, Όνομα) VALUES(@counter, @CustomersName)
end
end
Εικόνα 4.1
Η αντίστοιχη εντολή σε SQL είναι:
+
go
Create Procedure myphone1
as
Select Τηλέφωνο
From ΠΕΛΑΤΗΣ
Η αντίστοιχη εντολή σε SQL είναι:
+
go
Create Procedure myphone2
@lastname varchar(40)
as
Select Τηλέφωνο
From ΠΕΛΑΤΗΣ
Where Όνομα = @lastname
ON {table | view}
{FOR | AFTER } { [DELETE] [,] [INSERT] [,] [UPDATE] }
AS
sql_statement
Η αντίστοιχη εντολή σε SQL είναι:
+
go
Alter Table ΔΙΣΚΟΣ
add loaned char(1) default 'n'
Η αντίστοιχη εντολή σε SQL είναι:
+
go
UPDATE ΔΙΣΚΟΣ
SET loaned='n'
Η αντίστοιχη εντολή σε SQL είναι:
+
go
Create Trigger loaned_updater ON
ΕΝΟΙΚΙΑΣΗ For Insert
as
Update ΔΙΣΚΟΣ Set loaned = 'y'
From ΔΙΣΚΟΣ, INSERTED i
Where ΔΙΣΚΟΣ.ID = i.IDΔίσκου
Η αντίστοιχη εντολή σε SQL είναι:
+
ON ΕΝΟΙΚΙΑΣΗ AFTER DELETE
AS
BEGIN
SET NOCOUNT ON
([IDΠελάτη],[IDΔίσκου] ,[Από] ,[Έως] ,[DateTimeOfDelete])
END
Η αντίστοιχη εντολή σε SQL είναι:
+
go
Values (2,2,'07/24/20133',Null)
Go
Update ΔΙΣΚΟΣ SET loaned=’y’ where Id= 2
Go Το αποτέλεσμα του ερωτήματος είναι:
+
Msg 241, Level 16, State 1, Line 1
Convers ion failed when
converting date and/or time from character string.
(1 row(s) affected)
Η αντίστοιχη εντολή σε SQL είναι:
+
go
Select @myerror = 0
Begin Transaction
IF @myerror != 0 GOTO handle_error
IF @myerror != 0
Begin
print 'Error in Script. Rollback is applied'
Rollback Transaction
End
Η αντίστοιχη εντολή σε SQL είναι:
+
from Τ_Σ_Ρ, ΣΥΝΤΕΛΕΣΤΗΣ
where ΣΥΝΤΕΛΕΣΤΗΣ.ID = Τ_Σ_Ρ.IDΣυντελεστή and
Όνομα='Grace Kelly' Το αποτέλεσμα του ερωτήματος είναι:
+
Εικόνα 4.4
Εικόνα 4.5
Η αντίστοιχη εντολή σε SQL είναι:
+
Η αντίστοιχη εντολή σε SQL είναι:
+
from Τ_Σ_Ρ, ΣΥΝΤΕΛΕΣΤΗΣ with(INDEX(IND_ΣΥΝΤΕΛΕΣΤΗΣ))
where ΣΥΝΤΕΛΕΣΤΗΣ.ID = Τ_Σ_Ρ.IDΣυντελεστή and Όνομα='Grace Kelly'
Η αντίστοιχη εντολή σε SQL είναι:
+
Η αντίστοιχη εντολή σε SQL είναι:
+
Drop index ΣΥΝΤΕΛΕΣΤΗΣ.IND_ΣΥΝΤΕΛΕΣΤΗΣ
Η αντίστοιχη εντολή σε SQL είναι:
+
From ΠΕΛΑΤΗΣ
Where Όνομα like '1%'
Order by Όνομα desc
Εικόνα 4.6
Πίνακας 4.2
create user employee for login employee with
default_schema=[dbo]
Η αντίστοιχη εντολή σε SQL είναι:
+
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
• object_name
είναι το όνομα ενός αντικειμένου της βάσης (πίνακας, όψη)
• user_name
είναι το όνομα του χρήστη στον οποίο εκχωρείται ένα δικαίωμα πρόσβασης
• PUBLIC
χρησιμοποιείται για την εκχώρηση δικαιωμάτων πρόσβασης σε όλους τους
χρήστες
• ROLES είναι
ένα σύνολο δικαιωμάτων που ομαδοποιούνται
• WITH GRANT
OPTION είναι προαιρετικό και επιτρέπει σε ένα
χρήστη να εκχωρήσει δικαιώματα πρόσβασης σε άλλους χρήστες.
grant select, insert, update on ΕΝΟΙΚΙΑΣΗ to employee
grant select, insert, update on ΣΥΝΤΕΛΕΣΤΗΣ to employee
grant select, insert, update on ΔΙΣΚΟΣ to employee
grant select, insert, update on Τ_Σ_Ρ to employee
grant select, insert, update on ΤΑΙΝΙΑ to employee
grant select, insert, update on ΡΟΛΟΣ to employee
Εικόνα 4.16
Εικόνα 4.17
Εικόνα 4.18
Εικόνα 4.19
Εικόνα 4.20
Εικόνα 4.22
ON object_name
FROM {user_name |PUBLIC |role_name}
On object_name
TO user_name