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