Oracle Study Notes 4

Functions can be used to complete a series of operations. The difference between databases is that the support for functions is different.

The learning function of this note. Functions are divided into single-row functions and multi-row functions. Here we focus on single-row functions.

Single Row function category:

Character Functions

Numeric Functions

Date Functions

Conversion functions

Common functions

Character Functions: It can also be divided into case-sensitive and character-controlled functions.

1) case-insensitive functions:

LOWER ()

UPPER ()

INITCAP ()

SQL> select lower('SQL COURSE') , upper('sql course') , initcap('SQL course') from dual ; LOWER('SQLCOURSE') UPPER('SQLCOURSE') INITCAP('SQLCOURSE')------------------ ------------------ --------------------sql course         SQL COURSE         Sql Course

 

The preceding query results show that:

The Lower () function converts characters to lowercase letters.

The Upper () function converts characters to uppercase.

The Initcap () function converts the initial character to uppercase.

For example, to query the employee information whose name is king, the lower function and upper () function are introduced to ensure the validity of the query because we are not sure about the case format of the database name.

SQL> select * from emp  where lower(ename) = 'king' ; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7839 KING       PRESIDENT       1981/11/17    5000.00               10SQL> select * from emp  where ename = upper('king' ); EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7839 KING       PRESIDENT       1981/11/17    5000.00               10

For example, convert the first letter of the query result into an uppercase letter using the initcap function.

SQL> select initcap(ename) from emp where ename = 'KING';

 INITCAP(ENAME)--------------King

Character control functions:

Concat ()

Substr ()

Length ()

Replace ()

For example, you can use the concat () function to connect strings in addition to '|.

SQL> select concat('Hello ' , 'world'  ) from  dual ; CONCAT('HELLO','WORLD')-----------------------Hello worldSQL> select substr('hello' , 1 , 2) ,length('world') , replace('sql' , 'q' , 'X') from dual ; SUBSTR('HELLO',1,2) LENGTH('WORLD') REPLACE('SQL','Q','X')------------------- --------------- ----------------------he                   5 sXl

In the above example, we can see that the function of substr () is to intercept a string, followed by two parameters. The first parameter represents

Start position. The second parameter indicates the number of interceptions. The length () function returns the length of the string. Replace () string followed by two

Parameters. The first parameter indicates the letter to be replaced, and the second parameter indicates replacing the first letter with the second letter.

 

In addition, Oracle supports reverse string truncation:

SQL> select ename ,substr(ename ,-3 ) from emp  where deptno = 10 ; ENAME      SUBSTR(ENAME,-3)---------- ----------------CLARK      ARKKING       INGMILLER     LER

Numeric functions:

Round (): Rounding Function

Trunc (): truncates decimal places.

Mod (): returns the remainder function.

Example:

SQL> select round(234.12 , 2) , round(234.324) , round(234.25 , -2) from dual; ROUND(234.12,2) ROUND(234.324) ROUND(234.25,-2)--------------- -------------- ----------------    234.12            234              200

The following parameter 2 indicates that the last two digits of the decimal point are retained. If this parameter is not specified, the default value is 0.-2 indicates that the number before the decimal point is rounded to ROUND (234.25,-2) and rounded to 4, 3. Get 200.

Example:

SQL> select trunc(234.12 , 2) , trunc(234.324) , trunc(248.25 , -2) from dual; TRUNC(234.12,2) TRUNC(234.324) TRUNC(248.25,-2)--------------- -------------- ----------------         234.12            234              200

Trunc (234.12, 2) specifies the number of digits to retain decimal places. trunc (234.324) directly removes decimal places, trunc (248.25,-2) removes the first two decimal places.

Conclusion: The difference between the trunc () and round () functions is that trunc () performs corresponding operations directly without rounding.

Example:

SQL> select mod(10 , 3) from dual ;  MOD(10,3)----------         1

Use the mod () function to perform the remainder operation.

Date functions:

Some date operations:

Date-number = Date

Date + number = Date

Date-date = days

Addition operations cannot be performed between dates.

For example, estimate the number of months for an employee. sysdate indicates the current time.

SQL> select empno , ename , round((sysdate - hiredate)/30) days from emp ; EMPNO ENAME            DAYS----- ---------- ---------- 7369 SMITH             406 7499 ALLEN             403 7521 WARD              403 7566 JONES             402 7654 MARTIN            396 7698 BLAKE             401 7782 CLARK             400 7788 SCOTT             328 7839 KING              394 7844 TURNER            397 7876 ADAMS             327 7900 JAMES             394 7902 FORD              394 7934 MILLER            392 14 rows selected

Other date functions:

MONTHS_BETWEEN (): calculates the number of months in the specified date range.

ADD_MONTHS (): add the specified number of months to the specified date to obtain the subsequent date.

NEXT_DAY (): the next day is the date.

LAST_DAY (): returns the date of the last day of the given date.

For example, query the number of months with different dates. sysdate indicates the current time.

SQL> select empno , ename , months_between(sysdate , hiredate) days from emp ; EMPNO ENAME            DAYS----- ---------- ---------- 7369 SMITH      399.772141 7499 ALLEN      397.675367 7521 WARD       397.610850 7566 JONES      396.256012 7654 MARTIN     390.417302 7698 BLAKE      395.288270 7782 CLARK             394 7788 SCOTT      323.707625 7839 KING       388.772141 7844 TURNER     391.062463 7876 ADAMS      322.578592 7900 JAMES      388.223754 7902 FORD       388.223754 7934 MILLER     386.578592 14 rows selected

Example: The current date plus four months later. SQL> select add_months (sysdate, 4) from dual; ADD_MONTHS (SYSDATE, 4) --------------------- 22:29:57

Example: The date of the next Wednesday. SQL> select next_day (sysdate, 'wedday') from dual; NEXT_DAY (SYSDATE, 'wedday') ---------------------- 22:31:23 example: The date of the last day of the month. SQL> select last_day (sysdate) from dual; LAST_DAY (SYSDATE) ---------------

Conversion functions:

To_char (): Convert to string

To_number (): convert to a number

To_date (): Convert to date

The relationships between the three elements are as follows:

<-- To_date --> to_number

Date =

--> To_char <-- to_char

SQL> select empno , ename ,to_char( hiredate , 'yyyy-mm-dd') from emp ; EMPNO ENAME      TO_CHAR(HIREDATE,'YYYY-MM-DD')----- ---------- ------------------------------ 7369 SMITH      1980-12-17 7499 ALLEN      1981-02-20 7521 WARD       1981-02-22 7566 JONES      1981-04-02 7654 MARTIN     1981-09-28 7698 BLAKE      1981-05-01 7782 CLARK      1981-06-09 7788 SCOTT      1987-04-19 7839 KING       1981-11-17 7844 TURNER     1981-09-08 7876 ADAMS      1987-05-23 7900 JAMES      1981-12-03 7902 FORD       1981-12-03 7934 MILLER     1982-01-23 14 rows selected

Format of the conversion date. Year: y. year indicates yyyy, month: m indicates mm, and day: d Indicates dd.

 

Converts a number to a character. Use Symbols to separate numbers to improve readability. You can use '$' and 'l' to represent the region. '$' Indicates the dollar, and L: local indicates the amount displayed in the local language.

SQL> select sal , to_char(sal , '$99,999') from emp ;       SAL TO_CHAR(SAL,'$99,999')--------- ----------------------   800.00     $800  1600.00   $1,600  1250.00   $1,250  2975.00   $2,975  1250.00   $1,250  2850.00   $2,850  2450.00   $2,450  3000.00   $3,000  5000.00   $5,000  1500.00   $1,500  1100.00   $1,100   950.00     $950  3000.00   $3,000  1300.00   $1,300 14 rows selected

 

For example, a function that converts a to_number () string to a number.

 

SQL> select to_number('34')+46 from dual; TO_NUMBER('34')+46------------------                80

Convert the string to a number, and then calculate the number.

For example, to_date () converts a string to the date type.

SQL> select to_date('2014-4-9' ,'yyyy/mm/dd') dates from dual ; DATES-----------2014/4/9

Common functions:

NVL (): changes a specified null value to the specified content. NULL values cannot be involved in the operation, so the null value is replaced with 0.

SQL> select empno ,(nvl(comm , 0 )+sal)*12 annual from emp ; EMPNO     ANNUAL----- ---------- 7369       9600 7499      22800 7521      21000 7566      35700 7654      31800 7698      34200 7782      29400 7788      36000 7839      60000 7844      18000 7876      13200 7900      11400 7902      36000 7934      15600 14 rows selected

The decode () function is similar to the if... Else if... Else

Example:

SQL> select decode (1, 1, 'value 1', 2, 'value 2', 3, 'value 3') from dual; DECODE (, 'value 1 ', 2, 'value 2', 3 ------------------------------ value 1 SQL> select decode (2, 1, 'value 1', 2, 'value 2', 3, 'value 3 ') from dual; DECODE (, 'value 1', 2, 'value 2', 3 -------------------------------- value 2 SQL> select decode (3, 1, 'value 1', 2, 'value 2', 3, 'value 3') from dual; DECODE (3,1, 'value 1', 2, 'value 2', 3 ---------------------------- value 3

Through the results of the preceding three queries, we can easily find out the functions and usage of the decode () function. The first parameter is the value to be matched, followed by a group of two parameters. The first character in a group is compared with the matching character. If the same, the second character is output.

The usage of case () is basically the same as that of decode:

Syntax format:

Selectename, caseename

When 'King' then 'hello'

When 'Ford 'then' word'

When 'miller' then 'helloword'

Elseename

End name

Fromemp

Wheredeptno = 10

The query result is:

ENAME NAME

--------------------

CLARK

KING Hello

MILLER HELLOWORD