Common Oracle Functions

Common Oracle Functions

Select * from scott. dept;
Select * from scott. emp;

-- Dual is a virtual table provided by Oracle.
Select length ('hello') from dual;

-- Common functions
-- Lower converts uppercase to lowercase upper converts lowercase to uppercase
Select * from scott. emp where ename = 'Smith ';
-- Search results after letters are converted to lowercase letters
Select * from scott. emp where lower (ename) = 'Smith ';

Select upper ('helloworld') from dual;
Select lower ('helloworld') from dual;

-- INITCAP: converts the first letter of all words in the string to uppercase.
Select initcap ('SQL course') from dual;

-- CONCAT connects two strings
Select concat ('hello', 'World') from dual;

-- Substr: substring, starting from start, count
Select substr ('helloworld', 1, 5) from dual;

-- Substr: Obtain the substring from 4 to the end.
Select substr ('helloworld',-4) from dual;

-- LENGTH: returns the LENGTH of a string.
Select length ('helloworld') from dual;

-- INSTR (string, char) searches for a specified character in a string and returns the location where the specified character is found, starting from 1.
Select instr ('helloworld', 'O') from dual;

-- The RPAD is pasted on the right of the column, and the LPAD is pasted on the left of the column.
Select rpad (sal, 8, '*') from scott. emp;

Select lpad (sal, 8, '*') from scott. emp;

-- TRIM deletes the empty string at the beginning and end
Select trim ('helloworld') from dual;
Select length ('helloworld') from dual;
Select length (trim ('helloworld') from dual;

-- TRIM deletes the beginning and end of H (case sensitive)
Select trim ('H' from 'helloworld') from dual;

-- TRIM deletes the first H
Select trim (leading 'H' from 'helloworld') from dual;

-- TRIM deletes the end of H
Select trim (trailing 'H' from 'helloworld') from dual;

-- TRIM deletes the beginning and end of H
Select trim (both 'H' from 'helloworld') from dual;

-- REPLACE ('string', 's1', 's2 ')
-- String the character or variable to be replaced
-- S1: string to be replaced by string s2
Select replace ('helloworld','ll ', 'ff') from dual;

-- Value Function Round rounded
Select Round (3.14159, 3) from dual;

-- TRUNC Truncation
Select TRUNC (3.14159, 3) from dual;

-- Modulo
Select MOD (1600,300) from dual;

-- Date function

Create table tb_test (
Currdate date
);

Select * from tb_test;

-- Query System Time
Select sysdate from dual;

-- Insert sysdate
Insert into tb_test (currdate) values (sysdate );
-- To_date: inserts a string of the custom date format into data of the date type.
Insert into tb_test (currdate) values (to_date ('2017/14 02:03:42 ', 'yyyy/mm/dd hh: mi: ss '));
Insert into tb_test (currdate) values (to_date ('2014/1/14', 'yyyy/mm/dd '));
-- Date function
-- When the oracle date type is set, the default date is yyyy/mm/dd hh: mi: ss.
Select sysdate from dual;

-- Add or subtract a number on the date and the result is still the date.
Select sysdate + 84 from dual;

-- Three conversion functions: to_date to_char to_number
-- To_date (date string, Format)
Select to_date ('2014/1/14 02:03:42 ', 'yyyy/mm/dd hh: mi: ss') from dual;
Select to_date ('2014/1/14', 'yyyy/mm/dd') from dual;

-- To_char (date, format) converts a date to a character
Select to_char (sysdate, 'yyyy/mm/dd') from dual;
Select to_char (sysdate, 'yyyy/mm/dd hh: mi: ss') from dual;
Select to_char (sysdate, 'mm') from dual;
-- Convert digits into characters
Select to_char (3.14159) from dual;

-- To_number (string) converts the number of a string to the number type.
Select to_number ('192. 123 ') from dual;
-- You cannot convert a non-numeric string to the number type.
Select tb_number ('abc') from dual;

-- Returns the number of days of difference between two dates.
Select to_date ('2014/1/08', 'yyyy/mm/dd')-sysdate from dual;
Select TRUNC (to_date ('2014/1/08', 'yyyy/mm/dd')-sysdate, 0) from dual;
Select sysdate-to_date ('1970/08', 'yyyy/mm/dd') from dual;

-- The number can be divided by 24 to add or minus the hour in the day period.
Select sysdate + 2/24 from dual;
-- You can divide the number by 24 and then 60 to the date plus minutes.
Select sysdate + 2/24/60 from dual;

-- Returns the number of months between d1 and d2. d1 and d2 can be strings.
Select months_between (
To_date ('2017-04-08 ', 'yyyy-mm-dd '),
To_date ('2017-11-20 ', 'yyyy-mm-dd ')
) As months from dual;

-- Returns the date d plus the date after 3 months. 3 is an integer.
Select add_months (sysdate, 3) from dual;

-- Returns the date of the first day of the week after date d.
Select next_day (sysdate, 'tues') from dual;
-- Foreign Sunday is 1, SO 3 refers to Tuesday
Select next_day (sysdate, 3) from dual;

-- Returns the date of the last day of the month where d is located.
Select last_day (sysdate) from dual;

-- Get 0 minutes 0 seconds next hour
Select trunc (sysdate + 1/24, 'hh') from dual;

-- Get 0 minutes 0 seconds next day
Select trunc (sysdate + 1, 'dd') from dual;

-- Get the following message: 0 minutes 0 seconds, January 1, January 1.
Select last_day (sysdate) from dual;
Select trunc (last_day (sysdate) + 1) from dual;

-- Get 0 minutes 0 seconds, January 1, January 1 next year.
Select trunc (sysdate, 'yyyy') from dual;
Select add_months (trunc (sysdate, 'yyyy'), 12) from dual;

-- The Round rounding function performs 4 homes by dd by default. 5 homes enter the next day after noon.
Select Round (sysdate) from dual;

-- Returns the next month after the 15th day
Select Round (sysdate, 'mm') from dual;
-- If it is more than June, return to the next year's January 1
Select Round (sysdate, 'yyyy') from dual;

-- Truncation date
Select trunc (sysdate, 'mm') from dual;
Select trunc (sysdate, 'dd') from dual;
Select trunc (sysdate, 'hh') from dual;

-- Timer
-- Execute every minute
Select trunc (sysdate, 'mi') + 1/24/60 from dual;

-- Scheduled daily execution of 3
-- Example: Execute at every day
Select trunc (sysdate) + 1 + 2/24 from dual;

-- Weekly scheduled execution
-- Example: Execute at every Monday
Select trunc (next_day (sysdate, 2) + 2/24 from dual;

-- Scheduled monthly execution
-- Example: Execute at on the first day of every month
Select trunc (last_day (sysdate) + 1 + 2/24 from dual;

-- Periodical execution on a quarterly basis
-- For example, the statement is executed at on the first day of each quarter.
Select trunc (add_months (sysdate, 3), 'q') + 2/24 from dual;

-- Scheduled execution every six months
-- For example, at a.m. on July 15, July 1 and July 15, January 1
Select add_months (trunc (sysdate, 'yyyy'), 6) + 2/24 from dual;

-- Periodical execution every year
-- For example, execute the command at a.m. on January 1, January 1 every year.
Select add_months (trunc (sysdate, 'yyyy'), 12) + 2/24 from dual;

-- General functions complete some functions
-- Nvl function if the first parameter is null, the second parameter is used.
Select comm from scott. emp;
Select nvl (comm, 0) from scott. emp;

-- Replace null comm with 0 to calculate the annual income.
Select ename, sal, comm, (sal + comm) * 12 annual income from scott. emp;
Select ename, sal, nvl (comm, 0), (sal + nvl (comm, 0) * 12 from scott. emp;

-- Nvl2 function if the first parameter is null, the third parameter is used. Otherwise, the second parameter is used.
Select nvl2 (comm, comm, 0) from scott. emp;

-- Nullif equal return null, not equal return expr1
Select nullif (1, 3) from dual;

-- Multiline Functions
-- Case
Select * from scott. emp;

Select ename, job, sal basic salary,
Case job
When 'salesman' THEN sal + sal * 0.9
When 'manager' THEN sal + sal * 0.85
When 'analyst' THEN sal + 100
When 'cler' THEN sal + 200
Else sal
End
As pay-as-you-go-as an alias
From scott. emp;

-- Decode Function
Select ename, job, sal basic salary,
Decode (job,
'Salesman', sal + sal * 0.39,
'Manager', sal + sal * 0.85,
'Analyst', sal + 100,
'Cler', sal + 200,
'President ', sal
) As pay-as-you-go-alias
From scott. emp;

-- Multi-table join
Create table tb_course (
Name varchar2 (18 ),
Course varchar2 (18 ),
Grade number
);

Insert into tb_course (NAME, course, grade) VALUES ('Tom ', 'jdbc', 20 );
Insert into tb_course (NAME, course, grade) VALUES ('Tom ', 'hibernate', 50 );
Insert into tb_course (NAME, course, grade) VALUES ('Tom ', 'spring', 80 );
Insert into tb_course (NAME, course, grade) VALUES ('Mary ', 'jdbc', 30 );
Insert into tb_course (NAME, course, grade) VALUES ('Mary ', 'hibernate', 60 );
Insert into tb_course (NAME, course, grade) VALUES ('Mary ', 'spring', 70 );

-- Multi-table join 1.
Select * from tb_course;
Select name, grade as JDBC from tb_course where course = 'jdbc ';
Select name, grade as Hibernate from tb_course where course = 'hibernate ';
Select name, grade as Spring from tb_course where course = 'spring ';

Select T4.name, T1.JDBC, T2.Hibernate, T3.Spring
From
(Select name, grade as JDBC from tb_course where course = 'jdbc ') T1,
(Select name, grade as Hibernate from tb_course where course = 'hibernate ') T2,
(Select name, grade as Spring from tb_course where course = 'spring') T3,
(Select distinct name from tb_course) T4
Where T4.name = T1.name and T4.name = T2.name and T4.name = T3.name;

-- Multi-table join 2.
Select
Name,
Decode (course, 'jdbc ', grade, 0) as jdbc. -- if the value of the 'Course' column is 'jdbc', the value of 'grad' is displayed. Otherwise, 0 is displayed.
Decode (course, 'hibernate ', grade, 0) AS Hibernate,
Decode (course, 'spring', grade, 0) AS Spring
From
Tb_course;

-- Decode (column, value, cloumnvalue, default)
-- Group, group function, decode
Select
Name,
Sum (decode (course, 'jdbc ', grade, 0) JDBC,
Sum (decode (course, 'hibernate ', grade, 0) Hibernate,
Sum (decode (course, 'spring', grade, 0) Spring
From
Tb_course t
Group by t. name;