ORALCE常用语法及函数

下划线即代表是方法参数

[ 中括号内的是可选的方法参数 ]

\ 转义字符

对于特殊字符需要搭配转义字符来使用

null 和 空字符''

oracle 中 null 大多时候等于空字符，但是 null 可以是任何类型，而空字符的类型是varchar2

TRANSLATE（expr，from，to）字符串替换

对 expr 根据 from 和 to 以字符为单位一一对应实现字符串的替换。

select TRANSLATE（'abcd'，'ab '，'cd'）

=> cdcd

当 from 对应的 to 位置没有值时，该值会被用null替换（删除）

select TRANSLATE（'abcd'，'abc '，' a'）

=> ad

当 to 参数是空字符串时，返回null

select TRANSLATE（'abcd'，'ab '，' '）

=> null

ORDER BY condition [ASC|DESC] 排序

排序，可选参数 ASC 代表升序排序，DESC 代表降序排序, 不使用时默认升序

select name,age from student order by name; -- 按姓名排序
select name,age from student order by 1; -- 按姓名排序
select name,age from student order by age，name; -- 先按年龄，再按姓名排序
select name,age from student order by 1 nulls first; -- 按姓名排序，且空值优先
select name,age from student order by 1 nulls last; -- 按姓名排序，且空值置后

有时排序的条件很苛刻，可以先将条件查询出来。

-- 按姓名的第一个字符排序
select name,SUBSTR(name,1,1) as first order by first; 
-- (10,20]岁的优先排序
select name,
    	 age,
       case when age > 10 and age <= 20 then 1 else 2 end as condition
order by condition,age
	-- 也可以将条件直接写在order by 后
select name,
    	 age
order by case when age > 10 and age <= 20 then 1 else 2 end,
				 age;

ROWNUM 限制数据数

每一列的序号，用于对返回的行数进行限制 约等于 mysql的limit

select name,age from student where rownum <= 2; - 取出两条数据

rouwnum的序号是从1开始逐条生成的，所以想要取到第二行即rownum=2的数据，需要嵌套一层查询

select * from
	(select name,age,rownum from student where rownum <= 2)
where rownum = 2; - 取出第二条数据

select * from
	(select name,age,rownum from student where rownum <= 2)
where rownum > 2; - 取出第二条数据

dbms_random 随机抽查

通过dbms_random.value() 生成随机数来实现随机查询

select * from
	(select name,age,rownum from student order by dbms_random.value())
where rownum > 2; 

其中要嵌套一层后再分页的原因是

一条普通的select 语句中，各部分的执行顺序是这样的

1. select
2. rownum
3. order by

不嵌套一层会导致先取出数据后以后再随机排序，导致随机失败。

union，union all 组合结果类型相同的两条查询

or 条件的语句，用union 改写后能够同时利用表中的多个单个索引，而非联合索引

-- 仅走 name+age 的联合索引
select name，age from student where name = '张三' or age = '18'
-- 走 name 索引 和 age 索引
select name，age from student where name = '张三' 
union
select name，age from student where age = '18'

union具有去重功能，不仅两条查询语句中重复的部分会被去除，原查询中相同的数据也会被去除造成错误，当然，如果你查询的列中含有主键，那么就不会有这个问题。

或者可以用rowid来伪造一个主键。

with dbname as sql 创建临时视图

临时创建一个在查询期间存在的视图，查询结束后消失。

with newview as (select rownum,name,age from stundent)
select name
from newview
where rownum <= 2

full join 检测两个表中的数据及对应数据的条数是否相同

-- 检测两个表中的数据是否相同，不相同的数据
select 1.name,2.name from student1 1 full join on student2 2
-- 如果2表中有重复数据，查询时需要加上count来作为左右不同的标识
select 1.name,count(1.*) as cnt1,2.name,count(2.*) as cnt2
from student1 1 full join on student2 2
group by 1.name,2.name

NVL(),NVL2

DECODE（value，test1，value1，...，else） oracle中的if函数

value代表填入的值，当值等于test1，返回value1，以此类推，否则返回else

-- 如果sex等于girl会变girl，否则变成男
select DECODE(sex,'girl','女','男') as sex from student

SIGN(number) 判断值为正负0

sign会根据参数值为负、0、正，分别返回-1、0、1

select sign(monkey) from student
-- 也可以结合decode使用,比如monkey不允许为负，至少为0时
select decode(sign(monkey),-1,0) as monkey from student

TRUNC(date, [fmt]) 便捷截断日期

date 是要截断的日期时间值，fmt 是可选参数，表示要截断到的精确度。如果省略 fmt 参数，则默认截断为日期格式（即只保留年月日部分）。

fmt 参数支持以下取值：

• 'YYYY'：截断到年份。
• 'MM'：截断到月份。
• 'DD'：截断到天。
• 'HH24' 或 'HH'：截断到小时。
• 'MI'：截断到分钟。
• 'SS'：截断到秒。

-- 查询当前时间
SELECT SYSDATE FROM dual;
-- 仅查询当前日期
SELECT TRUNC(SYSDATE) FROM dual;
-- 查询当前年的第一天==仅查询当前年份，但是返回不是字符串而是时间格式，所以月日填充为01-01
SELECT TRUNC(SYSDATE,'YYYY') FROM dual;

TO_DATE(string, format)

用于将字符串转换为日期格式，表示输入字符串对应的日期。一般在向日期格式的字段插入时使用。

string是要转换为日期的字符串，format是指定该字符串的日期格式的模式。

-- 日期格式
insert into student(brithday) VALUES(TO_DATE('2023-04-23', 'YYYY-MM-DD'))
-- 日期24时间格式
insert into student(brithday)
VALUES(TO_DATE('2021/09/01 12:30:45', 'YYYY-MM-DD HH24:MI:SS'))

TO_CHAR(date, format)

用于将日期时间值转换为字符串。可以通过指定格式模板来定义输出的字符串格式。一般从日期格式的字段取值时使用。

还可以将数字值、字符值等转换为不同格式的字符串。

date是要转换为字符串的日期，format是指定该日期的字符串格式的模式。

-- 日期格式
insert into student(brithday) VALUES(TO_CHAR('2023/04/23', 'YYYY-MM-DD')) -- 2023-04-23
-- 日期24时间格式
insert into student(brithday)
VALUES(TO_DATE('2021/09/01 12-30-45', 'YYYY/MM/DD HH24:MI:SS')) -- 2021/09/01 12:30:45

TO_NUMBER(num, [format], [nlsparam])

• string：要转换为数值类型的字符串。
• format：可选参数，用于指定字符串的格式。格式字符串可以包含数字、小数点、千分位分隔符等字符。例如，'9999.99' 表示最多包含 4 位整数和 2 位小数的数字。如果省略此参数，则默认使用当前会话所设置的 NLS 数字格式。
• nlsparam：可选参数，用于指定语言环境参数。它包含一个或多个 NLS 初始化参数，如果省略此参数，则默认使用当前会话所设置的 NLS 参数。一般用不上。

SELECT TO_NUMBER('123.45') FROM DUAL;-- Output: 123.45
-- 截断为两位小数，且会四舍五入
SELECT TO_NUMBER('123.4567', '9999.99') FROM DUAL;-- Output: 123.46

LISTAGG(colum, str) within group(order by colum) 聚合拼接多条数据

COUNT(colum)

count(*)最快，count(最后列)最慢

业务场景的使用的方法

SELECT COUNT(*) FROM TMP_TEST_COUNT WHERE ROWNUM = 1;

参考:

http://t.csdn.cn/vWIBb

http://t.csdn.cn/ky0rV

ROUND(colum,num) 限定位数

联表时取表第一条

left join (
  select *
  from (
    select a.MEMOCK,a.SALESHTNO,
    row_number() over(partition by a.SHEETNO order by SHEETDT desc) as rown
    from orsrtb10 a
  ) a
  where rown = 1
) b on  (a.SHEETNO = b.SALESHTNO)

表备份

create table ORCGTB12_DETAIL as select * from orcgtb12_detail_backup;

获取表对应的触发器

select trigger_name from all_triggers where table_name='ORSRTB16_JL_IMPORT';

select text from all_source where type='TRIGGER' AND name='TRI_ORSRTB16_JL_IMPORT';

LEAD(colum, num, default) over (order by colum)/LAG() 查询结果偏移

select t.id id, 
       --当前数据的下一行
       lead(t.id, 1, null) over(partition by cphm order by t.id) next_same_cphm_id,
       --当前数据的上一行
       lag(t.id, 1, null) over(partition by cphm order by t.id) next_same_cphm_id,
       t.cphm
from tb_test t
     order by t.id asc