Home » SQL & PL/SQL » SQL & PL/SQL » How to get count of records in a delete statement
How to get count of records in a delete statement [message #255744] Wed, 01 August 2007 11:27 Go to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi,
I am writing a PL/SQL procedure which involves a few delete statements.Each delete will work if a few conditions are satisfied. There is a cursor selecting records and in delete, the table values are also compared with the manipulation of values in the records hold by cursor.
Now I want to get a count of how many records are deleted from the table under each condition?How will I get that?
Thanks a million
Re: How to get count of records in a delete statement [message #255749 is a reply to message #255744] Wed, 01 August 2007 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Count them, use sql%rowcount, maybe other thing, who knows, you don't show us what you're doing.
Put the code but...
Read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Post your Oracle version (4 decimals).

Regards
Michel

Re: How to get count of records in a delete statement [message #255942 is a reply to message #255749] Thu, 02 August 2007 08:30 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
--Cursor definition
CURSOR c_street_hnr1
   IS
      SELECT ROWID, house_number_code, street_id, building_nr
        FROM alpha_building_no
       WHERE (building_nr LIKE '%#_%' ESCAPE '#')
          OR (building_nr LIKE '%/%')
          OR (building_nr LIKE '%-%');
OPEN c_street_hnr1;
   LOOP
      FETCH c_street_hnr1
       INTO v_str_rowid, v_hnr_sortcode, v_street_id, v_building_nr;
EXIT WHEN c_street_hnr1%NOTFOUND;
--Other codes
IF v_num_seq = '-'
      THEN
         IF INSTR (var_num_low, '$') = 0 AND INSTR (var_num_high, '$') = 0
         THEN
            DELETE FROM alpha_building_no
                  WHERE house_number_code = v_hnr_sortcode
                    AND street_id = v_street_id
                    AND DECODE
                           (INSTR
                               (TRANSLATE
                                   (Trim_Building_Nr_Parts.trim_building_nr_left
                                                                  (building_nr),
                                    'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
                                    '$$$$$$$$$$$$$$$$$$$$$$$$$$'
                                   ),
                                '$'
                               ),
                            0, NVL
                               (TO_NUMBER
                                   (Trim_Building_Nr_Parts.trim_building_nr_left
                                                                  (building_nr)
                                   ),
                                0
                               ),
                            -1
                           ) BETWEEN v_num_low_out AND v_num_high_out
                    AND DECODE
                           (INSTR
                               (TRANSLATE
                                   (Trim_Building_Nr_Parts.trim_building_nr_right
                                                                  (building_nr),
                                    'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
                                    '$$$$$$$$$$$$$$$$$$$$$$$$$$'
                                   ),
                                '$'
                               ),
                            0, NVL
                               (TO_NUMBER
                                   (Trim_Building_Nr_Parts.trim_building_nr_right
                                                                  (building_nr)
                                   ),
                                0
                               ),
                            -1
                           ) BETWEEN v_num_low_out AND v_num_high_out
                    AND ROWID <> v_str_rowid;
--rest of the code for other,conditions closing if,closing cursor etc comes here


There is only 1 delete statement shown here.Similarly there are a number of deletes.I want the total count for all the deletes inside the cursor and be able to write into a table how many deletes happened altogether.Just show me for this one and then I should be able to do it for the rest.

[EDITED by LF - modified [code] tags]

[Updated on: Thu, 02 August 2007 14:53] by Moderator

Report message to a moderator

Re: How to get count of records in a delete statement [message #255947 is a reply to message #255942] Thu, 02 August 2007 09:11 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
code tags must be between [] and not <>.

You can use sql%rowcount after each delete and add to a counter.

Regards
Michel
Previous Topic: How to find records with CR within VARCHAR2
Next Topic: Displaying a Word/Excel document on the Web
Goto Forum:
  


Current Time: Sat Dec 03 13:44:33 CST 2016

Total time taken to generate the page: 0.10820 seconds