oracle中的函数概念,oracle函数概念

-- dual表的存在，主要是为了能够运行函数，以满足语法需求

SQL> desc dual;

Name Type Nullable Default Comments

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

DUMMY VARCHAR2(1) Y

SQL> select * from dual;

DUMMY

-----

X

-- 没有访问任何表，导致sysdate运行失败

SQL> select sysdate;

select sysdate

ORA-00923: FROM keyword not found where expected

SQL> select sysdate from dual;

SYSDATE

-----------

2006-1-17 2

-- 起别名 (alias)

SQL> select sysdate thisisnowtime from dual;

THISISNOWTIME

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

2006-1-17 20:

SQL> desc tbl_student;

Name Type Nullable Default Comments

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

STU_NO CHAR(3)

STU_NAME VARCHAR2(30)

STU_AGE INTEGER

STU_HEIGHT NUMBER(5,2) Y

STU_MARK NUMBER(5,3) Y

SQL> alter table tbl_student add stu_birth date;

Table altered

SQL> desc tbl_student;

Name Type Nullable Default Comments

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

STU_NO CHAR(3)

STU_NAME VARCHAR2(30)

STU_AGE INTEGER

STU_HEIGHT NUMBER(5,2) Y

STU_MARK NUMBER(5,3) Y

STU_BIRTH DATE Y

-- 插入数据，直接从sysdate获得时间

SQL> insert into tbl_student values('010','mike',13,130.5,97.5,sysdate);

1 row inserted

SQL> select * from tbl_student;

STU_NO STU_NAME STU_AGE STU_HEIGHT STU_MARK STU_BIRTH

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

001 mary 14 131.50

002 david 14 132.50

003 tom 15 137.50

006 kent 14 136.50

009 jenny 15 136.50

010 mike 13 130.50 97.500 2006-1-17 2

6 rows selected

SQL> insert into tbl_student values('010','mike',13,130.5,97.5,'2000-1-1 10:00:00');

insert into tbl_student values('010','mike',13,130.5,97.5,'2000-1-1 10:00:00')

ORA-01861: literal does not match format string

-- 使用to_date完成字符串到日期的转化

SQL> insert into tbl_student values('010','mike',13,130.5,97.5,to_date('2000-1-1 10:00:00','yyyy-mm-dd hh24:mi:ss'));

insert into tbl_student values('010','mike',13,130.5,97.5,to_date('2000-1-1 10:00:00','yyyy-mm-dd hh24:mi:ss'))

ORA-00001: unique constraint (TEST1.SYS_C005269) violated

SQL> insert into tbl_student values('011','mike',13,130.5,97.5,to_date('2000-1-1 10:00:00','yyyy-mm-dd hh24:mi:ss'));

1 row inserted

SQL> select * from tbl_student;

STU_NO STU_NAME STU_AGE STU_HEIGHT STU_MARK STU_BIRTH

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

001 mary 14 131.50

002 david 14 132.50

003 tom 15 137.50

006 kent 14 136.50

009 jenny 15 136.50

010 mike 13 130.50 97.500 2006-1-17 2

011 mike 13 130.50 97.500 2000-1-1 10

7 rows selected

SQL> select to_char(sysdate,'yyyy/mm/ss') from dual;

TO_CHAR(SYSDATE,'YYYY/MM/SS')

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

2006/01/46

SQL> select to_char(sysdate,'yyyy/mm/dd') from dual;

TO_CHAR(SYSDATE,'YYYY/MM/DD')

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

2006/01/17

-- 使用to_char完成日期到字符串的转化

SQL> select to_char(sysdate,'yyyy/mm/dd') nowdate from dual;

NOWDATE

----------

2006/01/17

--转小写

SQL> select lower('AbcDD') from dual;

LOWER('ABCDD')

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

abcdd

--转大写

SQL> select upper('AbcDD') from dual;

UPPER('ABCDD')

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

ABCDD

--切割掉左右空格

SQL> select trim('AbcDD ') from dual;

TRIM('ABCDD')

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

AbcDD

-- 左侧补0

SQL> select lpad('1',5,'0') from dual;

LPAD('1',5,'0')

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

00001

-- 右侧补0

SQL> select rpad('1',5,'0') from dual;

RPAD('1',5,'0')

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

10000

SQL> select lpad('1',5,' ') from dual;

LPAD('1',5,'')

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

1

-- 胶水符 (glue),类似于concat函数

SQL> select 'dfrz'||lpad('1',5,'0') from dual;

'DFRZ'||LPAD('1',5,'0')

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

dfrz00001

SQL> select 'dfrz'||lpad('1',5,'0') empid from dual;

EMPID

---------

dfrz00001

SQL> select * from tbl_student;

STU_NO STU_NAME STU_AGE STU_HEIGHT STU_MARK STU_BIRTH

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

001 mary 14 131.50

002 david 14 132.50

003 tom 15 137.50

006 kent 14 136.50

009 jenny 15 136.50

010 mike 13 130.50 97.500 2006-1-17 2

011 mike 13 130.50 97.500 2000-1-1 10

7 rows selected

--五大聚合函数，count(),sum(),max(),min(),avg()

SQL> select count(*) from tbl_student;

COUNT(*)

----------

7

SQL> select max(stu_height) from tbl_student;

MAX(STU_HEIGHT)

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

137.5

SQL> select min(stu_height) from tbl_student;

MIN(STU_HEIGHT)

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

130.5

SQL> select avg(stu_height) from tbl_student;

AVG(STU_HEIGHT)

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

133.64285714285

SQL> select sum(stu_height) from tbl_student;

SUM(STU_HEIGHT)

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

935.5

SQL> select sum(stu_height)/avg(stu_height) from tbl_student;

SUM(STU_HEIGHT)/AVG(STU_HEIGHT

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

7

SQL> select sum(stu_height)/count(stu_height) from tbl_student;

SUM(STU_HEIGHT)/COUNT(STU_HEIG

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

133.642857142857