oracle常用函数

来源：互联网发布：淘宝子账号分流设置编辑：程序博客网时间：2024/06/06 14:32

oracle常用函数

【1】字符串函数
①lpad和rpad：填充补全
--格式化深圳股票代码
select lpad('21','6','0') stock_code from dual;
--结果
STOCK_CODE
----------
000021
--如果原字符串长度超过，则截断
select lpad('1234567','6','0') stock_code from dual;
--结果
STOCK_CODE
----------
123456
--格式化深圳股票代码
select rpad('21','6','0') stock_code from dual;
--结果
STOCK_CODE
----------
210000
--如果原字符串长度超过，则截断
select lpad('1234567','6','0') stock_code from dual;
--结果
STOCK_CODE
----------
123456

②lower和upper 转化大小写

③initcap 单词首字母大写(将非单词字符作为单词的分隔符进行首字母大写)
select initcap('big') from dual;--Big
select initcap('big_big_big') from dual;--Big_Big_Big
select initcap('big/big/big') from dual;--Big/Big/Big
select initcap('big big big') from dual;--Big Big Big
select initcap('big*big*big') from dual;--Big*Big*Big
......

④length 返回字符串的长度（其他类型转为字符串后也可以返回其长度）
select length('abc') from dual;--3
select length('abc ') from dual;--5 空格也算
select length('ab c') from dual;--4 空格也算
select length(12.32) from dual;--5 12.32转为字符串后返回其长度
select length('') from dual;--null 空串返回的是null 因为length(null)=null

⑤substr 截取字符串
select substr('1234567890',5,4) from dual;--5678

⑥instr 返回子串在父串中出现的位置
select instr('bigbigbig','big') from dual;--1 父串,子串
select instr('bigbigbig','big',2) from dual;--4 父串,子串,开始搜索的位置
select instr('bigbigbig','big',2,2) from dual;--7 父串,子串,开始搜索的位置，第几次出现

⑦trim 删除首尾空格 ltrim 删除左边空格 rtrim 删除右边空格
select trim(' abc ') from dual;--abc
select ltrim(rtrim(' abc ')) from dual;--abc
select ltrim(' abc ') from dual;--abc_
select rtrim(' abc ') from dual;--_abc

⑧to_char
select ltrim(to_char(120,'99999')) from dual;--120
select ltrim(to_char(0.96,'0.00')) from dual;--0.96
select ltrim(to_char(5678.99,'999,999,999.999')) from dual;--5,678.990
select ltrim(to_char(5678.99,'$999,999,999.999')) from dual;--$5,678.990
select ltrim(to_char(5678.99,'U999,999,999.999')) from dual;--￥5,678.990
select to_char(sysdate,'yyyy-mm-dd') from dual;--2017-09-10
select to_char(sysdate,'q') from dual;--返回季度

⑨chr函数将ascii码转换为字符串

⑩translate 翻译字符串
select translate('1234232o3jrojfflfjwajoojflweo','abcdefghijklmnopqrstuvwxyz',' ') from dual;--12342323
=====================================================
【2】数学函数

①abs 绝对值
select abs(-2.1) from dual;--2.1

②round 四舍五入
select round(2.3,-1) from dual;--0

③ceil 向上取整
select round(2.3,-1) from dual;--0

④floor 向下取整
select floor(2.99) from dual;

⑤sign 判断正负性
select sign(-8) from dual;-- -1
select sign(8) from dual;--1
select sign(0) from dual;--0

⑥sqrt 开方
select round(sqrt(2),3) from dual;--1.414

⑥power 平方
select power(2,4) from dual;--16

⑦trunc 截断函数
select trunc(2.345,2) from dual;--2.34
select trunc(2.345,-1) from dual;--0
select trunc(2.345) from dual;--2

⑧vsize 返回实际的存储空间大小
select vsize('abc') from dual;--3
select vsize(123456789) from dual;--6

⑨to_number
select to_number('122345678.990') from dual;--122345678.99
=====================================================
【3】日期函数

①to_date
select to_date('1999-1-1 12:12:12','yyyy-mm-dd hh:mi:ss') from dual;-1999/1/1 12:12:12

②add_months 添加月份

select to_char(add_months(sysdate,2),'yyyy-mm-dd') from dual;--2017-11-10

--当前时间一年前的时间
select add_months(sysdate,-12) from dual;

--当前时间一年后的时间
select add_months(sysdate,12) from dual;

③last_day 返回特定日期的最后一天
select to_char(last_day(sysdate),'yyyy-mm-dd') from dual;--2017-09-30

④返回两个日期相差的月数
select floor(months_between(sysdate,to_date('2000-07-12','yyyy-mm-dd'))) from dual;--205

⑤current_date返回当前的时区
select sessiontimezone,to_char(current_date,'yyyy-mm-dd hh24:mi:ss') from dual;
--结果
SESSIONTIMEZONE TO_CHAR(CURRENT_DATE,'YYYY-MM-
--------------------------------------------------------------------------- ------------------------------
+08:00 2017-09-10 23:38:39
select to_char(current_date,'yyyy-mm-dd hh24:mi:ss') from dual;--2017-09-10 23:40:59

⑥current_timestamp 当前时间戳
select to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss') from dual;--2017-09-10 23:40:59

⑦extract 返回日期域中的某个值
select extract(year from sysdate) from dual;--2017
select extract(month from sysdate) from dual;--9

【4】聚合函数

max,min,avg,sum,count

--查询工资最高的员工信息
select e.employee_id,e.employee_name,s.salary
from t_employee e,t_salary s
where e.employee_id=s.employee_id
and s.salary=(select max(salary) from t_salary);

--测试count(*),count(column),count(1)的区别
--向t_employee表中插入下面数据
insert into t_employee values(13,null,null,null);
--count(*)
select count(*) from t_employee;--13
--count(employee_name)
select count(employee_name) from t_employee;--12
--count(1)
select count(1) from t_employee;--13

注意：count(1)的速度最快，只要有一条记录就累加1

【5】oracle中的其他函数

①decode 功能类似于if else
decode(表达式，值1，返回1，值2，返回2，。。。，默认值)

②nvl函数
select employee_id,nvl(employee_name,'未知名字') from t_employee;
select nvl(sum(salary),0) from t_salary;

③cast函数常用在强制转换列以创建新表
--创建tmp_salary
create table tmp_salary as
select
cast(salary_id as varchar2(10)) salary_id,
cast(employee_id as varchar2(10)) employee_id,
cast(month as varchar2(10)) month,
cast(salary as varchar2(10)) salary
from t_salary;

④oracle中的表达式
数学运算 +-*/
逻辑运算and or <> !=
位运算 bitand
select bitand(192,100) from dual;--11000000 011001000 bitand=01000000=64

⑤oracle中的特殊判式
between 效率比 >=,<=慢
in
like
select * from emp where ename like '李\%' escape '\';--匹配李%
is null
exists
some,同any
any,比最小的大相当于or

all 比最大的还大相当于and

【6】oracle高级函数--分析函数和窗口函数

排名函数 rank(会跳跃) dense_rank(不会跳跃) row_number(排名号唯一)
分区函数 partition by

--创建学生表
create table student(student_id number,student_name varchar2(10),student_age number);

select * from student;

insert all
into student values(5,'孙七',12)
into student values(6,'周八',14)
into student values(7,'刘九',13)
into student values(8,'胥十',12)
select * from dual;

select * from student;

排名函数 rank会跳跃
--根据学生表的年龄降序排列，使用窗函数over和分析函数rank进行处理，查找学生在排名中的位置
select student_name,rank() over(order by student_age) from student;

--结果
STUDENT_NAME RANK()OVER(ORDERBYSTUDENT_AGE)
------------ ------------------------------
张三 1
赵六 1
孙七 1
胥十 1
刘九 5
王五 5
周八 7
李四 7
8 rows selected

排名函数 dense_rank不会跳跃
select student_name,dense_rank() over(order by student_age) from student;
--结果
STUDENT_NAME DENSE_RANK()OVER(ORDERBYSTUDEN
------------ ------------------------------
张三 1
赵六 1
孙七 1
胥十 1
刘九 2
王五 2
周八 3
李四 3
8 rows selected

row_number

select student_name,row_number() over(order by student_age) from student;
--结果
STUDENT_NAME ROW_NUMBER()OVER(ORDERBYSTUDEN
------------ ------------------------------
张三 1
赵六 2
孙七 3
胥十 4
刘九 5
王五 6
周八 7
李四 8
8 rows selected
=====================================================
分区函数 partition by

--按照部门分类后根据薪资排序
select t.*,dense_rank() over(partition by department order by salary) from salary t order by t.employee_id;

--按照部门求出部门工资总额和部门平均工资
select
t.*,
sum(salary) over(partition by department),
round(avg(salary) over(partition by department))
from salary t
order by t.employee_id;

注意：avg(salary) over(partition by department)是一个整体，所以四舍五入时需要将其看成一个整体

=====================================================

几种分析函数

fisrt_value,last_value,lag,lead

--需求1 获取所有部门中工资最低的员工信息

--方法1 常规写法可能存在多个结果
select t2.department,t2.employee_name.t2.salary
from (select department,min(salary) from salary group by depratment) t1
left join salary t2 on t1.department=t2.department
and t1.min(salary)=t2.salary;

--方法2 使用first_value 仅能返回一个结果
select
distinct department,
first_value(employee_name) over(partition by department order by salary) employee_name,
first_value(salary) over(partition by department order by salary) salary,
from salary;

--需求2 获取所有部门中工资最高的员工信息
select
distinct department,
last_value(employee_name) over(partition by department order by salary
rows between unbounded preceding and unbounded following) employee_name,
last_value(salary) over(partition by department order by salary
rows between unbounded preceding and unbounded following) salary,
from salary;

注意：默认情况partition by department order by salary是在最开始记录到当前记录，所以
rows between unbounded preceding and unbounded following 是在所有结果集中查找记录

--需求3 部门内部按照工资排序，排序后并获取下一员工的名字
select
s.department,s.employee_name,s.salary,
lead(employee_name,1,'N/A') over(partition by department order by salary) pre_name
from salary s;

注意：lead(employee_name,1,'N/A')中employee_name为表达式，1为偏移量，'N/A'为无值时会默认返回的值

--需求4 部门内部按照工资排序，排序后并获取上一员工的名字
select
s.department,s.employee_name,s.salary,
lag(employee_name,1,'N/A') over(partition by department order by salary) pre_name
from salary s;

注意：lag(employee_name,1,'N/A')中employee_name为表达式，1为偏移量，'N/A'为无值时会默认返回的值

阅读全文

0 0