create or replace procedure delete_tb_proc is v_cnt number(18,0); begin select count(*) into v_cnt from table_name; if v_cnt > 500000 then delete from table_name a where rowid in (select rowid from table_name where rowid<=300000); commit; end if; end; /
-- 修正一下:create or replace procedure delete_tb_proc is v_cnt number(18,0); begin select count(*) into v_cnt from emp; if v_cnt > 10 then delete from emp a where rownum in (select rownum from emp where rownum<=5); commit; end if; end; /
-- 再次修正一下:、create or replace procedure delete_tb_proc is v_cnt number(18,0); begin select count(*) into v_cnt from emp; if v_cnt > 10 then delete from emp a where rowid in (select rowid from emp where rownum<=5); commit; end if; end; /
-- 这就是楼主想要的代码啦:create or replace procedure delete_tb_proc is v_cnt number(18,0); begin select count(*) into v_cnt from table_name; if v_cnt > 500000 then delete from table_name a where rowid in (select rowid from table_name where rownum<=300000); commit; end if; end; /
你表这么大,一次删除这么多条数据,回滚段压力很大的,删除肯定很慢。 建议: 1:批量删除,每删除5万左右提交。 2:新建表create table a as select * from b where rownum<=$num(删除顺序是什么,随意删除?) drop table b; rename a to b;
Mysql中怎样写,暂不考虑性能问题,删除的是先前插入的数据
SQL> SQL> create or replace procedure yes270453910(drow in number) is 2 v_sql varchar2(100); 3 v_rowid varchar2(40); 4 begin 5 select nvl(rowid, '0') 6 into v_rowid 7 from (select dbms_rowid.rowid_row_number(rowid) rn, rowid from a) 8 where rn = drow; 9 v_sql := 'create table tmp_a as select * from a where rowid>=''' || v_rowid||''''; 10 execute immediate v_sql; 11 execute immediate 'truncate table a'; 12 execute immediate 'insert into a select * from tmp_a'; 13 execute immediate 'drop table tmp_a'; 14 commit; 15 end; 16 /Procedure createdSQL> SQL> select * from a; ID ---------- 4 5 6SQL> drop table tmp_a;Table droppedSQL> execute yes270453910(1);PL/SQL procedure successfully completedSQL> select * from a; ID ---------- 5 6SQL> execute yes270453910(1);PL/SQL procedure successfully completedSQL> select * from a; ID ---------- 6SQL>
-- 倒腾了半天,又说是My SQL的,真郁闷:自己慢慢去改吧!
各位大哥,能写一个Mysql的不?
luoyoumou 很是感激你,你在想想吧!
SELECT TOP 10 * FROM (SELECT TOP 20 * FROM g_sys_unitinfo ORDER BY cid) ORDER BY cid DESC 根据这个sql自己去写个存储过程吧
v_cnt number(18,0);
begin
select count(*) into v_cnt from table_name;
if v_cnt > 500000 then
delete from table_name a
where rowid in (select rowid from table_name where rowid<=300000);
commit;
end if;
end;
/
v_cnt number(18,0);
begin
select count(*) into v_cnt from emp;
if v_cnt > 10 then
delete from emp a
where rownum in (select rownum from emp where rownum<=5);
commit;
end if;
end;
/
v_cnt number(18,0);
begin
select count(*) into v_cnt from emp;
if v_cnt > 10 then
delete from emp a
where rowid in (select rowid from emp where rownum<=5);
commit;
end if;
end;
/
v_cnt number(18,0);
begin
select count(*) into v_cnt from table_name;
if v_cnt > 500000 then
delete from table_name a
where rowid in (select rowid from table_name where rownum<=300000);
commit;
end if;
end;
/
建议:
1:批量删除,每删除5万左右提交。
2:新建表create table a as select * from b where rownum<=$num(删除顺序是什么,随意删除?)
drop table b;
rename a to b;
SQL>
SQL> create or replace procedure yes270453910(drow in number) is
2 v_sql varchar2(100);
3 v_rowid varchar2(40);
4 begin
5 select nvl(rowid, '0')
6 into v_rowid
7 from (select dbms_rowid.rowid_row_number(rowid) rn, rowid from a)
8 where rn = drow;
9 v_sql := 'create table tmp_a as select * from a where rowid>=''' || v_rowid||'''';
10 execute immediate v_sql;
11 execute immediate 'truncate table a';
12 execute immediate 'insert into a select * from tmp_a';
13 execute immediate 'drop table tmp_a';
14 commit;
15 end;
16 /Procedure createdSQL>
SQL> select * from a; ID
----------
4
5
6SQL> drop table tmp_a;Table droppedSQL> execute yes270453910(1);PL/SQL procedure successfully completedSQL> select * from a; ID
----------
5
6SQL> execute yes270453910(1);PL/SQL procedure successfully completedSQL> select * from a; ID
----------
6SQL>