(1)从数据库中查询出所有开头为Info的表
(2)然后计算出每个Info表的总记录数
(3)最后需要的结果集是表名和对应得总记录数这个问题我提问过,
http://topic.csdn.net/u/20110610/09/88c9538d-e190-41d7-91e9-a3c8f3d886c7.html
以下语句可以解决问题:
execute dbms_stats.gather_schema_stats('用户名');
select t.table_name,t.num_rows from user_tables t where t.table_name like 'Info%'但是由于此方案得调用 execute dbms_stats.gather_schema_stats('用户名');如果用户下面的表众多的话,每次调用该语句会消耗大量的时间。因此我想 1)先从user_tables中查询出所有开头为Info的表
2)在Info开头的表中,用select count(*) form Info_table,查询该表的记录数
请问如何实现?不知能否写成一个存储过程。
(2)然后计算出每个Info表的总记录数
(3)最后需要的结果集是表名和对应得总记录数这个问题我提问过,
http://topic.csdn.net/u/20110610/09/88c9538d-e190-41d7-91e9-a3c8f3d886c7.html
以下语句可以解决问题:
execute dbms_stats.gather_schema_stats('用户名');
select t.table_name,t.num_rows from user_tables t where t.table_name like 'Info%'但是由于此方案得调用 execute dbms_stats.gather_schema_stats('用户名');如果用户下面的表众多的话,每次调用该语句会消耗大量的时间。因此我想 1)先从user_tables中查询出所有开头为Info的表
2)在Info开头的表中,用select count(*) form Info_table,查询该表的记录数
请问如何实现?不知能否写成一个存储过程。
SELECT 'select ''' || t.table_name || ''' table_name,count(*) from ' ||
table_name || ';'
FROM user_tables t
WHERE t.table_name LIKE 'INFO%'
然后select t.table_name,queryRecord('t.table_name') from user_tables t where t.table_name like 'Info%';
不知这样可行不,谁弄完了告诉我一声,我也试一下。
CREATE GLOBAL TEMPORARY TABLE table_rows
(table_name varchar2(30),
lines number(18,0)
)
on commit preserve rows;CREATE OR REPLACE PROCEDURE tb_lines_proc(
tb_like in varchar2, -- 模糊搜索表名的字串,例如:INFO
o_cur out sys_refcursor
)
IS
v_table_name varchar2(30);
v_lines number(18,0);
CURSOR cur(p_like IN VARCHAR2) IS
SELECT table_name FROM user_tables WHERE table_name LIKE p_like||'%';
BEGIN
DELETE FROM table_rows;
FOR cr IN cur(tb_like) LOOP
v_table_name := cr.table_name;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v_table_name INTO v_lines;
INSERT INTO table_rows(table_name, lines)
VALUES(v_table_name, v_lines);
END LOOP;
OPEN o_cur FOR 'SELECT table_name, lines FROM table_rows';
COMMIT;
END;
/set serveroutput on;
var c_cur refcursor;
exec tb_lines_proc('INFO',:c_cur);
print c_cur;
CREATE OR REPLACE FUNCTION f_rec_cnt(i_tablename VARCHAR2) RETURN NUMBER IS
v_ret NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select count(*) from ' || i_tablename
INTO v_ret;
RETURN v_ret;
END;
/
------------------------------------------------------------
T_HASHED
TTT
TT
TMP1
TEST1
TEST
TB_TITLE
TB_REPLAY
TB
TABLE2
TABLE1
T2
T1已选择13行。scott@SZTYORA> CREATE GLOBAL TEMPORARY TABLE table_rows
2 (table_name varchar2(30),
3 lines number(18,0)
4 )
5 on commit preserve rows;
create public synonym TABLE_ROWS for scott.TABLE_ROWS表已创建。scott@SZTYORA>
scott@SZTYORA> CREATE OR REPLACE PROCEDURE tb_lines_proc(
2 tb_like in varchar2, -- 模糊搜索表名的字串,例如:INFO
3 o_cur out sys_refcursor
4 )
5 IS
6 v_table_name varchar2(30);
7 v_lines number(18,0);
8 CURSOR cur(p_like IN VARCHAR2) IS
9 SELECT table_name FROM user_tables WHERE table_name LIKE p_like||'%';
10 BEGIN
11 DELETE FROM table_rows;
12 FOR cr IN cur(tb_like) LOOP
13 v_table_name := cr.table_name;
14 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v_table_name INTO v_lines;
15 INSERT INTO table_rows(table_name, lines)
16 VALUES(v_table_name, v_lines);
17 END LOOP;
18 OPEN o_cur FOR 'SELECT table_name, lines FROM table_rows';
19 COMMIT;
20 END;
21 /过程已创建。scott@SZTYORA> set serveroutput on;
scott@SZTYORA> var c_cur refcursor;
scott@SZTYORA> exec tb_lines_proc('INFO',:c_cur);PL/SQL 过程已成功完成。scott@SZTYORA> print c_cur;未选定行scott@SZTYORA> set serveroutput on;
scott@SZTYORA> var c_cur refcursor;
scott@SZTYORA> exec tb_lines_proc('T',:c_cur);PL/SQL 过程已成功完成。scott@SZTYORA> print c_cur;TABLE_NAME LINES
------------------------------------------------------------ ----------
T2 6
TABLE2 3
TABLE1 2
T1 10
TB 19
TEST 14
TMP1 11
TT 0
TB_TITLE 0
TB_REPLAY 0
TEST1 8
T_HASHED 49400
TTT 1
TABLE_ROWS 13已选择14行。
SQL> select t.TABLE_NAME,f_rec_cnt(t.TABLE_NAME) cnt from user_tables t
where t.TABLE_NAME like 'A%';
TABLE_NAME CNT
------------------------------ ----------
ALLOW_USER 0
A111 0
ATEST 33
AB 3
ABC1 8
ABC2 8
ABC 1
A2 10
ADDTOTAL 4
9 rows selected
SQL>