PL/SQL delete set of values

PetrM

Hi, could you help?

I have never written procedures in PL/SQL and I do not know how to do it:

I need to write a PL/SQL procedure that will be called for a set of values (for example X) and will end depending on the final condition.

Something like that:

An input set of values:  X = (1,2,3,4,5)

CREATE PROCEDURE DELETE_VALUES.

BEGIN

Steps in procedure:

1. COUNT (*) FROM Table_1 WHERE index IN X  - (A)

2. COUNT (*) FROM Table_2 WHERE index IN X  - (B)

3. DELETE from Table_1 WHERE index IN X  - (C)

4. DELETE from Table_2 WHERE index IN X  - (D)

5. IF  (A) = (C) AND (B) = (D) COMMIT    (if not then ROLLBACK)

Could u please help me?

Thank you in advance

jaramill

Appreciate that you state up front that you do NOT know PL/SQL.  There are many places to learn.

1. For beginners to learn https://www.oracle.com/database/technologies/appdev/plsql.html
2. The PL/SQL Reference Documentation --> https://docs.oracle.com/database/121/LNPLS/toc.htm
3. Also here's a link in general on -->

With that said, is this request for a Homework assignment?  I assume that because if it is for work, then they should hire someone who DOES know PL/SQL

mathguy

What do you mean by (A) = (C)? Perhaps (A) is the count of rows where index in X; but what number is (C)?   (Note: "index" is a terrible name for a column!)

Frank Kulash

Hi,

PetrM wrote:Hi, could you help?I have never written procedures in PL/SQL and I do not know how to do it:I need to write a PL/SQL procedure that will be called for a set of values (for example X) and will end depending on the final condition.Something like that:An input set of values: X = (1,2,3,4,5)CREATE PROCEDURE DELETE_VALUES.BEGIN Steps in procedure:1. COUNT (*) FROM Table_1 WHERE index IN X - (A)
2. COUNT (*) FROM Table_2 WHERE index IN X - (B)3. DELETE from Table_1 WHERE index IN X - (C)4. DELETE from Table_2 WHERE index IN X - (D)5. IF (A) = (C) AND (B) = (D) COMMIT (if not then ROLLBACK)Could u please help me?Thank you in advance

Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.
If you're asking about a DML statement, such as DELETE, then the CREATE TABLE and INSERT statements you post should re-create the tables as they are before the DML, and the results  will be the contents of the changed table(s) when everything is finished.

Always say which version of Oracle you're using (for example, 12.2.0.1.0).
See the Forum FAQ:

What are A, B, C and D above?  Are they parameters to a stored procedure?

Before writing any SELECT or DELETE statements in PL/SQL, you need to write and test corresponding SELECT and DELETE statements in SQL.  Start there.  Show how you would do what you need in pure SQL, and how you would decide what to do next after each step.  Then we can work on a procedure to do it all in PL/SQL.  If you need help with any of the SQL steps, ask a specific question.

jaramill

mathguy wrote:What do you mean by (A) = (C)? Perhaps (A) is the count of rows where index in X; but what number is (C)? (Note: "index" is a terrible name for a column!)

LOL...it's why I created a table with a column called "index_col".  OP says he knows nothing of PL/SQL so I cut him/her some slack on using an Oracle reserved word as a table column name.

jaramill

Are you passing in a "string of values"?  Or are you passing in 4 separate values?  If the former then that would involve a little bit more logic.

mathguy

I am trying to understand the logic here.

You are deleting some rows in steps 3 and 4. Why would you NOT commit in all cases?

You are comparing the number of deleted rows (I am guessing - waiting for you to confirm) against the counts obtained in steps 1 and 2. Is that it?

If so, then the thinking is fatally flawed.

If everything was done at the same time, there is no reason why the number of rows deleted from each table would not equal the counts from steps 1 and 2. However, note that the moment in time captured by the COUNT(*) statements is strictly earlier (even if by a very small amount) than the moment of the DELETE operations. Is that the whole point of the exercise, to make sure that the data hasn't changed between the moments of step 1 and step 3 (and similarly steps 2 and 4)? If so, why do you care about the counts in steps 1 and 2 at all? What is special about the moment those counts were taken (which is NOT the moment of performing the DELETE operations)?

Suppose somehow the time of the COUNT(*) steps is important (relevant to your organization in some way). Well, the count may remain the same by the time you perform DELETE, even though the data HAS changed! Indeed, it's possible that an UPDATE statement in the meantime changed a value from 1 to 8 and another value from 9 to 3. So the COUNT of rows with values in X has not changed, but WHICH those rows are HAS changed!  It would be exceptionally odd if you only cared that the right NUMBER of rows were deleted, even if they weren't the right ROWS.

If you really need to freeze things until you are done deleting, you should lock the tables (rather than just the rows affected by DELETE), and then COMMIT in all cases. Not a common occurrence, but if you really need the data to stay put, that's how you do it, not with the logic you are trying to implement.

Saubhik

PetrM wrote:Hi, could you help?I have never written procedures in PL/SQL and I do not know how to do it:I need to write a PL/SQL procedure that will be called for a set of values (for example X) and will end depending on the final condition.Something like that:An input set of values: X = (1,2,3,4,5)CREATE PROCEDURE DELETE_VALUES.BEGIN Steps in procedure:1. COUNT (*) FROM Table_1 WHERE index IN X - (A)
2. COUNT (*) FROM Table_2 WHERE index IN X - (B)3. DELETE from Table_1 WHERE index IN X - (C)4. DELETE from Table_2 WHERE index IN X - (D)5. IF (A) = (C) AND (B) = (D) COMMIT (if not then ROLLBACK)Could u please help me?Thank you in advance

YES.. I can help you with this link, this is not a free code writing service, write your own code and come back with specific problems.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/index.html

Billy Verreynne

Steps 1, 2 and 5, are totally idiotic. Nonsensical. Illogical. Stupid. Orange tinged.

This is NOT how one deals with transaction processing in a multi-user and multi-process environment.

Please convey this to whoever gave you these silly steps to code.

BEDE

So, you mean something that in pl/sql would be written:

declare

  a number;

  b number;

  c number;

  d number;

begin

  select COUNT (*) into a

  FROM Table_1 WHERE col_index in (1.2,3,4);

   select count(*) into B

  COUNT (*) FROM Table_2 WHERE col_index in (1.2,3,4);

  DELETE from Table_1 WHERE col_index in (1.2,3,4);

  c:=sql%rowcount;

  DELETE from Table_2 WHERE col_index in (1.2,3,4);

  d:=sql%rowcount;

  if a=c and b=2 then

    commit;

  else

    rollback;

  end if;

end;

This should run... But may well lead to a deadlock if there are many users running transactions that affect table_1 and/or table_2.

So, if there are several users entering transactions on table_1, until the first select count(*) from Table_1 WHERE col_index in (1.2,3,4) executes, one o several transactions will commit, so that, the very next second the same select will return a different number of rows, and, of course, the delete will delete a different number of rows unless some rows are locked by other database sessions and you get a deadlock.

What are you actually trying to do? What are your goals?

In my mind it just makes no sense to select a count for a given filter condition and then delete the rows that meet that condition. If you just want to delete and know how many rows you have deleted you have the sql%rowcount that you shoud store in a variable immediately after the delete for later use.

Or is it just a dumb school assignment? For, anyway, to me it doesn't seem like a real life problem.

Cookiemonster76

That code by itself will never cause deadlocks.

If there's another piece of code that locks table2 and then table1 then the two bits of code running together can cause deadlocks.

Also, removing the pointless selects and IF will have zero effect on what does and doesn't get locked so I'm not sure why locking is getting mentioned at all.