在存储过程里,通过参数传入表名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;
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;
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;
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;
随了只会一步一趋地照别人说的去做而自己不花一点心思去想, 去思考, 再怎么帮助也没用的
PLS-00201: 必须说明标识符
就已经指出有些变量(COL1, COL2)没有定义,你不会连这基本的分析能力都没有吧?难道你看不出来我上面写的那些代码只是伪代码吗?