8-Oracle函数有哪些

CSDN话题挑战赛第2期
参赛话题：学习笔记

Oracle基础知识整理：C站下载链接

1 Oracle 基础知识	2 Oracle 安装（附详细安装操作手册）	3 Oracle 基本使用
4 Oracle 用户管理	6 -1Oracle 表的管理-创建修改表	6-2 Oracle 表的管理-表查询
6-3 Oracle 表的管理-表复杂查询	6-4 Oracle表复杂查询 -多表查询	6-5 Oracle表复杂查询 -子查询
6-6 Oracle表复杂查询 -合并查询-增删改数据	7-java连接oracle-Oracle中的事务处理	12

9 Oracle 函数

9.1 sql函数的使用 --字符函数

·介绍

字符函数是oracle中最常用的函数，我们来看看有哪些字符函数：

*lower(char):将字符串转化为小写的格式

*upper(char):将字符串转化为大写的格式

*length(char):返回字符串的长度

*substr(char,m,n):取字符串的子串,从m所在的位置取，取出n个来

1.将所有员工的名字按小写的方式显示

select lower(ename) from emp;

2.将所有员工的名字按大写的方式显示

select upper(ename) from emp;

3.显示正好为5个字符的员工的姓名

select * from emp where length(ename)=5;

4.显示所有员工姓名的前三个字符

select subStr(ename,0,3) from emp;

5.以首字母大写的方式显示所有员工的姓名

select upper(substr(ename,1,1)) ||   lower(substr(ename,2,length(ename)-1))   from emp ;

6.以首字母小写的方式显示所有员工的姓名

 select lower(substr(ename,1,1)) ||   upper(substr(ename,2,length(ename)-1))   from emp ;

*replace(char1,search_string,replace_string)

7.显示所有员工的姓名，用"a"替换所有“A”

select replace(ename,'A','a') from emp;

*instr(start,SearchString,SearchChar, [compare])

8.取子串在字符串的位置,start，用于设置每次搜索的开始位置,compare(0和1)0表示执行二进制比较，1表示执行文本比较查找0在名字中的位置

 select ename,instr(ename,'O') from emp;

9.2 sql函数的使用 --数学函数

·介绍

数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round,我们讲最常用的：

*round(n,[m])

*trunc(n,[m])

*mod(m,n)

*floor(n)

*ceil(n)

对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报表有不同的结果。

·round(n,[m])该函数用于执行四舍五入，

如果省掉m,则四舍五入到整数；

如果m是正数，则四舍五入到小数点的m位后， 如果m是负数，则四舍五入到小数点的m位前。

·trunc(n,[m])该函数用于截取数字。

如果省掉m，就截取小数部分，

如果m是整数，就截取到小数点的m位后，

如果m是负数，则截取到小数点的前m位

·mod(m,n) 取模、

eg.

  select mod(10,2) from dual; 结果为0

  select mod(10,3) from dual; 结果为1

1.在oracle中有一个dual表，是用来做测试的

·floor(n)返回小于或是等于n的最大整数

floor(55.66) 取值结果为55

向下取整

·ceil(n)返回大于或是等于n的最小整数

ceil(55.66) 取值结果为56

向上取整

2.显示在一个月为30天的情况下所有员工的日薪金，忽略余数。

select ename,trunc(sal/30) from emp;  或 select ename,floor(sal/30) from emp;

3.其它的数学函数，有兴趣的可去看看

其他数学函数
*abs(n) 返回数字n的绝对值 select abs(-13) from dual; 结果13
*acos(n):返回数字的反余弦值
*asin(n):返回数字的反正弦值
*atan(n):返回数字的反正切
*cos(n):
*exp(n):返回e的n次幂
*log(m,n):返回对数值 （m的x次幂=n）
*power(m,n):返回m的n次幂

9.3 sql函数的使用 --日期函数

·介绍

日期函数用于处理date类型的数据

默认情况下日期格式是dd-mon-yy 即12-7月-78

(1)sysdate:该函数返回系统时间

(2)add_months(d,n):在指定日期d上加n个月

(3)last_day(d):返回指定日期所在月份的最后 一天

1.查找已经入职8个月的员工

select * from emp where sysdate>add_months(hiredate,8);

2.显示满10年服务年限的员工的姓名和受雇日期

select * from emp where sysdate>=add_months(hiredate,12*10);

3.对于每个员工，显示其加入公司的天数

select ename, trunc(sysdate-hiredate)   "入职天数" from emp;

4.找出各月倒数第3天受雇的所有员工

select ename,hiredate from emp where   last_day(hiredate)-2=hiredate;

9.4 sql函数的使用 --转换函数

·介绍

**转换函数:**用于将数据类型从一种转为另外一种，在某些情况下，oracle server允许值的数据类型和实际的不一样，这时oracle server会隐含的转化数据类型

比如：

create table tl(id int);

 insert into t1 values('10')-->这样oracle会自动的将'10'-->10

 create table t2(id varchar2(10));

 insert into t2 values(1); -->这样oracle就会自动的将1 -->'1'；

我们要说的是尽管oracle可以进行隐含的数据类型的转换，但是它并不适应所有的情况，为了提高程序的可靠性，我们应该使用转换函数进行转换。

·to_char 转换字符串

日期是否可以显示 时/分/秒

 select to_char(sysdate, 'yyyy-mm-dd HH24:mi:ss') from dual;

薪水是否可以显示指定的货币符号

 select ename,to_char(sal,'L99,999.99')   from emp;

 select ename,to_char(sal,'$99,999.99')  from emp;

其他转换函数
yy:两位数字的年份 2004->04	9:显示数字，并忽略前面0
yyyy:四位数字的年份 2004年	0:显示数字,如位数不足，则用0补齐
mm:两位数字的月份 8月–>08	.:在指定位置显示小数点
dd:两位数字的天 30号–>30	,:在指定位置显示逗号
hh24: 8点 -->20	$:在数字前加美元
hh12: 8点 -->08	L:在数字前加本地货币符号
mi/ss -->显示分钟/秒	C:在数字前加国际货币符号
	G:在指定位置显示组分隔符
	D:在指定位置显示小数点符号（.）

1. 显示1980年入职的所有员工

 select * from emp where to_char  (hiredate,'yyyy')=1980;

2.显示所有12月入职的员工

select * from emp where to_char  (hiredate,'mm')=12; 

·sys_context

1)terminal:当前会话客户所对应的终端的标识符

2)language:语言

3)db.name:当前数据库名称

4)nls_date_format:当前会话客户所对应的日期格式

5)session_user:当前会话客户所对应的数据库用户名

6)current_schema:当前会话客户所对应的默认方案名？

7)host:返回数据库所在主机的名称

通过该函数，可以查询一些重要信息，比如你现在使用那些数据库？

select sys_context('userenv','db_name') from dual;

·用户和方案的关系

用户一旦创建以后，oracle就自动的创建一个方案，方案的名称和用户名一模一样，方案里边有很多的数据对象（表，视图，触发器，存储过程，角色，表空间），它是以方案的方式来组织数据对象的。

注意：用户名和方案是一样的，一个用户对应一个方案