sql 语句积累

--删除前10条语句
delete actionlog where rowid in (
select rowid from ( select * from actionlog order by actiondate desc) --where rownum<=10
)
--时间

select sysdate,sysdate +1,sysdate + 1/24,sysdate + 1/24/60
from dual;
---创建查询job----
declare job number;
begin
sys.dbms_job.submit(job,‘Pro_Check_LockUser;‘,sysdate,‘sysdate+10/1440‘);
end;

select * from dba_jobs_running

select * from user_jobs
或者
select * from dba_jobs

---递归
select * from daybuyplan WHERE CONNECT_BY_ISLEAF=1　connect by prior planno= newplanno　start with planno= ‘DBP1608180560‘ 　－－儿子节点
select * from daybuyplan start with planno=‘’ connect by prior newplanno=planno
select * from daybuyplan where newplanno is null connect by prior newplanno = planno start with planno= b.addition1
----合并行、列
select listagg((a.name || ‘,‘|| a.job || ‘,‘|| a.telephone || ‘,‘|| a.mobilephone || ‘,‘|| a.email),‘;‘)
within group (order by (a.name || ‘,‘|| a.job || ‘,‘|| a.telephone || ‘,‘|| a.mobilephone || ‘,‘|| a.email)) userinfo
from vendorcontact a where a.tempvendorcode=‘V20160201152258869‘

--游标

select count(*) from v$open_cursor;

select * from v$open_cursor
alter system set open_cursors=1000;

select * from v$parameter vp where vp.name = ‘open_cursors‘;

sql 语句积累

原文：http://www.cnblogs.com/wjh1285600586/p/6693832.html