在Oracle的游标中,能select * from table; 这个table是变量吗
SQL code
create or replace procedure test
as
cursor ctables is select * from all_t_pk;
each_row ctables%rowtype;
table_name varchar(255);
column_name varchar(255);
maxId number;
begin
open ctables;
loop
fetch ctables into each_row;
exit when ctables%notfound;
table_name := each_row.table_name;
column_name := each_row.column_name;
--问题就在这个代码,我想column_name 和table_name 都是变量??
select max(column_name) into maxId from table_name;
dbms_output.put_line(table_name||'-'||column_name);
end loop;
end test;
加了这句就通过不了:
select max(column_name) into maxId from table_name;
能否这样动态指定字段和表呢??大侠们,急救!!!
下面是游标遍历的表。都是些表和主键。
select * from all_t_pk;
TABLE_NAME COLUMN_NAME
------------------------------ --------------------------------------------
COMMON_ANSWER ANSWER_ID
COMMON_ANSWER_DETAILS ANSWER_DETAILS_ID
COMMON_EXEM EXAM_ID
COMMON_EXEM_DETAILS EXAM_DETAIL_ID
CONFIG_MAJOR MAJOR_ID
CONFIG_MAJOR_KIND MAJOR_KIND_ID
CONFIG_PUBLIC_ATTRIBUTE ATTRIBUTE_ID
CONFIG_QUESTIONS QUESTION_ID
CONFIG_QUESTION_OPTION OPTION_ID
ENGAGE_INERVIEW INTERVIEW_ID
ENGAGE_MAJOR_ISSUE MAJOR_ISSUE_ID
ENGAGE_RESUME RESUME_ID
HUMAN_ARCHIVE HUMANID
HUMAN_ARCHIVE_CHANGE HUMANCHANGEID
HUMAN_ATTENDANCE TIME_SHEET_ID
HUMAN_BONUS BONUSID
HUMAN_MAJOR_CHANGE MAJORCHANGEID
MERITRATING MERITRATINGID
MERITRATINGDETAIL MERITRATINGDETAILSID
MERITRATINGTEMPLATE TEMPLATEID
如不能,有替代方案吗???
------解决方案--------------------
这个需要用动态SQL拼凑。
sqlstr := 'select max('||column_name||') from '||table_name;
execute immediate into maxId;