Μετατροπές NVL για διάφορους τύπους δεδομένων. Δηλώσεις κλάδου στην εντολή SELECT περιγραφή Oracle nvl
Αγγίσαμε τις ένθετες λειτουργίες λίγο νωρίτερα, τώρα θα τις εξετάσουμε λίγο πιο αναλυτικά. Θα εξετάσουμε επίσης συναρτήσεις για εργασία με την τιμή NULL και συναρτήσεις που βοηθούν στην υλοποίηση της λειτουργίας διακλάδωσης σε ένα ερώτημα.
Ένθετες συναρτήσεις
Οι ένθετες συναρτήσεις χρησιμοποιούν την τιμή επιστροφής μιας συνάρτησης ως παράμετρο εισόδου σε μια άλλη συνάρτηση. Οι συναρτήσεις επιστρέφουν πάντα μόνο μία τιμή. Επομένως, μπορείτε να αντιμετωπίζετε το αποτέλεσμα μιας κλήσης συνάρτησης ως κυριολεκτική τιμή όταν το χρησιμοποιείτε ως παράμετρο σε μια άλλη κλήση συνάρτησης. Οι συναρτήσεις συμβολοσειράς μπορούν να ενσωματωθούν σε οποιοδήποτε επίπεδο ένθεσης. Μια κλήση συνάρτησης μοιάζει με αυτό
Συνάρτηση1(παράμετρος1,παράμετρος2,…) = αποτέλεσμα
Η αντικατάσταση μιας παραμέτρου συνάρτησης με μια κλήση σε μια άλλη συνάρτηση μπορεί να οδηγήσει σε εκφράσεις όπως
F1(param1.1, F2(param2.1, param2.2, F3(param3.1)), param1.3)
Οι ένθετες συναρτήσεις αξιολογούνται πρώτα πριν χρησιμοποιηθούν τα αποτελέσματά τους ως τιμές εισόδου για άλλες συναρτήσεις. Οι συναρτήσεις αξιολογούνται από το βαθύτερο επίπεδο ένθεσης μέχρι το πάνω από αριστερά προς τα δεξιά. Η προηγούμενη έκφραση εκτελείται ως εξής
- Η συνάρτηση F3(param1) αξιολογείται και η τιμή επιστροφής χρησιμοποιείται ως τρίτη παράμετρος για τη συνάρτηση 2, ας την ονομάσουμε param2.3
- Στη συνέχεια αξιολογείται η συνάρτηση F2(param1, param2.2, param2.3) και η επιστρεφόμενη τιμή χρησιμοποιείται ως δεύτερη παράμετρος της συνάρτησης F1 - param1.2
- Τέλος, αξιολογείται η συνάρτηση F1(param1, param2, param1.3) και το αποτέλεσμα επιστρέφεται στο καλούν πρόγραμμα.
Έτσι, η συνάρτηση F3 βρίσκεται στο τρίτο επίπεδο ένθεσης.
Ας εξετάσουμε το αίτημα
επιλέξτε next_day(last_day(sysdate)-7, 'tue') από dual;
- Υπάρχουν τρεις συναρτήσεις σε αυτό το ερώτημα, από το κατώτερο επίπεδο στο ανώτερο επίπεδο - SYSDATE, LAST_DAY, NEXT_DAY. Το αίτημα εκτελείται ως εξής
- Η ένθετη συνάρτηση SYSDATE εκτελείται. Επιστρέφει την τρέχουσα ώρα συστήματος. Ας υποθέσουμε ότι η τρέχουσα ημερομηνία είναι η 28η Οκτωβρίου 2009
- Στη συνέχεια, υπολογίζεται το αποτέλεσμα της συνάρτησης δεύτερου επιπέδου LAST_DAY. Η LAST_DATE('28-OCT-2009') επιστρέφει την τελευταία ημέρα του Οκτωβρίου 2009, δηλαδή 31 Οκτωβρίου 2009.
- Στη συνέχεια, αφαιρούνται επτά ημέρες από αυτήν την ημερομηνία - αποδεικνύεται 24 Οκτωβρίου.
- Τέλος, αξιολογείται η συνάρτηση NEXT_DAY('24-OCT-2009', 'Tue') και το ερώτημα επιστρέφει την τελευταία Τρίτη του Οκτωβρίου - που στο παράδειγμά μας είναι 27-OCT-2009.
Είναι αρκετά δύσκολο να κατανοήσουμε και να δημιουργήσουμε σύνθετες εκφράσεις χρησιμοποιώντας πολλές ένθετες κλήσεις συναρτήσεων, αλλά αυτό έρχεται με τον χρόνο και την πρακτική. Μπορείτε να χωρίσετε τέτοιες εκφράσεις σε μέρη και να τις δοκιμάσετε ξεχωριστά. Ο πίνακας DUAL είναι πολύ χρήσιμος για τον έλεγχο των ερωτημάτων και των αποτελεσμάτων κλήσης συναρτήσεων. Μπορείτε να δοκιμάσετε και να διορθώσετε μικρά στοιχεία, τα οποία στη συνέχεια συνδυάζονται σε μια μεγάλη επιθυμητή έκφραση.
Λειτουργίες διακλάδωσης
Οι συναρτήσεις διακλάδωσης, γνωστές και ως IF-THEN-ELSE, χρησιμοποιούνται για τον προσδιορισμό της διαδρομής εκτέλεσης ανάλογα με ορισμένες περιστάσεις. Οι συναρτήσεις διακλάδωσης επιστρέφουν διαφορετικά αποτελέσματα με βάση το αποτέλεσμα της αξιολόγησης συνθήκης. Η ομάδα τέτοιων συναρτήσεων περιλαμβάνει συναρτήσεις για εργασία με την τιμή NULL: NVL, NVL2, NULLIF και COALESCE. Και επίσης οι κοινές συναρτήσεις που αντιπροσωπεύονται από τη συνάρτηση DECODE και την έκφραση CASE. Η συνάρτηση DECODE είναι μια συνάρτηση Oracle, ενώ η έκφραση CASE υπάρχει στο πρότυπο ANSI SQL.
Λειτουργία NVL
Η συνάρτηση NVL ελέγχει την τιμή μιας στήλης ή μιας έκφρασης οποιουδήποτε τύπου δεδομένων έναντι του NULL. Εάν η τιμή είναι NULL, επιστρέφει μια εναλλακτική προεπιλεγμένη τιμή που δεν είναι NULL, διαφορετικά επιστρέφει την αρχική τιμή.
Η συνάρτηση NVL έχει δύο απαιτούμενες παραμέτρους και η σύνταξη είναι NVL(original, ifnull) όπου original είναι η αρχική τιμή προς έλεγχο και ifnull είναι το αποτέλεσμα που επιστρέφεται από τη συνάρτηση εάν η αρχική τιμή είναι NULL. Ο τύπος δεδομένων των παραμέτρων ifnull και original πρέπει να είναι συμβατός. Δηλαδή, είτε ο τύπος δεδομένων πρέπει να είναι ο ίδιος είτε πρέπει να είναι δυνατή η σιωπηρή μετατροπή τιμών από έναν τύπο σε άλλο. Η συνάρτηση NVL επιστρέφει μια τιμή του ίδιου τύπου δεδομένων με τον τύπο δεδομένων της αρχικής παραμέτρου. Ας εξετάσουμε τρία ερωτήματα
Ερώτημα 1: επιλέξτε nvl(1234) από το dual.
Ερώτημα 2: επιλέξτε nvl(null, 1234) από το dual.
Ερώτημα 3: επιλέξτε nvl(substr('abc', 4), 'No substring exist') από το dual;
Δεδομένου ότι η συνάρτηση NVL απαιτεί δύο παραμέτρους, το αίτημα 1 θα επιστρέψει το σφάλμα ORA-00909: μη έγκυρος αριθμός ορισμάτων. Το ερώτημα 2 θα επιστρέψει 1234 επειδή η τιμή NULL είναι επιλεγμένη και είναι NULL. Το ερώτημα τρία χρησιμοποιεί μια ένθετη συνάρτηση SUBSTR που επιχειρεί να εξαγάγει τον τέταρτο χαρακτήρα από μια συμβολοσειρά μήκους τριών χαρακτήρων, επιστρέφοντας NULL και τη συνάρτηση NVL που επιστρέφει τη συμβολοσειρά "Δεν υπάρχει sbusstring".
Η λειτουργία NVL είναι πολύ χρήσιμη όταν εργάζεστε με αριθμούς. Χρησιμοποιείται για τη μετατροπή τιμών NULL σε 0, έτσι ώστε οι αριθμητικές πράξεις σε αριθμούς να μην επιστρέφουν NULL
Λειτουργία NVL2
Η συνάρτηση NVL2 παρέχει περισσότερη λειτουργικότητα από το NVL, αλλά χρησιμεύει επίσης για το χειρισμό τιμών NULL. Ελέγχει την τιμή μιας στήλης ή μιας έκφρασης οποιουδήποτε τύπου έναντι του NULL. Εάν η τιμή δεν είναι NULL, τότε επιστρέφεται η δεύτερη παράμετρος, διαφορετικά επιστρέφεται η τρίτη παράμετρος, σε αντίθεση με τη συνάρτηση NVL, η οποία σε αυτήν την περίπτωση επιστρέφει την αρχική τιμή.
Η συνάρτηση NVL2 έχει τρεις απαιτούμενες παραμέτρους και η σύνταξη είναι NVL2 (original, ifnotnull, ifnull), όπου original είναι η τιμή που ελέγχεται, ifnotnull είναι η τιμή που επιστρέφεται εάν το original δεν είναι NULL και ifnull είναι η τιμή που επιστρέφεται εάν το original είναι NULL. Οι τύποι δεδομένων των παραμέτρων ifnotnull και ifnull πρέπει να είναι συμβατοί και δεν μπορούν να είναι τύπου LONG. Ο τύπος δεδομένων που επιστρέφεται από τη συνάρτηση NVL2 είναι ίσος με τον τύπο δεδομένων της παραμέτρου ifnotnull. Ας δούμε μερικά παραδείγματα
Ερώτημα 1: επιλέξτε nvl2(1234, 1, 'a string') από το dual.
Ερώτημα 2: επιλέξτε nvl2(null, 1234, 5678) από το dual.
Ερώτημα 3: επιλέξτε nvl2(substr('abc', 2), 'Not bc', 'No substring') από το dual;
Η παράμετρος ifnotnull στο αίτημα 1 είναι ένας αριθμός και η παράμετρος ifnull είναι μια συμβολοσειρά. Επειδή οι τύποι δεδομένων δεν είναι συμβατοί, επιστρέφεται το σφάλμα "ORA-01722: μη έγκυρος αριθμός". Το ερώτημα δύο επιστρέφει την παράμετρο ifnull, αφού το αρχικό είναι NULL και το αποτέλεσμα θα είναι 5678. Το ερώτημα τρία χρησιμοποιεί τη συνάρτηση SUBSTR που επιστρέφει 'bc' και καλεί NVL2('bc','Not bc','No substring') - η οποία επιστρέφει η παράμετρος ifnotnull – «Όχι bc».
Λειτουργία NULLIF
Η συνάρτηση NULLIF ελέγχει εάν δύο τιμές είναι ίδιες. Εάν είναι τα ίδια, επιστρέφεται NULL, διαφορετικά επιστρέφεται η πρώτη παράμετρος. Η συνάρτηση NULLIF έχει δύο απαιτούμενες παραμέτρους και η σύνταξη είναι NULLIF (ifunequal, σύγκριση_αντικείμενο). Η συνάρτηση συγκρίνει δύο παραμέτρους και εάν είναι ίδιες, επιστρέφεται NULL, διαφορετικά η παράμετρος είναι αν ίση. Ας εξετάσουμε τα αιτήματα
Ερώτημα 1: επιλέξτε nullif(1234, 1234) από το dual.
Το ερώτημα ένα επιστρέφει NULL αφού οι παράμετροι είναι πανομοιότυπες. Οι συμβολοσειρές στο Ερώτημα 2 δεν μετατρέπονται σε ημερομηνία, αλλά συγκρίνονται ως συμβολοσειρές. Δεδομένου ότι οι συμβολοσειρές έχουν διαφορετικά μήκη, η παράμετρος ifunequal επιστρέφεται 24-ΙΟΥΛ-2009.
Στο Σχήμα 10-4, η συνάρτηση NULLIF είναι ένθετη στη συνάρτηση NVL2. Η συνάρτηση NULLIF με τη σειρά της χρησιμοποιεί τις συναρτήσεις SUBSTR και UPPER ως μέρος μιας έκφρασης στην παράμετρο ifunequal. Η στήλη EMAIL συγκρίνεται με αυτήν την έκφραση, η οποία επιστρέφει το πρώτο γράμμα του ονόματος σε συνδυασμό με το επίθετο για υπαλλήλους των οποίων το όνομα έχει μήκος 4 χαρακτήρες. Όταν αυτές οι τιμές είναι ίσες, το NULLIF θα επιστρέψει NULL, διαφορετικά θα επιστρέψει την τιμή της παραμέτρου ifunequal. Αυτές οι τιμές χρησιμοποιούνται ως παράμετρος για τη συνάρτηση NVL2. Το NVL2 με τη σειρά του επιστρέφει μια περιγραφή για το εάν τα συγκριτικά στοιχεία ταιριάζουν ή όχι.
Εικόνα 10-4 – Χρήση της συνάρτησης NULLIF
Συνάρτηση COALESCE
Η συνάρτηση COALESCE επιστρέφει την πρώτη τιμή που δεν είναι NULL από τη λίστα παραμέτρων. Εάν όλες οι παράμετροι είναι NULL, τότε επιστρέφεται NULL. Η συνάρτηση COALESCE έχει δύο απαιτούμενες παραμέτρους και οποιονδήποτε αριθμό προαιρετικών παραμέτρων και η σύνταξη είναι COALESCE(expr1, expr2, ..., exprn) όπου το αποτέλεσμα θα είναι expr1 εάν η τιμή του expr 1 δεν είναι NULL, διαφορετικά το αποτέλεσμα θα είναι expr2 αν δεν είναι NULL κ.λπ. Το COALESCE είναι ίσο στην έννοια με τις ένθετες συναρτήσεις NVL
COALESCE(expr1, expr2) = NVL(expr1, expr2)
COALESCE(expr1, expr2, expr3) = NVL(expr1,NVL(expr2, expr3))
Ο τύπος δεδομένων της τιμής που επιστρέφεται εάν βρεθεί μια τιμή που δεν είναι NULL είναι ίσος με τον τύπο δεδομένων της πρώτης τιμής που δεν είναι NULL. Για να αποφευχθεί το σφάλμα «ORA-00932: ασυνεπείς τύποι δεδομένων», όλες οι παράμετροι που δεν είναι NULL πρέπει να είναι συμβατές με την πρώτη παράμετρο που δεν είναι NULL. Ας δούμε τρία παραδείγματα
Ερώτημα 1: επιλέξτε coalesce(null, null, null, 'a string') από το dual.
Ερώτημα 2: επιλέξτε coalesce(null, null, null) από το dual.
Ερώτημα 3: επιλέξτε coalesce(substr('abc', 4), 'Not bc', 'No substring') από το dual;
Το ερώτημα 1 επιστρέφει την τέταρτη παράμετρο: μια συμβολοσειρά, καθώς είναι η πρώτη παράμετρος που δεν είναι NULL. Το ερώτημα δύο επιστρέφει NULL επειδή όλες οι παράμετροι είναι NULL. Το ερώτημα 3 αξιολογεί την πρώτη παράμετρο, λαμβάνει την τιμή NULL και επιστρέφει τη δεύτερη παράμετρο, καθώς είναι η πρώτη παράμετρος που δεν είναι NULL.
Οι παράμετροι της συνάρτησης NVL2 μπορεί να προκαλούν σύγχυση εάν είστε ήδη εξοικειωμένοι με τη συνάρτηση NVL. Το NVL(original, ifnull) επιστρέφει το αρχικό εάν η τιμή δεν είναι NULL, διαφορετικά το ifnull. Το NVL2(original, ifnotnull, ifnull) επιστρέφει το ifnotnull εάν το αρχικό δεν είναι NULL διαφορετικά το ifnull. Η σύγχυση προέρχεται από το γεγονός ότι η δεύτερη παράμετρος της συνάρτησης NVL είναι ifnull, ενώ η συνάρτηση NVL2 είναι ifnotnull. Επομένως, μην βασίζεστε στη θέση της παραμέτρου στη συνάρτηση.
Λειτουργία ΑΠΟΚΩΔΙΚΟΠΟΙΗΣΗ
Η συνάρτηση DECODE υλοποιεί τη λογική if-then-else δοκιμάζοντας τις δύο πρώτες παραμέτρους για ισότητα και επιστρέφοντας μια τρίτη τιμή εάν είναι ίσες ή μια διαφορετική τιμή εάν δεν είναι ίσες. Η συνάρτηση DECODE έχει τρεις απαιτούμενες παραμέτρους και η σύνταξη είναι DECODE(expr1, comp1, iftrue1, , ). Αυτές οι παράμετροι χρησιμοποιούνται όπως φαίνεται στο ακόλουθο παράδειγμα ψευδοκώδικα
IF expr1=comp1, τότε επιστρέψτε iftrue1
Διαφορετικά, εάν expr1=comp2, τότε επιστρέψτε iftrue2
Διαφορετικά, εάν exprN=compN, τότε επιστρέψτε iftrueN
Διαφορετικά επιστροφή NULL|iffalse;
Πρώτον, το expr1 συγκρίνεται με το comp1. Εάν είναι ίσα, επιστρέφεται iftrue1. Εάν το expr1 δεν είναι ίσο με το comp1, τότε το τι θα συμβεί στη συνέχεια εξαρτάται από το αν έχουν καθοριστεί οι παράμετροι comp2 και iftrue2. Εάν δοθεί, η τιμή του expr1 συγκρίνεται με το comp2. Εάν οι τιμές είναι ίσες, τότε επιστρέφεται iftrue2. Εάν όχι, τότε εάν υπάρχουν ζεύγη παραμέτρων compN, συγκρίνονται οι iftrueN, expr1 και compN και, εάν είναι ίσες, επιστρέφεται iftrueN. Εάν δεν βρέθηκε αντιστοίχιση σε κανένα σύνολο παραμέτρων, τότε επιστρέφεται είτε εάν έχει καθοριστεί αυτή η παράμετρος είτε αν είναι άστοχο είτε NULL.
Όλες οι παράμετροι στη συνάρτηση DECODE μπορούν να είναι εκφράσεις. Ο τύπος της τιμής επιστροφής είναι ίσος με τον τύπο του πρώτου στοιχείου επικύρωσης - της παραμέτρου comp 1. Έκφραση εκφρ 1 μετατρέπεται σιωπηρά στον τύπο δεδομένων της παραμέτρου comp1. Όλες οι άλλες διαθέσιμες παράμετροι comp 1...συμΝ μετατρέπονται επίσης σιωπηρά σε τύπο comp 1. Το DECODE αντιμετωπίζει μια τιμή NULL ως ίση με μια άλλη τιμή NULL, δηλ. εάν το expr1 είναι NULL και το comp3 είναι NULL και το comp2 δεν είναι NULL, τότε το iftrue3 επιστρέφεται. Ας δούμε μερικά παραδείγματα
Ερώτημα 1: επιλέξτε αποκωδικοποίηση (1234, 123, «Το 123 είναι ταίριασμα») από το dual.
Ερώτημα 2: επιλέξτε αποκωδικοποίηση (1234, 123, «123 είναι ταίριασμα», «Δεν ταιριάζει») από το dual.
Ερώτημα 3: επιλέξτε decode('search', 'comp1', 'true1', 'comp2', 'true2', 'search', 'true3', substr('2search', 2, 6), 'true4', ' false') από dual?
Το πρώτο ερώτημα συγκρίνει τις τιμές 1234 και 123. Επειδή δεν είναι ίσες, το iftrue1 αγνοείται και επειδή η τιμή iffalse δεν έχει οριστεί, επιστρέφεται NULL. Το αίτημα δύο είναι το ίδιο με το αίτημα 1 εκτός από το ότι ορίζεται η τιμή iffalse. Δεδομένου ότι το 1234 δεν είναι ίσο με 123, επιστρέφει ειλικρινές - "Χωρίς ταίριασμα". Το ερώτημα τρία ελέγχει τις τιμές των παραμέτρων ώστε να ταιριάζουν με την τιμή αναζήτησης. Οι παράμετροι comp1 και comp2 δεν είναι ίσες με «αναζήτηση», επομένως τα αποτελέσματα των iftrue1 και iftrue2 παραλείπονται. Βρίσκεται ένα ταίριασμα στην τρίτη λειτουργία σύγκρισης του στοιχείου comp3 (θέση παραμέτρου 6) και επιστρέφεται η τιμή του iftrue3 (παράμετρος 7) που ισούται με 'true3'. Εφόσον βρέθηκε αντιστοιχία, δεν γίνονται άλλοι υπολογισμοί. Δηλαδή, παρά το γεγονός ότι η τιμή του comp4 (παράμετρος 8) ταιριάζει επίσης με το expr1, αυτή η έκφραση δεν υπολογίζεται ποτέ αφού η αντιστοίχιση βρέθηκε στην προηγούμενη σύγκριση.
ΠΕΡΙΠΤΩΣΗ έκφραση
Όλες οι γλώσσες προγραμματισμού τρίτης και τέταρτης γενιάς υλοποιούν την κατασκευή περίπτωσης. Όπως η συνάρτηση DECODE, η έκφραση CASE σας επιτρέπει να εφαρμόσετε τη λογική εάν-τότε-άλλο. Υπάρχουν δύο επιλογές για τη χρήση της έκφρασης CASE. Μια απλή έκφραση CASE ρυθμίζει το στοιχείο πηγής για σύγκριση μία φορά και, στη συνέχεια, παραθέτει όλες τις απαραίτητες συνθήκες δοκιμής. Η σύνθετη (αναζήτηση) CASE αξιολογεί και τις δύο προτάσεις για κάθε συνθήκη.
Η έκφραση CASE έχει τρεις απαιτούμενες παραμέτρους. Η σύνταξη της έκφρασης εξαρτάται από τον τύπο. Για μια απλή έκφραση CASE μοιάζει με αυτό
CASE search_expr
ΟΤΑΝ σύγκριση_expr1 ΤΟΤΕ iftrue1
)
Η συνάρτηση TRUNC επιστρέφει τον αριθμό n, περικομμένο σε m δεκαδικά ψηφία. Η παράμετρος m ενδέχεται να μην καθορίζεται· σε αυτήν την περίπτωση, το n περικόπτεται σε έναν ακέραιο.
SELECT TRUNC(100.25678) X1, TRUNC(-100.25678) X2, TRUNC(100.99) X3,
TRUNC(100.25678, 2) X4
ΑΠΟ DUAL
Λειτουργία SIGN(n)
Η συνάρτηση SIGN καθορίζει το πρόσημο ενός αριθμού. Εάν το n είναι θετικό, τότε η συνάρτηση επιστρέφει 1. Εάν το n είναι αρνητικό, επιστρέφει -1. Αν ισούται με μηδέν, τότε επιστρέφεται το 0. Για παράδειγμα:
SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3
ΑΠΟ DUAL
Ένα ενδιαφέρον χαρακτηριστικό αυτής της συνάρτησης είναι η δυνατότητα μετάδοσης m ίσου με μηδέν χωρίς να προκαλείται σφάλμα διαίρεσης με το 0.
Λειτουργία POWER(n, m)
Η συνάρτηση POWER αυξάνει τον αριθμό n στην ισχύ m. Ο βαθμός μπορεί να είναι κλασματικός και αρνητικός, γεγονός που διευρύνει σημαντικά τις δυνατότητες αυτής της συνάρτησης.
SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,
POWER(1000, 1/3) X3, POWER(1000, -1/3) X4
ΑΠΟ DUAL
| Χ1 | X2 | X3 | Χ4 |
| 100 | 10 | 10 | 0,1 |
Σε ορισμένες περιπτώσεις, ενδέχεται να προκύψει εξαίρεση κατά την κλήση αυτής της συνάρτησης. Για παράδειγμα:
SELECT POWER(-100, 1/2) X2
ΑΠΟ DUAL
Σε αυτήν την περίπτωση, επιχειρείται να υπολογιστεί η τετραγωνική ρίζα ενός αρνητικού αριθμού, η οποία θα έχει ως αποτέλεσμα το σφάλμα ORA-01428 "Επιχείρημα εκτός εύρους".
Συνάρτηση SQRT(n)
Αυτή η λειτουργίαεπιστρέφει την τετραγωνική ρίζα του n. Για παράδειγμα:
SELECT SQRT(100) X
ΑΠΟ DUAL
Συναρτήσεις EXP(n) και LN(n).
Η συνάρτηση EXP αυξάνει το e στην ισχύ n και η συνάρτηση LN υπολογίζει τον φυσικό λογάριθμο του n (το n πρέπει να είναι μεγαλύτερο από μηδέν). Παράδειγμα:
SELECT EXP(2) X1, LN(1) X2, LN(EXP(2)) X3
Λειτουργία NVL
Η συνάρτηση NVL χρησιμοποιείται γενικά πιο συχνά. Η συνάρτηση λαμβάνει δύο παραμέτρους: NVL(expr1, expr2). Εάν η πρώτη παράμετρος expr1 δεν είναι NULL, τότε η συνάρτηση επιστρέφει την τιμή της. Εάν η πρώτη παράμετρος είναι NULL, τότε η συνάρτηση επιστρέφει την τιμή της δεύτερης παραμέτρου expr2.
Ας δούμε ένα πρακτικό παράδειγμα. Το πεδίο COMM στον πίνακα EMP μπορεί να περιέχει NULL τιμές. Κατά την εκτέλεση ενός ερωτήματος όπως:
ΕΠΙΛΟΓΗ EMPNO, ENAME, COMM, NVL(COMM, 0) NVL_COMM
ΑΠΟ SCOTT.EMP
η τιμή NULL θα αντικατασταθεί από το μηδέν. Σημειώστε ότι όταν μια τιμή δημιουργείται χρησιμοποιώντας μια συνάρτηση, της εκχωρείται ένα ψευδώνυμο. Τα αποτελέσματα του ερωτήματος θα έχουν την εξής μορφή:
| ΕΜΠΝΟ | ENAME | ΚΟΙΝ | NVL_COMM |
| 7369 | ΣΙΔΗΡΟΥΡΓΟΣ | 0 | |
| 7499 | ALLEN | 300 | 300 |
| 7521 | ΠΤΕΡΥΓΑ | 500 | 500 |
| 7566 | JONES | 0 | |
| 7654 | ΧΕΛΙΔΟΝΙ | 1400 | 1400 |
| 7698 | BLAKE | 0 | |
| 7782 | ΚΛΑΡΚ | 0 | |
| 7839 | ΒΑΣΙΛΙΑΣ | 0 | |
| 7844 | ΤΟΡΝΑΔΟΡΟΣ | 0 | 0 |
| 7900 | Τζέιμς | 0 | |
| 7902 | ΠΕΡΑΣΜΑ | 0 | |
| 7934 | ΜΥΛΩΝΑΣ | 0 |
Συνάρτηση CEIL(n)
Η συνάρτηση CEIL επιστρέφει τον μικρότερο ακέραιο μεγαλύτερο ή ίσο με τον αριθμό n που δόθηκε ως παράμετρος. Για παράδειγμα:
SELECT CEIL(100) X1, CEIL(-100) X2, CEIL(100.2) X3 , CEIL(-100.2) X4
ΑΠΟ DUAL
Συνάρτηση TRUNC(n [,m])
Η συνάρτηση TRUNC επιστρέφει τον αριθμό n, περικομμένο σε m δεκαδικά ψηφία. Η παράμετρος m ενδέχεται να μην καθορίζεται· σε αυτήν την περίπτωση, το n περικόπτεται σε έναν ακέραιο.
SELECT TRUNC(100.25678) X1, TRUNC(-100.25678) X2, TRUNC(100.99) X3,
TRUNC(100.25678, 2) X4
ΑΠΟ DUAL
Λειτουργία SIGN(n)
Η συνάρτηση SIGN καθορίζει το πρόσημο ενός αριθμού. Εάν το n είναι θετικό, τότε η συνάρτηση επιστρέφει 1. Εάν το n είναι αρνητικό, επιστρέφει -1. Αν ισούται με μηδέν, τότε επιστρέφεται το 0. Για παράδειγμα:
SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3
ΑΠΟ DUAL
Ένα ενδιαφέρον χαρακτηριστικό αυτής της συνάρτησης είναι η δυνατότητα μετάδοσης m ίσου με μηδέν χωρίς να προκαλείται σφάλμα διαίρεσης με το 0.
Λειτουργία POWER(n, m)
Η συνάρτηση POWER αυξάνει τον αριθμό n στην ισχύ m. Ο βαθμός μπορεί να είναι κλασματικός και αρνητικός, γεγονός που διευρύνει σημαντικά τις δυνατότητες αυτής της συνάρτησης.
SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,
POWER(1000, 1/3) X3, POWER(1000, -1/3) X4
ΑΠΟ DUAL
| Χ1 | X2 | X3 | Χ4 |
| 100 | 10 | 10 | 0,1 |
Σε ορισμένες περιπτώσεις, ενδέχεται να προκύψει εξαίρεση κατά την κλήση αυτής της συνάρτησης. Για παράδειγμα:
SELECT POWER(-100, 1/2) X2
ΑΠΟ DUAL
Σε αυτήν την περίπτωση, επιχειρείται να υπολογιστεί η τετραγωνική ρίζα ενός αρνητικού αριθμού, η οποία θα έχει ως αποτέλεσμα το σφάλμα ORA-01428 "Επιχείρημα εκτός εύρους".
Συνάρτηση SQRT(n)
Αυτή η συνάρτηση επιστρέφει την τετραγωνική ρίζα του αριθμού n. Για παράδειγμα:
SELECT SQRT(100) X
ΑΠΟ DUAL
Συναρτήσεις EXP(n) και LN(n).
Η συνάρτηση EXP αυξάνει το e στην ισχύ n και η συνάρτηση LN υπολογίζει τον φυσικό λογάριθμο του n (το n πρέπει να είναι μεγαλύτερο από μηδέν). Παράδειγμα:
SELECT EXP(2) X1, LN(1) X2, LN(EXP(2)) X3
Συνάρτηση TO_CHAR με αριθμούς
Λειτουργίες μετατροπής δεδομένων σε άλλους τύπους δεδομένων. TO_CHAR(αριθμός) μετατρέπει έναν αριθμό σε κείμενο. Το TO_NUMBER(string) μετατρέπει το κείμενο σε αριθμό.
SELECT TO_CHAR (123) FROM DUAL θα επιστρέψει τη συμβολοσειρά 123, SELECT TO_NUMBER (`12345") FROM DUAL θα επιστρέψει τον αριθμό 12345.
Εργαστηριακές εργασίες. Αλλαγή της μορφής αριθμού εξόδου
Αλλαγές στη μορφή των αριθμητικών τιμών στο Oracle SQL, τη συνάρτηση TO_CHAR για εργασία με αριθμητικές τιμές.
Ασκηση:
Γράψτε ένα ερώτημα που θα εμφανίζει πληροφορίες σχετικά με το όνομα, το επώνυμο και τον μισθό των εργαζομένων από τον πίνακα hr.employees με τη μορφή που φαίνεται στο Σχ. 3.4-1:
Ρύζι. 3.4 -1
Σε αυτήν την περίπτωση, τα δεδομένα θα πρέπει να ταξινομηθούν με τέτοιο τρόπο ώστε να εμφανίζονται πρώτα οι σειρές για τους υπαλλήλους με τον υψηλότερο μισθό.
Σημείωση:
Ορισμένες τιμές μισθού στο Σχ. 3.4-1 έχουν τροποποιηθεί, επομένως ενδέχεται να μην είναι ίδιες με τις τιμές σας.
Λύση:
ΕΠΙΛΕΞΤΕ όνομα ΩΣ "Όνομα", επώνυμο Ως "Επώνυμο", TO_CHAR (ΜΙΣΘΟΣ, "L999999999.99") Ως "Μισθός" ΑΠΟ hr.employees ΠΑΡΑΓΓΕΛΙΑ ΚΑΤΑ ΜΙΣΘΟ ΠΕΡΙΦ.
Λειτουργίες TO_NUMBER και TO_DATE
Λειτουργία μετατροπής μιας συμβολοσειράς σε ημερομηνία TO_DATE (συμβολοσειρά, μορφή). Οι πιθανές τιμές μορφής έχουν ήδη συζητηθεί παραπάνω, επομένως θα δώσω πολλά παραδείγματα χρήσης αυτής της συνάρτησης. Παραδείγματα:
ΕΠΙΛΕΓΩ TO_DATE("01/01/2010", `ΗΗ.ΜΜ.ΕΕΕΕ") ΑΠΟΤο DUAL θα επιστρέψει την ημερομηνία «01/01/2010».
ΕΠΙΛΕΓΩ TO_DATE("01.ΙΑΝ.2010", `ΗΗ.ΔΕΥ.ΕΕΕΕ") ΑΠΟΤο DUAL θα επιστρέψει την ημερομηνία «01/01/2009».
ΕΠΙΛΕΓΩ TO_DATE("15-01-10", `ΗΗ-ΜΜ-ΕΕ") ΑΠΟΤο DUAL θα επιστρέψει την ημερομηνία «15/01/2010».
Λειτουργία μετατροπής συμβολοσειράς σε αριθμητική τιμή TO_NUMBER (συμβολοσειρά, μορφή). Οι πιο συνηθισμένες τιμές μορφής παρατίθενται στον πίνακα, οπότε ας δούμε τη χρήση αυτής της συνάρτησης χρησιμοποιώντας παραδείγματα. Παραδείγματα:
ΕΠΙΛΕΓΩ TO_NUMBER(`100") ΑΠΟΤο DUAL θα επιστρέψει τον αριθμό 100 SELECT TO_NUMBER (`0010.01", "9999D99") ΑΠΟΤο DUAL θα επιστρέψει τον αριθμό 10.01.
ΕΠΙΛΕΓΩ TO_NUMBER("500.000", "999G999") ΑΠΟΤο DUAL θα επιστρέψει τον αριθμό 500000.
Στοιχείο RR σε μορφή ημερομηνίας
Το στοιχείο μορφής ημερομηνίας RR είναι παρόμοιο με το στοιχείο μορφής ημερομηνίας ώρας YY, αλλά παρέχει πρόσθετη ευελιξία για την αποθήκευση τιμών ημερομηνίας σε άλλους αιώνες. Το στοιχείο μορφής ημερομηνίας RR επιτρέπει την αποθήκευση ημερομηνιών του 20ου αιώνα στον 21ο αιώνα, προσδιορίζοντας μόνο τα δύο τελευταία ψηφία του έτους.
Εάν τα δύο τελευταία ψηφία του τρέχοντος έτους είναι από το 00 έως το 49, τότε το έτος που επιστρέφεται έχει τα ίδια δύο πρώτα ψηφία με το τρέχον έτος.
Εάν τα δύο τελευταία ψηφία του τρέχοντος έτους είναι από το 50 έως το 99, τότε τα 2 πρώτα ψηφία του έτους που επιστρέφεται είναι 1 μεγαλύτερα από τα 2 πρώτα ψηφία του τρέχοντος έτους.
Εάν τα δύο τελευταία ψηφία του τρέχοντος έτους είναι 00 έως 49, τότε τα 2 πρώτα ψηφία του έτους που επιστρέφεται είναι 1 λιγότερα από τα 2 πρώτα ψηφία του τρέχοντος έτους.
Εάν τα δύο τελευταία ψηφία του τρέχοντος έτους είναι από 50 έως 99, τότε το έτος που επιστρέφεται έχει τα ίδια πρώτα δύο ψηφία με το τρέχον έτος.
Λειτουργία NVL
Η συνάρτηση NVL χρησιμοποιείται γενικά πιο συχνά. Η συνάρτηση λαμβάνει δύο παραμέτρους: NVL (expr1, expr2). Εάν η πρώτη παράμετρος expr1 δεν είναι NULL, τότε η συνάρτηση επιστρέφει την τιμή της. Εάν η πρώτη παράμετρος είναι NULL, τότε η συνάρτηση επιστρέφει την τιμή της δεύτερης παραμέτρου expr2.
Παράδειγμα: Επιλέξτε NVL (supplier_city, n/a") από προμηθευτές:
Η παραπάνω δήλωση SQL θα επιστρέψει n/" εάν το πεδίο προμηθευτής_πόλης περιέχει μηδενική τιμή. Διαφορετικά, θα επιστρέψει την τιμή προμηθευτή_πόλης.
Ένα άλλο παράδειγμα χρήσης της συνάρτησης NVL στο Oracle/PLSQL είναι:
επιλέξτε προμηθευτής_αναγνωριστικό, NVL (supplier_desc, προμηθευτής_όνομα) από προμηθευτές.
Αυτή η δήλωση SQL θα επιστρέψει Όνομα προμηθευτήπεδίο αν προμηθευτής_περιγραφήπεριέχει μηδενική τιμή. Διαφορετικά θα επιστρέψει προμηθευτής_περιγραφή.
Τελευταίο παράδειγμα: Η χρήση της συνάρτησης NVL στο Oracle/PLSQL είναι: επιλέξτε NVL(προμήθεια, 0) από τις πωλήσεις.
Αυτή η δήλωση SQL επέστρεψε την τιμή 0 if επιτροπήΤο πεδίο περιέχει μια μηδενική τιμή. Διαφορετικά θα επέστρεφε προμήθειεςπεδίο.
Μετατροπές NVL για διάφορους τύπους δεδομένων
Για να μετατρέψετε μια μηδενική τιμή σε πραγματική τιμή, χρησιμοποιήστε τη συνάρτηση NVL: NVL ( έκφραση1, έκφραση2), Οπου:
έκφραση 1-Η αρχική ή η υπολογισμένη τιμή, η οποία μπορεί να είναι απροσδιόριστη.
έκφραση2- Η τιμή που αντικαθιστά την απροσδιόριστη τιμή.
Σημείωση:Η συνάρτηση NVL μπορεί να χρησιμοποιηθεί για τη μετατροπή οποιουδήποτε τύπου δεδομένων, αλλά το αποτέλεσμα θα είναι πάντα ο ίδιος τύπος έκφραση 1.
Μετατροπή NVL για διάφοροι τύποι:
ΑΡΙΘΜΟΣ - NVL (αριθμητική στήλη, 9).
CHAR ή VARCHAR2 - NVL (χαρακτήρες|στήλη,"Μη διαθέσιμος").
Εργαστηριακές εργασίες. Χρήση της συνάρτησης NVL
Λειτουργία NVL για εργασία με μηδενικές τιμές στο Oracle SQL.
Ασκηση:
Γράψτε ένα ερώτημα που εμφανίζει τις πληροφορίες ονόματος και επωνύμου του υπαλλήλου από τον πίνακα hr.employees., καθώς και το ποσοστό προμήθειας (στήλη COMMISSION_PCT) για τον εργαζόμενο. Σε αυτήν την περίπτωση, για τους εργαζόμενους για τους οποίους δεν έχει καθοριστεί η προμήθεια, πρέπει να εμφανίζεται η τιμή 0. Το αποτέλεσμα του αιτήματος θα πρέπει να είναι όπως φαίνεται στο Σχ. 3,5-1.
Ρύζι. 3.5 -1 (οι τιμές εμφανίζονται ξεκινώντας από τη γραμμή 51)
Λύση:
Ο αντίστοιχος κωδικός αιτήματος θα μπορούσε να είναι ως εξής:
SELECT first_name AS "First Name", last_name As "Last Name", NVL (COMMISSION_PCT, 0) Ως "Commission Rate" FROM hr.employees.
