[Oracle]~常用

Oracle常用语句

---

• 数据库查询

查询数据库版本

select * from v$version

查询数据库编码集

select userenv('language') from dual;

---

• 字段处理

首字母处理(下划线转驼峰)

--首字母大写
select REPLACE(INITCAP('first_second_three'),'_','') from dual;
 
--首字母小写
select lower(substr('first_second_three',1,1)) || substr(REPLACE(INITCAP('first_second_three'),'_',''),2) from dual;

分组拼接字符串

行转列函数Listagg()
LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)

--通过studentid分组，按照orderkey排序，拼接subject字段，拼接符为，
select studentid, listagg(subject, ',') within group(order by orderkey)
from student
group by studentid

---

• 问题处理

数字字段拼接小数点前0消失问题

    to_char(字段,'fm9999999999999990.00')
    select to_char(0.12,'fm9999990.9999') ||'%' from dual;