我写了一个存过,先是找数据库里的tmp开头的表,找到后判断一下这个表是否有记录,如果没有记录的话就drop掉。实现的过程如下:
create or replace procedure p_tmp_table is
type v_table_name_list is table of varchar2(32);
v_table_name v_table_name_list;
v_count integer;begin
select table_name bulk collect into v_table_name from user_tables where table_name like 'TMP_%';
for i in v_table_name.first..v_table_name.last
loop
select count(*) into v_count from v_table_name;
if v_count = 0 then
EXECUTE IMMEDIATE 'drop table v_table_name';
dbms_output.put_line('table '||v_table_name||' has been droped!'); end if;
end loop;end p_tmp_table;但是这里select count(*) into v_count from v_table_name;有问题,提示说找不到表名v_table_name。大家有没有什么解决办法?
create or replace procedure p_tmp_table is
type v_table_name_list is table of varchar2(32);
v_table_name v_table_name_list;
v_count integer;begin
select table_name bulk collect into v_table_name from user_tables where table_name like 'TMP_%';
for i in v_table_name.first..v_table_name.last
loop
select count(*) into v_count from v_table_name;
if v_count = 0 then
EXECUTE IMMEDIATE 'drop table v_table_name';
dbms_output.put_line('table '||v_table_name||' has been droped!'); end if;
end loop;end p_tmp_table;但是这里select count(*) into v_count from v_table_name;有问题,提示说找不到表名v_table_name。大家有没有什么解决办法?
v_table_name.count
EXECUTE IMMEDIATE 'drop table '||v_table_name(i);
但是select count(*) into v_count from v_table_name(i);
这个改成这样后又报错:SQL command not properly ended.这个地方怎么办呢?
EXECUTE IMMEDIATE 'select count(1) from '||v_table_name(i) into v_count;
AS
strSQLA varchar2(1000);
strSQLB VARCHAR2(1000);
begin
/*
strSQLA:='create table TABLE_TMP AS SELECT TOTAL FROM ( SELECT COUNT(*) TOTAL
FROM job_info WHERE JOBDATE=TO_DATE('||IN_DATE||','||'''YYYYMMDD'''||') UNION ALL
SELECT COUNT(*) TOTAL
FROM JOB_INFO_20100729 WHERE JOBDATE=TO_DATE('||IN_DATE||','||'''YYYYMMDD'''||')
)' ;
*/
strSQLA:='WITH TABLE_TMP AS (SELECT TOTAL FROM ( SELECT COUNT(*) TOTAL
FROM job_info WHERE JOBDATE=TO_DATE('||IN_DATE||','||'''YYYYMMDD'''||') UNION ALL
SELECT COUNT(*) TOTAL
FROM JOB_INFO_20100729 WHERE JOBDATE=TO_DATE('||IN_DATE||','||'''YYYYMMDD'''||')
)) SELECT * FROM TABLE_TMP' ;
DBMS_OUTPUT.put_line('strSQLA :'||strSQLA);
execute immediate strSQLA;
strSQLB:='INSERT INTO PRO_TABLE SELECT SUM(TOTAL) FROM TABLE_TMP';
DBMS_OUTPUT.put_line('strSQLB :'||strSQLB);
EXECUTE IMMEDIATE strSQLB;
COMMIT ;
END proc_add;
晕死,这怎么回事
type v_table_name_list is table of varchar2(32);
v_table_name v_table_name_list;begin
select table_name bulk collect into v_table_name from user_tables where table_name like 'TMP_%';
for i in v_table_name.first..v_table_name.last
loop
DBMS_OUTPUT.put_line('select count(1) from '||v_table_name(i));
end loop;end p_tmp_table;