oracle数据库常用的语法与复合函数

oracle查用到一些复合函数以及一些常用的方法用来快速查询数据，以下是我收集的一下常用方法，推荐给大家：

1、 当分组之后，针对某一属性值进行合并并以逗号进行分割：

（1）所有版本都可使用：

• 合并数据：使用wm_concat(column)函数进行数据合并，以分组的形式将同一人的权限进行合并展示在一个属性值中，结果为集合形式clob；
  select u_id, wmsys.wm_concat(goods) goods_sum from shopping group by u_id
• 切割数据：合并数据后将clob形式转换为字符串形式，使用dbms_lob.substr(字段，长度，起始位置)方法将clob转换为varchar2类型 ；
  select dbms_lob.substr(t.text,4000,1) from jgw_wblk t where t.createdate between to_date(‘2015-07-01’,‘yyyy-mm-dd’) and to_date(‘2015-07-31’,‘yyyy-mm-dd’)

（2）oracle11g版本之后可使用：推荐方法

• 使用listagg() within group()函数
  例：select listagg(name, ‘,’) within group( order by name) as name from user;

2、查询所在节点所有子节点信息，以树的形式呈现

• 层次化查询—oracle特有功能：
  select * from t_user start with id=xx connect by prior id=parent_id

3、指定属性在为空的情况下修改，不为空时值不变

（1）nvl函数

• 例：update tb_user set age=nvl(age,‘xxx’) where name=‘tt’;
  age为空时修改为xxx，不为空时不变

（2）使用case when …then…else…end

• 例：update ss
  set bengtime = case when bengtime is null then sysdate else bengtime end,
  endtime = case when endtime is null then sysdate else endtime end
  where id = …

（3）with … as …select 临时表

• 例：with buyer as (select m.no,count() cout from tb_buyers m group by m.no having count(m.no)>500) select s.no,b.cout from tb_ss s,buyer b where s.no=b.no and s.end_date>=to_date(‘2021-02-04 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’);
  —等价于
  select b.no,b.cout from tb_ss s,(select m.no,count() cout from tb_buyers m group by m.no having count(m.no)>500) b where b.no=s.no and s.end_date>=to_date(‘2021-02-04 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’);

4、另外，常用到日期形式的查询条件，因此涉及类型转换问题，如：

（1）Between …… and …… + 日期的date和varchar2转换

• 例：Select * from tb_user u where u.update_date between to_date(“2020-11-18 00:00:01”,”yyyy-mm-dd hh24:mi:ss”) and to_date(“2020-11-18 23:59:59”,”yyyy-mm-dd hh24:mi:ss”);
  Select * from tb_user where to_char(start_end,’yyyy-mm-dd hh24:mi:ss’)>=’2019-01-01 00:00:01’ and to_char(end_date,’yyyy-mm-dd hh24:mi:ss’) < =’2019-12-31 23:59:59’

（2）日期相减：

• 例：Select ceil(to_date(“2020-11-18 00:00:01”,”yyyy-mm-dd hh24:mi:ss”)- to_date(“2020-11-18 00:00:01”,”yyyy-mm-dd hh24:mi:ss”)) from dual;
  Select round(to_number(sysdate- to_date(“2020-11-18 00:00:01”,”yyyy-MM-dd hh24:mi:ss”))) from dual;

5、一些常用的sql语法推荐给大家

（1）解压缩表

解压缩表：alter table 表名 move nocompress;
索引失效：alter index 索引 rebuild online;

（2）常用的ddl语句

增加一个列：alter table 表名 add (字段名 数据类型); 括号可加可不加
增加多个列：alter table 表名 add (字段名1 数据类型,字段名2 数据类型,……)；
删除一列：alter table 表名 drop column 字段名;
删除多列：alter table 表名 drop column (字段名1，字段名2，……);
重新定义某列：alter table 表名 modify 字段名 数据类型;
定义表名注释：comment on table表名is “注释”
定义列名备注释：comment on column表名.列名 is “注释”

（3）序列

创建序列：create sequence seq_sys_user minvalue 1 maxvalue 999999999999999999 start with 1 increment by 1 nocache
语法：

• create sequence 序列名
  start with 2 –从2开始
  increment by 3—每次加3
  nomaxvalue—没有最大值
  minvalue 1—最小值1
  nocycle—不循环
  nocache;–不缓存 **

使用序列插入数据库表：insert into sys_user(id,user_name,password) values(seq_sys_user.nextval,1,11);
查询所有已经建好的序列：select * from user_sequences;
查询seq_sys_user 的下一个序列：select seq_sys_user.nextval from dual;
查询当前序列：select seq_sys_user.currval from dual;
删除序列：drop sequence seq_sys_user;
修改序列：alter sequence seq_sys_user increment by 79;