oracle怎么循环查询下一条记录

发布网友发布时间：2022-04-08 07:04

共4个回答

热心网友时间：2022-04-08 08:33

以下为样例：
……
type tab_partition_name is table of all_ind_partitions.partition_name%type;
type tab_index_name is table of all_indexes.index_name%type;
type tab_last_analyzed is table of all_indexes.last_analyzed%type;
vt_partition_nametab_partition_name;
vt_index_nametab_index_name;
vt_last_analyzed tab_last_analyzed;

begin

o_err := 'Successfully!';
v_owner := upper(rtrim(i_owner));
v_tablename := upper(rtrim(i_tablename));

-- select indexes of the table to rebuild
-- Command: alter index <index name> rebuild [partition <partition name>] online;
select a.index_name,b.partition_name,a.last_analyzed
bulk collect into vt_index_name,vt_partition_name,vt_last_analyzed
from all_indexes a,all_ind_partitions b
where a.table_owner=v_owner
and a.table_name=v_tablename
and a.owner=b.index_owner(+)
and a.index_name=b.index_name(+);

if vt_index_name.FIRST IS NULL OR vt_index_name.FIRST=0 THEN
-- 'No index to rebuild!'
o_err := 'The table of ['||v_owner||'.'||v_tablename||'] has not any index to rebuild!';
RETURN;
END IF;

for i_index in vt_index_name.FIRST..vt_index_name.LAST loop

if vt_partition_name(i_index) is null or length(trim(vt_partition_name(i_index)))=0 then
-- can not rebuild online in store procere
if vt_last_analyzed is null then
v_execsql := 'alter index '||v_owner||'.'||vt_index_name(i_index)||' rebuild online';
else
v_execsql := 'alter index '||v_owner||'.'||vt_index_name(i_index)||' rebuild compute statistics online';
end if;
o_err := 'Alter index ['||v_owner||'.'||vt_index_name(i_index)||'] rebuild failed!';
else
if vt_last_analyzed is null then
v_execsql := 'alter index '||v_owner||'.'||vt_index_name(i_index)||' rebuild partition '||vt_partition_name(i_index)||' online';
else
v_execsql := 'alter index '||v_owner||'.'||vt_index_name(i_index)||' rebuild partition '||vt_partition_name(i_index)||' compute statistics online';
end if;
o_err := 'Alter index ['||v_owner||'.'||vt_index_name(i_index)||':'||vt_partition_name(i_index)||'] rebuild failed!';
end if;
begin
execute immediate v_execsql;
exception
when others then
o_err := o_err||chr(10)||sqlerrm;
return;
end;
end loop;
……

热心网友时间：2022-04-08 09:51

使用 Oracle 的分析函数

LAG/LEAD
LAG 为取当前行的前 n 行的数据
LEAD为取当前行的前 n 行的数据

处理起来非常的简单。

参考资料：http://hi.baidu.com/wangqing999/blog/item/a2b22c3a991776ed828b13cc.html

热心网友时间：2022-04-08 11:26

SELECT T2.PLAN_ID,T2.OPENDATE,T2.CLOSEDATE
INTO v_planId,v_openDate,v_closeDate
FROM (select rownum r_num, t.*TB_COURSEPLAN T
WHERE T.ROOM_ID = i_roomId) t2 where t2.r_num=i;

热心网友时间：2022-04-08 13:17

for rec in (SELECT T.PLAN_ID,T.OPENDATE,T.CLOSEDATE
INTO v_planId,v_openDate,v_closeDate
FROM TB_COURSEPLAN T)
loop
--your code
end loop;