declare -- Local variables here i integer; v_SQL VARCHAR2(200); v_msg VARCHAR2(200); begin -- Test statements here i:=0; FOR v IN (SELECT ut.table_name FROM User_Tables ut where --ut.table_owner='ACCT' AND ut.table_name in ('ACCT_ITEM_OWE','PAYMENT','BILL','ACCT_BALANCE', 'BALANCE_SOURCE','BALANCE_PAYOUT','BALANCE_SOURCE_PAYOUT_RELA','INVOICE') /* AND (NOT BUFFER_POOL IS NULL)*/) LOOP begin v_sql:='drop table '||v.table_name; EXECUTE IMMEDIATE v_sql; i:=i+1; exception WHEN OTHERS THEN v_msg:=SQLERRM; dbms_output.put_line(i||v_sql); dbms_output.put_line('error:'||v_msg); end; END LOOP; dbms_output.put_line('drop了'||i||'个表');
EXCEPTION WHEN OTHERS THEN v_msg:=SQLERRM; dbms_output.put_line(i||v_sql);
-- Local variables here
i integer;
v_SQL VARCHAR2(200);
v_msg VARCHAR2(200);
begin
-- Test statements here
i:=0; FOR v IN (SELECT ut.table_name FROM User_Tables ut
where
--ut.table_owner='ACCT' AND
ut.table_name in ('ACCT_ITEM_OWE','PAYMENT','BILL','ACCT_BALANCE',
'BALANCE_SOURCE','BALANCE_PAYOUT','BALANCE_SOURCE_PAYOUT_RELA','INVOICE')
/* AND (NOT BUFFER_POOL IS NULL)*/) LOOP
begin
v_sql:='drop table '||v.table_name;
EXECUTE IMMEDIATE v_sql; i:=i+1;
exception
WHEN OTHERS THEN
v_msg:=SQLERRM;
dbms_output.put_line(i||v_sql);
dbms_output.put_line('error:'||v_msg);
end;
END LOOP;
dbms_output.put_line('drop了'||i||'个表');
EXCEPTION
WHEN OTHERS THEN
v_msg:=SQLERRM;
dbms_output.put_line(i||v_sql);
dbms_output.put_line(i||v_sql);
dbms_output.put_line(v_msg);
end;
v_sql:= 'drop table ' ¦ ¦v.table_name; 这句可以稍加解释吗?
v_flag boolean default true;
v_fsql VARCHAR2(2000);
v_nsql VARCHAR2(2000);
v_fmsg VARCHAR2(2000);
v_nmsg VARCHAR2(2000);
v_err integer default 0;
v_cont integer default 0;
cursor v_query is
SELECT table_name FROM User_Tables where 1 = 1;
begin
FOR v IN v_query LOOP
begin
v_fsql := 'drop table ' || v.table_name;
v_cont := v_query%ROWCOUNT;
--EXECUTE IMMEDIATE v_fsql;
exception
WHEN OTHERS THEN
v_flag := true;
v_fmsg := SQLERRM;
begin
v_nsql := 'drop table "' || v.table_name || '" ';
EXECUTE IMMEDIATE v_nsql;
exception
when others then
v_nmsg := SQLERRM;
dbms_output.put_line('ExeSQL: ' || v_nsql || '@Error: ' ||
v_nmsg);
v_flag := false;
end;
if (v_flag = false) then
dbms_output.put_line('ExeSQL: ' || v_fsql || '@Error: ' ||
v_fmsg);
v_err := v_err + 1;
end if;
end;
END LOOP;
dbms_output.put_line('成功删除了' || (v_cont - v_err) || '个表, 失败了' ||
v_err || '个表');
EXCEPTION
WHEN OTHERS THEN
v_fmsg := SQLERRM;
dbms_output.put_line('@Error: ' || v_fmsg);
end;