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
如不能,有替代方案吗???
解决方案 »
- 数据库小问题
- 大家帮我看一个oracle查询语句中的临时变量的问题
- select df,fdf,sdf, from tabe1 a,tale2 b where b.date>=sysdate-10 and a.date(+)>=sysdate-10
- 本机如何连接局域网oracle9i数据库???????
- 用powerdesigner设计问题
- 软回车的字符是什么?
- 请问如何在FAT32分区的磁盘上安装ORACLE8I以上版本的SERVER
- 一个不知道能不能解决的问题,有关计算字段。还望各位有经验的献计献策才是亚。
- 关于资源使用者组的问题
- oracle报无效数字,请问我怎么查出来哪个数值有问题?
- Oracle的问题
- 问个oracle小问题
Execute immediate
参考:http://hi.baidu.com/38608338/blog/item/b3508bdc613bf9e576c638ff.html
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;
Execute immediate 'select max('||column_name||') into maxId from '||table_name ;
commit;
dbms_output.put_line(table_name||'-'||column_name);
end loop;
end test4;
sqlstr := 'select max('||column_name||') from '||table_name;
execute immediate into maxId;
嘿嘿,这句运行的时候报 关键字错误。
用你的execute immediate into maxId; 就没问题。
要带into 只能 execute immediate str_sql into xxx;还有一个问题 不是这个变量用不了 如果cursor 定义的时候 select语句中的表名是变量的时候怎么办?
str_sql varchar2(1000);
type my_cursor is ref cursor;
latn_cursor my_cursor;
BEGIN
begin --开始游标
str_sql:='select latn_id from ods_latn_id where table_code='''||v_table_code||''' and update_flag=0';
open latn_cursor for str_sql; --打开游标
loop
fetch latn_cursor into v_latn_id;
bms_output.put_line(v_latn_id);
end loop;
close latn_cursor;
END;