DECLARE
str VARCHAR2(10000);
va VARCHAR2(10000);
d1 DATE;
d2 DATE;
i NUMBER;
userrow user_views%rowtype;
CURSOR userrows IS
SELECT
view_name
FROM
user_views
ORDER BY 1;
BEGIN
i := 0;
dbms_output.put_line('序号'
|| chr(9)
|| '视图名'
|| chr(9)
|| '返回行数'
|| chr(9)
|| '耗时(秒)');
FOR userrow IN userrows LOOP
i := i + 1;
d1 := SYSDATE;
str := 'SELECT COUNT(0) C FROM ' || userrow.view_name;
EXECUTE IMMEDIATE str INTO
va;
d2 := SYSDATE;
dbms_output.put_line(i
|| chr(9)
|| userrow.view_name
|| chr(9)
|| va
|| chr(9)
|| round( (d2 - d1) * 34 * 60 * 60) );
END LOOP;
END;