oracle 存储过程是否支持多层游标循环

我来答

Edward

2021-04-21

10M

oracle

需求：
1、想做个自动查找HW表的存储过程，定时查找高水位表并进行相应的回收。

实现：
1、写了个存储过程，分成两个游标循环，第一个循环查找表并执行HW的回收；
2、在存储过程中第二个循环查询由于第一步回收表导致表上的索引失效，执行重建索引。

问题：
存储过程编写完成后，执行时第一个游标循环执行了，第二个没有执行。见标记黄色的位置

create PROCEDURE Clean_HW_Table
as
S_SQL VARCHAR2(2000);
t_tablename varchar2(255);
t_index_name varchar2(2000);
CURSOR c1 IS SELECT
D.TABLE_NAME
FROM DBA_TABLES D
WHERE D.BLOCKS > 10
AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5
AND d.OWNER =(SELECT SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) ur FROM DUAL)
AND d.TABLE_NAME not like ‘VT%’;
BEGIN
BEGIN
FOR x IN c1
LOOP
t_tablename := x.TABLE_NAME;
S_SQL :=‘alter table ‘||t_tablename ||’ deallocate unused’;
EXECUTE IMMEDIATE S_SQL;
S_SQL :=‘alter table ‘||t_tablename ||’ move’;
EXECUTE IMMEDIATE S_SQL;
dbms_stats.gather_table_stats(‘EAS1’,t_tablename, estimate_percent => 100,cascade=>TRUE, method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,degree =>6);
END LOOP;
END;
BEGIN
FOR LINE2 IN (SELECT owner, index_name
FROM dba_indexes
WHERE owner =(SELECT SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) ur FROM DUAL)
AND status NOT IN (‘VALID’, ‘N/A’)
ORDER BY owner, index_name
) LOOP
t_index_name:= ‘alter index ’ || LINE2.OWNER ||’.’|| LINE2.index_name || ’ rebuild online nologging’;
DBMS_OUTPUT.PUT_LINE (t_index_name);
EXECUTE IMMEDIATE t_index_name;
END LOOP;
END;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE (‘NO_DATA_FOUND’);
RETURN;
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (‘OTHERS’);
RETURN;
END;
/

我来答

添加附件

问题补充

4条回答

默认

最新

你好我是李白

一个begin跟一个end就可以了。

begin

for ... loop
...
end loop

for ... loop
...
end loop

end;
/

复制

有用 1

打赏 0

cqiwen

你这个应该把重建索引的写在第一个begin end 的循环体中，即收集好统计信息后，马上重建对应表的索引，这样才能1V1对应上你要重建哪一个表的索引，否则如果前面要收缩高水位的表很多，则可能在收缩完成所有表之前，导致这些表的索引全部失效，影响正常生产。

有用 0

打赏 0

Edward

create or replace PROCEDURE Clean_HW_Table
as
S_SQL VARCHAR2(2000);
t_table_name varchar2(255);
t_index_name varchar2(2000);
CURSOR c1 IS SELECT
D.TABLE_NAME
FROM DBA_TABLES D
WHERE D.BLOCKS > 10
AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5
AND d.OWNER =(SELECT SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) ur FROM DUAL)
AND d.TABLE_NAME not like ‘VT%’;
BEGIN
FOR x IN c1
LOOP
t_table_name := x.TABLE_NAME;
S_SQL :=‘alter table ‘||t_table_name ||’ deallocate unused’;
EXECUTE IMMEDIATE S_SQL;
S_SQL :=‘alter table ‘||t_table_name ||’ move’;
EXECUTE IMMEDIATE S_SQL;
dbms_stats.gather_table_stats(‘EAS1’,t_table_name, estimate_percent => 100,cascade=>TRUE, method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,degree =>6);
END LOOP;
FOR LINE2 IN (SELECT owner, index_name
FROM dba_indexes
WHERE owner =(SELECT SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) ur FROM DUAL)
AND status NOT IN (‘VALID’, ‘N/A’)
ORDER BY owner, index_name
) LOOP
t_index_name:= ‘alter index ’ || LINE2.OWNER ||’.’|| LINE2.index_name || ’ rebuild online nologging’;
DBMS_OUTPUT.PUT_LINE (t_index_name);
EXECUTE IMMEDIATE t_index_name;
END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE (‘NO_DATA_FOUND’);
RETURN;
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (‘OTHERS’);
RETURN;
END;
各位老师，目前的问题是第二段重建索引不执行，是那里写的不对吗？

有用 0

打赏 0

DarkAthena

只要游标里有数据，就是可以执行的，所以你先把第二个游标拿出来直接查下，看看是不是有数据。另外你这里写了几个异常处理，全部都是正常返回，如果第一个游标的循环里，某一条已经报错了，当然第二个循环就不会再跑了，所以建议你先把这些异常处理都拿掉，看看有些什么错

有用 1

打赏 0

回答交流

Markdown

请输入正文

导入word

提交