Single line function
Single line function:
Operation data object
Accept parameters and return a result
Transform only one row
One result per row
Data types can be converted
Can be nested
The parameter can be a column or a value
function_name [(arg1, arg2,...)]
Single-Row Functions
Character function
LOWER('SQL Course')--sql course
UPPER('SQL Course')--SQL COURSE
INITCAP('SQL Course')--Sql Course
SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'higgins'; no rows selected
SQL> SELECT employee_id, last_name, department_id
2 FROM employees
3 WHERE LOWER(last_name) = 'higgins';
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID
----------- ------------------------- -------------
205 Higgins 110
1 row selected
Character control function
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(job_id, 4) = 'REP';
EMPLOYEE_ID NAME JOB_ID LENGTH(LAST_NAME) Contains 'a'?
----------- --------------------------------------------- ---------- ----------------- -------------
174 EllenAbel SA_REP 4 0
176 JonathonTaylor SA_REP 6 2
178 KimberelyGrant SA_REP 5 3
202 PatFay MK_REP 3 2
150 PeterTucker SA_REP 6 0
151 DavidBernstein SA_REP 9 0
152 PeterHall SA_REP 4 2
153 ChristopherOlsen SA_REP 5 0
154 NanetteCambrault SA_REP 9
Digital function
ROUND: ROUND
ROUND(45.926, 2) 45.93
TRUNC: truncation
TRUNC(45.926, 2) 45.92
MOD: remainder
MOD(1600, 300) 100
DUAL is a 'pseudo table' that can be used to test functions and expressions
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),
2 ROUND(45.923,-1)
3 FROM DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
1 row selected
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923),
2 TRUNC(45.923,-2)
3 FROM DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-2)
--------------- ------------- ----------------
45.92 45 0
1 row selected
SQL> SELECT last_name, salary, MOD(salary, 5000) 2 FROM employees 3 WHERE job_id = 'SA_REP'; LAST_NAME SALARY MOD(SALARY,5000) ------------------------- ---------- ---------------- Abel 11000.00 1000 Taylor 8600.00 3600 Grant 7000.00 2000
Date data in Oracle actually contains two values: date and time.
SQL> SELECT last_name, hire_date 2 FROM employees 3 WHERE last_name like 'G%'; LAST_NAME HIRE_DATE ------------------------- ----------- Grant 1999/5/24 Gietz 1994/6/7 Greenberg 1994/8/17
The SYSDATE function returns:
date
time
Date operation
Add or subtract a number from the date and the result remains the date.
Subtracting two dates returns the number of days difference between the two dates.
The date cannot be added, which is meaningless
You can divide the number by 24 to add or subtract days from the day period
SQL> SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS 2 FROM employees 3 WHERE department_id = 90; LAST_NAME WEEKS ------------------------- ---------- King 1787.52749 Kochhar 1669.38463 De Haan 1496.52749 3 rows selected
Date function
SQL> SELECT MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') FROM DUAL;
MONTHS_BETWEEN('01-SEP-95','11-JAN-94')
---------------------------------------
19.6774193548387
1 row selected
SQL> SELECT ADD_MONTHS('18,SEP,21',2) FROM DUAL;
ADD_MONTHS('18,SEP,21',2)
-------------------------
2021/11/18
1 row selected
SQL> SELECT NEXT_DAY ('01-SEP-21','FRIDAY') FROM DUAL;
NEXT_DAY('01-SEP-21','FRIDAY')
------------------------------
2021/9/3
1 row selected
SQL> SELECT LAST_DAY('10-SEP-21') FROM DUAL;
LAST_DAY('10-SEP-21')
---------------------
2021/9/30
1 row selected
Assume SYSDATE = '18-SEP-21'): SQL> SELECT ROUND(SYSDATE,'MONTH') FROM DUAL; ROUND(SYSDATE,'MONTH') ---------------------- 2021/10/1 1 row selected SQL> SELECT ROUND(SYSDATE ,'YEAR') FROM DUAL; ROUND(SYSDATE,'YEAR') --------------------- 2022/1/1 1 row selected SQL> SELECT TRUNC(SYSDATE ,'MONTH') FROM DUAL; TRUNC(SYSDATE,'MONTH') ---------------------- 2021/9/1 1 row selected SQL> SELECT TRUNC(SYSDATE ,'YEAR') FROM DUAL; TRUNC(SYSDATE,'YEAR') --------------------- 2021/1/1
Implicit data type conversion
Display data type conversion
TO_ Conversion of date by char function
TO_CHAR(date, 'format_model') //Format: //Must be enclosed in single quotes and case sensitive. //Can contain any valid date format. //Dates are separated by commas. SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') FROM dual;
Date format element
Elements of date format
HH24:MI:SS AM //15:45:32 PM DD "of" MONTH //12 of OCTOBER
SQL> SELECT last_name, 2 TO_CHAR(hire_date, 'DD Month YYYY') 3 AS HIREDATE 4 FROM employees; LAST_NAME HIREDATE ------------------------- --------------------- King 17 June 1987 Kochhar 21 September 1989 De Haan 13 January 1993
SQL> select employee_id,last_name,hire_date
2 from employees
3 where to_char(hire_date,'yyyy-mm-dd')='1987-09-17';
EMPLOYEE_ID LAST_NAME HIRE_DATE
----------- ------------------------- -----------
200 Whalen 1987/9/17
1 row selected
TO_ The date function converts characters
TO_DATE(char[, 'format_model'])
TO_DATE('2012 October 29, 2008:10:21','yyyy"year"mm"month"dd"day" hh:mi:ss')
From dual;
Conversion of numeric types
TO_CHAR(number, 'format_model')
SQL> SELECT TO_CHAR(salary, '$99,999.00') SALARY
2 FROM employees
3 WHERE last_name = 'Ernst';
SALARY
-----------
$6,000.00
1 row selected
TO_NUMBER(char[, 'format_model'])
TO_NUMBER('￥1,234,567,890.00','L999,999,999,999.99')
----------------------------------------------------
1234567890
1 row selected
Generic functions (these functions apply to any data type as well as null values)
NVL (expr1, expr2) NVL2 (expr1, expr2, expr3) NULLIF (expr1, expr2) COALESCE (expr1, expr2, ..., exprn)
NVL function
Convert null value to a known value:
The data types that can be used are date, character and number.
General form of function:
NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')
SQL> SELECT last_name, salary, NVL(commission_pct, 0), 2 (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL 3 FROM employees; LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL ------------------------- ---------- --------------------- ---------- King 24000.00 0 288000 Kochhar 17000.00 0 204000 De Haan 17000.00 0 204000 Hunold 9000.00 0 108000 Ernst 6000.00 0 72000 Lorentz 4200.00 0 50400
SELECT last_name, salary, commission_pct,
NVL2(commission_pct,
'SAL*10', 'SAL') income
FROM employees WHERE department_id IN (50, 80)
LAST_NAME SALARY COMMISSION_PCT INCOME
------------------------- ---------- -------------- --------
Mourgos 5800.00 SAL
Rajs 3500.00 SAL
Davies 3100.00 SAL
Matos 2600.00 SAL
Vargas 2500.00 SAL
Zlotkey 10500.00 0.20 SAL+COMM
Abel 11000.00 0.30 SAL+COMM
Taylor 8600.00 0.20 SAL+COMM
NULLIF (expr1, expr2): equal returns NULL, unequal returns expr1
SQL> SELECT first_name, LENGTH(first_name) "expr1", 2 last_name, LENGTH(last_name) "expr2", 3 NULLIF(LENGTH(first_name), LENGTH(last_name)) result 4 FROM employees; FIRST_NAME expr1 LAST_NAME expr2 RESULT -------------------- ---------- ------------------------- ---------- ---------- Steven 6 King 4 6 Neena 5 Kochhar 7 5 Lex 3 De Haan 7 3 Alexander 9 Hunold 6 9 Bruce 5 Ernst 5 Diana 5 Lorentz 7 5
COALESCE function
COALESCE is a function, (expression_1, expression_2,..., expression_n) refers to each parameter expression in turn. It stops and returns the value when it encounters a non null value. If all expressions are null, a null value will eventually be returned. Using COALESCE is that most expressions that contain null values will eventually return null values.
The advantage of COALESCE over NVL is that COALESCE can handle alternating multiple values at the same time.
If the first expression is empty, the next expression will be returned and the other parameters will be coalesced.
SQL> SELECT last_name, 2 COALESCE(commission_pct, salary, 10) comm 3 FROM employees 4 ORDER BY commission_pct; LAST_NAME COMM ------------------------- ---------- Banda 0.1 Marvins 0.1 Bates 0.15 Greene 0.15 King 24000 Kochhar 17000
Conditional expression
Using IF-THEN-ELSE logic in SQL statements
Two methods are used:
CASE expression
DECODE function
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
DECODE function
When IF-THEN-ELSE logic is required:
DECODE(col|expression, search1, result1 ,
[, search2, result2,...,]
[, default])
SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROM employees
WHERE department_id = 80;
Nested function
Single line functions can be nested.
Nested functions are executed from the inside out.
SQL> SELECT last_name, 2 NVL(TO_CHAR(manager_id), 'No Manager') 3 FROM employees 4 WHERE manager_id IS NULL; LAST_NAME NVL(TO_CHAR(MANAGER_ID),'NOMANAGER') ------------------------- ---------------------------------------- King No Manager
summary
Use functions to calculate data
Modifying data using functions
Use functions to control the output format of a set of data
Use the function to change the display format of the date
Changing data types using functions
Using NVL functions
Use IF-THEN-ELSE logic