Oracle3

函数

个人的Oracle3

1 基本概念以及预备尝试

a 所谓单行函数就是作用与表里的每一行数据，执行一次的函数

select employee_id , first_name , abs(salary) from employees;

abs()是计算绝对值的函数

b DUAL表

一行一列的特殊表，属于SYS用户，如果我们只需要函数执行一次，可以使用这个表。
dual表里的数据没有任何意义，只是为了维护select的完整性。

 select  abs(-10)  from ??? ; 

如果问号处我们写入employees，那么该条查询语句会针对表里的每一行执行一次。虽然abs(-10)与表里数据没有任何关系，但仍然会返回107行结果。如果填入dual，那么因为dual表里只有一行数据，所以该条查询语句只会有一个结果。

2 字符串相关函数

length(str) 获取字符串长度

查询员工的姓，及其长度select last_name ,length(last_name) from employees;查询姓长度为5的员工select last_name from employeeswhere length(last_name) = 5;

字符串拼接

**语法：

select 'a' || 'b' from dual**<br>

也可使用
select concat(‘a’,’b’) from dual;

截取字符串 substr(str,begin,length)

对str 做截取，从begin(⭐下标从1开始) 开始截length位

select substr('abcdefg',2,4) from dual;

对’abcdefg’字符串的第二位元素开始截取到四个元素
其结果为：”bcde”

查找姓以S开头的员工信息select * from employees where substr(first_name,1,1) = 'S';

查找子字符串 inster(str1,str2,begin)

在str1 中找 str2 , 从begin （下标1开始）开始查找， 找到返回str2首字符所在的下标，否则返回0。

查询名字中含有gt字符的员工select employee_id , fist_name , salary from employees where instr(first_name,'gt',1)! = 0

字符串转换大小写 lower(str) upper(str)

将字符串str 转换大小写

查询姓是'a'或'A'开头的员工信息select employee_id , first_name ,salary from employeeswhere lower(substr(first_name,1,1)) ='a';解释：首先截取到当前姓的第一个字符，并且转换为小写 ，最后在对比。

3 数字相关函数

取模 mod(num1,num2)

类似Java|C语言中的：num1 % num2;

打印员工编号为奇数的员工select * from employeeswhere mod(employee_id,2)=1;解释 ：员工id 模 2 若等 1 的时候满足条件

截取数字n trunc(n,length)

对数字 n 进行截取，精确到小数点后length位。

截取数字select trunc(1234.54321,1) from dual; -----------1234.5

四舍五入 round(n1,length)

对数字n1四舍五入，精确到小数点后length位。

四舍五入select round(123.4567,2) from dual; -----------123.45select round(123.4567) from dual; -----------123

取绝对值 abs(num)

对num 取绝对值(前面提到过)

获取随机数 dbms_random.random()

产生一个很大的随机数(可正可负)

请数据产生一个正整数select mod(abs(dbms_random.random()),100) from dual;解释：产生一个-99~99之间的数后取绝对值请随机查询表中的一行数据select employee_id ,first_name , salary from employeeswhere employee_id  = mod(abs(dbms_random.random()),107)+100;解释：首先产生一个很大的随机数模107(数据总条数)在取绝对值，+100是为了让最后产生的随机数在100~207之间；

4 日期相关操作及函数

时间与日期是数据库中常用的数据类型，在处理很多运算的时候都需要用到。比如银行应用在处理开户业务时要存储开户日期，电信应用在结算通讯费用是也要用到通话时间。下面我们就来学习oracle数据库中关于日期和实践的相关运算和函数

系统时间 sysdate

获取当前系统时间select sysdate from dual;

在sysdate提供的时间数据中，不仅包含年月日的信息，还包括时分秒的信息，
甚至可以精确到毫秒。本例中我们只看到了年月日信息是因为oracle默认的日
期显示格式省略了时分秒等额外信息。oracle里默认的日期格式为’yyyy-mm-dd hh:mi:ss’

获取十天后的日期select sysdate+10 from dual;

注意：对于日期进行算数讯算时，默认单位是天

对月份的算数运算 add_months(myDate,num)

显示上个月的今天selrct add_months(sysdate,-1)from dual;

获取月份的最后一天 last_day()

获取当前月份的最后一天select last_day(sysdate) from dual;

计算两个日期之间相聚多少个月 返回小数

select months_between('1994-07-13',sysdate) from dual;

根据日期的格式做四舍五入

求当年日期距离上一年近还是下一年近select round(sysdate , 'yyyy') from dual;-------2018-01-01

（8-20日书写）

根据日期的格式进行截取

获取年的第一天select trunc(sysdate,'yyyy') from dual;获取月的第一天select trunc(sysdate,'mm') from dual获取周的第一天select trunc(sysdate,'day') from dual;当天的零时零分零秒select trunc(sysdate) from dual;

注意：Oracle是以为周日为一周的第一天

5 类型转换相关操作和函数

字符串转数字 to_number(str)

将给定的字符串str转换成数字类型值

 将字符串1234转换成数字1234 select to_number('1234') from dual;

注意：如果给定的str不是有纯数字组成，sql无法执行，报异常。

将给定类型转字符串 to_char()

to_char(myNumber,’格式字符串’)
将数字变成字符串常见格式：0或者9表示占位，还可以使用各种货币符号￥$等。

请用科学计数法显示员工的工资信息select employee_id,first_name,to_char(salary,'$999,999.00') from employees;

注意：当实际数值超过给定的字符串格式时，数据无法正常显示，会出现若干*符号。

to_char(myDate,’格式字符串’)
将给定的日期值转换成字符串时间表示【重点】
因为Oracle默认的日期显示格式省略了时分秒等额外信息。如果我们想在现实时间的时候看到时分秒的信息的话，就需要手动指定日期的显示格式了。

种类|常用格式字符串--|---------------------年|yy yyyy  rr  rrrr  year(英语年份)月|mm  mon  month日|dd  ddsp  ddth  ddspth星期|d  dy  day小时|hh  am,ap  hh24钟|mi秒|ss

to_date(strDate,日期格式字符串)
根据给定的日期格式字符串转成日期

输出 2008-08-08 是周几selrct to_char(to_date('08-08-08','mm-dd-yy'),'day') from dual;

6 NVL函数 类似与三目运算

问题引入：

与java 语言类似，Oracle中使用NULL代表一个值不存在。需要注意的是NULL值参与运算总返回NULL，这给我们的运算过程带来问题。当我们计算employees中每个员工每月实发工资（工资+奖金，奖金是工资*奖金比率）时，问题就出现了。
我们发现很多员工的实发工资变成了NULL，这很显然是不对的。造成这个错误的原因在于有的员工的commission_pct 为NULL , 在计算总工资时，由于有空值参与，所以导致整个运算结果为空。

问题解决

nvl(n1,n2)
判断n1是否是null，是null执行n2，否则执行n1。(n1==null?n2:n1)实际上我们希望当commission_pct为NULL时，它的值按0来处理。这时我们就可以使用NVL函数解决此问题（将NULL转化为默认值进行计算）。我们将刚才的SQL改写成如下形式：

select employee_id , first_name , salary ,commission_pct,(salary + salary * nvl(commission_pct,0))  as  总月薪  from employees;

我们看到，原来总工资为空的数据现在都恢复正常了

今天的知道汇总到此结束 由于我这变网络还是一个问题所以有些文档先写在本地，找机会上传！

如果我的汇总对你有帮助请点赞关注 如果你对我的代码有疑惑或者意见欢迎提出，本人邮箱：[e-mail]

谢谢