好玩的SQL

查找数据库中所有字段 以对应的表
select C.column_name,C.TABLE_NAME from dba_tab_columns C where owner=''

查每个科目class 分数scro前三名
select id, name, class, scro
from (select row_number() over(partition by class order by scro desc) cnt,
id,
name,
class,
scro
from student) a
where a.cnt <= 3;

查找排序后的前三行
select *
from (select rw.*, rownum
from (select *
from student d
where d.class = 'b'
order by d.scro desc) rw
where rw.id >= 1
order by rw.class desc) n
where rownum <= 3


表复制
insert into table_a (id,name,age) select b.id,b.name,b.age from table_b;

--删除表数据的触发器
CREATE OR REPLACE PROCEDURE delete_data
IS
BEGIN
delete from test ;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END delete_data;


--定时删除 每隔5分钟执行一次的计划
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'delete_data;'
,next_date => to_date('25/08/2008 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate+1/24/12'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;

--查看当前oracle计划
select job,next_date,next_sec,failures,broken from user_jobs;

--删除一个job
begin
dbms_job.remove(46);--46为job号
end;

--给新表插入旧表对应字段的所有数据
insert into aaa(id,name) select b.id,b.name from bbb;