select * from table where (orderid,itemid) in (('',''),('',''))
--批量提交
declare
-- 数组变量,保存查询条件
TYPE t_startmobileno IS TABLE OF mobilephonesegment.startmobileno%TYPE;
TYPE t_endmobileno IS TABLE OF mobilephonesegment.endmobileno%TYPE;
TYPE t_mobiletype IS TABLE OF mobilephonesegment.mobiletype%TYPE;
TYPE t_bosstype IS TABLE OF mobilephonesegment.bosstype%TYPE;
TYPE t_provincecode IS TABLE OF mobilephonesegment.provincecode%TYPE;
TYPE t_citycode IS TABLE OF mobilephonesegment.citycode%TYPE;
TYPE t_provincename IS TABLE OF mobilephonesegment.provincename%TYPE;
TYPE t_cityname IS TABLE OF mobilephonesegment.cityname%TYPE;
v_t_startmobileno t_startmobileno;
v_t_endmobileno t_endmobileno;
v_t_mobiletype t_mobiletype;
v_t_bosstype t_bosstype;
v_t_provincecode t_provincecode;
v_t_citycode t_citycode;
v_t_provincename t_provincename;
v_t_cityname t_cityname;
CURSOR c IS
SELECT * FROM mobilephonesegment;
BEGIN
OPEN c;
LOOP
-- 批量更新,一次更新10000条数据
fetch c bulk collect
into v_t_startmobileno,v_t_endmobileno,v_t_mobiletype,v_t_bosstype,v_t_provincecode,v_t_citycode,v_t_provincename,v_t_cityname LIMIT 10000;
-- 这里用forall效率更高
FORALL i IN 1 .. v_t_startmobileno.COUNT
insert into mobilephonesegment_bak
(STARTMOBILENO,
endmobileno,
mobiletype,
bosstype,
provincecode,
citycode,
provincename,
cityname)
values
(v_t_startmobileno(i),
v_t_endmobileno(i),
v_t_mobiletype(i),
v_t_bosstype(i),
v_t_provincecode(i),
v_t_citycode(i),
v_t_provincename(i),
v_t_cityname(i));
-- 提交
COMMIT;
-- 循环退出
exit when c%NOTFOUND;
END LOOP;
COMMIT;
end;
查锁
SELECT lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#,
'alter system kill session ''' || s.SID || ',' || s.SERIAL# || ''';'
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY o.object_id, xidusn DESC
查询存储过程命令
SELECT TEXT FROM USER_SOURCE WHERE NAME = upper('proc_bus_monthly_rent_stat') AND TYPE = 'PROCEDURE' ORDER BY LINE;