在Oracle的游标中,会select * from table; 这个table是变量吗
在Oracle的游标中,能select * from table; 这个table是变量吗
加了这句就通过不了:
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;
- 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;