第五课.txt

实例精华

select to_char(sal,'L90,000.99'),sal from emp;
select to_char(sysdate,'"今天是："a.d.yyyy"年"-mm"月"-dd"号"-day "现在的时间是："HH24:MI:SS pm') from dual;
select trunc(sysdate,'D') from dual;
//alter session set nls_language = 'AMERICAN';
//alter session set nls_language = 'SIMPLIFIED CHINESE';
select to_char(sysdate,'A.D.YYYY-MONTH-DD-DAY HH24:MI:SS pm') from dual;
select round(mod(153.456,55.2),1) from dual;
select mod(153.456,55.2) from dual;
select trunc(153.456,2) from dual;
select trunc(153.456,-2) from dual;
select round(153.456,2) from dual;
select round(153.456,-2) from dual;
select trim(' abc ') from dual;
select ltrim('HelloWorld','Hel') from dual;
select rtrim('HelloWorld','World') from dual;
select lower('ABCDE') from dual;
select upper('abcde') from dual;
select concat('Hello','World') from dual;
select substr('HelloWorld',5,3) from dual;
select instr('HelloWorld','W') from dual;
select lpad(10,5,'*') from dual;
select rpad(10,5,'*') from dual;
select length('HelloWorld') from dual;


SELECT ENAME,SAL,COMM,
CASE
WHEN COMM IS NOT NULL THEN (SAL+COMM)*12
WHEN COMM IS NULL THEN SAL*12
END NX
FROM EMP;

SELECT ENAME,SAL,COMM,
CASE
WHEN COMM IS NOT NULL THEN (SAL+COMM)*12
ELSE SAL*12 END NX
FROM EMP;

SELECT ENAME,SAL,COMM,
DECODE(JOB,'SCOTT',SAL*1.1,
           'MANAGER',SAL*1.2,
           'SALESMAN',SAL*2,
           SAL)
REV
FROM EMP;