查詢多張表記錄數的儲存過程

下面的儲存過程，略加修改後，可以用來檢視某個使用者所有表的記錄數，每個表佔用的空間等[@more@]

CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN INTEGER AS
rows INTEGER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;
RETURN rows;
END;
/

SQL> declare
2 type TabCurTyp is ref cursor;
3 tab_cv TabCurTyp;
4 v_tname varchar2(20);
5 v_sql varchar2(200);
6 v_count number;
7 begin
8 open tab_cv for
9 'select tname from tab where tname in (''EMP'',''DEPT'',''BONUS'')';
10 LOOP
11 fetch tab_cv into v_tname;
12 exit when tab_cv%NOTFOUND;
13 dbms_output.put_line('table name: '||v_tname);
14 v_count := row_count(v_tname);
15 dbms_output.put_line('row number:'||to_char(v_count));
16 dbms_output.put_line(' ');
17 END LOOP;
18 CLOSE tab_cv;
19 end;
20 /

執行結果：
table name: BONUS
row number:0
table name: DEPT
row number:4
table name: EMP
row number:14

PL/SQL procedure successfully completed.