Detailed explanation of single line function operation in Oracle

Single line function -- PL/SQL

(1) Character function

Function number

Say clearly

 CONCAT

Concatenate two strings, same as |

 INITCAP

Change the first letter of the string to uppercase

 INSTR

Find the position of a string

 LENGTH

Give the length of the string in characters

 REPLACE

Perform string search and replace

 SUBSTR

Take substring of string

 TRIM

Crop out the front or back strings

 LOWER

Convert string to lowercase

 UPPER

Change string to uppercase

 

 

practical:

--(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
(1)
select concat('abc','d') from dual;
(2)
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

function

explain

ABS(value)

absolute value

CEIL(value)

Minimum integer greater than or equal to value

FLOOR(value)

Maximum integer less than or equal to value

MOD(value,divisor)

Modulus

POWER(value,exponent)

exponent power of value

ROUND(value,precision)

Round to the nearest 5 with precision 4

SQRT(value)

Square root of value

TRUNC(value, press precision)

Intercept value according to precision

 

practical:

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

Description

ADD_MONTHS

Add count months to date

LAST_DAY( date )

Return the last day of the month of the date

MONTHS_BETWEEN(date2,date1)

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
Sat.(Saturday)
Sun.(Sunday) Sunday

ROUND(date,'format')

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

TRUNC(date,'format')

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

Description

TO_CHAR

Convert date format to string

TO_DATE

Converts a string to a date type in the specified format

TO_NUMBER

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 ow.name,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 ow.name,(case ow.ownertypeid 
when 1 then 'resident'
when 2 then 'Administrative institutions'
when 3 then 'business'
 else 'other'  
 end 
) 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;



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

select ad.name,decode(ad.name,'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