Step-by-Step Oracle functions (Focus)

Single-line functions-character Functions

Although each database supports SQL statements, each database also has operation functions supported by each database. These are single-row functions. If you want to develop a database, in addition to SQL, you need to learn more functions.

Single-row functions are divided into the following five types: character functions, numeric functions, date functions,Conversion functions and general functions;

1-Character functions:

Character functions are mainly used to operate string data. Below are several character functions:

* UPPER (string | column): converts the input string to uppercase and returns the result;

* LOWER (string | column): converts the input string to lowercase and returns the result;

* INITCAP (string | column): starts with an uppercase letter;

* LENGTH (string | column): returns the LENGTH of the string;

* REPLACE (string | column): REPLACE;

* SUBSTR (string | column, start point [end point]): String truncation:

It is a little troublesome in Oracle. Even if you want to verify the string, you must write a complete SQL statement. Therefore, for the convenience of user queries in Oracle Database, a specialDual virtual table.

Example 1:Observe the function to convert to uppercase

SELECT UPPER('hello') FROM dual ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image002 "border =" 0 "alt =" clip_image002 "src =" http://www.bkjia.com/uploads/allimg/131229/125604G30-0.jpg "height =" 137 "/>

Usage of capital conversion: in general use, when users input data, do they care whether the data is stored in upper or lower case?

SELECT * FROM emp WHRER ename='&str';

At this time, if the input is in lower case, the data cannot be queried. Therefore, the user is not required at this time. Therefore, the program can only adapt to this problem and add a function:

SELECT * FROM emp WHERE ename=UPPER('&str');

Of course, the above "&" and) operations are the content of the substitution variable, this part of content is not focused.

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image004 "border =" 0 "alt =" clip_image004 "src =" http://www.bkjia.com/uploads/allimg/131229/1256042937-1.jpg "height =" 256 "/>

Example 2:Observe the lower-case conversion operation and return the names of all employees to the lower-case letters;

SELECT LOWER('ename') FROM emp ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image006 "border =" 0 "alt =" clip_image006 "src =" http://www.bkjia.com/uploads/allimg/131229/1256042Y4-2.jpg "height =" 362 "/>

Example 3:Capital the first letter of each employee's name

SELECT INITCAP(ename) FROM emp ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image008 "border =" 0 "alt =" clip_image008 "src =" http://www.bkjia.com/uploads/allimg/131229/1256046248-3.jpg "height =" 355 "/>

Example 4:Query the length of each employee's name

SELECT ename,LENGTH(ename) FROM emp ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image010 "border =" 0 "alt =" clip_image010 "src =" http://www.bkjia.com/uploads/allimg/131229/125604D55-4.jpg "height =" 357 "/>

Example 5:The employee name must be exactly 5 characters long;

SELECT ename,LENGTH(ename) FROM emp WHERE LENGTH(ename)=5 ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image012 "border =" 0 "alt =" clip_image012 "src =" http://www.bkjia.com/uploads/allimg/131229/1256046131-5.jpg "height =" 264 "/>

Example 6:Replace "A" in the name with the letter;

col REPLACE(ename,'A','_') FORMAT A50 ;SELECT REPLACE(ename,'A','_') FROM emp ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image014 "border =" 0 "alt =" clip_image014 "src =" http://www.bkjia.com/uploads/allimg/131229/1256042608-6.jpg "height =" 374 "/>

String truncation has two syntaxes:

|-Syntax 1: SUBSTPR string | column,Start Point), IndicatingStart PointAlways truncate to the end;

|-Syntax 2: SUBSTPR string | column, start point, end point), which indicates that part of the content is intercepted from the start point to the end point;

Example 7:Syntax 1: SUBSTPR string | column, start point), which indicates a forward slash from the start point to the end;

SELECT ename, SUBSTR (ename, 3) FROM emp; // starts FROM 3rd characters to the end!

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image016 "border =" 0 "alt =" clip_image016 "src =" http://www.bkjia.com/uploads/allimg/131229/1256043261-7.jpg "height =" 358 "/>

Example 8:Syntax 2: SUBSTPR string | column, start point, end point), which indicates intercepting part of the content from the start point to the end point;

SELECT ename, SUBSTR (ename,) FROM emp; // intercept the first three characters! SELECT ename, SUBSTR (ename, 1, 3) FROM emp; // intercept the first three characters!

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image018 "border =" 0 "alt =" clip_image018 "src =" http://www.bkjia.com/uploads/allimg/131229/125604C12-8.jpg "height =" 355 "/>

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image020 "border =" 0 "alt =" clip_image020 "src =" http://www.bkjia.com/uploads/allimg/131229/125604MC-9.jpg "height =" 357 "/>

Example 9:The last three letters of each employee's name must be intercepted;

* Normal idea: Determine the start point through length-2

SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp ;

* New Idea: set a negative number to specify the truncation position from the back.

SELECT ename,SUBSTR(ename,-3) FROM emp ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image022 "border =" 0 "alt =" clip_image022 "src =" http://www.bkjia.com/uploads/allimg/131229/12560440U-10.jpg "height =" 353 "/>

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image024 "border =" 0 "alt =" clip_image024 "src =" http://www.bkjia.com/uploads/allimg/131229/1256041561-11.jpg "height =" 359 "/>

Interview Questions:

1. Could you tell the SUBSTR) when the function intercepts the subscript from 0 or 1?

* In the Oracle database, the SUBSTR function is the same from 0 or 1. SeeExample 8)

* SUBSTR) the function can also be set to a negative number, indicating that the start point of the truncation is specified later. SeeExample 9)

2-Numeric Functions
There are three numeric functions:

* ROUND (number | column [, number of digits to retain decimal places]): rounding operation;

* TRUNC (number | column [, number of digits retained]): discard the content at the specified position;

* MOD (number 1, number 2): modulo and remainder;

Example 10:Verify the ROUND () function

SELECT ROUND(903.5) FROM dual ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image026 "border =" 0 "alt =" clip_image026 "src =" http://www.bkjia.com/uploads/allimg/131229/1256043536-12.jpg "height =" 119 "/>

Select round (903.5), ROUND (-903.53567) FROM dual; select round (903.5), ROUND (-903.53567), ROUND (903.53567,-1) FROM dual; select round (903.5), ROUND (-903.53567), ROUND (903.53567,-1), ROUND (903.53567, 2) FROM dual; // retain 2 digits

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image028 "border =" 0 "alt =" clip_image028 "src =" http://www.bkjia.com/uploads/allimg/131229/1256041919-13.jpg "height =" 316 "/>

Example 11:Verify the TRUNC () function

SELECT TRUNC(903.5),TRUNC(-903.53567),TRUNC(903.53567,-1),TRUNC(903.53567,2) FROM dual ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image030 "border =" 0 "alt =" clip_image030 "src =" http://www.bkjia.com/uploads/allimg/131229/1256042R3-14.jpg "height =" 110 "/>

Example 12:Verify the MOD () function

SELECT MOD(10,3) FROM dual ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image032 "border =" 0 "alt =" clip_image032 "src =" http://www.bkjia.com/uploads/allimg/131229/1256042357-15.jpg "height =" 119 "/>

The above three major mathematical functions will also be matched in learning Java!

2-Date Functions

If you want to perform a date operation, you must first solve the problem of how to obtain the current date, which can be obtained using "SYSDATE". The Code is as follows:

SELECT SYSDATE FROM dual ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image034 "border =" 0 "alt =" clip_image034 "src =" http://www.bkjia.com/uploads/allimg/131229/1256044359-16.jpg "height =" 118 "/>

Example 13:In addition to the preceding current date, you can also perform some calculations on the date:

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

SELECT SYSDATE+3,SYSDATE+300 FROM dual ;

* Date-number = date, indicating several daysBefore;

SELECT SYSDATE-3,SYSDATE-300 FROM dual ;

* Date-date = number indicates the number of days of two dates, but it must be a big date-a small date;

Example 14:Find the number of employment days for each employee until today;

SELECT ename,hiredate,SYSDATE-hiredate FROM emp ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image036 "border =" 0 "alt =" clip_image036 "src =" http://www.bkjia.com/uploads/allimg/131229/1256042J0-17.jpg "height =" 211 "/>

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image038 "border =" 0 "alt =" clip_image038 "src =" http://www.bkjia.com/uploads/allimg/131229/1256044132-18.jpg "height =" 354 "/>

Note: In many programming languages, we also propose a concept that dates can be expressed by numbers!

In addition to the above three formulas, four operation functions are also provided:

* LAST_DAY: returns the last day of the specified date;

Example 15:Returns the date of the last day of the month.

SELECT LAST_DAY(SYSDATE) FROM dual ;

* NEXT_DAY (date, number of weeks): calculates the date of X in the next specified Week;

Example 16:Find the next Monday

SELECT NEXT_DAY (SYSDATE, 'monday') FROM dual;

* ADD_MONTHS (date, number): returns the date after several months;

Example 17:Determine when it will take four months.

SELECT ADD_MONTHS(SYSDATE,4) FROM dual ;

* MONTHS_BETWEEN (date 1, date 2): returns the month between two dates;

Example 18:Find the month of employment for each employee until today;

SELECT ename,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp ;SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image040 "border =" 0 "alt =" clip_image040 "src =" http://www.bkjia.com/uploads/allimg/131229/1256041B0-19.jpg "height =" 313 "/>

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image042 "border =" 0 "alt =" clip_image042 "src =" http://www.bkjia.com/uploads/allimg/131229/12560430S-20.jpg "height =" 693 "/>

We recommend that you use the above functions for date operations during all development, because these functions can avoid the leap year problem.

4-Conversion Function (CORE)

Currently, three types of data in the Oracle database are available: NUMBER, VACHAR2, and DATE ), the main function of the conversion function is to complete the conversion operations between these types of data. There are three types of conversion functions:

* TO_CHAR (string | column, Format String): converts a date or number to a string for display;

* TO_DATE (string, Format String): converts the string to DATE for data display;

* TO_NUMBER (string): converts a string to a number for display;

1, TO_CHAR () function

Query the current system date and time before:

SELECT SYSDATE FROM dual ;

The format is displayed in the format of "day-month-year". Obviously, this format does not conform to the normal idea. Normally, the format is "year-month-day ",

Example 19:TO_CHAR () function, but to use this function, some format strings are required: Year (yyyy), month (mm), Day (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 ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image044 "border =" 0 "alt =" clip_image044 "src =" http://www.bkjia.com/uploads/allimg/131229/1256044425-21.jpg "height =" 103 "/>

Example 20:However, in the displayed data at this time, we can find that there will be a leading 0. If you want to eliminate this 0, you can add a "fm ".

SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd') FROM dual ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image046 "border =" 0 "alt =" clip_image046 "src =" http://www.bkjia.com/uploads/allimg/131229/1256044560-22.jpg "height =" 117 "/>

Example 21:Normal people add 0, so this mark is OK, but in Oracle, DATE contains the time, but the previous Code does not show the time, to display the time, you need to add a flag:

SELECT TO_CHAR (SYSDATE, 'fmyyyy-mm-dd hh: mi: ss') day FROM dual; // SELECT TO_CHAR (SYSDATE, 'fmyyyy-mm-dd hh24: mi: ss') day FROM dual;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image048 "border =" 0 "alt =" clip_image048 "src =" http://www.bkjia.com/uploads/allimg/131229/1256043215-23.jpg "height =" 118 "/>

Example 22:Note: After the TO_CHAR () function is used, all content is a string and no longer data of the previous DATE type. The TO_CHAR () function can also be formatted with numbers, at this time, each "9" represents the concept of a digit, rather than the concept of a number 9.

SELECT TO_CHAR(11157191115719,'999,999,999,999,999') FROM dual ;SELECT TO_CHAR(11157191115719,'L999,999,999,999,999') FROM dual ;

The letter "L" indicates the meaning of "Locale", that is, the currency symbol in the current language environment.

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image050 "border =" 0 "alt =" clip_image050 "src =" http://www.bkjia.com/uploads/allimg/131229/1256041613-24.jpg "height =" 217 "/>

2, TO_DATE () function

Example 23:The main function of this function is to convert a string into DATE data.

col TO_DATE('1988-8-8','yyyy-mm-dd') format a50 ;SELECT TO_DATE('1988-8-8','yyyy-mm-dd') FROM dual ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image052 "border =" 0 "alt =" clip_image052 "src =" http://www.bkjia.com/uploads/allimg/131229/125604AQ-25.jpg "height =" 131 "/>

This function is usually used to update data!

3, TO_NUMBER () function: basically no!

Example 24:The TO_NUMBER () function changes the string to a number at first glance:

SELECT TO_NUMBER('1') + TO_NUMBER('2') + TO_NUMBER('3') FROM dual ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image054 "border =" 0 "alt =" clip_image054 "src =" http://www.bkjia.com/uploads/allimg/131229/12560464b-26.jpg "height =" 114 "/>

Example 25:However, it is intelligent in Oracle, so the above functions can be completed without using the TO_NUMBER () function.

SELECT '1' + '2' + '3' FROM dual ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image056 "border =" 0 "alt =" clip_image056 "src =" http://www.bkjia.com/uploads/allimg/131229/1256045113-27.jpg "height =" 115 "/>

Important:

* The TO_NUMBER () function is not considered;

* The TO_CHAR () function is the focus;

* The TO_DATE () function is the secondary focus.

5-General functions (CORE)

There are two common functions: NVL) and DECODE (). These two functions are Oracle's own special functions;

1. NVL () function, processing null

Example 26:: Query the total annual salary of each employee.

SQL> SELECT ename, sal, comm, (sal + comm) * 12 annual salary FROM emp;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image058 "border =" 0 "alt =" clip_image058 "src =" http://www.bkjia.com/uploads/allimg/131229/125604A07-28.jpg "height =" 360 "/>

Example 27:From the above we can see that the annual salary of an employee has changed to null, and the key to this problem is that the comm field is null. To solve this problem, we must do one thing: convert null to 0, which is the function of NVL.

SELECT ename,sal,comm,(sal+comm)*12,NVL(comm,0) FROM emp ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image060 "border =" 0 "alt =" clip_image060 "src =" http://www.bkjia.com/uploads/allimg/131229/1256045025-29.jpg "height =" 378 "/>

Example 28:It seems that the annual salary is still 0. Next let's take a look!

SQL> SELECT ename,sal,comm,(sal+NVL(comm,0))*12 ?êD? FROM emp ;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image062 "border =" 0 "alt =" clip_image062 "src =" http://www.bkjia.com/uploads/allimg/131229/1256045963-30.jpg "height =" 357 "/>

2. DECODE () function: multi-value judgment

The DECODE () function is very similar to the if... The only difference between the else statements is that the DECODE () function determines the value rather than the logical condition.

For example, you must display the positions of all employees, but these positions must be displayed in Chinese:

* CLERK: CLERK;

* SALESMAN: sales;

* MANAGER: MANAGER;

* ANALYST: ANALYST;

* PRESIDENT: PRESIDENT;

This kind of judgment must be Row-by-row, so the DECODE () function must be used at this time, and the syntax of this function is as follows:

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

Example 29:Display operation

SELECT empno, ename, job, DECODE (job, 'cler', 'clerks ', 'salesman', 'salesman', 'manager', 'manager', 'analyst ', 'Analyst ', 'President', 'President') FROM emp;

650) this. width = 650; "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "title =" clip_image064 "border =" 0 "alt =" clip_image064 "src =" http://www.bkjia.com/uploads/allimg/131229/1256045244-31.jpg "height =" 344 "/>

DECODE ()Functions are the most distinctive functions in Oracle !!!

Summary:

1. the basic format of the SQL statement. Here, the most basic syntax is given: the relationship between SELECT, FROM, WHERE, and ORDER;

2. Write down information about all data tables in the SCOTT user, including the column name, role, and type;

3. Several single-row functions in Oracle must be noted down. All functions must be used.

This article is from the "beyondhedefang" blog, please be sure to keep this source http://beyondhdf.blog.51cto.com/229452/1284698