批量删除Orcale数据库用户表

SET SERVEROUTPUT ON


DECLARE
  -- step 1: declare the variables
  v_product_name varchar(100);
  v_product_count integer:=0;

  -- step 2: declare the cursor
  CURSOR cv_product_cursor IS
    SELECT  table_name
    FROM user_tables
    where table_name like '_/_%' escape'/';
BEGIN

  -- step 3: open the cursor
  OPEN cv_product_cursor;
 
  --display devision
  DBMS_OUTPUT.PUT_LINE('------------------------------BEGIN------------------------------------');

  LOOP

    -- step 4: fetch the rows from the cursor
    FETCH cv_product_cursor
    INTO v_product_name;

    -- exit the loop when there are no more rows, as indicated by
    -- the Boolean variable cv_product_cursor%NOTFOUND (= true when
    -- there are no more rows)
    EXIT WHEN cv_product_cursor%NOTFOUND;

    -- drop table
    execute immediate 'drop table ' || v_product_name;   

    --set the number of dropped tables
    v_product_count :=v_product_count +1;
    
    --use DBMS_OUTPUT.PUT_LINE() to display execute result    
    DBMS_OUTPUT.PUT_LINE(v_product_count ||' : '||
      'dorp table ' || v_product_name ||' sucessfully'
      
    );    

  END LOOP;
 
  --display devision
  DBMS_OUTPUT.PUT_LINE('-------------------------------END-------------------------------------');

  --dispaly the number of dropped tables
  DBMS_OUTPUT.PUT_LINE('Total:'||v_product_count );
 
  -- step 5: close the cursor
  CLOSE cv_product_cursor;
 
EXCEPTION
  WHEN OTHERS THEN    
   
  DBMS_OUTPUT.PUT_LINE(v_product_count+1 ||' : '||
      'dorp table ' || v_product_name ||' Failed'
      
    );   
  --display devision
  DBMS_OUTPUT.PUT_LINE('-------------------------------END-------------------------------------');

  --dispaly the number of dropped tables
  DBMS_OUTPUT.PUT_LINE('Total:'||v_product_count );

  DBMS_OUTPUT.PUT_LINE('------------------------------ERRORMSG---------------------------------');
  DBMS_OUTPUT.PUT_LINE(v_product_count+1||' : '||SQLERRM);
  DBMS_OUTPUT.PUT_LINE('------------------------------ERRORMSG---------------------------------');

  -- step 5: close the cursor
  CLOSE cv_product_cursor;

END;
/