能否根据表名和字段名字符串进行查询？

[kobe21]

在存储过程里，通过参数传入表名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;

[DragonBill]

改成:

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;

[kobe21]

在for line in cur1这行报错：CUR1不是过程名或尚未定义？

[DragonBill]

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;

[kobe21]

这个EXIT又不对了。。。PLS-00103：出现符号“EXIT”在需要下列之一时：.(,%;limit

[wsklt]

FETCH cur1 INTO COL1, COL2,... 后面加分号

[wvivw]

哈

[nkannn]

加了分号会出现“PLS-00201: 必须说明标识符”的问题，这个怎么办？

[DragonBill]

没有办法了, 因为你连简单的分析能力都没有








随了只会一步一趋地照别人说的去做而自己不花一点心思去想, 去思考, 再怎么帮助也没用的


PLS-00201: 必须说明标识符
就已经指出有些变量(COL1, COL2)没有定义,你不会连这基本的分析能力都没有吧?

难道你看不出来我上面写的那些代码只是伪代码吗?