PL/SQL built-in functions

Build-in functions for numbers

select TRUNC(123.456, 2) from dual; -- truncates after comma digits, result: 123.45;
select TRUNC(TO_DATE('27-09-92','DD-MM-YY'), 'YEAR') "New Year" from dual; -- truncate the date to years, result '92/01/01;
select MOD(10, 3) from dual; -- modulo, resul: 1;
select round(123.45), round(123.456, 2), round(1234.56, -2) from dual; -- rounding, when 1 rounds to one place after comma, when -1 rounds to tenths, result: 123/ 123,46/ 1200;

Build-in functions for dates
Default date format in SQL Developer is ‘DD/MMM/RR’ and in SQL Plus is ‘DD-MMM-RR. RR means 2000-year compliant, what is different from YY.

select trunc(TO_DATE('27-09-92','DD-MM-YY'), 'YEAR') "New Year" from dual; -- truncate the date to years, result '92/01/01;
select trunc(to_date('20170620', 'rrrrmmdd'), 'MONTH') from dual; -- truncates to first day of the month;
select months_between('01-01-2020', '01-12-2020') from dual;
select months_between('20201201', '20200101') from dual; -- differnce in months between first and second date, result: 11;
select add_months('20200101', 11) from dual; --adds months to given date, result: 2020-12-01;
select last_day('20200101') from dual; -- returns last day of given month;
select next_day(sysdate, 'WTOREK'), next_day(sysdate, 2) from dual; -- both return next tuesday after given date;
select sysdate from dual; -- curent date from server;
select round(sysdate, 'MONTH') from dual; -- rounds date to first or last day of the month;

Build-in functions for strings

SELECT SUBSTR('Test', 0,2) from dual; -- returns substring from to ('Te')
select UPPER('teSt'), LOWER('teSt'), INITCAP('teSt') from dual; -- changes case of given string: TEST test Test;
select LENGTH('Mohl'), LENGTHB('Möhl') from dual; -- calculates the length of given string in chars/ bytes: result is 4/ 5;
select CONCAT(CONCAT('This ', 'is ') , 'concatenated ') from dual; -- appends strings like || operator, exactly two parameters must be passed;
select INSTR('', '.', 1, 1) FROM DUAL; -- searches first position of '.' in given string starting from first char, result: 4;
select INSTR('', '.', 10, 2) FROM DUAL; -- searches second position of '.' in given string starting from 10th char, result: 17;
select LPAD('test', 10, '.') from dual; -- adds '.' string on the left side to have given string of given length, result: '......test';
select TRIM('.' from '...test...'), TRIM(leading '.' from '...test...'), TRIM(trailing '.' from '...test...') from dual; -- removes given char from left and right of given string, result: 'test', 'test...', '...test';
select TRIM(' test') from dual; -- removes blank space from both sides of the string, result: 'test';
select REPLACE('dog', 'd', 'hotd') from dual; --replaces 'd' with 'hotd' in given string, result: 'hotdog';

Build-in functions for nulls

select NVL('test', 'this is null') from dual; -- checks whether first value is null selects the second one, result: 'test';
select NVL(null, 'this is null') from dual; -- result: 'this is null'
select NVL2(null, 'this is not null', 'this is null') from dual; -- selects 2nd value if first is not null,otherwise the 3rd one;
select NULLIF(1,1), NULLIF(1,2) from dual; -- returns null if expressions are equal, otherwise returns first expression;
select COALESCE(null, null, null, 1, 2) from dual; -- resturn first value which is not null;

Build-in functions for conversion

select to_char(sysdate, 'DAY'), to_char(sysdate, 'YEAR') from dual; -- gets day name or week in year number as string, result: "MONDAY", "TWENTY SEVENTEEN";
select TO_DATE('31-12-2017', 'dd-MM-rrrr') from dual; -- converts string to date.
select TO_NUMBER('0,123') from dual; -- converts string to number;

Build-in functions for code flow

IF result > 0 
   THEN dbms_output.put_line("high result");
   ELSE dbms_output.put_line("low result");

select CASE 
		WHEN &&x <= 0 THEN 'cold' 
		WHEN &&x <= 10 THEN 'not cold' 
		ELSE 'warm' END 
		from dual; -- well known if else if clause;
select decode(2, 
        1, 'dog',
        2, 'cat',
        3, 'frog',
		from dual; -- decode is well known switch clause, gets first value and returns matched value for it, result: 'cat';

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s