declare
ncount number;
begin
select count(*) into ncount from user_tables
where table_name = 'STAT_1DAY_DOMAIN_' || 2424;
if ncount>0 then
execute immediate 'drop table STAT_1DAY_DOMAIN_2424';
end if;
end;
第一个方法测试使用成功declare
ncount number;
begin
select count(*) into ncount from user_tables
where table_name = 'STAT_1DAY_DOMAIN_' || user_tables;
if ncount>0 then
execute immediate 'drop table' 'STAT_1DAY_DOMAIN_' || user_tables; end if;
end;
我将上面第二个方法放入到存储过程中进行对动态表名的操作但是删除表名的语句提示表名错误,应该如何改写
ncount number;
begin
select count(*) into ncount from user_tables
where table_name = 'STAT_1DAY_DOMAIN_' || 2424;
if ncount>0 then
execute immediate 'drop table STAT_1DAY_DOMAIN_2424';
end if;
end;
第一个方法测试使用成功declare
ncount number;
begin
select count(*) into ncount from user_tables
where table_name = 'STAT_1DAY_DOMAIN_' || user_tables;
if ncount>0 then
execute immediate 'drop table' 'STAT_1DAY_DOMAIN_' || user_tables; end if;
end;
我将上面第二个方法放入到存储过程中进行对动态表名的操作但是删除表名的语句提示表名错误,应该如何改写
调试 是 写存储过程很好的测试方法。
declare
ncount number;
begin
select count(*) into ncount from user_tables
where table_name = 'STAT_1DAY_DOMAIN_' || 'user_tables';
if ncount>0 then
execute immediate 'drop table STAT_1DAY_DOMAIN_' || 'user_tables';
end if;
end;
ncount number;
begin
select count(*) into ncount from user_tables
where table_name = 'STAT_1DAY_DOMAIN_' || user_tables;
if ncount>0 then
execute immediate 'drop table' 'STAT_1DAY_DOMAIN_' || user_tables; end if;
end;
--创建表
create table STAT_1DAY_DOMAIN_test(id int);
/
select * from STAT_1DAY_DOMAIN_test;
/
--创建sp
--http://topic.csdn.net/u/20120530/11/3a88bd75-c7b5-4c8e-afe2-80b6872df06a.html?124
create or replace procedure sp_dropTable(v_tbname in varchar2)
as
v_sql varchar2(4000);
begin
v_sql:='drop table STAT_1DAY_DOMAIN_'||v_tbname;
execute immediate v_sql;
end sp_dropTable;
/
--执行sp
begin
sp_dropTable('test') ;
end;
/
--查询表
--select * from STAT_1DAY_DOMAIN_test;
--创建表
create table STAT_1DAY_DOMAIN_test(id int);
/
select * from STAT_1DAY_DOMAIN_test;
/
--创建sp
create or replace procedure sp_dropTable(v_tbname in varchar2)
as
v_sql varchar2(4000);
begin
v_sql:='drop table STAT_1DAY_DOMAIN_'||v_tbname;
execute immediate v_sql;
--异常处理
EXCEPTION
when others then
dbms_output.put_line('table not exists');
end sp_dropTable;
/
--执行sp
begin
sp_dropTable('test') ;
end;
/
--查询表
--select * from STAT_1DAY_DOMAIN_test;
这句,为何要将table_name串接user_tables??
这句,为何要将table_name串接user_tables??