Oracle Single-line function

Syntax for single-line functions

Function_name (Column|expression, [Arg1, Arg2, ...])

Parameter description:

Function_name Function Name

Column Name

Expression Expressions

Arg1,arg2,... Parameters

Single-line function classification:

• Character functions: Receive character input and return characters or values

• Numeric function: Accepts data entry and returns a value

• Date functions: Manipulating date-based data

• Conversion functions: Converting from one data type to another data type

• General function: NVL function DECODE function

character Functions

--Lower () converts the string to lowercase select ename from emp where lower (ename) like '%a% '; select ename from emp where ename like '%a% ' or ENA Me like '%a% '; --Upper () converts the string to uppercase select Ename from EMP where Upper (ename) like '%a% ';--initcap () capitalizes the first letter of the word, and the remaining letters to lowercase select initcap (' H Ello World ") from Dual;select Initcap (ename) from emp;--concat () Connect strings to select concat (' Hello ', ' world ') from Dual;select ' Hello ' | | ' World ' from dual;--substr () string intercept select Ename, substr (ename, 1, 2), substr (ename, -3, 3) from emp;--length () calculated lengths Select Length (' abcdef '), Length (' 12345678 ') from the dual;--replace () string replaces the Select substr (' Hello ', 3, 2) with substring lengths (' Hello '), rep Lace (' Hello ', ' l ', ' X ') from dual;--Chr () converts the number to its corresponding ASCII character, select Chr, Chr ($) from dual;--ASCII () converts the corresponding character to its ASCII code counterpart The number select ASCII (' A '), ASCII (' B ') from dual;

Numeric Functions

--round () rounded, the default precision to the digit, specify the precision to the decimal point after several select round (23.652) from dual; Select Round (23.652, 1) from Dual;select round (23.652,-1) from dual;--trunc () truncates decimal places, truncates decimals or integers by specified precision (without rounding) select Tru NC (25.46, 1) from Dual;select round (25.46, 1) from Dual;select trunc (25.46,-1) from Dual;select round (25.46,-1) from Dua l;--mod () to a number of remainder select mod (3) from dual;

Date Function

Oracle provides a number of functions related to date operations, mainly including add and subtract.

There are some rules to follow when adding and subtraction dates.

Date-Number = Date

Date + Number = Date

Date-date = number indicates the number of days separated by two dates

--Shows the number of weeks that the 10-door employee entered the company select Empno, ename, Deptno, round ((sysdate-hiredate)/7) from emp where deptno = 10;--Months_between  () returns the number of months between two given dates--the number of months that the employee working on the 10 department is queried select Deptno, empno, ename, Months_between (Sysdate, HireDate) from emp where deptno = 10;select Deptno, Empno, ename, round (Months_between (Sysdate, HireDate)) from emp where deptno = 10;--add_months () returns the given Date plus specified number of months after select Empno, ename, HireDate, Add_months (HireDate, 5), Add_months (HireDate,-1) from emp;--next_day () Specify date The next specified day of the week. Select Sysdate, Next_day (sysdate, ' Monday ') from dual;--Last_day () find the last date in the month of the given date select Last_day (sysdate) From Dual;select last_day (to_date (' 2015-02-13 ', ' Yyyy-mm-dd ')) from dual;

Conversion Functions

To_char () Use the format control character, the format control character is not case-sensitive letter

Year: Y, the year is four digits, it should be written as: yyyy or YYYY

Month: M, the month is two digits, it should be written as: mm or mm

Day: D, the day is two digits, it should be written as: DD or DD

---To_char () converts a number or date into a string--transforms the system's date format display so that it is displayed according to Chinese date habits, i.e. "yyyy-mm-dd" select Empno, ename, To_char (HireDate, ' Yyyy-mm-dd ') from emp;--FM minus single digit day, month front 0select empno, ename, To_char (hiredate, ' Fmyyyy-mm-dd ') from emp;--on entry date by year, month, day into Row Split Select Empno,ename,to_char (hiredate, ' yyyy ') year, To_char (HireDate, ' mm ') month, To_char (hiredate, ' DD ') DAYF Rom Empselect empno,ename, to_char (hiredate, ' yyyy ') | | ' Year ' | | To_char (hiredate, ' mm ') | | ' Month ' | | To_char (hiredate, ' dd ') | | ' Day ' from emp--to_char () Formats the number select Empno,ename,to_char (Sal, ' 999,999,999 ') from Emp;select Empno,ename,to_char (Sal, ' 000,000,000 ') from the Emp;select Empno,ename,to_char (Sal, ' $99,999 ') from emp;--to_number () converts the current fixed-format string to a digital select Sal From EMP where Sal>to_number (' $1,250.00 ', ' $9,999.99 '), select To_number (' + ') + to_number (' n ') from dual;--to_ Date () Converts a string of the current fixed format to a date-queries the employee who entered the job after 1981-1-1, select Ename, hiredate from Empwhere hiredate >= to_date (' 1981-1-1 ', ' yyyy -mm-dd '); select Ename, HireDate from Empwhere hiredate ≫= date ' 1981-1-1 '; 

General Functions

Decode (), this function has a statement similar to If...elseif...else, used to judge multiple branches

Syntax: decode (COL/EXPRESSION,SEARCH1,RESULT1[,SEARCH2,RESULT2,......] [, default])

Description

Col/expression: for a column name or an expression

Search1,search2......searchi: for a variety of possible conditions

Result1,result2......resulti: The return value when the corresponding Shearch is satisfied

--NVL () processing of fields with null values--for each employee's annual salary (including bonuses) select Empno, ename, (Sal+comm) *12 from Emp;select EMPNO,ENAME,SAL,NVL (comm,0) new_ Comm, (SAL+NVL (comm,0)) *12 incom from emp;--docode () Branch Judging Select decode (2,1, ' content 1 ', 2, ' Content 2 ', 3, ' Content 3 ') from Dual;select  empno Employee number, ENAME employee name,   decode (Job, ' clerk ', ' salesman ', ' salesman ', ' Sales person ',              ' manager ', ' manager ', ' analyst ', ' analyst ', ' President ', ' President ') works from EMP

Links: Oracle Simple query, limited query and sorting

Oracle Single-line function