oracle日常学习整理

检索所有Procedure里的某些关键字

select distinct name from USER_SOURCE where type = ‘PROCEDURE’ and text like ‘%lease_user_audit%’

###并行处理

select
/*+ parallel(A , 8) (B , 8) (C , 8) (D , 8) */
from A,B,C,D
where …

SELECT /*+parallel(n)*/ * FROM table

parallel：并行处理
A、B、C、D为用到的表的别名

多行字段值拼接起来

LISTAGG(目标字段,分隔符) WITHIN GROUP( ORDER BY 排序字段)
示例：
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
结果：

特殊字符表示

换行符：chr(10)

正则函数

regexp_like
regexp_replace

CLOB转字符串

使用dbms_lob.substr(clob_column)，转换为字符串

查看当前库名

select name from v$database;