oracle train 2

1,  Function : to_char(hire_date,'format_model')51Testing软件测试网m4?+v&s| \:Kc
F

LS: select last_name, to_char(hire_date,'fmDD Month YYYY')AS HIREDATE

from employees;  ( 17 6月 1987)51Testing软件测试网0_#rf|]2Zvb7Wq

function: to_char(number ,’format_model’)51Testing软件测试网_p8Y%_I!eOw;[

LS: select to_char(salary,'$99,999.00') salary from employees

where last_name='Ernst';   ($6,000.00)

If we change '$99,999.00' into 'L99,999.00', then enquerying result is (RMB6,000.00)

 2, function to_number () and to_date()51Testing软件测试网0M(E3c Ec'B

 3,nesting functiom

nvl(expr1, expr2)  if expr1 is null. Then change expr1 into expr251Testing软件测试网/]2wY7\)Z@

nvl2(expr1,expr2,expr3) if expr1 isn’t null, change expr2 into expr1, else change expr3 into expr151Testing软件测试网 rv${N9Rxh(u._

 4, function nullif()

nullif(length(last_name),length(first_name)) , if length(last_name)=length(first_name), then values of nullif() is null, else …..is length(last_name)

 5,function coalesce(1,2,3, ) if 1 isn’t null, the values of this function is 1, else judge 2,if 2 isn’t null, then 2 is this function’s values,else………..

 6,case expression

select last_name,job_id,salary,51Testing软件测试网|+L F$X;K KQ

case job_ID WHEN 'IT_PROG' THEN 1.10*salary51Testing软件测试网ld'v?Irw'O

            WHEN 'ST_CLERK' THEN 1.15*salary

            WHEN 'SA_REP' THEN 1.20*salary

else salary  

end "REVISED_SALARY"

FROM employees;

(

)