oracle+10+sqlplus,10g_sqlplus

sqlplus

the following notes is the key to 007 lessons.

1)char

1@@@@#man ascii,check the ascii number

SQL> create table t00702_a(a varchar2(20));

SQL> insert into t00702_a values('''');

SQL> insert into t00702_a values('a');

SQL> insert into t00702_a values('_1');

SQL> insert into t00702_a values('%111');

SQL> insert into t00702_a values('%');

SQL> insert into t00702_a values('_');

SQL> insert into t00702_a values(chr(39)||'2');

SQL> insert into t00702_a values(chr(40));

SQL> insert into t00702_a values('');

SQL> select * from t00702_a;

A

--------------------

%

%111

_

_1

a

'

'2

:

(

10 rows selected.

2@@@@some spical command about char.

SQL> select * from t00702_a where a like '\%' escape '\';

A

--------------------

%

SQL> select * from t00702_a where a like '\%___' escape '\';

A

--------------------

%111

SQL> select * from t00702_a where a like '\%_' escape '\';

no rows selected

SQL> select * from t00702_a where a like '\%%' escape '\';

A

--------------------

%

%111

3@@@@you can search the ascii form in linux.

[root@station23 ~]# man ascii

047   39    27    ’                           147   103   67    g

050   40    28    (

2)null

1@@@@147(4-16)(p142)logical relationship true,false,null

null AND false = false;

null OR true= true;

not null = null;

2@@@@not null= null;

SQL> select * from employees where employee_id not in (select manager_id from employees where manager_id is not null);

SQL> select * from employees where manager_id not in (102,200,201,null);

no rows selected

3)logical parentheses

1@@@@logical priority change, because the sql in the parentheses is firstly executed;

SQL> select count(*) from employees where (salary<7100 or salary>2100) and salary<5000;

COUNT(*)

----------

49

SQL> select count(*) from employees where salary<7100 or salary>2100 and salary<5000;

COUNT(*)

----------

63

4)order by

1@@@@default is ascending, the descending abbreviation is desc.

SQL> select last_name, department_id, salary from employees order by department_id, salary desc;

SQL> select last_name,salary from hr.employees order by 2 desc;

5)single-row functions

1@@@@the boundary of the date is 15 in the function round(x,y)

SQL> select round(to_date('2011-2-15','yyyy-mm-dd'),'month') from dual;

ROUND(TO_DATE('201

------------------

01-2-11

SQL> select round(to_date('2011-2-16','yyyy-mm-dd'),'month') from dual;

ROUND(TO_DATE('201

------------------

01-3-11

2@@@@the Chinese characters occupy the three or more bytes, it's up to your character set.

SQL> create table t00702_a(a varchar2(20));

SQL> insert into t00702_a values('tangjun');

SQL> insert into t00702_a values('堂君');

SQL> select a,length(a), lengthb(a) from t00702_a ;

3@@@@ the different culturehavedifferent date.

SQL> show parameter territory

NAME                            TYPE        VALUE

nls_territory                        string      AMERICA

select next_day(sysdate,6) from dual; 6=>Friday

SQL> alter session set nls_territory='china';

Session altered.

select next_day(sysdate,5 from dual; 6=>Friday

4@@@@alter territory=' Australia ' to test.the last_day is universal function to calculate the last day

every month in different year.

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDATE)

-------------------

2011-09-30 15:55:22

SQL> select last_day(add_months(sysdate,1)) from dual;

LAST_DAY(ADD_MONTHS

-------------------

2011-10-31 15:55:37

SQL> select to_char(sysdate,'d dd ddd') from dual;

TO_CHAR(

--------

1 18 261

SQL> alter session set nls_territory='Australia';

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               WEEKPAST_MONTHS

------------------------- ---------- -----------

King                      1265.66817  291.054103

7@@@@set session nls_language.you could see different country wordage.

[root@station23 ~]# export NLS_LANG="simplifiedchinese_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;

TO_CHAR(SALARY,'L999,999,999,

-----------------------------

$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 

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@@@@simpleand complexcaseand 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_idWHEN '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,

CASEWHEN last_name='Hunold' then 2*salary

WHEN job_id='IT_PROG' THEN 1.10*salary

WHEN job_id='ST_CLERK' THEN 1.15*salary

WHEN job_id='SELECT last_name, salary,

ELSEsalaryEND   "REVISED_SALARY"

FROM   employees;

DECODE(col|expression, search1, result1

[, search2, result2,...,]

[, default])

SELECT last_name, job_id, salary,

DECODE(job_id, 'IT_PROG', 1.10*salary,

'ST_CLERK', 1.15*salary,

'SA_REP',   1.20*salary,

salary)

REVISED_SALARY

FROM   employees;

12@@@@check the support language in your oracle database.

select * from v$nls_valid_values

6) date

1@@@@change the date format,so that you can see the accurate seconds.

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

sysdate

-------------------

2011-09-18 14:13:15

SQL> show parameter nls_date

NAME                                 TYPE        VALUE

nls_date_format                      string      yyyy-mm-dd hh24:mi:ss

nls_date_language                    string

SQL> select sysdate from dual;

SYSDATE

------------------

18-SEP-11