1. MERGEINTO:
MERGE INTO T_TD_BU_MODIFY_NOTICE_LS A
USING (
select MODIFY_NOTICE_ID,REJECT_REASON from T_TD_BU_MODIFY_NOTICE WHERE MODIFY_NOTICE_ID='0c3f2a97-33b8-4227-b425-32a22517f2aa' and REJECT_REASON is not null
UNION
select MODIFY_NOTICE_D_ID,REJECT_REASON from T_TD_BU_MODIFY_NOTICE_D WHERE MODIFY_NOTICE_ID='0c3f2a97-33b8-4227-b425-32a22517f2aa' and REJECT_REASON is not null
) B
ON (A.LS_ID=B.MODIFY_NOTICE_ID)
WHEN MATCHED THEN
UPDATE SET A.ATTRIBUTE1=B.REJECT_REASON,A.IS_DELETE='1'
WHEN NOT MATCHED THEN
INSERT (A.LS_ID,A.attribute1,A.IS_DELETE)VALUES(B.MODIFY_NOTICE_ID,B.REJECT_REASON,'1')
2.function:-for
CREATE OR REPLACE FUNCTION fun_getreplace(eno VARCHAR2)
RETURN VARCHAR2 --返回类型
AS
V_FILENAME VARCHAR2(200);
BEGIN
V_FILENAME:=eno;
for c in (select ATTRIBUTE1,ATTRIBUTE2 from T_EDP_DB_LOOKUP_VALUE WHERE LOOKUP_TYPE_CODE='FILE_NAME') loop
V_FILENAME:= replace(V_FILENAME,c.attribute1,c.attribute2);
end loop;
RETURN V_FILENAME;
END;
3.ROWNUM:
select'oracle获取前多少条数据'fromdualwhereROWNUM=1
注意:直接使用ROWNUM=2无效
select*FROM(select'oracle获取前多少条数据'A,ROWNUMBfromdualwhereROWNUM=1)WHEREB=2
--查看被锁的表
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
--查看那个用户那个进程照成死锁
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
--查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;
--杀掉进程 sid,serial#
alter system kill session'210,11562';(SID和serial#)
逐级遍历表中数据:
select t.org_id, t.parent_org_realation_id, level
from T_EAP_SYS_ORGANIZATION_REAL t
start with
(select org_id
from T_EAP_SYS_USER
where user_id = '3db3e055-b220-4844-87f6-ebec75b13938')=t.org_id
connect by nocycle prior t.parent_org_realation_id = t.org_id
order by level asc
--逐级遍历表中数据
select 'id','父级id',level
from dual --这里是要遍历的表
start with 'id'='父级id'
connect by nocycle prior '父级id'='id'--注意:先后顺序,这里是下一级的条件赋值
order by level asc
含义解释:decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
该函数的含义如下:IF条件=值1 THENRETURN(翻译值1)ELSIF条件=值2 THENRETURN(翻译值2)......ELSIF条件=值n THENRETURN(翻译值n)ELSERETURN(缺省值)END IF