Session altered.
SQL> select to_char(sysdate,'d dd ddd') from dual;
TO_CHAR(
--------
7 18 261
6@@@@the calculation of date.
SQL> select last_name,(sysdate-hire_date)/7 week,
months_between(sysdate,hire_date) past_months from hr.employees
where employee_id=100;
LAST_NAME WEEK PAST_MONTHS
------------------------- ---------- -----------
King 1265.66817 291.054103
7@@@@set session nls_language.you could see different country wordage.
[root@station23 ~]# export NLS_LANG="simplified chinese_china.AL32UTF8"
SQL> alter session set nls_language='simplified chinese';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------------
18/9月 /11
SQL> alter session set nls_language='korean';
Session altered.
SQL> select to_char(sysdate,'month') from dual;
TO_CHAR(
--------
9
SQL> alter session set nls_language='french';
SQL> select to_char(sysdate,'month') from dual;
TO_CHAR(SYSDATE,'MONTH')
------------------------------------
septembre
SQL> alter session set nls_language='simplified chinese';
Session altered.
SQL> select to_char(sysdate,'day') from dual;
TO_CHAR(SYSD
------------
星期日
8@@@@you can embed the text in your return value.using "text".
SQL> select to_char(sysdate,'"this is" day') from dual;
TO_CHAR(SYSDATE,'"TH
--------------------
this is 星期日
9@@@@the General Purpose Command to_char(x,y).
SQL> select to_char(salary,'L999,999,999,999.00') from hr.employees where employee_id=100;
-----------------------------
$24,000.00
SQL> select to_char(salary,'L000,000,000.00') from hr.employees where employee_id=100;
TO_CHAR(SALARY,'L000,000,
-------------------------
$000,024,000.00
SQL> alter session set nls_territory='china';
Session altered.
SQL> select to_char(salary,'L000,000,000.00') from hr.employees where employee_id=100;
TO_CHAR(SALARY,'L000,000,
-------------------------
￥000,024,000.00
SQL> select to_char(salary,'L00.00') from hr.employees where employee_id=100;
TO_CHAR(SALARY,'
----------------
################
SQL> SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')
FROM employees
WHERE hire_date < TO_DATE('01-Jan-90', 'DD-Mon-RR');
LAST_NAME TO_CHAR(HIRE_DATE,'D
------------------------- --------------------
Whalen 17-Sep-1987
King 17-Jun-1987
Kochhar 21-Sep-1989
10@@@@nvl function to control the null values.
NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3) @@@@$1 null=>expr3, $1 not null=>expr2
NULLIF (expr1, expr2) @@@@$1=$2 => null $1!=$2 =>expr1
COALESCE (expr1, expr2, ..., exprn) @@@@if expr$ not null=>expr$ ,or echo exprn.
11@@@@simple and complex case and decode.the complex case could not be transform to decode.
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
SELECT last_name, salary,
CASE WHEN last_name='Hunold' then 2*salary
WHEN job_id='IT_PROG' THEN 1.10*sal