Sql代码

复制代码 代码如下:

select count(*) from user_objects where object_name=upper(p_table_name);

select count(*) from user_tables where table_name=upper(p_table_name);

create or replace procedure p_drop_table_if_exist_v1(

p_table_name in varchar2

) is

v_count number(10);

begin

select count(*)

into v_count

from user_objects

where object_name=upper(p_table_name);

if v_count > 0 then

execute immediate 'drop table ' || p_table_name || ' purge';

end if;

exception

when no_data_found then

begin

null;

end;

end;

/

create or replace procedure p_drop_table_if_exist_v2(

p_table_name in varchar2

) is

v_table_name varchar2(20);

begin

select table_name

into v_table_name

from user_tables

where table_name=upper(p_table_name);

if length(v_table_name)>0 then

execute immediate 'drop table ' || p_table_name || ' cascade constraints';

end if;

exception

when no_data_found then

begin

null;

end;

end;

/