sql函数使用

积累工作中发现的一下比较好用的函数：

1 ：coalesce 类似与 nvl，不同的是 coalesce 可以有多个参数。例如：select coalesce (col1,col2,col3,'A') from temp; 从左到右会返回第一个不为空的字段，如果都为空返回'A'。注意字段类型必须是一样的。

2：merge into table1 a using  (select  col1,col2 from table2 where ..) b on ( 匹配条件)  

when matched then 

   update 执行语句  

   delete 执行语句 

   where 条件   

when not matched then 

   inser 执行语句 ；

可以减少查询表的次数。

3：分析函数over () ，over (partition by col order by) a 。

4：根据表名模糊查询表：

select table_name,tablespace_name,temporary from user_tables  where table_name like '%tb_name%'  ;

通过字段查询表: select table_name from user_tab_columns where column_name = 'NAME'

通过索引名查询表名和相应的字段名: select index_name,table_name,column_name from dba_ind_columns where index_name='PK_EMP';

根据字段名查询表和对应字段: select owner, table_name, COLUMN_NAME   from dba_tab_cols  where column_name like '%_ORG_PATH'     or column_name like '%_PATH'    and owner = 'ECIQ_OPERATION';

oracle查询用户下的所有表:

select * from all_tab_comments -- 查询所有用户的表,视图等

select * from user_tab_comments   -- 查询本用户的表,视图等

select * from all_col_comments --查询所有用户的表的列名和注释.

select * from user_col_comments -- 查询本用户的表的列名和注释

select * from all_tab_columns --查询所有用户的表的列名等信息(详细但是没有备注).

select * from user_tab_columns --查询本用户的表的列名等信息(详细但是没有备注).

5：性能查询：

SELECT sql_text  FROM v$sqltext a WHERE (a.hash_value, a.address) IN

    (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),

    DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)

    FROM v$session b WHERE b.paddr =  (SELECT addr FROM v$process c WHERE c.spid = '21340'))  ORDER BY piece ASC;

6：查看锁表数据：

select t2.username, t2.sid, t2.serial#, t3.object_name, t2.OSUSER, t2.MACHINE, t2.PROGRAM, t2.LOGON_TIME, t2.COMMAND, t2.LOCKWAIT, t2.SADDR, t2.PADDR, t2.TADDR, t2.SQL_ADDRESS, t1.LOCKED_MODE from v$locked_object t1, v$session t2, dba_objects t3 where t1.session_id = t2.sid and t1.object_id = t3.object_id order by t2.logon_time;

7：查看表数据快照信息可用于恢复误删除的数据

select * from aaa as of timestamp sysdate - 0.1

8：查看sql监控

--查询SQL绑定变量

select sm.status,

sm.username,

sm.sql_id,

sm.sql_text,

sm.sql_exec_start,

sm.sid,

se.SERIAL#,

trunc(sm.elapsed_time / 1000 / 1000, 2) as elapsed_sec,

(select listagg(bvalue, ' | ') within group(order by bpos)

from xmltable('/binds/bind' passing

xmltype(nvl(sm.binds_xml, '<T/>')) columns bpos

varchar2(9) path '/bind/@pos',

bvalue varchar2(90) path '/bind')) binds

from gv$sql_monitor sm, gv$session se

where sm.SID = se.SID

and sm.status = 'EXECUTING'

order by sm.SQL_EXEC_START;

--查看sql执行情况的试图，可用于排查特别消耗资源的sql语句

select * from v$sql;