Πώς γράφουμε προχωρημένα SQL queries
Πιστοποιήσεις:
- Πώς συλλέγουμε to actual execution plan από τα queries με χρήση Extended Event και πως διαβάζουμε τα δεδομένα του - 2 Δεκέμβριος 2024
- Πώς βρίσκουμε τι δικαιώματα έχει ένας χρήστης σε βάση δεδομένων της Oracle - 1 Νοέμβριος 2024
- Πώς ενεργοποιούμε το Unified Auditing σε Oracle Database - 7 Οκτώβριος 2024
Σε προηγούμενο άρθρο είχαμε αναλύσει τι είναι η SQL (Structured Query Language) και πως γίνεται η χρήση της με βασικά παραδείγματα.
Σε αυτό το άρθρο θα δούμε πιο προχωρημένες λειτουργίες της.
O κώδικας είναι γραμμένος με την προέκταση Transact-SQL που γίνεται χρήση από στον SQL Server. Σε άλλα RDBMS η σύνταξη ενδέχεται να διαφέρει όπως π.χ. στην Oracle που κάνει χρήση την PL/SQL.
Η δομή και τα αρχικά δεδομένα για τα παραδείγματα μας είναι η κάτωθι:
create table pinakas (
id INT identity (1,1) primary key,
onoma varchar(100),
tilefono INT,
epitheto varchar(100)
)
insert into pinakas values
('Nikos','215294882′,null),
('Kwstas','210772049′,null),
('Kwstas','210772049′,null);
Correlated subquery
To Correlated subquery είναι το εσωτερικό subquery που εξαρτάται από το εξωτερικό query και κάνει σύγκρισή το ένα με το άλλο εγγραφή εγγραφή.
Θέλουμε να ενημερώσουμε το επίθετο ώστε η κάθε εγγραφή να έχει για επίθετο το όνομα με _Epi. Θα χρειαστεί να δηλώσουμε τον πίνακα που καλούμε για δεύτερη φορά με ένα άλλο alias όνομα πχ (from pinakas as p2) και θα πρέπει να ορίσουμε ότι το id από τον πίνακα να είναι ίσο με το ίδιο πεδίο στο alias του ίδιου πίνακα ώστε να γίνει αντιστοίχιση γραμμή γραμμή:
update pinakas set epitheto = (select onoma+'_Epi' from pinakas as p2 where pinakas.id = p2.id) select * from pinakas
Non-correlated subquery
Το Non-correlated subquery είναι το subquery που εκτελείται ανεξάρτητα από το εξωτερικό query. Εκτελείται πρώτα το εσωτερικό subquery και περνάει τα αποτελέσματα στο εξωτερικό query:
select * from pinakas where id = (select 4,6,7)
Αφού παίξαμε με το subquery ας σβήσουμε τη στήλη του επιθέτου με ένα drop (ddl statement):
alter table pinakas drop column epitheto select * from pinakas
Case
Μπορούμε ανάλογα με το τι τιμή έχει η κάθε εγγραφή να φέρουμε κάτι άλλο ανά περίπτωση;
Ναι μπορούμε, με την χρήση της CASE:
select
CASE
WHEN onoma = 'Kwstas' THEN 'Kwnstantinos'
WHEN onoma ='Nikos' THEN 'Nikolaos'
ELSE 'Asxetos'
END AS 'EinaiOdikosMas'
from pinakas
Loops
Μέσω SQL μπορούμε να καλέσουμε βρόγχους όπως ένα κλασικό while. Θα πρέπει να δηλώσουμε μια μεταβλητή(@i) και να της δώσουμε μία τιμή(set). Τέλος θα πρέπει να είναι μέσα σε ένα transaction begin/end:
DECLARE @i AS INT
SET @i=5
while(@i>0)
BEGIN
PRINT('test'+@i)
SET @i=@i-1
END
Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value 'test' to data type int.
Total execution time: 00:00:00.001Ουπς… Κάτι δεν πήγε καλά τί άραγε;
Πολύ απλά προσπαθήσαμε να ενώσουμε ένα κείμενο με έναν αριθμό μετατρέποντας το κείμενο σε αριθμό που αυτό δεν γίνεται οπότε τι κάνουμε;
Cast
Με το cast function μπορούμε να μετατρέψουμε οποιοδήποτε πεδίο σε συγκεκριμένο τύπο δεδομένων πχ πεδίο αριθμού σε πεδίο κειμένου (όπως γίνεται και με την T-SQL function convert) . Για να λύσουμε το μήνυμα λάθους που μας εμφανίστηκε πολύ απλά κάνουμε cast το πεδίο που είναι αριθμός σε varchar ώστε να λογιστεί ως κείμενο:
DECLARE @i AS INT
SET @i=5
while(@i>0)
BEGIN
PRINT('test'+cast(@i as varchar(2)))
SET @i=@i-1
END
test5
test4
test3
test2
test1
Total execution time: 00:00:00.001Dynamic Query
Μπορούμε να έχουμε κάλλιστα κώδικα σε μεταβλητές και αναλόγως την περίπτωση να τις ενώσουμε και να τις καλέσουμε ως ένα αίτημα. Πως; Με το Dynamic Query.
Θα πρέπει να έχουμε ορίζει μεταβλητές που θα περιέχουν τον κάθε κομμάτι του κώδικα που θέλουμε, έπειτα με την εντολή EXECUTE και τις μεταβλητές αυτές μέσα σε παρένθεση θα εκτελεστούν σαν να ήτανε ένα ολοκληρωμένο query.
Με print βλέπουμε πως θα είναι το σύνολο των μεταβλητών:
DECLARE @part1 as VARCHAR(100), @part2 as VARCHAR(100) set @part1='SELECT onoma' set @part2='from pinakas' print(@part1+@part2); EXECUTE (@part1+@part2)
Nested Select
Με το nested select μπορούμε να κάνουμε select όχι από ένα πίνακα ή view αλλά από ένα άλλο select. Αυτό το select γίνεται να είναι ακόμα και από τον ίδιο πίνακα.
Ας δούμε ένα παράδειγμα:
select onoma from (select onoma,tilefono from pinakas) as nested
Common Table Expression ή αλλιώς CTE
Όταν θέλουμε ένα query να παραμείνει στην μνήμη με υψηλή απόδοση καθώς επαναλαμβάνεται, επιστέφοντας υποσύνολα δεδομένων έως όταν ολοκληρωθούν όλα το CTE είναι ο τρόπος.
Ας δούμε ένα απλό παράδειγμα χωρίς ιδιαίτερη χρήση:
Θα ορίσουμε ένα CTE με όνομα dokimi_cte και θα το καλέσουμε με select στην αμέσως επόμενη εντολή:
with dokimi_cte as (select onoma from pinakas group by onoma) select * from dokimi_cte
Cursors
Με τους cursors έχουμε την δυνατότητα να κάνουμε scrolling μπρος πίσω στις εγγραφές ώστε να χτίσουμε ένα επιθυμητό αποτέλεσμα.
Στο συγκεκριμένο παράδειγμα θέλαμε για το κάθε όνομα που έχει ο πίνακας να το εκτυπώσουμε προσθέτοντας έξτρα κείμενο πριν.
Θα φτιάξουμε έναν κέρσορα με όνομα kersoras ο οποίος θα γεμίζει με όλο το πλήθος των ονομάτων που περιέχει ο πίνακας, για όσο υπάρχουν ακόμα εγγραφές θα συνεχίζει να κάνει action και όταν τελειώνει θα πηγαίνει στην επόμενη:
declare @names varchar(1000) declare kersoras CURSOR for select onoma from pinakas; open kersoras FETCH NEXT FROM kersoras INTO @names WHILE @@FETCH_STATUS = 0 BEGIN print 'Mr '+@names FETCH NEXT FROM kersoras INTO @names END CLOSE kersoras DEALLOCATE kersoras
Mr Nikos
Mr Kwstas
Total execution time: 00:00:00.008Select into (copy/backup έναν πίνακα σε έναν άλλον)
Με το set εντολών select into έχουμε την δυνατότητα να κάνουμε copy τις εγγραφές δημιουργώντας ένα καινούργιο πίνακα αντίστοιχο με αυτόν που καλέσαμε τα δεδομένα:
select * into pinakas2 from pinakas select * from pinakas2
MERGE
Με την εντολή MERGE μπορούμε να ενημερώσουμε έναν πίνακα (target) με τιμές ενός άλλου πίνακα (source).
Ας πούμε ότι έχουμε τον πίνακα pinakas(target) και θέλουμε να ενημερώσουμε τα τηλέφωνα με καινούργια. Επίσης θέλουμε να περάσουμε καινούργιους πελάτες αν δεν υπάρχουν ήδη. Η πληροφορία θα βρίσκεται στον πίνακα prosorinos_pinakas(source).
Ας δούμε αναλυτικά τους πίνακες:
select * from pinakas
insert into prosorinos_pinakas values
('Kwstas','690909090'),
('Giorgos','210555555')
select * from prosorinos_pinakas
Θα χρησιμοποιήσουμε συγκριτικό πεδίο το όνομα του πελάτη, όπου ταιριάξει θα ενημερώσει το τηλέφωνο με το καινούργιο (από το prosorinos_pinakas), όποιο όνομα δεν βρει θα προσθέσει ολόκληρη τη εγγραφή στον αρχικό μας πίνακα (pinakas):
MERGE pinakas t
USING prosorinos_pinakas s
ON (s.onoma = t.onoma)
WHEN MATCHED
THEN UPDATE SET
t.tilefono = s.tilefono
WHEN NOT MATCHED BY TARGET
THEN INSERT (onoma,tilefono)
VALUES (s.onoma,s.tilefono);
Τώρα αν ξαναδούμε τον πίνακα θα έχουμε άλλο τηλέφωνο στον ‘Kwstas’ και θα έχουμε τον καινούργιο πελάτη με όνομα ‘Giorgos’ που δεν υπήρχε:
select * from pinakas
