oracle

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;