Oracle Functions-Single-line functions-conversion functions, conditional expressions

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8C/27/wKioL1hjtGywsFtaAABh8bZO-Vc257.png-wh_500x0-wm_3 -wmp_4-s_59910427.png "title=" 1.png "alt=" Wkiol1hjtgywsftaaabh8bzo-vc257.png-wh_50 "/>

Single-line function

============================================================

Characteristics:

Each row returns one result, and the input output has a one by one corresponding relationship

can be nested use, the output of one function can do the input of another function such as: SELECT Lowner (Upper (' AA ')) from dual;

The passed-in variable can be either a column value or an expression. such as select lower (ename) from EMP;

============================================================

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/8C/2B/wKiom1hjtJ3AfQO_AABZfRwro0A326.png-wh_500x0-wm_3 -wmp_4-s_1357296537.png "title=" 2.png "alt=" Wkiom1hjtj3afqo_aabzfrwro0a326.png-wh_50 "/>

Conversion functions:

To_number: Converting data of character types to numeric types

To_date: Convert data of character type to date type D

To_char: Converts data of number or date type to character type

============================================================

1.1. To_char: Convert data of time type to character type

Syntax: to_char (date, ' Format_model ')

Format_model: Date format type

yyyy digit year form four-digit number

The spelling form of year years

MM Digital Date Form

Full spell format for month months

Mon Month abbreviated three digits

DY abbreviated three digits of the week

Full writing of day week

DD numeric form date double digit

For example:

① Query the current time, the time format is displayed as 2011-01-02 12:10:13

Sql>select sysdate from dual; ----Show the default time format

Sql>select to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

② Query the current time, the time format is displayed as "Week full write four-year-long month full write"

Sql>select To_char (sysdate, "Day yyyy month") from dual;

Use of ③FM minus leading 0

Sql> Select To_char (sysdate-13, ' DD ') from dual;

To

--

07

Sql> Select To_char (sysdate-13, ' FMDD ') from dual;

To

--

7

④ the character in the date format, you need to enclose the character in double quotation marks, such as "Week full write Victor four-digit month full write"

Sql>select to_char (sysdate, ' Day ' Victor ' yyyy month ') from dual;

============================================================

1.2. To_char: Converts data of numeric type to character type

Syntax: to_char (number, ' Format_model ')

Format_model: Format type

9----How many numbers (that is, how many) are actually displayed, if the front is 0, followed by number, the previous 0 does not show

0----How many digits to show how many bits, insufficient to fill with 0

$----shown in USD

L----Use local currency symbol

.

,

① test the difference between 9 and 0: Want to show the salary as x,xxx.xx this format

Sql>select Ename,to_char (Sal, ' 9,999.99 ') from EMP;

Change into

Sql>select Ename,to_char (Sal, ' 9,009.09 ') from EMP;

Found no difference, because 9 and 0 are all representations of one digit

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

Change into

Sql>select Ename,to_char (Sal, ' 0,999.99 ') from EMP;

Now you need to show 4 digits, 800 to display as 0800, because the front is 0, that is not enough to fill with 0

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

Change into

Sql>select Ename,to_char (Sal, ' 09909090,999.99 ') from EMP;

Summary: Both 9 and 0 represent a number, 0 means that the insufficient bit of time with 0 to fill, generally only used in the leading bit.

============================================================

2. To_number: Used to convert character types to numeric types

Sql>select to_number ('234234.4350 ', ' 999999.0000 ') from dual;

To_number (' RMB234234.4350 ', ' L999999.0000 ')

——————————————

234234.435

① is used to convert 16 binary to 10 binary

Sql>select to_number (' fa ', ' xxxxx ') from dual;

============================================================

3. To_date: Used to convert data to date type

Syntax: to_date (' char ', ' Format_model ')

Format_model: format type

yyyy digit year form four-digit number

The spelling form of year years

MM Digital Date Form

Full spell format for month months

Mon Month abbreviated three digits

DY abbreviated three digits of the week

Full writing of day week

DD numeric form date double digit

①select * from EMP where HireDate < To_date (' 1986-07-25 ', ' yyyy-mm-dd ');

============================================================

4. Null value function

①NVL (EXPR1,EXPR2): Expr1 is judged, if Expr1 is empty, return expr2, if EXPR1 is not empty, return EXPR1 itself

Sql>select ENAME,COMM,NVL (comm,0) from EMP;

Note: Data types need to be matched, such as expr1 for number, then EXPR2 must be number, otherwise an error will be

②NVL2 (EXP1,EXPR2,EXPR3): Expr1 is judged, if the expr1 is empty, then return EXPR3, if EXPR1 is not empty, then return EXPR2;

Sql>select Ename,comm,nvl2 (comm,1,0) from EMP;

③nullif (EXPR1,EXPR2): Returns null if EXPR1 and EXPR2 are equal, otherwise returns EXPR1

Sql> Select Sal,nullif (sal,3000) from EMP;

============================================================

5. Conditional expressions

①case's Syntax:

Case expr when expr1 when RETURN_EXPR1

When EXPR2 when RETURN_EXPR2

When EXPR3 when RETURN_EXPR3

else else_expr

End

Eg: to judge the Deptno, if the department 10th returns one-fold wages (1*sal), if it is Department 20th, returns twice times the wage (2*sal), if it is 30, returns 3 times times the wage, if not, then returns half the wage

Select Ename,deptno,sal,

Case Deptno

When Ten then 1*sal

When 2*sal

When 3*sal

else SAL/2

End

from EMP;

②decode:decode (EXPR1,EXPR2,EXPR3,.........)

Eg: Judge Deptno, if it is department 10th, returns one-fold pay (1*sal), or 0 if not.

Sql>select Ename,deptno,sal,decode (deptno,10,sal,0) from EMP;

Eg: to judge Deptno, if it is Department 10th, return one-fold salary (1*sal), return 30 times times Salary (30*sal) If it is Department number 30th, and return 50 if it is department 41st.

sql>Select Ename,deptno,sal,decode (deptno,10,sal,30,30*sal,41,50) from EMP;

Use decode to rewrite the case in case

sql>Select Ename,deptno,sal,decode (DEPTNO,10,SAL,20,2*SAL,30,3*SAL,SAL/2) from EMP;

This article is from "Rookie Talent" blog, please be sure to keep this source http://omphy.blog.51cto.com/7921842/1887066

Oracle Functions-Single-line functions-conversion functions, conditional expressions