oracle函数学习

SQL> select substr(‘liuyuehui‘,3) as first_name from dual;

FIRST_NAME
--------------
uyuehui

SQL> select substr(‘liuyuehui‘,1) as first_name from dual;

FIRST_NAME
------------------
liuyuehui

SQL> select substr(‘liuyuehui‘,0) as first_name from dual;

FIRST_NAME
------------------
liuyuehui

SQL> select substr(‘liuyuehui‘,-3,3) from dual;

SUBSTR
------
hui

SQL> select substr(‘liuyuehui‘,length(‘liuyuehui‘)-5) as first_name from dual;

FIRST_NAME
------------
yuehui

SQL> select substr(‘feng‘,lenth(‘feng‘)-1) from dual;
select substr(‘feng‘,lenth(‘feng‘)-1) from dual
                     *
第 1 行出现错误:
ORA-00904: "LENTH": 标识符无效

SQL> select substr(‘feng‘,length(‘feng‘)-1) from dual;

SUBS
----
ng

SQL> select substr(‘liufeng‘,3,7) as firstname,substr(‘liufeng‘,-4,4) as secondn
ame from dual;

FIRSTNAME  SECONDNA
---------- --------
ufeng      feng

SQL> select substr(‘liufeng‘,0,3) as firstname,substr(‘liufeng‘,-4,4) as secondn
ame from dual;

FIRSTN SECONDNA
------ --------
liu    feng

SQL> select upper(substr(‘liufeng‘,0,3) ) as firstname,lower(substr(‘liuyuehui‘,
3)) as secondname from dual;

FIRSTN SECONDNAME
------ --------------
LIU    uyuehui

SQL> select upper(substr(‘liufeng‘,0,3) ) as firstname,lower(substr(‘liuyuehui‘,
4)) as secondname from dual;

FIRSTN SECONDNAME
------ ------------
LIU    yuehui

SQL> select instr(‘Hello World!‘,‘or‘) from dual;

INSTR(‘HELLOWORLD!‘,‘OR‘)
-------------------------
                        8

SQL> select trim(‘   hello   ‘) from dual;

TRIM(‘HELL
----------
hello

SQL> select trim(‘   hello   ‘) as hi from dual;

HI
----------
hello

SQL> select lpad(‘smith‘,10,‘*‘) from dual;

LPAD(‘SMITH‘,10,‘*‘)
--------------------
*****smith

SQL> select lpad(‘smith‘,10,‘*‘) as star_smith from dual;

STAR_SMITH
--------------------
*****smith

SQL> select rpad(‘smith‘,10,‘*‘) as star_smith from dual;

STAR_SMITH
--------------------
smith*****

SQL> select rpad(‘smith‘,10,‘*‘) as reft_star_smith,lpad(‘smith‘,10,‘*‘) as righ
t_star_smith from dual;

REFT_STAR_SMITH      RIGHT_STAR_SMITH
-------------------- --------------------
smith*****           *****smith

SQL> select substr(instr(‘testmyname‘,‘or‘),-4,4) from dual;

S
-

SQL>
SQL> select substr(instr(‘testmyname‘,‘or‘),-4,4) from dual;

S
-

SQL> select instr(‘testmyname‘,‘or‘) from dual;

INSTR(‘TESTMYNAME‘,‘OR‘)
------------------------
                       0

SQL> select instr(‘test myname‘,‘or‘) from dual;

INSTR(‘TESTMYNAME‘,‘OR‘)
------------------------
                       0

SQL> select rpad(‘smith‘,10,‘月‘) as reft_star_smith,lpad(‘smith‘,10,‘*‘) as rig
ht_star_smith from dual;

REFT_STAR_SMITH          RIGHT_STAR_SMITH
------------------------ --------------------
smith月月                *****smith

SQL> select rpad(‘smith‘,10,‘月‘) as reft_star_smith,lpad(‘smith‘,10,‘月‘) as ri
ght_star_smith from dual;

REFT_STAR_SMITH          RIGHT_STAR_SMITH
------------------------ ------------------------
smith月月                 月月smith

SQL> select next_day(sysdate,‘星期一‘) from dual;

NEXT_DAY(SYSDA
--------------
19-1月 -15

SQL> select next_day(sysdate,‘星期天‘) from dual;
select next_day(sysdate,‘星期天‘) from dual
                        *
第 1 行出现错误:
ORA-01846: 周中的日无效

SQL> select next_day(sysdate,‘星期日‘) from dual;

NEXT_DAY(SYSDA
--------------
18-1月 -15

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDA
--------------
31-1月 -15

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

ADD_MONTHS(SYS
--------------
14-2月 -15

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

NEXT_MONTH
--------------
14-2月 -15

SQL> select to_char(sysdate,‘yyyy‘) from dual;

TO_CHAR(
--------
2015

SQL> select to_char(sysdate,‘mmmm‘) from dual;

TO_CHAR(
--------
0101

SQL> select to_char(sysdate,‘mm‘) from dual;

TO_C
----
01

SQL> select to_char(sysdate,‘mm-ss‘) from dual;

TO_CHAR(SY
----------
01-52

SQL> select to_char(sysdate,’D’) from dual;//返回星期
  2
SQL> select to_char(sysdate,’D’) from dual;
select to_char(sysdate,’D’) from dual
                       *
第 1 行出现错误:
ORA-01756: 引号内的字符串没有正确结束

SQL> select to_char(sysdate,‘D‘) from dual;

TO
--
4

SQL> Select to_date(?20090210?,?yyyyMMdd?) from dual;
Select to_date(?20090210?,?yyyyMMdd?) from dual
               *
第 1 行出现错误:
ORA-00911: 无效字符

SQL> Select to_date(‘20090210"?,?yyyyMMdd?) from dual;
ERROR:
ORA-01756: 引号内的字符串没有正确结束

SQL> Select to_date(‘20090210‘?,?yyyyMMdd?) from dual;
Select to_date(‘20090210‘?,?yyyyMMdd?) from dual
                         *
第 1 行出现错误:
ORA-00911: 无效字符

SQL> Select to_date(‘20090210‘,yyyyMMdd?) from dual;
Select to_date(‘20090210‘,yyyyMMdd?) from dual
                                  *
第 1 行出现错误:
ORA-00911: 无效字符

SQL> Select to_date(‘20090210‘,‘yyyyMMdd‘?) from dual;
Select to_date(‘20090210‘,‘yyyyMMdd‘?) from dual
                                    *
第 1 行出现错误:
ORA-00911: 无效字符

SQL> Select to_date(‘20090210‘,‘yyyyMMdd‘) from dual;

TO_DATE(‘20090
--------------
10-2月 -09

SQL> select empno, ename, sal,
  2  case deptno
  3  when 10 then ‘财务部‘
  4  when 20 then ‘研发部‘
  5  when 30 then ‘销售部‘
  6  else ‘未知部门‘
  7  end 部门
  8  from emp;
from emp
       *
第 8 行出现错误:
ORA-00942: 表或视图不存在

SQL> select empno, lpad(initcap(trim(ename)),10,‘ ‘) name, job, sal from emp;
select empno, lpad(initcap(trim(ename)),10,‘ ‘) name, job, sal from emp
                                                                    *
第 1 行出现错误:
ORA-00942: 表或视图不存在

SQL> 从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开

C:\Users\yuehui>

C:\Users\yuehui>

C:\Users\yuehui>