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

能否根据表名和字段名字符串进行查询?
在存储过程里,通过参数传入表名table_name和列名col_name,都是字符串型的。

现在想在存储过程中查询table_name中的col_name字段,应该怎么写啊?

我现在这样写但报错啊!

create   or   replace   procedure   validdata1(table_name   varchar2,col_name   varchar2)
is
  cursor   cur1  
      is  
      select   *   from   $table_name   where   (floor(col_name/100)   not   between   1900   and   2010   )   or   ((col_name   -   floor(col_name/100)*100)   not   between   1   and   12);
  begin
      for   line   in   cur1
          loop  
                  dbms_output.put_line(line.col_name);
          end   loop;
end;


------解决方案--------------------
改成:

create or replace procedure validdata1(table_name varchar2,col_name varchar2)

is
TYPE RefCur is REF CURSOR;
cur1 RefCur;
V_SQL VARCHAR2(5000);
/*
cursor cur1
is
select * from $table_name where (floor(col_name/100) not between 1900 and 2010 ) or ((col_name - floor(col_name/100)*100) not between 1 and 12);
*/
begin
V_SQL = 'select * from ' || $table_name || 'where (floor( ' ||
col_name || '/100) not between 1900 and 2010 ) or (( ' || col_name ||
' - floor( ' || col_name || '/100)*100) not between 1 and 12) ';
OPEN cur1 FOR V_SQL;
for line in cur1
loop
dbms_output.put_line(line.col_name);
end loop;

CLOSE cur1;
end;
------解决方案--------------------
OPEN cur1 FOR V_SQL;
for line in cur1 loop
dbms_output.put_line(line.col_name);
end loop;

改成:


LOOP
FETCH cur1 INTO COL1, COL2,...
EXIT WHEN cur1%NOTFOUND;
END LOOP;
------解决方案--------------------
FETCH cur1 INTO COL1, COL2,... 后面加分号