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

在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;