oracle database single row function

1. Character function

1. Commonly used character functions

Insert picture description here

2. Case control function

[External link image transfer failed. The source site may have an anti-hotlinking mechanism. It is recommended to save the image and upload it directly (img-mhtiXmvt-1622621438268)(C:\Users\user\AppData\Roaming\Typora\typora-user-images\ 1581125585759.png)]

2. Numerical functions

2.1 Commonly used numerical functions

Insert picture description here

2.2 Rounding - round

Insert picture description here

2.3 Interception – trunc

2.4 Take the remainder - mod

Insert picture description here

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.1 commonly used date and time functions

Insert picture description here

3.2 Date and time function - sysdate

Insert picture description here

3.3 Date and time functions - months_between

Insert picture description here

3.4 Date and time functions-add_months

Insert picture description here

3.5 Date and time functions-next_day

Insert picture description here

3.6 Date and time functions-last_day

Insert picture description here

3.7 Date and time functions-round

Insert picture description here

3.8 Date and time functions-trunc

[External link image transfer failed. The source site may have an anti-hotlinking mechanism. It is recommended to save the image and upload it directly (img-GQXA0f7y-1622621438284)(C:\Users\user\AppData\Roaming\Typora\typora-user-images\ 1581300506089.png)]

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.1 Automatically converted data types

Insert picture description here

4.2 Data Conversion Diagram

Insert picture description here

4.3 Date to character --to_char

4.3 Digit to character --to_char

4.4 Turn date --to_date

Insert picture description here

4.5 Turn data --to_number

Insert picture description here

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.1 Commonly used general functions

[External link image transfer failed. The source site may have an anti-hotlinking mechanism. It is recommended to save the image and upload it directly (img-k0YCQRjS-1622621438290)(C:\Users\user\AppData\Roaming\Typora\typora-user-images\ 1581419980489.png)]

5.2 Handling null

5.2 nvl

5.3 nvl2

Insert picture description here

5.4 nullif

Insert picture description here

5.5 coalesce

Insert picture description here

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.1 Commonly used conditional expressions

Insert picture description here

6.2 Expression --case

Insert picture description here

6.3 Expression --decode

Insert picture description here

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;

7. Nested functions

7.1 Definition of nested functions

Insert picture description here

7.2 Demand One

[External link image transfer failed. The source site may have an anti-hotlinking mechanism. It is recommended to save the image and upload it directly (img-WgnN1opL-1622621438297)(C:\Users\user\AppData\Roaming\Typora\typora-user-images\ 1581645144805.png)]

7.3 Demand Two

Insert picture description here

7.4 Examples

------------------------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;