Detailed explanation of single line function operation in Oracle

Single line function -- PL/SQL

(1) Character function

Function number

Say clearly


Concatenate two strings, same as |


Change the first letter of the string to uppercase


Find the position of a string


Give the length of the string in characters


Perform string search and replace


Take substring of string


Crop out the front or back strings


Convert string to lowercase


Change string to uppercase




--(1) Character function
-- dual Virtual table,Because the data does not belong to any table,Using virtual table
--1.concat :String splicing splices two strings, and || identical
select concat('abc','d') from dual;
select 'abc' || '123' from dual;

--2.  initcap: Capital letter,Capitalize a word,Convert other letters to lowercase
select initcap('hello') from dual;

--3,  instr: Find the string position,Count from 1
select instr('hello','e') from dual;

--4. length: String length
select length('abcd') from dual;

--5.replace replace(character string,Replaced content,replace content)
select replace('hello','e','x') from dual;

--6. substr: Intercept string
--substr('character string',Starting position),from'Starting position'reach'last'
select substr('abcde',2) from dual;
--substr('character string',Starting position),'Starting position','length'
select substr('abcde',2,2) from dual;

--7. trim: Remove spaces at both ends
select length('abcde') from dual;
select length(trim('abcde') ) from dual;

--8.lower: Convert lowercase
select lower('hEELLo') from dual;

--9.upper: Convert to uppercase
select upper('hEEllO')from dual;



(2) Numerical function




absolute value


Minimum integer greater than or equal to value


Maximum integer less than or equal to value




exponent power of value


Round to the nearest 5 with precision 4


Square root of value

TRUNC(value, press precision)

Intercept value according to precision



--(2) Numerical function
--1. abs(value) absolute value
select abs(-90) from dual;  --90

--2.ceil(value) Round up
select ceil(12.1) from dual;  --13
select ceil(12.9) from dual;

--3.floor(value) Round down (Tail removal)
select floor(12.1) from dual;  --12
select floor(12.9) from dual;

--4.mod(value,divisor) Take mold(remainder) mysql: 10 % 3
select mod(10,3) from dual;  --1

--5.power(value,exponent) power 
select power(2,3) from dual;

--6.round(value,precision) rounding
--round(numerical value)Round values,No decimal is
--round(numerical value,accuracy)Keep decimals for rounding

select round(12.1) from dual;
select round(12.9) from dual;

--Precision can be involved
select round(12.2383,2) from dual;

--7.sqrt(value) Square root
select sqrt(16) from dual;

--8.trunc(value,Press precision)intercept
select trunc(12.32423) from dual;  --12
select trunc(12.3456,2) from dual;  --12.34



(3) Date function

Function number



Add count months to date

LAST_DAY( date )

Return the last day of the month of the date


Give the number of months (can be decimal) of Date2 - date1

NEXT_DAY( date,'day')

Give the date of the next day after the date, where the day is the week, such as:

Mon.(Monday) Monday
Tuesday Tuesday
Wed.(Wednesday) Wednesday
Thursday Thursday
Friday, Friday
Sun.(Sunday) Sunday


The interval of judging time by the ROUND function

YEAR/YYYY means to judge whether it is the first half or the second half of the year

MONTH/MM to determine whether it is the first half of the month


YEAR/YYYY means the first day of the year,

MONTH/MM gets the first day of the month,

DAY gets the first DAY of the week

Note: (mysql gets the current system time: now()

Use sysdate in oracle)

Practical examples:

--(3) Date function
--1.Current system time
select sysdate from dual;

--2.add_months add to count Months
select add_months(sysdate,2) from dual;
select add_months(sysdate,-2) from dual;

----2017 What is the date three months before January 20
select add_months(to_date('20170120','yyyymmdd'),-3) from dual;

--3.last_day Last day of the month
select last_day(sysdate) from dual;

--4. to_date Convert string to date
select to_date('20200202','yyyy-mm-dd') from dual;

--4 Last day of the month

select last_day(to_date('20200402','yyyy-mm-dd'))from dual;

--5.months_between Get the number of months between two dates
select months_between(to_date('2020-04-02 23:59:59','yyyy-mm-dd hh24:mi:ss'),to_date('20200222','yyyymmdd')) from dual;

--How old are you??
--1.Number of months from the date of birth
select months_between(sysdate,to_date('1999-12-25','yyyy-mm-dd')) from dual;

--2.Number of years from the date of birth(Decimal places included)
select months_between(sysdate,to_date('1999-12-25','yyyy-mm-dd'))/12  from dual;

--3.Value up 20.4 ->21
select ceil(months_between(sysdate,to_date('1999-12-25','yyyy-mm-dd'))/12 )from dual;

--4.Patchwork'year'character string
select ceil( months_between(sysdate,to_date('1999-12-25','yyyy-mm-dd hh24:mi:ss'))/12)|| 'year' from dual;

--Mode 2
select trunc(months/12) || 'year' || trunc(mod(months, 12)) || 'month' ||
       trunc(sysdate - add_months(birth, trunc(months))) || 'day' age
  from (select months_between(sysdate, birth) months, birth
          from (select date '1999-12-15' birth from dual));

--6.Next specified date next_day(date,week)
--Sunday is the first day of the week,Can be represented by 1
select next_day(sysdate,'Sunday') from dual;

select next_day(sysdate,1) from dual;

--7.Rounding date format
--yyyy A five person year, Return date,Valid data is year,Month and day do not need to be considered
select round(sysdate, 'yyyy') from dual;

--mm Round to the nearest month,Return date;Valid data is month,No need to consider days
select round(sysdate, 'mm') from dual;

--8. trunc: format(Date intercept)
select trunc(sysdate) from dual;
--YEAR/YYYY The first day of the year,
select trunc(sysdate, 'yyyy') from dual;

--MONTH/MM Get the first day of the month,
select trunc(sysdate, 'mm') from dual;

--DAY Get the first day of the week

select trunc(sysdate, 'dd') from dual;



(4) Conversion function

Function number



Convert date format to string


Converts a string to a date type in the specified format


Convert a numeric string to a number

For example:

--(4) Conversion function
--yyyy year
--mm month
--dd day
--hh Time(12 hour)
--hh24 Time(24 hour)
--mi branch
--ss  second

--1. to_char Convert date to string
select to_char(1024) from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

select to_char(to_date('2020-05-27 11:27:33','yyyy-mm-dd 24hh:mi:ss'),'yyyy-mm')
--2.to_date Convert string to date
select to_date('2027-01-01','yyyy-mm-dd') from dual;

--3.to_number Convert a number string to a number
select to_number('1024') from dual;

select to_number('1024.24') from dual;

select to_number('1024.2424') from dual;



(5) Other functions

--(5) Other functions
--Null processing function  nvl(field,If is null Display corresponding value)
select nvl(t.depname,0),t.depno from DEP t

select nvl(a.usenum,0),a.* from t_account a;

--Show owner type in price schedule ID Price record for 1, if the upper limit is NULL,Then 999999 will be displayed 
select p.price,p.minnum,nvl(maxnum,999999) 
from t_pricetable p where p.ownertypeid=1; 

--2.nvl2(field, Not for null display, by null display)
select nvl2(t.depno,9999,0),t.depno from DEP t

--Demand: Show owner type in price list ID Price record for 1, if the upper limit is NULL,Display "unlimited".
select p.price,p.minnum,nvl2(p.maxnum,to_char(p.maxnum),'unlimited') from t_pricetable p where p.ownertypeid=1;

--nul2(t.usenum,t.usenum,0) equivalent nul(t.usenum,0)
select nvl2(t.usenum,t.usenum,0),t.* from t_account t;

--3. decode (Condition value)amount to java switch 
select decode(id,1,'ha-ha',2,'hey',3,'Haha') from t_area ;

-- select decode(id,1,'male',0,'female') from t_area ;

--Requirement: display the following information (do not query the owner type table, directly judge the value of 1.23)
select,decode(ow.ownertypeid,1,'resident',2,'Administrative institutions',3,'business')
as type from t_owners ow;

--The second way of writing:case when then 
select,(case ow.ownertypeid 
when 1 then 'resident'
when 2 then 'Administrative institutions'
when 3 then 'business'
 else 'other'  
) from t_owners ow;             

--The third way of writing:
select name,(case  
              when ownertypeid= 1 then 'resident'
              when ownertypeid= 2 then 'Administration' 
              when ownertypeid= 3 then 'business'
            end )
            from T_OWNERS;

select,decode(,1,'Mingxing Garden',2,'Xinyuan autumn Villa') from t_address ad;

select,decode(,'Mingxing Garden',1,'Xinyuan autumn Villa',2) from t_address ad;




After watching, Congratulations, and know a little bit!!!

The more you know, the more you don't know

~Thank you for reading. Your support is the biggest driving force for my study! Come on, strangers work together and encourage together!!

Tags: Database MySQL SQL less Oracle

Posted on Sun, 31 May 2020 08:55:23 -0700 by rushenas