CREATE OR REPLACE PROCEDURE del_hubei_ssf (

   p_count   IN   VARCHAR2             -- Commit after delete How many records

)

AS

   PRAGMA AUTONOMOUS_TRANSACTION;

   sql_stat   VARCHAR2 (1000) := '';

   n_delete   NUMBER          := 0;

BEGIN

   /** 3. delete data from the hubei  SSF **/

   DBMS_OUTPUT.put_line ('3. Start delete from the hubei  SSF!!!');

   WHILE 1 = 1

   LOOP

      EXECUTE IMMEDIATE 'DELETE /*+ RULE */ from SSF WHERE mid IN (SELECT mid FROM temp_mid_hubei) and rownum<=:rn'

                  USING p_count;

      IF SQL%NOTFOUND

      THEN

         EXIT;

      ELSE

         n_delete := n_delete + SQL%ROWCOUNT;

      END IF;

      COMMIT;

      DBMS_OUTPUT.put_line (sql_stat);

      DBMS_OUTPUT.put_line (TO_CHAR (n_delete) || ' records deleted ...');

   END LOOP;

   COMMIT;

   DBMS_OUTPUT.put_line ('Full Finished!!!');

   DBMS_OUTPUT.put_line (   'Totally '

                         || TO_CHAR (n_delete)

                         || ' records deleted from hubei_SSF  !!!'

                        );

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (SQLERRM);

END;

/