如果在存储过程的话只能用一个包来实现,声明一个游标类型,然后定义一个游标变量. declare cursor c_rc is select .... where.... v_rc c_rc%rowtype;begin open c_rc; fetch c_rc into v_rc; .... 输出v_rc.col1 ..... end;
9i例子.create global temporary table ao on commit PRESERVE ROWS as select * from all_objects where 1=0;alter table ao add constraint ao_pk primary key(object_id);insert into ao select * from all_objects;create table t1 as select * from all_objects where rownum<= (select count(*)/2 from all_objects);alter table t1 add constraint t1_pk primary key(object_id);analyze table t1 compute statistics for table for all indexes for all indexed columns;create table t2 as select * from all_objects where rownum<= (select count(*)/2 from all_objects);alter table t2 add constraint t2_pk primary key(object_id); Table altered.analyze table t2 compute statistics for table for all indexes for all indexed columns; Table analyzed.So, t1 and t2 are for all intents and purposes the same -- we'll upsert them from AO..declare l_start number; l_run2 number; begin l_start := dbms_utility.get_time; for x in ( select * from ao ) loop update t2 set ROW = x where object_id = x.object_id; if ( sql%rowcount = 0 ) then insert into t2 values X; end if; end loop; commit; l_run2 := (dbms_utility.get_time-l_start); dbms_output.put_line( l_run2 || ' hsecs' ); end;
必须: 变量.字段1,变量.字段2 ...我想知道有没有什么快捷的方法??
一个个地用变量里面字段。
难道ORACLE这么让人失望?
declare
cursor c_rc is
select ....
where....
v_rc c_rc%rowtype;begin
open c_rc;
fetch c_rc into v_rc;
....
输出v_rc.col1
.....
end;
on commit PRESERVE ROWS
as
select * from all_objects where 1=0;alter table ao
add constraint
ao_pk primary key(object_id);insert into ao select * from all_objects;create table t1
as
select *
from all_objects
where rownum<= (select count(*)/2 from all_objects);alter table t1 add constraint t1_pk primary key(object_id);analyze table t1 compute statistics
for table for all indexes for all indexed columns;create table t2
as
select *
from all_objects
where rownum<= (select count(*)/2 from all_objects);alter table t2 add constraint t2_pk primary
key(object_id);
Table altered.analyze table t2 compute statistics
for table for all indexes for all indexed columns;
Table analyzed.So, t1 and t2 are for all intents and purposes the same -- we'll upsert them
from AO..declare
l_start number;
l_run2 number;
begin
l_start := dbms_utility.get_time;
for x in ( select * from ao )
loop
update t2 set ROW = x where object_id = x.object_id;
if ( sql%rowcount = 0 )
then
insert into t2 values X;
end if;
end loop;
commit;
l_run2 := (dbms_utility.get_time-l_start);
dbms_output.put_line( l_run2 || ' hsecs' );
end;