if the statements are inside the stored procedure together, they will be processed simultaneously and because the tables are related that way it will cause a problem.
No, the statements in a stored procedure are processed sequentially, not in parallel. A stored procedure executes in a single thread, just as if you had issued the queries one after another... not simultaneously.
There are some optimizations you could make, however.
If the `titnum` table doesn't have a multi-column index on the three columns (titvalpag, titemi, titnum) together, you should probably add one.
The delete statements can be rewritten without the IN().
Use caution with this, because doing it incorrectly will remove the wrong data.
delete from mov_pedido
where pednot in(select distinct titnum
from mov_titulo
where titnum like 'A4%'
and titemi <='2012-01-01' and titvalpag >0);
...becomes...
DELETE p
FROM mov_pedido p
JOIN mov_titulo m ON m.titnum = p.pednot
WHERE m.titnum like 'A4%' and m.titemi <='2012-01-01' and m.titvalpag >0;
This is the multi-table delete syntax. "DELETE p" will only remove the rows from "p" (an alias for mov_pedido) that match rows in "m" (mov_titulo). It won't remove the rows from "m" unless you say "DELETE p, m" -- so don't do that.
The mov_pedido table (and the others) should have an index on the column you're using to join (pednot, etc.) if they don't already.
If you want to create a user that can admin any of the pluggable databases, prefix the username with C##.
For example:
create user c##name identified by password;
Best Answer
You can create a column for update and/or delete status for the records. Do update instead of delete and assign a value that specifies this process. Assign another value for update process so that you can track the record's status.
When fetching you should have a where condition that returns records which have neither of these assigned values. After fetching these 200 records, you can do your delete or update process. I wouldn't use paging for this requirement, but if you want, just add your condition to the below paging
Sample paging: