日期:2014-05-17  浏览次数:21137 次

怎么将查询结果作为表名继续使用?
我写了一个存过,先是找数据库里的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。大家有没有什么解决办法?

------解决方案--------------------
此处 v_table_name 并不是表、而是个collections
v_table_name.count
------解决方案--------------------
EXECUTE IMMEDIATE 'drop table v_table_name';改为
EXECUTE IMMEDIATE 'drop table '||v_table_name(i);
------解决方案--------------------
SQL code

EXECUTE IMMEDIATE 'select count(1) from '||v_table_name(i) into v_count;

------解决方案--------------------
我觉得你可以看看这个,使用with来将查询作为临时表

create or replace procedure proc_add(IN_DATE IN VARCHAR2)
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;
------解决方案--------------------
是不是 存在一些特殊的字符 导致拼接的SQL语句错误了 你打印出来再去查询

DBMS_OUTPUT.put_line('select count(1) from '||v_table_name(i))

看打印的结果