Oracle常用函数

Adopat

已于 2023-02-27 15:29:09 修改

阅读量4.8k

点赞数 1

分类专栏：数据库文章标签：数据库 oracle

于 2022-04-11 15:54:47 首次发布

本文链接：https://blog.csdn.net/weixin_44446122/article/details/124101095

版权

数据库同时被 2 个专栏收录

4 篇文章 1 订阅

订阅专栏

Oracle

1 篇文章 0 订阅

订阅专栏

oracle常用函数

时间函数

获取周日时间

--获取最近的周日信息
select to_char(trunc(sysdate,'iw') - 1 from dual
-- 获取本周周一
SELECT to_char(trunc(sysdate,'iw'),'yyyymmdd') FROM dual 
-- 获取上周周日
SELECT to_char(trunc(sysdate,'iw') - 1,'yyyymmdd') FROM dual

周相关函数

select
       trunc(sysdate,'W'), --每月1日作为第一个星期第一天   取当前周第一天对应日期
       trunc(sysdate,'WW'), --每年1月1日作为第一个新奇第一天,取当前周对应第一天日期
       trunc(sysdate,'IW'),  --当前日期的周一对应日期（自然周)
       to_char(sysdate,'W'), --每月1日为当月第一周第一天,当天是第几周
       to_char(sysdate,'WW'), --每年1月1日时第一周第一天,当前时间是当年第几周
       to_char(sysdate,'IW')  --当前时间是当年第几周（自然周）
 from dual;

时间计算

SELECT ADD_MONTHS(SYSDATE,1) FROM dual  
-- 返回当前日期的最后一天
SELECT LAST_DAY(SYSDATE) FROM dual 
----截取到年（本年的第一天
select trunc(sysdate,'year') from dual; 
--截取到季度（本季度的第一天）
select trunc(sysdate,'q') from dual; 
--截取到月（本月的第一天）
select trunc(sysdate,'month') from dual; 
-- 本年的天数
select add_months(trunc(sysdate, 'year'), 12)  - trunc(sysdate, 'year') from dual;
-- 加减时间
SELECT sysdate,SYSDATE + INTERVAL '10' month FROM dual; 
SELECT sysdate,SYSDATE + INTERVAL '10' MINUTE FROM dual;
SELECT sysdate,SYSDATE + INTERVAL '10' SECOND FROM dual;
SELECT sysdate,SYSDATE + INTERVAL '10' HOUR FROM dual;
SELECT sysdate,SYSDATE + INTERVAL '10' YEAR FROM dual;
--输出当前日期 星期,月份，年份
select to_char(SYSDATE,'day') from dual;
--select to_char(SYSDATE,'year','NLS_DATE_LANGUAGE = AMERICAN') from dual; 
--select to_char(SYSDATE,'month') from dual; 
-- 计算月份差
SELECT floor(MONTHS_BETWEEN(to_date('2021-05-01','yyyy-mm-dd'), to_date('2021-04-02','yyyy-mm-dd'))) AS 月份差  FROM dual;
SELECT ceil(MONTHS_BETWEEN(to_date('2021-05-01','yyyy-mm-dd'), to_date('2021-04-02','yyyy-mm-dd'))) AS 月份差  FROM dual;
-- 计算天数
SELECT CEIL(to_number(sysdate - to_date('20220401','yyyymmdd'))) FROM dual
-- 获取当天 23:59:00
SELECT TRUNC(SYSDATE)+1-1/86400 FROM dual
-- 获取当天 0点
select TRUNC(SYSDATE) FROM dual

日期格式化

-- 时间格式化为字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; 
-- 返回的是自然周于本年的周数，一年周数最少不低于52  
select to_char(SYSDATE,'iw') from dual;
--返回季度
select to_char(sysdate,'q') from dual; 
--当前时间的年 
select to_char(sysdate,'yyyy') from dual; 
--当前时间的月   
select to_char(sysdate,'mm') from dual;  
--当前时间的日   
select to_char(sysdate,'dd') from dual;  
--当前时间的时  
select to_char(sysdate,'hh24') from dual;
--当前时间的分     
select to_char(sysdate,'mi') from dual; 
--当前时间的秒    
select to_char(sysdate,'ss') from dual;

不同格式的第一天

select TRUNC(sysdate,'DD') from dual;-- 今天的开始

select TRUNC(sysdate,'iw') from dual;-- 这周的第一天

select TRUNC(sysdate,'MM') from dual;-- 这个月的第一天

select TRUNC(sysdate,'q') from dual;-- 这个季度的第一天

select TRUNC(sysdate,'YYYY') from dual; --今年第一天

今天是第几天

-- 今天是这个星期的第几天,这个月的第几天，今年第几天,周天为一周的第一天
SELECT to_char(sysdate,'D') FROM dual  --这个星期的第几天
SELECT to_char(SYSDATE,'DD') FROM dual
SELECT to_char(SYSDATE,'DDD') FROM dual

聚合函数

LEAST()

GREATEST()

-- 求两个数之间的最大值
SELECT GREATEST(12,56) FROM dual;
-- 求两数之间的最小值
SELECT LEAST(-10,-12) FROM dual;

行偏移函数

LAG()

向下偏移,LAG() 不是分组函数

LEAD()

向上偏移，LEAD() 不是分组函数

with tmp as(  
SELECT '202201' AS MONTHNAME,'3000' AS Salary FROM dual
UNION ALL 
SELECT '202202' AS MONTHNAME,'3050' AS Salary FROM dual
UNION ALL
SELECT '202203' AS MONTHNAME,'4000' AS Salary FROM dual
UNION ALL
SELECT '202204' AS MONTHNAME,'3900' AS Salary FROM dual
UNION ALL
SELECT '202205' AS MONTHNAME,'3800' AS Salary FROM dual
UNION ALL
SELECT '202206' AS MONTHNAME,'3200' AS Salary FROM dual
UNION ALL
SELECT '202207' AS MONTHNAME,'5000' AS Salary FROM dual
UNION ALL
SELECT '202208' AS MONTHNAME,'6000' AS Salary FROM dual
UNION ALL
SELECT '202209' AS MONTHNAME,'6000' AS Salary FROM dual
UNION ALL
SELECT '202210' AS MONTHNAME,'7000' AS Salary FROM dual
UNION ALL
SELECT '202211' AS MONTHNAME,'9000' AS Salary FROM dual
UNION ALL
SELECT '202212' AS MONTHNAME,'10000' AS Salary FROM dual
)  
SELECT tmp.*,
LAG(Salary) over(ORDER BY MONTHNAME asc ) 向下偏移,
LEAD(Salary) over(ORDER BY MONTHNAME asc ) 向上偏移
FROM tmp

在这里插入图片描述

正则函数

REGEX_LIKE()

字符串函数

ASCII

--ASCII 返回ASCII码值
SELECT ASCII('a') FROM dual  --97
SELECT ASCII('A') FROM dual  --65

CHR

--CHR 返回ASCII码值对应的字符
SELECT chr(65) FROM dual --A

INSTR

--INSTR 返回字符第一次出现的位置
SELECT INSTR('hello world','llo') FROM dual  --3

CONCAT

--CONCAT 字符串拼接
SELECT CONCAT('hello',' world') FROM dual  --'hello world'

LENGTH

--LENGTH 返回字符串长度
SELECT LENGTH ('hello world') FROM dual --11
SELECT LENGTH ('你好') FROM dual --2

LENTHB

--LENTHB 返回字符的字节数
SELECT LENGTHB ('hello world') FROM dual --11
SELECT LENGTHB ('你好') FROM dual --6

SUBSTR

--SUBSTR 字符出截取，注意索引从1开始
SELECT SUBSTR('hello world',7) FROM dual --world

LPAD

--LPAD 左填充
SELECT LPAD('hello world',20,'0') FROM dual --000000000hello world

RPAD

--RPAD 右填充
SELECT RPAD('hello world',20,'0') FROM dual --hello world000000000

TRIM

-- TRIM 去除前后指定字符
SELECT trim(BOTH '0' FROM '000000000hello world') FROM dual --hello world

排序函数

row_number() over()

是没有重复值的排序(即使两条记录相同，序号也不重复的)，不会有同名次。
dense_rank() over()

是连续的排序，两个第二名仍然跟着第三名。

rank() over()

是跳跃排序，两个第二名下来就是第四名

WITH workers AS(    
  SELECT 'DOM1' dept, 'zhangsan' names , 23 age, 4000 salaries FROM dual UNION ALL    
  SELECT 'DOM1' dept, 'lisi' names     , 35 age, 9000 salaries FROM dual UNION ALL   
  SELECT 'DOM1' dept, 'zhangchen' names, 35 age, 9000 salaries FROM dual UNION ALL  
  SELECT 'DOM1' dept, 'qiansi' names   , 35 age, 4000 salaries FROM dual UNION ALL  
  SELECT 'DOM2' dept, 'wangwu' names   , 26 age, 6500 salaries FROM dual UNION ALL    
  SELECT 'DOM2' dept, 'maliu' names    , 28 age, 6000 salaries FROM dual UNION ALL    
  SELECT 'DOM2' dept, 'zhaoqi' names   , 26 age, 5000 salaries FROM dual UNION ALL    
  SELECT 'DOM1' dept, 'liba' names     , 23 age, 3000 salaries FROM dual     
)  
SELECT 
workers.*,
ROW_NUMBER ()over(ORDER BY SALARIES desc) rn,
DENSE_RANK () over(ORDER BY SALARIES desc) dn,
RANK() over(ORDER BY SALARIES desc) rnk
FROM workers

在这里插入图片描述

行列转换

行转列

case when

with temp as(
SELECT '张三' AS NAME,'语文' AS COURSE,'67' AS SCORE FROM dual
UNION ALL
SELECT '张三' AS NAME,'数学' AS COURSE,'76' AS SCORE FROM dual
UNION ALL
SELECT '张三' AS NAME,'英语' AS COURSE,'43' AS SCORE FROM dual
UNION ALL
SELECT '李四' AS NAME,'语文' AS COURSE,'70' AS SCORE FROM dual
UNION ALL
SELECT '李四' AS NAME,'数学' AS COURSE,'80' AS SCORE FROM dual
UNION ALL
SELECT '李四' AS NAME,'英语' AS COURSE,'90' AS SCORE FROM dual
UNION ALL
SELECT '王五' AS NAME,'语文' AS COURSE,'90' AS SCORE FROM dual
UNION ALL
SELECT '王五' AS NAME,'数学' AS COURSE,'80' AS SCORE FROM dual
UNION ALL
SELECT '王五' AS NAME,'英语' AS COURSE,'70' AS SCORE FROM dual
)
SELECT 
NAME,
SUM(CASE WHEN COURSE='语文' THEN score ELSE '0' end) AS 语文,
SUM(CASE WHEN COURSE='数学' THEN score ELSE '0' end) AS 数学,
SUM(CASE WHEN COURSE='英语' THEN score ELSE '0' end) AS 英语
FROM temp
GROUP BY NAME

在这里插入图片描述

PIVOT

with temp as(
SELECT '张三' AS NAME,'语文' AS COURSE,'67' AS SCORE FROM dual
UNION ALL
SELECT '张三' AS NAME,'数学' AS COURSE,'76' AS SCORE FROM dual
UNION ALL
SELECT '张三' AS NAME,'英语' AS COURSE,'43' AS SCORE FROM dual
UNION ALL
SELECT '李四' AS NAME,'语文' AS COURSE,'70' AS SCORE FROM dual
UNION ALL
SELECT '李四' AS NAME,'数学' AS COURSE,'80' AS SCORE FROM dual
UNION ALL
SELECT '李四' AS NAME,'英语' AS COURSE,'90' AS SCORE FROM dual
UNION ALL
SELECT '王五' AS NAME,'语文' AS COURSE,'90' AS SCORE FROM dual
UNION ALL
SELECT '王五' AS NAME,'数学' AS COURSE,'80' AS SCORE FROM dual
UNION ALL
SELECT '王五' AS NAME,'英语' AS COURSE,'70' AS SCORE FROM dual
)
SELECT * FROM temp
PIVOT(SUM(SCORE) FOR COURSE IN('语文' as 语文,'数学' as 数学,'英语' as 英语));

列转行

union

with temp as(
SELECT '张三' AS NAME,'67' AS 语文,'76' AS 数学,'43' AS 英语 FROM dual
UNION ALL
SELECT '李四' AS NAME,'70' AS 语文,'80' AS 数学,'90' AS 英语 FROM dual
UNION ALL
SELECT '王五' AS NAME,'90' AS 语文,'80' AS 数学,'70' AS 英语 FROM dual
)
SELECT name ,语文 AS SCORE ,'语文' AS course FROM temp
UNION ALL 
SELECT name ,数学 AS SCORE,'数学' AS course FROM temp
UNION ALL
SELECT name ,英语 AS SCORE,'英语' AS course FROM temp

在这里插入图片描述

UNPIVOT

with temp as(
SELECT '张三' AS NAME,'67' AS 语文,'76' AS 数学,'43' AS 英语 FROM dual
UNION ALL
SELECT '李四' AS NAME,'70' AS 语文,'80' AS 数学,'90' AS 英语 FROM dual
UNION ALL
SELECT '王五' AS NAME,'90' AS 语文,'80' AS 数学,'70' AS 英语 FROM dual
)
select name, course, score
from temp 
unpivot((score) for course in(语文,数学,英语));

注意unpivot会自动忽略空值，如果要显示空值应该使用unpivot include nulls

with temp as(
SELECT '张三' AS NAME,'67' AS 语文,'76' AS 数学,'43' AS 英语 FROM dual
UNION ALL
SELECT '李四' AS NAME,'70' AS 语文,'80' AS 数学,null AS 英语 FROM dual
UNION ALL
SELECT '王五' AS NAME,'90' AS 语文,'80' AS 数学,'70' AS 英语 FROM dual
)

select name, course, score
from temp 
unpivot ((score) for course in(语文,数学,英语));

在这里插入图片描述

使用unpivot include nulls

列合并

LISTAGG()

with temp as(
select '中国' nation ,'江苏' city from dual union all
select '中国' nation ,'上海' city from dual union all
select '中国' nation ,'北京' city from dual union all
select '美国' nation ,'纽约' city from dual union all
select '美国' nation ,'波士顿' city from dual union all
select '日本' nation ,'东京' city from dual
)
select nation,listagg(city,',') within GROUP (order by city) as Cities
from temp
group by nation

在这里插入图片描述

with temp as(
select '中国' nation ,'江苏' city from dual union all
select '中国' nation ,'上海' city from dual union all
select '中国' nation ,'北京' city from dual union all
select '美国' nation ,'纽约' city from dual union all
select '美国' nation ,'波士顿' city from dual union all
select '日本' nation ,'东京' city from dual
)
select temp.*,listagg(city,',') within GROUP (order by city) over(PARTITION BY NATION) as Cities
from temp

在这里插入图片描述

行拼接

||

with temp as(
SELECT '张三' AS name,'11' AS age FROM dual
UNION ALL 
SELECT '李四' AS name,'12' AS age FROM dual
UNION ALL
SELECT '王五' AS name,'13' AS age FROM dual
UNION ALL
SELECT '赵六' AS name,'14' AS age FROM dual
UNION ALL
SELECT '田七' AS name,'1511' AS age FROM dual
)
SELECT name||'-'||age AS new_info FROM temp

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FX7Y5WeH-1649663210093)(Oracle常用函数.assets/image-20220411142826417.png)]

0093)]

行拼接

||

with temp as(
SELECT '张三' AS name,'11' AS age FROM dual
UNION ALL 
SELECT '李四' AS name,'12' AS age FROM dual
UNION ALL
SELECT '王五' AS name,'13' AS age FROM dual
UNION ALL
SELECT '赵六' AS name,'14' AS age FROM dual
UNION ALL
SELECT '田七' AS name,'1511' AS age FROM dual
)
SELECT name||'-'||age AS new_info FROM temp

在这里插入图片描述

oracle锁表

-- 1.查看是否有锁表的SQL
select 'blocker('||lb.sid||':'||sb.username||')-sql:'|| qb.sql_text blockers, 
'waiter ('||lw.sid||':'||sw.username||')-sql:'|| qw.sql_text waiters 
from v$lock lb, 
v$lock lw, 
v$session sb, 
v$session sw, 
v$sql qb, 
v$sql qw 
where lb.sid=sb.sid 
and lw.sid=sw.sid 
and sb.prev_sql_addr=qb.address 
and sw.sql_address=qw.address 
and lb.id1=lw.id1 
and sw.lockwait is not null 
and sb.lockwait is null 
and lb.block=1 ;
-- 2.查看被锁的表
select p.spid,a.serial#,c.object_name,b.session_id,b.oracle_username,b.os_user_name 
from v$process p,v$session a, v$locked_object b,all_objects c 
where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id ; 
-- 3.查看那个用户那个进程造成死锁，锁的级别
select b.owner,b.object_name,l.session_id,l.locked_mode from v$locked_object l, dba_objects b;
-- 4.查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session; 
-- 5.查看是哪个session引起的
select b.username,b.sid,b.serial#,logon_time 
from v$locked_object a,v$session b 
where a.session_id = b.sid order by b.logon_time; 
-- 6.杀掉进程
alter system kill session 'sid,serial#';

sqlserver锁表

--查看被锁表
SELECT
request_session_id spid,  --锁表进程ID
OBJECT_NAME(
resource_associated_entity_id
) tableName                --锁表表名
FROM
sys.dm_tran_locks
WHERE
resource_type = 'OBJECT'
ORDER BY request_session_id ASC

--解锁
DECLARE
@spid INT
SET @spid = 85--锁表进程ID
DECLARE
@SQL VARCHAR (1000)
SET @SQL = 'kill ' + CAST (@spid AS VARCHAR) EXEC (@SQL)

Adopat

关注

1
点赞
踩
13

收藏

觉得还不错? 一键收藏
0
评论
Oracle常用函数

oracle常用函数时间函数获取周日时间--获取最近的周日信息select to_char(trunc(sysdate,'iw') - 1 from dual周相关函数select trunc(sysdate,'W'), --每月1日作为第一个星期第一天取当前周第一天对应日期 trunc(sysdate,'WW'), --每年1月1日作为第一个新奇第一天,取当前周对应第一天日期 trunc(sysdate,'IW'), --当前日期的周
复制链接

扫一扫