Using Single Row Functions Customize Output
Salamlar, Bloguma xoş gəlmisiniz.
Bugünkü movzumuz ORACLE SQL-dəki funksiyalar haqqında olacaq. ORACLE SQL də funksiyalar 2 tipə bölünürlər, 1ci tip scalar funksiyalar 2ci tip isə aggregate funksiyalar adlanır. Bu yazımda mən scalar funksiyalardan danışacağam. Scalar funksiyalar əslində single-row funksiyaların bir digər adıdır. Single-row funksiyalar aggregate funksiyalardan fərqli olaraq tək bir sətirə aid olurlar yəni sütundakı bütün məlumatlara tək-tək tətbiq edilirlər.
Məs: ROUND(sütun_adı) scalar funksiyadır həmin sütundakı sətirləri tək-tək yuvarlaqlaşdırır ancaq SUM(sütun_adı) aggregate funksiyadır sütundakı bütün məlumatları qrup halında cəmləyir. Bu tip hazır funksiyalar ORACLE SQL-də “built-in” olaraq adlandırılır, özümüzün PL/SQL vasitəsilə yazdığımız funksiyalar isə “user-defined” funksiyalar adlandırılır.
ORACLE SQL-də bir çox scalar funksiyalar mövcuddur. Bu funksiyaların sql sorğularda istifadəsi əsasən:
- SELECT– ifadəsində və həmçinin WHERE şərtində
- İNSERT-ifadəsində VALUES-in parametrlərində
- UPDATE-ifadəsində SET –in tərəfində və WHERE şərtində
- DELETE-ifadəsində WHERE şərtində
Mən bu funksiyaları ilk öncə verilən tiplərinə ayırıb sonra tək tək hər tip üçün ən çox istifadə olunan funksiyalardan bəhs edəcəyəm.
CHARACTER Funksiyalar
Bu tip funksiyalar əsasən hər fvə mətn tipli məlumatlarda istifadə olunur. Bunlara misal olaraq LENGTH, RPAD, LPAD, RTRIM, LTRIM, TRIM, INSTR, SUBSTR, REPLACE, SOUNDEX və.s aiddir.
NUMBER Funksiyalar
Bu tip funksiyalar riyazi analizlər və hesablamalar aparmaq üçün istifadə olunurlar. Bunlara misal olaraq SIN, ASIN, SINH,COS, ACOS, COSH, TAN, ATAN, ATAN2, TANH, ABS, SIGN, ROUND, TRUNC və.s aiddir.
DATE Funskiyalar
Bu tip funksiyalar tarix tipli məlumatlar üzərində əməliyyatlar aparmaq üçündür. Ən maraqlı funksiyalar deyerdimki bu tipdə mövcuddur. Bunlara misal olaraq SYSDATE, SYSTIMESTAMP,ROUND, TRUNC, MONTHS_BETWEEN, ADD_MONTHS və.s aiddir.
Bu funksiyaları bir-bir incələyəcəyik.
DUAL cədvəli
DUAL cədvəli ORACLE yükləndiyi zaman avtomatik yaratdığı cədvəldir. DUMMY adında 1 sütunu var və VARCHAR2(1) tipində dəyər saxlayır ilkin dəyəri “X”-dir. DUAL cədvəlini istifadə edərək bir çox lazımlı məlumatları əldə edə bilərik. Məs:
SELECT USER FROM DUAL – hal hazırda qoşulmuş olduğumuz istifadəçi adını qaytaracaq.
SELECT SYSDATE FROM DUAL – cari SYSDATE qiymətini qaytaracaq.
CHARACTER Funksiyaları
–UPPER, LOWER
UPPER(S1), LOWER(S1);
S1 – hərf və ya hərflər toplusudur(string).
Verilmiş string-in bütün hərflərini böyüdür(upper) və ya kiçildir(lower).
SELECT ID FROM EMP WHERE UPPER(NAME)= ‘abdulali’;
–INITCAP
INITCAP(S1);
S1 – hərf və ya hərflər toplusudur(string).
Verilmiş string-in baş hərflərini böyüdür.
SELECT INITCAP(‘test’), INITCAP(‘McDonald’ ’s’) FROM DUAL;
INITCAP(‘test’) INITCAP(‘McDonald’ ’s’)
——————- ——————————–
Test Mcdonald’s
–CONCAT
CONCAT(S1, S2), S1 || S2;
Verilmiş string-ləri birləşdirir.
S1, S2 – hərf(işarə) və ya hərflər toplusudur(string).
- SELECT CONCAT(‘Hello, ‘, ‘world!’) FROM DUAL;
- SELECT ‘Hello, ‘ || ‘world!’ FROM DUAL;
A və B ekvivalent sorğulardır.
–LPAD, RPAD
LPAD(S1, N, S2), RPAD(S1, N, S2);
S1, S2 – – hərf(işarə) və ya hərflər toplusudur(string).
N – rəqəm tipindədir.
LPAD və RPAD funksiyaları verilmiş s1 string-in müvafiq olaraq soluna və sağına s2 string-ni əlavə edir. Diqqət etmək lazım gərəkən nöqtə, N- əlavə olunduqdan sonrakı alınan string-in uzunluğudur neçə s2 string-inin s1-ə əlavə olunacağını təyin etmir.
SELECT RPAD('Chapter One - I Am Born',40,'.') FROM DUAL;
RPAD(‘CHAPTERONE-IAMBORN’,40,’.’)
———————————————————
Chapter One – I Am Born……………..
SELECT RPAD(CHAPTER_TITLE || ' ',30,'.') || LPAD(' ' || PAGE_NUMBER,30,'.') Contents FROM BOOK_CONTENTS ORDER BY PAGE_NUMBER;
Contents
———————————————–
Introduction ……………………………………… 1
Chapter 1 ………………………………………… 5
Chapter 2 ……………………………………….. 23
Chapter 3 ……………………………………….. 57
Index …………………………………………… 79
–LTRIM, RTRIM
LTRIM(S1, S2), RTRIM(S1,S2)
S1, S2 – – hərf(işarə) və ya hərflər toplusudur(string).
S1 string-in solundan(LTRIM) və ya sağından(RTRIM) S2-ni xaric edir.
SELECT RTRIM('Seven thousand--------','-') FROM DUAL;
RTRIM(‘SEVENTHOUSAND——–‘,’-‘)
——————————————————
Seven thousand
–TRIM
TRIM(trim_info trim_char FROM trim_source)
trim_info: 3 mümkün halı var. LEADING, TRAILING, BOTH
- LEADING – LTRIM-ə ekvivalentdir.
- TRAILING – RTRIM-ə ekvivalentdir.
- BOTH – trim_source-un hər iki tərəfindən trim_char-ı xaric edir.
trim_char: xaric ediləcək string-i bildirir.
trim_source: trim_char-ın hansı string-dən xaric ediləcəyin bildirir.
SELECT TRIM(TRAILING '-' FROM 'Seven thousand--------') FROM DUAL;
TRIM(TRAILING’-‘FROM’SEVENTHOUSAND——–‘)
——————————————————————-
Seven thousand
–LENGTH
LENGTH(S1)
S1-string-in uzunluğunu hesablayır.
SELECT LENGTH('Supercalifragilisticexpialidocious') FROM DUAL;
LENGTH(‘SUPERCALIFRAGILISTICEXPIALIDOCIOUS’)
————————————————————————-
34
–INSTR
INSTR(s1, s2, pos, n)
S1, S2 – – hərf(işarə) və ya hərflər toplusudur(string).
S2- S1 daxilində axtaracağımız string-dir.
pos – 1 və ya -1 qiymətləri ala bilər. 1 aldıqdan axtarılan string S1-də başdan , -1 aldıqda arxadan başlayaraq axtarılır.
n – S1 daxilində n-ci S2-ni axtarır.
SELECT INSTR('Mississippi','is',1,2) FROM DUAL;
INSTR(‘MISSISSIPPI’,’IS’,1,2)
————————————-
5
(Mississippi sözündə başdan başlayaraq 2ci “is”-in harda yerləşdiyini bizə qaytarır)
–SUBSTR
SUBSTR(s,pos,len)
Verilmiş string-i parçalamaq üçün istifadə olunur.
s – hərf(işarə) və ya hərflər toplusudur(string).
pos – string daxilində hardan başlayacağımız təyin edir
len – hansı uzunluqda bir string kəsəcəyimizi təyin edir
SELECT SUBSTR(‘ABDULALIYEV.COM’,12, 3 ) DOMAIN FROM DUAL;
DOMAIN
———–
.COM
–SOUNDEX
SOUNDEX(s)
s – hərf(işarə) və ya hərflər toplusudur(string).
SOUNDEX verilmiş string-i(yalniz ingilis dilində olanları) xususi koda çevirir. Verilmiş sözün oxunuşuna görə bir koda çevirir və bu söz kimi oxunanları axtarır. Aşağıdakı cədvəldə hərflərin soundex ekvivalenti verilib.
Məs: Oxunuşu ‘Worthen’ ilə eyni olan işçinin adını qaytaran sorğu.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE SOUNDEX(LAST_NAME) = SOUNDEX('Worthen');
EMPLOYEE_ID FIRST_NAME LAST_NAME
—————— —————- —————
7 Buffy Worthington
NUMBER Funksiyalar
–ROUND
ROUND(n, i)
n– yuvarlaqlaşdırılmasını istədiyimiz ədəddir.
i – yuvarlaqlaşdırıldıqdan sonra vergüldən sağda neçə ədəd qalacağını bildirir. i – yazılmadığı halda ədəd tam yuvarlaqlaşdırılır. i – mənfi olduğu zaman verilmiş n-ədədini n-dən böyük ən yaxın onluq ədədə yuvarlaqlaşdırır. ROUND verilmiş ədədləri yuxarıya doğru yuvarlaqlaşdırır.
SELECT ROUND(12.355143, 2), ROUND(259.99,-1) FROM DUAL;
ROUND(12.355143,2) ROUND(259.99,-1)
———————- ———————-
12.36 260
–TRUNC
TRUNC(n, i)
n– yuvarlaqlaşdırılmasını istədiyimiz ədəddir.
i – yuvarlaqlaşdırıldıqdan sonra vergüldən sağda neçə ədəd qalacağını bildirir. i – yazılmadığı halda ədəd tam yuvarlaqlaşdırılır. i – mənfi olduğu zaman verilmiş n-ədədini n-dən kiçik ən yaxın onluq ədədə yuvarlaqlaşdırır. TRUNC verilmiş ədədləri aşagıya doğru yuvarlaqlaşdırır.
SELECT TRUNC(12.355143, 2), TRUNC(259.99,-1) FROM DUAL;
TRUNC(12.355143,2) TRUNC(259.99,-1)
——————— ———————
12.35 250
–REMAİNDER
REMAİNDER(n1, n2)
n1, n2 – ədəd tipindədirlər.
REMAİNDER funksiyasının işləmə şəkli belədir: fərz edəki n1-in daxilində n2 k dəfə yerləşir bu zaman nəticə = n1 – n2*k; bəzən isə n1-ə ən yaxın böyük ədəd n2-ni k dəfə özündə tam saxlayır bu zaman yenə nəticə = n1 – n2*k ilə hesablanır ancaq k n1-ə ən yaxın olan ədədin n2-ni ozündə neçə dəfə saxladığı olur.
SELECT REMAINDER(9,3), REMAINDER(10,3), REMAINDER(11,3) FROM DUAL;
REMAINDER(9,3) REMAINDER(10,3) REMAINDER(11,3)
—————— ——————– ——————-
0 1 -1
–MOD
MOD(n1, n2)
n1, n2 – ədəd tipindədirlər.
MOD funksiyası bildiyimiz qalıq tapmaq işini görür.
SELECT MOD(9,3), MOD(10,3), MOD(11,3) FROM DUAL;
MOD(9,3) MOD(10,3) MOD(11,3)
————– ————– ———————-
0 1 2
DATE Funksiyalar
–SYSDATE
ORACLE serverin yükləndiyi əməliyyat sistemin-dəki zamanı və günü özündəsaxlayır ancaq bizə NLS_DATE_FORMAT parametrində təyin olunmuş formada məlumat qaytarır.
SELECT SYSDATE FROM DUAL;
SYSDATE
———————
17-NOV-15
–ROUND – date
ROUND(d, i)
d – DATE tipindədir.
i – format modeldir
Verilmiş date dəyərini i – format modelinə görə yuxarıya yuvarlaqlaşdırır.
SELECT SYSDATE TODAY, ROUND(SYSDATE,'MM') R_MONTH, ROUND(SYSDATE,'RR') R_YEAR FROM DUAL;
TODAY R_MONTH R_YEAR
————- —————– —————–
17-NOV-15 01-DEC-15 01-JAN-16
–TRUNC – date
TRUNC(d,i)
d – DATE tipindədir.
i – format modeldir
Verilmiş date dəyərini i – format modelinə görə aşağıya yuvarlaqlaşdırır.
SELECT SYSDATE TODAY, TRUNC (SYSDATE,'MM') T_MONTH, ROUND(SYSDATE,'RR') T_YEAR FROM DUAL;
TODAY T_MONTH T_YEAR
————- ————- —————–
17-NOV-15 01-NOV-15 01-JAN-15
–NEXT_DAY
NEXT_DAY(d,c)
d – DATE tipindədir.
c – həftənin günlərinin text formasında yazılışıdır.
Verilmiş gündən c – gün sonrasını qaytarır.
SELECT NEXT_DAY('17-NOV-15','Saturday') FROM DUAL;
NEXT_DAY(’17-NOV-15′,’SATURDAY’)
—————————————-
21-NOV-15
–LAST_DAY
LAST_DAY(d)
d – DATE tipindədir.
Verilmiş tarixin son gününü qaytarır.
SELECT LAST_DAY('17-NOV-15'), LAST_DAY(SYSDATE) FROM DUAL;
LAST_DAY(’17-NOV-15′) LAST_DAY(’17-NOV-15′)
————————- ————————-
30-NOV-15 30-NOV-15
–ADD_MONTHS
ADD_MONTHS(d,n)
d – DATE tipindədir.
n – number tipindədir.
Verilmiş d-tarixinə n-qədər ay əlavə edir.
SELECT ADD_MONTHS('17-NOV-15',1), ADD_MONTHS('17-NOV-15',4) FROM DUAL;
ADD_MONTHS(’17-NOV-15′,1) ADD_MONTHS(’17-NOV-15′,4)
——————————– ———————————
17-DEC-15 17-MAR-16
–MONTHS_BETWEEN
MONTHS_BETWEEN(d1,d2)
d1, d2 – DATE tipindədirlər.
Verilmis tarixlər arasındakı ay fərqini(d1-d2) hesablayır.
SELECT MONTHS_BETWEEN(SYSDATE, '01-JAN-15') FROM DUAL;
MONTHS_BETWEEN(SYSDATE,’01-JAN-15′)
————————————————
10.5430933
–NUMTOYMINTERVAL
NUMTOYMINTERVAL(n, interval_vahidi)
n – ədəd tipindədir.
Interval_vahidi– 2 dəyər ala bilir: YEAR, MONTH.
Verilmiş n ədədini verilmiş vaxt intervalına çevirir və bizə İNTERVAL YEAR TO MONTH tipində dəyər qaytarır.
SELECT NUMTOYMINTERVAL(27,'MONTH') FROM DUAL;
NUMTOYMINTERVAL(27,’MONTH’)
—————————————
2-3
Yəni 27ay = 2il 3 ay.
–NUMTODSINTERVAL
NUMTODSINTERVAL(n, interval_vahidi)
n-ədəd tipindədir.
Interval_vahidi – 4 dəyər ala bilir: DAY, HOUR, MINUTE, SECOND
Verilmiş n ədədini verilmiş vaxt intervalı-na çevirir və bizə INTERVAL DAY TO SECOND tipində dəyər qaytarır.
SELECT NUMTODSINTERVAL(36,'HOUR') FROM DUAL;
NUMTODSINTERVAL(36,’HOUR’)
————————————
1 12:0:0.0
Digər Funksiyalar
–NVL
NVL(e1, e2)
e1, e2 – eyni verilən tipində olmalıdırlar.
NVL funksiyası e1 qiyməti NULL olduğu halda e2 qiymətini qaytaracaq əks halda yenə e1-i qaytaracaq.
SELECT NVL(NULL,0) ANSWER_1,14+NULL-4 ANSWER_2, 14+NVL(NULL,0)-4 ANSWER_3 FROM DUAL;
FIRST_ANSWER SECOND_ANSWER THIRD_ANSWER
———————- ———————- ———————
10
–DECODE
DECODE(sütun,sütun_qiyməti, sütun_alternativ_qiymət )
DECODE funksiyasını ən yaxşı misallarla anlamaq olar məncə.
Məs:
CREATE TABLE TABLE_1(
ID NUMBER PRIMARY KEY,
DOMAIN VARCHAR2(10) NOT NULL);
INSERT INTO TABLE_1 VALUES(1, ‘.COM’);
INSERT INTO TABLE_1 VALUES(2, ‘.ORG’);
INSERT INTO TABLE_1 VALUES(3, ‘.EDU’);
INSERT INTO TABLE_1 VALUES(4, ‘.AZ’);
COMMIT;
SELECT DOMAIN, DECODE(DOMAIN, ‘.COM’, ‘COMMERCIAL’,
‘.ORG’, ‘ORGANIZATIONAL’,
‘.EDU’, ‘EDUCATION’
‘.AZ’, ‘AZERBAIJAN’)
FROM TABLE_1;
–CASE
CASE-i bir misal üzərində izah edim. Eyni DECODE-da yazdığımız kodu CASE ilə yazaq.
SELECT DOMAIN,
CASE DOMAIN
WHEN ‘.COM’ THEN ‘COMMERCIAL’
WHEN ‘.ORG’ THEN ‘ORGANIZATIONAL’
WHEN ‘.EDU’ THEN ‘EDUCATION’
WHEN ‘.AZ’ THEN AZERBAIJAN
END AS “DOMAIN_INFO”
FROM TABLE_1;
NƏTİCƏ EYNİLƏ DECODE FUNKSİYASI ZAMANI ALDIĞIMIZ KİMİ OLACAQ.
–NULLIF
NULLIF(e1,e2)
e1, e2- hər ikisi eyni verilən tipində olmalıdır.
Verilmis e1 və e2 eyni olduğu zaman funksiya NULL qaytaracq əks halda e1 dəyərini qaytaracaq.
Məs:
SELECT SCORE_1, SCORE_2, NULLIF(SCORE_2, SCORE_1) REVISION_ONLY FROM SCORES;
SCORE_1 SCORE_2 REVISION_ONLY
———- ————- ————-
95 95
55 75 75
83 83
Boş olan hissələr NULL-dır, dəqiqləşdirmək üçün kodumuzda kiçik bir dəyişiklik edək.
SELECT SCORE_1, SCORE_2, DECODE(NVL(NULLIF(SCORE_2, SCORE_1), 0), 0, ‘NO_CHANGE’, NULLIF(SCORE_2, SCORE_1)) REVISION_ONLY FROM SCORES;
SCORE_1 SCORE_2 REVISION_ONLY
———- ————- ————-
95 95 NO_CHANGE
55 75 75
83 83 NO_CHANGE
ÇEVİRMƏ FUNKSİYALARI(CONVERSİON FUNCTİONS)
ORACLE SQL-də bir digər funksiya tipi də çevirmə funksiyalarıdır. Bu funksiyalar verilmiş tipdəki məlumatı bir digər verilən tipinə və ya bir digər formata çevirib bizə qaytarırlar.
–TO_NUMBER
TO_NUMBER(e1, format_model)
e1 – ədəd tipinə çevirmək istədiyimiz ifadə
format_model – hansı formatda nəticə almaq istəyiriksə o formatı təyin edirik.Bu şəkildə ədəd tipi üçün olan format modellərinə baxa bilərsiniz.
SELECT TO_NUMBER('$17,000.23','$999,999.99') FROM DUAL;
TO_NUMBER(‘$17,000.23′,’$999,999.99’)
——————————————-
17000.23
SELECT TO_NUMBER('17.000,23', '999G999D99') REFORMATTED_NUMBER FROM DUAL;
REFORMATTED_NUMBER
——————————
17000.23
–TO_CHAR – CHARACTER
TO_CHAR(c)
c – NCHAR, VARCHAR2, CLOB və NCLOB verilənlər tipində ola bilər.
Verilmiş tipdəki dəyərləri VARCHAR2 tipinə çevirir.
SELECT TO_CHAR('Hello') FROM DUAL;
TO_CHAR(‘HELLO’)
———————
Hello
–TO_CHAR – NUMBER
TO_CHAR(n, format_model)
n – ədəd tipindədir.
format_model – hansı formatda nəticə almaq istəyiriksə o formatı təyin edirik.Bu şəkildə ədəd tipi üçün olan format modellərinə baxa bilərsiniz.
SELECT TO_CHAR(198,'$999.99') FROM DUAL;
TO_CHAR(198,’$999.99′)
——————————–
$198.00
–TO_CHAR—DATE
TO_CHAR(d, format_model)
d – tarix və ya tarix intervalıdır
format_model – hansı formatda nəticə almaq istəyiriksə o formatı təyin edirik..Bu şəkildə tarix tipi üçün olan format modellərinə baxa bilərsiniz.
SELECT TO_CHAR(SYSDATE,'DAY, "THE" DD "OF" MONTH, RRRR') FROM DUAL;
TO_CHAR(SYSDATE,’DAY,”THE”DD”OF”MONTH,RRRR’)
————————————————————-
THURSDAY , THE 19 OF NOVEMBER , 2015
SELECT TO_CHAR(SYSDATE,'FMDay, "the" Dd "of" Month, RRRR') FROM DUAL;
(FM – gərəksiz boşluqları aradan qaldırır)
TO_CHAR(SYSDATE,’FMDAY,”THE”DD”OF”MONTH,RRRR’)
————————————————————–
Thursday, the 19 of November, 2015
SELECT TO_CHAR(SYSDATE,'FMDay, "the" Ddth "of" Month, RRRR') FROM DUAL;
TO_CHAR(SYSDATE,’FMDAY,”THE”DDTH”OF”MONTH,RRRR’)
—————————————————————–
Thursday, the 19th of July, 2015
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS AM') FROM DUAL;
TO_CHAR(SYSDATE,’HH24:MI:SSAM’)
—————————————–
01:51:02 AM
–TO_DATE
TO_DATE(c, format_model)
c – char tipindədir.
format_model – hansı formatda nəticə almaq istəyiriksə o formatı təyin edirik.Bu şəkildə tarix tipi üçün format modellərinə baxa bilərsiniz.
SELECT TO_DATE('2015-11-18','RRRR-MM-DD') FROM DUAL;
TO_DATE(‘2015-11-18′,’RRRR-MM-DD’)
—————————————-
18-NOV-15
–TO_TIMESTAMP
TO_TIMESTAMP(c, format_model)
c – char tipindədir.
format_model – hansı formatda nəticə almaq istəyiriksə o formatı təyin edirik.Aşağıdakı şəkildə bu format modelləri verilib.
Nəticə olaraq TIMESTAMP tipində məlumat qaytarır.
SELECT TO_TIMESTAMP('2015-NOV-18 03:36:00:093423', 'RRRR-MON-DD HH24:MI:SS:FF') EVENT_TIME FROM DUAL;
EVENT_TIME
—————————————
18-NOV-15 03.36.00.093423000 AM
–TO_DSINTERVAL
TO_DSINTERVAL(sql_format)
sql_format – INTERVAL DAY TO SECOND(DAYS HH24:MI:SS.FF) tipinə uyğun string-dir.
Nəticə olaraq INTERVAL DAT TO SECOND tipində məlumat qaytarır.
SELECT TO_DSINTERVAL('40 08:30:00.03225') EVENT_TIME FROM DUAL;
EVENT_TIME
————————-
40 8:30:0.032250000
–TO_YMINTERVAL
TO_YMINTERVAL(‘y-m’)
y-m – hər ikisi ədəd tipindədir.
Nəticə olaraq INTERVAL YEAR TO MONTH tipində məlumat qaytarır.
SELECT TO_YMINTERVAL('04-06') EVENT_TIME FROM DUAL;
EVENT_TIME
————–
4-6
Bu mövzumuz uzun oldu biraz 🙂 Səbrlə oxuyacağını düşünürəm, əlimdən gəldiyi qədər izah edici yazmağa çalışdım, inşallah faydalı ola bildim 🙂