Şahin Kərimov

Oracle SQL funksiyaları

· Sql dərsləri

Təksətirli Oracle SQL funksiyaları.

Həm tələbələrimiz, həm də bütün maraqlanan insanlar üçün Arifə və Fidan xanım belə bir izahlı yazı hazırladılar. Buyurun, faydalanın.

Simvol tipinə tətbiq olunan funksiyalar

1.lower(text_argument) – mətn tipli arqumentin bütün simvollarını kiçildir.

Nümunə:

SQL
select lower('ORACLE') from dual; /* nəticə: oracle */
select lower('Sql') from dual; /* nəticə: sql */

2.upper(text_argument) – mətn tipli arqumentin bütün simvollarını böyüdür.

Nümunə:

SQL
select upper('oracle') from dual; /* nəticə: ORACLE */
select upper('Sql') from dual; /* nəticə: SQL */

3.initcap(text_argument) – mətn tipli arqumentdə bütün sözlərin ilk hərflərini böyüdür, digər hərflərini isə kiçildir.

Nümunə:

SQL
select initcap('ORACLE sql') from dual; /* nəticə: Oracle Sql */

4.length(text_argument) –mətn tipli  arqumentin uzunluğunu qaytarır.

Nümunə:

SQL
select length('oracle') from dual; /* nəticə: 6 */

5.concat(text_argument_1, text_argument_2) – mətn tipli 2 arqument  qəbul edir.Verilmiş arqument ləri birləşdirir.

Nümunə:

SQL
select concat('oracle','sql') from dual; /* nəticə: oraclesql */

6.substr(text_argument,n,m) – mətn tipli arqumentin n-ci simvoldan başlayaraq m simvolunu qaytarır. m-in qiymətini qeyd etmədikdə mətnin  sonuna kimi olan hissəni qaytarır. n arqument i mənfi olduqda substr funksiyası sondan saymağa başlayır.

Nümunə:

SQL
select substr('oracle',2,3) from dual; /* nəticə: rac */
select substr('oracle',3) from dual; /* nəticə: acle */
select substr('oracle',-4) from dual; /* nəticə: acle */
select substr('oracle',-5,2) from dual; /* nəticə: ra */

7. instr(text_argument,search_argument, n,m) – mətn tipli arqument -də n-ci simvoldan başlayaraq m-ci rast gəldiyi search_argumentin  inkeksini qaytarır.  n və m qeyd edilmdikdə, ilk simvoldan başlayaraq ilk rast gəldiyi indeksi qaytarır. n mənfi olduqda sonuncu simvoldan başlayaraq m-ci rast gəldiyi indeksi qaytarır.search_argument verilmiş text_Argument içərisində olmadıqda instr nəticə olaraq 0 qaytarır.

Nümunə:

SQL
select instr('programming','g',1,2) from dual; /* nəticə: 11 */
select instr('programming''m',1,1) from dual; /* nəticə: 7 */
select instr('programming','m') from dual; /* nəticə: 7 */
select instr('programming','ing',1,1) from dual; /* nəticə: 9 */
select instr('programming','a',-1,1) from dual; /* nəticə: 6 */
select instr('programming','u') from dual; /* nəticə: 0 */

8.lpad(text_argument1, n,text_argument2) – mətn tipli text_argument1-I text_argument2-I soldan artırmaqla n simvol uzunluğuna tamamlayır. text_argument2 qeyd edilmədikdə  boşluq ilə tamamlayır.Əgər n mətnin ümumi uzunluğundan kiçik olarsa, lpad  verilmiş mətnin  ilk n  sayda simvolunu qaytarır.

Nümunə:

SQL
select lpad('oracle',10,'*') from dual; /* nəticə: ****oracle */
select lpad('oracle',5) from dual; /* nəticə: oracl */

9.rpad(text_argument1, n,text_argument2) – mətn tipli text_argument1-i text_argument2-ni sağdan artıqmaqla n simvol uzunluğuna tamamlayır. text_argument2 qeyd edilmədikdə  boşluq ilə tamamlayır.Əgər n mətnin ümumi uzunluğundan kiçik olarsa, rpad  verilmiş mətnin ilk n  sayda simvolunu qaytarır.

Nümunə:

SQL
select rpad('oracle',10,'*') from dual; /* nəticə: oracle**** */
select rpad('oracle',3) from dual; /* nəticə: ora */

10.rtrim(text_argument1, text_argument2) –  text_argument1-in sonundan text_argument2-ni silir.Text_argument2 qeyd edilmədikdə sondakı  boşluqları silir.

Nümunə:

SQL
select rtrim('oracle','e') from dual; /* nəticə: oracl */
select rtrim('oracleee','e') from dual; /* nəticə: oracl */
select rtrim('500000','0') from dual; /* nəticə: 5 */
select rtrim('oracle   ') from dual; /* nəticə: oracle ( boşluqlar silindi) */

11.ltrim(text_argument1, text_argument2) – text_argument1-in əvvəlindən text_argument2-ni silir.Text_argument2 qeyd edilmədikdə  əvvəldəki boşluqları silir.

Nümunə:

SQL
select ltrim('oracle','o') from dual; /* nəticə: racle */
select ltrim('oracle','ora') from dual; /* nəticə: cle */
select ltrim('55000','5') from dual; /* nəticə: 000 */
select ltrim(' oracle') from dual; /* nəticə: oracle ( boşluqlar silindi) */

12.trim(text_argument1, text_argument2) – text_argument1-in əvvəlindən və sonundan text_argument2-ni silir.text_argument2 qeyd edilmədikdə əvvəldən və sondan boşluqları silir.

Nümunə:

SQL
select ltrim('alabama', 'a') from dual; /* nəticə: labama */
select rtrim ('alabama', 'a') from dual; /* nəticə: alabam */
select trim('    alabama          ') from dual; /* nəticə: alabama */

trim(both|leading|trailing  ‘trim_argument’ from text_argument) – text_argument1- in  əvvəlindən ( leading) , sonundan ( leading) və ya həm əvvəlindən həm də sonundan ( both) trim_argumenti silir. 1-ci arqument  qeyd edilmədikdə  both götürülür, yəni  trim_argumenti həm əvvəldən həm də sondan silir. trim_argument qeyd edilmədikdə boşluqları silir.

Nümunə:

SQL
select trim(leading 'a' from 'alabama') from dual; /* nəticə: labama */
select trim (trailing 'a' from 'alabama') from dual; /* nəticə: alabam */
select trim(both 'a' from 'alabama') from dual; /* nəticə: labam */
select trim('a' from 'alabama') from dual; /* nəticə: labam */
select trim('    alabama    ') from dual; /* nəticə: alabama */

13.replace(text_argument,replace_string,replace_with) – text_argument-dəki replace_String hissəsini replace_with ilə əvəz edir. replace_with verilmədikdə boşluq ilə əvəz edilir.

Nümunə:

SQL
select replace('oracle','r','d') from dual; /* nəticə: odacle */
select replace('oracle','le','la') from dual; /* nəticə: oracla */
select replace('oracle','r') from dual; /* nəticə: oacle */

14.translate(text_argument,translate_string,translate_with) – text_argument-dəki translate_string-in hər bir simvolunu translate_with-dəki uyğun simvolla əvəz edir.

Nümunə:

SQL
select translate('alabama', 'la','ku') from dual; /* nəticə: ukubumu */
select translate('123abc321','123','789') from dual; /* nəticə: 789abc987 */

15.reverse(text_argument) – verilmiş mətni tərsinə çevirir.

Nümunə:

SQL
select reverse('oracle') from dual; /* nəticə: elcaro */

NUMBER functions

Rəqəm tipi ilə işləyən funksiyalar

  1. Round(number_arqument1, number_arqument2 ) – Yuvarlaqlaşdırma funksiyası ən çox 2 arqument qəbul edir.

1-ci arqument  funksiyanın tətbiq olunduğu sütunu göstərir.

2-ci arqument  isə yuvarlaqlaşdırmanın neçə onluğa qədər olduğunu göstərir.

2-ci arqument  müsbət olduqda kəsr hissədə,mənfi olduqda isə tam hissədə yuvarlaqlaşma gedir.

2-ci arqumentin default qiyməti 0-dır

SQL
select round(2231.38, 0) from dual; /* nəticə: 2231 */
select round(2231.68, 0) from dual; /* nəticə: 2232 */
select round(2231.68) from dual; /* nəticə: 2232 */
select round(2231.68, 1) from dual; /* nəticə: 2231.7 */
select round(2231.674764, 3) from dual; /* nəticə: 2231.675 */
select round(2231.674264, 3) from dual; /* nəticə: 2231.674 */
select round(2261.67, -1) from dual; /* nəticə: 2260 */

-1 olduğu üçün nöqtədən 1 vahid sola doğru yuvarlaqlaşdırır.

SQL
select round(2261.67, -2) from dual; /* nəticə: 2300 */

-2 olduğu üçün nöqtədən 1 vahid sola doğru yuvarlaqlaşdırır.

SQL
select round(43.455, -1) from dual; /* nəticə: 40 */
select round(46.923, -1) from dual; /* nəticə: 50 */
--

2.Trunc(number_arqument1, number_arqument2) – Alt mərtəbəyə yuvarlaqlaşdırma funksiyasıdır.Ən çox 2 arqument  qəbul edir.

1-ci arqument  funksiyanın tətbiq olunduğu sütunu göstərir.

2-ci arqument  isə yuvarlaqlaşdırmanın neçə onluğa qədər olduğunu göstərir.

Daxil edilən 1 -ci arqument i,2-ci arqumentin qiyməti qədər aşağı onluğa yuvarlaqlaşdırır.

2-ci arqument  müsbət olduqda kəsr hissədə,mənfi olduqda isə tam hissədə yuvarlaqlaşma gedir.

2-ci arqumentin default qiyməti 0-dır.

SQL
select trunc(2231.37, 0) from dual; /* nəticə: 2231 */
select trunc(2231.67, 0) from dual; /* nəticə: 2231 */
select trunc(2231.67) from dual; /* nəticə: 2231 */
select trunc(2231.67, 1) from dual; /* nəticə: 2231.6 */
select trunc(2231.674564, 3) from dual; /* nəticə: 2231.674 */
select trunc(2268.67, -1) from dual; /* nəticə: 2260 */
select trunc(2268.67, -2) from dual; /* nəticə: 2200 */
  1. Floor(number_arqument ) – bir aşağı ədədə yuvarlaqlaşdırır.1 arqument i olur.
SQL
select floor(234.73) from dual; /* nəticə: 234 */
  1. Ceil(number_arqument ) bir yuxarı ədədə yuvarlaqlaşdırır.1 arqument i olur.
SQL
select ceil(234.23) from dual; /* nəticə: 235 */
  1. Mod(number_arqument1, number_arqument2) – funksiyası qalıq tapmaq üçün istifadə olunur.2 arqument i olur.

1-ci arqument i bölünən,2-ci arqument i böləndir.

SQL
select mod(10,3) from dual; -- 10/3 =3(1)
select mod(15,4) from dual; -- 15/4 =3(3)
select employee_id as "Odd",
last_name
from employees
where mod(employee_id, 2) = 1;
select employee_id as "Even",
last_name
from employees
where mod(employee_id, 2) = 0;

6.Abs(number_arqument ) – funksiyası bir ədədin mütləq qiymətini almaq üçün istifadə olunur.

Yəni bir ədədin işarəsiz dəyəridir(Məsafə kimi başa düşülür)

SQL
select abs(-50) from dual; /* nəticə: 50 */
select abs(50) from dual; /* nəticə: 50 */
  1. Greatest(number_arqument ) – Daxil olunan arqument lər çoxluğundan ən böyük qiyməti qaytarır.
SQL
select greatest(5, 10) from dual; /* nəticə: 10 */
select greatest(5, 10, 3) from dual; /* nəticə: 10 */

8.Least(number_arqument ) – Daxil olunan arqument lər çoxluğundan ən kiçik qiyməti qaytarır

SQL
select least(5, 10, 3) from dual; /* nəticə: 3 */
select
first_name,
last_name,
length(first_name),
length(last_name),
greatest(length(first_name), length(last_name))
from employees;

Datatype conversion

Məlumat tipinin dəyişdililməsi funksiyaları

  1. To_char funksiyasının tarix tipinə tətbiqi

To_char(date_argument, format_model) –  tarix tipli məlumatı müxtəlif formatlarda.

Format modellər

1.yyyy- ilin rəqəmlərlə ifadəsi  ( məsələn, 2022)

  1. year – ilin sözlərlə ifadəsi (twent –twenty two)
  2. mm – ayın ikirəqəmli ifadəsi ( oktyabrr 10)
  3. month – ayın bütöv adı (məsələn, october)
  4. mon – ayın adının 3 hərfli abreviaturası (məsələn oct)
  5. dy – həftənin gününün adının 3 hərli abreviaturası ( məsələn, fri,sun)
  6. day – həftənin gününün bütöv adı ( məsələn, Sunday)
  7. dd – ayın gününün rəqəmli ifadəsi (məsələn, 15,23)
  8. hh12 – 12 saatlıq format
  9. hh24- 24 saatlıq format

11.MI – dəqiqə ( 0-59)

12 ss – saniyə (0-59)

  1. To_char funksiyasının rəqəm tipinə tətbiqi

To_char(number_argument,format_model) – rəqəm tipli arqument i müxtəlif formatlı mətn tipinə çevirir.

Nümunə:

SQL
select to_char(1000.45, '$9,999.00') from dual; /* nəticə: $1,000.45 */

Format modellər:

  1. 9 – rəqəmi ifadə edir
  2. 0 – sıfrı ifadə edir
  3. $ – dollar simvolu
  4. D – kəsri hissəni ayırır (İngilis formatında default qiyməti: . )
  5. G – minlik qruplarını ayırır (İngilis formatında default qiyməti: , )
  6. L –valyuta simvolu
  1. to_date(text_argument, format_model) – verilmiş mətn tipli arqument i tarix tipinə çevirir.

Format modeller :  to_char with dates format modellerinə bax.

Nümunə:

SQL
select to_date('2022/10/25','yyyy/mm/dd') from dual; /* nəticə: tarix tipində məlumat qaytarır. */
  1. to_number(text_argument,format_model) – mətn tipli məlumatı rəqəm tipinə çevirir.

Format modellər : to_char with numbers, format modellərə bax.

Nümunə:

SQL
select to_number('$500','$999') from dual; /* nəticə: 500 */

NULL FUNCTIONS

1.Nvl(arqument1,arqument2) – funksiyası 2 arqument qəbul edir.Tətbiq olunan sütunda əgər null dəyərlər varsa, funksiyanın 2-ci parametri nəticədə qayıdır,null deyilsə elə 1-ci arqument qayıdır.

1-ci arqument funksiyanın tətbiq olunduğu sütunu bildirir.

2-ci arqument isə 1-ci parametrin dəyəri null olduğu halda qayıdır.

2-ci parametrin tipi 1-ci parametrə çevrilə bilən olmalıdır (1-ci number,2-ci varchar olsa onda error qayıdacaq).

SQL
select nvl(null,'1') from dual; /* nəticə: 1 */
select nvl('','1') from dual; /* nəticə: 1 */
select nvl('sql','2') from dual; /* nəticə: sql */
select commission_pct, nvl(commission_pct, 0) from employees;

Commission_pct sütunundakı dəyər null-dursa 0 qaytaracaq,deyilsə elə commission_pct-nin özünü qaytaracaq.

SQL
select
commission_pct,
nvl(commission_pct, ' ') -- ikinci arqument number-e çevrilə bilmir, ona göre Error-dur
from employees;
select
commission_pct,
nvl(commission_pct, '11') -- ikinci arqument char olsa da number-ye çevrilə bilir, ona göre OK-dir
from employees;
select
department_name,
nvl(department_name, department_id) -- OK-dir
from departments;
select
department_name,
nvl(department_id, department_name) -- Error-dur
from departments;
  1. Coalesce(arqument1,arqument2……arqumentN) – 2 və daha çox arqument qəbul edə bilir.

Bu funksiya arqumentlər arasında, əvvəldən sonra məntiqi ilə rast gəldiyi ilk null olmayan dəyəri qaytarır.

Əgər bütün parametrlər null olsa nəticə null olacaq.

Bütün parametrlərin tipi eyni olmalıdır.

SQL
select coalesce(1, null, 2, null, 3, 4, 5) from dual; /* nəticə: 1 */
select coalesce(null, null, 2, null, 3, 4, 5) from dual; /* nəticə: 2 */
select coalesce(null, null, null, null, 3, 4, null) from dual; /* nəticə: 3 */
select
commission_pct,
coalesce(commission_pct, ' ') -- paramerlərin  datatype-lari fərqlidir, ona görə Error-dur
from employees;
select
commission_pct,
coalesce(commission_pct, '11') -- paramerlərin datatype-lari fərqlidir, ona görə Error-dur
from employees;
select
department_name,
coalesce(department_name, department_id) -- error
from departments;
select
department_name,
coalesce(department_name, to_char(department_id)) /*düzgündür, çünki 2-ci parametrə to_char funksiyası tətbiq edilərək  char-a çevrildi.*/
from hr.departments;

3.Nvl2(arqument1,arqument2, arqument3) – funksiyası 3 arqument qəbul edir.

Əgər 1-ci paramerin dəyəri null deyilsə ,2-ci parametrin dəyəri qayıdır.

Əgər 1-ci parametrin dəyəri null olarsa 3-cü parametrin dəyəri qayıdır.

Hər zaman 3-cü arqumentin tipini 2-ci arqumentin tipinə çevirməyə çalışır.

SQL
select nvl2('Burda null olmayan qiymet var','A','B') from dual; /* nəticə: A */
select
commission_pct,
nvl2(commission_pct,'var', 'yoxdur') /*commission_pct null olmasa  'var',null olsa  'yoxdur' qayıdacaq nəticədə*/
from employees;
select
commission_pct,
nvl2(commission_pct, 1, 0)—commission_pct null olmasa  1 ,null olsa  0 qayıdacaq nəticədə
from employees;
  1. Nullif(arqument1, arqument2) – funksiyası 2 arqument qəbul edir.

2 parametrin bərabər olub olmamasını yoxlayır.Bərabərdirsə null,deyilsə 1- ci parametri qaytarır.

SQL
select nullif(5,4) from dual /* nəticə: 5 */
select nullif('a','a') from dual /* nəticə: null */
select
length(last_name),
length(first_name),
nullif(length(last_name),length(first_name))
from employees;
select nullif(1,2), nullif(2,1), nullif(1,1) from dual;

IF THEN ELSE məntiqi ilə işləyən funksiyalar

  1. Simple Case. Case-də yazılan dəyər when-də yazılan dəyərlər ilə müqayisə olunur.Müqayisə yuxarıdan aşağı gedir.

Əgər hansısa when shərti ödənilirsə artıq həmin sətir üçün end blokuna keçid olunur,yəni digər when -lərin yoxlanması dayandırılır.

Simple case yalnız bərabərlik şərtinin yoxlanılması üçündür(‘<‘,’>’,'<=’,’>=’,’!=’,’like’,’not like’ ,’in’,’not in’ olmaz)

Heç bir şərt ödənmədikdə else operatoruna keçid olunur. Heç bir şərt ödənmirsə və else də  qeyd olunmayıbsa,həmin sətir üçün dəyər null olacaq.

SQL
select last_name, job_id,
case job_id
when 'SA_REP' then 'Reporter'
when 'IT_PROG' then 'Programmer'
when 'SA_MAN' then 'Manager'
end AS "Prof"
from employees;
select last_name, job_id,
case job_id
when 'SA_REP' then 'Reporter'
when 'IT_PROG' then 'Programmer'
when 'SA_MAN' then 'Manager'
else 'Other'
end AS "Prof"
from employees;
  1. Search Case – Hər sətir üçün when-də yazılan şərtlər yuxarıdan aşağı yoxlanılır

Əgər hansısa when shərti ödənilirsə artıq həmin sətir üçün end blokuna keçid olunur,yəni digər when -lərin yoxlanması dayandırılır.

Heç bir şərt ödənmədikdə else operatoruna keçid olunur. Heç bir şərt ödənmirsə və else də  qeyd olunmayıbsa,həmin sətir üçün dəyər null olacaq.

SQL
select last_name, department_id, job_id,
case
when department_id = 50 then JOB_ID
when job_id like 'SA%' then 'SA-XXXXXXX'
when job_id like '%PROG' then 'Programmer'
else 'Other'
end as "Prof"
from employees;
  1. Decode(arqument1, arqument2, … arqumentN) – Funksiyası simple case-in kompaktlaşmış formasıdır.

Aşağıdakı nümunədə Job_id sütununa baxırıq.

Əgər  job_id=’SA_REP’ olsa onda ‘Reporter’ yazılacaq.

Yox əgər job_id=’IT_PROG’ olsa onda  ‘Programmer’ yazılacaq.

Yox əgər job_id=’SA_MAN’ olsa onda ,’Manager’ yazılacaq.

Bunların heç biri olmasa onda  ‘Other’ yazılacaq.

SQL
select last_name, job_id,
decode(job_id,
'SA_REP'    ,'Reporter',
'IT_PROG'   ,'Programmer',
'SA_MAN'    ,'Manager',
'Other') AS "Prof-Decode"
from employees;

Else halı decode-da qeyd olunmasa null qaytarır.

SQL
select last_name, job_id,
decode(job_id,
'SA_REP'    ,'Reporter',
'IT_PROG'   ,'Programmer',
'SA_MAN'    ,'Manager'
)
AS "Prof-Decode"
from employees;

← Bütün yazılar