X. Common functions of Oracle

One, character functions
The character function is the most commonly used function in Oracle, so let's see what the character functions are:
Lower (char): Converts a string to a lowercase format.
Upper (char): Converts a string to an uppercase format.
Length (char): Returns the lengths of the strings.
substr (char, M, N): intercepts a substring of a string, n means n characters, not a delegate to Nth
Replace (CHAR1, search_string, replace_string)
InStr (C1,C2,I,J)--Determines whether a character or string exists, returns the index of where it appears, otherwise returns less than 1, searches for the specified character in a string, and returns the location of the specified character;
C1 string to be searched
C2 the string you want to search
I Search start position, default is 1
Where J appears, default is 1

Problem: Display all employees ' names in lowercase
Sql> Select Lower (ename) from EMP;
Issue: Display the names of all employees in uppercase.
Sql> Select Upper (ename) from EMP;
Issue: Displays the name of an employee who is exactly 5 characters.
Sql> SELECT * from emp where length (ename) = 5;
Issue: Displays the first three characters of all employee names.
Sql> Select substr (ename, 1, 3) from EMP;
Issue: The names of all employees are displayed in uppercase letters and lowercase in the following way.
Sql> Select Upper (substr (ename,1,1)) | | Lower (substr (Ename,2,length (ename)-1)) from EMP;
Issue: Displays the names of all employees in lowercase with the first letter, followed by uppercase.
Sql> Select Lower (substr (ename,1,1)) | | Upper (Substr (Ename,2,length (ename)-1)) from EMP;
Problem: Show all employees ' names, replace All "a" with "I am Tiger"
Sql> Select Replace (ename, ' a ', ' I am a tiger ') from EMP;
Question: InStr (Char1,char2,[,n[,m]) usage
Sql> Select InStr (' Azhangsanbcd ', ' Zhangsan ') from dual; --Return 2
Sql> Select InStr (' Oracle traning ', ' Ra ', 1, 1) instring from dual; --Return 2
Sql> Select InStr (' Oracle traning ', ' Ra ', 1, 2) instring from dual; --Return 9
Sql> Select InStr (' Oracle traning ', ' Ra ', 1, 3) instring from dual; --return 0, according to the conditions, due to RA only two times, the fourth parameter 3, that is, the 3rd occurrence of the position of RA, it is clear that the 3rd time is no longer appear, so the result returned 0. Note that a space is also counted as a character
Sql> Select InStr (' abc ', ' d ') from dual; --Return 0

second, mathematical functions
The input parameters and the data type of the return value of the mathematical function are numeric types. Mathematical functions include COS,COSH,EXP,LN, log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round, etc.
We talk about the most commonly used:
Round (n,[m]) This function is used to perform rounding,
If M is omitted, it is rounded to an integer.
If M is a positive number, it is rounded to the M-bit of the decimal point.
If M is a negative number, it is rounded to the M-bit of the decimal point.
eg, SELECT round (23.75123) from dual; --Return 24
SELECT round (23.75123,-1) from dual; --Return 20
SELECT round (27.75123,-1) from dual; --Return 30
SELECT round (23.75123,-3) from dual; --Return 0
SELECT round (23.75123, 1) from dual; --Return 23.8
SELECT round (23.75123, 2) from dual; --Return 23.75
SELECT round (23.75123, 3) from dual; --Return 23.751
trunc (n,[m]) This function is used to intercept numbers.
If M is omitted, the fractional part is truncated,
if M is a positive number and the M-bit of the decimal point is truncated,
if M is a negative number, the first m bit of the decimal point is truncated.
Eg, SELECT trunc (23.75123) from dual;--return to
SELECT trunc (23.75123,-1) from dual;--return to
SELECT trunc (27.75123 ,-1) from dual; --Return to
Select Trunc (23.75123,-3) from dual;--return 0
SELECT trunc (23.75123, 1) from dual;--return to 23.7
SELECT Trunc (23 .75123, 2) from dual; --Return 23.75
SELECT trunc (23.75123, 3) from dual;--return 23.751
mod (M,n) Take the remainder function
eg, Select mod (10,2) from dual; --Returns 0
SELECT MOD (10,3) from dual;--returns 1
floor (n) returns the largest integer less than or equal to n
ceil (n) returns the smallest integer greater than or equal to n
eg, SELECT ceil (24.56) from dual;--return to
SELECT floor (24.56) from dual;--Return to
abs (n) returns the absolute value of the number n
the processing of numbers, the most used in the financial system or the banking system, different processing methods, and different results for the financial statements

third, date function
Date functions are used to process data of the date type. By default, the date format is Dd-mon-yy, which is "December-July-12"
(1)Sysdate Return to System time
eg, sql> select sysdate from dual;
(2) Oracleadd_months function
The Oracle Add_months (time,months) function can get n months before or after a certain time
eg, select Add_months (sysdate,-6) from dual; --the result of the query is the time before the current time of half a year
Select Add_months (sysdate,6) from dual; --The result of this query is the time after half a year of the current time
(3)last_day (d): Returns the last day of the month in which the specified date
problem: Find employees who have been in the workforce for more than 8 months
sql> select * from EMP where Sysdate>=add_months (hiredate,8);
Problem: Displays the name and date of employment of the employee who has been in service for 10 years.
Sql> Select ename, hiredate from EMP where sysdate>=add_months (HIREDATE,12*10);
Issue: For each employee, show the number of days they joined the company.
Sql> Select Floor (sysdate-hiredate) "Number of days in the job", ename from EMP;
or
sql> select Trunc (sysdate-hiredate) "Number of days in the job", ename from EMP;
Question: Find out all employees employed on the 3rd day of the month.
Sql> Select Hiredate,ename from emp where Last_day (hiredate) -2=hiredate;
          
Four, conversion functions
Conversion functions are used to convert data types from one type to another. In some cases, the data type of the Oracle server allow value is not the same as the actual one, when Oracle server implicitly converts the conversion data type
such as:
CREATE TABLE t1 (id int);
INSERT into T1 values (' 10 ');--so that Oracle automatically-->10
CREATE TABLE t2 (ID varchar2);
INSERT into T2 values (1); -So Oracle will automatically 1--' 1 ';
What we're saying is that while Oracle can do the implicit conversion of the data type, it doesn't fit all, and in order to improve the reliability of the program, we should use the conversion function.

To_char () function
You can use select Ename, HireDate, Sal from emp where deptno = 10; Display information, however, in some cases, this does not meet your needs.
Question: Whether the date can be displayed in/minutes/sec
Sql> Select Ename, To_char (hiredate, ' Yyyy-mm-dd hh24:mi:ss ') from EMP;
Question: Whether the salary can display the specified currency symbol
Sql>
YY: two-digit year 2004-->04
YYYY: four-digit year 2004
Span style= "color: #ff0000;" >MM: Two-digit month August-->08
DD: two-digit day 30th-->30
mi, ss--> Display minutes \ Seconds
0: Displays the number, if the number of digits is not sufficient, use 0 to complement the
Span style= "color: #ff0000;" >.: Displays the decimal point
$: Add USD before the number
l: Precede the number with the local currency symbol
c: Precede the number with the international currency symbol
g: Display the group separator at the specified location ,
d: Displays the decimal symbol (.) in the specified position

Question: When the salary is displayed, add the local currency unit to the front
Sql> Select Ename, To_char (hiredate, ' yyyy-mm-dd hh24:mi:ss '), To_char (Sal, ' L99999.99 ') from EMP;
Issue: Show all employees who have been in employment in 1980
Sql> SELECT * from emp where TO_CHAR (hiredate, ' yyyy ') = 1980;
Issue: Show all employees who have been in employment in December
Sql> SELECT * from emp where TO_CHAR (hiredate, ' mm ') = 12;

To_date () function
The function to_date is used to convert a string into a date type of data.
Question: Can I add a date in the way the Chinese are accustomed to the year-month-day.
eg, SELECT to_date (' 2012-02-18 09:25:30 ', ' yyyy-mm-dd hh24:mi:ss ') from dual;

Five, Sys_context () system functions
1) Terminal: The identifier of the terminal for the current session client, such as computer name
2) Language: Language
3) db_name: Current database name
4) Nls_date_format: the date format for the current session customer
5) Session_user: The database user name for the current session client
6) Current_schema: Default scheme name for the current session client
7) Host: Returns the name of the host where the database resides
through this function, you can query some important information, such as which database you are using?
Select Sys_context (' USERENV ', ' db_name ') from dual;
Note: USERENV is fixed, cannot be changed, db_name can be replaced by other,
eg, select Sys_context (' USERENV ', ' Language ') from dual;
Select Sys_context (' USERENV ', ' Current_schema ') from dual;