Oracle 小技巧

来源：互联网发布：企业级网管软件编辑：程序博客网时间：2024/06/05 19:06

1. 每天的8：00到23：00每隔5分钟执行一个sql语句的JOB

--建立一个存储过程

CREATE OR REPLACE PROCEDURE p_jobtest IS

v_hh VARCHAR2(2);

BEGIN

v_hh := to_char(SYSDATE, 'hh24');

IF v_hh >= '08' AND v_hh <= '22' THEN

--你的sql语句

NULL;

END IF;

END;

--提交一个JOB

DECLARE

v_jobno NUMBER;

BEGIN

dbms_job.submit(v_jobno,

'p_jobtest;',

trunc(SYSDATE, 'mi') + 1 / 1440,

'trunc(SYSDATE, ''mi'') + 5 / 1440');

END;

2. 建表前判断表是否存在的存储过程。

在Oracle 中没有drop table... if exists语法。所以我们可以在创建表之前用如下存储过程来判断。

create or replace procedure proc_dropifexists(

p_table in varchar2

) is

v_count number(10);

begin

select count(*)

into v_count

from user_objects

where object_name = upper(p_table);

if v_count > 0 then

execute immediate 'drop table ' || p_table ||' purge';

end if;

end;

3. Oracle利用现有的表创建一张新表，只要表结构相同
   create table david as select * from all_users where 1<>1;

4. 循环插入数据
declare i integer;
     begin
         for i in 1..100000 loop
         insert into test values(i);
    end loop;
    commit;
  end;

5. 有没有被lock，可以通过这2张dynamic view来确定：
v$locked_object,V$session

可以把该 session杀掉。
select sid,serial# from v$session where username ='XXXX'
把得到的sid，serial#号替换到下面的语句中：
alter system kill session 'SID,SERIAL#'

6. 查看表上是否存在的索引
select * from user_indexes where table_name = 'yourtablename'
create index IX_Tablename_column on tablename(column)

7.   查询表的行数
    select count(*) from table_name;  全表扫描，会自已找表有索引列并且该列为非空的(因为只有非空才能确保记录数是全的),走INDEX_FFS.
    select count(1) from table_name; 不走索引，效率要高，但在表中有非空索引时也是走 INDEX_FFS 的

8. oracle update 多表关联
UPDATE a
   SET (ID, NAME) = (SELECT b.ID, b.NAME
                       FROM b
                      WHERE a.ID = b.ID)
WHERE EXISTS (SELECT 1
                 FROM b
                WHERE a.ID = b.ID)

9. 分页一般用到两种办法：
1，利用rownum
2，分析函数row_number()over()
1.
select from(
   select t.*,rownum rn from(
     select * from a
     order by col1)t
   where rn between 101 and 200)
2.
   select *
   from(select t.*,row_number()over(order by col1)rn
     from t)
   where rn between 101 and 200

10. 重复数据只显示一条：
select min(id) id,b,c from tb group by b,c

11. 删除重复数据：
delete from tb where rowid not in (select min(rowid) from tb group by b,c);

12. oracle 会将SQL语句中 in 后面的东西生成一张内存中的临时表。然后进行查询。所以在相关字段上见索引比较重要。

0 0