Shulou(Shulou.com)06/01 Report--

One-line function

=

Features:

Each row returns a result, and there is an one-to-one correspondence between input and output.

Can be nested, the output of one function can be used as the input of another function, such as: select lowner (upper ('aa')) from dual

The variable passed in can be the value of a column or an expression. Such as select lower (ename) from emp

=

Conversion function:

To_number: converts data of character types to numeric types

To_date: converts data of character types to date type d

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

=

1.1.The to_char: convert data of time type to character type

Syntax: to_char (date,'format_model')

Format_model: date format type

Yyyy digital year form four digits

Spelling form of year year

Mm numeric date form

Full spelling format of month month

Abbreviated three digits of mon month

Abbreviated three digits of dy week

Full writing of day week

Dd numeric form date double digit

For example:

① query the current time, which is displayed in the format of 2011-01-02 12:10:13

SQL > select sysdate from dual;-displays the default time format

SQL > select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') from dual

② queries the current time, and the time format is shown as "write all week, four digits, year and month".

SQL > select to_char (sysdate,'day yyyy month') from dual

The use of ③ fm removes leading 0

SQL > select to_char (sysdate-13,'dd') from dual

TO

--

07

SQL > select to_char (sysdate-13,'fmdd') from dual

TO

--

seven

To insert characters into the ④ date format, you need to enclose the characters in double quotation marks, such as "write the whole week, victor, all the four digits, the year and the month."

SQL > select to_char (sysdate,'day "victor" yyyy month') from dual

=

To_char: convert data of numeric type to character type

Syntax: to_char (number,'format_model')

Format_model: format typ

9-how many number (that is, how many bits) are displayed truly. If the front is 0, the back is number, and the front 0 is not displayed.

0-the number of bits is displayed, and the insufficient bits are filled with 0.

$- display in US dollars

L-use local currency symbols

.

The difference between ① test 9 and 0: you want to display your salary in the format xjournal xxx.xx.

SQL > select ename,to_char (sal,'9999.99') from emp

Change to

SQL > select ename,to_char (sal,'9009.09') from emp

It is found that there is no difference, because 9 and 0 represent one digit.

-

Change to

SQL > select ename,to_char (sal,'0999.99') from emp

Now we need to display 4 digits, and 800s should be displayed as 0800, because the front is 0, that is to say, it is not enough to fill it with 0.

-

Change to

SQL > select ename,to_char (sal,'09909090999.99') from emp

Summary: both 9 and 0 represent a number, and 0 indicates that the insufficient bit is filled with 0, which is generally used only in the leading bit.

=

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

SQL > select to_number ('234234.4350') from dual

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

-

234234.435

① is used to convert hexadecimal to decimal

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 typ

Yyyy digital year form four digits

Spelling form of year year

Mm numeric date form

Full spelling format of month month

Abbreviated three digits of mon month

Abbreviated three digits of dy 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、空值函数 ①NVL(expr1,expr2)：对expr1进行判断，如果expr1为空，则返回expr2，如果expr1不为空，则返回expr1本身 SQL>

Select ename,comm,nvl (comm,0) from emp

Note: data types need to match. For example, if expr1 is number, then expr2 should also be number, otherwise an error will be reported.

② NVL2 (exp1,expr2,expr3): determines expr1, returns expr3 if expr1 is empty, and expr2 if expr1 is not empty

SQL > select ename,comm,nvl2 (comm,1,0) from emp

③ NULLIF (expr1,expr2): returns null if expr1 and expr2 are equal, expr1 otherwise

SQL > select sal,nullif (sal,3000) from emp

=

5. Conditional expression

Syntax for ① case:

Case expr when expr1 when return_expr1

When expr2 when return_expr2

When expr3 when return_expr3

Else else_expr

End

Eg: to judge deptno, if department 10 returns double salary (1*sal), if it is department 20, return double salary (2*sal), if it is 30, return triple salary, if none, return half salary

Select ename,deptno,sal

Case deptno

When 10 then 1*sal

When 20 then 2*sal

When 30 then 3*sal

Else sal/2

End

From emp

② decode:decode (expr1,expr2,expr3,.)

Eg: judge the deptno, if it is department 10, return double the salary (1*sal), if not, return 0.

SQL > select ename,deptno,sal,decode (deptno,10,sal,0) from emp

Eg: judge the deptno. If it is department 10, return double salary (1*sal); if it is department 30, return 30 times salary (30*sal); if department 41, return 50.

SQL > select ename,deptno,sal,decode (deptno,10,sal,30,30*sal,41,50) from emp

Rewrite column children in case using decode

SQL > select ename,deptno,sal,decode (deptno,10,sal,20,2*sal,30,3*sal,sal/2) from emp

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.