Oracle delete row via plsql (rowid ) ....
Create table !!!
create table cust
(cust_id number
,last_name varchar2(30)
,first_name varchar2(30)
);
insert into cust (cust_id, last_name, first_name) values(1, 'ANUJ','SINGH');
Insert
insert into cust
select level + 1
,dbms_random.string('U',dbms_random.value(3,15)) rand_last_name
,dbms_random.string('U',dbms_random.value(3,15)) rand_first_name
from dual
connect by level < 100000;
SQL>
insert into cust
select level + 1
,dbms_random.string('U',dbms_random.value(3,15)) rand_last_name
,dbms_random.string('U',dbms_random.value(3,15)) rand_first_name
from dual
connect by level < 10000000;
99999 rows created.
SQL> commit;
Commit complete.
set serveroutput on
declare
v_count number ;
x NUMBER := 0;
v_CUST CUST%ROWTYPE;
begin
for i in (select rowid, c.* from CUST C) loop
begin
dbms_output.put_line(v_count||' '||'Total No of Row Count ON Cust');
delete from CUST where rowid=i.rowid ;
x := x + 1; -- Commits after every X number of records
IF x > 1000 THEN
dbms_output.put_line(x||' '||'Deleted Row from Cust');
COMMIT;
x := 0;
END IF;
exception
when no_data_found
then
dbms_output.put_line('exception');
end;
end loop;
end;
*****************************************************************
SQL> set serveroutput on
SQL> declare
2 x NUMBER := 0;
v_CUST CUST%ROWTYPE;
3 4 begin
5 for i in (select rowid, c.* from CUST C) loop
6 begin
delete from CUST where rowid=i.rowid ;
7 8 x := x + 1; -- Commits after every X number of records
IF x > 1000 THEN
9 10 dbms_output.put_line(x||' '||'Deleted Row from Cust');
11
12 COMMIT;
13 x := 0;
14 END IF;
15 exception
when no_data_found
16 17 then
18 dbms_output.put_line('exception');
19 end;
end loop;
20 21 end;
22 /
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
1001 Deleted Row from Cust
PL/SQL procedure successfully completed.
1 comment:
set serveroutput on
declare
v_count number ;
t_count number;
D_count number;
P_count number ;
x NUMBER := 0;
begin
select count(*) into t_count from cust;
for i in (select rowid, c.* from CUST C) loop
begin
-- dbms_output.put_line(t_count||' '||'Total No of Row Count ON Cust');
delete from CUST where rowid=i.rowid ;
x := x + 1;
IF x > 1000 THEN
-- dbms_output.put_line(x||' '||'Deleted Row from Cust');
COMMIT;
x := 0;
END IF;
exception
when no_data_found
then
dbms_output.put_line('exception');
end;
end loop;
select count(*) into D_count from cust;
P_count:=t_count-D_count;
dbms_output.put_line(p_count||' '||'Deleted Row from Cust');
dbms_output.put_line(t_count||' '||'Total No of Row Count ON Cust');
commit;
end;
/
Post a Comment