oracle train 2

1,  Function : to_char(hire_date,'format_model')

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

from employees;  ( 17 6月 1987)51Testing软件测试网SqT U1Tt5[(g6KZ

function: to_char(number ,’format_model’)

LS: select to_char(salary,'$99,999.00') salary from employees51Testing软件测试网}!D9ao:c6j(Y

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

If we change '$99,999.00' into 'L99,999.00', then enquerying result is (RMB6,000.00)51Testing软件测试网(T4^+H(Gu4Z8u

 2, function to_number () and to_date()51Testing软件测试网'YJ{K_/?

 3,nesting functiom51Testing软件测试网%y1i&jP"[0R

nvl(expr1, expr2)  if expr1 is null. Then change expr1 into expr251Testing软件测试网\P#x4{@9E.p |

nvl2(expr1,expr2,expr3) if expr1 isn’t null, change expr2 into expr1, else change expr3 into expr1

 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)51Testing软件测试网S$m$GVe"~ Mo

 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 expression51Testing软件测试网e5RJ"|m1T

select last_name,job_id,salary,

case job_ID WHEN 'IT_PROG' THEN 1.10*salary51Testing软件测试网*}U] r*URyp/Y
r"Z+J

            WHEN 'ST_CLERK' THEN 1.15*salary

            WHEN 'SA_REP' THEN 1.20*salary51Testing软件测试网7clQ^$zbJn&E9_

else salary  

end "REVISED_SALARY"

FROM employees;

( 51Testing软件测试网3g!W&H-f*n

)51Testing软件测试网8W}9xk?A