DBA Oracle 2022-07-28

ORACLE开发：Oracle数据库中通用的函数实例详解

一、 Scott用户下的表结构

SCOTT。是在Oracle数据库中，一个示例用户的名称。其作用是为初学者提供一些简单的应用示例，不过其默认是锁定状态，在安装时，根据用户需要，在“数据库配置助手”界面完成后，弹出的对话框中–口令管理，里面解锁。

SCOTT是ORACLE内部的一个示例用户，缺省口令为tiger，下面有表emp, dept等，这些表和表间的关系演示了关系型数据库的一些基本原理

1、如果自己没有Scoot表就可以自己创建一个

（1）创建DEPT表

CREATE  TABLE  DEPT  (

DEPTNO  NUMBER(2)  CONSTRAINT  PK_DEPT   PRIMARY KEY,

DNAME  VARCHAR2(14) ,

LOC  VARCHAR2(13)

) ;

（2）表DEPT添加数据

INSERT  INTO  DEPT  VALUES  (10  ,  ‘ACCOUNTING’  ,  ‘NEW YORK’  );

COMMIT;

INSERT  INTO  DEPT  VALUES  (20  ,  ‘RESEARCH’  ,  ‘DALLAS’  );

COMMIT;

INSERT  INTO  DEPT  VALUES  (30  ,  ‘SALES’  ,  ‘CHICAGO’  );

COMMIT;

INSERT  INTO  DEPT  VALUES  (40  ,  ‘OPERATIONS’  ,  ‘BOSTON’  );

COMMIT;

（3）创建EMP表

CREATE  TABLE  EMP  (

EMPNO  NUMBER(4)    CONSTRAINT PK_EMP PRIMARY KEY,

ENAME  VARCHAR2(10),

JOB  VARCHAR2(9),

MGR  NUMBER(4),

HIREDATE  DATE,

SAL  NUMBER(7,2),

COMM  NUMBER(7,2),

DEPTNO  NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT

);

（4）表EMP添加数据

INSERT   INTO   EMP   VALUES  (7369,’SMITH’,’CLERK’,7902,to_date(’17-12-1980′,’dd-mm-yyyy’),800,NULL,20);

COMMIT;

INSERT   INTO   EMP   VALUES  (7499,’ALLEN’,’SALESMAN’,7698,to_date(’20-2-1981′,’dd-mm-yyyy’),1600,300,30);

COMMIT;

INSERT   INTO   EMP   VALUES  (7521,’WARD’,’SALESMAN’,7698,to_date(’22-2-1981′,’dd-mm-yyyy’),1250,500,30);

COMMIT;

INSERT   INTO   EMP   VALUES  (7566,’JONES’,’MANAGER’,7839,to_date(‘2-4-1981′,’dd-mm-yyyy’),2975,NULL,20);

COMMIT;

INSERT   INTO   EMP   VALUES  (7654,’MARTIN’,’SALESMAN’,7698,to_date(’28-9-1981′,’dd-mm-yyyy’),1250,1400,30);

COMMIT;

INSERT   INTO   EMP   VALUES  (7698,’BLAKE’,’MANAGER’,7839,to_date(‘1-5-1981′,’dd-mm-yyyy’),2850,NULL,30);

COMMIT;

INSERT   INTO   EMP   VALUES  (7782,’CLARK’,’MANAGER’,7839,to_date(‘9-6-1981′,’dd-mm-yyyy’),2450,NULL,10);

COMMIT;

INSERT   INTO   EMP   VALUES  (7788,’SCOTT’,’ANALYST’,7566,to_date(’19-04-1987′,’dd-mm-yyyy’)-85,3000,NULL,20);

COMMIT;

INSERT   INTO   EMP   VALUES  (7839,’KING’,’PRESIDENT’,NULL,to_date(’17-11-1981′,’dd-mm-yyyy’),5000,NULL,10);

COMMIT;

INSERT   INTO   EMP   VALUES  (7844,’TURNER’,’SALESMAN’,7698,to_date(‘8-9-1981′,’dd-mm-yyyy’),1500,0,30);

COMMIT;

INSERT   INTO   EMP   VALUES  (7876,’ADAMS’,’CLERK’,7788,to_date(’23-05-1987′,’dd-mm-yyyy’)-51,1100,NULL,20);

COMMIT;

INSERT   INTO   EMP   VALUES  (7900,’JAMES’,’CLERK’,7698,to_date(‘3-12-1981′,’dd-mm-yyyy’),950,NULL,30);

COMMIT;

INSERT   INTO   EMP   VALUES  (7902,’FORD’,’ANALYST’,7566,to_date(‘3-12-1981′,’dd-mm-yyyy’),3000,NULL,20);

COMMIT;

INSERT   INTO   EMP   VALUES  (7934,’MILLER’,’CLERK’,7782,to_date(’23-1-1982′,’dd-mm-yyyy’),1300,NULL,10);

COMMIT;

（5）创建SALGRADE表

CREATE   TABLE   SALGRADE    (

GRADE   NUMBER,

LOSAL   NUMBER,

HISAL   NUMBER

);

（6）表SALGRADE添加数据

INSERT   INTO   SALGRADE   VALUES   (1,700,1200);

COMMIT;

INSERT   INTO   SALGRADE   VALUES   (2,1201,1400);

COMMIT;

INSERT   INTO   SALGRADE   VALUES   (3,1401,2000);

COMMIT;

INSERT   INTO   SALGRADE   VALUES   (4,2001,3000);

COMMIT;

INSERT   INTO   SALGRADE   VALUES   (5,3001,9999);

COMMIT;

（7）创建BONUS表

CREATE   TABLE   BONUS    (

ENAME   VARCHAR2(10)  ,

JOB   VARCHAR2(9)  ,

SAL   NUMBER,

COMM   NUMBER

) ;

二、单行函数

1、字符函数

接收字符输入返回字符或者数值，dual 是伪表

（1）把小写的字符转换成大写的字符

–（1）把小写的字符转换成大写的字符

select upper(‘smith’) from dual;

（2）把大写字符变成小写字符

–（2）把大写字符变成小写字符

select lower(‘WHJ’) from dual;

2、数值函数

（1）四舍五入函数：round()

默认情况下 ROUND 四舍五入取整，可以自己指定保留的位数
四舍五入函数小数第一位小于5

–四舍五入函数 小数第一位小于5

select round(5.342345) from dual;

四舍五入函数小数第一位大于5

–四舍五入函数 小数第一位小于5

select round(5.342345) from dual;

四舍五入函数小数点保留两位

–四舍五入函数 小数点保留两位

select round(5.12764,2) from dual;

（2）日期函数

Oracle 中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律

日期 – 数字 = 日期

日期 + 数字 = 日期

日期 – 日期 = 数字

范例：查询雇员的进入公司的周数。（分析：查询雇员进入公司的天数(sysdate – 入职日期)/7就是周数）

–查询雇员的进入公司的周数。（分析：查询雇员进入公司的天数(sysdate – 入职日期)/7就是周数）

–1.员工表

select * from emp;

–2.查询ward 进入公司的周数

select Ename,round((sysdate-hiredate)/7) from emp where Ename=’WARD’;

获得两个时间段中的月数：MONTHS_BETWEEN()

范例：查询所有雇员进入公司的月数

–查询所有雇员进入公司的月数

select ename,round(months_between(sysdate,hiredate)) as 进入公司月数 from emp;

（3）转换函数

TO_CHAR:字符串转换函数

范例：查询所有的雇员将将年月日分开，此时可以使用 TO_CHAR 函数来拆分

拆分时需要使用通配符
年：y, 年是四位使用 yyyy
月：m, 月是两位使用 mm
日：d, 日是两位使用 dd

查询所有的雇员将将年月日分开

–查询所有的雇员将将年月日分开

select empno,ename,

       to_char(hiredate,’yyyy’) as 年,

       to_char(hiredate,’mm’) as 月,

       to_char(hiredate,’dd’) as 日

       from emp;

日期将日期格式改为yyyy-mm-dd字符串格式

— 初始格式

select * from emp;

–日期将日期格式改为yyyy-mm-dd字符串格式

select empno,ename,to_char(hiredate,’yyyy-mm-dd’) from emp;

在结果中10以下的月前面补了0，可以使用fm去掉前置0

–在结果中10以下的月前面补了0，可以使用fm去掉前置0

select empno,ename,to_char(hiredate,’fmyyyy-mm-dd’) from emp;

TO_DATE:日期转换函数

TO_DATE 可以把字符串的数据转换成日期类型

–TO_DATE 可以把字符串的数据转换成日期类型

select to_date(‘2022-03-10′,’yyyy/mm/dd:ss’)as 当前日期 from dual;

（4）通用函数

空值处理 nvl

范例：查询所有的雇员的年薪

–查询所有的雇员的年薪 comm年终奖

select ename,sal*12+comm from emp;

我们发现很多员工的年薪是空的，原因是很多员工的奖金是 null，null 和任何数值计算都是

null，这时我们可以使用 nvl 来处理

–查询所有的雇员的年薪 comm年终奖

select ename,nvl(comm,0) 年终奖 ,sal*12+nvl(comm,0)年薪 from emp;

Decode 函数

–该函数类似 if….else if…esle

–语法：
DECODE(col/expression, [search1,result1],[search2, result2]….[default])
Col/expression:列名或表达式

1. Search1，search2…:用于比较的条件
2. Result1, result2…:返回值
3. 如果 col/expression 和 Searchi 匹配就返回 resulti,否则返回 default 的默认值

–decode函数

–1. 我是1

select decode(1,1,’我是1′,2,’我是2′,’我是无名’) from dual;

–2. 我是2

select decode(2,1,’我是1′,2,’我是2′,’我是无名’) from dual;

–3. 我是无名

select decode(3,1,’我是1′,2,’我是2′,’我是无名’) from dual;

范例：查询出所有雇员的职位的中文名

select ename,decode(job,

                   ‘clerk’,’业务员’,

                   ‘SALESMAN’,’销售’,

                   ‘PRESIDENT’,’总裁’,

                   ‘MANAGER’,’经理’,

                   ‘NALYST’,’分析员’,

                   ‘员工’

                   ) from emp ;

case when

CASE expr WHEN comparison_expr1 THEN return_expr1

 [WHEN comparison_expr2 THEN return_expr2

 WHEN comparison_exprn THEN return_exprn

 ELSE else_expr]

END

范例：查询出所有雇员的职位的中文名

–范例：Case when 查询出所有雇员的职位的中文名

select t.empno,t.ename,

       case

          when t.job=’clerk’ then ‘业务员’

          when t.job=’SALESMAN’ then ‘销售’

          when t.job=’PRESIDENT’ then ‘总裁’

          when t.job=’MANAGER’ then ‘经理’

          when t.job=’NALYST’ then ‘分析员’

          else ‘员工’

          end

          from emp t;

三、多行函数（聚合函数）

1、统计记录数

范例：查询出所有员工的记录数

— 范例：查询出所有员工的记录数

select count(*) from emp;

不建议使用 count(*)，可以使用一个具体的列以免影响性能。

–不建议使用 count(*)，可以使用一个具体的列以免影响性能。

select count(ename) from emp;

2、最小值查询 min()

范例：查询出来员工最低工资

–范例：查询出来员工最低工资

select min(sal) from emp;

3、最大值查询 max()

范例：查询出员工的最高工资

–范例：查询出员工的最高工资

select max(sal) from emp;

4、平均值查询 avg()

范例：查询出员工的平均工资

–范例：查询出员工的平均工资

select avg(sal) from emp;

5、求和函数

范例：查询出 20 号部门的员工的工资总和

–范例：查询出 20 号部门的员工的工资总和

select sum(sal) from emp where deptno=20;

四、分组统计

分组统计需要使用 GROUP BY 来分组

–语法：

SELECT * |列名 FROM 表名

{WEHRE 查询条件}

{GROUP BY 分组字段}

 ORDER BY

 列 名 1 ASC|DESC，列名 2…ASC|DESC

范例：查询每个部门的人数

— 范例：查询每个部门的人数

select deptno,count(ename) from emp group by deptno;

范例：查询出每个部门的平均工资

— 范例：查询出每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

如果我们想查询出来部门编号，和部门下的人数

— 如果我们想查询出来部门编号，和部门下的人数

select deptno,count(ename) from emp;

我们发现报了一个 ORA-00937 的错误

注意：

1.如果使用分组函数，SQL 只可以把 GOURP BY 分组条件字段和分组函数查询出来，不能有其

他字段。

2. 如果使用分组函数，不使用 GROUP BY 只可以查询出来分组函数的值

范例：按部门分组，查询出部门名称和部门的员工数量

— 范例：按部门分组，查询出部门名称和部门的员工数量

select d.deptno,d.dname,count(e.ename)

from emp e,dept d

where e.deptno=d.deptno

group by d.deptno,d.dname

范例：查询出部门人数大于 5 人的部门

— 范例：查询出部门人数大于 5 人的部门

–(分析：需要给 count(ename)加条件，此时在本查询中不能使用 where，可以使用 HAVING)

select d.deptno,d.dname,count(e.ename)

 from emp e,dept d

 where e.deptno=d.deptno

 group by d.deptno,d.dname

 having  count(e.ename)>5 ;

分析：需要给 count(ename)加条件，此时在本查询中不能使用 where，可以使用 HAVING

范例：查询出部门平均工资大于 2000 的部门

— 范例：查询出部门平均工资大于 2000 的部门

select d.deptno,d.dname,avg(e.sal)
from dept d ,emp e
where d.deptno=e.deptno
group by d.deptno,d.dname
having avg(e.sal)>2000;

总结

到此这篇关于Oracle数据库中通用的文章就介绍到这了,更多相关Oracle通用函数内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

ORACLE 技术文档数据库

数据库运维技术服务 » ORACLE开发：Oracle数据库中通用的函数实例详解

DBA 普通

分享到：