Oracle Single-line function

Although each database is supported by SQL statements, each database has its own operation function supported by each database, which is a single-line function, and if you want to do database development, except to use SQL, is to learn more functions.

Single-line functions are divided into the following five categories: Character functions, numeric functions, date functions, conversion functions, general functions;

One, character functions

The function of the character function is mainly the operation of string data, the following is a few character functions:

• UPPER (String | column): Returns the input string into uppercase;
• LOWER (String | column): Turns the input string back to lowercase;
• Initcap (String | column): Start with the first letter capitalized;
• Length (String | column): Find out how long the string is;
• Replace (String | column): replace;
• SUBSTR (String | column, start point [, End Point]): string intercept;

Oracle is a bit more troublesome, even if you want to validate the string, you must write a complete SQL statement, so in the Oracle database for user query convenience, so dedicated to provide a "dual" virtual table.

Example: observing a function that turns uppercase

SELECT UPPER (' Hello ') from dual;

sql> SELECT UPPER (' Hello ') from dual; UPPER-----HELLO

The use of uppercase conversions: In general usage, when the user input data to care about the data itself is stored in uppercase and lowercase?

SELECT * from emp WHERE ename= ' &str ';

Sql> SELECT * from emp where ename= ' &str '; input str value:  Smith original Value    1:select * from emp where ename= ' &str ' new value C3/>1:select * from EMP where ename= ' Smith ' unselected line sql> select * from emp where ename= ' &str '; input str value:  Smith original Value 
   1:select * from emp where ename= ' &str ' new value    1:select * from emp where ename= ' SMITH '     EMPNO ename      JOB              M GR hiredate              SAL       COMM     DEPTNO--------------------------------------------------------------------- --------------      7369 SMITH      clerk           7902 1 July-December -80                    20

At this point if the input is lowercase, you certainly cannot query out the data, so this time can not require so many users, so this time can only be adapted by the program itself, add a function:

SELECT * from emp WHERE ename=upper (' &str ');

Sql> SELECT * from EMP where ename=upper (' &str '); input str value:  Smith original Value    1:select * from EMP where Ename=upper ( ' &str ') new value    1:select * from emp WHERE ename=upper (' Smith ')     EMPNO ename      JOB              MGR hiredate              SAL       COMM     DEPTNO-----------------------------------------------------------------------------------      7369 SMITH      Clerk           7902 1 July-December -80                    20

Of course, the above "&" operation belongs to the content of alternative variables, this part of the content does not focus.

Example: observing a lowercase operation, returning all employee names in lowercase letters

SELECT LOWER (ename) from EMP;

Example: capitalize the first letter of each employee's name

SELECT Initcap (ename) from EMP;

Example: querying the length of each employee's name

SELECT Ename,length (ename) from EMP;

Example: asking for employee information with a name length of exactly 5

SELECT Ename,length (ename) from Empwhere LENGTH (ename) = 5;

Example: using the letter "_" to replace all the letters "A" in the name

SELECT REPLACE (ename, ' A ', ' _ ') from EMP;

There are two types of syntax for a string intercept operation:

Syntax One: SUBSTR (String | column, start point), which indicates that it has been intercepted from the start point to the end;

SELECT Ename,substr (ename,3) from EMP;

Syntax Two: SUBSTR (String | column, start point, end point), which means intercept from the start point to the end point and intercept some content;

SELECT Ename,substr (ename,0,3) from EMP; SELECT Ename,substr (ename,1,3) from EMP;

Example: required to intercept the last three letters of each employee's name

• Normal thinking: Determine the starting point by length-2

SELECT ename,substr (Ename,length (ename)-2) from EMP;

• New Idea: Set a negative number, indicating the location from the post-specified intercept;

SELECT Ename,substr (ename,-3) from EMP;

Interview Question: does the substr () function intercept when the subscript starts from 0 or 1?

• In the Oracle database, the SUBSTR () function is the same starting from 0 or 1;
• SUBSTR () can also be set to a negative number, indicating the start point of the Intercept after the specified;

Second, the number function

There are three number functions:

• ROUND (number | column [, Number of decimal places reserved]): rounding operation;
• TRUNC (number | column [, Number of decimal places reserved]): Discards the contents of the specified position;
• MOD (number 1, number 2): modulo, take the remainder;

Example: validating the Round () function

SELECT ROUND (903.53567), ROUND ( -903.53567), ROUND (903.53567,2), ROUND ( -90353567,-1) from dual;

ROUND (903.53567) ROUND ( -903.53567) ROUND (903.53567,2) ROUND ( -90353567,-1)---------------------------------------- ------------------------------             904              -904             903.54           -90353570

Example: validating the trunc () function

SELECT TRUNC (903.53567), TRUNC ( -903.53567), TRUNC (903.53567,2), TRUNC ( -90353567,-1) from dual;

TRUNC (903.53567) TRUNC ( -903.53567) TRUNC (903.53567,2) TRUNC ( -90353567,-1)---------------------------------------- ------------------------------             903              -903             903.53           -90353560

Example: modulo operation

SELECT MOD (10,3) from dual;

MOD (10,3)----------         1

The three main mathematical functions above will also have a matching content in learning java.

Third, date function

If you want to do a date now, then there is a problem that must be solved first, that is, how to get the current date, the current date can be obtained using "Sysdate", the code is as follows:

SELECT sysdate from dual;

In addition to the current date above, several calculations can be made in the date:

• Date + number = date, indicating the date after several days;

SELECT Sysdate + 3,sysdate + from dual;

• Date – Number = Date, which represents a date several days ago;

SELECT sysdate-3,sysdate-300 from dual;

• Date – date = number, indicating the number of days between two days, but certainly the big date – the small date;

Example: find out how many days each employee has been employed so far today

SELECT ename,hiredate,sysdate-hiredate from EMP;

And in many programming languages, a concept is presented, and dates can be represented by numbers.

In addition to the above three formulas, the following four operation functions are available:

• Last_day (date): Find the last day of the specified date;

Example: Finding the date of the last day of the month

SELECT Last_day (sysdate) from dual;

• Next_day (date, day of the week): Find the date of the next specified week x;

Example: finding the next Monday

SELECT next_day (sysdate, ' Monday ') from dual;

• Add_months (date, number): Find a date after a number of months;

Example: find out the date after four months

SELECT add_months (sysdate,4) from dual;

• Months_between (date 1, date 2): Find the month between two dates;

Example: find the month of employment for each employee until today

SELECT Ename,hiredate,trunc (Months_between (sysdate,hiredate)) from EMP;

In all of the development, if it is a date operation, it is recommended to use the above functions, because these functions can avoid the problem of leap years.

Iv. Conversion Functions

Now we have access to three of the data in the Oracle database: number, string (VARCHAR2), date, and the main function of the conversion function is to accomplish the conversion between these data, there are three kinds of conversion functions:

• To_char (String | column, format string): Converts a date or a number into a string display;
• To_date (string, format string): Turns the string into a date data display;
• To_number (String): Turns the string into a digital display;

A, To_char () function

Before querying the current system date time:

SELECT sysdate from dual;

This time is displayed according to the "Day-month-year" format, it is obvious that this display format does not conform to normal thinking, normal is "year-month-day", so this case can use the To_char () function, but the use of this function requires some format string: year (yyyy), month (mm), DD.

SELECT to_char (sysdate, ' yyyy-mm-dd '), To_char (sysdate, ' yyyy ') year, To_char (sysdate, ' mm ') month, To_char (sysdate, ' DD ') Day from dual;

To_char (SY year MO DA------------------2012-08-12 2012 08 12

However, this time the display data can be found to have a leading 0, if you want to eliminate the 0, you can add an "FM."

SELECT to_char (sysdate, ' fmyyyy-mm-dd ') day from dual;

Day----------2012-8-12

Normal people add 0, so this tag knows, but in Oracle, date contains time, but the previous code does not show the time, to show the time you need to increase the tag:

SELECT to_char (sysdate, ' fmyyyy-mm-dd hh24:mi:ss ') day from dual;

Day-------------------2012-8-12 16:13:38

It is important to note that after using the To_char () function, all the content is a string, no longer the date data, the To_char () function can also be used for the formatting of numbers, when each "9" represents the concept of a digit, not the concept of the number 9.

SELECT to_char (89078907890, ' l999,999,999,999,999 ') from dual;

To_char (89078907890, ' l999,999,------------------------------              ￥89,078,907,890

The letter "L", which denotes the meaning of "Local", is the current currency symbol in the locale.

B, to_date () function

The main function of this function is to change a string into date data.

SELECT to_date (' 1989-09-12 ', ' YYYY-MM-DD ') from dual;

To_date (' 1989---------------December-September-89

In general, this function is used more when updating the database;

C, To_number () function: Basic No

The To_number () function knows that a string is changed to a number at a glance:

SELECT to_number (' 1 ') + to_number (' 2 ') from dual;

But it's really smart in Oracle, so the above features don't use To_number () to do it:

SELECT ' 1 ' + ' 2 ' from dual;

sql> SELECT to_number (' 1 ') + to_number (' 2 ') from dual; To_number (' 1 ') +to_number (' 2 ')-----------------------------                            3sql> SELECT ' 1 ' + ' 2 ' from dual;   ' 1 ' + ' 2 '----------         3

So now the To_number () function is basically not considered, the focus of the function on To_char (), followed by the To_date () function.

V. General functions

There are two general functions: NVL (), DECODE (), which are the characteristic functions of Oracle's own function;

A, NVL () function, handling null

Example: asking for a full annual salary for each employee

SELECT Ename,sal,comm, (sal+comm) *12 from EMP;

Sql> SELECT Ename,sal,comm, (sal+comm) *12 from EMP; ename             SAL       COMM (sal+comm) *12-------------------------------------------SMITH             800ALLEN         22800WARD             1250         21000JONES            2975MARTIN           1250       1400         31800BLAKE            2850CLARK            2450SCOTT             800KING             5000TURNER          0         18000ADAMS            1100JAMES             950FORD             3000MILLER           1300 14 rows have been selected.

At this point, the annual salary of some employees becomes null, and the key to this problem is that the Comm field is NULL, so if you want to solve this problem, you have to do something: change null to 0, and this is what the NVL () function does.

SELECT Ename,sal,comm, (SAL+NVL (comm,0)) *12,NVL (comm,0) from EMP;

Sql> SELECT Ename,sal,comm, (SAL+NVL (comm,0)) *12,NVL (comm,0) from EMP; ename SAL COMM (SAL+NVL (comm,0)) *12 NVL (comm,0)----------------------------------------------------- --------SMITH 9600 0ALLEN 1600 300 2                           2800 300WARD 1250 21000 500JONES 2975                           35700 0MARTIN 1250 1400 31800 1400BLAKE 2850                            34200 0CLARK 2450 29400 0SCOTT 800           9600 0KING 60000 0TURNER             0 18000 0ADAMS 1100 13200 0JAMES 950 11400 0FORD 336000 0MILLER 1300 15600 0 14 rows have been selected. 

B, DECODE () function: Majority value judgment

The DECODE () function is very similar to the If...else in the program ... Statement, the only difference is that the decode () function determines the value, not the logical condition.

For example, it is now required to display positions for all employees, but these positions require a replacement for the Chinese display:

• Clerk: Clerk;
• Salesman: sales;
• Manager: Managers;
• Analyst: analyst;
• President: President;

This judgment must be judged on a row-by-line basis, so this is the time to use Decode (), and the syntax for this function is as follows:

DECODE (Value | column, Judgment value 1, display value 1, Judgment value 2, display value 2, Judgment value 3, display value 3, ...)

Example: implementing the displayed operation function

SELECT empno,ename,job,decode (Job, ' clerk ', ' clerk ', ' salesman ', ' Sales person ', ' manager ', ' manager ', ' analyst ', ' analyst ', ' president ', ' President ') from EMP;

Sql> SELECT empno,ename,job,decode (Job, ' clerk ', ' clerk ', ' salesman ', ' Sales person ', ' manager ', ' manager ', ' analyst ', ' analyst ', ' President ', ' President ') from     EMP;     EMPNO ename      JOB       DECODE (J-------------------------------------      7369 SMITH      Clerk     Clerk      7499 ALLEN      salesman  sales staff      7521 WARD       salesman  sales      7566 JONES manager   Manager      7654 MARTIN     salesman  sales staff      7698 BLAKE      manager   managers      7782 CLARK      Manager   Manager      7788 SCOTT      Clerk     Clerk      7839 KING       President President      7844 TURNER     salesman  sales staff      7876 ADAMS      Clerk     Clerk      7900 JAMES      Clerk     Clerk      7902 FORD       analyst analyst      7934 MILLER     The clerk     Clerk has selected 14 rows.

The DECODE () function is the most characteristic function in the entire Oracle and must be mastered.

Exercise Guide

1. Select all employees in the Department 30.

SELECT * from EMP WHERE deptno=30;

2. List the name, number and department number of all clerks (clerk).

SELECT empno, ename, deptno from emp WHERE job= ' clerk ';

3. Find out the employee with higher commission than salary.

SELECT * from EMP WHERE comm>sal;

4. Find out 60% of employees who have a higher commission than salary.

SELECT * from EMP WHERE comm>sal*0.6;

5. Find out the details of all the clerks (clerk) in all manager and department 20 in department 10.

SELECT * from Empwhere (job= ' MANAGER ' and deptno=10) or (job= ' clerk ' and deptno=20);

6. Find out all the managers in department 10 and all the clerks in department 20 (clerk), who are neither managers nor clerks but whose salary is greater than or equal to 2000 of all employees ' detailed information.

SELECT * from Empwhere (job= ' manager ' and deptno=10) or (job= ' clerk ' and deptno=20) or (Job not in (' manager ', ' clerk ') and sal>=2000);

7. Find out the different work of the employees who receive the Commission.

SELECT DISTINCT job from EMP WHERE comm are not NULL;

8. Find employees who do not charge a commission or receive a commission less than 100.

SELECT * from EMP WHERE comm is NULL OR comm<100;

9. Find all employees employed on the 3rd day of the month.

Each employee's employment date must be different, so it is now necessary to find the last day of each employee's month of employment, and then follow the "date-number" method for the first three days of the date, which must match the date of employment to meet the conditions.

SELECT * from emp WHERE last_day (hiredate) -2=hiredate;

10. Identify employees who were employed before 12.

If the year is required, the most accurate practice is to use the total number of months/12;

SELECT * from emp WHERE months_between (sysdate,hiredate)/12>12;

11. Display the names of all employees in uppercase letters.

SELECT Initcap (ename) from EMP;

12. Display the name of the employee who is exactly 5 characters.

SELECT ename from emp WHERE LENGTH (ename) = 5;

13. Display the names of employees without "R".

SELECT ename from emp WHERE ename does like '%r% ';

14. Displays the first three characters of all employee names.

SELECT SUBSTR (ename,0,3) from EMP;

15. Display the names of all employees and replace all "a" with "a".

SELECT REPLACE (ename, ' a ', ' a ') from EMP;

16. Display the name and date of employment of the employee who has been in service for 10 years.

SELECT ename, HireDate from Empwhere months_between (sysdate,hiredate)/12>10;

17, display the details of the staff, sorted by name.

SELECT * from emp ORDER by ename;

18. Display the employee's name and the date of employment, according to their service life, the oldest employees in the front.

SELECT ename, HireDate from emp ORDER by HireDate;

19. Display the name, work and salary of all employees, sorted in descending order of work, and sort by salary if work is the same.

SELECT ename, Job, Sal from the EMP ORDER by Job desc,sal;

20. Show all employee names, year and month of affiliation, sort all months by hire date, and if the month is the same, the employee of the earliest year is ranked first.

The program needs to take the year and month from the date and complete it with the To_char () function.

SELECT Ename,to_char (hiredate, ' yyyy ') Year,to_char (hiredate, ' mm ') Monthsfrom Emporder by Months,year;

21. Show the daily salary of all employees in one months for 30 days, ignoring the remainder.

SELECT Ename,sal,trunc (SAL/30) from EMP;

22. Identify all employees employed in February (in any year).

SELECT * from emp WHERE to_char (hiredate, ' mm ') = 2;

23. For each employee, show the number of days they joined the company.

SELECT ename,sysdate-hiredate from EMP;

24. Displays the names of all employees who have "A" anywhere in the Name field.

SELECT ename from emp WHERE ename like '%a% ';

25. Display the service life of all employees in the way of month and day.

First step: find out the number of years of employment per employee: Total number of months employed/12 = number of years;

SELECT ename, Hiredate,trunc (Months_between (sysdate,hiredate)/12) yearfrom emp;

The second step: seeking Moon number, the above calculation is ignored in the decimal point is actually the month, so directly take the surplus can be;

SELECT ename, Hiredate,trunc (Months_between (sysdate,hiredate)/12) Year,trunc (MOD (Months_between (SYSDATE,hiredate) , ()) Monthsfrom EMP;

The third step: to find out the number of days, the most accurate approach is not more than 30 days in the range of the request;

Now know the current time to use sysdate out, and the date of employment use hiredate out, but the gap between HireDate and Sysdate is too big, so there will certainly be errors, Then we must find a way to raise the HireDate date to the sysdate gap within the 30-day range.

You have learned two functions before:

• Months_between (): Find out the number of months of the two-day period, if it is: Months_between (sysdate,hiredate) to find the employment date to today's employment month;
• Add_months (): On a date added to the date after the specified month, if said HireDate + the number of months apart from today = a new date, and this new date must not be more than 30 days apart from Sysdate.

SELECT ename, Hiredate,trunc (Months_between (sysdate,hiredate)/12) Year,trunc (MOD (Months_between (SYSDATE,hiredate) , ()) Months,trunc (Sysdate-add_months (Hiredate,months_between (sysdate,hiredate))) Dayfrom EMP;

The above procedure belongs to the comprehensive application of the DATE function.

Oracle Single-line function