Question

用脚本如何统计ORACLE当前用户下所有表中的记录数

Answer 1

CREATE OR REPLACE FUNCTION calcrows
RETURN NUMBER
IS
v_sqlstr VARCHAR2 (4000);
v_totalrows NUMBER;
v_temprows NUMBER;
BEGIN
v_totalrows := 0;

FOR c IN (SELECT t.TABLE_NAME
FROM user_tables t)
LOOP
v_sqlstr := 'select count(*) from ' || c.table_name;

BEGIN
EXECUTE IMMEDIATE v_sqlstr INTO v_temprows;
EXCEPTION
WHEN OTHERS
THEN
v_temprows := 0;
END;

v_totalrows := v_totalrows + v_temprows;
END LOOP;

RETURN v_totalrows;
END calcrows;
/
先创建这个function，然后再select calcrows from dual;就好了