3. 查询schema下所有表的记录数
-- 创建计算表记录数的函数[@more@]3. 查询schema下所有表的记录数
-- 创建计算表记录数的函数
CREATE OR REPLACE FUNCTION sf_tab_count(iv_table_name IN VARCHAR2)
RETURN NUMBER IS
v_cursor_handle INTEGER; -- 句柄
v_count INTEGER; -- 计数器
v_text VARCHAR2(4000); -- 运算PL/SQL串
r_num NUMBER;
BEGIN
v_text := v_text || 'DECLARE v_num NUMBER; BEGIN SELECT COUNT(*) INTO :v_num FROM '||iv_table_name||'; END;';
IF DBMS_SQL.IS_OPEN(v_cursor_handle) THEN
DBMS_SQL.CLOSE_CURSOR(v_cursor_handle);
END IF;
v_cursor_handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor_handle, v_text, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(v_cursor_handle, 'v_num', r_num);
v_count := DBMS_SQL.EXECUTE(v_cursor_handle);
DBMS_SQL.VARIABLE_VALUE(v_cursor_handle, 'v_num', r_num);
DBMS_SQL.CLOSE_CURSOR(v_cursor_handle);
RETURN r_num;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_cursor_handle);
RETURN NULL;
END sf_tab_count;
-- 查询schema下表的记录数
SELECT owner "用户", table_name "表名", sf_tab_count(owner||'.'||table_name) "记录数" FROM DBA_TABLES
WHERE owner IN ('HR', 'SCOTT')
ORDER BY owner, table_name;