Summary of common functions for Oracle personal learning

This document is to record common functions

Oracle functions

sysdate date function

sysdate: used by Oracle to obtain the date and time

select sysdate from dual;
#Get current date

Specific common methods: -center/collection-list?type=1&spm=1001.2101.3001.4506

rowid and rownum

rowid: used to locate a piece of data in the data table. It is unique and will not change

Rownum: indicates the position of a query record in the whole result set. The corresponding rownum is different for the same record with different query conditions, but the rowid will not change

The rowid is unique and will not be changed. It is used to delete the same data. Specific sql:

delete from emp where rowid not in (select min(t.rowid)  from EMP t group by t.emp_no)
#Query all rowid s in groups, exclude the smallest ones, and delete the remaining ones.

rownum can fetch the number of data pieces, for example:

select * from (select rownum no,t.* from emp t order by name)   where no between 3 and 7 order by name desc
#First, query the number of all rownum entries by sorting, and then take the data of interval 3-7
select rownum,id,name from student where rownum <3;
#Find two pieces of data

substr string interception function

substr: function format

Format 1: substr (string, string, int a, int b);

  • String the string to be intercepted

  • Start position of interception, (0 and 1 are represented as the first element)

  • b the length of the string to be intercepted

Format 2: substr (string, int a);

  • String the string to be intercepted

  • A from the a-th element to the last element

select ename,
substr(ename,1),   #Intercept from the first to the last;
substr(ename,-3),  #Cut from the penultimate to the end;
substr(ename,3,3)  #Start from the third and intercept 3 bits;
from emp;

#The format of instr() function (commonly known as character lookup function) is often used in conjunction with instr
substr(information,instr(information,'|',1,3)+1,instr(information,'|',1,4)-instr(information,'|',1,3)-1) Age

instr() character lookup function


  1. instr ('source string ',' target string ',' start position ',' how many occurrences') --- the nth subscript, such as dividing the contents between |, which is used in conjunction with substr
  2. instr ('source string ',' destination string ')
select instr('helloworld','l',2,3) from dual;    #Return result: 9, that is, start at position 2(e) of "Hello world" and find the position of "l" for the third time
select instr('helloworld','lo') from dual; #Return result: 4, that is, the position where "l" begins to appear in "lo"

decode() conditional function


  1. Decode (condition, value 1, return value 1, value 2, return value 2,... Value n, return value n, default value)
IF condition=Value 1 THEN
    RETURN(Return value 1)
ELSIF condition=Value 2 THEN
    RETURN(Return value 2)
ELSIF condition=value n THEN
    RETURN(Return value n)
    RETURN(Default value)

# Specific sql
select id,name,
	decode(id,1,'first',2,'the second',3,'Third','No,') new_id 
from t_decode;
  1. Decode (field or field operation, value 1, value 2, value 3)

The result of this function is that when the value of the field or field operation is equal to the value 1, the function returns the value 2, otherwise it returns the value 3
Of course, values 1, 2 and 3 can also be expressions. This function makes some sql statements much simpler

Often used in conjunction with the sign function:

decode(sign(avg(t.salary + t.bonus)-3000),1,'3000 above',-1,'3000 following',0,'Flat') Average income level

sign() operation function

The sign() function returns 0, 1, - 1 respectively according to whether a value is 0, positive or negative

It is used for operation comparison and is often used in combination with decode.

SELECT sign(120 - 100) FROM DUAL;   --  1
SELECT sign(100 - 100) FROM DUAL;   --  0
SELECT sign(100 - 120) FROM DUAL;   -- -1

to_char() character conversion function

You can convert the date format or splice characters that cannot be added in sql, such as single quotation marks: to_char(38)

select,to_char(a.join,'yyyy-mm-dd') date,to_char(a.join,'yyyy"year"mm"month"dd"day"') Chinese display from emp1 a where name='Li Li'

The output style is: [the external chain picture transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-v1mzmqsu-163789341947) (C: \ users \ love \ appdata \ roaming \ typora \ typora user images \ image-20211126091342345. PNG)]

select  to_char(sysdate,'dl') from dual;   #Gets the current date and day of the week
select   to_char(sysdate,'yyyy')  from dual; #Acquisition year

#Character to date
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual  
#Date to character
select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual   

To_char (data, FM999990.09) formatting function

  • Where 9 means that if there is a number, the number will be displayed, and there is no space

  • Where 0 means that if there is a number, the number will be displayed, and if there is no number, 0 will be displayed, that is, a placeholder

  • Where FM means that if it is a space brought by 9, it will be deleted

to_date date conversion function

  1. When using Oracle's to_date function for date conversion, you may intuitively use the format of "yyyy MM DD HH: mm: SS" as the format for conversion, but an error will be caused in Oracle: "ORA 01810 format code appears twice". For example, select to_date('2005-01-01 13:14:20 ',' yyyy mm DD hh24: mm: Ss') From dual; because SQL is not case sensitive, mm and mm are considered to be the same format code, so mi is used in Oracle SQL instead of minutes. select to_date('2005-01-01 13:14:20 ',' yyyy MM DD hh24: mi: Ss') from dual;
  2. In addition, it shall be displayed in the form of 24 hours, and HH24 shall be used
#Specific conversion to time
to_date('2001/1/1 12:12:12','yyyy-mm-dd hh24:mi:ss')

#Character to date
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual  
#Date to character
select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual   

months_between() function

MONTHS_BETWEEN (date1, date2): used to calculate the number of months between date1 and date2.

  • If date1 is later than date2 in the calendar, MONTHS_BETWEEN() returns a positive number.
  • If date1 is earlier than date2 in the calendar, MONTHS_BETWEEN() returns a negative number.
  • If date1 and date2 are the same, MONTHS_BETWEEN() returns a 0.
#Calculate the difference between two months. Normally, the big month comes first and the small month comes later
select months_between(to_date('2014-3-21','yyyy-mm-dd'), to_date('2014-1-10','yyyy-mm-dd')) months from dual;
#Sometimes in the same month, 0.2 is displayed without 0 in front, so you need to convert the string
Select name full name,to_char(months_between(to_date(sysdate),to_date(to_char(join,'yyyy-mm-dd'),'yyyy-mm-dd') ),'fm9990.09')||'month' Number of months from emp1 where rownum <4;

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-A9NSCcIj-1637893421951)(C:\Users \ love \ appdata \ roaming \ typora \ typora user images \ image-20211126092245069. PNG)] output style

round() and trunc() functions

General summary:

  • For data retrieval, if the number of digits is enough, trunc takes decimals without rounding, and round should be rounded.
  • For time, round is rounded according to the value of the next digit. For example, take the month. When the subsequent days > 15 days, the number of months + 1. trunc directly takes the current month, year and day, and the subsequent values are the original values. For example, 2021 / 11 / 18, the year is 2021 / 1 / 1, and the month is 2021 / 11 / 1

Case realization:

#For the date value, to judge the most accurate gap, you should use round to get the next digit, which is more accurate, such as the month to the day
select round(to_date('2001/7/18 ','yyyy-mm-dd'),'mm') from dual   #2021/8/1
select trunc(to_date('2001/7/18 ','yyyy-mm-dd'),'mm') from dual   #2021/7/1

#Number value
select round(5555,-2) from dual   #5600
select trunc(5555,-2) from dual   #5500

select round(5555.555,2) from dual  #5555.56
select trunc(5555.555,2) from dual  #5555.55

Tags: Oracle plsql

Posted on Fri, 26 Nov 2021 07:27:52 -0500 by brianb