Acum să ne uităm la caracteristicile de lucru cu funcția TO_CHAR()în raport cu transformarea numerelor. La efectuarea acestei operații, funcția are următoarea sintaxă:
TO_CHAR(număr, [, format[, parametru_nls]])
În acest caz, funcția TO_CHAR() tip convertește NUMĂRîn tip VARCHAR2. Tabelul arată ce formate există și cum sunt utilizate în conversie:
| Element de format | Un exemplu de șir de acest format | Descriere |
|---|---|---|
| 9 | 99 | Fiecare cifră 9 reprezintă o cifră semnificativă a rezultatului. Număr cifre semnificative valoarea returnată este egală cu numărul de cifre 9, sens negativ precedat de un semn minus. Toate zerourile de început sunt înlocuite cu spații. |
| 0 | 0999 | Returnează un număr cu zerouri la început, mai degrabă decât spații. |
| 0 | 9990 | Este returnat un număr cu zerouri în urmă, mai degrabă decât spații. |
| $ | $999 | Valoarea de returnare este precedată de un semn dolar, indiferent de simbolul valutar utilizat și poate fi utilizată împreună cu zerourile de început sau de la urmă. |
| B | B999 | Sunt returnate spații libere în locul părții întregi zero a numărului zecimal. |
| MI | 999MI | Returnează un număr negativ cu semnul minus la sfârșit și nu la început. Într-o valoare pozitivă va exista un spațiu în acest loc. |
| S | S9999 | Numărul returnat este precedat de un semn: + pentru numerele pozitive, numere - pentru cele negative. |
| S | 9999S | Numărul returnat se termină cu un semn: + pentru numerele pozitive, numere - pentru cele negative. |
| relatii cu publicul | 99PR | Returnează un număr negativ între paranteze unghiulare "<", ">". numere pozitive, sunt spații în acest loc. |
| D | 99D9 | Returnează un număr cu virgulă zecimală la poziția specificată. Numărul 9 de pe ambele părți indică număr maxim numere |
| G | 9G999 | Returnează un număr cu un separator de grup la poziția specificată. G |
| C | C99 | Returnează un număr cu simbolul monedei ISO la poziția specificată. C poate apărea în șirul de format specificat de mai multe ori. |
| L | L999 | Returnează un număr cu simbolul monedei în limba națională la poziția specificată. |
| , | 999,999 | Returnează un număr urmat de o virgulă la poziția specificată, indiferent de separatorul de grup selectat. |
| . | 99.99 | Returnează un număr cu virgulă zecimală la poziția specificată, indiferent de separatorul zecimal selectat. |
| V | 99V999 | Returnează un număr înmulțit cu 10n, Unde n este numărul de cifre 9 după V. Dacă este necesar, valoarea este rotunjită. |
| EEEE | 9,99 EEEE | Returnează un număr în notație științifică. |
| R.M. | R.M. | Returnează un număr folosind cifre romane majuscule. |
De exemplu, iată următorul bloc:
SETATE IEȘIREA SERVERULUI PE ÎNCEPERE -- Funcția TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(534523)); -- Funcția TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(34387, "99999")); -- Funcția TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(5000, "$9999")); -- Funcția TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(-9, "9S")); -- Funcția TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(-34, "S99")); -- Funcția TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(3 - 5, "999MI")); -- Funcția TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(7 - 3, "S9")); -- Funcția TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(4 - 5, "99PR")); -- Funcția TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(8900, "L9999")); -- Funcția TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(10000000, "9.9EEEE")); -- Funcția TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(10, "RM")); -- Funcția TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(105, "RM")); SFÂRŞIT; /
După lansare obținem:
SQL> BEGIN 2 3 -- Funcția TO_CHAR() -- 4 DBMS_OUTPUT.put_line(TO_CHAR(534523)); 5 6 -- Funcția TO_CHAR() -- 7 DBMS_OUTPUT.put_line(TO_CHAR(34387, "99999")); 8 9 -- Funcția TO_CHAR() -- 10 DBMS_OUTPUT.put_line(TO_CHAR(5000, "$9999")); 11 12 -- Funcția TO_CHAR() -- 13 DBMS_OUTPUT.put_line(TO_CHAR(-9, "9S")); 14 15 -- Funcția TO_CHAR() -- 16 DBMS_OUTPUT.put_line(TO_CHAR(-34, "S99")); 17 18 -- Funcția TO_CHAR() -- 19 DBMS_OUTPUT.put_line(TO_CHAR(3 - 5, "999MI")); 20 21 -- Funcția TO_CHAR() -- 22 DBMS_OUTPUT.put_line(TO_CHAR(7 - 3, "S9")); 23 24 -- Funcția TO_CHAR() -- 25 DBMS_OUTPUT.put_line(TO_CHAR(4 - 5, "99PR")); 26 27 -- Funcția TO_CHAR() -- 28 DBMS_OUTPUT.put_line(TO_CHAR(8900, "L9999")); 29 30 -- Funcția TO_CHAR() -- 31 DBMS_OUTPUT.put_line(TO_CHAR(10000000, "9.9EEEE")); 32 33 -- Funcția TO_CHAR() -- 34 DBMS_OUTPUT.put_line(TO_CHAR(10, "RM")); 35 36 -- Funcția TO_CHAR() -- 37 DBMS_OUTPUT.put_line(TO_CHAR(105, "RM")); 38 39 SFÂRȘIT; 40 / 534523 34387 $5000 9- -34 2- +4 RUR 8900 1.0E+07 X CV Procedura PL/SQL finalizată cu succes.
Și de dragul completității, să ne uităm la funcție TO_NUMBER(), din moment ce cu TO_CHAR() practic sunt gemeni, dar din două laturi opuse. Sintaxă TO_NUMBER Următorul:
TO_NUMBER(șir de caractere, [, format [, parametru_nls]])
ÎN în acest caz,„șir_caracter” este un tip CHAR sau VARCHAR2, care atunci când este aplicat la „format” este convertit în tip NUMĂR. „format” în această funcție este același ca pentru TO_CHAR(). Este destul de simplu. Și, dacă în exemplul anterior schimbăm, de exemplu, a treia linie de jos astfel:
DBMS_OUTPUT.put_line(TO_CHAR(TO_NUMBER("10000000", "9.9EEEE"), "9.9EEEE"));
În articolul anterior ne-am uitat la funcțiile încorporate pentru lucrul cu șiruri. În acest articol vom vorbi despre funcțiile pentru lucrul cu data/ora și funcțiile de conversie a tipurilor pentru date. Pentru a stoca data și ora, Oracle oferă un tip special DATE. CU punct fizic vizualizați-l un număr fracționar, întreaga parte care stochează numărul de zile de la o anumită dată de bază, iar cea fracționată stochează timpul. Acest lucru vă permite să efectuați peste date operatii aritmetice- adunare si scadere.
Funcția SYSDATE
Aceasta este una dintre cele mai frecvent utilizate funcții; returnează data și ora curente în funcție de ceasul serverului.
Funcția LAST_DAY(d)
Returnează ultima zi a lunii specificată în data d.
SELECTARE SYSDATE d,
LAST_DAY(SYSDATE) d1
Funcția MONTHS_BETWEEN(d1, d2)
Funcția MONTH_BETWEEN returnează numărul de luni dintre două date d1 și d2, ținând cont de semnul ca d1-d2, numărul returnat este o fracție.
SELECT MONTHS_BETWEEN(„2.09.2006”, „2.05.2006”) d1,
MONTHS_BETWEEN(„12.09.2006”, „2.05.2006”) d2,
MONTHS_BETWEEN(„2.05.2006”, „12.09.2006”) d3
Sa luam in considerare exemple tipice- trunchierea datei la ore, zile, lună și an. Masca de format implicită este „DD”
SELECTARE SYSDATE d1,
TRUNC(SYSDATE, "HH24") d2,
TRUNC(SYSDATE, „DD”) d3,
TRUNC(SYSDATE, „MM”) d4,
TRUNC(SYSDATE, "AAAA") d5
Formatarea măștilor permise pentru funcțiile TRUNC și ROUND
Să aruncăm o privire mai atentă asupra măștilor de format și a caracteristicilor utilizării acestora.
|
Masca |
Scop |
Prima zi a secolului |
|
AN, sau AAAA, sau AA, sau A |
Prima zi a anului |
Prima zi a trimestrului |
|
MONTH, sau MON, sau MM |
Prima zi a lunii |
Aceeași zi a săptămânii ca prima zi a anului curent |
|
Aceeași zi a săptămânii ca prima zi a lunii curente |
|
DAY sau DY sau D |
Prima zi a săptămânii |
HH, sau HH12 sau HH24 |
|
Funcția TO_DATE(str[,mask [,nls_lang]])
Funcția TO_DATE convertește șirul str într-o dată. Conversia se realizează folosind masca de mască, dacă este specificată. Dacă masca nu este specificată, atunci este luată masca implicită. Dacă specificați o mască, puteți specifica încă un parametru - limba folosită la formatarea numelor lunilor și zilelor. Dacă există o eroare la analizarea șirului str în conformitate cu masca specificată, apare o eroare: situatie exceptionala. Cea mai frecventă eroare este „ORA-01830: șablonul de format de dată se termină înainte de a converti întregul șir de intrare”. În plus, eroarea „ORA-01821: format de dată nu este recunoscut” este adesea întâlnită - apare atunci când este specificată o mască de format nevalidă.
SELECTARE TO_DATE("12.09.2006") d
Formatarea măștilor permise pentru funcțiile TO_CHAR la formatarea datelor
Secol, cu semnul minus înainte de datele î.Hr.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE-1000000, „SCC”) d2
26.09.2006 17:14:21 |
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „YYYY”) d2
Anul, scris în cuvinte folosind limba națională actuală.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „YEAR”) d2
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „MM”) d2
Numele lunii în cuvinte.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „MONTH”) d2
Săptămâna anului.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „WW”) d2
Ziua anului.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „DDD”) d2
Ziua săptămânii este scrisă cu cuvinte, dacă este necesar, completate cu până la nouă caractere cu spații.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „DAY”) d2
data calendarului iulian. Este numărul de zile de la 01/01/4712 î.Hr.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „J”) d2
Ora din zi pe o scară de 12 ore (1-12).
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „HH”) d2
Minute (0-59).
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „MI”) d2
Această mască de format este utilă pentru măsurarea intervalelor de timp în secunde.
Semne de punctuatie. Acestea sunt scoase în locurile corespunzătoare din data formatată.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „ZZ.LL.AAAA HH24.MI”) d2,
TO_CHAR(SYSDATE, „ZZ/LL/AAAA HH12.MI PM”) d3
Elementele de masca de format concepute pentru a afișa informațiile text sunt sensibile la majuscule și minuscule - majusculele controlează majusculele textului formatat. Să ne uităm la gestionarea registrelor folosind masca format DAY ca exemplu:
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „DAY”) d2,
TO_CHAR(SYSDATE, „Ziua”) d3,
TO_CHAR(SYSDATE, „zi”) d4
26.09.2006 17:47:45 |
Este ușor de observat că dacă masca este scrisă cu majuscule, atunci formatată valoarea textului revine cu litere mari. Dacă în cea inferioară, valoarea formatată va fi și în cea inferioară. Și în sfârșit, înregistrarea măștii cu majusculă face ca textul generat să fie formatat astfel încât să înceapă cu o literă majusculă.
Concluzie
În acest articol am trecut în revistă toate principalele încorporate Funcții Oracle, conceput pentru a lucra cu date și pentru a efectua operațiuni de conversie a tipului cu tipul „date”. În următorul articol, ne vom uita la funcțiile de conversie și formatare a numerelor și vom trece la interogări practice folosind funcțiile discutate mai devreme.
Pentru a simplifica lucrul cu șiruri, există o serie de funcții încorporate, care facilitează foarte mult operațiuni precum conversia șirurilor în date de alte tipuri, căutarea unui subșir într-un șir, determinarea lungimii unui șir etc. În acest articol. ne vom uita la cele mai comune funcții pentru lucrul cu șiruri.
1) Funcția pentru determinarea lungimii unui șir LENGTH(șir), returnează numărul de caractere din șir, inclusiv spațiile finale.
SELECT LENGTH(‘șir’) FROM DUAL va returna valoarea 7.
2) Funcții de conversie a majusculelor de caractere UPPER(șir), LOWER(șir), INITCAP(șir). Pentru a converti caracterele în majuscule, utilizați funcția UPPER().
SELECT UPPER('șir') FROM DUAL va returna STRING.
Dacă trebuie să convertiți caractere șir în litere mici Este folosită funcția LOWER().
SELECT LOWER('STRING') FROM DUAL va returna string.
Funcția INITCAP convertește fiecare prim caracter al unui cuvânt în majuscule și toate celelalte caractere în minuscule, cu condiția ca caracterul separator de cuvinte să fie un spațiu.
SELECT INITCAP('string1 string2') FROM DUAL va returna String1 String2.
3) Funcții pentru tăierea spațiilor de început și de final LTRIM(șir), RTRIM(șir), TRIM(șir). În consecință, prima funcție decupează toate spațiile de început ale liniei, a doua - toate spațiile de sfârșit, iar a treia - toate spațiile de început și de sfârșit.
SELECT LTRIM(‘str1’) FROM DUAL va returna șirul str1,
SELECT RTRIM(‘str2’) FROM DUAL va returna șirul str2,
SELECT TRIM(‘ str3 ’) FROM DUAL va returna șirul str3.
4) Funcția de înlocuire a unei părți a unui șir cu un alt șir REPLACE (șir_original, subșir_inlocuit, subșir_înlocuitor). Pentru o mai mare claritate, să ne uităm la un exemplu în care un număr este stocat într-un anumit câmp de text dintr-un tabel. Mai mult, caracterul separator dintre întregul și parte fracționatăîn unele câmpuri există „.”, iar pentru prelucrarea ulterioară a datelor trebuie să fie „,” în toate câmpurile. Pentru aceasta vom folosi Funcția ÎNLOCUIREîn felul următor. REPLACE(câmp1, ’.’, ’,’) și toate „.” în câmpul câmpului va fi înlocuit cu simbolul „,”.
SELECT REPLACE('My_string','_','@') FROM DUAL va returna sirul My@string.
5) Funcții pentru conversia datelor în alte tipuri de date. TO_CHAR(număr) convertește un număr în text. TO_NUMBER(șir) convertește textul într-un număr. TO_DATE(șir, format_date) convertește un șir într-un anumit format de dată.
SELECT TO_CHAR(123) FROM DUAL va returna rândul 123,
SELECT TO_NUMBER('12345') FROM DUAL va returna numărul 12345,
SELECT TO_DATE('01.01.2010','dd.mon.yyyy') FROM DUAL va returna data 01.JAN.2010.
6) Funcție pentru determinarea apariției unui subșir într-un șir INSTR (sursă_șir, subșir, caracter_număr). Această funcție vă permite să determinați numărul caracterului din linia originală de la care începe subșirul căutat (dacă există). În caz contrar, se returnează 0. De exemplu, trebuie să determinăm toate pozițiile din tabelul Table1, în numele căruia apare subșirul „manager”. Următorul operator este destul de potrivit pentru asta
SELECTAȚI * FROM TABLE1 WHERE INSTR(POST, „manager”, 1) > 0.
Acesta este instrucțiunea SELECT va afișa doar acele înregistrări din tabelul TABLE1 în care va fi găsit subșirul căutat „manager”. Mai mult, căutarea se va efectua de la primul caracter. Dacă căutarea trebuie efectuată dintr-o poziție diferită, atunci numărul caracterului pentru a începe căutarea este indicat în al treilea parametru.
SELECT INSTR('Șir mic', 'șir', 1) FROM DUAL va returna valoarea 7,
SELECT INSTR('Șir mic', 'Șir', 1) FROM DUAL va returna valoarea 0.
7) Funcție de selectare a unui subșir în șirul sursă SUBSTR (șir_sursă, număr_caracter_începător, număr_caractere). Să luăm în considerare acest exemplu: tabelul de utilizatori stochează adresa sub forma numelui localității, numele străzii și numărul casei. Mai mult, știm sigur că pentru numele unei localități sunt alocate strict 20 de caractere (dacă numele unei localități este mai mic de 20 de caractere, restul se umple cu spații), pentru numele unei străzi 30 de caractere, pt. numărul casei 3 caractere. În continuare, trebuie să transferăm toate adresele din tabelul nostru în altul și, în același timp, toate cele 3 componente ale adresei trebuie să fie în câmpuri diferite. Pentru a selecta componentele adresei, utilizați funcția SUBSTR().
SELECTAȚI SUBSTR(TABLE_1.ADDRESS, 1, 20) ORAȘ, SUBSTR(TABLE_1.ADDRESS, 21, 30) STRADA, SUBSTR(TABLE_1.ADDRESS, 52, 3) ORAȘ FROM TABLE_1
Desigur, pentru a transfera date trebuie să utilizați operatorul INSERT, dar pentru a înțelege funcționarea funcției SUBSTR, exemplul discutat este destul de potrivit.
SELECT SUBSTR('My_string', 4, 3) FROM DUAL va returna sirul str.
Funcțiile discutate mai sus pot fi utilizate în parametrii de intrare. Deci, dacă trebuie să selectăm toate caracterele după unul anume, atunci putem trece numărul caracterului căutat din funcția INSTR la funcția SUBSTR. De exemplu, dacă trebuie să transferați toate caracterele dintr-un câmp de tabel care sunt situate după „,”, atunci puteți utiliza această construcție
SELECTAȚI SUBSTR(Șirul_meu, INSTR(Șirul_meu, ',', 1), LENGTH(Șirul_meu)- INSTR(Șirul_meu, ',', 1)+1) FROM DUAL.
Pentru a determina caracterul de început, apelăm funcția INSTR(), care va returna numărul caracterului primei apariții a subșirurilor ",". În continuare, definim numărul de caractere până la sfârșitul șirului ca diferență dintre lungimea șirului și numărul primei apariții a subșirului.
8) Pentru a determina codul caracterului, se folosește funcția ASCII(șir), care returnează codul unui caracter al șirului. De exemplu
SELECT ASCII(W) FROM DUAL va returna valoarea 87.
9) Funcție inversă Convertirea unui cod de caracter într-un caracter CHR (număr).
SELECT CHR(87) FROM DUAL va returna caracterul W.
Funcții pentru lucrul cu numere în Oracle.
ÎN Oracle DBMS Există o serie de funcții pentru a lucra cu numere. Acestea includ funcții pentru ridicarea unui număr la o putere POWER(), rotunjirea ROUND(), etc.
1) Funcția ABS(număr) returnează valoarea absolută a argumentului.
SELECT ABS(-3) FROM DUAL va returna valoarea 3.
2) Funcția CEIL(număr) returnează cel mai mic număr întreg mai mare sau egal cu parametrul transmis.
SELECT CEIL(4.5) FROM DUAL va returna valoarea 5.
3) Funcția FLOOR(număr) returnează cel mai mare număr întreg mai mic sau egal cu parametrul transmis.
SELECT FLOOR(3.8) FROM DUAL va returna valoarea 3.
4) Funcția MOD(număr_1, număr_2) returnează restul împărțirii primului parametru cu al doilea.
SELECT MOD(5, 3) FROM DUAL va returna valoarea 2. Notă. Dacă al doilea parametru este 0, atunci funcția returnează primul parametru.
5) Funcția de rotunjire ROUND(număr_1, număr_2). Rotunjește primul parametru trecut la numărul de cifre trecute în al doilea parametru. Dacă al doilea parametru nu este specificat, atunci este luat egal cu 0, adică rotunjirea este efectuată la valoarea întreagă. Exemple
SELECT ROUND(101.34) FROM DUAL va returna valoarea 101,
SELECT ROUND(100.1268, 2) FROM DUAL va returna valoarea 100.13
SELECT ROUND(1234000.3254, -2) FROM DUAL va returna valoarea 1234000,
SELECT ROUND(-100.122, 2) FROM DUAL va returna -100.12.
6) Funcția de trunchiere a valorii TRUNC(number_1, number_2). Returnează valoarea primului parametru trunchiată la numărul de zecimale specificat în al doilea parametru. Exemple
SELECT TRUNC(150.58) FROM DUAL va returna valoarea 150
SELECT TRUNC(235.4587, 2) FROM DUAL va returna 235.45
SELECT TRUNC(101.23, -1) FROM DUAL va returna valoarea 100
7) SGBD Oracle are un număr de funcții trigonometrice SIN(număr), COS(număr), TAN(număr) și inversele lor ACOS(număr), ASIN(număr), ATAN(număr). Ele returnează valoarea funcției trigonometrice corespunzătoare numelui. Pentru funcțiile directe, parametrul este valoarea unghiului în radiani, iar pentru funcțiile inverse, valoarea funcției. Exemple
SELECT COS(0.5) FROM DUAL va returna valoarea 0.877582561890373
SELECT SIN(0.5) FROM DUAL va returna valoarea 0.479425538604203
SELECT TAN(0.5) FROM DUAL va returna valoarea 0.546302489843791
SELECT ACOS(0.5) FROM DUAL va returna valoarea 1.0471975511966
SELECT ASIN(0.5) FROM DUAL va returna 0.523598775598299
SELECT ATAN(0.5) FROM DUAL va returna valoarea 0.463647609000806
8) Funcții hiperbolice. SINH(număr),
COSH(număr), TANH(număr). SINH() returnează sinusul hiperbolic al parametrului trecut, COSH() returnează cosinusul hiperbolic al parametrului trecut, TANH() returnează tangentei hiperbolice a parametrului trecut. Exemple
SELECT COSH(0.5) FROM DUAL va returna valoarea 1.12762596520638
SELECT SINH(0.5) FROM DUAL va returna valoarea 0.521095305493747 SELECT TANH(0.5) FROM DUAL va returna valoarea 0.46211715726001
9) Funcția de exponențiere POWER(număr_1, număr_2). Exemple
SELECT POWER(10, 2) FROM DUAL va returna valoarea 100
SELECT POWER(100, -2) FROM DUAL va returna valoarea 0,0001
10) Funcții logaritmice. LN(number) returneaza logaritmul natural al parametrului trecut, LOG(number_1, number_2) returneaza logaritmul celui de-al doilea parametru trecut la baza trecuta la primul parametru. Mai mult, primul parametru trebuie să fie mai mare decât zero și să nu fie egal cu 1. Exemple
SELECT LN(5) FROM DUAL va returna valoarea 1,6094379124341
SELECT LOG(10, 3) FROM DUAL va returna valoarea 0,477121254719662
11) Funcția de evacuare rădăcină pătrată SQRT(număr). Exemplu
SELECT SQRT(4) FROM DUAL va returna valoarea 2.
12) Funcție de ridicare a numărului e la puterea EXP(număr). Exemplu
SELECT EXP(2) FROM DUAL va returna valoarea 7,38905609893065.
Funcții Oracle Data
În practică, de foarte multe ori este necesară analizarea datelor sub formă de date, efectuarea unor operații asupra acestora și modificarea formatului. Toate aceste operațiuni sunt deja implementate ca funcții încorporate. Să ne uităm la cele mai elementare dintre ele.
1) ADD_MONTHS(data, number_of_months) returnează o dată separată de data trecută în primul parametru de numărul de luni specificat în al doilea parametru. Exemple
SELECT ADD_MONTHS('01-JAN-2010', 2) FROM DUAL va returna data '03/01/2010'
SELECT ADD_MONTHS('01-JAN-2010', -3) FROM DUAL va returna data '01.10.2009'
SELECT ADD_MONTHS('30-JAN-2010', 1) FROM DUAL va returna data '02/28/2010'
2) A determina data curentași momentul în care funcția SYSDATE este aplicată. Domeniul de aplicare al acestei funcții este mult mai larg decât ar părea la prima vedere. În primul rând, acesta este controlul asupra introducerii datelor în baza de date. Multe tabele au un câmp separat pentru stocarea datei ultimei modificări. De asemenea, este foarte convenabil să controlezi anumiți parametri de intrare pentru rapoarte, mai ales dacă aceștia nu ar trebui să fie mai mari decât data curentă. Pe lângă dată, această funcție returnează și ora exactă la secunde. Exemplu
SELECT SYSDATE FROM DUAL va returna data „05/22/2010 14:51:20”
3) Dacă trebuie să determinați ultima zi a lunii, atunci funcția LAST_DAY(data) este destul de potrivită pentru aceasta. Poate fi folosit pentru a determina numărul de zile rămase într-o lună.
SELECTAȚI LAST_DAY(SYSDATE) – SYSDATE FROM DUAL.
Ca urmare a executării acestei instrucțiuni, va fi afișat numărul de zile de la data curentă până la sfârșitul lunii. Exemplu
SELECT LAST_DAY('15-FEB-2010') FROM DUAL va returna data '02/28/2010'.
4) Funcția de determinare a numărului de luni dintre datele MONTHS_BETWEEN(data_1, date_2). Exemple
SELECT MONTHS_BETWEEN('01-JUL-2009', '01-JAN-2010') FROM DUAL va reveni -6
SELECT MONTHS_BETWEEN('01-JUL-2009', '10-JAN-2010') FROM DUAL va returna -6,29032258064516.
Notă. Dacă zilele lunilor sunt aceleași, atunci funcția returnează un număr întreg, în caz contrar rezultatul va fi fracționat, iar numărul de zile din lună va fi 31.
5) Funcția NEXT_DAY(data, day_of_week) vă permite să determinați următoarea dată de la data trecută în primul parametru, care corespunde zilei săptămânii trecute în al doilea parametru. Exemplu
SELECT NEXT_DAY('01-JUL-2009', 'lun') FROM DUAL va returna data '07/06/2009', adică următoarea luni după 1 iulie 2009 a avut loc pe 6.
6) Rotunjirea datei ROUND (data, format). Al doilea parametru este opțional; dacă nu este specificat, este luat drept „DD”, adică rotunjirea se va face la cea mai apropiată zi. Exemple
SELECT ROUND(SYSDATE) FROM DUAL va returna data „05/23/2010”
SELECT ROUND(SYSDATE, MONTH) FROM DUAL va returna data „06/01/2010”, rotunjită la cea mai apropiată prima zi a lunii.
7) Trunchierea datei. Funcția TRUNC(data, format). La fel ca cel discutat mai sus, este posibil să nu aibă un al doilea parametru. În acest caz, trunchierea va fi efectuată până a doua zi. Exemple
SELECT TRUNC(SYSDATE) FROM DUAL va returna data „05/22/2010”
SELECT TRUNC(SYSDATE, 'WW') FROM DUAL va returna data '05/01/2010'
SELECT TRUNC(SYSDATE, 'Day') FROM DUAL va returna data '05/16/2010'.
Funcții de conversie a datelor în Oracle
Această secțiune este dedicată luării în considerare a conversiei datelor în diverse formate. În practică, situațiile sunt destul de frecvente când este necesar să se trateze valorile șirurilor ca numere și invers. În ciuda numărului mic de funcții, capacitățile lor sunt destul de suficiente pentru a rezolva probleme foarte complexe ale aplicațiilor.
1) TO_CHAR(date, format). La prima vedere, sintaxa este destul de simplă, dar datorită celui de-al doilea parametru puteți descrie foarte precis în ce format ar trebui convertite datele. Deci, puteți converti atât o dată, cât și o valoare numerică într-un șir. Luați în considerare opțiunea de a converti o dată într-un șir. Valorile celor mai comune formate sunt date în tabel, mai mult informatii complete cuprinse în documentația tehnică.
Tabel de valori de format pentru conversia unui număr într-un șir.
SELECT TO_CHAR(SYSDATE, 'D-MONTH-YY') FROM DUAL va returna șirul '7-MAY -10'
SELECT TO_CHAR(SYSDATE, „DDD-LL-YYYY”) FROM DUAL va returna șirul „142-05-2010”
SELECT TO_CHAR(SYSDATE, 'Q-D-LL-YYY') FROM DUAL va returna șirul '2-7-05-010'
SELECT TO_CHAR(1050, '9.99EEEE) FROM DUAL va returna șirul '1.050E+03'
SELECT TO_CHAR(1400, '9999V999') FROM DUAL va returna șirul '1400000'
SELECT TO_CHAR(48, 'RM') FROM DUAL va returna șirul 'XLVIII'
2) Funcție pentru conversia unui șir într-o dată TO_DATE(șir, format). Valorile posibile de format au fost deja discutate mai sus, așa că voi da câteva exemple de utilizare a acestei funcție. Exemple
SELECT TO_DATE('01/01/2010', 'DD.LL.YYYY') FROM DUAL va returna data '01/01/2010'
SELECT TO_DATE('01.JAN.2010', 'DD.MON.YYYY') FROM DUAL va returna data '01.01.2009'
SELECT TO_DATE('15-01-10', 'DD-LL-AA') FROM DUAL va returna data '01/15/2010'.
3) Funcție de conversie a unui șir într-o valoare numerică TO_NUMBER(șir, format). Cele mai comune valori de format sunt enumerate în tabel, așa că să ne uităm la utilizarea acestei funcții folosind exemple. Exemple
SELECT TO_NUMBER('100') FROM DUAL va returna numărul 100
SELECT TO_NUMBER('0010.01', '9999D99') FROM DUAL va returna numărul 10.01
SELECT TO_NUMBER("500,000","999G999") FROM DUAL va returna numărul 500000.
Până la două cifre zecimale, definiți cantitatea medie de arme pentru clasele cuirasate.
Am presupus că există o singură problemă în această sarcină și aceasta este rotunjirea. Dar recent am primit următoarea soluție:
SELECTAȚI SUM(sum_g)/SUM(count_g) FROM
(SELECTARE SUM(numGuns) AS sum_g , COUNT(*) AS count_g
FROM Classes INNER JOIN Ships ON Classes.class = Ships.class
WHERE type="bb"
UNIUNE
SELECTAȚI SUM(numGuns) AS sum_g , COUNT(*) AS count_g
FROM Classes INNER JOIN Rezultate ON Classes.class = Outcomes.ship
WHERE type="bb") AS a
Este foarte bogat pentru analiza erorilor J. Să începem de la rotunjire. Numărul de arme este un întreg (în funcție de tipul de coloană, nu de logică!). De aceea, suma în sine va fi un număr întreg. Când împărțim numere întregi în SQL Server, obținem întotdeauna un număr întreg. Și rezultatul este obținut nu prin rotunjire, ci prin ȘEDEREA părții fracționale. De exemplu, executați următoarea interogare:
Rezultatul va fi 0, iar asta confirmă ceea ce am spus. Deci, pentru a face îmbunătățiri cosmetice ale acestei interogări, cel puțin un operand ar trebui convertit în tipul real. După cum am scris în Ajutor de la www.sql-ex site-ul .ru, puteți utiliza conversia de tip implicit:
SELECTAȚI SUM(sum_g)*1,0/SUM(count_g)
adică la înmulțirea cu o unitate reală, numărătorul devine însuși un număr real.
Acum, deoarece trebuie să numărăm media pe clase, la început, nu ar trebui să luăm în considerare navele și, în al doilea rând, nu trebuie să ne amintim navele din tabelul Rezultate.
Dar, pentru a analiza greșelile, să luăm în considerare soluția în interpretarea autorului său, adică vom defini valoarea medie a tuturor navelor liniare din baza de date, iar aceasta se dovedește a fi sarcina 54. Am discutat despre asta. sarcină dar cu o altă greșeală.
Deci, numărul de arme și cantitatea sunt numărate separat în funcție de nave din tabelul Nave și de navele principale din tabelul Rezultate. Apoi, în interogarea principală, rezumăm numărul de arme si cantitatea de prin navele fiecare tabel și împărțiți primul la secundă pentru a obține valoarea medie.
Să discutăm un exemplu. Să fie 2 nave cu 11 și 8 tunuri în tabelul Nave și 1 navă cu 11 tunuri în tabelul Rezultate. Deci obținem 3 nave cu 30 de tunuri. Valoarea medie este 30/3= 10. Este corect? Nu, adică este corect doar pentru anumite cazuri, în timp ce trebuie să scriem o interogare care să fie corectă pentru orice date. Văd câteva exemple care să dovedească acest lucru.
Primul. Ce se întâmplă dacă nu există nicio navă principală care să corespundă termenilor sarcinii din tabelul Rezultate? Apoi a doua interogare va reveni: 0 nave, numărul de arme este NULL. Ca rezultat al calculării mediei pe care o vom obține
(19 + NULL)/(2+0) = NULL
Al doilea. Lăsați nava principală din clasa „bb” să fie atât în tabelul Nave, cât și în tabelul Rezultate, asta înseamnă că este aceeași navă. Atunci ar trebui să obținem 19/2, dar nu 30/3 așa cum o prezintă soluția.
Al treilea. Dar ce se întâmplă dacă în situația anterioară pe nave, nava principală a luat parte la lupte de două ori? Apoi vom obține (19 + 22) /(2 + 2) în loc de 19/2.
Al patrulea… Gândește-te la asta. Așa se formează baza de verificare pe site J .
» Exemplele date aici pot fi făcute direct pe site, bifând caseta de selectare „Fără bifare” de pe pagina cu exerciții SELECT.
