# oracle database single row function

### 2. Numerical functions

#### 2.4 Take the remainder - mod

2.5 Examples

------------------Commonly used numerical functions-------------------------
rounding function round query Daily salary and rounded to 2 decimal places
select empno, ename, round(sal/30,2) from emp;-truncation
function trunc queries daily salary and truncated to 2 decimal places
select empno, ename, trunc(sal/30, 2) from emp;
– remainder function mod to query employees whose numbers are even numbers
select * from emp where mod(empno,2)=0;

### 3. Date and time functions

#### 3.8 Date and time functions-trunc

Instance

-----------------Date and time function----------------------
sysdate queries the day before the current time of the system, Now time, the next day
select sysdate-1, sysdate, sysdate from dual;
- months_between calculate how long the employee has been in the company and round to the nearest day
select empno, ename, round(months_between(sysdate,hiredate)) from emp where empno = 7369;
– months_between Calculate how long the employee has been in the company and round it to the month
select empno, ename, round(months_between(sysdate,hiredate)/12) from emp where empno = 7369;
- add_months query the year after each employee has been employed for 30 years
select empno, ename , add_months(hiredate,30*12) from emp;
– next_day query the date of next Monday
select next_day(sysdate,'Monday') from dual;
– last_day query the last day of the current month
select last_day(sysdate) from emp;
– round Query the resignation date year and month
select hiredate, round(hiredate,'year'), round(hiredate,'month') from emp;
– trunc intercept the resignation date year and month
select hiredate, trunc(hiredate,'year'), trunc(hiredate,'month') from emp;

#### 3.9 Get system date processing

​ Current date: sysdate

​ One day before the current date: sysdate-interval'1' day

### 4. Conversion function

#### 4.3 Date to character --to_char

4.3 Digit to character --to_char

#### 4.5 Turn data --to_number

Instance

-----------------------Conversion function------------------------- ------------
Date to string to_char Query the date of resignation and output in the specified format
select ename, to_char(hiredate,'YYYY-MM-DD') from emp;
select ename, to_char(hiredate,' yyyy-mm-dd') from emp;
select ename, hiredate, to_char(hiredate,'yyyy"年"mm"month"dd"日"') from emp;
select ename, hiredate, to_char(hiredate,'DD-MON -RR','NLS_DATE_LANGUAGE=AMERICAN') FROM EMP; -Number
to character to_char to convert the value to character
select ename, to_char(sal,'L999,999,999,99') from emp;
select ename, to_char(sal,'\$999,999,999 ,99') from emp;
select ename, to_char(sal,'L000,000,000,00') from emp;
select ename, to_char(sal,'\$000,000,000,00') from emp;
– transfer date to_date query for employment after 1982 Employee information
select ename, hiredate from emp where hiredate> to_date('1981-12-31','yyyy-mm-dd');
select * from emp;
– to number to_number
select ename, sal from emp where sal> to_number('¥ 1600','L9999999999999');
select ename, sal from emp where sal> to_number('\$1600','\$999999999999');

### 5. General functions

#### 5.5 coalesce

Instance

--------------------General functions -----------------------
nvl handles null
select ename , sal, comm, nvl(sal+comm, sal) from emp;
– nvl2 handles null
select ename, sal, comm, nvl2(comm,sal+comm,sal) from emp;
– nullif queries the entry date of all employees, and Distinguish between employees who joined at the beginning of this month and other employees
select ename, hiredate, nullif(hiredate, trunc(sysdate,'month')) from emp;
- coalesce query the result of salary plus subsidy, if the subsidy is null, it will display the salary
select ename, sal, comm, coalesce(sal+comm,sal) from emp;

### 6. Conditional expression

#### 6.3 Expression --decode

Instance

------------------Conditional expression---------------------
case expression
-want to display all The position of the employee, but these position requirements are replaced with Chinese display
-clark: clerk salesman: sales manager: manager analyst: analyst president: president
select ename,
case job
when'CLARK' then'clerk
' when'SALESMAN' then'sales'
when 'mANAGER' then 'manager'
the when 'aNALYST' the then 'analysts'
ELSE 'president'
End
from emp;
- decode expression
- all employees want to display jobs, but these jobs requires replaced by Chinese show
- clark: clerk salesman: sales manager: manager analyst: analyst president: president
select ename, decode(job,'CLARK','clerk','SALESMAN','sales','MANGER','manager','ANALYST','analysis Employee','President') from emp;
– query salary range
select ename,sal,
case
when sal <2000 then'low salary'
when sal <5000 then'medium salary'
else'high salary'
end
from emp;

## ------------------------Nested function----------------------- – Want to display the date of the next Monday three months after the hire date, and the date format is: 2017-01-06 select ename, hiredate,to_char(hiredate,'YYYY-MM-DD'), to_char(next_day (add_months(hiredate,3),'Monday'),'YYYY-MM-DD') from emp; -Display the result of the employee's daily salary and rounded to 2 decimal places, and then use'¥1,182.19' for the salary qualification formula Formatting in example form

– Want to display the date of the next Monday three months after the hire date, and the date format is: 2017-01-06
select ename, hiredate,to_char(hiredate,'YYYY-MM-DD'), to_char(next_day (add_months(hiredate,3),'Monday'),'YYYY-MM-DD') from emp;
-Display the result of the employee's daily salary and rounded to 2 decimal places, and then use'¥1,182.19' for the salary qualification formula Format in example form
select ename, sal, to_char(round(sal/30,2),'L999,999.99') from emp;