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).

  1. SELECT CONCAT(‘Hello, ‘, ‘world!’) FROM DUAL;
  2. SELECT ‘Hello, ‘ || ‘world!’ FROM DUAL;

A 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.
  • BOTHtrim_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.

soundex

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;

 DECODE

–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 e1e2 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 🙂